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 01:24:53 UTC
[19/25] 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/3d9adc6f/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
new file mode 100644
index 0000000..14b569a
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
@@ -0,0 +1,775 @@
+/*
+ * 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.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.execute.CommitException;
+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 IndexUsageIT extends ParallelStatsDisabledIT {
+
+ /**
+ * 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 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 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 testGroupByCountImmutableIndex() throws Exception {
+ helpTestGroupByCount(false, false);
+ }
+
+ @Test
+ public void testGroupByCountImmutableLocalIndex() throws Exception {
+ helpTestGroupByCount(false, true);
+ }
+
+ @Test
+ public void testGroupByCountMutableIndex() throws Exception {
+ helpTestGroupByCount(true, false);
+ }
+
+ @Test
+ public void testGroupByCountMutableLocalIndex() throws Exception {
+ helpTestGroupByCount(true, true);
+ }
+
+ protected void helpTestGroupByCount(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);
+ String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+ + " (int_col1+int_col2)";
+ conn.createStatement().execute(ddl);
+
+ String groupBySql = "SELECT (int_col1+int_col2), COUNT(*) FROM " + fullDataTableName
+ + " GROUP BY (int_col1+int_col2)";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + groupBySql);
+ String expectedPlan = "CLIENT PARALLEL 1-WAY "
+ + (localIndex ? "RANGE SCAN OVER " + fullDataTableName + " [1]"
+ : "FULL SCAN OVER INDEX_TEST." + indexName)
+ + "\n SERVER FILTER BY FIRST KEY ONLY\n SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [TO_BIGINT(\"(A.INT_COL1 + B.INT_COL2)\")]"
+ + (localIndex ? "\nCLIENT MERGE SORT" : "");
+ assertEquals(expectedPlan, QueryUtil.getExplainPlan(rs));
+ rs = conn.createStatement().executeQuery(groupBySql);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(2));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testSelectDistinctImmutableIndex() throws Exception {
+ helpTestSelectDistinct(false, false);
+ }
+
+ @Test
+ public void testSelectDistinctImmutableIndexLocal() throws Exception {
+ helpTestSelectDistinct(false, true);
+ }
+
+ @Test
+ public void testSelectDistinctMutableIndex() throws Exception {
+ helpTestSelectDistinct(true, false);
+ }
+
+ @Test
+ public void testSelectDistinctMutableLocalIndex() throws Exception {
+ helpTestSelectDistinct(true, true);
+ }
+
+ protected void helpTestSelectDistinct(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);
+ String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+ + " (int_col1+1)";
+ conn.createStatement().execute(ddl);
+ String sql = "SELECT distinct int_col1+1 FROM " + fullDataTableName + " where int_col1+1 > 0";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+ String expectedPlan = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + (localIndex ? fullDataTableName + " [1,0] - [1,*]"
+ : "INDEX_TEST." + indexName + " [0] - [*]")
+ + "\n SERVER FILTER BY FIRST KEY ONLY\n SERVER DISTINCT PREFIX FILTER OVER [TO_BIGINT(\"(A.INT_COL1 + 1)\")]\n SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [TO_BIGINT(\"(A.INT_COL1 + 1)\")]"
+ + (localIndex ? "\nCLIENT MERGE SORT" : "");
+ assertEquals(expectedPlan, QueryUtil.getExplainPlan(rs));
+ rs = conn.createStatement().executeQuery(sql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testInClauseWithImmutableIndex() throws Exception {
+ helpTestInClauseWithIndex(false, false);
+ }
+
+ @Test
+ public void testInClauseWithImmutableLocalIndex() throws Exception {
+ helpTestInClauseWithIndex(false, true);
+ }
+
+ @Test
+ public void testInClauseWithMutableIndex() throws Exception {
+ helpTestInClauseWithIndex(true, false);
+ }
+
+ @Test
+ public void testInClauseWithMutableLocalIndex() throws Exception {
+ helpTestInClauseWithIndex(true, false);
+ }
+
+ protected void helpTestInClauseWithIndex(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);
+ String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+ + " (int_col1+1)";
+
+ conn.createStatement().execute(ddl);
+ String sql = "SELECT int_col1+1 FROM " + fullDataTableName + " where int_col1+1 IN (2)";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + (localIndex ? fullDataTableName + " [1,2]\n SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT"
+ : "INDEX_TEST." + indexName + " [2]\n SERVER FILTER BY FIRST KEY ONLY"), QueryUtil.getExplainPlan(rs));
+ rs = conn.createStatement().executeQuery(sql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testOrderByWithImmutableIndex() throws Exception {
+ helpTestSelectAliasAndOrderByWithIndex(false, false);
+ }
+
+ @Test
+ public void testOrderByWithImmutableLocalIndex() throws Exception {
+ helpTestSelectAliasAndOrderByWithIndex(false, true);
+ }
+
+ @Test
+ public void testOrderByWithMutableIndex() throws Exception {
+ helpTestSelectAliasAndOrderByWithIndex(true, false);
+ }
+
+ @Test
+ public void testOrderByWithMutableLocalIndex() throws Exception {
+ helpTestSelectAliasAndOrderByWithIndex(true, false);
+ }
+
+ protected void helpTestSelectAliasAndOrderByWithIndex(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);
+ String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+ + " (int_col1+1)";
+
+ conn.createStatement().execute(ddl);
+ String sql = "SELECT int_col1+1 AS foo FROM " + fullDataTableName + " ORDER BY foo";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+ assertEquals("CLIENT PARALLEL 1-WAY "
+ + (localIndex ? "RANGE SCAN OVER " + fullDataTableName
+ + " [1]\n SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT"
+ : "FULL SCAN OVER INDEX_TEST." + indexName + "\n SERVER FILTER BY FIRST KEY ONLY"),
+ QueryUtil.getExplainPlan(rs));
+ rs = conn.createStatement().executeQuery(sql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testImmutableIndexWithCaseSensitiveCols() throws Exception {
+ helpTestIndexWithCaseSensitiveCols(false, false);
+ }
+
+ @Test
+ public void testImmutableLocalIndexWithCaseSensitiveCols() throws Exception {
+ helpTestIndexWithCaseSensitiveCols(false, true);
+ }
+
+ @Test
+ public void testMutableIndexWithCaseSensitiveCols() throws Exception {
+ helpTestIndexWithCaseSensitiveCols(true, false);
+ }
+
+ @Test
+ public void testMutableLocalIndexWithCaseSensitiveCols() throws Exception {
+ helpTestIndexWithCaseSensitiveCols(true, true);
+ }
+
+ protected void helpTestIndexWithCaseSensitiveCols(boolean mutable, boolean localIndex) throws Exception {
+ String dataTableName = generateUniqueName();
+ String indexName = generateUniqueName();
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.createStatement().execute("CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, \"cf1\".\"V1\" VARCHAR, \"CF2\".\"v2\" VARCHAR) "+ (mutable ? "IMMUTABLE_ROWS=true" : ""));
+ String query = "SELECT * FROM " + dataTableName;
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+ String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (\"cf1\".\"V1\" || '_' || \"CF2\".\"v2\") INCLUDE (\"V1\",\"v2\")";
+ conn.createStatement().execute(ddl);
+ query = "SELECT * FROM " + indexName;
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " 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 (\"V1\" || '_' || \"v2\"), k, \"V1\", \"v2\" FROM " + dataTableName + " WHERE (\"V1\" || '_' || \"v2\") = 'x_1'";
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ if(localIndex){
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,'x_1']\n"
+ + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+ } else {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['x_1']", QueryUtil.getExplainPlan(rs));
+ }
+
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("x_1",rs.getString(1));
+ assertEquals("a",rs.getString(2));
+ assertEquals("x",rs.getString(3));
+ assertEquals("1",rs.getString(4));
+ //TODO figure out why this " " is needed
+ assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\""));
+ assertEquals("a",rs.getString("k"));
+ assertEquals("x",rs.getString("V1"));
+ assertEquals("1",rs.getString("v2"));
+ assertFalse(rs.next());
+
+ query = "SELECT \"V1\", \"V1\" as foo1, (\"V1\" || '_' || \"v2\") as foo, (\"V1\" || '_' || \"v2\") as \"Foo1\", (\"V1\" || '_' || \"v2\") FROM " + dataTableName + " ORDER BY foo";
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ if(localIndex){
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1]\nCLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ } else {
+ assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName, 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("x_1",rs.getString(3));
+ assertEquals("x_1",rs.getString("Foo"));
+ assertEquals("x_1",rs.getString(4));
+ assertEquals("x_1",rs.getString("Foo1"));
+ assertEquals("x_1",rs.getString(5));
+ assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'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("y_2",rs.getString(3));
+ assertEquals("y_2",rs.getString("Foo"));
+ assertEquals("y_2",rs.getString(4));
+ assertEquals("y_2",rs.getString("Foo1"));
+ assertEquals("y_2",rs.getString(5));
+ assertEquals("y_2",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\""));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testSelectColOnlyInDataTableImmutableIndex() throws Exception {
+ helpTestSelectColOnlyInDataTable(false, false);
+ }
+
+ @Test
+ public void testSelectColOnlyInDataTableImmutableLocalIndex() throws Exception {
+ helpTestSelectColOnlyInDataTable(false, true);
+ }
+
+ @Test
+ public void testSelectColOnlyInDataTableMutableIndex() throws Exception {
+ helpTestSelectColOnlyInDataTable(true, false);
+ }
+
+ @Test
+ public void testSelectColOnlyInDataTableMutableLocalIndex() throws Exception {
+ helpTestSelectColOnlyInDataTable(true, true);
+ }
+
+ protected void helpTestSelectColOnlyInDataTable(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);
+ String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+ + " (int_col1+1)";
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ conn.setAutoCommit(false);
+ conn.createStatement().execute(ddl);
+ String sql = "SELECT int_col1+1, int_col2 FROM " + fullDataTableName + " WHERE int_col1+1=2";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+ assertEquals("CLIENT PARALLEL 1-WAY "
+ + (localIndex ? "RANGE SCAN OVER " + fullDataTableName
+ + " [1,2]\n SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT" : "FULL SCAN OVER "
+ + fullDataTableName + "\n SERVER FILTER BY (A.INT_COL1 + 1) = 2"),
+ QueryUtil.getExplainPlan(rs));
+ rs = conn.createStatement().executeQuery(sql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals(1, rs.getInt(2));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testUpdatableViewWithIndex() throws Exception {
+ helpTestUpdatableViewIndex(false);
+ }
+
+ @Test
+ public void testUpdatableViewWithLocalIndex() throws Exception {
+ helpTestUpdatableViewIndex(true);
+ }
+
+ private void helpTestUpdatableViewIndex(boolean local) throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String dataTableName = generateUniqueName();
+ String indexName1 = generateUniqueName();
+ String viewName = generateUniqueName();
+ String indexName2 = generateUniqueName();
+ try {
+ String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, k3 DECIMAL, s1 VARCHAR, s2 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2, k3))";
+ conn.createStatement().execute(ddl);
+ ddl = "CREATE VIEW " + viewName + " AS SELECT * FROM " + dataTableName + " WHERE k1 = 1";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(120,'foo0','bar0',50.0)");
+ conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(121,'foo1','bar1',51.0)");
+ conn.commit();
+
+ ResultSet rs;
+ conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX " + indexName1 + " on " + viewName + "(k1+k2+k3) include (s1, s2)");
+ conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(120,'foo2','bar2',50.0)");
+ conn.commit();
+
+ String query = "SELECT k1, k2, k3, s1, s2 FROM " + viewName + " WHERE k1+k2+k3 = 173.0";
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ String queryPlan = QueryUtil.getExplainPlan(rs);
+ if (local) {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,173]\n" + "CLIENT MERGE SORT",
+ queryPlan);
+ } else {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_" + dataTableName + " [" + Short.MIN_VALUE + ",173]", queryPlan);
+ }
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(121, rs.getInt(2));
+ assertTrue(BigDecimal.valueOf(51.0).compareTo(rs.getBigDecimal(3))==0);
+ assertEquals("foo1", rs.getString(4));
+ assertEquals("bar1", rs.getString(5));
+ assertFalse(rs.next());
+
+ conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX " + indexName2 + " on " + viewName + "(s1||'_'||s2)");
+
+ query = "SELECT k1, k2, s1||'_'||s2 FROM " + viewName + " WHERE (s1||'_'||s2)='foo2_bar2'";
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ if (local) {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [" + (2)
+ + ",'foo2_bar2']\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ } else {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_" + dataTableName + " [" + (Short.MIN_VALUE + 1) + ",'foo2_bar2']\n"
+ + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
+ }
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(120, rs.getInt(2));
+ assertEquals("foo2_bar2", rs.getString(3));
+ assertFalse(rs.next());
+ }
+ finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testViewUsesMutableTableIndex() throws Exception {
+ helpTestViewUsesTableIndex(false);
+ }
+
+ @Test
+ public void testViewUsesImmutableTableIndex() throws Exception {
+ helpTestViewUsesTableIndex(true);
+ }
+
+ private void helpTestViewUsesTableIndex(boolean immutable) throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ try
+ {
+ String dataTableName = generateUniqueName();
+ String indexName1 = generateUniqueName();
+ String viewName = generateUniqueName();
+ String indexName2 = generateUniqueName();
+ ResultSet rs;
+ String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, s1 VARCHAR, s2 VARCHAR, s3 VARCHAR, s4 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2)) " + (immutable ? "IMMUTABLE_ROWS = true" : "");
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute("CREATE INDEX " + indexName1 + " ON " + dataTableName + "(k2, s2, s3, s1)");
+ conn.createStatement().execute("CREATE INDEX " + indexName2 + " ON " + dataTableName + "(k2, s2||'_'||s3, s1, s4)");
+
+ ddl = "CREATE VIEW " + viewName + " AS SELECT * FROM " + dataTableName + " WHERE s1 = 'foo'";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,1,'foo','abc','cab')");
+ conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(2,2,'bar','xyz','zyx')");
+ conn.commit();
+
+ rs = conn.createStatement().executeQuery("SELECT count(*) FROM " + viewName);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getLong(1));
+ assertFalse(rs.next());
+
+ //i2 should be used since it contains s3||'_'||s4 i
+ String query = "SELECT s2||'_'||s3 FROM " + viewName + " WHERE k2=1 AND (s2||'_'||s3)='abc_cab'";
+ rs = conn.createStatement( ).executeQuery("EXPLAIN " + query);
+ String queryPlan = QueryUtil.getExplainPlan(rs);
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName2 + " [1,'abc_cab','foo']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY", queryPlan);
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("abc_cab", rs.getString(1));
+ assertFalse(rs.next());
+
+ conn.createStatement().execute("ALTER VIEW " + viewName + " DROP COLUMN s4");
+ //i2 cannot be used since s4 has been dropped from the view, so i1 will be used
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ queryPlan = QueryUtil.getExplainPlan(rs);
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName1 + " [1]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY AND ((\"S2\" || '_' || \"S3\") = 'abc_cab' AND \"S1\" = 'foo')", queryPlan);
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("abc_cab", rs.getString(1));
+ assertFalse(rs.next());
+ }
+ finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testExpressionThrowsException() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String dataTableName = generateUniqueName();
+ String indexName = generateUniqueName();
+ try {
+ String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER PRIMARY KEY, k2 INTEGER)";
+ conn.createStatement().execute(ddl);
+ ddl = "CREATE INDEX " + indexName + " on " + dataTableName + "(k1/k2)";
+ conn.createStatement().execute(ddl);
+ // upsert should succeed
+ conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,1)");
+ conn.commit();
+ // divide by zero should fail
+ conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,0)");
+ conn.commit();
+ fail();
+ } catch (CommitException e) {
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testImmutableCaseSensitiveFunctionIndex() throws Exception {
+ helpTestCaseSensitiveFunctionIndex(false, false);
+ }
+
+ @Test
+ public void testImmutableLocalCaseSensitiveFunctionIndex() throws Exception {
+ helpTestCaseSensitiveFunctionIndex(false, true);
+ }
+
+ @Test
+ public void testMutableCaseSensitiveFunctionIndex() throws Exception {
+ helpTestCaseSensitiveFunctionIndex(true, false);
+ }
+
+ @Test
+ public void testMutableLocalCaseSensitiveFunctionIndex() throws Exception {
+ helpTestCaseSensitiveFunctionIndex(true, true);
+ }
+
+ protected void helpTestCaseSensitiveFunctionIndex(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 + " (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) "
+ + (!mutable ? "IMMUTABLE_ROWS=true" : ""));
+ String query = "SELECT * FROM " + dataTableName;
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+ String ddl = "CREATE " + (localIndex ? "LOCAL" : "")
+ + " INDEX " + indexName + " ON " + dataTableName + " (REGEXP_SUBSTR(v,'id:\\\\w+'))";
+ conn.createStatement().execute(ddl);
+ query = "SELECT * FROM " + indexName;
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)");
+ stmt.setString(1, "k1");
+ stmt.setString(2, "{id:id1}");
+ stmt.execute();
+ stmt.setString(1, "k2");
+ stmt.setString(2, "{id:id2}");
+ stmt.execute();
+ conn.commit();
+
+ query = "SELECT k FROM " + dataTableName + " WHERE REGEXP_SUBSTR(v,'id:\\\\w+') = 'id:id1'";
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ if (localIndex) {
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,'id:id1']\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ } else {
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['id:id1']\n"
+ + " SERVER FILTER BY FIRST KEY ONLY",
+ QueryUtil.getExplainPlan(rs));
+ }
+
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("k1", rs.getString(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testImmutableTableGlobalIndexExpressionWithJoin() throws Exception {
+ helpTestIndexExpressionWithJoin(false, false);
+ }
+
+ @Test
+ public void testImmutableTableLocalIndexExpressionWithJoin() throws Exception {
+ helpTestIndexExpressionWithJoin(false, true);
+ }
+
+ @Test
+ public void testMutableTableGlobalIndexExpressionWithJoin() throws Exception {
+ helpTestIndexExpressionWithJoin(true, false);
+ }
+
+ @Test
+ public void testMutableTableLocalIndexExpressionWithJoin() throws Exception {
+ helpTestIndexExpressionWithJoin(true, true);
+ }
+
+ public void helpTestIndexExpressionWithJoin(boolean mutable,
+ boolean localIndex) throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String nameSuffix = "T" + (mutable ? "MUTABLE" : "_IMMUTABLE") + (localIndex ? "_LOCAL" : "_GLOBAL");
+ String tableName = "T" + nameSuffix;
+ String indexName = "IDX" + nameSuffix;
+ try {
+ conn.createStatement().execute(
+ "CREATE TABLE "
+ + tableName
+ + "( c_customer_sk varchar primary key, c_first_name varchar, c_last_name varchar )"
+ + (!mutable ? "IMMUTABLE_ROWS=true" : ""));
+ String query = "SELECT * FROM " + tableName;
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ conn.createStatement().execute(
+ "CREATE " + (localIndex ? "LOCAL" : "")
+ + " INDEX " + indexName + " ON " + tableName + " (c_customer_sk || c_first_name asc)");
+ query = "SELECT * FROM " + indexName;
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?,?)");
+ stmt.setString(1, "1");
+ stmt.setString(2, "David");
+ stmt.setString(3, "Smith");
+ stmt.execute();
+ conn.commit();
+
+ query = "select c.c_customer_sk from " + tableName + " c "
+ + "left outer join " + tableName + " c2 on c.c_customer_sk = c2.c_customer_sk "
+ + "where c.c_customer_sk || c.c_first_name = '1David'";
+ rs = conn.createStatement().executeQuery("EXPLAIN "+query);
+ String explainPlan = QueryUtil.getExplainPlan(rs);
+ if (localIndex) {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1,'1David']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT", explainPlan);
+ }
+ else {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['1David']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY", explainPlan);
+ }
+
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("1", rs.getString(1));
+ 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/IndexWithTableSchemaChangeIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexWithTableSchemaChangeIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexWithTableSchemaChangeIT.java
new file mode 100644
index 0000000..659866b
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexWithTableSchemaChangeIT.java
@@ -0,0 +1,375 @@
+/*
+ * 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.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.util.IndexUtil;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Test;
+
+public class IndexWithTableSchemaChangeIT extends ParallelStatsDisabledIT {
+
+ @Test
+ public void testImmutableIndexDropIndexedColumn() throws Exception {
+ helpTestDropIndexedColumn(false, false);
+ }
+
+ @Test
+ public void testImmutableLocalIndexDropIndexedColumn() throws Exception {
+ helpTestDropIndexedColumn(false, true);
+ }
+
+ @Test
+ public void testMutableIndexDropIndexedColumn() throws Exception {
+ helpTestDropIndexedColumn(true, false);
+ }
+
+ @Test
+ public void testMutableLocalIndexDropIndexedColumn() throws Exception {
+ helpTestDropIndexedColumn(true, true);
+ }
+
+ public void helpTestDropIndexedColumn(boolean mutable, boolean local) throws Exception {
+ String query;
+ ResultSet rs;
+ PreparedStatement stmt;
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+
+ String dataTableName = generateUniqueName();
+ String indexName = generateUniqueName();
+
+ try {
+ conn.setAutoCommit(false);
+
+ // make sure that the tables are empty, but reachable
+ conn.createStatement().execute(
+ "CREATE TABLE " + dataTableName
+ + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + (!mutable ? " IMMUTABLE_ROWS=true" : ""));
+ query = "SELECT * FROM " + dataTableName ;
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+ conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (v1 || '_' || v2)");
+
+ query = "SELECT * FROM " + dataTableName;
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ // load some data into the table
+ stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)");
+ stmt.setString(1, "a");
+ stmt.setString(2, "x");
+ stmt.setString(3, "1");
+ stmt.execute();
+ conn.commit();
+
+ assertIndexExists(conn, dataTableName, true);
+ conn.createStatement().execute("ALTER TABLE " + dataTableName + " DROP COLUMN v1");
+ assertIndexExists(conn, dataTableName, false);
+
+ query = "SELECT * FROM " + dataTableName;
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals("1",rs.getString(2));
+ assertFalse(rs.next());
+
+ // load some data into the table
+ stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)");
+ stmt.setString(1, "a");
+ stmt.setString(2, "2");
+ stmt.execute();
+ conn.commit();
+
+ query = "SELECT * FROM " + dataTableName;
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals("2",rs.getString(2));
+ assertFalse(rs.next());
+ }
+ finally {
+ conn.close();
+ }
+ }
+
+ private static void assertIndexExists(Connection conn, String tableName, boolean exists) throws SQLException {
+ ResultSet rs = conn.getMetaData().getIndexInfo(null, null, tableName, false, false);
+ assertEquals(exists, rs.next());
+ }
+
+ @Test
+ public void testImmutableIndexDropCoveredColumn() throws Exception {
+ helpTestDropCoveredColumn(false, false);
+ }
+
+ @Test
+ public void testImmutableLocalIndexDropCoveredColumn() throws Exception {
+ helpTestDropCoveredColumn(false, true);
+ }
+
+ @Test
+ public void testMutableIndexDropCoveredColumn() throws Exception {
+ helpTestDropCoveredColumn(true, false);
+ }
+
+ @Test
+ public void testMutableLocalIndexDropCoveredColumn() throws Exception {
+ helpTestDropCoveredColumn(true, true);
+ }
+
+ public void helpTestDropCoveredColumn(boolean mutable, boolean local) throws Exception {
+ ResultSet rs;
+ PreparedStatement stmt;
+ String dataTableName = generateUniqueName();
+ String indexName = generateUniqueName();
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.setAutoCommit(false);
+
+ // make sure that the tables are empty, but reachable
+ conn.createStatement().execute(
+ "CREATE TABLE " + dataTableName
+ + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)");
+ String dataTableQuery = "SELECT * FROM " + dataTableName;
+ rs = conn.createStatement().executeQuery(dataTableQuery);
+ assertFalse(rs.next());
+
+ conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (k || '_' || v1) include (v2, v3)");
+ String indexTableQuery = "SELECT * FROM " + indexName;
+ rs = conn.createStatement().executeQuery(indexTableQuery);
+ assertFalse(rs.next());
+
+ // load some data into the table
+ stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?,?)");
+ stmt.setString(1, "a");
+ stmt.setString(2, "x");
+ stmt.setString(3, "1");
+ stmt.setString(4, "j");
+ stmt.execute();
+ conn.commit();
+
+ assertIndexExists(conn, dataTableName, true);
+ conn.createStatement().execute("ALTER TABLE " + dataTableName + " DROP COLUMN v2");
+ assertIndexExists(conn, dataTableName, true);
+
+ // verify data table rows
+ rs = conn.createStatement().executeQuery(dataTableQuery);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals("x",rs.getString(2));
+ assertEquals("j",rs.getString(3));
+ assertFalse(rs.next());
+
+ // verify index table rows
+ rs = conn.createStatement().executeQuery(indexTableQuery);
+ assertTrue(rs.next());
+ assertEquals("a_x",rs.getString(1));
+ assertEquals("a",rs.getString(2));
+ assertEquals("j",rs.getString(3));
+ assertFalse(rs.next());
+
+ // add another row
+ stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)");
+ stmt.setString(1, "b");
+ stmt.setString(2, "y");
+ stmt.setString(3, "k");
+ stmt.execute();
+ conn.commit();
+
+ // verify data table rows
+ rs = conn.createStatement().executeQuery(dataTableQuery);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals("x",rs.getString(2));
+ assertEquals("j",rs.getString(3));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals("y",rs.getString(2));
+ assertEquals("k",rs.getString(3));
+ assertFalse(rs.next());
+
+ // verify index table rows
+ rs = conn.createStatement().executeQuery(indexTableQuery);
+ assertTrue(rs.next());
+ assertEquals("a_x",rs.getString(1));
+ assertEquals("a",rs.getString(2));
+ assertEquals("j",rs.getString(3));
+ assertTrue(rs.next());
+ assertEquals("b_y",rs.getString(1));
+ assertEquals("b",rs.getString(2));
+ assertEquals("k",rs.getString(3));
+ assertFalse(rs.next());
+ }
+ finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testImmutableIndexAddPKColumnToTable() throws Exception {
+ helpTestAddPKColumnToTable(false, false);
+ }
+
+ @Test
+ public void testImmutableLocalIndexAddPKColumnToTable() throws Exception {
+ helpTestAddPKColumnToTable(false, true);
+ }
+
+ @Test
+ public void testMutableIndexAddPKColumnToTable() throws Exception {
+ helpTestAddPKColumnToTable(true, false);
+ }
+
+ @Test
+ public void testMutableLocalIndexAddPKColumnToTable() throws Exception {
+ helpTestAddPKColumnToTable(true, true);
+ }
+
+ public void helpTestAddPKColumnToTable(boolean mutable, boolean local) throws Exception {
+ ResultSet rs;
+ PreparedStatement stmt;
+
+ String dataTableName = generateUniqueName();
+ String indexName = generateUniqueName();
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.setAutoCommit(false);
+
+ // make sure that the tables are empty, but reachable
+ conn.createStatement().execute(
+ "CREATE TABLE " + dataTableName
+ + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)");
+ String dataTableQuery = "SELECT * FROM " + dataTableName;
+ rs = conn.createStatement().executeQuery(dataTableQuery);
+ assertFalse(rs.next());
+
+ conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (v1 || '_' || v2)");
+ String indexTableQuery = "SELECT * FROM " + indexName;
+ rs = conn.createStatement().executeQuery(indexTableQuery);
+ assertFalse(rs.next());
+
+ // load some data into the table
+ stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)");
+ stmt.setString(1, "a");
+ stmt.setString(2, "x");
+ stmt.setString(3, "1");
+ stmt.execute();
+ conn.commit();
+
+ assertIndexExists(conn, dataTableName, true);
+ conn.createStatement().execute("ALTER TABLE " + dataTableName + " ADD v3 VARCHAR, k2 DECIMAL PRIMARY KEY");
+ rs = conn.getMetaData().getPrimaryKeys("", "", dataTableName);
+ assertTrue(rs.next());
+ assertEquals("K",rs.getString("COLUMN_NAME"));
+ assertEquals(1, rs.getShort("KEY_SEQ"));
+ assertTrue(rs.next());
+ assertEquals("K2",rs.getString("COLUMN_NAME"));
+ assertEquals(2, rs.getShort("KEY_SEQ"));
+
+ rs = conn.getMetaData().getPrimaryKeys("", "", indexName);
+ assertTrue(rs.next());
+ assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "(V1 || '_' || V2)",rs.getString("COLUMN_NAME"));
+ int offset = local ? 1 : 0;
+ assertEquals(offset+1, rs.getShort("KEY_SEQ"));
+ assertTrue(rs.next());
+ assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "K",rs.getString("COLUMN_NAME"));
+ assertEquals(offset+2, rs.getShort("KEY_SEQ"));
+ assertTrue(rs.next());
+ assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "K2",rs.getString("COLUMN_NAME"));
+ assertEquals(offset+3, rs.getShort("KEY_SEQ"));
+
+ // verify data table rows
+ rs = conn.createStatement().executeQuery(dataTableQuery);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals("x",rs.getString(2));
+ assertEquals("1",rs.getString(3));
+ assertNull(rs.getBigDecimal(4));
+ assertFalse(rs.next());
+
+ // verify index table rows
+ rs = conn.createStatement().executeQuery(indexTableQuery);
+ assertTrue(rs.next());
+ assertEquals("x_1",rs.getString(1));
+ assertEquals("a",rs.getString(2));
+ assertNull(rs.getBigDecimal(3));
+ assertFalse(rs.next());
+
+ // load some data into the table
+ stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + "(K,K2,V1,V2) VALUES(?,?,?,?)");
+ stmt.setString(1, "b");
+ stmt.setBigDecimal(2, BigDecimal.valueOf(2));
+ stmt.setString(3, "y");
+ stmt.setString(4, "2");
+ stmt.execute();
+ conn.commit();
+
+ // verify data table rows
+ rs = conn.createStatement().executeQuery(dataTableQuery);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals("x",rs.getString(2));
+ assertEquals("1",rs.getString(3));
+ assertNull(rs.getString(4));
+ assertNull(rs.getBigDecimal(5));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals("y",rs.getString(2));
+ assertEquals("2",rs.getString(3));
+ assertNull(rs.getString(4));
+ assertEquals(BigDecimal.valueOf(2),rs.getBigDecimal(5));
+ assertFalse(rs.next());
+
+ // verify index table rows
+ rs = conn.createStatement().executeQuery(indexTableQuery);
+ assertTrue(rs.next());
+ assertEquals("x_1",rs.getString(1));
+ assertEquals("a",rs.getString(2));
+ assertNull(rs.getBigDecimal(3));
+ assertTrue(rs.next());
+ assertEquals("y_2",rs.getString(1));
+ assertEquals("b",rs.getString(2));
+ assertEquals(BigDecimal.valueOf(2),rs.getBigDecimal(3));
+ 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/LocalImmutableNonTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java
index f2d18d2..7b7c6ad 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
import org.junit.runners.Parameterized.Parameters;
-public class LocalImmutableNonTxIndexIT extends IndexIT {
+public class LocalImmutableNonTxIndexIT extends BaseIndexIT {
public LocalImmutableNonTxIndexIT(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/LocalImmutableTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java
index 9e6fd5f..5ff6d04 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
import org.junit.runners.Parameterized.Parameters;
-public class LocalImmutableTxIndexIT extends IndexIT {
+public class LocalImmutableTxIndexIT extends BaseIndexIT {
public LocalImmutableTxIndexIT(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/LocalMutableNonTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java
index 9785d20..4b9688b 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
import org.junit.runners.Parameterized.Parameters;
-public class LocalMutableNonTxIndexIT extends IndexIT {
+public class LocalMutableNonTxIndexIT extends BaseIndexIT {
public LocalMutableNonTxIndexIT(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/LocalMutableTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java
index c09a8c0..5f5dd0f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
import org.junit.runners.Parameterized.Parameters;
-public class LocalMutableTxIndexIT extends IndexIT {
+public class LocalMutableTxIndexIT extends BaseIndexIT {
public LocalMutableTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) {
super(localIndex, mutable, transactional, columnEncoded);