SEO

G'day all,

PG version: 8.1.4 (also 7.4.13)
OS: Linux (debian/testing)

In an update, using a sub-select from a table with the same name
but in a different schema results in an error. The same thing
works if you alias the sub-select table or if you qualify the
schema of the update table.

See script below.

I'm not sure if this is a bug or if it's displaying my ignorance
of this corner of SQL...

Cheers,

Chris.

----------------------------------------------------------------------
create table a (id integer, name text);
insert into a values ('1', 'thomas');
insert into a values ('2', 'edward');

create schema temp;
create table temp.a (id integer, name text);
insert into temp.a values ('1', 'tom');
insert into temp.a values ('2', 'eddie');

--
-- fails with:
-- ERROR: more than one row returned by a subquery used as an expression
--
update a set name = (
select name
from temp.a
where temp.a.id = a.id
)

--
-- same thing with schema-qualified update table: works as expected
--
update public.a set name = (
select name
from temp.a
where temp.a.id = public.a.id
);

--
-- same thing but using an alias: works as expected
--
update a set name = (
select name
from temp.a foo
where foo.id = a.id
);
----------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 in the case of alias-less relation
* RTEs. It is possible that such a refname matches multiple RTEs in the
* nearest nesting level that has a match; if so, we report an error via
* ereport().
*
* A qualified refname (schemaname != NULL) can only match a relation RTE
* that (a) has no alias and (b) is for the same relation identified by
* schemaname.refname. In this 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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...3/msg00403.php
It looks like we followed Oracle.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org