On Thu, Dec 3, 2009 at 8:21 PM, Fred Wei <w...@niwa.co.nz> wrote: > > The following bug has been logged online: > > Bug reference: 5233 > Logged by: Fred Wei > Email address: w...@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.
In the first query, k refers to tmp.k. This is fairly surprising in this particular case, but it's intentional. I've found that it's a good idea to almost-always qualify variable references in queries that mention more than one table. There are all kinds of confusing things that can happen if you don't. ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
