phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [1/2] phoenix git commit: PHOENIX-3491 OrderBy can not be compiled out if GroupBy is not orderPreserving and OrderBy is reverse (chenglei)
Date Fri, 18 Nov 2016 23:36:31 GMT
Repository: phoenix
Updated Branches:
  refs/heads/master 66d6bba6d -> 279eb1707


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/91090262
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/91090262
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/91090262

Branch: refs/heads/master
Commit: 9109026240ef0a0ece534f0b625a34c49be44568
Parents: 66d6bba
Author: James Taylor <jamestaylor@apache.org>
Authored: Fri Nov 18 13:34:58 2016 -0800
Committer: James Taylor <jamestaylor@apache.org>
Committed: Fri Nov 18 13:34:58 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/91090262/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/91090262/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/91090262/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/91090262/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);


Mime
View raw message