[
https://issues.apache.org/jira/browse/CALCITE-5145?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17538980#comment-17538980
]
Yingyu Wang commented on CALCITE-5145:
--------------------------------------
Actually the type mismatch exception occurs even without using alias:
{noformat}
0: jdbc:calcite:model=src/test/resources/mode> SELECT empno,
. . . . . . . . . . . . . . . . . . . . . . .> CASE WHEN name IN
('Fred','Eric') THEN 'CEO' ELSE 'Other' END
. . . . . . . . . . . . . . . . . . . . . . .> FROM emps
. . . . . . . . . . . . . . . . . . . . . . .> GROUP BY GROUPING SETS ((empno,
CASE WHEN name IN ('Fred','Eric') THEN 'CEO' ELSE 'Other' END),(empno));
java.lang.AssertionError: Conversion to relational algebra failed to preserve
datatypes:
validated type:
RecordType(INTEGER EMPNO, CHAR(5) NOT NULL EXPR$1) NOT NULL
converted type:
RecordType(INTEGER EMPNO, CHAR(5) EXPR$1) NOT NULL
rel:
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]])
LogicalProject(EMPNO=[$0], EXPR$1=[CASE(OR(=($1, 'Fred'), =($1, 'Eric')),
CAST('CEO'):CHAR(5) NOT NULL, 'Other')])
LogicalTableScan(table=[[SALES, EMPS]])
at
org.apache.calcite.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:474)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:591)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:264)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:230)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:758)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:619)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:589)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:221)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:603)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
at
org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:209)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
{noformat}
So it's a problem of using CASE statement within GROUPING SETS. It is different
from CALCITE-4512 and CALCITE-5045.
> Alias of 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
>
> 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)
