You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2016/09/20 18:05:59 UTC
[36/47] phoenix git commit: PHOENIX-3290 Move and/or combine as many
NeedsOwnCluster tests to bring down test run time
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
index d9a59a9..a3c36fa 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
@@ -69,7 +69,6 @@ public abstract class BaseViewIT extends BaseOwnClusterHBaseManagedTimeIT {
public static void doSetup() throws Exception {
Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
props.put(QueryServices.STATS_GUIDEPOST_WIDTH_BYTES_ATTRIB, Integer.toString(20));
- props.put(QueryServices.QUEUE_SIZE_ATTRIB, Integer.toString(1024));
props.put(QueryServices.TRANSACTIONS_ENABLED, Boolean.toString(true));
setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/CsvBulkLoadToolIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CsvBulkLoadToolIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CsvBulkLoadToolIT.java
index 8968555..4971fc3 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CsvBulkLoadToolIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CsvBulkLoadToolIT.java
@@ -40,7 +40,9 @@ import org.apache.phoenix.mapreduce.CsvBulkLoadTool;
import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.query.QueryServicesOptions;
import org.apache.phoenix.util.DateUtil;
+import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.TestUtil;
import org.junit.BeforeClass;
import org.junit.Test;
@@ -57,8 +59,8 @@ public class CsvBulkLoadToolIT extends BaseOwnClusterHBaseManagedTimeIT {
serverProps.put(QueryServices.EXTRA_JDBC_ARGUMENTS_ATTRIB, QueryServicesOptions.DEFAULT_EXTRA_JDBC_ARGUMENTS);
Map<String, String> clientProps = Maps.newHashMapWithExpectedSize(1);
clientProps.put(QueryServices.TRANSACTIONS_ENABLED, "true");
- setUpRealDriver(new ReadOnlyProps(serverProps.entrySet().iterator()), new ReadOnlyProps(clientProps.entrySet().iterator()));
- zkQuorum = "localhost:" + getUtility().getZkCluster().getClientPort();
+ setUpTestDriver(new ReadOnlyProps(serverProps.entrySet().iterator()), new ReadOnlyProps(clientProps.entrySet().iterator()));
+ zkQuorum = TestUtil.LOCALHOST + PhoenixRuntime.JDBC_PROTOCOL_SEPARATOR + getUtility().getZkCluster().getClientPort();
conn = DriverManager.getConnection(getUrl());
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexExtendedIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexExtendedIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexExtendedIT.java
new file mode 100644
index 0000000..b23e342
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexExtendedIT.java
@@ -0,0 +1,578 @@
+/*
+ * 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 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.Arrays;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+import java.util.UUID;
+
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.hbase.HRegionInfo;
+import org.apache.hadoop.hbase.MetaTableAccessor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.client.HBaseAdmin;
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.mapreduce.index.IndexTool;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.query.QueryServicesOptions;
+import org.apache.phoenix.util.ByteUtil;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.SchemaUtil;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+/**
+ * Tests for the {@link IndexTool}
+ */
+@RunWith(Parameterized.class)
+public class IndexExtendedIT extends BaseOwnClusterHBaseManagedTimeIT {
+ private final boolean localIndex;
+ private final boolean transactional;
+ private final boolean directApi;
+ private final String tableDDLOptions;
+ private final boolean mutable;
+
+ public IndexExtendedIT(boolean transactional, boolean mutable, boolean localIndex, boolean directApi) {
+ this.localIndex = localIndex;
+ this.transactional = transactional;
+ this.directApi = directApi;
+ this.mutable = mutable;
+ StringBuilder optionBuilder = new StringBuilder();
+ if (!mutable) {
+ optionBuilder.append(" IMMUTABLE_ROWS=true ");
+ }
+ if (transactional) {
+ if (!(optionBuilder.length()==0)) {
+ optionBuilder.append(",");
+ }
+ optionBuilder.append(" TRANSACTIONAL=true ");
+ }
+ optionBuilder.append(" SPLIT ON(1,2)");
+ this.tableDDLOptions = optionBuilder.toString();
+ }
+
+ @BeforeClass
+ public static void doSetup() throws Exception {
+ Map<String, String> serverProps = Maps.newHashMapWithExpectedSize(1);
+ serverProps.put(QueryServices.EXTRA_JDBC_ARGUMENTS_ATTRIB, QueryServicesOptions.DEFAULT_EXTRA_JDBC_ARGUMENTS);
+ Map<String, String> clientProps = Maps.newHashMapWithExpectedSize(1);
+ clientProps.put(QueryServices.TRANSACTIONS_ENABLED, Boolean.TRUE.toString());
+ setUpTestDriver(new ReadOnlyProps(serverProps.entrySet().iterator()), new ReadOnlyProps(clientProps.entrySet()
+ .iterator()));
+ }
+
+ @Parameters(name="transactional = {0} , mutable = {1} , localIndex = {2}, directApi = {3}")
+ public static Collection<Boolean[]> data() {
+ return Arrays.asList(new Boolean[][] {
+ { false, false, false, false }, { false, false, false, true }, { false, false, true, false }, { false, false, true, true },
+ { false, true, false, false }, { false, true, false, true }, { false, true, true, false }, { false, true, true, true },
+ { true, false, false, false }, { true, false, false, true }, { true, false, true, false }, { true, false, true, true },
+ { true, true, false, false }, { true, true, false, true }, { true, true, true, false }, { true, true, true, true }
+ });
+ }
+
+ /**
+ * This test is to assert that updates that happen to rows of a mutable table after an index is created in ASYNC mode and before
+ * the MR job runs, do show up in the index table .
+ * @throws Exception
+ */
+ @Test
+ public void testMutableIndexWithUpdates() throws Exception {
+ if (!mutable || transactional) {
+ return;
+ }
+ String schemaName = generateRandomString();
+ String dataTableName = generateRandomString();
+ String dataTableFullName = SchemaUtil.getTableName(schemaName, dataTableName);
+ String indexTableName = generateRandomString();
+ String indexTableFullName = SchemaUtil.getTableName(schemaName, indexTableName);
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ Statement stmt = conn.createStatement();
+ try {
+ stmt.execute(String.format("CREATE TABLE %s (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR, ZIP INTEGER)",dataTableFullName));
+ String upsertQuery = String.format("UPSERT INTO %s VALUES(?, ?, ?)",dataTableFullName);
+ PreparedStatement stmt1 = conn.prepareStatement(upsertQuery);
+
+ int id = 1;
+ // insert two rows
+ IndexExtendedIT.upsertRow(stmt1, id++);
+ IndexExtendedIT.upsertRow(stmt1, id++);
+ conn.commit();
+
+ stmt.execute(String.format("CREATE " + (localIndex ? "LOCAL" : "") + " INDEX %s ON %s (UPPER(NAME)) ASYNC ", indexTableName,dataTableFullName));
+
+ //update a row
+ stmt1.setInt(1, 1);
+ stmt1.setString(2, "uname" + String.valueOf(10));
+ stmt1.setInt(3, 95050 + 1);
+ stmt1.executeUpdate();
+ conn.commit();
+
+ //verify rows are fetched from data table.
+ String selectSql = String.format("SELECT ID FROM %s WHERE UPPER(NAME) ='UNAME2'",dataTableFullName);
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
+ String actualExplainPlan = QueryUtil.getExplainPlan(rs);
+
+ //assert we are pulling from data table.
+ assertEquals(String.format("CLIENT PARALLEL 1-WAY FULL SCAN OVER %s\n" +
+ " SERVER FILTER BY UPPER(NAME) = 'UNAME2'",dataTableFullName),actualExplainPlan);
+
+ rs = stmt1.executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertFalse(rs.next());
+
+ //run the index MR job.
+ runIndexTool(schemaName, dataTableName, indexTableName);
+
+ //assert we are pulling from index table.
+ rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
+ actualExplainPlan = QueryUtil.getExplainPlan(rs);
+ // TODO: why is it a 1-WAY parallel scan only for !transactional && mutable && localIndex
+ assertExplainPlan(actualExplainPlan, dataTableFullName, indexTableFullName);
+
+ rs = stmt.executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ private void runIndexTool(String schemaName, String dataTableName, String indexTableName) throws Exception {
+ IndexTool indexingTool = new IndexTool();
+ Configuration conf = new Configuration(getUtility().getConfiguration());
+ conf.set(QueryServices.TRANSACTIONS_ENABLED, Boolean.TRUE.toString());
+ indexingTool.setConf(conf);
+ final String[] cmdArgs = getArgValues(schemaName, dataTableName, indexTableName);
+ int status = indexingTool.run(cmdArgs);
+ assertEquals(0, status);
+ }
+
+ @Test
+ public void testSecondaryIndex() throws Exception {
+ String schemaName = generateRandomString();
+ String dataTableName = generateRandomString();
+ String dataTableFullName = SchemaUtil.getTableName(schemaName, dataTableName);
+ String indexTableName = generateRandomString();
+ String indexTableFullName = SchemaUtil.getTableName(schemaName, indexTableName);
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String stmString1 = "CREATE TABLE " + dataTableFullName + " (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR, ZIP INTEGER) " + tableDDLOptions;
+ conn.createStatement().execute(stmString1);
+ String upsertQuery = String.format("UPSERT INTO %s VALUES(?, ?, ?)", dataTableFullName);
+ PreparedStatement stmt1 = conn.prepareStatement(upsertQuery);
+
+ // insert two rows
+ upsertRow(stmt1, 1);
+ upsertRow(stmt1, 2);
+ conn.commit();
+
+ if (transactional) {
+ // insert two rows in another connection without committing so that they are not visible to other transactions
+ try (Connection conn2 = DriverManager.getConnection(getUrl(), props)) {
+ conn2.setAutoCommit(false);
+ PreparedStatement stmt2 = conn2.prepareStatement(upsertQuery);
+ upsertRow(stmt2, 5);
+ upsertRow(stmt2, 6);
+ ResultSet rs = conn.createStatement().executeQuery("SELECT count(*) from "+dataTableFullName);
+ assertTrue(rs.next());
+ assertEquals("Unexpected row count ", 2, rs.getInt(1));
+ assertFalse(rs.next());
+ rs = conn2.createStatement().executeQuery("SELECT count(*) from "+dataTableFullName);
+ assertTrue(rs.next());
+ assertEquals("Unexpected row count ", 4, rs.getInt(1));
+ assertFalse(rs.next());
+ }
+ }
+
+ String stmtString2 = String.format("CREATE %s INDEX %s ON %s (LPAD(UPPER(NAME),8,'x')||'_xyz') ASYNC ", (localIndex ? "LOCAL" : ""), indexTableName, dataTableFullName);
+ conn.createStatement().execute(stmtString2);
+
+ //verify rows are fetched from data table.
+ String selectSql = String.format("SELECT ID FROM %s WHERE LPAD(UPPER(NAME),8,'x')||'_xyz' = 'xxUNAME2_xyz'", dataTableFullName);
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
+ String actualExplainPlan = QueryUtil.getExplainPlan(rs);
+
+ //assert we are pulling from data table.
+ assertEquals(
+ String.format("CLIENT PARALLEL 1-WAY FULL SCAN OVER %s\n"
+ + " SERVER FILTER BY (LPAD(UPPER(NAME), 8, 'x') || '_xyz') = 'xxUNAME2_xyz'", dataTableFullName), actualExplainPlan);
+
+ rs = stmt1.executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertFalse(rs.next());
+ conn.commit();
+
+ //run the index MR job.
+ runIndexTool(schemaName, dataTableName, indexTableName);
+
+ // insert two more rows
+ upsertRow(stmt1, 3);
+ upsertRow(stmt1, 4);
+ conn.commit();
+
+ //assert we are pulling from index table.
+ rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
+ actualExplainPlan = QueryUtil.getExplainPlan(rs);
+ assertExplainPlan(actualExplainPlan, dataTableFullName, indexTableFullName);
+
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ private void assertExplainPlan(final String actualExplainPlan, String dataTableFullName, String indexTableFullName) {
+ String expectedExplainPlan;
+ if(localIndex) {
+ expectedExplainPlan = String.format(" RANGE SCAN OVER %s [1,",
+ dataTableFullName);
+ } else {
+ expectedExplainPlan = String.format(" RANGE SCAN OVER %s",
+ indexTableFullName);
+ }
+ assertTrue(actualExplainPlan + "\n expected to contain \n" + expectedExplainPlan, actualExplainPlan.contains(expectedExplainPlan));
+ }
+
+ private String[] getArgValues(String schemaName, String dataTable, String indxTable) {
+ final List<String> args = Lists.newArrayList();
+ if (schemaName!=null) {
+ args.add("-s");
+ args.add(schemaName);
+ }
+ args.add("-dt");
+ args.add(dataTable);
+ args.add("-it");
+ args.add(indxTable);
+ if(directApi) {
+ args.add("-direct");
+ // Need to run this job in foreground for the test to be deterministic
+ args.add("-runfg");
+ }
+
+ args.add("-op");
+ args.add("/tmp/"+UUID.randomUUID().toString());
+ return args.toArray(new String[0]);
+ }
+
+ private static void upsertRow(PreparedStatement stmt, int i) throws SQLException {
+ // insert row
+ stmt.setInt(1, i);
+ stmt.setString(2, "uname" + String.valueOf(i));
+ stmt.setInt(3, 95050 + i);
+ stmt.executeUpdate();
+ }
+
+ @Test
+ public void testDeleteFromImmutable() throws Exception {
+ if (transactional || mutable) {
+ return;
+ }
+ if (localIndex) { // TODO: remove this return once PHOENIX-3292 is fixed
+ return;
+ }
+ String schemaName = generateRandomString();
+ String dataTableName = generateRandomString();
+ String dataTableFullName = SchemaUtil.getTableName(schemaName, dataTableName);
+ String indexTableName = generateRandomString();
+ String indexTableFullName = SchemaUtil.getTableName(schemaName, indexTableName);
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.createStatement().execute("CREATE TABLE " + dataTableFullName + " (\n" +
+ " pk1 VARCHAR NOT NULL,\n" +
+ " pk2 VARCHAR NOT NULL,\n" +
+ " pk3 VARCHAR\n" +
+ " CONSTRAINT PK PRIMARY KEY \n" +
+ " (\n" +
+ " pk1,\n" +
+ " pk2,\n" +
+ " pk3\n" +
+ " )\n" +
+ " ) IMMUTABLE_ROWS=true");
+ conn.createStatement().execute("upsert into " + dataTableFullName + " (pk1, pk2, pk3) values ('a', '1', '1')");
+ conn.createStatement().execute("upsert into " + dataTableFullName + " (pk1, pk2, pk3) values ('b', '2', '2')");
+ conn.commit();
+ conn.createStatement().execute("CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexTableName + " ON " + dataTableFullName + " (pk3, pk2) ASYNC");
+
+ // this delete will be issued at a timestamp later than the above timestamp of the index table
+ conn.createStatement().execute("delete from " + dataTableFullName + " where pk1 = 'a'");
+ conn.commit();
+
+ //run the index MR job.
+ runIndexTool(schemaName, dataTableName, indexTableName);
+
+ // upsert two more rows
+ conn.createStatement().execute(
+ "upsert into " + dataTableFullName + " (pk1, pk2, pk3) values ('a', '3', '3')");
+ conn.createStatement().execute(
+ "upsert into " + dataTableFullName + " (pk1, pk2, pk3) values ('b', '4', '4')");
+ conn.commit();
+
+ // validate that delete markers were issued correctly and only ('a', '1', 'value1') was
+ // deleted
+ String query = "SELECT pk3 from " + dataTableFullName + " ORDER BY pk3";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ String expectedPlan =
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexTableFullName + "\n"
+ + " SERVER FILTER BY FIRST KEY ONLY";
+ assertEquals("Wrong plan ", expectedPlan, QueryUtil.getExplainPlan(rs));
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("2", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("3", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("4", rs.getString(1));
+ assertFalse(rs.next());
+ }
+ }
+
+ private Connection getConnectionForLocalIndexTest() throws SQLException{
+ Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
+ props.put(QueryServices.FORCE_ROW_KEY_ORDER_ATTRIB, Boolean.TRUE.toString());
+ return DriverManager.getConnection(getUrl(),props);
+ }
+
+ private void createBaseTable(String tableName, String splits) throws SQLException {
+ Connection conn = getConnectionForLocalIndexTest();
+ String ddl = "CREATE TABLE " + tableName + " (t_id VARCHAR NOT NULL,\n" +
+ "k1 INTEGER NOT NULL,\n" +
+ "k2 INTEGER NOT NULL,\n" +
+ "k3 INTEGER,\n" +
+ "v1 VARCHAR,\n" +
+ "CONSTRAINT pk PRIMARY KEY (t_id, k1, k2))\n"
+ + (splits != null ? (" split on " + splits) : "");
+ conn.createStatement().execute(ddl);
+ conn.close();
+ }
+
+ // Moved from LocalIndexIT because it was causing parallel runs to hang
+ @Test
+ public void testLocalIndexScanAfterRegionSplit() throws Exception {
+ // This test just needs be run once
+ if (!localIndex || transactional || mutable || directApi) {
+ return;
+ }
+ String schemaName = generateRandomString();
+ String tableName = schemaName + "." + generateRandomString();
+ String indexName = "IDX_" + generateRandomString();
+ TableName physicalTableName = SchemaUtil.getPhysicalTableName(tableName.getBytes(), false);
+ String indexPhysicalTableName = physicalTableName.getNameAsString();
+
+ createBaseTable(tableName, "('e','j','o')");
+ Connection conn1 = getConnectionForLocalIndexTest();
+ try{
+ String[] strings = {"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"};
+ for (int i = 0; i < 26; i++) {
+ conn1.createStatement().execute(
+ "UPSERT INTO " + tableName + " values('"+strings[i]+"'," + i + ","
+ + (i + 1) + "," + (i + 2) + ",'" + strings[25 - i] + "')");
+ }
+ conn1.commit();
+ conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)");
+ conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + "_2 ON " + tableName + "(k3)");
+
+ ResultSet rs = conn1.createStatement().executeQuery("SELECT * FROM " + tableName);
+ assertTrue(rs.next());
+
+ HBaseAdmin admin = conn1.unwrap(PhoenixConnection.class).getQueryServices().getAdmin();
+ for (int i = 1; i < 5; i++) {
+ admin.split(physicalTableName, ByteUtil.concat(Bytes.toBytes(strings[3*i])));
+ List<HRegionInfo> regionsOfUserTable =
+ MetaTableAccessor.getTableRegions(getUtility().getZooKeeperWatcher(), admin.getConnection(),
+ physicalTableName, false);
+
+ while (regionsOfUserTable.size() != (4+i)) {
+ Thread.sleep(100);
+ regionsOfUserTable = MetaTableAccessor.getTableRegions(getUtility().getZooKeeperWatcher(),
+ admin.getConnection(), physicalTableName, false);
+ }
+ assertEquals(4+i, regionsOfUserTable.size());
+ String[] tIdColumnValues = new String[26];
+ String[] v1ColumnValues = new String[26];
+ int[] k1ColumnValue = new int[26];
+ String query = "SELECT t_id,k1,v1 FROM " + tableName;
+ rs = conn1.createStatement().executeQuery(query);
+ Thread.sleep(1000);
+ for (int j = 0; j < 26; j++) {
+ assertTrue(rs.next());
+ tIdColumnValues[j] = rs.getString("t_id");
+ k1ColumnValue[j] = rs.getInt("k1");
+ v1ColumnValues[j] = rs.getString("V1");
+ }
+ Arrays.sort(tIdColumnValues);
+ Arrays.sort(v1ColumnValues);
+ Arrays.sort(k1ColumnValue);
+ assertTrue(Arrays.equals(strings, tIdColumnValues));
+ assertTrue(Arrays.equals(strings, v1ColumnValues));
+ for(int m=0;m<26;m++) {
+ assertEquals(m, k1ColumnValue[m]);
+ }
+
+ rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+ assertEquals(
+ "CLIENT PARALLEL " + (4 + i) + "-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+
+ query = "SELECT t_id,k1,k3 FROM " + tableName;
+ rs = conn1.createStatement().executeQuery("EXPLAIN "+query);
+ assertEquals(
+ "CLIENT PARALLEL "
+ + ((strings[3 * i].compareTo("j") < 0) ? (4 + i) : (4 + i - 1))
+ + "-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [2]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+ rs = conn1.createStatement().executeQuery(query);
+ Thread.sleep(1000);
+ int[] k3ColumnValue = new int[26];
+ for (int j = 0; j < 26; j++) {
+ assertTrue(rs.next());
+ tIdColumnValues[j] = rs.getString("t_id");
+ k1ColumnValue[j] = rs.getInt("k1");
+ k3ColumnValue[j] = rs.getInt("k3");
+ }
+ Arrays.sort(tIdColumnValues);
+ Arrays.sort(k1ColumnValue);
+ Arrays.sort(k3ColumnValue);
+ assertTrue(Arrays.equals(strings, tIdColumnValues));
+ for(int m=0;m<26;m++) {
+ assertEquals(m, k1ColumnValue[m]);
+ assertEquals(m+2, k3ColumnValue[m]);
+ }
+ }
+ } finally {
+ conn1.close();
+ }
+ }
+
+ // Moved from LocalIndexIT because it was causing parallel runs to hang
+ @Test
+ public void testLocalIndexScanAfterRegionsMerge() throws Exception {
+ // This test just needs be run once
+ if (!localIndex || transactional || mutable || directApi) {
+ return;
+ }
+ String schemaName = generateRandomString();
+ String tableName = schemaName + "." + generateRandomString();
+ String indexName = "IDX_" + generateRandomString();
+ TableName physicalTableName = SchemaUtil.getPhysicalTableName(tableName.getBytes(), false);
+ String indexPhysicalTableName = physicalTableName.getNameAsString();
+
+ createBaseTable(tableName, "('e','j','o')");
+ Connection conn1 = getConnectionForLocalIndexTest();
+ try{
+ String[] strings = {"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"};
+ for (int i = 0; i < 26; i++) {
+ conn1.createStatement().execute(
+ "UPSERT INTO " + tableName + " values('"+strings[i]+"'," + i + ","
+ + (i + 1) + "," + (i + 2) + ",'" + strings[25 - i] + "')");
+ }
+ conn1.commit();
+ conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)");
+ conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + "_2 ON " + tableName + "(k3)");
+
+ ResultSet rs = conn1.createStatement().executeQuery("SELECT * FROM " + tableName);
+ assertTrue(rs.next());
+
+ HBaseAdmin admin = conn1.unwrap(PhoenixConnection.class).getQueryServices().getAdmin();
+ List<HRegionInfo> regionsOfUserTable =
+ MetaTableAccessor.getTableRegions(getUtility().getZooKeeperWatcher(), admin.getConnection(),
+ physicalTableName, false);
+ admin.mergeRegions(regionsOfUserTable.get(0).getEncodedNameAsBytes(),
+ regionsOfUserTable.get(1).getEncodedNameAsBytes(), false);
+ regionsOfUserTable =
+ MetaTableAccessor.getTableRegions(getUtility().getZooKeeperWatcher(), admin.getConnection(),
+ physicalTableName, false);
+
+ while (regionsOfUserTable.size() != 3) {
+ Thread.sleep(100);
+ regionsOfUserTable = MetaTableAccessor.getTableRegions(getUtility().getZooKeeperWatcher(),
+ admin.getConnection(), physicalTableName, false);
+ }
+ String query = "SELECT t_id,k1,v1 FROM " + tableName;
+ rs = conn1.createStatement().executeQuery(query);
+ Thread.sleep(1000);
+ for (int j = 0; j < 26; j++) {
+ assertTrue(rs.next());
+ assertEquals(strings[25 - j], rs.getString("t_id"));
+ assertEquals(25 - j, rs.getInt("k1"));
+ assertEquals(strings[j], rs.getString("V1"));
+ }
+ rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+ assertEquals(
+ "CLIENT PARALLEL " + 3 + "-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName
+ + " [1]\n" + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+
+ query = "SELECT t_id,k1,k3 FROM " + tableName;
+ rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+ assertEquals(
+ "CLIENT PARALLEL " + 3 + "-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName
+ + " [2]\n" + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+
+ rs = conn1.createStatement().executeQuery(query);
+ Thread.sleep(1000);
+ for (int j = 0; j < 26; j++) {
+ assertTrue(rs.next());
+ assertEquals(strings[j], rs.getString("t_id"));
+ assertEquals(j, rs.getInt("k1"));
+ assertEquals(j + 2, rs.getInt("k3"));
+ }
+ } finally {
+ conn1.close();
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexToolIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexToolIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexToolIT.java
deleted file mode 100644
index 16db876..0000000
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/IndexToolIT.java
+++ /dev/null
@@ -1,257 +0,0 @@
-/*
- * 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 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.Arrays;
-import java.util.Collection;
-import java.util.List;
-import java.util.Map;
-import java.util.Properties;
-import java.util.UUID;
-
-import org.apache.hadoop.conf.Configuration;
-import org.apache.phoenix.mapreduce.index.IndexTool;
-import org.apache.phoenix.query.QueryServices;
-import org.apache.phoenix.query.QueryServicesOptions;
-import org.apache.phoenix.util.PropertiesUtil;
-import org.apache.phoenix.util.QueryUtil;
-import org.apache.phoenix.util.ReadOnlyProps;
-import org.apache.phoenix.util.SchemaUtil;
-import org.junit.BeforeClass;
-import org.junit.Test;
-import org.junit.runner.RunWith;
-import org.junit.runners.Parameterized;
-import org.junit.runners.Parameterized.Parameters;
-
-import com.google.common.collect.Lists;
-import com.google.common.collect.Maps;
-
-/**
- * Tests for the {@link IndexTool}
- */
-@RunWith(Parameterized.class)
-public class IndexToolIT extends BaseOwnClusterHBaseManagedTimeIT {
-
- private final String schemaName;
- private final String dataTable;
-
- private final boolean localIndex;
- private final boolean transactional;
- private final boolean directApi;
- private final String tableDDLOptions;
-
- public IndexToolIT(boolean transactional, boolean localIndex, boolean mutable, boolean directApi) {
- this.schemaName = "S";
- this.dataTable = "T" + (transactional ? "_TXN" : "");
- this.localIndex = localIndex;
- this.transactional = transactional;
- this.directApi = directApi;
- StringBuilder optionBuilder = new StringBuilder();
- if (!mutable)
- optionBuilder.append(" IMMUTABLE_ROWS=true ");
- if (transactional) {
- if (!(optionBuilder.length()==0))
- optionBuilder.append(",");
- optionBuilder.append(" TRANSACTIONAL=true ");
- }
- optionBuilder.append(" SPLIT ON(1,2)");
- this.tableDDLOptions = optionBuilder.toString();
- }
-
- @BeforeClass
- public static void doSetup() throws Exception {
- Map<String, String> serverProps = Maps.newHashMapWithExpectedSize(1);
- serverProps.put(QueryServices.EXTRA_JDBC_ARGUMENTS_ATTRIB, QueryServicesOptions.DEFAULT_EXTRA_JDBC_ARGUMENTS);
- Map<String, String> clientProps = Maps.newHashMapWithExpectedSize(1);
- clientProps.put(QueryServices.TRANSACTIONS_ENABLED, "true");
- setUpRealDriver(new ReadOnlyProps(serverProps.entrySet().iterator()), new ReadOnlyProps(clientProps.entrySet().iterator()));
- }
-
- @Parameters(name="transactional = {0} , mutable = {1} , localIndex = {2}, directApi = {3}")
- public static Collection<Boolean[]> data() {
- return Arrays.asList(new Boolean[][] {
- { false, false, false, false }, { false, false, false, true }, { false, false, true, false }, { false, false, true, true },
- { false, true, false, false }, { false, true, false, true }, { false, true, true, false }, { false, true, true, true },
- { true, false, false, false }, { true, false, false, true }, { true, false, true, false }, { true, false, true, true },
- { true, true, false, false }, { true, true, false, true }, { true, true, true, false }, { true, true, true, true }
- });
- }
-
- @Test
- public void testSecondaryIndex() throws Exception {
- final String fullTableName = SchemaUtil.getTableName(schemaName, dataTable);
- final String indxTable = String.format("%s_%s", dataTable, "INDX");
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- props.setProperty(QueryServices.TRANSACTIONS_ENABLED, Boolean.TRUE.toString());
- props.setProperty(QueryServices.EXPLAIN_ROW_COUNT_ATTRIB, Boolean.FALSE.toString());
- Connection conn = DriverManager.getConnection(getUrl(), props);
- Statement stmt = conn.createStatement();
- try {
-
- stmt.execute(String.format("CREATE TABLE %s (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR, ZIP INTEGER) %s", fullTableName, tableDDLOptions));
- String upsertQuery = String.format("UPSERT INTO %s VALUES(?, ?, ?)", fullTableName);
- PreparedStatement stmt1 = conn.prepareStatement(upsertQuery);
-
- // insert two rows
- upsertRow(stmt1, 1);
- upsertRow(stmt1, 2);
- conn.commit();
-
- if (transactional) {
- // insert two rows in another connection without committing so that they are not visible to other transactions
- try (Connection conn2 = DriverManager.getConnection(getUrl(), props)) {
- conn2.setAutoCommit(false);
- PreparedStatement stmt2 = conn2.prepareStatement(upsertQuery);
- upsertRow(stmt2, 5);
- upsertRow(stmt2, 6);
- ResultSet rs = conn.createStatement().executeQuery("SELECT count(*) from "+fullTableName);
- assertTrue(rs.next());
- assertEquals("Unexpected row count ", 2, rs.getInt(1));
- assertFalse(rs.next());
- rs = conn2.createStatement().executeQuery("SELECT count(*) from "+fullTableName);
- assertTrue(rs.next());
- assertEquals("Unexpected row count ", 4, rs.getInt(1));
- assertFalse(rs.next());
- }
- }
-
- stmt.execute(String.format("CREATE %s INDEX %s ON %s (LPAD(UPPER(NAME),8,'x')||'_xyz') ASYNC ", (localIndex ? "LOCAL" : ""), indxTable, fullTableName));
-
- //verify rows are fetched from data table.
- String selectSql = String.format("SELECT LPAD(UPPER(NAME),8,'x')||'_xyz',ID FROM %s", fullTableName);
- ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
- String actualExplainPlan = QueryUtil.getExplainPlan(rs);
-
- //assert we are pulling from data table.
- assertEquals(String.format("CLIENT 3-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER %s", fullTableName), actualExplainPlan);
-
- rs = stmt1.executeQuery(selectSql);
- assertTrue(rs.next());
- assertEquals("xxUNAME1_xyz", rs.getString(1));
- assertTrue(rs.next());
- assertEquals("xxUNAME2_xyz", rs.getString(1));
- assertFalse(rs.next());
- conn.commit();
-
- //run the index MR job.
- final IndexTool indexingTool = new IndexTool();
- Configuration conf = new Configuration(getUtility().getConfiguration());
- conf.set(QueryServices.TRANSACTIONS_ENABLED, Boolean.TRUE.toString());
- indexingTool.setConf(conf);
-
- final String[] cmdArgs = getArgValues(schemaName, dataTable, indxTable, directApi);
- int status = indexingTool.run(cmdArgs);
- assertEquals(0, status);
-
- // insert two more rows
- upsertRow(stmt1, 3);
- upsertRow(stmt1, 4);
- conn.commit();
-
- rs = stmt1.executeQuery("SELECT LPAD(UPPER(NAME),8,'x')||'_xyz',ID FROM "+fullTableName);
-
- //assert we are pulling from index table.
- rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
- actualExplainPlan = QueryUtil.getExplainPlan(rs);
- assertExplainPlan(actualExplainPlan, schemaName, dataTable, indxTable, localIndex);
-
- rs = stmt.executeQuery(selectSql);
- assertTrue(rs.next());
- assertEquals("xxUNAME1_xyz", rs.getString(1));
- assertEquals(1, rs.getInt(2));
-
- assertTrue(rs.next());
- assertEquals("xxUNAME2_xyz", rs.getString(1));
- assertEquals(2, rs.getInt(2));
-
- assertTrue(rs.next());
- assertEquals("xxUNAME3_xyz", rs.getString(1));
- assertEquals(3, rs.getInt(2));
-
- assertTrue(rs.next());
- assertEquals("xxUNAME4_xyz", rs.getString(1));
- assertEquals(4, rs.getInt(2));
-
- assertFalse(rs.next());
-
- conn.createStatement().execute(String.format("DROP INDEX %s ON %s",indxTable , fullTableName));
- } finally {
- conn.close();
- }
- }
-
- public static void assertExplainPlan(final String actualExplainPlan, String schemaName, String dataTable,
- String indxTable, boolean isLocal) {
-
- String expectedExplainPlan = "";
- if(isLocal) {
- final String localIndexName = SchemaUtil.getTableName(schemaName, dataTable);
- expectedExplainPlan = String.format("CLIENT 3-CHUNK PARALLEL 3-WAY ROUND ROBIN RANGE SCAN OVER %s [1]"
- + "\n SERVER FILTER BY FIRST KEY ONLY", localIndexName);
- } else {
- expectedExplainPlan = String.format("CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER %s"
- + "\n SERVER FILTER BY FIRST KEY ONLY",SchemaUtil.getTableName(schemaName, indxTable));
- }
- assertEquals(expectedExplainPlan,actualExplainPlan);
- }
-
- public static String[] getArgValues(String schemaName, String dataTable, String indxTable) {
- return getArgValues(schemaName, dataTable, indxTable, false);
- }
-
- public static String[] getArgValues(String schemaName, String dataTable, String indxTable, boolean directApi) {
- final List<String> args = Lists.newArrayList();
- if (schemaName!=null) {
- args.add("-s");
- args.add(schemaName);
- }
- args.add("-dt");
- args.add(dataTable);
- args.add("-it");
- args.add(indxTable);
- if(directApi) {
- args.add("-direct");
- // Need to run this job in foreground for the test to be deterministic
- args.add("-runfg");
- }
-
- args.add("-op");
- args.add("/tmp/"+UUID.randomUUID().toString());
- return args.toArray(new String[0]);
- }
-
- public static void upsertRow(PreparedStatement stmt, int i) throws SQLException {
- // insert row
- stmt.setInt(1, i);
- stmt.setString(2, "uname" + String.valueOf(i));
- stmt.setInt(3, 95050 + i);
- stmt.executeUpdate();
- }
-
-}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/KeyOnlyIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/KeyOnlyIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/KeyOnlyIT.java
index 4497c49..50e0709 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/KeyOnlyIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/KeyOnlyIT.java
@@ -52,8 +52,7 @@ public class KeyOnlyIT extends BaseOwnClusterClientManagedTimeIT {
public static void doSetup() throws Exception {
Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
// Must update config before starting server
- props.put(QueryServices.STATS_GUIDEPOST_WIDTH_BYTES_ATTRIB, Long.toString(50));
- props.put(QueryServices.QUEUE_SIZE_ATTRIB, Integer.toString(100));
+ props.put(QueryServices.STATS_GUIDEPOST_WIDTH_BYTES_ATTRIB, Long.toString(20));
setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
}
@@ -81,7 +80,7 @@ public class KeyOnlyIT extends BaseOwnClusterClientManagedTimeIT {
assertEquals(4, rs.getInt(2));
assertFalse(rs.next());
List<KeyRange> splits = getAllSplits(conn5, "KEYONLY");
- assertEquals(2, splits.size());
+ assertEquals(3, splits.size());
conn5.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts+60));
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/MultiCfQueryExecIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/MultiCfQueryExecIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/MultiCfQueryExecIT.java
index 15fc01d..b9d27ca 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/MultiCfQueryExecIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/MultiCfQueryExecIT.java
@@ -52,7 +52,6 @@ public class MultiCfQueryExecIT extends BaseOwnClusterClientManagedTimeIT {
Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
// Must update config before starting server
props.put(QueryServices.STATS_GUIDEPOST_WIDTH_BYTES_ATTRIB, Long.toString(20));
- props.put(QueryServices.QUEUE_SIZE_ATTRIB, Long.toString(200));
setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableIndexToolIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableIndexToolIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableIndexToolIT.java
deleted file mode 100644
index c335ff8..0000000
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableIndexToolIT.java
+++ /dev/null
@@ -1,129 +0,0 @@
-/*
- * 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.assertTrue;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.Statement;
-import java.util.Map;
-import java.util.Properties;
-
-import org.apache.hadoop.conf.Configuration;
-import org.apache.phoenix.mapreduce.index.IndexTool;
-import org.apache.phoenix.query.QueryServices;
-import org.apache.phoenix.query.QueryServicesOptions;
-import org.apache.phoenix.util.PropertiesUtil;
-import org.apache.phoenix.util.QueryUtil;
-import org.apache.phoenix.util.ReadOnlyProps;
-import org.junit.BeforeClass;
-import org.junit.Test;
-
-import com.google.common.collect.Maps;
-
-public class MutableIndexToolIT extends BaseOwnClusterHBaseManagedTimeIT {
-
- @BeforeClass
- public static void doSetup() throws Exception {
- Map<String, String> serverProps = Maps.newHashMapWithExpectedSize(1);
- serverProps.put(QueryServices.EXTRA_JDBC_ARGUMENTS_ATTRIB, QueryServicesOptions.DEFAULT_EXTRA_JDBC_ARGUMENTS);
- setUpRealDriver(new ReadOnlyProps(serverProps.entrySet().iterator()), ReadOnlyProps.EMPTY_PROPS);
- }
-
- /**
- * This test is to assert that updates that happen to rows of a mutable table after an index is created in ASYNC mode and before
- * the MR job runs, do show up in the index table .
- * @throws Exception
- */
- @Test
- public void testMutableIndexWithUpdates() throws Exception {
-
- final String dataTable = "DATA_TABLE5";
- final String indxTable = String.format("%s_%s",dataTable,"INDX");
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- props.setProperty(QueryServices.EXPLAIN_ROW_COUNT_ATTRIB, Boolean.FALSE.toString());
- Connection conn = DriverManager.getConnection(getUrl(), props);
- Statement stmt = conn.createStatement();
- try {
-
- stmt.execute(String.format("CREATE TABLE %s (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR, ZIP INTEGER)",dataTable));
- String upsertQuery = String.format("UPSERT INTO %s VALUES(?, ?, ?)",dataTable);
- PreparedStatement stmt1 = conn.prepareStatement(upsertQuery);
-
- int id = 1;
- // insert two rows
- IndexToolIT.upsertRow(stmt1, id++);
- IndexToolIT.upsertRow(stmt1, id++);
- conn.commit();
-
- stmt.execute(String.format("CREATE INDEX %s ON %s (UPPER(NAME)) ASYNC ", indxTable,dataTable));
-
- //update a row
- stmt1.setInt(1, 1);
- stmt1.setString(2, "uname" + String.valueOf(10));
- stmt1.setInt(3, 95050 + 1);
- stmt1.executeUpdate();
- conn.commit();
-
- //verify rows are fetched from data table.
- String selectSql = String.format("SELECT UPPER(NAME),ID FROM %s",dataTable);
- ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
- String actualExplainPlan = QueryUtil.getExplainPlan(rs);
-
- //assert we are pulling from data table.
- assertEquals(String.format("CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER %s",dataTable),actualExplainPlan);
-
- rs = stmt1.executeQuery(selectSql);
- assertTrue(rs.next());
- assertEquals("UNAME10", rs.getString(1));
- assertTrue(rs.next());
- assertEquals("UNAME2", rs.getString(1));
-
- //run the index MR job.
- final IndexTool indexingTool = new IndexTool();
- indexingTool.setConf(new Configuration(getUtility().getConfiguration()));
-
- final String[] cmdArgs = IndexToolIT.getArgValues(null, dataTable,indxTable);
- int status = indexingTool.run(cmdArgs);
- assertEquals(0, status);
-
- //assert we are pulling from index table.
- rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
- actualExplainPlan = QueryUtil.getExplainPlan(rs);
- IndexToolIT.assertExplainPlan(actualExplainPlan,null,dataTable,indxTable,false);
-
- rs = stmt.executeQuery(selectSql);
- assertTrue(rs.next());
- assertEquals("UNAME10", rs.getString(1));
- assertEquals(1, rs.getInt(2));
-
- assertTrue(rs.next());
- assertEquals("UNAME2", rs.getString(1));
- assertEquals(2, rs.getInt(2));
- conn.createStatement().execute(String.format("DROP INDEX %s ON %s",indxTable , dataTable));
- } finally {
- conn.close();
- }
- }
-
-}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/14d16c85/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFuncIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFuncIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFuncIT.java
new file mode 100644
index 0000000..a5251a9
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFuncIT.java
@@ -0,0 +1,683 @@
+/*
+ * 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.apache.phoenix.util.TestUtil.closeStmtAndConn;
+import static org.junit.Assert.assertEquals;
+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.Time;
+import java.sql.Timestamp;
+import java.util.Properties;
+
+import org.apache.phoenix.expression.function.CeilFunction;
+import org.apache.phoenix.expression.function.FloorFunction;
+import org.apache.phoenix.expression.function.RoundFunction;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.util.DateUtil;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Before;
+import org.junit.Test;
+
+import com.google.common.primitives.Doubles;
+import com.google.common.primitives.Floats;
+
+/**
+ *
+ * End to end tests for {@link RoundFunction}, {@link FloorFunction}, {@link CeilFunction}
+ *
+ */
+public class RoundFloorCeilFuncIT extends BaseHBaseManagedTimeTableReuseIT {
+ private static final long millisPart = 660;
+ private static final int nanosPart = 500100;
+ private static final BigDecimal decimalUpserted = BigDecimal.valueOf(1.264);
+ private static final double doubleUpserted = 1.264d;
+ private static final double unsignedDoubleUpserted = 1.264d;
+ private static final float floatUpserted = 1.264f;
+ private static final float unsignedFloatUpserted = 1.264f;
+
+ private String tableName;
+
+ @Before
+ public void initTable() throws Exception {
+ tableName = generateRandomString();
+ String testString = "abc";
+ Connection conn = null;
+ PreparedStatement stmt = null;
+ try {
+ conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS " + tableName
+ + " (s VARCHAR NOT NULL PRIMARY KEY, dt DATE, t TIME, ts TIMESTAMP, dec DECIMAL, doub DOUBLE, undoub UNSIGNED_DOUBLE, fl FLOAT, unfl UNSIGNED_FLOAT)";
+ conn.createStatement().execute(ddl);
+
+ Date dateUpserted = DateUtil.parseDate("2012-01-01 14:25:28");
+ dateUpserted = new Date(dateUpserted.getTime() + millisPart); // this makes the dateUpserted equivalent to 2012-01-01 14:25:28.660
+ long millis = dateUpserted.getTime();
+
+ Time timeUpserted = new Time(millis);
+ Timestamp tsUpserted = DateUtil.getTimestamp(millis, nanosPart);
+
+ stmt = conn.prepareStatement(
+ "UPSERT INTO " + tableName + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, testString);
+ stmt.setDate(2, dateUpserted);
+ stmt.setTime(3, timeUpserted);
+ stmt.setTimestamp(4, tsUpserted);
+ stmt.setBigDecimal(5, decimalUpserted);
+ stmt.setDouble(6, doubleUpserted);
+ stmt.setDouble(7, unsignedDoubleUpserted);
+ stmt.setFloat(8, floatUpserted);
+ stmt.setFloat(9, unsignedFloatUpserted);
+ stmt.executeUpdate();
+ conn.commit();
+ } finally {
+ closeStmtAndConn(stmt, conn);
+ }
+ }
+
+ @Test
+ public void testRoundingUpDate() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT ROUND(dt, 'day'), ROUND(dt, 'hour', 1), ROUND(dt, 'minute', 1), ROUND(dt, 'second', 1), "
+ + " ROUND(dt,'week'), ROUND(dt,'month') , ROUND(dt,'year') FROM " + tableName);
+ assertTrue(rs.next());
+ Date expectedDate = DateUtil.parseDate("2012-01-02 00:00:00");
+ assertEquals(expectedDate, rs.getDate(1));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:00:00");
+ assertEquals(expectedDate, rs.getDate(2));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:00");
+ assertEquals(expectedDate, rs.getDate(3));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:29");
+ assertEquals(expectedDate, rs.getDate(4));
+ expectedDate = DateUtil.parseDate("2012-01-02 00:00:00");
+ assertEquals(expectedDate, rs.getDate(5));
+ expectedDate = DateUtil.parseDate("2012-01-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(6));
+ expectedDate = DateUtil.parseDate("2012-01-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(7));
+ }
+
+ @Test
+ public void testRoundingUpDateInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ + " WHERE ROUND(dt, 'day') = to_date('2012-01-02 00:00:00')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testFloorDate() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT FLOOR(dt, 'day', 1), FLOOR(dt, 'hour', 1), FLOOR(dt, 'minute', 1), FLOOR(dt, 'second', 1),"
+ + " FLOOR(dt,'week'), FLOOR(dt,'month'), FLOOR(dt,'year') FROM " + tableName);
+ assertTrue(rs.next());
+ Date expectedDate = DateUtil.parseDate("2012-01-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(1));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:00:00");
+ assertEquals(expectedDate, rs.getDate(2));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:00");
+ assertEquals(expectedDate, rs.getDate(3));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:28");
+ assertEquals(expectedDate, rs.getDate(4));
+ expectedDate = DateUtil.parseDate("2011-12-26 00:00:00");
+ assertEquals(expectedDate, rs.getDate(5));
+ expectedDate = DateUtil.parseDate("2012-01-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(6));
+ expectedDate = DateUtil.parseDate("2012-01-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(7));
+ }
+
+ @Test
+ public void testFloorDateInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ + " WHERE FLOOR(dt, 'hour') = to_date('2012-01-01 14:00:00')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testCeilDate() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT CEIL(dt, 'day', 1), CEIL(dt, 'hour', 1), CEIL(dt, 'minute', 1), CEIL(dt, 'second', 1), "
+ + " CEIL(dt,'week') , CEIL(dt,'month') , CEIL(dt,'year') FROM " + tableName);
+ assertTrue(rs.next());
+ //Date upserted is 2012-01-01 14:25:28.660. So we will end up bumping up in every case.
+ Date expectedDate = DateUtil.parseDate("2012-01-02 00:00:00");
+ assertEquals(expectedDate, rs.getDate(1));
+ expectedDate = DateUtil.parseDate("2012-01-01 15:00:00");
+ assertEquals(expectedDate, rs.getDate(2));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:26:00");
+ assertEquals(expectedDate, rs.getDate(3));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:29");
+ assertEquals(expectedDate, rs.getDate(4));
+ expectedDate = DateUtil.parseDate("2012-01-02 00:00:00");
+ System.out.println(String.format(" the expected time is [%s] and the actual time is [%s]",expectedDate.getTime(),rs.getDate(5).getTime()));
+ assertEquals(expectedDate, rs.getDate(5));
+ expectedDate = DateUtil.parseDate("2012-02-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(6));
+ expectedDate = DateUtil.parseDate("2013-01-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(7));
+ }
+
+ @Test
+ public void testCeilDateInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ + " WHERE CEIL(dt, 'second') = to_date('2012-01-01 14:25:29')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testRoundingUpTimestamp() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ROUND(ts, 'day'), ROUND(ts, 'hour', 1), ROUND(ts, 'minute', 1), ROUND(ts, 'second', 1), ROUND(ts, 'millisecond', 1) FROM "
+ + tableName);
+ assertTrue(rs.next());
+ Timestamp expectedTimestamp;
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(1));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(2));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(3));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(4));
+
+ // Rounding of "2012-01-01 14:25:28.660" + nanosPart will end up bumping up the millisecond part of date.
+ // That is, it should be evaluated as "2012-01-01 14:25:28.661".
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime() + millisPart + 1);
+ assertEquals(expectedTimestamp, rs.getTimestamp(5));
+ }
+
+ @Test
+ public void testRoundingUpTimestampInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ + " WHERE ROUND(ts, 'second') = to_date('2012-01-01 14:25:29')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testFloorTimestamp() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT FLOOR(ts, 'day'), FLOOR(ts, 'hour', 1), FLOOR(ts, 'minute', 1), FLOOR(ts, 'second', 1), "
+ + " FLOOR(ts, 'millisecond', 1) , FLOOR(ts,'week') , FLOOR(ts,'month') FROM "
+ + tableName);
+ assertTrue(rs.next());
+ Timestamp expectedTimestamp;
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(1));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(2));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(3));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(4));
+
+ // FLOOR of "2012-01-01 14:25:28.660" + nanosPart will end up removing the nanos part.
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime() + millisPart);
+ assertEquals(expectedTimestamp, rs.getTimestamp(5));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2011-12-26 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(6));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(7));
+ }
+
+ @Test
+ public void testFloorTimestampInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ + " WHERE FLOOR(ts, 'second') = to_date('2012-01-01 14:25:28')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testWeekFloorTimestampInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ + " WHERE FLOOR(ts, 'week') = to_date('2011-12-26 00:00:00')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testCeilTimestamp() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT CEIL(ts, 'day'), CEIL(ts, 'hour', 1), CEIL(ts, 'minute', 1), CEIL(ts, 'second', 1), CEIL(ts, 'millisecond', 1),"
+ + " CEIL(ts,'week'), CEIL(ts,'month') , CEIL(ts,'year') FROM " + tableName);
+ assertTrue(rs.next());
+ Timestamp expectedTimestamp;
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(1));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 15:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(2));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:26:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(3));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(4));
+
+ // CEIL of "2012-01-01 14:25:28.660" + nanosPart will end up bumping up the millisecond part of date.
+ // That is, it should be evaluated as "2012-01-01 14:25:28.661".
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime() + millisPart + 1);
+ assertEquals(expectedTimestamp, rs.getTimestamp(5));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(6));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-02-01 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(7));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2013-01-01 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(8));
+ }
+
+ @Test
+ public void testCeilTimestampInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ + " WHERE CEIL(ts, 'second') = to_date('2012-01-01 14:25:29')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testRoundingUpTime() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT ROUND(t, 'day', 1), ROUND(t, 'hour', 1), ROUND(t, 'minute', 1), ROUND(t, 'second', 1),"
+ + " ROUND(t,'week') , ROUND(t,'month') , ROUND(t,'year') FROM " + tableName);
+ assertTrue(rs.next());
+ Time expectedTime = new Time(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(1));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(2));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTime, rs.getTime(3));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTime, rs.getTime(4));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(5));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(6));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(7));
+ }
+
+ @Test
+ public void testFloorTime() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT FLOOR(t, 'day', 1), FLOOR(t, 'hour', 1), FLOOR(t, 'minute', 1), FLOOR(t, 'second', 1), "
+ + " FLOOR(t, 'week'), FLOOR(t, 'month'), FLOOR(t, 'year') FROM " + tableName);
+ assertTrue(rs.next());
+ Time expectedTime = new Time(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(1));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(2));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTime, rs.getTime(3));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:28").getTime());
+ assertEquals(expectedTime, rs.getTime(4));
+ expectedTime = new Time(DateUtil.parseDate("2011-12-26 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(5));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(6));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(7));
+ }
+
+ @Test
+ public void testCeilTime() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT CEIL(t, 'day', 1), CEIL(t, 'hour', 1), CEIL(t, 'minute', 1), CEIL(t, 'second', 1),"
+ + " CEIL(t,'week') , CEIL(t,'month') , CEIL(t,'year') FROM " + tableName);
+ assertTrue(rs.next());
+ Time expectedTime = new Time(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(1));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 15:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(2));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:26:00").getTime());
+ assertEquals(expectedTime, rs.getTime(3));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTime, rs.getTime(4));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(5));
+ expectedTime = new Time(DateUtil.parseDate("2012-02-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(6));
+ expectedTime = new Time(DateUtil.parseDate("2013-01-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(7));
+ }
+
+ @Test
+ public void testRoundingUpDecimal() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ROUND(dec), ROUND(dec, 1), ROUND(dec, 2), ROUND(dec, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ BigDecimal expectedBd = BigDecimal.valueOf(1);
+ assertEquals(expectedBd, rs.getBigDecimal(1));
+ expectedBd = BigDecimal.valueOf(1.3);
+ assertEquals(expectedBd, rs.getBigDecimal(2));
+ expectedBd = BigDecimal.valueOf(1.26);
+ assertEquals(expectedBd, rs.getBigDecimal(3));
+ expectedBd = BigDecimal.valueOf(1.264);
+ assertEquals(expectedBd, rs.getBigDecimal(4));
+ }
+
+ @Test
+ public void testRoundingUpDecimalInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT * FROM " + tableName + " WHERE ROUND(dec, 2) = 1.26");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testFloorDecimal() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT FLOOR(dec), FLOOR(dec, 1), FLOOR(dec, 2), FLOOR(dec, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ BigDecimal expectedBd = BigDecimal.valueOf(1);
+ assertEquals(expectedBd, rs.getBigDecimal(1));
+ expectedBd = BigDecimal.valueOf(1.2);
+ assertEquals(expectedBd, rs.getBigDecimal(2));
+ expectedBd = BigDecimal.valueOf(1.26);
+ assertEquals(expectedBd, rs.getBigDecimal(3));
+ expectedBd = BigDecimal.valueOf(1.264);
+ assertEquals(expectedBd, rs.getBigDecimal(4));
+ }
+
+ @Test
+ public void testFloorDecimalInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT * FROM " + tableName + " WHERE FLOOR(dec, 2) = 1.26");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testCeilDecimal() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT CEIL(dec), CEIL(dec, 1), CEIL(dec, 2), CEIL(dec, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ BigDecimal expectedBd = BigDecimal.valueOf(2);
+ assertEquals(expectedBd, rs.getBigDecimal(1));
+ expectedBd = BigDecimal.valueOf(1.3);
+ assertEquals(expectedBd, rs.getBigDecimal(2));
+ expectedBd = BigDecimal.valueOf(1.27);
+ assertEquals(expectedBd, rs.getBigDecimal(3));
+ expectedBd = BigDecimal.valueOf(1.264);
+ assertEquals(expectedBd, rs.getBigDecimal(4));
+ }
+
+ @Test
+ public void testCeilDecimalInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT * FROM " + tableName + " WHERE CEIL(dec, 2) = 1.27");
+ assertTrue(rs.next());
+ }
+ @Test
+ public void testRoundingUpDouble() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ROUND(doub), ROUND(doub, 1), ROUND(doub, 2), ROUND(doub, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ assertEquals(0, Doubles.compare(1, rs.getDouble(1)));
+ assertEquals(0, Doubles.compare(1.3, rs.getDouble(2)));
+ assertEquals(0, Doubles.compare(1.26, rs.getDouble(3)));
+ assertEquals(0, Doubles.compare(1.264, rs.getDouble(4)));
+ }
+
+ @Test
+ public void testRoundingUpDoubleInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT * FROM " + tableName + " WHERE ROUND(dec, 2) = 1.26");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testCeilDouble() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT CEIL(doub), CEIL(doub, 1), CEIL(doub, 2), CEIL(doub, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ assertEquals(0, Doubles.compare(2, rs.getDouble(1)));
+ assertEquals(0, Doubles.compare(1.3, rs.getDouble(2)));
+ assertEquals(0, Doubles.compare(1.27, rs.getDouble(3)));
+ assertEquals(0, Doubles.compare(1.264, rs.getDouble(4)));
+ }
+
+ @Test
+ public void testCeilDoubleInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT * FROM " + tableName + " WHERE CEIL(doub, 2) = 1.27");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testFloorDouble() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT FLOOR(doub), FLOOR(doub, 1), FLOOR(doub, 2), FLOOR(doub, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ assertEquals(0, Doubles.compare(1, rs.getDouble(1)));
+ assertEquals(0, Doubles.compare(1.2, rs.getDouble(2)));
+ assertEquals(0, Doubles.compare(1.26, rs.getDouble(3)));
+ assertEquals(0, Doubles.compare(1.264, rs.getDouble(4)));
+ }
+
+ @Test
+ public void testFloorDoubleInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT * FROM " + tableName + " WHERE FLOOR(doub, 2) = 1.26");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testRoundFloat() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ROUND(fl), ROUND(fl, 1), ROUND(fl, 2), ROUND(fl, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ assertEquals(0, Floats.compare(1, rs.getFloat(1)));
+ assertEquals(0, Floats.compare(1.3f, rs.getFloat(2)));
+ assertEquals(0, Floats.compare(1.26f, rs.getFloat(3)));
+ assertEquals(0, Floats.compare(1.264f, rs.getFloat(4)));
+ }
+
+ @Test
+ public void testRoundUnsignedFloat() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ROUND(unfl), ROUND(unfl, 1), ROUND(unfl, 2), ROUND(unfl, 3) FROM " + tableName);
+ assertTrue(rs.next());
+ assertEquals(0, Floats.compare(1, rs.getFloat(1)));
+ assertEquals(0, Floats.compare(1.3f, rs.getFloat(2)));
+ assertEquals(0, Floats.compare(1.26f, rs.getFloat(3)));
+ assertEquals(0, Floats.compare(1.264f, rs.getFloat(4)));
+ }
+
+ @Test
+ public void testRoundUnsignedDouble() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ROUND(undoub), ROUND(undoub, 1), ROUND(undoub, 2), ROUND(undoub, 3) FROM "
+ + tableName);
+ assertTrue(rs.next());
+ assertEquals(0, Floats.compare(1, rs.getFloat(1)));
+ assertEquals(0, Floats.compare(1.3f, rs.getFloat(2)));
+ assertEquals(0, Floats.compare(1.26f, rs.getFloat(3)));
+ assertEquals(0, Floats.compare(1.264f, rs.getFloat(4)));
+ }
+
+ @Test
+ public void testTimestampAggregateFunctions() throws Exception {
+ String dateString = "2015-03-08 09:09:11.665";
+ Properties props = new Properties();
+ props.setProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB, "GMT+1");
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.prepareStatement(
+ "create table TIME_AGG_TABLE("
+ + "ID unsigned_int NOT NULL, "
+ + "THE_DATE TIMESTAMP, "
+ + "constraint PK primary key (ID))").execute();
+ PreparedStatement stmt = conn.prepareStatement("upsert into "
+ + "TIME_AGG_TABLE(" + " ID, " + " THE_DATE)"
+ + "VALUES (?, ?)");
+ stmt.setInt(1, 1);
+ stmt.setTimestamp(2, DateUtil.parseTimestamp(dateString));
+ stmt.execute();
+ conn.commit();
+
+ ResultSet rs = conn.prepareStatement(
+ "SELECT THE_DATE ,TRUNC(THE_DATE,'DAY') AS day_from_dt "
+ + ",TRUNC(THE_DATE,'HOUR') AS hour_from_dt "
+ + ",TRUNC(THE_DATE,'MINUTE') AS min_from_dt "
+ + ",TRUNC(THE_DATE,'SECOND') AS sec_from_dt "
+ + ",TRUNC(THE_DATE,'MILLISECOND') AS mil_from_dt "
+ + "FROM TIME_AGG_TABLE").executeQuery();
+ assertTrue(rs.next());
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("THE_DATE"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 00:00:00.0"),
+ rs.getTimestamp("day_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:00:00.0"),
+ rs.getTimestamp("hour_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:00.0"),
+ rs.getTimestamp("min_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.0"),
+ rs.getTimestamp("sec_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("mil_from_dt"));
+ rs.close();
+
+ rs = conn.prepareStatement(
+ "SELECT THE_DATE ,ROUND(THE_DATE,'DAY') AS day_from_dt "
+ + ",ROUND(THE_DATE,'HOUR') AS hour_from_dt "
+ + ",ROUND(THE_DATE,'MINUTE') AS min_from_dt "
+ + ",ROUND(THE_DATE,'SECOND') AS sec_from_dt "
+ + ",ROUND(THE_DATE,'MILLISECOND') AS mil_from_dt "
+ + "FROM TIME_AGG_TABLE").executeQuery();
+ assertTrue(rs.next());
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("THE_DATE"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 00:00:00.0"),
+ rs.getTimestamp("day_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:00:00.0"),
+ rs.getTimestamp("hour_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:00.0"),
+ rs.getTimestamp("min_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:12.0"),
+ rs.getTimestamp("sec_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("mil_from_dt"));
+ rs.close();
+
+ rs = conn.prepareStatement(
+ "SELECT THE_DATE ,FLOOR(THE_DATE,'DAY') AS day_from_dt "
+ + ",FLOOR(THE_DATE,'HOUR') AS hour_from_dt "
+ + ",FLOOR(THE_DATE,'MINUTE') AS min_from_dt "
+ + ",FLOOR(THE_DATE,'SECOND') AS sec_from_dt "
+ + ",FLOOR(THE_DATE,'MILLISECOND') AS mil_from_dt "
+ + "FROM TIME_AGG_TABLE").executeQuery();
+ assertTrue(rs.next());
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("THE_DATE"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 00:00:00.0"),
+ rs.getTimestamp("day_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:00:00.0"),
+ rs.getTimestamp("hour_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:00.0"),
+ rs.getTimestamp("min_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.0"),
+ rs.getTimestamp("sec_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("mil_from_dt"));
+ rs.close();
+
+ rs = conn.prepareStatement(
+ "SELECT THE_DATE ,CEIL(THE_DATE,'DAY') AS day_from_dt "
+ + ",CEIL(THE_DATE,'HOUR') AS hour_from_dt "
+ + ",CEIL(THE_DATE,'MINUTE') AS min_from_dt "
+ + ",CEIL(THE_DATE,'SECOND') AS sec_from_dt "
+ + ",CEIL(THE_DATE,'MILLISECOND') AS mil_from_dt "
+ + "FROM TIME_AGG_TABLE").executeQuery();
+ assertTrue(rs.next());
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("THE_DATE"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-09 00:00:00.0"),
+ rs.getTimestamp("day_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 10:00:00.0"),
+ rs.getTimestamp("hour_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:10:00.0"),
+ rs.getTimestamp("min_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:12.0"),
+ rs.getTimestamp("sec_from_dt"));
+ assertEquals(DateUtil.parseTimestamp("2015-03-08 09:09:11.665"),
+ rs.getTimestamp("mil_from_dt"));
+ rs.close();
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRoundOffFunction() throws SQLException {
+ long ts = nextTimestamp();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String ddl = "create table round_test(k bigint primary key)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into round_test values(1380603308885)");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select round(k/1000000,0) from round_test");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1380603, rs.getLong(1));
+
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
+ conn = DriverManager.getConnection(getUrl(), props);
+ stmt = conn.prepareStatement("select round(k/1000000,0) x from round_test group by x");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1380603, rs.getLong(1));
+ }
+
+}