alex-plekhanov commented on code in PR #10902:
URL: https://github.com/apache/ignite/pull/10902#discussion_r1334412102
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/logical/ExposeIndexRule.java:
##########
@@ -77,13 +88,63 @@ private static boolean preMatch(IgniteLogicalTableScan
scan) {
assert !indexes.isEmpty();
+ IgniteBiTuple<List<IgniteLogicalIndexScan>, Boolean> hintedIndexes =
processHints(scan, indexes);
+
+ indexes = hintedIndexes.get1();
+
+ if (indexes.isEmpty())
+ return;
+
+ if (hintedIndexes.get2())
+ cluster.getPlanner().prune(scan);
+
Map<RelNode, RelNode> equivMap = new HashMap<>(indexes.size());
for (int i = 1; i < indexes.size(); i++)
equivMap.put(indexes.get(i), scan);
call.transformTo(F.first(indexes), equivMap);
}
+ /**
+ * @return Actual indixes list and prune-table-scan flag if any index is
forced to use.
+ */
+ private IgniteBiTuple<List<IgniteLogicalIndexScan>, Boolean>
processHints(TableScan scan,
Review Comment:
Code style: each argument should be on it's own line for multi-line method
declaration.
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintUtils.java:
##########
@@ -0,0 +1,141 @@
+/*
+ * 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.hint;
+
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Set;
+import java.util.stream.Collectors;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.hint.HintStrategyTable;
+import org.apache.calcite.rel.hint.Hintable;
+import org.apache.calcite.rel.hint.RelHint;
+import org.apache.calcite.rel.logical.LogicalAggregate;
+import org.apache.calcite.sql.SqlExplainLevel;
+import org.apache.ignite.IgniteLogger;
+import
org.apache.ignite.internal.processors.query.calcite.prepare.BaseQueryContext;
+import org.apache.ignite.internal.processors.query.calcite.util.Commons;
+import org.apache.ignite.internal.util.typedef.F;
+
+import static
org.apache.ignite.internal.processors.query.calcite.hint.HintDefinition.EXPAND_DISTINCT_AGG;
+
+/**
+ * Base class for working with Calcite's SQL hints.
+ */
+public final class HintUtils {
+ /** */
+ private HintUtils() {
+ // No-op.
+ }
+
+ /**
+ * @return Combined list options of all {@code hints} filtered with {@code
hintDef}.
+ * @see #filterHints(RelNode, Collection, List)
+ */
+ public static Collection<String> options(RelNode rel, Collection<RelHint>
hints, HintDefinition hintDef) {
+ return F.flatCollections(filterHints(rel, hints,
Collections.singletonList(hintDef)).stream()
+ .map(h -> h.listOptions).collect(Collectors.toList()));
+ }
+
+ /**
+ * @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, HintDefinition... hintDefs)
{
+ return rel.getCluster().getHintStrategies()
+ .apply(filterHints(rel, allRelHints(rel),
Arrays.asList(hintDefs)), rel);
+ }
+
+ /**
+ * @return 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> allRelHints(RelNode rel) {
+ return rel instanceof Hintable ? ((Hintable)rel).getHints() :
Collections.emptyList();
+ }
+
+ /**
+ * @return Distinct hints within {@code hints} filtered with {@code
hintDefs}, {@link HintOptionsChecker} and
+ * removed inherit pathes.
+ * @see HintOptionsChecker
+ * @see RelHint#inheritPath
+ */
+ private static List<RelHint> filterHints(RelNode rel, Collection<RelHint>
hints, List<HintDefinition> hintDefs) {
+ Set<String> requiredHintDefs =
hintDefs.stream().map(Enum::name).collect(Collectors.toSet());
+
+ List<RelHint> res = hints.stream().filter(h ->
requiredHintDefs.contains(h.hintName))
+ .map(h -> {
+ RelHint.Builder rb = RelHint.builder(h.hintName);
+
+ if (!h.listOptions.isEmpty())
+ rb.hintOptions(h.listOptions);
+ else if (!h.kvOptions.isEmpty())
+ rb.hintOptions(h.kvOptions);
+
+ return rb.build();
+ }).distinct().collect(Collectors.toList());
+
+ // Validate hint options.
+ Iterator<RelHint> it = res.iterator();
+
+ while (it.hasNext()) {
+ RelHint hint = it.next();
+
+ String optsErr =
HintDefinition.valueOf(hint.hintName).optionsChecker().apply(hint);
+
+ if (!F.isEmpty(optsErr)) {
+ skippedHint(rel, hint, optsErr);
+
+ it.remove();
+ }
+ }
+
+ return res;
+ }
+
+ /**
+ * @return {@code True} if {@code rel} is hinted with {@link
HintDefinition#EXPAND_DISTINCT_AGG}.
+ * {@code False} otherwise.
+ */
+ public static boolean isExpandDistinctAggregate(LogicalAggregate rel) {
+ return !hints(rel, EXPAND_DISTINCT_AGG).isEmpty()
+ &&
rel.getAggCallList().stream().anyMatch(AggregateCall::isDistinct);
+ }
+
+ /**
+ * Logs skipped hint.
+ */
+ public static void skippedHint(RelNode relNode, RelHint hint, String
reason) {
+ IgniteLogger log =
Commons.context(relNode).unwrap(BaseQueryContext.class).logger();
+
+ if (log.isDebugEnabled()) {
+ String hintOptions = hint.listOptions.isEmpty() ? "" : "with
options "
+ + hint.listOptions.stream().map(o -> '\'' + o +
'\'').collect(Collectors.joining(","))
+ + ' ';
+
+ log.debug(String.format("Skipped hint '%s' %sfor relation operator
'%s'. %s", hint.hintName,
+ hintOptions, RelOptUtil.toString(relNode,
SqlExplainLevel.EXPPLAN_ATTRIBUTES).trim(), reason));
Review Comment:
`RelOptUtil.toString` prints all plan starting from this node with all
inputs. Perhaps currently for TableScans and IndexScans it's ok, since scans
are terminal nodes, but generally, I think it's too verbose.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/CommonHintsPlannerTest.java:
##########
@@ -0,0 +1,122 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+import org.apache.ignite.testframework.LogListener;
+import org.apache.ignite.testframework.junits.logger.GridTestLog4jLogger;
+import org.apache.logging.log4j.Level;
+import org.junit.Test;
+
+/**
+ * Common test for SQL hints.
+ */
+public class CommonHintsPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ tbl = createTable("TBL", 100, IgniteDistributions.random(), "ID",
Integer.class, "VAL",
+ Integer.class).addIndex(QueryUtils.PRIMARY_KEY_INDEX,
0).addIndex("IDX", 1);
+
+ schema = createSchema(tbl);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /**
+ * Tests hint 'DISABLE_RULE' works for whole query despite it is not set
for the root node.
+ */
+ @Test
+ public void testDisableRuleInHeader() throws Exception {
+ assertPlan("SELECT /*+ DISABLE_RULE('ExposeIndexRule') */ VAL FROM TBL
UNION ALL " +
+ "SELECT VAL FROM TBL", schema,
nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate());
+
+ assertPlan("SELECT VAL FROM TBL where val=1 UNION ALL " +
+ "SELECT /*+ DISABLE_RULE('ExposeIndexRule') */ VAL FROM TBL",
schema,
+ nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate());
+ }
+
+ /** */
+ @Test
+ public void testWrongParamsDisableRule() throws Exception {
+ LogListener lsnr = LogListener.matches("Hint 'DISABLE_RULE' must have
at least one option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("SELECT /*+ DISABLE_RULE */ VAL FROM TBL", schema);
+
+ assertTrue(lsnr.check());
+
+ lsnrLog.registerListener(lsnr);
+
+ lsnrLog.clearListeners();
+
+ lsnr = LogListener.matches("Hint 'DISABLE_RULE' can't have any
key-value option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ physicalPlan("SELECT /*+ DISABLE_RULE(a='b') */ VAL FROM TBL", schema);
+
+ assertTrue(lsnr.check());
+ }
+
+ /** */
+ @Test
+ public void testWrongParamsExpandDistinct() throws Exception {
+ LogListener lsnr = LogListener.matches("Hint 'EXPAND_DISTINCT_AGG'
can't have any option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("SELECT /*+ EXPAND_DISTINCT_AGG(OPTION) */ MAX(VAL) FROM
TBL", schema);
+
+ assertTrue(lsnr.check());
+
+ lsnrLog.registerListener(lsnr);
Review Comment:
Redundant
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java:
##########
@@ -336,6 +337,166 @@ public void checkMultiColumnsInlineScan(
}
}
+ /** */
+ @Test
+ public void testNoIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER) WITH TEMPLATE=PARTITIONED");
+ executeSql("INSERT INTO t1 VALUES (1), (2), (30), (40)");
+ executeSql("CREATE INDEX t1_idx ON t1(i1)");
+
+ executeSql("CREATE TABLE t2(i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ for (int i = 0; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t2 VALUES (%d, %d)", i, i));
+
+ executeSql("CREATE INDEX t2_idx ON t2(i2)");
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX('T2_IDX') */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX,T2_IDX) */ i1, i3 FROM t1, t2
where i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i1, i3 FROM t1, t2 where
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX) */ i1, i3 FROM t1 JOIN t2 on
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT * FROM t1 WHERE i1 = (SELECT /*+ NO_INDEX(T2_IDX)
*/ i3 from t2 where i2=40)")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(40)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testForcedIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ executeSql("INSERT INTO t1 VALUES (1, 2, 3)");
+
+ executeSql("CREATE INDEX t1_idx1 ON t1(i1)");
+ executeSql("CREATE INDEX t1_idx2 ON t1(i2)");
+ executeSql("CREATE INDEX t1_idx3 ON t1(i3)");
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2 and i3=3")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX1")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T2_IDX3")))
+ .check();
+
+ testIdxUsedOnTable1();
+
+ for (int i = 1; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t1 VALUES (%d, %d, %d)", i +
1, i + 2, i + 3));
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2 and i3=3")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX1")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T2_IDX3")))
+ .check();
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsTableScan("PUBLIC",
"T1")))
+ .matches(CoreMatchers.anyOf(
+ QueryChecker.containsIndexScan("PUBLIC", "T1", "T1_IDX1"),
+ QueryChecker.containsIndexScan("PUBLIC", "T1", "T1_IDX2")))
+ .check();
+
+ testIdxUsedOnTable1();
+
+ executeSql("CREATE TABLE t2(i21 INTEGER, i22 INTEGER, i23 INTEGER)
WITH TEMPLATE=PARTITIONED");
+
+ for (int i = 99; i < 300; ++i)
+ executeSql(String.format("INSERT INTO t2 VALUES (%d, %d, %d)", i +
1, i + 1, i + 1));
+
+ executeSql("CREATE INDEX t2_idx1 ON t2(i21)");
+ executeSql("CREATE INDEX t2_idx2 ON t2(i22)");
+ executeSql("CREATE INDEX t2_idx3 ON t2(i23)");
+
+ assertQuery("SELECT i1, i22 FROM t1, t2 where i2=i22 and i3=i23 + 1")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T2"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX2")))
+ .check();
+
+ assertQuery("SELECT /*+ FORCE_INDEX(T1_IDX2) */ i1, i22 FROM t1, t2
where i2=i22 and i3=i23 + 1")
+ .matches(CoreMatchers.not(QueryChecker.containsTableScan("PUBLIC",
"T1")))
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T2"))
Review Comment:
Let's check only tables with explicitely set hints.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testJoins() throws Exception {
+ // Make sure there is no full tnl scan on TBL2 for INNER and LEFT.
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 LEFT JOIN TBL2 t2 on
t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
Review Comment:
We can't guarantee table scan without NO_INDEX hint. Let's check only
`nodeOrAnyChild(isIndexScan("TBL2",
"IDX2_2")).or(nodeOrAnyChild(isIndexScan("TBL2", "IDX23"))` for this and
similar queries
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/NoIndexHintPlannerTest.java:
##########
@@ -0,0 +1,316 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 index hints.
+ */
+public class NoIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ tbl1 = createTable("TBL1", 100, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", String.class, "VAL2", String.class, "VAL3", String.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_23", 2, 3)
+ .addIndex("IDX1_3", 3);
+
+ tbl2 = createTable("TBL2", 100_000, IgniteDistributions.single(),
"ID", Integer.class,
+ "VAL1", String.class, "VAL2", String.class, "VAL3", String.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testWrongParams() throws Exception {
+ LogListener lsnr = LogListener.matches("Skipped hint 'NO_INDEX' with
options 'IDX2_1','IDX2_1'")
+ .times(1).build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("SELECT /*+ NO_INDEX(IDX2_1,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);
+
+ physicalPlan("SELECT /*+ NO_INDEX, NO_INDEX(IDX2_1) */ * FROM TBL2
WHERE val2='v'", schema);
+
+ assertTrue(lsnr.check());
+ }
+
+ /** */
+ @Test
+ public void testCertainIndex() throws Exception {
+ assertNoAnyIndex("SELECT /*+ NO_INDEX */ * FROM TBL2 WHERE val2='v'");
+
+ // Checks lower-case idx name.
+ assertCertainIndex("SELECT /*+ NO_INDEX('idx1_1') */ * FROM TBL1 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");
+
+ assertCertainIndex("SELECT /*+ NO_INDEX(" +
QueryUtils.PRIMARY_KEY_INDEX +
+ ") */ * FROM TBL1 WHERE id = 0", "TBL1",
QueryUtils.PRIMARY_KEY_INDEX);
+ assertNoAnyIndex("SELECT /*+ NO_INDEX('" +
QueryUtils.PRIMARY_KEY_INDEX + "') */ * FROM TBL1 WHERE id = 0");
+
+ assertNoAnyIndex("SELECT /*+
NO_INDEX('idx1_1','IDX1_1','IDX1_23','IDX1_3') */ * FROM TBL1 WHERE val1='v' " +
+ "and val2='v' and val3='v'");
+
+ // Wrong names should be just skipped.
+ assertNoAnyIndex("SELECT " +
+ "/*+
NO_INDEX('UNEXISTING','idx1_1','UNEXISTING2','IDX1_1','UNEXISTING3','IDX1_23','IDX1_3')
*/ * " +
+ "FROM TBL1 WHERE val1='v' and val2='v' and val3='v'");
+
+ // Mixed with no-tbl-name hint.
+ assertNoAnyIndex("SELECT /*+ NO_INDEX('idx1_1'),
NO_INDEX(IDX1_1,IDX1_23,IDX1_3) */ * FROM TBL1 WHERE " +
+ "val1='v' and val2='v' and val3='v'");
+
+ // 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'");
+
+ // Index of the second table.
+ assertPlan("SELECT /*+ NO_INDEX(IDX2_3) */ t1.val3, t2.val3 FROM TBL1
t1, TBL2 t2 WHERE " +
+ "t1.val3='v' and t2.val3='v'", schema,
+ nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate());
+ }
+
+ /** */
+ @Test
+ public void testSecondQuery() throws Exception {
+ assertPlan("SELECT /*+ NO_INDEX(IDX1_23) */ * FROM TBL1 t1, (select *
FROM TBL2 WHERE " +
+ "val2='v') t2 WHERE t1.val2='v'", schema,
+ nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))));
+
+ // Propagated, pushed-down hint.
+ assertPlan("SELECT /*+ NO_INDEX(IDX2_3) */ * FROM TBL1 t1, (select *
FROM TBL2 WHERE " +
+ "val3='v') t2 WHERE t1.val2='v'", schema,
nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ // Not-root hint.
+ assertPlan("SELECT * FROM TBL1 t1, (select /*+ NO_INDEX(IDX2_3) */ *
FROM TBL2 WHERE " +
+ "val3='v') t2 WHERE t1.val2='v'", schema,
nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ // Wrong idx name for the table.
+ assertPlan("SELECT * FROM TBL1 t1, (select /*+ NO_INDEX(IDX1_23) */ *
FROM TBL2 WHERE " +
+ "val2='v') t2 WHERE t1.val2='v'", schema,
+ nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))));
+ }
+
+ /** */
+ @Test
+ public void testCorrelatedSubquery() throws Exception {
+ doTestDisabledInTable2Val3("t1.val3");
+ }
+
+ /** */
+ @Test
+ public void testSubquery() throws Exception {
+ doTestDisabledInTable2Val3("'v'");
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertCertainIndex("SELECT * FROM TBL1 order by val3", "TBL1",
"IDX1_3");
+
+ assertCertainIndex("SELECT * FROM TBL1 order by val2, val3", "TBL1",
"IDX1_23");
+ assertNoCertainIndex("SELECT /*+ NO_INDEX(IDX1_23) */ val3 FROM TBL1
order by val2, val3", "TBL1", "IDX1_23");
+ }
+
+ /** */
+ @Test
+ public void testAggregates() throws Exception {
+ doTestAggregate("sum");
+ doTestAggregate("avg");
+ doTestAggregate("min");
+ doTestAggregate("max");
+ }
+
+ /** */
+ private void doTestAggregate(String op) throws Exception {
+ assertCertainIndex("SELECT " + op + "(val1) FROM TBL2 group by val3",
"TBL2", "IDX2_3");
+
+ assertNoAnyIndex("SELECT /*+ NO_INDEX(IDX2_3) */ " + op + "(val1) FROM
TBL2 group by val3");
+
+ assertNoAnyIndex("SELECT /*+ NO_INDEX */ " + op + "(val1) FROM TBL2
group by val3");
+
+ assertCertainIndex("SELECT /*+ NO_INDEX(IDX1_1) */" + op + "(val1)
FROM TBL2 group by val3", "TBL2", "IDX2_3");
+ }
+
+ /** */
+ @Test
+ 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 /*+ NO_INDEX(IDX1_3,IDX2_3) */ t1.val1,
t2.val2 FROM TBL1 t1, TBL2 t2 where " +
+ "t2.val3=t1.val3");
+
+ assertPlan("SELECT /*+ NO_INDEX(IDX1_3) */ t1.val1, t2.val2 FROM TBL1
t1 JOIN TBL2 t2 on " +
+ "t1.val3=t2.val3", schema, nodeOrAnyChild(isIndexScan("TBL1",
"IDX1_3")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ NO_INDEX(IDX2_3) */ t1.val1, t2.val2 FROM TBL1
t1 JOIN TBL2 t2 on " +
+ "t1.val3=t2.val3", schema, nodeOrAnyChild(isIndexScan("TBL1",
"IDX1_3"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ // With a filter
+ assertPlan("SELECT /*+ NO_INDEX(IDX2_2) */ t1.val1, t2.val2 FROM TBL1
t1, TBL2 t2 where t1.val2='v' " +
+ "and t2.val2=t1.val2", schema, nodeOrAnyChild(isIndexScan("TBL2",
"IDX2_2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))));
+ }
+
+ /** */
+ @Test
+ public void testUnion() throws Exception {
+ doTestUnions("UNION");
+ }
+
+ /** */
+ @Test
+ public void testIntersect() throws Exception {
+ doTestUnions("INTERSECT");
+ }
+
+ /** */
+ private void doTestUnions(String operation) throws Exception {
Review Comment:
Let's rename to `doTestSetOps`
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java:
##########
@@ -336,6 +337,166 @@ public void checkMultiColumnsInlineScan(
}
}
+ /** */
+ @Test
+ public void testNoIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER) WITH TEMPLATE=PARTITIONED");
+ executeSql("INSERT INTO t1 VALUES (1), (2), (30), (40)");
+ executeSql("CREATE INDEX t1_idx ON t1(i1)");
+
+ executeSql("CREATE TABLE t2(i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ for (int i = 0; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t2 VALUES (%d, %d)", i, i));
+
+ executeSql("CREATE INDEX t2_idx ON t2(i2)");
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX('T2_IDX') */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX,T2_IDX) */ i1, i3 FROM t1, t2
where i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i1, i3 FROM t1, t2 where
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX) */ i1, i3 FROM t1 JOIN t2 on
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT * FROM t1 WHERE i1 = (SELECT /*+ NO_INDEX(T2_IDX)
*/ i3 from t2 where i2=40)")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(40)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testForcedIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ executeSql("INSERT INTO t1 VALUES (1, 2, 3)");
+
+ executeSql("CREATE INDEX t1_idx1 ON t1(i1)");
+ executeSql("CREATE INDEX t1_idx2 ON t1(i2)");
+ executeSql("CREATE INDEX t1_idx3 ON t1(i3)");
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2 and i3=3")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX1")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T2_IDX3")))
+ .check();
+
+ testIdxUsedOnTable1();
+
+ for (int i = 1; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t1 VALUES (%d, %d, %d)", i +
1, i + 2, i + 3));
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2 and i3=3")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX1")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T2_IDX3")))
+ .check();
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsTableScan("PUBLIC",
"T1")))
+ .matches(CoreMatchers.anyOf(
+ QueryChecker.containsIndexScan("PUBLIC", "T1", "T1_IDX1"),
+ QueryChecker.containsIndexScan("PUBLIC", "T1", "T1_IDX2")))
+ .check();
+
+ testIdxUsedOnTable1();
+
+ executeSql("CREATE TABLE t2(i21 INTEGER, i22 INTEGER, i23 INTEGER)
WITH TEMPLATE=PARTITIONED");
+
+ for (int i = 99; i < 300; ++i)
+ executeSql(String.format("INSERT INTO t2 VALUES (%d, %d, %d)", i +
1, i + 1, i + 1));
+
+ executeSql("CREATE INDEX t2_idx1 ON t2(i21)");
+ executeSql("CREATE INDEX t2_idx2 ON t2(i22)");
+ executeSql("CREATE INDEX t2_idx3 ON t2(i23)");
+
+ assertQuery("SELECT i1, i22 FROM t1, t2 where i2=i22 and i3=i23 + 1")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T2"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX2")))
+ .check();
+
+ assertQuery("SELECT /*+ FORCE_INDEX(T1_IDX2) */ i1, i22 FROM t1, t2
where i2=i22 and i3=i23 + 1")
+ .matches(CoreMatchers.not(QueryChecker.containsTableScan("PUBLIC",
"T1")))
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T2"))
+ .matches(QueryChecker.containsIndexScan("PUBLIC", "T1", "T1_IDX2"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX2")))
+ .check();
Review Comment:
For integration tests it's better to check result too (or anything else,
additional to plan).
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testJoins() throws Exception {
+ // Make sure there is no full tnl scan on TBL2 for INNER and LEFT.
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 LEFT JOIN TBL2 t2 on
t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX23")))));
+
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 INNER JOIN TBL2 t2
on t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ doTestJoins("LEFT");
+ doTestJoins("RIGHT");
+ doTestJoins("INNER");
+ }
+
+ /** */
+ private void doTestJoins(String jt) throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2) */ t1.val1, t2.val22 FROM
TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ 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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ // With additional filter.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ t1.val1, t2.val22
FROM TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22 where t2.val22=2 and
t1.val3=3 and t2.val21=1", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertPlan("SELECT val2, val3 FROM TBL1 ORDER by val2, val1, val3",
schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+
.and(nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_3) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))));
+ }
+
+ /** */
+ @Test
+ public void testAggregates() throws Exception {
+ doTestAggregates("sum");
+ doTestAggregates("avg");
+ doTestAggregates("min");
+ doTestAggregates("max");
+ }
+
+ /** */
+ private void doTestAggregates(String op) throws Exception {
+ assertPlan("SELECT avg(val1) FROM TBL1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate()));
+
+ assertPlan("SELECT " + op + "(val1) FROM TBL1 where val1=1 group by
val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ " + op + "(val1) FROM
TBL1 where val1=1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ " + op + "(val1) FROM
TBL1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ " + op + "(val1) FROM
TBL1 where val1=1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1) */ " + op + "(val1) FROM
TBL1 where val1=1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))));
+
+ assertPlan("SELECT " + op + "(val1) FROM TBL1 where val1=1 group by
val2",
Review Comment:
Looks like the same as the second query in this method
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testJoins() throws Exception {
+ // Make sure there is no full tnl scan on TBL2 for INNER and LEFT.
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 LEFT JOIN TBL2 t2 on
t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX23")))));
+
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 INNER JOIN TBL2 t2
on t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ doTestJoins("LEFT");
+ doTestJoins("RIGHT");
+ doTestJoins("INNER");
+ }
+
+ /** */
+ private void doTestJoins(String jt) throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2) */ t1.val1, t2.val22 FROM
TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ 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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ // With additional filter.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ t1.val1, t2.val22
FROM TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22 where t2.val22=2 and
t1.val3=3 and t2.val21=1", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertPlan("SELECT val2, val3 FROM TBL1 ORDER by val2, val1, val3",
schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+
.and(nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_3) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))));
+ }
+
+ /** */
+ @Test
+ public void testAggregates() throws Exception {
+ doTestAggregates("sum");
+ doTestAggregates("avg");
+ doTestAggregates("min");
+ doTestAggregates("max");
+ }
+
+ /** */
+ private void doTestAggregates(String op) throws Exception {
+ assertPlan("SELECT avg(val1) FROM TBL1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate()));
+
+ assertPlan("SELECT " + op + "(val1) FROM TBL1 where val1=1 group by
val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate()));
Review Comment:
Plan looks strange too. We can use IDX1_1 for filtering, or IDX1_2 for
collation, why these indexes are not choosen?
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java:
##########
@@ -336,6 +337,166 @@ public void checkMultiColumnsInlineScan(
}
}
+ /** */
+ @Test
+ public void testNoIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER) WITH TEMPLATE=PARTITIONED");
+ executeSql("INSERT INTO t1 VALUES (1), (2), (30), (40)");
+ executeSql("CREATE INDEX t1_idx ON t1(i1)");
+
+ executeSql("CREATE TABLE t2(i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ for (int i = 0; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t2 VALUES (%d, %d)", i, i));
+
+ executeSql("CREATE INDEX t2_idx ON t2(i2)");
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX('T2_IDX') */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX,T2_IDX) */ i1, i3 FROM t1, t2
where i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i1, i3 FROM t1, t2 where
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX) */ i1, i3 FROM t1 JOIN t2 on
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT * FROM t1 WHERE i1 = (SELECT /*+ NO_INDEX(T2_IDX)
*/ i3 from t2 where i2=40)")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(40)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testForcedIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ executeSql("INSERT INTO t1 VALUES (1, 2, 3)");
+
+ executeSql("CREATE INDEX t1_idx1 ON t1(i1)");
+ executeSql("CREATE INDEX t1_idx2 ON t1(i2)");
+ executeSql("CREATE INDEX t1_idx3 ON t1(i3)");
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2 and i3=3")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX1")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T2_IDX3")))
+ .check();
+
+ testIdxUsedOnTable1();
+
+ for (int i = 1; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t1 VALUES (%d, %d, %d)", i +
1, i + 2, i + 3));
Review Comment:
Rewrite to dynamic parameters
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java:
##########
@@ -336,6 +337,166 @@ public void checkMultiColumnsInlineScan(
}
}
+ /** */
+ @Test
+ public void testNoIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER) WITH TEMPLATE=PARTITIONED");
+ executeSql("INSERT INTO t1 VALUES (1), (2), (30), (40)");
+ executeSql("CREATE INDEX t1_idx ON t1(i1)");
+
+ executeSql("CREATE TABLE t2(i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ for (int i = 0; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t2 VALUES (%d, %d)", i, i));
Review Comment:
`executeSql("INSERT INTO t2 VALUES (?, ?)", i, i);`?
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IndexScanlIntegrationTest.java:
##########
@@ -336,6 +337,166 @@ public void checkMultiColumnsInlineScan(
}
}
+ /** */
+ @Test
+ public void testNoIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER) WITH TEMPLATE=PARTITIONED");
+ executeSql("INSERT INTO t1 VALUES (1), (2), (30), (40)");
+ executeSql("CREATE INDEX t1_idx ON t1(i1)");
+
+ executeSql("CREATE TABLE t2(i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ for (int i = 0; i < 100; ++i)
+ executeSql(String.format("INSERT INTO t2 VALUES (%d, %d)", i, i));
+
+ executeSql("CREATE INDEX t2_idx ON t2(i2)");
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX('T2_IDX') */ i3 FROM t2 where i2=2")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(2)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX,T2_IDX) */ i1, i3 FROM t1, t2
where i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T2_IDX) */ i1, i3 FROM t1, t2 where
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT /*+ NO_INDEX(T1_IDX) */ i1, i3 FROM t1 JOIN t2 on
i2=i1")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX")))
+ .returns(1, 1)
+ .returns(2, 2)
+ .returns(30, 30)
+ .returns(40, 40)
+ .check();
+
+ assertQuery("SELECT * FROM t1 WHERE i1 = (SELECT /*+ NO_INDEX(T2_IDX)
*/ i3 from t2 where i2=40)")
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T2", "T2_IDX")))
+ .returns(40)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testForcedIndexHint() {
+ executeSql("CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER) WITH
TEMPLATE=PARTITIONED");
+
+ executeSql("INSERT INTO t1 VALUES (1, 2, 3)");
+
+ executeSql("CREATE INDEX t1_idx1 ON t1(i1)");
+ executeSql("CREATE INDEX t1_idx2 ON t1(i2)");
+ executeSql("CREATE INDEX t1_idx3 ON t1(i3)");
+
+ assertQuery("SELECT i1 FROM t1 where i1=1 and i2=2 and i3=3")
+ .matches(QueryChecker.containsTableScan("PUBLIC", "T1"))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX1")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T1_IDX2")))
+ .matches(CoreMatchers.not(QueryChecker.containsIndexScan("PUBLIC",
"T1", "T2_IDX3")))
Review Comment:
If we check that plan contains table scan, that other checks are redundant.
There is no guarantee that there will be table scan, it depends on
statistics. Statistics calculation can be slightely changed in the future and
there will be another plan.
Let's remove this check (and other similar) and check only statements with
FORCE_INDEX hints, without changing rows count.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/NoIndexHintPlannerTest.java:
##########
@@ -0,0 +1,316 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 index hints.
+ */
+public class NoIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ tbl1 = createTable("TBL1", 100, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", String.class, "VAL2", String.class, "VAL3", String.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_23", 2, 3)
+ .addIndex("IDX1_3", 3);
+
+ tbl2 = createTable("TBL2", 100_000, IgniteDistributions.single(),
"ID", Integer.class,
+ "VAL1", String.class, "VAL2", String.class, "VAL3", String.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testWrongParams() throws Exception {
+ LogListener lsnr = LogListener.matches("Skipped hint 'NO_INDEX' with
options 'IDX2_1','IDX2_1'")
+ .times(1).build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("SELECT /*+ NO_INDEX(IDX2_1,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);
+
+ physicalPlan("SELECT /*+ NO_INDEX, NO_INDEX(IDX2_1) */ * FROM TBL2
WHERE val2='v'", schema);
+
+ assertTrue(lsnr.check());
+ }
+
+ /** */
+ @Test
+ public void testCertainIndex() throws Exception {
+ assertNoAnyIndex("SELECT /*+ NO_INDEX */ * FROM TBL2 WHERE val2='v'");
+
+ // Checks lower-case idx name.
+ assertCertainIndex("SELECT /*+ NO_INDEX('idx1_1') */ * FROM TBL1 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");
+
+ assertCertainIndex("SELECT /*+ NO_INDEX(" +
QueryUtils.PRIMARY_KEY_INDEX +
+ ") */ * FROM TBL1 WHERE id = 0", "TBL1",
QueryUtils.PRIMARY_KEY_INDEX);
+ assertNoAnyIndex("SELECT /*+ NO_INDEX('" +
QueryUtils.PRIMARY_KEY_INDEX + "') */ * FROM TBL1 WHERE id = 0");
+
+ assertNoAnyIndex("SELECT /*+
NO_INDEX('idx1_1','IDX1_1','IDX1_23','IDX1_3') */ * FROM TBL1 WHERE val1='v' " +
+ "and val2='v' and val3='v'");
+
+ // Wrong names should be just skipped.
+ assertNoAnyIndex("SELECT " +
+ "/*+
NO_INDEX('UNEXISTING','idx1_1','UNEXISTING2','IDX1_1','UNEXISTING3','IDX1_23','IDX1_3')
*/ * " +
+ "FROM TBL1 WHERE val1='v' and val2='v' and val3='v'");
+
+ // Mixed with no-tbl-name hint.
+ assertNoAnyIndex("SELECT /*+ NO_INDEX('idx1_1'),
NO_INDEX(IDX1_1,IDX1_23,IDX1_3) */ * FROM TBL1 WHERE " +
+ "val1='v' and val2='v' and val3='v'");
+
+ // 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'");
+
+ // Index of the second table.
+ assertPlan("SELECT /*+ NO_INDEX(IDX2_3) */ t1.val3, t2.val3 FROM TBL1
t1, TBL2 t2 WHERE " +
+ "t1.val3='v' and t2.val3='v'", schema,
+ nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate());
+ }
+
+ /** */
+ @Test
+ public void testSecondQuery() throws Exception {
+ assertPlan("SELECT /*+ NO_INDEX(IDX1_23) */ * FROM TBL1 t1, (select *
FROM TBL2 WHERE " +
+ "val2='v') t2 WHERE t1.val2='v'", schema,
+ nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))));
+
+ // Propagated, pushed-down hint.
+ assertPlan("SELECT /*+ NO_INDEX(IDX2_3) */ * FROM TBL1 t1, (select *
FROM TBL2 WHERE " +
+ "val3='v') t2 WHERE t1.val2='v'", schema,
nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ // Not-root hint.
+ assertPlan("SELECT * FROM TBL1 t1, (select /*+ NO_INDEX(IDX2_3) */ *
FROM TBL2 WHERE " +
+ "val3='v') t2 WHERE t1.val2='v'", schema,
nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ // Wrong idx name for the table.
+ assertPlan("SELECT * FROM TBL1 t1, (select /*+ NO_INDEX(IDX1_23) */ *
FROM TBL2 WHERE " +
+ "val2='v') t2 WHERE t1.val2='v'", schema,
+ nodeOrAnyChild(isIndexScan("TBL1", "IDX1_23"))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))));
+ }
+
+ /** */
+ @Test
+ public void testCorrelatedSubquery() throws Exception {
+ doTestDisabledInTable2Val3("t1.val3");
+ }
+
+ /** */
+ @Test
+ public void testSubquery() throws Exception {
+ doTestDisabledInTable2Val3("'v'");
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertCertainIndex("SELECT * FROM TBL1 order by val3", "TBL1",
"IDX1_3");
Review Comment:
Looks redundant. Or at least we should check NO_INDEX hint with the same
query
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/CommonHintsPlannerTest.java:
##########
@@ -0,0 +1,122 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+import org.apache.ignite.testframework.LogListener;
+import org.apache.ignite.testframework.junits.logger.GridTestLog4jLogger;
+import org.apache.logging.log4j.Level;
+import org.junit.Test;
+
+/**
+ * Common test for SQL hints.
+ */
+public class CommonHintsPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ tbl = createTable("TBL", 100, IgniteDistributions.random(), "ID",
Integer.class, "VAL",
+ Integer.class).addIndex(QueryUtils.PRIMARY_KEY_INDEX,
0).addIndex("IDX", 1);
+
+ schema = createSchema(tbl);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /**
+ * Tests hint 'DISABLE_RULE' works for whole query despite it is not set
for the root node.
+ */
+ @Test
+ public void testDisableRuleInHeader() throws Exception {
+ assertPlan("SELECT /*+ DISABLE_RULE('ExposeIndexRule') */ VAL FROM TBL
UNION ALL " +
+ "SELECT VAL FROM TBL", schema,
nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate());
+
+ assertPlan("SELECT VAL FROM TBL where val=1 UNION ALL " +
+ "SELECT /*+ DISABLE_RULE('ExposeIndexRule') */ VAL FROM TBL",
schema,
+ nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate());
+ }
+
+ /** */
+ @Test
+ public void testWrongParamsDisableRule() throws Exception {
+ LogListener lsnr = LogListener.matches("Hint 'DISABLE_RULE' must have
at least one option").build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("SELECT /*+ DISABLE_RULE */ VAL FROM TBL", schema);
+
+ assertTrue(lsnr.check());
+
+ lsnrLog.registerListener(lsnr);
Review Comment:
Redundant
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/NoIndexHintPlannerTest.java:
##########
@@ -0,0 +1,316 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 index hints.
+ */
+public class NoIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ tbl1 = createTable("TBL1", 100, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", String.class, "VAL2", String.class, "VAL3", String.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_23", 2, 3)
+ .addIndex("IDX1_3", 3);
+
+ tbl2 = createTable("TBL2", 100_000, IgniteDistributions.single(),
"ID", Integer.class,
+ "VAL1", String.class, "VAL2", String.class, "VAL3", String.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testWrongParams() throws Exception {
+ LogListener lsnr = LogListener.matches("Skipped hint 'NO_INDEX' with
options 'IDX2_1','IDX2_1'")
+ .times(1).build();
+
+ lsnrLog.registerListener(lsnr);
+
+ ((GridTestLog4jLogger)log).setLevel(Level.DEBUG);
+
+ physicalPlan("SELECT /*+ NO_INDEX(IDX2_1,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);
+
+ physicalPlan("SELECT /*+ NO_INDEX, NO_INDEX(IDX2_1) */ * FROM TBL2
WHERE val2='v'", schema);
+
+ assertTrue(lsnr.check());
+ }
+
+ /** */
+ @Test
+ public void testCertainIndex() throws Exception {
+ assertNoAnyIndex("SELECT /*+ NO_INDEX */ * FROM TBL2 WHERE val2='v'");
+
+ // Checks lower-case idx name.
+ assertCertainIndex("SELECT /*+ NO_INDEX('idx1_1') */ * FROM TBL1 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");
+
+ assertCertainIndex("SELECT /*+ NO_INDEX(" +
QueryUtils.PRIMARY_KEY_INDEX +
+ ") */ * FROM TBL1 WHERE id = 0", "TBL1",
QueryUtils.PRIMARY_KEY_INDEX);
+ assertNoAnyIndex("SELECT /*+ NO_INDEX('" +
QueryUtils.PRIMARY_KEY_INDEX + "') */ * FROM TBL1 WHERE id = 0");
Review Comment:
For hint with explicitely defined indexes (like this query) let's check that
query doesn't contain certain indexes.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
Review Comment:
If we check that index is used, than other checks (negate) are redundant.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
Review Comment:
Why IDX2_2 or IDX2_3 if we define IDX2_3 twice?
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testJoins() throws Exception {
+ // Make sure there is no full tnl scan on TBL2 for INNER and LEFT.
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 LEFT JOIN TBL2 t2 on
t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX23")))));
+
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 INNER JOIN TBL2 t2
on t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ doTestJoins("LEFT");
+ doTestJoins("RIGHT");
+ doTestJoins("INNER");
+ }
+
+ /** */
+ private void doTestJoins(String jt) throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2) */ t1.val1, t2.val22 FROM
TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ 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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ // With additional filter.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ t1.val1, t2.val22
FROM TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22 where t2.val22=2 and
t1.val3=3 and t2.val21=1", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertPlan("SELECT val2, val3 FROM TBL1 ORDER by val2, val1, val3",
schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+
.and(nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_3) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))));
+ }
+
+ /** */
+ @Test
+ public void testAggregates() throws Exception {
+ doTestAggregates("sum");
+ doTestAggregates("avg");
+ doTestAggregates("min");
+ doTestAggregates("max");
+ }
+
+ /** */
+ private void doTestAggregates(String op) throws Exception {
+ assertPlan("SELECT avg(val1) FROM TBL1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate()));
Review Comment:
Why there can't be IDX1_2? Looks like merge aggregate with this index is
most apropriate plan
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testJoins() throws Exception {
+ // Make sure there is no full tnl scan on TBL2 for INNER and LEFT.
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 LEFT JOIN TBL2 t2 on
t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX23")))));
+
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 INNER JOIN TBL2 t2
on t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ doTestJoins("LEFT");
+ doTestJoins("RIGHT");
+ doTestJoins("INNER");
+ }
+
+ /** */
+ private void doTestJoins(String jt) throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2) */ t1.val1, t2.val22 FROM
TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ 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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ // With additional filter.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ t1.val1, t2.val22
FROM TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22 where t2.val22=2 and
t1.val3=3 and t2.val21=1", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertPlan("SELECT val2, val3 FROM TBL1 ORDER by val2, val1, val3",
schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+
.and(nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_3) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))));
+ }
+
+ /** */
+ @Test
+ public void testAggregates() throws Exception {
+ doTestAggregates("sum");
+ doTestAggregates("avg");
+ doTestAggregates("min");
+ doTestAggregates("max");
+ }
+
+ /** */
+ private void doTestAggregates(String op) throws Exception {
+ assertPlan("SELECT avg(val1) FROM TBL1 group by val2", schema,
Review Comment:
op instead of avg
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testJoins() throws Exception {
+ // Make sure there is no full tnl scan on TBL2 for INNER and LEFT.
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 LEFT JOIN TBL2 t2 on
t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX23")))));
+
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 INNER JOIN TBL2 t2
on t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ doTestJoins("LEFT");
+ doTestJoins("RIGHT");
+ doTestJoins("INNER");
+ }
+
+ /** */
+ private void doTestJoins(String jt) throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2) */ t1.val1, t2.val22 FROM
TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ 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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ // With additional filter.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ t1.val1, t2.val22
FROM TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22 where t2.val22=2 and
t1.val3=3 and t2.val21=1", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertPlan("SELECT val2, val3 FROM TBL1 ORDER by val2, val1, val3",
schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+
.and(nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate()));
Review Comment:
Can't guarantee this (here and for similar queries)
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/hints/ForceIndexHintPlannerTest.java:
##########
@@ -0,0 +1,348 @@
+/*
+ * 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.ignite.internal.processors.query.QueryUtils;
+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.rel.IgniteIndexScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import
org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+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 force index hint.
+ */
+public class ForceIndexHintPlannerTest extends AbstractPlannerTest {
+ /** */
+ private IgniteSchema schema;
+
+ /** */
+ private TestTable tbl1;
+
+ /** */
+ private TestTable tbl2;
+
+ /** {@inheritDoc} */
+ @Override public void setup() {
+ super.setup();
+
+ // A tiny table.
+ tbl1 = createTable("TBL1", 1, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL1", Integer.class, "VAL2", Integer.class, "VAL3",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX1_1", 1)
+ .addIndex("IDX1_2", 2)
+ .addIndex("IDX1_3", 3);
+
+ // A large table. Has the same first inndex name 'IDX1' as of TBL1.
+ tbl2 = createTable("TBL2", 10_000, IgniteDistributions.single(), "ID",
Integer.class,
+ "VAL21", Integer.class, "VAL22", Integer.class, "VAL23",
Integer.class)
+ .addIndex(QueryUtils.PRIMARY_KEY_INDEX, 0)
+ .addIndex("IDX2_1", 1)
+ .addIndex("IDX2_2", 2)
+ .addIndex("IDX2_3", 3);
+
+ schema = createSchema(tbl1, tbl2);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() throws Exception {
+ super.afterTest();
+
+ ((GridTestLog4jLogger)log).setLevel(Level.INFO);
+ }
+
+ /** */
+ @Test
+ public void testBasicIndexSelection() throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(UNEXISTING,IDX2_3,UNEXISTING) */ *
FROM TBL2 WHERE val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_3,IDX2_1) */ * FROM TBL2 WHERE
val23=1 and val21=2 " +
+ "and val22=3", schema, nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")
+ .or(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ * FROM TBL2 WHERE
val23=1 and val21=2 and val22=3",
+ schema, nodeOrAnyChild(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(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(isTableScan("TBL2")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testJoins() throws Exception {
+ // Make sure there is no full tnl scan on TBL2 for INNER and LEFT.
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 LEFT JOIN TBL2 t2 on
t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX23")))));
+
+ assertPlan("SELECT t1.val1, t2.val22 FROM TBL1 t1 INNER JOIN TBL2 t2
on t1.val3=t2.val23 and " +
+ "t1.val1=t2.val22", schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ doTestJoins("LEFT");
+ doTestJoins("RIGHT");
+ doTestJoins("INNER");
+ }
+
+ /** */
+ private void doTestJoins(String jt) throws Exception {
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2) */ t1.val1, t2.val22 FROM
TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22", schema,
nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")).negate()));
+
+ 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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,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(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+
+ // With additional filter.
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX2_2,IDX2_3) */ t1.val1, t2.val22
FROM TBL1 t1 " + jt + " JOIN TBL2 " +
+ "t2 on t1.val3=t2.val23 and t1.val1=t2.val22 where t2.val22=2 and
t1.val3=3 and t2.val21=1", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isTableScan("TBL2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL2", "IDX2_3")))));
+ }
+
+ /** */
+ @Test
+ public void testOrderBy() throws Exception {
+ assertPlan("SELECT val2, val3 FROM TBL1 ORDER by val2, val1, val3",
schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+
.and(nodeOrAnyChild(isInstanceOf(IgniteIndexScan.class)).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_3) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_3"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1) */ val2, val3 FROM TBL1
ORDER by val2, val1, val3", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))));
+ }
+
+ /** */
+ @Test
+ public void testAggregates() throws Exception {
+ doTestAggregates("sum");
+ doTestAggregates("avg");
+ doTestAggregates("min");
+ doTestAggregates("max");
+ }
+
+ /** */
+ private void doTestAggregates(String op) throws Exception {
+ assertPlan("SELECT avg(val1) FROM TBL1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate()));
+
+ assertPlan("SELECT " + op + "(val1) FROM TBL1 where val1=1 group by
val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ " + op + "(val1) FROM
TBL1 where val1=1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ " + op + "(val1) FROM
TBL1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_2) */ " + op + "(val1) FROM
TBL1 where val1=1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1) */ " + op + "(val1) FROM
TBL1 where val1=1 group by val2", schema,
+ nodeOrAnyChild(isTableScan("TBL1")).negate()
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2")).negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1"))));
+
+ assertPlan("SELECT " + op + "(val1) FROM TBL1 where val1=1 group by
val2",
+ schema, nodeOrAnyChild(isTableScan("TBL1"))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2").negate()))
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")).negate()));
+
+ assertPlan("SELECT /*+ FORCE_INDEX(IDX1_1,IDX1_2) */ " + op + "(val1)
FROM TBL1 where val1=1 group by val2",
+ schema, nodeOrAnyChild(isTableScan("TBL1").negate())
+ .and(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_2"))
+ .or(nodeOrAnyChild(isIndexScan("TBL1", "IDX1_1")))));
+ }
+
+ /** */
+ @Test
+ public void testOverridesOverTinyTableScan() throws Exception {
Review Comment:
We should not rely on table size
--
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]
