You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by al...@apache.org on 2022/10/13 14:50:30 UTC
[ignite] branch master updated: IGNITE-13022 SQL Calcite: Merge index conditions for the same field - Fixes #10306.
This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new 3d24cf73456 IGNITE-13022 SQL Calcite: Merge index conditions for the same field - Fixes #10306.
3d24cf73456 is described below
commit 3d24cf73456e594c846542f8a597e595a551c4ee
Author: Aleksey Plekhanov <pl...@gmail.com>
AuthorDate: Thu Oct 13 17:47:44 2022 +0300
IGNITE-13022 SQL Calcite: Merge index conditions for the same field - Fixes #10306.
Signed-off-by: Aleksey Plekhanov <pl...@gmail.com>
---
.../processors/query/calcite/util/RexUtils.java | 164 +++++++++++++++++----
.../CalciteBasicSecondaryIndexIntegrationTest.java | 43 ++++++
...Test.java => IndexSearchBoundsPlannerTest.java} | 55 ++++++-
.../apache/ignite/testsuites/PlannerTestSuite.java | 4 +-
4 files changed, 231 insertions(+), 35 deletions(-)
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/RexUtils.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/RexUtils.java
index e8e120edd73..3bfeb4d2839 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/RexUtils.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/RexUtils.java
@@ -331,6 +331,7 @@ public class RexUtils {
for (RexCall pred : collFldPreds) {
RexNode val = null;
+ RexNode ref = pred.getOperands().get(0);
if (isBinaryComparison(pred)) {
val = removeCast(pred.operands.get(1));
@@ -349,60 +350,88 @@ public class RexUtils {
else if (op.kind == SEARCH) {
Sarg<?> sarg = ((RexLiteral)pred.operands.get(1)).getValueAs(Sarg.class);
- int complexity = prevComplexity * sarg.complexity();
+ List<SearchBounds> bounds = expandSargToBounds(fc, cluster, fldType, prevComplexity, sarg, ref);
- // Limit amount of search bounds tuples.
- if (complexity > MAX_SEARCH_BOUNDS_COMPLEXITY)
- return null;
-
- RexNode sargCond = sargRef(builder, pred.operands.get(0), sarg, fldType, RexUnknownAs.UNKNOWN);
- List<RexNode> disjunctions = RelOptUtil.disjunctions(RexUtil.toDnf(builder, sargCond));
- List<SearchBounds> bounds = new ArrayList<>(disjunctions.size());
+ if (bounds == null)
+ continue;
- for (RexNode bound : disjunctions) {
- List<RexNode> conjunctions = RelOptUtil.conjunctions(bound);
- List<RexCall> calls = new ArrayList<>(conjunctions.size());
+ if (bounds.size() == 1) {
+ if (bounds.get(0) instanceof RangeBounds && collFldPreds.size() > 1) {
+ // Try to merge bounds.
+ boolean ascDir = !fc.getDirection().isDescending();
+ RangeBounds rangeBounds = (RangeBounds)bounds.get(0);
+ if (rangeBounds.lowerBound() != null) {
+ if (lowerBound != null && lowerBound != nullVal) {
+ lowerBound = leastOrGreatest(builder, !ascDir, lowerBound, rangeBounds.lowerBound(), nullVal);
+ lowerInclude |= rangeBounds.lowerInclude();
+ }
+ else {
+ lowerBound = rangeBounds.lowerBound();
+ lowerInclude = rangeBounds.lowerInclude();
+ }
+ lowerCond = lessOrGreater(builder, !ascDir, lowerInclude, ref, lowerBound);
+ }
+
+ if (rangeBounds.upperBound() != null) {
+ if (upperBound != null && upperBound != nullVal) {
+ upperBound = leastOrGreatest(builder, ascDir, upperBound, rangeBounds.upperBound(), nullVal);
+ upperInclude |= rangeBounds.upperInclude();
+ }
+ else {
+ upperBound = rangeBounds.upperBound();
+ upperInclude = rangeBounds.upperInclude();
+ }
+ upperCond = lessOrGreater(builder, ascDir, upperInclude, ref, upperBound);
+ }
- for (RexNode rexNode : conjunctions) {
- if (isSupportedTreeComparison(rexNode))
- calls.add((RexCall)rexNode);
- else
- return null; // Cannot filter using this predicate (NOT_EQUALS for example).
+ continue;
}
-
- bounds.add(createBounds(fc, calls, cluster, fldType, complexity));
+ else
+ return bounds.get(0);
}
- if (bounds.size() == 1)
- return bounds.get(0);
-
return new MultiBounds(pred, bounds);
}
// Range bounds.
boolean lowerBoundBelow = !fc.getDirection().isDescending();
+ boolean includeBound = op.kind == GREATER_THAN_OR_EQUAL || op.kind == LESS_THAN_OR_EQUAL;
+ boolean lessCondition = false;
+
switch (op.kind) {
case LESS_THAN:
case LESS_THAN_OR_EQUAL:
+ lessCondition = true;
lowerBoundBelow = !lowerBoundBelow;
// Fall through.
case GREATER_THAN:
case GREATER_THAN_OR_EQUAL:
if (lowerBoundBelow) {
- lowerCond = pred;
- lowerBound = val;
-
- if (op.kind == GREATER_THAN || op.kind == LESS_THAN)
- lowerInclude = false;
+ if (lowerBound == null || lowerBound == nullVal) {
+ lowerCond = pred;
+ lowerBound = val;
+ lowerInclude = includeBound;
+ }
+ else {
+ lowerBound = leastOrGreatest(builder, lessCondition, lowerBound, val, nullVal);
+ lowerInclude |= includeBound;
+ lowerCond = lessOrGreater(builder, lessCondition, lowerInclude, ref, lowerBound);
+ }
}
else {
- upperCond = pred;
- upperBound = val;
-
- if (op.kind == GREATER_THAN || op.kind == LESS_THAN)
- upperInclude = false;
+ if (upperBound == null || upperBound == nullVal) {
+ upperCond = pred;
+ upperBound = val;
+ upperInclude = includeBound;
+ }
+ else {
+ upperBound = leastOrGreatest(builder, lessCondition, upperBound, val, nullVal);
+ upperInclude |= includeBound;
+ upperCond = lessOrGreater(builder, lessCondition, upperInclude, ref, upperBound);
+ }
}
+ // Fall through.
case IS_NOT_NULL:
if (fc.nullDirection == RelFieldCollation.NullDirection.FIRST && lowerBound == null) {
@@ -433,6 +462,79 @@ public class RexUtils {
return new RangeBounds(cond, lowerBound, upperBound, lowerInclude, upperInclude);
}
+ /** */
+ private static List<SearchBounds> expandSargToBounds(
+ RelFieldCollation fc,
+ RelOptCluster cluster,
+ RelDataType fldType,
+ int prevComplexity,
+ Sarg<?> sarg,
+ RexNode ref
+ ) {
+ int complexity = prevComplexity * sarg.complexity();
+
+ // Limit amount of search bounds tuples.
+ if (complexity > MAX_SEARCH_BOUNDS_COMPLEXITY)
+ return null;
+
+ RexBuilder builder = builder(cluster);
+
+ RexNode sargCond = sargRef(builder, ref, sarg, fldType, RexUnknownAs.UNKNOWN);
+ List<RexNode> disjunctions = RelOptUtil.disjunctions(RexUtil.toDnf(builder, sargCond));
+ List<SearchBounds> bounds = new ArrayList<>(disjunctions.size());
+
+ for (RexNode bound : disjunctions) {
+ List<RexNode> conjunctions = RelOptUtil.conjunctions(bound);
+ List<RexCall> calls = new ArrayList<>(conjunctions.size());
+
+ for (RexNode rexNode : conjunctions) {
+ if (isSupportedTreeComparison(rexNode))
+ calls.add((RexCall)rexNode);
+ else // Cannot filter using this predicate (NOT_EQUALS for example), give a chance to other predicates.
+ return null;
+ }
+
+ bounds.add(createBounds(fc, calls, cluster, fldType, complexity));
+ }
+
+ return bounds;
+ }
+
+ /** */
+ private static RexNode leastOrGreatest(RexBuilder builder, boolean least, RexNode arg0, RexNode arg1, RexNode nullVal) {
+ // There is no implementor for LEAST/GREATEST, so convert this calls directly to CASE operator.
+ List<RexNode> argList = new ArrayList<>();
+
+ // CASE
+ // WHEN arg0 IS NULL OR arg1 IS NULL THEN NULL
+ // WHEN arg0 < arg1 THEN arg0
+ // ELSE arg1
+ // END
+ argList.add(builder.makeCall(SqlStdOperatorTable.OR,
+ builder.makeCall(SqlStdOperatorTable.IS_NULL, arg0),
+ builder.makeCall(SqlStdOperatorTable.IS_NULL, arg1)));
+ argList.add(nullVal);
+ argList.add(builder.makeCall(least ? SqlStdOperatorTable.LESS_THAN : SqlStdOperatorTable.GREATER_THAN, arg0, arg1));
+ argList.add(arg0);
+ argList.add(arg1);
+
+ return builder.makeCall(SqlStdOperatorTable.CASE, argList);
+ }
+
+ /** */
+ private static RexNode lessOrGreater(
+ RexBuilder builder,
+ boolean less,
+ boolean includeBound,
+ RexNode arg0,
+ RexNode arg1
+ ) {
+ return builder.makeCall(less ?
+ (includeBound ? SqlStdOperatorTable.LESS_THAN_OR_EQUAL : SqlStdOperatorTable.LESS_THAN) :
+ (includeBound ? SqlStdOperatorTable.GREATER_THAN_OR_EQUAL : SqlStdOperatorTable.GREATER_THAN),
+ arg0, arg1);
+ }
+
/**
* Builds index conditions.
*/
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/CalciteBasicSecondaryIndexIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/CalciteBasicSecondaryIndexIntegrationTest.java
index 6415d3bf50a..54a62ddec35 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/CalciteBasicSecondaryIndexIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/CalciteBasicSecondaryIndexIntegrationTest.java
@@ -1115,6 +1115,49 @@ public class CalciteBasicSecondaryIndexIntegrationTest extends AbstractBasicInte
.check();
}
+ /**
+ * Test index search bounds merge.
+ */
+ @Test
+ public void testIndexBoundsMerge() {
+ assertQuery("SELECT id FROM Developer WHERE depId > 19 AND depId > ?")
+ .withParams(20)
+ .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
+ .returns(22)
+ .returns(23)
+ .check();
+
+ assertQuery("SELECT id FROM Developer WHERE depId > 20 AND depId > ?")
+ .withParams(19)
+ .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
+ .returns(22)
+ .returns(23)
+ .check();
+
+ assertQuery("SELECT id FROM Developer WHERE depId >= 20 AND depId > ?")
+ .withParams(19)
+ .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
+ .returns(21)
+ .returns(22)
+ .returns(23)
+ .check();
+
+ assertQuery("SELECT id FROM Developer WHERE depId BETWEEN ? AND ? AND depId > 19")
+ .withParams(19, 21)
+ .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
+ .returns(21)
+ .returns(22)
+ .check();
+
+ // Index with DESC ordering.
+ assertQuery("SELECT id FROM Birthday WHERE name BETWEEN 'B' AND 'D' AND name > ?")
+ .withParams("Bach")
+ .matches(containsIndexScan("PUBLIC", "BIRTHDAY", NAME_DATE_IDX))
+ .returns(2)
+ .returns(6)
+ .check();
+ }
+
/** */
private static class Developer {
/** */
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/SearchSargOnIndexPlannerTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/IndexSearchBoundsPlannerTest.java
similarity index 88%
rename from modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/SearchSargOnIndexPlannerTest.java
rename to modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/IndexSearchBoundsPlannerTest.java
index a77a0fcd582..6849e3c393a 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/SearchSargOnIndexPlannerTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/IndexSearchBoundsPlannerTest.java
@@ -43,9 +43,9 @@ import org.apache.ignite.internal.processors.query.calcite.util.RexUtils;
import org.junit.Test;
/**
- * Planner test for SEARCH/SARG condition on indexed fields.
+ * Planner test for indexed fields scans.
*/
-public class SearchSargOnIndexPlannerTest extends AbstractPlannerTest {
+public class IndexSearchBoundsPlannerTest extends AbstractPlannerTest {
/** */
private IgniteSchema publicSchema;
@@ -378,6 +378,57 @@ public class SearchSargOnIndexPlannerTest extends AbstractPlannerTest {
);
}
+ /** Tests bounds merge. */
+ @Test
+ public void testBoundsMerge() throws Exception {
+ assertBounds("SELECT * FROM TEST WHERE C1 > ? AND C1 >= 1",
+ range(leastOrGreatest(false, "?0", "1", "INTEGER"), null, true, true)
+ );
+
+ assertBounds("SELECT * FROM TEST WHERE C1 > ? AND C1 >= ? AND C1 > ?",
+ range(
+ leastOrGreatest(false, leastOrGreatest(false, "?0", "?1", "INTEGER"), "?2", "INTEGER"),
+ null, true, true
+ )
+ );
+
+ assertBounds("SELECT * FROM TEST WHERE C1 > ? AND C1 >= 1 AND C1 < ? AND C1 < ?",
+ range(
+ leastOrGreatest(false, "?0", "1", "INTEGER"),
+ leastOrGreatest(true, "?1", "?2", "INTEGER"),
+ true, false
+ )
+ );
+
+ assertBounds("SELECT * FROM TEST WHERE C1 < ? AND C1 BETWEEN 1 AND 10 ",
+ range(1, leastOrGreatest(true, "?0", "10", "INTEGER"), true, true)
+ );
+
+ assertBounds("SELECT * FROM TEST WHERE C1 NOT IN (1, 2) AND C1 >= ?",
+ range("?0", null, true, true)
+ );
+
+ tbl.addIndex(RelCollations.of(TraitUtils.createFieldCollation(3, false),
+ TraitUtils.createFieldCollation(2, true)), "C4");
+
+ assertBounds("SELECT * FROM TEST WHERE C4 > ? AND C4 >= 1 AND C4 < ? AND C4 < ?",
+ empty(),
+ empty(),
+ empty(),
+ range(
+ leastOrGreatest(true, "?1", "?2", "INTEGER"),
+ leastOrGreatest(false, "?0", "1", "INTEGER"),
+ false, true
+ )
+ );
+ }
+
+ /** String representation of LEAST or CREATEST operator converted to CASE. */
+ private String leastOrGreatest(boolean least, String val0, String val1, String type) {
+ return "CASE(OR(IS NULL(" + val0 + "), IS NULL(" + val1 + ")), null:" + type + ", " + (least ? '<' : '>') +
+ '(' + val0 + ", " + val1 + "), " + val0 + ", " + val1 + ')';
+ }
+
/** */
private void assertBounds(String sql, Predicate<SearchBounds>... predicates) throws Exception {
assertPlan(sql, publicSchema, nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)
diff --git a/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java b/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java
index e967eb26b64..bb92571bd83 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java
@@ -24,6 +24,7 @@ import org.apache.ignite.internal.processors.query.calcite.planner.CorrelatedSub
import org.apache.ignite.internal.processors.query.calcite.planner.HashAggregatePlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.HashIndexSpoolPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.IndexRebuildPlannerTest;
+import org.apache.ignite.internal.processors.query.calcite.planner.IndexSearchBoundsPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.JoinColocationPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.JoinCommutePlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.JoinWithUsingPlannerTest;
@@ -32,7 +33,6 @@ import org.apache.ignite.internal.processors.query.calcite.planner.MergeJoinPlan
import org.apache.ignite.internal.processors.query.calcite.planner.PlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.PlannerTimeoutTest;
import org.apache.ignite.internal.processors.query.calcite.planner.ProjectFilterScanMergePlannerTest;
-import org.apache.ignite.internal.processors.query.calcite.planner.SearchSargOnIndexPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.SetOpPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.SortAggregatePlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.SortedIndexSpoolPlannerTest;
@@ -72,7 +72,7 @@ import org.junit.runners.Suite;
ProjectFilterScanMergePlannerTest.class,
IndexRebuildPlannerTest.class,
PlannerTimeoutTest.class,
- SearchSargOnIndexPlannerTest.class,
+ IndexSearchBoundsPlannerTest.class,
})
public class PlannerTestSuite {
}