SSC-FDM-BQ0001

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
([40, 12, 30])[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 [email protected]