Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE

On Fri, 5 Apr 2019 at 09:26, David G. Johnston
 wrote:
>
> On Thu, Apr 4, 2019 at 1:08 PM PG Bug reporting form  wrote:
>> -- I am referring contract_id column here from deletedata but it does not
>> exist
>> delete from core.contract where contract_id in ( select distinct contract_id
>> from deletedata) -- returning contract_id
>
>
> No bugs; its just the hard (but common) way to really learn the difference between a correlated subquery and an independent one; and to test your deletion queries thoroughly before running them live.

Ouch! ... the hard way to learn to always give your tables an alias
and prefix the column names with them.

There are cases where it could also happen if a column is dropped.
Best not to leave these landmines laying around:

postgres=# delete from t1 where b in(select b from t2);
DELETE 0
postgres=# alter table t2 drop column b;
ALTER TABLE
postgres=# delete from t1 where b in(select b from t2);
DELETE 1000

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services