[4/9] phoenix git commit: PHOENIX-4964 ORDER BY should use a LOCAL index even if the query is not fully covered.
PHOENIX-4964 ORDER BY should use a LOCAL index even if the query is not fully
covered.
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/d6b574c0
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/d6b574c0
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/d6b574c0
Branch: refs/heads/4.14-cdh5.13
Commit: d6b574c0b0e99454a93669e3a1716da10163225c
Parents: 0ecc818
Author: Lars Hofhansl
Authored: Fri Oct 12 06:46:53 2018 +0100
Committer: pboado
Committed: Mon Dec 3 20:32:41 2018 +
--
.../phoenix/end2end/index/LocalIndexIT.java | 59
.../apache/phoenix/optimize/QueryOptimizer.java | 9 ++-
2 files changed, 66 insertions(+), 2 deletions(-)
--
http://git-wip-us.apache.org/repos/asf/phoenix/blob/d6b574c0/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
--
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
index 796d5a2..42cdab3 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
@@ -266,6 +266,65 @@ public class LocalIndexIT extends BaseLocalIndexIT {
}
indexTable.close();
}
+
+@Test
+public void testLocalIndexUsedForUncoveredOrderBy() throws Exception {
+String tableName = schemaName + "." + generateUniqueName();
+String indexName = "IDX_" + generateUniqueName();
+TableName physicalTableName =
SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped);
+String indexPhysicalTableName = physicalTableName.getNameAsString();
+
+createBaseTable(tableName, null, "('e','i','o')");
+try (Connection conn1 = getConnection()) {
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('b',1,2,4,'z')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('f',1,2,3,'a')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('j',2,4,2,'a')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('q',3,1,1,'c')");
+conn1.commit();
+conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName
+ " ON " + tableName + "(v1)");
+
+String query = "SELECT * FROM " + tableName +" ORDER BY V1";
+ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+
query);
+
+HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(),
TestUtil.TEST_PROPERTIES).getAdmin();
+int numRegions = admin.getTableRegions(physicalTableName).size();
+
+assertEquals(
+"CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
++ indexPhysicalTableName + " [1]\n"
++ "SERVER FILTER BY FIRST KEY ONLY\n"
++ "CLIENT MERGE SORT",
+QueryUtil.getExplainPlan(rs));
+
+rs = conn1.createStatement().executeQuery(query);
+String v = "";
+while(rs.next()) {
+String next = rs.getString("v1");
+assertTrue(v.compareTo(next) <= 0);
+v = next;
+}
+rs.close();
+
+query = "SELECT * FROM " + tableName +" ORDER BY V1 DESC NULLS
LAST";
+rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+assertEquals(
+"CLIENT PARALLEL " + numRegions + "-WAY REVERSE RANGE SCAN
OVER "
++ indexPhysicalTableName + " [1]\n"
++ "SERVER FILTER BY FIRST KEY ONLY\n"
++ "CLIENT MERGE SORT",
+QueryUtil.getExplainPlan(rs));
+
+rs = conn1.createStatement().executeQuery(query);
+v = "zz";
+while(rs.next()) {
+String next = rs.getString("v1");
+assertTrue(v.compareTo(next) >= 0);
+v = next;
+}
+rs.close();
+
+}
+}
@Test
public void testLocalIndexScanJoinColumnsFromDataTable() throws Exception {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/d6b574c0/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
--
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index
[4/9] phoenix git commit: PHOENIX-4964 ORDER BY should use a LOCAL index even if the query is not fully covered.
PHOENIX-4964 ORDER BY should use a LOCAL index even if the query is not fully
covered.
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/e861abb5
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/e861abb5
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/e861abb5
Branch: refs/heads/4.14-cdh5.12
Commit: e861abb5ffa6c0583f93cbca0cfe7ba353a47a1d
Parents: 4736ec3
Author: Lars Hofhansl
Authored: Fri Oct 12 06:46:53 2018 +0100
Committer: pboado
Committed: Mon Dec 3 20:27:09 2018 +
--
.../phoenix/end2end/index/LocalIndexIT.java | 59
.../apache/phoenix/optimize/QueryOptimizer.java | 9 ++-
2 files changed, 66 insertions(+), 2 deletions(-)
--
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e861abb5/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
--
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
index 796d5a2..42cdab3 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
@@ -266,6 +266,65 @@ public class LocalIndexIT extends BaseLocalIndexIT {
}
indexTable.close();
}
+
+@Test
+public void testLocalIndexUsedForUncoveredOrderBy() throws Exception {
+String tableName = schemaName + "." + generateUniqueName();
+String indexName = "IDX_" + generateUniqueName();
+TableName physicalTableName =
SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped);
+String indexPhysicalTableName = physicalTableName.getNameAsString();
+
+createBaseTable(tableName, null, "('e','i','o')");
+try (Connection conn1 = getConnection()) {
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('b',1,2,4,'z')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('f',1,2,3,'a')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('j',2,4,2,'a')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('q',3,1,1,'c')");
+conn1.commit();
+conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName
+ " ON " + tableName + "(v1)");
+
+String query = "SELECT * FROM " + tableName +" ORDER BY V1";
+ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+
query);
+
+HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(),
TestUtil.TEST_PROPERTIES).getAdmin();
+int numRegions = admin.getTableRegions(physicalTableName).size();
+
+assertEquals(
+"CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
++ indexPhysicalTableName + " [1]\n"
++ "SERVER FILTER BY FIRST KEY ONLY\n"
++ "CLIENT MERGE SORT",
+QueryUtil.getExplainPlan(rs));
+
+rs = conn1.createStatement().executeQuery(query);
+String v = "";
+while(rs.next()) {
+String next = rs.getString("v1");
+assertTrue(v.compareTo(next) <= 0);
+v = next;
+}
+rs.close();
+
+query = "SELECT * FROM " + tableName +" ORDER BY V1 DESC NULLS
LAST";
+rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+assertEquals(
+"CLIENT PARALLEL " + numRegions + "-WAY REVERSE RANGE SCAN
OVER "
++ indexPhysicalTableName + " [1]\n"
++ "SERVER FILTER BY FIRST KEY ONLY\n"
++ "CLIENT MERGE SORT",
+QueryUtil.getExplainPlan(rs));
+
+rs = conn1.createStatement().executeQuery(query);
+v = "zz";
+while(rs.next()) {
+String next = rs.getString("v1");
+assertTrue(v.compareTo(next) >= 0);
+v = next;
+}
+rs.close();
+
+}
+}
@Test
public void testLocalIndexScanJoinColumnsFromDataTable() throws Exception {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e861abb5/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
--
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index
[4/9] phoenix git commit: PHOENIX-4964 ORDER BY should use a LOCAL index even if the query is not fully covered.
PHOENIX-4964 ORDER BY should use a LOCAL index even if the query is not fully
covered.
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/0a65646c
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/0a65646c
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/0a65646c
Branch: refs/heads/4.14-cdh5.14
Commit: 0a65646c18abdf70568b7d837d00075a1c371f8c
Parents: 1d407cd
Author: Lars Hofhansl
Authored: Fri Oct 12 06:46:53 2018 +0100
Committer: Pedro Boado
Committed: Mon Dec 3 08:36:53 2018 +
--
.../phoenix/end2end/index/LocalIndexIT.java | 59
.../apache/phoenix/optimize/QueryOptimizer.java | 9 ++-
2 files changed, 66 insertions(+), 2 deletions(-)
--
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0a65646c/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
--
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
index 796d5a2..42cdab3 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
@@ -266,6 +266,65 @@ public class LocalIndexIT extends BaseLocalIndexIT {
}
indexTable.close();
}
+
+@Test
+public void testLocalIndexUsedForUncoveredOrderBy() throws Exception {
+String tableName = schemaName + "." + generateUniqueName();
+String indexName = "IDX_" + generateUniqueName();
+TableName physicalTableName =
SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped);
+String indexPhysicalTableName = physicalTableName.getNameAsString();
+
+createBaseTable(tableName, null, "('e','i','o')");
+try (Connection conn1 = getConnection()) {
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('b',1,2,4,'z')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('f',1,2,3,'a')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('j',2,4,2,'a')");
+conn1.createStatement().execute("UPSERT INTO " + tableName + "
values('q',3,1,1,'c')");
+conn1.commit();
+conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName
+ " ON " + tableName + "(v1)");
+
+String query = "SELECT * FROM " + tableName +" ORDER BY V1";
+ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+
query);
+
+HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(),
TestUtil.TEST_PROPERTIES).getAdmin();
+int numRegions = admin.getTableRegions(physicalTableName).size();
+
+assertEquals(
+"CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
++ indexPhysicalTableName + " [1]\n"
++ "SERVER FILTER BY FIRST KEY ONLY\n"
++ "CLIENT MERGE SORT",
+QueryUtil.getExplainPlan(rs));
+
+rs = conn1.createStatement().executeQuery(query);
+String v = "";
+while(rs.next()) {
+String next = rs.getString("v1");
+assertTrue(v.compareTo(next) <= 0);
+v = next;
+}
+rs.close();
+
+query = "SELECT * FROM " + tableName +" ORDER BY V1 DESC NULLS
LAST";
+rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+assertEquals(
+"CLIENT PARALLEL " + numRegions + "-WAY REVERSE RANGE SCAN
OVER "
++ indexPhysicalTableName + " [1]\n"
++ "SERVER FILTER BY FIRST KEY ONLY\n"
++ "CLIENT MERGE SORT",
+QueryUtil.getExplainPlan(rs));
+
+rs = conn1.createStatement().executeQuery(query);
+v = "zz";
+while(rs.next()) {
+String next = rs.getString("v1");
+assertTrue(v.compareTo(next) >= 0);
+v = next;
+}
+rs.close();
+
+}
+}
@Test
public void testLocalIndexScanJoinColumnsFromDataTable() throws Exception {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0a65646c/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
--
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index
