You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by vl...@apache.org on 2019/12/31 16:00:38 UTC
[calcite] branch master updated: [CALCITE-3656]
EnumerableNestedLoopJoin cost should account for cost of inner restarts
This is an automated email from the ASF dual-hosted git repository.
vladimirsitnikov pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new be1b04d [CALCITE-3656] EnumerableNestedLoopJoin cost should account for cost of inner restarts
be1b04d is described below
commit be1b04d93dfdd6dd9fd16097b14e14e1162051de
Author: Vladimir Sitnikov <si...@gmail.com>
AuthorDate: Sun Dec 29 23:57:52 2019 +0300
[CALCITE-3656] EnumerableNestedLoopJoin cost should account for cost of inner restarts
---
.../enumerable/EnumerableBatchNestedLoopJoin.java | 2 +-
.../enumerable/EnumerableNestedLoopJoin.java | 39 ++++++++------------
.../java/org/apache/calcite/test/JdbcTest.java | 11 +++---
.../apache/calcite/test/MaterializationTest.java | 38 +++++++++++--------
core/src/test/resources/sql/join.iq | 10 ++---
core/src/test/resources/sql/misc.iq | 12 +++---
core/src/test/resources/sql/some.iq | 4 +-
core/src/test/resources/sql/sub-query.iq | 43 ++++++++++++----------
8 files changed, 81 insertions(+), 78 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableBatchNestedLoopJoin.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableBatchNestedLoopJoin.java
index 382fd11..50112ae 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableBatchNestedLoopJoin.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableBatchNestedLoopJoin.java
@@ -106,7 +106,7 @@ public class EnumerableBatchNestedLoopJoin extends Join implements EnumerableRel
return planner.getCostFactory().makeInfiniteCost();
}
- Double restartCount = mq.getRowCount(getLeft()) / variablesSet.size();
+ double restartCount = mq.getRowCount(getLeft()) / variablesSet.size();
RelOptCost rightCost = planner.getCost(getRight(), mq);
RelOptCost rescanCost =
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java
index 948ebe7..40b56c9 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java
@@ -25,12 +25,10 @@ import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.rel.RelCollationTraitDef;
import org.apache.calcite.rel.RelNode;
-import org.apache.calcite.rel.RelNodes;
import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.metadata.RelMdCollation;
-import org.apache.calcite.rel.metadata.RelMdUtil;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.util.BuiltInMethod;
@@ -86,32 +84,25 @@ public class EnumerableNestedLoopJoin extends Join implements EnumerableRel {
RelMetadataQuery mq) {
double rowCount = mq.getRowCount(this);
- // Joins can be flipped, and for many algorithms, both versions are viable
- // and have the same cost. To make the results stable between versions of
- // the planner, make one of the versions slightly more expensive.
- switch (joinType) {
- case SEMI:
- case ANTI:
- // SEMI and ANTI join cannot be flipped
- break;
- case RIGHT:
- rowCount = RelMdUtil.addEpsilon(rowCount);
- break;
- default:
- if (RelNodes.COMPARATOR.compare(left, right) > 0) {
- rowCount = RelMdUtil.addEpsilon(rowCount);
- }
- }
-
final double rightRowCount = right.estimateRowCount(mq);
final double leftRowCount = left.estimateRowCount(mq);
- if (Double.isInfinite(leftRowCount)) {
- rowCount = leftRowCount;
+ if (Double.isInfinite(leftRowCount) || Double.isInfinite(rightRowCount)) {
+ return planner.getCostFactory().makeInfiniteCost();
}
- if (Double.isInfinite(rightRowCount)) {
- rowCount = rightRowCount;
+ RelOptCost rightCost = planner.getCost(right, mq);
+ if (rightCost.isInfinite()) {
+ return rightCost;
}
- return planner.getCostFactory().makeCost(rowCount, 0, 0);
+ // Note: -1 here is because the total cost of the plan would include costs of left and right
+ // inputs anyway.
+ RelOptCost rescanCost = rightCost.multiplyBy(Math.max(1.0, leftRowCount - 1));
+ // Note: even if filter would reduce the resulting set to 1 row, we would still have to
+ // restart the inner relation several times.
+ // Inner relation is restarted multiple times, so an epsilon is added
+ // to represent startup cost.
+ return planner.getCostFactory()
+ .makeCost(rowCount + leftRowCount, 0, 0)
+ .plus(rescanCost);
}
public Result implement(EnumerableRelImplementor implementor, Prefer pref) {
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index badfe8e..f066c88 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2703,11 +2703,12 @@ public class JdbcTest {
.query("select empno, desc from sales.emps,\n"
+ " (SELECT * FROM (VALUES (10, 'SameName')) AS t (id, desc)) as sn\n"
+ "where emps.deptno = sn.id and sn.desc = 'SameName' group by empno, desc")
- .explainContains("EnumerableAggregate(group=[{0, 3}])\n"
- + " EnumerableNestedLoopJoin(condition=[=(CAST($1):INTEGER NOT NULL, $2)], joinType=[inner])\n"
- + " EnumerableTableScan(table=[[SALES, EMPS]])\n"
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=['SameName'], expr#3=[=($t1, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
- + " EnumerableValues(tuples=[[{ 10, 'SameName' }]])\n")
+ .explainContains("EnumerableCalc(expr#0..1=[{inputs}], EMPNO=[$t1], DESC=[$t0])\n"
+ + " EnumerableAggregate(group=[{1, 2}])\n"
+ + " EnumerableNestedLoopJoin(condition=[=(CAST($3):INTEGER NOT NULL, $0)], joinType=[inner])\n"
+ + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=['SameName'], expr#3=[=($t1, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
+ + " EnumerableValues(tuples=[[{ 10, 'SameName' }]])\n"
+ + " EnumerableTableScan(table=[[SALES, EMPS]])\n")
.returns("EMPNO=1; DESC=SameName\n");
}
diff --git a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java
index 7aa0a8c..38e223f 100644
--- a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java
+++ b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java
@@ -85,7 +85,6 @@ import static org.junit.jupiter.api.Assertions.assertTrue;
* query and one or more materializations (what Oracle calls materialized views)
* and checks that the materialization is used.
*/
-@Tag("slow")
public class MaterializationTest {
private static final Consumer<ResultSet> CONTAINS_M0 =
CalciteAssert.checkResultContains(
@@ -427,6 +426,7 @@ public class MaterializationTest {
}
}
+ @Tag("slow")
@Test public void testFilterQueryOnFilterView() {
checkMaterialize(
"select \"deptno\", \"empid\", \"name\" from \"emps\" where \"deptno\" = 10",
@@ -1074,6 +1074,7 @@ public class MaterializationTest {
checkNoMaterialize(mv, query, HR_FKUK_MODEL, true);
}
+ @Tag("slow")
@Test public void testSwapJoin() {
checkMaterialize(
"select count(*) as c from \"foodmart\".\"sales_fact_1997\" as s join \"foodmart\".\"time_by_day\" as t on s.\"time_id\" = t.\"time_id\"",
@@ -1952,6 +1953,7 @@ public class MaterializationTest {
"expr#13=[OR($t10, $t12)], expr#14=[AND($t6, $t8, $t13)]"));
}
+ @Tag("slow")
@Test public void testJoinAggregateMaterializationNoAggregateFuncs10() {
checkMaterialize(
"select \"depts\".\"name\", \"dependents\".\"name\" as \"name2\", "
@@ -2089,11 +2091,10 @@ public class MaterializationTest {
+ "group by \"dependents\".\"empid\"",
HR_FKUK_MODEL,
CalciteAssert.checkResultContains(
- "EnumerableAggregate(group=[{4}], S=[$SUM0($6)])\n"
- + " EnumerableCalc(expr#0..6=[{inputs}], expr#7=[=($t5, $t0)], proj#0..6=[{exprs}], $condition=[$t7])\n"
- + " EnumerableNestedLoopJoin(condition=[true], joinType=[inner])\n"
- + " EnumerableTableScan(table=[[hr, depts]])\n"
- + " EnumerableTableScan(table=[[hr, m0]])"));
+ "EnumerableAggregate(group=[{0}], S=[$SUM0($2)])\n"
+ + " EnumerableHashJoin(condition=[=($1, $3)], joinType=[inner])\n"
+ + " EnumerableTableScan(table=[[hr, m0]])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])"));
}
@Test public void testJoinAggregateMaterializationAggregateFuncs8() {
@@ -2109,11 +2110,10 @@ public class MaterializationTest {
+ "group by \"depts\".\"name\"",
HR_FKUK_MODEL,
CalciteAssert.checkResultContains(
- "EnumerableAggregate(group=[{1}], S=[$SUM0($6)])\n"
- + " EnumerableCalc(expr#0..6=[{inputs}], expr#7=[=($t5, $t0)], proj#0..6=[{exprs}], $condition=[$t7])\n"
- + " EnumerableNestedLoopJoin(condition=[true], joinType=[inner])\n"
- + " EnumerableTableScan(table=[[hr, depts]])\n"
- + " EnumerableTableScan(table=[[hr, m0]])"));
+ "EnumerableAggregate(group=[{4}], S=[$SUM0($2)])\n"
+ + " EnumerableHashJoin(condition=[=($1, $3)], joinType=[inner])\n"
+ + " EnumerableTableScan(table=[[hr, m0]])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])"));
}
@Test public void testJoinAggregateMaterializationAggregateFuncs9() {
@@ -2145,6 +2145,7 @@ public class MaterializationTest {
HR_FKUK_MODEL);
}
+ @Tag("slow")
@Test public void testJoinAggregateMaterializationAggregateFuncs11() {
checkMaterialize(
"select \"depts\".\"deptno\", \"dependents\".\"empid\", count(\"emps\".\"salary\") as s\n"
@@ -2291,10 +2292,12 @@ public class MaterializationTest {
+ "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")",
HR_FKUK_MODEL,
CalciteAssert.checkResultContains(
- "EnumerableCalc(expr#0..2=[{inputs}], empid=[$t0])\n"
- + " EnumerableNestedLoopJoin(condition=[=(CAST($1):VARCHAR, CAST($2):VARCHAR)], joinType=[inner])\n"
- + " EnumerableTableScan(table=[[hr, dependents]])\n"
- + " EnumerableTableScan(table=[[hr, m0]])"));
+ "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t2])\n"
+ + " EnumerableHashJoin(condition=[=($1, $4)], joinType=[inner])\n"
+ + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}])\n"
+ + " EnumerableTableScan(table=[[hr, m0]])\n"
+ + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}])\n"
+ + " EnumerableTableScan(table=[[hr, dependents]])"));
}
@Test public void testJoinMaterialization9() {
@@ -2311,6 +2314,7 @@ public class MaterializationTest {
CONTAINS_M0);
}
+ @Tag("slow")
@Test public void testJoinMaterialization10() {
checkMaterialize(
"select \"depts\".\"deptno\", \"dependents\".\"empid\"\n"
@@ -2341,6 +2345,7 @@ public class MaterializationTest {
"PLAN=EnumerableTableScan(table=[[hr, m0]])"));
}
+ @Tag("slow")
@Test public void testJoinMaterialization12() {
checkMaterialize(
"select \"empid\", \"emps\".\"name\", \"emps\".\"deptno\", \"depts\".\"name\"\n"
@@ -2408,6 +2413,7 @@ public class MaterializationTest {
"PLAN=EnumerableTableScan(table=[[hr, m0]])"));
}
+ @Tag("slow")
@Test public void testJoinMaterializationUKFK5() {
checkMaterialize(
"select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n"
@@ -2423,6 +2429,7 @@ public class MaterializationTest {
+ " EnumerableTableScan(table=[[hr, m0]])"));
}
+ @Tag("slow")
@Test public void testJoinMaterializationUKFK6() {
checkMaterialize(
"select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n"
@@ -2465,6 +2472,7 @@ public class MaterializationTest {
HR_FKUK_MODEL);
}
+ @Tag("slow")
@Test public void testJoinMaterializationUKFK9() {
checkMaterialize(
"select * from \"emps\"\n"
diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
index c9fd61c..5918542 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -228,12 +228,12 @@ where e.deptno + 10 = d.deptno * 2;
(9 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t1], DEPTNO0=[$t2])
- EnumerableNestedLoopJoin(condition=[=(+($1, 10), *($2, 2))], joinType=[inner])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
+EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t3], DEPTNO0=[$t0])
+ EnumerableHashJoin(condition=[=($1, $4)], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], expr#4=[*($t0, $t3)], DEPTNO=[$t0], $f1=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], EMPNO=[$t0], DEPTNO=[$t7], $f2=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
### [CALCITE-801] NullPointerException using USING on table alias with column aliases
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq
index 4e63283..41372dd 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -335,14 +335,14 @@ where exists (select 1 from "hr"."emps");
(3 rows)
!ok
-EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t0])
+EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t1])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
- EnumerableTableScan(table=[[hr, depts]])
EnumerableCalc(expr#0=[{inputs}], expr#1=[IS NOT NULL($t0)], $f0=[$t0], $condition=[$t1])
EnumerableAggregate(group=[{}], agg#0=[MIN($0)])
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5])
EnumerableTableScan(table=[[hr, emps]])
+ EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+ EnumerableTableScan(table=[[hr, depts]])
!plan
# Un-correlated NOT EXISTS
@@ -374,14 +374,14 @@ where exists (select 1 from "hr"."emps" where "empid" < 0);
(0 rows)
!ok
-EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t0])
+EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t1])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
- EnumerableTableScan(table=[[hr, depts]])
EnumerableCalc(expr#0=[{inputs}], expr#1=[IS NOT NULL($t0)], $f0=[$t0], $condition=[$t1])
EnumerableAggregate(group=[{}], agg#0=[MIN($0)])
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[0], expr#7=[<($t0, $t6)], $f0=[$t5], $condition=[$t7])
EnumerableTableScan(table=[[hr, emps]])
+ EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+ EnumerableTableScan(table=[[hr, depts]])
!plan
# Un-correlated NOT EXISTS (table empty)
diff --git a/core/src/test/resources/sql/some.iq b/core/src/test/resources/sql/some.iq
index 8ed4cf3..e7727b9 100644
--- a/core/src/test/resources/sql/some.iq
+++ b/core/src/test/resources/sql/some.iq
@@ -133,11 +133,11 @@ from "scott".emp;
(14 rows)
!ok
-EnumerableCalc(expr#0..10=[{inputs}], expr#11=[0], expr#12=[=($t9, $t11)], expr#13=[>($t9, $t10)], expr#14=[null:BOOLEAN], expr#15=[<>($t9, $t11)], expr#16=[<=($t5, $t8)], expr#17=[IS NOT TRUE($t16)], expr#18=[AND($t13, $t14, $t15, $t17)], expr#19=[>($t5, $t8)], expr#20=[<=($t9, $t10)], expr#21=[AND($t19, $t15, $t17, $t20)], expr#22=[OR($t12, $t18, $t21)], proj#0..7=[{exprs}], X=[$t22])
+EnumerableCalc(expr#0..10=[{inputs}], expr#11=[0], expr#12=[=($t1, $t11)], expr#13=[>($t1, $t2)], expr#14=[null:BOOLEAN], expr#15=[<>($t1, $t11)], expr#16=[<=($t8, $t0)], expr#17=[IS NOT TRUE($t16)], expr#18=[AND($t13, $t14, $t15, $t17)], expr#19=[>($t8, $t0)], expr#20=[<=($t1, $t2)], expr#21=[AND($t19, $t15, $t17, $t20)], expr#22=[OR($t12, $t18, $t21)], EMPNO=[$t3], ENAME=[$t4], JOB=[$t5], MGR=[$t6], HIREDATE=[$t7], SAL=[$t8], COMM=[$t9], DEPTNO=[$t10], X=[$t22])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], m=[MAX($6)], c=[COUNT()], d=[COUNT($6)])
EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# NOT SOME; left side NOT NULL, right side nullable; converse of previous query.
diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq
index a7d7c77..9bd8a7f 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -34,18 +34,19 @@ where t1.x not in (select t2.x from t2);
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t1, $t5)], expr#7=[IS NULL($t4)], expr#8=[>=($t2, $t1)], expr#9=[IS NOT NULL($t0)], expr#10=[AND($t7, $t8, $t9)], expr#11=[OR($t6, $t10)], X=[$t0], $condition=[$t11])
EnumerableHashJoin(condition=[=($0, $3)], joinType=[left])
- EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t2], c=[$t0], ck=[$t1])
+ EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1])
+ EnumerableValues(tuples=[[{ 0 }]])
EnumerableUnion(all=[true])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1])
+ EnumerableValues(tuples=[[{ 0 }]])
EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1])
EnumerableValues(tuples=[[{ 0 }]])
EnumerableAggregate(group=[{0, 1}])
@@ -1388,13 +1389,13 @@ select sal from "scott".emp
(14 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
+EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- 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=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# Test filter literal IN nullable
@@ -1421,13 +1422,13 @@ select sal from "scott".emp
(14 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
+EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t2])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- 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=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# Test filter null NOT IN null non-correlated
@@ -2055,11 +2056,13 @@ EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1:BIGINT], expr#4=[IS NOT NULL($t1)], DNAME=[$t1], $f1=[$t3], $f2=[$t3], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableNestedLoopJoin(condition=[=(+($3, 100), $0)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableAggregate(group=[{5}])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4], SAL=[$t0])
+ EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)], SAL=[$t0], $f1=[$t2])
+ EnumerableAggregate(group=[{5}])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Correlated ANY sub-query