PHOENIX-1639 Enhance function/expression index tests
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/2e5a6308 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/2e5a6308 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/2e5a6308 Branch: refs/heads/calcite Commit: 2e5a63089f98c21e8556fae0ec80988079e3ca55 Parents: b8c0559 Author: Thomas D'Silva <twdsi...@gmail.com> Authored: Tue Feb 17 12:32:55 2015 -0800 Committer: Thomas D'Silva <twdsi...@gmail.com> Committed: Tue Feb 17 12:32:55 2015 -0800 ---------------------------------------------------------------------- .../apache/phoenix/end2end/AlterTableIT.java | 58 +- .../org/apache/phoenix/end2end/BaseViewIT.java | 4 +- .../java/org/apache/phoenix/end2end/ViewIT.java | 5 + .../end2end/index/IndexExpressionIT.java | 714 +++++++++++++++---- .../coprocessor/MetaDataEndpointImpl.java | 5 +- .../phoenix/exception/SQLExceptionCode.java | 5 +- .../apache/phoenix/index/IndexMaintainer.java | 4 +- .../apache/phoenix/schema/MetaDataClient.java | 33 +- .../phoenix/compile/QueryCompilerTest.java | 72 ++ 9 files changed, 715 insertions(+), 185 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java index 7f5649b..59698d6 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java @@ -308,7 +308,6 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT { @Test public void testDropCoveredColumn() throws Exception { - String query; ResultSet rs; PreparedStatement stmt; @@ -320,19 +319,21 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT { conn.createStatement().execute( "CREATE TABLE " + DATA_TABLE_FULL_NAME + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)"); - query = "SELECT * FROM " + DATA_TABLE_FULL_NAME; - rs = conn.createStatement().executeQuery(query); + String dataTableQuery = "SELECT * FROM " + DATA_TABLE_FULL_NAME; + rs = conn.createStatement().executeQuery(dataTableQuery); assertFalse(rs.next()); conn.createStatement().execute( "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)"); conn.createStatement().execute( "CREATE LOCAL INDEX " + LOCAL_INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)"); - query = "SELECT * FROM " + INDEX_TABLE_FULL_NAME; - rs = conn.createStatement().executeQuery(query); + rs = conn.createStatement().executeQuery(dataTableQuery); assertFalse(rs.next()); - query = "SELECT * FROM " + LOCAL_INDEX_TABLE_FULL_NAME; - rs = conn.createStatement().executeQuery(query); + String indexTableQuery = "SELECT * FROM " + INDEX_TABLE_NAME; + rs = conn.createStatement().executeQuery(indexTableQuery); + assertFalse(rs.next()); + String localIndexTableQuery = "SELECT * FROM " + LOCAL_INDEX_TABLE_FULL_NAME; + rs = conn.createStatement().executeQuery(localIndexTableQuery); assertFalse(rs.next()); // load some data into the table @@ -346,16 +347,31 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT { assertIndexExists(conn,true); conn.createStatement().execute("ALTER TABLE " + DATA_TABLE_FULL_NAME + " DROP COLUMN v2"); - // TODO: verify meta data that we get back to confirm our column was dropped assertIndexExists(conn,true); - query = "SELECT * FROM " + DATA_TABLE_FULL_NAME; - rs = conn.createStatement().executeQuery(query); + // 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("x",rs.getString(1)); + assertEquals("a",rs.getString(2)); + assertEquals("j",rs.getString(3)); + assertFalse(rs.next()); + + // verify local index table rows + rs = conn.createStatement().executeQuery(localIndexTableQuery); + assertTrue(rs.next()); + assertEquals("x",rs.getString(1)); + assertEquals("a",rs.getString(2)); + assertEquals("j",rs.getString(3)); + assertFalse(rs.next()); // load some data into the table stmt = conn.prepareStatement("UPSERT INTO " + DATA_TABLE_FULL_NAME + " VALUES(?,?,?)"); @@ -365,13 +381,29 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT { stmt.execute(); conn.commit(); - query = "SELECT * FROM " + DATA_TABLE_FULL_NAME; - rs = conn.createStatement().executeQuery(query); + // verify data table rows + rs = conn.createStatement().executeQuery(dataTableQuery); assertTrue(rs.next()); assertEquals("a",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("y",rs.getString(1)); + assertEquals("a",rs.getString(2)); + assertEquals("k",rs.getString(3)); + assertFalse(rs.next()); + + // verify local index table rows + rs = conn.createStatement().executeQuery(localIndexTableQuery); + assertTrue(rs.next()); + assertEquals("y",rs.getString(1)); + assertEquals("a",rs.getString(2)); + assertEquals("k",rs.getString(3)); + assertFalse(rs.next()); } @Test @@ -427,8 +459,6 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT { assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "K2",rs.getString("COLUMN_NAME")); assertEquals(3, rs.getShort("KEY_SEQ")); - assertIndexExists(conn,true); - query = "SELECT * FROM " + DATA_TABLE_FULL_NAME; rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java index dc8e768..19d011f 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java @@ -130,7 +130,7 @@ public abstract class BaseViewIT extends BaseOwnClusterHBaseManagedTimeIT { rs = conn.createStatement().executeQuery("EXPLAIN " + query); String queryPlan = QueryUtil.getExplainPlan(rs); if (localIndex) { - assertEquals("CLIENT PARALLEL 3-WAY RANGE SCAN OVER _LOCAL_IDX_T [-32768,51]\n" + assertEquals("CLIENT PARALLEL "+ (saltBuckets == null ? 1 : saltBuckets) +"-WAY RANGE SCAN OVER _LOCAL_IDX_T [-32768,51]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT", queryPlan); @@ -166,7 +166,7 @@ public abstract class BaseViewIT extends BaseOwnClusterHBaseManagedTimeIT { assertFalse(rs.next()); rs = conn.createStatement().executeQuery("EXPLAIN " + query); if (localIndex) { - assertEquals("CLIENT PARALLEL 3-WAY RANGE SCAN OVER _LOCAL_IDX_T [" + (Short.MIN_VALUE+1) + ",'foo']\n" + assertEquals("CLIENT PARALLEL "+ (saltBuckets == null ? 1 : saltBuckets) +"-WAY RANGE SCAN OVER _LOCAL_IDX_T [" + (Short.MIN_VALUE+1) + ",'foo']\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT",QueryUtil.getExplainPlan(rs)); } else { http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java index 003db4c..266438d 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java @@ -109,6 +109,11 @@ public class ViewIT extends BaseViewIT { } @Test + public void testNonSaltedUpdatableViewWithLocalIndex() throws Exception { + testUpdatableViewWithIndex(null, true); + } + + @Test public void testUpdatableOnUpdatableView() throws Exception { testUpdatableView(null); Connection conn = DriverManager.getConnection(getUrl()); http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java index 28124b6..5c51bda 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java @@ -15,6 +15,7 @@ import static org.apache.phoenix.util.TestUtil.MUTABLE_INDEX_DATA_TABLE; 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 static org.junit.Assert.fail; @@ -30,8 +31,10 @@ import java.util.Properties; import org.apache.commons.lang.StringUtils; import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT; import org.apache.phoenix.exception.SQLExceptionCode; +import org.apache.phoenix.execute.CommitException; import org.apache.phoenix.query.QueryConstants; import org.apache.phoenix.util.DateUtil; +import org.apache.phoenix.util.IndexUtil; import org.apache.phoenix.util.PropertiesUtil; import org.apache.phoenix.util.QueryUtil; import org.junit.Test; @@ -41,22 +44,22 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { private static final int NUM_MILLIS_IN_DAY = 86400000; @Test - public void testImmutableIndexCreationAndUpdate() throws Exception { + public void testImmutableIndexCreateAndUpdate() throws Exception { helpTestCreateAndUpdate(false, false); } @Test - public void testImmutableLocalIndexCreationAndUpdate() throws Exception { + public void testImmutableLocalIndexCreateAndUpdate() throws Exception { helpTestCreateAndUpdate(false, true); } @Test - public void testMutableIndexCreationAndUpdate() throws Exception { + public void testMutableIndexCreateAndUpdate() throws Exception { helpTestCreateAndUpdate(true, false); } @Test - public void testMutableLocalIndexCreationAndUpdate() throws Exception { + public void testMutableLocalIndexCreateAndUpdate() throws Exception { helpTestCreateAndUpdate(true, true); } @@ -72,20 +75,20 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { stmt.setString(2, "char" + String.valueOf(i)); stmt.setInt(3, i); stmt.setLong(4, i); - stmt.setBigDecimal(5, new BigDecimal(Double.valueOf(i))); + stmt.setBigDecimal(5, new BigDecimal(i*0.5d)); Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i - 1) * NUM_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((double)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((double)i)); + stmt.setBigDecimal(17, new BigDecimal(i*0.5d)); stmt.setDate(18, date); stmt.executeUpdate(); } @@ -95,7 +98,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("CHAR" + String.valueOf(i), 6, ' ') + "_A.VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("B.CHAR" + String.valueOf(i), 10, ' '), rs.getString(1)); - assertEquals(i * 4, rs.getInt(2)); + assertEquals(i * 3, rs.getInt(2)); Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i) * NUM_MILLIS_IN_DAY); assertEquals(date, rs.getDate(3)); assertEquals(date, rs.getDate(4)); @@ -104,7 +107,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("char" + String.valueOf(i), rs.getString(7)); assertEquals(i, rs.getInt(8)); assertEquals(i, rs.getLong(9)); - assertEquals(i, rs.getDouble(10), 0.000001); + assertEquals(i*0.5d, rs.getDouble(10), 0.000001); assertEquals(i, rs.getLong(11)); assertEquals(i, rs.getLong(12)); } @@ -123,7 +126,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName - + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2))," + + " ((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)"; PreparedStatement stmt = conn.prepareStatement(ddl); @@ -132,14 +135,14 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { // 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(char_col2) = ?" + + " 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=?"; stmt = conn.prepareStatement(whereSql); stmt.setString(1, "VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 "); - stmt.setInt(2, 4); + stmt.setInt(2, 3); Date date = DateUtil.parseDate("2015-01-02 00:00:00"); stmt.setDate(3, date); stmt.setDate(4, date); @@ -150,8 +153,8 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals( localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_INDEX_TEST." + dataTableName - + " [-32768,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',4,'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.IDX ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',4,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]", + + " [-32768,'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.IDX ['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 @@ -162,7 +165,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { 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(char_col2), " + 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, " @@ -193,52 +196,79 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { verifyResult(rs, 3); verifyResult(rs, 4); - // update the first row - upsert = "UPSERT INTO " + conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + } finally { + conn.close(); + } + } + + @Test + public void testMutableIndexUpdate() throws Exception { + helpTestUpdate(false); + } + + @Test + public void testMutableLocalIndexUpdate() throws Exception { + helpTestUpdate(true); + } + + protected void helpTestUpdate(boolean localIndex) throws Exception { + String dataTableName = MUTABLE_INDEX_DATA_TABLE; + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + conn.setAutoCommit(false); + populateDataTable(conn, dataTableName); + + // create an expression index + String ddl = "CREATE " + + (localIndex ? "LOCAL" : "") + + " INDEX IDX 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, a.varchar_col1) VALUES(?, ?, ?, ?, ?, ?, ?)"; + + "(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(1.0)); + 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) from " + String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from " + fullDataTableName; - rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql); + 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)); - assertTrue(rs.next()); - assertEquals("VARCHAR3_CHAR3 _A.VARCHAR3_B.CHAR3 ", rs.getString(1)); - assertTrue(rs.next()); - assertEquals("VARCHAR4_CHAR4 _A.VARCHAR4_B.CHAR4 ", 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()); - // if the data table is immutable, the index table will have one more - // row - if (!mutable) { - assertEquals("VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ", rs.getString(1)); - assertTrue(rs.next()); - } - assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_" + (mutable ? "B.CHAR1 " : ""), rs.getString(1)); + 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)); - assertTrue(rs.next()); - assertEquals("VARCHAR3_CHAR3 _A.VARCHAR3_B.CHAR3 ", rs.getString(1)); - assertTrue(rs.next()); - assertEquals("VARCHAR4_CHAR4 _A.VARCHAR4_B.CHAR4 ", rs.getString(1)); + assertEquals(2, rs.getLong(2)); assertFalse(rs.next()); conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); } finally { @@ -546,8 +576,6 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + " (int_col1+1)"; - conn = DriverManager.getConnection(getUrl(), props); - conn.setAutoCommit(false); PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); String sql = "SELECT int_col1+1 FROM " + fullDataTableName + " where int_col1+1 IN (2)"; @@ -567,25 +595,25 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { @Test public void testOrderByWithImmutableIndex() throws Exception { - helpTestOrderByWithIndex(false, false); + helpTestSelectAliasAndOrderByWithIndex(false, false); } @Test public void testOrderByWithImmutableLocalIndex() throws Exception { - helpTestOrderByWithIndex(false, true); + helpTestSelectAliasAndOrderByWithIndex(false, true); } @Test public void testOrderByWithMutableIndex() throws Exception { - helpTestOrderByWithIndex(true, false); + helpTestSelectAliasAndOrderByWithIndex(true, false); } @Test public void testOrderByWithMutableLocalIndex() throws Exception { - helpTestOrderByWithIndex(true, false); + helpTestSelectAliasAndOrderByWithIndex(true, false); } - protected void helpTestOrderByWithIndex(boolean mutable, boolean localIndex) throws Exception { + protected void helpTestSelectAliasAndOrderByWithIndex(boolean mutable, boolean localIndex) throws Exception { String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); @@ -596,11 +624,9 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + " (int_col1+1)"; - conn = DriverManager.getConnection(getUrl(), props); - conn.setAutoCommit(false); PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); - String sql = "SELECT int_col1+1 FROM " + fullDataTableName + " ORDER BY int_col1+1"; + 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 _LOCAL_IDX_" + fullDataTableName @@ -620,66 +646,13 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } @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, false); - } - - protected void helpTestSelectColOnlyInDataTable(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName - + " (int_col1+1)"; - - conn = DriverManager.getConnection(getUrl(), props); - conn.setAutoCommit(false); - PreparedStatement stmt = conn.prepareStatement(ddl); - stmt.execute(); - 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 _LOCAL_IDX_" + fullDataTableName - + " [-32768,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()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); - } finally { - conn.close(); - } - } - - @Test public void testImmutableIndexWithCaseSensitiveCols() throws Exception { helpTestIndexWithCaseSensitiveCols(false, false); } @Test public void testImmutableLocalIndexWithCaseSensitiveCols() throws Exception { - helpTestIndexWithCaseSensitiveCols(true, false); + helpTestIndexWithCaseSensitiveCols(false, true); } @Test @@ -689,27 +662,25 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { @Test public void testMutableLocalIndexWithCaseSensitiveCols() throws Exception { - helpTestIndexWithCaseSensitiveCols(true, false); + helpTestIndexWithCaseSensitiveCols(true, true); } protected void helpTestIndexWithCaseSensitiveCols(boolean mutable, boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { - conn.createStatement().execute("CREATE TABLE cs (k VARCHAR NOT NULL PRIMARY KEY, \"V1\" VARCHAR, \"v2\" VARCHAR) "+ (mutable ? "IMMUTABLE_ROWS=true" : "")); + conn.createStatement().execute("CREATE TABLE cs (k VARCHAR NOT NULL PRIMARY KEY, \"cf1\".\"V1\" VARCHAR, \"CF2\".\"v2\" VARCHAR) "+ (mutable ? "IMMUTABLE_ROWS=true" : "")); String query = "SELECT * FROM cs"; ResultSet rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); - if (localIndex) { - conn.createStatement().execute("CREATE LOCAL INDEX ics ON cs (\"v2\" || '_modified') INCLUDE (\"V1\",\"v2\")"); - } else { - conn.createStatement().execute("CREATE INDEX ics ON cs (\"V1\" || '_' || \"v2\") INCLUDE (\"V1\",\"v2\")"); - } + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX ics ON cs (\"cf1\".\"V1\" || '_' || \"CF2\".\"v2\") INCLUDE (\"V1\",\"v2\")"; + PreparedStatement stmt = conn.prepareStatement(ddl); + stmt.execute(); query = "SELECT * FROM ics"; rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); - PreparedStatement stmt = conn.prepareStatement("UPSERT INTO cs VALUES(?,?,?)"); + stmt = conn.prepareStatement("UPSERT INTO cs VALUES(?,?,?)"); stmt.setString(1,"a"); stmt.setString(2, "x"); stmt.setString(3, "1"); @@ -720,7 +691,6 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { stmt.execute(); conn.commit(); - //TODO FIX THIS change this to * query = "SELECT (\"V1\" || '_' || \"v2\"), k, \"V1\", \"v2\" FROM cs WHERE (\"V1\" || '_' || \"v2\") = 'x_1'"; rs = conn.createStatement().executeQuery("EXPLAIN " + query); if(localIndex){ @@ -737,7 +707,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("x",rs.getString(3)); assertEquals("1",rs.getString(4)); //TODO figure out why this " " is needed - assertEquals("x_1",rs.getString("\"('V1' || '_' || 'v2')\"")); + assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\"")); assertEquals("a",rs.getString("k")); assertEquals("x",rs.getString("V1")); assertEquals("1",rs.getString("v2")); @@ -763,7 +733,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("x_1",rs.getString(4)); assertEquals("x_1",rs.getString("Foo1")); assertEquals("x_1",rs.getString(5)); - assertEquals("x_1",rs.getString("\"('V1' || '_' || 'v2')\"")); + assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\"")); assertTrue(rs.next()); assertEquals("y",rs.getString(1)); assertEquals("y",rs.getString("V1")); @@ -774,12 +744,65 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("y_2",rs.getString(4)); assertEquals("y_2",rs.getString("Foo1")); assertEquals("y_2",rs.getString(5)); - assertEquals("y_2",rs.getString("\"('V1' || '_' || 'v2')\"")); + assertEquals("y_2",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\"")); assertFalse(rs.next()); conn.createStatement().execute("DROP INDEX ICS ON CS"); } 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 = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + conn.setAutoCommit(false); + populateDataTable(conn, dataTableName); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + + " (int_col1+1)"; + + conn = DriverManager.getConnection(getUrl(), props); + conn.setAutoCommit(false); + PreparedStatement stmt = conn.prepareStatement(ddl); + stmt.execute(); + 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 _LOCAL_IDX_" + fullDataTableName + + " [-32768,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()); + conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + } finally { + conn.close(); + } } @Test @@ -809,58 +832,439 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); - conn.setAutoCommit(false); - - // make sure that the tables are empty, but reachable - conn.createStatement().execute( - "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); - query = "SELECT * FROM t" ; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h"); - conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON t (v1 || '_' || v2)"); + try { + conn.setAutoCommit(false); + + // make sure that the tables are empty, but reachable + conn.createStatement().execute( + "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); + query = "SELECT * FROM t" ; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h"); + conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON t (v1 || '_' || v2)"); + + query = "SELECT * FROM t"; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + // load some data into the table + stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + conn.commit(); + + assertIndexExists(conn,true); + conn.createStatement().execute("ALTER TABLE t DROP COLUMN v1"); + assertIndexExists(conn,false); + + query = "SELECT * FROM t"; + 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 t VALUES(?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "2"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM t"; + 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, boolean exists) throws SQLException { + ResultSet rs = conn.getMetaData().getIndexInfo(null, null, "T", 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; - query = "SELECT * FROM t"; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); + 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 t" + + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)"); + String dataTableQuery = "SELECT * FROM t"; + rs = conn.createStatement().executeQuery(dataTableQuery); + assertFalse(rs.next()); + + String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h"); + conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON t (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 t VALUES(?,?,?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.setString(4, "j"); + stmt.execute(); + conn.commit(); + + assertIndexExists(conn,true); + conn.createStatement().execute("ALTER TABLE t DROP COLUMN v2"); + assertIndexExists(conn,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 t 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; - // load some data into the table - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)"); - stmt.setString(1, "a"); - stmt.setString(2, "x"); - stmt.setString(3, "1"); - stmt.execute(); + 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 t" + + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); + String dataTableQuery = "SELECT * FROM t"; + rs = conn.createStatement().executeQuery(dataTableQuery); + assertFalse(rs.next()); + + String indexName = "IT_" + (mutable ? "M" : "IM") + "_" + (local ? "L" : "H"); + conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON t (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 t VALUES(?,?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + conn.commit(); + + assertIndexExists(conn,true); + conn.createStatement().execute("ALTER TABLE t ADD v3 VARCHAR, k2 DECIMAL PRIMARY KEY"); + rs = conn.getMetaData().getPrimaryKeys("", "", "T"); + 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 t(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(); + } + } + + @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()); + try { + String ddl = "CREATE TABLE t (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 v AS SELECT * FROM t WHERE k1 = 1"; + conn.createStatement().execute(ddl); + conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(120,'foo0','bar0',50.0)"); + conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(121,'foo1','bar1',51.0)"); + conn.commit(); + + ResultSet rs; + conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX i1 on v(k1+k2+k3) include (s1, s2)"); + conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(120,'foo2','bar2',50.0)"); + conn.commit(); + + String query = "SELECT k1, k2, k3, s1, s2 FROM v 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 _LOCAL_IDX_T [-32768,173]\n" + "CLIENT MERGE SORT", + queryPlan); + } else { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + 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 i2 on v(s1||'_'||s2)"); + + query = "SELECT k1, k2, s1||'_'||s2 FROM v WHERE (s1||'_'||s2)='foo2_bar2'"; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if (local) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_T [" + (Short.MIN_VALUE + 1) + + ",'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_T [" + (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 testViewUsesTableIndex() throws Exception { + ResultSet rs; + Connection conn = DriverManager.getConnection(getUrl()); + String ddl = "CREATE TABLE t (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, s1 VARCHAR, s2 VARCHAR, s3 VARCHAR, s4 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2))"; + conn.createStatement().execute(ddl); + conn.createStatement().execute("CREATE INDEX i1 ON t(k2, s2, s3, s1)"); + conn.createStatement().execute("CREATE INDEX i2 ON t(k2, s2||'_'||s3, s1, s4)"); + + ddl = "CREATE VIEW v AS SELECT * FROM t WHERE s1 = 'foo'"; + conn.createStatement().execute(ddl); + conn.createStatement().execute("UPSERT INTO t VALUES(1,1,'foo','abc','cab')"); + conn.createStatement().execute("UPSERT INTO t VALUES(2,2,'bar','xyz','zyx')"); conn.commit(); - - assertIndexExists(conn,true); - conn.createStatement().execute("ALTER TABLE t DROP COLUMN v1"); - assertIndexExists(conn,false); - - query = "SELECT * FROM t"; - rs = conn.createStatement().executeQuery(query); + + rs = conn.createStatement().executeQuery("SELECT count(*) FROM v"); assertTrue(rs.next()); - assertEquals("a",rs.getString(1)); - assertEquals("1",rs.getString(2)); + assertEquals(1, rs.getLong(1)); assertFalse(rs.next()); - - // load some data into the table - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)"); - stmt.setString(1, "a"); - stmt.setString(2, "2"); - stmt.execute(); - conn.commit(); - - query = "SELECT * FROM t"; + + conn.createStatement().execute("CREATE INDEX vi1 on v(k2)"); + + //i2 should be used since it contains s3||'_'||s4 i + String query = "SELECT s2||'_'||s3 FROM v 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 I2 [1,'abc_cab','foo']\n" + + " SERVER FILTER BY FIRST KEY ONLY", queryPlan); rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); - assertEquals("a",rs.getString(1)); - assertEquals("2",rs.getString(2)); + assertEquals("abc_cab", rs.getString(1)); assertFalse(rs.next()); + + conn.createStatement().execute("ALTER VIEW v DROP COLUMN s4"); + conn.createStatement().execute("CREATE INDEX vi2 on v(k2)"); + //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 I1 [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()); } - private static void assertIndexExists(Connection conn, boolean exists) throws SQLException { - ResultSet rs = conn.getMetaData().getIndexInfo(null, null, "T", false, false); - assertEquals(exists, rs.next()); - } + @Test + public void testExpressionThrowsException() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY, k2 INTEGER)"; + try { + conn.createStatement().execute(ddl); + ddl = "CREATE INDEX i on t(k1/k2)"; + conn.createStatement().execute(ddl); + // upsert should succeed + conn.createStatement().execute("UPSERT INTO T VALUES(1,1)"); + conn.commit(); + // divide by zero should fail + conn.createStatement().execute("UPSERT INTO T VALUES(1,0)"); + conn.commit(); + fail(); + } catch (CommitException e) { + } + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java index ce81e1f..e234498 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java @@ -61,7 +61,6 @@ import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; -import java.util.Comparator; import java.util.List; import java.util.Set; @@ -1414,12 +1413,12 @@ public class MetaDataEndpointImpl extends MetaDataProtocol implements Coprocesso for (PTable index : table.getIndexes()) { try { IndexMaintainer indexMaintainer = index.getIndexMaintainer(table, connection); - // get the columns required to create the index + // get the columns required for the index pk Set<ColumnReference> indexColumns = indexMaintainer.getIndexedColumns(); byte[] indexKey = SchemaUtil.getTableKey(tenantId, index .getSchemaName().getBytes(), index.getTableName().getBytes()); - // If index requires this column, then drop it + // If index requires this column for its pk, then drop it if (indexColumns.contains(new ColumnReference(columnToDelete.getFamilyName().getBytes(), columnToDelete.getName().getBytes()))) { // Since we're dropping the index, lock it to ensure // that a change in index state doesn't http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java index b2ca979..f4b4f98 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java @@ -154,8 +154,9 @@ public enum SQLExceptionCode { /** * Expression Index exceptions. */ - AGGREGATE_EXPRESSION_NOT_ALLOWED_IN_INDEX(520, "42897", "Aggreagaate expression are not allowed in an index"), - NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX(521, "42898", "Non-deterministic expression are not allowed in an index"), + AGGREGATE_EXPRESSION_NOT_ALLOWED_IN_INDEX(520, "42897", "Aggreagaate expression not allowed in an index"), + NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX(521, "42898", "Non-deterministic expression not allowed in an index"), + STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX(522, "42899", "Stateless expression not allowed in an index"), /** * HBase and Phoenix specific implementation defined sub-classes. http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java b/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java index 7199dad..fd006c9 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java @@ -692,10 +692,10 @@ public class IndexMaintainer implements Writable, Iterable<ColumnReference> { indexFields[pos] = dataRowKeySchema.getField(i); } } - Iterator<Expression> expressionSetItr = indexedExpressions.iterator(); + Iterator<Expression> expressionItr = indexedExpressions.iterator(); for (Field indexField : indexFields) { if (indexField == null) { // Add field for kv column in index - final PDataType dataType = expressionSetItr.next().getDataType(); + final PDataType dataType = expressionItr.next().getDataType(); builder.addField(new PDatum() { @Override http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java b/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java index 61ee081..64e62f5 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java @@ -433,14 +433,34 @@ public class MetaDataClient { for (PTable index : indexes) { if (index.getViewIndexId() == null) { boolean containsAllReqdCols = true; - // Ensure that all indexed columns from index on physical table + // Ensure that all columns required to create index // exist in the view too (since view columns may be removed) - List<PColumn> pkColumns = index.getPKColumns(); - for (int i = index.getBucketNum() == null ? 0 : 1; i < pkColumns.size(); i++) { + IndexMaintainer indexMaintainer = index.getIndexMaintainer(physicalTable, connection); + // check that the columns required for the index pk (not including the pk columns of the data table) + // are present in the view + Set<ColumnReference> indexColRefs = indexMaintainer.getIndexedColumns(); + for (ColumnReference colRef : indexColRefs) { + try { + byte[] cf= colRef.getFamily(); + byte[] cq= colRef.getQualifier(); + if (cf!=null) { + table.getColumnFamily(cf).getColumn(cq); + } + else { + table.getColumn( Bytes.toString(cq)); + } + } catch (ColumnNotFoundException e) { // Ignore this index and continue with others + containsAllReqdCols = false; + break; + } + } + // check that pk columns of the data table (which are also present in the index pk) are present in the view + List<PColumn> pkColumns = physicalTable.getPKColumns(); + for (int i = physicalTable.getBucketNum() == null ? 0 : 1; i < pkColumns.size(); i++) { try { PColumn pkColumn = pkColumns.get(i); - IndexUtil.getDataColumn(table, pkColumn.getName().getString()); - } catch (IllegalArgumentException e) { // Ignore this index and continue with others + table.getColumn(pkColumn.getName().getString()); + } catch (ColumnNotFoundException e) { // Ignore this index and continue with others containsAllReqdCols = false; break; } @@ -993,9 +1013,8 @@ public class MetaDataClient { if (expression.getDeterminism() != Determinism.ALWAYS) { throw new SQLExceptionInfo.Builder(SQLExceptionCode.NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException(); } - // true for any constant (including a view constant), as we don't need these in the index if (expression.isStateless()) { - continue; + throw new SQLExceptionInfo.Builder(SQLExceptionCode.STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException(); } unusedPkColumns.remove(expression); http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java index 466db9f..4accd38 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java @@ -1493,5 +1493,77 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { assertTrue(scan.getFilter() instanceof FirstKeyOnlyFilter); assertEquals(1, scan.getFamilyMap().size()); } + + @Test + public void testNonDeterministicExpressionIndex() throws Exception { + String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)"; + Connection conn = DriverManager.getConnection(getUrl()); + Statement stmt = null; + try { + stmt = conn.createStatement(); + stmt.execute(ddl); + stmt.execute("CREATE INDEX i ON t (RAND())"); + fail(); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(), e.getErrorCode()); + } + finally { + stmt.close(); + } + } + + @Test + public void testStatelessExpressionIndex() throws Exception { + String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)"; + Connection conn = DriverManager.getConnection(getUrl()); + Statement stmt = null; + try { + stmt = conn.createStatement(); + stmt.execute(ddl); + stmt.execute("CREATE INDEX i ON t (2)"); + fail(); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(), e.getErrorCode()); + } + finally { + stmt.close(); + } + } + + @Test + public void testAggregateExpressionIndex() throws Exception { + String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)"; + Connection conn = DriverManager.getConnection(getUrl()); + Statement stmt = null; + try { + stmt = conn.createStatement(); + stmt.execute(ddl); + stmt.execute("CREATE INDEX i ON t (SUM(k1))"); + fail(); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.AGGREGATE_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(), e.getErrorCode()); + } + finally { + stmt.close(); + } + } + + @Test + public void testDivideByZeroExpressionIndex() throws Exception { + String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)"; + Connection conn = DriverManager.getConnection(getUrl()); + Statement stmt = null; + try { + stmt = conn.createStatement(); + stmt.execute(ddl); + stmt.execute("CREATE INDEX i ON t (k1/0)"); + fail(); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.DIVIDE_BY_ZERO.getErrorCode(), e.getErrorCode()); + } + finally { + stmt.close(); + } + } }
