Обсуждение: BUG #5233: delete wrong doing with subquery where condition

Поиск
Список
Период
Сортировка

BUG #5233: delete wrong doing with subquery where condition

От
"Fred Wei"
Дата:
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.

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

От
Robert Haas
Дата:
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

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

От
Heikki Linnakangas
Дата:
Fred Wei wrote:
> cod_old=# delete from tmp where k in (select k from tmp2);
> DELETE 1
> --this is wrong, because k does not exist in tmp2!

That behavior is correct, k is referring to tmp.k. See
http://archives.postgresql.org/message-id/200910160726.n9G7QEEg097413@wwwmaster.postgresql.org

This should be added to the FAQ...

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com