http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java deleted file mode 100644 index b540ad1..0000000 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java +++ /dev/null @@ -1,1194 +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.index; - -import static org.apache.phoenix.query.QueryConstants.MILLIS_IN_DAY; -import static org.apache.phoenix.util.TestUtil.ROW5; -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.assertNotNull; -import static org.junit.Assert.assertNull; -import static org.junit.Assert.assertTrue; -import static org.junit.Assert.fail; - -import java.io.IOException; -import java.math.BigDecimal; -import java.sql.Connection; -import java.sql.Date; -import java.sql.DriverManager; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Statement; -import java.util.Properties; -import java.util.Random; - -import org.apache.hadoop.hbase.Cell; -import org.apache.hadoop.hbase.CellScanner; -import org.apache.hadoop.hbase.HConstants; -import org.apache.hadoop.hbase.HTableDescriptor; -import org.apache.hadoop.hbase.KeyValue; -import org.apache.hadoop.hbase.client.HBaseAdmin; -import org.apache.hadoop.hbase.client.HTableInterface; -import org.apache.hadoop.hbase.client.Result; -import org.apache.hadoop.hbase.client.ResultScanner; -import org.apache.hadoop.hbase.client.Scan; -import org.apache.hadoop.hbase.ipc.PhoenixRpcSchedulerFactory; -import org.apache.phoenix.compile.ColumnResolver; -import org.apache.phoenix.compile.FromCompiler; -import org.apache.phoenix.end2end.ParallelStatsDisabledIT; -import org.apache.phoenix.exception.SQLExceptionCode; -import org.apache.phoenix.jdbc.PhoenixConnection; -import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData; -import org.apache.phoenix.jdbc.PhoenixResultSet; -import org.apache.phoenix.jdbc.PhoenixStatement; -import org.apache.phoenix.parse.NamedTableNode; -import org.apache.phoenix.parse.TableName; -import org.apache.phoenix.query.BaseTest; -import org.apache.phoenix.schema.PTable; -import org.apache.phoenix.schema.PTableImpl; -import org.apache.phoenix.schema.PTableKey; -import org.apache.phoenix.schema.PTableType; -import org.apache.phoenix.util.DateUtil; -import org.apache.phoenix.util.EnvironmentEdgeManager; -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.apache.phoenix.util.TransactionUtil; -import org.junit.Test; -import org.junit.runner.RunWith; -import org.junit.runners.Parameterized; - -@RunWith(Parameterized.class) -public abstract class IndexIT extends ParallelStatsDisabledIT { - private static final Random RAND = new Random(); - - private final boolean localIndex; - private final boolean transactional; - private final boolean mutable; - private final String tableDDLOptions; - - protected IndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) { - this.localIndex = localIndex; - this.transactional = transactional; - this.mutable = mutable; - StringBuilder optionBuilder = new StringBuilder(); - if (!columnEncoded) { - if (optionBuilder.length()!=0) - optionBuilder.append(","); - optionBuilder.append("COLUMN_ENCODED_BYTES=0"); - } - if (!mutable) { - if (optionBuilder.length()!=0) - optionBuilder.append(","); - optionBuilder.append("IMMUTABLE_ROWS=true"); - if (!columnEncoded) { - optionBuilder.append(",IMMUTABLE_STORAGE_SCHEME="+PTableImpl.ImmutableStorageScheme.ONE_CELL_PER_COLUMN); - } - } - if (transactional) { - if (optionBuilder.length()!=0) - optionBuilder.append(","); - optionBuilder.append(" TRANSACTIONAL=true "); - } - this.tableDDLOptions = optionBuilder.toString(); - } - - @Test - public void testIndexWithNullableFixedWithCols() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - BaseTest.populateTestTable(fullTableName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName - + " (char_col1 ASC, int_col1 ASC)" - + " INCLUDE (long_col1, long_col2)"; - stmt.execute(ddl); - - String query = "SELECT d.char_col1, int_col1 from " + fullTableName + " as d"; - ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if(localIndex) { - assertEquals( - "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1]\n" + - " SERVER FILTER BY FIRST KEY ONLY\n" + - "CLIENT MERGE SORT", - QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName + "\n" - + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("chara", rs.getString(1)); - assertEquals("chara", rs.getString("char_col1")); - assertEquals(2, rs.getInt(2)); - assertTrue(rs.next()); - assertEquals("chara", rs.getString(1)); - assertEquals(3, rs.getInt(2)); - assertTrue(rs.next()); - assertEquals("chara", rs.getString(1)); - assertEquals(4, rs.getInt(2)); - assertFalse(rs.next()); - - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName); - - query = "SELECT char_col1, int_col1 from " + fullTableName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - - query = "SELECT char_col1, int_col1 from "+indexName; - try{ - rs = conn.createStatement().executeQuery(query); - fail(); - } catch (SQLException e) { - assertEquals(SQLExceptionCode.TABLE_UNDEFINED.getErrorCode(), e.getErrorCode()); - } - } - } - - @Test - public void testDeleteFromAllPKColumnIndex() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - BaseTest.populateTestTable(fullTableName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName - + " (long_pk, varchar_pk)" - + " INCLUDE (long_col1, long_col2)"; - stmt.execute(ddl); - - ResultSet rs; - - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullTableName); - assertTrue(rs.next()); - assertEquals(3,rs.getInt(1)); - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexName); - assertTrue(rs.next()); - assertEquals(3,rs.getInt(1)); - - String dml = "DELETE from " + fullTableName + " WHERE long_col2 = 4"; - assertEquals(1,conn.createStatement().executeUpdate(dml)); - conn.commit(); - - String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + fullTableName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(3L, rs.getLong(1)); - assertFalse(rs.next()); - - query = "SELECT long_pk FROM " + fullTableName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(3L, rs.getLong(1)); - assertFalse(rs.next()); - - query = "SELECT * FROM " + fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(3L, rs.getLong(1)); - assertFalse(rs.next()); - - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName); - } - } - - @Test - public void testCreateIndexAfterUpsertStarted() throws Exception { - testCreateIndexAfterUpsertStarted(false, - SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName()), - SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName())); - } - - @Test - public void testCreateIndexAfterUpsertStartedTxnl() throws Exception { - if (transactional) { - testCreateIndexAfterUpsertStarted(true, - SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName()), - SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName())); - } - } - - private void testCreateIndexAfterUpsertStarted(boolean readOwnWrites, String fullTableName, String fullIndexName) throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - try (Connection conn1 = DriverManager.getConnection(getUrl(), props)) { - conn1.setAutoCommit(true); - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - Statement stmt1 = conn1.createStatement(); - stmt1.execute(ddl); - BaseTest.populateTestTable(fullTableName); - - ResultSet rs; - - rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullTableName); - assertTrue(rs.next()); - assertEquals(3,rs.getInt(1)); - - try (Connection conn2 = DriverManager.getConnection(getUrl(), props)) { - - String upsert = "UPSERT INTO " + fullTableName - + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; - PreparedStatement pstmt2 = conn2.prepareStatement(upsert); - pstmt2.setString(1, "varchar4"); - pstmt2.setString(2, "char4"); - pstmt2.setInt(3, 4); - pstmt2.setLong(4, 4L); - pstmt2.setBigDecimal(5, new BigDecimal(4.0)); - Date date = DateUtil.parseDate("2015-01-01 00:00:00"); - pstmt2.setDate(6, date); - pstmt2.setString(7, "varchar_a"); - pstmt2.setString(8, "chara"); - pstmt2.setInt(9, 2); - pstmt2.setLong(10, 2L); - pstmt2.setBigDecimal(11, new BigDecimal(2.0)); - pstmt2.setDate(12, date); - pstmt2.setString(13, "varchar_b"); - pstmt2.setString(14, "charb"); - pstmt2.setInt(15, 3); - pstmt2.setLong(16, 3L); - pstmt2.setBigDecimal(17, new BigDecimal(3.0)); - pstmt2.setDate(18, date); - pstmt2.executeUpdate(); - - if (readOwnWrites) { - String query = "SELECT long_pk FROM " + fullTableName + " WHERE long_pk=4"; - rs = conn2.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertFalse(rs.next()); - } - - String indexName = SchemaUtil.getTableNameFromFullName(fullIndexName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName - + " (long_pk, varchar_pk)" - + " INCLUDE (long_col1, long_col2)"; - stmt1.execute(ddl); - - /* - * Commit upsert after index created through different connection. - * This forces conn2 (which doesn't know about the index yet) to update the metadata - * at commit time, recognize the new index, and generate the correct metadata (or index - * rows for immutable indexes). - * - * For transactional data, this is problematic because the index - * gets a timestamp *after* the commit timestamp of conn2 and thus won't be seen during - * the commit. Also, when the index is being built, the data hasn't yet been committed - * and thus won't be part of the initial index build (fixed by PHOENIX-2446). - */ - conn2.commit(); - - stmt1 = conn1.createStatement(); - rs = stmt1.executeQuery("SELECT COUNT(*) FROM " + fullTableName); - assertTrue(rs.next()); - assertEquals(4,rs.getInt(1)); - assertEquals(fullIndexName, stmt1.unwrap(PhoenixStatement.class).getQueryPlan().getTableRef().getTable().getName().getString()); - - String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + fullTableName; - rs = conn1.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(2L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(3L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(4L, rs.getLong(1)); - assertFalse(rs.next()); - } - } - } - - @Test - public void testDeleteFromNonPKColumnIndex() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - BaseTest.populateTestTable(fullTableName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName - + " (long_col1, long_col2)" - + " INCLUDE (decimal_col1, decimal_col2)"; - stmt.execute(ddl); - } - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - ResultSet rs; - - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullTableName); - assertTrue(rs.next()); - assertEquals(3,rs.getInt(1)); - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexName); - assertTrue(rs.next()); - assertEquals(3,rs.getInt(1)); - - String dml = "DELETE from " + fullTableName + " WHERE long_col2 = 4"; - assertEquals(1,conn.createStatement().executeUpdate(dml)); - conn.commit(); - - // query the data table - String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + fullTableName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(3L, rs.getLong(1)); - assertFalse(rs.next()); - - // query the index table - query = "SELECT long_pk FROM " + fullTableName + " ORDER BY long_col1"; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertTrue(rs.next()); - assertEquals(3L, rs.getLong(1)); - assertFalse(rs.next()); - - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName); - } - } - - @Test - public void testGroupByCount() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - BaseTest.populateTestTable(fullTableName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col2)"; - stmt.execute(ddl); - ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT int_col2, COUNT(*) FROM " + fullTableName + " GROUP BY int_col2"); - assertTrue(rs.next()); - assertEquals(1,rs.getInt(2)); - } - } - - @Test - public void testSelectDistinctOnTableWithSecondaryImmutableIndex() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - BaseTest.populateTestTable(fullTableName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col2)"; - conn.createStatement().execute(ddl); - ResultSet rs = conn.createStatement().executeQuery("SELECT distinct int_col2 FROM " + fullTableName + " where int_col2 > 0"); - assertTrue(rs.next()); - assertEquals(3, rs.getInt(1)); - assertTrue(rs.next()); - assertEquals(4, rs.getInt(1)); - assertTrue(rs.next()); - assertEquals(5, rs.getInt(1)); - assertFalse(rs.next()); - } - } - - @Test - public void testInClauseWithIndexOnColumnOfUsignedIntType() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - BaseTest.populateTestTable(fullTableName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col1)"; - stmt.execute(ddl); - ResultSet rs = conn.createStatement().executeQuery("SELECT int_col1 FROM " + fullTableName + " where int_col1 IN (1, 2, 3, 4)"); - assertTrue(rs.next()); - assertEquals(2, rs.getInt(1)); - assertTrue(rs.next()); - assertEquals(3, rs.getInt(1)); - assertTrue(rs.next()); - assertEquals(4, rs.getInt(1)); - assertFalse(rs.next()); - } - } - - @Test - public void createIndexOnTableWithSpecifiedDefaultCF() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - String ddl ="CREATE TABLE " + fullTableName - + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) DEFAULT_COLUMN_FAMILY='A'" + (!tableDDLOptions.isEmpty() ? "," + tableDDLOptions : ""); - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - - query = "SELECT * FROM " + tableName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - String options = localIndex ? "SALT_BUCKETS=10, MULTI_TENANT=true, IMMUTABLE_ROWS=true, DISABLE_WAL=true" : ""; - conn.createStatement().execute( - "CREATE INDEX " + indexName + " ON " + fullTableName + " (v1) INCLUDE (v2) " + options); - query = "SELECT * FROM " + fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - //check options set correctly on index - TableName indexTableName = TableName.create(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - NamedTableNode indexNode = NamedTableNode.create(null, indexTableName, null); - ColumnResolver resolver = FromCompiler.getResolver(indexNode, conn.unwrap(PhoenixConnection.class)); - PTable indexTable = resolver.getTables().get(0).getTable(); - // Can't set IMMUTABLE_ROWS, MULTI_TENANT or DEFAULT_COLUMN_FAMILY_NAME on an index - assertNull(indexTable.getDefaultFamilyName()); - assertFalse(indexTable.isMultiTenant()); - assertEquals(mutable, !indexTable.isImmutableRows()); // Should match table - if(localIndex) { - assertEquals(10, indexTable.getBucketNum().intValue()); - assertTrue(indexTable.isWALDisabled()); - } - } - } - - @Test - public void testIndexWithNullableDateCol() throws Exception { - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - Date date = new Date(System.currentTimeMillis()); - - TestUtil.createMultiCFTestTable(conn, fullTableName, tableDDLOptions); - populateMultiCFTestTable(fullTableName, date); - String ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (date_col)"; - conn.createStatement().execute(ddl); - - String query = "SELECT int_pk from " + fullTableName ; - ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if (localIndex) { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullTableName +" [1]\n" - + " SERVER FILTER BY FIRST KEY ONLY\n" - + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" - + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(2, rs.getInt(1)); - assertTrue(rs.next()); - assertEquals(1, rs.getInt(1)); - assertTrue(rs.next()); - assertEquals(3, rs.getInt(1)); - assertFalse(rs.next()); - - query = "SELECT date_col from " + fullTableName + " order by date_col" ; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if (localIndex) { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullTableName + " [1]\n" - + " SERVER FILTER BY FIRST KEY ONLY\n" - + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" - + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(date, rs.getDate(1)); - assertTrue(rs.next()); - assertEquals(new Date(date.getTime() + MILLIS_IN_DAY), rs.getDate(1)); - assertTrue(rs.next()); - assertEquals(new Date(date.getTime() + 2 * MILLIS_IN_DAY), rs.getDate(1)); - assertFalse(rs.next()); - } - } - - @Test - public void testSelectAllAndAliasWithIndex() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - String ddl = "CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) " + tableDDLOptions; - conn.createStatement().execute(ddl); - query = "SELECT * FROM " + fullTableName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v2 DESC) INCLUDE (v1)"; - conn.createStatement().execute(ddl); - query = "SELECT * FROM " + fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); - stmt.setString(1,"a"); - stmt.setString(2, "x"); - stmt.setString(3, "1"); - stmt.execute(); - stmt.setString(1,"b"); - stmt.setString(2, "y"); - stmt.setString(3, "2"); - stmt.execute(); - conn.commit(); - - query = "SELECT * FROM " + fullTableName; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if(localIndex){ - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullTableName+" [1]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("b",rs.getString(1)); - assertEquals("y",rs.getString(2)); - assertEquals("2",rs.getString(3)); - assertEquals("b",rs.getString("k")); - assertEquals("y",rs.getString("v1")); - assertEquals("2",rs.getString("v2")); - assertTrue(rs.next()); - assertEquals("a",rs.getString(1)); - assertEquals("x",rs.getString(2)); - assertEquals("1",rs.getString(3)); - assertEquals("a",rs.getString("k")); - assertEquals("x",rs.getString("v1")); - assertEquals("1",rs.getString("v2")); - assertFalse(rs.next()); - - query = "SELECT v1 as foo FROM " + fullTableName + " WHERE v2 = '1' ORDER BY foo"; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if(localIndex){ - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +fullTableName + " [1,~'1']\n" + - " SERVER SORTED BY [\"V1\"]\n" + - "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +fullIndexName + " [~'1']\n" + - " SERVER SORTED BY [\"V1\"]\n" + - "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("x",rs.getString(1)); - assertEquals("x",rs.getString("foo")); - assertFalse(rs.next()); - } - } - - @Test - public void testSelectCF() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - String ddl = "CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, a.v1 VARCHAR, a.v2 VARCHAR, b.v1 VARCHAR) " + tableDDLOptions; - conn.createStatement().execute(ddl); - query = "SELECT * FROM " + fullTableName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v2 DESC) INCLUDE (a.v1)"; - conn.createStatement().execute(ddl); - query = "SELECT * FROM " + fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?,?)"); - stmt.setString(1,"a"); - stmt.setString(2, "x"); - stmt.setString(3, "1"); - stmt.setString(4, "A"); - stmt.execute(); - stmt.setString(1,"b"); - stmt.setString(2, "y"); - stmt.setString(3, "2"); - stmt.setString(4, "B"); - stmt.execute(); - conn.commit(); - - query = "SELECT * FROM " + fullTableName; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullTableName, QueryUtil.getExplainPlan(rs)); - - query = "SELECT a.* FROM " + fullTableName; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if(localIndex) { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullTableName+" [1]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); - } - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("y",rs.getString(1)); - assertEquals("2",rs.getString(2)); - assertEquals("y",rs.getString("v1")); - assertEquals("2",rs.getString("v2")); - assertTrue(rs.next()); - assertEquals("x",rs.getString(1)); - assertEquals("1",rs.getString(2)); - assertEquals("x",rs.getString("v1")); - assertEquals("1",rs.getString("v2")); - assertFalse(rs.next()); - } - } - - @Test - public void testUpsertAfterIndexDrop() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - // make sure that the tables are empty, but reachable - conn.createStatement().execute( - "CREATE TABLE " + fullTableName - + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions); - query = "SELECT * FROM " + fullTableName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - conn.createStatement().execute( - "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (v1, v2)"); - query = "SELECT * FROM " + fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - // load some data into the table - PreparedStatement stmt = - conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); - stmt.setString(1, "a"); - stmt.setString(2, "x"); - stmt.setString(3, "1"); - stmt.execute(); - conn.commit(); - - // make sure the index is working as expected - query = "SELECT * FROM " + fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("x", rs.getString(1)); - assertEquals("1", rs.getString(2)); - assertEquals("a", rs.getString(3)); - assertFalse(rs.next()); - - String ddl = "DROP INDEX " + indexName + " ON " + fullTableName; - conn.createStatement().execute(ddl); - - stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k, v1) VALUES(?,?)"); - stmt.setString(1, "a"); - stmt.setString(2, "y"); - stmt.execute(); - conn.commit(); - - query = "SELECT * FROM " + fullTableName; - - // check that the data table matches as expected - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("a", rs.getString(1)); - assertEquals("y", rs.getString(2)); - assertFalse(rs.next()); - } - } - - @Test - public void testMultipleUpdatesAcrossRegions() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - - String testTable = fullTableName+"_MULTIPLE_UPDATES"; - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - // make sure that the tables are empty, but reachable - conn.createStatement().execute( - "CREATE TABLE " + testTable - + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) " - + (!tableDDLOptions.isEmpty() ? tableDDLOptions : "") + " SPLIT ON ('b')"); - query = "SELECT * FROM " + testTable; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - conn.createStatement().execute( - "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + testTable + " (v1, v2)"); - query = "SELECT * FROM " + fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - // load some data into the table - PreparedStatement stmt = - conn.prepareStatement("UPSERT INTO " + testTable + " VALUES(?,?,?)"); - stmt.setString(1, "a"); - stmt.setString(2, "x"); - stmt.setString(3, "1"); - stmt.execute(); - stmt.setString(1, "b"); - stmt.setString(2, "y"); - stmt.setString(3, "2"); - stmt.execute(); - stmt.setString(1, "c"); - stmt.setString(2, "z"); - stmt.setString(3, "3"); - stmt.execute(); - conn.commit(); - - query = "SELECT /*+ NO_INDEX */ * FROM " + testTable; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("a", rs.getString(1)); - assertEquals("x", rs.getString(2)); - assertEquals("1", rs.getString(3)); - assertTrue(rs.next()); - assertEquals("b", rs.getString(1)); - assertEquals("y", rs.getString(2)); - assertEquals("2", rs.getString(3)); - assertTrue(rs.next()); - assertEquals("c", rs.getString(1)); - assertEquals("z", rs.getString(2)); - assertEquals("3", rs.getString(3)); - assertFalse(rs.next()); - - // make sure the index is working as expected - query = "SELECT * FROM " + testTable; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if (localIndex) { - assertEquals("CLIENT PARALLEL 2-WAY RANGE SCAN OVER " + testTable+" [1]\n" - + " SERVER FILTER BY FIRST KEY ONLY\n" - + "CLIENT MERGE SORT", - QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" - + " SERVER FILTER BY FIRST KEY ONLY", - QueryUtil.getExplainPlan(rs)); - } - - // check that the data table matches as expected - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("a", rs.getString(1)); - assertEquals("x", rs.getString(2)); - assertEquals("1", rs.getString(3)); - assertTrue(rs.next()); - assertEquals("b", rs.getString(1)); - assertEquals("y", rs.getString(2)); - assertEquals("2", rs.getString(3)); - assertTrue(rs.next()); - assertEquals("c", rs.getString(1)); - assertEquals("z", rs.getString(2)); - assertEquals("3", rs.getString(3)); - assertFalse(rs.next()); - } - } - - @Test - public void testIndexWithCaseSensitiveCols() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - conn.createStatement().execute("CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, \"V1\" VARCHAR, \"v2\" VARCHAR)"+tableDDLOptions); - query = "SELECT * FROM "+fullTableName; - rs = conn.createStatement().executeQuery(query); - long ts = conn.unwrap(PhoenixConnection.class).getTable(new PTableKey(null,fullTableName)).getTimeStamp(); - assertFalse(rs.next()); - conn.createStatement().execute( - "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + "(\"v2\") INCLUDE (\"V1\")"); - query = "SELECT * FROM "+fullIndexName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); - stmt.setString(1,"a"); - stmt.setString(2, "x"); - stmt.setString(3, "1"); - stmt.execute(); - stmt.setString(1,"b"); - stmt.setString(2, "y"); - stmt.setString(3, "2"); - stmt.execute(); - conn.commit(); - - query = "SELECT * FROM " + fullTableName + " WHERE \"v2\" = '1'"; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if(localIndex){ - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullTableName + " [1,'1']\n" - + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullIndexName + " ['1']", QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("a",rs.getString(1)); - assertEquals("x",rs.getString(2)); - assertEquals("1",rs.getString(3)); - assertEquals("a",rs.getString("k")); - assertEquals("x",rs.getString("V1")); - assertEquals("1",rs.getString("v2")); - assertFalse(rs.next()); - - query = "SELECT \"V1\", \"V1\" as foo1, \"v2\" as foo, \"v2\" as \"Foo1\", \"v2\" FROM " + fullTableName + " ORDER BY foo"; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if(localIndex){ - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullTableName + " [1]\nCLIENT MERGE SORT", - QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER "+fullIndexName, QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("x",rs.getString(1)); - assertEquals("x",rs.getString("V1")); - assertEquals("x",rs.getString(2)); - assertEquals("x",rs.getString("foo1")); - assertEquals("1",rs.getString(3)); - assertEquals("1",rs.getString("Foo")); - assertEquals("1",rs.getString(4)); - assertEquals("1",rs.getString("Foo1")); - assertEquals("1",rs.getString(5)); - assertEquals("1",rs.getString("v2")); - assertTrue(rs.next()); - assertEquals("y",rs.getString(1)); - assertEquals("y",rs.getString("V1")); - assertEquals("y",rs.getString(2)); - assertEquals("y",rs.getString("foo1")); - assertEquals("2",rs.getString(3)); - assertEquals("2",rs.getString("Foo")); - assertEquals("2",rs.getString(4)); - assertEquals("2",rs.getString("Foo1")); - assertEquals("2",rs.getString(5)); - assertEquals("2",rs.getString("v2")); - assertFalse(rs.next()); - - assertNoIndexDeletes(conn, ts, fullIndexName); - } - } - - private void assertNoIndexDeletes(Connection conn, long minTimestamp, String fullIndexName) throws IOException, SQLException { - if (!this.mutable) { - PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class); - PTable index = pconn.getTable(new PTableKey(null, fullIndexName)); - byte[] physicalIndexTable = index.getPhysicalName().getBytes(); - try (HTableInterface hIndex = pconn.getQueryServices().getTable(physicalIndexTable)) { - Scan scan = new Scan(); - scan.setRaw(true); - if (this.transactional) { - minTimestamp = TransactionUtil.convertToNanoseconds(minTimestamp); - } - scan.setTimeRange(minTimestamp, HConstants.LATEST_TIMESTAMP); - ResultScanner scanner = hIndex.getScanner(scan); - Result result; - while ((result = scanner.next()) != null) { - CellScanner cellScanner = result.cellScanner(); - while (cellScanner.advance()) { - Cell current = cellScanner.current(); - assertEquals (KeyValue.Type.Put.getCode(), current.getTypeByte()); - } - } - }; - } - } - - @Test - public void testInFilterOnIndexedTable() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - String ddl = "CREATE TABLE " + fullTableName +" (PK1 CHAR(2) NOT NULL PRIMARY KEY, CF1.COL1 BIGINT) " + tableDDLOptions; - conn.createStatement().execute(ddl); - ddl = "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + "(COL1)"; - conn.createStatement().execute(ddl); - - query = "SELECT COUNT(COL1) FROM " + fullTableName +" WHERE COL1 IN (1,25,50,75,100)"; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - } - } - - @Test - public void testIndexWithDecimalCol() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - String query; - ResultSet rs; - Date date = new Date(System.currentTimeMillis()); - - TestUtil.createMultiCFTestTable(conn, fullTableName, tableDDLOptions); - populateMultiCFTestTable(fullTableName, date); - String ddl = null; - ddl = "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (decimal_pk) INCLUDE (decimal_col1, decimal_col2)"; - conn.createStatement().execute(ddl); - - query = "SELECT decimal_pk, decimal_col1, decimal_col2 from " + fullTableName ; - rs = conn.createStatement().executeQuery("EXPLAIN " + query); - if(localIndex) { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullTableName+" [1]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); - } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); - } - - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(new BigDecimal("1.1"), rs.getBigDecimal(1)); - assertEquals(new BigDecimal("2.1"), rs.getBigDecimal(2)); - assertEquals(new BigDecimal("3.1"), rs.getBigDecimal(3)); - assertTrue(rs.next()); - assertEquals(new BigDecimal("2.2"), rs.getBigDecimal(1)); - assertEquals(new BigDecimal("3.2"), rs.getBigDecimal(2)); - assertEquals(new BigDecimal("4.2"), rs.getBigDecimal(3)); - assertTrue(rs.next()); - assertEquals(new BigDecimal("3.3"), rs.getBigDecimal(1)); - assertEquals(new BigDecimal("4.3"), rs.getBigDecimal(2)); - assertEquals(new BigDecimal("5.3"), rs.getBigDecimal(3)); - assertFalse(rs.next()); - } - } - - /** - * Ensure that HTD contains table priorities correctly. - */ - @Test - public void testTableDescriptorPriority() throws SQLException, IOException { - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - // Check system tables priorities. - try (HBaseAdmin admin = driver.getConnectionQueryServices(null, null).getAdmin(); - Connection c = DriverManager.getConnection(getUrl())) { - ResultSet rs = c.getMetaData().getTables("", - "\""+ PhoenixDatabaseMetaData.SYSTEM_CATALOG_SCHEMA + "\"", - null, - new String[] {PTableType.SYSTEM.toString()}); - ReadOnlyProps p = c.unwrap(PhoenixConnection.class).getQueryServices().getProps(); - while (rs.next()) { - String schemaName = rs.getString(PhoenixDatabaseMetaData.TABLE_SCHEM); - String tName = rs.getString(PhoenixDatabaseMetaData.TABLE_NAME); - org.apache.hadoop.hbase.TableName hbaseTableName = SchemaUtil.getPhysicalTableName(SchemaUtil.getTableName(schemaName, tName), p); - HTableDescriptor htd = admin.getTableDescriptor(hbaseTableName); - String val = htd.getValue("PRIORITY"); - assertNotNull("PRIORITY is not set for table:" + htd, val); - assertTrue(Integer.parseInt(val) - >= PhoenixRpcSchedulerFactory.getMetadataPriority(config)); - } - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String ddl ="CREATE TABLE " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions; - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - conn.setAutoCommit(false); - Statement stmt = conn.createStatement(); - stmt.execute(ddl); - BaseTest.populateTestTable(fullTableName); - ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName - + " ON " + fullTableName + " (long_col1, long_col2)" - + " INCLUDE (decimal_col1, decimal_col2)"; - stmt.execute(ddl); - } - - HTableDescriptor dataTable = admin.getTableDescriptor( - org.apache.hadoop.hbase.TableName.valueOf(fullTableName)); - String val = dataTable.getValue("PRIORITY"); - assertTrue(val == null || Integer.parseInt(val) < HConstants.HIGH_QOS); - - if (!localIndex && mutable) { - HTableDescriptor indexTable = admin.getTableDescriptor( - org.apache.hadoop.hbase.TableName.valueOf(indexName)); - val = indexTable.getValue("PRIORITY"); - assertNotNull("PRIORITY is not set for table:" + indexTable, val); - assertTrue(Integer.parseInt(val) >= PhoenixRpcSchedulerFactory.getIndexPriority(config)); - } - } - } - - @Test - public void testQueryBackToDataTableWithDescPKColumn() throws SQLException { - doTestQueryBackToDataTableWithDescPKColumn(true); - doTestQueryBackToDataTableWithDescPKColumn(false); - } - - private void doTestQueryBackToDataTableWithDescPKColumn(boolean isSecondPKDesc) throws SQLException { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - - try (Connection conn = DriverManager.getConnection(getUrl(), props)) { - // create data table and index table - conn.setAutoCommit(true); - Statement stmt = conn.createStatement(); - String ddl = "CREATE TABLE " + fullTableName + "(p1 integer not null, p2 integer not null, " + - " a integer, b integer CONSTRAINT PK PRIMARY KEY "; - if (isSecondPKDesc) { - ddl += "(p1, p2 desc))"; - } else { - ddl += "(p1 desc, p2))"; - } - stmt.executeUpdate(ddl); - ddl = "CREATE "+ (localIndex ? "LOCAL " : "") + " INDEX " + fullIndexName + " on " + fullTableName + "(a)"; - stmt.executeUpdate(ddl); - - // upsert a single row - String upsert = "UPSERT INTO " + fullTableName + " VALUES(1,2,3,4)"; - stmt.executeUpdate(upsert); - - // try select with index - // a = 3, should hit index table, but we select column B, so it will query back to data table - String query = "SELECT /*+index(" + fullTableName + " " + fullIndexName + "*/ b from " + fullTableName + - " WHERE a = 3"; - ResultSet rs = stmt.executeQuery(query); - assertTrue(rs.next()); - assertEquals(4, rs.getInt(1)); - assertFalse(rs.next()); - rs.close(); - stmt.close(); - } - } - - @Test - public void testReturnedTimestamp() throws Exception { - String tenantId = getOrganizationId(); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - String indexName = generateUniqueName(); - String tableName = - initATableValues(generateUniqueName(), tenantId, getDefaultSplits(tenantId), - new Date(System.currentTimeMillis()), null, getUrl(), tableDDLOptions); - String ddl = "CREATE "+ (localIndex ? "LOCAL " : "") + " INDEX " + indexName + " on " + tableName + "(A_STRING) INCLUDE (B_STRING)"; - conn.createStatement().executeUpdate(ddl); - String query = "SELECT ENTITY_ID,A_STRING,B_STRING FROM " + tableName + " WHERE organization_id=? and entity_id=?"; - - PreparedStatement statement = conn.prepareStatement(query); - statement.setString(1, tenantId); - - long currentTime = EnvironmentEdgeManager.currentTimeMillis(); - String entityId = mutable ? ROW5 : Integer.toString(Math.abs(RAND.nextInt() % 1000000000)); - PreparedStatement ddlStatement = conn.prepareStatement("UPSERT INTO " + tableName + "(ORGANIZATION_ID, ENTITY_ID,A_STRING) VALUES('" + tenantId + "',?,?)"); - ddlStatement.setString(1, entityId); - ddlStatement.setString(2, Integer.toString(Math.abs(RAND.nextInt() % 1000000000))); - ddlStatement.executeUpdate(); - conn.commit(); - - statement.setString(2, entityId); - ResultSet rs = statement.executeQuery(); - assertTrue (rs.next()); - assertTrue(rs.unwrap(PhoenixResultSet.class).getCurrentRow().getValue(0).getTimestamp() >= currentTime); - assertEquals(rs.getString(1).trim(), entityId); - assertFalse(rs.next()); - - currentTime = EnvironmentEdgeManager.currentTimeMillis(); - entityId = mutable ? ROW5 : Integer.toString(Math.abs(RAND.nextInt() % 1000000000)); - ddlStatement = conn.prepareStatement("UPSERT INTO " + tableName + "(ORGANIZATION_ID, ENTITY_ID,B_STRING) VALUES('" + tenantId + "',?,?)"); - ddlStatement.setString(1, entityId); - ddlStatement.setString(2, Integer.toString(Math.abs(RAND.nextInt() % 1000000000))); - ddlStatement.executeUpdate(); - conn.commit(); - - statement.setString(2, entityId); - rs = statement.executeQuery(); - assertTrue (rs.next()); - assertTrue(rs.unwrap(PhoenixResultSet.class).getCurrentRow().getValue(0).getTimestamp() >= currentTime); - assertEquals(rs.getString(1).trim(), entityId); - assertFalse(rs.next()); - - } finally { - conn.close(); - } - } - - -}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMaintenanceIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMaintenanceIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMaintenanceIT.java new file mode 100644 index 0000000..7d02e80 --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMaintenanceIT.java @@ -0,0 +1,457 @@ +/* + * 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.index; + +import static org.apache.phoenix.query.QueryConstants.MILLIS_IN_DAY; +import static org.apache.phoenix.util.TestUtil.INDEX_DATA_SCHEMA; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import 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.util.Properties; + +import org.apache.commons.lang.StringUtils; +import org.apache.phoenix.end2end.ParallelStatsDisabledIT; +import org.apache.phoenix.exception.SQLExceptionCode; +import org.apache.phoenix.query.QueryConstants; +import org.apache.phoenix.util.DateUtil; +import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.QueryUtil; +import org.apache.phoenix.util.TestUtil; +import org.junit.Test; + +public class IndexMaintenanceIT extends ParallelStatsDisabledIT { + + @Test + public void testImmutableIndexCreateAndUpdate() throws Exception { + helpTestCreateAndUpdate(false, false); + } + + @Test + public void testImmutableLocalIndexCreateAndUpdate() throws Exception { + helpTestCreateAndUpdate(false, true); + } + + @Test + public void testMutableIndexCreateAndUpdate() throws Exception { + helpTestCreateAndUpdate(true, false); + } + + @Test + public void testMutableLocalIndexCreateAndUpdate() throws Exception { + helpTestCreateAndUpdate(true, true); + } + + /** + * Adds a row to the index data table + * + * @param i + * row number + */ + private void insertRow(PreparedStatement stmt, int i) throws SQLException { + // insert row + stmt.setString(1, "varchar" + String.valueOf(i)); + stmt.setString(2, "char" + String.valueOf(i)); + stmt.setInt(3, i); + stmt.setLong(4, i); + stmt.setBigDecimal(5, new BigDecimal(i*0.5d)); + Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i - 1) * MILLIS_IN_DAY); + stmt.setDate(6, date); + stmt.setString(7, "a.varchar" + String.valueOf(i)); + stmt.setString(8, "a.char" + String.valueOf(i)); + stmt.setInt(9, i); + stmt.setLong(10, i); + stmt.setBigDecimal(11, new BigDecimal(i*0.5d)); + stmt.setDate(12, date); + stmt.setString(13, "b.varchar" + String.valueOf(i)); + stmt.setString(14, "b.char" + String.valueOf(i)); + stmt.setInt(15, i); + stmt.setLong(16, i); + stmt.setBigDecimal(17, new BigDecimal(i*0.5d)); + stmt.setDate(18, date); + stmt.executeUpdate(); + } + + private void verifyResult(ResultSet rs, int i) throws SQLException { + assertTrue(rs.next()); + assertEquals("VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("CHAR" + String.valueOf(i), 10, ' ') + + "_A.VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("B.CHAR" + String.valueOf(i), 10, ' '), + rs.getString(1)); + assertEquals(i * 3, rs.getInt(2)); + Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i) * MILLIS_IN_DAY); + assertEquals(date, rs.getDate(3)); + assertEquals(date, rs.getDate(4)); + assertEquals(date, rs.getDate(5)); + assertEquals("varchar" + String.valueOf(i), rs.getString(6)); + assertEquals("char" + String.valueOf(i), rs.getString(7)); + assertEquals(i, rs.getInt(8)); + assertEquals(i, rs.getLong(9)); + assertEquals(i*0.5d, rs.getDouble(10), 0.000001); + assertEquals(i, rs.getLong(11)); + assertEquals(i, rs.getLong(12)); + } + + private void createDataTable(Connection conn, String dataTableName, String tableProps) throws SQLException { + String tableDDL = "create table " + dataTableName + TestUtil.TEST_TABLE_SCHEMA + tableProps; + conn.createStatement().execute(tableDDL); + } + + private void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) throws Exception { + String dataTableName = generateUniqueName(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateUniqueName(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + conn.setAutoCommit(false); + createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true"); + populateDataTable(conn, fullDataTableName); + + // create an expression index + String ddl = "CREATE " + + (localIndex ? "LOCAL" : "") + + " INDEX " + indexName + " ON " + + fullDataTableName + + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2))," + + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )" + + " INCLUDE (long_col1, long_col2)"; + conn.createStatement().execute(ddl); + + // run select query with expression in WHERE clause + String whereSql = "SELECT long_col1, long_col2 from " + + fullDataTableName + + " WHERE UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2) = ?" + + " AND decimal_pk+int_pk+decimal_col2+int_col1=?" + // since a.date1 and b.date2 are NULLABLE and date is fixed width, these expressions are stored as + // DECIMAL in the index (which is not fixed width) + + " AND date_pk+1=? AND date1+1=? AND date2+1=?"; + PreparedStatement stmt = conn.prepareStatement(whereSql); + stmt.setString(1, "VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 "); + stmt.setInt(2, 3); + Date date = DateUtil.parseDate("2015-01-02 00:00:00"); + stmt.setDate(3, date); + stmt.setDate(4, date); + stmt.setDate(5, date); + + // verify that the query does a range scan on the index table + ResultSet rs = stmt.executeQuery("EXPLAIN " + whereSql); + assertEquals( + localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." + + dataTableName + + " [1,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT MERGE SORT" + : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." + indexName + " ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]", + QueryUtil.getExplainPlan(rs)); + + // verify that the correct results are returned + rs = stmt.executeQuery(); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertEquals(1, rs.getInt(2)); + assertFalse(rs.next()); + + // verify all rows in data table are present in index table + String indexSelectSql = "SELECT UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2), " + + "decimal_pk+int_pk+decimal_col2+int_col1, " + + "date_pk+1, date1+1, date2+1, " + + "varchar_pk, char_pk, int_pk, long_pk, decimal_pk, " + + "long_col1, long_col2 " + + "from " + + fullDataTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + indexSelectSql); + assertEquals(localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullDataTableName + + " [1]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST." + indexName, + QueryUtil.getExplainPlan(rs)); + rs = conn.createStatement().executeQuery(indexSelectSql); + verifyResult(rs, 1); + verifyResult(rs, 2); + + // Insert two more rows to the index data table + String upsert = "UPSERT INTO " + fullDataTableName + + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; + stmt = conn.prepareStatement(upsert); + insertRow(stmt, 3); + insertRow(stmt, 4); + conn.commit(); + + rs = conn.createStatement().executeQuery(indexSelectSql); + verifyResult(rs, 1); + verifyResult(rs, 2); + // verify that two rows added after index was created were also added to + // the index table + verifyResult(rs, 3); + verifyResult(rs, 4); + } finally { + conn.close(); + } + } + + @Test + public void testMutableIndexUpdate() throws Exception { + String dataTableName = generateUniqueName(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateUniqueName(); + helpTestUpdate(fullDataTableName, indexName, false); + } + + @Test + public void testMutableLocalIndexUpdate() throws Exception { + String dataTableName = generateUniqueName(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateUniqueName(); + helpTestUpdate(fullDataTableName, indexName, true); + } + + private void helpTestUpdate(String fullDataTableName, String indexName, boolean localIndex) throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + conn.setAutoCommit(false); + createDataTable(conn, fullDataTableName, ""); + populateDataTable(conn, fullDataTableName); + + // create an expression index + String ddl = "CREATE " + + (localIndex ? "LOCAL" : "") + + " INDEX " + indexName + " ON " + + fullDataTableName + + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2))," + + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )" + + " INCLUDE (long_col1, long_col2)"; + PreparedStatement stmt = conn.prepareStatement(ddl); + stmt.execute(); + + // update index pk column and covered column + String upsert = "UPSERT INTO " + + fullDataTableName + + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"; + + stmt = conn.prepareStatement(upsert); + stmt.setString(1, "varchar1"); + stmt.setString(2, "char1"); + stmt.setInt(3, 1); + stmt.setLong(4, 1l); + stmt.setBigDecimal(5, new BigDecimal(0.5)); + stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00")); + stmt.setString(7, "a.varchar_updated"); + stmt.setLong(8, 101); + stmt.executeUpdate(); + conn.commit(); + + // verify only one row was updated in the data table + String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from " + + fullDataTableName; + ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql); + assertTrue(rs.next()); + assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1 ", rs.getString(1)); + assertEquals(101, rs.getLong(2)); + assertTrue(rs.next()); + assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1)); + assertEquals(2, rs.getLong(2)); + assertFalse(rs.next()); + + // verify that the rows in the index table are also updated + rs = conn.createStatement().executeQuery("SELECT " + selectSql); + assertTrue(rs.next()); + assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1 ", rs.getString(1)); + assertEquals(101, rs.getLong(2)); + assertTrue(rs.next()); + assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1)); + assertEquals(2, rs.getLong(2)); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + + private void populateDataTable(Connection conn, String dataTable) throws SQLException { + String upsert = "UPSERT INTO " + dataTable + + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; + PreparedStatement stmt1 = conn.prepareStatement(upsert); + // insert two rows + insertRow(stmt1, 1); + insertRow(stmt1, 2); + conn.commit(); + } + + @Test + public void testDeleteIndexedExpressionImmutableIndex() throws Exception { + helpTestDeleteIndexedExpression(false, false); + } + + @Test + public void testDeleteIndexedExpressionImmutableLocalIndex() throws Exception { + helpTestDeleteIndexedExpression(false, true); + } + + @Test + public void testDeleteIndexedExpressionMutableIndex() throws Exception { + helpTestDeleteIndexedExpression(true, false); + } + + @Test + public void testDeleteIndexedExpressionMutableLocalIndex() throws Exception { + helpTestDeleteIndexedExpression(true, true); + } + + protected void helpTestDeleteIndexedExpression(boolean mutable, boolean localIndex) throws Exception { + String dataTableName = generateUniqueName(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateUniqueName(); + String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexName; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + conn.setAutoCommit(false); + createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true"); + populateDataTable(conn, fullDataTableName); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + + " (2*long_col2)"; + conn.createStatement().execute(ddl); + + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + + conn.setAutoCommit(true); + String dml = "DELETE from " + fullDataTableName + " WHERE long_col2 = 2"; + try { + conn.createStatement().execute(dml); + if (!mutable) { + fail(); + } + } catch (SQLException e) { + if (!mutable) { + assertEquals(SQLExceptionCode.INVALID_FILTER_ON_IMMUTABLE_ROWS.getErrorCode(), e.getErrorCode()); + } + } + + if (!mutable) { + dml = "DELETE from " + fullDataTableName + " WHERE 2*long_col2 = 4"; + conn.createStatement().execute(dml); + } + + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + } finally { + conn.close(); + } + } + + @Test + public void testDeleteCoveredColImmutableIndex() throws Exception { + helpTestDeleteCoveredCol(false, false); + } + + @Test + public void testDeleteCoveredColImmutableLocalIndex() throws Exception { + helpTestDeleteCoveredCol(false, true); + } + + @Test + public void testDeleteCoveredColMutableIndex() throws Exception { + helpTestDeleteCoveredCol(true, false); + } + + @Test + public void testDeleteCoveredColMutableLocalIndex() throws Exception { + helpTestDeleteCoveredCol(true, true); + } + + protected void helpTestDeleteCoveredCol(boolean mutable, boolean localIndex) throws Exception { + String dataTableName = generateUniqueName(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateUniqueName(); + String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexName; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + conn.setAutoCommit(false); + createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true"); + populateDataTable(conn, fullDataTableName); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + + " (long_pk, varchar_pk, 1+long_pk, UPPER(varchar_pk) )" + " INCLUDE (long_col1, long_col2)"; + conn.createStatement().execute(ddl); + + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + + String sql = "SELECT LONG_COL1 from " + fullDataTableName + " WHERE LONG_COL2 = 2"; + rs = conn.createStatement().executeQuery(sql); + assertTrue(rs.next()); + assertFalse(rs.next()); + + String dml = "DELETE from " + fullDataTableName + " WHERE long_col2 = 2"; + assertEquals(1, conn.createStatement().executeUpdate(dml)); + conn.commit(); + + String query = "SELECT /*+ NO_INDEX */ long_pk, varchar_pk, 1+long_pk, UPPER(varchar_pk) FROM " + + fullDataTableName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(1L, rs.getLong(1)); + assertEquals("varchar1", rs.getString(2)); + assertEquals(2L, rs.getLong(3)); + assertEquals("VARCHAR1", rs.getString(4)); + assertFalse(rs.next()); + + query = "SELECT long_pk, varchar_pk, 1+long_pk, UPPER(varchar_pk) FROM " + fullDataTableName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(1L, rs.getLong(1)); + assertEquals("varchar1", rs.getString(2)); + assertEquals(2L, rs.getLong(3)); + assertEquals("VARCHAR1", rs.getString(4)); + assertFalse(rs.next()); + + query = "SELECT * FROM " + fullIndexTableName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + + assertEquals(1L, rs.getLong(1)); + assertEquals("varchar1", rs.getString(2)); + assertEquals(2L, rs.getLong(3)); + assertEquals("VARCHAR1", rs.getString(4)); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMetadataIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMetadataIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMetadataIT.java index e9f0194..0ce36dd 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMetadataIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexMetadataIT.java @@ -607,4 +607,71 @@ public class IndexMetadataIT extends ParallelStatsDisabledIT { "order by table_name" ); assertFalse(rs.next()); } + + @Test + public void testImmutableTableOnlyHasPrimaryKeyIndex() throws Exception { + helpTestTableOnlyHasPrimaryKeyIndex(false, false); + } + + @Test + public void testImmutableLocalTableOnlyHasPrimaryKeyIndex() throws Exception { + helpTestTableOnlyHasPrimaryKeyIndex(false, true); + } + + @Test + public void testMutableTableOnlyHasPrimaryKeyIndex() throws Exception { + helpTestTableOnlyHasPrimaryKeyIndex(true, false); + } + + @Test + public void testMutableLocalTableOnlyHasPrimaryKeyIndex() throws Exception { + helpTestTableOnlyHasPrimaryKeyIndex(true, true); + } + + private void helpTestTableOnlyHasPrimaryKeyIndex(boolean mutable, + boolean localIndex) throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + String dataTableName = generateUniqueName(); + String indexName = generateUniqueName(); + try { + conn.createStatement().execute( + "CREATE TABLE " + dataTableName + " (" + + "pk1 VARCHAR not null, " + + "pk2 VARCHAR not null, " + + "CONSTRAINT PK PRIMARY KEY (pk1, pk2))" + + (!mutable ? "IMMUTABLE_ROWS=true" : "")); + String query = "SELECT * FROM " + dataTableName; + ResultSet rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + conn.createStatement().execute( + "CREATE " + (localIndex ? "LOCAL" : "") + + " INDEX " + indexName + " ON " + dataTableName + " (pk2, pk1)"); + query = "SELECT * FROM " + indexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)"); + stmt.setString(1, "k11"); + stmt.setString(2, "k21"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + indexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("k21", rs.getString(1)); + assertEquals("k11", rs.getString(2)); + assertFalse(rs.next()); + + query = "SELECT * FROM " + dataTableName + " WHERE pk2='k21'"; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("k11", rs.getString(1)); + assertEquals("k21", rs.getString(2)); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } }
