Repository: phoenix Updated Branches: refs/heads/master 29acb7786 -> d76a4f4e5
PHOENIX-4248 Breakup IndexExpressionIT into several integration tests so as not to create too many tables in one test (addendum) Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/d76a4f4e Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/d76a4f4e Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/d76a4f4e Branch: refs/heads/master Commit: d76a4f4e515af02e1c6f9a0dcb7b5e4318b4fbfb Parents: 29acb77 Author: James Taylor <[email protected]> Authored: Thu Sep 28 21:36:01 2017 -0700 Committer: James Taylor <[email protected]> Committed: Thu Sep 28 21:36:01 2017 -0700 ---------------------------------------------------------------------- .../end2end/index/IndexExpressionIT.java | 1537 ------------------ 1 file changed, 1537 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/d76a4f4e/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 deleted file mode 100644 index 2578fe4..0000000 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java +++ /dev/null @@ -1,1537 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE - * file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the - * License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by - * applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language - * governing permissions and limitations under the License. - */ -package org.apache.phoenix.end2end.index; - -import static org.apache.phoenix.query.QueryConstants.MILLIS_IN_DAY; -import static org.apache.phoenix.util.TestUtil.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.assertNull; -import static org.junit.Assert.assertTrue; -import static org.junit.Assert.fail; - -import java.math.BigDecimal; -import java.sql.Connection; -import java.sql.Date; -import java.sql.DriverManager; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.Properties; - -import org.apache.commons.lang.StringUtils; -import org.apache.phoenix.end2end.ParallelStatsDisabledIT; -import org.apache.phoenix.exception.SQLExceptionCode; -import org.apache.phoenix.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.apache.phoenix.util.TestUtil; -import org.junit.Test; - -public class IndexExpressionIT extends ParallelStatsDisabledIT { - - @Test - public void testImmutableIndexCreateAndUpdate() throws Exception { - helpTestCreateAndUpdate(false, false); - } - - @Test - public void testImmutableLocalIndexCreateAndUpdate() throws Exception { - helpTestCreateAndUpdate(false, true); - } - - @Test - public void testMutableIndexCreateAndUpdate() throws Exception { - helpTestCreateAndUpdate(true, false); - } - - @Test - public void testMutableLocalIndexCreateAndUpdate() throws Exception { - helpTestCreateAndUpdate(true, true); - } - - /** - * Adds a row to the index data table - * - * @param i - * row number - */ - private void insertRow(PreparedStatement stmt, int i) throws SQLException { - // insert row - stmt.setString(1, "varchar" + String.valueOf(i)); - stmt.setString(2, "char" + String.valueOf(i)); - stmt.setInt(3, i); - stmt.setLong(4, i); - stmt.setBigDecimal(5, new BigDecimal(i*0.5d)); - Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i - 1) * MILLIS_IN_DAY); - stmt.setDate(6, date); - stmt.setString(7, "a.varchar" + String.valueOf(i)); - stmt.setString(8, "a.char" + String.valueOf(i)); - stmt.setInt(9, i); - stmt.setLong(10, i); - stmt.setBigDecimal(11, new BigDecimal(i*0.5d)); - stmt.setDate(12, date); - stmt.setString(13, "b.varchar" + String.valueOf(i)); - stmt.setString(14, "b.char" + String.valueOf(i)); - stmt.setInt(15, i); - stmt.setLong(16, i); - stmt.setBigDecimal(17, new BigDecimal(i*0.5d)); - stmt.setDate(18, date); - stmt.executeUpdate(); - } - - private void verifyResult(ResultSet rs, int i) throws SQLException { - assertTrue(rs.next()); - assertEquals("VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("CHAR" + String.valueOf(i), 10, ' ') - + "_A.VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("B.CHAR" + String.valueOf(i), 10, ' '), - rs.getString(1)); - assertEquals(i * 3, rs.getInt(2)); - Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i) * MILLIS_IN_DAY); - assertEquals(date, rs.getDate(3)); - assertEquals(date, rs.getDate(4)); - assertEquals(date, rs.getDate(5)); - assertEquals("varchar" + String.valueOf(i), rs.getString(6)); - assertEquals("char" + String.valueOf(i), rs.getString(7)); - assertEquals(i, rs.getInt(8)); - assertEquals(i, rs.getLong(9)); - assertEquals(i*0.5d, rs.getDouble(10), 0.000001); - assertEquals(i, rs.getLong(11)); - assertEquals(i, rs.getLong(12)); - } - - private void createDataTable(Connection conn, String dataTableName, String tableProps) throws SQLException { - String tableDDL = "create table " + dataTableName + TestUtil.TEST_TABLE_SCHEMA + tableProps; - conn.createStatement().execute(tableDDL); - } - - protected void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - String indexName = generateUniqueName(); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - conn.setAutoCommit(false); - createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true"); - populateDataTable(conn, fullDataTableName); - - // create an expression index - String ddl = "CREATE " - + (localIndex ? "LOCAL" : "") - + " INDEX " + indexName + " ON " - + fullDataTableName - + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2))," - + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )" - + " INCLUDE (long_col1, long_col2)"; - conn.createStatement().execute(ddl); - - // run select query with expression in WHERE clause - String whereSql = "SELECT long_col1, long_col2 from " - + fullDataTableName - + " WHERE UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2) = ?" - + " AND decimal_pk+int_pk+decimal_col2+int_col1=?" - // since a.date1 and b.date2 are NULLABLE and date is fixed width, these expressions are stored as - // DECIMAL in the index (which is not fixed width) - + " AND date_pk+1=? AND date1+1=? AND date2+1=?"; - PreparedStatement stmt = conn.prepareStatement(whereSql); - stmt.setString(1, "VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 "); - stmt.setInt(2, 3); - Date date = DateUtil.parseDate("2015-01-02 00:00:00"); - stmt.setDate(3, date); - stmt.setDate(4, date); - stmt.setDate(5, date); - - // verify that the query does a range scan on the index table - ResultSet rs = stmt.executeQuery("EXPLAIN " + whereSql); - assertEquals( - localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." - + dataTableName - + " [1,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT MERGE SORT" - : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." + indexName + " ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]", - QueryUtil.getExplainPlan(rs)); - - // verify that the correct results are returned - rs = stmt.executeQuery(); - assertTrue(rs.next()); - assertEquals(1, rs.getInt(1)); - assertEquals(1, rs.getInt(2)); - assertFalse(rs.next()); - - // verify all rows in data table are present in index table - String indexSelectSql = "SELECT UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2), " - + "decimal_pk+int_pk+decimal_col2+int_col1, " - + "date_pk+1, date1+1, date2+1, " - + "varchar_pk, char_pk, int_pk, long_pk, decimal_pk, " - + "long_col1, long_col2 " - + "from " - + fullDataTableName; - rs = conn.createStatement().executeQuery("EXPLAIN " + indexSelectSql); - assertEquals(localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullDataTableName - + " [1]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST." + indexName, - QueryUtil.getExplainPlan(rs)); - rs = conn.createStatement().executeQuery(indexSelectSql); - verifyResult(rs, 1); - verifyResult(rs, 2); - - // Insert two more rows to the index data table - String upsert = "UPSERT INTO " + fullDataTableName - + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; - stmt = conn.prepareStatement(upsert); - insertRow(stmt, 3); - insertRow(stmt, 4); - conn.commit(); - - rs = conn.createStatement().executeQuery(indexSelectSql); - verifyResult(rs, 1); - verifyResult(rs, 2); - // verify that two rows added after index was created were also added to - // the index table - verifyResult(rs, 3); - verifyResult(rs, 4); - } finally { - conn.close(); - } - } - - @Test - public void testMutableIndexUpdate() throws Exception { - String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - String indexName = generateUniqueName(); - helpTestUpdate(fullDataTableName, indexName, false); - } - - @Test - public void testMutableLocalIndexUpdate() throws Exception { - String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - String indexName = generateUniqueName(); - helpTestUpdate(fullDataTableName, indexName, true); - } - - protected void helpTestUpdate(String fullDataTableName, String indexName, boolean localIndex) throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - conn.setAutoCommit(false); - createDataTable(conn, fullDataTableName, ""); - populateDataTable(conn, fullDataTableName); - - // create an expression index - String ddl = "CREATE " - + (localIndex ? "LOCAL" : "") - + " INDEX " + indexName + " ON " - + fullDataTableName - + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2))," - + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )" - + " INCLUDE (long_col1, long_col2)"; - PreparedStatement stmt = conn.prepareStatement(ddl); - stmt.execute(); - - // update index pk column and covered column - String upsert = "UPSERT INTO " - + fullDataTableName - + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"; - - stmt = conn.prepareStatement(upsert); - stmt.setString(1, "varchar1"); - stmt.setString(2, "char1"); - stmt.setInt(3, 1); - stmt.setLong(4, 1l); - stmt.setBigDecimal(5, new BigDecimal(0.5)); - stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00")); - stmt.setString(7, "a.varchar_updated"); - stmt.setLong(8, 101); - stmt.executeUpdate(); - conn.commit(); - - // verify only one row was updated in the data table - String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from " - + fullDataTableName; - ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql); - assertTrue(rs.next()); - assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1 ", rs.getString(1)); - assertEquals(101, rs.getLong(2)); - assertTrue(rs.next()); - assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1)); - assertEquals(2, rs.getLong(2)); - assertFalse(rs.next()); - - // verify that the rows in the index table are also updated - rs = conn.createStatement().executeQuery("SELECT " + selectSql); - assertTrue(rs.next()); - assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1 ", rs.getString(1)); - assertEquals(101, rs.getLong(2)); - assertTrue(rs.next()); - assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1)); - assertEquals(2, rs.getLong(2)); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - private void populateDataTable(Connection conn, String dataTable) throws SQLException { - String upsert = "UPSERT INTO " + dataTable - + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; - PreparedStatement stmt1 = conn.prepareStatement(upsert); - // insert two rows - insertRow(stmt1, 1); - insertRow(stmt1, 2); - conn.commit(); - } - - @Test - public void testDeleteIndexedExpressionImmutableIndex() throws Exception { - helpTestDeleteIndexedExpression(false, false); - } - - @Test - public void testDeleteIndexedExpressionImmutableLocalIndex() throws Exception { - helpTestDeleteIndexedExpression(false, true); - } - - @Test - public void testDeleteIndexedExpressionMutableIndex() throws Exception { - helpTestDeleteIndexedExpression(true, false); - } - - @Test - public void testDeleteIndexedExpressionMutableLocalIndex() throws Exception { - helpTestDeleteIndexedExpression(true, true); - } - - protected void helpTestDeleteIndexedExpression(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - String indexName = generateUniqueName(); - String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexName; - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - conn.setAutoCommit(false); - createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true"); - populateDataTable(conn, fullDataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName - + " (2*long_col2)"; - conn.createStatement().execute(ddl); - - ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName); - assertTrue(rs.next()); - assertEquals(2, rs.getInt(1)); - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); - assertTrue(rs.next()); - assertEquals(2, rs.getInt(1)); - - conn.setAutoCommit(true); - String dml = "DELETE from " + fullDataTableName + " WHERE long_col2 = 2"; - try { - conn.createStatement().execute(dml); - if (!mutable) { - fail(); - } - } catch (SQLException e) { - if (!mutable) { - assertEquals(SQLExceptionCode.INVALID_FILTER_ON_IMMUTABLE_ROWS.getErrorCode(), e.getErrorCode()); - } - } - - if (!mutable) { - dml = "DELETE from " + fullDataTableName + " WHERE 2*long_col2 = 4"; - conn.createStatement().execute(dml); - } - - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName); - assertTrue(rs.next()); - assertEquals(1, rs.getInt(1)); - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); - assertTrue(rs.next()); - assertEquals(1, rs.getInt(1)); - } finally { - conn.close(); - } - } - - @Test - public void testDeleteCoveredColImmutableIndex() throws Exception { - helpTestDeleteCoveredCol(false, false); - } - - @Test - public void testDeleteCoveredColImmutableLocalIndex() throws Exception { - helpTestDeleteCoveredCol(false, true); - } - - @Test - public void testDeleteCoveredColMutableIndex() throws Exception { - helpTestDeleteCoveredCol(true, false); - } - - @Test - public void testDeleteCoveredColMutableLocalIndex() throws Exception { - helpTestDeleteCoveredCol(true, true); - } - - protected void helpTestDeleteCoveredCol(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = generateUniqueName(); - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - String indexName = generateUniqueName(); - String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexName; - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - conn.setAutoCommit(false); - createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true"); - populateDataTable(conn, fullDataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName - + " (long_pk, varchar_pk, 1+long_pk, UPPER(varchar_pk) )" + " INCLUDE (long_col1, long_col2)"; - conn.createStatement().execute(ddl); - - ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName); - assertTrue(rs.next()); - assertEquals(2, rs.getInt(1)); - rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); - assertTrue(rs.next()); - assertEquals(2, rs.getInt(1)); - - String sql = "SELECT LONG_COL1 from " + fullDataTableName + " WHERE LONG_COL2 = 2"; - rs = conn.createStatement().executeQuery(sql); - assertTrue(rs.next()); - assertFalse(rs.next()); - - String dml = "DELETE from " + fullDataTableName + " WHERE long_col2 = 2"; - assertEquals(1, conn.createStatement().executeUpdate(dml)); - conn.commit(); - - String query = "SELECT /*+ NO_INDEX */ long_pk, varchar_pk, 1+long_pk, UPPER(varchar_pk) FROM " - + fullDataTableName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertEquals("varchar1", rs.getString(2)); - assertEquals(2L, rs.getLong(3)); - assertEquals("VARCHAR1", rs.getString(4)); - assertFalse(rs.next()); - - query = "SELECT long_pk, varchar_pk, 1+long_pk, UPPER(varchar_pk) FROM " + fullDataTableName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals(1L, rs.getLong(1)); - assertEquals("varchar1", rs.getString(2)); - assertEquals(2L, rs.getLong(3)); - assertEquals("VARCHAR1", rs.getString(4)); - assertFalse(rs.next()); - - query = "SELECT * FROM " + fullIndexTableName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - - assertEquals(1L, rs.getLong(1)); - assertEquals("varchar1", rs.getString(2)); - assertEquals(2L, rs.getLong(3)); - assertEquals("VARCHAR1", rs.getString(4)); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @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 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(); - } - } - - @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 testImmutableTableOnlyHasPrimaryKeyIndex() throws Exception { - helpTestTableOnlyHasPrimaryKeyIndex(false, false); - } - - @Test - public void testImmutableLocalTableOnlyHasPrimaryKeyIndex() throws Exception { - helpTestTableOnlyHasPrimaryKeyIndex(false, true); - } - - @Test - public void testMutableTableOnlyHasPrimaryKeyIndex() throws Exception { - helpTestTableOnlyHasPrimaryKeyIndex(true, false); - } - - @Test - public void testMutableLocalTableOnlyHasPrimaryKeyIndex() throws Exception { - helpTestTableOnlyHasPrimaryKeyIndex(true, true); - } - - private void helpTestTableOnlyHasPrimaryKeyIndex(boolean mutable, - boolean localIndex) throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - String dataTableName = generateUniqueName(); - String indexName = generateUniqueName(); - try { - conn.createStatement().execute( - "CREATE TABLE " + dataTableName + " (" - + "pk1 VARCHAR not null, " - + "pk2 VARCHAR not null, " - + "CONSTRAINT PK PRIMARY KEY (pk1, pk2))" - + (!mutable ? "IMMUTABLE_ROWS=true" : "")); - String query = "SELECT * FROM " + dataTableName; - ResultSet rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - conn.createStatement().execute( - "CREATE " + (localIndex ? "LOCAL" : "") - + " INDEX " + indexName + " ON " + dataTableName + " (pk2, pk1)"); - query = "SELECT * FROM " + indexName; - rs = conn.createStatement().executeQuery(query); - assertFalse(rs.next()); - - PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)"); - stmt.setString(1, "k11"); - stmt.setString(2, "k21"); - stmt.execute(); - conn.commit(); - - query = "SELECT * FROM " + indexName; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("k21", rs.getString(1)); - assertEquals("k11", rs.getString(2)); - assertFalse(rs.next()); - - query = "SELECT * FROM " + dataTableName + " WHERE pk2='k21'"; - rs = conn.createStatement().executeQuery(query); - assertTrue(rs.next()); - assertEquals("k11", rs.getString(1)); - assertEquals("k21", rs.getString(2)); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @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(); - } - } - -}
