It would also be helpful if you could create a Jira task for this. I think the issue is as follows:
The DELETE statement WHERE part is generated from the source SELECT [1]. If the source SELECT looks like this: SELECT * from tab1 b where exists (select 1 from tab2 a where a.c2=2 and a.c1=b.c1); hen the WHERE part will be empty, and the generated statement will be: DELETE FROM "tab1" as you mentioned. [1] https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L1164 On Fri, Oct 24, 2025 at 2:33 AM Mihai Budiu <[email protected]> wrote: > Calcite is really a framework to build query execution engines, it does > not store data or execute statements by itself. You have to describe more > about how your setup uses Calcite. > > Mihai > > ________________________________ > From: Jordin Catanzaro <[email protected]> > Sent: Thursday, October 23, 2025 2:54 PM > To: [email protected] <[email protected]> > Subject: Potential Bug, delete statement with correlated subquery and > conditionals unexpectedly removes all records > > > Hi! > > > > We have encountered a delete query with subquery containing conditionals > that deletes all records from the target table (Apache Calcite, version: > 1.40.0). The same query with postgres driver removes the single matching > record as expected > `delete from tab1 b where exists (select 1 from tab2 a where a.c2=2 and > a.c1=b.c1);` > > > > Here is how to reproduce the issue: > ```sql > -- 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" > ``` > > > > I have attached the query plan for reference. It appears 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. > Is this a known issue, and are there any workarounds? I didn't see > anything when I browsed through the Jira - happy to report a bug if this is > unexpected behavior. > > > > Please let me know if you need more details. > Thank you! > > > > Jordin > > >
