[jira] [Created] (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

["Ian Bertolacci (Jira)" <ji...@apache.org>]

Ian Bertolacci created CALCITE-5210:
---------------------------------------

             Summary: "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
            Reporter: Ian Bertolacci


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)