v.21.4Improvements

Handling NULL Tuples in SQL IN Operator

Official CH changelog text
If tuple of NULLs, e.g. (NULL, NULL) is on the left hand side of IN operator with tuples of non-NULLs on the right hand side, e.g. SELECT (NULL, NULL) IN ((0, 0), (3, 1)) return 0 instead of throwing an exception about incompatible types. The expression may also appear due to optimization of something like SELECT (NULL, NULL) = (8, 0) OR (NULL, NULL) = (3, 2) OR (NULL, NULL) = (0, 0) OR (NULL, NULL) = (3, 1). This closes #22017. #22063 (alexey-milovidov).
Handle tuple of NULLs on the left side of IN operator to return 0 instead of throwing an exception.

Why it matters

Previously, using a tuple of NULLs on the left side of the IN operator against tuples of non-NULL values would cause an incompatible types exception. This feature fixes that to return 0 gracefully, improving stability and correctness when such expressions arise, for example, through query optimizations.

How to use it

No special configuration is needed. Queries like SELECT (NULL, NULL) IN ((0, 0), (3, 1)) will now return 0 without error automatically after upgrading to the fixed version.

Related resources

PreviousNext