[ https://issues.apache.org/jira/browse/CALCITE-4434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17255767#comment-17255767 ]
Julian Hyde commented on CALCITE-4434: -------------------------------------- Only a couple of minor things: * the close-parenthesis should be on the same line, not a new line; * the comment {{// generate relation for `=(Row1,Row2)`}} is unclear to me; "expand 'ROW (x0, x1, ...) = ROW (y0, y1, ...)' to 'x0 = y0 AND x1 = y1 AND ...'" would be clearer; * consider using {{Pair.forEach(call0.getOperands(), call1.getOperands(), (op0, op1) -> \{...})}} rather than {{for (... cmpCount ...)}} * use {{RexUtil.composeConjunction}} rather than {{rexBuilder.makeCall(type, SqlStdOperatorTable.AND, ...}}. > 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)