Migrate queries
Learn how to migrate your Sigma queries from the version running on Presto v334 to the version running on Trino v414.
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![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
The AMERICA/NEW_
time zone is no longer valid in Trino v414.
-- FAILED: Presto error: NOT_SUPPORTED: Time zone not supported: AMERICA/NEW_YORK select date_format( c.created AT TIME ZONE 'AMERICA/NEW_YORK', '%Y-%m-%d' ) from charges c
Instead, use America/New_
.
-- VALID select date_format( c.created AT TIME ZONE 'America/New_York', '%Y-%m-%d' ) from charges c
Invalid column references![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
Trino v414 doesn’t allow referencing column names with their originating sub-query or conditional table expression (CTE) out of scope.
-- FAILED: Presto error: COLUMN_NOT_FOUND: Column 'c.created' cannot be resolved select c.created from (select created from charges c)
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.
-- VALID select created from (select created from charges c)
-- VALID select c.created from (select created from charges) c
Either reference the column without the sub-query or define the sub-query at the same level as its reference.
Scientific notation![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
Casting a double to a varchar in Trino v414 produces results in scientific notation instead of decimal notation as in Presto v334.
-- RESULT: 1.0E2 select cast(100.0 as varchar)
To maintain this decimal notation, cast the double as a decimal and then as a varchar.
-- RESULT: 100.0 select cast(cast(100.0 as decimal(18,1)) as varchar)
Timestamp functions![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
FROM_UNIXTIME![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
Trino v414 assumes that the result timestamp is in UTC and adds a trailing “UTC” when using from_unixtime.
-- Trino v414 RESULT: 1970-01-01 00:00:00.000 UTC -- Presto v334 RESULT: 1970-01-01 00:00:00 +0000 select from_unixtime(0)
To remove the trailing “UTC”, cast the result of from_
as timestamp
.
-- RESULT: 1970-01-01 00:00:00 +0000 select cast(from_unixtime(0) as timestamp)
TO_ISO8601![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
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.
-- Presto v334 RESULT: 2024-04-01T00:00:00.000Z -- Trino v414 RESULT: 2024-04-01T00:00:00 select to_iso8601(timestamp '2024-04-01')
To make sure the trailing Zulu time zone suffix is added, interpret the timestamp in UTC before calling to_
.
-- RESULT: 2024-04-01T00:00:00Z select to_iso8601(timestamp '2024-04-01' at time zone 'UTC')
Query non-determinism![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
If your 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![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
-- POTENTIALLY NON-DETERMINISTIC select * from charges order by created DESC limit 10
If the 10th and 11th latest created charges were created at the same time, there’s no guarantee which charge is returned. Make sure you also sort on a unique identifier for deterministic results.
-- DETERMINISTIC select * from charges order by created DESC, id limit 10
Window aggregation![](https://b.stripecdn.com/docs-statics-srv/assets/fcc3a1c24df6fcffface6110ca4963de.svg)
-- POTENTIALLY NON-DETERMINISTIC select * from ( select c.*, row_number() over ( partition by c.customer_id order by c.amount DESC ) as row_rank from charges c ) where row_rank = 1
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.
-- DETERMINISTIC select * from ( select c.*, row_number() over ( partition by c.customer_id order by c.amount DESC, c.created DESC, c.id ) as row_rank from charges c ) where row_rank = 1