Repository navigation
Commit 2bb383e
fix: keep a subquery correlated when an aggregate sits above a pulled up scalar aggregate (apache#26544)
## Which issue does this PR close?
- Part of apache#26508.
## Rationale for this change
An aggregate without `GROUP BY` returns one row even when its input is
empty. When such an aggregate is in a correlated subquery and another
aggregate sits above it, DataFusion returns wrong results for outer rows
that match no inner rows:
```sql
SELECT x.a,
(SELECT count(*) FROM (SELECT max(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a)) AS v1,
(SELECT max(n) FROM (SELECT count(*) AS n FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a)) AS v2
FROM (VALUES (1), (2)) AS x(a)
```
For `a = 2` this returns `v1 = 0, v2 = NULL` instead of `v1 = 1, v2 =
0`, which DuckDB, SQLite and PostgreSQL return. The same shape gives
wrong results under `IN` and `LEFT JOIN LATERAL` too.
When the correlated filter is pulled up, the inner scalar aggregate
becomes a grouped aggregate, which returns no row for the outer rows it
does not match. The aggregate above then runs over an empty input. The
count bug handling only fixes the aggregate the join reads, which here
is the outer one, so the inner row is never restored. The existing
`TODO` next to `pulled_up_scalar_agg` described this case.
## What changes are included in this PR?
`PullUpCorrelatedExpr` stops the pull up when it reaches an `Aggregate`
after a scalar aggregate has already been pulled up. The subquery stays
correlated, the same way a window over an aggregate with count bug
handling does today. The rewrite stops at that `Aggregate`, so the nodes
above it are not rebuilt over the pulled up plan. Before, the rebuild
could fail with a schema error. Such a query now fails with `Physical
plan does not support logical expression ScalarSubquery` instead of
returning wrong rows.
A projection over the scalar aggregate (`SELECT n + 1 FROM (SELECT
count(*) AS n ...)`) and an aggregate over a grouped aggregate are still
pulled up.
## What is the testing strategy for this PR?
New `sqllogictest` cases at the end of `subquery.slt` cover the two
queries from the issue, the `IN`, `LATERAL` and `EXISTS` forms, a
projection, a filter, a `GROUP BY`-only aggregate and a grouped
aggregate between the aggregates, a `max` over `max` query that is now
blocked, and the two shapes that must still be pulled up. All
`sqllogictest` files, the `datafusion-optimizer` unit tests and the
`core_integration` `sql::` tests (which include the TPC-DS planning
tests) pass locally.
## Are there any user-facing changes?
Queries with this shape now return a "not implemented" error instead of
wrong results. Some queries of this shape returned correct rows before
and now fail too, for example `max` over `max`. Telling them apart needs
the outer aggregate evaluated on the inner one's empty-input row. No API
changes.
---------
Co-authored-by: Jay Zhan <[email protected]>1 parent 53e1210 commit 2bb383e
2 files changed
Lines changed: 84 additions & 2 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
83 | 83 | | |
84 | 84 | | |
85 | 85 | | |
| 86 | + | |
| 87 | + | |
| 88 | + | |
86 | 89 | | |
87 | 90 | | |
88 | 91 | | |
| |||
556 | 559 | | |
557 | 560 | | |
558 | 561 | | |
| 562 | + | |
| 563 | + | |
| 564 | + | |
| 565 | + | |
| 566 | + | |
| 567 | + | |
| 568 | + | |
| 569 | + | |
559 | 570 | | |
560 | 571 | | |
561 | 572 | | |
| |||
647 | 658 | | |
648 | 659 | | |
649 | 660 | | |
650 | | - | |
651 | | - | |
652 | 661 | | |
653 | 662 | | |
654 | 663 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
4003 | 4003 | | |
4004 | 4004 | | |
4005 | 4005 | | |
| 4006 | + | |
| 4007 | + | |
| 4008 | + | |
| 4009 | + | |
| 4010 | + | |
| 4011 | + | |
| 4012 | + | |
| 4013 | + | |
| 4014 | + | |
| 4015 | + | |
| 4016 | + | |
| 4017 | + | |
| 4018 | + | |
| 4019 | + | |
| 4020 | + | |
| 4021 | + | |
| 4022 | + | |
| 4023 | + | |
| 4024 | + | |
| 4025 | + | |
| 4026 | + | |
| 4027 | + | |
| 4028 | + | |
| 4029 | + | |
| 4030 | + | |
| 4031 | + | |
| 4032 | + | |
| 4033 | + | |
| 4034 | + | |
| 4035 | + | |
| 4036 | + | |
| 4037 | + | |
| 4038 | + | |
| 4039 | + | |
| 4040 | + | |
| 4041 | + | |
| 4042 | + | |
| 4043 | + | |
| 4044 | + | |
| 4045 | + | |
| 4046 | + | |
| 4047 | + | |
| 4048 | + | |
| 4049 | + | |
| 4050 | + | |
| 4051 | + | |
| 4052 | + | |
| 4053 | + | |
| 4054 | + | |
| 4055 | + | |
| 4056 | + | |
| 4057 | + | |
| 4058 | + | |
| 4059 | + | |
| 4060 | + | |
| 4061 | + | |
| 4062 | + | |
| 4063 | + | |
| 4064 | + | |
| 4065 | + | |
| 4066 | + | |
| 4067 | + | |
| 4068 | + | |
| 4069 | + | |
| 4070 | + | |
| 4071 | + | |
| 4072 | + | |
| 4073 | + | |
| 4074 | + | |
| 4075 | + | |
| 4076 | + | |
| 4077 | + | |
| 4078 | + | |
0 commit comments