[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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.30.0
• Fix Version/s: 1.33.0
• Assignee: Unassigned
• Reporter: Ian Bertolacci
• Created: 12/Jul/22 01:51
• Updated: 17/Apr/23 17:57
• Resolved: 17/Apr/23 17:57

Description

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:

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

The exception is:

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)

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:

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(...)

Additionally, the below queries also produce RelNode trees
Using an alias:

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

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 }]])

Not using the group-by value in the projection:

select
    count(*)
from T1000
group by
    case
        when Column_3 in (1, 2, 3, 4, 5) THEN 1
        else 0
    end

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 }]])

Issue Links

• is fixed by: CALCITE-5209 Proper sub-query handling if it is used inside select list and group by
• is related to: CALCITE-5645 Correlated scalar sub-query returns incorrect results when the correlating variable is NULL
• is related to: CALCITE-5156 Support implicit integer type cast for IN Sub-query
• relates to: CALCITE-5209 Proper sub-query handling if it is used inside select list and group by