Thread: 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: > 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