[CALCITE-5296] In a query with ROLLUP, validator wrongly infers that a column is NOT NULL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.33.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Xurenhe
• Reporter: Xurenhe
• Created: 23/Sep/22 03:10
• Updated: 07/Feb/23 22:33
• Resolved: 09/Oct/22 06:44

Description

CALCITE throws exception during the stage of sql_to_rel,  when executing SELECT statement  after the statement of ROLLUP, and grouping's item exists in the SELECT statement.

 

Error message:

Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(INTEGER DEPTNO, INTEGER NOT NULL EXPR$1) NOT NULL
converted type:
RecordType(INTEGER DEPTNO, INTEGER EXPR$1) NOT NULL
rel:
LogicalProject(DEPTNO=[$0], EXPR$1=[CASE(=($2, 0), $0, 1)])
  LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[GROUPING($0)])
    LogicalProject(DEPTNO=[$7], JOB=[$2])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(INTEGER DEPTNO, INTEGER NOT NULL EXPR$1) NOT NULL
converted type:
RecordType(INTEGER DEPTNO, INTEGER EXPR$1) NOT NULL
rel:
LogicalProject(DEPTNO=[$0], EXPR$1=[CASE(=($2, 0), $0, 1)])
  LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[GROUPING($0)])
    LogicalProject(DEPTNO=[$7], JOB=[$2])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])    at org.apache.calcite.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:492)
    at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:607)
    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.testCaseWhenGroupingSet(SqlToRelConverterTest.java:4687) 

 

Test case:(org.apache.calcite.test.SqlToRelConverterTest#testCaseWhenGroupingSet)

// JAVA: org.apache.calcite.test.SqlToRelConverterTest#testCaseWhenGroupingSet

@Test void testCaseWhenGroupingSet() {
  final String sql = "select deptno, case when grouping(deptno) = 0 then deptno else 1 end\n"
      + "from emp\n"
      + "group by rollup(deptno, job)";
  sql(sql).ok();
}

// XML: org/apache/calcite/test/SqlToRelConverterTest.xml:456 
  <TestCase name="testCaseWhenGroupingSet">
    <Resource name="sql">
      <![CDATA[SELECT deptno, CASE WHEN grouping(deptno) = 0 THEN deptno ELSE 1 END
FROM emp
GROUP BY ROLLUP(deptno, job)]]>
    </Resource>
    <Resource name="plan">
      <![CDATA[
LogicalProject(DEPTNO=[$0], EXPR$1=[CASE(=($2, 0), $0, 1)])
  LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[GROUPING($0)])
    LogicalProject(DEPTNO=[$7], JOB=[$2])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
    </Resource>
  </TestCase>

 

 

Issue Links

• links to: GitHub Pull Request #2920