Re: [HACKERS] 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>.