| /* |
| * 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(); |
| } |
| } |
| } |
| } |
