I spelled out the predicates below to clarify my thinking. Please correct me if I am wrong or I am missing something.

• ([NULL] IN [NULL]) -- is TRUE but should be FALSE <-- WHY?
  • The predicate asks: "Is in the right array an array with one element being a null?" No, as the right array holds only a null. In comparison:

  ([NULL] IN [[NULL]]) -- should be TRUE
  

• ([NULL] CONTAINS [NULL]) -- is TRUE but should be FALSE <-- WHY?
  • This is the same in reverse: "Does the left array contain an array with one element being null?" No, as the left array holds only a null.
• [NULL] CONTAINSANY NULL) -- is FALSE is fine if the right-hand-side has to be a collection, BUT <-- IT SHOULS RETURN TRUE
  • I agree by the same logic of above: "Does the left array contain at least one element being a null?" Yes it does.
• ([NULL] CONTAINSALL NULL) -- is TRUE which is inconsistent with CONTAINSANY<-- OK? SEE ABOVE
  • This is fine.

I see it.

In our code, I guess inherited from OrientDB, when both left and right are arrays (collections), it does this:

    if (right instanceof Collection) {
      if (left instanceof Collection<?>)
        return ((Collection<?>) right).containsAll((Collection<?>) left);

I don't know how many use cases are useful when you check if an array is in an array of arrays. Also in classic SQL this seems to not be supported.

We could just document this special behavior: if a collection of elements is tested against another collections of elements IN , then the single values of coll1are checked if contained incoll2`. @gramian WDYT?

@lvca I am fine with that (and can also do that in the docs), but wouldn't that make CONTAINSANY unnecessary?

Since you can have a sub query with the IN, I don't see any use case for the containsany...

Besides being more semantic me neither.

Fixed the basic cases with null values.