alex-plekhanov commented on code in PR #10910:
URL: https://github.com/apache/ignite/pull/10910#discussion_r1356998543
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/HintsTestSuite.java:
##########
@@ -27,7 +27,8 @@
@Suite.SuiteClasses({
CommonHintsPlannerTest.class,
NoIndexHintPlannerTest.class,
- ForceIndexHintPlannerTest.class
+ ForceIndexHintPlannerTest.class,
+ JoinOrderHintsPlannerTest.class
Review Comment:
Let's add `.` to the end of the string
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java:
##########
@@ -202,6 +215,42 @@ public void testBatch() throws Exception {
}
}
+ /** 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 sql = "EXPLAIN PLAN FOR SELECT p2.Name from Person1 p1 LEFT
JOIN Person2 p2 on p2.NAME=p1.NAME";
+
+ String scan1 = "Scan(table=[[PUBLIC, PERSON1]]";
+ String scan2 = "Scan(table=[[PUBLIC, PERSON2]]";
+
+ url += "&enforceJoinOrder=true";
+
+ connect();
+
+ try (ResultSet rs = stmt.executeQuery(sql)) {
Review Comment:
Let's check the same query with different tables order (p1 after p2).
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/RootQuery.java:
##########
@@ -457,6 +461,16 @@ public long remainingTime() {
return curTimeout <= 0 ? 0 : curTimeout;
}
+ /** */
+ private PlanningContext.Builder addQueryParams(PlanningContext.Builder
builder) {
+ SqlFieldsQuery sqlFieldsQuery = ctx.unwrap(SqlFieldsQuery.class);
Review Comment:
It's better to avoid using ignite-core classes inside pure calcite entities
(we still trying to provide compatibility for some classes between ignite2 and
ignite3). Let's process this flag the same way as isLocal flag (pass as
parameter to constructor, perhaps store it inside BaseQueryContext)
To reduce constuctor parameters count we can build BaseQueryContext outside
of RootQuery constructor and pass it as parameter.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinOrderHintsPlannerTest.java:
##########
@@ -0,0 +1,248 @@
+/*
+ * 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.rules.CoreRules;
+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#RIGHT} is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinRight() throws Exception {
+ String disabledRules =
Review Comment:
Why do we need to disable rules JOIN_COMMUTE, JoinPushThroughJoinRule.LEFT?
Aren't they disabled by ORDERED_JOINS hint?
Why do we need to disable MergeJoinConverter and CorrelatedNestedLoopJoin as
well?
The same for other tests.
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintsConfig.java:
##########
@@ -77,9 +81,38 @@ private 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()))
Review Comment:
Looks like just masking of ORDERED_JOINS (perhaps it's not correct condition
for all hints with disabled rules in future). And why do we need it only for
ORDERED_JOINS?
##########
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_OUTER and
overrides it if defined after.
Review Comment:
`CoreRules#JOIN_COMMUTE_OUTER` -> `CoreRules#JOIN_COMMUTE` ?
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java:
##########
@@ -202,6 +215,42 @@ public void testBatch() throws Exception {
}
}
+ /** 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 sql = "EXPLAIN PLAN FOR SELECT p2.Name from Person1 p1 LEFT
JOIN Person2 p2 on p2.NAME=p1.NAME";
+
+ String scan1 = "Scan(table=[[PUBLIC, PERSON1]]";
+ String scan2 = "Scan(table=[[PUBLIC, PERSON2]]";
+
+ url += "&enforceJoinOrder=true";
Review Comment:
Specific order of tests execution is not guaranteed by JUnit, so this change
can affect other test.
I think it's better to leave `url` field final and add url as parameter to
`connect` method.
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/PlannerHelper.java:
##########
@@ -73,8 +75,9 @@ public static IgniteRel optimize(SqlNode sqlNode,
IgnitePlanner planner, IgniteL
// Convert to Relational operators graph.
RelRoot root = planner.rel(sqlNode);
- planner.setDisabledRules(HintUtils.options(root.rel,
extractRootHints(root.rel),
- HintDefinition.DISABLE_RULE));
+ root = addExternalHint(root, planner);
+
+ planner.setDisabledRules(HintUtils.options(root.rel,
extractRootHints(root.rel), HintDefinition.DISABLE_RULE));
Review Comment:
We use two different mechanisms to disable rules (planner.setDisabledRules
and HintStrategy.excludedRules) can we use only one of them?
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java:
##########
@@ -435,7 +438,7 @@ public ExecutionService<Object[]> executionService() {
AtomicBoolean miss = new AtomicBoolean();
plan = queryPlanCache().queryPlan(
- new CacheKey(schema.getName(), sql,
contextKey(qryCtx), params),
+ new CacheKey(schema.getName(), sql,
contextKey(qryCtx), params, additionalQueryParams(qryCtx)),
Review Comment:
Let's reuse contextKey for `enforceJoinOrder` too, for example, you can
modify `contextKey` like this:
```
return sqlFieldsQry != null ? F.asList(sqlFieldsQry.isLocal(),
sqlFieldsQry.isEnforceJoinOrder()) : null;
```
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinOrderHintsPlannerTest.java:
##########
@@ -0,0 +1,248 @@
+/*
+ * 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.rules.CoreRules;
+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#RIGHT} is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinRight() throws Exception {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ CoreRules.JOIN_COMMUTE.toString(),
JoinPushThroughJoinRule.LEFT.toString());
+
+ // 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.ORDERED_JOINS.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 {@link JoinPushThroughJoinRule#LEFT} is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinLeft() throws Exception {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ CoreRules.JOIN_COMMUTE.toString(),
JoinPushThroughJoinRule.RIGHT.toString());
+
+ // 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.ORDERED_JOINS.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#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledLeftJoinTypeCommuting() throws Exception {
+ doTestDisabledJoinTypeCommuting("LEFT");
+ }
+
+ /**
+ * Tests commuting of RIGHT-to-LEFT JOIN is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @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 {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ JoinPushThroughJoinRule.LEFT.toString(),
JoinPushThroughJoinRule.RIGHT.toString());
+
+ // 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.ORDERED_JOINS.name(),
disabledRules, joinType, joinType);
+
+ assertPlan(sql, schema, hasChildThat(isInstanceOf(Join.class)
+ .and(j -> j.getJoinType() !=
JoinRelType.valueOf(joinType))).negate());
+ }
+
+ /**
+ * Tests the commuting of join inputs is disabled by by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledCommutingOfJoinInputs() throws Exception {
+ String disabledRules = String.format("DISABLE_RULE('%s', '%s', '%s',
'%s')", "MergeJoinConverter",
+ "CorrelatedNestedLoopJoin",
JoinPushThroughJoinRule.LEFT.toString(),
+ JoinPushThroughJoinRule.RIGHT.toString());
+
+ 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.ORDERED_JOINS.name(), disabledRules);
+
+ // Tests the plan has no commuted join inputs.
+ assertPlan(sql, schema, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, isTableScan("TBL3")))
+ .and(input(1, isTableScan("TBL1")))).negate());
+ }
+
+ /**
+ * 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.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";
+
+ 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 testDisabledCommutingOfJoinInputsInSubquery() throws Exception
{
+ String sqlTpl = "SELECT %s t1.v1, t3.v2 from TBL1 t1 JOIN TBL3 t3 on
t1.v3=t3.v3 where t1.v2 in " +
+ "(SELECT %s t2.v2 from TBL2 t2 JOIN TBL3 t3 on t2.v1=t3.v1)";
+
+ // Ensure that the sub-join has inputs order matching the sub-query:
'TBL2->TBL3'.
+ assertPlan(String.format(sqlTpl, "/*+ " + HintDefinition.ORDERED_JOINS
+ " */", ""), schema,
+ nodeOrAnyChild(isInstanceOf(Join.class).and(input(0,
nodeOrAnyChild(isTableScan("TBL1"))))
+ .and(input(1, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL2"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBl3")))))))));
+
+ assertPlan(String.format(sqlTpl, "", "/*+ " +
HintDefinition.ORDERED_JOINS + " */"), schema,
+ nodeOrAnyChild(isInstanceOf(Join.class).and(input(0,
nodeOrAnyChild(isTableScan("TBL1"))))
+ .and(input(1, 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.ORDERED_JOINS
+ + "' can't have any key-value option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("select /*+ " + HintDefinition.ORDERED_JOINS.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.ORDERED_JOINS +
"' can't have any option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
Review Comment:
Redundant
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintUtils.java:
##########
@@ -162,6 +169,11 @@ private NoInputsRelNodeWrap(RelNode relNode) {
return Collections.emptyList();
}
+ /** {@inheritDoc} */
+ @Override public RelNode getInput(int i) {
+ return getInputs().get(i);
Review Comment:
Input for "No inputs rel node" looks weird.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinOrderHintsPlannerTest.java:
##########
@@ -0,0 +1,248 @@
+/*
+ * 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.rules.CoreRules;
+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#RIGHT} is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinRight() throws Exception {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ CoreRules.JOIN_COMMUTE.toString(),
JoinPushThroughJoinRule.LEFT.toString());
+
+ // 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.ORDERED_JOINS.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 {@link JoinPushThroughJoinRule#LEFT} is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinLeft() throws Exception {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ CoreRules.JOIN_COMMUTE.toString(),
JoinPushThroughJoinRule.RIGHT.toString());
+
+ // 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.ORDERED_JOINS.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#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledLeftJoinTypeCommuting() throws Exception {
+ doTestDisabledJoinTypeCommuting("LEFT");
+ }
+
+ /**
+ * Tests commuting of RIGHT-to-LEFT JOIN is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @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 {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ JoinPushThroughJoinRule.LEFT.toString(),
JoinPushThroughJoinRule.RIGHT.toString());
+
+ // 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.ORDERED_JOINS.name(),
disabledRules, joinType, joinType);
+
+ assertPlan(sql, schema, hasChildThat(isInstanceOf(Join.class)
+ .and(j -> j.getJoinType() !=
JoinRelType.valueOf(joinType))).negate());
+ }
+
+ /**
+ * Tests the commuting of join inputs is disabled by by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledCommutingOfJoinInputs() throws Exception {
+ String disabledRules = String.format("DISABLE_RULE('%s', '%s', '%s',
'%s')", "MergeJoinConverter",
+ "CorrelatedNestedLoopJoin",
JoinPushThroughJoinRule.LEFT.toString(),
+ JoinPushThroughJoinRule.RIGHT.toString());
+
+ 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.ORDERED_JOINS.name(), disabledRules);
+
+ // Tests the plan has no commuted join inputs.
+ assertPlan(sql, schema, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, isTableScan("TBL3")))
+ .and(input(1, isTableScan("TBL1")))).negate());
+ }
+
+ /**
+ * 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.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";
+
+ 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 testDisabledCommutingOfJoinInputsInSubquery() throws Exception
{
+ String sqlTpl = "SELECT %s t1.v1, t3.v2 from TBL1 t1 JOIN TBL3 t3 on
t1.v3=t3.v3 where t1.v2 in " +
+ "(SELECT %s t2.v2 from TBL2 t2 JOIN TBL3 t3 on t2.v1=t3.v1)";
+
+ // Ensure that the sub-join has inputs order matching the sub-query:
'TBL2->TBL3'.
+ assertPlan(String.format(sqlTpl, "/*+ " + HintDefinition.ORDERED_JOINS
+ " */", ""), schema,
+ nodeOrAnyChild(isInstanceOf(Join.class).and(input(0,
nodeOrAnyChild(isTableScan("TBL1"))))
Review Comment:
Move `.and(input(0, nodeOrAnyChild(isTableScan("TBL1"))))` to the next line
to improve readability.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/JoinOrderHintsPlannerTest.java:
##########
@@ -0,0 +1,248 @@
+/*
+ * 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.rules.CoreRules;
+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#RIGHT} is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinRight() throws Exception {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ CoreRules.JOIN_COMMUTE.toString(),
JoinPushThroughJoinRule.LEFT.toString());
+
+ // 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.ORDERED_JOINS.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 {@link JoinPushThroughJoinRule#LEFT} is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledJoinPushThroughJoinLeft() throws Exception {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ CoreRules.JOIN_COMMUTE.toString(),
JoinPushThroughJoinRule.RIGHT.toString());
+
+ // 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.ORDERED_JOINS.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#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledLeftJoinTypeCommuting() throws Exception {
+ doTestDisabledJoinTypeCommuting("LEFT");
+ }
+
+ /**
+ * Tests commuting of RIGHT-to-LEFT JOIN is disabled by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @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 {
+ String disabledRules =
+ String.format("DISABLE_RULE('MergeJoinConverter',
'CorrelatedNestedLoopJoin', '%s', '%s')",
+ JoinPushThroughJoinRule.LEFT.toString(),
JoinPushThroughJoinRule.RIGHT.toString());
+
+ // 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.ORDERED_JOINS.name(),
disabledRules, joinType, joinType);
+
+ assertPlan(sql, schema, hasChildThat(isInstanceOf(Join.class)
+ .and(j -> j.getJoinType() !=
JoinRelType.valueOf(joinType))).negate());
+ }
+
+ /**
+ * Tests the commuting of join inputs is disabled by by {@link
HintDefinition#ORDERED_JOINS}.
+ */
+ @Test
+ public void testDisabledCommutingOfJoinInputs() throws Exception {
+ String disabledRules = String.format("DISABLE_RULE('%s', '%s', '%s',
'%s')", "MergeJoinConverter",
+ "CorrelatedNestedLoopJoin",
JoinPushThroughJoinRule.LEFT.toString(),
+ JoinPushThroughJoinRule.RIGHT.toString());
+
+ 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.ORDERED_JOINS.name(), disabledRules);
+
+ // Tests the plan has no commuted join inputs.
+ assertPlan(sql, schema, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, isTableScan("TBL3")))
+ .and(input(1, isTableScan("TBL1")))).negate());
+ }
+
+ /**
+ * 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.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";
+
+ 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 testDisabledCommutingOfJoinInputsInSubquery() throws Exception
{
+ String sqlTpl = "SELECT %s t1.v1, t3.v2 from TBL1 t1 JOIN TBL3 t3 on
t1.v3=t3.v3 where t1.v2 in " +
+ "(SELECT %s t2.v2 from TBL2 t2 JOIN TBL3 t3 on t2.v1=t3.v1)";
+
+ // Ensure that the sub-join has inputs order matching the sub-query:
'TBL2->TBL3'.
+ assertPlan(String.format(sqlTpl, "/*+ " + HintDefinition.ORDERED_JOINS
+ " */", ""), schema,
+ nodeOrAnyChild(isInstanceOf(Join.class).and(input(0,
nodeOrAnyChild(isTableScan("TBL1"))))
+ .and(input(1, nodeOrAnyChild(isInstanceOf(Join.class)
+ .and(input(0, nodeOrAnyChild(isTableScan("TBL2"))))
+ .and(input(1, nodeOrAnyChild(isTableScan("TBl3")))))))));
+
+ assertPlan(String.format(sqlTpl, "", "/*+ " +
HintDefinition.ORDERED_JOINS + " */"), schema,
+ nodeOrAnyChild(isInstanceOf(Join.class).and(input(0,
nodeOrAnyChild(isTableScan("TBL1"))))
Review Comment:
Move `.and(input(0, nodeOrAnyChild(isTableScan("TBL1"))))` to the next line
to improve readability.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
