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