bug: reserved words cause compilation error

[pnadolny13]

I have a table that inserted some reserved words like select and update from tap-meltanohub, on initial creation the merge statement fails because of a compilation error due to select/update not being quoted:

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): 01acd554-0603-1f7b-0051-5c83035999a2: SQL compilation error:
syntax error line 1 at position 611 unexpected 'SELECT'.
syntax error line 1 at position 705 unexpected 'UPDATE'.
syntax error line 1 at position 2,589 unexpected 'SELECT'.
syntax error line 1 at position 2,615 unexpected 'UPDATE'.
[SQL: merge into USERDEV_RAW.PNADOLNY_MELTANOHUB_NEW.PLUGINS d using (select $1:description::VARCHAR as DESCRIPTION, $1:label::VARCHAR as LABEL, $1:name::VARCHAR as NAME, $1:logo_url::VARCHAR as LOGO_URL, $1:namespace::VARCHAR as NAMESPACE, $1:variant::VARCHAR as VARIANT, $1:pip_url::VARCHAR as PIP_URL, $1:repo::VARCHAR as REPO, $1:settings::VARIANT as SETTINGS, $1:capabilities::VARIANT as CAPABILITIES, $1:docs::VARCHAR as DOCS, $1:default::BOOLEAN as DEFAULT, $1:id::VARCHAR as ID, $1:plugin_type::VARCHAR as PLUGIN_TYPE, $1:settings_group_validation::VARIANT as SETTINGS_GROUP_VALIDATION, $1:select::VARIANT as SELECT, $1:hidden::BOOLEAN as HIDDEN, $1:metadata::VARIANT as METADATA, $1:update::BOOLEAN as UPDATE, $1:dialect::VARCHAR as DIALECT, $1:target_schema::VARCHAR as TARGET_SCHEMA, $1:executable::VARCHAR as EXECUTABLE, $1:commands::VARIANT as COMMANDS, $1:requires::VARIANT as REQUIRES, $1:_sdc_received_at::TIMESTAMP_NTZ as _SDC_RECEIVED_AT, $1:_sdc_deleted_at::TIMESTAMP_NTZ as _SDC_DELETED_AT, $1:_sdc_extracted_at::TIMESTAMP_NTZ as _SDC_EXTRACTED_AT, $1:_sdc_batched_at::TIMESTAMP_NTZ as _SDC_BATCHED_AT, $1:_sdc_table_version::DECIMAL as _SDC_TABLE_VERSION, $1:_sdc_sequence::DECIMAL as _SDC_SEQUENCE from '@~/target-snowflake/plugins-2825d13f-b9ea-430a-92ae-3dcc0ede7e4f'(file_format => USERDEV_RAW.PNADOLNY_MELTANOHUB_NEW."plugins-2825d13f-b9ea-430a-92ae-3dcc0ede7e4f")) s on d."ID" = s."ID" when matched then update set d."DESCRIPTION" = s."DESCRIPTION", d."LABEL" = s."LABEL", d."NAME" = s."NAME", d."LOGO_URL" = s."LOGO_URL", d."NAMESPACE" = s."NAMESPACE", d."VARIANT" = s."VARIANT", d."PIP_URL" = s."PIP_URL", d."REPO" = s."REPO", d."SETTINGS" = s."SETTINGS", d."CAPABILITIES" = s."CAPABILITIES", d."DOCS" = s."DOCS", d."DEFAULT" = s."DEFAULT", d."ID" = s."ID", d."PLUGIN_TYPE" = s."PLUGIN_TYPE", d."SETTINGS_GROUP_VALIDATION" = s."SETTINGS_GROUP_VALIDATION", d."SELECT" = s."SELECT", d."HIDDEN" = s."HIDDEN", d."METADATA" = s."METADATA", d."UPDATE" = s."UPDATE", d."DIALECT" = s."DIALECT", d."TARGET_SCHEMA" = s."TARGET_SCHEMA", d."EXECUTABLE" = s."EXECUTABLE", d."COMMANDS" = s."COMMANDS", d."REQUIRES" = s."REQUIRES", d."_SDC_RECEIVED_AT" = s."_SDC_RECEIVED_AT", d."_SDC_DELETED_AT" = s."_SDC_DELETED_AT", d."_SDC_EXTRACTED_AT" = s."_SDC_EXTRACTED_AT", d."_SDC_BATCHED_AT" = s."_SDC_BATCHED_AT", d."_SDC_TABLE_VERSION" = s."_SDC_TABLE_VERSION", d."_SDC_SEQUENCE" = s."_SDC_SEQUENCE" when not matched then insert (DESCRIPTION, LABEL, NAME, LOGO_URL, NAMESPACE, VARIANT, PIP_URL, REPO, SETTINGS, CAPABILITIES, DOCS, DEFAULT, ID, PLUGIN_TYPE, SETTINGS_GROUP_VALIDATION, SELECT, HIDDEN, METADATA, UPDATE, DIALECT, TARGET_SCHEMA, EXECUTABLE, COMMANDS, REQUIRES, _SDC_RECEIVED_AT, _SDC_DELETED_AT, _SDC_EXTRACTED_AT, _SDC_BATCHED_AT, _SDC_TABLE_VERSION, _SDC_SEQUENCE) values (s."DESCRIPTION", s."LABEL", s."NAME", s."LOGO_URL", s."NAMESPACE", s."VARIANT", s."PIP_URL", s."REPO", s."SETTINGS", s."CAPABILITIES", s."DOCS", s."DEFAULT", s."ID", s."PLUGIN_TYPE", s."SETTINGS_GROUP_VALIDATION", s."SELECT", s."HIDDEN", s."METADATA", s."UPDATE", s."DIALECT", s."TARGET_SCHEMA", s."EXECUTABLE", s."COMMANDS", s."REQUIRES", s."_SDC_RECEIVED_AT", s."_SDC_DELETED_AT", s."_SDC_EXTRACTED_AT", s."_SDC_BATCHED_AT", s."_SDC_TABLE_VERSION", s."_SDC_SEQUENCE")]

Additionally on the second sync, where the table exists already with reserved words, it does a diff on the column names and incorrectly decides to create that column as if its missing. An error is thrown because it already exists. It looks like usually sqlalchemy returns all lowercase columns but in the reserved word case it comes back uppercase. In the DDL of the snowflake table its wrapped in double quotes so I think sqlalchemy is taking it literally while maybe lowercasing the rest of the columns, not sure.

snowflake.connector.errors.ProgrammingError: 001430 (42601): 01acd544-0603-1f7b-0051-5c8303595c36: SQL compilation error:
column 'SELECT' already exists

Fixed in MeltanoLabs/target-postgres#35

[pnadolny13]

The create table statement using sqlalchemy will succeed with reserved words, minus ansi reserved like "CURRET_DATE". The logic that needs to be fixed is where we build sql queries and dont let sqlalchemy auto quote reserved words. This happens in the merge statement and in the comparison logic to see if columns exist. Ideally we could pass a column name through sqlalchemy somehow to let it decide for us but if thats not possible we will need to keep a list of reserved words on the side and manually map them. In target-postgres Derek is taking the approach of using sqlalchemy as much as possible to avoid stuff like this caused by manually building queries, we can do that but it will be challenging with merge statements. Maybe its best to get the table metadata as the source for the columns to build the merge statement instead of the singer schema.