PHOENIX-3452 NULLS FIRST/NULL LAST should not impact whether GROUP BY is order preserving (chenglei)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/6fd8e720 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/6fd8e720 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/6fd8e720 Branch: refs/heads/master Commit: 6fd8e72045fd0a8808cb774f7f5f7a96ef0cef79 Parents: 7cdd7ca Author: James Taylor <[email protected]> Authored: Mon Nov 14 16:36:16 2016 -0800 Committer: James Taylor <[email protected]> Committed: Wed Nov 16 21:45:50 2016 -0800 ---------------------------------------------------------------------- .../apache/phoenix/end2end/GroupByCaseIT.java | 308 ++++++++++++++++++ .../phoenix/compile/OrderPreservingTracker.java | 6 +- .../phoenix/compile/QueryCompilerTest.java | 319 +++++++++++++++++++ 3 files changed, 630 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/6fd8e720/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java index 1b32cfc..31cd9c4 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java @@ -512,4 +512,312 @@ public class GroupByCaseIT extends ParallelStatsDisabledIT { } catch (AmbiguousColumnException e) {} conn.close(); } + + @Test + public void testDistinctGroupByBug3452WithoutMultiTenant() throws Exception { + doTestDistinctGroupByBug3452(""); + } + + @Test + public void testDistinctGroupByBug3452WithMultiTenant() throws Exception { + doTestDistinctGroupByBug3452("VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=31536000"); + } + + private void doTestDistinctGroupByBug3452(String options) 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 CHAR(15) NOT NULL,"+ + "CONTAINER_ID CHAR(15) NOT NULL,"+ + "ENTITY_ID CHAR(15) NOT NULL,"+ + "SCORE DOUBLE,"+ + "CONSTRAINT TEST_PK PRIMARY KEY ( "+ + "ORGANIZATION_ID,"+ + "CONTAINER_ID,"+ + "ENTITY_ID"+ + ")) "+options; + conn.createStatement().execute(sql); + + String indexTableName=generateUniqueName(); + conn.createStatement().execute("DROP INDEX IF EXISTS "+indexTableName+" ON "+tableName); + conn.createStatement().execute("CREATE INDEX "+indexTableName+" ON "+tableName+" (CONTAINER_ID, SCORE DESC, ENTITY_ID DESC)"); + + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('org1','container1','entityId6',1.1)"); + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('org1','container1','entityId5',1.2)"); + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('org1','container1','entityId4',1.3)"); + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('org1','container1','entityId3',1.4)"); + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('org1','container1','entityId2',1.5)"); + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('org1','container1','entityId1',1.6)"); + conn.commit(); + + sql="SELECT DISTINCT entity_id,score FROM "+tableName+" WHERE organization_id = 'org1' AND container_id = 'container1' ORDER BY score DESC"; + ResultSet rs=conn.createStatement().executeQuery(sql); + assertTrue(rs.next()); + assertTrue(rs.getString(1).equals("entityId1")); + assertEquals(rs.getDouble(2),1.6,0.0001); + + assertTrue(rs.next()); + assertTrue(rs.getString(1).equals("entityId2")); + assertEquals(rs.getDouble(2),1.5,0.0001); + + assertTrue(rs.next()); + assertTrue(rs.getString(1).equals("entityId3")); + assertEquals(rs.getDouble(2),1.4,0.0001); + + assertTrue(rs.next()); + assertTrue(rs.getString(1).equals("entityId4")); + assertEquals(rs.getDouble(2),1.3,0.0001); + + assertTrue(rs.next()); + assertTrue(rs.getString(1).equals("entityId5")); + assertEquals(rs.getDouble(2),1.2,0.0001); + + assertTrue(rs.next()); + assertTrue(rs.getString(1).equals("entityId6")); + assertEquals(rs.getDouble(2),1.1,0.0001); + assertTrue(!rs.next()); + } finally { + if(conn!=null) { + conn.close(); + } + } + } + + @Test + public void testGroupByDescColumnWithNullsLastBug3452() 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,"+ + "ENTITY_ID VARCHAR NOT NULL,"+ + "CONSTRAINT TEST_PK PRIMARY KEY ( "+ + "ORGANIZATION_ID DESC,"+ + "CONTAINER_ID DESC,"+ + "ENTITY_ID"+ + "))"; + conn.createStatement().execute(sql); + + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('a',null,'11')"); + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'2','22')"); + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('c','3','33')"); + conn.commit(); + + //-----ORGANIZATION_ID + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS FIRST"; + ResultSet rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{null,"a"},{"3","c"},}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null}}); + + //----CONTAINER_ID + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{"2",null},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"}}); + + conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,'44')"); + conn.commit(); + + //-----ORGANIZATION_ID ASC CONTAINER_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,null},{"2",null},{null,"a"},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{null,null},{null,"a"},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{null,null},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null},{null,null}}); + + //-----ORGANIZATION_ID ASC CONTAINER_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,null},{"2",null},{null,"a"},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{null,null},{null,"a"},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{null,null},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null},{null,null}}); + + //-----ORGANIZATION_ID DESC CONTAINER_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,null},{"2",null},{"3","c"},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{null,null},{"3","c"},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{null,null},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null},{null,null}}); + + //-----ORGANIZATION_ID DESC CONTAINER_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_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 String[][]{{null,null},{"2",null},{"3","c"},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_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 String[][]{{"2",null},{null,null},{"3","c"},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_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 String[][]{{"3","c"},{null,"a"},{null,null},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_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 String[][]{{"3","c"},{null,"a"},{"2",null},{null,null}}); + + //-----CONTAINER_ID ASC ORGANIZATION_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"2",null},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"2",null},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,null},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"},{null,null}}); + + //-----CONTAINER_ID ASC ORGANIZATION_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"2",null},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"2",null},{"3","c"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,null},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"},{null,null}}); + + //-----CONTAINER_ID DESC ORGANIZATION_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"3","c"},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"3","c"},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,null},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"},{null,null}}); + + //-----CONTAINER_ID DESC ORGANIZATION_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"3","c"},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"3","c"},{"2",null}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,null},{null,"a"}}); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"},{null,null}}); + } finally { + if(conn!=null) { + conn.close(); + } + } + } + + private void assertResultSet(ResultSet rs,String[][] 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]; + if(realValue==null) { + assertTrue(expectedValue==null); + } + else { + assertTrue(realValue.equals(expectedValue)); + } + } + } + assertTrue(!rs.next()); + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/6fd8e720/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 8f09337..3aa6f06 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 @@ -100,10 +100,10 @@ public class OrderPreservingTracker { public void track(Expression node) { SortOrder sortOrder = node.getSortOrder(); - track(node, sortOrder, sortOrder != SortOrder.getDefault()); + track(node, sortOrder, null); } - public void track(Expression node, SortOrder sortOrder, boolean isNullsLast) { + public void track(Expression node, SortOrder sortOrder, Boolean isNullsLast) { if (isOrderPreserving) { Info info = node.accept(visitor); if (info == null) { @@ -138,7 +138,7 @@ public class OrderPreservingTracker { return; } } - if (node.isNullable()) { + if (isNullsLast!=null && node.isNullable()) { if (!Boolean.valueOf(isNullsLast).equals(isReverse)) { isOrderPreserving = false; isReverse = false; http://git-wip-us.apache.org/repos/asf/phoenix/blob/6fd8e720/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 7488c72..a5e9383 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 @@ -2779,4 +2779,323 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { conn.close(); } } + + @Test + public void testGroupByDescColumnBug3452() throws Exception { + + Connection conn=null; + try { + conn= DriverManager.getConnection(getUrl()); + + conn.createStatement().execute("DROP TABLE if exists GROUPBYDESC3452"); + String sql="CREATE TABLE GROUPBYDESC3452 ( "+ + "ORGANIZATION_ID VARCHAR,"+ + "CONTAINER_ID VARCHAR,"+ + "ENTITY_ID VARCHAR NOT NULL,"+ + "CONSTRAINT TEST_PK PRIMARY KEY ( "+ + "ORGANIZATION_ID DESC,"+ + "CONTAINER_ID DESC,"+ + "ENTITY_ID"+ + "))"; + conn.createStatement().execute(sql); + + //-----ORGANIZATION_ID + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS FIRST"; + QueryPlan queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy()== OrderBy.REV_ROW_KEY_ORDER_BY); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy()== OrderBy.FWD_ROW_KEY_ORDER_BY); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + + //----CONTAINER_ID + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC NULLS LAST")); + + //-----ORGANIZATION_ID ASC CONTAINER_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy() == OrderBy.REV_ROW_KEY_ORDER_BY); + + //-----ORGANIZATION_ID ASC CONTAINER_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS FIRST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID DESC NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS LAST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID ASC NULLS LAST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID DESC NULLS LAST")); + + //-----ORGANIZATION_ID DESC CONTAINER_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID NULLS LAST")); + + //-----ORGANIZATION_ID DESC CONTAINER_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID DESC NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("CONTAINER_ID DESC NULLS LAST")); + + //-----CONTAINER_ID ASC ORGANIZATION_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID NULLS LAST")); + + //-----CONTAINER_ID ASC ORGANIZATION_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + + //-----CONTAINER_ID DESC ORGANIZATION_ID ASC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID NULLS LAST")); + + //-----CONTAINER_ID DESC ORGANIZATION_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID,CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM GROUPBYDESC3452 group by ORGANIZATION_ID, CONTAINER_ID order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().isOrderPreserving()); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==2); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC NULLS LAST")); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(1).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); + + } 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); + queryPlan.iterator(); + return queryPlan; + } }
