SSC-FDM-OR0019

Window frame output may not be equivalent

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 may differ once it is migrated to the target platform. Manually adding extra ORDER BY clauses can help mitigate or remove this issue.

Note that as the Oracle documentation states: "The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering."

According to this is recommended to check if the function returned deterministic results beforehand to avoid any issues.

Example Code

Input Code:

IN -> Oracle_01.sql

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

Output Code:

OUT -> Oracle_01.sql

SELECT
SUM(C_BIRTH_DAY)
OVER (
    ORDER BY C_BIRTH_COUNTRY ROWS UNBOUNDED PRECEDING /*** SSC-FDM-OR0019 - WINDOW FRAME OUTPUT MAY NOT BE EQUIVALENT ***/) AS MAX1
FROM
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]