Unable to parse escaped tables

[michael-s-molina]

Bug description

The following query throws This database does not allow for DDL/DML, and the query could not be parsed to confirm it is a read-only query. Please contact your administrator for more assistance.

select *
from `birth_names`

The ability to escape table names is essential given that we might have names that conflict with reserved keywords.

This bug was introduced in #30417.

Screenshots/recordings

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[betodealmeida]

@michael-s-molina backticks are not valid Postgres quoting identifiers, you should use double quotes instead: https://www.postgresql.org/docs/17/sql-syntax-lexical.html

superset=# SELECT * FROM `birth_names`;
ERROR:  syntax error at or near "`"
LINE 1: SELECT * FROM `birth_names`;

[michael-s-molina]

@betodealmeida backticks are valid quoting identifiers for other databases such as Trino and MySQL. I just used the Postgres database as an example. We have many queries that use them.

[betodealmeida]

@michael-s-molina the parsing is now engine dependent, so if the database uses backticks for quoting then it should work. For MySQL, eg:

[betodealmeida]

If you have a database where this fails but the SQL is actually valid let me know.

[sadpandajoe]

It could also be using a default dialect if there isn't one in sqlglot, right @betodealmeida? If that's the case we'll probably need to just add a dialect file with sqlglot?

[betodealmeida]

@sadpandajoe right, if this is happening on a database that doesn't have a sqlglot dialect it would fallback to the generic dialect, which probably uses double quotes since that's the ANSI SQL standard.

[michael-s-molina]

@betodealmeida @sadpandajoe I was able to confirm that the root cause for the error is because we have custom database engine spec which does not map to any SQLGlot dialect in SQLGLOT_DIALECTS and end up using the default dialect which does not support backticks. I think the dialect should come from the database engine spec instead of a static map. I'll work on a fix for this.