Repository navigation
Commit 3af8737
fix: keep the column null test for a
A null-aware `LeftAnti` join does not apply the residual join filter when it
decides whether a NULL makes the result UNKNOWN
(apache#25336). It looks only at whether
the probe side holds any row. For a correlated `NOT IN` with a non-equality
correlation this is wrong in one direction: a build row whose key is NULL is
dropped although the correlated subquery result for that row is empty, and
`NULL NOT IN (<empty set>)` is TRUE.
The column test that this branch replaces never reached that state, because a
key expression such as `NULLIF(k, 1)` over `NOT NULL` columns holds no nullable
column. The expression test does reach it, so it is a new wrong result:
```sql
CREATE TABLE ra(k INT NOT NULL, z INT NOT NULL) AS VALUES (1, 10), (2, 20);
CREATE TABLE rb(k INT NOT NULL, z INT NOT NULL) AS VALUES (5, 50);
SELECT k FROM ra WHERE NULLIF(ra.k, 1) NOT IN (SELECT rb.k FROM rb WHERE rb.z < ra.z);
-- correct, and `main`: 1, 2. before this commit: 2
```
The expression test thus applies only when the split gives one key and no
residual. A residual now keeps the column test, as more than one key already
does. Every result of this branch is then the same as `main`'s or better.
Co-Authored-By: Claude Opus 5 <[email protected]>NOT IN anti join with a residual filter1 parent e501c78 commit 3af8737
2 files changed
Lines changed: 50 additions & 7 deletions
File tree
- datafusion
- optimizer/src
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
763 | 763 | | |
764 | 764 | | |
765 | 765 | | |
766 | | - | |
767 | | - | |
768 | | - | |
769 | | - | |
770 | | - | |
771 | | - | |
772 | | - | |
| 766 | + | |
| 767 | + | |
| 768 | + | |
| 769 | + | |
| 770 | + | |
| 771 | + | |
| 772 | + | |
| 773 | + | |
| 774 | + | |
| 775 | + | |
| 776 | + | |
| 777 | + | |
| 778 | + | |
| 779 | + | |
| 780 | + | |
| 781 | + | |
| 782 | + | |
773 | 783 | | |
774 | 784 | | |
775 | 785 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
399 | 399 | | |
400 | 400 | | |
401 | 401 | | |
| 402 | + | |
| 403 | + | |
| 404 | + | |
| 405 | + | |
| 406 | + | |
| 407 | + | |
| 408 | + | |
| 409 | + | |
| 410 | + | |
| 411 | + | |
| 412 | + | |
| 413 | + | |
| 414 | + | |
| 415 | + | |
| 416 | + | |
| 417 | + | |
| 418 | + | |
| 419 | + | |
| 420 | + | |
| 421 | + | |
| 422 | + | |
| 423 | + | |
| 424 | + | |
| 425 | + | |
| 426 | + | |
| 427 | + | |
| 428 | + | |
| 429 | + | |
| 430 | + | |
| 431 | + | |
| 432 | + | |
| 433 | + | |
| 434 | + | |
402 | 435 | | |
403 | 436 | | |
404 | 437 | | |
| |||
0 commit comments