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 <[email protected]> Authored: Fri Nov 18 13:34:58 2016 -0800 Committer: James Taylor <[email protected]> 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);
