Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4434

Cannot implement 'CASE row WHEN row ...'

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.27.0
    • Component/s: None

      Description

      Cannot implement 'CASE row WHEN row ...'.

      For example, the test

      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
      +
      

      fails with the following stack trace:

      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)
      

      The test contains an equivalent query where ROW (deptno, job) = ROW (20, 'CLERK') has been expanded to deptno = 20 AND job = 'CLERK'.

        Attachments

          Activity

            People

            • Assignee:
              zhenw Zhen Wang
              Reporter:
              julianhyde Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 2.5h
                2.5h