[
https://issues.apache.org/jira/browse/CALCITE-5296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17608579#comment-17608579
]
Julian Hyde commented on CALCITE-5296:
--------------------------------------
That's a clever bug! I guess {{deptno}} is NOT NULL in the source but becomes
nullable (due to the rollup) by the time it leaves the {{GROUP BY}}, and
something is looking at for the type in the wrong context.
An analogous thing happens during outer join:
{code}
SELECT d.deptno
FROM Emp AS e LEFT JOIN Dept AS d ON e.deptno = d.deptno
{code}
{{d.deptno}} is NOT NULL in line 2 but nullable in line 1.
> SELECT function within grouping-set's item throws exception, during
> converting sql to relnode
> ---------------------------------------------------------------------------------------------
>
> Key: CALCITE-5296
> URL: https://issues.apache.org/jira/browse/CALCITE-5296
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Xurenhe
> Assignee: Xurenhe
> Priority: Major
>
> 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{*}:
> {code:java}
> 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)
> {code}
>
> {*}Test
> case{*}:(org.apache.calcite.test.SqlToRelConverterTest#testCaseWhenGroupingSet)
> {code:java}
> // 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>{code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
