[
https://issues.apache.org/jira/browse/CALCITE-5145?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17541785#comment-17541785
]
xiong duan commented on CALCITE-5145:
-------------------------------------
[~yingyu] This issue does not just appear in a Case expression statement. For
example:
{noformat}
@Test public void testGroupingSetsDerivedCol() {
sql("select dname,deptno\n" +
"from dept\n" +
"group by grouping sets ((dname,deptno),(dname))")
.withConformance(SqlConformanceEnum.LENIENT).ok();
}{noformat}
will be failed too. Please add the test case about this SQL too. This issue is
because the select item is not nullable, but the group makes the item become
nullable.
> CASE statement within GROUPING SETS throws type mis-match exception
> -------------------------------------------------------------------
>
> Key: CALCITE-5145
> URL: https://issues.apache.org/jira/browse/CALCITE-5145
> Project: Calcite
> Issue Type: Bug
> Reporter: Yingyu Wang
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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
> {code:java}
> //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();
> }
> {code}
> The error:
> {noformat}
> 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)
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
