[CALCITE-4434] Cannot implement 'CASE row WHEN row ...'

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Labels: pull-request-available
• Assignee: Zhen Wang
• Reporter: Julian Hyde
• Created: 10/Dec/20 08:07
• Updated: 03/Jun/21 22:32
• Resolved: 05/Jan/21 20:23

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'.

Issue Links

• links to: GitHub Pull Request #2298
• links to: GitHub Pull Request #2301
• links to: GitHub Pull Request #2309