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

Wed, 15 Dec 1999 12:05:41 -0500
Tom Lane <tgl@sss.pgh.pa.us>
SELECT ... AS ... names in WHERE/GROUP BY/HAVING
At 12:05 PM 12/15/99 -0500, Tom Lane wrote:
>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;

FWIW, here's what Oracle does:


SQL> select * from foo;

         I          J
---------- ----------
         1          2

SQL> select i as ii from foo where ii=3;
select i as ii from foo where ii=3
                              *
ERROR at line 1:
ORA-00904: invalid column name

This seems in agreement with PostgreSQL's rejection of the query.

>
>Our parser will accept AS-names in ORDER BY and GROUP BY clauses,

Oracle, again:


SQL> select i as ii from foo where i=1 group by ii;
select i as ii from foo where i=1 group by ii
                                           *
ERROR at line 1:
ORA-00904: invalid column name

BTW, at times at least it seems that PostgreSQL REQUIRES use of the
"as" name in group by, at least I've had queries I've been unable to move
from Oracle to PostgreSQL unless I've done so.  It's not consistent,
though, i.e. there's some kind of bug that pops up for some queries.
I've not had time to attempt to figure out exactly what differentiates
queries that work from queries that fail if I don't use the "as" name.

Here's Oracle's pronouncement on order by:


SQL> select i as ii from foo where i=1 order by ii;

        II
----------
         1


>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. 

And this jives with Oracle.  I offer this as supporting evidence only,
of course, I'm sure Oracle violates the standard in some ways as well
so we can't take their implementation as being definitive in regard
to standards issues.



>         <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.

Well, really it's a scoping issue, not a syntax issue.  What is the scope
of an identifier defined by an "as" identifier?  Of course, this is simple
enough that they might've been able to encapsulate the scope in the syntax.

Do you have the syntax for the various clauses available?  For instance,
two kinds of identifiers might be defined, say a column_id which must
really be the name of a real column and a more general id which is the
union of real column ids and "as" names.  

I just looked at the grammar in Date's book, and it says:

order-item ::= { column | integer } [ ASC | DESC ]

and GROUP BY is followed by a "column-ref-commalist"

which would leave me to think that the issue of where an "as" identifier
can be used is addressed semantically, not syntactically, since both
simply refer to "column" identifiers.  I don't have time at the 
moment to dig into Date's book further to see what he says, I can look
later if you want.

Keep in mind this is the very first time I've looked at the formal
syntax for SQL.  I have a background in language-lawyering, though.

>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?

My personal feeling is that minor extensions, including accidental
ones, work against the goal of standards which is of course to make
it easier to move stuff from one implementation to another.  From my
current perspective of moving nearly 9,000 lines of Oracle SQL (just
in the data model, with thousands more in the code that uses it) examples
like this where postgres implements a superset of the standard is a lot
easier to deal with than those areasa where postgres implements a subset
(no outer joins, for instance)!

But philosophically I'm a believer in standards and in making it
as easy as possible to move code back and forth between various
SQL engines.

Can it silently break a query, i.e. are there examples where an
identifier might refer to different columns in the two cases?  If not,
I wouldn't worry about it too much though if it were up to me I'd probably
adhere to the standard.  Silent breakage (i.e. "working" but returning an
incorrect result compared to the result you'd get with a standard
implementation)
is more insidious as such queries can be hard to uncover when porting
something.

>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.

Oops...silent breakage, all right.  Bad.  Yeah, it should be fixed, I
don't think there should be any question about it - assuming that a
closer reading of the standard verifies that it should work as you
and Oracle both seem to think it should.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Wed, 15 Dec 1999 11:12:54 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] SELECT ... AS ... names in WHERE/GROUP BY/HAVING
On 1999-12-15, Tom Lane mentioned:

> 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.

The AS-names are way too convenient to drop them. In the particular
example of ambiguity you could send a notice or a reject it. (What does
ORDER BY foo do in this case? Same problem.)

Perhaps it's really time for the --enable-sql option. :)

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Fri, 17 Dec 1999 01:31:39 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] SELECT ... AS ... names in WHERE/GROUP BY/HAVING