Repository: phoenix Updated Branches: refs/heads/4.x-HBase-1.1 3cb0a1d89 -> f98f16f0e
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/f98f16f0 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/f98f16f0 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/f98f16f0 Branch: refs/heads/4.x-HBase-1.1 Commit: f98f16f0e17472fa7c007d588ebe21e830e3a918 Parents: 3cb0a1d Author: James Taylor <jtay...@salesforce.com> Authored: Thu Jan 25 16:43:06 2018 -0800 Committer: James Taylor <jtay...@salesforce.com> Committed: Fri Jan 26 14:33:58 2018 -0800 ---------------------------------------------------------------------- .../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/f98f16f0/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/f98f16f0/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/f98f16f0/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))");
