Re: BUG #16520: Deleting from non-existent column in CTE removes all rows

On Tuesday, June 30, 2020, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16520
> Logged by: Jesse Lieberg
> Email address: jesse007(at)ymail(dot)com
> PostgreSQL version: 12.3
> Operating system: debian:buster-slim
> Description:
>
> However, the this will not return an error and instead deletes all rows:
> ```
> WITH to_delete AS (
> SELECT MIN(id), username
> FROM test
> GROUP BY username
> HAVING count(*) > 1
> )
> DELETE FROM test
> WHERE id IN (
> SELECT id
> FROM to_delete
> );
>

Yes, because the column id does exist - you just omitted the table
reference which ends up making the subquery query equivalent to: “select
test.id from to_delete” which is mandatory, and generally useful, sql
syntax.

David J.