Description
1, about the data type validation:
In Snowflake’s array_append, array_prepend and array_insert functions, the element data type does not need to match the data type of the existing elements in the array.
While in Spark, we want to leverage the same data type validation as array_remove.
2, about the NULL handling
Currently, SparkSQL, SnowSQL and PostgreSQL deal with NULL values in different ways.
Existing functions array_contains, array_position and array_remove in SparkSQL handle NULL in this way, if the input array or/and element is NULL, returns NULL. However, this behavior should be broken.
We should implement the NULL handling in array_prepend in this way:
2.1, if the array is NULL, returns NULL;
2.2 if the array is not NULL, the element is NULL, append the NULL value into the array