Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE
| От | David G. Johnston |
|---|---|
| Тема | Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE |
| Дата | |
| Msg-id | CAKFQuwZ_Wm7Uj8VBGuT0Gd=_32PKkz6b9+LL4cL1Q2=xomsbKQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | BUG #15737: Unexpectedly Deleting full table when referring CTE (With Clause ) data,in a Subquery in another CTE (PG Bug reporting form <noreply@postgresql.org>) |
| Ответы |
Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE
|
| Список | pgsql-bugs |
On Thu, Apr 4, 2019 at 1:08 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15737
Logged by: Chandan Ahuja
Email address: chandanahuja7@gmail.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.
В списке pgsql-bugs по дате отправления: