Re: [BUGS] Bug or Feature? Subquery issue.

#1

Josh Berkus <josh (AT) agliodbs (DOT) com> writes:

Quote:

Came across this counter-intuitive behavior on IRC today:

Given that this step in your example failed:

Quote:

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,

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#2

Tom,

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] Bug or Feature? Subquery issue.

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] Bug or Feature? Subquery issue. - 10-21-2003 , 10:02 PM

Re: [BUGS] Bug or Feature? Subquery issue. - 10-21-2003 , 10:29 PM