[
https://issues.apache.org/jira/browse/CALCITE-2947?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16801427#comment-16801427
]
Xiening Dai edited comment on CALCITE-2947 at 3/26/19 6:38 AM:
---------------------------------------------------------------
This is different than CALCITE-2901. In Haisheng's test, the exception is
thrown while building below logic plan -
{quote}LogicalFilter(condition=[=($9, $10)])
LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(DEPTNO=[$7], $f1=[true])
LogicalFilter(condition=[=($0, $cor0.EMPNO)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){quote}
When creating LogicalCorrelate, since the correlate has left join type, its row
type is derived from join row type which would add nullability to the original
INTEGER type (see SqlValidatorUtil.deriveJoinRowType), and cause the validation
of filter node to fail.
In my view, this issue cannot be fixed without generating a different plan. The
fix of CALCITE-2004, which pushes down the filter, might fix this as well.
was (Author: xndai):
This is different than CALCITE-2901. In Haisheng's test, the exception is
thrown while building below logic plan -
LogicalFilter(condition=[=($9, $10)])
LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(DEPTNO=[$7], $f1=[true])
LogicalFilter(condition=[=($0, $cor0.EMPNO)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
When creating LogicalCorrelate, since the correlate has left join type, its row
type is derived from join row type which would add nullability to the original
INTEGER type (see SqlValidatorUtil.deriveJoinRowType), and cause the validation
of filter node to fail.
In my view, this issue cannot be fixed without generating a different plan. The
fix of Calcite-2004, which pushes down the filter, might fix this as well.
> Type mismatch assertion error when converting NOT IN subquery
> -------------------------------------------------------------
>
> Key: CALCITE-2947
> URL: https://issues.apache.org/jira/browse/CALCITE-2947
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Haisheng Yuan
> Priority: Major
>
> Repro:
> Add the following test to SqlToRelConverterTest.java.
> {code:java}
> @Test public void testSubQueryNotIN() {
> final String sql = "select deptno\n"
> + "from EMP e\n"
> + "where deptno not in (select deptno\n"
> + "from EMP where empno=e.empno)";
> sql(sql).ok();
> }
> {code}
> Error:
> {code:java}
> java.lang.AssertionError: type mismatch:
> ref:
> INTEGER NOT NULL
> input:
> INTEGER
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
> at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:1832)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:125)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:57)
> at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
> at org.apache.calcite.rel.core.Filter.isValid(Filter.java:120)
> at
> org.apache.calcite.rel.logical.LogicalFilter.<init>(LogicalFilter.java:70)
> at
> org.apache.calcite.rel.logical.LogicalFilter.create(LogicalFilter.java:114)
> at
> org.apache.calcite.rel.logical.LogicalFilter.create(LogicalFilter.java:101)
> at
> org.apache.calcite.rel.core.RelFactories$FilterFactoryImpl.createFilter(RelFactories.java:300)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2433)
> {code}
> If we change not in subquery to in subquery, it can run without error.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
