This is an automated email from the ASF dual-hosted git repository.
ipavlukhin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new 299cf813e IGNITE-12277 Enable SQL index usage for mixed IN and EQUALS
queries - Fixes #7029.
299cf813e is described below
commit 299cf813e34bd0a4c2454e570bf320a2c44c72af
Author: Evgeny Stanilovskiy <estanilovs...@gridgain.com>
AuthorDate: Fri Nov 1 11:39:53 2019 +0300
IGNITE-12277 Enable SQL index usage for mixed IN and EQUALS queries - Fixes
#7029.
Signed-off-by: ipavlukhin <vololo...@gmail.com>
---
.../processors/query/h2/opt/GridH2Table.java | 2 +-
.../processors/query/h2/twostep/ReduceTable.java | 2 +-
.../query/h2/twostep/ReduceTableWrapper.java | 2 +-
.../processors/cache/index/BasicIndexTest.java | 87 +++++++++++++++++++++-
4 files changed, 89 insertions(+), 4 deletions(-)
diff --git
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2Table.java
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2Table.java
index dc4ee1a..9bdf622 100644
---
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2Table.java
+++
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2Table.java
@@ -1129,7 +1129,7 @@ public class GridH2Table extends TableBase {
/** {@inheritDoc} */
@Override public TableType getTableType() {
- return TableType.EXTERNAL_TABLE_ENGINE;
+ return TableType.TABLE;
}
/** {@inheritDoc} */
diff --git
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTable.java
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTable.java
index 520bfcf..0030fcd 100644
---
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTable.java
+++
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTable.java
@@ -116,7 +116,7 @@ public class ReduceTable extends TableBase {
/** {@inheritDoc} */
@Override public TableType getTableType() {
- return TableType.EXTERNAL_TABLE_ENGINE;
+ return TableType.TABLE;
}
/** {@inheritDoc} */
diff --git
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTableWrapper.java
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTableWrapper.java
index 3b07993..d57b92d 100644
---
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTableWrapper.java
+++
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/twostep/ReduceTableWrapper.java
@@ -179,7 +179,7 @@ public class ReduceTableWrapper extends Table {
/** {@inheritDoc} */
@Override public TableType getTableType() {
- return TableType.EXTERNAL_TABLE_ENGINE;
+ return TableType.TABLE;
}
/** {@inheritDoc} */
diff --git
a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/BasicIndexTest.java
b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/BasicIndexTest.java
index 57e7aef..404bbe5 100644
---
a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/BasicIndexTest.java
+++
b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/BasicIndexTest.java
@@ -491,13 +491,24 @@ public class BasicIndexTest extends
AbstractIndexingCommonTest {
String s0 = sqlIns;
for (int f = 0; f < reqFlds.length; ++f)
- s0 += i + ((f < reqFlds.length - 1) ? ", " : ")");
+ s0 += (i + f) + ((f < reqFlds.length - 1) ? ", " : ")");
qryProc.querySqlFields(new SqlFieldsQuery(s0), true).getAll();
}
}
/**
+ * Checks index usage instead of full scan.
+ * @param res Result set.
+ * @return {@code True} if index usage found.
+ */
+ private boolean checkIdxUsage(List<List<?>> res, String idx) {
+ String plan = res.get(0).get(0).toString();
+
+ return idx != null ? plan.contains(idx) :
!plan.contains(SCAN_INDEX_NAME_SUFFIX);
+ }
+
+ /**
* Checks index usage for full coverage.
*/
@Test
@@ -646,6 +657,80 @@ public class BasicIndexTest extends
AbstractIndexingCommonTest {
}
/**
+ * Tests IN with EQUALS index usage.
+ */
+ @Test
+ public void testInWithEqualsIdxUsage() throws Exception {
+ inlineSize = 10;
+
+ isPersistenceEnabled = false;
+
+ String idxName = "idx2";
+
+ IgniteEx ig0 = startGrid(0);
+
+ GridQueryProcessor qryProc = ig0.context().query();
+
+ populateTable(qryProc, TEST_TBL_NAME, 2, "FIRST_NAME", "LAST_NAME",
+ "ADDRESS", "LANG");
+
+ String sqlIdx2 = String.format("create index \"%s\" on %s(LANG)",
idxName, TEST_TBL_NAME);
+
+ qryProc.querySqlFields(new SqlFieldsQuery(sqlIdx2), true).getAll();
+
+ List<List<?>> res = qryProc.querySqlFields(new SqlFieldsQuery("explain
select * from " + TEST_TBL_NAME +
+ " where LANG in (?1, ?2) and (ADDRESS = ?1 or ADDRESS =
?2)").setArgs(3, 4), true).getAll();
+
+ assertTrue(checkIdxUsage(res, idxName));
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("explain select * from
" + TEST_TBL_NAME +
+ " where LANG in (select ADDRESS from " + TEST_TBL_NAME + " where
ADDRESS in(?1, ?2)) " +
+ "and (ADDRESS = ?1 or ADDRESS = ?2)").setArgs(3, 4),
true).getAll();
+
+ assertTrue(checkIdxUsage(res, idxName));
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("explain select * from
" + TEST_TBL_NAME +
+ " where LANG in (?1, ?2) and (ADDRESS = ?3 or ADDRESS =
?3)").setArgs(3, 4, 5), true).getAll();
+
+ assertTrue(checkIdxUsage(res, idxName));
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("explain select * from
" + TEST_TBL_NAME +
+ " where LANG in (?1, ?2) and ADDRESS = ?3").setArgs(3, 4, 5),
true).getAll();
+
+ assertTrue(checkIdxUsage(res, idxName));
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("explain select * from
" + TEST_TBL_NAME +
+ " where LANG in (3, 4) and ADDRESS = 5"), true).getAll();
+
+ assertTrue(checkIdxUsage(res, idxName));
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("select * from " +
TEST_TBL_NAME +
+ " where LANG in (?1, ?2) and (ADDRESS = ?3 or ADDRESS = ?4) ORDER
BY LAST_NAME")
+ .setArgs(3, 4, 5, 6), true).getAll();
+
+ assertEquals(res.size(), 0);
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("select * from " +
TEST_TBL_NAME +
+ " where LANG in (?3, ?4) and (ADDRESS = ?1 or ADDRESS = ?2) ORDER
BY LAST_NAME")
+ .setArgs(3, 4, 5, 6), true).getAll();
+
+ assertEquals(res.size(), 1);
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("select * from " +
TEST_TBL_NAME +
+ " where LANG in (?2, ?3) and (ADDRESS = ?1 or ADDRESS = ?2) ORDER
BY LAST_NAME")
+ .setArgs(3, 4, 5), true).getAll();
+
+ assertEquals(res.size(), 2);
+
+ assertEquals(res.get(0).get(0), "1");
+
+ res = qryProc.querySqlFields(new SqlFieldsQuery("select * from " +
TEST_TBL_NAME +
+ " where LANG in (4, 5) and (ADDRESS = 3 or ADDRESS = 4) ORDER BY
LAST_NAME"), true).getAll();
+
+ assertEquals(res.size(), 2);
+ }
+
+ /**
* Tests different fields sequence in indexes.
* Last field not participate in any index.
*/
