You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2023/04/16 16:19:00 UTC
[jira] [Commented] (CALCITE-5210) "type mismatch" litmus test failure during during SqlToRelConverter for group-by on `case` having `in` expression predicates exceeding SqlRelConverter.Config InSubQueryThreshold with nullable left-hand-side
[ https://issues.apache.org/jira/browse/CALCITE-5210?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17712807#comment-17712807 ]
Julian Hyde commented on CALCITE-5210:
--------------------------------------
[~ian.bertolacci], Now that CALCITE-5209 is fixed, can you please revise the summary/description or close as a duplicate? There are a lot of open cases relating to correlated scalar subquery and it is helpful if each case is focused on just one problem.
> "type mismatch" litmus test failure during during SqlToRelConverter for group-by on `case` having `in` expression predicates exceeding SqlRelConverter.Config InSubQueryThreshold with nullable left-hand-side
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5210
> URL: https://issues.apache.org/jira/browse/CALCITE-5210
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.30.0
> Reporter: Ian Bertolacci
> Priority: Major
>
> For these examples assume that:
> - {{`SqlToRelConverter.Config.config().withInSubQueryThreshold(5)`}} has been set.
> - The source table has 4 columns (Column_0 through Column_3)
> - Each column is of type BIGINT (nullable = true)
> The failing query is:
> {code}
> select
> case
> when Column_0 in (1, 2, 3, 4, 5) THEN 1
> else 0
> end
> from T1000
> group by
> case
> when Column_0 in (1, 2, 3, 4, 5) THEN 1
> else 0
> end
> {code}
> The exception is:
> {code}
> jjava.lang.AssertionError: type mismatch:
> ref:
> BIGINT
> input:
> INTEGER NOT NULL
> java.lang.AssertionError: type mismatch:
> ref:
> BIGINT
> input:
> INTEGER NOT NULL
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32)
> at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2211)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:129)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:61)
> at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:114)
> at org.apache.calcite.rel.core.Project.isValid(Project.java:219)
> at org.apache.calcite.rel.core.Project.<init>(Project.java:98)
> at org.apache.calcite.rel.logical.LogicalProject.<init>(LogicalProject.java:69)
> at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:126)
> at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:114)
> at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:178)
> at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:2025)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1797)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1769)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1758)
> at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:4680)
> at org.apache.calcite.sql2rel.SqlToRelConverter.substituteSubQuery(SqlToRelConverter.java:1268)
> at org.apache.calcite.sql2rel.SqlToRelConverter.replaceSubQueries(SqlToRelConverter.java:1127)
> at org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:3325)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:3192)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:738)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:664)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3589)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:589)
> at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:259)
> {code}
> The table could contain N >= 0 columns, and the use of any column below index 3 will also cause this exception.
> The use of any column at or beyond index 3 triggers an exception described in CALCITE-5209.
> If the number of predicates is less than the subquery threshold, there are no issues, and produces the RelNode tree:
> {code}
> 24:LogicalAggregate(group=[{0}])
> 23:LogicalProject(EXPR$0=[CASE(SEARCH($0, Sarg[1L:BIGINT, 2L:BIGINT, 3L:BIGINT, 4L:BIGINT]:BIGINT), 1, 0)])
> 22:TableScan(...)
> {code}
> Additionally, the below queries also produce RelNode trees
> Using an alias:
> {code}
> select
> case
> when Column_0 in (1, 2, 3, 4, 5) THEN 1
> else 0
> end as CASE_ALIAS
> from T1000
> group by
> CASE_ALIAS
> {code}
> {code}
> 66:LogicalAggregate(group=[{0}])
> 65:LogicalProject(CASE_ALIAS=[CASE(AND(<>($1, 0), IS NOT NULL($5)), 1, 0)])
> 64:LogicalJoin(condition=[=($3, $4)], joinType=[left])
> 60:LogicalProject(Column_0=[$0], $f0=[$1], $f1=[$2], Column_00=[$0])
> 59:LogicalJoin(condition=[true], joinType=[inner])
> 55:TableScan(...)
> 58:LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
> 57:LogicalProject(ROW_VALUE=[$0], $f1=[true])
> 56:LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }]])
> 63:LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
> 62:LogicalProject(ROW_VALUE=[$0], $f1=[true])
> 61:LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }]])
> {code}
> Not using the group-by value in the projection:
> {code}
> select
> count(*)
> from T1000
> group by
> case
> when Column_3 in (1, 2, 3, 4, 5) THEN 1
> else 0
> end
> {code}
> {code}
> 92:LogicalProject(EXPR$0=[$1])
> 91:LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> 90:LogicalProject($f0=[CASE(AND(<>($1, 0), IS NOT NULL($5)), 1, 0)])
> 89:LogicalJoin(condition=[=($3, $4)], joinType=[left])
> 85:LogicalProject(Column_0=[$0], $f0=[$1], $f1=[$2], Column_00=[$0])
> 84:LogicalJoin(condition=[true], joinType=[inner])
> 80:TableScan(...)
> 83:LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
> 82:LogicalProject(ROW_VALUE=[$0], $f1=[true])
> 81:LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }]])
> 88:LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
> 87:LogicalProject(ROW_VALUE=[$0], $f1=[true])
> 86:LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)