[ https://issues.apache.org/jira/browse/CALCITE-5145?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
ASF GitHub Bot updated CALCITE-5145: ------------------------------------ Labels: pull-request-available (was: ) > 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)