phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tdsi...@apache.org
Subject [6/6] phoenix git commit: PHOENIX-4981 Add tests for ORDER BY, GROUP BY and salted tables using phoenix-spark
Date Tue, 06 Nov 2018 23:14:36 GMT
PHOENIX-4981 Add tests for ORDER BY, GROUP BY and salted tables using phoenix-spark


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/51d38d7f
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/51d38d7f
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/51d38d7f

Branch: refs/heads/4.x-HBase-1.4
Commit: 51d38d7fc1978468670053c980a4a3a866f52287
Parents: 8ccf69f
Author: Thomas D'Silva <tdsilva@apache.org>
Authored: Thu Oct 18 22:00:01 2018 -0700
Committer: Thomas D'Silva <tdsilva@apache.org>
Committed: Tue Nov 6 14:52:26 2018 -0800

----------------------------------------------------------------------
 .../org/apache/phoenix/end2end/AggregateIT.java |  987 +---------------
 .../apache/phoenix/end2end/BaseAggregateIT.java | 1022 +++++++++++++++++
 .../apache/phoenix/end2end/BaseOrderByIT.java   |  940 ++++++++++++++++
 .../org/apache/phoenix/end2end/OrderByIT.java   |  943 ++--------------
 .../end2end/ParallelStatsDisabledIT.java        |   40 +
 .../end2end/salted/BaseSaltedTableIT.java       |  474 ++++++++
 .../phoenix/end2end/salted/SaltedTableIT.java   |  450 +-------
 .../org/apache/phoenix/util/QueryBuilder.java   |  211 ++++
 .../java/org/apache/phoenix/util/QueryUtil.java |   38 +-
 .../index/IndexScrutinyTableOutputTest.java     |    6 +-
 .../util/PhoenixConfigurationUtilTest.java      |    6 +-
 .../org/apache/phoenix/util/QueryUtilTest.java  |   10 +-
 phoenix-spark/pom.xml                           |    8 +
 .../org/apache/phoenix/spark/AggregateIT.java   |   91 ++
 .../org/apache/phoenix/spark/OrderByIT.java     |  460 ++++++++
 .../org/apache/phoenix/spark/SaltedTableIT.java |   53 +
 .../org/apache/phoenix/spark/SparkUtil.java     |   87 ++
 .../apache/phoenix/spark/PhoenixSparkIT.scala   |    9 +-
 .../apache/phoenix/spark/SparkResultSet.java    | 1056 ++++++++++++++++++
 .../org/apache/phoenix/spark/PhoenixRDD.scala   |   27 +-
 pom.xml                                         |    2 +-
 21 files changed, 4650 insertions(+), 2270 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/51d38d7f/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateIT.java
index 2059311..8916d4d 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateIT.java
@@ -18,506 +18,28 @@
 package org.apache.phoenix.end2end;
 
 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 static org.junit.Assert.fail;
-import static org.apache.phoenix.util.TestUtil.assertResultSet;
 
-import java.io.IOException;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.sql.Statement;
-import java.util.List;
 import java.util.Properties;
 
-import org.apache.hadoop.hbase.util.Bytes;
-import org.apache.phoenix.compile.QueryPlan;
-import org.apache.phoenix.jdbc.PhoenixConnection;
 import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData;
-import org.apache.phoenix.jdbc.PhoenixStatement;
-import org.apache.phoenix.query.KeyRange;
 import org.apache.phoenix.schema.AmbiguousColumnException;
-import org.apache.phoenix.schema.types.PChar;
-import org.apache.phoenix.schema.types.PInteger;
-import org.apache.phoenix.util.ByteUtil;
 import org.apache.phoenix.util.PropertiesUtil;
-import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.QueryBuilder;
 import org.apache.phoenix.util.TestUtil;
 import org.junit.Test;
 
