[SPARK-49520] ArrayRemove() Function Need Remove NULL Value

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.2.1
• Component/s: SQL
• Labels: Function SQL

Environment

Spark Version: 3.2.1

• Flags: Patch
• Assignee: Unassigned
• Reporter: Feng Jie
• Created: 05/Sep/24 03:33
• Updated: 21/Oct/24 06:53

Description

I want to calculate the intersection of two arrays like this: 

select   
    case when intersect_size > 0 then 1 else 0 end as is_include
from ( 
    select 
        size(array_intersect(array_a, array_b)) as intersect_size 
from table_a
)

 
But, the NULL will affect the output:

SELECT size(array_intersect(array(1, 2, 3, null), array(null)))

Output: 1 

So I want remove the NULL in first array by using array_remove：

SELECT array_remove(array(1, 2, 3, null, 3), null) 

Output: null

I want to add extra logic for function array_remove to remove NULL. Shall I overwrite the function (May be named: array_remove(array_a, array_b, isIgnoreNull)) or just fix the original function?