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 79b9a4758ad IGNITE-20255 SQL Calcite: Add ENFORCE_JOIN_ORDER hint -
Fixes #10910.
79b9a4758ad is described below
commit 79b9a4758ad3cb8d85fb129a6968340b53c3e8f9
Author: Vladimir Steshin <[email protected]>
AuthorDate: Thu Oct 26 20:45:50 2023 +0300
IGNITE-20255 SQL Calcite: Add ENFORCE_JOIN_ORDER hint - Fixes #10910.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
docs/_docs/SQL/sql-calcite.adoc | 6 +-
.../query/calcite/CalciteQueryProcessor.java | 3 +-
.../processors/query/calcite/RootQuery.java | 6 +-
.../query/calcite/hint/HintDefinition.java | 32 +++
.../processors/query/calcite/hint/HintUtils.java | 14 +-
.../processors/query/calcite/hint/HintsConfig.java | 35 ++-
.../query/calcite/prepare/BaseQueryContext.java | 27 +-
.../processors/query/calcite/prepare/CacheKey.java | 2 +-
.../query/calcite/prepare/PlannerHelper.java | 35 ++-
.../query/calcite/prepare/PlannerPhase.java | 2 +-
.../query/calcite/jdbc/JdbcQueryTest.java | 65 ++++-
.../calcite/planner/hints/HintsTestSuite.java | 3 +-
.../planner/hints/JoinOrderHintsPlannerTest.java | 279 +++++++++++++++++++++
13 files changed, 491 insertions(+), 18 deletions(-)
diff --git a/docs/_docs/SQL/sql-calcite.adoc b/docs/_docs/SQL/sql-calcite.adoc
index a35689bc0ea..63e65244a2f 100644
--- a/docs/_docs/SQL/sql-calcite.adoc
+++ b/docs/_docs/SQL/sql-calcite.adoc
@@ -331,15 +331,15 @@ 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 > ?;
----
-==== ORDERED_JOINS
+==== ENFORCE_JOIN_ORDER
Forces join order as appears in a query. Fastens building of joins plan.
===== Example:
[source, SQL]
----
-SELECT /*+ ORDERED_JOINS */ T1.V1, T2.V1, T2.V2, T3.V1, T3.V2, T3.V3 FROM TBL1
T1 JOIN TBL2 T2 ON T1.V3=T2.V1 JOIN TBL3 T3 ON T2.V3=T3.V1 AND T2.V2=T3.V2
+SELECT /*+ ENFORCE_JOIN_ORDER */ T1.V1, T2.V1, T2.V2, T3.V1, T3.V2, T3.V3 FROM
TBL1 T1 JOIN TBL2 T2 ON T1.V3=T2.V1 JOIN TBL3 T3 ON T2.V3=T3.V1 AND T2.V2=T3.V2
-SELECT t1.v1, t3.v2 FROM TBL1 t1 JOIN TBL3 t3 on t1.v3=t3.v3 WHERE t1.v2 in
(SELECT /*+ ORDERED_JOINS */ t2.v2 FROM TBL2 t2 JOIN TBL3 t3 ON t2.v1=t3.v1)
+SELECT t1.v1, t3.v2 FROM TBL1 t1 JOIN TBL3 t3 on t1.v3=t3.v3 WHERE t1.v2 in
(SELECT /*+ ENFORCE_JOIN_ORDER */ t2.v2 FROM TBL2 t2 JOIN TBL3 t3 ON
t2.v1=t3.v1)
----
==== EXPAND_DISTINCT_AGG
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
index 5e60cd44b17..49720a4a242 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
@@ -563,7 +563,7 @@ public class CalciteQueryProcessor extends
GridProcessorAdapter implements Query
SqlFieldsQuery sqlFieldsQry = qryCtx.unwrap(SqlFieldsQuery.class);
- return sqlFieldsQry != null ? sqlFieldsQry.isLocal() : null;
+ return sqlFieldsQry != null ? F.asList(sqlFieldsQry.isLocal(),
sqlFieldsQry.isEnforceJoinOrder()) : null;
}
/** */
@@ -588,6 +588,7 @@ public class CalciteQueryProcessor extends
GridProcessorAdapter implements Query
params,
qryCtx,
fldsQry != null && fldsQry.isLocal(),
+ fldsQry != null && fldsQry.isEnforceJoinOrder(),
fldsQry != null ? fldsQry.getPartitions() : null,
exchangeSvc,
(q, ex) -> qryReg.unregister(q.id(), ex),
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/RootQuery.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/RootQuery.java
index 8a1f1c20555..31fafc96eee 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/RootQuery.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/RootQuery.java
@@ -108,6 +108,7 @@ public class RootQuery<RowT> extends Query<RowT> implements
TrackableQuery {
Object[] params,
QueryContext qryCtx,
boolean isLocal,
+ boolean forcedJoinOrder,
int[] parts,
ExchangeService exch,
BiConsumer<Query<RowT>, Throwable> unregister,
@@ -146,6 +147,7 @@ public class RootQuery<RowT> extends Query<RowT> implements
TrackableQuery {
.build()
)
.local(isLocal)
+ .forcedJoinOrder(forcedJoinOrder)
.partitions(parts)
.logger(log)
.build();
@@ -160,8 +162,8 @@ public class RootQuery<RowT> extends Query<RowT> implements
TrackableQuery {
* @param schema new schema.
*/
public RootQuery<RowT> childQuery(SchemaPlus schema) {
- return new RootQuery<>(sql, schema, params, QueryContext.of(cancel),
ctx.isLocal(), ctx.partitions(), exch, unregister, log,
- plannerTimeout, totalTimeout);
+ return new RootQuery<>(sql, schema, params, QueryContext.of(cancel),
ctx.isLocal(), ctx.isForcedJoinOrder(),
+ ctx.partitions(), exch, unregister, log, plannerTimeout,
totalTimeout);
}
/** */
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 748bbc59913..a20e2675282 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
@@ -17,8 +17,14 @@
package org.apache.ignite.internal.processors.query.calcite.hint;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Collections;
+import org.apache.calcite.plan.RelOptRule;
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;
/**
@@ -47,6 +53,25 @@ public enum HintDefinition {
}
},
+ /** Forces join order as appears in query. Fastens building of joins plan.
*/
+ ENFORCE_JOIN_ORDER {
+ /** {@inheritDoc} */
+ @Override public HintPredicate predicate() {
+ return HintPredicates.JOIN;
+ }
+
+ /** {@inheritDoc} */
+ @Override public HintOptionsChecker optionsChecker() {
+ return HintsConfig.OPTS_CHECK_EMPTY;
+ }
+
+ /** {@inheritDoc} */
+ @Override public Collection<RelOptRule> disabledRules() {
+ // CoreRules#JOIN_COMMUTE also disables
CoreRules.JOIN_COMMUTE_OUTER.
+ return Arrays.asList(CoreRules.JOIN_COMMUTE,
JoinPushThroughJoinRule.LEFT, JoinPushThroughJoinRule.RIGHT);
+ }
+ },
+
/** Disables indexes. */
NO_INDEX {
/** {@inheritDoc} */
@@ -86,4 +111,11 @@ public enum HintDefinition {
HintOptionsChecker optionsChecker() {
return HintsConfig.OPTS_CHECK_PLAIN;
}
+
+ /**
+ * @return Rules to excluded by current hint.
+ */
+ public Collection<RelOptRule> disabledRules() {
+ return Collections.emptyList();
+ }
}
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 dc3da9a7227..7cc01556719 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
@@ -138,13 +138,20 @@ public final class HintUtils {
+ ' ';
if (!relNode.getInputs().isEmpty())
- relNode = new NoInputsRelNodeWrap(relNode);
+ relNode = noInputsRelWrap(relNode);
log.debug(String.format("Skipped hint '%s' %sfor relation operator
'%s'. %s", hint.hintName,
hintOptions, RelOptUtil.toString(relNode,
SqlExplainLevel.EXPPLAN_ATTRIBUTES).trim(), reason));
}
}
+ /**
+ * @return A RelNode witout any inputs. For logging purposes.
+ */
+ public static RelNode noInputsRelWrap(RelNode rel) {
+ return new NoInputsRelNodeWrap(rel);
+ }
+
/** */
private static final class NoInputsRelNodeWrap extends AbstractRelNode {
/** Original rel. */
@@ -162,6 +169,11 @@ public final class HintUtils {
return Collections.emptyList();
}
+ /** {@inheritDoc} */
+ @Override public RelNode getInput(int i) {
+ throw new UnsupportedOperationException("Failed to pass any node
input. This a no-inputs node.");
+ }
+
/** {@inheritDoc} */
@Override protected RelDataType deriveRowType() {
return rel.getRowType();
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintsConfig.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintsConfig.java
index 2f4e902ef9d..a3471845169 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintsConfig.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintsConfig.java
@@ -18,10 +18,14 @@
package org.apache.ignite.internal.processors.query.calcite.hint;
import java.util.Arrays;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.hint.HintPredicate;
import org.apache.calcite.rel.hint.HintStrategy;
import org.apache.calcite.rel.hint.HintStrategyTable;
import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.util.Litmus;
+import org.apache.ignite.internal.util.typedef.F;
import org.jetbrains.annotations.Nullable;
/**
@@ -77,9 +81,38 @@ public final class HintsConfig {
public static HintStrategyTable buildHintTable() {
HintStrategyTable.Builder b =
HintStrategyTable.builder().errorHandler(Litmus.IGNORE);
+ RelOptRule[] disabledRulesTpl = new RelOptRule[0];
+
Arrays.stream(HintDefinition.values()).forEach(hintDef ->
- b.hintStrategy(hintDef.name(),
HintStrategy.builder(hintDef.predicate()).build()));
+ b.hintStrategy(hintDef.name(),
HintStrategy.builder(hintPredicate(hintDef))
+
.excludedRules(hintDef.disabledRules().toArray(disabledRulesTpl)).build()));
return b.build();
}
+
+ /**
+ * Adds hint options checker to {@link HintPredicate} if {@code hintDef}
has rules to exclude.
+ *
+ * @return Hint predicate.
+ */
+ private static HintPredicate hintPredicate(HintDefinition hintDef) {
+ if (F.isEmpty(hintDef.disabledRules()))
+ return hintDef.predicate();
+
+ return new HintPredicate() {
+ @Override public boolean apply(RelHint hint, RelNode rel) {
+ if (!hintDef.predicate().apply(hint, rel))
+ return false;
+
+ String optsErrMsg = hintDef.optionsChecker().apply(hint);
+
+ if (F.isEmpty(optsErrMsg))
+ return true;
+
+ HintUtils.skippedHint(rel, hint, optsErrMsg);
+
+ return false;
+ }
+ };
+ }
}
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/BaseQueryContext.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/BaseQueryContext.java
index e5f81d91d08..2f1e1bb5845 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/BaseQueryContext.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/BaseQueryContext.java
@@ -160,6 +160,9 @@ public final class BaseQueryContext extends
AbstractQueryContext {
/** */
private final boolean isLocal;
+ /** */
+ private final boolean forcedJoinOrder;
+
/** */
private final int[] parts;
@@ -171,6 +174,7 @@ public final class BaseQueryContext extends
AbstractQueryContext {
Context parentCtx,
IgniteLogger log,
boolean isLocal,
+ boolean forcedJoinOrder,
int[] parts
) {
super(Contexts.chain(parentCtx, cfg.getContext()));
@@ -182,6 +186,8 @@ public final class BaseQueryContext extends
AbstractQueryContext {
this.isLocal = isLocal;
+ this.forcedJoinOrder = forcedJoinOrder;
+
this.parts = parts;
qryCancel = unwrap(GridQueryCancel.class);
@@ -283,6 +289,11 @@ public final class BaseQueryContext extends
AbstractQueryContext {
return isLocal;
}
+ /** */
+ public boolean isForcedJoinOrder() {
+ return forcedJoinOrder;
+ }
+
/** */
public int[] partitions() {
if (parts != null)
@@ -312,7 +323,10 @@ public final class BaseQueryContext extends
AbstractQueryContext {
private IgniteLogger log = new NullLogger();
/** */
- private boolean isLocal = false;
+ private boolean isLocal;
+
+ /** */
+ private boolean forcedJoinOrder;
/** */
private int[] parts = null;
@@ -353,6 +367,15 @@ public final class BaseQueryContext extends
AbstractQueryContext {
return this;
}
+ /**
+ * @param forcedJoinOrder Forced join orders flag.
+ * @return Builder for chaining.
+ */
+ public Builder forcedJoinOrder(boolean forcedJoinOrder) {
+ this.forcedJoinOrder = forcedJoinOrder;
+ return this;
+ }
+
/**
* @param parts Array of partitions' numbers.
* @return Builder for chaining.
@@ -370,7 +393,7 @@ public final class BaseQueryContext extends
AbstractQueryContext {
* @return Planner context.
*/
public BaseQueryContext build() {
- return new BaseQueryContext(frameworkCfg, parentCtx, log, isLocal,
parts);
+ return new BaseQueryContext(frameworkCfg, parentCtx, log, isLocal,
forcedJoinOrder, parts);
}
}
}
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java
index 07cedd66a3c..f7aef65df62 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java
@@ -49,7 +49,7 @@ public class CacheKey {
this.query = query;
this.contextKey = contextKey;
paramTypes = params.length == 0 ? null :
- Arrays.stream(params).map(p -> (p != null) ? p.getClass() :
Void.class).toArray(Class[]::new);;
+ Arrays.stream(params).map(p -> (p != null) ? p.getClass() :
Void.class).toArray(Class[]::new);
}
/**
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerHelper.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerHelper.java
index b979c7997c6..7618793255f 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerHelper.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerHelper.java
@@ -22,6 +22,7 @@ import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;
+import java.util.stream.Stream;
import com.google.common.collect.ImmutableSet;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.RelTraitSet;
@@ -31,6 +32,7 @@ import org.apache.calcite.rel.RelRoot;
import org.apache.calcite.rel.core.SetOp;
import org.apache.calcite.rel.core.Spool;
import org.apache.calcite.rel.core.TableScan;
+import org.apache.calcite.rel.hint.Hintable;
import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexNode;
@@ -73,8 +75,9 @@ public class PlannerHelper {
// Convert to Relational operators graph.
RelRoot root = planner.rel(sqlNode);
- planner.setDisabledRules(HintUtils.options(root.rel,
extractRootHints(root.rel),
- HintDefinition.DISABLE_RULE));
+ root = addExternalOptions(root);
+
+ planner.setDisabledRules(HintUtils.options(root.rel,
extractRootHints(root.rel), HintDefinition.DISABLE_RULE));
RelNode rel = root.rel;
@@ -126,6 +129,34 @@ public class PlannerHelper {
}
}
+ /**
+ * Add external options as hints to {@code root.rel}.
+ *
+ * @return New or old root node.
+ */
+ private static RelRoot addExternalOptions(RelRoot root) {
+ if (!Commons.context(root.rel).isForcedJoinOrder())
+ return root;
+
+ if (!(root.rel instanceof Hintable)) {
+ Commons.context(root.rel).logger().warning("Unable to set hint " +
HintDefinition.ENFORCE_JOIN_ORDER
+ + " passed as an external parameter to the root relation
operator ["
+ +
RelOptUtil.toString(HintUtils.noInputsRelWrap(root.rel)).trim()
+ + "] because it is not a Hintable.");
+
+ return root;
+ }
+
+ List<RelHint> newHints =
Stream.concat(HintUtils.allRelHints(root.rel).stream(),
+
Stream.of(RelHint.builder(HintDefinition.ENFORCE_JOIN_ORDER.name()).build())).collect(Collectors.toList());
+
+ root = root.withRel(((Hintable)root.rel).withHints(newHints));
+
+ RelOptUtil.propagateRelHints(root.rel, false);
+
+ return root;
+ }
+
/**
* Extracts planner-level hints like 'DISABLE_RULE' if the root node is a
combining node like 'UNION'.
*/
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerPhase.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerPhase.java
index 8de3f3018b6..73b315ae3d6 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerPhase.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerPhase.java
@@ -207,8 +207,8 @@ public enum PlannerPhase {
CoreRules.MINUS_MERGE,
CoreRules.INTERSECT_MERGE,
CoreRules.UNION_REMOVE,
- CoreRules.JOIN_COMMUTE,
CoreRules.AGGREGATE_REMOVE,
+ // Works also as CoreRules#JOIN_COMMUTE and overrides it
if defined after.
CoreRules.JOIN_COMMUTE_OUTER,
// Useful of this rule is not clear now.
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java
index 97504e94ca1..ee0a1adc613 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java
@@ -76,14 +76,27 @@ public class JdbcQueryTest extends GridCommonAbstractTest {
/** {@inheritDoc} */
@Override protected void beforeTest() throws Exception {
startGrids(nodesCnt);
- conn = DriverManager.getConnection(url);
- conn.setSchema("PUBLIC");
- stmt = conn.createStatement();
+
+ connect(url);
assert stmt != null;
assert !stmt.isClosed();
}
+ /** */
+ private void connect(String url) throws Exception {
+ if (stmt != null)
+ stmt.close();
+
+ if (conn != null)
+ conn.close();
+
+ conn = DriverManager.getConnection(url);
+ conn.setSchema("PUBLIC");
+
+ stmt = conn.createStatement();
+ }
+
/** {@inheritDoc} */
@Override protected void afterTest() throws Exception {
if (stmt != null && !stmt.isClosed()) {
@@ -202,6 +215,52 @@ public class JdbcQueryTest extends GridCommonAbstractTest {
}
}
+ /** Test enforced join order parameter. */
+ @Test
+ public void testEnforcedJoinOrder() throws Exception {
+ stmt.execute("CREATE TABLE Person1(\"ID\" INT, PRIMARY KEY(\"ID\"),
\"NAME\" VARCHAR) WITH template=REPLICATED");
+ stmt.execute("CREATE TABLE Person2(\"ID\" INT, PRIMARY KEY(\"ID\"),
\"NAME\" VARCHAR) WITH template=REPLICATED");
+
+ for (int i = 0; i < 3; ++i)
+ stmt.execute(String.format("INSERT INTO Person1 VALUES (%d,
'Name')", i));
+
+ for (int i = 0; i < 100; ++i)
+ stmt.addBatch(String.format("INSERT INTO Person2 VALUES (%d,
'Name')", i));
+
+ stmt.executeBatch();
+
+ String scan1 = "Scan(table=[[PUBLIC, PERSON1]]";
+ String scan2 = "Scan(table=[[PUBLIC, PERSON2]]";
+
+ connect(url + "&enforceJoinOrder=true");
+
+ try (ResultSet rs = stmt.executeQuery("EXPLAIN PLAN FOR SELECT p2.Name
from Person1 p1 LEFT JOIN Person2 " +
+ "p2 on p2.NAME=p1.NAME")) {
+ assertTrue(rs.next());
+
+ String plan = rs.getString(1);
+
+ // Joins as in the query.
+ assertTrue(plan.indexOf(scan1) < plan.indexOf(scan2));
+
+ // Join type is not changed.
+ assertTrue(plan.contains("joinType=[left]"));
+ }
+
+ try (ResultSet rs = stmt.executeQuery("EXPLAIN PLAN FOR SELECT /*+
DISABLE_RULE('NestedLoopJoinConverter') */ " +
+ "p2.Name from Person2 p2 RIGHT JOIN Person1 p1 on
p2.NAME=p1.NAME")) {
+ assertTrue(rs.next());
+
+ String plan = rs.getString(1);
+
+ // Joins as in the query.
+ assertTrue(plan.indexOf(scan1) > plan.indexOf(scan2));
+
+ // Join type is not changed.
+ assertTrue(plan.contains("joinType=[right]"));
+ }
+ }
+
/** Test batched execution of prepared statement. */
@Test
public void testBatchPrepared() throws Exception {
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/HintsTestSuite.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/HintsTestSuite.java
index 4271935d626..f54352e0fb0 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/HintsTestSuite.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/HintsTestSuite.java
@@ -27,7 +27,8 @@ import org.junit.runners.Suite;
@Suite.SuiteClasses({
CommonHintsPlannerTest.class,
NoIndexHintPlannerTest.class,
- ForceIndexHintPlannerTest.class
+ ForceIndexHintPlannerTest.class,
+ JoinOrderHintsPlannerTest.class,
})
public class HintsTestSuite {
}
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinOrderHintsPlannerTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinOrderHintsPlannerTest.java
new file mode 100644
index 00000000000..a0a9a54ea9d
--- /dev/null
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinOrderHintsPlannerTest.java
@@ -0,0 +1,279 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.processors.query.calcite.planner.hints;
+
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.core.SetOp;
+import org.apache.calcite.rel.rules.JoinPushThroughJoinRule;
+import org.apache.ignite.internal.processors.query.calcite.hint.HintDefinition;
+import
org.apache.ignite.internal.processors.query.calcite.planner.AbstractPlannerTest;
+import org.apache.ignite.internal.processors.query.calcite.planner.TestTable;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+import org.apache.ignite.internal.util.typedef.internal.U;
+import org.apache.ignite.testframework.LogListener;
+import org.apache.ignite.testframework.junits.logger.GridTestLog4jLogger;
+import org.apache.logging.log4j.Level;
+import org.junit.Test;
+
+/**
+ * Planner test for join order hints.
+ */
+public class JoinOrderHintsPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ int tblNum = 3;
+ int fldNum = 3;
+
+ TestTable[] tables = new TestTable[tblNum];
+ Object[] fields = new Object[tblNum * 2];
+
+ for (int f = 0; f < fldNum; ++f) {
+ fields[f * 2] = "V" + (f + 1);
+ fields[f * 2 + 1] = Integer.class;
+ }
+
+ // Tables with growing records number.
+ for (int t = 0; t < tables.length; ++t) {
+ tables[t] = createTable("TBL" + (t + 1), Math.min(1_000_000,
(int)Math.pow(10, t + 1)),
+ IgniteDistributions.broadcast(), fields);
+ }
+
+ schema = createSchema(tables);
+ }
+
+ /**
+ * Tests {@link JoinPushThroughJoinRule#LEFT} is disabled by {@link
HintDefinition#ENFORCE_JOIN_ORDER}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinLeft() throws Exception {
+ // Disabling some join rules simplifies exposing of commuted and/or
re-ordered joins.
+ String disabledRules = "DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin')";
+
+ // Tests swapping of joins is disabled and the order appears in the
query, 'TBL3 -> TBL2 -> TBL1':
+ // Join
+ // Join
+ // TableScan(TBL3)
+ // TableScan(TBL2)
+ // TableScan(TBL1)
+ String sql = String.format("select /*+ %s, %s */ t3.* from TBL3 t3,
TBL2 t2, TBL1 t1 where t1.v1=t3.v1 and " +
+ "t1.v2=t2.v2", HintDefinition.ENFORCE_JOIN_ORDER.name(),
disabledRules);
+
+ assertPlan(sql, schema, hasChildThat(isInstanceOf(Join.class)
+ .and(input(0, isInstanceOf(Join.class)
+ .and(input(0, isTableScan("TBL3")))
+ .and(input(1, isTableScan("TBL2")))))
+ .and(input(1, isTableScan("TBL1")))));
+ }
+
+ /**
+ * Tests commuting of LEFT-to-RIGHT JOIN is disabled by {@link
HintDefinition#ENFORCE_JOIN_ORDER}.
+ */
+ @Test
+ public void testDisabledLeftJoinTypeCommuting() throws Exception {
+ doTestDisabledJoinTypeCommuting("LEFT");
+ }
+
+ /**
+ * Tests commuting of RIGHT-to-LEFT JOIN is disabled by {@link
HintDefinition#ENFORCE_JOIN_ORDER}.
+ */
+ @Test
+ public void testDisabledRightJoinTypeCommuting() throws Exception {
+ doTestDisabledJoinTypeCommuting("RIGHT");
+ }
+
+ /**
+ * Tests commuting of {@code joinType} is disabled.
+ *
+ * @param joinType LEFT or RIGHT JOIN type to test in upper case.
+ */
+ private void doTestDisabledJoinTypeCommuting(String joinType) throws
Exception {
+ // Disabling some join rules simplifies exposing of commuted and/or
re-ordered joins.
+ String disabledRules = "DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin')";
+
+ // Tests commuting of the join type is disabled.
+ String sql = String.format("select /*+ %s, %s */ t3.* from TBL2 t2 %s
JOIN TBL1 t1 on t2.v2=t1.v1 %s JOIN " +
+ "TBL3 t3 on t2.v1=t3.v3",
HintDefinition.ENFORCE_JOIN_ORDER.name(), disabledRules, joinType, joinType);
+
+ assertPlan(sql, schema, hasChildThat(isInstanceOf(Join.class)
+ .and(j -> j.getJoinType() !=
JoinRelType.valueOf(joinType))).negate());
+ }
+
+ /**
+ * Tests {@link JoinPushThroughJoinRule#RIGHT} is disabled by {@link
HintDefinition#ENFORCE_JOIN_ORDER}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinRight() throws Exception {
+ // Disabling some join rules simplifies exposing of commuted and/or
re-ordered joins.
+ String disabledRules = "DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin')";
+
+ // Tests the swapping of joins is disabled and the order appears as in
the query, 'TBL1->TBL2->TBL3':
+ // Join
+ // Join
+ // TableScan(TBL1)
+ // TableScan(TBL2)
+ // TableScan(TBL3)
+ String sql = String.format("select /*+ %s, %s */ t3.* from TBL1 t1,
TBL2 t2, TBL3 t3 where t1.v1=t3.v1 and " +
+ "t1.v2=t2.v2", HintDefinition.ENFORCE_JOIN_ORDER.name(),
disabledRules);
+
+ assertPlan(sql, schema, hasChildThat(isInstanceOf(Join.class)
+ .and(input(0, isInstanceOf(Join.class)
+ .and(input(0, isTableScan("TBL1")))
+ .and(input(1, isTableScan("TBL2")))))
+ .and(input(1, isTableScan("TBL3")))));
+ }
+
+ /**
+ * Tests the commuting of join inputs is disabled by {@link
HintDefinition#ENFORCE_JOIN_ORDER}.
+ */
+ @Test
+ public void testDisabledCommutingOfJoinInputs() throws Exception {
+ // Disabling some join rules simplifies exposing of commuted and/or
re-ordered joins.
+ String disabledRules = "DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin')";
+
+ String sql = String.format("select /*+ %s, %s */ t3.* from TBL1 t1
JOIN TBL3 t3 on t1.v1=t3.v3 JOIN TBL2 t2 on " +
+ "t2.v2=t1.v1", HintDefinition.ENFORCE_JOIN_ORDER.name(),
disabledRules);
+
+ // Tests the plan has no commuted join inputs.
+ assertPlan(sql, schema, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, isTableScan("TBL1")))
+ .and(input(1, isTableScan("TBL3")))));
+ }
+
+ /**
+ * Tests that the sub-join has inputs order matching the sub-query.
+ */
+ @Test
+ public void testDisabledCommutingOfJoinInputsInSubquery() throws Exception
{
+ String sqlTpl = "SELECT %s t2.v1, t3.v2 from TBL2 t2 JOIN TBL3 t3 on
t2.v1=t3.v1 where t2.v2 in " +
+ "(SELECT %s t2.v2 from TBL2 t2 JOIN TBL3 t3 on t2.v2=t3.v3)";
+
+ // Tests the hint is applied for the whole query.
+ assertPlan(String.format(sqlTpl, "/*+ " +
HintDefinition.ENFORCE_JOIN_ORDER + " */", ""), schema,
+ nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL2"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL3")))))))
+ .and(input(1, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL2"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL3")))))))));
+
+ // Tests the hint is applied for the sub-query.
+ assertPlan(String.format(sqlTpl, "", "/*+ " +
HintDefinition.ENFORCE_JOIN_ORDER + " */"), schema,
+ nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0,
nodeOrAnyChild(isTableScan("TBL2").or(isTableScan("TBL3")))))
+ .and(input(1, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL2"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL3")))))))));
+ }
+
+ /**
+ * Tests join plan building duration. Without enabled forced order, takes
too long.
+ */
+ @Test
+ public void testJoinPlanBuildingDuration() throws Exception {
+ // Just a 3-tables join.
+ String sql = "SELECT /*+ " + HintDefinition.ENFORCE_JOIN_ORDER + " */
T1.V1, T2.V1, T2.V2, T3.V1, T3.V2, T3.V3 " +
+ "FROM TBL1 T1 JOIN TBL2 T2 ON T1.V3=T2.V1 JOIN TBL3 T3 ON
T2.V3=T3.V1 AND T2.V2=T3.V2";
+
+ long time = 0;
+
+ // Heat a bit and measure only the last run.
+ for (int i = 0; i < 6; ++i) {
+ time = System.nanoTime();
+
+ physicalPlan(sql, schema);
+
+ time = U.nanosToMillis(System.nanoTime() - time);
+
+ log.info("Plan building took " + time + "ms.");
+ }
+
+ assertTrue("Plan building took too long: " + time + "ms.", time <
3000L);
+ }
+
+ /** */
+ @Test
+ public void testUnions() throws Exception {
+ String sqlTpl = "SELECT %s t2.v1, t3.v2 from TBL2 t2 JOIN TBL3 t3 on
t2.v1=t3.v1 UNION ALL " +
+ "SELECT %s t1.v1, t2.v2 from TBL1 t1 JOIN TBL2 t2 on t1.v1=t2.v2";
+
+ String hint = HintDefinition.ENFORCE_JOIN_ORDER.toString();
+
+ assertPlan(String.format(sqlTpl, "/*+ " + hint + " */", ""), schema,
+ nodeOrAnyChild(isInstanceOf(SetOp.class)
+ .and(input(0, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL2"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL3")))))))));
+
+ assertPlan(String.format(sqlTpl, "", "/*+ " + hint + " */"), schema,
+ nodeOrAnyChild(isInstanceOf(SetOp.class)
+ .and(input(1, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL1"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL2")))))))));
+
+ assertPlan(String.format(sqlTpl, "/*+ " + hint + " */", "/*+ " + hint
+ " */"), schema,
+ nodeOrAnyChild(isInstanceOf(SetOp.class)
+ .and(input(1, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL1"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL2")))))))
+ .and(input(0, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL2"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBL3")))))))));
+ }
+
+ /** */
+ @Test
+ public void testWrongParams() throws Exception {
+ LogListener lsnr = LogListener.matches("Hint '" +
HintDefinition.ENFORCE_JOIN_ORDER
+ + "' can't have any key-value option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("select /*+ " + HintDefinition.ENFORCE_JOIN_ORDER.name()
+ "(a='b') */ t3.* from TBL1 t1, " +
+ "TBL2 t2, TBL3 t3 where t1.v1=t3.v1 and t1.v2=t2.v2", schema);
+
+ assertTrue(lsnr.check());
+
+ lsnrLog.clearListeners();
+
+ lsnr = LogListener.matches("Hint '" +
HintDefinition.ENFORCE_JOIN_ORDER + "' can't have any option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ physicalPlan("select /*+ " + HintDefinition.ENFORCE_JOIN_ORDER.name()
+ "(OPTION) */ t3.* from TBL1 t1, " +
+ "TBL2 t2, TBL3 t3 where t1.v1=t3.v1 and t1.v2=t2.v2", schema);
+
+ assertTrue(lsnr.check());
+ }
+}
