PHOENIX-1639 Enhance function/expression index tests

Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/2e5a6308
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/2e5a6308
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/2e5a6308

Branch: refs/heads/calcite
Commit: 2e5a63089f98c21e8556fae0ec80988079e3ca55
Parents: b8c0559
Author: Thomas D'Silva <twdsi...@gmail.com>
Authored: Tue Feb 17 12:32:55 2015 -0800
Committer: Thomas D'Silva <twdsi...@gmail.com>
Committed: Tue Feb 17 12:32:55 2015 -0800

----------------------------------------------------------------------
 .../apache/phoenix/end2end/AlterTableIT.java    |  58 +-
 .../org/apache/phoenix/end2end/BaseViewIT.java  |   4 +-
 .../java/org/apache/phoenix/end2end/ViewIT.java |   5 +
 .../end2end/index/IndexExpressionIT.java        | 714 +++++++++++++++----
 .../coprocessor/MetaDataEndpointImpl.java       |   5 +-
 .../phoenix/exception/SQLExceptionCode.java     |   5 +-
 .../apache/phoenix/index/IndexMaintainer.java   |   4 +-
 .../apache/phoenix/schema/MetaDataClient.java   |  33 +-
 .../phoenix/compile/QueryCompilerTest.java      |  72 ++
 9 files changed, 715 insertions(+), 185 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
