Re: BUG #5233: delete wrong doing with subquery where condition

On Thu, Dec 3, 2009 at 8:21 PM, Fred Wei <weif(at)niwa(dot)co(dot)nz> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5233
> Logged by:          Fred Wei
> Email address:      weif(at)niwa(dot)co(dot)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