Thread: Bug or Feature? Subquery issue.

Folks,

Came across this counter-intuitive behavior on IRC today:

test1=3D> create table vhost(idvhost serial primary key, foo integer);
NOTICE:  CREATE TABLE will create implicit sequence "vhost_idvhost_seq" for=
=20
"serial" column "vhost.idvhost"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "vhost_pkey"=
=20
for table "vhost"
CREATE TABLE
test1=3D> create table domain(iddomain serial primary key, bar integer);
NOTICE:  CREATE TABLE will create implicit sequence "domain_iddomain_seq" f=
or=20
"serial" column "domain.iddomain"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "domain_pkey=
"=20
for table "domain"
CREATE TABLE
test1=3D> create table forwarding(idforwarding serial primary key, iddomain=
=20
integer references domain, baz integer);
NOTICE:  CREATE TABLE will create implicit sequence=20
"forwarding_idforwarding_seq" for "serial" column "forwarding.idforwarding"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index=20
"forwarding_pkey" for table "forwarding"
ERROR:  relation "forwarding_idforwarding_seq" already exists
test1=3D> insert into domain
test1-> values (100, 5);
INSERT 147824 1
test1=3D> insert into forwarding
test1-> values (1, 100, 15);
INSERT 147825 1
test1=3D> insert into vhost values (100, 15);
INSERT 147826 1
test1=3D> --this generates an error
test1=3D> select iddomain from vhost where IDvhost =3D 100;
ERROR:  column "iddomain" does not exist
test1=3D> -- This should generate an error, because IDdomain isn't a column=
 of=20
vhost
test1=3D> --instead it deletes a row.
test1=3D> delete from forwarding where iddomain in (select iddomain from vh=
ost=20
where idvhost =3D 100);
DELETE 1
test1=3D>

According to Neil, what's happening is that "select iddomain" in the subque=
ry=20
is grabbing the iddomain column from the forwarding table in the outer quer=
y.=20=20
This is not intutive, for certain; however, what I don't know is if it's SQ=
L=20
Spec.

So, my question:  does the SQL spec allow for citing the outer query in the=
=20
SELECT target list of a subquery?=20=20=20=20=20

If yes, this is a feature, if no, a bug.=20=20=20

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco

On Tue, 21 Oct 2003, Josh Berkus wrote:

> Folks,
>
> Came across this counter-intuitive behavior on IRC today:

> test1=> --this generates an error
> test1=> select iddomain from vhost where IDvhost = 100;
> ERROR:  column "iddomain" does not exist
> test1=> -- This should generate an error, because IDdomain isn't a column of
> vhost
> test1=> --instead it deletes a row.
> test1=> delete from forwarding where iddomain in (select iddomain from vhost
> where idvhost = 100);
> DELETE 1
> test1=>
>
> According to Neil, what's happening is that "select iddomain" in the subquery
> is grabbing the iddomain column from the forwarding table in the outer query.
> This is not intutive, for certain; however, what I don't know is if it's SQL
> Spec.
>
> So, my question:  does the SQL spec allow for citing the outer query in the
> SELECT target list of a subquery?

AFAICT yes. I don't see anything that would limit a column reference that
was an outer reference from being in the target list in general (there are
specific limitations for some subcases) at least in sql92.

Josh Berkus <josh@agliodbs.com> writes:
> Came across this counter-intuitive behavior on IRC today:

Given that this step in your example failed:

> test1=> create table forwarding(idforwarding serial primary key, iddomain
> integer references domain, baz integer);
> NOTICE:  CREATE TABLE will create implicit sequence
> "forwarding_idforwarding_seq" for "serial" column "forwarding.idforwarding"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "forwarding_pkey" for table "forwarding"
> ERROR:  relation "forwarding_idforwarding_seq" already exists

it's impossible to be certain what situation you are really
testing... but assuming that that isn't affecting the results,

> test1=> select iddomain from vhost where IDvhost = 100;
> ERROR:  column "iddomain" does not exist
> test1=> -- This should generate an error, because IDdomain isn't a column of
> vhost
> test1=> --instead it deletes a row.
> test1=> delete from forwarding where iddomain in (select iddomain from vhost
> where idvhost = 100);

This is absolutely NOT an error.  iddomain in the subquery is a
legitimate outer reference, if it's not otherwise known in the subquery.
There is no clause in the SQL spec that says that outer references are
invisible in any context ... even if it means you just deleted your
whole table, which is what I think will happen here...

            regards, tom lane

Tom,

> This is absolutely NOT an error.  iddomain in the subquery is a
> legitimate outer reference, if it's not otherwise known in the subquery.
> There is no clause in the SQL spec that says that outer references are
> invisible in any context ... even if it means you just deleted your
> whole table, which is what I think will happen here...

Yup, that's what happened.

Wasn't sure.    We're OK then.

--
Josh Berkus
Aglio Database Solutions
San Francisco