+public class AggregateIT extends BaseAggregateIT {
 
-public class AggregateIT extends ParallelStatsDisabledIT {
-    private static void initData(Connection conn, String tableName) throws SQLException {
-        conn.createStatement().execute("create table " + tableName +
-                "   (id varchar not null primary key,\n" +
-                "    uri varchar, appcpu integer)");
-        insertRow(conn, tableName, "Report1", 10, 1);
-        insertRow(conn, tableName, "Report2", 10, 2);
-        insertRow(conn, tableName, "Report3", 30, 3);
-        insertRow(conn, tableName, "Report4", 30, 4);
-        insertRow(conn, tableName, "SOQL1", 10, 5);
-        insertRow(conn, tableName, "SOQL2", 10, 6);
-        insertRow(conn, tableName, "SOQL3", 30, 7);
-        insertRow(conn, tableName, "SOQL4", 30, 8);
-        conn.commit();
-    }
-
-    private static void insertRow(Connection conn, String tableName, String uri, int appcpu, int id) throws SQLException {
-        PreparedStatement statement = conn.prepareStatement("UPSERT INTO " + tableName + "(id, uri, appcpu) values (?,?,?)");
-        statement.setString(1, "id" + id);
-        statement.setString(2, uri);
-        statement.setInt(3, appcpu);
-        statement.executeUpdate();
-    }
-
-    @Test
-    public void testDuplicateTrailingAggExpr() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        conn.createStatement().execute("create table " + tableName +
-                "   (nam VARCHAR(20), address VARCHAR(20), id BIGINT "
-                + "constraint my_pk primary key (id))");
-        PreparedStatement statement = conn.prepareStatement("UPSERT INTO " + tableName + "(nam, address, id) values (?,?,?)");
-        statement.setString(1, "pulkit");
-        statement.setString(2, "badaun");
-        statement.setInt(3, 1);
-        statement.executeUpdate();
-        conn.commit();
-        Statement stmt = conn.createStatement();
-        ResultSet rs = stmt.executeQuery("select distinct 'harshit' as \"test_column\", trim(nam), trim(nam) from " + tableName);
-        assertTrue(rs.next());
-        assertEquals("harshit", rs.getString(1));
-        assertEquals("pulkit", rs.getString(2));
-        assertEquals("pulkit", rs.getString(3));
-        conn.close();
-    }
-
-    @Test
-    public void testExpressionInGroupBy() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        String ddl = " create table " + tableName + "(tgb_id integer NOT NULL,utc_date_epoch integer NOT NULL,tgb_name varchar(40),ack_success_count integer" +
-                ",ack_success_one_ack_count integer, CONSTRAINT pk_tgb_counter PRIMARY KEY(tgb_id, utc_date_epoch))";
-        String query = "SELECT tgb_id, tgb_name, (utc_date_epoch/10)*10 AS utc_epoch_hour,SUM(ack_success_count + ack_success_one_ack_count) AS ack_tx_sum" +
-                " FROM " + tableName + " GROUP BY tgb_id, tgb_name, utc_epoch_hour";
-
-        createTestTable(getUrl(), ddl);
-        String dml = "UPSERT INTO " + tableName + " VALUES(?,?,?,?,?)";
-        PreparedStatement stmt = conn.prepareStatement(dml);
-        stmt.setInt(1, 1);
-        stmt.setInt(2, 1000);
-        stmt.setString(3, "aaa");
-        stmt.setInt(4, 1);
-        stmt.setInt(5, 1);
-        stmt.execute();
-        stmt.setInt(1, 2);
-        stmt.setInt(2, 2000);
-        stmt.setString(3, "bbb");
-        stmt.setInt(4, 2);
-        stmt.setInt(5, 2);
-        stmt.execute();
-        conn.commit();
-
-        ResultSet rs = conn.createStatement().executeQuery(query);
-        assertTrue(rs.next());
-        assertEquals(1,rs.getInt(1));
-        assertEquals("aaa",rs.getString(2));
-        assertEquals(1000,rs.getInt(3));
-        assertEquals(2,rs.getInt(4));
-        assertTrue(rs.next());
-        assertEquals(2,rs.getInt(1));
-        assertEquals("bbb",rs.getString(2));
-        assertEquals(2000,rs.getInt(3));
-        assertEquals(4,rs.getInt(4));
-        assertFalse(rs.next());
-        rs.close();
-        conn.close();
-    }
-    
-    @Test
-    public void testBooleanInGroupBy() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        String ddl = " create table " + tableName + "(id varchar primary key,v1 boolean, v2 integer, v3 integer)";
-
-        createTestTable(getUrl(), ddl);
-        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + "(id,v2,v3) VALUES(?,?,?)");
-        stmt.setString(1, "a");
-        stmt.setInt(2, 1);
-        stmt.setInt(3, 1);
-        stmt.execute();
-        stmt.close();
-        stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?,?,?)");
-        stmt.setString(1, "b");
-        stmt.setBoolean(2, false);
-        stmt.setInt(3, 2);
-        stmt.setInt(4, 2);
-        stmt.execute();
-        stmt.setString(1, "c");
-        stmt.setBoolean(2, true);
-        stmt.setInt(3, 3);
-        stmt.setInt(4, 3);
-        stmt.execute();
-        conn.commit();
-
-        String[] gbs = {"v1,v2,v3","v1,v3,v2","v2,v1,v3"};
-        for (String gb : gbs) {
-            ResultSet rs = conn.createStatement().executeQuery("SELECT v1, v2, v3 from " + tableName + " group by " + gb);
-            assertTrue(rs.next());
-            assertEquals(false,rs.getBoolean("v1"));
-            assertTrue(rs.wasNull());
-            assertEquals(1,rs.getInt("v2"));
-            assertEquals(1,rs.getInt("v3"));
-            assertTrue(rs.next());
-            assertEquals(false,rs.getBoolean("v1"));
-            assertFalse(rs.wasNull());
-            assertEquals(2,rs.getInt("v2"));
-            assertEquals(2,rs.getInt("v3"));
-            assertTrue(rs.next());
-            assertEquals(true,rs.getBoolean("v1"));
-            assertEquals(3,rs.getInt("v2"));
-            assertEquals(3,rs.getInt("v3"));
-            assertFalse(rs.next());
-            rs.close();
-        }
-        conn.close();
-    }
-    
-    @Test
-    public void testScanUri() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        initData(conn, tableName);
-        Statement stmt = conn.createStatement();
-        ResultSet rs = stmt.executeQuery("select uri from " + tableName);
-        assertTrue(rs.next());
-        assertEquals("Report1", rs.getString(1));
-        assertTrue(rs.next());
-        assertEquals("Report2", rs.getString(1));
-        assertTrue(rs.next());
-        assertEquals("Report3", rs.getString(1));
-        assertTrue(rs.next());
-        assertEquals("Report4", rs.getString(1));
-        assertTrue(rs.next());
-        assertEquals("SOQL1", rs.getString(1));
-        assertTrue(rs.next());
-        assertEquals("SOQL2", rs.getString(1));
-        assertTrue(rs.next());
-        assertEquals("SOQL3", rs.getString(1));
-        assertTrue(rs.next());
-        assertEquals("SOQL4", rs.getString(1));
-        assertFalse(rs.next());
-        conn.close();
-    }
-
-    @Test
-    public void testCount() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        initData(conn, tableName);
-        Statement stmt = conn.createStatement();
-        ResultSet rs = stmt.executeQuery("select count(1) from " + tableName);
-        assertTrue(rs.next());
-        assertEquals(8, rs.getInt(1));
-        assertFalse(rs.next());
-        conn.close();
-    }
-
-    @Test
-    public void testGroupByCase() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        String groupBy1 = "select " +
-                "case when uri LIKE 'Report%' then 'Reports' else 'Other' END category" +
-                ", avg(appcpu) from " + tableName +
-                " group by category";
-
-        String groupBy2 = "select " +
-                "case uri when 'Report%' then 'Reports' else 'Other' END category" +
-                ", avg(appcpu) from " + tableName +
-                " group by appcpu, category";
-        
-        String groupBy3 = "select " +
-                "case uri when 'Report%' then 'Reports' else 'Other' END category" +
-                ", avg(appcpu) from " + tableName +
-                " group by avg(appcpu), category";
-        initData(conn, tableName);
-        conn.createStatement().executeQuery(groupBy1);
-        conn.createStatement().executeQuery(groupBy2);
-        // TODO: validate query results
-        try {
-            conn.createStatement().executeQuery(groupBy3);
-            fail();
-        } catch (SQLException e) {
-            assertTrue(e.getMessage().contains("Aggregate expressions may not be used in GROUP BY"));
-        }
-        conn.close();
-    }
-
-
-    @Test
-    public void testGroupByArray() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-
-        String tableName = generateUniqueName();
-        conn.createStatement().execute("CREATE TABLE " + tableName + "(\n" + 
-                "  a VARCHAR NOT NULL,\n" + 
-                "  b VARCHAR,\n" + 
-                "  c INTEGER,\n" + 
-                "  d VARCHAR,\n" + 
-                "  e VARCHAR ARRAY,\n" + 
-                "  f BIGINT,\n" + 
-                "  g BIGINT,\n" + 
-                "  CONSTRAINT pk PRIMARY KEY(a)\n" + 
-                ")");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('1', 'val', 100, 'a', ARRAY ['b'], 1, 2)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('2', 'val', 100, 'a', ARRAY ['b'], 3, 4)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('3', 'val', 100, 'a', ARRAY ['b','c'], 5, 6)");
-        conn.commit();
-        
-        ResultSet rs = conn.createStatement().executeQuery("SELECT c, SUM(f + g) AS sumone, d, e\n" + 
-                "FROM " + tableName + "\n" + 
-                "WHERE b = 'val'\n" + 
-                "  AND a IN ('1','2','3')\n" + 
-                "GROUP BY c, d, e\n" + 
-                "ORDER BY sumone DESC");
-        assertTrue(rs.next());
-        assertEquals(100, rs.getInt(1));
-        assertEquals(11, rs.getLong(2));
-        assertTrue(rs.next());
-        assertEquals(100, rs.getInt(1));
-        assertEquals(10, rs.getLong(2));
-        assertFalse(rs.next());
-        conn.close();
-    }
-    
-    @Test
-    public void testGroupByOrderPreserving() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-
-        conn.createStatement().execute("CREATE TABLE " + tableName + "(ORGANIZATION_ID char(15) not null, \n" + 
-                "JOURNEY_ID char(15) not null, \n" + 
-                "DATASOURCE SMALLINT not null, \n" + 
-                "MATCH_STATUS TINYINT not null, \n" + 
-                "EXTERNAL_DATASOURCE_KEY varchar(30), \n" + 
-                "ENTITY_ID char(15) not null, \n" + 
-                "CONSTRAINT PK PRIMARY KEY (\n" + 
-                "    ORGANIZATION_ID, \n" + 
-                "    JOURNEY_ID, \n" + 
-                "    DATASOURCE, \n" + 
-                "    MATCH_STATUS,\n" + 
-                "    EXTERNAL_DATASOURCE_KEY,\n" + 
-                "    ENTITY_ID))");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('000001111122222', '333334444455555', 0, 0, 'abc', '666667777788888')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('000001111122222', '333334444455555', 0, 0, 'abcd', '666667777788889')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('000001111122222', '333334444455555', 0, 0, 'abc', '666667777788899')");
-        conn.commit();
-        String query =
-                "SELECT COUNT(1), EXTERNAL_DATASOURCE_KEY As DUP_COUNT\n" + 
-                "    FROM " + tableName + " \n" + 
-                "   WHERE JOURNEY_ID='333334444455555' AND \n" + 
-                "                 DATASOURCE=0 AND MATCH_STATUS <= 1 and \n" + 
-                "                 ORGANIZATION_ID='000001111122222' \n" + 
-                "    GROUP BY MATCH_STATUS, EXTERNAL_DATASOURCE_KEY \n" + 
-                "    HAVING COUNT(1) > 1";
-        ResultSet rs = conn.createStatement().executeQuery(query);
-        assertTrue(rs.next());
-        assertEquals(2,rs.getInt(1));
-        assertEquals("abc", rs.getString(2));
-        assertFalse(rs.next());
-        
-        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
-        assertEquals(
-                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " ['000001111122222','333334444455555',0,*] - ['000001111122222','333334444455555',0,1]\n" + 
-                "    SERVER FILTER BY FIRST KEY ONLY\n" + 
-                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [MATCH_STATUS, EXTERNAL_DATASOURCE_KEY]\n" + 
-                "CLIENT FILTER BY COUNT(1) > 1",QueryUtil.getExplainPlan(rs));
-    }
-    
-    @Test
-    public void testGroupByOrderPreservingDescSort() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName(); 
-        conn.createStatement().execute("CREATE TABLE " + tableName + " (k1 char(1) not null, k2 char(1) not null, constraint pk primary key (k1,k2)) split on ('ac','jc','nc')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'a')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'b')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'c')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'd')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'a')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'b')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'c')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'd')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'a')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'b')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'c')");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'd')");
-        conn.commit();
-        String query = "SELECT k1,count(*) FROM " + tableName + " GROUP BY k1 ORDER BY k1 DESC";
-        ResultSet rs = conn.createStatement().executeQuery(query);
-        assertTrue(rs.next());
-        assertEquals("n", rs.getString(1));
-        assertEquals(4, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("j", rs.getString(1));
-        assertEquals(4, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("a", rs.getString(1));
-        assertEquals(4, rs.getInt(2));
-        assertFalse(rs.next());
-        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
-        assertEquals(
-                "CLIENT PARALLEL 1-WAY REVERSE FULL SCAN OVER " + tableName + "\n" + 
-                "    SERVER FILTER BY FIRST KEY ONLY\n" + 
-                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [K1]", QueryUtil.getExplainPlan(rs));
-    }
-    
-    @Test
-    public void testSumGroupByOrderPreservingDesc() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-
-        PreparedStatement stmt = conn.prepareStatement("CREATE TABLE " + tableName + " (k1 char(1) not null, k2 integer not null, constraint pk primary key (k1,k2)) split on (?,?,?)");
-        stmt.setBytes(1, ByteUtil.concat(PChar.INSTANCE.toBytes("a"), PInteger.INSTANCE.toBytes(3)));
-        stmt.setBytes(2, ByteUtil.concat(PChar.INSTANCE.toBytes("j"), PInteger.INSTANCE.toBytes(3)));
-        stmt.setBytes(3, ByteUtil.concat(PChar.INSTANCE.toBytes("n"), PInteger.INSTANCE.toBytes(3)));
-        stmt.execute();
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 1)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 2)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 3)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 4)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('b', 5)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 1)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 2)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 3)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 4)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 1)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 2)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 3)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 4)");
-        conn.commit();
-        String query = "SELECT k1,sum(k2) FROM " + tableName + " GROUP BY k1 ORDER BY k1 DESC";
-        ResultSet rs = conn.createStatement().executeQuery(query);
-        assertTrue(rs.next());
-        assertEquals("n", rs.getString(1));
-        assertEquals(10, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("j", rs.getString(1));
-        assertEquals(10, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("b", rs.getString(1));
-        assertEquals(5, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("a", rs.getString(1));
-        assertEquals(10, rs.getInt(2));
-        assertFalse(rs.next());
-        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
-        assertEquals(
-                "CLIENT PARALLEL 1-WAY REVERSE FULL SCAN OVER " + tableName + "\n" + 
-                "    SERVER FILTER BY FIRST KEY ONLY\n" + 
-                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [K1]", QueryUtil.getExplainPlan(rs));
-    }
-
-    @Test
-    public void testAvgGroupByOrderPreservingWithStats() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        ResultSet rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + "\"SYSTEM\".\"STATS\"" + " WHERE " + PhoenixDatabaseMetaData.PHYSICAL_NAME + " ='" + tableName + "'");
-        assertTrue(rs.next());
-        assertEquals(0,rs.getInt(1));
-        initAvgGroupTable(conn, tableName, PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=20 ");
-        testAvgGroupByOrderPreserving(conn, tableName, 13);
-        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + "\"SYSTEM\".\"STATS\"" + " WHERE " + PhoenixDatabaseMetaData.PHYSICAL_NAME + " ='" + tableName + "'");
-        assertTrue(rs.next());
-        assertEquals(13,rs.getInt(1));
-        conn.setAutoCommit(true);
-        conn.createStatement().execute("DELETE FROM " + "\"SYSTEM\".\"STATS\"");
-        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + "\"SYSTEM\".\"STATS\"" + " WHERE " + PhoenixDatabaseMetaData.PHYSICAL_NAME + " ='" + tableName + "'");
-        assertTrue(rs.next());
-        assertEquals(0,rs.getInt(1));
-        TestUtil.doMajorCompaction(conn, tableName);
-        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + "\"SYSTEM\".\"STATS\"" + " WHERE " + PhoenixDatabaseMetaData.PHYSICAL_NAME + " ='" + tableName + "'");
-        assertTrue(rs.next());
-        assertEquals(13,rs.getInt(1));
-        testAvgGroupByOrderPreserving(conn, tableName, 13);
-        conn.createStatement().execute("ALTER TABLE " + tableName + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=100");
-        testAvgGroupByOrderPreserving(conn, tableName, 6);
-        conn.createStatement().execute("ALTER TABLE " + tableName + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=null");
-        testAvgGroupByOrderPreserving(conn, tableName, 4);
-    }
-    
-    @Test
-    public void testAvgGroupByOrderPreservingWithNoStats() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        String tableName = generateUniqueName();
-        initAvgGroupTable(conn, tableName, "");
-        testAvgGroupByOrderPreserving(conn, tableName, 4);
-    }
-    
-    private void initAvgGroupTable(Connection conn, String tableName, String tableProps) throws SQLException {
-        PreparedStatement stmt = conn.prepareStatement("CREATE TABLE " + tableName + " (k1 char(1) not null, k2 integer not null, constraint pk primary key (k1,k2)) " + tableProps + " split on (?,?,?)");
-        stmt.setBytes(1, ByteUtil.concat(PChar.INSTANCE.toBytes("a"), PInteger.INSTANCE.toBytes(3)));
-        stmt.setBytes(2, ByteUtil.concat(PChar.INSTANCE.toBytes("j"), PInteger.INSTANCE.toBytes(3)));
-        stmt.setBytes(3, ByteUtil.concat(PChar.INSTANCE.toBytes("n"), PInteger.INSTANCE.toBytes(3)));
-        stmt.execute();
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 1)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 2)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 3)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 6)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('b', 5)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 1)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 2)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 3)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 10)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 1)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 2)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 3)");
-        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 2)");
-        conn.commit();
-    }
-    
-    private void testAvgGroupByOrderPreserving(Connection conn, String tableName, int nGuidePosts) throws SQLException, IOException {
-        String query = "SELECT k1,avg(k2) FROM " + tableName + " GROUP BY k1";
-        ResultSet rs = conn.createStatement().executeQuery(query);
-        assertTrue(rs.next());
-        assertEquals("a", rs.getString(1));
-        assertEquals(3, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("b", rs.getString(1));
-        assertEquals(5, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("j", rs.getString(1));
-        assertEquals(4, rs.getInt(2));
-        assertTrue(rs.next());
-        assertEquals("n", rs.getString(1));
-        assertEquals(2, rs.getInt(2));
-        assertFalse(rs.next());
-        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
-        assertEquals(
-                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + tableName + "\n" + 
-                "    SERVER FILTER BY FIRST KEY ONLY\n" + 
-                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [K1]", QueryUtil.getExplainPlan(rs));
-        TestUtil.analyzeTable(conn, tableName);
-        List<KeyRange> splits = TestUtil.getAllSplits(conn, tableName);
-        assertEquals(nGuidePosts, splits.size());
-    }
-    
     @Test
     public void testGroupByWithAliasWithSameColumnName() throws SQLException {
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
@@ -557,344 +79,6 @@ public class AggregateIT extends ParallelStatsDisabledIT {
     }
 
     @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 testGroupByOrderByDescBug3451() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
-            String tableName=generateUniqueName();
-            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=generateUniqueName();
-            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;
-        try
-        {
-            Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-            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+" 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();
-            }
-        }
-    }
-
-    @Test
     public void testGroupByCoerceExpressionBug3453() throws Exception {
         final Connection conn = DriverManager.getConnection(getUrl());
         try {
@@ -955,16 +139,6 @@ public class AggregateIT extends ParallelStatsDisabledIT {
     }
 
     @Test
-    public void testCountNullInEncodedNonEmptyKeyValueCF() throws Exception {
-        testCountNullInNonEmptyKeyValueCF(1);
-    }
-    
-    @Test
-    public void testCountNullInNonEncodedNonEmptyKeyValueCF() throws Exception {
-        testCountNullInNonEmptyKeyValueCF(0);
-    }
-
-    @Test
     public void testNestedGroupedAggregationWithBigInt() throws Exception {
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
         String tableName = generateUniqueName();
@@ -983,7 +157,41 @@ public class AggregateIT extends ParallelStatsDisabledIT {
         }
     }
 
-    private void testCountNullInNonEmptyKeyValueCF(int columnEncodedBytes) throws Exception {
+    @Test
+    public void testAvgGroupByOrderPreservingWithStats() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        String tableName = generateUniqueName();
+        QueryBuilder queryBuilder = new QueryBuilder()
+            .setSelectExpression("COUNT(*)")
+            .setFullTableName(PhoenixDatabaseMetaData.SYSTEM_STATS_NAME)
+            .setWhereClause(PhoenixDatabaseMetaData.PHYSICAL_NAME + " ='" + tableName + "'");
+        ResultSet rs = executeQuery(conn, queryBuilder);
+        assertTrue(rs.next());
+        assertEquals(0,rs.getInt(1));
+        initAvgGroupTable(conn, tableName, PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=20 ");
+        testAvgGroupByOrderPreserving(conn, tableName, 13);
+        rs = executeQuery(conn, queryBuilder);
+        assertTrue(rs.next());
+        assertEquals(13,rs.getInt(1));
+        conn.setAutoCommit(true);
+        conn.createStatement().execute("DELETE FROM " + "\"SYSTEM\".\"STATS\"");
+        rs = executeQuery(conn, queryBuilder);
+        assertTrue(rs.next());
+        assertEquals(0,rs.getInt(1));
+        TestUtil.doMajorCompaction(conn, tableName);
+        rs = executeQuery(conn, queryBuilder);
+        assertTrue(rs.next());
+        assertEquals(13,rs.getInt(1));
+        testAvgGroupByOrderPreserving(conn, tableName, 13);
+        conn.createStatement().execute("ALTER TABLE " + tableName + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=100");
+        testAvgGroupByOrderPreserving(conn, tableName, 6);
+        conn.createStatement().execute("ALTER TABLE " + tableName + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=null");
+        testAvgGroupByOrderPreserving(conn, tableName, 4);
+    }
+
+    @Override
+    protected void testCountNullInNonEmptyKeyValueCF(int columnEncodedBytes) throws Exception {
         try (Connection conn = DriverManager.getConnection(getUrl())) {
             //Type is INT
             String intTableName=generateUniqueName();
@@ -998,119 +206,26 @@ public class AggregateIT extends ParallelStatsDisabledIT {
             conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (5,1)");
             conn.commit();
 
-            TestUtil.dumpTable(conn.unwrap(PhoenixConnection.class).getQueryServices().getTable(Bytes.toBytes(intTableName)));
-
             sql="select count(*) from "+intTableName;
-            ResultSet rs=conn.createStatement().executeQuery(sql);
+            QueryBuilder queryBuilder = new QueryBuilder()
+                .setSelectExpression("COUNT(*)")
+                .setFullTableName(intTableName);
+            ResultSet rs = executeQuery(conn, queryBuilder);
             assertTrue(rs.next());
-            assertEquals(5, rs.getInt(1));
-            
+            assertEquals(5, rs.getLong(1));
+
             sql="select count(*) from "+intTableName + " where b.colb is not null";
-            rs=conn.createStatement().executeQuery(sql);
+            queryBuilder.setWhereClause("B.COLB IS NOT NULL");
+            rs = executeQuery(conn, queryBuilder);
             assertTrue(rs.next());
-            assertEquals(1, rs.getInt(1));
+            assertEquals(1, rs.getLong(1));
 
             sql="select count(*) from "+intTableName + " where b.colb is null";
-            rs=conn.createStatement().executeQuery(sql);
+            queryBuilder.setWhereClause("B.COLB IS NULL");
+            rs = executeQuery(conn, queryBuilder);
             assertTrue(rs.next());
-            assertEquals(4, rs.getInt(1));
-        }
-    }
-
-    @Test
-    public void testGroupByOrderMatchPkColumnOrderBug4690() throws Exception {
-        this.doTestGroupByOrderMatchPkColumnOrderBug4690(false, false);
-        this.doTestGroupByOrderMatchPkColumnOrderBug4690(false, true);
-        this.doTestGroupByOrderMatchPkColumnOrderBug4690(true, false);
-        this.doTestGroupByOrderMatchPkColumnOrderBug4690(true, true);
-    }
-
-    private void doTestGroupByOrderMatchPkColumnOrderBug4690(boolean desc ,boolean salted) throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = null;
-        try {
-            conn = DriverManager.getConnection(getUrl(), props);
-            String tableName = generateUniqueName();
-            String sql = "create table " + tableName + "( "+
-                    " pk1 integer not null , " +
-                    " pk2 integer not null, " +
-                    " pk3 integer not null," +
-                    " pk4 integer not null,"+
-                    " v integer, " +
-                    " CONSTRAINT TEST_PK PRIMARY KEY ( "+
-                       "pk1 "+(desc ? "desc" : "")+", "+
-                       "pk2 "+(desc ? "desc" : "")+", "+
-                       "pk3 "+(desc ? "desc" : "")+", "+
-                       "pk4 "+(desc ? "desc" : "")+
-                    " )) "+(salted ? "SALT_BUCKETS =4" : "split on(2)");
-            conn.createStatement().execute(sql);
-
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,8,10,20,30)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,8,11,21,31)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,9,5 ,22,32)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,9,6 ,12,33)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,9,6 ,13,34)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,9,7 ,8,35)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,3,15,25,35)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,7,16,26,36)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,7,17,27,37)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,2,18,28,38)");
-            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,2,19,29,39)");
-            conn.commit();
-
-            sql = "select pk2,pk1,count(v) from " + tableName + " group by pk2,pk1 order by pk2,pk1";
-            ResultSet rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{2,3,2L},{3,2,1L},{7,2,2L},{8,1,2L},{9,1,4L}});
-
-            sql = "select pk1,pk2,count(v) from " + tableName + " group by pk2,pk1 order by pk1,pk2";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{1,8,2L},{1,9,4L},{2,3,1L},{2,7,2L},{3,2,2L}});
-
-            sql = "select pk2,pk1,count(v) from " + tableName + " group by pk2,pk1 order by pk2 desc,pk1 desc";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{9,1,4L},{8,1,2L},{7,2,2L},{3,2,1L},{2,3,2L}});
-
-            sql = "select pk1,pk2,count(v) from " + tableName + " group by pk2,pk1 order by pk1 desc,pk2 desc";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{3,2,2L},{2,7,2L},{2,3,1L},{1,9,4L},{1,8,2L}});
-
-
-            sql = "select pk3,pk2,count(v) from " + tableName + " where pk1=1 group by pk3,pk2 order by pk3,pk2";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{5,9,1L},{6,9,2L},{7,9,1L},{10,8,1L},{11,8,1L}});
-
-            sql = "select pk2,pk3,count(v) from " + tableName + " where pk1=1 group by pk3,pk2 order by pk2,pk3";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{8,10,1L},{8,11,1L},{9,5,1L},{9,6,2L},{9,7,1L}});
-
-            sql = "select pk3,pk2,count(v) from " + tableName + " where pk1=1 group by pk3,pk2 order by pk3 desc,pk2 desc";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{11,8,1L},{10,8,1L},{7,9,1L},{6,9,2L},{5,9,1L}});
-
-            sql = "select pk2,pk3,count(v) from " + tableName + " where pk1=1 group by pk3,pk2 order by pk2 desc,pk3 desc";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{9,7,1L},{9,6,2L},{9,5,1L},{8,11,1L},{8,10,1L}});
-
-
-            sql = "select pk4,pk3,pk1,count(v) from " + tableName + " where pk2=9 group by pk4,pk3,pk1 order by pk4,pk3,pk1";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{8,7,1,1L},{12,6,1,1L},{13,6,1,1L},{22,5,1,1L}});
-
-            sql = "select pk1,pk3,pk4,count(v) from " + tableName + " where pk2=9 group by pk4,pk3,pk1 order by pk1,pk3,pk4";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{1,5,22,1L},{1,6,12,1L},{1,6,13,1L},{1,7,8,1L}});
-
-            sql = "select pk4,pk3,pk1,count(v) from " + tableName + " where pk2=9 group by pk4,pk3,pk1 order by pk4 desc,pk3 desc,pk1 desc";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{22,5,1,1L},{13,6,1,1L},{12,6,1,1L},{8,7,1,1L}});
-
-            sql = "select pk1,pk3,pk4,count(v) from " + tableName + " where pk2=9 group by pk4,pk3,pk1 order by pk1 desc,pk3 desc,pk4 desc";
-            rs = conn.prepareStatement(sql).executeQuery();
-            assertResultSet(rs, new Object[][]{{1,7,8,1L},{1,6,13,1L},{1,6,12,1L},{1,5,22,1L}});
-        } finally {
-            if(conn != null) {
-                conn.close();
-            }
+            assertEquals(4, rs.getLong(1));
         }
     }
 }
+


Mime
View raw message