PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15737
PG Version11.1
OSRHEL
Opened2019-04-04 20:08:02+00
Reported byChandan Ahuja
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15737
Logged by:          Chandan Ahuja
Email address:      (redacted)
PostgreSQL version: 11.1
Operating system:   RHEL
Description:        

Hi , 

I faced  this unexpected behavior when i use CTE (WITH clause) query to
delete the rows from multiple tables. Because of which my entire data-set
has been deleted. It looks like this is a bug.

I have 3 tables , (1)Contract , (2)ContractComponent  and (3)ContractRole. 
Lets call them CT, CC and CR respectively.
CT has One-To-Many relation with CC  
CC   has One-To-Many relation with CR.
Table Structure:
(1)Contract (contract_id(PK)  )  , (2)ContractComponent
(Contractcomponent_id(PK) , contract_id(FK) ), (3)ContractRole
(ContractRole_id(PK) ,  ContractComponent_id (FK),  
party_source_system_record_pk   ).

I have applied Foreign Key constraint in these 3 tables with On-Delete
Cascade option.

I wanted to delete ONLY one particular Contract (CT) record and its
corresponding records in CC and CR tables so I made the following query. 
This Query is giving 2 strange results which i am not able to fathom, and it
appears to me that it is a BUG.

ISSUES/BUG:

1. Contract_id column is not specified in the Select clause of "deletedata "
, still PostgreSQL does not complains in the "delct" and goes on to execute
the SQL.
2. But the bigger issue is that on execution it deletes all the records from
the Contract and ContractComponent table, and  not just the one meeting the
filter clause specified  (where cr.party_source_system_record_pk  =
'20000151686').

------------------------------------------------------------------------------------------------------------------------------------------------------------

with deletedata as
(
select  cc.contractcomponent_id   
---- Contract_id column is not specified in the Select clause of
"deletedata"  , still PostgreSQL does  not complains in the "delct"
from 
core.contractrole cr
join core.contractcomponent cc on  cc.contractcomponent_id =
cr.contractcomponent_id
join core.contract ct on  cc.contract_id = ct.contract_id
where 
cr.party_source_system_record_pk  = '20000151686'
)
, delcc as
(
delete from core.contractcomponent where contractcomponent_id in ( select
distinct contractcomponent_id from deletedata)
	-- returning contractcomponent_id
)
, delct as
(
-- 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
)
select distinct contractcomponent_id from deletedata
------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks an anticipation.

Messages

DateAuthorSubject
2019-04-04 20:08:02+00PG Bug reporting formBUG #15737: Unexpectedly Deleting full table when referring CTE (With Clause ) data,in a Subquery in another CTE
2019-04-04 20:26:21+00"David G(dot) Johnston"Re: BUG #15737: Unexpectedly Deleting full table when referring CTE (With Clause ) data,in a Subquery in another CTE
2019-04-05 00:43:52+00David RowleyRe: BUG #15737: Unexpectedly Deleting full table when referring CTE (With Clause ) data,in a Subquery in another CTE
2019-04-10 20:59:10+00Chandan AhujaRe: BUG #15737: Unexpectedly Deleting full table when referring CTE (With Clause ) data,in a Subquery in another CTE