[ https://issues.apache.org/jira/browse/CALCITE-4434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17248764#comment-17248764 ]
Zhen Wang commented on CALCITE-4434: ------------------------------------ https://github.com/apache/calcite/pull/2298 Array Compare is actually using Arrays.deepEq which treats tinyint and literal int differently. I can see this causes other issues else where, should this be fixed ? > Cannot implement 'CASE row WHEN row ...' > ---------------------------------------- > > Key: CALCITE-4434 > URL: https://issues.apache.org/jira/browse/CALCITE-4434 > Project: Calcite > Issue Type: Bug > Reporter: Julian Hyde > Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Cannot implement 'CASE row WHEN row ...'. > For example, the test > {noformat} > diff --git a/core/src/test/resources/sql/misc.iq > b/core/src/test/resources/sql/misc.iq > index 3c945e2cc..84ef67b6c 100644 > --- a/core/src/test/resources/sql/misc.iq > +++ b/core/src/test/resources/sql/misc.iq > @@ -1065,6 +1065,47 @@ Expression 'DEPTNO' is not being grouped > > !use scott > > +# [CALCITE-xxxx] Cannot implement 'CASE row WHEN row ...' > +SELECT deptno, job, > + CASE (deptno, job) > + WHEN (20, 'CLERK') THEN 1 > + WHEN (30, 'SALESMAN') THEN 2 > + ELSE 3 > + END AS x > +FROM "scott".emp > +WHERE empno < 7600; > ++--------+----------+---+ > +| DEPTNO | JOB | X | > ++--------+----------+---+ > +| 20 | CLERK | 1 | > +| 20 | MANAGER | 3 | > +| 30 | SALESMAN | 2 | > +| 30 | SALESMAN | 2 | > ++--------+----------+---+ > +(4 rows) > +!ok > + > +# Equivalent to previous > +SELECT deptno, job, > + CASE > + WHEN deptno = 20 AND job = 'CLERK' THEN 1 > + WHEN deptno = 30 AND job = 'SALESMAN' THEN 2 > + ELSE 3 > + END AS x > +FROM "scott".emp > +WHERE empno < 7600; > ++--------+----------+---+ > +| DEPTNO | JOB | X | > ++--------+----------+---+ > +| 20 | CLERK | 1 | > +| 20 | MANAGER | 3 | > +| 30 | SALESMAN | 2 | > +| 30 | SALESMAN | 2 | > ++--------+----------+---+ > +(4 rows) > + > +!ok > + > {noformat} > fails with the following stack trace: > {noformat} > Unable to implement EnumerableCalc(expr#0..7=[{inputs}], expr#8=[ROW($t7, > $t2)], expr#9=[CAST($t8):RecordType(INTEGER EXPR$0, VARCHAR(9) EXPR$1) NOT > NULL], expr#10=[20], expr#11=['CLERK'], expr#12=[ROW($t10, $t11)], > expr#13=[CAST($t12):RecordType(INTEGER EXPR$0, VARCHAR(9) EXPR$1) NOT NULL], > expr#14=[=($t9, $t13)], expr#15=[1], expr#16=[30], expr#17=['SALESMAN'], > expr#18=[ROW($t16, $t17)], expr#19=[CAST($t18):RecordType(INTEGER EXPR$0, > VARCHAR(9) EXPR$1) NOT NULL], expr#20=[=($t9, $t19)], expr#21=[2], > expr#22=[3], expr#23=[CASE($t14, $t15, $t20, $t21, $t22)], expr#24=[7600], > expr#25=[<($t0, $t24)], DEPTNO=[$t7], JOB=[$t2], X=[$t23], > $condition=[$t25]): rowcount = 7.0, cumulative cost = {21.0 rows, 435.0 cpu, > 0.0 io}, id = 49495 > EnumerableTableScan(table=[[scott, EMP]]): rowcount = 14.0, cumulative cost > = {14.0 rows, 15.0 cpu, 0.0 io}, id = 49458 > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:322) > ... > Caused by: java.lang.IllegalStateException: Unable to implement > EnumerableCalc(expr#0..7=[{inputs}], expr#8=[ROW($t7, $t2)], > expr#9=[CAST($t8):RecordType(INTEGER EXPR$0, VARCHAR(9) EXPR$1) NOT NULL], > expr#10=[20], expr#11=['CLERK'], expr#12=[ROW($t10, $t11)], > expr#13=[CAST($t12):RecordType(INTEGER EXPR$0, VARCHAR(9) EXPR$1) NOT NULL], > expr#14=[=($t9, $t13)], expr#15=[1], expr#16=[30], expr#17=['SALESMAN'], > expr#18=[ROW($t16, $t17)], expr#19=[CAST($t18):RecordType(INTEGER EXPR$0, > VARCHAR(9) EXPR$1) NOT NULL], expr#20=[=($t9, $t19)], expr#21=[2], > expr#22=[3], expr#23=[CASE($t14, $t15, $t20, $t21, $t22)], expr#24=[7600], > expr#25=[<($t0, $t24)], DEPTNO=[$t7], JOB=[$t2], X=[$t23], > $condition=[$t25]): rowcount = 7.0, cumulative cost = {21.0 rows, 435.0 cpu, > 0.0 io}, id = 49495 > EnumerableTableScan(table=[[scott, EMP]]): rowcount = 14.0, cumulative cost > = {14.0 rows, 15.0 cpu, 0.0 io}, id = 49458 > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114) > at > org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113) > at > org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1130) > ... > Suppressed: java.lang.NullPointerException: SqlTypeFamily for > RecordType(INTEGER EXPR$0, VARCHAR(9) EXPR$1) > at java.base/java.util.Objects.requireNonNull(Objects.java:348) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.scaleIntervalToNumber(RexToLixTranslator.java:943) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCast(RexToLixTranslator.java:593) > at > org.apache.calcite.adapter.enumerable.RexImpTable$CastImplementor.implementSafe(RexImpTable.java:2511) > at > org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.genValueStatement(RexImpTable.java:2972) > at > org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.implement(RexImpTable.java:2937) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1142) > ... > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.implementCaseWhen(RexToLixTranslator.java:1199) > {noformat} > The test contains an equivalent query where {{ROW (deptno, job) = ROW (20, > 'CLERK')}} has been expanded to {{deptno = 20 AND job = 'CLERK'}}. -- This message was sent by Atlassian Jira (v8.3.4#803005)