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

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.

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?
        regards, tom lane

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

Simon Riggs <simon@2ndquadrant.com> writes:
> On Mon, 2006-10-02 at 11:38 -0400, Tom Lane wrote:
>> Postgres treats "FROM temp.a" the same as "FROM temp.a AS a",

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

I'm sorry, I phrased that sloppily.  In fact we don't treat them the
same, because with the "AS a" you can only refer to the entry as "a".
But the point here is that given "FROM temp.a" without AS, you can refer
to the entry as either "a" (if that is unique among the FROM-entries)
or "temp.a".

I came across a footnote in Date & Darwen (p. 140 in the 4th Edition)
implying that our interpretation is correct, but I still don't see where
the spec itself covers the point.

The comments for refnameRangeTblEntry indicate that at one time we
thought we saw support for our approach in the spec:
* An unqualified refname (schemaname == NULL) can match any RTE with matching* alias, or matching unqualified relname
inthe case of alias-less relation* RTEs.  It is possible that such a refname matches multiple RTEs in the* nearest
nestinglevel that has a match; if so, we report an error via* ereport().** A qualified refname (schemaname != NULL) can
onlymatch a relation RTE* that (a) has no alias and (b) is for the same relation identified by* schemaname.refname.
Inthis case we convert schemaname.refname to a* relation OID and search by relid, rather than by alias name.  This is*
peculiar,but it's what SQL92 says to do.
 
        regards, tom lane

I wrote:
> The comments for refnameRangeTblEntry indicate that at one time we
> thought we saw support for our approach in the spec:

Here is the discussion thread that settled on our current behavior:
http://archives.postgresql.org/pgsql-hackers/2002-03/msg00403.php
It looks like we followed Oracle.
        regards, tom lane