Who's up for a little language-lawyering discussion?
I have just noticed that our parser is probably in error in treating
GROUP BY and ORDER BY expressions similarly. This came up while
checking whether we were doing the right thing in rejecting
SELECT complicated-expression AS foo FROM table WHERE foo < 42;
Our parser will accept AS-names in ORDER BY and GROUP BY clauses,
but not in WHERE or HAVING. But eyeballing the spec makes it look like
AS-names should *only* be recognized in ORDER BY, nowhere else. The
spec's organization of a SELECT query is
<direct select statement: multiple rows> ::= <query expression> [ <order by clause> ]
<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>
<table expression> ::= <from clause> [ <where clause> ] [ <group by clause>
] [ <having clause> ]
(<query expression> reduces to <query specification>s combined by
UNION/INTERSECT/EXCEPT, which are not of interest here).
Now the interesting thing about this is that WHERE, GROUP BY, and HAVING
are all defined to use column names that are defined by the <table
expression> they're in. As far as I can see, that means they can use
column names that come from tables in the FROM clause. There isn't any
suggestion that they can refer to SELECT-list items from the enclosing
<query specification>.
The ORDER BY clause, however, is allowed to reference columns of the
<query expression>'s result --- ie, columns from the <select list>
--- either by name or number. So it's definitely OK to use an AS-name
in ORDER BY.
Currently, because the parser uses the same code to interpret ORDER BY
and GROUP BY lists, it will accept AS-names and column numbers in both
kinds of clauses. Unless I've misread the spec, this is an error.
Can anyone confirm or refute my reasoning?
Next question is, do we want to leave the code as-is, or tighten up
the parser to reject AS-names and column numbers in GROUP BY?
It seems to me we should change it, because there are cases where the
existing code will do the wrong thing according to the SQL spec.
If "foo" is a column name and also an AS-name for something else,
"GROUP BY foo" should group on the raw column according to the spec,
but right now we will pick the SELECT result value instead.
regards, tom lane