alamb opened a new issue, #23626:
URL: https://github.com/apache/datafusion/issues/23626
### Describe the bug
`SELECT DISTINCT` can return duplicate rows when its input has a functional
dependency whose determinant columns cover all output columns, but whose
uniqueness was lost earlier in the plan.
The `ReplaceDistinctWithAggregate` optimizer rule removes `Distinct::All`
whenever a functional dependence's `source_indices` cover all input fields,
without checking the dependency `mode`. After a join,
`FunctionalDependencies::join` downgrades dependencies to `Dependency::Multi`
because a formerly-unique key (e.g. a primary key) may now occur in many rows.
The rule ignores this downgrade and removes the `Distinct` anyway, so
duplicates are returned to the user.
### To Reproduce
Run the following in `datafusion-cli`:
```sql
CREATE TABLE users_with_pk (id INT, name VARCHAR, PRIMARY KEY (id)) AS VALUES
(1, 'alice'),
(2, 'bob');
CREATE TABLE user_orders (user_id INT, amount INT) AS VALUES
(1, 10),
(1, 20),
(2, 30);
SELECT DISTINCT u.id
FROM users_with_pk u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY u.id;
```
Actual output — `id = 1` is repeated once per matching order:
```
+----+
| id |
+----+
| 1 |
| 1 |
| 2 |
+----+
```
`EXPLAIN` shows the `DISTINCT` has been dropped entirely — there is no
`Aggregate` (or any other deduplication) in the plan:
```
logical_plan
01)Projection: u.id
02)--Left Join: u.id = o.user_id
03)----SubqueryAlias: u
04)------TableScan: users_with_pk projection=[id]
05)----SubqueryAlias: o
06)------TableScan: user_orders projection=[user_id]
```
### Expected behavior
The query should return each `u.id` once:
```
+----+
| id |
+----+
| 1 |
| 2 |
+----+
```
and the plan should retain an `Aggregate` (or equivalent deduplication) for
the `DISTINCT`.
### Additional context
The faulty check is in
`datafusion/optimizer/src/replace_distinct_aggregate.rs`
(`ReplaceDistinctWithAggregate::rewrite`): it only verifies that
`dep.source_indices` covers all fields, but a `Dependency::Multi` dependence
merely means equal determinant keys imply equal rows — the key may still occur
in multiple rows, so the input is not necessarily duplicate-free. The removal
is only sound for `Dependency::Single` dependencies.
Fixed by #23548.
--
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]
