Search Postgresql Archives

Re: Column aliases in GROUP BY and HAVING

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Peter J. Holzer" <hjp-pgsql@xxxxxx> writes:
> In my mental model of how SQL works, the clauses of an SQL query (if
> present) are processed in a certain order:

>     FROM
>     WHERE
>     SELECT
>     GROUP BY
>     HAVING
>     ORDER BY
>     LIMIT

The SELECT list is certainly done after GROUP BY/HAVING.  Consider

SELECT 1/x FROM ... GROUP BY x HAVING x > 0;

One would be justifiably upset to get a divide-by-zero error from this.

Its relationship to ORDER BY is a bit more tenuous, mainly because of
the SQL92 legacy notion that you can ORDER BY a select-list column.
I'm too lazy to check the code right now, but I think our current
policy is that SELECT expressions are evaluated after ORDER BY/LIMIT
unless they are used as GROUP BY or ORDER BY keys.  Without that,
you'd get unhappy performance results from

SELECT id, expensive_function(x) FROM ... ORDER BY id LIMIT 1;

> In the GROUP BY clause I can use the alias year which was defined
> earlier in SELECT. 

This is a pretty unfortunate legacy thing that we support because
backwards compatibility (and because "GROUP BY 1" is so frequently
a handy shortcut).  Semantically, it's a mess, not only because of
the when-to-evaluate confusion but because it's not too clear
whether a column name refers to a SELECT output column or to some
table column emitted by the FROM clause.  We try to limit the
potential for that sort of confusion by only trying to match
SELECT output names to GROUP/ORDER BY items when the latter are
simple identifiers.

> HAVING comes after GROUP BY, so I should be able to use that there, too.
> Right?

No.  We're not going there.  The core reason why not is that HAVING
expressions are seldom plain column names, so it wouldn't work anyway
unless we opened the floodgates on where SELECT output names could be
matched.

> This seems inconsistent to me. Is there a technical or semantic reason
> for this or is just "because the standard says so".

I think SQL versions newer than SQL92 disallow these references entirely.

(I'm being fairly brief here, but this has all been litigated multiple
times before.  See the archives.)

			regards, tom lane





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux