adriangb opened a new issue, #25808:
URL: https://github.com/apache/datafusion/issues/25808
### Describe the bug
A `LATERAL` subquery with `SELECT DISTINCT` and a correlated filter that is
not an equality (for example `>`) returns duplicate rows. The correlated filter
is pulled above the `DISTINCT`, and the column that the filter reads is added
to the `DISTINCT` keys. Rows that differ only in that column are then not
merged.
### To Reproduce
```sql
CREATE TABLE po(k INT) AS VALUES (0), (5);
CREATE TABLE pb(x INT, y INT) AS VALUES (1, 1), (1, 2), (2, 3);
SELECT po.k, s.x
FROM po, LATERAL (SELECT DISTINCT pb.x FROM pb WHERE pb.y > po.k) AS s
ORDER BY po.k, s.x;
```
For `po.k = 0`, all three rows of `pb` match, and the distinct values of `x`
are `1` and `2`.
| DataFusion | DuckDB 1.5.2 | PostgreSQL 17.11 |
| --- | --- | --- |
| `(0, 1)`, **`(0, 1)`**, `(0, 2)` | `(0, 1)`, `(0, 2)` | `(0, 1)`, `(0, 2)`
|
The same query with `GROUP BY` instead of `DISTINCT` does not give wrong
results. DataFusion does not decorrelate it and fails with a not-implemented
error:
```sql
SELECT po.k, s.x, s.n
FROM po, LATERAL (SELECT pb.x, count(*) AS n FROM pb WHERE pb.y > po.k GROUP
BY pb.x) AS s;
-- This feature is not implemented: Physical plan does not support logical
expression OuterReferenceColumn(...)
```
With an equality filter (`pb.y = po.k`) the `DISTINCT` form gives the
correct result.
### Expected behavior
The results of DuckDB and PostgreSQL above: each distinct `x` one time for
each `po` row.
### Additional context
The plan shows the cause. `pb.y` is now a group-by key of the `DISTINCT`
aggregate, and `pb.y > po.k` is the join filter:
```
Projection: po.k, s.x
Inner Join: Filter: s.y > po.k
TableScan: po projection=[k]
SubqueryAlias: s
Aggregate: groupBy=[[pb.x, pb.y]], aggr=[[]]
TableScan: pb projection=[x, y]
```
In `PullUpCorrelatedExpr::f_up` in
`datafusion/optimizer/src/decorrelate.rs`, the `Aggregate` arm does the
`can_pull_over_aggregation` check only when the aggregate is not a `DISTINCT`.
That check allows only equality filters to move above an aggregate. For
`EXISTS` and `IN`, adding a column to the `DISTINCT` keys does not change the
result, because duplicate rows do not change whether a match exists. For
`LATERAL`, every row is output, so the duplicate rows are visible.
A fix can do the `can_pull_over_aggregation` check for a `DISTINCT`
aggregate as well, except for `EXISTS` and `IN` subqueries. The `GROUP BY` form
already gets this check.
Found on `main` at 6a792c6713.
This is the same class of bug as these: `PullUpCorrelatedExpr` pulls a
correlated filter through a plan node where that changes the result.
- https://github.com/apache/datafusion/issues/25507 (outer join)
- https://github.com/apache/datafusion/issues/25792 (window function)
- https://github.com/apache/datafusion/issues/25283 (`Limit` with an
`OFFSET`)
--
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]
