Обсуждение: Delete from table where id in (bugged query) - fully remove all data without any notice.

`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.

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

On Fri, Oct 21, 2022 at 11:38 PM Kostya M <kostya27@gmail.com> wrote:
>
> Ok, thanks.
> For me even this scope resolution looks quite strange and unexpected.
>

See also the FAQ entry for this question:


https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F