[
https://issues.apache.org/jira/browse/CALCITE-7318?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18043322#comment-18043322
]
Zhen Chen commented on CALCITE-7318:
------------------------------------
This SQL query originated from CALCITE-5588, but the current error is
different. Therefore, a new JIRA ticket has been created to track this issue.
If this problem is resolved, CALCITE-5588 can also be closed.
> Execution fails when the JOIN ON condition contains references to columns
> from both the left and right sides
> ------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7318
> URL: https://issues.apache.org/jira/browse/CALCITE-7318
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: Zhen Chen
> Priority: Major
>
> The following SQL does not return correct results when executed in join.iq.
> {code:java}
> !use scott
> select Header.Name from ( VALUES (1, 'A'), (2, 'B')) as Header(Id, Name)
> join (values (11, 1), (12, 1), (21, 2)) as Version(Id, Parent)
> on not exists (select 1 from (values (11, 1), (12, 1), (21, 2)) as
> Version2(Id, Parent)
> where Version2.Parent = Header.Id and Version2.Id > Version.Id);
> +------+
> | NAME |
> +------+
> | A |
> | A |
> | B |
> +------+
> (3 rows)
> !ok {code}
> This result is from PostgreSQL execution.
> Error reported as follows:
> {code:java}
> select Header.Name from ( VALUES (1, 'A'), (2, 'B')) as Header(Id, Name)
> join (values (11, 1), (12, 1), (21, 2)) as Version(Id, Parent)
> on not exists (select 1 from (values (11, 1), (12, 1), (21, 2)) as
> Version2(Id, Parent)
> where Version2.Parent = Header.Id and Version2.Id > Version.Id);
> java.sql.SQLException: Error while executing SQL "select Header.Name from (
> VALUES (1, 'A'), (2, 'B')) as Header(Id, Name)
> join (values (11, 1), (12, 1), (21, 2)) as Version(Id, Parent)
> on not exists (select 1 from (values (11, 1), (12, 1), (21, 2)) as
> Version2(Id, Parent)
> where Version2.Parent = Header.Id and Version2.Id > Version.Id)": Unable to
> implement EnumerableCalc(expr#0..3=[{inputs}], NAME=[$t1]): rowcount = 1.5,
> cumulative cost = {21.5 rows, 9.5 cpu, 0.0 io}, id = 11974
> EnumerableNestedLoopJoin(condition=[NOT(EXISTS({
> LogicalFilter(condition=[AND(=($1, $cor0.ID), >($0, $cor0.ID0))])
> LogicalValues(tuples=[[{ 11, 1 }, { 12, 1 }, { 21, 2 }]])
> }))], joinType=[inner]): rowcount = 1.5, cumulative cost = {20.0 rows, 2.0
> cpu, 0.0 io}, id = 11970
> EnumerableValues(tuples=[[{ 1, 'A' }, { 2, 'B' }]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 1.0 cpu, 0.0 io}, id = 11910
> EnumerableValues(tuples=[[{ 11, 1 }, { 12, 1 }, { 21, 2 }]]): rowcount =
> 3.0, cumulative cost = {3.0 rows, 1.0 cpu, 0.0 io}, id = 11913
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:317)
> at net.hydromatic.quidem.Quidem.access$2600(Quidem.java:54)
> at net.hydromatic.quidem.Quidem$ContextImpl.checkResult(Quidem.java:1778)
> at
> net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:985)
> at net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1522)
> at net.hydromatic.quidem.Quidem.execute(Quidem.java:204)
> at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:307)
> at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:484)
> at org.apache.calcite.test.CoreQuidemTest.main(CoreQuidemTest.java:54)
> Caused by: java.lang.IllegalStateException: Unable to implement
> EnumerableCalc(expr#0..3=[{inputs}], NAME=[$t1]): rowcount = 1.5, cumulative
> cost = {21.5 rows, 9.5 cpu, 0.0 io}, id = 11974
> EnumerableNestedLoopJoin(condition=[NOT(EXISTS({
> LogicalFilter(condition=[AND(=($1, $cor0.ID), >($0, $cor0.ID0))])
> LogicalValues(tuples=[[{ 11, 1 }, { 12, 1 }, { 21, 2 }]])
> }))], joinType=[inner]): rowcount = 1.5, cumulative cost = {20.0 rows, 2.0
> cpu, 0.0 io}, id = 11970
> EnumerableValues(tuples=[[{ 1, 'A' }, { 2, 'B' }]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 1.0 cpu, 0.0 io}, id = 11910
> EnumerableValues(tuples=[[{ 11, 1 }, { 12, 1 }, { 21, 2 }]]): rowcount =
> 3.0, cumulative cost = {3.0 rows, 1.0 cpu, 0.0 io}, id = 11913
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:117)
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1190)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:332)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:221)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:673)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:524)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:492)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:246)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:654)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> ... 10 more
> Suppressed: java.lang.RuntimeException: cannot translate expression
> EXISTS({
> LogicalFilter(condition=[AND(=($1, $cor0.ID), >($0, $cor0.ID0))])
> LogicalValues(tuples=[[{ 11, 1 }, { 12, 1 }, { 21, 2 }]])
> })
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitSubQuery(RexToLixTranslator.java:1748)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitSubQuery(RexToLixTranslator.java:110)
> at org.apache.calcite.rex.RexSubQuery.accept(RexSubQuery.java:164)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.implementCallOperand(RexToLixTranslator.java:1480)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1467)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:110)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:208)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:1347)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:110)
> at org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:78)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:262)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:250)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCondition(RexToLixTranslator.java:1209)
> at
> org.apache.calcite.adapter.enumerable.EnumUtils.generatePredicate(EnumUtils.java:909)
> at
> org.apache.calcite.adapter.enumerable.EnumerableNestedLoopJoin.implement(EnumerableNestedLoopJoin.java:167)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
> ... 21 more
> !ok {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
