You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2017/09/29 02:38:13 UTC
[16/26] phoenix git commit: PHOENIX-4248 Breakup IndexExpressionIT
into several integration tests so as not to create too many tables in one
test
http://git-wip-us.apache.org/repos/asf/phoenix/blob/ede28e00/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/ede28e00/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/ede28e00/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();
+ }
+ }
}