| /* |
| * 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); |
| |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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)); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + dataTableName); |
| } 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()); |
| conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); |
| } 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 fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; |
| 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 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); |
| |
| // 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 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); |
| |
| // 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 fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; |
| 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(); |
| } |
| } |
| |
| } |
