Skip to content

array_has has inconsistent null handling for scalars and arrays #13682

Closed

Description

@Kimahriman

Describe the bug

array_has handles nulls differently for scalars and arrays. Scalars return null if all the left hand side values are null, while arrays don't care what values in the left hand side are null. Additionally, other systems like Spark and Postgres have different null behavior, such that if at least one element of the left hand side is null, return null instead of false. I can't find any definitive SQL standard for this, but it might be good to behave consistent with those other systems.

To Reproduce

> create table arrays as values (make_array(1, 2, 3), 4), (make_array(1, 2, NULL), 4), (make_array(NULL, NULL, NULL), 4);
0 row(s) fetched. 
Elapsed 0.017 seconds.

> select array_has(column1, column2) from arrays;
+------------------------------------------+
| array_has(arrays.column1,arrays.column2) |
+------------------------------------------+
| false                                    |
| false                                    |
| false                                    |
+------------------------------------------+
3 row(s) fetched. 
Elapsed 0.007 seconds.

> select array_has([1, 2, 3], 4),
array_has([1, 2, NULL], 4),
array_has([NULL, NULL, NULL], 4);
+------------------------------------------------------------+--------------------------------------------------------+------------------------------------------------+
| array_has(make_array(Int64(1),Int64(2),Int64(3)),Int64(4)) | array_has(make_array(Int64(1),Int64(2),NULL),Int64(4)) | array_has(make_array(NULL,NULL,NULL),Int64(4)) |
+------------------------------------------------------------+--------------------------------------------------------+------------------------------------------------+
| false                                                      | false                                                  |                                                |
+------------------------------------------------------------+--------------------------------------------------------+------------------------------------------------+
1 row(s) fetched. 
Elapsed 0.007 seconds.

Expected behavior

These queries return the same results.

Additional context

No response

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions