Migrate queries

We upgraded Sigmas 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_YORK 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_York.

-- VALID
select
    date_format(
        c.created AT TIME ZONE 'America/New_York',
        '%Y-%m-%d'
    )
from
    charges c

Invalid column references

Trino v414 doesnt 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' can't be resolved
select c.created from (select created from charges c)

The previous query is invalid because the sub-query c isnt 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

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

FROM_UNIXTIME

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_unixtime as timestamp.

-- RESULT: 1970-01-01 00:00:00 +0000
select cast(from_unixtime(0) 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 doesnt.

-- 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_iso8601.

-- RESULT: 2024-04-01T00:00:00Z
select to_iso8601(timestamp '2024-04-01' at time zone 'UTC')

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

-- 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, theres no guarantee which charge is returned. Make sure to also sort on a unique identifier for deterministic results.

-- DETERMINISTIC
select * from charges order by created DESC, id limit 10

Window aggregation

-- 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, theres 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