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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
