[CALCITE-5145] CASE statement within GROUPING SETS throws type mis-match exception

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.31.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Yingyu Wang
• Created: 10/May/22 02:46
• Updated: 03/Aug/22 16:21
• Resolved: 28/Jun/22 07:23

Description

Adding following test in SqlToRelConverterTest.java can reproduce the problem

Note that the problem occurs when there are more than one grouping sets in the query, e.g.

• group by grouping sets ((empno, derived_col)) is ok

• group by grouping sets ((empno, derived_col),(empno)) produces the error

//core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
  @Test public void testGroupingSetsDerivedCol() {
    sql("SELECT empno,\n"
        + "CASE\n"
        + "when ename in ('Fred','Eric') then 'CEO'\n"
        + "else 'Other'\n"
        + "END AS derived_col\n"
        + "from emp\n"
        + "group by grouping sets ((empno, derived_col),(empno))")
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

The error:

Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(INTEGER NOT NULL EMPNO, CHAR(5) NOT NULL DERIVED_COL) NOT NULL
converted type:
RecordType(INTEGER NOT NULL EMPNO, CHAR(5) DERIVED_COL) NOT NULL
rel:
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]])
  LogicalProject(EMPNO=[$0], DERIVED_COL=[CASE(SEARCH($1, Sarg['Eric', 'Fred']:CHAR(4)), 'CEO  ', 'Other')])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(INTEGER NOT NULL EMPNO, CHAR(5) NOT NULL DERIVED_COL) NOT NULL
converted type:
RecordType(INTEGER NOT NULL EMPNO, CHAR(5) DERIVED_COL) NOT NULL
rel:
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]])
  LogicalProject(EMPNO=[$0], DERIVED_COL=[CASE(SEARCH($1, Sarg['Eric', 'Fred']:CHAR(4)), 'CEO  ', 'Other')])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

	at org.apache.calcite.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:487)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:602)
	at org.apache.calcite.sql.test.AbstractSqlTester.convertSqlToRel2(AbstractSqlTester.java:536)
	at org.apache.calcite.sql.test.AbstractSqlTester.assertSqlConvertsTo(AbstractSqlTester.java:477)
	at org.apache.calcite.sql.test.AbstractSqlTester.assertConvertsTo(AbstractSqlTester.java:455)
	at org.apache.calcite.test.SqlToRelFixture.convertsTo(SqlToRelFixture.java:106)
	at org.apache.calcite.test.SqlToRelFixture.ok(SqlToRelFixture.java:94)
	at org.apache.calcite.test.SqlToRelConverterTest.testGroupingSetsDerivedCol(SqlToRelConverterTest.java:344)

Issue Links

• relates to: CALCITE-5045 Alias within GroupingSets throws type mis-match exception
• links to: GitHub Pull Request #2818