This is an automated email from the ASF dual-hosted git repository.
amashenkov pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 1bc2dfb6895 IGNITE-28389 SQL. Eliminate table scans with always_false
predicate (#7959)
1bc2dfb6895 is described below
commit 1bc2dfb6895449054c236dfe105392d9ae6750f7
Author: amashenkov <[email protected]>
AuthorDate: Wed Apr 15 12:38:50 2026 +0300
IGNITE-28389 SQL. Eliminate table scans with always_false predicate (#7959)
---
.../ignite/internal/sql/engine/ItDmlTest.java | 12 ++++
.../internal/sql/engine/prepare/PlannerHelper.java | 2 +
.../internal/sql/engine/prepare/PlannerPhase.java | 25 ++++++++
.../engine/rule/logical/FilterScanMergeRule.java | 16 +++++
.../engine/rule/logical/PruneTableModifyRule.java | 74 ++++++++++++++++++++++
.../planner/ProjectFilterScanMergePlannerTest.java | 72 +++++++++++++++++++++
.../resources/mapping/test_partition_pruning.test | 53 ----------------
7 files changed, 201 insertions(+), 53 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDmlTest.java
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDmlTest.java
index f2011008192..988249c5b67 100644
---
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDmlTest.java
+++
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDmlTest.java
@@ -55,6 +55,7 @@ import org.apache.ignite.lang.ErrorGroups.Sql;
import org.apache.ignite.lang.IgniteException;
import org.apache.ignite.tx.Transaction;
import org.apache.ignite.tx.TransactionOptions;
+import org.hamcrest.Matchers;
import org.jetbrains.annotations.Nullable;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.Disabled;
@@ -1179,6 +1180,17 @@ public class ItDmlTest extends BaseSqlIntegrationTest {
);
}
+ @Test
+ public void insertFromSelectWithAlwaysFalseCondition() {
+ sql("CREATE TABLE test (id INT PRIMARY KEY, val REAL)");
+ sql("CREATE TABLE test2 (id INT PRIMARY KEY, val REAL)");
+
+ assertQuery("INSERT INTO test2 SELECT id, val FROM test WHERE val > 1
AND val < 0")
+ .matches(Matchers.not(containsSubPlan("TableModify")))
+ .returns(0L)
+ .check();
+ }
+
private static Stream<Arguments> decimalLimits() {
return Stream.of(
arguments(SqlTypeName.BIGINT.getName(), Long.MAX_VALUE,
Long.MIN_VALUE),
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java
index 89f9134fec1..1d565ccd4dd 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java
@@ -182,6 +182,8 @@ public final class PlannerHelper {
rel = planner.transform(PlannerPhase.HEP_PROJECT_PUSH_DOWN,
rel.getTraitSet(), rel);
+ rel = planner.transform(PlannerPhase.HEP_EMPTY_NODES_ELIMINATION,
rel.getTraitSet(), rel);
+
if (fastQueryOptimizationEnabled()) {
// the sole purpose of this code block is to limit scope of
`simpleOperation` variable.
// The result of `HEP_TO_SIMPLE_KEY_VALUE_OPERATION` phase
MUST NOT be passed to next stage,
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
index 303f08400a4..9631e6d28b8 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
@@ -78,6 +78,7 @@ import
org.apache.ignite.internal.sql.engine.rule.logical.IgniteMultiJoinOptimiz
import
org.apache.ignite.internal.sql.engine.rule.logical.IgniteProjectCorrelateTransposeRule;
import org.apache.ignite.internal.sql.engine.rule.logical.LogicalOrToUnionRule;
import org.apache.ignite.internal.sql.engine.rule.logical.ProjectScanMergeRule;
+import org.apache.ignite.internal.sql.engine.rule.logical.PruneTableModifyRule;
import org.apache.ignite.internal.sql.engine.util.Commons;
/**
@@ -161,6 +162,22 @@ public enum PlannerPhase {
}
},
+ HEP_EMPTY_NODES_ELIMINATION(
+ "Heuristic phase to eliminate empty nodes",
+ PruneEmptyRules.PROJECT_INSTANCE,
+ PruneEmptyRules.FILTER_INSTANCE,
+ PruneEmptyRules.SORT_INSTANCE,
+ PruneEmptyRules.AGGREGATE_INSTANCE,
+ PruneEmptyRules.JOIN_LEFT_INSTANCE,
+ PruneEmptyRules.JOIN_RIGHT_INSTANCE
+ ) {
+ /** {@inheritDoc} */
+ @Override
+ public Program getProgram(PlanningContext ctx) {
+ return hep(getRules(ctx));
+ }
+ },
+
HEP_OPTIMIZE_JOIN_ORDER(
"Heuristic phase to optimize join order"
) {
@@ -207,6 +224,13 @@ public enum PlannerPhase {
IgniteJoinConditionPushRule.INSTANCE,
CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES,
+ PruneEmptyRules.PROJECT_INSTANCE,
+ PruneEmptyRules.FILTER_INSTANCE,
+ PruneEmptyRules.SORT_INSTANCE,
+ PruneEmptyRules.AGGREGATE_INSTANCE,
+ PruneEmptyRules.JOIN_LEFT_INSTANCE,
+ PruneEmptyRules.JOIN_RIGHT_INSTANCE,
+
FilterIntoJoinRule.FilterIntoJoinRuleConfig.DEFAULT
.withOperandSupplier(b0 ->
b0.operand(LogicalFilter.class).oneInput(b1 ->
@@ -255,6 +279,7 @@ public enum PlannerPhase {
PruneEmptyRules.CORRELATE_LEFT_INSTANCE,
PruneEmptyRules.CORRELATE_RIGHT_INSTANCE,
+ PruneTableModifyRule.INSTANCE,
// Useful of this rule is not clear now.
// CoreRules.AGGREGATE_REDUCE_FUNCTIONS,
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/FilterScanMergeRule.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/FilterScanMergeRule.java
index 77828bb5de7..448c8d77a35 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/FilterScanMergeRule.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/FilterScanMergeRule.java
@@ -26,6 +26,7 @@ import org.apache.calcite.plan.RelRule;
import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.logical.LogicalFilter;
+import org.apache.calcite.rel.logical.LogicalValues;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexNode;
@@ -99,6 +100,21 @@ public abstract class FilterScanMergeRule<T extends
ProjectableFilterableTableSc
// We need to replace RexInputRef with RexLocalRef because TableScan
doesn't have inputs.
condition = RexUtils.replaceInputRefs(condition);
+ // Eliminate scan if always false condition found.
+ if (condition.isAlwaysFalse()) {
+ call.transformTo(LogicalValues.createEmpty(cluster,
scan.getRowType()));
+ call.getPlanner().prune(filter);
+ call.getPlanner().prune(scan);
+ return;
+ }
+
+ // Eliminate always true condition.
+ if (condition.isAlwaysTrue()) {
+ call.transformTo(scan);
+ call.getPlanner().prune(filter);
+ return;
+ }
+
// Set default traits, real traits will be calculated for physical
node.
RelTraitSet trait = cluster.traitSet();
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/PruneTableModifyRule.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/PruneTableModifyRule.java
new file mode 100644
index 00000000000..fe8164af5d3
--- /dev/null
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/PruneTableModifyRule.java
@@ -0,0 +1,74 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.sql.engine.rule.logical;
+
+import java.util.Collections;
+import java.util.List;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.TableModify;
+import org.apache.calcite.rel.core.Values;
+import org.apache.calcite.rel.rules.SubstitutionRule;
+import org.apache.calcite.rex.RexLiteral;
+import org.apache.ignite.internal.sql.engine.rex.IgniteRexBuilder;
+import
org.apache.ignite.internal.sql.engine.rule.logical.PruneTableModifyRule.Config;
+import org.immutables.value.Value;
+
+/**
+ * Rule that eliminates table modify node if it doesn't have any source rows.
+ */
[email protected]
+public class PruneTableModifyRule extends RelRule<Config> implements
SubstitutionRule {
+ public static final RelOptRule INSTANCE = Config.DEFAULT.toRule();
+
+ /**
+ * Constructor.
+ *
+ * @param config Rule configuration.
+ */
+ private PruneTableModifyRule(PruneTableModifyRule.Config config) {
+ super(config);
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ TableModify singleRel = call.rel(0);
+
+ // TODO https://issues.apache.org/jira/browse/IGNITE-23512: Default
Calcite RexBuilder ignores field type and extract type from
+ // the given value. E.g. for zero value RexBuilder creates INT
literal. Use simple way create `singleValue` after fixing the issue.
+ // RelNode singleValue = call.builder().values(singleRel.getRowType(),
0L).build();
+ RexLiteral zeroLiteral = IgniteRexBuilder.INSTANCE.makeLiteral(0L,
singleRel.getRowType().getFieldList().get(0).getType());
+ RelNode singleValue =
call.builder().values(List.of(List.of(zeroLiteral)),
singleRel.getRowType()).build();
+
+ singleValue = singleValue.copy(singleRel.getCluster().traitSet(),
Collections.emptyList());
+ call.transformTo(singleValue);
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable(singleton = false)
+ public interface Config extends RuleFactoryConfig<Config> {
+ Config DEFAULT = ImmutablePruneTableModifyRule.Config.builder()
+ .withDescription("PruneTableModify")
+ .withRuleFactory(PruneTableModifyRule::new)
+ .withOperandSupplier(b0 ->
+ b0.operand(TableModify.class).oneInput(b1 ->
+
b1.operand(Values.class).predicate(Values::isEmpty).noInputs()))
+ .build();
+ }
+}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/ProjectFilterScanMergePlannerTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/ProjectFilterScanMergePlannerTest.java
index 71c015daf1d..1ae5e6faad5 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/ProjectFilterScanMergePlannerTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/ProjectFilterScanMergePlannerTest.java
@@ -19,8 +19,10 @@ package org.apache.ignite.internal.sql.engine.planner;
import java.util.List;
import java.util.Objects;
+import java.util.function.Predicate;
import java.util.function.UnaryOperator;
import java.util.stream.Collectors;
+import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.util.ImmutableIntList;
import
org.apache.ignite.internal.sql.engine.framework.TestBuilders.TableBuilder;
@@ -28,6 +30,7 @@ import
org.apache.ignite.internal.sql.engine.prepare.bounds.SearchBounds;
import org.apache.ignite.internal.sql.engine.rel.IgniteAggregate;
import org.apache.ignite.internal.sql.engine.rel.IgniteIndexScan;
import org.apache.ignite.internal.sql.engine.rel.IgniteTableScan;
+import org.apache.ignite.internal.sql.engine.rel.IgniteValues;
import org.apache.ignite.internal.sql.engine.schema.IgniteSchema;
import org.apache.ignite.internal.sql.engine.trait.IgniteDistributions;
import org.apache.ignite.internal.type.NativeTypes;
@@ -275,6 +278,75 @@ public class ProjectFilterScanMergePlannerTest extends
AbstractPlannerTest {
"ProjectFilterTransposeRule", "FilterProjectTransposeRule");
}
+ @Test
+ public void testAlwaysTrueFilterPruning() throws Exception {
+ String sql = "SELECT a, c FROM tbl WHERE a > 1 OR a < 3 OR a IS NULL";
+
+ assertPlan(sql, publicSchema, isInstanceOf(IgniteTableScan.class)
+ .and(scan -> scan.projects() == null)
+ .and(scan -> scan.condition() == null)
+ .and(scan -> ImmutableIntList.of(0,
2).equals(scan.requiredColumns())),
+ "ProjectFilterTransposeRule", "FilterProjectTransposeRule");
+ }
+
+ @Test
+ public void testAlwaysFalseFilterPruning() throws Exception {
+ Predicate<IgniteValues> hasEmptyValuesOnly =
hasEmptyValuesOnlyPredicate();
+
+ // Table scan elimination.
+ String sql = "SELECT a, c FROM tbl WHERE a > 1 AND a < 0";
+ assertPlan(sql, publicSchema, hasEmptyValuesOnly);
+
+ sql = "SELECT a, c FROM (SELECT a, c FROM tbl WHERE a > 1) WHERE c = 1
AND c IS NULL";
+ assertPlan(sql, publicSchema, hasEmptyValuesOnly,
+ "ProjectFilterTransposeRule", "FilterProjectTransposeRule");
+
+ sql = "SELECT a, c FROM (SELECT a, c FROM tbl WHERE a > 1) WHERE a <
0";
+ assertPlan(sql, publicSchema, hasEmptyValuesOnly,
+ "ProjectFilterTransposeRule", "FilterProjectTransposeRule");
+
+ // JOIN branch elimination.
+ sql = "SELECT t1.a, t2.a, t1.c FROM tbl AS t1 LEFT JOIN tbl AS t2 ON
t1.a = t2.a WHERE t2.a = 1 AND t2.a IS NULL AND t1.c = 1";
+ assertPlan(sql, publicSchema, hasEmptyValuesOnly);
+
+ sql = "SELECT t1.a, t2.a, t1.c FROM tbl AS t1 INNER JOIN tbl AS t2 ON
t1.a = t2.a WHERE t2.a = 1 AND t2.a IS NULL";
+ assertPlan(sql, publicSchema, hasEmptyValuesOnly);
+
+ sql = "SELECT t1.a, t2.a, t1.c FROM tbl AS t1 INNER JOIN tbl AS t2 ON
t1.a = t2.a WHERE t1.a = 1 AND t2.a = 2";
+ assertPlan(sql, publicSchema, hasEmptyValuesOnlyPredicate());
+ }
+
+ @Test
+ public void testJoinWithAlwaysFalseConditionPruning() throws Exception {
+ String sql = "SELECT t1.a, t2.a, t1.c FROM tbl AS t1 LEFT JOIN tbl AS
t2 ON (t1.a = t2.a AND t2.a = 1 AND t2.a = 2) WHERE t1.c = 1";
+ assertPlan(sql, publicSchema, isInstanceOf(IgniteTableScan.class)
+ .and(scan -> scan.projects() != null)
+ .and(scan -> scan.condition() != null)
+ .and(scan -> "=($t1, 1)".equals(scan.condition().toString()))
+ );
+
+ sql = "SELECT t1.a, t2.a, t1.c FROM tbl AS t1 INNER JOIN tbl AS t2 ON
t1.a = t2.a AND t2.a = 1 AND t2.a = 2";
+ assertPlan(sql, publicSchema, hasEmptyValuesOnlyPredicate());
+ }
+
+ @Test
+ public void testAlwaysFalseFilterPruningWithDml() throws Exception {
+ Predicate<IgniteValues> zeroDmlResultPredicate =
isInstanceOf(IgniteValues.class)
+ .and(values -> values.getTuples().size() == 1) // single row
+ .and(values -> values.getTuples().get(0).size() == 1) // row
of single column
+ .and(values ->
RexLiteral.longValue(values.getTuples().get(0).get(0)) == 0L);
+
+ String sql = "INSERT INTO tbl (a, c) SELECT a, b FROM tbl WHERE a > 1
AND a < 0";
+ assertPlan(sql, publicSchema, zeroDmlResultPredicate);
+
+ sql = "INSERT INTO tbl (a, c) (SELECT a, c FROM (SELECT a, c FROM tbl
WHERE a > 1) WHERE a < 0)";
+ assertPlan(sql, publicSchema, zeroDmlResultPredicate);
+ }
+
+ private Predicate<IgniteValues> hasEmptyValuesOnlyPredicate() {
+ return isInstanceOf(IgniteValues.class).and(values ->
values.getTuples().isEmpty());
+ }
+
/**
* Convert search bounds to RexNodes.
*/
diff --git
a/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
b/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
index d0932226f45..febc16e2780 100644
--- a/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
+++ b/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
@@ -163,59 +163,6 @@ Fragment#4
fieldNames: [ID, C1, C2]
est: (rows=1)
---
-# Self join, different predicates that produce disjoint set of partitions
-# TODO https://issues.apache.org/jira/browse/IGNITE-28389: Fix the test. We
expect the mapper should eliminate all the disjoined parts.
-N1
-SELECT /*+ DISABLE_RULE('NestedLoopJoinConverter', 'HashJoinConverter',
'CorrelatedNestedLoopJoin') */ *
- FROM t1_n1n2n3 as t1, t1_n1n2n3 as t2
- WHERE t1.id = t2.id and t1.id IN (1, 3) and t2.id IN (42, 44)
----
-Fragment#2 root
- distribution: single
- executionNodes: [N1]
- exchangeSourceNodes: {3=[N1, N2, N3]}
- colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 3], assignments={},
partitionsWithConsistencyTokens={N1=[]}}
- colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 3], assignments={},
partitionsWithConsistencyTokens={N1=[]}}
- tree:
- Receiver
- fieldNames: [ID, C1, C2, ID$0, C1$0, C2$0]
- sourceFragmentId: 3
- est: (rows=1)
-
-Fragment#3
- distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1
- executionNodes: [N1, N2, N3]
- targetNodes: [N1]
- colocationGroup[0]: {nodes=[N1, N2, N3], sourceIds=[0, 1],
assignments={part_0=N1:3, part_1=N2:3, part_2=N3:3},
partitionsWithConsistencyTokens={N1=[part_0:3], N2=[part_1:3], N3=[part_2:3]}}
- colocationGroup[1]: {nodes=[N1, N2, N3], sourceIds=[0, 1],
assignments={part_0=N1:3, part_1=N2:3, part_2=N3:3},
partitionsWithConsistencyTokens={N1=[part_0:3], N2=[part_1:3], N3=[part_2:3]}}
- partitions: [T1_N1N2N3=[N1={0}, N2={1}, N3={2}]]
- tree:
- Sender
- distribution: single
- targetFragmentId: 2
- est: (rows=6250)
- MergeJoin
- predicate: =(ID, ID$0)
- fieldNames: [ID, C1, C2, ID$0, C1$0, C2$0]
- type: inner
- est: (rows=6250)
- Sort
- collation: [ID ASC]
- est: (rows=25000)
- TableScan
- table: PUBLIC.T1_N1N2N3
- predicate: false
- fieldNames: [ID, C1, C2]
- est: (rows=25000)
- Sort
- collation: [ID ASC]
- est: (rows=25000)
- TableScan
- table: PUBLIC.T1_N1N2N3
- predicate: false
- fieldNames: [ID, C1, C2]
- est: (rows=25000)
----
# Correlated
# Prune partitions from left arm statically, and pass meta to the right arm.
# Same set of nodes.
