Support for queries containing ODBC escape sequences

[prabhur]

While reviewing some queries that failed to parse from Snowflake QUERY_HISTORY view, observed some queries possibly generated by a tool / framework.

They are using ODBC escape sequences, https://community.snowflake.com/s/article/SQL-Escape-Sequences.

Detailed documentation about ODBC escape sequences https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/escape-sequences-in-odbc?view=sql-server-ver16

Possible to add support for these?

There are some functions, like, for example, CONVERT that seems to be a standard function in some dialects. However, CONVERT is also a function that can be used in an escape sequence like below.

select
    { fn CONVERT("number_type_column", SQL_DOUBLE) } * CAST(-1 as DOUBLE) as "NUMBER_COLUMN_ALIAS"
from
    db.schema.table
;

So, there is a possibility of conflicting signatures.

There is another nuance to this. From the Microsoft documentation above, here is a statement.

The escape sequence is recognized and parsed by drivers, which replace the escape sequences with DBMS-specific grammar.

This creates a dilemma about supporting these type of queries. It feels like the queries will be translated to a dialect/db specific grammar before execution. I assume Snowflake QUERY_HISTORY has the query before it hit the ODBC driver conversion routine.

Interested in what you think. Thanks!

[tobymao]

i don’t really see a reason to support these.