This is an automated email from the ASF dual-hosted git repository.
zhenchen pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 7bebc878de [CALCITE-7292] Replace "case when true then deptno else
null end" with a non-simplifiable expression
7bebc878de is described below
commit 7bebc878de653ea49d295279efb13db2e8642a55
Author: Thomas Rebele <[email protected]>
AuthorDate: Thu Nov 13 21:14:17 2025 +0100
[CALCITE-7292] Replace "case when true then deptno else null end" with a
non-simplifiable expression
---
.../org/apache/calcite/test/RelOptRulesTest.java | 4 +-
.../apache/calcite/test/SqlToRelConverterTest.java | 14 +++----
.../org/apache/calcite/test/RelOptRulesTest.xml | 24 +++++------
.../apache/calcite/test/SqlToRelConverterTest.xml | 32 +++++++--------
core/src/test/resources/sql/sub-query.iq | 46 +++++++++++-----------
5 files changed, 60 insertions(+), 60 deletions(-)
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index a44486e9d9..fe7b1bfcf9 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -8634,7 +8634,7 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule rule)
{
@Test void testExpandProjectInNullable() {
final String sql = "with e2 as (\n"
- + " select empno, case when true then deptno else null end as
deptno\n"
+ + " select empno, case when deptno > 0 then deptno else null end as
deptno\n"
+ " from sales.emp)\n"
+ "select empno,\n"
+ " deptno in (select deptno from e2 where empno < 20) as d\n"
@@ -8756,7 +8756,7 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule rule)
{
final String sql = "select empno\n"
+ "from sales.emp\n"
+ "where empno\n"
- + " < case deptno in (select case when true then deptno else null
end\n"
+ + " < case deptno in (select case when deptno > 0 then deptno else
null end\n"
+ " from sales.emp where empno < 20)\n"
+ " when true then 10\n"
+ " when false then 20\n"
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index e0477c6501..b332be680e 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2210,7 +2210,7 @@ void checkCorrelatedMapSubQuery(boolean expand) {
// -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
// WHERE clause -- so the translation is more complicated.
final String sql = "select name, deptno in (\n"
- + " select case when true then deptno else null end from emp)\n"
+ + " select case when deptno > 0 then deptno else null end from emp)\n"
+ "from dept";
sql(sql).ok();
}
@@ -2220,7 +2220,7 @@ void checkCorrelatedMapSubQuery(boolean expand) {
// -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
// WHERE clause -- so the translation is more complicated.
final String sql = "select name, deptno in (\n"
- + " select case when true then deptno else null end from emp)\n"
+ + " select case when deptno > 0 then deptno else null end from emp)\n"
+ "from dept";
sql(sql).withExpand(false).ok();
}
@@ -2231,14 +2231,14 @@ void checkCorrelatedMapSubQuery(boolean expand) {
+ "group by deptno\n"
+ "having count(*) > 2\n"
+ "and deptno in (\n"
- + " select case when true then deptno else null end from emp)";
+ + " select case when deptno > 0 then deptno else null end from emp)";
sql(sql).withExpand(false).ok();
}
@Test void testUncorrelatedScalarSubQueryInOrderRex() {
final String sql = "select ename\n"
+ "from emp\n"
- + "order by (select case when true then deptno else null end from emp)
desc,\n"
+ + "order by (select case when deptno > 0 then deptno else null end
from emp) desc,\n"
+ " ename";
sql(sql).withExpand(false).ok();
}
@@ -2247,7 +2247,7 @@ void checkCorrelatedMapSubQuery(boolean expand) {
final String sql = "select sum(sal) as s\n"
+ "from emp\n"
+ "group by deptno\n"
- + "order by (select case when true then deptno else null end from emp)
desc,\n"
+ + "order by (select case when deptno > 0 then deptno else null end
from emp) desc,\n"
+ " count(*)";
sql(sql).withExpand(false).ok();
}
@@ -2263,14 +2263,14 @@ void checkCorrelatedMapSubQuery(boolean expand) {
* an extra NOT. Both queries require 3-valued logic. */
@Test void testNotInUncorrelatedSubQueryInSelect() {
final String sql = "select empno, deptno not in (\n"
- + " select case when true then deptno else null end from dept)\n"
+ + " select case when deptno > 0 then deptno else null end from
dept)\n"
+ "from emp";
sql(sql).ok();
}
@Test void testNotInUncorrelatedSubQueryInSelectRex() {
final String sql = "select empno, deptno not in (\n"
- + " select case when true then deptno else null end from dept)\n"
+ + " select case when deptno > 0 then deptno else null end from
dept)\n"
+ "from emp";
sql(sql).withExpand(false).ok();
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 3c86cc1b2f..7268a959a5 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -4632,7 +4632,7 @@ LogicalProject(EMPNO=[$0])
<![CDATA[select empno
from sales.emp
where empno
- < case deptno in (select case when true then deptno else null end
+ < case deptno in (select case when deptno > 0 then deptno else null end
from sales.emp where empno < 20)
when true then 10
when false then 20
@@ -4643,11 +4643,11 @@ where empno
<![CDATA[
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[<($0, CASE(=(IN($7, {
-LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null:INTEGER)])
+LogicalProject(EXPR$0=[CASE(>($7, 0), CAST($7):INTEGER, null:INTEGER)])
LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
}), true), 10, =(IN($7, {
-LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null:INTEGER)])
+LogicalProject(EXPR$0=[CASE(>($7, 0), CAST($7):INTEGER, null:INTEGER)])
LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
}), false), 20, 30))])
@@ -4663,11 +4663,11 @@ LogicalProject(EMPNO=[$0])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
- LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null:INTEGER)])
+ LogicalProject(EXPR$0=[CASE(>($7, 0), CAST($7):INTEGER,
null:INTEGER)])
LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], i=[LITERAL_AGG(true)])
- LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null:INTEGER)])
+ LogicalProject(EXPR$0=[CASE(>($7, 0), CAST($7):INTEGER,
null:INTEGER)])
LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -5138,7 +5138,7 @@ LogicalProject(EMPNO=[$0], D=[CASE(IS NOT NULL($11),
true, false)])
<TestCase name="testExpandProjectInNullable">
<Resource name="sql">
<![CDATA[with e2 as (
- select empno, case when true then deptno else null end as deptno
+ select empno, case when deptno > 0 then deptno else null end as deptno
from sales.emp)
select empno,
deptno in (select deptno from e2 where empno < 20) as d
@@ -5146,10 +5146,10 @@ from e2]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject(EMPNO=[$0], D=[IN(CASE(true, CAST($7):INTEGER, null:INTEGER), {
+LogicalProject(EMPNO=[$0], D=[IN(CASE(>($7, 0), CAST($7):INTEGER,
null:INTEGER), {
LogicalProject(DEPTNO=[$1])
LogicalFilter(condition=[<($0, 20)])
- LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER,
null:INTEGER)])
+ LogicalProject(EMPNO=[$0], DEPTNO=[CASE(>($7, 0), CAST($7):INTEGER,
null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -5157,19 +5157,19 @@ LogicalProject(DEPTNO=[$1])
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject(EMPNO=[$0], D=[CASE(=($9, 0), false, IS NULL(CASE(true,
CAST($7):INTEGER, null:INTEGER)), null:BOOLEAN, IS NOT NULL($12), true, <($10,
$9), null:BOOLEAN, false)])
- LogicalJoin(condition=[=(CASE(true, CAST($7):INTEGER, null:INTEGER), $11)],
joinType=[left])
+LogicalProject(EMPNO=[$0], D=[CASE(=($9, 0), false, IS NULL(CASE(>($7, 0),
CAST($7):INTEGER, null:INTEGER)), null:BOOLEAN, IS NOT NULL($12), true, <($10,
$9), null:BOOLEAN, false)])
+ LogicalJoin(condition=[=(CASE(>($7, 0), CAST($7):INTEGER, null:INTEGER),
$11)], joinType=[left])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
LogicalProject(DEPTNO=[$1])
LogicalFilter(condition=[<($0, 20)])
- LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER,
null:INTEGER)])
+ LogicalProject(EMPNO=[$0], DEPTNO=[CASE(>($7, 0),
CAST($7):INTEGER, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], i=[LITERAL_AGG(true)])
LogicalProject(DEPTNO=[$1])
LogicalFilter(condition=[<($0, 20)])
- LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER,
null:INTEGER)])
+ LogicalProject(EMPNO=[$0], DEPTNO=[CASE(>($7, 0), CAST($7):INTEGER,
null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 45351cfb6f..382e39292f 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -3242,13 +3242,13 @@ from emp
group by deptno
having count(*) > 2
and deptno in (
- select case when true then deptno else null end from emp)]]>
+ select case when deptno > 0 then deptno else null end from emp)]]>
</Resource>
<Resource name="plan">
<![CDATA[
LogicalProject(S=[$1])
LogicalFilter(condition=[AND(>($2, 2), IN($0, {
-LogicalProject(EXPR$0=[CAST($7):INTEGER])
+LogicalProject(EXPR$0=[CASE(>($7, 0), $7, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
}))])
LogicalAggregate(group=[{0}], S=[SUM($1)], agg#1=[COUNT()])
@@ -3260,7 +3260,7 @@ LogicalProject(EXPR$0=[CAST($7):INTEGER])
<TestCase name="testInUncorrelatedSubQueryInSelect">
<Resource name="sql">
<![CDATA[select name, deptno in (
- select case when true then deptno else null end from emp)
+ select case when deptno > 0 then deptno else null end from emp)
from dept]]>
</Resource>
<Resource name="plan">
@@ -3271,10 +3271,10 @@ LogicalProject(NAME=[$1], EXPR$1=[OR(AND(IS NOT
NULL($6), <>($2, 0)), AND(<($3,
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
- LogicalProject(EXPR$0=[CAST($7):INTEGER], $f1=[true])
+ LogicalProject(EXPR$0=[CASE(>($7, 0), $7, null:INTEGER)], $f1=[true])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
- LogicalProject(EXPR$0=[CAST($7):INTEGER], $f1=[true])
+ LogicalProject(EXPR$0=[CASE(>($7, 0), $7, null:INTEGER)], $f1=[true])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -3282,13 +3282,13 @@ LogicalProject(NAME=[$1], EXPR$1=[OR(AND(IS NOT
NULL($6), <>($2, 0)), AND(<($3,
<TestCase name="testInUncorrelatedSubQueryInSelectRex">
<Resource name="sql">
<![CDATA[select name, deptno in (
- select case when true then deptno else null end from emp)
+ select case when deptno > 0 then deptno else null end from emp)
from dept]]>
</Resource>
<Resource name="plan">
<![CDATA[
LogicalProject(NAME=[$1], EXPR$1=[IN($0, {
-LogicalProject(EXPR$0=[CAST($7):INTEGER])
+LogicalProject(EXPR$0=[CASE(>($7, 0), $7, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -5709,7 +5709,7 @@ LogicalProject(EMPNO=[$0])
<TestCase name="testNotInUncorrelatedSubQueryInSelect">
<Resource name="sql">
<![CDATA[select empno, deptno not in (
- select case when true then deptno else null end from dept)
+ select case when deptno > 0 then deptno else null end from dept)
from emp]]>
</Resource>
<Resource name="plan">
@@ -5720,10 +5720,10 @@ LogicalProject(EMPNO=[$0], EXPR$1=[OR(=($9, 0),
AND(<($10, $9), null, IS NULL($1
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
- LogicalProject(EXPR$0=[CAST($0):INTEGER], $f1=[true])
+ LogicalProject(EXPR$0=[CASE(>($0, 0), $0, null:INTEGER)], $f1=[true])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
- LogicalProject(EXPR$0=[CAST($0):INTEGER], $f1=[true])
+ LogicalProject(EXPR$0=[CASE(>($0, 0), $0, null:INTEGER)], $f1=[true])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
@@ -5849,13 +5849,13 @@ LogicalProject(DEPTNO=[$0])
<TestCase name="testNotInUncorrelatedSubQueryInSelectRex">
<Resource name="sql">
<![CDATA[select empno, deptno not in (
- select case when true then deptno else null end from dept)
+ select case when deptno > 0 then deptno else null end from dept)
from emp]]>
</Resource>
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0], EXPR$1=[NOT(IN($7, {
-LogicalProject(EXPR$0=[CAST($0):INTEGER])
+LogicalProject(EXPR$0=[CASE(>($0, 0), $0, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
}))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -9054,7 +9054,7 @@ LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
<![CDATA[select sum(sal) as s
from emp
group by deptno
-order by (select case when true then deptno else null end from emp) desc,
+order by (select case when deptno > 0 then deptno else null end from emp) desc,
count(*)]]>
</Resource>
<Resource name="plan">
@@ -9062,7 +9062,7 @@ order by (select case when true then deptno else null end
from emp) desc,
LogicalProject(S=[$0])
LogicalSort(sort0=[$1], sort1=[$2], dir0=[DESC], dir1=[ASC])
LogicalProject(S=[$1], EXPR$1=[$SCALAR_QUERY({
-LogicalProject(EXPR$0=[CAST($7):INTEGER])
+LogicalProject(EXPR$0=[CASE(>($7, 0), $7, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})], EXPR$2=[$2])
LogicalAggregate(group=[{0}], S=[SUM($1)], agg#1=[COUNT()])
@@ -9075,7 +9075,7 @@ LogicalProject(EXPR$0=[CAST($7):INTEGER])
<Resource name="sql">
<![CDATA[select ename
from emp
-order by (select case when true then deptno else null end from emp) desc,
+order by (select case when deptno > 0 then deptno else null end from emp) desc,
ename]]>
</Resource>
<Resource name="plan">
@@ -9083,7 +9083,7 @@ order by (select case when true then deptno else null end
from emp) desc,
LogicalProject(ENAME=[$0])
LogicalSort(sort0=[$1], sort1=[$0], dir0=[DESC], dir1=[ASC])
LogicalProject(ENAME=[$1], EXPR$1=[$SCALAR_QUERY({
-LogicalProject(EXPR$0=[CAST($7):INTEGER])
+LogicalProject(EXPR$0=[CASE(>($7, 0), $7, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 015a94f69e..1dd2edf4df 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -1472,7 +1472,7 @@ EnumerableCalc(expr#0..3=[{inputs}],
expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL(
# Test project null IN nullable
select sal,
cast(null as int) IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
SAL | EXPR$1
@@ -1501,7 +1501,7 @@ EnumerableCalc(expr#0..3=[{inputs}],
expr#4=[null:BOOLEAN], expr#5=[IS NOT NULL(
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)], cs=[$t8])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1542,7 +1542,7 @@ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT
NULL($t2)], SAL=[$t1], EXPR$
# Test project literal IN nullable
select sal,
10 IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
SAL | EXPR$1
@@ -1571,7 +1571,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
FALSE($t2)], expr#5=[null:BOOLEA
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], cs=[$t8], $condition=[$t11])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1722,7 +1722,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[null:BOOLEAN
# Test project null NOT IN nullable
select sal,
cast(null as int) NOT IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
SAL | EXPR$1
@@ -1751,7 +1751,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[null:BOOLEAN
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)], cs=[$t8])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1792,7 +1792,7 @@ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS
NULL($t2)], SAL=[$t1], EXPR$1=[$
# Test project literal NOT IN nullable
select sal,
10 NOT IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept)
from "scott".emp;
SAL | EXPR$1
@@ -1821,7 +1821,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[IS FALSE($t2
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], cs=[$t8], $condition=[$t11])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1916,7 +1916,7 @@ EnumerableValues(tuples=[[]])
# Test filter null IN nullable
select sal from "scott".emp
where cast(null as int) IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept);
SAL
-----
@@ -1962,7 +1962,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
# Test filter literal IN nullable
select sal from "scott".emp
where 10 IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept);
SAL
---------
@@ -1988,7 +1988,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER], expr#6=[=($t4, $t5)], cs=[$t3], $condition=[$t6])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[0], expr#7=[>($t5, $t6)],
expr#8=[null:TINYINT], expr#9=[CASE($t7, $t0, $t8)],
expr#10=[CAST($t9):INTEGER], expr#11=[=($t4, $t10)], cs=[$t3],
$condition=[$t11])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2079,7 +2079,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], SAL=[$t1], $conditio
# Test filter null NOT IN nullable
select sal from "scott".emp
where cast(null as int) NOT IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept);
SAL
-----
@@ -2093,7 +2093,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], SAL=[$t1], $conditio
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)], cs=[$t8])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2121,7 +2121,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[NOT($t2)], e
# Test filter literal NOT IN nullable
select sal from "scott".emp
where 10 NOT IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept);
SAL
-----
@@ -2135,7 +2135,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[NOT($t2)], e
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], cs=[$t8], $condition=[$t11])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2253,7 +2253,7 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[RAND()],
expr#6=[CAST($t5):INTEGER
# Test filter null IN nullable correlated
select sal from "scott".emp e
where cast(null as int) IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
SAL
-----
@@ -2287,7 +2287,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
# Test filter literal IN nullable correlated
select sal from "scott".emp e
where 10 IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
SAL
---------
@@ -2301,7 +2301,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableHashJoin(condition=[=($2, $3)], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10],
expr#4=[CAST($t0):INTEGER], expr#5=[=($t3, $t4)], DEPTNO=[$t0],
$condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10],
expr#4=[CAST($t0):INTEGER NOT NULL], expr#5=[0], expr#6=[>($t4, $t5)],
expr#7=[null:TINYINT], expr#8=[CASE($t6, $t0, $t7)],
expr#9=[CAST($t8):INTEGER], expr#10=[=($t3, $t9)], DEPTNO=[$t0],
$condition=[$t10])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2369,7 +2369,7 @@ EnumerableValues(tuples=[[]])
# Test filter null NOT IN nullable correlated
select sal from "scott".emp e
where cast(null as int) NOT IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
SAL
-----
@@ -2415,7 +2415,7 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)],
expr#6=[IS NOT NULL($t3)
# Test filter literal NOT IN nullable correlated
select sal from "scott".emp e
where 10 NOT IN (
- select case when true then deptno else null end
+ select case when deptno > 0 then deptno else null end
from "scott".dept d where e.deptno=d.deptno);
SAL
---------
@@ -2439,9 +2439,9 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)],
expr#6=[IS NOT NULL($t3)
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], cs=[$t1], DEPTNO=[$t0])
- EnumerableWindow(window#0=[window(partition {0} aggs
[FIRST_VALUE($1)])], constants=[[true]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER],
expr#4=[10], expr#5=[=($t3, $t4)], DEPTNO=[$t0], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], cs=[$t2], DEPTNO=[$t0])
+ EnumerableWindow(window#0=[window(partition {0} order by [1 DESC]
range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[FIRST_VALUE($1)])])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], DEPTNO=[$t0], $1=[$t8], $condition=[$t11])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
