You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by vo...@apache.org on 2019/01/16 14:16:13 UTC
[ignite] branch master updated: IGNITE-10379: SQL: Extract
partitions from BETWEEN and range expressions for integer data types with
constants. This closes #5768.
This is an automated email from the ASF dual-hosted git repository.
vozerov 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 239a760 IGNITE-10379: SQL: Extract partitions from BETWEEN and range expressions for integer data types with constants. This closes #5768.
239a760 is described below
commit 239a7609084f31c9ef4816e235195d33d142ee84
Author: alapin <la...@gmail.com>
AuthorDate: Wed Jan 16 17:16:04 2019 +0300
IGNITE-10379: SQL: Extract partitions from BETWEEN and range expressions for integer data types with constants. This closes #5768.
---
.../org/apache/ignite/IgniteSystemProperties.java | 7 +
.../query/h2/affinity/PartitionExtractor.java | 155 ++++-
.../processors/query/h2/sql/GridSqlColumn.java | 7 +
.../BetweenOperationExtractPartitionSelfTest.java | 759 +++++++++++++++++++++
.../query/h2/twostep/JoinSqlTestHelper.java | 23 +-
.../IgniteBinaryCacheQueryTestSuite.java | 2 +
6 files changed, 946 insertions(+), 7 deletions(-)
diff --git a/modules/core/src/main/java/org/apache/ignite/IgniteSystemProperties.java b/modules/core/src/main/java/org/apache/ignite/IgniteSystemProperties.java
index f58f1aa..3b3a00b 100644
--- a/modules/core/src/main/java/org/apache/ignite/IgniteSystemProperties.java
+++ b/modules/core/src/main/java/org/apache/ignite/IgniteSystemProperties.java
@@ -1082,6 +1082,13 @@ public final class IgniteSystemProperties {
public static final String IGNITE_DISCOVERY_DISABLE_CACHE_METRICS_UPDATE = "IGNITE_DISCOVERY_DISABLE_CACHE_METRICS_UPDATE";
/**
+ * Maximum number of different partitions to be extracted from between expression within sql query.
+ * In case of limit exceeding all partitions will be used.
+ */
+ public static final String IGNITE_SQL_MAX_EXTRACTED_PARTS_FROM_BETWEEN =
+ "IGNITE_SQL_MAX_EXTRACTED_PARTS_FROM_BETWEEN";
+
+ /**
* Enforces singleton.
*/
private IgniteSystemProperties() {
diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/affinity/PartitionExtractor.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/affinity/PartitionExtractor.java
index cf27d90..12549ce 100644
--- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/affinity/PartitionExtractor.java
+++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/affinity/PartitionExtractor.java
@@ -17,7 +17,11 @@
package org.apache.ignite.internal.processors.query.h2.affinity;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
import org.apache.ignite.IgniteCheckedException;
+import org.apache.ignite.IgniteSystemProperties;
import org.apache.ignite.internal.processors.cache.query.GridCacheSqlQuery;
import org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing;
import org.apache.ignite.internal.processors.query.h2.opt.GridH2Table;
@@ -34,19 +38,25 @@ import org.apache.ignite.internal.processors.query.h2.sql.GridSqlSelect;
import org.apache.ignite.internal.processors.query.h2.sql.GridSqlTable;
import org.apache.ignite.internal.util.typedef.F;
import org.h2.table.Column;
+import org.h2.value.Value;
import org.jetbrains.annotations.Nullable;
-import java.util.HashSet;
-import java.util.List;
-import java.util.Set;
-
/**
* Partition tree extractor.
*/
public class PartitionExtractor {
+ /**
+ * Maximum number of partitions to be used in case of between expression.
+ * In case of exceeding all partitions will be used.
+ */
+ private static final int DFLT_MAX_EXTRACTED_PARTS_FROM_BETWEEN = 16;
+
/** Indexing. */
private final IgniteH2Indexing idx;
+ /** Maximum number of partitions to be used in case of between expression. */
+ private final int maxPartsCntBetween;
+
/**
* Constructor.
*
@@ -54,6 +64,11 @@ public class PartitionExtractor {
*/
public PartitionExtractor(IgniteH2Indexing idx) {
this.idx = idx;
+
+ maxPartsCntBetween = Integer.getInteger(
+ IgniteSystemProperties.IGNITE_SQL_MAX_EXTRACTED_PARTS_FROM_BETWEEN,
+ DFLT_MAX_EXTRACTED_PARTS_FROM_BETWEEN
+ );
}
/**
@@ -203,6 +218,11 @@ public class PartitionExtractor {
private PartitionNode extractFromAnd(GridSqlOperation op) throws IgniteCheckedException {
assert op.size() == 2;
+ PartitionNode betweenNodes = tryExtractBetween(op);
+
+ if (betweenNodes != null)
+ return betweenNodes;
+
PartitionNode part1 = extractFromExpression(op.child(0));
PartitionNode part2 = extractFromExpression(op.child(1));
@@ -366,4 +386,131 @@ public class PartitionExtractor {
private static PartitionTableDescriptor descriptor(GridH2Table tbl) {
return new PartitionTableDescriptor(tbl.cacheName(), tbl.getName());
}
+
+ /**
+ * Try to extract partitions from {@code op} assuming that it's between operation or simple range.
+ *
+ * @param op Sql operation.
+ * @return {@code PartitionSingleNode} if operation reduced to one partition,
+ * {@code PartitionGroupNode} if operation reduced to multiple partitions or null if operation is neither
+ * between nor simple range. Null also returns if it's not possible to extract partitions from given operation.
+ * @throws IgniteCheckedException If failed.
+ */
+ private PartitionNode tryExtractBetween(GridSqlOperation op) throws IgniteCheckedException {
+ // Between operation (or similar range) should contain exact two children.
+ assert op.size() == 2;
+
+ GridSqlAst left = op.child();
+ GridSqlAst right = op.child(1);
+
+ GridSqlOperationType leftOpType = retrieveOperationType(left);
+ GridSqlOperationType rightOpType = retrieveOperationType(right);
+
+ if ((GridSqlOperationType.BIGGER == rightOpType || GridSqlOperationType.BIGGER_EQUAL == rightOpType) &&
+ (GridSqlOperationType.SMALLER == leftOpType || GridSqlOperationType.SMALLER_EQUAL == leftOpType)) {
+ GridSqlAst tmp = left;
+ left = right;
+ right = tmp;
+ }
+ else if (!((GridSqlOperationType.BIGGER == leftOpType || GridSqlOperationType.BIGGER_EQUAL == leftOpType) &&
+ (GridSqlOperationType.SMALLER == rightOpType || GridSqlOperationType.SMALLER_EQUAL == rightOpType)))
+ return null;
+
+ // Try parse left AST.
+ GridSqlColumn leftCol;
+
+ if (left instanceof GridSqlOperation && left.child() instanceof GridSqlColumn &&
+ (((GridSqlColumn)left.child()).column().getTable() instanceof GridH2Table))
+ leftCol = left.child();
+ else
+ return null;
+
+ // Try parse right AST.
+ GridSqlColumn rightCol;
+
+ if (right instanceof GridSqlOperation && right.child() instanceof GridSqlColumn)
+ rightCol = right.child();
+ else
+ return null;
+
+ GridH2Table tbl = (GridH2Table)leftCol.column().getTable();
+
+ // Check that columns might be used for partition pruning.
+ if(!tbl.isColumnForPartitionPruning(leftCol.column()))
+ return null;
+
+ // Check that both left and right AST use same column.
+ if (!F.eq(leftCol.schema(), rightCol.schema()) ||
+ !F.eq(leftCol.columnName(), rightCol.columnName()) ||
+ !F.eq(leftCol.tableAlias(), rightCol.tableAlias()))
+ return null;
+
+ // Check columns type
+ if (!(leftCol.column().getType() == Value.BYTE || leftCol.column().getType() == Value.SHORT ||
+ leftCol.column().getType() == Value.INT || leftCol.column().getType() == Value.LONG))
+ return null;
+
+ // Try parse left AST right value (value to the right of '>' or '>=').
+ GridSqlConst leftConst;
+
+ if (left.child(1) instanceof GridSqlConst)
+ leftConst = left.child(1);
+ else
+ return null;
+
+ // Try parse right AST right value (value to the right of '<' or '<=').
+ GridSqlConst rightConst;
+
+ if (right.child(1) instanceof GridSqlConst)
+ rightConst = right.child(1);
+ else
+ return null;
+
+ long leftLongVal;
+ long rightLongVal;
+
+ try {
+ leftLongVal = leftConst.value().getLong();
+ rightLongVal = rightConst.value().getLong();
+ }
+ catch (Exception e) {
+ return null;
+ }
+
+ // Increment left long value if '>' is used.
+ if (((GridSqlOperation)left).operationType() == GridSqlOperationType.BIGGER)
+ leftLongVal++;
+
+ // Decrement right long value if '<' is used.
+ if (((GridSqlOperation)right).operationType() == GridSqlOperationType.SMALLER)
+ rightLongVal--;
+
+ Set<PartitionSingleNode> parts = new HashSet<>();
+
+ PartitionTableDescriptor desc = descriptor(tbl);
+
+ for (long i = leftLongVal; i <= rightLongVal; i++) {
+ parts.add(new PartitionConstantNode(desc,
+ idx.kernalContext().affinity().partition((tbl).cacheName(), i)));
+
+ if (parts.size() > maxPartsCntBetween)
+ return null;
+ }
+
+ return parts.isEmpty() ? PartitionNoneNode.INSTANCE :
+ parts.size() == 1 ? parts.iterator().next() : new PartitionGroupNode(parts);
+ }
+
+ /**
+ * Retrieves operation type.
+ *
+ * @param ast Tree
+ * @return Operation type.
+ */
+ private GridSqlOperationType retrieveOperationType(GridSqlAst ast) {
+ if (!(ast instanceof GridSqlOperation))
+ return null;
+
+ return ((GridSqlOperation)ast).operationType();
+ }
}
diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java
index a39cf06..9c4d8c0 100644
--- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java
+++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java
@@ -92,6 +92,13 @@ public class GridSqlColumn extends GridSqlElement {
this.tblAlias = tblAlias;
}
+ /**
+ * @return Table alias.
+ */
+ public String tableAlias() {
+ return tblAlias;
+ }
+
/** {@inheritDoc} */
@Override public String getSQL() {
String sql = Parser.quoteIdentifier(colName);
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/BetweenOperationExtractPartitionSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/BetweenOperationExtractPartitionSelfTest.java
new file mode 100644
index 0000000..fbdbfb0
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/BetweenOperationExtractPartitionSelfTest.java
@@ -0,0 +1,759 @@
+/*
+ * 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.processors.query.h2.twostep;
+
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+import java.util.concurrent.ConcurrentHashMap;
+import java.util.stream.Collectors;
+import org.apache.ignite.IgniteCache;
+import org.apache.ignite.IgniteException;
+import org.apache.ignite.IgniteSystemProperties;
+import org.apache.ignite.cache.CacheMode;
+import org.apache.ignite.cache.QueryEntity;
+import org.apache.ignite.cache.query.FieldsQueryCursor;
+import org.apache.ignite.cache.query.SqlFieldsQuery;
+import org.apache.ignite.cluster.ClusterNode;
+import org.apache.ignite.configuration.CacheConfiguration;
+import org.apache.ignite.configuration.IgniteConfiguration;
+import org.apache.ignite.internal.managers.communication.GridIoMessage;
+import org.apache.ignite.internal.processors.query.h2.twostep.msg.GridH2QueryRequest;
+import org.apache.ignite.lang.IgniteInClosure;
+import org.apache.ignite.plugin.extensions.communication.Message;
+import org.apache.ignite.spi.IgniteSpiException;
+import org.apache.ignite.spi.communication.tcp.TcpCommunicationSpi;
+import org.apache.ignite.testframework.GridTestUtils;
+import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+
+/**
+ * Test checks partition extraction for between (where x between 10 and 17) and simple range (where x > 10 and x < 17)
+ * expressions.
+ */
+@RunWith(JUnit4.class)
+public class BetweenOperationExtractPartitionSelfTest extends GridCommonAbstractTest {
+ /** Nodes count. */
+ private static final int NODES_COUNT = 8;
+
+ /** Organizations count. */
+ private static final int ORG_COUNT = 10000;
+
+ /** Organizations cache name. */
+ private static final String ORG_CACHE_NAME = "orgBetweenTest";
+
+ /** Empty partitions array. */
+ private static final int[] EMPTY_PARTITIONS_ARRAY = new int[]{};
+
+ /** Between query. */
+ private static final String BETWEEN_QRY = "select * from Organization org where org._KEY between %d and %d";
+
+ /** Range query. */
+ private static final String RANGE_QRY = "select * from Organization org where org._KEY %s %d and org._KEY %s %d";
+
+ /** And + between query. */
+ private static final String AND_BETWEEN_QRY =
+ "select * from Organization org where org._KEY > 10 and org._KEY between %d and %d";
+
+ /** And + range query. */
+ private static final String AND_RANGE_QRY =
+ "select * from Organization org where org._KEY > 10 and org._KEY %s %d and org._KEY %s %d";
+
+ /** Between + and query. */
+ private static final String BETWEEN_AND_QRY =
+ "select * from Organization org where org._KEY between %d and %d and org._KEY > 10";
+
+ /** Range + and query. */
+ private static final String RANGE_AND_QRY =
+ "select * from Organization org where org._KEY %s %d and org._KEY %s %d and org._KEY > 10";
+
+ /** Between + between query. */
+ private static final String BETWEEN_AND_BETWEEN_QRY =
+ "select * from Organization org where org._KEY between %d and %d and org._KEY between 10 and 20";
+
+ /** Range + Range query. */
+ private static final String RANGE_AND_RANGE_QRY =
+ "select * from Organization org where org._KEY %s %d and org._KEY %s %d and org._KEY >= 10 and org._KEY <= 20";
+
+ /** Between + and + between query. */
+ private static final String BETWEEN_AND_AND_AND_BETWEEN_QRY =
+ "select * from Organization org where org._KEY between %d and %d and org._KEY < 30 and org._KEY" +
+ " between 10 and 20";
+
+ /** Range + and + Range query. */
+ private static final String RANGE_AND_AND_AND_RANGE_QRY =
+ "select * from Organization org where org._KEY %s %d and org._KEY %s %d and org._KEY < 30 and" +
+ " org._KEY >= 10 and org._KEY <= 20";
+
+ /** Between + or query. */
+ private static final String BETWEEN_OR_QRY =
+ "select * from Organization org where org._KEY between %d and %d or org._KEY < 5";
+
+ /** Range + or query. */
+ private static final String RANGE_OR_QRY =
+ "select * from Organization org where org._KEY %s %d and org._KEY %s %d or org._KEY < 5";
+
+ /** Between + or query. */
+ private static final String OR_BETWEEN_QRY =
+ "select * from Organization org where org._KEY < 5 or org._KEY between %d and %d";
+
+ /** Range + or query. */
+ private static final String OR_RANGE_QRY =
+ "select * from Organization org where org._KEY < 5 or org._KEY %s %d and org._KEY %s %d";
+
+ /** Between or between query. */
+ private static final String BETWEEN_OR_BETWEEN_QRY =
+ "select * from Organization org where org._KEY between %d and %d or org._KEY between 20 and 25";
+
+ /** Range or range query. */
+ private static final String RANGE_OR_RANGE_QRY =
+ "select * from Organization org where org._KEY %s %d and org._KEY %s %d or org._KEY >= 20 and org._KEY <= 25";
+
+ /** Range or range query. */
+ private static final String RANGE_OR_BETWEEN_QRY =
+ "select * from Organization org where org._KEY %s %d and org._KEY %s %d or org._KEY between 20 and 25";
+
+ /** Empty Range. */
+ private static final String EMPTY_RANGE_QRY =
+ "select * from Organization org where org._KEY %s %d and org._KEY %s %d";
+
+ /** Organizations cache. */
+ private static IgniteCache<Integer, JoinSqlTestHelper.Organization> orgCache;
+
+ /** Client mode. */
+ private boolean clientMode;
+
+ /** {@inheritDoc} */
+ @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception {
+ IgniteConfiguration cfg = super.getConfiguration(gridName);
+
+ cfg.setCommunicationSpi(new BetweenOperationExtractPartitionSelfTest.TestCommunicationSpi());
+
+ cfg.setClientMode(clientMode);
+
+ return cfg;
+ }
+
+ /**
+ * @return Query entity for Organization.
+ */
+ private static Collection<QueryEntity> organizationQueryEntity() {
+ QueryEntity entity = new QueryEntity(Integer.class, JoinSqlTestHelper.Organization.class);
+
+ entity.setKeyFieldName("ID");
+ entity.getFields().put("ID", String.class.getName());
+
+ return Collections.singletonList(entity);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void beforeTestsStarted() throws Exception {
+ startGridsMultiThreaded(NODES_COUNT - 1, false);
+
+ clientMode = true;
+
+ startGrid(NODES_COUNT);
+
+ orgCache = ignite(NODES_COUNT).getOrCreateCache(new CacheConfiguration<Integer, JoinSqlTestHelper.Organization>(ORG_CACHE_NAME)
+ .setCacheMode(CacheMode.PARTITIONED)
+ .setQueryEntities(organizationQueryEntity())
+ );
+
+ awaitPartitionMapExchange();
+
+ populateDataIntoOrg();
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTestsStopped() throws Exception {
+ orgCache = null;
+
+ stopAllGrids();
+
+ super.afterTestsStopped();
+ }
+
+ /**
+ * Check between expression with constant values.
+ */
+ @Test
+ public void testBetweenConst() {
+ // select * from Organization org where org._KEY between %d and %d.
+ //
+ // between
+ //
+ testBetweenConstOperator(BETWEEN_QRY, 1, 3, 3);
+ testBetweenConstOperator(BETWEEN_QRY, 5, 5, 1);
+ testBetweenConstOperator(BETWEEN_QRY, 7, 8, 2);
+
+ // select * from Organization org where org._KEY > 10 and org._KEY between %d and %d
+ //
+ // and
+ // / \
+ // / \
+ // > between
+ //
+ testBetweenConstOperator(AND_BETWEEN_QRY, 11, 13, 3);
+ testBetweenConstOperator(AND_BETWEEN_QRY, 15, 15, 1);
+ testBetweenConstOperator(AND_BETWEEN_QRY, 17, 18, 2);
+
+ // select * from Organization org where org._KEY between %d and %d and org._KEY > 10
+ //
+ // and
+ // / \
+ // / \
+ // between >
+ //
+ testBetweenConstOperator(BETWEEN_AND_QRY, 11, 13, 3);
+ testBetweenConstOperator(BETWEEN_AND_QRY, 15, 15, 1);
+ testBetweenConstOperator(BETWEEN_AND_QRY, 17, 18, 2);
+
+ // select * from Organization org where org._KEY between %d and %d and org._KEY between 10 and 20
+ //
+ // and
+ // / \
+ // / \
+ // between between
+ //
+ testBetweenConstOperator(BETWEEN_AND_BETWEEN_QRY, 11, 13, 3);
+ testBetweenConstOperator(BETWEEN_AND_BETWEEN_QRY, 15, 15, 1);
+ testBetweenConstOperator(BETWEEN_AND_BETWEEN_QRY, 17, 18, 2);
+
+ // select * from Organization org where org._KEY between %d and %d and org._KEY < 30
+ // and org._KEY between 10 and 20
+ //
+ // and
+ // / \
+ // / \
+ // between and
+ // / \
+ // / \
+ // < between
+ //
+ testBetweenConstOperator(BETWEEN_AND_AND_AND_BETWEEN_QRY, 11, 13, 3);
+ testBetweenConstOperator(BETWEEN_AND_AND_AND_BETWEEN_QRY, 15, 15, 1);
+ testBetweenConstOperator(BETWEEN_AND_AND_AND_BETWEEN_QRY, 17, 18, 2);
+
+ // select * from Organization org where org._KEY between %d and %d or org._KEY < 5
+ //
+ // or
+ // / \
+ // / \
+ // between <
+ //
+ testBetweenConstOperator(BETWEEN_OR_QRY, 11, 13, 8, EMPTY_PARTITIONS_ARRAY);
+
+ // select * from Organization org where org._KEY < 5 or org._KEY between %d and %d
+ //
+ // or
+ // / \
+ // / \
+ // < between
+ //
+ testBetweenConstOperator(OR_BETWEEN_QRY, 11, 13, 8, EMPTY_PARTITIONS_ARRAY);
+
+ // select * from Organization org where org._KEY between %d and %d or between 20 and 25
+ //
+ // or
+ // / \
+ // / \
+ // between between
+ //
+ testBetweenConstOperator(BETWEEN_OR_BETWEEN_QRY, 11, 13, 9,
+ 11, 12, 13, 20, 21, 22, 23, 24, 25);
+ }
+
+ /**
+ * Check range expression with constant values.
+ */
+ @Test
+ public void testRangeConst() {
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d
+ //
+ // >(=) <(=)
+ //
+ testRangeConstOperator(RANGE_QRY, 1, 3, 3, false);
+ testRangeConstOperator(RANGE_QRY, 5, 5, 1, false);
+ testRangeConstOperator(RANGE_QRY, 7, 8, 2, false);
+
+ // At the moment between-based-partition-pruning doesn't support range expression
+ // with any extra expressions because of optimisations that change expressions order:
+ // org where org._KEY > 10 and org._KEY > 11 and org._KEY < 13 converts to
+ // ((ORG__Z0._KEY < 13) AND ((ORG__Z0._KEY > 10) AND (ORG__Z0._KEY > 11)))
+ // So bellow we only check expected result rows count and not expected partitions matching.
+
+ // select * from Organization org where org._KEY > 10 and org._KEY %s %d and org._KEY %s %d
+ //
+ // and
+ // / \
+ // / \
+ // > and
+ // / \
+ // / \
+ // >(=) <(=)
+ //
+ testRangeConstOperator(AND_RANGE_QRY, 11, 13, 3, true);
+ testRangeConstOperator(AND_RANGE_QRY, 15, 15, 1, true);
+ testRangeConstOperator(AND_RANGE_QRY, 17, 18, 2, true);
+
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d and org._KEY > 10
+ //
+ // and
+ // / \
+ // / \
+ // and >
+ // / \
+ // / \
+ // >(=) <(=)
+ //
+ testRangeConstOperator(RANGE_AND_QRY, 11, 13, 3, true);
+ testRangeConstOperator(RANGE_AND_QRY, 15, 15, 1, true);
+ testRangeConstOperator(RANGE_AND_QRY, 17, 18, 2, true);
+
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d and org._KEY >= 10 and org._KEY <= 20
+ //
+ // and
+ // / \
+ // / \
+ // / \
+ // and and
+ // / \ / \
+ // / \ / \
+ // >(=) <(=) >(=) <(=)
+ //
+ testRangeConstOperator(RANGE_AND_RANGE_QRY, 11, 13, 3, true);
+ testRangeConstOperator(RANGE_AND_RANGE_QRY, 15, 15, 1, true);
+ testRangeConstOperator(RANGE_AND_RANGE_QRY, 17, 18, 2, true);
+
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d and org._KEY < 30 and
+ // org._KEY >= 10 and org._KEY <= 20
+ //
+ // and
+ // / \
+ // / \
+ // / \
+ // and and
+ // / \ / \
+ // / \ / \
+ // >(=) <(=) >(=) and
+ // / \
+ // / \
+ // >(=) <(=)
+ //
+ testRangeConstOperator(RANGE_AND_AND_AND_RANGE_QRY, 11, 13, 3, true);
+ testRangeConstOperator(RANGE_AND_AND_AND_RANGE_QRY, 15, 15, 1, true);
+ testRangeConstOperator(RANGE_AND_AND_AND_RANGE_QRY, 17, 18, 2, true);
+
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d or org._KEY < 5
+ //
+ // or
+ // / \
+ // / \
+ // and <
+ // / \
+ // / \
+ // >(=) <(=)
+ //
+ testRangeConstOperator(RANGE_OR_QRY, 11, 13, ">", "<", 6,
+ EMPTY_PARTITIONS_ARRAY);
+ testRangeConstOperator(RANGE_OR_QRY, 11, 13, ">=", "<", 7,
+ EMPTY_PARTITIONS_ARRAY);
+ testRangeConstOperator(RANGE_OR_QRY, 11, 13, ">", "<=", 7,
+ EMPTY_PARTITIONS_ARRAY);
+ testRangeConstOperator(RANGE_OR_QRY, 11, 13, ">=", "<=", 8,
+ EMPTY_PARTITIONS_ARRAY);
+
+ // select * from Organization org where org._KEY < 5 or org._KEY %s %d and org._KEY %s %d
+ //
+ // and
+ // / \
+ // / \
+ // < and
+ // / \
+ // / \
+ // >(=) <(=)
+ //
+ testRangeConstOperator(OR_RANGE_QRY, 11, 13, ">", "<", 6,
+ EMPTY_PARTITIONS_ARRAY);
+ testRangeConstOperator(OR_RANGE_QRY, 11, 13, ">=", "<", 7,
+ EMPTY_PARTITIONS_ARRAY);
+ testRangeConstOperator(OR_RANGE_QRY, 11, 13, ">", "<=", 7,
+ EMPTY_PARTITIONS_ARRAY);
+ testRangeConstOperator(OR_RANGE_QRY, 11, 13, ">=", "<=", 8,
+ EMPTY_PARTITIONS_ARRAY);
+
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d or org._KEY >= 20 and org._KEY <= 25
+ //
+ // or
+ // / \
+ // / \
+ // / \
+ // and and
+ // / \ / \
+ // / \ / \
+ // >(=) <(=) >(=) <(=)
+ //
+ testRangeConstOperator(RANGE_OR_RANGE_QRY, 11, 13, ">", "<", 7,
+ 12, 20, 21, 22, 23, 24, 25);
+ testRangeConstOperator(RANGE_OR_RANGE_QRY, 11, 13, ">=", "<", 8,
+ 11, 12, 20, 21, 22, 23, 24, 25);
+ testRangeConstOperator(RANGE_OR_RANGE_QRY, 11, 13, ">", "<=", 8,
+ 12, 13, 20, 21, 22, 23, 24, 25);
+ testRangeConstOperator(RANGE_OR_RANGE_QRY, 11, 13, ">=", "<=", 9,
+ 11, 12, 13, 20, 21, 22, 23, 24, 25);
+
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d or org._KEY between 20 and 25
+ //
+ // or
+ // / \
+ // / \
+ // / \
+ // and and
+ // / \ / \
+ // / \ / \
+ // >(=) <(=) >(=) <(=)
+ //
+ testRangeConstOperator(RANGE_OR_BETWEEN_QRY, 11, 13, ">", "<", 7,
+ 12, 20, 21, 22, 23, 24, 25);
+ testRangeConstOperator(RANGE_OR_BETWEEN_QRY, 11, 13, ">=", "<", 8,
+ 11, 12, 20, 21, 22, 23, 24, 25);
+ testRangeConstOperator(RANGE_OR_BETWEEN_QRY, 11, 13, ">", "<=", 8,
+ 12, 13, 20, 21, 22, 23, 24, 25);
+ testRangeConstOperator(RANGE_OR_BETWEEN_QRY, 11, 13, ">=", "<=", 9,
+ 11, 12, 13, 20, 21, 22, 23, 24, 25);
+
+ // select * from Organization org where org._KEY < %d and org._KEY > %d
+ //
+ // Empty range < >
+ //
+ testRangeConstOperator(EMPTY_RANGE_QRY, 11, 13, "<", ">", 0,
+ EMPTY_PARTITIONS_ARRAY);
+ }
+
+ /**
+ * Check between expression against non-affinity column.
+ */
+ @Test
+ public void testBetweenConstAgainstNonAffinityColumn() {
+ testBetweenConstOperator("select * from Organization org where org.debtCapital between %d and %d",
+ 1, 3, 3, EMPTY_PARTITIONS_ARRAY);
+ }
+
+ /**
+ * Check range expression against different columns.
+ */
+ @Test
+ public void testBetweenConstAgainstDifferentColumns() {
+ testRangeConstOperator("select * from Organization org where org._key %s %d and org.debtCapital %s %d",
+ 1, 3, ">=", "<=", 3, EMPTY_PARTITIONS_ARRAY);
+ }
+
+ /**
+ * Check default partitions limit exceeding.
+ */
+ @Test
+ public void testBetweenPartitionsDefaultLimitExceeding() {
+ // Default limit (16) not exceeded.
+ testBetweenConstOperator(BETWEEN_QRY, 1, 16, 16);
+
+ // Default limit (16) exceeded.
+ testBetweenConstOperator(BETWEEN_QRY, 1, 17, 17, EMPTY_PARTITIONS_ARRAY);
+ }
+
+ /**
+ * Check custom partitions limit exceeding.
+ */
+ @Test
+ public void testBetweenPartitionsCustomLimitExceeding() {
+ try (GridTestUtils.SystemProperty ignored = new GridTestUtils.
+ SystemProperty(IgniteSystemProperties.IGNITE_SQL_MAX_EXTRACTED_PARTS_FROM_BETWEEN, "4")){
+
+ // Default limit (16) not exceeded.
+ testBetweenConstOperator(BETWEEN_QRY, 1, 4, 4);
+
+ // Default limit (16) exceeded.
+ testBetweenConstOperator(BETWEEN_QRY, 1, 5, 5, EMPTY_PARTITIONS_ARRAY);
+ }
+ }
+
+ /**
+ * Check range expression with constant values.
+ */
+ @Test
+ public void testRevertedRangeConst() {
+ // select * from Organization org where org._KEY %s %d and org._KEY %s %d
+ //
+ // <(=) >(=)
+ //
+ testRevertedRangeConstOperator(3, 1, 3);
+ testRevertedRangeConstOperator(5, 5, 1);
+ testRevertedRangeConstOperator(8, 7, 2);
+ }
+
+ /**
+ * Check that given sql query with between expression returns expect rows count and that expected partitions set
+ * matches used one.
+ *
+ * @param sqlQry SQL query
+ * @param from Between from const.
+ * @param to Between to const.
+ * @param expResCnt Expected result rows count.
+ */
+ private void testBetweenConstOperator(String sqlQry, int from, int to, int expResCnt) {
+ TestCommunicationSpi commSpi = runQuery(sqlQry, from, to, expResCnt);
+
+ assertEquals(extractExpectedPartitions(from, to), commSpi.partitionsSet());
+ }
+
+ /**
+ * Check that given sql query with between expression returns expect rows count and that expected partitions set
+ * matches used one.
+ *
+ * @param sqlQry SQL query
+ * @param from Between from const.
+ * @param to Between to const.
+ * @param expResCnt Expected result rows count.
+ * @param expPartitions Expected partitions.
+ */
+ private void testBetweenConstOperator(String sqlQry, int from, int to, int expResCnt, int... expPartitions) {
+ TestCommunicationSpi commSpi = runQuery(sqlQry, from, to, expResCnt);
+
+ Set<Integer> expPartitionsSet = new HashSet<>();
+
+ for (int expPartition: expPartitions)
+ expPartitionsSet.add(expPartition);
+
+ assertEquals(expPartitionsSet, commSpi.partitionsSet());
+ }
+
+ /**
+ * Check that given sql query with between expression returns expect rows count and that expected partitions set
+ * matches used one.
+ *
+ * @param sqlQry SQL query
+ * @param const1 Range const1 const.
+ * @param const2 Range const2 const.
+ * @param expResCnt Expected result rows count.
+ * @param skipPartitionsCheck Skip partitions matching check.
+ */
+ private void testRangeConstOperator(String sqlQry, int const1, int const2, int expResCnt,
+ boolean skipPartitionsCheck) {
+ // Range: > <.
+ TestCommunicationSpi commSpi = runQuery(sqlQry, const1, const2, ">", "<",
+ expResCnt - 2);
+
+ if (!skipPartitionsCheck)
+ assertEquals(extractExpectedPartitions(const1 + 1, const2 - 1), commSpi.partitionsSet());
+
+ // Range: >= <.
+ commSpi = runQuery(sqlQry, const1, const2, ">=", "<", expResCnt - 1);
+
+ if (!skipPartitionsCheck)
+ assertEquals(extractExpectedPartitions(const1, const2 - 1), commSpi.partitionsSet());
+
+ // Range: > <=.
+ commSpi = runQuery(sqlQry, const1, const2, ">", "<=", expResCnt - 1);
+
+ if (!skipPartitionsCheck)
+ assertEquals(extractExpectedPartitions(const1 + 1, const2), commSpi.partitionsSet());
+
+ // Range: >= <=.
+ commSpi = runQuery(sqlQry, const1, const2, ">=", "<=", expResCnt);
+
+ if (!skipPartitionsCheck)
+ assertEquals(extractExpectedPartitions(const1, const2), commSpi.partitionsSet());
+ }
+
+ /**
+ * Check that given sql query with reverted range expression returns expect rows count and that expected partitions
+ * set matches used one.
+ *
+ * @param const1 Range const1 const.
+ * @param const2 Range const2 const.
+ * @param expResCnt Expected result rows count.
+ */
+ private void testRevertedRangeConstOperator(int const1, int const2, int expResCnt) {
+ // Range: < >.
+ TestCommunicationSpi commSpi = runQuery(RANGE_QRY, const1, const2, "<", ">",
+ expResCnt - 2);
+
+ assertEquals(extractExpectedPartitions(const2 + 1, const1 - 1), commSpi.partitionsSet());
+
+ // Range: <= >.
+ commSpi = runQuery(RANGE_QRY, const1, const2, "<=", ">", expResCnt - 1);
+
+ assertEquals(extractExpectedPartitions(const2 + 1, const1), commSpi.partitionsSet());
+
+ // Range: < >=.
+ commSpi = runQuery(RANGE_QRY, const1, const2, "<", ">=", expResCnt - 1);
+
+ assertEquals(extractExpectedPartitions(const2, const1 - 1), commSpi.partitionsSet());
+
+ // Range: <= >=.
+ commSpi = runQuery(RANGE_QRY, const1, const2, "<=", ">=", expResCnt);
+
+ assertEquals(extractExpectedPartitions(const2, const1), commSpi.partitionsSet());
+ }
+
+ /**
+ * Check that given sql query with range expression returns expect rows count and that expected partitions set
+ * matches used one.
+ *
+ * @param sqlQry SQL query
+ * @param from Range from const.
+ * @param to Range to const.
+ * @param expResCnt Expected result rows count.
+ * @param expPartitions Expected partitions.
+ */
+ private void testRangeConstOperator(String sqlQry, int from, int to, String leftOp, String rightOp,
+ int expResCnt, int... expPartitions) {
+ TestCommunicationSpi commSpi = runQuery(sqlQry, from, to, leftOp, rightOp, expResCnt);
+
+ assertEquals(Arrays.stream(expPartitions).boxed().collect(Collectors.toSet()), commSpi.partitionsSet());
+ }
+
+ /**
+ * Runs query and checks that given sql query with between expression returns expect rows count.
+ *
+ * @param sqlQry SQL query
+ * @param from Between from const.
+ * @param to Between to const.
+ * @param expResCnt Expected result rows count.
+ * @return Communication SPI for further assertions.
+ */
+ private BetweenOperationExtractPartitionSelfTest.TestCommunicationSpi runQuery(String sqlQry, int from, int to,
+ int expResCnt) {
+ TestCommunicationSpi commSpi =
+ (TestCommunicationSpi)grid(NODES_COUNT).configuration().
+ getCommunicationSpi();
+
+ commSpi.resetPartitions();
+
+ try (FieldsQueryCursor<List<?>> cur = orgCache.query(new SqlFieldsQuery(String.format(sqlQry, from, to)))) {
+ assertNotNull(cur);
+
+ List<List<?>> rows = cur.getAll();
+
+ assertEquals(expResCnt, rows.size());
+ }
+ return commSpi;
+ }
+
+ /**
+ * Runs query and checks that given sql query with between expression returns expect rows count.
+ *
+ * @param sqlQry SQL query
+ * @param from Between from const.
+ * @param to Between to const.
+ * @param expResCnt Expected result rows count.
+ * @return Communication SPI for further assertions.
+ */
+ private BetweenOperationExtractPartitionSelfTest.TestCommunicationSpi runQuery(String sqlQry, int from, int to,
+ String leftRangeOperand, String rightRangeOperand, int expResCnt) {
+
+ TestCommunicationSpi commSpi =
+ (TestCommunicationSpi)grid(NODES_COUNT).configuration().
+ getCommunicationSpi();
+
+ commSpi.resetPartitions();
+
+ try (FieldsQueryCursor<List<?>> cur = orgCache.query(new SqlFieldsQuery(String.format(sqlQry,
+ leftRangeOperand, from, rightRangeOperand, to)))) {
+ assertNotNull(cur);
+
+ List<List<?>> rows = cur.getAll();
+
+ assertEquals(Math.max(expResCnt, 0), rows.size());
+ }
+
+ return commSpi;
+ }
+
+ /**
+ * Extract expected partitions set from between from/to keys.
+ *
+ * @param keyFrom Key from.
+ * @param keyTo Key to.
+ * @return Expected set of partitions.
+ */
+ private Set<Integer> extractExpectedPartitions(int keyFrom, int keyTo) {
+ Set<Integer> partitions = new HashSet<>();
+
+ for (int i = keyFrom; i <= keyTo; i++)
+ partitions.add(ignite(0).affinity(ORG_CACHE_NAME).partition(i));
+
+ return partitions;
+ }
+
+ /**
+ * Populate organization cache with test data.
+ */
+ private void populateDataIntoOrg() {
+ for (int i = 0; i < ORG_COUNT; i++) {
+ JoinSqlTestHelper.Organization org = new JoinSqlTestHelper.Organization();
+
+ org.setName("Organization #" + i);
+ org.debtCapital(i);
+
+ orgCache.put(i, org);
+ }
+ }
+
+ /**
+ * Test communication SPI.
+ */
+ private static class TestCommunicationSpi extends TcpCommunicationSpi {
+ /** Used partitions. */
+ Set<Integer> partitions = ConcurrentHashMap.newKeySet();
+
+ /** {@inheritDoc} */
+ @Override public void sendMessage(ClusterNode node, Message msg, IgniteInClosure<IgniteException> ackC)
+ throws IgniteSpiException {
+
+ if (((GridIoMessage)msg).message() instanceof GridH2QueryRequest) {
+ GridH2QueryRequest gridH2QryReq = (GridH2QueryRequest)((GridIoMessage)msg).message();
+
+ if (gridH2QryReq.queryPartitions() != null) {
+ for (int partition : gridH2QryReq.queryPartitions())
+ partitions.add(partition);
+ }
+ }
+
+ super.sendMessage(node, msg, ackC);
+ }
+
+ /**
+ * @return Used partitons set.
+ */
+ Set<Integer> partitionsSet() {
+ return partitions;
+ }
+
+ /**
+ * Clear partitions set.
+ */
+ void resetPartitions() {
+ partitions.clear();
+ }
+ }
+}
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java
index 5eaf147..27f5be8 100644
--- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java
@@ -17,13 +17,12 @@
package org.apache.ignite.internal.processors.query.h2.twostep;
+import java.util.Collection;
+import java.util.Collections;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.QueryEntity;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
-import java.util.Collection;
-import java.util.Collections;
-
/**
* Join sql test helper
*/
@@ -145,6 +144,10 @@ public class JoinSqlTestHelper {
@QuerySqlField(index = true)
private String name;
+ /** Debt capital. */
+ @QuerySqlField
+ private Integer debtCapital;
+
/** */
public String getName() {
return name;
@@ -154,5 +157,19 @@ public class JoinSqlTestHelper {
public void setName(String name) {
this.name = name;
}
+
+ /**
+ * @return Debt capital.
+ */
+ public Integer debtCapital() {
+ return debtCapital;
+ }
+
+ /**
+ * @param debtCapital Debt capital.
+ */
+ public void debtCapital(Integer debtCapital) {
+ this.debtCapital = debtCapital;
+ }
}
}
diff --git a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java
index 386f095..ce8f6cc 100644
--- a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java
+++ b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java
@@ -213,6 +213,7 @@ import org.apache.ignite.internal.processors.query.h2.sql.GridQueryParsingTest;
import org.apache.ignite.internal.processors.query.h2.sql.H2CompareBigQueryDistributedJoinsTest;
import org.apache.ignite.internal.processors.query.h2.sql.H2CompareBigQueryTest;
import org.apache.ignite.internal.processors.query.h2.twostep.AndOperationExtractPartitionSelfTest;
+import org.apache.ignite.internal.processors.query.h2.twostep.BetweenOperationExtractPartitionSelfTest;
import org.apache.ignite.internal.processors.query.h2.twostep.InOperationExtractPartitionSelfTest;
import org.apache.ignite.internal.processors.sql.IgniteCachePartitionedAtomicColumnConstraintsTest;
import org.apache.ignite.internal.processors.sql.IgniteCachePartitionedTransactionalColumnConstraintsTest;
@@ -525,6 +526,7 @@ import org.junit.runners.Suite;
// Partition pruning.
InOperationExtractPartitionSelfTest.class,
AndOperationExtractPartitionSelfTest.class,
+ BetweenOperationExtractPartitionSelfTest.class,
GridCacheDynamicLoadOnClientTest.class,
GridCacheDynamicLoadOnClientPersistentTest.class,