Repository navigation
Commit a16d10d
authored
fix: error instead of wrong rows for a LATERAL with a HAVING over an ungrouped aggregate (apache#26874)
## Which issue does this PR close?
- Part of apache#21198. It does not add `HAVING` support. Following
apache#5492 (comment),
it makes the shapes that return wrong rows error instead.
## Rationale for this change
- A `LATERAL` subquery over an aggregate without `GROUP BY`, with a
`HAVING`, returns wrong rows today instead of an error:
```sql
CREATE TABLE a(k INT) AS VALUES (1), (2), (3);
CREATE TABLE b(k INT, v INT) AS VALUES (1, 10), (1, 20), (2, 30);
SELECT a.k, s.c FROM a, LATERAL (SELECT count(*) AS c FROM b WHERE b.k = a.k HAVING count(*) > 1) s;
-- main: (1, 2), (2, NULL), (3, NULL)
-- DuckDB: (1, 2)
```
- The existing check only refuses a `HAVING` that `PullUpCorrelatedExpr`
pulls up, which happens when it is true on an empty group. Two cases get
past it:
- a `HAVING` that is false on an empty group stays above the aggregate.
The rewrite turns the inner join into an outer join, so an outer row
whose row the `HAVING` removes comes back with NULLs
- a `HAVING` that reads an outer column is moved into the join
condition, and the count bug handling turns its unmatched rows into a
count of 0. This is wrong for a left lateral join too (`(2, 0)` where
the answer is `(2, NULL)`)
## What changes are included in this PR?
- `DecorrelateLateralJoin` looks for a `HAVING` over an ungrouped
aggregate in the original subquery, and leaves the join correlated (so
it errors) when the join is inner, or when the `HAVING` reads an outer
column.
- A filter below the aggregate is not a `HAVING` and is not affected,
and neither is a left lateral join with a `HAVING` that reads only the
subquery.
## What is the testing strategy for this PR?
- New `lateral_join.slt` cases:
- inner join with `HAVING count(*) > 1` and with `HAVING max(t2.id) >
15`: now an error
- inner and left join with `HAVING count(*) > t1.id`: now an error
- left join with `HAVING count(*) > 1`, and an inner join with `GROUP BY
... HAVING`: still decorrelated, with the same rows as before
- Without the change, the four error cases return wrong rows instead of
failing.
- I compared 20 lateral aggregate shapes against DuckDB on main and on
this branch. The 8 that return wrong rows on main now error, the 8 that
return correct rows still return the same rows, and the 4 that already
errored still do.
- Optimizer unit tests and the full sqllogictest suite pass.
## Are there any user-facing changes?
- Queries that returned wrong rows now fail with `OuterReferenceColumn`.
Every query that returned correct rows still does.
🤖 Generated with [Claude Code](https://claude.com/claude-code)1 parent 2310b39 commit a16d10d
2 files changed
Lines changed: 136 additions & 1 deletion
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
19 | 19 | | |
20 | 20 | | |
21 | 21 | | |
22 | | - | |
| 22 | + | |
23 | 23 | | |
24 | 24 | | |
25 | 25 | | |
| |||
97 | 97 | | |
98 | 98 | | |
99 | 99 | | |
| 100 | + | |
| 101 | + | |
| 102 | + | |
| 103 | + | |
| 104 | + | |
| 105 | + | |
| 106 | + | |
| 107 | + | |
| 108 | + | |
| 109 | + | |
| 110 | + | |
| 111 | + | |
| 112 | + | |
| 113 | + | |
| 114 | + | |
| 115 | + | |
100 | 116 | | |
101 | 117 | | |
102 | 118 | | |
| |||
315 | 331 | | |
316 | 332 | | |
317 | 333 | | |
| 334 | + | |
| 335 | + | |
| 336 | + | |
| 337 | + | |
| 338 | + | |
| 339 | + | |
| 340 | + | |
| 341 | + | |
| 342 | + | |
| 343 | + | |
| 344 | + | |
| 345 | + | |
| 346 | + | |
| 347 | + | |
| 348 | + | |
| 349 | + | |
| 350 | + | |
| 351 | + | |
| 352 | + | |
| 353 | + | |
| 354 | + | |
| 355 | + | |
| 356 | + | |
| 357 | + | |
| 358 | + | |
| 359 | + | |
| 360 | + | |
| 361 | + | |
| 362 | + | |
| 363 | + | |
| 364 | + | |
| 365 | + | |
| 366 | + | |
318 | 367 | | |
319 | 368 | | |
320 | 369 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
726 | 726 | | |
727 | 727 | | |
728 | 728 | | |
| 729 | + | |
| 730 | + | |
| 731 | + | |
| 732 | + | |
| 733 | + | |
| 734 | + | |
| 735 | + | |
| 736 | + | |
| 737 | + | |
| 738 | + | |
| 739 | + | |
| 740 | + | |
| 741 | + | |
| 742 | + | |
| 743 | + | |
| 744 | + | |
| 745 | + | |
| 746 | + | |
| 747 | + | |
| 748 | + | |
| 749 | + | |
| 750 | + | |
| 751 | + | |
| 752 | + | |
| 753 | + | |
| 754 | + | |
| 755 | + | |
| 756 | + | |
| 757 | + | |
| 758 | + | |
| 759 | + | |
| 760 | + | |
| 761 | + | |
| 762 | + | |
| 763 | + | |
| 764 | + | |
| 765 | + | |
| 766 | + | |
| 767 | + | |
| 768 | + | |
| 769 | + | |
| 770 | + | |
| 771 | + | |
| 772 | + | |
| 773 | + | |
| 774 | + | |
| 775 | + | |
| 776 | + | |
| 777 | + | |
| 778 | + | |
| 779 | + | |
| 780 | + | |
| 781 | + | |
| 782 | + | |
| 783 | + | |
| 784 | + | |
| 785 | + | |
| 786 | + | |
| 787 | + | |
| 788 | + | |
| 789 | + | |
| 790 | + | |
| 791 | + | |
| 792 | + | |
| 793 | + | |
| 794 | + | |
| 795 | + | |
| 796 | + | |
| 797 | + | |
| 798 | + | |
| 799 | + | |
| 800 | + | |
| 801 | + | |
| 802 | + | |
| 803 | + | |
| 804 | + | |
| 805 | + | |
| 806 | + | |
| 807 | + | |
| 808 | + | |
| 809 | + | |
| 810 | + | |
| 811 | + | |
| 812 | + | |
| 813 | + | |
| 814 | + | |
729 | 815 | | |
730 | 816 | | |
731 | 817 | | |
| |||
0 commit comments