adriangb opened a new issue, #25519:
URL: https://github.com/apache/datafusion/issues/25519
## Describe the bug
A correlated subquery whose filter sits below an aggregate with a grouping
set gives wrong results after decorrelation. `PullUpCorrelatedExpr` moves the
correlated filter above the aggregate and adds the correlated column to every
grouping set. `ROLLUP(i.k)` is `GROUPING SETS ((i.k), ())`; after the pull up
it is `GROUPING SETS ((i.k), (i.k, i.k))`. The empty grouping set is gone, and
with it the grand-total row that the correlated subquery gives for every outer
row, also when the filter matches nothing.
`EXISTS` and `IN` are both wrong. `EXISTS (SELECT 1 FROM i WHERE i.k = o.k
GROUP BY ROLLUP(i.k))` is `true` for every outer row, because the grand-total
row always exists. DataFusion gives `false` when no `i.k` matches.
There is no error and no warning. The aggregate has no aggregate
expressions, so `is_distinct = aggregate.aggr_expr.is_empty()` in
`decorrelate.rs` treats it as a `DISTINCT` and keeps `can_pull_up` set.
## To Reproduce
```sql
CREATE TABLE o(k INT) AS VALUES (1), (2), (NULL), (4), (5);
CREATE TABLE i(k INT) AS VALUES (1), (NULL), (5), (2);
SELECT o.k, EXISTS (SELECT 1 FROM i WHERE i.k = o.k GROUP BY ROLLUP(i.k)) AS
e FROM o ORDER BY o.k;
SELECT o.k, o.k IN (SELECT i.k FROM i WHERE i.k = o.k GROUP BY ROLLUP(i.k))
AS m FROM o ORDER BY o.k;
```
`datafusion-cli` on `main` at 64871d923c:
| `o.k` | `EXISTS`, DataFusion | `EXISTS`, correct | `IN`, DataFusion |
`IN`, correct |
| --- | --- | --- | --- | --- |
| 1 | true | true | true | true |
| 2 | true | true | true | true |
| 4 | false | true | false | NULL |
| 5 | true | true | true | true |
| NULL | false | true | false | NULL |
DuckDB 1.5.2 and PostgreSQL give the "correct" columns. For `o.k = 4` the
correlated subquery result is `{NULL}`: the grand-total row, with `i.k` rolled
up to NULL. So `EXISTS` is true and `4 IN {NULL}` is UNKNOWN.
The plan for the `EXISTS` query on `main`. The grouping set `()` has become
`(i.k, i.k)`:
```
Projection: o.k, __correlated_sq_1.mark AS e
LeftMark Join: o.k = __correlated_sq_1.k
TableScan: o projection=[k]
SubqueryAlias: __correlated_sq_1
Projection: i.k
Aggregate: groupBy=[[GROUPING SETS ((i.k), (i.k, i.k))]], aggr=[[]]
TableScan: i projection=[k]
```
## Expected behavior
The results in the "correct" columns above. If the pull up cannot keep the
per-row semantics of a grouping set, it should refuse to decorrelate the
subquery with the `unsupported()` helper that
https://github.com/apache/datafusion/pull/25284 adds (`can_pull_up = false`),
instead of giving a wrong result.
## Additional context
Same class, a correlated filter pulled above a node that changes the row set:
- https://github.com/apache/datafusion/issues/25507: the nullable side of an
outer join.
- https://github.com/apache/datafusion/issues/24960 and
https://github.com/apache/datafusion/pull/25391: a groupless aggregate (the
count bug).
- https://github.com/apache/datafusion/issues/25283 and
https://github.com/apache/datafusion/pull/25284: `OFFSET`.
- https://github.com/apache/datafusion/issues/25480: the correlation repeats
the `IN` predicate and is dropped.
https://github.com/apache/datafusion/pull/25338 had a guard that made only
the `IN` form of this query return NULL. It removes that guard again, so that
the rule does not carry a list of plan nodes that can put a NULL back into a
column, and this issue tracks the fix in the pull up instead.
--
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]
