Empty Array gets NULL

CrateDB
1

I have a couple of text[] Array Columns which may be empty in some circumstances, so the value should be [] (an empty list), but actually even if I explicitely set the Value =[] it gets NULL.

This leads to further issues afterwards as I use Array functions (mainly a UDF array_to_string(text[],text) but also onboard functions) which don’t accept NULL as a parameter, but an empty list to work correctly.

Is this an intended behaviour? Do I have to rewrite my UDFs to accept a NULL Array? Do all array functions work like this?

Version Info: Crate 4.5.3, 5 Nodes on Kubernetes (AKS)

2

Not quite sure if I can follow you :confused:

cr> CREATE TABLE array_text ( 
    arr ARRAY(TEXT) 
    );                                                                                             
CREATE OK, 1 row affected  (1.516 sec)

cr> INSERT INTO array_text (arr) VALUES ([]),(['test']),(null);                                    
INSERT OK, 3 rows affected  (0.113 sec)

cr> SELECT * FROM array_text;                                                                      
+----------+
| arr      |
+----------+
| []       |
| ["test"] |
| NULL     |
+----------+
SELECT 3 rows in set (0.522 sec)
3

BTW CrateDB 4.6.0 / 4.6.1 got released including array_to_string() :wink:

1 Like
4

mhm…ok, this works also on my environment, and also when I declare arr TEXT[]

Also UPDATE SET arr=[] where arr is NULL works, but what I didn’t know [] is the same as NULL (gets interpreted as the same).

Perhaps I just mixed up this behavior and some unwanted NULL values hiding in my Hundrets of Millions of rows.

Thanks!

1 Like