This is an automated email from the ASF dual-hosted git repository.
ppa 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 b8e9c98465 IGNITE-21330 Sql. Support index scan for OR operator with
dynamic parameters (#3407)
b8e9c98465 is described below
commit b8e9c98465201c3449dd2aa25e3a07c389f01112
Author: Pavel Pereslegin <xxt...@gmail.com>
AuthorDate: Thu Mar 28 13:21:55 2024 +0300
IGNITE-21330 Sql. Support index scan for OR operator with dynamic
parameters (#3407)
---
.../engine/ItSecondaryIndexMultiRangeScanTest.java | 369 +++++++++++++++++++++
.../internal/sql/engine/ItSecondaryIndexTest.java | 4 +-
.../sql/engine/datatypes/uuid/ItUuidIndexTest.java | 7 -
.../sql/engine/exec/exp/ExpressionFactoryImpl.java | 196 +++++++++--
.../ignite/internal/sql/engine/util/RexUtils.java | 146 ++++++--
.../engine/exec/exp/ExpressionFactoryImplTest.java | 259 ++++++++++++++-
.../planner/IndexSearchBoundsPlannerTest.java | 33 +-
7 files changed, 935 insertions(+), 79 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexMultiRangeScanTest.java
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexMultiRangeScanTest.java
new file mode 100644
index 0000000000..9d6a5537af
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexMultiRangeScanTest.java
@@ -0,0 +1,369 @@
+/*
+ * 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;
+
+import static org.apache.ignite.internal.lang.IgniteStringFormatter.format;
+
+import java.util.Arrays;
+import java.util.List;
+import org.apache.ignite.internal.sql.BaseSqlIntegrationTest;
+import org.apache.ignite.internal.sql.engine.util.QueryChecker;
+import org.apache.ignite.internal.testframework.WithSystemProperty;
+import org.hamcrest.CoreMatchers;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/**
+ * Tests index multi-range scans (with SEARCH/SARG operator or with dynamic
parameters).
+ */
+@WithSystemProperty(key = "IMPLICIT_PK_ENABLED", value = "true")
+public class ItSecondaryIndexMultiRangeScanTest extends BaseSqlIntegrationTest
{
+ private static final String TEST_DISPLAY_NAME = "dynamicParams={0},
direction={1}";
+
+ @Override
+ protected int initialNodes() {
+ return 1;
+ }
+
+ /** {@inheritDoc} */
+ @BeforeAll
+ protected void prepare() {
+ sql("CREATE TABLE test_asc (c1 INTEGER, c2 VARCHAR, c3 INTEGER)");
+ sql("CREATE INDEX c1c2c3_asc ON test_asc(c1 ASC, c2 ASC, c3 ASC)");
+ sql("CREATE TABLE test_desc (c1 INTEGER, c2 VARCHAR, c3 INTEGER)");
+ sql("CREATE INDEX c1c2c3_desc ON test_desc(c1 DESC, c2 DESC, c3
DESC)");
+
+ for (String tbl : List.of("test_asc", "test_desc")) {
+ sql("INSERT INTO " + tbl + "(c1, c2, c3) VALUES (0, null, 0)");
+
+ for (int i = 0; i <= 5; i++) {
+ for (int j = 1; j <= 5; j++) {
+ sql("INSERT INTO " + tbl + "(c1, c2, c3) VALUES (?, ?,
?)", i == 0 ? null : i, Integer.toString(j), i * j);
+ }
+ }
+ }
+ }
+
+ @ParameterizedTest(name = TEST_DISPLAY_NAME)
+ @MethodSource("testParameters")
+ public void testIn(boolean useDynamicParameters, String direction) {
+ assertCondition(direction, useDynamicParameters,
+ "c1 IN (%s, %s) AND c2 IN (%s, %s) AND c3 IN (%s, %s)",
+ 2, 3, "2", "3", 6, 9)
+ .returns(2, "3", 6)
+ .returns(3, "2", 6)
+ .returns(3, "3", 9)
+ .check();
+
+ assertCondition(direction, useDynamicParameters,
+ "(c1 = %s OR c1 IS NULL) AND c2 IN (%s, %s) AND c3 IN (%s,
%s)",
+ 2, "2", "3", 0, 6)
+ .returns(null, "2", 0)
+ .returns(null, "3", 0)
+ .returns(2, "3", 6)
+ .check();
+ }
+
+ @ParameterizedTest(name = TEST_DISPLAY_NAME)
+ @MethodSource("testParameters")
+ public void testRange(boolean useDynamicParameters, String direction) {
+ assertCondition(direction, useDynamicParameters,
+ "((c1 > %s AND c1 < %s) OR (c1 > %s AND c1 < %s)) AND c2 > %s
AND c2 < %s",
+ 1, 3, 3, 5, "2", "5")
+ .returns(2, "3", 6)
+ .returns(2, "4", 8)
+ .returns(4, "3", 12)
+ .returns(4, "4", 16)
+ .check();
+
+ assertCondition(direction, useDynamicParameters,
+ "c1 IN (%s, %s) AND ((c2 >= %s AND c2 < %s) OR (c2 > %s AND c1
<= %s))",
+ 1, 2, "2", "3", "4", 5)
+ .returns(1, "2", 2)
+ .returns(1, "5", 5)
+ .returns(2, "2", 4)
+ .returns(2, "5", 10)
+ .check();
+
+ assertCondition(direction, useDynamicParameters,
+ "c1 IN (%s, %s) AND (c2 < %s OR (c2 >= %s AND c2 < %s)
OR (c2 > %s AND c2 < %s) OR c2 > %s)",
+ 1, 2, "1", "2", "3", "2", "4", "5")
+ .returns(1, "2", 2)
+ .returns(1, "3", 3)
+ .returns(2, "2", 4)
+ .returns(2, "3", 6)
+ .check();
+
+ assertCondition(direction, useDynamicParameters,
+ "c1 = %s AND c2 > %s AND c3 IN (%s, %s)",
+ 4, "3", 16, 20)
+ .returns(4, "4", 16)
+ .returns(4, "5", 20)
+ .check();
+
+ assertCondition(direction, useDynamicParameters,
+ "(c1 < %s OR c1 >= %s) AND c2 = c1",
+ 2, 4)
+ .returns(1, "1", 1)
+ .returns(4, "4", 16)
+ .returns(5, "5", 25)
+ .check();
+ }
+
+ @ParameterizedTest(name = TEST_DISPLAY_NAME)
+ @MethodSource("testParameters")
+ public void testNulls(boolean useDynamicParameters, String direction) {
+ assertCondition(direction, useDynamicParameters, "c1 IS NULL AND c2 <=
%s", "1")
+ .returns(null, "1", 0)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "(c1 IS NULL OR c1 =
%s) AND c2 = %s AND c3 in (%s, %s)",
+ 3, "1", 0, 3)
+ .returns(null, "1", 0)
+ .returns(3, "1", 3)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "(c1 IS NULL OR c1 <
%s) AND c2 = %s AND c3 in (%s, %s)",
+ 3, "1", 0, 1)
+ .returns(null, "1", 0)
+ .returns(1, "1", 1)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "(c1 IS NULL OR c1 >
%s) AND c2 = %s AND c3 in (%s, %s)",
+ 3, "5", 0, 25)
+ .returns(null, "5", 0)
+ .returns(5, "5", 25)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 IS NOT NULL AND
c2 IS NULL")
+ .returns(0, null, 0)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 IN(NULL, %s) AND
c2 = %s", 1, "1")
+ .returns(1, "1", 1)
+ .check();
+ }
+
+ /** Tests not supported range index scan conditions. */
+ @ParameterizedTest(name = "dynamicParams={0}")
+ @ValueSource(booleans = {true, false})
+ public void testNot(boolean useDynamicParameters) {
+ assertQuery(useDynamicParameters, "SELECT * FROM test_asc WHERE c1 <>
%s AND c3 = %s", 1, 6)
+ .matches(QueryChecker.containsTableScan("PUBLIC", "TEST_ASC"))
// Can't use index scan.
+ .returns(2, "3", 6)
+ .returns(3, "2", 6)
+ .check();
+
+ assertQuery(useDynamicParameters, "SELECT * FROM test_asc WHERE c1 NOT
IN (%s, %s, %s) AND c2 NOT IN (%s, %s, %s)",
+ 1, 2, 5, 1, 2, 5)
+ .matches(QueryChecker.containsTableScan("PUBLIC", "TEST_ASC"))
// Can't use index scan.
+ .returns(3, "3", 9)
+ .returns(3, "4", 12)
+ .returns(4, "3", 12)
+ .returns(4, "4", 16)
+ .check();
+ }
+
+ /** Test correct index ordering without additional sorting. */
+ @ParameterizedTest(name = "dynamicParams={0}")
+ @ValueSource(booleans = {true, false})
+ public void testOrdering(boolean useDynamicParameters) {
+ assertQuery(useDynamicParameters,
+ "SELECT /*+ FORCE_INDEX(c1c2c3_ASC) */ * FROM test_asc WHERE
c1 IN (%s, %s) AND c2 IN (%s, %s) ORDER BY c1, c2, c3",
+ 3, 2, "3", "2")
+
.matches(CoreMatchers.not(QueryChecker.containsSubPlan("Sort"))) // Don't
require additional sorting.
+ .ordered()
+ .returns(2, "2", 4)
+ .returns(2, "3", 6)
+ .returns(3, "2", 6)
+ .returns(3, "3", 9)
+ .check();
+
+ assertQuery(useDynamicParameters,
+ "SELECT /*+ FORCE_INDEX(c1c2c3_ASC) */ * FROM test_asc WHERE
c1 IN (%s, %s) AND c2 < %s ORDER BY c1, c2, c3", 2, 3, "3")
+
.matches(CoreMatchers.not(QueryChecker.containsSubPlan("Sort"))) // Don't
require additional sorting.
+ .ordered()
+ .returns(2, "1", 2)
+ .returns(2, "2", 4)
+ .returns(3, "1", 3)
+ .returns(3, "2", 6)
+ .check();
+
+ assertQuery(useDynamicParameters,
+ "SELECT /*+ FORCE_INDEX(c1c2c3_ASC) */ * FROM test_asc"
+ + " WHERE c1 IN (%s, %s) AND c2 IN (%s, %s) AND c3
BETWEEN %s AND %s ORDER BY c1, c2, c3",
+ 2, 3, "2", "3", 5, 7)
+
.matches(CoreMatchers.not(QueryChecker.containsSubPlan("Sort"))) // Don't
require additional sorting.
+ .ordered()
+ .returns(2, "3", 6)
+ .returns(3, "2", 6)
+ .check();
+
+ // Check order for table with DESC ordering.
+ assertQuery(useDynamicParameters,
+ "SELECT /*+ FORCE_INDEX(c1c2c3_DESC) */ * FROM test_desc"
+ + " WHERE c1 IN (%s, %s) AND c2 IN (%s, %s) ORDER BY
c1 DESC, c2 DESC, c3 DESC",
+ 3, 2, "3", "2")
+
.matches(CoreMatchers.not(QueryChecker.containsSubPlan("Sort"))) // Don't
require additional sorting.
+ .ordered()
+ .returns(3, "3", 9)
+ .returns(3, "2", 6)
+ .returns(2, "3", 6)
+ .returns(2, "2", 4)
+ .check();
+
+ assertQuery(useDynamicParameters,
+ "SELECT /*+ FORCE_INDEX(c1c2c3_DESC) */ * FROM test_desc"
+ + " WHERE c1 IN (%s, %s) AND c2 < %s ORDER BY c1 DESC,
c2 DESC, c3 DESC",
+ 2, 3, "3")
+
.matches(CoreMatchers.not(QueryChecker.containsSubPlan("Sort"))) // Don't
require additional sorting.
+ .ordered()
+ .returns(3, "2", 6)
+ .returns(3, "1", 3)
+ .returns(2, "2", 4)
+ .returns(2, "1", 2)
+ .check();
+
+ assertQuery(useDynamicParameters,
+ "SELECT /*+ FORCE_INDEX(c1c2c3_DESC) */ * FROM test_desc"
+ + " WHERE c1 IN (%s, %s) AND c2 IN (%s, %s) AND c3
BETWEEN %s AND %s"
+ + " ORDER BY c1 DESC, c2 DESC, c3 DESC",
+ 2, 3, "2", "3", 5, 7)
+
.matches(CoreMatchers.not(QueryChecker.containsSubPlan("Sort"))) // Don't
require additional sorting.
+ .ordered()
+ .returns(3, "2", 6)
+ .returns(2, "3", 6)
+ .check();
+ }
+
+ /** Tests not supported range index scan conditions. */
+ @ParameterizedTest(name = TEST_DISPLAY_NAME)
+ @MethodSource("testParameters")
+ public void testRangeIntersection(boolean useDynamicParameters, String
direction) {
+ assertCondition(direction, useDynamicParameters, "c1 IN (%s, %s, %s,
%s) and c3 in (%s, %s, %s, %s)",
+ 3, 4, 4, 3, 12, 9, 16, 12)
+ .returns(3, "3", 9)
+ .returns(3, "4", 12)
+ .returns(4, "3", 12)
+ .returns(4, "4", 16)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 IN (%s, %s) "
+ + "AND ((c2 > %s AND c2 < %s) OR (c2 > %s AND c2 <
%s))",
+ 3, 4, "1", "4", "3", "5")
+ .returns(3, "2", 6)
+ .returns(3, "3", 9)
+ .returns(3, "4", 12)
+ .returns(4, "2", 8)
+ .returns(4, "3", 12)
+ .returns(4, "4", 16)
+ .check();
+
+ // Different combinations of LESS_THAN and LESS_THAN_OR_EQUAL.
+ assertCondition(direction, useDynamicParameters, "c1 IN (%s, %s) AND
(c2 < %s OR c2 < %s)",
+ 3, 4, "1", "2")
+ .returns(3, "1", 3)
+ .returns(4, "1", 4)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 IN (%s, %s) AND
(c2 <= %s OR c2 < %s)",
+ 3, 4, "1", "2")
+ .returns(3, "1", 3)
+ .returns(4, "1", 4)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 IN (%s, %s) AND
(c2 <= %s OR c2 <= %s)",
+ 3, 4, "1", "2")
+ .returns(3, "1", 3)
+ .returns(3, "2", 6)
+ .returns(4, "1", 4)
+ .returns(4, "2", 8)
+ .check();
+
+ // Different combinations of LESS_THAN, LESS_THAN_OR_EQUAL,
GREATER_THAN, GREATER_THAN_OR_EQUAL.
+ assertCondition(direction, useDynamicParameters,
+ "c1 IN (%s, %s) AND ((c2 > %s AND c2 <= %s) OR (c2 > %s AND c2
<= %s) OR (c2 >= %s AND c2 < %s))",
+ 1, 2, "1", "2", "2", "3", "3", "4")
+ .returns(1, "2", 2)
+ .returns(1, "3", 3)
+ .returns(2, "2", 4)
+ .returns(2, "3", 6)
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 IN (%s, %s) AND
c2 BETWEEN %s AND %s",
+ 3, 3, "2", "4")
+ .returns(3, "2", 6)
+ .returns(3, "3", 9)
+ .returns(3, "4", 12)
+ .check();
+ }
+
+ @ParameterizedTest(name = TEST_DISPLAY_NAME)
+ @MethodSource("testParameters")
+ public void testInvalidRange(boolean useDynamicParameters, String
direction) {
+ assertCondition(direction, useDynamicParameters, "c1 BETWEEN %s AND
%s", 4, 3)
+ .returnNothing()
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 = %s AND c2 > %s
AND c2 < %s", 1, "2", "2")
+ .returnNothing()
+ .check();
+
+ assertCondition(direction, useDynamicParameters, "c1 = %s OR c1
BETWEEN %s AND %s", 1, 3, 2)
+ .returns(1, "1", 1)
+ .returns(1, "2", 2)
+ .returns(1, "3", 3)
+ .returns(1, "4", 4)
+ .returns(1, "5", 5)
+ .check();
+ }
+
+ private static QueryChecker assertCondition(String direction, boolean
dynamicParams, String condition, Object... params) {
+ String sqlPattern = format("SELECT /*+ FORCE_INDEX(c1c2c3_{}) */ *
FROM test_{} WHERE {}", direction, direction, condition);
+
+ return assertQuery(dynamicParams, sqlPattern, params);
+ }
+
+ private static QueryChecker assertQuery(boolean dynamicParams, String
sqlPattern, Object... params) {
+ Object[] args = new Object[params.length];
+
+ if (dynamicParams) {
+ Arrays.fill(args, "?");
+
+ return assertQuery(String.format(sqlPattern,
args)).withParams(params);
+ }
+
+ for (int i = 0; i < params.length; i++) {
+ args[i] = params[i] instanceof String ? '\'' +
params[i].toString() + '\'' : params[i].toString();
+ }
+
+ return assertQuery(String.format(sqlPattern, args));
+ }
+
+ private static List<Arguments> testParameters() {
+ return List.of(
+ Arguments.of(true, "ASC"),
+ Arguments.of(true, "DESC"),
+ Arguments.of(false, "ASC"),
+ Arguments.of(false, "DESC")
+ );
+ }
+}
diff --git
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
index d33ace2634..b0378fdeff 100644
---
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
+++
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
@@ -560,11 +560,9 @@ public class ItSecondaryIndexTest extends
BaseSqlIntegrationTest {
}
@Test
- @Disabled("https://issues.apache.org/jira/browse/IGNITE-21287";)
public void testOrCondition4() {
assertQuery("SELECT * FROM Developer WHERE depId=1 OR (name='Mozart'
AND depId=3)")
- .matches(containsUnion(true))
- .matches(containsIndexScan("PUBLIC", "DEVELOPER",
NAME_DEPID_CITY_IDX))
+ .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
.returns(1, "Mozart", 3, "Vienna", 33)
.returns(3, "Bach", 1, "Leipzig", 55)
.check();
diff --git
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/datatypes/uuid/ItUuidIndexTest.java
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/datatypes/uuid/ItUuidIndexTest.java
index e1b878f415..54a7904146 100644
---
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/datatypes/uuid/ItUuidIndexTest.java
+++
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/datatypes/uuid/ItUuidIndexTest.java
@@ -22,7 +22,6 @@ import
org.apache.ignite.internal.sql.engine.datatypes.DataTypeTestSpecs;
import
org.apache.ignite.internal.sql.engine.datatypes.tests.BaseIndexDataTypeTest;
import org.apache.ignite.internal.sql.engine.datatypes.tests.DataTypeTestSpec;
import org.apache.ignite.internal.sql.engine.type.UuidType;
-import org.junit.jupiter.api.Disabled;
/**
* Tests for queries that use indexes with {@link UuidType UUID data type}.
@@ -34,10 +33,4 @@ public class ItUuidIndexTest extends
BaseIndexDataTypeTest<UUID> {
protected DataTypeTestSpec<UUID> getTypeSpec() {
return DataTypeTestSpecs.UUID_TYPE;
}
-
- @Override
- @Disabled("https://issues.apache.org/jira/browse/IGNITE-21330";)
- public void testInLookUp() {
- super.testInLookUp();
- }
}
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImpl.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImpl.java
index 6e4e604ca1..75737052cf 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImpl.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImpl.java
@@ -50,6 +50,7 @@ import org.apache.calcite.linq4j.tree.ParameterExpression;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rel.RelFieldCollation.Direction;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
@@ -145,28 +146,38 @@ public class ExpressionFactoryImpl<RowT> implements
ExpressionFactory<RowT> {
return (o1, o2) -> {
RowHandler<RowT> hnd = ctx.rowHandler();
+ List<RelFieldCollation> collations =
collation.getFieldCollations();
- for (RelFieldCollation field : collation.getFieldCollations()) {
- int fieldIdx = field.getFieldIndex();
- int nullComparison = field.nullDirection.nullComparison;
+ int colsCountRow1 = hnd.columnCount(o1);
+ int colsCountRow2 = hnd.columnCount(o2);
- if (o1 == null || o2 == null) {
- if (o1 == o2) {
- return 0;
- } else if (o1 == null) {
- return nullComparison;
- } else {
- return -nullComparison;
- }
+ // The index range condition can contain the prefix of the index
columns (not all index columns).
+ int maxCols = Math.min(Math.max(colsCountRow1, colsCountRow2),
collations.size());
+
+ for (int i = 0; i < maxCols; i++) {
+ RelFieldCollation field = collations.get(i);
+ boolean ascending = field.direction == Direction.ASCENDING;
+
+ if (i == colsCountRow1) {
+ // There is no more values in first row.
+ return ascending ? -1 : 1;
}
+ if (i == colsCountRow2) {
+ // There is no more values in second row.
+ return ascending ? 1 : -1;
+ }
+
+ int fieldIdx = field.getFieldIndex();
+
Object c1 = hnd.get(fieldIdx, o1);
Object c2 = hnd.get(fieldIdx, o2);
- int res = (field.direction ==
RelFieldCollation.Direction.ASCENDING)
- ?
- compare(c1, c2, nullComparison) :
- compare(c2, c1, -nullComparison);
+ int nullComparison = field.nullDirection.nullComparison;
+
+ int res = ascending
+ ? compare(c1, c2, nullComparison)
+ : compare(c2, c1, -nullComparison);
if (res != 0) {
return res;
@@ -332,7 +343,6 @@ public class ExpressionFactoryImpl<RowT> implements
ExpressionFactory<RowT> {
return rows;
}
-
/** {@inheritDoc} */
@Override
public RangeIterable<RowT> ranges(
@@ -434,14 +444,12 @@ public class ExpressionFactoryImpl<RowT> implements
ExpressionFactory<RowT> {
.factory(TypeUtils.rowSchemaFromRelTypes(RelOptUtil.getFieldTypeList(upperType)));
ranges.add(new RangeConditionImpl(
- curLower,
- curUpper,
+ nullOrEmpty(curLower) ? null : scalar(curLower, lowerType),
+ nullOrEmpty(curUpper) ? null : scalar(curUpper, upperType),
lowerInclude,
upperInclude,
- lowerType,
- upperType,
- lowerFactory,
- upperFactory
+ lowerFactory.rowBuilder(),
+ upperFactory.rowBuilder()
));
return;
@@ -810,22 +818,20 @@ public class ExpressionFactoryImpl<RowT> implements
ExpressionFactory<RowT> {
private final RowBuilder<RowT> upperRowBuilder;
private RangeConditionImpl(
- List<RexNode> lower,
- List<RexNode> upper,
+ @Nullable SingleScalar lowerScalar,
+ @Nullable SingleScalar upperScalar,
boolean lowerInclude,
boolean upperInclude,
- RelDataType rowTypeLower,
- RelDataType rowTypeUpper,
- RowFactory<RowT> lowerFactory,
- RowFactory<RowT> upperFactory
+ RowBuilder<RowT> lowerRowBuilder,
+ RowBuilder<RowT> upperRowBuilder
) {
- this.lowerBound = nullOrEmpty(lower) ? null : scalar(lower,
rowTypeLower);
- this.upperBound = nullOrEmpty(upper) ? null : scalar(upper,
rowTypeUpper);
+ this.lowerBound = lowerScalar;
+ this.upperBound = upperScalar;
this.lowerInclude = lowerInclude;
this.upperInclude = upperInclude;
- this.lowerRowBuilder = lowerFactory.rowBuilder();
- this.upperRowBuilder = upperFactory.rowBuilder();
+ this.lowerRowBuilder = lowerRowBuilder;
+ this.upperRowBuilder = upperRowBuilder;
}
/** {@inheritDoc} */
@@ -875,7 +881,7 @@ public class ExpressionFactoryImpl<RowT> implements
ExpressionFactory<RowT> {
}
private class RangeIterableImpl implements RangeIterable<RowT> {
- private final List<RangeCondition<RowT>> ranges;
+ private List<RangeCondition<RowT>> ranges;
private final @Nullable Comparator<RowT> comparator;
@@ -908,12 +914,134 @@ public class ExpressionFactoryImpl<RowT> implements
ExpressionFactory<RowT> {
// Do not sort again if ranges already were sorted before,
different values of correlated variables
// should not affect ordering.
if (!sorted && comparator != null) {
- ranges.sort((o1, o2) -> comparator.compare(o1.lower(),
o2.lower()));
+ ranges.sort(this::compareRanges);
+
+ List<RangeCondition<RowT>> ranges0 = new
ArrayList<>(ranges.size());
+
+ RangeConditionImpl prevRange = null;
+
+ for (RangeCondition<RowT> range0 : ranges) {
+ RangeConditionImpl range = (RangeConditionImpl) range0;
+
+ if (compareLowerAndUpperBounds(range.lower(),
range.upper()) > 0) {
+ // Invalid range (low > up).
+ continue;
+ }
+
+ if (prevRange != null) {
+ RangeConditionImpl merged = tryMerge(prevRange, range);
+
+ if (merged == null) {
+ ranges0.add(prevRange);
+ } else {
+ range = merged;
+ }
+ }
+
+ prevRange = range;
+ }
+
+ if (prevRange != null) {
+ ranges0.add(prevRange);
+ }
+
+ ranges = ranges0;
sorted = true;
}
return ranges.iterator();
}
+
+ private int compareRanges(RangeCondition<RowT> first,
RangeCondition<RowT> second) {
+ int cmp = compareBounds(first.lower(), second.lower(), true);
+
+ if (cmp != 0) {
+ return cmp;
+ }
+
+ return compareBounds(first.upper(), second.upper(), false);
+ }
+
+ private int compareBounds(@Nullable RowT row1, @Nullable RowT row2,
boolean lower) {
+ assert comparator != null;
+
+ if (row1 == null || row2 == null) {
+ if (row1 == row2) {
+ return 0;
+ }
+
+ RowT row = lower ? row2 : row1;
+
+ return row == null ? 1 : -1;
+ }
+
+ return comparator.compare(row1, row2);
+ }
+
+ private int compareLowerAndUpperBounds(@Nullable RowT lower, @Nullable
RowT upper) {
+ assert comparator != null;
+
+ if (lower == null || upper == null) {
+ if (lower == upper) {
+ return 0;
+ } else {
+ // lower = null -> lower < any upper
+ // upper = null -> upper > any lower
+ return -1;
+ }
+ }
+
+ return comparator.compare(lower, upper);
+ }
+
+ /** Returns combined range if the provided ranges intersect, {@code
null} otherwise. */
+ @Nullable RangeConditionImpl tryMerge(RangeConditionImpl first,
RangeConditionImpl second) {
+ if (compareLowerAndUpperBounds(first.lower(), second.upper()) > 0
+ || compareLowerAndUpperBounds(second.lower(),
first.upper()) > 0) {
+ // Ranges are not intersect.
+ return null;
+ }
+
+ SingleScalar newLowerBound;
+ RowT newLowerRow;
+ boolean newLowerInclude;
+
+ int cmp = compareBounds(first.lower(), second.lower(), true);
+
+ if (cmp < 0 || (cmp == 0 && first.lowerInclude())) {
+ newLowerBound = first.lowerBound;
+ newLowerRow = first.lower();
+ newLowerInclude = first.lowerInclude();
+ } else {
+ newLowerBound = second.lowerBound;
+ newLowerRow = second.lower();
+ newLowerInclude = second.lowerInclude();
+ }
+
+ SingleScalar newUpperBound;
+ RowT newUpperRow;
+ boolean newUpperInclude;
+
+ cmp = compareBounds(first.upper(), second.upper(), false);
+
+ if (cmp > 0 || (cmp == 0 && first.upperInclude())) {
+ newUpperBound = first.upperBound;
+ newUpperRow = first.upper();
+ newUpperInclude = first.upperInclude();
+ } else {
+ newUpperBound = second.upperBound;
+ newUpperRow = second.upper();
+ newUpperInclude = second.upperInclude();
+ }
+
+ RangeConditionImpl newRangeCondition = new
RangeConditionImpl(newLowerBound, newUpperBound,
+ newLowerInclude, newUpperInclude, first.lowerRowBuilder,
first.upperRowBuilder);
+
+ newRangeCondition.lowerRow = newLowerRow;
+ newRangeCondition.upperRow = newUpperRow;
+
+ return newRangeCondition;
+ }
}
private class BiFieldGetter extends CommonFieldGetter {
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/RexUtils.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/RexUtils.java
index 6dffd47ec0..a237de02f5 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/RexUtils.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/RexUtils.java
@@ -29,6 +29,7 @@ import static org.apache.calcite.sql.SqlKind.IS_NULL;
import static org.apache.calcite.sql.SqlKind.LESS_THAN;
import static org.apache.calcite.sql.SqlKind.LESS_THAN_OR_EQUAL;
import static org.apache.calcite.sql.SqlKind.NOT;
+import static org.apache.calcite.sql.SqlKind.OR;
import static org.apache.calcite.sql.SqlKind.SEARCH;
import static org.apache.ignite.internal.util.CollectionUtils.nullOrEmpty;
@@ -44,6 +45,7 @@ import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
+import java.util.Comparator;
import java.util.EnumSet;
import java.util.HashSet;
import java.util.List;
@@ -82,6 +84,7 @@ import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.util.ControlFlowException;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Litmus;
+import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Sarg;
import org.apache.calcite.util.Util;
import org.apache.calcite.util.mapping.MappingType;
@@ -413,6 +416,18 @@ public class RexUtils {
boolean upperInclude = true;
boolean lowerInclude = true;
+ // Give priority to equality operators.
+ collFldPreds.sort(Comparator.comparingInt(pred -> {
+ switch (pred.getOperator().getKind()) {
+ case EQUALS:
+ case IS_NOT_DISTINCT_FROM:
+ case IS_NULL:
+ return 0;
+ default:
+ return 1;
+ }
+ }));
+
for (RexCall pred : collFldPreds) {
RexNode val = null;
RexNode ref = pred.getOperands().get(0);
@@ -429,6 +444,33 @@ public class RexUtils {
return new ExactBounds(pred, val);
} else if (op.kind == IS_NULL) {
return new ExactBounds(pred, nullValue);
+ } else if (op.kind == OR) {
+ List<SearchBounds> orBounds = new ArrayList<>();
+ int curComplexity = 0;
+
+ for (RexNode operand : pred.getOperands()) {
+ SearchBounds opBounds = createBounds(fc,
Collections.singletonList((RexCall) operand),
+ cluster, fldType, prevComplexity);
+
+ if (opBounds instanceof MultiBounds) {
+ curComplexity += ((MultiBounds)
opBounds).bounds().size();
+ orBounds.addAll(((MultiBounds) opBounds).bounds());
+ } else if (opBounds != null) {
+ curComplexity++;
+ orBounds.add(opBounds);
+ }
+
+ if (opBounds == null || curComplexity >
MAX_SEARCH_BOUNDS_COMPLEXITY) {
+ orBounds = null;
+ break;
+ }
+ }
+
+ if (orBounds == null) {
+ continue;
+ }
+
+ return new MultiBounds(pred, orBounds);
} else if (op.kind == SEARCH) {
Sarg<?> sarg = ((RexLiteral)
pred.operands.get(1)).getValueAs(Sarg.class);
@@ -614,40 +656,104 @@ public class RexUtils {
Int2ObjectMap<List<RexCall>> res = new
Int2ObjectOpenHashMap<>(conjunctions.size());
for (RexNode rexNode : conjunctions) {
- rexNode = expandBooleanFieldComparison(rexNode, builder(cluster));
+ Pair<Integer, RexCall> refPredicate = null;
- if (!isSupportedTreeComparison(rexNode)) {
- continue;
- }
+ if (rexNode instanceof RexCall && rexNode.getKind() == OR) {
+ List<RexNode> operands = ((RexCall) rexNode).getOperands();
- RexCall predCall = (RexCall) rexNode;
- RexSlot ref;
+ Integer ref = null;
+ List<RexCall> preds = new ArrayList<>(operands.size());
- if (isBinaryComparison(rexNode)) {
- ref = extractRefFromBinary(predCall, cluster);
+ for (RexNode operand : operands) {
+ Pair<Integer, RexCall> operandRefPredicate =
extractRefPredicate(operand, cluster);
- if (ref == null) {
- continue;
+ // Skip the whole OR condition if any operand does not
support tree comparison or not on reference.
+ if (operandRefPredicate == null) {
+ ref = null;
+ break;
+ }
+
+ // Ensure that we have the same field reference in all
operands.
+ if (ref == null) {
+ ref = operandRefPredicate.getKey();
+ } else if (!ref.equals(operandRefPredicate.getKey())) {
+ ref = null;
+ break;
+ }
+
+ // For correlated variables it's required to resort and
merge ranges on each nested loop,
+ // don't support it now.
+ if (containsFieldAccess(operandRefPredicate.getValue())) {
+ ref = null;
+ break;
+ }
+
+ preds.add(operandRefPredicate.getValue());
}
- // Let RexLocalRef be on the left side.
- if (refOnTheRight(predCall)) {
- predCall = (RexCall) invert(builder(cluster), predCall);
+ if (ref != null) {
+ refPredicate = Pair.of(ref, (RexCall)
builder(cluster).makeCall(((RexCall) rexNode).getOperator(), preds));
}
} else {
- ref = extractRefFromOperand(predCall, cluster, 0);
+ refPredicate = extractRefPredicate(rexNode, cluster);
+ }
- if (ref == null) {
- continue;
- }
+ if (refPredicate != null) {
+ List<RexCall> fldPreds =
res.computeIfAbsent(refPredicate.getKey(), k -> new
ArrayList<>(conjunctions.size()));
+
+ fldPreds.add(refPredicate.getValue());
}
+ }
+ return res;
+ }
- List<RexCall> fldPreds = res.computeIfAbsent(ref.getIndex(), k ->
new ArrayList<>(conjunctions.size()));
+ private static @Nullable Pair<Integer, RexCall>
extractRefPredicate(RexNode rexNode, RelOptCluster cluster) {
+ rexNode = expandBooleanFieldComparison(rexNode, builder(cluster));
- fldPreds.add(predCall);
+ if (!isSupportedTreeComparison(rexNode)) {
+ return null;
}
- return res;
+ RexCall predCall = (RexCall) rexNode;
+ RexSlot ref;
+
+ if (isBinaryComparison(rexNode)) {
+ ref = extractRefFromBinary(predCall, cluster);
+
+ if (ref == null) {
+ return null;
+ }
+
+ // Let RexLocalRef be on the left side.
+ if (refOnTheRight(predCall)) {
+ predCall = (RexCall) invert(builder(cluster), predCall);
+ }
+ } else {
+ ref = extractRefFromOperand(predCall, cluster, 0);
+
+ if (ref == null) {
+ return null;
+ }
+ }
+
+ return Pair.of(ref.getIndex(), predCall);
+ }
+
+ private static Boolean containsFieldAccess(RexNode node) {
+ RexVisitor<Void> v = new RexVisitorImpl<Void>(true) {
+ @Override
+ public Void visitFieldAccess(RexFieldAccess fieldAccess) {
+ throw Util.FoundOne.NULL;
+ }
+ };
+
+ try {
+ node.accept(v);
+
+ return false;
+ } catch (Util.FoundOne e) {
+ return true;
+ }
}
/** Extended version of {@link RexUtil#invert(RexBuilder, RexCall)} with
additional operators support. */
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImplTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImplTest.java
index e51b16ed61..bf27f49150 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImplTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/exec/exp/ExpressionFactoryImplTest.java
@@ -32,6 +32,7 @@ import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
+import java.util.Objects;
import java.util.UUID;
import java.util.function.BiPredicate;
import java.util.function.Function;
@@ -239,7 +240,7 @@ public class ExpressionFactoryImplTest extends
BaseIgniteAbstractTest {
List<SearchBounds> boundsList = List.of(
new MultiBounds(condition, List.of(
new ExactBounds(condition, intValue1),
- new RangeBounds(condition, intValue5, null, true,
true)
+ new RangeBounds(condition, nullValue, intValue5,
false, false)
))
);
@@ -250,14 +251,14 @@ public class ExpressionFactoryImplTest extends
BaseIgniteAbstractTest {
ranges.forEach(r -> list.add(new TestRange(r.lower(), r.upper())));
- assertEquals(List.of(new TestRange(new Object[]{5}, null), new
TestRange(new Object[]{1})), list);
+ assertEquals(List.of(new TestRange(new Object[]{null}, new
Object[]{5}), new TestRange(new Object[]{1})), list);
}
{ // range condition with null value as lower bound should respect
collation (NULLS FIRST)
List<SearchBounds> boundsList = List.of(
new MultiBounds(condition, List.of(
new ExactBounds(condition, intValue1),
- new RangeBounds(condition, nullValue, null, true,
true)
+ new RangeBounds(condition, null, nullValue, true,
true)
))
);
@@ -269,19 +270,19 @@ public class ExpressionFactoryImplTest extends
BaseIgniteAbstractTest {
ranges.forEach(r -> list.add(new TestRange(r.lower(), r.upper())));
- assertEquals(List.of(new TestRange(new Object[]{null}, null), new
TestRange(new Object[]{1})), list);
+ assertEquals(List.of(new TestRange(null, new Object[]{null}), new
TestRange(new Object[]{1})), list);
}
{ // range condition with null value as lower bound should respect
collation (NULLS LAST)
List<SearchBounds> boundsList = List.of(
new MultiBounds(condition, List.of(
- new ExactBounds(condition, intValue1),
- new RangeBounds(condition, nullValue, null, true,
true)
+ new RangeBounds(condition, nullValue, null, true,
true),
+ new ExactBounds(condition, intValue1)
))
);
Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
- new RelFieldCollation(0, Direction.DESCENDING,
NullDirection.LAST)));
+ new RelFieldCollation(0, Direction.ASCENDING,
NullDirection.LAST)));
RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
List<TestRange> list = new ArrayList<>();
@@ -311,6 +312,232 @@ public class ExpressionFactoryImplTest extends
BaseIgniteAbstractTest {
}
}
+ @Test
+ void multiBoundConditionsAreMergedCorrectly() {
+ RexBuilder rexBuilder = Commons.rexBuilder();
+
+ // condition expression is not used
+ RexLiteral condition = rexBuilder.makeLiteral(true);
+
+ RelDataType rowType = new Builder(typeFactory)
+ .add("C1", SqlTypeName.INTEGER)
+ .build();
+
+ RexNode intValue1 = rexBuilder.makeExactLiteral(new BigDecimal("1"));
+ RexNode intValue2 = rexBuilder.makeExactLiteral(new BigDecimal("2"));
+ RexNode intValue3 = rexBuilder.makeExactLiteral(new BigDecimal("3"));
+ RexNode intValue5 = rexBuilder.makeExactLiteral(new BigDecimal("5"));
+
+ { // conditions 'val < 1 or val = 1 or val = 5' can be combined to
'val <= 1 or val = 5' (ASCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new ExactBounds(condition, intValue5),
+ new ExactBounds(condition, intValue1),
+ new RangeBounds(condition, null, intValue1, true,
false)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.ASCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(null, new Object[]{1}), new
TestRange(new Object[]{5})), list);
+ }
+
+ { // conditions 'val < 1 or val = 1 or val = 5' can be combined to
'val <= 1 or val = 5' (DESCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue1, null, false,
true),
+ new ExactBounds(condition, intValue1),
+ new ExactBounds(condition, intValue5)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.DESCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{5}), new
TestRange(new Object[]{1}, null)), list);
+ }
+
+ { // conditions 'val >= 1 and val <= 5 or val > 1 and val < 5' must be
combined to single 'val >= 1 and val <= 5' (ASCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue1, intValue5,
true, true),
+ new RangeBounds(condition, intValue1, intValue5,
false, false)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.ASCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{1}, new
Object[]{5})), list);
+ }
+
+ { // conditions 'val >= 1 and val <= 5 or val > 1 and val < 5' must be
combined to single 'val >= 1 and val <= 5' (DESCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue5, intValue1,
true, true),
+ new RangeBounds(condition, intValue5, intValue1,
false, false)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.DESCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{5}, new
Object[]{1})), list);
+ }
+
+ { // conditions 'val >= 1 and val <= 2 or val >= 2 and val < 5' must
be combined to single 'val >= 1 and val < 5' (ASCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue1, intValue2,
true, true),
+ new RangeBounds(condition, intValue2, intValue5,
true, false)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.ASCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{1}, true, new
Object[]{5}, false)), list);
+ }
+
+ { // conditions 'val >= 1 and val <= 2 or val >= 2 and val < 5' must
be combined to single 'val >= 1 and val < 5' (DESCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue2, intValue1,
true, true),
+ new RangeBounds(condition, intValue5, intValue2,
false, true)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.DESCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{5}, false, new
Object[]{1}, true)), list);
+ }
+
+ { // conditions 'val >= 1 and val < 3 or val > 2 and val < 5' must be
combined into single 'val >= 1 and val <= 5' (ASCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue1, intValue3,
true, false),
+ new RangeBounds(condition, intValue2, intValue5,
false, false)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.ASCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{1}, true, new
Object[]{5}, false)), list);
+ }
+
+ { // conditions 'val >= 1 and val < 3 or val > 2 and val < 5' must be
combined into single 'val >= 1 and val <= 5' (DESCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue3, intValue1,
false, true),
+ new RangeBounds(condition, intValue5, intValue2,
false, false)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.DESCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{5}, false, new
Object[]{1}, true)), list);
+ }
+ }
+
+ @Test
+ public void testInvalidConditions() {
+ // At the moment, such conditions are impossible to obtain, but we
should be aware of them,
+ // since they can break the merge procedure.
+ RexBuilder rexBuilder = Commons.rexBuilder();
+
+ // condition expression is not used
+ RexLiteral condition = rexBuilder.makeLiteral(true);
+
+ RelDataType rowType = new Builder(typeFactory)
+ .add("C1", SqlTypeName.INTEGER)
+ .build();
+
+ RexNode intValue1 = rexBuilder.makeExactLiteral(new BigDecimal("1"));
+ RexNode intValue2 = rexBuilder.makeExactLiteral(new BigDecimal("2"));
+
+ { // conditions 'val between 2 and 1 or val = 2' should lead to single
'val = 2' (ASCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue2, intValue1,
true, true),
+ new ExactBounds(condition, intValue2)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.ASCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{2})), list);
+ }
+
+ { // conditions 'val between 2 and 1 or val = 2' should lead to single
'val = 2' (DESCENDING)
+ List<SearchBounds> boundsList = List.of(
+ new MultiBounds(condition, List.of(
+ new RangeBounds(condition, intValue1, intValue2,
true, true),
+ new ExactBounds(condition, intValue2)
+ ))
+ );
+
+ Comparator<Object[]> comparator =
expFactory.comparator(RelCollations.of(
+ new RelFieldCollation(0, Direction.DESCENDING)));
+
+ RangeIterable<Object[]> ranges = expFactory.ranges(boundsList,
rowType, comparator);
+ List<TestRange> list = new ArrayList<>();
+
+ ranges.forEach(r -> list.add(new TestRange(r.lower(),
r.lowerInclude(), r.upper(), r.upperInclude())));
+
+ assertEquals(List.of(new TestRange(new Object[]{2})), list);
+ }
+ }
+
@ParameterizedTest(name = "condition satisfies the index: [{0}]")
@ValueSource(booleans = {true, false})
public void testConditionsNotContainsNulls(boolean conditionSatisfyIdx) {
@@ -498,13 +725,23 @@ public class ExpressionFactoryImplTest extends
BaseIgniteAbstractTest {
final Object[] upper;
+ final boolean lowerInclude;
+
+ final boolean upperInclude;
+
TestRange(Object[] lower) {
this(lower, lower);
}
TestRange(Object @Nullable [] lower, Object @Nullable [] upper) {
+ this(lower, true, upper, true);
+ }
+
+ TestRange(Object @Nullable [] lower, boolean lowerInclude, Object
@Nullable [] upper, boolean upperInclude) {
this.lower = lower;
this.upper = upper;
+ this.lowerInclude = lowerInclude;
+ this.upperInclude = upperInclude;
}
@Override
@@ -516,12 +753,14 @@ public class ExpressionFactoryImplTest extends
BaseIgniteAbstractTest {
return false;
}
TestRange testRange = (TestRange) o;
- return Arrays.equals(lower, testRange.lower) &&
Arrays.equals(upper, testRange.upper);
+ return lowerInclude == testRange.lowerInclude && upperInclude ==
testRange.upperInclude && Arrays.equals(lower,
+ testRange.lower) && Arrays.equals(upper, testRange.upper);
}
@Override
public int hashCode() {
- int result = Arrays.hashCode(lower);
+ int result = Objects.hash(lowerInclude, upperInclude);
+ result = 31 * result + Arrays.hashCode(lower);
result = 31 * result + Arrays.hashCode(upper);
return result;
}
@@ -530,6 +769,8 @@ public class ExpressionFactoryImplTest extends
BaseIgniteAbstractTest {
public String toString() {
return "{lower=" + Arrays.toString(lower)
+ ", upper=" + Arrays.toString(upper)
+ + ", lowerInclude=" + lowerInclude
+ + ", upperInclude=" + upperInclude
+ '}';
}
}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/IndexSearchBoundsPlannerTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/IndexSearchBoundsPlannerTest.java
index ed3349185b..b805c56f4e 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/IndexSearchBoundsPlannerTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/IndexSearchBoundsPlannerTest.java
@@ -351,12 +351,8 @@ public class IndexSearchBoundsPlannerTest extends
AbstractPlannerTest {
/** Tests bounds with dynamic parameters. */
@Test
public void testBoundsDynamicParams() throws Exception {
- // Cannot optimize dynamic parameters to SEARCH/SARG, query is
splitted by or-to-union rule.
- // TODO: https://issues.apache.org/jira/browse/IGNITE-21287
- // assertPlan("SELECT * FROM TEST WHERE C1 IN (?, ?)", publicSchema,
isInstanceOf(IgniteUnionAll.class)
- // .and(input(0, isIndexScan("TEST", "C1C2C3")))
- // .and(input(1, isIndexScan("TEST", "C1C2C3"))), List.of(1, 1)
- // );
+ assertBounds("SELECT * FROM TEST WHERE C1 IN (?, ?)",
+ multi(exact("?0"), exact("?1")));
assertBounds("SELECT * FROM TEST WHERE C1 = ? AND C2 IN ('a', 'b')",
List.of(1), publicSchema,
exact("?0"),
@@ -445,6 +441,31 @@ public class IndexSearchBoundsPlannerTest extends
AbstractPlannerTest {
assertPlan("SELECT (SELECT C1 FROM TEST t2 WHERE t2.C1 < t1.C1 +
t2.C1) FROM TEST t1", publicSchema,
nodeOrAnyChild(isIndexScan("TEST", "C1C2C3")).negate());
+
+ // Here we have two OR sets in CNF, second set can't be used, since it
contains condition on C1 and C2 columns,
+ // so use only first OR set as bounds.
+ assertBounds("SELECT * FROM TEST WHERE C1 in (?, 1, 2) or (C1 = ? and
C2 > 'asd')",
+ multi(exact("?0"), exact(1), exact(2), exact("?1"))
+ );
+
+ assertBounds("SELECT * FROM TEST WHERE C1 in (?, ? + 1, ? * 2)",
+ multi(exact("?0"), exact("+(?1, 1)"), exact("*(?2, 2)"))
+ );
+
+ // Don't support expanding OR with correlate to bounds.
+ assertPlan("SELECT (SELECT C1 FROM TEST t2 WHERE C1 in (t1.C1, 1, ?))
FROM TEST t1", publicSchema,
+ nodeOrAnyChild(isIndexScan("TEST", "C1C2C3")).negate());
+
+ // Here "BETWEEN" generates AND condition, and we have two OR sets in
CNF, so we can't correctly use range
+ // with both upper and lower bounds. So, we use only first OR set as
bounds.
+ assertBounds("SELECT * FROM TEST WHERE C1 in (?, 1, 2) or C1 between ?
and ?",
+ multi(exact("?0"), exact(1), exact(2), range("?1", "null",
true, false))
+ );
+
+ // Check equality condition priority over SEARCH/SARG.
+ assertBounds("SELECT * FROM TEST WHERE (C1 BETWEEN 1 AND 10 OR C1 IN
(20, 30)) AND C1 = ?",
+ exact("?0")
+ );
}
/**
