From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Fred Wei <weif(at)niwa(dot)co(dot)nz> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5233: delete wrong doing with subquery where condition |
Date: | 2009-12-04 02:54:08 |
Message-ID: | 603c8f070912031854w400dfb62u5e8958bd080f342a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2009-12-04 03:26:03 | Re: BUG #5217: (new) error with VB 6.0 SP 6 and psqlODBC 8.4.1 |
Previous Message | Robert Haas | 2009-12-04 02:01:44 | Re: BUG #5230: Limit operator slows down |