PHOENIX-4248 Breakup IndexExpressionIT into several integration tests so as not to create too many tables in one test
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/3d9adc6f Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/3d9adc6f Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/3d9adc6f Branch: refs/heads/master Commit: 3d9adc6f3f298ab5077b8fddf9d17aa4e4a11e56 Parents: 834133a Author: James Taylor <[email protected]> Authored: Thu Sep 28 17:02:06 2017 -0700 Committer: James Taylor <[email protected]> Committed: Thu Sep 28 18:23:40 2017 -0700 ---------------------------------------------------------------------- .../phoenix/end2end/index/BaseIndexIT.java | 1194 ++++++++++++++++++ .../index/GlobalImmutableNonTxIndexIT.java | 2 +- .../end2end/index/GlobalImmutableTxIndexIT.java | 2 +- .../index/GlobalMutableNonTxIndexIT.java | 2 +- .../end2end/index/GlobalMutableTxIndexIT.java | 2 +- .../end2end/index/IndexExpressionIT.java | 15 - .../apache/phoenix/end2end/index/IndexIT.java | 1194 ------------------ .../end2end/index/IndexMaintenanceIT.java | 457 +++++++ .../phoenix/end2end/index/IndexMetadataIT.java | 67 + .../phoenix/end2end/index/IndexUsageIT.java | 775 ++++++++++++ .../index/IndexWithTableSchemaChangeIT.java | 375 ++++++ .../index/LocalImmutableNonTxIndexIT.java | 2 +- .../end2end/index/LocalImmutableTxIndexIT.java | 2 +- .../end2end/index/LocalMutableNonTxIndexIT.java | 2 +- .../end2end/index/LocalMutableTxIndexIT.java | 2 +- 15 files changed, 2876 insertions(+), 1217 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/BaseIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/BaseIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/BaseIndexIT.java new file mode 100644 index 0000000..049416c --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/BaseIndexIT.java @@ -0,0 +1,1194 @@ +/* + * 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 BaseIndexIT 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 BaseIndexIT(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/GlobalImmutableNonTxIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableNonTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableNonTxIndexIT.java index 52b219d..70ad73d 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableNonTxIndexIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableNonTxIndexIT.java @@ -22,7 +22,7 @@ import java.util.Collection; import org.junit.runners.Parameterized.Parameters; -public class GlobalImmutableNonTxIndexIT extends IndexIT { +public class GlobalImmutableNonTxIndexIT extends BaseIndexIT { public GlobalImmutableNonTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) { super(localIndex, mutable, transactional, columnEncoded); http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableTxIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableTxIndexIT.java index 58eab8c..1ba498c 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableTxIndexIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalImmutableTxIndexIT.java @@ -22,7 +22,7 @@ import java.util.Collection; import org.junit.runners.Parameterized.Parameters; -public class GlobalImmutableTxIndexIT extends IndexIT { +public class GlobalImmutableTxIndexIT extends BaseIndexIT { public GlobalImmutableTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) { super(localIndex, mutable, transactional, columnEncoded); http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableNonTxIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableNonTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableNonTxIndexIT.java index 9fe1938..f37182d 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableNonTxIndexIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableNonTxIndexIT.java @@ -22,7 +22,7 @@ import java.util.Collection; import org.junit.runners.Parameterized.Parameters; -public class GlobalMutableNonTxIndexIT extends IndexIT { +public class GlobalMutableNonTxIndexIT extends BaseIndexIT { public GlobalMutableNonTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) { super(localIndex, mutable, transactional, columnEncoded); http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableTxIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableTxIndexIT.java index 4536959..65ca2c5 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableTxIndexIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalMutableTxIndexIT.java @@ -22,7 +22,7 @@ import java.util.Collection; import org.junit.runners.Parameterized.Parameters; -public class GlobalMutableTxIndexIT extends IndexIT { +public class GlobalMutableTxIndexIT extends BaseIndexIT { public GlobalMutableTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) { super(localIndex, mutable, transactional, columnEncoded); http://git-wip-us.apache.org/repos/asf/phoenix/blob/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java index 2395b02..2578fe4 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java @@ -199,8 +199,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { // the index table verifyResult(rs, 3); verifyResult(rs, 4); - - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -279,7 +277,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1)); assertEquals(2, rs.getLong(2)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -362,7 +359,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -448,7 +444,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertEquals(2L, rs.getLong(3)); assertEquals("VARCHAR1", rs.getString(4)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -503,7 +498,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertTrue(rs.next()); assertEquals(1, rs.getInt(2)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -556,7 +550,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -606,7 +599,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -660,7 +652,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -769,7 +760,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertEquals("y_2",rs.getString(5)); assertEquals("y_2",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\"")); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + dataTableName); } finally { conn.close(); } @@ -824,7 +814,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { assertEquals(2, rs.getInt(1)); assertEquals(1, rs.getInt(2)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -859,7 +848,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { Connection conn = DriverManager.getConnection(getUrl(), props); String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; String indexName = generateUniqueName(); try { @@ -946,7 +934,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { ResultSet rs; PreparedStatement stmt; String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; String indexName = generateUniqueName(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); @@ -1058,7 +1045,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { PreparedStatement stmt; String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; String indexName = generateUniqueName(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); @@ -1181,7 +1167,6 @@ public class IndexExpressionIT extends ParallelStatsDisabledIT { private void helpTestUpdatableViewIndex(boolean local) throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; String indexName1 = generateUniqueName(); String viewName = generateUniqueName(); String indexName2 = generateUniqueName();
