You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by pb...@apache.org on 2018/02/01 00:07:07 UTC
[35/35] phoenix git commit: PHOENIX-4560 ORDER BY with GROUP BY
doesn't work if there is WHERE on pk column
PHOENIX-4560 ORDER BY with GROUP BY doesn't work if there is WHERE on pk column
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/9994059a
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/9994059a
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/9994059a
Branch: refs/heads/4.x-cdh5.11.2
Commit: 9994059a049122415464aa329cdfa126ae493de3
Parents: e5bfd0d
Author: James Taylor <jt...@salesforce.com>
Authored: Fri Jan 26 00:43:06 2018 +0000
Committer: Pedro Boado <pb...@apache.org>
Committed: Wed Jan 31 22:26:11 2018 +0000
----------------------------------------------------------------------
.../org/apache/phoenix/end2end/OrderByIT.java | 111 +++++++++++++++++++
.../org/apache/phoenix/compile/ScanRanges.java | 5 -
.../phoenix/compile/QueryCompilerTest.java | 15 +++
3 files changed, 126 insertions(+), 5 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/9994059a/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
index ebbeeb4..3bce9c7 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
@@ -39,6 +39,7 @@ import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
+import org.apache.phoenix.jdbc.PhoenixStatement;
import org.apache.phoenix.util.PropertiesUtil;
import org.junit.Test;
@@ -544,6 +545,116 @@ public class OrderByIT extends ParallelStatsDisabledIT {
}
@Test
+ public void testAggregateOrderBy() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String tableName = generateUniqueName();
+ String ddl = "create table " + tableName + " (ID VARCHAR NOT NULL PRIMARY KEY, VAL1 VARCHAR, VAL2 INTEGER)";
+ conn.createStatement().execute(ddl);
+
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABC','aa123', 11)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABD','ba124', 1)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABE','cf125', 13)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABF','dan126', 4)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABG','elf127', 15)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABH','fan128', 6)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAA','get211', 100)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAB','hat212', 7)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAC','aap12', 2)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAD','ball12', 3)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAE','inn2110', 13)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAF','key2112', 40)");
+ conn.commit();
+
+ ResultSet rs;
+ PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
+ rs = stmt.executeQuery("select distinct ID, VAL1, VAL2 from " + tableName + " where ID in ('ABC','ABD','ABE','ABF','ABG','ABH','AAA', 'AAB', 'AAC','AAD','AAE','AAF') order by VAL1");
+ assertFalse(stmt.getQueryPlan().getOrderBy().getOrderByExpressions().isEmpty());
+ assertTrue(rs.next());
+ assertEquals("ABC", rs.getString(1));
+ assertEquals("aa123", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("aap12", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("ba124", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("ball12", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("cf125", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("dan126", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("elf127", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("fan128", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("get211", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("hat212", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("inn2110", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("AAF", rs.getString(1));
+ assertEquals("key2112", rs.getString(2));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testAggregateOptimizedOutOrderBy() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String tableName = generateUniqueName();
+ String ddl = "create table " + tableName + " (K1 VARCHAR NOT NULL, K2 VARCHAR NOT NULL, VAL1 VARCHAR, VAL2 INTEGER, CONSTRAINT pk PRIMARY KEY(K1,K2))";
+ conn.createStatement().execute(ddl);
+
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABC','ABC','aa123', 11)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABD','ABC','ba124', 1)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABE','ABC','cf125', 13)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABF','ABC','dan126', 4)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABG','ABC','elf127', 15)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('ABH','ABC','fan128', 6)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAA','ABC','get211', 100)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAB','ABC','hat212', 7)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAC','ABC','aap12', 2)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAD','ABC','ball12', 3)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAE','ABC','inn2110', 13)");
+ conn.createStatement().execute("upsert into " + tableName + " values ('AAF','ABC','key2112', 40)");
+ conn.commit();
+
+ ResultSet rs;
+ PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
+ rs = stmt.executeQuery("select distinct K2, VAL1, VAL2 from " + tableName + " where K2 = 'ABC' order by VAL1");
+ assertTrue(stmt.getQueryPlan().getOrderBy().getOrderByExpressions().isEmpty());
+ assertTrue(rs.next());
+ assertEquals("ABC", rs.getString(1));
+ assertEquals("aa123", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("aap12", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("ba124", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("ball12", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("cf125", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("dan126", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("elf127", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("fan128", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("get211", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("hat212", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("inn2110", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("ABC", rs.getString(1));
+ assertEquals("key2112", rs.getString(2));
+ assertFalse(rs.next());
+ }
+
+ @Test
public void testNullsLastWithDesc() throws Exception {
Connection conn=null;
try {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/9994059a/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
index 817c1bc..18e575c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
@@ -44,7 +44,6 @@ import org.apache.phoenix.schema.ValueSchema.Field;
import org.apache.phoenix.schema.types.PDataType.PDataCodec;
import org.apache.phoenix.schema.types.PLong;
import org.apache.phoenix.util.ByteUtil;
-import org.apache.phoenix.util.DateUtil;
import org.apache.phoenix.util.ScanUtil;
import org.apache.phoenix.util.ScanUtil.BytesComparator;
import org.apache.phoenix.util.SchemaUtil;
@@ -628,10 +627,6 @@ public class ScanRanges {
}
public boolean hasEqualityConstraint(int pkPosition) {
- if (isPointLookup) {
- return true;
- }
-
int pkOffset = 0;
int nRanges = ranges.size();
http://git-wip-us.apache.org/repos/asf/phoenix/blob/9994059a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
index b3c7dca..5a672ba 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
@@ -1827,6 +1827,21 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
}
@Test
+ public void testNotOrderByOrderPreservingForAggregation() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE IF NOT EXISTS VA_TEST(ID VARCHAR NOT NULL PRIMARY KEY, VAL1 VARCHAR, VAL2 INTEGER)");
+ String[] queries = {
+ "select distinct ID, VAL1, VAL2 from VA_TEST where \"ID\" in ('ABC','ABD','ABE','ABF','ABG','ABH','AAA', 'AAB', 'AAC','AAD','AAE','AAF') order by VAL1 ASC"
+ };
+ String query;
+ for (int i = 0; i < queries.length; i++) {
+ query = queries[i];
+ QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query);
+ assertFalse("Expected order by not to be compiled out: " + query, plan.getOrderBy().getOrderByExpressions().isEmpty());
+ }
+ }
+
+ @Test
public void testGroupByOrderPreserving() throws Exception {
Connection conn = DriverManager.getConnection(getUrl());
conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 date not null, k3 date not null, v varchar, constraint pk primary key(k1,k2,k3))");