MSCEWI3086

Window frame output may not be equivalent

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is added when a ROWS window frame unit is found within the source code.

ROWS works by using physical row numbers for its computing, which it may differ once it is migrated to the target platform. Manually adding extra ORDER BY clauses can help mitigate or remove this issue.

Example Code

Input Code:

SELECT
SUM(C_BIRTH_DAY)
OVER (
    ORDER BY C_BIRTH_COUNTRY
    ROWS UNBOUNDED PRECEDING) AS MAX1
FROM WINDOW_TABLE;

Output Code:

SELECT
 SUM(C_BIRTH_DAY)
 OVER (
     ORDER BY C_BIRTH_COUNTRY ROWS /*** MSC-WARNING - MSCEWI3086 - WINDOW FRAME OUTPUT MAY NOT BE EQUIVALENT ***/ UNBOUNDED PRECEDING) AS MAX1
 FROM PUBLIC.WINDOW_TABLE;

Recommendations

• Ensure deterministic ordering for rows to ensure deterministic outputs when running in Snowflake.

• If you need more support, you can email us at [e-mail]