"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
