make_array should support pure null array

[izveigor]

Describe the bug

DataFusion supports now heterogeneous arrays (i.e. containing NULL with another data type), but does not support pure null array.

To Reproduce

DataFusion:

❯ select make_array(NULL, NULL, NULL);
+----------------------------+
| make_array(NULL,NULL,NULL) |
+----------------------------+
| []                         |
+----------------------------+

❯ select make_array(NULL, 1, NULL, 2, NULL);
+----------------------------------------------+
| make_array(NULL,Int64(1),NULL,Int64(2),NULL) |
+----------------------------------------------+
| [, 1, , 2, ]                                 |
+----------------------------------------------+

Expected behavior

The output should be equal like in other databases (DuckDB, ClickHouse, PostgreSQL):

DuckDB:

D select list_value(NULL, NULL, NULL);
┌──────────────────────────────┐
│ list_value(NULL, NULL, NULL) │
│           int32[]            │
├──────────────────────────────┤
│ [NULL, NULL, NULL]           │
└──────────────────────────────┘

D select list_value(NULL, 1, NULL, 2, NULL);
┌────────────────────────────────────┐
│ list_value(NULL, 1, NULL, 2, NULL) │
│              int32[]               │
├────────────────────────────────────┤
│ [NULL, 1, NULL, 2, NULL]           │
└────────────────────────────────────┘

ClickHouse:

SELECT [NULL, NULL, NULL]

Query id: fe6008c8-7d4c-4c1e-96c4-852c900e5530

┌─[NULL, NULL, NULL]─┐
│ [NULL,NULL,NULL]   │
└────────────────────┘

1 row in set. Elapsed: 0.001 sec.

SELECT [NULL, 1, NULL, 2, NULL]

Query id: 24391d20-dc13-4624-9df1-befb01edab4a

┌─[NULL, 1, NULL, 2, NULL]─┐
│ [NULL,1,NULL,2,NULL]     │
└──────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

PostgreSQL:

postgres=# select array[NULL, NULL, NULL];
      array       
------------------
 {NULL,NULL,NULL}
(1 row)

postgres=# select array[NULL, 1, NULL, 2, NULL];
        array         
----------------------
 {NULL,1,NULL,2,NULL}
(1 row)

Additional context

No response

[alamb]

I can't think of any practical usecase for making making pure null arrays , though maybe I am missing something. Adding lots of code for a case no one is likely to actually use seems unecessary to me.

Thus, perhaps following DuckDB's behavior and picking some arbitrary type like int32[] is reasonable. Or maybe even generating a "not yet supported" error and waiting for someone to report on their actual usecase before we spend engineering time on sorting out how to make it behave 🤔

[izveigor]

@alamb In my opinion, we should not pay attention to pure null arrays now, so I agree with you that it is better to wait until another user raises this issue.

[jayzhan211]

How about differentiating empty and null? Is the priority of this high?

select make_array(NULL, 1, NULL, 2, NULL); Expected to get [null,1,null,2,null] instead of [,1,,2,]