You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2016/11/17 06:09:18 UTC
[1/3] phoenix git commit: PHOENIX-3452 NULLS FIRST/NULL LAST should
not impact whether GROUP BY is order preserving (chenglei)
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 <ja...@apache.org>
Authored: Mon Nov 14 16:36:16 2016 -0800
Committer: James Taylor <ja...@apache.org>
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;
+ }
}
[3/3] phoenix git commit: PHOENX-3451 Incorrect determination of
preservation of order for an aggregate query leads to incorrect query results
(chenglei)
Posted by ja...@apache.org.
PHOENX-3451 Incorrect determination of preservation of order for an aggregate query leads to incorrect query results (chenglei)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/95307cd9
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/95307cd9
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/95307cd9
Branch: refs/heads/4.8-HBase-1.2
Commit: 95307cd9e2043469ea8d465607d1a5cd27cc0c74
Parents: 56562ea
Author: James Taylor <ja...@apache.org>
Authored: Wed Nov 16 10:40:31 2016 -0800
Committer: James Taylor <ja...@apache.org>
Committed: Wed Nov 16 22:08:52 2016 -0800
----------------------------------------------------------------------
.../apache/phoenix/end2end/GroupByCaseIT.java | 51 +++++++++-
.../phoenix/compile/OrderPreservingTracker.java | 100 ++++++++++++++++--
.../phoenix/compile/QueryCompilerTest.java | 101 +++++++++++++++++++
3 files changed, 245 insertions(+), 7 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/95307cd9/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 f26d8dd..25fbee6 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
@@ -32,6 +32,8 @@ import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
+import org.apache.phoenix.compile.QueryPlan;
+import org.apache.phoenix.jdbc.PhoenixStatement;
import org.apache.phoenix.schema.types.PChar;
import org.apache.phoenix.schema.types.PInteger;
import org.apache.phoenix.util.ByteUtil;
@@ -512,6 +514,54 @@ public class GroupByCaseIT extends BaseHBaseManagedTimeIT {
}
@Test
+ public void testGroupByOrderByDescBug3451() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ String tableName=generateRandomString();
+ String sql="CREATE TABLE " + tableName + " (\n" +
+ " ORGANIZATION_ID CHAR(15) NOT NULL,\n" +
+ " CONTAINER_ID CHAR(15) NOT NULL,\n" +
+ " ENTITY_ID CHAR(15) NOT NULL,\n" +
+ " SCORE DOUBLE,\n" +
+ " CONSTRAINT TEST_PK PRIMARY KEY (\n" +
+ " ORGANIZATION_ID,\n" +
+ " CONTAINER_ID,\n" +
+ " ENTITY_ID\n" +
+ " )\n" +
+ " )";
+ conn.createStatement().execute(sql);
+ String indexName=generateRandomString();
+ conn.createStatement().execute("CREATE INDEX " + indexName + " ON " + tableName + "(ORGANIZATION_ID,CONTAINER_ID, SCORE DESC, ENTITY_ID DESC)");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container2','entityId6',1.1)");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container1','entityId5',1.2)");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container2','entityId4',1.3)");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container1','entityId5',1.2)");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container1','entityId3',1.4)");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container3','entityId7',1.35)");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container3','entityId8',1.45)");
+ conn.commit();
+ String query = "SELECT DISTINCT entity_id, score\n" +
+ " FROM " + tableName + "\n" +
+ " WHERE organization_id = 'org2'\n" +
+ " AND container_id IN ( 'container1','container2','container3' )\n" +
+ " ORDER BY score DESC\n" +
+ " LIMIT 2";
+ Statement stmt = conn.createStatement();
+ ResultSet rs = stmt.executeQuery(query);
+ QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
+ assertEquals(indexName, plan.getContext().getCurrentTable().getTable().getName().getString());
+ assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty());
+ assertTrue(rs.next());
+ assertEquals("entityId8", rs.getString(1));
+ assertEquals(1.45, rs.getDouble(2),0.001);
+ assertTrue(rs.next());
+ assertEquals("entityId3", rs.getString(1));
+ assertEquals(1.4, rs.getDouble(2),0.001);
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
public void testGroupByDescColumnWithNullsLastBug3452() throws Exception {
Connection conn=null;
@@ -521,7 +571,6 @@ public class GroupByCaseIT extends BaseHBaseManagedTimeIT {
conn = DriverManager.getConnection(getUrl(), props);
String tableName=generateRandomString();
- conn.createStatement().execute("DROP TABLE if exists "+tableName);
String sql="CREATE TABLE "+tableName+" ( "+
"ORGANIZATION_ID VARCHAR,"+
"CONTAINER_ID VARCHAR,"+
http://git-wip-us.apache.org/repos/asf/phoenix/blob/95307cd9/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 3aa6f06..e9603d7 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
@@ -17,12 +17,15 @@ import java.util.List;
import org.apache.phoenix.compile.GroupByCompiler.GroupBy;
import org.apache.phoenix.execute.TupleProjector;
import org.apache.phoenix.expression.CoerceExpression;
+import org.apache.phoenix.expression.Determinism;
import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
import org.apache.phoenix.expression.ProjectedColumnExpression;
import org.apache.phoenix.expression.RowKeyColumnExpression;
import org.apache.phoenix.expression.RowValueConstructorExpression;
import org.apache.phoenix.expression.function.FunctionExpression.OrderPreserving;
import org.apache.phoenix.expression.function.ScalarFunction;
+import org.apache.phoenix.expression.visitor.StatelessTraverseAllExpressionVisitor;
import org.apache.phoenix.expression.visitor.StatelessTraverseNoExpressionVisitor;
import org.apache.phoenix.schema.PTable;
import org.apache.phoenix.schema.SortOrder;
@@ -71,6 +74,7 @@ public class OrderPreservingTracker {
private final Ordering ordering;
private final int pkPositionOffset;
private final List<Info> orderPreservingInfos;
+ private final TupleProjector projector;
private boolean isOrderPreserving = true;
private Boolean isReverse = null;
private int orderPreservingColumnCount = 0;
@@ -81,21 +85,23 @@ public class OrderPreservingTracker {
public OrderPreservingTracker(StatementContext context, GroupBy groupBy, Ordering ordering, int nNodes, TupleProjector projector) {
this.context = context;
- int pkPositionOffset = 0;
- PTable table = context.getResolver().getTables().get(0).getTable();
- isOrderPreserving = table.rowKeyOrderOptimizable();
- if (groupBy.isEmpty()) { // FIXME: would the below table have any of these set in the case of a GROUP BY?
+ if (groupBy.isEmpty()) {
+ PTable table = context.getResolver().getTables().get(0).getTable();
+ this.isOrderPreserving = table.rowKeyOrderOptimizable();
boolean isSalted = table.getBucketNum() != null;
boolean isMultiTenant = context.getConnection().getTenantId() != null && table.isMultiTenant();
boolean isSharedViewIndex = table.getViewIndexId() != null;
// TODO: util for this offset, as it's computed in numerous places
- pkPositionOffset = (isSalted ? 1 : 0) + (isMultiTenant ? 1 : 0) + (isSharedViewIndex ? 1 : 0);
+ this.pkPositionOffset = (isSalted ? 1 : 0) + (isMultiTenant ? 1 : 0) + (isSharedViewIndex ? 1 : 0);
+ } else {
+ this.isOrderPreserving = true;
+ this.pkPositionOffset = 0;
}
- this.pkPositionOffset = pkPositionOffset;
this.groupBy = groupBy;
this.visitor = new TrackOrderPreservingExpressionVisitor(projector);
this.orderPreservingInfos = Lists.newArrayListWithExpectedSize(nNodes);
this.ordering = ordering;
+ this.projector = projector;
}
public void track(Expression node) {
@@ -195,6 +201,31 @@ public class OrderPreservingTracker {
private boolean hasEqualityConstraints(int startPos, int endPos) {
ScanRanges ranges = context.getScanRanges();
+ // If a GROUP BY is being done, then the rows are ordered according to the GROUP BY key,
+ // not by the original row key order of the table (see PHOENIX-3451).
+ // We check each GROUP BY expression to see if it only references columns that are
+ // matched by equality constraints, in which case the expression itself would be constant.
+ // FIXME: this only recognizes row key columns that are held constant, not all columns.
+ // FIXME: we should optimize out any GROUP BY or ORDER BY expression which is deemed to
+ // be held constant based on the WHERE clause.
+ if (!groupBy.isEmpty()) {
+ for (int pos = startPos; pos < endPos; pos++) {
+ IsConstantVisitor visitor = new IsConstantVisitor(this.projector, ranges);
+ List<Expression> groupByExpressions = groupBy.getExpressions();
+ if (pos >= groupByExpressions.size()) { // sanity check - shouldn't be necessary
+ return false;
+ }
+ Expression groupByExpression = groupByExpressions.get(pos);
+ if ( groupByExpression.getDeterminism().ordinal() > Determinism.PER_STATEMENT.ordinal() ) {
+ return false;
+ }
+ Boolean isConstant = groupByExpression.accept(visitor);
+ if (!Boolean.TRUE.equals(isConstant)) {
+ return false;
+ }
+ }
+ return true;
+ }
for (int pos = startPos; pos < endPos; pos++) {
if (!ranges.hasEqualityConstraint(pos)) {
return false;
@@ -207,6 +238,63 @@ public class OrderPreservingTracker {
return Boolean.TRUE.equals(isReverse);
}
+ /**
+ *
+ * Determines if an expression is held constant. Only works for columns in the PK currently,
+ * as we only track whether PK columns are held constant.
+ *
+ */
+ private static class IsConstantVisitor extends StatelessTraverseAllExpressionVisitor<Boolean> {
+ private final TupleProjector projector;
+ private final ScanRanges scanRanges;
+
+ public IsConstantVisitor(TupleProjector projector, ScanRanges scanRanges) {
+ this.projector = projector;
+ this.scanRanges = scanRanges;
+ }
+
+ @Override
+ public Boolean defaultReturn(Expression node, List<Boolean> returnValues) {
+ if (node.getDeterminism().ordinal() > Determinism.PER_STATEMENT.ordinal() ||
+ returnValues.size() < node.getChildren().size()) {
+ return Boolean.FALSE;
+ }
+ for (Boolean returnValue : returnValues) {
+ if (!returnValue) {
+ return Boolean.FALSE;
+ }
+ }
+ return Boolean.TRUE;
+ }
+
+ @Override
+ public Boolean visit(RowKeyColumnExpression node) {
+ return scanRanges.hasEqualityConstraint(node.getPosition());
+ }
+
+ @Override
+ public Boolean visit(LiteralExpression node) {
+ return Boolean.TRUE;
+ }
+
+ @Override
+ public Boolean visit(ProjectedColumnExpression node) {
+ if (projector == null) {
+ return super.visit(node);
+ }
+ Expression expression = projector.getExpressions()[node.getPosition()];
+ // Only look one level down the projection.
+ if (expression instanceof ProjectedColumnExpression) {
+ return super.visit(node);
+ }
+ return expression.accept(this);
+ }
+ }
+ /**
+ *
+ * Visitor used to determine if order is preserved across a list of expressions (GROUP BY or ORDER BY expressions)
+ *
+ */
private static class TrackOrderPreservingExpressionVisitor extends StatelessTraverseNoExpressionVisitor<Info> {
private final TupleProjector projector;
http://git-wip-us.apache.org/repos/asf/phoenix/blob/95307cd9/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 b77fd75..c154d73 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
@@ -2468,6 +2468,107 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
}
@Test
+ public void testOrderPreservingGroupBy() throws Exception {
+ try (Connection conn= DriverManager.getConnection(getUrl())) {
+
+ conn.createStatement().execute("CREATE TABLE test (\n" +
+ " pk1 INTEGER NOT NULL,\n" +
+ " pk2 INTEGER NOT NULL,\n" +
+ " pk3 INTEGER NOT NULL,\n" +
+ " pk4 INTEGER NOT NULL,\n" +
+ " v1 INTEGER,\n" +
+ " CONSTRAINT pk PRIMARY KEY (\n" +
+ " pk1,\n" +
+ " pk2,\n" +
+ " pk3,\n" +
+ " pk4\n" +
+ " )\n" +
+ " )");
+ String[] queries = new String[] {
+ "SELECT pk3 FROM test WHERE pk2 = 1 GROUP BY pk2+1,pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk2 = 1 GROUP BY pk2,pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY pk1+pk2,pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY pk4,CASE WHEN pk1 > pk2 THEN pk1 ELSE pk2 END,pk3 ORDER BY pk4,pk3",
+ };
+ int index = 0;
+ for (String query : queries) {
+ QueryPlan plan = getQueryPlan(conn, query);
+ assertTrue((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty());
+ index++;
+ }
+ }
+ }
+
+ @Test
+ public void testNotOrderPreservingGroupBy() throws Exception {
+ try (Connection conn= DriverManager.getConnection(getUrl())) {
+
+ conn.createStatement().execute("CREATE TABLE test (\n" +
+ " pk1 INTEGER NOT NULL,\n" +
+ " pk2 INTEGER NOT NULL,\n" +
+ " pk3 INTEGER NOT NULL,\n" +
+ " pk4 INTEGER NOT NULL,\n" +
+ " v1 INTEGER,\n" +
+ " CONSTRAINT pk PRIMARY KEY (\n" +
+ " pk1,\n" +
+ " pk2,\n" +
+ " pk3,\n" +
+ " pk4\n" +
+ " )\n" +
+ " )");
+ String[] queries = new String[] {
+ "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY pk4,CASE WHEN pk1 > pk2 THEN coalesce(v1,1) ELSE pk2 END,pk3 ORDER BY pk4,pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY CASE WHEN pk1 > pk2 THEN v1 WHEN pk1 = pk2 THEN pk1 ELSE pk2 END,pk3 ORDER BY CASE WHEN pk1 > pk2 THEN v1 WHEN pk1 = pk2 THEN pk1 ELSE pk2 END,pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY CASE WHEN pk1 > pk2 THEN v1 WHEN pk1 = pk2 THEN pk1 ELSE pk2 END,pk3 ORDER BY CASE WHEN pk1 > pk2 THEN v1 WHEN pk1 = pk2 THEN pk1 ELSE pk2 END,pk3",
+ "SELECT pk3 FROM test GROUP BY pk2,pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 1 GROUP BY pk1,pk2,pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 1 GROUP BY RAND()+pk1,pk2,pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY CASE WHEN pk1 > pk2 THEN pk1 ELSE RAND(1) END,pk3 ORDER BY pk3",
+ };
+ int index = 0;
+ for (String query : queries) {
+ QueryPlan plan = getQueryPlan(conn, query);
+ assertFalse((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty());
+ index++;
+ }
+ }
+ }
+
+ @Test
+ public void testGroupByDescColumnBug3451() throws Exception {
+
+ try (Connection conn= DriverManager.getConnection(getUrl())) {
+
+ conn.createStatement().execute("CREATE TABLE IF NOT EXISTS GROUPBYTEST (\n" +
+ " ORGANIZATION_ID CHAR(15) NOT NULL,\n" +
+ " CONTAINER_ID CHAR(15) NOT NULL,\n" +
+ " ENTITY_ID CHAR(15) NOT NULL,\n" +
+ " SCORE DOUBLE,\n" +
+ " CONSTRAINT TEST_PK PRIMARY KEY (\n" +
+ " ORGANIZATION_ID,\n" +
+ " CONTAINER_ID,\n" +
+ " ENTITY_ID\n" +
+ " )\n" +
+ " )");
+ conn.createStatement().execute("CREATE INDEX SCORE_IDX ON GROUPBYTEST (ORGANIZATION_ID,CONTAINER_ID, SCORE DESC, ENTITY_ID DESC)");
+ QueryPlan plan = getQueryPlan(conn, "SELECT DISTINCT entity_id, score\n" +
+ " FROM GROUPBYTEST\n" +
+ " WHERE organization_id = 'org2'\n" +
+ " AND container_id IN ( 'container1','container2','container3' )\n" +
+ " ORDER BY score DESC\n" +
+ " LIMIT 2");
+ assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty());
+ plan = getQueryPlan(conn, "SELECT DISTINCT entity_id, score\n" +
+ " FROM GROUPBYTEST\n" +
+ " WHERE entity_id = 'entity1'\n" +
+ " AND container_id IN ( 'container1','container2','container3' )\n" +
+ " ORDER BY score DESC\n" +
+ " LIMIT 2");
+ assertTrue(plan.getOrderBy().getOrderByExpressions().isEmpty());
+ }
+ }
+
+ @Test
public void testGroupByDescColumnBug3452() throws Exception {
Connection conn=null;
[2/3] phoenix git commit: PHOENIX-3469 Incorrect sort order for DESC
primary key for NULLS LAST/NULLS FIRST (chenglei)
Posted by ja...@apache.org.
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/56562ead
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/56562ead
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/56562ead
Branch: refs/heads/4.8-HBase-1.2
Commit: 56562ead9abab657539848fcfc2126c7eacb02ec
Parents: 1cd94b2
Author: James Taylor <ja...@apache.org>
Authored: Mon Nov 14 16:46:04 2016 -0800
Committer: James Taylor <ja...@apache.org>
Committed: Wed Nov 16 22:02:45 2016 -0800
----------------------------------------------------------------------
.../apache/phoenix/end2end/GroupByCaseIT.java | 6 +-
.../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 ++++++++++++++++++-
6 files changed, 507 insertions(+), 7 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/56562ead/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 7c202ef..f26d8dd 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
@@ -452,7 +452,7 @@ public class GroupByCaseIT extends BaseHBaseManagedTimeIT {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
conn = DriverManager.getConnection(getUrl(), props);
- String tableName=generateUniqueName();
+ String tableName=generateRandomString();
conn.createStatement().execute("DROP TABLE if exists "+tableName);
String sql="CREATE TABLE "+ tableName +" ( "+
"ORGANIZATION_ID CHAR(15) NOT NULL,"+
@@ -466,7 +466,7 @@ public class GroupByCaseIT extends BaseHBaseManagedTimeIT {
")) "+options;
conn.createStatement().execute(sql);
- String indexTableName=generateUniqueName();
+ String indexTableName=generateRandomString();
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)");
@@ -520,7 +520,7 @@ public class GroupByCaseIT extends BaseHBaseManagedTimeIT {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
conn = DriverManager.getConnection(getUrl(), props);
- String tableName=generateUniqueName();
+ String tableName=generateRandomString();
conn.createStatement().execute("DROP TABLE if exists "+tableName);
String sql="CREATE TABLE "+tableName+" ( "+
"ORGANIZATION_ID VARCHAR,"+
http://git-wip-us.apache.org/repos/asf/phoenix/blob/56562ead/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 2c880e7..c38411a 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
@@ -536,4 +536,237 @@ public class OrderByIT extends BaseHBaseManagedTimeIT {
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=generateRandomString();
+ 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/56562ead/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/56562ead/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/56562ead/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 b1b19a4..8aed2d0 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/56562ead/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 3e43965..b77fd75 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
@@ -2474,7 +2474,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,"+
@@ -2779,6 +2778,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);