This is an automated email from the ASF dual-hosted git repository.
libenchao 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 7de37aefaa [CALCITE-5201] Improve SemiJoinRule to match Join's right
input which is unique for Join keys
7de37aefaa is described below
commit 7de37aefaaa72959a0145053319fae2956fbb9f1
Author: Benchao Li <[email protected]>
AuthorDate: Sun Jul 3 21:19:16 2022 +0800
[CALCITE-5201] Improve SemiJoinRule to match Join's right input which is
unique for Join keys
This closes #2848
---
.../java/org/apache/calcite/plan/RelOptRules.java | 1 +
.../org/apache/calcite/rel/rules/CoreRules.java | 6 ++
.../org/apache/calcite/rel/rules/SemiJoinRule.java | 84 +++++++++++++++++++++-
.../java/org/apache/calcite/tools/Programs.java | 1 +
.../java/org/apache/calcite/test/JdbcTest.java | 2 +
.../org/apache/calcite/test/RelOptRulesTest.java | 22 ++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 41 +++++++++++
core/src/test/resources/sql/agg.iq | 56 +++++++--------
core/src/test/resources/sql/misc.iq | 30 ++++----
core/src/test/resources/sql/sub-query.iq | 64 ++++++++---------
10 files changed, 225 insertions(+), 82 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
index aa56f7857a..760cd0649e 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
@@ -117,6 +117,7 @@ public class RelOptRules {
AbstractConverter.ExpandConversionRule.INSTANCE,
CoreRules.JOIN_COMMUTE,
CoreRules.PROJECT_TO_SEMI_JOIN,
+ CoreRules.JOIN_ON_UNIQUE_TO_SEMI_JOIN,
CoreRules.JOIN_TO_SEMI_JOIN,
CoreRules.AGGREGATE_REMOVE,
CoreRules.UNION_TO_DISTINCT,
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index 92165d992c..074bb4f635 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -584,6 +584,12 @@ public class CoreRules {
ReduceExpressionsRule.JoinReduceExpressionsRule.JoinReduceExpressionsRuleConfig
.DEFAULT.toRule();
+ /** Rule that creates a {@link Join#isSemiJoin semi-join} from a
+ * {@link Join}, and it's right keys are unique in its right input.
+ */
+ public static final SemiJoinRule.JoinOnUniqueToSemiJoinRule
JOIN_ON_UNIQUE_TO_SEMI_JOIN =
+
SemiJoinRule.JoinOnUniqueToSemiJoinRule.JoinOnUniqueToSemiJoinRuleConfig.DEFAULT.toRule();
+
/** Rule that converts a {@link LogicalJoin}
* into a {@link LogicalCorrelate}. */
public static final JoinToCorrelateRule JOIN_TO_CORRELATE =
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SemiJoinRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SemiJoinRule.java
index fc84206dcb..ef2623a710 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SemiJoinRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SemiJoinRule.java
@@ -26,6 +26,7 @@ import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinInfo;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.tools.RelBuilder;
@@ -42,7 +43,9 @@ import java.util.List;
/**
* Planner rule that creates a {@code SemiJoin} from a
* {@link org.apache.calcite.rel.core.Join} on top of a
- * {@link org.apache.calcite.rel.logical.LogicalAggregate}.
+ * {@link org.apache.calcite.rel.logical.LogicalAggregate} or
+ * on a {@link org.apache.calcite.rel.RelNode} which is
+ * unique for join's right keys.
*/
public abstract class SemiJoinRule
extends RelRule<SemiJoinRule.Config>
@@ -96,7 +99,6 @@ public abstract class SemiJoinRule
final RelBuilder relBuilder = call.builder();
relBuilder.push(left);
switch (join.getJoinType()) {
- case SEMI:
case INNER:
final List<Integer> newRightKeyBuilder = new ArrayList<>();
final List<Integer> aggregateKeys = aggregate.getGroupSet().asList();
@@ -232,6 +234,84 @@ public abstract class SemiJoinRule
}
}
+ /**
+ * SemiJoinRule that matches a Project on top of a Join with a RelNode
+ * which is unique for Join's right keys.
+ *
+ * @see CoreRules#JOIN_ON_UNIQUE_TO_SEMI_JOIN */
+ public static class JoinOnUniqueToSemiJoinRule extends SemiJoinRule {
+
+ /** Creates a JoinOnUniqueToSemiJoinRule. */
+ protected JoinOnUniqueToSemiJoinRule(JoinOnUniqueToSemiJoinRuleConfig
config) {
+ super(config);
+ }
+
+ @Override public boolean matches(RelOptRuleCall call) {
+ final Project project = call.rel(0);
+ final Join join = call.rel(1);
+ final RelNode left = call.rel(2);
+
+ final ImmutableBitSet bits =
+ RelOptUtil.InputFinder.bits(project.getProjects(), null);
+ final ImmutableBitSet rightBits =
+ ImmutableBitSet.range(left.getRowType().getFieldCount(),
+ join.getRowType().getFieldCount());
+ return !bits.intersects(rightBits);
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Project project = call.rel(0);
+ final Join join = call.rel(1);
+ final RelNode left = call.rel(2);
+ final RelNode right = call.rel(3);
+
+ final JoinInfo joinInfo = join.analyzeCondition();
+ final RelOptCluster cluster = join.getCluster();
+ final RelMetadataQuery mq = cluster.getMetadataQuery();
+ final Boolean unique = mq.areColumnsUnique(right, joinInfo.rightSet());
+ if (unique != null && unique) {
+ final RelBuilder builder = call.builder();
+ switch (join.getJoinType()) {
+ case INNER:
+ builder.push(left);
+ builder.push(right);
+ builder.join(JoinRelType.SEMI, join.getCondition());
+ break;
+ case LEFT:
+ builder.push(left);
+ break;
+ default:
+ throw new AssertionError(join.getJoinType());
+ }
+ builder.project(project.getProjects());
+ call.transformTo(builder.build());
+ }
+ }
+
+ /**
+ * Rule configuration.
+ */
+ @Value.Immutable
+ public interface JoinOnUniqueToSemiJoinRuleConfig extends
SemiJoinRule.Config {
+ JoinOnUniqueToSemiJoinRuleConfig DEFAULT =
ImmutableJoinOnUniqueToSemiJoinRuleConfig.of()
+ .withDescription("SemiJoinRule:unique")
+ .withOperandSupplier(b ->
+ b.operand(Project.class).oneInput(
+ b2 ->
b2.operand(Join.class).predicate(SemiJoinRule::isJoinTypeSupported).inputs(
+ b3 -> b3.operand(RelNode.class).anyInputs(),
+ b4 -> b4.operand(RelNode.class)
+ // If RHS is Aggregate, it will be covered by
ProjectToSemiJoinRule
+ .predicate(n -> !(n instanceof Aggregate))
+ .anyInputs()
+ )))
+ .as(JoinOnUniqueToSemiJoinRuleConfig.class);
+
+ @Override default JoinOnUniqueToSemiJoinRule toRule() {
+ return new JoinOnUniqueToSemiJoinRule(this);
+ }
+ }
+ }
+
/**
* Rule configuration.
*/
diff --git a/core/src/main/java/org/apache/calcite/tools/Programs.java
b/core/src/main/java/org/apache/calcite/tools/Programs.java
index 63bc8962f2..b23083981b 100644
--- a/core/src/main/java/org/apache/calcite/tools/Programs.java
+++ b/core/src/main/java/org/apache/calcite/tools/Programs.java
@@ -89,6 +89,7 @@ public class Programs {
EnumerableRules.ENUMERABLE_WINDOW_RULE,
EnumerableRules.ENUMERABLE_MATCH_RULE,
CoreRules.PROJECT_TO_SEMI_JOIN,
+ CoreRules.JOIN_ON_UNIQUE_TO_SEMI_JOIN,
CoreRules.JOIN_TO_SEMI_JOIN,
CoreRules.MATCH,
CalciteSystemProperty.COMMUTE.value()
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 0725666ef5..ff73459140 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -5432,6 +5432,8 @@ public class JdbcTest {
* Query with parameterized LIMIT and correlated sub-query throws
AssertionError "not a
* literal"</a>.
*/
+ @Disabled("[CALCITE-5229] JdbcTest#testDynamicParameterInLimitOffset"
+ + " throws IllegalArgumentException")
@Test void testDynamicParameterInLimitOffset() {
CalciteAssert.hr()
.query("SELECT * FROM \"hr\".\"emps\" AS a "
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 b305f8c930..6cb5d81e6b 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1145,6 +1145,28 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ @Test void testSemiJoinRuleDoNotMatchAggregate() {
+ final String sql = "select *\n"
+ + "from emp\n"
+ + "where exists(select * from dept where emp.deptno = dept.deptno)";
+ sql(sql)
+ .withDecorrelate(true)
+ .withPreRule(CoreRules.PROJECT_MERGE)
+ .withRule(CoreRules.JOIN_ON_UNIQUE_TO_SEMI_JOIN)
+ .checkUnchanged();
+ }
+
+ @Test void testSemiJoinRuleWithJoinOnUniqueInput() {
+ final String sql = "select *\n"
+ + "from emp\n"
+ + "where exists(select * from dept where emp.deptno = dept.deptno)";
+ sql(sql)
+ .withDecorrelate(true)
+ .withTrim(true)
+ .withRule(CoreRules.JOIN_ON_UNIQUE_TO_SEMI_JOIN)
+ .check();
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-1495";>[CALCITE-1495]
* SemiJoinRule should not apply to RIGHT and FULL JOIN</a>. */
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 87e5489f57..a3ad0a8f6f 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -12040,6 +12040,23 @@ LogicalJoin(condition=[=($0, $2)], joinType=[semi])
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[>($5, 100)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSemiJoinRuleDoNotMatchAggregate">
+ <Resource name="sql">
+ <![CDATA[select *
+from emp
+where exists(select * from dept where emp.deptno = dept.deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -12149,6 +12166,30 @@ LogicalProject(DNAME=[$1])
LogicalTableScan(table=[[scott, DEPT]])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSemiJoinRuleWithJoinOnUniqueInput">
+ <Resource name="sql">
+ <![CDATA[select *
+from emp
+where exists(select * from dept where emp.deptno = dept.deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalJoin(condition=[=($7, $9)], joinType=[semi])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index f8b56a8325..ec9b01987e 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -1805,12 +1805,12 @@ from "scott".emp join "scott".dept using (deptno);
(1 row)
!ok
-EnumerableAggregate(group=[{}], EXPR$0=[SUM($2)])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableAggregate(group=[{}], EXPR$0=[SUM($1)])
+ 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}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Push sum; no aggregate needed after join
@@ -1851,12 +1851,12 @@ group by emp.deptno;
!ok
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])
- EnumerableAggregate(group=[{3}], EXPR$0=[SUM($2)])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{2}], EXPR$0=[SUM($1)])
+ 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}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Push min; Join-Aggregate is optimized to SemiJoin
@@ -1874,12 +1874,12 @@ group by emp.deptno;
!ok
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])
- EnumerableAggregate(group=[{3}], EXPR$0=[MIN($2)])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{2}], EXPR$0=[MIN($1)])
+ 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}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Push sum and count
@@ -1893,12 +1893,12 @@ from "scott".emp join "scott".dept using (deptno);
(1 row)
!ok
-EnumerableAggregate(group=[{}], C=[COUNT()], S=[SUM($2)])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableAggregate(group=[{}], C=[COUNT()], S=[SUM($1)])
+ 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}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Push sum and count, group by join key
@@ -1916,12 +1916,12 @@ from "scott".emp join "scott".dept using (deptno) group
by emp.deptno;
!ok
# No aggregate on top, because output of join is unique
EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2])
- EnumerableAggregate(group=[{3}], C=[COUNT()], S=[SUM($2)])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{2}], C=[COUNT()], S=[SUM($1)])
+ 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}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Push sum and count, group by join key plus another column
@@ -1968,12 +1968,12 @@ from "scott".emp join "scott".dept using (deptno) group
by emp.job;
!ok
EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2])
- EnumerableAggregate(group=[{2}], C=[COUNT()], S=[SUM($3)])
- EnumerableHashJoin(condition=[=($0, $4)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{1}], C=[COUNT()], S=[SUM($2)])
+ EnumerableHashJoin(condition=[=($3, $4)], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Push count and sum, group by superset of join key
@@ -2027,12 +2027,12 @@ from "scott".emp join "scott".dept using (deptno) group
by emp.sal;
!ok
EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2])
- EnumerableAggregate(group=[{2}], C=[COUNT()], S=[SUM($2)])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{1}], C=[COUNT()], S=[SUM($1)])
+ 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}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Push sum, self-join, returning one row with a null value
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 6800f6ea06..4b92f2f6f3 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -361,14 +361,13 @@ where exists (select 1 from "hr"."emps");
(3 rows)
!ok
-EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t0])
- 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]])
+EnumerableNestedLoopJoin(condition=[true], joinType=[semi])
+ 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]])
!plan
# Un-correlated NOT EXISTS
@@ -400,14 +399,13 @@ where exists (select 1 from "hr"."emps" where "empid" <
0);
(0 rows)
!ok
-EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t0])
- 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]])
+EnumerableNestedLoopJoin(condition=[true], joinType=[semi])
+ 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]])
!plan
# Un-correlated NOT EXISTS (table empty)
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 35f8e05c76..0664f6b78c 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -448,11 +448,10 @@ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
# Uncorrelated
with t (a, b) as (select * from (values (60, 'b')))
select * from t where a in (select deptno from "scott".dept);
-EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
- EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])
- EnumerableValues(tuples=[[{ 60, 'b' }]])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableHashJoin(condition=[=($0, $2)], joinType=[semi])
+ EnumerableValues(tuples=[[{ 60, 'b' }]])
+ EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
+---+---+
| A | B |
@@ -583,12 +582,12 @@ where empno IN (
(0 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], SAL=[$t3])
- EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $2))], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..1=[{exprs}], $condition=[$t3])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
+ EnumerableHashJoin(condition=[AND(=($2, $4), =($0, $3))], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t2):VARCHAR(14)],
EMPNO=[$t0], SAL=[$t5], JOB0=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..1=[{exprs}], $condition=[$t3])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# As above, but for EXISTS
@@ -606,11 +605,10 @@ where exists (
(1 row)
!ok
-EnumerableCalc(expr#0..3=[{inputs}], DEPTNO=[$t1], DNAME=[$t2], LOC=[$t3])
- EnumerableHashJoin(condition=[=($0, $1)], joinType=[inner])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=['SMITH':VARCHAR(10)],
expr#9=[=($t1, $t8)], expr#10=[IS NOT NULL($t7)], expr#11=[AND($t9, $t10)],
DEPTNO=[$t7], $condition=[$t11])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableHashJoin(condition=[=($0, $3)], joinType=[semi])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=['SMITH':VARCHAR(10)],
expr#9=[=($t1, $t8)], expr#10=[IS NOT NULL($t7)], expr#11=[AND($t9, $t10)],
DEPTNO=[$t7], $condition=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# [DRILL-5644]
@@ -1696,12 +1694,12 @@ select sal from "scott".emp e
(3 rows)
!ok
-EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)],
DEPTNO=[$t0], $condition=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
+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=[=($t3, $t0)],
DEPTNO=[$t0], $condition=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal IN nullable correlated
@@ -1717,12 +1715,12 @@ select sal from "scott".emp e
(3 rows)
!ok
-EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)],
DEPTNO=[$t0], $condition=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
+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=[=($t3, $t0)],
DEPTNO=[$t0], $condition=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null NOT IN null correlated
@@ -1880,13 +1878,8 @@ select sal from "scott".emp e
(14 rows)
!ok
-EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t1])
- EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
- EnumerableSort(sort0=[$2], dir0=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3048,13 +3041,12 @@ where not unique (
!ok
-EnumerableCalc(expr#0..3=[{inputs}], DEPTNO=[$t1], DNAME=[$t2], LOC=[$t3])
- EnumerableHashJoin(condition=[=($0, $1)], joinType=[inner])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t1, $t2)],
DEPTNO=[$t0], $condition=[$t3])
- EnumerableAggregate(group=[{7}], c=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableHashJoin(condition=[=($0, $3)], joinType=[semi])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t1, $t2)],
DEPTNO=[$t0], $condition=[$t3])
+ EnumerableAggregate(group=[{7}], c=[COUNT()])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# Previous, as scalar sub-query.
