You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by ag...@apache.org on 2018/11/20 18:04:32 UTC
[39/50] [abbrv] ignite git commit: IGNITE-10217: SQL: store unwrapped
key fields in secondary indexes instead of full key when possible. Closes
#5407.
IGNITE-10217: SQL: store unwrapped key fields in secondary indexes instead of full key when possible. Closes #5407.
Project: http://git-wip-us.apache.org/repos/asf/ignite/repo
Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/a9b5c8c8
Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/a9b5c8c8
Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/a9b5c8c8
Branch: refs/heads/ignite-9720
Commit: a9b5c8c8aee9085f082fd524e288949853dd81f1
Parents: c8fd687
Author: Yuriy Gerzhedovich <yg...@gridgain.com>
Authored: Tue Nov 20 14:47:15 2018 +0300
Committer: devozerov <vo...@gridgain.com>
Committed: Tue Nov 20 14:47:15 2018 +0300
----------------------------------------------------------------------
.../IgnitePKIndexesMigrationToUnwrapPkTest.java | 6 +-
.../processors/query/h2/H2TableDescriptor.java | 28 ++-
.../internal/processors/query/h2/H2Utils.java | 13 +
.../index/ComplexPrimaryKeyUnwrapSelfTest.java | 18 +-
.../ComplexSecondaryKeyUnwrapSelfTest.java | 249 +++++++++++++++++++
.../IgniteBinaryCacheQueryTestSuite.java | 2 +
6 files changed, 300 insertions(+), 16 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java
----------------------------------------------------------------------
diff --git a/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java b/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java
index 22ccae0..316e574 100644
--- a/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java
+++ b/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java
@@ -145,10 +145,12 @@ public class IgnitePKIndexesMigrationToUnwrapPkTest extends IgnitePersistenceCom
*/
@NotNull private static void initializeTable(IgniteEx igniteEx, String tblName) {
executeSql(igniteEx, "CREATE TABLE " + tblName + " (id int, name varchar, age int, company varchar, city varchar, " +
- "primary key (id, name, city))");
+ "primary key (id, name, city)) WITH \"affinity_key=name\"");
- executeSql(igniteEx, "INSERT INTO " + tblName + " (id, name, age, company, city) VALUES(1,'name',2,'company', 'city')");
+ executeSql(igniteEx, "CREATE INDEX ON " + tblName + "(city, age)");
+ for (int i = 0; i < 1000; i++)
+ executeSql(igniteEx, "INSERT INTO " + tblName + " (id, name, age, company, city) VALUES(?,'name',2,'company', 'city')", i);
}
/**
http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java
index aa66939..6c20727 100644
--- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java
+++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java
@@ -204,7 +204,7 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory {
if (affCol != null && H2Utils.equals(affCol, keyCol))
affCol = null;
- List<IndexColumn> unwrappedKeyCols = extractKeyColumns(tbl, keyCol, affCol);
+ List<IndexColumn> unwrappedKeyAndAffinityCols = extractKeyColumns(tbl, keyCol, affCol);
List<IndexColumn> wrappedKeyCols = H2Utils.treeIndexColumns(tbl.rowDescriptor(),
new ArrayList<>(2), keyCol, affCol);
@@ -224,7 +224,7 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory {
tbl,
true,
false,
- unwrappedKeyCols,
+ unwrappedKeyAndAffinityCols,
wrappedKeyCols,
-1
);
@@ -271,14 +271,24 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory {
// Add explicit affinity key index if nothing alike was found.
if (!affIdxFound) {
- List<IndexColumn> columns = H2Utils.treeIndexColumns(tbl.rowDescriptor(), new ArrayList<>(2), affCol, keyCol);
+ List<IndexColumn> unwrappedKeyCols = extractKeyColumns(tbl, keyCol, null);
+
+ ArrayList<IndexColumn> colsWithUnwrappedKey = new ArrayList<>(unwrappedKeyCols.size());
+
+ colsWithUnwrappedKey.add(affCol);
+
+ //We need to reorder PK columns to have affinity key as first column, that's why we can't use simple PK columns
+ H2Utils.addUniqueColumns(colsWithUnwrappedKey, unwrappedKeyCols);
+
+ List<IndexColumn> cols = H2Utils.treeIndexColumns(tbl.rowDescriptor(), new ArrayList<>(2), affCol, keyCol);
+
idxs.add(idx.createSortedIndex(
AFFINITY_KEY_IDX_NAME,
tbl,
false,
true,
- columns,
- columns,
+ colsWithUnwrappedKey,
+ cols,
-1)
);
}
@@ -378,6 +388,12 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory {
GridH2RowDescriptor desc = tbl.rowDescriptor();
if (idxDesc.type() == QueryIndexType.SORTED) {
+ List<IndexColumn> unwrappedKeyCols = extractKeyColumns(tbl, keyCol, affCol);
+
+ List<IndexColumn> colsWithUnwrappedKey = new ArrayList<>(cols);
+
+ H2Utils.addUniqueColumns(colsWithUnwrappedKey, unwrappedKeyCols);
+
cols = H2Utils.treeIndexColumns(desc, cols, keyCol, affCol);
return idx.createSortedIndex(
@@ -385,7 +401,7 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory {
tbl,
false,
false,
- cols,
+ colsWithUnwrappedKey,
cols,
idxDesc.inlineSize()
);
http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java
index b9d9d8e..865754f 100644
--- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java
+++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java
@@ -285,4 +285,17 @@ public class H2Utils {
return resCur;
}
+
+ /**
+ * Add only new columns to destination list.
+ *
+ * @param dest List of index columns to add new elements from src list.
+ * @param src List of IndexColumns to add to dest list.
+ */
+ public static void addUniqueColumns(List<IndexColumn> dest, List<IndexColumn> src) {
+ for (IndexColumn col : src) {
+ if (!containsColumn(dest, col))
+ dest.add(col);
+ }
+ }
}
http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java
index 8f4eaf0..376a0d4 100644
--- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java
@@ -88,18 +88,19 @@ public class ComplexPrimaryKeyUnwrapSelfTest extends GridCommonAbstractTest {
put("uuid", "'1'");
}
};
+
for (Map.Entry<String, String> entry : types.entrySet()) {
String tblName = createTableName();
String type = entry.getKey();
- String value = entry.getValue();
+ String val = entry.getValue();
executeSql("CREATE TABLE " + tblName +
" (id " + type + " , name varchar, age int, company varchar, city varchar," +
" primary key (id))");
- checkUsingIndexes(tblName, value);
+ checkUsingIndexes(tblName, val);
}
}
@@ -128,18 +129,19 @@ public class ComplexPrimaryKeyUnwrapSelfTest extends GridCommonAbstractTest {
put("uuid", "'1'");
}
};
+
for (Map.Entry<String, String> entry : types.entrySet()) {
String tblName = createTableName();
String type = entry.getKey();
- String value = entry.getValue();
+ String val = entry.getValue();
executeSql("CREATE TABLE " + tblName +
" (id " + type + " , name varchar, age int, company varchar, city varchar," +
" primary key (id)) WITH \"affinity_key=id\"");
- checkUsingIndexes(tblName, value);
+ checkUsingIndexes(tblName, val);
}
}
@@ -160,18 +162,18 @@ public class ComplexPrimaryKeyUnwrapSelfTest extends GridCommonAbstractTest {
*
* @param tblName name of table which should be checked to using PK indexes.
*/
- private void checkUsingIndexes(String tblName, String idValue) {
+ private void checkUsingIndexes(String tblName, String idVal) {
String explainSQL = "explain SELECT * FROM " + tblName + " WHERE ";
- List<List<?>> results = executeSql(explainSQL + "id=" + idValue);
+ List<List<?>> results = executeSql(explainSQL + "id=" + idVal);
assertUsingPkIndex(results);
- results = executeSql(explainSQL + "id=" + idValue + " and name=''");
+ results = executeSql(explainSQL + "id=" + idVal + " and name=''");
assertUsingPkIndex(results);
- results = executeSql(explainSQL + "id=" + idValue + " and name='' and city='' and age=0");
+ results = executeSql(explainSQL + "id=" + idVal + " and name='' and city='' and age=0");
assertUsingPkIndex(results);
}
http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java
new file mode 100644
index 0000000..fe3cbfa
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java
@@ -0,0 +1,249 @@
+/*
+ * 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.cache.index;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import org.apache.ignite.cache.query.SqlFieldsQuery;
+import org.apache.ignite.cache.query.annotations.QuerySqlField;
+import org.apache.ignite.internal.IgniteEx;
+import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;
+
+/**
+ * Test of creating and using secondary indexes for tables created through SQL.
+ */
+@SuppressWarnings({"unchecked", "ThrowableResultOfMethodCallIgnored"})
+public class ComplexSecondaryKeyUnwrapSelfTest extends GridCommonAbstractTest {
+
+ /** Counter to generate unique table names. */
+ private static int tblCnt = 0;
+
+ /** {@inheritDoc} */
+ @Override protected void beforeTestsStarted() throws Exception {
+ super.beforeTestsStarted();
+
+ startGrid(0);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTestsStopped() throws Exception {
+ super.afterTestsStopped();
+
+ stopAllGrids();
+ }
+
+ /**
+ * Test secondary index with complex PK. Columns for secondary and PK indexes are intersect.
+ */
+ public void testSecondaryIndexWithIntersectColumnsComplexPk() {
+ String tblName = createTableName();
+
+ executeSql("CREATE TABLE " + tblName + " (id int, name varchar, age int, company varchar, city varchar, " +
+ "primary key (name, city))");
+
+ executeSql("CREATE INDEX ON " + tblName + "(id, name, city)");
+
+ checkUsingIndexes(tblName, "'1'");
+ }
+
+ /**
+ * Test using secondary index with simple PK.
+ */
+ public void testSecondaryIndexSimplePk() {
+ HashMap<String, String> types = new HashMap() {
+ {
+ put("boolean", "1");
+ put("char", "'1'");
+ put("varchar", "'1'");
+ put("real", "1");
+ put("number", "1");
+ put("int", "1");
+ put("long", "1");
+ put("float", "1");
+ put("double", "1");
+ put("tinyint", "1");
+ put("smallint", "1");
+ put("bigint", "1");
+ put("varchar_ignorecase", "'1'");
+ put("time", "'11:11:11'");
+ put("timestamp", "'20018-11-02 11:11:11'");
+ put("uuid", "'1'");
+ }
+ };
+
+ for (Map.Entry<String, String> entry : types.entrySet()) {
+
+ String tblName = createTableName();
+
+ String type = entry.getKey();
+ String val = entry.getValue();
+
+ executeSql("CREATE TABLE " + tblName +
+ " (id int, name " + type + ", age int, company varchar, city varchar," +
+ " primary key (name))");
+
+ executeSql("CREATE INDEX ON " + tblName + "(id, name, city)");
+
+ checkUsingIndexes(tblName, val);
+ }
+ }
+
+ /**
+ * Check using secondary indexes for few cases.
+ *
+ * @param tblName name of table which should be checked to using secondary indexes.
+ * @param nameVal Value for name param.
+ */
+ private void checkUsingIndexes(String tblName, String nameVal) {
+ String explainSQL = "explain SELECT * FROM " + tblName + " WHERE ";
+
+ List<List<?>> results = executeSql(explainSQL + "id=1");
+
+ assertUsingSecondaryIndex(results);
+
+ results = executeSql(explainSQL + "id=1 and name=" + nameVal);
+
+ assertUsingSecondaryIndex(results);
+
+ results = executeSql(explainSQL + "id=1 and name=" + nameVal + " and age=0");
+
+ assertUsingSecondaryIndex(results);
+ }
+
+ /**
+ * Check that explain plan result shown using Secondary index and don't use scan.
+ *
+ * @param results result of execut explain plan query.
+ */
+ private void assertUsingSecondaryIndex(List<List<?>> results) {
+ assertEquals(2, results.size());
+
+ String explainPlan = (String)results.get(0).get(0);
+
+ assertTrue(explainPlan, explainPlan.contains("_idx\": "));
+
+ assertFalse(explainPlan, explainPlan.contains("_SCAN_"));
+ }
+
+ /**
+ * Create unique table name.
+ *
+ * @return unique name of table.
+ */
+ private String createTableName() {
+ return "TST_TABLE_" + tblCnt++;
+ }
+
+ /**
+ * Run SQL statement on default node.
+ *
+ * @param stmt Statement to run.
+ * @param args arguments of statements
+ * @return Run result.
+ */
+ private List<List<?>> executeSql(String stmt, Object... args) {
+ return executeSql(node(), stmt, args);
+ }
+
+ /**
+ * Run SQL statement on specified node.
+ *
+ * @param node node to execute query.
+ * @param stmt Statement to run.
+ * @param args arguments of statements
+ * @return Run result.
+ */
+ private List<List<?>> executeSql(IgniteEx node, String stmt, Object... args) {
+ return node.context().query().querySqlFields(new SqlFieldsQuery(stmt).setArgs(args), true).getAll();
+ }
+
+ /**
+ * @return Node to initiate operations from.
+ */
+ private IgniteEx node() {
+ return grid(0);
+ }
+
+ /**
+ *
+ */
+ static class TestKey {
+ /** */
+ @QuerySqlField
+ private int id;
+
+ /**
+ * @param id ID.
+ */
+ public TestKey(int id) {
+ this.id = id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ if (this == o)
+ return true;
+
+ if (o == null || getClass() != o.getClass())
+ return false;
+
+ TestKey testKey = (TestKey)o;
+
+ return id == testKey.id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+ }
+
+ /**
+ *
+ */
+ static class TestValue {
+ /** */
+ @QuerySqlField()
+ private String name;
+ /** */
+ @QuerySqlField()
+ private String company;
+ /** */
+ @QuerySqlField()
+ private String city;
+ /** */
+ @QuerySqlField()
+ private int age;
+
+ /**
+ * @param age Age.
+ * @param name Name.
+ * @param company Company.
+ * @param city City.
+ */
+ public TestValue(int age, String name, String company, String city) {
+ this.age = age;
+ this.name = name;
+ this.company = company;
+ this.city = city;
+ }
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java
index ddfeaef..25cb473 100644
--- a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java
+++ b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java
@@ -251,6 +251,8 @@ public class IgniteBinaryCacheQueryTestSuite extends TestSuite {
suite.addTestSuite(ExplainSelfTest.class);
suite.addTestSuite(RunningQueriesTest.class);
+ suite.addTestSuite(ComplexPrimaryKeyUnwrapSelfTest.class);
+
suite.addTestSuite(PartitionedSqlTest.class);
suite.addTestSuite(ReplicatedSqlTest.class);