You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by sa...@apache.org on 2016/11/22 02:54:28 UTC
[24/36] phoenix git commit: PHOENIX-3491 OrderBy can not be compiled
out if GroupBy is not orderPreserving and OrderBy is reverse (chenglei)
PHOENIX-3491 OrderBy can not be compiled out if GroupBy is not orderPreserving and OrderBy is reverse (chenglei)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/b9ca16d7
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/b9ca16d7
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/b9ca16d7
Branch: refs/heads/encodecolumns2
Commit: b9ca16d79a2234bf7843e01da37c68d224adaa6c
Parents: 8cdcbe8
Author: James Taylor <ja...@apache.org>
Authored: Fri Nov 18 13:34:58 2016 -0800
Committer: James Taylor <ja...@apache.org>
Committed: Fri Nov 18 15:39:04 2016 -0800
----------------------------------------------------------------------
.../org/apache/phoenix/end2end/OrderByIT.java | 317 +++++++++++++-
.../phoenix/compile/OrderPreservingTracker.java | 10 -
.../apache/phoenix/execute/AggregatePlan.java | 14 +-
.../phoenix/compile/QueryCompilerTest.java | 419 ++++++++++++++++++-
4 files changed, 743 insertions(+), 17 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/b9ca16d7/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 1de9af5..cbdd6e1 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
@@ -760,12 +760,323 @@ public class OrderByIT extends ParallelStatsDisabledIT {
}
}
- private void assertResultSet(ResultSet rs,String[][] rows) throws Exception {
+ @Test
+ public void testOrderByReverseOptimizationBug3491() throws Exception {
+ for(boolean salted: new boolean[]{true,false}) {
+ doTestOrderByReverseOptimizationBug3491(salted,true,true,true);
+ doTestOrderByReverseOptimizationBug3491(salted,true,true,false);
+ doTestOrderByReverseOptimizationBug3491(salted,true,false,true);
+ doTestOrderByReverseOptimizationBug3491(salted,true,false,false);
+ doTestOrderByReverseOptimizationBug3491(salted,false,true,true);
+ doTestOrderByReverseOptimizationBug3491(salted,false,true,false);
+ doTestOrderByReverseOptimizationBug3491(salted,false,false,true);
+ doTestOrderByReverseOptimizationBug3491(salted,false,false,false);
+ }
+ }
+
+ private void doTestOrderByReverseOptimizationBug3491(boolean salted,boolean desc1,boolean desc2,boolean desc3) throws Exception {
+ Connection conn = null;
+ try {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ conn = DriverManager.getConnection(getUrl(), props);
+ String tableName=generateUniqueName();
+ conn.createStatement().execute("DROP TABLE if exists "+tableName);
+ String sql="CREATE TABLE "+tableName+" ( "+
+ "ORGANIZATION_ID INTEGER NOT NULL,"+
+ "CONTAINER_ID INTEGER NOT NULL,"+
+ "SCORE INTEGER NOT NULL,"+
+ "ENTITY_ID INTEGER NOT NULL,"+
+ "CONSTRAINT TEST_PK PRIMARY KEY ( "+
+ "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
+ "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
+ "SCORE"+(desc3 ? " DESC" : "" )+","+
+ "ENTITY_ID"+
+ ")) "+(salted ? "SALT_BUCKETS =4" : "split on(4)");
+ conn.createStatement().execute(sql);
+
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,1,1,1)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,2,2,2)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,3,3,3)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (4,4,4,4)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (5,5,5,5)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (6,6,6,6)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,1,1,11)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,2,2,22)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,3,3,33)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (4,4,4,44)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (5,5,5,55)");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (6,6,6,66)");
+ conn.commit();
+
+ //groupBy orderPreserving orderBy asc asc
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID ASC";
+ ResultSet rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+ //groupBy orderPreserving orderBy asc desc
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID desc";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+ //groupBy orderPreserving orderBy desc asc
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID DESC";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+
+ //groupBy orderPreserving orderBy desc desc
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID DESC";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+
+ //groupBy not orderPreserving orderBy asc asc
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE ASC";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+ //groupBy not orderPreserving orderBy asc desc
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE ASC";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+ //groupBy not orderPreserving orderBy desc asc
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+
+ //groupBy not orderPreserving orderBy desc desc
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+ } finally {
+ if(conn!=null) {
+ conn.close();
+ }
+ }
+ }
+
+ @Test
+ public void testOrderByReverseOptimizationWithNUllsLastBug3491() throws Exception{
+ for(boolean salted: new boolean[]{true,false}) {
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,true,true);
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,true,false);
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,false,true);
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,false,false);
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,true,true);
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,true,false);
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,false,true);
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,false,false);
+ }
+ }
+
+ private void doTestOrderByReverseOptimizationWithNUllsLastBug3491(boolean salted,boolean desc1,boolean desc2,boolean desc3) throws Exception {
+ Connection conn = null;
+ try {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ conn = DriverManager.getConnection(getUrl(), props);
+ String tableName=generateUniqueName();
+ conn.createStatement().execute("DROP TABLE if exists "+tableName);
+ String sql="CREATE TABLE "+tableName+" ( "+
+ "ORGANIZATION_ID VARCHAR,"+
+ "CONTAINER_ID VARCHAR,"+
+ "SCORE VARCHAR,"+
+ "ENTITY_ID VARCHAR NOT NULL,"+
+ "CONSTRAINT TEST_PK PRIMARY KEY ( "+
+ "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
+ "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
+ "SCORE"+(desc3 ? " DESC" : "" )+","+
+ "ENTITY_ID"+
+ ")) "+(salted ? "SALT_BUCKETS =4" : "split on('4')");
+ conn.createStatement().execute(sql);
+
+ for(int i=1;i<=6;i++) {
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'"+i+"','"+i+"','"+i+"')");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'"+i+"',null,'"+i+"')");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,'"+i+"','"+i+"')");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,null,'"+i+"')");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"','"+i+"','"+i+"','"+i+"')");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"','"+i+"',null,'"+i+"')");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"',null,'"+i+"','"+i+"')");
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"',null,null,'"+i+"')");
+ }
+ conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,null,'66')");
+ conn.commit();
+
+ //groupBy orderPreserving orderBy asc asc
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
+ ResultSet rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+ //groupBy orderPreserving orderBy asc desc
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+ //groupBy orderPreserving orderBy desc asc
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+ //groupBy orderPreserving orderBy desc desc
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+ sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+ //-----groupBy not orderPreserving--
+
+ //groupBy not orderPreserving orderBy asc asc
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+ //groupBy not orderPreserving orderBy asc desc
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+ //groupBy not orderPreserving orderBy desc asc
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+ //groupBy not orderPreserving orderBy desc desc
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+ sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+ //-------test only one return column----------------------------------
+
+ sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null},{"1"},{"2"},{"3"},{"4"},{"5"},{"6"}});
+
+ sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"1"},{"2"},{"3"},{"4"},{"5"},{"6"},{null}});
+
+ sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS FIRST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{null},{"6"},{"5"},{"4"},{"3"},{"2"},{"1"}});
+
+ sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS LAST";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{"6"},{"5"},{"4"},{"3"},{"2"},{"1"},{null}});
+ } finally {
+ if(conn!=null) {
+ conn.close();
+ }
+ }
+ }
+
+ private void assertResultSet(ResultSet rs,Object[][] rows) throws Exception {
for(int rowIndex=0;rowIndex<rows.length;rowIndex++) {
assertTrue(rs.next());
for(int columnIndex=1;columnIndex<= rows[rowIndex].length;columnIndex++) {
- String realValue=rs.getString(columnIndex);
- String expectedValue=rows[rowIndex][columnIndex-1];
+ Object realValue=rs.getObject(columnIndex);
+ Object expectedValue=rows[rowIndex][columnIndex-1];
if(realValue==null) {
assertTrue(expectedValue==null);
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/b9ca16d7/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java
index e9603d7..0c073ce 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java
@@ -120,16 +120,6 @@ public class OrderPreservingTracker {
if (node.getSortOrder() != sortOrder) {
if (isReverse == null) {
isReverse = true;
- /*
- * When a GROUP BY is not order preserving, we cannot do a reverse
- * scan to eliminate the ORDER BY since our server-side scan is not
- * ordered in that case.
- */
- if (!groupBy.isEmpty() && !groupBy.isOrderPreserving()) {
- isOrderPreserving = false;
- isReverse = false;
- return;
- }
} else if (!isReverse){
isOrderPreserving = false;
isReverse = false;
http://git-wip-us.apache.org/repos/asf/phoenix/blob/b9ca16d7/phoenix-core/src/main/java/org/apache/phoenix/execute/AggregatePlan.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/execute/AggregatePlan.java b/phoenix-core/src/main/java/org/apache/phoenix/execute/AggregatePlan.java
index 00d478a..84bb402 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/execute/AggregatePlan.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/execute/AggregatePlan.java
@@ -129,14 +129,22 @@ public class AggregatePlan extends BaseQueryPlan {
private static class OrderingResultIteratorFactory implements ParallelIteratorFactory {
private final QueryServices services;
+ private final OrderBy orderBy;
- public OrderingResultIteratorFactory(QueryServices services) {
+ public OrderingResultIteratorFactory(QueryServices services,OrderBy orderBy) {
this.services = services;
+ this.orderBy=orderBy;
}
@Override
public PeekingResultIterator newIterator(StatementContext context, ResultIterator scanner, Scan scan, String tableName, QueryPlan plan) throws SQLException {
Expression expression = RowKeyExpression.INSTANCE;
- OrderByExpression orderByExpression = new OrderByExpression(expression, false, true);
+ boolean isNullsLast=false;
+ boolean isAscending=true;
+ if(this.orderBy==OrderBy.REV_ROW_KEY_ORDER_BY) {
+ isNullsLast=true; //which is needed for the whole rowKey.
+ isAscending=false;
+ }
+ OrderByExpression orderByExpression = new OrderByExpression(expression, isNullsLast, isAscending);
int threshold = services.getProps().getInt(QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB, QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES);
return new OrderedResultIterator(scanner, Collections.<OrderByExpression>singletonList(orderByExpression), threshold);
}
@@ -167,7 +175,7 @@ public class AggregatePlan extends BaseQueryPlan {
innerFactory = new SpoolingResultIterator.SpoolingResultIteratorFactory(services);
}
} else {
- innerFactory = new OrderingResultIteratorFactory(services);
+ innerFactory = new OrderingResultIteratorFactory(services,this.getOrderBy());
}
if (parallelIteratorFactory == null) {
return innerFactory;
http://git-wip-us.apache.org/repos/asf/phoenix/blob/b9ca16d7/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 1706133..8daefc0 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
@@ -3461,7 +3461,424 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
}
}
-
+ @Test
+ public void testOrderByReverseOptimizationBug3491() throws Exception {
+ for(boolean salted: new boolean[]{true,false}) {
+ boolean[] groupBys=new boolean[]{true,true,true,true,false,false,false,false};
+ doTestOrderByReverseOptimizationBug3491(salted,true,true,true,
+ groupBys,
+ new OrderBy[]{
+ OrderBy.REV_ROW_KEY_ORDER_BY,null,null,OrderBy.FWD_ROW_KEY_ORDER_BY,
+ OrderBy.REV_ROW_KEY_ORDER_BY,null,null,OrderBy.FWD_ROW_KEY_ORDER_BY});
+
+ doTestOrderByReverseOptimizationBug3491(salted,true,true,false,
+ groupBys,
+ new OrderBy[]{
+ OrderBy.REV_ROW_KEY_ORDER_BY,null,null,OrderBy.FWD_ROW_KEY_ORDER_BY,
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null});
+
+ doTestOrderByReverseOptimizationBug3491(salted,true,false,true,
+ groupBys,
+ new OrderBy[]{
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null,
+ OrderBy.REV_ROW_KEY_ORDER_BY,null,null,OrderBy.FWD_ROW_KEY_ORDER_BY});
+
+ doTestOrderByReverseOptimizationBug3491(salted,true,false,false,
+ groupBys,
+ new OrderBy[]{
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null,
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null});
+
+ doTestOrderByReverseOptimizationBug3491(salted,false,true,true,
+ groupBys,
+ new OrderBy[]{
+ null,OrderBy.FWD_ROW_KEY_ORDER_BY,OrderBy.REV_ROW_KEY_ORDER_BY,null,
+ null,OrderBy.FWD_ROW_KEY_ORDER_BY,OrderBy.REV_ROW_KEY_ORDER_BY,null});
+
+ doTestOrderByReverseOptimizationBug3491(salted,false,true,false,
+ groupBys,
+ new OrderBy[]{
+ null,OrderBy.FWD_ROW_KEY_ORDER_BY,OrderBy.REV_ROW_KEY_ORDER_BY,null,
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY});
+
+ doTestOrderByReverseOptimizationBug3491(salted,false,false,true,
+ groupBys,
+ new OrderBy[]{
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ null,OrderBy.FWD_ROW_KEY_ORDER_BY,OrderBy.REV_ROW_KEY_ORDER_BY,null});
+
+ doTestOrderByReverseOptimizationBug3491(salted,false,false,false,
+ groupBys,
+ new OrderBy[]{
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY});
+ }
+ }
+
+ private void doTestOrderByReverseOptimizationBug3491(boolean salted,boolean desc1,boolean desc2,boolean desc3,boolean[] groupBys,OrderBy[] orderBys) throws Exception {
+ Connection conn = null;
+ try {
+ conn= DriverManager.getConnection(getUrl());
+ String tableName="ORDERBY3491_TEST";
+ conn.createStatement().execute("DROP TABLE if exists "+tableName);
+ String sql="CREATE TABLE "+tableName+" ( "+
+ "ORGANIZATION_ID INTEGER NOT NULL,"+
+ "CONTAINER_ID INTEGER NOT NULL,"+
+ "SCORE INTEGER NOT NULL,"+
+ "ENTITY_ID INTEGER NOT NULL,"+
+ "CONSTRAINT TEST_PK PRIMARY KEY ( "+
+ "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
+ "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
+ "SCORE"+(desc3 ? " DESC" : "" )+","+
+ "ENTITY_ID"+
+ ")) "+(salted ? "SALT_BUCKETS =4" : "");
+ conn.createStatement().execute(sql);
+
+
+ String[] sqls={
+ //groupBy orderPreserving orderBy asc asc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID ASC",
+ //groupBy orderPreserving orderBy asc desc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID DESC",
+ //groupBy orderPreserving orderBy desc asc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID ASC",
+ //groupBy orderPreserving orderBy desc desc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID DESC",
+
+ //groupBy not orderPreserving orderBy asc asc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE ASC",
+ //groupBy not orderPreserving orderBy asc desc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE DESC",
+ //groupBy not orderPreserving orderBy desc asc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE ASC",
+ //groupBy not orderPreserving orderBy desc desc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC"
+ };
+
+ for(int i=0;i< sqls.length;i++) {
+ sql=sqls[i];
+ QueryPlan queryPlan=getQueryPlan(conn, sql);
+ assertTrue((i+1) + ") " + sql,queryPlan.getGroupBy().isOrderPreserving()== groupBys[i]);
+ OrderBy orderBy=queryPlan.getOrderBy();
+ if(orderBys[i]!=null) {
+ assertTrue((i+1) + ") " + sql,orderBy == orderBys[i]);
+ }
+ else {
+ assertTrue((i+1) + ") " + sql,orderBy.getOrderByExpressions().size() > 0);
+ }
+ }
+ } finally {
+ if(conn!=null) {
+ conn.close();
+ }
+ }
+ }
+
+ @Test
+ public void testOrderByReverseOptimizationWithNUllsLastBug3491() throws Exception {
+ for(boolean salted: new boolean[]{true,false}) {
+ boolean[] groupBys=new boolean[]{
+ //groupBy orderPreserving orderBy asc asc
+ true,true,true,true,
+ //groupBy orderPreserving orderBy asc desc
+ true,true,true,true,
+ //groupBy orderPreserving orderBy desc asc
+ true,true,true,true,
+ //groupBy orderPreserving orderBy desc desc
+ true,true,true,true,
+
+ //groupBy not orderPreserving orderBy asc asc
+ false,false,false,false,
+ //groupBy not orderPreserving orderBy asc desc
+ false,false,false,false,
+ //groupBy not orderPreserving orderBy desc asc
+ false,false,false,false,
+ //groupBy not orderPreserving orderBy desc desc
+ false,false,false,false,
+
+ false,false,false,false};
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,true,true,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+
+ //groupBy not orderPreserving orderBy asc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy not orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null});
+
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,true,false,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+
+ //groupBy not orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy asc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy not orderPreserving orderBy desc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy not orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY});
+
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,false,true,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy asc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy orderPreserving orderBy desc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ //groupBy not orderPreserving orderBy asc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy not orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null});
+
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,false,false,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy asc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy orderPreserving orderBy desc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ //groupBy not orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy asc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy not orderPreserving orderBy desc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy not orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY});
+
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,true,true,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy asc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy orderPreserving orderBy desc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ //groupBy not orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy asc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy not orderPreserving orderBy desc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy not orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null});
+
+
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,true,false,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy asc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy orderPreserving orderBy desc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ //groupBy not orderPreserving orderBy asc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy not orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY});
+
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,false,true,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+
+ //groupBy not orderPreserving orderBy asc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy asc desc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy not orderPreserving orderBy desc asc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+ //groupBy not orderPreserving orderBy desc desc
+ null,null,null,null,
+
+ null,OrderBy.REV_ROW_KEY_ORDER_BY,OrderBy.FWD_ROW_KEY_ORDER_BY,null});
+
+ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,false,false,
+ groupBys,
+ new OrderBy[]{
+ //groupBy orderPreserving orderBy asc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy orderPreserving orderBy desc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+
+ //groupBy not orderPreserving orderBy asc asc
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,null,
+ //groupBy not orderPreserving orderBy asc desc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc asc
+ null,null,null,null,
+ //groupBy not orderPreserving orderBy desc desc
+ null,null,null,OrderBy.REV_ROW_KEY_ORDER_BY,
+
+ OrderBy.FWD_ROW_KEY_ORDER_BY,null,null,OrderBy.REV_ROW_KEY_ORDER_BY});
+ }
+ }
+
+ private void doTestOrderByReverseOptimizationWithNUllsLastBug3491(boolean salted,boolean desc1,boolean desc2,boolean desc3,boolean[] groupBys,OrderBy[] orderBys) throws Exception {
+ Connection conn = null;
+ try {
+ conn= DriverManager.getConnection(getUrl());
+ String tableName="ORDERBY3491_TEST";
+ conn.createStatement().execute("DROP TABLE if exists "+tableName);
+ String sql="CREATE TABLE "+tableName+" ( "+
+ "ORGANIZATION_ID VARCHAR,"+
+ "CONTAINER_ID VARCHAR,"+
+ "SCORE VARCHAR,"+
+ "ENTITY_ID VARCHAR NOT NULL,"+
+ "CONSTRAINT TEST_PK PRIMARY KEY ( "+
+ "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
+ "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
+ "SCORE"+(desc3 ? " DESC" : "" )+","+
+ "ENTITY_ID"+
+ ")) "+(salted ? "SALT_BUCKETS =4" : "");
+ conn.createStatement().execute(sql);
+
+ String[] sqls={
+ //groupBy orderPreserving orderBy asc asc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST",
+
+ //groupBy orderPreserving orderBy asc desc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST",
+
+ //groupBy orderPreserving orderBy desc asc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST",
+
+ //groupBy orderPreserving orderBy desc desc
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST",
+
+ //-----groupBy not orderPreserving
+
+ //groupBy not orderPreserving orderBy asc asc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST",
+
+ //groupBy not orderPreserving orderBy asc desc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST",
+
+ //groupBy not orderPreserving orderBy desc asc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST",
+
+ //groupBy not orderPreserving orderBy desc desc
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST",
+ "SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST",
+
+ //-------only one return column----------------------------------
+ "SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS FIRST",
+ "SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS LAST",
+ "SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS FIRST",
+ "SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS LAST"
+ };
+
+ for(int i=0;i< sqls.length;i++) {
+ sql=sqls[i];
+ QueryPlan queryPlan=getQueryPlan(conn, sql);
+ assertTrue((i+1) + ") " + sql,queryPlan.getGroupBy().isOrderPreserving()== groupBys[i]);
+ OrderBy orderBy=queryPlan.getOrderBy();
+ if(orderBys[i]!=null) {
+ assertTrue((i+1) + ") " + sql,orderBy == orderBys[i]);
+ }
+ else {
+ assertTrue((i+1) + ") " + sql,orderBy.getOrderByExpressions().size() > 0);
+ }
+ }
+ } finally {
+ if(conn!=null) {
+ conn.close();
+ }
+ }
+ }
+
private static QueryPlan getQueryPlan(Connection conn,String sql) throws SQLException {
PhoenixPreparedStatement statement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
QueryPlan queryPlan = statement.optimizeQuery(sql);