| /* |
| * 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.query.QueryConstants.MILLIS_IN_DAY; |
| 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 java.math.BigDecimal; |
| import java.sql.Connection; |
| import java.sql.Date; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Types; |
| import java.util.Collections; |
| import java.util.List; |
| import java.util.Properties; |
| |
| import org.apache.hadoop.hbase.TableName; |
| import org.apache.hadoop.hbase.client.Admin; |
| import org.apache.hadoop.hbase.util.Bytes; |
| import org.apache.phoenix.jdbc.PhoenixConnection; |
| import org.apache.phoenix.query.QueryConstants; |
| import org.apache.phoenix.schema.types.PDate; |
| import org.apache.phoenix.util.ByteUtil; |
| import org.apache.phoenix.util.DateUtil; |
| import org.apache.phoenix.util.PropertiesUtil; |
| import org.apache.phoenix.util.SchemaUtil; |
| import org.junit.Test; |
| |
| import com.google.common.collect.Lists; |
| import com.google.common.collect.Ordering; |
| |
| |
| public class ProductMetricsIT extends ParallelStatsDisabledIT { |
| private static final String PRODUCT_METRICS_NAME = "PRODUCT_METRICS"; |
| private static final String PRODUCT_METRICS_SCHEMA_NAME = ""; |
| private static final String DS1 = "1970-01-01 00:58:00"; |
| private static final String DS2 = "1970-01-01 01:02:00"; |
| private static final String DS3 = "1970-01-01 01:30:00"; |
| private static final String DS4 = "1970-01-01 01:45:00"; |
| private static final String DS5 = "1970-01-01 02:00:00"; |
| private static final String DS6 = "1970-01-01 04:00:00"; |
| private static final Date D1 = toDate(DS1); |
| private static final Date D2 = toDate(DS2); |
| private static final Date D3 = toDate(DS3); |
| private static final Date D4 = toDate(DS4); |
| private static final Date D5 = toDate(DS5); |
| private static final Date D6 = toDate(DS6); |
| private static final Object ROUND_1HR = toDate("1970-01-01 01:00:00"); |
| private static final Object ROUND_2HR = toDate("1970-01-01 02:00:00"); |
| private static final String F1 = "A"; |
| private static final String F2 = "B"; |
| private static final String F3 = "C"; |
| private static final String R1 = "R1"; |
| private static final String R2 = "R2"; |
| |
| private static byte[][] getSplits(String tenantId) { |
| return new byte[][] { |
| ByteUtil.concat(Bytes.toBytes(tenantId), PDate.INSTANCE.toBytes(D3)), |
| ByteUtil.concat(Bytes.toBytes(tenantId), PDate.INSTANCE.toBytes(D5)), |
| }; |
| } |
| |
| private static Date toDate(String dateString) { |
| return DateUtil.parseDate(dateString); |
| } |
| |
| private static void initTable(String tablename, byte[][] splits) throws Exception { |
| ensureTableCreated(getUrl(), tablename, PRODUCT_METRICS_NAME, splits, null, null); |
| } |
| |
| private static void assertNoRows(String tablename,Connection conn) throws SQLException { |
| Statement stmt = conn.createStatement(); |
| ResultSet rs = stmt.executeQuery("select 1 from "+tablename); |
| assertFalse(rs.next()); |
| } |
| |
| private static void initTableValues(String tablename, String tenantId, byte[][] splits) throws Exception { |
| initTable(tablename, splits); |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| assertNoRows(tablename, conn); |
| initTableValues(tablename, conn, tenantId); |
| conn.commit(); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| private static void initTableValues(String tablename, Connection conn, String tenantId) throws Exception { |
| PreparedStatement stmt = conn.prepareStatement( |
| "upsert into " + tablename + |
| " (" + |
| " ORGANIZATION_ID, " + |
| " \"DATE\", " + |
| " FEATURE, " + |
| " UNIQUE_USERS, " + |
| " TRANSACTIONS, " + |
| " CPU_UTILIZATION, " + |
| " DB_UTILIZATION, " + |
| " REGION, " + |
| " IO_TIME)" + |
| "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, D1); |
| stmt.setString(3, F1); |
| stmt.setInt(4, 10); |
| stmt.setLong(5, 100L); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(0.5)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.2)); |
| stmt.setString(8, R2); |
| stmt.setNull(9, Types.BIGINT); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, D2); |
| stmt.setString(3, F1); |
| stmt.setInt(4, 20); |
| stmt.setLong(5, 200); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(1.0)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.4)); |
| stmt.setString(8, null); |
| stmt.setLong(9, 2000); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, D3); |
| stmt.setString(3, F1); |
| stmt.setInt(4, 30); |
| stmt.setLong(5, 300); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(2.5)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.6)); |
| stmt.setString(8, R1); |
| stmt.setNull(9, Types.BIGINT); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, D4); |
| stmt.setString(3, F2); |
| stmt.setInt(4, 40); |
| stmt.setLong(5, 400); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(3.0)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.8)); |
| stmt.setString(8, R1); |
| stmt.setLong(9, 4000); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, D5); |
| stmt.setString(3, F3); |
| stmt.setInt(4, 50); |
| stmt.setLong(5, 500); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(3.5)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(1.2)); |
| stmt.setString(8, R2); |
| stmt.setLong(9, 5000); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, D6); |
| stmt.setString(3, F1); |
| stmt.setInt(4, 60); |
| stmt.setLong(5, 600); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(4.0)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(1.4)); |
| stmt.setString(8, null); |
| stmt.setNull(9, Types.BIGINT); |
| stmt.execute(); |
| } |
| |
| private static void initDateTableValues(String tablename, String tenantId, byte[][] splits, Date startDate) throws Exception { |
| initDateTableValues(tablename, tenantId, splits, startDate, 2.0); |
| } |
| |
| private static void initDateTableValues(String tablename, String tenantId, byte[][] splits, Date startDate, double dateIncrement) throws Exception { |
| initTable(tablename, splits); |
| |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| assertNoRows(tablename, conn); |
| initDateTableValues(tablename, conn, tenantId, startDate, dateIncrement); |
| conn.commit(); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| private static void initDateTableValues(String tablename, Connection conn, String tenantId, Date startDate, double dateIncrement) throws Exception { |
| PreparedStatement stmt = conn.prepareStatement( |
| "upsert into " +tablename+ |
| "(" + |
| " ORGANIZATION_ID, " + |
| " \"DATE\", " + |
| " FEATURE, " + |
| " UNIQUE_USERS, " + |
| " TRANSACTIONS, " + |
| " CPU_UTILIZATION, " + |
| " DB_UTILIZATION, " + |
| " REGION, " + |
| " IO_TIME)" + |
| "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, startDate); |
| stmt.setString(3, "A"); |
| stmt.setInt(4, 10); |
| stmt.setLong(5, 100L); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(0.5)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.2)); |
| stmt.setString(8, R2); |
| stmt.setNull(9, Types.BIGINT); |
| stmt.execute(); |
| |
| startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement)); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, startDate); |
| stmt.setString(3, "B"); |
| stmt.setInt(4, 20); |
| stmt.setLong(5, 200); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(1.0)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.4)); |
| stmt.setString(8, null); |
| stmt.setLong(9, 2000); |
| stmt.execute(); |
| |
| startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement)); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, startDate); |
| stmt.setString(3, "C"); |
| stmt.setInt(4, 30); |
| stmt.setLong(5, 300); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(2.5)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.6)); |
| stmt.setString(8, R1); |
| stmt.setNull(9, Types.BIGINT); |
| stmt.execute(); |
| |
| startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement)); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, startDate); |
| stmt.setString(3, "D"); |
| stmt.setInt(4, 40); |
| stmt.setLong(5, 400); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(3.0)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.8)); |
| stmt.setString(8, R1); |
| stmt.setLong(9, 4000); |
| stmt.execute(); |
| |
| startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement)); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, startDate); |
| stmt.setString(3, "E"); |
| stmt.setInt(4, 50); |
| stmt.setLong(5, 500); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(3.5)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(1.2)); |
| stmt.setString(8, R2); |
| stmt.setLong(9, 5000); |
| stmt.execute(); |
| |
| startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement)); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, startDate); |
| stmt.setString(3, "F"); |
| stmt.setInt(4, 60); |
| stmt.setLong(5, 600); |
| stmt.setBigDecimal(6, BigDecimal.valueOf(4.0)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(1.4)); |
| stmt.setString(8, null); |
| stmt.setNull(9, Types.BIGINT); |
| stmt.execute(); |
| } |
| |
| |
| @Test |
| public void testDateRangeAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(1), feature f FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND \"DATE\" <= to_date(?) GROUP BY f"; |
| //String query = "SELECT count(1), feature FROM PRODUCT_METRICS GROUP BY feature"; |
| |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, DS2); |
| statement.setString(3, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(2, rs.getLong(1)); |
| assertEquals(F1, rs.getString(2)); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getLong(1)); |
| assertEquals(F2, rs.getString(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testTableAliasSameAsTableName() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum("+tablename+".transactions) FROM "+tablename+" PRODUCT_METRICS"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(2100, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testPartiallyEvaluableAnd() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT \"DATE\" FROM "+tablename+" WHERE organization_id=? AND unique_users >= 30 AND transactions >= 300 AND cpu_utilization > 2 AND db_utilization > 0.5 AND io_time = 4000"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(D4, rs.getDate(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testPartiallyEvaluableOr() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT \"DATE\" FROM "+tablename+" WHERE organization_id=? AND (transactions = 10000 OR unset_column = 5 OR io_time = 4000)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(D4, rs.getDate(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testConstantTrueHaving() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(1), feature FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND \"DATE\" <= to_date(?) GROUP BY feature HAVING 1=1"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, DS2); |
| statement.setString(3, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(2, rs.getLong(1)); |
| assertEquals(F1, rs.getString(2)); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getLong(1)); |
| assertEquals(F2, rs.getString(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testConstantFalseHaving() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(1), feature FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND \"DATE\" <= to_date(?) GROUP BY feature HAVING 1=1 and 0=1"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, DS2); |
| statement.setString(3, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testDateRangeHavingAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(1), feature FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND \"DATE\" <= to_date(?) GROUP BY feature HAVING count(1) >= 2"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, DS2); |
| statement.setString(3, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(2, rs.getLong(1)); |
| assertEquals(F1, rs.getString(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testDateRangeSumLongAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(transactions), feature FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND \"DATE\" <= to_date(?) GROUP BY feature"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, DS2); |
| statement.setString(3, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(500, rs.getLong(1)); |
| assertEquals(F1, rs.getString(2)); |
| assertTrue(rs.next()); |
| assertEquals(400, rs.getLong(1)); |
| assertEquals(F2, rs.getString(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testRoundAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT round(\"DATE\",'hour',1) r,count(1) FROM "+tablename+" WHERE organization_id=? GROUP BY r"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| Date d; |
| int c; |
| ResultSet rs = statement.executeQuery(); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| c = rs.getInt(2); |
| assertEquals(1 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 1 hr |
| assertEquals(2, c); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| c = rs.getInt(2); |
| assertEquals(2 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 2 hr |
| assertEquals(3, c); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| c = rs.getInt(2); |
| assertEquals(4 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 4 hr |
| assertEquals(1, c); |
| |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testRoundScan() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT round(\"DATE\",'hour') FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| Date d; |
| long t; |
| ResultSet rs = statement.executeQuery(); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| t = 1 * 60 * 60 * 1000; |
| assertEquals(t, d.getTime()); // Date bucketed into 1 hr |
| assertTrue(rs.next()); |
| assertEquals(t, d.getTime()); // Date bucketed into 1 hr |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| t = 2 * 60 * 60 * 1000; |
| assertEquals(t, d.getTime()); // Date bucketed into 2 hr |
| assertTrue(rs.next()); |
| assertEquals(t, d.getTime()); // Date bucketed into 2 hr |
| assertTrue(rs.next()); |
| assertEquals(t, d.getTime()); // Date bucketed into 2 hr |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| t = 4 * 60 * 60 * 1000; |
| assertEquals(t, d.getTime()); // Date bucketed into 4 hr |
| |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testTruncAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT trunc(\"DATE\",'hour'),count(1) FROM "+tablename+" WHERE organization_id=? GROUP BY trunc(\"DATE\",'hour')"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| Date d; |
| int c; |
| ResultSet rs = statement.executeQuery(); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| c = rs.getInt(2); |
| assertEquals(0, d.getTime()); // Date bucketed into 0 hr |
| assertEquals(1, c); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| c = rs.getInt(2); |
| assertEquals(1 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 1 hr |
| assertEquals(3, c); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| c = rs.getInt(2); |
| assertEquals(2 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 2 hr |
| assertEquals(1, c); |
| |
| assertTrue(rs.next()); |
| d = rs.getDate(1); |
| c = rs.getInt(2); |
| assertEquals(4 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 4 hr |
| assertEquals(1, c); |
| |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,sum(unique_users) FROM "+tablename+" WHERE organization_id=? AND transactions > 0 GROUP BY feature"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertTrue(rs.next()); |
| assertTrue(rs.next()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testHavingAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,sum(unique_users) FROM "+tablename+" WHERE organization_id=? AND transactions > 0 GROUP BY feature HAVING feature=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, F1); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testConstantSumAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(1),sum(unique_users) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(6,rs.getInt(1)); |
| assertEquals(210,rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testMultiDimAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,region,sum(unique_users) FROM "+tablename+" WHERE organization_id=? GROUP BY feature,region"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| |
| assertTrue(rs.next()); |
| assertEquals(F1,rs.getString(1)); |
| assertEquals(null,rs.getString(2)); |
| assertEquals(80,rs.getInt(3)); |
| assertTrue(rs.next()); |
| assertEquals(F1,rs.getString(1)); |
| assertEquals(R1,rs.getString(2)); |
| assertEquals(30,rs.getInt(3)); |
| assertTrue(rs.next()); |
| assertEquals(F1,rs.getString(1)); |
| assertEquals(R2,rs.getString(2)); |
| assertEquals(10,rs.getInt(3)); |
| |
| assertTrue(rs.next()); |
| assertEquals(F2,rs.getString(1)); |
| assertEquals(R1,rs.getString(2)); |
| assertEquals(40,rs.getInt(3)); |
| |
| assertTrue(rs.next()); |
| assertEquals(F3,rs.getString(1)); |
| assertEquals(R2,rs.getString(2)); |
| assertEquals(50,rs.getInt(3)); |
| |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testMultiDimRoundAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT round(\"DATE\",'hour',1),feature,sum(unique_users) FROM "+tablename+" WHERE organization_id=? GROUP BY round(\"DATE\",'hour',1),feature"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| |
| Date bucket1 = new Date(1 * 60 * 60 * 1000); |
| Date bucket2 = new Date(2 * 60 * 60 * 1000); |
| Date bucket3 = new Date(4 * 60 * 60 * 1000); |
| |
| assertTrue(rs.next()); |
| assertEquals(bucket1, rs.getDate(1)); |
| assertEquals(F1,rs.getString(2)); |
| assertEquals(30,rs.getInt(3)); |
| |
| |
| assertTrue(rs.next()); |
| assertEquals(bucket2, rs.getDate(1)); |
| assertEquals(F1,rs.getString(2)); |
| assertEquals(30,rs.getInt(3)); |
| |
| assertTrue(rs.next()); |
| assertEquals(bucket2.getTime(), rs.getDate(1).getTime()); |
| assertEquals(F2,rs.getString(2)); |
| assertEquals(40,rs.getInt(3)); |
| |
| assertTrue(rs.next()); |
| assertEquals(bucket2, rs.getDate(1)); |
| assertEquals(F3,rs.getString(2)); |
| assertEquals(50,rs.getInt(3)); |
| |
| |
| assertTrue(rs.next()); |
| assertEquals(bucket3, rs.getDate(1)); |
| assertEquals(F1,rs.getString(2)); |
| assertEquals(60,rs.getInt(3)); |
| |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testDateRangeSumNumberUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(cpu_utilization) FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND \"DATE\" <= to_date(?)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, DS2); |
| statement.setString(3, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(BigDecimal.valueOf(6.5), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSumUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(210, rs.getInt(1)); |
| assertEquals(BigDecimal.valueOf(14.5), rs.getBigDecimal(2)); |
| assertEquals(2100L, rs.getLong(3)); |
| assertEquals(BigDecimal.valueOf(4.6), rs.getBigDecimal(4)); |
| assertEquals(0, rs.getLong(5)); |
| assertEquals(true, rs.wasNull()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testResetColumnInSameTxn() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(transactions) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| Connection upsertConn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTable(tablename,getSplits(tenantId)); |
| initTableValues(tablename, upsertConn, tenantId); |
| PreparedStatement stmt = upsertConn.prepareStatement( |
| "upsert into " + tablename+ |
| "(" + |
| " ORGANIZATION_ID, " + |
| " \"DATE\", " + |
| " FEATURE, " + |
| " UNIQUE_USERS," + |
| " TRANSACTIONS) " + |
| "VALUES (?, ?, ?, ?, ?)"); |
| stmt.setString(1, tenantId); |
| stmt.setDate(2, D1); |
| stmt.setString(3, F1); |
| stmt.setInt(4, 10); |
| stmt.setInt(5, 200); // Change TRANSACTIONS from 100 to 200 |
| stmt.execute(); |
| upsertConn.commit(); |
| |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(2200, rs.getInt(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSumUngroupedHavingAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time) FROM "+tablename+" WHERE organization_id=? HAVING sum(unique_users) > 200 AND sum(db_utilization) > 4.5"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(210, rs.getInt(1)); |
| assertEquals(BigDecimal.valueOf(14.5), rs.getBigDecimal(2)); |
| assertEquals(2100L, rs.getLong(3)); |
| assertEquals(BigDecimal.valueOf(4.6), rs.getBigDecimal(4)); |
| assertEquals(0, rs.getLong(5)); |
| assertEquals(true, rs.wasNull()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSumUngroupedHavingAggregation2() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time) FROM "+tablename+" WHERE organization_id=? HAVING sum(unique_users) > 200 AND sum(db_utilization) > 5"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testMinUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT min(unique_users),min(cpu_utilization),min(transactions),min(db_utilization),min('X'),min(response_time) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(10, rs.getInt(1)); |
| assertEquals(BigDecimal.valueOf(0.5), rs.getBigDecimal(2)); |
| assertEquals(100L, rs.getLong(3)); |
| assertEquals(BigDecimal.valueOf(0.2), rs.getBigDecimal(4)); |
| assertEquals("X", rs.getString(5)); |
| assertEquals(0, rs.getLong(6)); |
| assertEquals(true, rs.wasNull()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testMinUngroupedAggregation1() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT min(cpu_utilization) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(BigDecimal.valueOf(0.5), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testMaxUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT max(unique_users),max(cpu_utilization),max(transactions),max(db_utilization),max('X'),max(response_time) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(60, rs.getInt(1)); |
| assertEquals(BigDecimal.valueOf(4), rs.getBigDecimal(2)); |
| assertEquals(600L, rs.getLong(3)); |
| assertEquals(BigDecimal.valueOf(1.4), rs.getBigDecimal(4)); |
| assertEquals("X", rs.getString(5)); |
| assertEquals(0, rs.getLong(6)); |
| assertEquals(true, rs.wasNull()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testMaxGroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,max(transactions) FROM "+tablename+" WHERE organization_id=? GROUP BY feature"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F1,rs.getString(1)); |
| assertEquals(600,rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F2,rs.getString(1)); |
| assertEquals(400,rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F3,rs.getString(1)); |
| assertEquals(500,rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testCountUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(1) FROM "+tablename; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(6, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testCountColumnUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(io_time),sum(io_time),avg(io_time) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(3, rs.getLong(1)); |
| assertEquals(11000, rs.getLong(2)); |
| // Scale is automatically capped at 4 if no scale is specified. |
| assertEquals(new BigDecimal("3666.6666"), rs.getBigDecimal(3)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testNoRowsUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(io_time),sum(io_time),avg(io_time),count(1) FROM "+tablename+" WHERE organization_id=? AND feature > ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2,F3); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(0, rs.getLong(1)); |
| assertFalse(rs.wasNull()); |
| assertEquals(0, rs.getLong(2)); |
| assertTrue(rs.wasNull()); |
| assertEquals(null, rs.getBigDecimal(3)); |
| assertEquals(0, rs.getLong(4)); |
| assertFalse(rs.wasNull()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testAvgUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT avg(unique_users) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(BigDecimal.valueOf(35), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testAvgUngroupedAggregationOnValueField() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT AVG(DB_UTILIZATION) FROM "+tablename+" WHERE organization_id=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| // The column is defined as decimal(31,10), so the value is capped at 10 decimal points. |
| assertEquals(new BigDecimal("0.7666666666"), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| /** |
| * Test aggregate query with rownum limit that does not explicity contain a count(1) as a select expression |
| * @throws Exception |
| */ |
| @Test |
| public void testLimitSumUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| // No count(1) aggregation, so it will get added automatically |
| // LIMIT has no effect, since it's applied at the end and we'll always have a single row for ungrouped aggregation |
| String query = "SELECT sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time) feature FROM "+tablename+" WHERE organization_id=? LIMIT 3"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(210, rs.getInt(1)); |
| assertEquals(BigDecimal.valueOf(14.5), rs.getBigDecimal(2)); |
| assertEquals(2100L, rs.getLong(3)); |
| assertEquals(BigDecimal.valueOf(4.6), rs.getBigDecimal(4)); |
| assertEquals(0, rs.getLong(5)); |
| assertEquals(true, rs.wasNull()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| /** |
| * Test grouped aggregation query with a mix of aggregated data types |
| * @throws Exception |
| */ |
| @Test |
| public void testSumGroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time),count(1) c FROM "+tablename+" WHERE organization_id=? AND feature < ? GROUP BY feature"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, F3); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString("feature")); |
| assertEquals(120, rs.getInt("sum(unique_users)")); |
| assertEquals(BigDecimal.valueOf(8), rs.getBigDecimal(3)); |
| assertEquals(1200L, rs.getLong(4)); |
| assertEquals(BigDecimal.valueOf(2.6), rs.getBigDecimal(5)); |
| assertEquals(0, rs.getLong(6)); |
| assertEquals(true, rs.wasNull()); |
| assertEquals(4, rs.getLong("c")); |
| assertTrue(rs.next()); |
| assertEquals(F2, rs.getString("feature")); |
| assertEquals(40, rs.getInt(2)); |
| assertEquals(BigDecimal.valueOf(3), rs.getBigDecimal(3)); |
| assertEquals(400L, rs.getLong(4)); |
| assertEquals(BigDecimal.valueOf(0.8), rs.getBigDecimal(5)); |
| assertEquals(0, rs.getLong(6)); |
| assertEquals(true, rs.wasNull()); |
| assertEquals(1, rs.getLong("c")); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testDegenerateAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT count(1), feature FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND \"DATE\" <= to_date(?) GROUP BY feature"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| // Start date larger than end date |
| statement.setString(2, DS4); |
| statement.setString(3, DS2); |
| ResultSet rs = statement.executeQuery(); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| /** |
| * Query with multiple > expressions on continquous PK columns |
| * @throws Exception |
| */ |
| @Test |
| public void testFeatureDateRangeAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,unique_users FROM "+tablename+" WHERE organization_id=? AND \"DATE\" >= to_date(?) AND feature > ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, DS2); |
| statement.setString(3, F2); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F3, rs.getString(1)); |
| assertEquals(50, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| /** |
| * Query with non contiguous PK column expressions (i.e. no expresion for DATE) |
| * @throws Exception |
| */ |
| @Test |
| public void testFeatureGTAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,unique_users FROM "+tablename+" WHERE organization_id=? AND feature > ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, F2); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F3, rs.getString(1)); |
| assertEquals(50, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testFeatureGTEAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,unique_users FROM "+tablename+" WHERE organization_id=? AND feature >= ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, F2); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F2, rs.getString(1)); |
| assertEquals(40, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F3, rs.getString(1)); |
| assertEquals(50, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testFeatureEQAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,unique_users FROM "+tablename+" WHERE organization_id=? AND feature = ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, F2); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F2, rs.getString(1)); |
| assertEquals(40, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| |
| @Test |
| public void testFeatureLTAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,unique_users FROM "+tablename+" WHERE organization_id=? AND feature < ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, F2); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(10, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(20, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(30, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(60, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testFeatureLTEAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,unique_users FROM "+tablename+" WHERE organization_id=? AND feature <= ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setString(2, F2); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(10, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(20, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(30, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F2, rs.getString(1)); |
| assertEquals(40, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(F1, rs.getString(1)); |
| assertEquals(60, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| @Test |
| public void testOrderByNonAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| String query = "SELECT \"DATE\", transactions t FROM "+tablename+" WHERE organization_id=? AND unique_users <= 30 ORDER BY t DESC LIMIT 2"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(D3.getTime(), rs.getDate(1).getTime()); |
| assertTrue(rs.next()); |
| assertEquals(D2.getTime(), rs.getDate(1).getTime()); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testOrderByUngroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(unique_users) sumUsers,count(feature) " + |
| "FROM " + tablename+ |
| " WHERE organization_id=? " + |
| "ORDER BY 100000-sumUsers"; |
| |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(210, rs.getInt(1)); |
| assertEquals(6, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testOrderByGroupedAggregation() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature,sum(unique_users) s,count(feature),round(\"DATE\",'hour',1) r " + |
| "FROM "+tablename+ |
| " WHERE organization_id=? " + |
| "GROUP BY feature, r " + |
| "ORDER BY 1 desc,feature desc,r,feature,s"; |
| |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| |
| Object[][] expected = { |
| {F3, 50, 1}, |
| {F2, 40, 1}, |
| {F1, 30, 2}, |
| {F1, 30, 1}, |
| {F1, 60, 1}, |
| }; |
| |
| for (int i = 0; i < expected.length; i++) { |
| assertTrue(rs.next()); |
| assertEquals(expected[i][0], rs.getString(1)); |
| assertEquals(expected[i][1], rs.getInt(2)); |
| assertEquals(expected[i][2], rs.getInt(3)); |
| } |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testOrderByUnprojected() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT sum(unique_users), count(feature) c " + |
| "FROM " + tablename+ |
| " WHERE organization_id=? " + |
| "GROUP BY feature " + |
| "ORDER BY 100-c,feature"; |
| |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| |
| int[] expected = {120, 40, 50}; |
| for (int i = 0; i < expected.length; i++) { |
| assertTrue(rs.next()); |
| assertEquals(expected[i], rs.getInt(1)); |
| } |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testOrderByNullColumns__nullsFirst() throws Exception { |
| helpTestOrderByNullColumns(true); |
| } |
| |
| @Test |
| public void testOrderByNullColumns__nullsLast() throws Exception { |
| helpTestOrderByNullColumns(false); |
| } |
| |
| private void helpTestOrderByNullColumns(boolean nullsFirst) throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT region " + |
| "FROM " +tablename+ |
| " WHERE organization_id=? " + |
| "GROUP BY region " + |
| "ORDER BY region nulls " + (nullsFirst ? "first" : "last"); |
| |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| |
| List<String> expected = Lists.newArrayList(null, R1, R2); |
| Ordering<String> regionOrdering = Ordering.natural(); |
| regionOrdering = nullsFirst ? regionOrdering.nullsFirst() : regionOrdering.nullsLast(); |
| Collections.sort(expected, regionOrdering); |
| |
| for (String region : expected) { |
| assertTrue(rs.next()); |
| assertEquals(region, rs.getString(1)); |
| } |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| /** |
| * Test to repro ArrayIndexOutOfBoundException that happens during filtering in BinarySubsetComparator |
| * only after a flush is performed |
| * @throws Exception |
| */ |
| @Test |
| public void testFilterOnTrailingKeyColumn() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| |
| Admin admin = null; |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| admin = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin(); |
| admin.flush(TableName.valueOf(SchemaUtil.getTableNameAsBytes(PRODUCT_METRICS_SCHEMA_NAME,tablename))); |
| String query = "SELECT SUM(TRANSACTIONS) FROM " + tablename + " WHERE FEATURE=?"; |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, F1); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(1200, rs.getInt(1)); |
| } finally { |
| if (admin != null) admin.close(); |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testFilterOnTrailingKeyColumn2() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT organization_id, \"DATE\", feature FROM "+tablename+" WHERE substr(organization_id,1,3)=? AND \"DATE\" > to_date(?)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId.substring(0,3)); |
| statement.setString(2, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(tenantId, rs.getString(1)); |
| assertEquals(D5.getTime(), rs.getDate(2).getTime()); |
| assertEquals(F3, rs.getString(3)); |
| assertTrue(rs.next()); |
| assertEquals(tenantId, rs.getString(1)); |
| assertEquals(D6, rs.getDate(2)); |
| assertEquals(F1, rs.getString(3)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSubstringNotEqual() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT organization_id, \"DATE\", feature FROM "+tablename+" WHERE organization_id=? AND \"DATE\" > to_date(?)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId.substring(0,3)); |
| statement.setString(2, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testKeyOrderedAggregation1() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT \"DATE\", sum(UNIQUE_USERS) FROM "+tablename+" WHERE \"DATE\" > to_date(?) GROUP BY organization_id, \"DATE\""; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(D5, rs.getDate(1)); |
| assertEquals(50, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(D6, rs.getDate(1)); |
| assertEquals(60, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testKeyOrderedAggregation2() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT \"DATE\", sum(UNIQUE_USERS) FROM "+tablename+" WHERE \"DATE\" < to_date(?) GROUP BY organization_id, \"DATE\""; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(D1, rs.getDate(1)); |
| assertEquals(10, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(D2, rs.getDate(1)); |
| assertEquals(20, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(D3, rs.getDate(1)); |
| assertEquals(30, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testKeyOrderedRoundAggregation1() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT round(\"DATE\",'HOUR'), sum(UNIQUE_USERS) FROM "+tablename+" WHERE \"DATE\" < to_date(?) GROUP BY organization_id, round(\"DATE\",'HOUR')"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(ROUND_1HR, rs.getDate(1)); |
| assertEquals(30, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(ROUND_2HR, rs.getDate(1)); |
| assertEquals(30, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testKeyOrderedRoundAggregation2() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT round(\"DATE\",'HOUR'), sum(UNIQUE_USERS) FROM "+tablename+" WHERE \"DATE\" <= to_date(?) GROUP BY organization_id, round(\"DATE\",'HOUR')"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| initTableValues(tablename, tenantId, getSplits(tenantId)); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, DS4); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(ROUND_1HR, rs.getDate(1)); |
| assertEquals(30, rs.getInt(2)); |
| assertTrue(rs.next()); |
| assertEquals(ROUND_2HR, rs.getDate(1)); |
| assertEquals(70, rs.getInt(2)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testEqualsRound() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and trunc(\"DATE\",'DAY')=?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| Date startDate = new Date(System.currentTimeMillis()); |
| Date equalDate = new Date((startDate.getTime() + 2 * QueryConstants.MILLIS_IN_DAY)/ QueryConstants.MILLIS_IN_DAY*QueryConstants.MILLIS_IN_DAY); |
| initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate, 1.0); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setDate(2, equalDate); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals("C", rs.getString(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testTruncateNotTraversableToFormScanKey() throws Exception { |
| String tablename=generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and TRUNC(\"DATE\",'DAY') <= ?"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| Date startDate = toDate("2013-01-01 00:00:00"); |
| initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate, 0.5); |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setString(1, tenantId); |
| statement.setDate(2, new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * 0.25))); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals("A", rs.getString(1)); |
| assertTrue(rs.next()); |
| assertEquals("B", rs.getString(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSaltedOrderBy() throws Exception { |
| String tablename=generateUniqueName(); |
| String ddl = "create table " + tablename + |
| " (organization_id char(15) not null," + |
| " \"DATE\" date not null," + |
| " feature char(1) not null," + |
| " unique_users integer not null,\n" + |
| " db_utilization decimal(31,10),\n" + |
| " transactions bigint,\n" + |
| " cpu_utilization decimal(31,10),\n" + |
| " response_time bigint,\n" + |
| " io_time bigint,\n" + |
| " region varchar,\n" + |
| " unset_column decimal(31,10)\n" + |
| " CONSTRAINT pk PRIMARY KEY (organization_id, \"DATE\", feature, unique_users)) salt_buckets=3"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| try(Connection conn = DriverManager.getConnection(getUrl(), props);) { |
| conn.createStatement().execute(ddl); |
| } |
| |
| String tenantId = getOrganizationId(); |
| Date startDate = new Date(System.currentTimeMillis()); |
| initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate); |
| // Add more date data |
| props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| try(Connection conn = DriverManager.getConnection(getUrl(), props);) { |
| initDateTableValues(tablename, conn, tenantId, new Date(startDate.getTime() + MILLIS_IN_DAY * 10), |
| 2.0); |
| initDateTableValues(tablename, conn, tenantId, new Date(startDate.getTime() + MILLIS_IN_DAY * 20), |
| 2.0); |
| conn.commit(); |
| } |
| |
| props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| try(Connection conn = DriverManager.getConnection(getUrl(), props);){ |
| PreparedStatement statement = conn.prepareStatement("SELECT count(1) FROM "+tablename+" WHERE organization_id = ?"); |
| statement.setString(1, tenantId); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(18, rs.getLong(1)); |
| |
| statement = conn.prepareStatement("SELECT \"DATE\" FROM "+tablename+" WHERE organization_id = ? order by \"DATE\" desc limit 10"); |
| statement.setString(1, tenantId); |
| rs = statement.executeQuery(); |
| Date date = null; |
| int count = 0; |
| while (rs.next()) { |
| if (date != null) { |
| assertTrue(date.getTime() >= rs.getDate(1).getTime()); |
| } |
| count++; |
| date = rs.getDate(1); |
| } |
| assertEquals(10,count); |
| } |
| } |
| |
| } |