SQL: Array comparisons with NULL #1785
Description
ArcadeDB Version:
ArcadeDB Server v24.10.1 (build ac46a0da6ebe4f5d337e50874f3d8a0d1bf092b1/1728751149872/main)
OS and JDK Version:
Running on Linux 6.2.0-26-generic - OpenJDK 64-Bit Server VM 17.0.12
It seems array comparisons with NULL can give unexpected results:
(NULL IN [NULL]) -- is FALSE but should be TRUE
([NULL] IN [NULL]) -- is TRUE but should be FALSE
([NULL] CONTAINS [NULL]) -- is TRUE but should be FALSE
([NULL] CONTAINSANY NULL) -- is FALSE is fine if the right-hand-side has to be a collection, BUT
([NULL] CONTAINSALL NULL) -- is TRUE which is inconsistent with `CONTAINSANY`
Steps to reproduce
SELECT (NULL IN [NULL])
SELECT ([NULL] IN [NULL])
SELECT ([NULL] CONTAINS [NULL])
SELECT ([NULL] CONTAINSANY NULL)
SELECT ([NULL] CONTAINSALL NULL)