MSCINF0005

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

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

Severity

None

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:

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

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

Output Code:

 --** MSC-INFORMATION - MSCINF0005 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDIXES IN SNOWFLAKE **
SELECT ([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 [e-mail]