fix: wrong aggregate functional dependencies when a GROUP BY column is not determined by the key - #27113

Which issue does this PR close?

• Closes Wrong results: an aggregate over a primary key and another column is treated as unique on the key #27112.

Rationale for this change

The subquery below groups by i_item_sk, s_date. Item 1 has two dates, so it forms two groups:

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;
-- 30, while the correct result is 40 (see #27112 for the data)

aggregate_functional_dependencies marked i_item_sk as Dependency::Single on the output of f (a key: at most one row per value). That is wrong, because item 1 has two rows.

It did so because nothing determines s_date. The check compared what i_item_sk determines with what the whole GROUP BY list determines. s_date was in neither set, so the two sets were equal.

EliminateJoin trusted the wrong key and turned the join into a semi join (each orders row kept once instead of once per matching group). Item 1's order was counted once, hence 30.

The function also gave every dependency all output columns as target_indices. That claims i_item_sk determines s_date, which breaks other rules, for example an outer GROUP BY i_item_sk, s_date drops s_date.

What changes are included in this PR?

• A re-expressed dependency is now Single only if it is Single in the input or its determinant determines every GROUP BY expression. A deterministic computed GROUP BY expression is determined when every column it reads is, for example upper(i_desc) by i_item_sk. Volatile expressions, and expressions with subqueries or outer references, are never determined.
• Otherwise the dependency keeps its mode and targets only the GROUP BY expressions it determines, not the aggregate values.
• The dependency on the whole GROUP BY list is skipped only when a Single dependency replaces it, and its extraneous expressions are removed: columns that the other GROUP BY columns determine, and deterministic computed expressions whose fields the remaining key columns determine. The SQL planner adds determined columns to the GROUP BY, so without this the dependency was lost when a projection removed them, and selecting an aggregate value through such a projection failed to plan.
• aggregate_functional_dependencies keeps its signature and is deprecated. The new aggregate_functional_dependencies_with_computed_inputs takes the columns that each computed GROUP BY expression reads.

What is the testing strategy for this PR?

SQL logic tests in functional_dependencies.slt:

• the query above (the inner join is kept)
• GROUP BY the key alone (the semi join is still used)
• an outer GROUP BY over the aggregate keeps all groups
• an aggregate value selected through a projection that removes a determined column
• the DISTINCT and window-frame symptoms from the issue (the DISTINCT is kept, the frame stays RANGE)
• a deterministic computed expression next to the key (upper(i_desc)), one that reads an undetermined column (s_date + 1), and a volatile one (random() > 0.5)
• computed expressions next to a column the key does not determine (GROUP BY i_item_sk, s_date, upper(i_desc), s_date + 1), and a GROUP BY that reads no column (GROUP BY 'x')

The first and third fail without the fix, and the projection case fails without the change to the GROUP BY dependency. The s_date + 1 case fails if every computed expression counts as determined, and the random() case fails without the volatility check. The GROUP BY i_item_sk, s_date, ... case fails without removing computed expressions from the GROUP BY key, and the GROUP BY 'x' case returns no row instead of 0 if a key can have no columns. All sqllogictests pass.

Are there any user-facing changes?

Yes. Queries such as the one above now return the correct result. Queries that relied on the wrong dependencies, such as SELECT s_date ... GROUP BY i_item_sk over f, are now rejected at planning.

aggregate_functional_dependencies is deprecated in favour of aggregate_functional_dependencies_with_computed_inputs, as described in the upgrade guide. There are no breaking API changes.

Disclaimer: I used AI to assist in the code generation, I have manually reviewed the output and it matches my intention and understanding.