This is an automated email from the ASF dual-hosted git repository.
asolimando 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 25daf51444 [CALCITE-7125] Impossible to get a plan with partial
aggregate push-down via IntersectToDistinctRule
25daf51444 is described below
commit 25daf51444b6de23c6bb72c319ad5b9568b83e22
Author: Alessandro Solimando <[email protected]>
AuthorDate: Tue Jul 22 16:14:05 2025 +0200
[CALCITE-7125] Impossible to get a plan with partial aggregate push-down
via IntersectToDistinctRule
---
.../org/apache/calcite/rel/rules/CoreRules.java | 12 +-
.../calcite/rel/rules/IntersectToDistinctRule.java | 143 +++++++++++++++++----
.../java/org/apache/calcite/test/JdbcTest.java | 27 ----
.../org/apache/calcite/test/RelOptRulesTest.java | 33 +++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 110 ++++++++++++++--
core/src/test/resources/sql/set-op.iq | 68 ++++++++++
6 files changed, 331 insertions(+), 62 deletions(-)
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 1e222b639e..462f227f26 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
@@ -375,12 +375,18 @@ private CoreRules() {}
public static final IntersectReorderRule INTERSECT_REORDER =
IntersectReorderRule.Config.DEFAULT.toRule();
- /** Rule that translates a distinct
- * {@link Intersect} into a group of operators
- * composed of {@link Union}, {@link Aggregate}, etc. */
+ /** Rule that translates a distinct {@link Intersect} into a group of
operators
+ * composed of {@link Union}, {@link Aggregate}, etc. The rule also applies a
+ * partial aggregation pushdown into the union branches. */
public static final IntersectToDistinctRule INTERSECT_TO_DISTINCT =
IntersectToDistinctRule.Config.DEFAULT.toRule();
+ /** As {@link #INTERSECT_TO_DISTINCT} but not applying (partial) aggregate
pushdown
+ * into the union branches (behaviour introduced in CALCITE-6893). */
+ @RuleConfig(value = "NO_AGGREGATE_PUSHDOWN")
+ public static final IntersectToDistinctRule
INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN =
+ IntersectToDistinctRule.Config.NO_AGGREGATE_PUSHDOWN.toRule();
+
/** Rule that translates a {@link Intersect}
* into a {@link Exists} subquery. */
public static final IntersectToExistsRule INTERSECT_TO_EXISTS =
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/IntersectToDistinctRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/IntersectToDistinctRule.java
index ebd5ac8f58..a8d2713e09 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/IntersectToDistinctRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/IntersectToDistinctRule.java
@@ -16,18 +16,23 @@
*/
package org.apache.calcite.rel.rules;
+import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Intersect;
import org.apache.calcite.rel.logical.LogicalIntersect;
+import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RelBuilder.AggCall;
import org.apache.calcite.tools.RelBuilderFactory;
import org.apache.calcite.util.ImmutableBitSet;
+import org.apache.calcite.util.Util;
import org.immutables.value.Value;
+import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
@@ -41,30 +46,12 @@
* {@link org.apache.calcite.rel.core.Union},
* {@link org.apache.calcite.rel.core.Aggregate}, etc.
*
- * <h2>Example</h2>
- *
- * <p>Original query:
- * <pre>{@code
- * SELECT job FROM "scott".emp WHERE deptno = 10
- * INTERSECT
- * SELECT job FROM "scott".emp WHERE deptno = 20
- * }</pre>
- *
- * <p>Query after conversion:
- * <pre>{@code
- * SELECT job
- * FROM (
- * SELECT job, 0 AS i FROM "scott".emp WHERE deptno = 10
- * UNION ALL
- * SELECT job, 1 AS i FROM "scott".emp WHERE deptno = 20
- * )
- * GROUP BY job
- * HAVING COUNT(*) FILTER (WHERE i = 0) > 0
- * AND COUNT(*) FILTER (WHERE i = 1) > 0
- * }</pre>
+ * <p>The rule has a configuration option to control whether it should also
perform
+ * a (partial) aggregation pushdown in the union branches (default behavior).
*
* @see org.apache.calcite.rel.rules.UnionToDistinctRule
* @see CoreRules#INTERSECT_TO_DISTINCT
+ * @see CoreRules#INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN
*/
@Value.Enclosing
public class IntersectToDistinctRule
@@ -85,8 +72,38 @@ public IntersectToDistinctRule(Class<? extends Intersect>
intersectClass,
}
//~ Methods ----------------------------------------------------------------
-
@Override public void onMatch(RelOptRuleCall call) {
+ if (config.isAggregatePushdown()) {
+ onMatchAggregatePushdown(call);
+ } else {
+ onMatchAggregateOnUnion(call);
+ }
+ }
+
+ /**
+ * Variant not performing a partial aggregation pushdown.
+ *
+ * <p>Original query:
+ * <pre>{@code
+ * SELECT job FROM "scott".emp WHERE deptno = 10
+ * INTERSECT
+ * SELECT job FROM "scott".emp WHERE deptno = 20
+ * }</pre>
+ *
+ * <p>Query after conversion:
+ * <pre>{@code
+ * SELECT job
+ * FROM (
+ * SELECT job, 0 AS i FROM "scott".emp WHERE deptno = 10
+ * UNION ALL
+ * SELECT job, 1 AS i FROM "scott".emp WHERE deptno = 20
+ * )
+ * GROUP BY job
+ * HAVING COUNT(*) FILTER (WHERE i = 0) > 0
+ * AND COUNT(*) FILTER (WHERE i = 1) > 0
+ * }</pre>
+ */
+ public void onMatchAggregateOnUnion(RelOptRuleCall call) {
final Intersect intersect = call.rel(0);
if (intersect.all) {
return; // nothing we can do
@@ -126,12 +143,86 @@ public IntersectToDistinctRule(Class<? extends Intersect>
intersectClass,
call.transformTo(relBuilder.build());
}
+ /**
+ * Variant performing a partial aggregation pushdown.
+ *
+ * <p>Original query:
+ * <pre>{@code
+ * SELECT job FROM "scott".emp WHERE deptno = 10
+ * INTERSECT
+ * SELECT job FROM "scott".emp WHERE deptno = 20
+ * }</pre>
+ *
+ * <p>Query after conversion:
+ * <pre>{@code
+ * SELECT job
+ * FROM (
+ * SELECT job, COUNT(*) AS c
+ * FROM (
+ * SELECT job, COUNT(*) FROM "scott".emp
+ * WHERE deptno = 10 GROUP BY job
+ * UNION ALL
+ * SELECT job, COUNT(*) FROM "scott".emp
+ * WHERE deptno = 20 GROUP BY job)
+ * GROUP BY job)
+ * WHERE c = 2
+ * }</pre>
+ */
+ public void onMatchAggregatePushdown(RelOptRuleCall call) {
+ final Intersect intersect = call.rel(0);
+ if (intersect.all) {
+ return; // nothing we can do
+ }
+ final RelOptCluster cluster = intersect.getCluster();
+ final RexBuilder rexBuilder = cluster.getRexBuilder();
+ final RelBuilder relBuilder = call.builder();
+
+ // 1st level aggregate: create an aggregate(col_0, ..., col_n, count(*)),
for each branch
+ for (RelNode input : intersect.getInputs()) {
+ relBuilder.push(input);
+ relBuilder.aggregate(relBuilder.groupKey(relBuilder.fields()),
+ relBuilder.countStar(null));
+ }
+
+ // create a union above all the branches
+ final int branchCount = intersect.getInputs().size();
+ relBuilder.union(true, branchCount);
+ final RelNode union = relBuilder.peek();
+
+ // 2nd level aggregate: create an aggregate(col_0, ..., col_n, count(*)),
for each branch
+ // the index of the counter is union.getRowType().getFieldList().size() - 1
+ final int fieldCount = union.getRowType().getFieldCount();
+
+ final ImmutableBitSet groupSet =
+ ImmutableBitSet.range(fieldCount - 1);
+ relBuilder.aggregate(relBuilder.groupKey(groupSet),
+ relBuilder.countStar(null));
+
+ // add a filter count(*) = #branches
+ relBuilder.filter(
+ relBuilder.equals(relBuilder.field(fieldCount - 1),
+ rexBuilder.makeBigintLiteral(new BigDecimal(branchCount))));
+
+ // Project all but the last field
+ relBuilder.project(Util.skipLast(relBuilder.fields()));
+
+ // the schema for intersect distinct matches that of the relation,
+ // built here with an extra last column for the count,
+ // which is projected out by the final project we added
+ call.transformTo(relBuilder.build());
+ }
+
/** Rule configuration. */
@Value.Immutable
public interface Config extends RelRule.Config {
Config DEFAULT = ImmutableIntersectToDistinctRule.Config.of()
.withOperandFor(LogicalIntersect.class);
+ Config NO_AGGREGATE_PUSHDOWN = DEFAULT
+ .withDescription("IntersectToDistinctRule(NoAggregatePushDown)")
+ .as(Config.class)
+ .withAggregatePushdown(false);
+
@Override default IntersectToDistinctRule toRule() {
return new IntersectToDistinctRule(this);
}
@@ -141,5 +232,13 @@ default Config withOperandFor(Class<? extends Intersect>
intersectClass) {
return withOperandSupplier(b -> b.operand(intersectClass).anyInputs())
.as(Config.class);
}
+
+ /** Whether to apply partial aggregate pushdown; default true. */
+ @Value.Default default boolean isAggregatePushdown() {
+ return true;
+ }
+
+ /** Sets {@link #isAggregatePushdown()} ()}. */
+ Config withAggregatePushdown(boolean aggregatePushdown);
}
}
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 5add262ee5..64fb5b0caf 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -4144,33 +4144,6 @@ public void checkOrderBy(final boolean desc,
.returnsUnordered("empid=150; name=Sebastian");
}
- /**
- * Test case of
- * <a
href="https://issues.apache.org/jira/browse/CALCITE-6893";>[CALCITE-6893]
- * Remove agg from Union children in IntersectToDistinctRule</a>. */
- @Test void testIntersectToDistinct() {
- final String sql = ""
- + "select \"empid\", \"name\" from \"hr\".\"emps\" where
\"deptno\"=10\n"
- + "intersect\n"
- + "select \"empid\", \"name\" from \"hr\".\"emps\" where
\"empid\">=150";
- final String[] returns = new String[] {
- "empid=150; name=Sebastian"};
-
- CalciteAssert.hr()
- .query(sql)
- .explainContains("EnumerableIntersect")
- .returnsUnordered(returns);
-
- CalciteAssert.hr()
- .query(sql)
- .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>)
- p -> {
- p.removeRule(EnumerableRules.ENUMERABLE_INTERSECT_RULE);
- })
- .explainContains("EnumerableUnion(all=[true])")
- .returnsUnordered(returns);
- }
-
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6904";>[CALCITE-6904]
* IS_NOT_DISTINCT_FROM is converted error in EnumerableJoinRule</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 df8789414d..c8fdd3111c 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3686,6 +3686,22 @@ private void
checkPushJoinThroughUnionOnRightDoesNotMatchSemiOrAntiJoin(JoinRelT
.check();
}
+ /** Tests {@link org.apache.calcite.rel.rules.IntersectToDistinctRule} with
+ * the option to not perform aggregation pushdown (see
+ * {@link CoreRules#INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN}).
+ * which rewrites an {@link Intersect} operator with 3 inputs. */
+ @Test void testIntersectToDistinctNoAggregatePushdown() {
+ final String sql = "select * from emp where deptno = 10\n"
+ + "intersect\n"
+ + "select * from emp where deptno = 20\n"
+ + "intersect\n"
+ + "select * from emp where deptno = 30\n";
+ sql(sql)
+ .withRule(CoreRules.INTERSECT_MERGE,
+ CoreRules.INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN)
+ .check();
+ }
+
/** Tests that {@link org.apache.calcite.rel.rules.IntersectToDistinctRule}
* correctly ignores an {@code INTERSECT ALL}. It can only handle
* {@code INTERSECT DISTINCT}. */
@@ -3701,6 +3717,23 @@ private void
checkPushJoinThroughUnionOnRightDoesNotMatchSemiOrAntiJoin(JoinRelT
.check();
}
+ /** Tests {@link org.apache.calcite.rel.rules.IntersectToDistinctRule} with
+ * the option to not perform aggregation pushdown (see
+ * {@link CoreRules#INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN}).
+ * correctly ignores an {@code INTERSECT ALL}. It can only handle
+ * {@code INTERSECT DISTINCT}. */
+ @Test void testIntersectToDistinctAllNoAggregatePushdown() {
+ final String sql = "select * from emp where deptno = 10\n"
+ + "intersect\n"
+ + "select * from emp where deptno = 20\n"
+ + "intersect all\n"
+ + "select * from emp where deptno = 30\n";
+ sql(sql)
+ .withRule(CoreRules.INTERSECT_MERGE,
+ CoreRules.INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN)
+ .check();
+ }
+
/** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-6880";>
* [CALCITE-6880] Implement IntersectToSemiJoinRule</a>. */
@Test void testIntersectToSemiJoin() {
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 0e1f49959e..8beb8062ad 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6329,26 +6329,70 @@ LogicalIntersect(all=[false])
<Resource name="planAfter">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[AND(>($9, 0), >($10, 0), >($11, 0))])
- LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], count_i0=[COUNT()
FILTER $9], count_i1=[COUNT() FILTER $10], count_i2=[COUNT() FILTER $11])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f10=[=($9, 0)],
$f11=[=($9, 1)], $f12=[=($9, 2)])
+ LogicalFilter(condition=[=($9, 3)])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], agg#0=[COUNT()])
+ LogicalUnion(all=[true])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], agg#0=[COUNT()])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], agg#0=[COUNT()])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], agg#0=[COUNT()])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 30)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testIntersectToDistinctAll">
+ <Resource name="sql">
+ <![CDATA[select * from emp where deptno = 10
+intersect
+select * from emp where deptno = 20
+intersect all
+select * from emp where deptno = 30
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalIntersect(all=[true])
+ LogicalIntersect(all=[false])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 30)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalIntersect(all=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($9, 2)])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], agg#0=[COUNT()])
LogicalUnion(all=[true])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[0])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}],
agg#0=[COUNT()])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[1])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}],
agg#0=[COUNT()])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[=($7, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[2])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($7, 30)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 30)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
- <TestCase name="testIntersectToDistinctAll">
+ <TestCase name="testIntersectToDistinctAllNoAggregatePushdown">
<Resource name="sql">
<![CDATA[select * from emp where deptno = 10
intersect
@@ -6391,6 +6435,52 @@ LogicalIntersect(all=[true])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[=($7, 30)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testIntersectToDistinctNoAggregatePushdown">
+ <Resource name="sql">
+ <![CDATA[select * from emp where deptno = 10
+intersect
+select * from emp where deptno = 20
+intersect
+select * from emp where deptno = 30
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalIntersect(all=[false])
+ LogicalIntersect(all=[false])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 30)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[AND(>($9, 0), >($10, 0), >($11, 0))])
+ LogicalAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], count_i0=[COUNT()
FILTER $9], count_i1=[COUNT() FILTER $10], count_i2=[COUNT() FILTER $11])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f10=[=($9, 0)],
$f11=[=($9, 1)], $f12=[=($9, 2)])
+ LogicalUnion(all=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[1])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[2])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, 30)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/set-op.iq
b/core/src/test/resources/sql/set-op.iq
index 4b3917ad30..b52debdf3e 100644
--- a/core/src/test/resources/sql/set-op.iq
+++ b/core/src/test/resources/sql/set-op.iq
@@ -47,6 +47,74 @@ intersect
!ok
+!use scott
+
+!set planner-rules "
+-EnumerableRules.ENUMERABLE_INTERSECT_RULE,
+-AGGREGATE_REMOVE"
+# AGGREGATE_REMOVE is disabled as it would remove the inner COUNTs
+# as the grouping columns are unique
+
+# Intersect rewrite as aggregation + union with partial aggregation pushdown
+select empno, ename from emp where deptno = 10
+intersect
+select empno, ename from emp where empno >= 150;
+
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2:BIGINT], expr#4=[=($t2, $t3)],
proj#0..1=[{exprs}], $condition=[$t4])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
+ EnumerableUnion(all=[true])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER],
expr#9=[10], expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[COUNT()])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT
NULL], expr#9=[150], expr#10=[>=($t8, $t9)], proj#0..7=[{exprs}],
$condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++-------+--------+
+| EMPNO | ENAME |
++-------+--------+
+| 7782 | CLARK |
+| 7839 | KING |
+| 7934 | MILLER |
++-------+--------+
+(3 rows)
+
+!ok
+!set planner-rules original
+
+!use scott
+
+!set planner-rules "
+-CoreRules.INTERSECT_TO_DISTINCT,
+-EnumerableRules.ENUMERABLE_INTERSECT_RULE,
++CoreRules.INTERSECT_TO_DISTINCT_NO_AGGREGATE_PUSHDOWN"
+
+# Intersect rewrite as aggregation + union without partial aggregation pushdown
+select empno, ename from emp where deptno = 10
+intersect
+select empno, ename from emp where empno >= 150;
+
+EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[>($t2, $t4)],
expr#6=[>($t3, $t4)], expr#7=[AND($t5, $t6)], proj#0..1=[{exprs}],
$condition=[$t7])
+ EnumerableAggregate(group=[{0, 1}], count_i0=[COUNT() FILTER $2],
count_i1=[COUNT() FILTER $3])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[0], expr#4=[=($t2, $t3)],
expr#5=[1], expr#6=[=($t2, $t5)], proj#0..1=[{exprs}], $f3=[$t4], $f4=[$t6])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0],
expr#9=[CAST($t7):INTEGER], expr#10=[10], expr#11=[=($t9, $t10)],
proj#0..1=[{exprs}], i=[$t8], $condition=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1],
expr#9=[CAST($t0):INTEGER NOT NULL], expr#10=[150], expr#11=[>=($t9, $t10)],
proj#0..1=[{exprs}], i=[$t8], $condition=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++-------+--------+
+| EMPNO | ENAME |
++-------+--------+
+| 7782 | CLARK |
+| 7839 | KING |
+| 7934 | MILLER |
++-------+--------+
+(3 rows)
+
+!ok
+!set planner-rules original
+
# Intersect all with null value rows
select * from
(select x, y from (values (cast(NULL as int), cast(NULL as varchar(1))),
