asolimando opened a new issue, #27112:
URL: https://github.com/apache/datafusion/issues/27112
### Describe the bug
With a primary key declared, a join against an aggregate that groups by the
key plus another column returns wrong results.
`aggregate_functional_dependencies` marks the key as `Dependency::Single` on
the aggregate output, so the output looks unique on the key alone, although it
is unique only on the key and the other column. `EliminateJoin` trusts this and
converts the inner join to a semi join, which keeps each row of the other side
once instead of once per matching group.
### To Reproduce
```sql
CREATE TABLE item (i_item_sk INT, i_desc VARCHAR, PRIMARY KEY (i_item_sk))
AS VALUES (1, 'a'), (2, 'b');
CREATE TABLE sales (s_item_sk INT, s_date INT) AS VALUES (1, 100), (1, 101),
(2, 100);
CREATE TABLE orders (o_item_sk INT, o_amount INT) AS VALUES (1, 10), (2, 20);
SELECT sum(o_amount) FROM orders
JOIN (SELECT i_item_sk, s_date FROM item JOIN sales ON i_item_sk = s_item_sk
GROUP BY i_item_sk, s_date) f
ON o_item_sk = f.i_item_sk;
```
Returns 30. Without the primary key it returns 40. `EXPLAIN VERBOSE` shows
the semi join first in `logical_plan after eliminate_join`.
### Expected behavior
Returns 40: item 1 forms two groups, `(1, 100)` and `(1, 101)`, so its order
row joins twice.
### Additional context
`aggregate_functional_dependencies` promotes a re-expressed dependency to
`Single` when the targets reached from the whole GROUP BY list equal the
targets reached from its determinant. A GROUP BY column that no input
dependency targets (`s_date` here) is in neither set, so the sets compare
equal. The function also gives every re-expressed dependency all output columns
as `target_indices`, which claims `i_item_sk -> s_date`.
Other rules that read these dependencies return wrong results over the same
aggregate `f`, for example:
- `SELECT DISTINCT i_item_sk FROM f` returns 3 rows instead of 2 (the
DISTINCT is removed).
- `count(*) OVER (ORDER BY i_item_sk)` uses a ROWS frame instead of RANGE,
so the two rows of item 1 get 1 and 2 instead of 2 and 2.
- An outer `GROUP BY i_item_sk, s_date` drops `s_date` and returns 2 groups
instead of 3.
TPC-DS q23 (the variant that joins `frequent_ss_items`) with the TPC-DS
primary keys registered returns 3 wrong rows out of 100 at SF10 because of this.
I plan to open a PR with a fix.
--
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]
