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)); + } + +}
