[jira] [Commented] (FLINK-32530) array_position semantic should align with array_contains instead of spark

["xuyang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-32530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17805570#comment-17805570 ] 

xuyang commented on FLINK-32530:
--------------------------------

Hi, [~jackylau] . Thanks for pointing it!

Have you looked at the behavior of other DBMS or big data frameworks? This change may cause a little break for users, so we'd better confirm what the correct behavior is.

BTW, does map has the same behavior while using function `MAP[KEY]` like `map_data[cast(null as int)]`?

> array_position semantic should align with array_contains instead of spark
> -------------------------------------------------------------------------
>
>                 Key: FLINK-32530
>                 URL: https://issues.apache.org/jira/browse/FLINK-32530
>             Project: Flink
>          Issue Type: Improvement
>          Components: Table SQL / Planner
>    Affects Versions: 1.18.0
>            Reporter: Jacky Lau
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.19.0
>
>
> when i supports array_contains to calcite https://issues.apache.org/jira/browse/CALCITE-5707 i found the spark and flink's behavior is different.
> {code:java}
> spark: array_contains(array[1, null], null) -> null
> flink: array_contains(array[1, null], null) -> true  {code}
> so array_remove is also different(the array_remove is  supported by me, which aligns with flink).
> {code:java}
> spark: array_remove(array[1, null], null) -> null 
> flink: array_remove(array[1, null], null) -> 1  {code}
> while array_position is align with spark, i think it is not correct.
> {code:java}
> spark: array_position(array[1, null], null) -> 2 
> flink: array_position(array[1, null], null) -> 2   {code}
> and i test on postgre which is also 2
> {code:java}
> postgre:
> select array_position(ARRAY[1, null], null); {code}
> so the semantic should only follow one way to handle null element. so i think it should be changed



--
This message was sent by Atlassian Jira
(v8.20.10#820010)