[I] Support multi-column (tuple) NOT IN subqueries with null-aware semantics [datafusion]

["viirya (via GitHub)" <gi...@apache.org>]

viirya opened a new issue, #25737:
URL: https://github.com/apache/datafusion/issues/25737

   ### Is your feature request related to a problem or challenge?
   
   DataFusion rejects multi-column (tuple) `IN` / `NOT IN` subqueries at planning time:
   
   ```sql
   SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT x, y FROM t2);
   -- Error: Too many columns! The subquery should only return one column
   
   SELECT * FROM t1 WHERE (c2, c3) NOT IN (
     SELECT c2, c3 FROM t2 WHERE t1.c1 = t2.c1
   );
   ```
   
   Other engines such as PostgreSQL and Spark SQL accept this form.
   
   Supporting it is not only a planning change. `NOT IN` needs SQL three-valued logic over the whole tuple. A NULL in one element does not make every comparison UNKNOWN the way a scalar NULL does:
   
   - `(NULL, 8) NOT IN ((1, 2))`: `(NULL, 8) = (1, 2)` is `UNKNOWN AND FALSE`, which is FALSE, so the row is kept.
   - `(NULL, 2) NOT IN ((1, 2))`: `(NULL, 2) = (1, 2)` is `UNKNOWN AND TRUE`, which is UNKNOWN, so the row is dropped.
   
   The null-aware hash join added for scalar `NOT IN` (#10583) handles a single value key. A correlated scalar `NOT IN` puts its value key in `on[0]` and its correlation scope keys in `on[1..]`. This layout cannot tell tuple elements apart from correlation keys.
   
   ### Describe the solution you'd like
   
   - Accept `(a, b, ...) [NOT] IN (SELECT x, y, ...)` when the tuple and subquery column counts match. Apply type coercion and placeholder inference element by element.
   - Decorrelate the tuple into one equality per element, followed by any correlation predicates.
   - Record the number of `NOT IN` value keys on null-aware joins, in the logical `Join`, in `HashJoinExec` and in protobuf. The key layout becomes `on[..V]` for the tuple elements and `on[V..]` for correlation keys.
   - In the null-aware hash join, treat a tuple comparison as UNKNOWN only when no element pair is a definite mismatch and at least one of them involves a NULL. This applies to both `LeftAnti` (filter context) and `LeftMark` (projection / `OR` context), with or without correlation.
   
   ### Describe alternatives you've considered
   
   - Rewrite the tuple as a nested-loop `NOT EXISTS (... WHERE (a = x AND b = y) IS NOT FALSE)`. This gives correct results but loses the hash join for the common case.
   - Support only uncorrelated multi-column `NOT IN`. This leaves correlated subqueries unsupported and still needs a way to tell tuple keys apart from correlation keys.
   
   ### Additional context
   
   A tuple compared with a single struct-typed column, `(a, b) IN (SELECT s FROM ...)`, is a scalar struct comparison and should keep its current behavior.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Support multi-column (tuple) NOT IN subqueries with null-aware semantics [datafusion]

["viirya (via GitHub)" <gi...@apache.org>]

viirya closed issue #25737: Support multi-column (tuple) NOT IN subqueries with null-aware semantics
URL: https://github.com/apache/datafusion/issues/25737


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org