[SPARK-44840] array_insert() give wrong results for ngative index

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.4.0
• Fix Version/s: 3.4.2, 4.0.0, 3.5.1
• Component/s: Spark Core
• Assignee: Max Gekk
• Reporter: Serge Rielau
• Shepherd: Max Gekk
• Created: 17/Aug/23 00:14
• Updated: 24/Aug/23 16:57
• Resolved: 22/Aug/23 18:04

Description

Unlike in Snowflake we decided that array_inert() is 1 based.
This means 1 is the first element in an array and -1 is the last. 
This matches the behavior of functions such as substr() and element_at().

 

> SELECT array_insert(array('a', 'b', 'c'), 1, 'z');
["z","a","b","c"]
> SELECT array_insert(array('a', 'b', 'c'), 0, 'z');
Error
> SELECT array_insert(array('a', 'b', 'c'), -1, 'z');
["a","b","c","z"]
> SELECT array_insert(array('a', 'b', 'c'), 5, 'z');
["a","b","c",NULL,"z"]
> SELECT array_insert(array('a', 'b', 'c'), -5, 'z');
["z",NULL,"a","b","c"]
> SELECT array_insert(array('a', 'b', 'c'), 2, cast(NULL AS STRING));
["a",NULL,"b","c"]

 

Issue Links

• links to: [Github] Pull Request #42564 (MaxGekk)