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

Gregory_William

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

Bhawana

Hi Gregory, We have internal ticket opened to fix this issue. This is schedule to fix in Crane release. Thank you, Bhawana

Gregory_William

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

Ori_Kremer

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?

Oz_Levi

This happens for us as well - crane did not resolve this issue