> 4.24.13 Known functional dependencies in the result of a <group by clause>
Not that this is about the result of a <group by clause>
edit: The matrix I have in the article is not complete. I've just picked a few examples from the standard and checked them to get an overview. I guess the fact that PostgreSQL supports some of them but MySQL more of them is well represented in the matrix.
The full text is below. I'm happy to correct, when I'm interpreting this wrong (which is perfectly possible).
Let T1 be the table that is the operand of the <group by clause>, and let R be the result of the <group by clause>. Let G be the set of columns specified by the <grouping column reference list> of the <group by clause>, after
applying all syntactic transformations to eliminate ROLLUP, CUBE, and GROUPING SETS.
The columns of R are the columns of G, with an additional column CI, whose value in any particular row of R somehow denotes the subset of rows of T1 that is associated with the combined value of the columns of G in that row.
If every element of G is a column reference to a known not null column, then G is a BUC-set of R. If G is a subset of a BPK-set of columns of T1, then G is a BPK-set of R.
G ↦ CI is a known functional dependency in R.
Hmmm. PostgreSQL treats output rowsets as their own thing, so this works differently in a subquery than in a top-level query. Not that that's desireable, it's just a consequence of an implementation that gives a lot more flexibility in other areas (table functions, for example, where Postgres goes well beyond the standard). So looking at the standard I agree that having your example work is superior to not having it work. I'd appreciate a footnote for that particular item for Postgres, though, since the most common forms of GROUP BY dependencies do work.
> 4.24.13 Known functional dependencies in the result of a <group by clause>
Not that this is about the result of a <group by clause>
edit: The matrix I have in the article is not complete. I've just picked a few examples from the standard and checked them to get an overview. I guess the fact that PostgreSQL supports some of them but MySQL more of them is well represented in the matrix.
The full text is below. I'm happy to correct, when I'm interpreting this wrong (which is perfectly possible).
Let T1 be the table that is the operand of the <group by clause>, and let R be the result of the <group by clause>. Let G be the set of columns specified by the <grouping column reference list> of the <group by clause>, after applying all syntactic transformations to eliminate ROLLUP, CUBE, and GROUPING SETS. The columns of R are the columns of G, with an additional column CI, whose value in any particular row of R somehow denotes the subset of rows of T1 that is associated with the combined value of the columns of G in that row. If every element of G is a column reference to a known not null column, then G is a BUC-set of R. If G is a subset of a BPK-set of columns of T1, then G is a BPK-set of R. G ↦ CI is a known functional dependency in R.