index 7f5649b..59698d6 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
@@ -308,7 +308,6 @@ public class AlterTableIT extends 
BaseOwnClusterHBaseManagedTimeIT {
 
     @Test
     public void testDropCoveredColumn() throws Exception {
-        String query;
         ResultSet rs;
         PreparedStatement stmt;
 
@@ -320,19 +319,21 @@ public class AlterTableIT extends 
BaseOwnClusterHBaseManagedTimeIT {
         conn.createStatement().execute(
           "CREATE TABLE " + DATA_TABLE_FULL_NAME
               + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 
VARCHAR)");
-        query = "SELECT * FROM " + DATA_TABLE_FULL_NAME;
-        rs = conn.createStatement().executeQuery(query);
+        String dataTableQuery = "SELECT * FROM " + DATA_TABLE_FULL_NAME;
+        rs = conn.createStatement().executeQuery(dataTableQuery);
         assertFalse(rs.next());
 
         conn.createStatement().execute(
           "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + 
" (v1) include (v2, v3)");
         conn.createStatement().execute(
             "CREATE LOCAL INDEX " + LOCAL_INDEX_TABLE_NAME + " ON " + 
DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)");
-        query = "SELECT * FROM " + INDEX_TABLE_FULL_NAME;
-        rs = conn.createStatement().executeQuery(query);
+        rs = conn.createStatement().executeQuery(dataTableQuery);
         assertFalse(rs.next());
-        query = "SELECT * FROM " + LOCAL_INDEX_TABLE_FULL_NAME;
-        rs = conn.createStatement().executeQuery(query);
+        String indexTableQuery = "SELECT * FROM " + INDEX_TABLE_NAME;
+        rs = conn.createStatement().executeQuery(indexTableQuery);
+        assertFalse(rs.next());
+        String localIndexTableQuery = "SELECT * FROM " + 
LOCAL_INDEX_TABLE_FULL_NAME;
+        rs = conn.createStatement().executeQuery(localIndexTableQuery);
         assertFalse(rs.next());
 
         // load some data into the table
@@ -346,16 +347,31 @@ public class AlterTableIT extends 
BaseOwnClusterHBaseManagedTimeIT {
 
         assertIndexExists(conn,true);
         conn.createStatement().execute("ALTER TABLE " + DATA_TABLE_FULL_NAME + 
" DROP COLUMN v2");
-        // TODO: verify meta data that we get back to confirm our column was 
dropped
         assertIndexExists(conn,true);
 
-        query = "SELECT * FROM " + DATA_TABLE_FULL_NAME;
-        rs = conn.createStatement().executeQuery(query);
+        // verify data table rows
+        rs = conn.createStatement().executeQuery(dataTableQuery);
         assertTrue(rs.next());
         assertEquals("a",rs.getString(1));
         assertEquals("x",rs.getString(2));
         assertEquals("j",rs.getString(3));
         assertFalse(rs.next());
+        
+        // verify index table rows
+        rs = conn.createStatement().executeQuery(indexTableQuery);
+        assertTrue(rs.next());
+        assertEquals("x",rs.getString(1));
+        assertEquals("a",rs.getString(2));
+        assertEquals("j",rs.getString(3));
+        assertFalse(rs.next());
+        
+        // verify local index table rows
+        rs = conn.createStatement().executeQuery(localIndexTableQuery);
+        assertTrue(rs.next());
+        assertEquals("x",rs.getString(1));
+        assertEquals("a",rs.getString(2));
+        assertEquals("j",rs.getString(3));
+        assertFalse(rs.next());
 
         // load some data into the table
         stmt = conn.prepareStatement("UPSERT INTO " + DATA_TABLE_FULL_NAME + " 
VALUES(?,?,?)");
@@ -365,13 +381,29 @@ public class AlterTableIT extends 
BaseOwnClusterHBaseManagedTimeIT {
         stmt.execute();
         conn.commit();
 
-        query = "SELECT * FROM " + DATA_TABLE_FULL_NAME;
-        rs = conn.createStatement().executeQuery(query);
+        // verify data table rows
+        rs = conn.createStatement().executeQuery(dataTableQuery);
         assertTrue(rs.next());
         assertEquals("a",rs.getString(1));
         assertEquals("y",rs.getString(2));
         assertEquals("k",rs.getString(3));
         assertFalse(rs.next());
+        
+        // verify index table rows
+        rs = conn.createStatement().executeQuery(indexTableQuery);
+        assertTrue(rs.next());
+        assertEquals("y",rs.getString(1));
+        assertEquals("a",rs.getString(2));
+        assertEquals("k",rs.getString(3));
+        assertFalse(rs.next());
+        
+        // verify local index table rows
+        rs = conn.createStatement().executeQuery(localIndexTableQuery);
+        assertTrue(rs.next());
+        assertEquals("y",rs.getString(1));
+        assertEquals("a",rs.getString(2));
+        assertEquals("k",rs.getString(3));
+        assertFalse(rs.next());
     }
 
     @Test
@@ -427,8 +459,6 @@ public class AlterTableIT extends 
BaseOwnClusterHBaseManagedTimeIT {
         assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + 
"K2",rs.getString("COLUMN_NAME"));
         assertEquals(3, rs.getShort("KEY_SEQ"));
 
-        assertIndexExists(conn,true);
-
         query = "SELECT * FROM " + DATA_TABLE_FULL_NAME;
         rs = conn.createStatement().executeQuery(query);
         assertTrue(rs.next());

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
index dc8e768..19d011f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
@@ -130,7 +130,7 @@ public abstract class BaseViewIT extends 
BaseOwnClusterHBaseManagedTimeIT {
         rs = conn.createStatement().executeQuery("EXPLAIN " + query);
         String queryPlan = QueryUtil.getExplainPlan(rs);
         if (localIndex) {
-            assertEquals("CLIENT PARALLEL 3-WAY RANGE SCAN OVER _LOCAL_IDX_T 
[-32768,51]\n"
+            assertEquals("CLIENT PARALLEL "+ (saltBuckets == null ? 1 : 
saltBuckets)  +"-WAY RANGE SCAN OVER _LOCAL_IDX_T [-32768,51]\n"
                     + "    SERVER FILTER BY FIRST KEY ONLY\n"
                     + "CLIENT MERGE SORT",
                 queryPlan);
@@ -166,7 +166,7 @@ public abstract class BaseViewIT extends 
BaseOwnClusterHBaseManagedTimeIT {
         assertFalse(rs.next());
         rs = conn.createStatement().executeQuery("EXPLAIN " + query);
         if (localIndex) {
-            assertEquals("CLIENT PARALLEL 3-WAY RANGE SCAN OVER _LOCAL_IDX_T 
[" + (Short.MIN_VALUE+1) + ",'foo']\n"
+            assertEquals("CLIENT PARALLEL "+ (saltBuckets == null ? 1 : 
saltBuckets)  +"-WAY RANGE SCAN OVER _LOCAL_IDX_T [" + (Short.MIN_VALUE+1) + 
",'foo']\n"
                     + "    SERVER FILTER BY FIRST KEY ONLY\n"
                     + "CLIENT MERGE SORT",QueryUtil.getExplainPlan(rs));
         } else {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
index 003db4c..266438d 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
@@ -109,6 +109,11 @@ public class ViewIT extends BaseViewIT {
     }
     
     @Test
+    public void testNonSaltedUpdatableViewWithLocalIndex() throws Exception {
+        testUpdatableViewWithIndex(null, true);
+    }
+    
+    @Test
     public void testUpdatableOnUpdatableView() throws Exception {
         testUpdatableView(null);
         Connection conn = DriverManager.getConnection(getUrl());

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java
 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java
index 28124b6..5c51bda 100644
--- 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java
+++ 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java
@@ -15,6 +15,7 @@ import static 
org.apache.phoenix.util.TestUtil.MUTABLE_INDEX_DATA_TABLE;
 import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNull;
 import static org.junit.Assert.assertTrue;
 import static org.junit.Assert.fail;
 
@@ -30,8 +31,10 @@ import java.util.Properties;
 import org.apache.commons.lang.StringUtils;
 import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT;
 import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.execute.CommitException;
 import org.apache.phoenix.query.QueryConstants;
 import org.apache.phoenix.util.DateUtil;
+import org.apache.phoenix.util.IndexUtil;
 import org.apache.phoenix.util.PropertiesUtil;
 import org.apache.phoenix.util.QueryUtil;
 import org.junit.Test;
@@ -41,22 +44,22 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
     private static final int NUM_MILLIS_IN_DAY = 86400000;
 
     @Test
-    public void testImmutableIndexCreationAndUpdate() throws Exception {
+    public void testImmutableIndexCreateAndUpdate() throws Exception {
         helpTestCreateAndUpdate(false, false);
     }
 
     @Test
-    public void testImmutableLocalIndexCreationAndUpdate() throws Exception {
+    public void testImmutableLocalIndexCreateAndUpdate() throws Exception {
         helpTestCreateAndUpdate(false, true);
     }
 
     @Test
-    public void testMutableIndexCreationAndUpdate() throws Exception {
+    public void testMutableIndexCreateAndUpdate() throws Exception {
         helpTestCreateAndUpdate(true, false);
     }
 
     @Test
-    public void testMutableLocalIndexCreationAndUpdate() throws Exception {
+    public void testMutableLocalIndexCreateAndUpdate() throws Exception {
         helpTestCreateAndUpdate(true, true);
     }
 
@@ -72,20 +75,20 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
         stmt.setString(2, "char" + String.valueOf(i));
         stmt.setInt(3, i);
         stmt.setLong(4, i);
-        stmt.setBigDecimal(5, new BigDecimal(Double.valueOf(i)));
+        stmt.setBigDecimal(5, new BigDecimal(i*0.5d));
         Date date = new Date(DateUtil.parseDate("2015-01-01 
00:00:00").getTime() + (i - 1) * NUM_MILLIS_IN_DAY);
         stmt.setDate(6, date);
         stmt.setString(7, "a.varchar" + String.valueOf(i));
         stmt.setString(8, "a.char" + String.valueOf(i));
         stmt.setInt(9, i);
         stmt.setLong(10, i);
-        stmt.setBigDecimal(11, new BigDecimal((double)i));
+        stmt.setBigDecimal(11, new BigDecimal(i*0.5d));
         stmt.setDate(12, date);
         stmt.setString(13, "b.varchar" + String.valueOf(i));
         stmt.setString(14, "b.char" + String.valueOf(i));
         stmt.setInt(15, i);
         stmt.setLong(16, i);
-        stmt.setBigDecimal(17, new BigDecimal((double)i));
+        stmt.setBigDecimal(17, new BigDecimal(i*0.5d));
         stmt.setDate(18, date);
         stmt.executeUpdate();
     }
@@ -95,7 +98,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT 
{
         assertEquals("VARCHAR" + String.valueOf(i) + "_" + 
StringUtils.rightPad("CHAR" + String.valueOf(i), 6, ' ')
                 + "_A.VARCHAR" + String.valueOf(i) + "_" + 
StringUtils.rightPad("B.CHAR" + String.valueOf(i), 10, ' '),
                 rs.getString(1));
-        assertEquals(i * 4, rs.getInt(2));
+        assertEquals(i * 3, rs.getInt(2));
         Date date = new Date(DateUtil.parseDate("2015-01-01 
00:00:00").getTime() + (i) * NUM_MILLIS_IN_DAY);
         assertEquals(date, rs.getDate(3));
         assertEquals(date, rs.getDate(4));
@@ -104,7 +107,7 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
         assertEquals("char" + String.valueOf(i), rs.getString(7));
         assertEquals(i, rs.getInt(8));
         assertEquals(i, rs.getLong(9));
-        assertEquals(i, rs.getDouble(10), 0.000001);
+        assertEquals(i*0.5d, rs.getDouble(10), 0.000001);
         assertEquals(i, rs.getLong(11));
         assertEquals(i, rs.getLong(12));
     }
@@ -123,7 +126,7 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
                     + (localIndex ? "LOCAL" : "")
                     + " INDEX IDX ON "
                     + fullDataTableName
-                    + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || 
UPPER(varchar_col1) || '_' || UPPER(char_col2)),"
+                    + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || 
UPPER(varchar_col1) || '_' || UPPER(b.char_col2)),"
                     + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " 
date_pk+1, date1+1, date2+1 )"
                     + " INCLUDE (long_col1, long_col2)";
             PreparedStatement stmt = conn.prepareStatement(ddl);
@@ -132,14 +135,14 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             // run select query with expression in WHERE clause
             String whereSql = "SELECT long_col1, long_col2 from "
                     + fullDataTableName
-                    + " WHERE UPPER(varchar_pk) || '_' || UPPER(char_pk) || 
'_' || UPPER(varchar_col1) || '_' || UPPER(char_col2) = ?"
+                    + " WHERE UPPER(varchar_pk) || '_' || UPPER(char_pk) || 
'_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2) = ?"
                     + " AND decimal_pk+int_pk+decimal_col2+int_col1=?"
                     // since a.date1 and b.date2 are NULLABLE and date is 
fixed width, these expressions are stored as
                     // DECIMAL in the index (which is not fixed width)
                     + " AND date_pk+1=? AND date1+1=? AND date2+1=?";
             stmt = conn.prepareStatement(whereSql);
             stmt.setString(1, "VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ");
-            stmt.setInt(2, 4);
+            stmt.setInt(2, 3);
             Date date = DateUtil.parseDate("2015-01-02 00:00:00");
             stmt.setDate(3, date);
             stmt.setDate(4, date);
@@ -150,8 +153,8 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             assertEquals(
                     localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER 
_LOCAL_IDX_INDEX_TEST."
                             + dataTableName
-                            + " [-32768,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   
',4,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT 
MERGE SORT"
-                            : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER 
INDEX_TEST.IDX ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ',4,'2015-01-02 
00:00:00.000',1,420,156,800,000,1,420,156,800,000]",
+                            + " [-32768,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   
',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT 
MERGE SORT"
+                            : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER 
INDEX_TEST.IDX ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ',3,'2015-01-02 
00:00:00.000',1,420,156,800,000,1,420,156,800,000]",
                     QueryUtil.getExplainPlan(rs));
 
             // verify that the correct results are returned
@@ -162,7 +165,7 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             assertFalse(rs.next());
 
             // verify all rows in data table are present in index table
-            String indexSelectSql = "SELECT UPPER(varchar_pk) || '_' || 
UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), "
+            String indexSelectSql = "SELECT UPPER(varchar_pk) || '_' || 
UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2), "
                     + "decimal_pk+int_pk+decimal_col2+int_col1, "
                     + "date_pk+1, date1+1, date2+1, "
                     + "varchar_pk, char_pk, int_pk, long_pk, decimal_pk, "
@@ -193,52 +196,79 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             verifyResult(rs, 3);
             verifyResult(rs, 4);
 
-            // update the first row
-            upsert = "UPSERT INTO "
+            conn.createStatement().execute("DROP INDEX IDX ON " + 
fullDataTableName);
+        } finally {
+            conn.close();
+        }
+    }
+    
+    @Test
+    public void testMutableIndexUpdate() throws Exception {
+       helpTestUpdate(false);
+    }
+
+    @Test
+    public void testMutableLocalIndexUpdate() throws Exception {
+       helpTestUpdate(true);
+    }
+    
+    protected void helpTestUpdate(boolean localIndex) throws Exception {
+        String dataTableName = MUTABLE_INDEX_DATA_TABLE;
+        String fullDataTableName = INDEX_DATA_SCHEMA + 
QueryConstants.NAME_SEPARATOR + dataTableName;
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.setAutoCommit(false);
+            populateDataTable(conn, dataTableName);
+
+            // create an expression index
+            String ddl = "CREATE "
+                    + (localIndex ? "LOCAL" : "")
+                    + " INDEX IDX ON "
+                    + fullDataTableName
+                    + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || 
UPPER(varchar_col1) || '_' || UPPER(char_col2)),"
+                    + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " 
date_pk+1, date1+1, date2+1 )"
+                    + " INCLUDE (long_col1, long_col2)";
+            PreparedStatement stmt = conn.prepareStatement(ddl);
+            stmt.execute();
+
+            // update index pk column and covered column
+            String upsert = "UPSERT INTO "
                     + fullDataTableName
-                    + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, 
date_pk, a.varchar_col1) VALUES(?, ?, ?, ?, ?, ?, ?)";
+                    + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, 
date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
 
             stmt = conn.prepareStatement(upsert);
             stmt.setString(1, "varchar1");
             stmt.setString(2, "char1");
             stmt.setInt(3, 1);
             stmt.setLong(4, 1l);
-            stmt.setBigDecimal(5, new BigDecimal(1.0));
+            stmt.setBigDecimal(5, new BigDecimal(0.5));
             stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00"));
             stmt.setString(7, "a.varchar_updated");
+            stmt.setLong(8, 101);
             stmt.executeUpdate();
             conn.commit();
 
             // verify only one row was updated in the data table
-            String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || 
'_' || UPPER(varchar_col1) || '_' || UPPER(char_col2) from "
+            String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || 
'_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from "
                     + fullDataTableName;
-            rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " 
+ selectSql);
+            ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ 
NO_INDEX */ " + selectSql);
             assertTrue(rs.next());
             assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", 
rs.getString(1));
+            assertEquals(101, rs.getLong(2));
             assertTrue(rs.next());
             assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", 
rs.getString(1));
-            assertTrue(rs.next());
-            assertEquals("VARCHAR3_CHAR3 _A.VARCHAR3_B.CHAR3   ", 
rs.getString(1));
-            assertTrue(rs.next());
-            assertEquals("VARCHAR4_CHAR4 _A.VARCHAR4_B.CHAR4   ", 
rs.getString(1));
+            assertEquals(2, rs.getLong(2));
             assertFalse(rs.next());
 
             // verify that the rows in the index table are also updated
             rs = conn.createStatement().executeQuery("SELECT " + selectSql);
             assertTrue(rs.next());
-            // if the data table is immutable, the index table will have one 
more
-            // row
-            if (!mutable) {
-                assertEquals("VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ", 
rs.getString(1));
-                assertTrue(rs.next());
-            }
-            assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_" + (mutable ? 
"B.CHAR1   " : ""), rs.getString(1));
+            assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", 
rs.getString(1));
+            assertEquals(101, rs.getLong(2));
             assertTrue(rs.next());
             assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", 
rs.getString(1));
-            assertTrue(rs.next());
-            assertEquals("VARCHAR3_CHAR3 _A.VARCHAR3_B.CHAR3   ", 
rs.getString(1));
-            assertTrue(rs.next());
-            assertEquals("VARCHAR4_CHAR4 _A.VARCHAR4_B.CHAR4   ", 
rs.getString(1));
+            assertEquals(2, rs.getLong(2));
             assertFalse(rs.next());
             conn.createStatement().execute("DROP INDEX IDX ON " + 
fullDataTableName);
         } finally {
@@ -546,8 +576,6 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX 
ON " + fullDataTableName
                     + " (int_col1+1)";
 
-            conn = DriverManager.getConnection(getUrl(), props);
-            conn.setAutoCommit(false);
             PreparedStatement stmt = conn.prepareStatement(ddl);
             stmt.execute();
             String sql = "SELECT int_col1+1 FROM " + fullDataTableName + " 
where int_col1+1 IN (2)";
@@ -567,25 +595,25 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
 
     @Test
     public void testOrderByWithImmutableIndex() throws Exception {
-        helpTestOrderByWithIndex(false, false);
+        helpTestSelectAliasAndOrderByWithIndex(false, false);
     }
 
     @Test
     public void testOrderByWithImmutableLocalIndex() throws Exception {
-        helpTestOrderByWithIndex(false, true);
+        helpTestSelectAliasAndOrderByWithIndex(false, true);
     }
 
     @Test
     public void testOrderByWithMutableIndex() throws Exception {
-        helpTestOrderByWithIndex(true, false);
+        helpTestSelectAliasAndOrderByWithIndex(true, false);
     }
 
     @Test
     public void testOrderByWithMutableLocalIndex() throws Exception {
-        helpTestOrderByWithIndex(true, false);
+        helpTestSelectAliasAndOrderByWithIndex(true, false);
     }
 
-    protected void helpTestOrderByWithIndex(boolean mutable, boolean 
localIndex) throws Exception {
+    protected void helpTestSelectAliasAndOrderByWithIndex(boolean mutable, 
boolean localIndex) throws Exception {
         String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : 
INDEX_DATA_TABLE;
         String fullDataTableName = INDEX_DATA_SCHEMA + 
QueryConstants.NAME_SEPARATOR + dataTableName;
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
@@ -596,11 +624,9 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX 
ON " + fullDataTableName
                     + " (int_col1+1)";
 
-            conn = DriverManager.getConnection(getUrl(), props);
-            conn.setAutoCommit(false);
             PreparedStatement stmt = conn.prepareStatement(ddl);
             stmt.execute();
-            String sql = "SELECT int_col1+1 FROM " + fullDataTableName + " 
ORDER BY int_col1+1";
+            String sql = "SELECT int_col1+1 AS foo FROM " + fullDataTableName 
+ " ORDER BY foo";
             ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + 
sql);
             assertEquals("CLIENT PARALLEL 1-WAY "
                     + (localIndex ? "RANGE SCAN OVER _LOCAL_IDX_" + 
fullDataTableName
@@ -620,66 +646,13 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
     }
 
     @Test
-    public void testSelectColOnlyInDataTableImmutableIndex() throws Exception {
-        helpTestSelectColOnlyInDataTable(false, false);
-    }
-
-    @Test
-    public void testSelectColOnlyInDataTableImmutableLocalIndex() throws 
Exception {
-        helpTestSelectColOnlyInDataTable(false, true);
-    }
-
-    @Test
-    public void testSelectColOnlyInDataTableMutableIndex() throws Exception {
-        helpTestSelectColOnlyInDataTable(true, false);
-    }
-
-    @Test
-    public void testSelectColOnlyInDataTableMutableLocalIndex() throws 
Exception {
-        helpTestSelectColOnlyInDataTable(true, false);
-    }
-
-    protected void helpTestSelectColOnlyInDataTable(boolean mutable, boolean 
localIndex) throws Exception {
-        String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : 
INDEX_DATA_TABLE;
-        String fullDataTableName = INDEX_DATA_SCHEMA + 
QueryConstants.NAME_SEPARATOR + dataTableName;
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        try {
-            conn.setAutoCommit(false);
-            populateDataTable(conn, dataTableName);
-            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX 
ON " + fullDataTableName
-                    + " (int_col1+1)";
-
-            conn = DriverManager.getConnection(getUrl(), props);
-            conn.setAutoCommit(false);
-            PreparedStatement stmt = conn.prepareStatement(ddl);
-            stmt.execute();
-            String sql = "SELECT int_col1+1, int_col2 FROM " + 
fullDataTableName + " WHERE int_col1+1=2";
-            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + 
sql);
-            assertEquals("CLIENT PARALLEL 1-WAY "
-                    + (localIndex ? "RANGE SCAN OVER _LOCAL_IDX_" + 
fullDataTableName
-                            + " [-32768,2]\n    SERVER FILTER BY FIRST KEY 
ONLY\nCLIENT MERGE SORT" : "FULL SCAN OVER "
-                            + fullDataTableName + "\n    SERVER FILTER BY 
(A.INT_COL1 + 1) = 2"),
-                    QueryUtil.getExplainPlan(rs));
-            rs = conn.createStatement().executeQuery(sql);
-            assertTrue(rs.next());
-            assertEquals(2, rs.getInt(1));
-            assertEquals(1, rs.getInt(2));
-            assertFalse(rs.next());
-            conn.createStatement().execute("DROP INDEX IDX ON " + 
fullDataTableName);
-        } finally {
-            conn.close();
-        }
-    }
-    
-    @Test
     public void testImmutableIndexWithCaseSensitiveCols() throws Exception {
         helpTestIndexWithCaseSensitiveCols(false, false);
     }
     
     @Test
     public void testImmutableLocalIndexWithCaseSensitiveCols() throws 
Exception {
-        helpTestIndexWithCaseSensitiveCols(true, false);
+        helpTestIndexWithCaseSensitiveCols(false, true);
     }
     
     @Test
@@ -689,27 +662,25 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
     
     @Test
     public void testMutableLocalIndexWithCaseSensitiveCols() throws Exception {
-        helpTestIndexWithCaseSensitiveCols(true, false);
+        helpTestIndexWithCaseSensitiveCols(true, true);
     }
     
     protected void helpTestIndexWithCaseSensitiveCols(boolean mutable, boolean 
localIndex) throws Exception {
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
-            conn.createStatement().execute("CREATE TABLE cs (k VARCHAR NOT 
NULL PRIMARY KEY, \"V1\" VARCHAR, \"v2\" VARCHAR) "+ (mutable ? 
"IMMUTABLE_ROWS=true" : ""));
+            conn.createStatement().execute("CREATE TABLE cs (k VARCHAR NOT 
NULL PRIMARY KEY, \"cf1\".\"V1\" VARCHAR, \"CF2\".\"v2\" VARCHAR) "+ (mutable ? 
"IMMUTABLE_ROWS=true" : ""));
             String query = "SELECT * FROM cs";
             ResultSet rs = conn.createStatement().executeQuery(query);
             assertFalse(rs.next());
-            if (localIndex) {
-                conn.createStatement().execute("CREATE LOCAL INDEX ics ON cs 
(\"v2\" || '_modified') INCLUDE (\"V1\",\"v2\")");
-            } else {
-                conn.createStatement().execute("CREATE INDEX ics ON cs (\"V1\" 
|| '_' || \"v2\") INCLUDE (\"V1\",\"v2\")");
-            }
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX ics 
ON cs (\"cf1\".\"V1\" || '_' || \"CF2\".\"v2\") INCLUDE (\"V1\",\"v2\")";
+            PreparedStatement stmt = conn.prepareStatement(ddl);
+            stmt.execute();
             query = "SELECT * FROM ics";
             rs = conn.createStatement().executeQuery(query);
             assertFalse(rs.next());
 
-            PreparedStatement stmt = conn.prepareStatement("UPSERT INTO cs 
VALUES(?,?,?)");
+            stmt = conn.prepareStatement("UPSERT INTO cs VALUES(?,?,?)");
             stmt.setString(1,"a");
             stmt.setString(2, "x");
             stmt.setString(3, "1");
@@ -720,7 +691,6 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             stmt.execute();
             conn.commit();
 
-            //TODO FIX THIS change this to *
             query = "SELECT (\"V1\" || '_' || \"v2\"), k, \"V1\", \"v2\"  FROM 
cs WHERE (\"V1\" || '_' || \"v2\") = 'x_1'";
             rs = conn.createStatement().executeQuery("EXPLAIN " + query);
             if(localIndex){
@@ -737,7 +707,7 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             assertEquals("x",rs.getString(3));
             assertEquals("1",rs.getString(4));
             //TODO figure out why this " " is needed
-            assertEquals("x_1",rs.getString("\"('V1' || '_' || 'v2')\""));
+            assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 
'CF2'.'v2')\""));
             assertEquals("a",rs.getString("k"));
             assertEquals("x",rs.getString("V1"));
             assertEquals("1",rs.getString("v2"));
@@ -763,7 +733,7 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             assertEquals("x_1",rs.getString(4));
             assertEquals("x_1",rs.getString("Foo1"));
             assertEquals("x_1",rs.getString(5));
-            assertEquals("x_1",rs.getString("\"('V1' || '_' || 'v2')\""));
+            assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 
'CF2'.'v2')\""));
             assertTrue(rs.next());
             assertEquals("y",rs.getString(1));
             assertEquals("y",rs.getString("V1"));
@@ -774,12 +744,65 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
             assertEquals("y_2",rs.getString(4));
             assertEquals("y_2",rs.getString("Foo1"));
             assertEquals("y_2",rs.getString(5));
-            assertEquals("y_2",rs.getString("\"('V1' || '_' || 'v2')\""));
+            assertEquals("y_2",rs.getString("\"('cf1'.'V1' || '_' || 
'CF2'.'v2')\""));
             assertFalse(rs.next());
             conn.createStatement().execute("DROP INDEX ICS ON CS");
         } finally {
             conn.close();
         }
+    }    
+    
+    @Test
+    public void testSelectColOnlyInDataTableImmutableIndex() throws Exception {
+        helpTestSelectColOnlyInDataTable(false, false);
+    }
+
+    @Test
+    public void testSelectColOnlyInDataTableImmutableLocalIndex() throws 
Exception {
+        helpTestSelectColOnlyInDataTable(false, true);
+    }
+
+    @Test
+    public void testSelectColOnlyInDataTableMutableIndex() throws Exception {
+        helpTestSelectColOnlyInDataTable(true, false);
+    }
+
+    @Test
+    public void testSelectColOnlyInDataTableMutableLocalIndex() throws 
Exception {
+        helpTestSelectColOnlyInDataTable(true, true);
+    }
+
+    protected void helpTestSelectColOnlyInDataTable(boolean mutable, boolean 
localIndex) throws Exception {
+        String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : 
INDEX_DATA_TABLE;
+        String fullDataTableName = INDEX_DATA_SCHEMA + 
QueryConstants.NAME_SEPARATOR + dataTableName;
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.setAutoCommit(false);
+            populateDataTable(conn, dataTableName);
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX 
ON " + fullDataTableName
+                    + " (int_col1+1)";
+
+            conn = DriverManager.getConnection(getUrl(), props);
+            conn.setAutoCommit(false);
+            PreparedStatement stmt = conn.prepareStatement(ddl);
+            stmt.execute();
+            String sql = "SELECT int_col1+1, int_col2 FROM " + 
fullDataTableName + " WHERE int_col1+1=2";
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + 
sql);
+            assertEquals("CLIENT PARALLEL 1-WAY "
+                    + (localIndex ? "RANGE SCAN OVER _LOCAL_IDX_" + 
fullDataTableName
+                            + " [-32768,2]\n    SERVER FILTER BY FIRST KEY 
ONLY\nCLIENT MERGE SORT" : "FULL SCAN OVER "
+                            + fullDataTableName + "\n    SERVER FILTER BY 
(A.INT_COL1 + 1) = 2"),
+                    QueryUtil.getExplainPlan(rs));
+            rs = conn.createStatement().executeQuery(sql);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertEquals(1, rs.getInt(2));
+            assertFalse(rs.next());
+            conn.createStatement().execute("DROP INDEX IDX ON " + 
fullDataTableName);
+        } finally {
+            conn.close();
+        }
     }
     
     @Test
@@ -809,58 +832,439 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
 
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
         Connection conn = DriverManager.getConnection(getUrl(), props);
-        conn.setAutoCommit(false);
-
-        // make sure that the tables are empty, but reachable
-        conn.createStatement().execute(
-          "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 
VARCHAR)");
-        query = "SELECT * FROM t" ;
-        rs = conn.createStatement().executeQuery(query);
-        assertFalse(rs.next());
-        String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" 
: "h");
-        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " 
INDEX " + indexName + " ON t (v1 || '_' || v2)");
+        try {
+               conn.setAutoCommit(false);
+       
+               // make sure that the tables are empty, but reachable
+               conn.createStatement().execute(
+                 "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, 
v2 VARCHAR)");
+               query = "SELECT * FROM t" ;
+               rs = conn.createStatement().executeQuery(query);
+               assertFalse(rs.next());
+               String indexName = "it_" + (mutable ? "m" : "im") + "_" + 
(local ? "l" : "h");
+               conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : 
"") + " INDEX " + indexName + " ON t (v1 || '_' || v2)");
+       
+               query = "SELECT * FROM t";
+               rs = conn.createStatement().executeQuery(query);
+               assertFalse(rs.next());
+       
+               // load some data into the table
+               stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
+               stmt.setString(1, "a");
+               stmt.setString(2, "x");
+               stmt.setString(3, "1");
+               stmt.execute();
+               conn.commit();
+       
+               assertIndexExists(conn,true);
+               conn.createStatement().execute("ALTER TABLE t DROP COLUMN v1");
+               assertIndexExists(conn,false);
+       
+               query = "SELECT * FROM t";
+               rs = conn.createStatement().executeQuery(query);
+               assertTrue(rs.next());
+               assertEquals("a",rs.getString(1));
+               assertEquals("1",rs.getString(2));
+               assertFalse(rs.next());
+       
+               // load some data into the table
+               stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)");
+               stmt.setString(1, "a");
+               stmt.setString(2, "2");
+               stmt.execute();
+               conn.commit();
+       
+               query = "SELECT * FROM t";
+               rs = conn.createStatement().executeQuery(query);
+               assertTrue(rs.next());
+               assertEquals("a",rs.getString(1));
+               assertEquals("2",rs.getString(2));
+               assertFalse(rs.next());
+        }
+        finally {
+               conn.close();
+        }
+    }
+    
+    private static void assertIndexExists(Connection conn, boolean exists) 
throws SQLException {
+        ResultSet rs = conn.getMetaData().getIndexInfo(null, null, "T", false, 
false);
+        assertEquals(exists, rs.next());
+    }
+    
+    @Test
+    public void testImmutableIndexDropCoveredColumn() throws Exception {
+       helpTestDropCoveredColumn(false, false);
+    }
+    
+    @Test
+    public void testImmutableLocalIndexDropCoveredColumn() throws Exception {
+       helpTestDropCoveredColumn(false, true);
+    }
+    
+    @Test
+    public void testMutableIndexDropCoveredColumn() throws Exception {
+       helpTestDropCoveredColumn(true, false);
+    }
+    
+    @Test
+    public void testMutableLocalIndexDropCoveredColumn() throws Exception {
+       helpTestDropCoveredColumn(true, true);
+    }
+    
+    public void helpTestDropCoveredColumn(boolean mutable, boolean local) 
throws Exception {
+        ResultSet rs;
+        PreparedStatement stmt;
 
-        query = "SELECT * FROM t";
-        rs = conn.createStatement().executeQuery(query);
-        assertFalse(rs.next());
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+               conn.setAutoCommit(false);
+       
+               // make sure that the tables are empty, but reachable
+               conn.createStatement().execute(
+                 "CREATE TABLE t"
+                     + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 
VARCHAR, v3 VARCHAR)");
+               String dataTableQuery = "SELECT * FROM t";
+               rs = conn.createStatement().executeQuery(dataTableQuery);
+               assertFalse(rs.next());
+       
+               String indexName = "it_" + (mutable ? "m" : "im") + "_" + 
(local ? "l" : "h");
+               conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : 
"") + " INDEX " + indexName + " ON t (k || '_' || v1) include (v2, v3)");
+               String indexTableQuery = "SELECT * FROM " + indexName;
+               rs = conn.createStatement().executeQuery(indexTableQuery);
+               assertFalse(rs.next());
+       
+               // load some data into the table
+               stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?,?)");
+               stmt.setString(1, "a");
+               stmt.setString(2, "x");
+               stmt.setString(3, "1");
+               stmt.setString(4, "j");
+               stmt.execute();
+               conn.commit();
+       
+               assertIndexExists(conn,true);
+               conn.createStatement().execute("ALTER TABLE t DROP COLUMN v2");
+               assertIndexExists(conn,true);
+       
+               // verify data table rows
+               rs = conn.createStatement().executeQuery(dataTableQuery);
+               assertTrue(rs.next());
+               assertEquals("a",rs.getString(1));
+               assertEquals("x",rs.getString(2));
+               assertEquals("j",rs.getString(3));
+               assertFalse(rs.next());
+               
+               // verify index table rows
+               rs = conn.createStatement().executeQuery(indexTableQuery);
+               assertTrue(rs.next());
+               assertEquals("a_x",rs.getString(1));
+               assertEquals("a",rs.getString(2));
+               assertEquals("j",rs.getString(3));
+               assertFalse(rs.next());
+       
+               // add another row
+               stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
+               stmt.setString(1, "b");
+               stmt.setString(2, "y");
+               stmt.setString(3, "k");
+               stmt.execute();
+               conn.commit();
+       
+               // verify data table rows
+               rs = conn.createStatement().executeQuery(dataTableQuery);
+               assertTrue(rs.next());
+               assertEquals("a",rs.getString(1));
+               assertEquals("x",rs.getString(2));
+               assertEquals("j",rs.getString(3));
+               assertTrue(rs.next());
+               assertEquals("b",rs.getString(1));
+               assertEquals("y",rs.getString(2));
+               assertEquals("k",rs.getString(3));
+               assertFalse(rs.next());
+               
+               // verify index table rows
+               rs = conn.createStatement().executeQuery(indexTableQuery);
+               assertTrue(rs.next());
+               assertEquals("a_x",rs.getString(1));
+               assertEquals("a",rs.getString(2));
+               assertEquals("j",rs.getString(3));
+               assertTrue(rs.next());
+               assertEquals("b_y",rs.getString(1));
+               assertEquals("b",rs.getString(2));
+               assertEquals("k",rs.getString(3));
+               assertFalse(rs.next());
+        }
+        finally {
+               conn.close();
+        }
+    }
+    
+    @Test
+    public void testImmutableIndexAddPKColumnToTable() throws Exception {
+       helpTestAddPKColumnToTable(false, false);
+    }
+    
+    @Test
+    public void testImmutableLocalIndexAddPKColumnToTable() throws Exception {
+       helpTestAddPKColumnToTable(false, true);
+    }
+    
+    @Test
+    public void testMutableIndexAddPKColumnToTable() throws Exception {
+       helpTestAddPKColumnToTable(true, false);
+    }
+    
+    @Test
+    public void testMutableLocalIndexAddPKColumnToTable() throws Exception {
+       helpTestAddPKColumnToTable(true, true);
+    }
+    
+    public void helpTestAddPKColumnToTable(boolean mutable, boolean local) 
throws Exception {
+        ResultSet rs;
+        PreparedStatement stmt;
 
-        // load some data into the table
-        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
-        stmt.setString(1, "a");
-        stmt.setString(2, "x");
-        stmt.setString(3, "1");
-        stmt.execute();
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+               conn.setAutoCommit(false);
+       
+               // make sure that the tables are empty, but reachable
+               conn.createStatement().execute(
+                 "CREATE TABLE t"
+                     + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 
VARCHAR)");
+               String dataTableQuery = "SELECT * FROM t";
+               rs = conn.createStatement().executeQuery(dataTableQuery);
+               assertFalse(rs.next());
+       
+               String indexName = "IT_" + (mutable ? "M" : "IM") + "_" + 
(local ? "L" : "H");
+               conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : 
"") + " INDEX " + indexName + " ON t (v1 || '_' || v2)");
+               String indexTableQuery = "SELECT * FROM " + indexName;
+               rs = conn.createStatement().executeQuery(indexTableQuery);
+               assertFalse(rs.next());
+       
+               // load some data into the table
+               stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
+               stmt.setString(1, "a");
+               stmt.setString(2, "x");
+               stmt.setString(3, "1");
+               stmt.execute();
+               conn.commit();
+       
+               assertIndexExists(conn,true);
+               conn.createStatement().execute("ALTER TABLE t ADD v3 VARCHAR, 
k2 DECIMAL PRIMARY KEY");
+               rs = conn.getMetaData().getPrimaryKeys("", "", "T");
+               assertTrue(rs.next());
+               assertEquals("K",rs.getString("COLUMN_NAME"));
+               assertEquals(1, rs.getShort("KEY_SEQ"));
+               assertTrue(rs.next());
+               assertEquals("K2",rs.getString("COLUMN_NAME"));
+               assertEquals(2, rs.getShort("KEY_SEQ"));
+       
+               rs = conn.getMetaData().getPrimaryKeys("", "", indexName);
+               assertTrue(rs.next());
+               assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "(V1 || '_' || 
V2)",rs.getString("COLUMN_NAME"));
+               int offset = local ? 1 : 0;
+               assertEquals(offset+1, rs.getShort("KEY_SEQ"));
+               assertTrue(rs.next());
+               assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + 
"K",rs.getString("COLUMN_NAME"));
+               assertEquals(offset+2, rs.getShort("KEY_SEQ"));
+               assertTrue(rs.next());
+               assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + 
"K2",rs.getString("COLUMN_NAME"));
+               assertEquals(offset+3, rs.getShort("KEY_SEQ"));
+       
+               // verify data table rows
+               rs = conn.createStatement().executeQuery(dataTableQuery);
+               assertTrue(rs.next());
+               assertEquals("a",rs.getString(1));
+               assertEquals("x",rs.getString(2));
+               assertEquals("1",rs.getString(3));
+               assertNull(rs.getBigDecimal(4));
+               assertFalse(rs.next());
+               
+               // verify index table rows
+               rs = conn.createStatement().executeQuery(indexTableQuery);
+               assertTrue(rs.next());
+               assertEquals("x_1",rs.getString(1));
+               assertEquals("a",rs.getString(2));
+               assertNull(rs.getBigDecimal(3));
+               assertFalse(rs.next());
+       
+               // load some data into the table
+               stmt = conn.prepareStatement("UPSERT INTO t(K,K2,V1,V2) 
VALUES(?,?,?,?)");
+               stmt.setString(1, "b");
+               stmt.setBigDecimal(2, BigDecimal.valueOf(2));
+               stmt.setString(3, "y");
+               stmt.setString(4, "2");
+               stmt.execute();
+               conn.commit();
+       
+               // verify data table rows
+               rs = conn.createStatement().executeQuery(dataTableQuery);
+               assertTrue(rs.next());
+               assertEquals("a",rs.getString(1));
+               assertEquals("x",rs.getString(2));
+               assertEquals("1",rs.getString(3));
+               assertNull(rs.getString(4));
+               assertNull(rs.getBigDecimal(5));
+               assertTrue(rs.next());
+               assertEquals("b",rs.getString(1));
+               assertEquals("y",rs.getString(2));
+               assertEquals("2",rs.getString(3));
+               assertNull(rs.getString(4));
+               assertEquals(BigDecimal.valueOf(2),rs.getBigDecimal(5));
+               assertFalse(rs.next());
+               
+               // verify index table rows
+               rs = conn.createStatement().executeQuery(indexTableQuery);
+               assertTrue(rs.next());
+               assertEquals("x_1",rs.getString(1));
+               assertEquals("a",rs.getString(2));
+               assertNull(rs.getBigDecimal(3));
+               assertTrue(rs.next());
+               assertEquals("y_2",rs.getString(1));
+               assertEquals("b",rs.getString(2));
+               assertEquals(BigDecimal.valueOf(2),rs.getBigDecimal(3));
+               assertFalse(rs.next());
+        }
+        finally {
+               conn.close();
+        }
+    }
+    
+    @Test
+    public void testUpdatableViewWithIndex() throws Exception {
+        helpTestUpdatableViewIndex(false);
+    }
+    
+    @Test
+    public void testUpdatableViewWithLocalIndex() throws Exception {
+        helpTestUpdatableViewIndex(true);
+    }
+       
+    private void helpTestUpdatableViewIndex(boolean local) throws Exception {
+       Connection conn = DriverManager.getConnection(getUrl());
+       try {
+               String ddl = "CREATE TABLE t (k1 INTEGER NOT NULL, k2 INTEGER 
NOT NULL, k3 DECIMAL, s1 VARCHAR, s2 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2, 
k3))";
+               conn.createStatement().execute(ddl);
+               ddl = "CREATE VIEW v AS SELECT * FROM t WHERE k1 = 1";
+               conn.createStatement().execute(ddl);
+               conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) 
VALUES(120,'foo0','bar0',50.0)");
+               conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) 
VALUES(121,'foo1','bar1',51.0)");
+               conn.commit();
+               
+               ResultSet rs;
+               conn.createStatement().execute("CREATE " + (local ? "LOCAL" : 
"") + " INDEX i1 on v(k1+k2+k3) include (s1, s2)");
+               conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) 
VALUES(120,'foo2','bar2',50.0)");
+               conn.commit();
+       
+               String query = "SELECT k1, k2, k3, s1, s2 FROM v WHERE  
k1+k2+k3 = 173.0";
+               rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+               String queryPlan = QueryUtil.getExplainPlan(rs);
+               if (local) {
+                   assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER 
_LOCAL_IDX_T [-32768,173]\n" + "CLIENT MERGE SORT",
+                           queryPlan);
+               } else {
+                   assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T 
[" + Short.MIN_VALUE + ",173]", queryPlan);
+               }
+               rs = conn.createStatement().executeQuery(query);
+               assertTrue(rs.next());
+               assertEquals(1, rs.getInt(1));
+               assertEquals(121, rs.getInt(2));
+               
assertTrue(BigDecimal.valueOf(51.0).compareTo(rs.getBigDecimal(3))==0);
+               assertEquals("foo1", rs.getString(4));
+               assertEquals("bar1", rs.getString(5));
+               assertFalse(rs.next());
+       
+               conn.createStatement().execute("CREATE " + (local ? "LOCAL" : 
"") + " INDEX i2 on v(s1||'_'||s2)");
+               
+               query = "SELECT k1, k2, s1||'_'||s2 FROM v WHERE 
(s1||'_'||s2)='foo2_bar2'";
+               rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+               if (local) {
+                   assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER 
_LOCAL_IDX_T [" + (Short.MIN_VALUE + 1)
+                           + ",'foo2_bar2']\n" + "    SERVER FILTER BY FIRST 
KEY ONLY\n" + "CLIENT MERGE SORT",
+                           QueryUtil.getExplainPlan(rs));
+               } else {
+                   assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T 
[" + (Short.MIN_VALUE + 1) + ",'foo2_bar2']\n"
+                           + "    SERVER FILTER BY FIRST KEY ONLY", 
QueryUtil.getExplainPlan(rs));
+               }
+               rs = conn.createStatement().executeQuery(query);
+               assertTrue(rs.next());
+               assertEquals(1, rs.getInt(1));
+               assertEquals(120, rs.getInt(2));
+               assertEquals("foo2_bar2", rs.getString(3));
+               assertFalse(rs.next());
+       }
+        finally {
+               conn.close();
+        }
+    }
+    
+    @Test
+    public void testViewUsesTableIndex() throws Exception {
+        ResultSet rs;
+        Connection conn = DriverManager.getConnection(getUrl());
+        String ddl = "CREATE TABLE t (k1 INTEGER NOT NULL, k2 INTEGER NOT 
NULL, s1 VARCHAR, s2 VARCHAR, s3 VARCHAR, s4 VARCHAR CONSTRAINT pk PRIMARY KEY 
(k1, k2))";
+        conn.createStatement().execute(ddl);
+        conn.createStatement().execute("CREATE INDEX i1 ON t(k2, s2, s3, s1)");
+        conn.createStatement().execute("CREATE INDEX i2 ON t(k2, s2||'_'||s3, 
s1, s4)");
+        
+        ddl = "CREATE VIEW v AS SELECT * FROM t WHERE s1 = 'foo'";
+        conn.createStatement().execute(ddl);
+        conn.createStatement().execute("UPSERT INTO t 
VALUES(1,1,'foo','abc','cab')");
+        conn.createStatement().execute("UPSERT INTO t 
VALUES(2,2,'bar','xyz','zyx')");
         conn.commit();
-
-        assertIndexExists(conn,true);
-        conn.createStatement().execute("ALTER TABLE t DROP COLUMN v1");
-        assertIndexExists(conn,false);
-
-        query = "SELECT * FROM t";
-        rs = conn.createStatement().executeQuery(query);
+        
+        rs = conn.createStatement().executeQuery("SELECT count(*) FROM v");
         assertTrue(rs.next());
-        assertEquals("a",rs.getString(1));
-        assertEquals("1",rs.getString(2));
+        assertEquals(1, rs.getLong(1));
         assertFalse(rs.next());
-
-        // load some data into the table
-        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)");
-        stmt.setString(1, "a");
-        stmt.setString(2, "2");
-        stmt.execute();
-        conn.commit();
-
-        query = "SELECT * FROM t";
+        
+        conn.createStatement().execute("CREATE INDEX vi1 on v(k2)");
+
+        //i2 should be used since it contains s3||'_'||s4 i
+        String query = "SELECT s2||'_'||s3 FROM v WHERE k2=1 AND 
(s2||'_'||s3)='abc_cab'";
+        rs = conn.createStatement(  ).executeQuery("EXPLAIN " + query);
+        String queryPlan = QueryUtil.getExplainPlan(rs);
+        assertEquals(
+                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER I2 
[1,'abc_cab','foo']\n" + 
+                "    SERVER FILTER BY FIRST KEY ONLY", queryPlan);
         rs = conn.createStatement().executeQuery(query);
         assertTrue(rs.next());
-        assertEquals("a",rs.getString(1));
-        assertEquals("2",rs.getString(2));
+        assertEquals("abc_cab", rs.getString(1));
         assertFalse(rs.next());
+        
+        conn.createStatement().execute("ALTER VIEW v DROP COLUMN s4");
+        conn.createStatement().execute("CREATE INDEX vi2 on v(k2)");
+        //i2 cannot be used since s4 has been dropped from the view, so i1 
will be used 
+        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+        queryPlan = QueryUtil.getExplainPlan(rs);
+        assertEquals(
+                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER I1 [1]\n" + 
+                "    SERVER FILTER BY FIRST KEY ONLY AND ((\"S2\" || '_' || 
\"S3\") = 'abc_cab' AND \"S1\" = 'foo')", queryPlan);
+        rs = conn.createStatement().executeQuery(query);
+        assertTrue(rs.next());
+        assertEquals("abc_cab", rs.getString(1));
+        assertFalse(rs.next());    
     }
     
