[MarkusWinand]

Hi!

The first feature matrix you see is about checking of functional dependencies.

For example, PostgreSQL doesn't see the functional dependency between the columns ID and MA in the result of this query.

    SELECT id
         , max(a) ma
      FROM ...
     GROUP BY id

As a consequence, this query doesn't work, because the outer query refers to MA which is not in the GROUP BY clause of the outer query:

    SELECT COUNT(*) cnt
         , ma
      FROM (SELECT id
                 , max(a) ma
              FROM ...
             GROUP BY id) x
      GROUP BY id

Detecting these things makes life easier.

[joshberkus]

That's a fairly obtuse example. Is there a standard you're drawing from, for this? Because this example works fine:

    postgres=# create table func_test ( id int primary key, data text );
    CREATE TABLE
    postgres=# insert into func_test values ( 1, 'josh' ), ( 2, 'markus' );
    INSERT 0 2
    postgres=# select id, data from func_test group by id;
     id |  data  
    ----+--------
      2 | markus 
      1 | josh

[MarkusWinand]

In SQL:2016, it is

> 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.

[joshberkus]

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.

[wesd]

That example code seems overly complicated and redundant. Should the code be simpler like this:

  SELECT id
        , max(a) ma
         , count(*) Cnt
   FROM ...
  GROUP BY id

[MarkusWinand]

The purpose of the example is to demonstrate functional dependencies based on grouped tables.