SSC-FDM-BQ0001

Accessing arrays produces NULL instead of an error for positive out of bounds indexes in Snowflake.

Description

When accessing an ARRAY object by index in Snowflake, specifying an index greater than the size of the array will result in a NULL value, this differs with the behavior of BigQuery, where accessing an ARRAY with an index that is out of bounds will produce an error, unless the functions SAFE_OFFSET or SAFE_ORDINAL are used.

This warning will be added to any ARRAY access that is not safe.

Code Example

Input Code:

BigQuery

IN -> BigQuery_01.sql

SELECT ([40, 12, 30])[8];

SELECT ([40, 12, 30])[SAFE_OFFSET(8)];

Output Code:

Snowflake

OUT -> BigQuery_01.sql

SELECT ([40, 12, 30])
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
[8];

SELECT PUBLIC.SAFE_OFFSET_UDF(([40, 12, 30]), 8);

Recommendations

• Analyze the uses of array access in the code, if there was never the risk of getting an out of bounds error in the original code then no difference will be observed and this warning can be safely ignored.

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