[ https://issues.apache.org/jira/browse/SPARK-41234?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17653778#comment-17653778 ]
Ruifeng Zheng commented on SPARK-41234: --------------------------------------- {code:sql} +--------------------------------------------+ | ARRAY_INSERT(ARRAY_CONSTRUCT(1,2,3), 0, 4) | |--------------------------------------------| | [ | | 4, | | 1, | | 2, | | 3 | | ] | +--------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.139s +--------------------------------------------+ | ARRAY_INSERT(ARRAY_CONSTRUCT(1,2,3), 2, 4) | |--------------------------------------------| | [ | | 1, | | 2, | | 4, | | 3 | | ] | +--------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.116s +-----------------------------------------------+ | ARRAY_INSERT(ARRAY_CONSTRUCT(1,2,3), 2, NULL) | |-----------------------------------------------| | [ | | 1, | | 2, | | undefined, | | 3 | | ] | +-----------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.130s +--------------------------+ | ARRAY_INSERT(NULL, 2, 1) | |--------------------------| | NULL | +--------------------------+ 1 Row(s) produced. Time Elapsed: 0.106s +-----------------------------+ | ARRAY_INSERT(NULL, 2, NULL) | |-----------------------------| | NULL | +-----------------------------+ 1 Row(s) produced. Time Elapsed: 0.113s +---------------------------------------------+ | ARRAY_INSERT(ARRAY_CONSTRUCT(1,2,3), 10, 1) | |---------------------------------------------| | [ | | 1, | | 2, | | 3, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | 1 | | ] | +---------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.116s +----------------------------------------------+ | ARRAY_INSERT(ARRAY_CONSTRUCT(1,2,3), -10, 1) | |----------------------------------------------| | [ | | 1, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | 1, | | 2, | | 3 | | ] | +----------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.111s +------------------------------------------------+ | ARRAY_INSERT(ARRAY_CONSTRUCT(1,2,3), 10, NULL) | |------------------------------------------------| | [ | | 1, | | 2, | | 3, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined | | ] | +------------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.109s +-------------------------------------------------+ | ARRAY_INSERT(ARRAY_CONSTRUCT(1,2,3), -10, NULL) | |-------------------------------------------------| | [ | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | undefined, | | 1, | | 2, | | 3 | | ] | +-------------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.210s {code} > High-order function: array_insert > --------------------------------- > > Key: SPARK-41234 > URL: https://issues.apache.org/jira/browse/SPARK-41234 > Project: Spark > Issue Type: Sub-task > Components: PySpark, SQL > Affects Versions: 3.4.0 > Reporter: Ruifeng Zheng > Priority: Major > > refer to > https://docs.snowflake.com/en/developer-guide/snowpark/reference/python/api/snowflake.snowpark.functions.array_insert.html > 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_insert 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 -- This message was sent by Atlassian Jira (v8.20.10#820010) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org