RE: [HACKERS] SELECT ... AS ... names in WHERE/GROUP BY/HAVING

Hi, Tom,

It's my understanding that WHERE and GROUP BY will only accept table or view
columns, while ORDER BY and HAVING will accept SELECT columns (aliases) as
well.  I'll double check this with Oracle (Oracle tends to be pretty SQL
compliant), but it makes sense to me.

So according to my view of the world ;-) HAVING is broken, because it
rejects aliases, and GROUP BY is broken because it accepts them.  Of course,
I haven't looked at the spec, and Oracle could adhere to an older spec which
may have changed.  At least I don't have to take any responsibility for my
claims ;-)

OK, I've just checked it against Oracle, and what you had originally seems
to be the way to go: no aliases for WHERE, GROUP BY, or HAVING.  However,
aggregates are allowed in the HAVING clause.  Also, aliases are allowed for
ORDER BY.

So, according to Oracle's view of the world, HAVING is orrect because it
rejects aliases, but GROUP BY is broken because it accepts them.


MikeA


-----Original Message-----
From: Tom Lane
To: pgsql-hackers@postgreSQL.org
Sent: 99/12/15 07:05
Subject: [HACKERS] SELECT ... AS ... names in WHERE/GROUP BY/HAVING

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

************