Re: Feedback about Drupal SQL debugging

* Greg Stark (gsstark(at)mit(dot)edu) wrote:
> select distinct on (a) a,b,c from a ORDER BY a,b,c
>
> But Postgres insists you have an ORDER BY which has to agree with the
> DISTINCT ON columns and provide some extra column(s) to determine
> which values of b,c are chosen.

Not quite technically correct. You have to have an ORDER BY which
includes the columns inside the DISTINCT ON, but not any more than that.
At that point, the values you get for the other columns are arbitrary.
PG does *allow* you to provide other columns in the ORDER BY, so you can
specify which values from those other columns should be used.

I'm not advocating that we force another column to be used, nor do I
think you are, but I have to admit that I don't think I've ever used it
w/o other columns in the ORDER BY.

> If Postgres changed on this front it would be to support the SQL
> Standard concept of "functional dependency". In cases where some
> columns are guaranteed to be unique you can leave them out of the
> GROUP BY but still use them in the select list. This isn't MySQL's
> behaviour of just allowing you to leave them out and hope that it
> doesn't matter which row's values are used. The database has to
> actually determine that it really doesn't matter. Typically that would
> be because you've grouped by a set of columns which form the key of a
> unique constraint, in which case every other column from that table
> would also necessarily be the same since they would all come from the
> same row of that table.

Hrmm. That sounds kinda neat, but you'd still have to specify one of
the columns in the GROUP BY, I presume? Or could you just say 'GROUP
BY' without any columns, and have it GROUP BY the key of the table
you're using?

Thanks,

Stephen