Thread: Problems with outer joins in 7.1beta5

My problem is that my two outer joined tables have columns that have the
same names.  Therefore when my select list tries to reference the
columns they are ambiguously defined.  Looking at the doc I see the way
to deal with this is by using the following syntax:

table as alias (column1alias, column2alias,...)

So we can alias the conficting column names to resolve the problem.
However the problem with this is that the column aliases are positional
per the table structure. Thus column1alias applies to the first column
in the table. Code that relies on the order of columns in a table is
very brittle. As adding a column always places it at the end of the
table, it is very easy to have a newly installed site have one order
(the order the create table command creates them in) and a site
upgrading from an older version (where the upgrade simply adds the new
columns) to have column orders be different.

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.

I will fix my code so that it works given how postgres currently
supports the column aliases.

Can anyone point me to a copy of the SQL92 spec so that I can research
this more?

thanks,
--Barry

Barry Lind <barry@xythos.com> writes:
> 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.

Unfortunately, the spec authors seem to have forgotten that basic design
rule when they wrote the aliasing syntax.  Column alias lists are
position-sensitive:

         <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 column list> ::= <column name list>

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

SQL99 seems to be no better.  Sorry.

            regards, tom lane

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