BUG [TRINO]: @generate_surrogate_key macro does not work with SHA256 on trino

[krutoileshii]

Summary

The built-in @generate_surrogate_key macro only produces valid output for MD5. For
hash_function := 'SHA256' (and 'SHA512') it emits a bare SHA256(CONCAT(...)), which
is invalid on Trino/Presto: sha256 accepts only varbinary (not varchar), and there is
no TO_HEX, so the result would be raw bytes rather than the documented string key.

Versions

• sqlmesh 0.235.4
• sqlglot 30.8.0
• engine: Trino (also affects Presto)

Reproduction

from sqlglot import parse_one
from sqlmesh.core.macros import MacroEvaluator

ev = MacroEvaluator(dialect="trino")
sql = "SELECT @GENERATE_SURROGATE_KEY(a, b, hash_function := 'SHA256') AS k"
print(ev.transform(parse_one(sql, dialect="trino")).sql("trino"))

Actual output

SELECT SHA256(CONCAT(
  CAST(COALESCE(CAST(a AS VARCHAR), '_sqlmesh_surrogate_key_null_') AS VARCHAR),
  CAST('|' AS VARCHAR),
  CAST(COALESCE(CAST(b AS VARCHAR), '_sqlmesh_surrogate_key_null_') AS VARCHAR)
)) AS k

Running this on Trino fails at execution time:
TrinoUserError(type=USER_ERROR, name=FUNCTION_NOT_FOUND,
message="Unexpected parameters (varchar) for function sha256. Expected: sha256(varbinary)")

Expected

A surrogate key that is valid (and returns a string) on the target engine, e.g. on Trino:

SELECT LOWER(TO_HEX(SHA256(TO_UTF8(CONCAT(...))))) AS k

Root cause

In sqlmesh/core/macros.py, generate_surrogate_key builds a generic hash node and then
only fixes up MD5:
func = exp.func(hash_function.name, exp.func("CONCAT", *string_fields), dialect=evaluator.dialect)
if isinstance(func, exp.MD5Digest):
func = exp.MD5(this=func.this) # gives Trino LOWER(TO_HEX(MD5(TO_UTF8(...))))
return func
exp.MD5 is a semantic node that the Trino/Presto generator wraps in TO_UTF8/TO_HEX.
No analogous handling exists for SHA-256/SHA-512, so those hashes fall through as a literal
SHA256() and break on engines where the digest function requires varbinary
and/or returns binary.

Suggested direction

Have the macro construct an engine-valid string digest for non-MD5 hashes too (i.e. apply
the same TO_UTF8 input / TO_HEX output treatment for engines that need it), rather than
relying on sqlglot to opinionatedly rewrite a generic SHA256(...) call. At minimum,
SHA256/SHA512 should be documented + tested per supported engine, since hash_function
is an advertised option.

For now I have this custom macro to work around it:

import typing as t

from sqlmesh import macro
from sqlglot import exp


@macro()
def surrogate_key(
    evaluator,
    *fields: exp.Expr,
    hash_function: exp.Literal = exp.Literal.string("SHA256"),
) -> exp.Expr:
    """Generates a hashed surrogate key (lowercase hex string) for the given fields.

    Field handling matches the built-in @generate_surrogate_key (NULLs coalesced to
    '_sqlmesh_surrogate_key_null_', joined by '|'), but the hash step is correct for
    Trino's binary digest functions (MD5, SHA1, SHA256, SHA512): the concatenated input
    is wrapped in TO_UTF8 (they require varbinary) and the binary digest is converted
    back to a lowercase hex string via TO_HEX/LOWER.

    The digest is built as an Anonymous node so sqlglot emits the function name verbatim;
    exp.func("MD5", ...) would hit sqlglot's MD5 semantic node and double-wrap it.
    """
    string_fields: t.List[exp.Expr] = []
    for i, field in enumerate(fields):
        if i > 0:
            string_fields.append(exp.Literal.string("|"))
        string_fields.append(
            exp.func(
                "COALESCE",
                exp.cast(field, exp.DataType.build("text")),
                exp.Literal.string("_sqlmesh_surrogate_key_null_"),
            )
        )

    digest = exp.Anonymous(
        this=hash_function.name.lower(),
        expressions=[exp.func("TO_UTF8", exp.func("CONCAT", *string_fields))],
    )
    return exp.func("LOWER", exp.func("TO_HEX", digest))

[Pawansingh3889]

Reproduced on 0.235.4 with sqlglot 30.8.0. The control case explains it: with hash_function := 'MD5' the macro renders LOWER(TO_HEX(MD5(TO_UTF8(...)))) on Trino, because it builds sqlglot's MD5 expression and the Trino generator transpiles it. With 'SHA256' it emits a plain SHA256(...) function call, which no generator rewrites, so Trino gets varchar into sha256(varbinary) and no hex conversion. The fix I have in mind is to build the hash as sqlglot expressions (exp.SHA2 with length 256/512) so each dialect transpiles it natively, with tests covering md5, sha256 and sha512 on trino, presto and duckdb. Happy to open a PR for this in the next few days unless a maintainer is already on it.

[Pawansingh3889]

Dug into the sqlglot side and the root cause is one level deeper. Presto/Trino has a custom md5_sql generator that renders LOWER(TO_HEX(MD5(TO_UTF8(x)))) for exp.MD5, and its parser maps the native varbinary MD5 to exp.MD5Digest, keeping the two semantics separate: MD5 is string to hex string, MD5Digest is binary to binary. SHA has the same expression pair (SHA2 and SHA2Digest), but Presto/Trino wires exp.SHA2 to the generic sha256_sql, which renders the native varbinary function, and its parser maps native SHA256/SHA512 to exp.SHA2 rather than SHA2Digest. So the macro cannot express "sha256 of a string returning hex" portably even if it builds exp.SHA2.

Proposal, mirroring the MD5 precedent exactly: in sqlglot, give Presto/Trino a sha2_sql generator (LOWER(TO_HEX(SHA256(TO_UTF8(x)))), SHA512 alike) and remap the native parser entries to exp.SHA2Digest; in sqlmesh, have generate_surrogate_key build exp.SHA2 with length 256/512 instead of an anonymous function. Happy to open both PRs, sqlglot first, with tests across trino, presto, duckdb and bigquery. Flagging before I start in case you would rather keep the fix contained to sqlmesh.

[Pawansingh3889]

The sqlglot half of this merged today: tobymao/sqlglot#7824. Presto/Trino now maps native SHA256/512 to exp.SHA2Digest and renders exp.SHA2 with Presto's string-to-hex semantics (LOWER(TO_HEX(SHA256(...))), with TO_UTF8 applied when the argument is typed as text), mirroring the MD5/MD5Digest handling. It's on main, not yet in a release (latest is 30.12.0 from 26 June).

That leaves the macro side here: generate_surrogate_key builds exp.MD5 for the default but an anonymous SHA256 call for hash_function := 'SHA256'. Its own doctest shows the asymmetry: TO_HEX(MD5(...)) versus bare SHA256(...). The fix is to build exp.SHA2 with the right length so dialect-specific rendering applies, plus tests on trino/presto/duckdb and the doctest updates. Happy to open that PR once a sqlglot release carries the upstream fix, or sooner behind a version check if you'd prefer.