DBD generates invalid base names for projections when table names start with digits

Execute the following to create a test schema, table, and fill it with some dummy data: ---START--- create schema test; create table test."123" (col1 varchar(50)); insert into test."123" values ('Fred'); insert into test."123" values ('Jane'); commit; ---END--- Follow these steps in DBD: - Select the database; - Enter the database's password; - Choose a directory for the DBD output; - Enter a design name (I entered "test"); - Choose Comprehensive design type; - Choose the above "test" schema; - Choose the "Update statistics" and "Deploy design" options. "Optimize with queries" not chosen; - Enter a K-safety value of 1; - Accept Proceed at the confirmation stage. This is the DBD output: ---START--- Database Designer started. For large databases a design session could take a long time; allow it to complete uninterrupted. Use Ctrl+C if you must cancel the session. Setting up design session... Examining table data... No existing projections found. Creating design and deploying projections... [100%] Optimizing storage footprint... Completed of . [ 0%] Deployment in progress... Deploying and generating deployment script... [ 0%] Deploying/Dropping projections... Completed 0 of 4 projections. Completed 0 of 4 projections. WARNING: Count mismatch. Some tables/projections could have been dropped by user before deployment or while deploying Design script is located in /home/dbadmin/gregory/test/dbd/test_design.sql Deployment script is located in /home/dbadmin/gregory/test/dbd/test_deploy.sql Database Designer finished. Press to return to the Administration Tools menu. ---END--- The deployment failed. So I investigated the deployment script that's generated: ---START--- CREATE PROJECTION "123_DBD_1_seg_test_b0" /*+basename(123_DBD_1_seg_test),createtype(D)*/ ( col1 ENCODING AUTO ) AS SELECT col1 FROM test."123" ORDER BY col1 SEGMENTED BY MODULARHASH (col1) ALL NODES OFFSET 0; CREATE PROJECTION "123_DBD_1_seg_test_b1" /*+basename(123_DBD_1_seg_test),createtype(D)*/ ( col1 ENCODING AUTO ) AS SELECT col1 FROM test."123" ORDER BY col1 SEGMENTED BY MODULARHASH (col1) ALL NODES OFFSET 1; select refresh('test."123"'); select make_ahm_now(); DROP PROJECTION test."123_b0", test."123_b1" CASCADE; ---END--- If I execute the script it fails due to the basename starting with digits: ---START--- $ vsql -w password -f test_deploy.sql vsql:test_deploy.sql:11: WARNING 3628: Invalid character in hint at or near "(1" at character 54 vsql:test_deploy.sql:11: WARNING 3628: Invalid character in hint at or near "(12" at character 54 vsql:test_deploy.sql:11: WARNING 3628: Invalid character in hint at or near "(123" at character 54 vsql:test_deploy.sql:11: ROLLBACK 5644: Projection basename "_DBD_1_seg_test" is not a prefix of projection name "123_DBD_1_seg_test_b0" vsql:test_deploy.sql:21: WARNING 3628: Invalid character in hint at or near "(1" at character 54 vsql:test_deploy.sql:21: WARNING 3628: Invalid character in hint at or near "(12" at character 54 vsql:test_deploy.sql:21: WARNING 3628: Invalid character in hint at or near "(123" at character 54 vsql:test_deploy.sql:21: ROLLBACK 5644: Projection basename "_DBD_1_seg_test" is not a prefix of projection name "123_DBD_1_seg_test_b1" refresh ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Refresh completed with the following outcomes: Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)] ---------------------------------------------------------------------------------------- "n/a"."n/a": [n/a] [failed: no such table "test."123""] [] [1] [0] (1 row) make_ahm_now ---------------------------------- AHM set (New AHM Epoch: 1056743) (1 row) vsql:test_deploy.sql:27: ROLLBACK 4122: No up-to-date super projection left on the anchor table of projection 123_b1 HINT: Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead ---END--- However if I try to resolve this by adjusting the deployment script to use double-quotes around the basename in the hints it also fails with warning 3122: Double quotes are not allowed in hint at or near "("" at character 54

Comments

  • Hi Gregory, We have internal ticket opened to fix this issue. This is schedule to fix in Crane release. Thank you, Bhawana
  • Bhawana, Thanks for letting us know. Please could you let us know the internal bug ticket ID to enable us to track it. Also please can I confirm that Crane is the codeword for the next major release of Vertica, which I understand to be planned for some time this month? Thanks, Gregory
  • We're seeing the exact same behavior with Vertica 7.0.
    Trying to execute a projection created by DBD
    that looks like:
    CREATE PROJECTION ad_calls_DBD_1_seg_mvt_query_b1 /* +basename(ad_calls_DBD_1_seg_mvt_query),createtype(D)*/         (
             ad_unit_type ENCODING RLE,
    results in:
     
    [Error Code: 4856, SQL State: HY000]  [Vertica][VJDBC](4856) ERROR: Syntax error at or near "/* +"
    Any suggestions?
  • This happens for us as well - crane did not resolve this issue

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.