[jira] [Commented] (CALCITE-5145) CASE statement within GROUPING SETS throws type mis-match exception

["yanjing.wang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5145?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17558317#comment-17558317 ] 

yanjing.wang commented on CALCITE-5145:
---------------------------------------

Hi [~yingyu] , sorry for seeing your comments late, it's actually two cases of the select item type re-deriving problem, I fuse your changes into [pr-2838|https://github.com/apache/calcite/pull/2838], add you as a co-author, and make some simplifications, please review when you are available and let me know if you have some suggestions, sorry for adding your tests into the pr without your permission, I want to the pr could catch up with the 1.31.0 release ASAP and ruben is following it.

> 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)