Re: Problems with outer joins in 7.1beta5

On Fri, Mar 16, 2001 at 10:17:33AM -0800, Barry Lind wrote:
>
> My feeling is that postgres has misinterpreted the SQL92 spec in this
> regards. But I am having problems finding an online copy of the SQL92
> spec so that I can verify.
>
> What I would expect the syntax to be is:
>
> table as alias (columna as aliasa, columnb as aliasb,...)
>
> This will allow the query to work regardless of what the table column
> order is.  Generally the SQL spec has tried not to tie query behaviour
> to the table column order.
>

What you expect, and what's in the spec. can be very different. As
the following quote shows, the definition is in fact order dependent:
note that a <derived column list> is a simple comma delimited list of
column names.

Quote from SQL'92:

    6.3  <table reference>

    Function

    Reference a table.

    Format

    <table reference> ::=
           <table name> [ [ AS ] <correlation name>
               [ <left paren> <derived column list> <right paren> ] ]
         | <derived table> [ AS ] <correlation name>
               [ <left paren> <derived column list> <right paren> ]
         | <joined table>

    <derived table> ::= <table subquery>

    <derived column list> ::= <column name list>

    <column name list> ::=
         <column name> [ { <comma> <column name> }... ]


    Syntax Rules

[...]

    7) If a <derived column list> is specified in a <table reference>,
       then the number of <column name>s in the <derived column list>
       shall be the same as the degree of the table specified by the
       <derived table> or the <table name> of that <table reference>,
       and the name of the i-th column of that <derived table> or the
       effective name of the i-th column of that <table name> is the
       i-th <column name> in that <derived column list>.