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/21 12:56:00 UTC
[ignite] branch master updated: IGNITE-17889 SQL Calcite: Avoid full index scans in case of NULLs in search bounds - Fixes #10338.
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 a3804cca6ba IGNITE-17889 SQL Calcite: Avoid full index scans in case of NULLs in search bounds - Fixes #10338.
a3804cca6ba is described below
commit a3804cca6bace0d85bdbdf28bc7c373e8ab71e2f
Author: Aleksey Plekhanov <pl...@gmail.com>
AuthorDate: Fri Oct 21 15:49:59 2022 +0300
IGNITE-17889 SQL Calcite: Avoid full index scans in case of NULLs in search bounds - Fixes #10338.
Signed-off-by: Aleksey Plekhanov <pl...@gmail.com>
---
.../query/calcite/exec/AbstractIndexScan.java | 11 ++-
.../query/calcite/exec/ExecutionContext.java | 8 ++
.../calcite/exec/exp/ExpressionFactoryImpl.java | 46 ++++++++-
.../query/calcite/exec/exp/IgniteSqlFunctions.java | 24 +++++
.../query/calcite/exec/exp/RangeIterable.java | 8 +-
.../query/calcite/exec/exp/RexImpTable.java | 14 ++-
.../calcite/sql/fun/IgniteOwnSqlOperatorTable.java | 47 +++++++++
.../query/calcite/util/IgniteMethod.java | 11 ++-
.../processors/query/calcite/util/RexUtils.java | 49 ++++------
.../exec/rel/SortedIndexSpoolExecutionTest.java | 4 +-
.../integration/IndexScanlIntegrationTest.java | 107 ++++++++++++++++-----
.../planner/IndexSearchBoundsPlannerTest.java | 39 +++-----
.../planner/SortedIndexSpoolPlannerTest.java | 3 +-
13 files changed, 274 insertions(+), 97 deletions(-)
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/AbstractIndexScan.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/AbstractIndexScan.java
index 228680a9473..a180fe7c81d 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/AbstractIndexScan.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/AbstractIndexScan.java
@@ -16,6 +16,7 @@
*/
package org.apache.ignite.internal.processors.query.calcite.exec;
+import java.util.Collections;
import java.util.Iterator;
import java.util.NoSuchElementException;
import java.util.function.Function;
@@ -88,8 +89,14 @@ public abstract class AbstractIndexScan<Row, IdxRow> implements Iterable<Row>, A
idx.find(lower, upper, range.lowerInclude(), range.upperInclude(), indexQueryContext()));
};
- if (ranges.size() == 1)
- return clo.apply(ranges.iterator().next());
+ if (!ranges.multiBounds()) {
+ Iterator<RangeCondition<Row>> it = ranges.iterator();
+
+ if (it.hasNext())
+ return clo.apply(it.next());
+ else
+ return Collections.emptyIterator();
+ }
return F.flat(F.iterator(ranges, clo, true));
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionContext.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionContext.java
index 1f09e873fb4..4d22af73017 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionContext.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionContext.java
@@ -53,6 +53,9 @@ public class ExecutionContext<Row> extends AbstractQueryContext implements DataC
/** Placeholder for values, which expressions is not specified. */
private static final Object UNSPECIFIED_VALUE = new Object();
+ /** Placeholder for NULL values in search bounds. */
+ private static final Object NULL_BOUND = new Object();
+
/** */
private final UUID qryId;
@@ -310,6 +313,11 @@ public class ExecutionContext<Row> extends AbstractQueryContext implements DataC
return UNSPECIFIED_VALUE;
}
+ /** */
+ public Object nullBound() {
+ return NULL_BOUND;
+ }
+
/** {@inheritDoc} */
@Override public boolean equals(Object o) {
if (this == o)
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
index f5c3f91705b..8e3cc5dd6b8 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
@@ -695,6 +695,9 @@ public class ExpressionFactoryImpl<Row> implements ExpressionFactory<Row> {
/** */
private Row upperRow;
+ /** Cached skip range flag. */
+ private Boolean skip;
+
/** */
private final RowFactory<Row> factory;
@@ -738,12 +741,41 @@ public class ExpressionFactoryImpl<Row> implements ExpressionFactory<Row> {
Row res = factory.create();
scalar.execute(ctx, null, res);
+ RowHandler<Row> hnd = ctx.rowHandler();
+
+ // Check bound for NULL values. If bound contains NULLs, the whole range should be skipped.
+ // There is special placeholder for searchable NULLs, make this replacement here too.
+ for (int i = 0; i < hnd.columnCount(res); i++) {
+ Object fldVal = hnd.get(i, res);
+
+ if (fldVal == null)
+ skip = Boolean.TRUE;
+
+ if (fldVal == ctx.nullBound())
+ hnd.set(i, res, null);
+ }
+
return res;
}
/** Clear cached rows. */
public void clearCache() {
lowerRow = upperRow = null;
+ skip = null;
+ }
+
+ /** Skip this range. */
+ public boolean skip() {
+ if (skip == null) {
+ // Precalculate skip flag.
+ lower();
+ upper();
+
+ if (skip == null)
+ skip = Boolean.FALSE;
+ }
+
+ return skip;
}
}
@@ -765,16 +797,20 @@ public class ExpressionFactoryImpl<Row> implements ExpressionFactory<Row> {
}
/** {@inheritDoc} */
- @Override public int size() {
- return ranges.size();
+ @Override public boolean multiBounds() {
+ return ranges.size() > 1;
}
/** {@inheritDoc} */
@Override public Iterator<RangeCondition<Row>> iterator() {
ranges.forEach(b -> ((RangeConditionImpl)b).clearCache());
- if (ranges.size() == 1)
- return ranges.iterator();
+ if (ranges.size() == 1) {
+ if (((RangeConditionImpl)ranges.get(0)).skip())
+ return Collections.emptyIterator();
+ else
+ return ranges.iterator();
+ }
// Sort ranges using collation comparator to produce sorted output. There should be no ranges
// intersection.
@@ -785,7 +821,7 @@ public class ExpressionFactoryImpl<Row> implements ExpressionFactory<Row> {
sorted = true;
}
- return ranges.iterator();
+ return F.iterator(ranges.iterator(), r -> r, true, r -> !((RangeConditionImpl)r).skip());
}
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
index 4238dec504f..5c6130af64e 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
@@ -154,6 +154,30 @@ public class IgniteSqlFunctions {
return b == null ? null : new String(b.getBytes(), Commons.typeFactory().getDefaultCharset());
}
+ /** LEAST2. */
+ public static Object least2(Object arg0, Object arg1) {
+ return leastOrGreatest(true, arg0, arg1);
+ }
+
+ /** GREATEST2. */
+ public static Object greatest2(Object arg0, Object arg1) {
+ return leastOrGreatest(false, arg0, arg1);
+ }
+
+ /** */
+ private static Object leastOrGreatest(boolean least, Object arg0, Object arg1) {
+ if (arg0 == null || arg1 == null)
+ return null;
+
+ assert arg0 instanceof Comparable && arg1 instanceof Comparable :
+ "Unexpected class [arg0=" + arg0.getClass().getName() + ", arg1=" + arg1.getClass().getName() + ']';
+
+ if (((Comparable<Object>)arg0).compareTo(arg1) < 0)
+ return least ? arg0 : arg1;
+ else
+ return least ? arg1 : arg0;
+ }
+
/** */
private static class RangeTable implements ScannableTable {
/** Start of the range. */
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RangeIterable.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RangeIterable.java
index 7e0be826c19..71d302a7a26 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RangeIterable.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RangeIterable.java
@@ -21,6 +21,10 @@ package org.apache.ignite.internal.processors.query.calcite.exec.exp;
* Iterable over range conditions.
*/
public interface RangeIterable<Row> extends Iterable<RangeCondition<Row>> {
- /** Count of ranges in iterable. */
- public int size();
+ /**
+ * Multi bounds condition.
+ *
+ * @return {@code True} if iterable can have more than one item.
+ */
+ public boolean multiBounds();
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
index ba043f871c5..fc28fe51338 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
@@ -227,6 +227,9 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TRUNCATE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UNARY_MINUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UNARY_PLUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UPPER;
+import static org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.GREATEST2;
+import static org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.LEAST2;
+import static org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.NULL_BOUND;
import static org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.QUERY_ENGINE;
import static org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.SYSTEM_RANGE;
import static org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.TYPEOF;
@@ -513,18 +516,23 @@ public class RexImpTable {
new MethodImplementor(BuiltInMethod.IS_JSON_SCALAR.method,
NullPolicy.NONE, false)));
- // System functions
+ // System functions.
final SystemFunctionImplementor systemFunctionImplementor = new SystemFunctionImplementor();
map.put(SYSTEM_RANGE, systemFunctionImplementor);
- // Current time functions
+ // Current time functions.
map.put(CURRENT_TIME, systemFunctionImplementor);
map.put(CURRENT_TIMESTAMP, systemFunctionImplementor);
map.put(CURRENT_DATE, systemFunctionImplementor);
map.put(LOCALTIME, systemFunctionImplementor);
map.put(LOCALTIMESTAMP, systemFunctionImplementor);
+
map.put(TYPEOF, systemFunctionImplementor);
map.put(QUERY_ENGINE, systemFunctionImplementor);
+ map.put(NULL_BOUND, systemFunctionImplementor);
+
+ defineMethod(LEAST2, IgniteMethod.LEAST2.method(), NullPolicy.ALL);
+ defineMethod(GREATEST2, IgniteMethod.GREATEST2.method(), NullPolicy.ALL);
}
/** */
@@ -1694,6 +1702,8 @@ public class RexImpTable {
}
else if (op == QUERY_ENGINE)
return Expressions.constant(CalciteQueryEngineConfiguration.ENGINE_NAME);
+ else if (op == NULL_BOUND)
+ return Expressions.call(root, IgniteMethod.CONTEXT_NULL_BOUND.method());
throw new AssertionError("unknown function " + op);
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java
index 7a1bb78c606..b9f994c26fc 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java
@@ -21,6 +21,8 @@ import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeTransforms;
import org.apache.calcite.sql.util.ReflectiveSqlOperatorTable;
/**
@@ -76,6 +78,51 @@ public class IgniteOwnSqlOperatorTable extends ReflectiveSqlOperatorTable {
OperandTypes.NILADIC,
SqlFunctionCategory.SYSTEM);
+ /**
+ * Replacement for NULL values in search bounds. Required to distinguish searchable NULL values
+ * (for example, 'a IS NULL' condition) and not searchable NULL values (for example, 'a = NULL' condition).
+ *
+ * Note: System function, cannot be used by user.
+ */
+ public static final SqlFunction NULL_BOUND =
+ new SqlFunction(
+ "$NULL_BOUND",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.explicit(SqlTypeName.ANY),
+ null,
+ OperandTypes.NILADIC,
+ SqlFunctionCategory.SYSTEM);
+
+ /**
+ * Least of two arguments. Unlike LEAST, which is converted to CASE WHEN THEN END clause, this function
+ * is natively implemented.
+ *
+ * Note: System function, cannot be used by user.
+ */
+ public static final SqlFunction LEAST2 =
+ new SqlFunction(
+ "$LEAST2",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
+ null,
+ OperandTypes.SAME_SAME,
+ SqlFunctionCategory.SYSTEM);
+
+ /**
+ * Greatest of two arguments. Unlike GREATEST, which is converted to CASE WHEN THEN END clause, this function
+ * is natively implemented.
+ *
+ * Note: System function, cannot be used by user.
+ */
+ public static final SqlFunction GREATEST2 =
+ new SqlFunction(
+ "$GREATEST2",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
+ null,
+ OperandTypes.SAME_SAME,
+ SqlFunctionCategory.SYSTEM);
+
/**
* Returns the Ignite operator table, creating it if necessary.
*/
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMethod.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMethod.java
index 16d9f0322be..af39a6118f1 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMethod.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMethod.java
@@ -51,6 +51,9 @@ public enum IgniteMethod {
/** See {@link ExecutionContext#unspecifiedValue()} */
CONTEXT_UNSPECIFIED_VALUE(ExecutionContext.class, "unspecifiedValue"),
+ /** See {@link ExecutionContext#nullBound()} */
+ CONTEXT_NULL_BOUND(ExecutionContext.class, "nullBound"),
+
/** See {@link ExecutionContext#getCorrelated(int)} */
CONTEXT_GET_CORRELATED_VALUE(ExecutionContext.class, "getCorrelated", int.class),
@@ -82,7 +85,13 @@ public enum IgniteMethod {
BYTESTRING_TO_STRING(IgniteSqlFunctions.class, "toString", ByteString.class),
/** See {@link IgniteSqlFunctions#toByteString(String)} */
- STRING_TO_BYTESTRING(IgniteSqlFunctions.class, "toByteString", String.class);
+ STRING_TO_BYTESTRING(IgniteSqlFunctions.class, "toByteString", String.class),
+
+ /** See {@link IgniteSqlFunctions#least2(Object, Object)} */
+ LEAST2(IgniteSqlFunctions.class, "least2", Object.class, Object.class),
+
+ /** See {@link IgniteSqlFunctions#greatest2(Object, Object)} */
+ GREATEST2(IgniteSqlFunctions.class, "greatest2", Object.class, Object.class);
/** */
private final Method method;
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 396be25e579..16ca55a4868 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
@@ -68,6 +68,7 @@ import org.apache.ignite.internal.processors.query.calcite.prepare.bounds.ExactB
import org.apache.ignite.internal.processors.query.calcite.prepare.bounds.MultiBounds;
import org.apache.ignite.internal.processors.query.calcite.prepare.bounds.RangeBounds;
import org.apache.ignite.internal.processors.query.calcite.prepare.bounds.SearchBounds;
+import org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable;
import org.apache.ignite.internal.processors.query.calcite.trait.TraitUtils;
import org.apache.ignite.internal.util.typedef.F;
import org.apache.ignite.internal.util.typedef.internal.U;
@@ -321,7 +322,7 @@ public class RexUtils {
) {
RexBuilder builder = builder(cluster);
- RexNode nullVal = builder.makeNullLiteral(fldType);
+ RexNode nullBound = builder.makeCall(IgniteOwnSqlOperatorTable.NULL_BOUND);
RexNode upperCond = null;
RexNode lowerCond = null;
@@ -347,7 +348,7 @@ public class RexUtils {
if (op.kind == EQUALS)
return new ExactBounds(pred, val);
else if (op.kind == IS_NULL)
- return new ExactBounds(pred, nullVal);
+ return new ExactBounds(pred, nullBound);
else if (op.kind == SEARCH) {
Sarg<?> sarg = ((RexLiteral)pred.operands.get(1)).getValueAs(Sarg.class);
@@ -362,8 +363,8 @@ public class RexUtils {
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);
+ if (lowerBound != null && lowerBound != nullBound) {
+ lowerBound = leastOrGreatest(builder, !ascDir, lowerBound, rangeBounds.lowerBound());
lowerInclude |= rangeBounds.lowerInclude();
}
else {
@@ -374,8 +375,8 @@ public class RexUtils {
}
if (rangeBounds.upperBound() != null) {
- if (upperBound != null && upperBound != nullVal) {
- upperBound = leastOrGreatest(builder, ascDir, upperBound, rangeBounds.upperBound(), nullVal);
+ if (upperBound != null && upperBound != nullBound) {
+ upperBound = leastOrGreatest(builder, ascDir, upperBound, rangeBounds.upperBound());
upperInclude |= rangeBounds.upperInclude();
}
else {
@@ -409,25 +410,25 @@ public class RexUtils {
case GREATER_THAN:
case GREATER_THAN_OR_EQUAL:
if (lowerBoundBelow) {
- if (lowerBound == null || lowerBound == nullVal) {
+ if (lowerBound == null || lowerBound == nullBound) {
lowerCond = pred;
lowerBound = val;
lowerInclude = includeBound;
}
else {
- lowerBound = leastOrGreatest(builder, lessCondition, lowerBound, val, nullVal);
+ lowerBound = leastOrGreatest(builder, lessCondition, lowerBound, val);
lowerInclude |= includeBound;
lowerCond = lessOrGreater(builder, lessCondition, lowerInclude, ref, lowerBound);
}
}
else {
- if (upperBound == null || upperBound == nullVal) {
+ if (upperBound == null || upperBound == nullBound) {
upperCond = pred;
upperBound = val;
upperInclude = includeBound;
}
else {
- upperBound = leastOrGreatest(builder, lessCondition, upperBound, val, nullVal);
+ upperBound = leastOrGreatest(builder, lessCondition, upperBound, val);
upperInclude |= includeBound;
upperCond = lessOrGreater(builder, lessCondition, upperInclude, ref, upperBound);
}
@@ -437,12 +438,12 @@ public class RexUtils {
case IS_NOT_NULL:
if (fc.nullDirection == RelFieldCollation.NullDirection.FIRST && lowerBound == null) {
lowerCond = pred;
- lowerBound = nullVal;
+ lowerBound = nullBound;
lowerInclude = false;
}
else if (fc.nullDirection == RelFieldCollation.NullDirection.LAST && upperBound == null) {
upperCond = pred;
- upperBound = nullVal;
+ upperBound = nullBound;
upperInclude = false;
}
break;
@@ -502,24 +503,12 @@ public class RexUtils {
}
/** */
- 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 leastOrGreatest(RexBuilder builder, boolean least, RexNode arg0, RexNode arg1) {
+ return builder.makeCall(
+ least ? IgniteOwnSqlOperatorTable.LEAST2 : IgniteOwnSqlOperatorTable.GREATEST2,
+ arg0,
+ arg1
+ );
}
/** */
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/rel/SortedIndexSpoolExecutionTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/rel/SortedIndexSpoolExecutionTest.java
index f28e08bf682..3d733a47096 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/rel/SortedIndexSpoolExecutionTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/rel/SortedIndexSpoolExecutionTest.java
@@ -256,8 +256,8 @@ public class SortedIndexSpoolExecutionTest extends AbstractExecutionTest {
}
/** {@inheritDoc} */
- @Override public int size() {
- return 1;
+ @Override public boolean multiBounds() {
+ return false;
}
/** {@inheritDoc} */
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java
index 799a0fc6663..e87e74302eb 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java
@@ -28,6 +28,7 @@ import org.apache.ignite.internal.processors.query.calcite.QueryChecker;
import org.apache.ignite.internal.processors.query.calcite.exec.ExecutionContext;
import org.apache.ignite.internal.processors.query.calcite.exec.exp.RangeIterable;
import org.apache.ignite.internal.processors.query.calcite.metadata.ColocationGroup;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteIndex;
import org.apache.ignite.internal.processors.query.calcite.schema.IgniteTable;
import org.apache.ignite.internal.util.typedef.F;
import org.jetbrains.annotations.Nullable;
@@ -44,34 +45,16 @@ public class IndexScanlIntegrationTest extends AbstractBasicIntegrationTest {
/** */
@Test
- public void testNullsInSearchRow() {
+ public void testNullsInCNLJSearchRow() {
executeSql("CREATE TABLE t(i1 INTEGER, i2 INTEGER) WITH TEMPLATE=REPLICATED ");
- executeSql("INSERT INTO t VALUES (0, null), (1, null), (2, 2), (3, null), (4, null)");
+ executeSql("INSERT INTO t VALUES (0, null), (1, null), (2, 2), (3, null), (4, null), (null, 5)");
executeSql("CREATE INDEX t_idx ON t(i1)");
IgniteTable tbl = (IgniteTable)queryProcessor(grid(0)).schemaHolder().schema("PUBLIC").getTable("T");
- AtomicInteger filteredRows = new AtomicInteger();
- tbl.addIndex(new DelegatingIgniteIndex(tbl.getIndex("T_IDX")) {
- @Override public <Row> Iterable<Row> scan(
- ExecutionContext<Row> execCtx,
- ColocationGroup grp,
- Predicate<Row> filters,
- RangeIterable<Row> ranges,
- Function<Row, Row> rowTransformer,
- @Nullable ImmutableBitSet requiredColumns
- ) {
- Predicate<Row> filter = row -> {
- filteredRows.incrementAndGet();
-
- return true;
- };
+ RowCountingIndex idx = new RowCountingIndex(tbl.getIndex("T_IDX"));
- filters = filter.and(filters);
-
- return delegate.scan(execCtx, grp, filters, ranges, rowTransformer, requiredColumns);
- }
- });
+ tbl.addIndex(idx);
String sql = "SELECT /*+ DISABLE_RULE('NestedLoopJoinConverter', 'MergeJoinConverter') */ t1.i1, t2.i1 " +
"FROM t t1 " +
@@ -85,11 +68,53 @@ public class IndexScanlIntegrationTest extends AbstractBasicIntegrationTest {
.returns(2, 2)
.returns(3, null)
.returns(4, null)
+ .returns(null, null)
.check();
// There shouldn't be full index scan in case of null values in search row, only one value must be found by
// range scan and passed to predicate.
- assertEquals(1, filteredRows.get());
+ assertEquals(1, idx.rowsProcessed());
+ }
+
+ /** */
+ @Test
+ public void testNullsInSearchRow() {
+ executeSql("CREATE TABLE t(i1 INTEGER, i2 INTEGER) WITH TEMPLATE=REPLICATED ");
+ executeSql("INSERT INTO t VALUES (null, 0), (1, null), (2, 2), (3, null)");
+ executeSql("CREATE INDEX t_idx ON t(i1, i2)");
+
+ IgniteTable tbl = (IgniteTable)queryProcessor(grid(0)).schemaHolder().schema("PUBLIC").getTable("T");
+
+ RowCountingIndex idx = new RowCountingIndex(tbl.getIndex("T_IDX"));
+
+ tbl.addIndex(idx);
+
+ assertQuery("SELECT * FROM t WHERE i1 = ?")
+ .withParams(new Object[] { null })
+ .matches(QueryChecker.containsIndexScan("PUBLIC", "T", "T_IDX"))
+ .check();
+
+ assertEquals(0, idx.rowsProcessed());
+
+ assertQuery("SELECT * FROM t WHERE i1 = 1 AND i2 = ?")
+ .withParams(new Object[] { null })
+ .matches(QueryChecker.containsIndexScan("PUBLIC", "T", "T_IDX"))
+ .check();
+
+ // Multi ranges.
+ assertQuery("SELECT * FROM t WHERE i1 IN (1, 2, 3) AND i2 = ?")
+ .withParams(new Object[] { null })
+ .matches(QueryChecker.containsIndexScan("PUBLIC", "T", "T_IDX"))
+ .check();
+
+ assertEquals(0, idx.rowsProcessed());
+
+ assertQuery("SELECT * FROM t WHERE i1 IN (1, 2) AND i2 IS NULL")
+ .matches(QueryChecker.containsIndexScan("PUBLIC", "T", "T_IDX"))
+ .returns(1, null)
+ .check();
+
+ assertEquals(1, idx.rowsProcessed());
}
/** */
@@ -182,4 +207,40 @@ public class IndexScanlIntegrationTest extends AbstractBasicIntegrationTest {
.returns(4)
.check();
}
+
+ /** */
+ private static class RowCountingIndex extends DelegatingIgniteIndex {
+ /** */
+ private final AtomicInteger filteredRows = new AtomicInteger();
+
+ /** */
+ public RowCountingIndex(IgniteIndex delegate) {
+ super(delegate);
+ }
+
+ /** {@inheritDoc} */
+ @Override public <Row> Iterable<Row> scan(
+ ExecutionContext<Row> execCtx,
+ ColocationGroup grp,
+ Predicate<Row> filters,
+ RangeIterable<Row> ranges,
+ Function<Row, Row> rowTransformer,
+ @Nullable ImmutableBitSet requiredColumns
+ ) {
+ Predicate<Row> filter = row -> {
+ filteredRows.incrementAndGet();
+
+ return true;
+ };
+
+ filters = filter.and(filters);
+
+ return delegate.scan(execCtx, grp, filters, ranges, rowTransformer, requiredColumns);
+ }
+
+ /** */
+ public int rowsProcessed() {
+ return filteredRows.getAndSet(0);
+ }
+ }
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/IndexSearchBoundsPlannerTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/IndexSearchBoundsPlannerTest.java
index 528565e71c7..65d33da52db 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/IndexSearchBoundsPlannerTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/IndexSearchBoundsPlannerTest.java
@@ -81,7 +81,7 @@ public class IndexSearchBoundsPlannerTest extends AbstractPlannerTest {
range(1, 3, false, true));
assertBounds("SELECT * FROM TEST WHERE C1 < 3 AND C1 IS NOT NULL",
- range("null", 3, false, false));
+ range("$NULL_BOUND()", 3, false, false));
// Redundant "IS NOT NULL condition".
assertBounds("SELECT * FROM TEST WHERE C1 > 3 AND C1 IS NOT NULL",
@@ -158,14 +158,14 @@ public class IndexSearchBoundsPlannerTest extends AbstractPlannerTest {
assertBounds("SELECT * FROM TEST WHERE C1 = 1 AND (C2 > '1' OR C2 < '3')",
exact(1),
- range("null", null, false, true));
+ range("$NULL_BOUND()", null, false, true));
}
/** Simple SEARCH/SARG with "IS NULL" condition. */
@Test
public void testBoundsOneFieldSearchWithNull() throws Exception {
assertBounds("SELECT * FROM TEST WHERE C1 IN (1, 2, 3) OR C1 IS NULL",
- multi(exact("null"), exact(1), exact(2), exact(3)),
+ multi(exact("$NULL_BOUND()"), exact(1), exact(2), exact(3)),
empty(),
empty(),
empty()
@@ -188,15 +188,15 @@ public class IndexSearchBoundsPlannerTest extends AbstractPlannerTest {
empty(),
empty(),
empty(),
- range(1, "null", false, false));
+ range(1, "$NULL_BOUND()", false, false));
- assertBounds("SELECT * FROM TEST WHERE C4 IS NULL", empty(), empty(), empty(), exact("null"));
+ assertBounds("SELECT * FROM TEST WHERE C4 IS NULL", empty(), empty(), empty(), exact("$NULL_BOUND()"));
assertBounds("SELECT * FROM TEST WHERE C4 IS NOT NULL",
empty(),
empty(),
empty(),
- range(null, "null", true, false));
+ range(null, "$NULL_BOUND()", true, false));
assertBounds("SELECT * FROM TEST WHERE C4 IN (1, 2, 3) AND C3 > 1",
empty(),
@@ -382,26 +382,19 @@ public class IndexSearchBoundsPlannerTest extends AbstractPlannerTest {
@Test
public void testBoundsMerge() throws Exception {
assertBounds("SELECT * FROM TEST WHERE C1 > ? AND C1 >= 1",
- range(leastOrGreatest(false, "?0", "1", "INTEGER"), null, true, true)
+ range("$GREATEST2(?0, 1)", 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
- )
+ range("$GREATEST2($GREATEST2(?0, ?1), ?2)", 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
- )
+ range("$GREATEST2(?0, 1)", "$LEAST2(?1, ?2)", true, false)
);
assertBounds("SELECT * FROM TEST WHERE C1 < ? AND C1 BETWEEN 1 AND 10 ",
- range(1, leastOrGreatest(true, "?0", "10", "INTEGER"), true, true)
+ range(1, "$LEAST2(?0, 10)", true, true)
);
assertBounds("SELECT * FROM TEST WHERE C1 NOT IN (1, 2) AND C1 >= ?",
@@ -415,11 +408,7 @@ public class IndexSearchBoundsPlannerTest extends AbstractPlannerTest {
empty(),
empty(),
empty(),
- range(
- leastOrGreatest(true, "?1", "?2", "INTEGER"),
- leastOrGreatest(false, "?0", "1", "INTEGER"),
- false, true
- )
+ range("$LEAST2(?1, ?2)", "$GREATEST2(?0, 1)", false, true)
);
}
@@ -450,12 +439,6 @@ public class IndexSearchBoundsPlannerTest extends AbstractPlannerTest {
nodeOrAnyChild(isIndexScan("TEST", "C1C2C3")).negate());
}
- /** 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/internal/processors/query/calcite/planner/SortedIndexSpoolPlannerTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/SortedIndexSpoolPlannerTest.java
index 54b8f4893ea..ceed9c6d7d7 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/SortedIndexSpoolPlannerTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/SortedIndexSpoolPlannerTest.java
@@ -23,7 +23,6 @@ import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexFieldAccess;
-import org.apache.calcite.rex.RexLiteral;
import org.apache.ignite.internal.processors.query.calcite.prepare.bounds.ExactBounds;
import org.apache.ignite.internal.processors.query.calcite.prepare.bounds.RangeBounds;
import org.apache.ignite.internal.processors.query.calcite.prepare.bounds.SearchBounds;
@@ -214,7 +213,7 @@ public class SortedIndexSpoolPlannerTest extends AbstractPlannerTest {
assertTrue(fld1Bounds.lowerBound() instanceof RexFieldAccess);
assertFalse(fld1Bounds.lowerInclude());
// NULLS LAST in collation, so nulls can be skipped by upper bound.
- assertTrue(((RexLiteral)fld1Bounds.upperBound()).isNull());
+ assertEquals("$NULL_BOUND()", fld1Bounds.upperBound().toString());
assertFalse(fld1Bounds.upperInclude());
assertNull(searchBounds.get(2));