Repository: phoenix Updated Branches: refs/heads/4.8-HBase-1.2 0d964a05f -> 95307cd9e
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/1cd94b2f Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/1cd94b2f Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/1cd94b2f Branch: refs/heads/4.8-HBase-1.2 Commit: 1cd94b2fcadccbcbd8bbaeb447baf9f57bb532d8 Parents: 0d964a0 Author: James Taylor <[email protected]> Authored: Mon Nov 14 16:36:16 2016 -0800 Committer: James Taylor <[email protected]> Committed: Wed Nov 16 21:59:09 2016 -0800 ---------------------------------------------------------------------- .../apache/phoenix/end2end/GroupByCaseIT.java | 308 ++++++++++++++++ .../phoenix/compile/OrderPreservingTracker.java | 6 +- .../phoenix/compile/QueryCompilerTest.java | 358 +++++++++++++++++++ 3 files changed, 669 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/1cd94b2f/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 b0524da..7c202ef 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 @@ -435,4 +435,312 @@ public class GroupByCaseIT extends BaseHBaseManagedTimeIT { " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [K1]", QueryUtil.getExplainPlan(rs)); } + + @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/1cd94b2f/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/1cd94b2f/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 c91b855..3e43965 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 @@ -2427,4 +2427,362 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { conn.close(); } } + + @Test + public void testIndexOnViewWithChildView() throws SQLException { + try (Connection conn = DriverManager.getConnection(getUrl())) { + conn.createStatement().execute("CREATE TABLE PLATFORM_ENTITY.GLOBAL_TABLE (\n" + + " ORGANIZATION_ID CHAR(15) NOT NULL,\n" + + " KEY_PREFIX CHAR(3) NOT NULL,\n" + + " CREATED_DATE DATE,\n" + + " CREATED_BY CHAR(15),\n" + + " CONSTRAINT PK PRIMARY KEY (\n" + + " ORGANIZATION_ID,\n" + + " KEY_PREFIX\n" + + " )\n" + + ") VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true"); + conn.createStatement().execute("CREATE VIEW PLATFORM_ENTITY.GLOBAL_VIEW (\n" + + " INT1 BIGINT NOT NULL,\n" + + " DOUBLE1 DECIMAL(12, 3),\n" + + " IS_BOOLEAN BOOLEAN,\n" + + " TEXT1 VARCHAR,\n" + + " CONSTRAINT PKVIEW PRIMARY KEY\n" + + " (\n" + + " INT1\n" + + " )\n" + + ")\n" + + "AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE KEY_PREFIX = '123'"); + conn.createStatement().execute("CREATE INDEX GLOBAL_INDEX\n" + + "ON PLATFORM_ENTITY.GLOBAL_VIEW (TEXT1 DESC, INT1)\n" + + "INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE)"); + String query = "SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW\n" + + "WHERE ORGANIZATION_ID = '00Dxx0000002Col' AND TEXT1='Test' AND INT1=1"; + QueryPlan plan = getQueryPlan(conn, query); + assertEquals("PLATFORM_ENTITY.GLOBAL_VIEW", plan.getContext().getCurrentTable().getTable().getName() + .getString()); + query = "SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW\n" + + "WHERE ORGANIZATION_ID = '00Dxx0000002Col' AND TEXT1='Test'"; + plan = getQueryPlan(conn, query); + assertEquals("PLATFORM_ENTITY.GLOBAL_INDEX", plan.getContext().getCurrentTable().getTable().getName().getString()); + } + } + + @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; + } }
