Jordin Catanzaro created CALCITE-7250:
-----------------------------------------
Summary: Unexpected deletion of all table records for DELETE ...
WHERE EXISTS <subquery>
Key: CALCITE-7250
URL: https://issues.apache.org/jira/browse/CALCITE-7250
Project: Calcite
Issue Type: Bug
Affects Versions: 1.40.0
Reporter: Jordin Catanzaro
Attachments: planner-2025-10-22-1.log
We discovered a delete query with subquery containing conditionals that deletes
_all records from the target table_ (tested in Apache Calcite, version:
1.40.0). The same query with postgres driver removes the single matching record
as expected. Here is the offending query:
{code:java}
delete from tab1 b where exists (select 1 from tab2 a where a.c2=2 and
a.c1=b.c1);{code}
Here is how to reproduce the issue:
{code:java}
-- Setup tables and data in postgres
create table tab1 (c1 int, c2 int);
create table tab2 (c1 int,c2 int);
insert into tab1 values (1,1);
insert into tab1 values (2,2);
insert into tab2 values (2,2);
-- Delete query
delete from tab1 b where exists (
select 1 from tab2 a where a.c2=2 and a.c1=b.c1
);
-- Check results
select * from tab1;
-- Expected (postgres): (1,1)
-- Actual (calcite): (empty set)
-- Calcite generated query: DELETE FROM "tab1"
{code}
I have attached the query plan for reference. Perhaps there is an issue when
applying both `a.c2=2 and a.c1=b.c1`? Removing `a.c=2` allows `a.c1=b.c1` to
resolve correctly.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
