[
https://issues.apache.org/jira/browse/CALCITE-7250?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18033863#comment-18033863
]
Jordin Catanzaro commented on CALCITE-7250:
-------------------------------------------
[~dmsysolyatin] , If you know of others who might also have some ideas, {_}can
you tag them here{_}? We tried the suggestions you gave, and it didn't work
out. This was the error:
{code:java}
java.sql.SQLException: Error while executing SQL "delete from "public"."tab1" b
where exists (select 1 from "public"."tab2" a where a."c2"=2 and
a."c1"=b."c1")": class org.apache.calcite.rel.logical.LogicalTableModify cannot
be cast to class org.apache.calcite.adapter.enumerable.EnumerableRel
(org.apache.calcite.rel.logical.LogicalTableModify and
org.apache.calcite.adapter.enumerable.EnumerableRel are in unnamed module of
loader 'app') {code}
We may have found a strange work around that might shed some light on the
problem - I need to validate it and will post more details if so.
> 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.35.0, 1.40.0
> Reporter: Jordin Catanzaro
> Priority: Critical
> 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)
