On Fri, Oct 21, 2022 at 9:25 PM Kostya M <kostya27@gmail.com> wrote:
>
> `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.
>
No, what was done is expected due to scope resolution.
When the tmp_table has no id column then table2 is checked if it has
an id column. So the query is executed as:
delete from table2 where id in (select table2.id from tmp_table);
which will delete everything from table2 (as long as tmp_table has 1
row or more).
You should be prefixing columns with table name (or alias) so you
don't run into this, e,g.:
delete from table2 as t where t.id in (select tmp.id from tmp_table as tmp);
Pantelis Theodosiou