You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Zhen Wang (Jira)" <ji...@apache.org> on 2020/12/29 01:09:00 UTC
[jira] [Comment Edited] (CALCITE-4434) Cannot implement 'CASE row
WHEN row ...'
[ https://issues.apache.org/jira/browse/CALCITE-4434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17255759#comment-17255759 ]
Zhen Wang edited comment on CALCITE-4434 at 12/29/20, 1:08 AM:
---------------------------------------------------------------
[~julianhyde] I closed the previous and opened another. and created that per your suggestion, apart from the commit message are there anything else needs modification?
was (Author: zhenw):
[~julianhyde] I closed the previous and opened another. and created another per your suggestion, apart from the commit message are there anything else needs modification?
> 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)