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

On Thu, Dec 3, 2009 at 8:21 PM, Fred Wei <weif@niwa.co.nz> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A05233
> Logged by: =A0 =A0 =A0 =A0 =A0Fred Wei
> Email address: =A0 =A0 =A0weif@niwa.co.nz
> PostgreSQL version: 8.1.11
> Operating system: =A0 SUSE Linux Enterprise Server 10 SP2 (x86_64)
> Description: =A0 =A0 =A0 =A0delete wrong doing with subquery where condit=
ion
> Details:
>
> the following steps show a scenario where delete command removes all reco=
rds
> which are not supposed to do with a subquery condition:
>
> cod_old=3D# create table tmp(k int);
> CREATE TABLE
> cod_old=3D# insert into tmp values(1);
> INSERT 0 1
> cod_old=3D# create table tmp2(k2 int);
> CREATE TABLE
> cod_old=3D# insert into tmp2 values(2);
> INSERT 0 1
> cod_old=3D# =A0select count(*) from tmp;
> =A0count
> -------
> =A0 =A0 1
> (1 row)
>
> cod_old=3D# delete from tmp where k in (select k from tmp2);
> DELETE 1
> --this is wrong, because k does not exist in tmp2!
> cod_old=3D# select count(*) from tmp;
> =A0count
> -------
> =A0 =A0 0
> (1 row)
>
> cod_old=3D# insert into tmp values(1);
> INSERT 0 1
> cod_old=3D# delete from tmp where k in (select tmp2.k from tmp2);
> ERROR: =A0column 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