[
https://issues.apache.org/jira/browse/CALCITE-7250?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18034856#comment-18034856
]
Dmitry Sysolyatin edited comment on CALCITE-7250 at 11/2/25 3:32 PM:
---------------------------------------------------------------------
[~jordin] Sorry, I don’t really know anyone either. I tried to reproduce the
bug myself, but I’m not going to spend more time on it (at least this month).
Please "refloat" the discussion as Ruben said or invest your company resources
to fix it.
You can find test case by link [1]. I tried to disable `SubQueryRemoveRule`
[2], but I got an error:
{code}
Correlation variable $cor0 should be defined
java.lang.AssertionError: Correlation variable $cor0 should be defined
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.getCorrelVariableGetter(EnumerableRelImplementor.java:476)
at
org.apache.calcite.adapter.jdbc.JdbcCorrelationDataContextBuilderImpl.add(JdbcCorrelationDataContextBuilderImpl.java:52)
at
org.apache.calcite.adapter.jdbc.JdbcImplementor$2.field(JdbcImplementor.java:74)
{code}
This happens because the logic of {{EnumerableRelImplementor}} depends heavily
on {{SubQueryRemoveRule}}. The {{implementor.registerCorrelVariable}} method
isn’t called inside {{RexSubQuery}} to register correlation variables. I
haven’t tested this on PostgreSQL yet because I couldn’t quickly set up the
tests with {{CalciteAssert.DB == DatabaseInstance.POSTGRESQL}}. Probably in
that case, {{EnumerableRelImplementor}} isn’t used, since the whole query
should be handled on the PostgreSQL side.
[1]
https://github.com/apache/calcite/commit/7a9a09fe7259eea05fa8a93aef2117b8bb615fb3
[2]
https://github.com/apache/calcite/commit/7a9a09fe7259eea05fa8a93aef2117b8bb615fb3#diff-096406e2c7bd30d08842ce317776634533ce1956ec1fb4a1669a093dd85c6bc8R313-R314
was (Author: dmsysolyatin):
[~jordin] Sorry, I don’t really know anyone either. I tried to reproduce the
bug myself, but I’m not going to spend more time on it (at least this month).
Please "refloat" the discussion as Ruben said or invest your company resources
to fix it.
You can find test case by link [1]. I tried to disable `SubQueryRemoveRule`
[2], but I got an error:
{code}
Correlation variable $cor0 should be defined
java.lang.AssertionError: Correlation variable $cor0 should be defined
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.getCorrelVariableGetter(EnumerableRelImplementor.java:476)
at
org.apache.calcite.adapter.jdbc.JdbcCorrelationDataContextBuilderImpl.add(JdbcCorrelationDataContextBuilderImpl.java:52)
at
org.apache.calcite.adapter.jdbc.JdbcImplementor$2.field(JdbcImplementor.java:74)
{code}
This happens because the logic of EnumerableRelImplementor depends heavily on
SubQueryRemoveRule. The implementor.registerCorrelVariable method isn’t called
inside RexSubQuery to register correlation variables. I haven’t tested this on
PostgreSQL yet because I couldn’t quickly set up the tests with
CalciteAssert.DB == DatabaseInstance.POSTGRESQL. Probably in that case,
EnumerableRelImplementor isn’t used, since the whole query should be handled on
the PostgreSQL side.
[1]
https://github.com/apache/calcite/commit/7a9a09fe7259eea05fa8a93aef2117b8bb615fb3
[2]
https://github.com/apache/calcite/commit/7a9a09fe7259eea05fa8a93aef2117b8bb615fb3#diff-096406e2c7bd30d08842ce317776634533ce1956ec1fb4a1669a093dd85c6bc8R313-R314
> 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)
