PHOENIX-3469 Incorrect sort order for DESC primary key for NULLS LAST/NULLS FIRST (chenglei)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/319d1412 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/319d1412 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/319d1412 Branch: refs/heads/4.x-HBase-1.1 Commit: 319d14120702b61e57c17d017093dcd998340873 Parents: 9eccf91 Author: James Taylor <[email protected]> Authored: Mon Nov 14 16:46:04 2016 -0800 Committer: James Taylor <[email protected]> Committed: Wed Nov 16 21:47:15 2016 -0800 ---------------------------------------------------------------------- .../org/apache/phoenix/end2end/OrderByIT.java | 233 ++++++++++++++++ .../apache/phoenix/compile/OrderByCompiler.java | 1 - .../phoenix/expression/OrderByExpression.java | 1 - .../phoenix/jdbc/PhoenixDatabaseMetaData.java | 2 +- .../phoenix/compile/QueryCompilerTest.java | 271 ++++++++++++++++++- 5 files changed, 504 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/319d1412/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 457b38e..1de9af5 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 @@ -544,4 +544,237 @@ public class OrderByIT extends ParallelStatsDisabledIT { assertEquals(0.9, rs.getDouble(1), 0.01); assertEquals("a", rs.getString(2)); } + + @Test + public void testNullsLastWithDesc() 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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+" 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()); + } + } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/phoenix/blob/319d1412/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java index 9bc0c31..1097f70 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java @@ -141,7 +141,6 @@ public class OrderByCompiler { // since this is the order they actually are in. if (expression.getSortOrder() == SortOrder.DESC) { isAscending = !isAscending; - isNullsLast = !isNullsLast; } OrderByExpression orderByExpression = new OrderByExpression(expression, isNullsLast, isAscending); orderByExpressions.add(orderByExpression); http://git-wip-us.apache.org/repos/asf/phoenix/blob/319d1412/phoenix-core/src/main/java/org/apache/phoenix/expression/OrderByExpression.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/OrderByExpression.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/OrderByExpression.java index 456e58b..50a7847 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/OrderByExpression.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/OrderByExpression.java @@ -92,7 +92,6 @@ public class OrderByExpression implements Writable { // to the user. if (e.getSortOrder() == SortOrder.DESC) { isAscending = !isAscending; - isNullsLast = !isNullsLast; } return e + (isAscending ? "" : " DESC") + (isNullsLast ? " NULLS LAST" : ""); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/319d1412/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java index 2b4783b..54080d1 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java @@ -1085,7 +1085,7 @@ public class PhoenixDatabaseMetaData implements DatabaseMetaData { SELF_REFERENCING_COL_NAME + "," + REF_GENERATION + "," + IndexStateNameFunction.NAME + "(" + INDEX_STATE + ") AS " + INDEX_STATE + "," + - IMMUTABLE_ROWS + "," + + IMMUTABLE_ROWS + "," + SALT_BUCKETS + "," + MULTI_TENANT + "," + VIEW_STATEMENT + "," + http://git-wip-us.apache.org/repos/asf/phoenix/blob/319d1412/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 a5e9383..30e3aaa 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 @@ -2787,7 +2787,6 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { try { conn= DriverManager.getConnection(getUrl()); - conn.createStatement().execute("DROP TABLE if exists GROUPBYDESC3452"); String sql="CREATE TABLE GROUPBYDESC3452 ( "+ "ORGANIZATION_ID VARCHAR,"+ "CONTAINER_ID VARCHAR,"+ @@ -3092,6 +3091,276 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { } } + @Test + public void testOrderByDescWithNullsLastBug3469() throws Exception { + Connection conn=null; + try { + conn= DriverManager.getConnection(getUrl()); + + String sql="CREATE TABLE DESCNULLSLAST3469 ( "+ + "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 DESCNULLSLAST3469 order by ORGANIZATION_ID ASC NULLS FIRST"; + QueryPlan queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESCNULLSLAST3469 order by ORGANIZATION_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getOrderBy()== OrderBy.REV_ROW_KEY_ORDER_BY); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getOrderBy()== OrderBy.FWD_ROW_KEY_ORDER_BY); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESCNULLSLAST3469 order by CONTAINER_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getOrderBy() == OrderBy.REV_ROW_KEY_ORDER_BY); + + //-----ORGANIZATION_ID ASC CONTAINER_ID DESC + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESCNULLSLAST3469 order by ORGANIZATION_ID ASC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID ASC NULLS FIRST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID ASC NULLS LAST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID ASC NULLS LAST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + assertTrue(queryPlan.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY); + + sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"; + queryPlan =getQueryPlan(conn, sql); + 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 DESCNULLSLAST3469 order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"; + queryPlan =getQueryPlan(conn, sql); + 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);
