Folks, Came across this counter-intuitive behavior on IRC today:
test1=> create table vhost(idvhost serial primary key, foo integer); NOTICE: CREATE TABLE will create implicit sequence "vhost_idvhost_seq" for "serial" column "vhost.idvhost" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "vhost_pkey" for table "vhost" CREATE TABLE test1=> create table domain(iddomain serial primary key, bar integer); NOTICE: CREATE TABLE will create implicit sequence "domain_iddomain_seq" for "serial" column "domain.iddomain" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "domain_pkey" for table "domain" CREATE TABLE 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 test1=> insert into domain test1-> values (100, 5); INSERT 147824 1 test1=> insert into forwarding test1-> values (1, 100, 15); INSERT 147825 1 test1=> insert into vhost values (100, 15); INSERT 147826 1 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? If yes, this is a feature, if no, a bug. -- -Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
