Migrate queries
Migrate your Sigma queries from Presto to Trino.
We upgraded Sigma’s query infrastructure from Presto v334 to Trino v414. Most queries run faster as a result of the upgrade, but a few queries might error unexpectedly or produce results in different formats.
Use the following suggestions to make your Sigma queries compatible with Trino v414.
Invalid time zones
The AMERICA/NEW_ time zone is no longer valid in Trino v414.
Instead, use America/New_.
Invalid column references
Trino v414 doesn’t allow referencing column names with their originating sub-query or conditional table expression (CTE) out of scope.
The previous query is invalid because the sub-query c isn’t defined at the top level but is referenced at the top level, out of its defined scope.
Either reference the column without the sub-query or define the sub-query at the same level as its reference.
Scientific notation
Casting a double to a varchar in Trino v414 produces results in scientific notation instead of decimal notation as in Presto v334.
To maintain this decimal notation, cast the double as a decimal and then as a varchar.
Timestamp functions
FROM_UNIXTIME
Trino v414 assumes that the result timestamp is in UTC and adds a trailing “UTC” when using from_unixtime.
To remove the trailing “UTC”, cast the result of from_ as timestamp.
TO_ISO8601
In Presto v334, to_iso8601 adds a trailing Zulu time zone suffix (“Z”) to a timestamp without a time zone while Trino v414 doesn’t.
To make sure the trailing Zulu time zone suffix is added, interpret the timestamp in UTC before calling to_.
Query non-determinism
If you query is non-deterministic, regardless of the Sigma version, different executions can yield different results. Here are common query patterns that can lead to non-deterministic results.
Top K queries
If the 10th and 11th latest created charges were created at the same time, there’s no guarantee which charge is returned. Make sure to also sort on a unique identifier for deterministic results.
Window aggregation
The above query returns the biggest charge for each customer using row_number. See window functions. If a customer has multiple charges with the same maximum amount, there’s no guarantee which charge is returned.
Sort on a unique identifier in the window ordering for deterministic results.