Delete from table where id in (bugged query) - fully remove all data without any notice.

From: Kostya M <kostya27(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Delete from table where id in (bugged query) - fully remove all data without any notice.
Date: 2022-10-21 19:06:23
Message-ID: CACAwW2jDOAjQVMPcNJ00qEhhYao7HEiOrw+UDXO6rz0LXB43Tg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

`PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0,
64-bit`

I write little bugged query and fully remove my data. Which was unexpected.

`select min(id) into tmp_table from table1 group by some_field;`
`delete from table2 where id in (select id from tmp_table);`

Problem that `tmp_table` have no `id` column (accidentally column
called `min`). So subquery was with error. But delete just fully
remove data without crashing or noticing.

What i expected that delete just write that subquery crashed, and not
delete anything.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pantelis Theodosiou 2022-10-21 21:08:28 Re: Delete from table where id in (bugged query) - fully remove all data without any notice.
Previous Message PG Bug reporting form 2022-10-21 15:49:09 BUG #17659: error in rpmbuild when compiling psql