All Implemented Interfaces:
NamedWriteable, Writeable, TranslationAware, TranslationAware.SingleValueTranslationAware, Resolvable, EvaluatorMapper

The IN operator.

This function has quite "unique" null handling rules around null and multivalued fields. The null rules are inspired by PostgreSQL, and, presumably, every other SQL implementation. The multivalue rules are pretty much an extension of the "multivalued fields are like null in scalars" rule. Here's some examples:

  • 'x' IN ('a', 'b', 'c') => @{code false}
  • 'x' IN ('a', 'x', 'c') => @{code true}
  • null IN ('a', 'b', 'c') => @{code null}
  • ['x', 'y'] IN ('a', 'b', 'c') => @{code null} and a warning
  • 'x' IN ('a', null, 'c') => @{code null}
  • 'x' IN ('x', null, 'c') => @{code true}
  • 'x' IN ('x', ['a', 'b'], 'c') => @{code true} and a warning
  • 'x' IN ('a', ['a', 'b'], 'c') => @{code false} and a warning

And here's the decision tree for WHERE x IN (a, b, c):

  1. x IS NULL => return null
  2. MV_COUNT(x) > 1 => emit a warning and return null
  3. a IS NULL AND b IS NULL AND c IS NULL => return null
  4. MV_COUNT(a) > 1 OR MV_COUNT(b) > 1 OR MV_COUNT(c) > 1 => emit a warning and continue
  5. MV_COUNT(a) > 1 AND MV_COUNT(b) > 1 AND MV_COUNT(c) > 1 => return null
  6. x == a OR x == b OR x == c => return true
  7. a IS NULL OR b IS NULL OR c IS NULL => return null
  8. else => false

I believe the first five entries are *mostly* optimizations and making the Three-valued logic of SQL explicit and integrated with our multivalue field rules. And make all that work with the actual evaluator code. You could probably shorten this to the last three points, but lots of folks aren't familiar with SQL's three-valued logic anyway, so let's be explicit.

Because of this chain of logic we don't use the standard evaluator generators. They'd just require too many special cases and nothing else quite works like this. I mean, everything works just like this in that "three-valued logic" sort of way, but not in the "java code" sort of way. So! Instead of using the standard evaluator generators we use the String Template generators that we use for things like Block and Vector.