bug(trino): SQL formatter rewrites Trino-native functions to invalid names (date_diff→datediff, to_hex→hex) via sqlglot dialect mismatch

[shimonp]

Bug description

When "Format SQL" is enabled in SQL Lab, Superset passes Trino queries through
sqlglot for pretty-printing but does not thread the Trino dialect through to
sqlglot's generator. This causes sqlglot to fall back to its generic SQL
generator, which renames several Trino-native functions to names that Trino
does not recognise:

date_diff → datediff (FUNCTION_NOT_FOUND)
to_hex → hex (FUNCTION_NOT_FOUND)
from_hex → unhex (FUNCTION_NOT_FOUND)
date_trunc → (argument order swapped in some paths)

The error from Trino is always the same pattern:
TrinoUserError(type=USER_ERROR, name=FUNCTION_NOT_FOUND,
message="line N:N: Function 'datediff' not registered")

sqlglot itself is NOT at fault. When the dialect is passed correctly, it
round-trips Trino functions perfectly:

sqlglot.transpile("SELECT date_diff('day', a, b)", read="trino", write="trino")
→ ["SELECT date_diff('day', a, b)"] ✅

sqlglot.transpile("SELECT date_diff('day', a, b)") # no dialect
→ ["SELECT DATEDIFF(b, a)"] ❌ ← what Superset produces

Root cause: Superset's SQL formatting path calls sqlglot without specifying
the write dialect for TrinoEngineSpec, so the generic generator is used.
The fix belongs in superset/db_engine_specs/presto.py (PrestoBaseEngineSpec)
or trino.py, adding explicit sqlglot TRANSFORMS overrides for the affected
exp.* AST nodes so they emit the correct Trino function names regardless of
how the generator is invoked.

Screenshots/recordings

1. Connect Superset 6.0.0 (or 6.1rc1) to a Trino database (any version)
2. Open SQL Lab, ensure "Format SQL" is ON
3. Run:
   SELECT date_diff('day', DATE '2023-01-01', current_date)
4. Observe error:
   TrinoUserError: Function 'datediff' not registered
5. Disable "Format SQL" — same query runs successfully
6. Same issue reproducible with: to_hex(), from_hex(), sha256(), md5()

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

Expected result:
Trino-native functions (date_diff, to_hex, from_hex, md5, sha256, date_add)
are passed through to Trino unchanged, regardless of whether "Format SQL"
is enabled.

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.

[dosubot]

Good find — and you're right that sqlglot itself handles Trino functions correctly when the dialect is specified. The root cause is actually in the frontend, not the engine specs or the backend formatting code.

The backend formatting path in superset/sql/parse.py does accept and correctly use the Trino dialect when provided — both for sqlglot.parse(dialect=...) and Dialect.generate(...). Trino is properly mapped to Dialects.TRINO in the SQLGLOT_DIALECTS dictionary [1].

The problem is that SQL Lab's frontend never passes the engine parameter when calling the format endpoint. In superset-frontend/src/SqlLab/actions/sqlLab.js, the formatQuery function sends only { sql } — there's even a TODO comment acknowledging this:

// TODO (betodealmeida): pass engine as a parameter for better formatting
body: JSON.stringify({ sql }),

Without the engine parameter, the backend has no dialect to pass to sqlglot, so sqlglot falls back to its generic SQL generator — which is exactly what produces datediff, hex, unhex, etc.

Interestingly, the ViewQuery component in the Explore view does pass the engine parameter correctly by reading it from dataset metadata.

So the fix would be to pass the database engine (e.g., "trino") from the SQL Lab editor to the /api/v1/sqllab/format_sql/ endpoint, similar to how ViewQuery.tsx already does it. The SQL Lab editor has access to the selected database — it just needs to include its backend in the format request payload.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}
   

[shimonp]

I am new to the bug fixing process in Superset. Am I expected to provide the fix you have described or the core team will review, implement the fix and merge into main branch on its own? It does not work in v6.0.0 or 6.1rc1 and I hope that 6.1 won't be released without implementing and testing this trivial fix. We use Trino engine extensively, so custom syntax of Trino sql dialect is important to us. Best regards.