The following bug has been logged online:
Bug reference: 5233
Logged by: Fred Wei
Email address: weif@niwa.co.nz
PostgreSQL version: 8.1.11
Operating system: SUSE Linux Enterprise Server 10 SP2 (x86_64)
Description: delete wrong doing with subquery where condition
Details:
the following steps show a scenario where delete command removes all records
which are not supposed to do with a subquery condition:
cod_old=# create table tmp(k int);
CREATE TABLE
cod_old=# insert into tmp values(1);
INSERT 0 1
cod_old=# create table tmp2(k2 int);
CREATE TABLE
cod_old=# insert into tmp2 values(2);
INSERT 0 1
cod_old=# select count(*) from tmp;
count
-------
1
(1 row)
cod_old=# delete from tmp where k in (select k from tmp2);
DELETE 1
--this is wrong, because k does not exist in tmp2!
cod_old=# select count(*) from tmp;
count
-------
0
(1 row)
cod_old=# insert into tmp values(1);
INSERT 0 1
cod_old=# delete from tmp where k in (select tmp2.k from tmp2);
ERROR: column t.k does not exist
--the last line should be the correct behaviour.
Regards.
