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 062d293f766 IGNITE-21162 SQL Calcite: Use table level hints - Fixes
#11175.
062d293f766 is described below
commit 062d293f766067a24ec76d50827784219715c4ee
Author: Vladimir Steshin <[email protected]>
AuthorDate: Fri Mar 1 21:27:05 2024 +0300
IGNITE-21162 SQL Calcite: Use table level hints - Fixes #11175.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
docs/_docs/SQL/sql-calcite.adoc | 39 ++++++---
.../query/calcite/hint/HintDefinition.java | 18 ++--
.../processors/query/calcite/hint/HintUtils.java | 25 +++++-
.../rel/logical/IgniteLogicalTableScan.java | 2 +-
.../rule/AbstractIgniteJoinConverterRule.java | 70 ++++++++++------
.../query/calcite/schema/CacheTableImpl.java | 7 +-
.../query/calcite/schema/IgniteTable.java | 9 +-
.../query/calcite/schema/SystemViewTableImpl.java | 7 +-
.../query/calcite/planner/TestTable.java | 7 +-
.../planner/hints/ForceIndexHintPlannerTest.java | 44 ++++++++++
.../planner/hints/JoinTypeHintPlannerTest.java | 98 +++++++++++++++++++---
.../planner/hints/NoIndexHintPlannerTest.java | 44 ++++++++++
12 files changed, 299 insertions(+), 71 deletions(-)
diff --git a/docs/_docs/SQL/sql-calcite.adoc b/docs/_docs/SQL/sql-calcite.adoc
index 90a9a910aa4..ddeaab14640 100644
--- a/docs/_docs/SQL/sql-calcite.adoc
+++ b/docs/_docs/SQL/sql-calcite.adoc
@@ -242,14 +242,16 @@ SQL hints are optional to apply and might be skipped in
some cases.
====
=== Hints format
-SQL hints are defined by a special comment +++/*+ HINT */+++ reffered as a
_hint block_. Spaces before and after the
-hint name are required. The hint block is placed right after a relation
operator, often after _SELECT_. Several hint
-blocks for one relation operator *are not allowed*.
+SQL hints are defined by a special comment +++/*+ HINT */+++ reffered as _hint
block_. Spaces before and after the
+hint name are required. The hint block is placed right after _SELECT_ or after
a table name. Several hint blocks for
+one _SELECT_ or one table *are not allowed*. Several hints in one hint block
are separated with comma.
Example:
[source, SQL]
----
-SELECT /*+ NO_INDEX */ T1.* FROM TBL1 where T1.V1=? and T1.V2=?
+SELECT /*+ FORCE_INDEX(IDX_TBL1_V2), EXPAND_DISTINCT_AGG */ V2, AVG(DISTINCT
V3) FROM TBL1 WHERE V1=? and V2=? GROUP BY V2
+
+SELECT * FROM TBL1 /*+ FORCE_INDEX(IDX_TBL1_V2) */ where V1=? and V2=?
----
It is allowed to define several hints for the same relation operator. To use
several hints, separate them by comma
@@ -258,7 +260,7 @@ It is allowed to define several hints for the same relation
operator. To use sev
Example:
[source, SQL]
----
-SELECT /*+ NO_INDEX, EXPAND_DISTINCT_AGG */ SUM(DISTINCT V1), AVG(DISTINCT V2)
FROM TBL1 GROUP BY V3 WHERE V3=?
+SELECT /*+ NO_INDEX, EXPAND_DISTINCT_AGG */ SUM(DISTINCT V1), AVG(DISTINCT V2)
FROM TBL1 WHERE V3=? GROUP BY V3
----
==== Hint parameters
@@ -276,9 +278,9 @@ SELECT /*+ FORCE_INDEX('TBL2_idx1') */ T1.V1, T2.V1 FROM
TBL1 T1, TBL2 T2 WHERE
----
=== Hint scope
-Hints are defined for SELECT relation operator and are "visible" for the
following operators, queries and subqueries.
-The hints defined in the subquery are "visible" only for this subquery and its
subqueries. Hint is not "visible" to
-the previous relation operator if it is defined after it.
+Hints of a _SELECT_ are "visible" for this operation and the following
relation operators, queries and subqueries.
+Hints in a subquery have effective scope only for this subquery and its
subqueries. Hint, defined for a table, is
+effective only for this table.
Example:
[source, SQL]
@@ -286,8 +288,9 @@ Example:
SELECT /*+ NO_INDEX(TBL1_IDX2), FORCE_INDEX(TBL2_IDX2) */ T1.V1 FROM TBL1 T1
WHERE T1.V2 IN (SELECT T2.V2 FROM TBL2 T2 WHERE T2.V1=? AND T2.V2=?);
SELECT T1.V1 FROM TBL1 T1 WHERE T1.V2 IN (SELECT /*+ FORCE_INDEX(TBL2_IDX2) */
T2.V2 FROM TBL2 T2 WHERE T2.V1=? AND T2.V2=?);
-----
+SELECT T1.V1 FROM TBL1 T1 JOIN TBL2 /*+ MERGE_JOIN */ T2 ON T1.V2=T2.V2 and
T1.V3=T2.V3 and T2.V3=?;
+----
Note that only the first query has a hint in such a case as:
[source, SQL]
----
@@ -295,11 +298,23 @@ SELECT /*+ FORCE_INDEX */ V1 FROM TBL1 WHERE V1=? AND V2=?
UNION ALL
SELECT V1 FROM TBL1 WHERE V3>?
----
-
But *there are exceptions*: hints of engine or optimizer level, such as
link:#hint_disable_rule[_DISABLE_RULE_] or
link:#hint_query_engine[_QUERY_ENGINE_]. Such hints should be defined at the
beginning of the query and are related to
the whole query.
+=== Hints priority
+Hints, defined in subqueries or in the following _SELECTs_, have priority over
the preceding ones. In the following example,
+an index for _TBL2_ is actually applied.
+[source, SQL]
+----
+SELECT /*+ NO_INDEX */ * FROM TBL1 T1 WHERE T1.V1 = (SELECT /*+ FORCE_INDEX */
T2.V1 FROM TBL2 T2 where T2.V2=? and T2.V3=?)
+----
+Table hints usually have a bigger priority. In the following example, an index
for _TBL1_ is actually applied.
+[source, SQL]
+----
+SELECT /*+ NO_INDEX */ * FROM TBL /*+ FORCE_INDEX(IDX_TBL1_V2) */ where V1=?
and V2=? and V3=?;
+----
+
=== Hints errors
The optimizer tries to apply every hint and its parameters, if possible. But
it skips the hint or hint parameter if:
@@ -341,6 +356,8 @@ SELECT /*+ FORCE_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1,
T2.V1 FROM TBL1 T1, TBL2
SELECT /*+ NO_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?;
SELECT /*+ NO_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2
T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
+
+SELECT T1.V1, T2.V2 FROM TBL1 t1 JOIN TBL2 /*+ FORCE_INDEX(IDX2_2) */ T2 on
T1.V3=T2.V3 and T1.V1=T2.V2 and T2.V1=?";
----
==== ENFORCE_JOIN_ORDER
@@ -371,6 +388,8 @@ SELECT /*+ MERGE_JOIN */ t1.v1, t2.v2 FROM TBL1 t1, TBL2 t2
WHERE t1.v3=t2.v3
SELECT /*+ NL_JOIN(TBL3,TBL1) */ t4.v1, t2.v2 FROM TBL1 t4 JOIN TBL2 t2 on
t1.v3=t2.v3 WHERE t2.v1 in (SELECT t3.v3 FROM TBL3 t3 JOIN TBL1 t4 on
t3.v2=t4.v2)
SELECT t1.v1, t2.v2 FROM TBL2 t1 JOIN TBL1 t2 on t1.v3=t2.v3 WHERE t2.v3 in
(SELECT /*+ NO_CNL_JOIN(TBL4) */ t3.v3 FROM TBL3 t3 JOIN TBL4 t4 on t3.v1=t4.v1)
+
+SELECT t4.v1, t2.v2 FROM TBL1 t4 JOIN TBL2 t2 on t1.v3=t2.v3 WHERE t2.v1 in
(SELECT t3.v3 FROM TBL3 t3 JOIN TBL1 /*+ NL_JOIN */ t4 on t3.v2=t4.v2)
----
==== EXPAND_DISTINCT_AGG
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
index c8d4e2f2b4c..949c2990bbf 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
@@ -25,7 +25,6 @@ import org.apache.calcite.rel.hint.HintPredicate;
import org.apache.calcite.rel.hint.HintPredicates;
import org.apache.calcite.rel.rules.CoreRules;
import org.apache.calcite.rel.rules.JoinPushThroughJoinRule;
-import
org.apache.ignite.internal.processors.query.calcite.rel.logical.IgniteLogicalTableScan;
/**
* Holds supported SQL hints and their settings.
@@ -76,7 +75,7 @@ public enum HintDefinition {
NO_INDEX {
/** {@inheritDoc} */
@Override public HintPredicate predicate() {
- return (hint, rel) -> rel instanceof IgniteLogicalTableScan;
+ return HintPredicates.TABLE_SCAN;
}
/** {@inheritDoc} */
@@ -102,7 +101,7 @@ public enum HintDefinition {
MERGE_JOIN {
/** {@inheritDoc} */
@Override public HintPredicate predicate() {
- return HintPredicates.JOIN;
+ return joinHintPredicate();
}
/** {@inheritDoc} */
@@ -128,7 +127,7 @@ public enum HintDefinition {
NL_JOIN {
/** {@inheritDoc} */
@Override public HintPredicate predicate() {
- return HintPredicates.JOIN;
+ return joinHintPredicate();
}
/** {@inheritDoc} */
@@ -154,7 +153,7 @@ public enum HintDefinition {
CNL_JOIN {
/** {@inheritDoc} */
@Override public HintPredicate predicate() {
- return HintPredicates.JOIN;
+ return joinHintPredicate();
}
/** {@inheritDoc} */
@@ -176,6 +175,15 @@ public enum HintDefinition {
}
};
+ /**
+ * @return Hint predicate for join hints.
+ */
+ private static HintPredicate joinHintPredicate() {
+ // HintPredicates.VALUES might be mentioned too. But
RelShuttleImpl#visit(LogicalValues) does nothing and ignores
+ // setting any hints.
+ return HintPredicates.or(HintPredicates.JOIN,
HintPredicates.TABLE_SCAN);
+ }
+
/**
* @return Hint predicate which limits redundant hint copying and reduces
mem/cpu consumption.
*/
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintUtils.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintUtils.java
index 7cc01556719..40c267ded09 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintUtils.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintUtils.java
@@ -61,13 +61,21 @@ public final class HintUtils {
}
/**
- * @return Hints filtered with {@code hintDefs} and suitable for {@code
rel}.
+ * @return Hints of {@code rel} filtered with {@code hintDefs}.
* @see HintStrategyTable#apply(List, RelNode)
* @see #filterHints(RelNode, Collection, List)
*/
public static List<RelHint> hints(RelNode rel, HintDefinition... hintDefs)
{
- return rel.getCluster().getHintStrategies()
- .apply(filterHints(rel, allRelHints(rel),
Arrays.asList(hintDefs)), rel);
+ return hints(rel, allRelHints(rel), hintDefs);
+ }
+
+ /**
+ * @return Hints filtered with {@code hintDefs} and suitable for {@code
rel}.
+ * @see HintStrategyTable#apply(List, RelNode)
+ * @see #filterHints(RelNode, Collection, List)
+ */
+ public static List<RelHint> hints(RelNode rel, Collection<RelHint> hints,
HintDefinition... hintDefs) {
+ return rel.getCluster().getHintStrategies().apply(filterHints(rel,
hints, Arrays.asList(hintDefs)), rel);
}
/**
@@ -78,6 +86,17 @@ public final class HintUtils {
return rel instanceof Hintable ? ((Hintable)rel).getHints() :
Collections.emptyList();
}
+ /**
+ * @return Only noninherited hints of {@code rel} if it is a {@code
Hintable}. If is not or has no hints, empty
+ * collection.
+ * @see Hintable#getHints()
+ */
+ public static List<RelHint> nonInheritedRelHints(RelNode rel) {
+ return rel instanceof Hintable
+ ? ((Hintable)rel).getHints().stream().filter(hint ->
hint.inheritPath.isEmpty()).collect(Collectors.toList())
+ : Collections.emptyList();
+ }
+
/**
* @return Distinct hints within {@code hints} filtered with {@code
hintDefs}, {@link HintOptionsChecker} and
* removed inherit pathes.
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/logical/IgniteLogicalTableScan.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/logical/IgniteLogicalTableScan.java
index ed973c4ec36..caa08a90aef 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/logical/IgniteLogicalTableScan.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/logical/IgniteLogicalTableScan.java
@@ -36,7 +36,7 @@ public class IgniteLogicalTableScan extends
ProjectableFilterableTableScan {
RelOptCluster cluster,
RelTraitSet traits,
RelOptTable tbl,
- List<RelHint> hints,
+ @Nullable List<RelHint> hints,
@Nullable List<RexNode> proj,
@Nullable RexNode cond,
@Nullable ImmutableBitSet requiredColumns
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/AbstractIgniteJoinConverterRule.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/AbstractIgniteJoinConverterRule.java
index 5a135b3a51e..a79322b0711 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/AbstractIgniteJoinConverterRule.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/AbstractIgniteJoinConverterRule.java
@@ -19,21 +19,24 @@ package
org.apache.ignite.internal.processors.query.calcite.rule;
import java.util.ArrayList;
import java.util.Collection;
+import java.util.Collections;
import java.util.EnumMap;
import java.util.HashMap;
import java.util.HashSet;
-import java.util.LinkedHashSet;
import java.util.Map;
import java.util.Set;
+import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.plan.volcano.RelSubset;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.logical.LogicalJoin;
import org.apache.ignite.internal.processors.query.calcite.hint.HintDefinition;
import org.apache.ignite.internal.processors.query.calcite.hint.HintUtils;
+import org.apache.ignite.internal.util.typedef.F;
import static org.apache.calcite.util.Util.last;
import static
org.apache.ignite.internal.processors.query.calcite.hint.HintDefinition.CNL_JOIN;
@@ -82,57 +85,70 @@ abstract class AbstractIgniteJoinConverterRule extends
AbstractIgniteConverterRu
/** */
private boolean disabledByHints(LogicalJoin join) {
- if (HintUtils.allRelHints(join).isEmpty())
+ Collection<TableScan> joinTables = joinTables(join);
+
+ Collection<RelHint> rawHints = new ArrayList<>();
+
+ // Table hints have a bigger priority and go first.
+ joinTables.forEach(t ->
rawHints.addAll(HintUtils.nonInheritedRelHints(t)));
+
+ rawHints.addAll(HintUtils.allRelHints(join));
+
+ if (rawHints.isEmpty())
return false;
boolean ruleDisabled = false;
Map<String, Collection<HintDefinition>> hintedTables = new HashMap<>();
- Set<String> joinTbls = joinTblNames(join);
+ Set<String> joinTblNames = F.isEmpty(joinTables)
+ ? Collections.emptySet()
+ : joinTables.stream().map(t ->
last(t.getTable().getQualifiedName())).collect(Collectors.toSet());
- assert joinTbls.size() < 3;
+ Set<String> matchedTbls;
- for (RelHint hint : HintUtils.hints(join, ALL_HINTS)) {
- Set<String> matchedTbls = hint.listOptions.isEmpty() ? joinTbls :
new HashSet<>(hint.listOptions);
+ for (RelHint hint : HintUtils.hints(join, rawHints, ALL_HINTS)) {
+ if (hint.listOptions.isEmpty())
+ matchedTbls = joinTblNames;
+ else {
+ matchedTbls = new HashSet<>(hint.listOptions);
- if (!hint.listOptions.isEmpty())
- matchedTbls.retainAll(joinTbls);
+ matchedTbls.retainAll(joinTblNames);
- if (matchedTbls.isEmpty())
- continue;
+ // Do not skip if the hint has no option. It can be a
'global', request-level hint.
+ if (matchedTbls.isEmpty())
+ continue;
+ }
HintDefinition curHintDef = HintDefinition.valueOf(hint.hintName);
boolean curHintIsDisable = !HINTS.containsKey(curHintDef);
- boolean unableToProc = false;
+ boolean skipHint = false;
- for (String tbl : joinTbls) {
+ for (String tbl : joinTblNames) {
Collection<HintDefinition> prevTblHints =
hintedTables.get(tbl);
if (prevTblHints == null)
continue;
- Set<HintDefinition> disabled = null;
+ Set<HintDefinition> allDisables = new HashSet<>();
+
+ if (curHintIsDisable)
+ allDisables.add(curHintDef);
for (HintDefinition prevTblHint : prevTblHints) {
boolean prevHintIsDisable =
!HINTS.containsKey(prevTblHint);
- if (prevHintIsDisable) {
- if (disabled == null)
- disabled = new HashSet<>();
-
- disabled.add(prevTblHint);
- }
+ if (prevHintIsDisable)
+ allDisables.add(prevTblHint);
// Prohibited: disabling all join types, combinations of
forcing and disabling same join type,
// forcing of different join types.
- if (curHintIsDisable && (disabled != null &&
disabled.size() == HINTS.size() - 1)
- || isMutuallyExclusive(curHintDef, prevTblHint))
- unableToProc = true;
+ if (curHintIsDisable && allDisables.size() == HINTS.size()
|| isMutuallyExclusive(curHintDef, prevTblHint))
+ skipHint = true;
}
}
- if (unableToProc) {
+ if (skipHint) {
HintUtils.skippedHint(join, hint, "This join type is already
disabled or forced to use before " +
"by previous hints");
@@ -164,15 +180,15 @@ abstract class AbstractIgniteJoinConverterRule extends
AbstractIgniteConverterRu
}
/** */
- protected static Set<String> joinTblNames(Join join) {
- Set<String> res = new LinkedHashSet<>();
+ protected static Collection<TableScan> joinTables(Join join) {
+ Collection<TableScan> res = new ArrayList<>(2);
for (RelNode in : join.getInputs()) {
if (in instanceof RelSubset)
in = ((RelSubset)in).getOriginal();
- if (in.getTable() != null)
- res.add(last(in.getTable().getQualifiedName()));
+ if (in instanceof TableScan)
+ res.add((TableScan)in);
}
return res;
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/CacheTableImpl.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/CacheTableImpl.java
index 1fc923767fd..f53d1d1fed9 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/CacheTableImpl.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/CacheTableImpl.java
@@ -24,6 +24,7 @@ import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexNode;
@@ -99,10 +100,10 @@ public class CacheTableImpl extends AbstractTable
implements IgniteCacheTable {
RelOptTable relOptTbl,
@Nullable List<RexNode> proj,
@Nullable RexNode cond,
- @Nullable ImmutableBitSet requiredColumns
+ @Nullable ImmutableBitSet requiredColumns,
+ @Nullable List<RelHint> hints
) {
- return IgniteLogicalTableScan.create(cluster, cluster.traitSet(),
relOptTbl, Collections.emptyList(), proj,
- cond, requiredColumns);
+ return IgniteLogicalTableScan.create(cluster, cluster.traitSet(),
relOptTbl, hints, proj, cond, requiredColumns);
}
/** {@inheritDoc} */
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/IgniteTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/IgniteTable.java
index 492fca9c445..ca1afb2d221 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/IgniteTable.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/IgniteTable.java
@@ -21,6 +21,7 @@ import java.util.Map;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.rel.core.TableScan;
+import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexNode;
@@ -56,8 +57,8 @@ public interface IgniteTable extends TranslatableTable {
RelDataType getRowType(RelDataTypeFactory typeFactory, ImmutableBitSet
requiredColumns);
/** {@inheritDoc} */
- @Override default TableScan toRel(RelOptTable.ToRelContext context,
RelOptTable relOptTable) {
- return toRel(context.getCluster(), relOptTable, null, null, null);
+ @Override default TableScan toRel(RelOptTable.ToRelContext ctx,
RelOptTable relOptTable) {
+ return toRel(ctx.getCluster(), relOptTable, null, null, null,
ctx.getTableHints());
}
/**
@@ -68,6 +69,7 @@ public interface IgniteTable extends TranslatableTable {
* @param proj List of required projections.
* @param cond Conditions to filter rows.
* @param requiredColumns Set of columns to extract from original row.
+ * @param hints Table hints.
* @return Table relational expression.
*/
IgniteLogicalTableScan toRel(
@@ -75,7 +77,8 @@ public interface IgniteTable extends TranslatableTable {
RelOptTable relOptTbl,
@Nullable List<RexNode> proj,
@Nullable RexNode cond,
- @Nullable ImmutableBitSet requiredColumns
+ @Nullable ImmutableBitSet requiredColumns,
+ @Nullable List<RelHint> hints
);
/**
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SystemViewTableImpl.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SystemViewTableImpl.java
index 132f663b647..2ac2a70bab3 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SystemViewTableImpl.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SystemViewTableImpl.java
@@ -24,6 +24,7 @@ import com.google.common.collect.ImmutableList;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexNode;
@@ -92,10 +93,10 @@ public class SystemViewTableImpl extends AbstractTable
implements IgniteTable {
RelOptTable relOptTbl,
@Nullable List<RexNode> proj,
@Nullable RexNode cond,
- @Nullable ImmutableBitSet requiredColumns
+ @Nullable ImmutableBitSet requiredColumns,
+ @Nullable List<RelHint> hints
) {
- return IgniteLogicalTableScan.create(cluster, cluster.traitSet(),
relOptTbl, Collections.emptyList(), proj,
- cond, requiredColumns);
+ return IgniteLogicalTableScan.create(cluster, cluster.traitSet(),
relOptTbl, hints, proj, cond, requiredColumns);
}
/** {@inheritDoc} */
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/TestTable.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/TestTable.java
index d34841c9453..d8b9617b5ca 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/TestTable.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/TestTable.java
@@ -31,6 +31,7 @@ import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeField;
@@ -140,10 +141,10 @@ public class TestTable implements IgniteCacheTable {
RelOptTable relOptTbl,
@Nullable List<RexNode> proj,
@Nullable RexNode cond,
- @Nullable ImmutableBitSet requiredColumns
+ @Nullable ImmutableBitSet requiredColumns,
+ @Nullable List<RelHint> hints
) {
- return IgniteLogicalTableScan.create(cluster, cluster.traitSet(),
relOptTbl, Collections.emptyList(), proj,
- cond, requiredColumns);
+ return IgniteLogicalTableScan.create(cluster, cluster.traitSet(),
relOptTbl, hints, proj, cond, requiredColumns);
}
/** {@inheritDoc} */
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java
index 3283436df5b..fa88732a1b6 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java
@@ -76,6 +76,17 @@ public class ForceIndexHintPlannerTest extends
AbstractPlannerTest {
assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
+ assertPlan("SELECT * FROM TBL2 /*+ FORCE_INDEX(IDX2_3) */ WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
+
+ // Table hint has a bigger priority.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 /*+
FORCE_INDEX(IDX2_2) */ WHERE val23=1 and " +
+ "val21=2 and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2",
"IDX2_2")));
+
+ // First table hint has a bigger priority.
+ assertPlan("SELECT * FROM TBL2 /*+ FORCE_INDEX(IDX2_2),
FORCE_INDEX(IDX2_3) */ WHERE val23=1 and " +
+ "val21=2 and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2",
"IDX2_2")));
+
assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
"and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2",
"IDX2_3")));
@@ -87,6 +98,10 @@ public class ForceIndexHintPlannerTest extends
AbstractPlannerTest {
schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
.or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+ assertPlan("SELECT * FROM TBL2 /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3), FORCE_INDEX(IDX2_3) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
"and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2",
"IDX2_3")));
}
@@ -105,6 +120,16 @@ public class ForceIndexHintPlannerTest extends
AbstractPlannerTest {
"t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")));
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 /*+ FORCE_INDEX(IDX1_3)
*/ t1 " + jt
+ + " JOIN TBL2 /*+ FORCE_INDEX(IDX2_2) */ t2 on
t1.val3=t2.val23 and t1.val1=t2.val22", schema,
+ nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))));
+
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 " + jt + " JOIN TBL2
/*+ FORCE_INDEX */ t2 on " +
+ "t1.val3=t2.val23 and t1.val1=t2.val22", schema,
+ nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ t1.val1, t2.val22 FROM
TBL1 t1 " + jt + " JOIN TBL2 " +
"t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
@@ -180,8 +205,18 @@ public class ForceIndexHintPlannerTest extends
AbstractPlannerTest {
assertPlan("SELECT /*+ NO_INDEX(IDX2_1), FORCE_INDEX(IDX2_1),
FORCE_INDEX(IDX2_3) */ * FROM TBL2 where " +
"val21=1 and val22=2 and val23=3", schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
+ assertPlan("SELECT /*+ NO_INDEX */ t1.val1 FROM TBL1 t1 where t1.val2
= " +
+ "(SELECT t2.val23 from TBL2 /*+ FORCE_INDEX(IDX2_3) */ t2 where
t2.val21=10 and t2.val23=10 and " +
+ "t2.val21=10)", schema, nodeOrAnyChild(isIndexScan("TBL2",
"IDX2_3")));
+
assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3), NO_INDEX */ * FROM TBL2
where " +
"val21=1 and val22=2 and val23=3", schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
+
+ // Table hint has a bigger priority.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_1), FORCE_INDEX(IDX2_3) */ *
FROM TBL2 /*+ NO_INDEX(IDX2_1) */ where " +
+ "val21=1 and val22=2 and val23=3", schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
+ assertPlan("SELECT /*+ NO_INDEX */ * FROM TBL2 /*+ FORCE_INDEX(IDX2_3)
*/ where " +
+ "val21=1 and val22=2 and val23=3", schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
}
/** */
@@ -195,6 +230,10 @@ public class ForceIndexHintPlannerTest extends
AbstractPlannerTest {
assertPlan("SELECT t1.val1 FROM TBL1 t1 where t1.val2 = (SELECT /*+
FORCE_INDEX(IDX1_2,IDX2_3) */ " +
"t2.val23 from TBL2 t2 where t2.val21=10 and t2.val23=10 and
t2.val21=10)", schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
+
+ assertPlan("SELECT t1.val1 FROM TBL1 t1 where t1.val2 = (SELECT
t2.val23 from TBL2 " +
+ "/*+ FORCE_INDEX(IDX2_3) */ t2 where t2.val21=10 and
t2.val23=10 and t2.val21=10)", schema,
+ nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")));
}
/** */
@@ -204,5 +243,10 @@ public class ForceIndexHintPlannerTest extends
AbstractPlannerTest {
"val2=val22 and val3=val23", schema,
nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))
.and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
.or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))))));
+
+ assertPlan("SELECT val1 FROM TBL1 /*+ FORCE_INDEX(IDX1_1) */, TBL2 /*+
FORCE_INDEX(IDX2_1,IDX2_2) */ WHERE " +
+ "val1=val21 and val2=val22 and val3=val23", schema,
nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))))));
}
}
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinTypeHintPlannerTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinTypeHintPlannerTest.java
index 06e6b4e25cc..b03ba932e72 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinTypeHintPlannerTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinTypeHintPlannerTest.java
@@ -167,6 +167,18 @@ public class JoinTypeHintPlannerTest extends
AbstractPlannerTest {
"t2.v2 FROM TBL1 t1 JOIN TBL2 t2 on t1.v3=t2.v3", schema);
assertTrue(!lsnr.check());
+
+ // Next check: ensures that joins missing table scan inputs are
processed correctly too.
+ lsnrLog.clearListeners();
+
+ lsnr = LogListener.matches("Skipped hint '" + NL_JOIN + '\'').build();
+
+ lsnrLog.registerListener(lsnr);
+
+ physicalPlan("SELECT /*+ " + MERGE_JOIN + ',' + NL_JOIN + " */ t1.v1,
t2.v2 FROM TBL1 t1 JOIN TBL2 t2 " +
+ "on t1.v3=t2.v3 where t2.v1 in (SELECT t3.v3 from TBL3 t3 JOIN
TBL1 t4 on t3.v2=t4.v2)", schema);
+
+ assertTrue(lsnr.check());
}
/**
@@ -361,6 +373,37 @@ public class JoinTypeHintPlannerTest extends
AbstractPlannerTest {
nodeOrAnyChild(isInstanceOf(joinRel)).negate(), disabledRules);
}
+ /**
+ * Test table join hints.
+ */
+ @Test
+ public void testTableHints() throws Exception {
+ String sqlTpl = "SELECT %s A2.A, T3.V3, T1.V2 FROM (SELECT 1 AS A, 2
AS B) A2 JOIN TBL3 %s T3 ON A2.B=A2.B " +
+ "JOIN TBL1 %s T1 on T3.V3=T1.V1 where T1.V2=5";
+
+ assertPlan(String.format(sqlTpl, "", "/*+ " + NL_JOIN + " */", "/*+ "
+ MERGE_JOIN + " */"), schema,
+
nodeOrAnyChild(isInstanceOf(IgniteNestedLoopJoin.class).and(input(1,
isTableScan("TBL3"))))
+ .and(nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class)
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL1")))))),
CORE_JOIN_REORDER_RULES);
+
+ // Table hint has a bigger priority. Leading CNL_JOIN is ignored.
+ assertPlan(String.format(sqlTpl, "/*+ " + CNL_JOIN + " */", "/*+ " +
NL_JOIN + " */", "/*+ " + MERGE_JOIN + " */"),
+ schema,
nodeOrAnyChild(isInstanceOf(IgniteNestedLoopJoin.class).and(input(1,
isTableScan("TBL3"))))
+ .and(nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class)
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL1")))))),
CORE_JOIN_REORDER_RULES);
+
+ // Leading query hint works only for the second join.
+ assertPlan(String.format(sqlTpl, "/*+ " + CNL_JOIN + " */", "/*+ " +
NL_JOIN + " */", ""), schema,
+
nodeOrAnyChild(isInstanceOf(IgniteNestedLoopJoin.class).and(input(1,
isTableScan("TBL3"))))
+
.and(nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL1")))))),
CORE_JOIN_REORDER_RULES);
+
+ // Table hint with wrong table name is ignored.
+ assertPlan(String.format(sqlTpl, "", "/*+ " + NL_JOIN + "(TBL1), " +
CNL_JOIN + " */", ""), schema,
+ nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)
+ .and(input(1, isTableScan("TBL3")))), CORE_JOIN_REORDER_RULES);
+ }
+
/**
* Tests disable-join-hint works for a sub-query.
*/
@@ -403,17 +446,47 @@ public class JoinTypeHintPlannerTest extends
AbstractPlannerTest {
/** */
@Test
public void testNestedHintOverrides() throws Exception {
- assertPlan("SELECT /*+ " + MERGE_JOIN + "(TBL1) */ t1.v1, t2.v2 FROM
TBL1 t1 JOIN TBL2 t2 on t1.v3=t2.v3 " +
+ assertPlan("SELECT /*+ " + MERGE_JOIN + " */ t1.v1, t2.v2 FROM TBL1 t1
JOIN TBL2 t2 on t1.v3=t2.v3 " +
"where t2.v1 in (SELECT t3.v3 from TBL3 t3 JOIN TBL1 t4 on
t3.v2=t4.v2)", schema,
nodeOrAnyChild(isInstanceOf(IgniteNestedLoopJoin.class)).negate()
- .and(nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class))),
CORE_JOIN_REORDER_RULES);
+
.and(nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)).negate()),
CORE_JOIN_REORDER_RULES);
- assertPlan("SELECT /*+ " + MERGE_JOIN + "(TBL1) */ t1.v1, t2.v2 FROM
TBL1 " +
- "t1 JOIN TBL2 t2 on t1.v3=t2.v3 where t2.v1 in " +
- "(SELECT /*+ " + CNL_JOIN + "(TBL1) */ t3.v3 from TBL3 t3 JOIN
TBL1 t4 on t3.v2=t4.v2)", schema,
-
nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class).and(hasNestedTableScan("TBL1")))
-
.and(nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)
-
.and(hasNestedTableScan("TBL3")).and(hasNestedTableScan("TBL1")))),
CORE_JOIN_REORDER_RULES);
+ assertPlan("SELECT /*+ " + CNL_JOIN + "(TBL1)," + MERGE_JOIN + " */
t1.v1, t2.v2 FROM TBL1 t1 " +
+ "JOIN TBL2 t2 on t1.v3=t2.v3 where t2.v1 in (SELECT t3.v3 from
TBL3 t3 JOIN TBL1 t4 on t3.v2=t4.v2)",
+ schema, predicateForNestedHintOverrides(false),
CORE_JOIN_REORDER_RULES);
+
+ assertPlan("SELECT /*+ " + MERGE_JOIN + " */ t1.v1, t2.v2 FROM TBL1 t1
JOIN TBL2 t2 on t1.v3=t2.v3 " +
+ "where t2.v1 in (SELECT /*+ " + CNL_JOIN + "(TBL1) */ t3.v3
from TBL3 t3 JOIN TBL1 t4 on t3.v2=t4.v2)",
+ schema, predicateForNestedHintOverrides(true),
CORE_JOIN_REORDER_RULES);
+
+ assertPlan("SELECT /*+ " + MERGE_JOIN + " */ t1.v1, t2.v2 FROM TBL1 t1
JOIN TBL2 t2 on t1.v3=t2.v3 " +
+ "where t2.v1 in (SELECT t3.v3 from TBL3 /*+ " + CNL_JOIN + "
*/ t3 JOIN TBL1 t4 on t3.v2=t4.v2)",
+ schema, predicateForNestedHintOverrides(true),
CORE_JOIN_REORDER_RULES);
+
+ assertPlan("SELECT /*+ " + MERGE_JOIN + " */ t1.v1, t2.v2 FROM TBL1 t1
JOIN TBL2 t2 on t1.v3=t2.v3 " +
+ "where t2.v1 in (SELECT t3.v3 from TBL3 t3 JOIN TBL1 /*+ " +
CNL_JOIN + " */ t4 on t3.v2=t4.v2)",
+ schema, predicateForNestedHintOverrides(true),
CORE_JOIN_REORDER_RULES);
+ }
+
+ /**
+ * @return A {@link Predicate} for {@link #testNestedHintOverrides()}
+ */
+ private Predicate<RelNode> predicateForNestedHintOverrides(boolean
t1MergeT2) {
+ Predicate<RelNode> res =
nodeOrAnyChild(isInstanceOf(IgniteNestedLoopJoin.class)).negate()
+
.and(nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)
+
.and(hasNestedTableScan("TBL3")).and(hasNestedTableScan("TBL1"))));
+
+ Predicate<RelNode> t1MergeT2Pred =
nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL1")))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL2"))))));
+
+ Predicate<RelNode> t1CnlT2Pred =
nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL1")))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL2"))))));
+
+ return t1MergeT2
+ ? res.and(t1MergeT2Pred).and(t1CnlT2Pred.negate())
+ : res.and(t1MergeT2Pred.negate()).and(t1CnlT2Pred);
}
/**
@@ -461,14 +534,13 @@ public class JoinTypeHintPlannerTest extends
AbstractPlannerTest {
.and(hasNestedTableScan("TBL2")))),
CORE_JOIN_REORDER_RULES);
// Check many duplicated disables doesn't erase other disables.
- sqlTpl = "SELECT %s t1.v1, t2.v2, t3.v3, t4.v1 FROM TBL1 t1, TBL2 t2,
TBL3 t3, TBL4 t4 where " +
- "t1.v1=t2.v1 and t1.v2=t2.v2 and t1.v3=t3.v3 and t1.v1=t4.v1";
+ sqlTpl = "SELECT %s t1.v1, t2.v2 FROM TBL1 t1, TBL2 t2 where
t1.v1=t2.v1";
- String hints = "/*+ " + NO_CNL_JOIN + ',' + NO_CNL_JOIN + "(TBL1), " +
NO_CNL_JOIN + "(TBL1,TBL2), " + NO_CNL_JOIN
- + "(TBL1,TBL4), " + NO_MERGE_JOIN + "(TBL1) */";
+ String hints = "/*+ " + NO_CNL_JOIN + ',' + NO_CNL_JOIN + "(TBL1), " +
NO_CNL_JOIN + "(TBL1,TBL2), "
+ + NO_NL_JOIN + "(TBL1) */";
assertPlan(String.format(sqlTpl, hints), schema,
-
nodeOrAnyChild(isInstanceOf(IgniteMergeJoin.class).and(hasChildThat(isTableScan("TBL1")))).negate()
+
nodeOrAnyChild(isInstanceOf(IgniteNestedLoopJoin.class).and(hasChildThat(isTableScan("TBL1")))).negate()
.and(nodeOrAnyChild(isInstanceOf(IgniteCorrelatedNestedLoopJoin.class)
.and(hasChildThat(isTableScan("TBL1")))).negate()),
CORE_JOIN_REORDER_RULES);
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/NoIndexHintPlannerTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/NoIndexHintPlannerTest.java
index 39b8d9e7548..e79a766f7a5 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/NoIndexHintPlannerTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/NoIndexHintPlannerTest.java
@@ -92,15 +92,28 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
physicalPlan("SELECT /*+ NO_INDEX, NO_INDEX(IDX2_1) */ * FROM TBL2
WHERE val2='v'", schema);
assertTrue(lsnr.check());
+
+ lsnrLog.clearListeners();
+
+ lsnr = LogListener.matches("Skipped hint 'NO_INDEX' with options
'IDX2_1'").times(1).build();
+
+ lsnrLog.registerListener(lsnr);
+
+ // Table hint has a bigger priority.
+ physicalPlan("SELECT /*+ NO_INDEX(IDX2_1) */ * FROM TBL2 /*+ NO_INDEX
*/ WHERE val2='v'", schema);
+
+ assertTrue(lsnr.check());
}
/** */
@Test
public void testCertainIndex() throws Exception {
assertNoAnyIndex("SELECT /*+ NO_INDEX */ * FROM TBL2 WHERE val2='v'");
+ assertNoAnyIndex("SELECT * FROM TBL2 /*+ NO_INDEX */ WHERE val2='v'");
// Checks lower-case idx name.
assertCertainIndex("SELECT /*+ NO_INDEX('idx1_1') */ * FROM TBL1 WHERE
val1='v'", "TBL1", "IDX1_1");
+ assertCertainIndex("SELECT * FROM TBL1 /*+ NO_INDEX('idx1_1') */ WHERE
val1='v'", "TBL1", "IDX1_1");
// Without quotes, Calcite's parser makes lower-case upper.
assertNoCertainIndex("SELECT /*+ NO_INDEX(idx1_1) */ * FROM TBL1 WHERE
val1='v'", "TBL1", "IDX1_1");
@@ -121,10 +134,16 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
// Dedicated hint for each index.
assertNoAnyIndex("SELECT /*+ NO_INDEX('idx1_1'), NO_INDEX(IDX1_1),
NO_INDEX(IDX1_23), NO_INDEX(IDX1_3) */ * " +
"FROM TBL1 WHERE val1='v' and val2='v' and val3='v'");
+ assertNoAnyIndex("SELECT /*+ NO_INDEX('idx1_1'), NO_INDEX(IDX1_1) */ *
FROM TBL1 " +
+ "/*+ NO_INDEX(IDX1_23), NO_INDEX(IDX1_3) */ WHERE val1='v' and
val2='v' and val3='v'");
// Index of the second table.
assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX2_3) */ t1.val3, t2.val3
FROM TBL1 t1, TBL2 t2 WHERE " +
"t1.val3='v' and t2.val3='v'", "TBL2", "IDX2_3");
+ assertNoCertainIndex("SELECT t1.val3, t2.val3 FROM TBL1 t1, TBL2 /*+
NO_INDEX(IDX2_3) */ t2 WHERE " +
+ "t1.val3='v' and t2.val3='v'", "TBL2", "IDX2_3");
+ assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX2_1) */ t1.val3, t2.val3
FROM TBL1 t1, " +
+ "TBL2 /*+ NO_INDEX(IDX2_3) */ t2 WHERE t1.val3='v' and t2.val3='v'
and t2.val1='v'", "TBL2", "IDX2_3");
}
/** */
@@ -140,6 +159,8 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
// Not-root hint.
assertNoCertainIndex("SELECT * FROM TBL1 t1, (select /*+
NO_INDEX(IDX2_3) */ * FROM TBL2 WHERE " +
"val3='v') t2 WHERE t1.val2='v'", "TBL2", "IDX2_3");
+ assertNoCertainIndex("SELECT * FROM TBL1 t1, (select * FROM TBL2 /*+
NO_INDEX(IDX2_3) */ WHERE " +
+ "val3='v') t2 WHERE t1.val2='v'", "TBL2", "IDX2_3");
}
/** */
@@ -158,6 +179,7 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
@Test
public void testOrderBy() throws Exception {
assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX1_23) */ val3 FROM TBL1
order by val2, val3", "TBL1", "IDX1_23");
+ assertNoCertainIndex("SELECT val3 FROM TBL1 /*+ NO_INDEX(IDX1_23) */
order by val2, val3", "TBL1", "IDX1_23");
}
/** */
@@ -173,7 +195,11 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
private void doTestAggregate(String op) throws Exception {
assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX2_3) */ " + op + "(val1)
FROM TBL2 group by val3", "TBL2", "IDX2_3");
+ assertNoCertainIndex("SELECT " + op + "(val1) FROM TBL2 /*+
NO_INDEX(IDX2_3) */ group by val3", "TBL2", "IDX2_3");
+
assertNoAnyIndex("SELECT /*+ NO_INDEX */ " + op + "(val1) FROM TBL2
group by val3");
+
+ assertNoAnyIndex("SELECT " + op + "(val1) FROM TBL2 /*+ NO_INDEX */
group by val3");
}
/** */
@@ -181,11 +207,15 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
public void testJoins() throws Exception {
assertNoAnyIndex("SELECT /*+ NO_INDEX */ t1.val1, t2.val2 FROM TBL1
t1, TBL2 t2 where " +
"t2.val3=t1.val3");
+ assertNoAnyIndex("SELECT t1.val1, t2.val2 FROM TBL1 /*+ NO_INDEX */
t1, TBL2 /*+ NO_INDEX */ t2 where " +
+ "t2.val3=t1.val3");
assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX1_3,IDX2_3) */ t1.val1,
t2.val2 FROM TBL1 t1, TBL2 t2 where " +
"t2.val3=t1.val3", "TBL1", "IDX1_3");
assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX1_3,IDX2_3) */ t1.val1,
t2.val2 FROM TBL1 t1, TBL2 t2 where " +
"t2.val3=t1.val3", "TBL2", "IDX2_3");
+ assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX1_3) */ t1.val1, t2.val2
FROM TBL1 t1, " +
+ "TBL2 /*+ NO_INDEX(IDX2_3) */ t2 where t2.val3=t1.val3", "TBL2",
"IDX2_3");
assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX1_3) */ t1.val1, t2.val2
FROM TBL1 t1 JOIN TBL2 t2 on " +
"t1.val3=t2.val3", "TBL1", "IDX1_3");
@@ -227,11 +257,21 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))
.and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23")).negate()));
+ assertPlan(String.format("SELECT t1.* FROM TBL1 /*+ NO_INDEX */ t1
where t1.val2='v' %s " +
+ "SELECT t2.* FROM TBL2 t2 where t2.val3='v'", operation),
schema,
+ nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23")).negate()));
+
assertPlan(String.format("SELECT t1.* FROM TBL1 t1 where t1.val2='v'
%s " +
"SELECT /*+ NO_INDEX(IDX2_3) */ t2.* FROM TBL2 t2 where
t2.val3='v'", operation), schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()
.and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))));
+ assertPlan(String.format("SELECT t1.* FROM TBL1 t1 where t1.val2='v'
%s " +
+ "SELECT t2.* FROM TBL2 /*+ NO_INDEX(IDX2_3) */ t2 where
t2.val3='v'", operation), schema,
+ nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))));
+
assertPlan(String.format("SELECT t1.* FROM TBL1 t1 where t1.val2='v'
%s " +
"SELECT /*+ NO_INDEX */ t2.* FROM TBL2 t2 where t2.val3='v'",
operation), schema,
nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()
@@ -263,12 +303,16 @@ public class NoIndexHintPlannerTest extends
AbstractPlannerTest {
assertNoAnyIndex("SELECT /*+ NO_INDEX(IDX1_3), NO_INDEX(IDX2_3) */ *
FROM TBL1 t1 WHERE t1.val3 = " +
"(SELECT val2 from TBL2 WHERE val3=" + valueOfT2Val3 + ')');
+ assertNoAnyIndex("SELECT /*+ NO_INDEX(IDX1_3) */ * FROM TBL1 t1 WHERE
t1.val3 = " +
+ "(SELECT val2 from TBL2 /*+ NO_INDEX */ WHERE val3=" +
valueOfT2Val3 + ')');
assertNoAnyIndex("SELECT /*+ NO_INDEX(IDX1_3, IDX2_3) */ * FROM TBL1
t1 WHERE t1.val3 = " +
"(SELECT val2 from TBL2 WHERE val3=" + valueOfT2Val3 + ')');
assertNoAnyIndex("SELECT /*+ NO_INDEX */ * FROM TBL1 t1 WHERE t1.val3
= " +
"(SELECT val2 from TBL2 WHERE val3=" + valueOfT2Val3 + ')');
assertNoAnyIndex("SELECT /*+ NO_INDEX(IDX1_3) */ * FROM TBL1 t1 WHERE
t1.val3 = " +
"(SELECT /*+ NO_INDEX(IDX2_3) */ val2 from TBL2 WHERE val3=" +
valueOfT2Val3 + ')');
+ assertNoAnyIndex("SELECT /*+ NO_INDEX(IDX1_3) */ * FROM TBL1 t1 WHERE
t1.val3 = " +
+ "(SELECT val2 from TBL2 /*+ NO_INDEX(IDX2_3) */ WHERE val3=" +
valueOfT2Val3 + ')');
}
/** */
