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

*Thanks much Mr David Johnston and Mr David Rowley.*

Apologies for not replying sooner.
Yes this issue made me learn the importance of Aliasing the tables and
prefixing the column names in SQL.
I wont forget it now.
Thankfully the mistake didn't prove to be costly as i was able to restore
the DB from the backup :-)

Best Regards
Chandan Ahuja

On Fri, Apr 5, 2019 at 2:44 AM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On Fri, 5 Apr 2019 at 09:26, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> > On Thu, Apr 4, 2019 at 1:08 PM PG Bug reporting form <
> noreply(at)postgresql(dot)org> 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
>

--
Best Regards,
Chandan Ahuja