/* * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.phoenix.end2end; import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; 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 com.google.common.collect.Lists; 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.query.QueryServices; 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.QueryBuilder; import org.apache.phoenix.util.QueryUtil; import org.apache.phoenix.util.TestUtil; import org.junit.Test; public abstract class BaseAggregateIT 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); props.put(QueryServices.FORCE_ROW_KEY_ORDER_ATTRIB, Boolean.FALSE.toString()); 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(); QueryBuilder queryBuilder = new QueryBuilder() .setDistinct(true) .setSelectExpression("'harshit' as TEST_COLUMN, trim(NAM), trim(NAM)") .setSelectExpressionColumns(Lists.newArrayList("NAM")) .setFullTableName(tableName); ResultSet rs = executeQuery(conn, queryBuilder); 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))"; 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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("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") .setSelectExpressionColumns(Lists.newArrayList("TGB_ID", "TGB_NAME", "UTC_DATE_EPOCH", "ACK_SUCCESS_COUNT", "ACK_SUCCESS_ONE_ACK_COUNT")) .setGroupByClause("TGB_ID, TGB_NAME, UTC_EPOCH_HOUR") .setFullTableName(tableName) .setOrderByClause("TGB_ID, UTC_EPOCH_HOUR"); ResultSet rs = executeQuery(conn, queryBuilder); assertTrue(rs.next()); assertEquals(1,rs.getInt(1)); assertEquals("aaa",rs.getString(2)); assertEquals(1000,rs.getDouble(3), 1e-6); assertEquals(2,rs.getLong(4)); assertTrue(rs.next()); assertEquals(2,rs.getInt(1)); assertEquals("bbb",rs.getString(2)); assertEquals(2000,rs.getDouble(3), 1e-6); assertEquals(4,rs.getLong(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) { QueryBuilder queryBuilder = new QueryBuilder() .setSelectColumns(Lists.newArrayList("V1", "V2", "V3")) .setFullTableName(tableName) .setGroupByClause(gb); ResultSet rs = executeQuery(conn, queryBuilder); 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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectColumns(Lists.newArrayList("URI")) .setFullTableName(tableName); ResultSet rs = executeQuery(conn, queryBuilder); 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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("count(1)") .setFullTableName(tableName); ResultSet rs = executeQuery(conn, queryBuilder); assertTrue(rs.next()); assertEquals(8, rs.getLong(1)); assertFalse(rs.next()); conn.close(); } @Test public void testGroupByCase() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); props.put(QueryServices.FORCE_ROW_KEY_ORDER_ATTRIB, Boolean.FALSE.toString()); Connection conn = DriverManager.getConnection(getUrl(), props); String tableName = generateUniqueName(); initData(conn, tableName); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("CASE WHEN URI LIKE 'REPORT%' THEN 'REPORTS' ELSE 'OTHER' END CATEGORY, AVG(APPCPU)") .setSelectExpressionColumns(Lists.newArrayList("URI", "APPCPU")) .setFullTableName(tableName) .setGroupByClause("CATEGORY"); executeQuery(conn, queryBuilder); queryBuilder.setSelectExpression( "CASE URI WHEN 'REPORT%' THEN 'REPORTS' ELSE 'OTHER' END CATEGORY, AVG(APPCPU)") .setSelectExpressionColumns(Lists.newArrayList("URI", "APPCPU")) .setFullTableName(tableName) .setGroupByClause("APPCPU, CATEGORY"); executeQuery(conn, queryBuilder); queryBuilder.setSelectExpression( "CASE URI WHEN 'Report%' THEN 'Reports' ELSE 'Other' END CATEGORY, AVG(APPCPU)") .setSelectColumns(Lists.newArrayList("URI", "APPCPU")) .setFullTableName(tableName) .setGroupByClause("AVG(APPCPU), CATEGORY"); executeQueryThrowsException(conn, queryBuilder, "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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("C, SUM(F + G) AS SUMONE, D, E") .setSelectExpressionColumns(Lists.newArrayList("A", "B", "C", "F", "G", "D", "E")) .setWhereClause("B = 'val' AND A IN ('1','2','3')") .setFullTableName(tableName) .setGroupByClause("C, D, E") .setOrderByClause("SUMONE desc"); ResultSet rs = executeQuery(conn, queryBuilder); 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); props.put(QueryServices.FORCE_ROW_KEY_ORDER_ATTRIB, Boolean.FALSE.toString()); 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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("COUNT(1), EXTERNAL_DATASOURCE_KEY As DUP_COUNT") .setSelectExpressionColumns(Lists.newArrayList("EXTERNAL_DATASOURCE_KEY", "MATCH_STATUS", "JOURNEY_ID", "DATASOURCE", "ORGANIZATION_ID")) .setWhereClause( "JOURNEY_ID='333334444455555' AND DATASOURCE=0 AND MATCH_STATUS <= 1 and ORGANIZATION_ID='000001111122222'") .setFullTableName(tableName) .setGroupByClause("MATCH_STATUS, EXTERNAL_DATASOURCE_KEY") .setHavingClause("COUNT(1) > 1"); ResultSet rs = executeQuery(conn, queryBuilder); assertTrue(rs.next()); assertEquals(2,rs.getLong(1)); assertEquals("abc", rs.getString(2)); assertFalse(rs.next()); String expectedPhoenixPlan = "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"; validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null); } @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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("K1,COUNT(*)") .setSelectColumns(Lists.newArrayList("K1")) .setFullTableName(tableName) .setGroupByClause("K1") .setOrderByClause("K1 DESC"); ResultSet rs = executeQuery(conn, queryBuilder); assertTrue(rs.next()); assertEquals("n", rs.getString(1)); assertEquals(4, rs.getLong(2)); assertTrue(rs.next()); assertEquals("j", rs.getString(1)); assertEquals(4, rs.getLong(2)); assertTrue(rs.next()); assertEquals("a", rs.getString(1)); assertEquals(4, rs.getLong(2)); assertFalse(rs.next()); String expectedPhoenixPlan = "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]"; validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null); } @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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("K1,SUM(K2)") .setSelectExpressionColumns(Lists.newArrayList("K1", "K2")) .setFullTableName(tableName) .setGroupByClause("K1") .setOrderByClause("K1 DESC"); ResultSet rs = executeQuery(conn, queryBuilder); assertTrue(rs.next()); assertEquals("n", rs.getString(1)); assertEquals(10, rs.getLong(2)); assertTrue(rs.next()); assertEquals("j", rs.getString(1)); assertEquals(10, rs.getLong(2)); assertTrue(rs.next()); assertEquals("b", rs.getString(1)); assertEquals(5, rs.getLong(2)); assertTrue(rs.next()); assertEquals("a", rs.getString(1)); assertEquals(10, rs.getLong(2)); assertFalse(rs.next()); String expectedPhoenixPlan = "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]"; validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null); } @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); } protected 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(); } protected void testAvgGroupByOrderPreserving(Connection conn, String tableName, int nGuidePosts) throws SQLException, IOException { QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("K1, AVG(K2)") .setSelectExpressionColumns(Lists.newArrayList("K1", "K2")) .setFullTableName(tableName) .setGroupByClause("K1") .setOrderByClause("K1"); ResultSet rs = executeQuery(conn, queryBuilder); assertTrue(rs.next()); assertEquals("a", rs.getString(1)); assertEquals(3, rs.getDouble(2), 1e-6); assertTrue(rs.next()); assertEquals("b", rs.getString(1)); assertEquals(5, rs.getDouble(2), 1e-6); assertTrue(rs.next()); assertEquals("j", rs.getString(1)); assertEquals(4, rs.getDouble(2), 1e-6); assertTrue(rs.next()); assertEquals("n", rs.getString(1)); assertEquals(2, rs.getDouble(2), 1e-6); assertFalse(rs.next()); String expectedPhoenixPlan = "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + tableName + "\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [K1]"; validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null); TestUtil.analyzeTable(conn, tableName); List<KeyRange> splits = TestUtil.getAllSplits(conn, tableName); assertEquals(nGuidePosts, splits.size()); } @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(); QueryBuilder queryBuilder = new QueryBuilder() .setDistinct(true) .setSelectColumns(Lists.newArrayList("ENTITY_ID", "SCORE", "ORGANIZATION_ID", "CONTAINER_ID")) .setFullTableName(tableName) .setWhereClause("ORGANIZATION_ID = 'org1' AND CONTAINER_ID = 'container1'") .setOrderByClause("SCORE DESC"); ResultSet rs = executeQuery(conn, queryBuilder); 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(); QueryBuilder queryBuilder = new QueryBuilder() .setDistinct(true) .setSelectColumns(Lists.newArrayList("ENTITY_ID", "SCORE", "ORGANIZATION_ID", "CONTAINER_ID")) .setFullTableName(tableName) .setWhereClause( "ORGANIZATION_ID = 'org2' AND CONTAINER_ID IN ('container1','container2','container3')") .setOrderByClause("SCORE DESC") .setLimit(2); ResultSet rs = executeQuery(conn, queryBuilder); 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()); String expectedPhoenixPlan = ""; validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null); } } @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 QueryBuilder queryBuilder = new QueryBuilder() .setSelectColumns( Lists.newArrayList("CONTAINER_ID", "ORGANIZATION_ID")) .setFullTableName(tableName) .setGroupByClause("ORGANIZATION_ID, CONTAINER_ID") .setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST"); ResultSet rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{null,"a"},{"3","c"},}); queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null}}); //----CONTAINER_ID queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{"2",null},{"3","c"}}); queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"}}); conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,'44')"); conn.commit(); //-----ORGANIZATION_ID ASC CONTAINER_ID ASC queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{"2",null},{null,"a"},{"3","c"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{null,null},{null,"a"},{"3","c"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{null,null},{"2",null}}); queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null},{null,null}}); //-----ORGANIZATION_ID ASC CONTAINER_ID DESC queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{"2",null},{null,"a"},{"3","c"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{null,null},{null,"a"},{"3","c"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{null,null},{"2",null}}); queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null},{null,null}}); //-----ORGANIZATION_ID DESC CONTAINER_ID ASC queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{"2",null},{"3","c"},{null,"a"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{null,null},{"3","c"},{null,"a"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{null,null},{"2",null}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null},{null,null}}); //-----ORGANIZATION_ID DESC CONTAINER_ID DESC queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{"2",null},{"3","c"},{null,"a"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{null,null},{"3","c"},{null,"a"}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{null,null},{"2",null}}); queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null},{null,null}}); //-----CONTAINER_ID ASC ORGANIZATION_ID ASC queryBuilder.setOrderByClause("CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"2",null},{"3","c"}}); queryBuilder.setOrderByClause("CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"2",null},{"3","c"}}); queryBuilder.setOrderByClause("CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,null},{null,"a"}}); queryBuilder.setOrderByClause("CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"},{null,null}}); //-----CONTAINER_ID ASC ORGANIZATION_ID DESC queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"2",null},{"3","c"}}); queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"2",null},{"3","c"}}); queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,null},{null,"a"}}); queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"},{null,null}}); //-----CONTAINER_ID DESC ORGANIZATION_ID ASC queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"3","c"},{"2",null}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"3","c"},{"2",null}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,null},{null,"a"}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"},{null,null}}); //-----CONTAINER_ID DESC ORGANIZATION_ID DESC queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"3","c"},{"2",null}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"3","c"},{"2",null}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,null},{null,"a"}}); queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"},{null,null}}); } finally { if(conn!=null) { conn.close(); } } } @Test public void testCountNullInEncodedNonEmptyKeyValueCF() throws Exception { testCountNullInNonEmptyKeyValueCF(1); } @Test public void testCountNullInNonEncodedNonEmptyKeyValueCF() throws Exception { testCountNullInNonEmptyKeyValueCF(0); } protected abstract void testCountNullInNonEmptyKeyValueCF(int columnEncodedBytes) throws Exception; @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(); QueryBuilder queryBuilder = new QueryBuilder() .setSelectExpression("PK2,PK1,COUNT(V)") .setSelectExpressionColumns(Lists.newArrayList("PK1", "PK2", "V")) .setFullTableName(tableName) .setGroupByClause("PK2, PK1") .setOrderByClause("PK2, PK1"); ResultSet rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{2,3,2L},{3,2,1L},{7,2,2L},{8,1,2L},{9,1,4L}}); queryBuilder.setSelectExpression("PK1, PK2, COUNT(V)"); queryBuilder.setOrderByClause("PK1, PK2"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{1,8,2L},{1,9,4L},{2,3,1L},{2,7,2L},{3,2,2L}}); queryBuilder.setSelectExpression("PK2,PK1,COUNT(V)"); queryBuilder.setOrderByClause("PK2 DESC,PK1 DESC"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{9,1,4L},{8,1,2L},{7,2,2L},{3,2,1L},{2,3,2L}}); queryBuilder.setSelectExpression("PK1,PK2,COUNT(V)"); queryBuilder.setOrderByClause("PK1 DESC,PK2 DESC"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{3,2,2L},{2,7,2L},{2,3,1L},{1,9,4L},{1,8,2L}}); queryBuilder.setSelectExpression("PK3,PK2,COUNT(V)"); queryBuilder.setSelectExpressionColumns(Lists.newArrayList("PK1", "PK2", "PK3", "V")); queryBuilder.setFullTableName(tableName); queryBuilder.setGroupByClause("PK3,PK2"); queryBuilder.setOrderByClause("PK3,PK2"); queryBuilder.setWhereClause("PK1=1"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{5,9,1L},{6,9,2L},{7,9,1L},{10,8,1L},{11,8,1L}}); queryBuilder.setSelectExpression("PK2,PK3,COUNT(V)"); queryBuilder.setOrderByClause("PK2,PK3"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{8,10,1L},{8,11,1L},{9,5,1L},{9,6,2L},{9,7,1L}}); queryBuilder.setSelectExpression("PK3,PK2,COUNT(V)"); queryBuilder.setOrderByClause("PK3 DESC,PK2 DESC"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{11,8,1L},{10,8,1L},{7,9,1L},{6,9,2L},{5,9,1L}}); queryBuilder.setSelectExpression("PK2,PK3,COUNT(V)"); queryBuilder.setOrderByClause("PK2 DESC,PK3 DESC"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{9,7,1L},{9,6,2L},{9,5,1L},{8,11,1L},{8,10,1L}}); queryBuilder.setSelectExpression("PK4,PK3,PK1,COUNT(V)"); queryBuilder.setSelectExpressionColumns(Lists.newArrayList("PK1", "PK2", "PK3", "PK4", "V")); queryBuilder.setFullTableName(tableName); queryBuilder.setWhereClause("PK2=9 "); queryBuilder.setGroupByClause("PK4,PK3,PK1"); queryBuilder.setOrderByClause("PK4,PK3,PK1"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{8,7,1,1L},{12,6,1,1L},{13,6,1,1L},{22,5,1,1L}}); queryBuilder.setSelectExpression("PK1,PK3,PK4,COUNT(V)"); queryBuilder.setOrderByClause("PK1,PK3,PK4"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{1,5,22,1L},{1,6,12,1L},{1,6,13,1L},{1,7,8,1L}}); queryBuilder.setSelectExpression("PK4,PK3,PK1,COUNT(V)"); queryBuilder.setOrderByClause("PK4 DESC,PK3 DESC,PK1 DESC"); rs = executeQuery(conn, queryBuilder); assertResultSet(rs, new Object[][]{{22,5,1,1L},{13,6,1,1L},{12,6,1,1L},{8,7,1,1L}}); queryBuilder.setSelectExpression("PK1,PK3,PK4,COUNT(V)"); queryBuilder.setOrderByClause("PK1 DESC,PK3 DESC,PK4 DESC"); rs = executeQuery(conn, queryBuilder); 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(); } } } }