phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From pbo...@apache.org
Subject [04/28] phoenix git commit: PHOENIX-4981 Add tests for ORDER BY, GROUP BY and salted tables using phoenix-spark
Date Tue, 27 Nov 2018 15:18:14 GMT
http://git-wip-us.apache.org/repos/asf/phoenix/blob/678563f5/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseOrderByIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseOrderByIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseOrderByIT.java
new file mode 100644
index 0000000..31bf050
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseOrderByIT.java
@@ -0,0 +1,940 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.ROW1;
+import static org.apache.phoenix.util.TestUtil.ROW2;
+import static org.apache.phoenix.util.TestUtil.ROW3;
+import static org.apache.phoenix.util.TestUtil.ROW4;
+import static org.apache.phoenix.util.TestUtil.ROW5;
+import static org.apache.phoenix.util.TestUtil.ROW6;
+import static org.apache.phoenix.util.TestUtil.ROW7;
+import static org.apache.phoenix.util.TestUtil.ROW8;
+import static org.apache.phoenix.util.TestUtil.ROW9;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.apache.phoenix.util.TestUtil.assertResultSet;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Properties;
+
+import com.google.common.collect.Lists;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryBuilder;
+import org.junit.Test;
+
+
+public abstract class BaseOrderByIT extends ParallelStatsDisabledIT {
+
+    @Test
+    public void testMultiOrderByExpr() throws Exception {
+        String tenantId = getOrganizationId();
+        String tableName = initATableValues(tenantId, getDefaultSplits(tenantId), getUrl());
+        QueryBuilder queryBuilder = new QueryBuilder()
+            .setSelectColumns(
+                    Lists.newArrayList("ENTITY_ID", "B_STRING"))
+            .setFullTableName(tableName)
+            .setOrderByClause("B_STRING, ENTITY_ID");
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+            ResultSet rs = executeQuery(conn, queryBuilder);
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW4,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW7,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW3,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW6,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW9,rs.getString(1));
+
+            assertFalse(rs.next());
+        }
+    }
+
+
+    @Test
+    public void testDescMultiOrderByExpr() throws Exception {
+        String tenantId = getOrganizationId();
+        String tableName = initATableValues(tenantId, getDefaultSplits(tenantId), getUrl());
+        QueryBuilder queryBuilder = new QueryBuilder()
+            .setSelectColumns(
+                    Lists.newArrayList("ENTITY_ID", "B_STRING"))
+            .setFullTableName(tableName)
+            .setOrderByClause("B_STRING || ENTITY_ID DESC");
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+            ResultSet rs = executeQuery(conn, queryBuilder);
+            assertTrue (rs.next());
+            assertEquals(ROW9,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW6,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW3,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW7,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW4,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+
+            assertFalse(rs.next());
+        }
+    }
+
+    @Test
+    public void testOrderByDifferentColumns() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+            conn.setAutoCommit(false);
+            String tableName = generateUniqueName();
+            String ddl = "CREATE TABLE " + tableName +
+                    "  (a_string varchar not null, col1 integer" +
+                    "  CONSTRAINT pk PRIMARY KEY (a_string))\n";
+            createTestTable(getUrl(), ddl);
+
+            String dml = "UPSERT INTO " + tableName + " VALUES(?, ?)";
+            PreparedStatement stmt = conn.prepareStatement(dml);
+            stmt.setString(1, "a");
+            stmt.setInt(2, 40);
+            stmt.execute();
+            stmt.setString(1, "b");
+            stmt.setInt(2, 20);
+            stmt.execute();
+            stmt.setString(1, "c");
+            stmt.setInt(2, 30);
+            stmt.execute();
+            conn.commit();
+
+            QueryBuilder queryBuilder = new QueryBuilder()
+                .setSelectColumns(
+                        Lists.newArrayList("COL1"))
+                .setFullTableName(tableName)
+                .setSelectExpression("count(*)")
+                .setGroupByClause("COL1")
+                .setOrderByClause("COL1");
+            ResultSet rs = executeQuery(conn, queryBuilder);
+            assertTrue(rs.next());
+            assertEquals(1,rs.getLong(1));
+            assertTrue(rs.next());
+            assertEquals(1,rs.getLong(1));
+            assertTrue(rs.next());
+            assertEquals(1,rs.getLong(1));
+            assertFalse(rs.next());
+
+            queryBuilder = new QueryBuilder();
+            queryBuilder.setSelectColumns(
+                    Lists.newArrayList("A_STRING",  "COL1"));
+            queryBuilder.setFullTableName(tableName);
+            queryBuilder.setOrderByClause("A_STRING");
+            rs = executeQuery(conn, queryBuilder);
+            assertTrue(rs.next());
+            assertEquals("a",rs.getString(1));
+            assertEquals(40,rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals("b",rs.getString(1));
+            assertEquals(20,rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals("c",rs.getString(1));  
+            assertEquals(30,rs.getInt(2));
+            assertFalse(rs.next());  
+
+            queryBuilder.setSelectColumns(
+                    Lists.newArrayList("A_STRING", "COL1"));
+            queryBuilder.setFullTableName(tableName);
+            queryBuilder.setOrderByClause("COL1");
+            rs = executeQuery(conn, queryBuilder);
+            assertTrue(rs.next());
+            assertEquals("b",rs.getString(1));
+            assertEquals(20,rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals("c",rs.getString(1));  
+            assertEquals(30,rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals("a",rs.getString(1));
+            assertEquals(40,rs.getInt(2));
+            assertFalse(rs.next());  
+        }
+    }
+
+    @Test
+    public void testAggregateOrderBy() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        String tableName = generateUniqueName();
+        String ddl = "create table " + tableName + " (ID VARCHAR NOT NULL PRIMARY KEY, VAL1 VARCHAR, VAL2 INTEGER)";
+        conn.createStatement().execute(ddl);
+
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABC','aa123', 11)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABD','ba124', 1)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABE','cf125', 13)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABF','dan126', 4)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABG','elf127', 15)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABH','fan128', 6)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAA','get211', 100)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAB','hat212', 7)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAC','aap12', 2)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAD','ball12', 3)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAE','inn2110', 13)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAF','key2112', 40)");
+        conn.commit();
+
+        QueryBuilder queryBuilder = new QueryBuilder()
+            .setSelectColumns(
+                    Lists.newArrayList("VAL1", "VAL2"))
+            .setFullTableName(tableName)
+            .setOrderByClause("VAL1")
+            .setSelectExpression("DISTINCT(ID)")
+            .setSelectExpressionColumns(Lists.newArrayList("ID"))
+            .setWhereClause(
+                "ID in ('ABC','ABD','ABE','ABF','ABG','ABH','AAA', 'AAB', 'AAC','AAD','AAE','AAF')");
+        ResultSet rs = executeQuery(conn, queryBuilder);
+        assertTrue(rs.next());
+        assertEquals("ABC", rs.getString(1));
+        assertEquals("aa123", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("aap12", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("ba124", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("ball12", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("cf125", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("dan126", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("elf127", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("fan128", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("get211", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("hat212", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("inn2110", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("AAF", rs.getString(1));
+        assertEquals("key2112", rs.getString(2));
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testAggregateOptimizedOutOrderBy() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        String tableName = generateUniqueName();
+        String ddl = "create table " + tableName + " (K1 VARCHAR NOT NULL, K2 VARCHAR NOT NULL, VAL1 VARCHAR, VAL2 INTEGER, CONSTRAINT pk PRIMARY KEY(K1,K2))";
+        conn.createStatement().execute(ddl);
+
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABC','ABC','aa123', 11)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABD','ABC','ba124', 1)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABE','ABC','cf125', 13)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABF','ABC','dan126', 4)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABG','ABC','elf127', 15)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('ABH','ABC','fan128', 6)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAA','ABC','get211', 100)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAB','ABC','hat212', 7)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAC','ABC','aap12', 2)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAD','ABC','ball12', 3)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAE','ABC','inn2110', 13)");
+        conn.createStatement().execute("upsert into " + tableName + " values ('AAF','ABC','key2112', 40)");
+        conn.commit();
+
+        QueryBuilder queryBuilder = new QueryBuilder()
+            .setSelectColumns(
+                    Lists.newArrayList("VAL1", "VAL2"))
+            .setFullTableName(tableName)
+            .setOrderByClause("VAL1")
+            .setSelectExpressionColumns(Lists.newArrayList("K2"))
+            .setSelectExpression("DISTINCT(K2)")
+        .setWhereClause("K2 = 'ABC'");
+
+        // verify that the phoenix query plan doesn't contain an order by
+        String expectedPhoenixPlan = "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + tableName +"\n" +
+                "    SERVER FILTER BY K2 = 'ABC'\n" +
+                "    SERVER AGGREGATE INTO DISTINCT ROWS BY [K2, VAL1, VAL2]\n" +
+                "CLIENT MERGE SORT";
+        validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null);
+
+        ResultSet rs = executeQuery(conn, queryBuilder);
+        assertTrue(rs.next());
+        assertEquals("ABC", rs.getString(1));
+        assertEquals("aa123", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("aap12", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("ba124", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("ball12", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("cf125", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("dan126", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("elf127", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("fan128", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("get211", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("hat212", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("inn2110", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals("ABC", rs.getString(1));
+        assertEquals("key2112", rs.getString(2));
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testNullsLastWithDesc() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+            String tableName=generateUniqueName();
+            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";
+            QueryBuilder queryBuilder = new QueryBuilder()
+                .setSelectColumns(
+                        Lists.newArrayList("CONTAINER_ID", "ORGANIZATION_ID"))
+                .setFullTableName(tableName)
+                .setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST");
+            ResultSet rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            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";
+            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"},{null,null}});
+        }
+    }
+
+    @Test
+    public void testOrderByReverseOptimization() throws Exception {
+        for(boolean salted: new boolean[]{true,false}) {
+            doTestOrderByReverseOptimization(salted,true,true,true);
+            doTestOrderByReverseOptimization(salted,true,true,false);
+            doTestOrderByReverseOptimization(salted,true,false,true);
+            doTestOrderByReverseOptimization(salted,true,false,false);
+            doTestOrderByReverseOptimization(salted,false,true,true);
+            doTestOrderByReverseOptimization(salted,false,true,false);
+            doTestOrderByReverseOptimization(salted,false,false,true);
+            doTestOrderByReverseOptimization(salted,false,false,false);
+        }
+    }
+
+    private void doTestOrderByReverseOptimization(boolean salted,boolean desc1,boolean desc2,boolean desc3) throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+            String tableName=generateUniqueName();
+            String sql="CREATE TABLE "+tableName+" ( "+
+                    "ORGANIZATION_ID INTEGER NOT NULL,"+
+                    "CONTAINER_ID INTEGER NOT NULL,"+
+                    "SCORE INTEGER NOT NULL,"+
+                    "ENTITY_ID INTEGER NOT NULL,"+
+                    "CONSTRAINT TEST_PK PRIMARY KEY ( "+
+                    "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
+                    "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
+                    "SCORE"+(desc3 ? " DESC" : "" )+","+
+                    "ENTITY_ID"+
+                    ")) "+(salted ? "SALT_BUCKETS =4" : "split on(4)");
+            conn.createStatement().execute(sql);
+
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,1,1,1)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,2,2,2)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,3,3,3)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (4,4,4,4)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (5,5,5,5)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (6,6,6,6)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,1,1,11)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,2,2,22)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,3,3,33)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (4,4,4,44)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (5,5,5,55)");
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (6,6,6,66)");
+            conn.commit();
+
+            QueryBuilder queryBuilder = new QueryBuilder()
+                .setSelectColumns(
+                        Lists.newArrayList("CONTAINER_ID", "ORGANIZATION_ID"))
+                .setFullTableName(tableName)
+                .setGroupByClause("ORGANIZATION_ID, CONTAINER_ID")
+            .setOrderByClause(  "ORGANIZATION_ID ASC, CONTAINER_ID ASC");
+            //groupBy orderPreserving orderBy asc asc
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID ASC";
+            ResultSet rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+            //groupBy orderPreserving orderBy asc desc
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID desc";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC, CONTAINER_ID DESC");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+            //groupBy orderPreserving orderBy desc asc
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID ASC";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC, CONTAINER_ID ASC");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+
+            //groupBy orderPreserving orderBy desc desc
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID DESC";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC, CONTAINER_ID DESC");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+
+            //groupBy not orderPreserving orderBy asc asc
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE ASC";
+            queryBuilder.setSelectColumns(
+                    Lists.newArrayList("ORGANIZATION_ID", "SCORE"));
+            queryBuilder.setGroupByClause("ORGANIZATION_ID, SCORE");
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC, SCORE ASC");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+            //groupBy not orderPreserving orderBy asc desc
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE DESC";
+            queryBuilder.setGroupByClause("ORGANIZATION_ID, SCORE");
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC, SCORE DESC");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
+
+            //groupBy not orderPreserving orderBy desc asc
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE ASC";
+            queryBuilder.setGroupByClause("ORGANIZATION_ID, SCORE");
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC, SCORE ASC");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+
+            //groupBy not orderPreserving orderBy desc desc
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC";
+            queryBuilder.setGroupByClause("ORGANIZATION_ID, SCORE");
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC, SCORE DESC");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
+        }
+    }
+
+    @Test
+    public void testOrderByReverseOptimizationWithNullsLast() throws Exception{
+        for(boolean salted: new boolean[]{true,false}) {
+            doTestOrderByReverseOptimizationWithNullsLast(salted,true,true,true);
+            doTestOrderByReverseOptimizationWithNullsLast(salted,true,true,false);
+            doTestOrderByReverseOptimizationWithNullsLast(salted,true,false,true);
+            doTestOrderByReverseOptimizationWithNullsLast(salted,true,false,false);
+            doTestOrderByReverseOptimizationWithNullsLast(salted,false,true,true);
+            doTestOrderByReverseOptimizationWithNullsLast(salted,false,true,false);
+            doTestOrderByReverseOptimizationWithNullsLast(salted,false,false,true);
+            doTestOrderByReverseOptimizationWithNullsLast(salted,false,false,false);
+        }
+    }
+
+    private void doTestOrderByReverseOptimizationWithNullsLast(boolean salted,boolean desc1,boolean desc2,boolean desc3) throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+            String tableName=generateUniqueName();
+            String sql="CREATE TABLE "+tableName+" ( "+
+                    "ORGANIZATION_ID VARCHAR,"+
+                    "CONTAINER_ID VARCHAR,"+
+                    "SCORE VARCHAR,"+
+                    "ENTITY_ID VARCHAR NOT NULL,"+
+                    "CONSTRAINT TEST_PK PRIMARY KEY ( "+
+                    "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
+                    "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
+                    "SCORE"+(desc3 ? " DESC" : "" )+","+
+                    "ENTITY_ID"+
+                    ")) "+(salted ? "SALT_BUCKETS =4" : "split on('4')");
+            conn.createStatement().execute(sql);
+
+            for(int i=1;i<=6;i++) {
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'"+i+"','"+i+"','"+i+"')");
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'"+i+"',null,'"+i+"')");
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,'"+i+"','"+i+"')");
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,null,'"+i+"')");
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"','"+i+"','"+i+"','"+i+"')");
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"','"+i+"',null,'"+i+"')");
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"',null,'"+i+"','"+i+"')");
+                conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"',null,null,'"+i+"')");
+            }
+            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,null,'66')");
+            conn.commit();
+
+            //groupBy orderPreserving orderBy asc asc
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
+            QueryBuilder queryBuilder = new QueryBuilder()
+            .setSelectColumns(
+                    Lists.newArrayList("ORGANIZATION_ID", "CONTAINER_ID"))
+            .setFullTableName(tableName)
+            .setGroupByClause("ORGANIZATION_ID, CONTAINER_ID")
+            .setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST");
+            ResultSet rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+            //groupBy orderPreserving orderBy asc desc
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+            //groupBy orderPreserving orderBy desc asc
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+            //groupBy orderPreserving orderBy desc desc
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+//            sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+            //-----groupBy not orderPreserving--
+
+            //groupBy not orderPreserving orderBy asc asc
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST";
+            queryBuilder.setSelectColumns(
+                    Lists.newArrayList("ORGANIZATION_ID", "SCORE"))
+                .setFullTableName(tableName)
+                .setGroupByClause("ORGANIZATION_ID, SCORE")
+                .setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+            //groupBy not orderPreserving orderBy asc desc
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+            //groupBy not orderPreserving orderBy desc asc
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
+
+            //groupBy not orderPreserving orderBy desc desc
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
+
+//            sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST";
+            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
+
+            //-------test only one return column----------------------------------
+
+//            sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS FIRST";
+            queryBuilder.setSelectColumns(
+                    Lists.newArrayList("SCORE"))
+                .setFullTableName(tableName)
+                .setGroupByClause("SCORE")
+                .setOrderByClause("SCORE ASC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null},{"1"},{"2"},{"3"},{"4"},{"5"},{"6"}});
+
+//            sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS LAST";
+            queryBuilder.setOrderByClause("SCORE ASC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"1"},{"2"},{"3"},{"4"},{"5"},{"6"},{null}});
+
+//            sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS FIRST";
+            queryBuilder.setOrderByClause("SCORE DESC NULLS FIRST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{null},{"6"},{"5"},{"4"},{"3"},{"2"},{"1"}});
+
+//            sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS LAST";
+            queryBuilder.setOrderByClause("SCORE DESC NULLS LAST");
+            rs = executeQuery(conn, queryBuilder);
+            assertResultSet(rs, new Object[][]{{"6"},{"5"},{"4"},{"3"},{"2"},{"1"},{null}});
+        }
+    }
+
+}
\ No newline at end of file


Mime
View raw message