[ https://issues.apache.org/jira/browse/CALCITE-4434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17256580#comment-17256580 ]
Julian Hyde commented on CALCITE-4434: -------------------------------------- It doesn’t have to be a new PR. In fact it’s better to use the same PR. Also, as committer you can change the message before you merge someone else’s change. If that’s all that’s wrong, it’s less effort than asking the contributor to do it. > 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 > Assignee: Zhen Wang > Priority: Major > Labels: pull-request-available > Time Spent: 1h 40m > 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)