Re: Bug or Feature? Subquery issue.

Josh Berkus <josh(at)agliodbs(dot)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