adriangb commented on issue #25347:
URL: https://github.com/apache/datafusion/issues/25347#issuecomment-5689471249
Most of this is already covered by the two open PRs. I measured the 18-shape
matrix at each stage; what is left is one small gap.
## Where the matrix stands
#25339 fixes **every column-value shape**, A2/A4/A6 and their B and C
counterparts included. With #25348 on top of it, the shapes still wrong are
exactly the six with a **constant value and a correlated subquery**:
| # | Query | Correct | With #25339 + #25348 |
|---|---|---|---|
| A3 | `WHERE 3 NOT IN (... WHERE t_in.g = t_out.g)` | `1` | `1, 2` ❌ |
| A5 | `WHERE 3 NOT IN (... WHERE t_in.g > t_out.g)` | `2` | **error** ❌ |
| B3 | `... OR g = 99`, equality correlation | `1` | `1, 2` ❌ |
| B5 | `... OR g = 99`, non-equality correlation | `2` | **error** ❌ |
| C3 | `(...) IS NULL`, equality correlation | `2` | *(none)* ❌ |
| C5 | `(...) IS NULL`, non-equality correlation | `1` | **error** ❌ |
The errors are:
```
Error during planning: null_aware LeftAnti join requires equi-join keys, but
the join has none
Error during planning: null_aware LeftMark join requires equi-join keys, but
the join has none
```
The other twelve shapes are correct at that point. So this issue reduces to
part 2 of the plan above — the constant value — and parts 1 and 3 are handled
by #25339.
## Root cause of the remaining six
A constant value expression holds no column reference, so `3 = sq.id` is not
a valid equi-join key and cannot serve as the null-aware join's value
comparison.
For an **uncorrelated** subquery that left the join with no key at all,
which is what #25348 fixes by projecting the constant as a column of the outer
side. For a **correlated** subquery it is worse: the correlation supplies the
only equi-join key, so the join compares `grp` and never compares the value at
all, while still reporting itself null-aware.
## The fix
Extend the projection #25348 already performs to the correlated case: keep
the correlation predicate aside, rebuild the `IN` equality against the
projected column, then restore the correlation as a separate conjunct. The join
then has the value comparison as one key and the correlation as the other:
```
LeftAnti Join: __correlated_sq_1_value = __correlated_sq_1.id
AND outer_t.grp = __correlated_sq_1.grp null_aware
Projection: outer_t.id, outer_t.grp, Int32(3) AS __correlated_sq_1_value
TableScan: outer_t
SubqueryAlias: __correlated_sq_1
...
```
Multiple keys and a residual filter are both carried by the null-aware join
itself after #25339, so no shape needs a fallback and the plan stays a **single
join** — no three-valued decomposition, no extra scans of the subquery.
With this applied, all **18 of 18** shapes match DuckDB 1.5.2 and PostgreSQL
17.11.
## Status
The change is 44 lines in `decorrelate_predicate_subquery.rs` plus tests,
and a new `correlated_not_in.slt` covering the full matrix from this issue.
Verified on `#25339 + #25348 + this commit`:
| check | result |
|---|---|
| matrix | 18/18 correct |
| full `sqllogictest` suite | pass, 521 files |
| `datafusion-optimizer` unit tests | 853 pass |
| `cargo fmt --all` / `cargo clippy` | clean |
It depends on both #25339 and #25348 being in history, so rather than post a
three-PR stack I will open the PR once those two merge. The patch is attached
here in the meantime.
---
_Generated by [Claude Code](https://claude.ai/code)_
[25347-constant-correlated-not-in.patch](https://github.com/user-attachments/files/32264856/25347-constant-correlated-not-in.patch)
--
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]
