You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2020/12/10 08:08:00 UTC
[jira] [Created] (CALCITE-4434) Cannot implement 'CASE row WHEN row
...'
Julian Hyde created CALCITE-4434:
------------------------------------
Summary: 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
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)