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

On Thu, Apr 4, 2019 at 1:08 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15737
> Logged by: Chandan Ahuja
> Email address: chandanahuja7(at)gmail(dot)com
> PostgreSQL version: 11.1
> Operating system: RHEL
> Description:

....

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

Since the contract_id column has not been provided a specific table
qualifier the planner is free to choose any contract_id column it can
find. Since core.contract has a contract_id column that one is chosen; and
so you've created a correlated subquery that is the the equivalent of:

delete from core.contract where contract_id = contract_id;

Which you should agree deletes every row.

Writing the following would provoke the error you are expecting:

delete from core.contract where contract_id in (select distinct
deletedata.contract_id from deletedata);

Then, since you implemented ON CASCADE DELETE (which is good, why are you
bothering to perform a manual cascade per the above?), the deletion from
core.contract cascades to core.contractcomponent.

David J.