-    private static void assertIndexExists(Connection conn, boolean exists) 
throws SQLException {
-        ResultSet rs = conn.getMetaData().getIndexInfo(null, null, "T", false, 
false);
-        assertEquals(exists, rs.next());
-    }
+       @Test
+       public void testExpressionThrowsException() throws Exception {
+               Connection conn = DriverManager.getConnection(getUrl());
+               String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY, k2 
INTEGER)";
+               try {
+                       conn.createStatement().execute(ddl);
+                       ddl = "CREATE INDEX i on t(k1/k2)";
+                       conn.createStatement().execute(ddl);
+                       // upsert should succeed
+                       conn.createStatement().execute("UPSERT INTO T 
VALUES(1,1)");
+                       conn.commit();
+                       // divide by zero should fail
+                       conn.createStatement().execute("UPSERT INTO T 
VALUES(1,0)");
+                       conn.commit();
+                       fail();
+               } catch (CommitException e) {
+               }
+       }
 
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
 
b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
index ce81e1f..e234498 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
@@ -61,7 +61,6 @@ import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collections;
-import java.util.Comparator;
 import java.util.List;
 import java.util.Set;
 
@@ -1414,12 +1413,12 @@ public class MetaDataEndpointImpl extends 
MetaDataProtocol implements Coprocesso
                                     for (PTable index : table.getIndexes()) {
                                         try {
                                             IndexMaintainer indexMaintainer = 
index.getIndexMaintainer(table, connection);
-                                            // get the columns required to 
create the index 
+                                            // get the columns required for 
the index pk
                                             Set<ColumnReference> indexColumns 
= indexMaintainer.getIndexedColumns();
                                             byte[] indexKey =
                                                     
SchemaUtil.getTableKey(tenantId, index
                                                             
.getSchemaName().getBytes(), index.getTableName().getBytes());
-                                            // If index requires this column, 
then drop it
+                                            // If index requires this column 
for its pk, then drop it
                                             if (indexColumns.contains(new 
ColumnReference(columnToDelete.getFamilyName().getBytes(), 
columnToDelete.getName().getBytes()))) {
                                                 // Since we're dropping the 
index, lock it to ensure
                                                 // that a change in index 
state doesn't

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java 
b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
index b2ca979..f4b4f98 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
@@ -154,8 +154,9 @@ public enum SQLExceptionCode {
      /**
       *  Expression Index exceptions.
       */
-     AGGREGATE_EXPRESSION_NOT_ALLOWED_IN_INDEX(520, "42897", "Aggreagaate 
expression are not allowed in an index"),
-     NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX(521, "42898", 
"Non-deterministic expression are not allowed in an index"),
+     AGGREGATE_EXPRESSION_NOT_ALLOWED_IN_INDEX(520, "42897", "Aggreagaate 
expression not allowed in an index"),
+     NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX(521, "42898", 
"Non-deterministic expression not allowed in an index"),
+     STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX(522, "42899", "Stateless 
expression not allowed in an index"),
 
      /** 
      * HBase and Phoenix specific implementation defined sub-classes.

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java 
b/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java
index 7199dad..fd006c9 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/index/IndexMaintainer.java
@@ -692,10 +692,10 @@ public class IndexMaintainer implements Writable, 
Iterable<ColumnReference> {
                 indexFields[pos] = dataRowKeySchema.getField(i);
             } 
         }
-        Iterator<Expression> expressionSetItr = indexedExpressions.iterator();
+        Iterator<Expression> expressionItr = indexedExpressions.iterator();
         for (Field indexField : indexFields) {
             if (indexField == null) { // Add field for kv column in index
-                final PDataType dataType = 
expressionSetItr.next().getDataType();
+                final PDataType dataType = expressionItr.next().getDataType();
                 builder.addField(new PDatum() {
 
                     @Override

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java 
b/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java
index 61ee081..64e62f5 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java
@@ -433,14 +433,34 @@ public class MetaDataClient {
         for (PTable index : indexes) {
             if (index.getViewIndexId() == null) {
                 boolean containsAllReqdCols = true;
-                // Ensure that all indexed columns from index on physical table
+                // Ensure that all columns required to create index
                 // exist in the view too (since view columns may be removed)
-                List<PColumn> pkColumns = index.getPKColumns();
-                for (int i = index.getBucketNum() == null ? 0 : 1; i < 
pkColumns.size(); i++) {
+                IndexMaintainer indexMaintainer = 
index.getIndexMaintainer(physicalTable, connection);
+                // check that the columns required for the index pk (not 
including the pk columns of the data table)
+                // are present in the view
+                Set<ColumnReference> indexColRefs = 
indexMaintainer.getIndexedColumns();
+                for (ColumnReference colRef : indexColRefs) {
+                    try {
+                        byte[] cf= colRef.getFamily();
+                        byte[] cq= colRef.getQualifier();
+                        if (cf!=null) {
+                            table.getColumnFamily(cf).getColumn(cq);
+                        }
+                        else {
+                            table.getColumn( Bytes.toString(cq));
+                        }
+                    } catch (ColumnNotFoundException e) { // Ignore this index 
and continue with others
+                        containsAllReqdCols = false;
+                        break;
+                    }
+                }
+                // check that pk columns of the data table (which are also 
present in the index pk) are present in the view
+                List<PColumn> pkColumns = physicalTable.getPKColumns();
+                for (int i = physicalTable.getBucketNum() == null ? 0 : 1; i < 
pkColumns.size(); i++) {
                     try {
                         PColumn pkColumn = pkColumns.get(i);
-                        IndexUtil.getDataColumn(table, 
pkColumn.getName().getString());
-                    } catch (IllegalArgumentException e) { // Ignore this 
index and continue with others
+                        table.getColumn(pkColumn.getName().getString());
+                    } catch (ColumnNotFoundException e) { // Ignore this index 
and continue with others
                         containsAllReqdCols = false;
                         break;
                     }
@@ -993,9 +1013,8 @@ public class MetaDataClient {
                     if (expression.getDeterminism() != Determinism.ALWAYS) {
                         throw new 
SQLExceptionInfo.Builder(SQLExceptionCode.NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException();
                     }
-                    // true for any constant (including a view constant), as 
we don't need these in the index
                     if (expression.isStateless()) {
-                        continue;
+                        throw new 
SQLExceptionInfo.Builder(SQLExceptionCode.STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException();
                     }
                     unusedPkColumns.remove(expression);
                     

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e5a6308/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java 
b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
index 466db9f..4accd38 100644
--- 
a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
+++ 
b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
@@ -1493,5 +1493,77 @@ public class QueryCompilerTest extends 
BaseConnectionlessQueryTest {
         assertTrue(scan.getFilter() instanceof FirstKeyOnlyFilter);
         assertEquals(1, scan.getFamilyMap().size());
     }
+    
+    @Test 
+    public void testNonDeterministicExpressionIndex() throws Exception {
+        String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)";
+        Connection conn = DriverManager.getConnection(getUrl());
+        Statement stmt = null;
+        try {
+            stmt = conn.createStatement();
+            stmt.execute(ddl);
+            stmt.execute("CREATE INDEX i ON t (RAND())");
+            fail();
+        } catch (SQLException e) {
+            
assertEquals(SQLExceptionCode.NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(),
 e.getErrorCode());
+        }
+        finally {
+            stmt.close();
+        }
+    }
+    
+    @Test 
+    public void testStatelessExpressionIndex() throws Exception {
+        String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)";
+        Connection conn = DriverManager.getConnection(getUrl());
+        Statement stmt = null;
+        try {
+            stmt = conn.createStatement();
+            stmt.execute(ddl);
+            stmt.execute("CREATE INDEX i ON t (2)");
+            fail();
+        } catch (SQLException e) {
+            
assertEquals(SQLExceptionCode.STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(),
 e.getErrorCode());
+        }
+        finally {
+            stmt.close();
+        }
+    }
+    
+    @Test 
+    public void testAggregateExpressionIndex() throws Exception {
+        String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)";
+        Connection conn = DriverManager.getConnection(getUrl());
+        Statement stmt = null;
+        try {
+            stmt = conn.createStatement();
+            stmt.execute(ddl);
+            stmt.execute("CREATE INDEX i ON t (SUM(k1))");
+            fail();
+        } catch (SQLException e) {
+            
assertEquals(SQLExceptionCode.AGGREGATE_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(),
 e.getErrorCode());
+        }
+        finally {
+            stmt.close();
+        }
+    }
+    
+    @Test 
+    public void testDivideByZeroExpressionIndex() throws Exception {
+        String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY)";
+        Connection conn = DriverManager.getConnection(getUrl());
+        Statement stmt = null;
+        try {
+            stmt = conn.createStatement();
+            stmt.execute(ddl);
+            stmt.execute("CREATE INDEX i ON t (k1/0)");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.DIVIDE_BY_ZERO.getErrorCode(), 
e.getErrorCode());
+        }
+        finally {
+            stmt.close();
+        }
+    }
 
 }

Reply via email to