Wrong results: an aggregate over a primary key and another column is treated as unique on the key #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

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.