Zhen Chen created CALCITE-7318:
----------------------------------
Summary: 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
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)
