[SPARK-45078] The ArrayInsert function should make explicit casting when element type not equals derived component type

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.4.1
• Fix Version/s: 3.4.2, 4.0.0, 3.5.1
• Component/s: SQL
• Labels: pull-request-available
• Assignee: Jia Fan
• Reporter: Ran Tao
• Created: 05/Sep/23 11:24
• Updated: 18/Sep/23 18:56
• Resolved: 17/Sep/23 08:17

Description

Generally speaking, array_insert has same insert semantic with  array_prepend/array_append. however, if we run sql use element cast like below, array_prepend/array_append can get right result. but array_insert failed.

spark-sql (default)> select array_prepend(array(1), cast(2 as tinyint));
[2,1]
Time taken: 0.123 seconds, Fetched 1 row(s) 

spark-sql (default)> select array_append(array(1), cast(2 as tinyint)); 
[1,2] 
Time taken: 0.206 seconds, Fetched 1 row(s)

spark-sql (default)> select array_insert(array(1), 2, cast(2 as tinyint));
[DATATYPE_MISMATCH.ARRAY_FUNCTION_DIFF_TYPES] Cannot resolve "array_insert(array(1), 2, CAST(2 AS TINYINT))" due to data type mismatch: Input to `array_insert` should have been "ARRAY" followed by a value with same element type, but it's ["ARRAY<INT>", "TINYINT"].; line 1 pos 7;
'Project [unresolvedalias(array_insert(array(1), 2, cast(2 as tinyint)), None)]
+- OneRowRelation 

The reported error is clear, however, we may should do explicit casting here. because multiset type such as array or map allow the operands of same type family  to coexist.

Issue Links

• links to: GitHub Pull Request #42951
• links to: GitHub Pull Request #42960