Re: [BUGS] Update using sub-select table in schema

On Mon, 2006-10-02 at 11:38 -0400, Tom Lane wrote:
> Chris Dunlop <chris@onthe.net.au> writes:
> > I'm not sure if this is a bug or if it's displaying my ignorance
> > of this corner of SQL...
>
> > update a set name = (
> >   select name
> >   from temp.a
> >   where temp.a.id = a.id
> > )
>
> Postgres treats "FROM temp.a" the same as "FROM temp.a AS a", and then
> the "a.id" references that rather than the outer query's A.  Try just
> "select name from temp.a where temp.a.id = a.id;" to see this in action.

[Which AFAICS doesn't follow SQL:2003, since a reference to temp.a
should not be allowed following its redefinition as a.]

> Looking at the SQL spec, I'm having a hard time finding any verbiage
> that either confirms or denies this interpretation.  It says that a FROM
> item without a correlation name (ie, an AS alias) exposes the table
> name, and then a column reference can refer to the table name, but it's
> not at all clear whether the table name must be qualified or not in the
> reference.  Comments anyone?

In my SQL:2003 draft, there is a "Language Opportunity" here:

"242 [From London] The following Opportunity exists:  For language consistency, a correlation name should be permitted
for
the modified table in positioned and searched update and delete
statements."

However, somebody claiming to be Joe Celko is quoted here as saying that
is not part of the SQL:2003 standard
http://www.thescripts.com/forum/thread65819.html

ISTM the most obvious route in this situation differs from normal usage:
treat any unqualified names that match the target table as a reference
to the target table, rather than potentially another table. i.e. treat
this situation as a correlated sub-query rather than as an independent
query.

No correlation name on the target table is allowed, so there is no
possibility of writing a correlation name to allow the query to be more
readable:

> update a correlationname set name = (
>    select name
>    from temp.a
>    where temp.a.id = correlationname.a.id
> )

(which is definitely not allowed by SQL:2003)

Having said all of that, its clearly a grey area so no need to change
this as part of beta, since we could easily cause more wierdness than we
solve.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com