phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject phoenix git commit: PHOENIX-4248 Breakup IndexExpressionIT into several integration tests so as not to create too many tables in one test (addendum)
Date Fri, 29 Sep 2017 04:36:43 GMT
Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-1.1 def7ba3db -> 388b21ab8


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/388b21ab
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/388b21ab
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/388b21ab

Branch: refs/heads/4.x-HBase-1.1
Commit: 388b21ab800f8c0bd7d4e082f7ca091a31246a73
Parents: def7ba3
Author: James Taylor <jtaylor@salesforce.com>
Authored: Thu Sep 28 21:36:01 2017 -0700
Committer: James Taylor <jtaylor@salesforce.com>
Committed: Thu Sep 28 21:36:45 2017 -0700

----------------------------------------------------------------------
 .../end2end/index/IndexExpressionIT.java        | 1537 ------------------
 1 file changed, 1537 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/388b21ab/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();
-        }
-	}
-
-}


Mime
View raw message