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/9bfaf183 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/9bfaf183 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/9bfaf183 Branch: refs/heads/omid2 Commit: 9bfaf183a3d092bce6b2dfcacd77ba46aa1b078b Parents: 1b2a3d5 Author: Thomas D'Silva <tdsi...@apache.org> Authored: Thu Oct 18 22:00:01 2018 -0700 Committer: Thomas D'Silva <tdsi...@apache.org> Committed: Tue Nov 6 14:51:13 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/9bfaf183/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)); } } } +
