You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2026/07/16 00:59:22 UTC

[I] SELECT DISTINCT returns duplicate rows when a unique key is downgraded by a join [datafusion]

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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] SELECT DISTINCT returns duplicate rows when a unique key is downgraded by a join [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #23626: SELECT DISTINCT returns duplicate rows when a unique key is downgraded by a join
URL: https://github.com/apache/datafusion/issues/23626


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org