BUG #4220: delete statement deleted too many rows
The following bug has been logged online:
Bug reference: 4220
Logged by: Lon Varscsak
Email address: varscsak@smarthealth.com
PostgreSQL version: 8.3.1
Operating system: Linux (RHEL 5)
Description: delete statement deleted too many rows
Details:
I executed this query:
delete from customer_transactions_detail where transaction_id in (select
transaction_id from test);
The transaction_id column does NOT exist in the temporary table named
'test'). I would think this would just result in an error, instead it
delete all rows in the customer_transactions_detail table.
Yikes!
On Wed, Jun 04, 2008 at 06:46:42PM +0000, Lon Varscsak wrote:
delete from customer_transactions_detail where transaction_id in (select
transaction_id from test);
The transaction_id column does NOT exist in the temporary table named
'test'). I would think this would just result in an error, instead it
delete all rows in the customer_transactions_detail table.
what you got is so called "correlated subquery", and is perfectly valid
- even if it's surprising sometimes.
i wrote about it in more details in here:
http://www.depesz.com/index.php/2007/09/06/postgresql-gotchas/
anyway - it's definitely not a bug.
depesz
hubert depesz lubaczewski <depesz@depesz.com> writes:
On Wed, Jun 04, 2008 at 06:46:42PM +0000, Lon Varscsak wrote:
delete from customer_transactions_detail where transaction_id in (select
transaction_id from test);
The transaction_id column does NOT exist in the temporary table named
'test'). I would think this would just result in an error, instead it
delete all rows in the customer_transactions_detail table.
i wrote about it in more details in here:
http://www.depesz.com/index.php/2007/09/06/postgresql-gotchas/
This isn't a "postgres gotcha", it's a "SQL standard gotcha". Any DBMS
that fails to execute the query exactly that way is violating the spec.
regards, tom lane
Wow, I want it to violate the spec so I can get my rows back! :)
I understand the problem and why it did what it did now though.
Thanks for your help,
Lon
On Wed, Jun 4, 2008 at 1:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
hubert depesz lubaczewski <depesz@depesz.com> writes:
On Wed, Jun 04, 2008 at 06:46:42PM +0000, Lon Varscsak wrote:
delete from customer_transactions_detail where transaction_id in (select
transaction_id from test);
The transaction_id column does NOT exist in the temporary table named
'test'). I would think this would just result in an error, instead it
delete all rows in the customer_transactions_detail table.i wrote about it in more details in here:
http://www.depesz.com/index.php/2007/09/06/postgresql-gotchas/This isn't a "postgres gotcha", it's a "SQL standard gotcha". Any DBMS
that fails to execute the query exactly that way is violating the spec.regards, tom lane
On Wed, Jun 04, 2008 at 01:58:19PM -0700, Lon Varscsak wrote:
Wow, I want it to violate the spec so I can get my rows back! :)
I understand the problem and why it did what it did now though.
you might find this post also helpful (for future):
http://www.depesz.com/index.php/2007/07/27/update-account-set-password-new_password-oops/
Best regards,
depesz