[05/50] [abbrv] phoenix git commit: PHOENIX-3042 Using functional index expression in where statement for join query fails

2016-08-05 Thread samarth 
PHOENIX-3042 Using functional index expression in where statement for join 
query fails


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

Branch: refs/heads/encodecolumns
Commit: 51214638f1fd3d7881addab2fd347d8ef10980cd
Parents: 6416181
Author: Thomas D'Silva 
Authored: Mon Jul 18 17:00:43 2016 -0700
Committer: Thomas D'Silva 
Committed: Mon Jul 18 17:00:43 2016 -0700

--
 .../end2end/index/IndexExpressionIT.java| 82 
 .../phoenix/compile/IndexStatementRewriter.java | 12 ++-
 .../apache/phoenix/compile/JoinCompiler.java| 17 ++--
 .../apache/phoenix/optimize/QueryOptimizer.java |  2 +-
 .../parse/IndexExpressionParseNodeRewriter.java |  8 +-
 .../phoenix/compile/QueryCompilerTest.java  | 41 +-
 6 files changed, 144 insertions(+), 18 deletions(-)
--


http://git-wip-us.apache.org/repos/asf/phoenix/blob/51214638/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 2e55594..21da43a 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
@@ -1411,5 +1411,87 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
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" +
+

phoenix git commit: PHOENIX-3042 Using functional index expression in where statement for join query fails

2016-07-18 Thread tdsilva 
Repository: phoenix
Updated Branches:
  refs/heads/master 6a0afa4fa -> a6f61cb40


PHOENIX-3042 Using functional index expression in where statement for join 
query fails


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

Branch: refs/heads/master
Commit: a6f61cb40c3eb031cd3b8b2192a243709bce37c6
Parents: 6a0afa4
Author: Thomas D'Silva 
Authored: Mon Jul 18 17:06:13 2016 -0700
Committer: Thomas D'Silva 
Committed: Mon Jul 18 17:06:13 2016 -0700

--
 .../end2end/index/IndexExpressionIT.java| 82 
 .../phoenix/compile/IndexStatementRewriter.java | 12 ++-
 .../apache/phoenix/compile/JoinCompiler.java| 17 ++--
 .../apache/phoenix/optimize/QueryOptimizer.java |  2 +-
 .../parse/IndexExpressionParseNodeRewriter.java |  8 +-
 .../phoenix/compile/QueryCompilerTest.java  | 41 +-
 6 files changed, 144 insertions(+), 18 deletions(-)
--


http://git-wip-us.apache.org/repos/asf/phoenix/blob/a6f61cb4/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 2e55594..21da43a 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
@@ -1411,5 +1411,87 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
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" +
+

phoenix git commit: PHOENIX-3042 Using functional index expression in where statement for join query fails

2016-07-18 Thread tdsilva 
Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-1.0 69782b958 -> 4f5bee662


PHOENIX-3042 Using functional index expression in where statement for join 
query fails


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

Branch: refs/heads/4.x-HBase-1.0
Commit: 4f5bee662b08964d836563c14d065de1e5fa6732
Parents: 69782b9
Author: Thomas D'Silva 
Authored: Mon Jul 18 17:05:35 2016 -0700
Committer: Thomas D'Silva 
Committed: Mon Jul 18 17:05:35 2016 -0700

--
 .../end2end/index/IndexExpressionIT.java| 82 
 .../phoenix/compile/IndexStatementRewriter.java | 12 ++-
 .../apache/phoenix/compile/JoinCompiler.java| 17 ++--
 .../apache/phoenix/optimize/QueryOptimizer.java |  2 +-
 .../parse/IndexExpressionParseNodeRewriter.java |  8 +-
 .../phoenix/compile/QueryCompilerTest.java  | 41 +-
 6 files changed, 144 insertions(+), 18 deletions(-)
--


http://git-wip-us.apache.org/repos/asf/phoenix/blob/4f5bee66/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 2e55594..21da43a 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
@@ -1411,5 +1411,87 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
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" +
+

phoenix git commit: PHOENIX-3042 Using functional index expression in where statement for join query fails

2016-07-18 Thread tdsilva 
Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-0.98 6416181f8 -> 51214638f


PHOENIX-3042 Using functional index expression in where statement for join 
query fails


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

Branch: refs/heads/4.x-HBase-0.98
Commit: 51214638f1fd3d7881addab2fd347d8ef10980cd
Parents: 6416181
Author: Thomas D'Silva 
Authored: Mon Jul 18 17:00:43 2016 -0700
Committer: Thomas D'Silva 
Committed: Mon Jul 18 17:00:43 2016 -0700

--
 .../end2end/index/IndexExpressionIT.java| 82 
 .../phoenix/compile/IndexStatementRewriter.java | 12 ++-
 .../apache/phoenix/compile/JoinCompiler.java| 17 ++--
 .../apache/phoenix/optimize/QueryOptimizer.java |  2 +-
 .../parse/IndexExpressionParseNodeRewriter.java |  8 +-
 .../phoenix/compile/QueryCompilerTest.java  | 41 +-
 6 files changed, 144 insertions(+), 18 deletions(-)
--


http://git-wip-us.apache.org/repos/asf/phoenix/blob/51214638/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 2e55594..21da43a 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
@@ -1411,5 +1411,87 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
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" +
+

phoenix git commit: PHOENIX-3042 Using functional index expression in where statement for join query fails

2016-07-18 Thread tdsilva 
Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-1.1 13ee5caa4 -> 1ef00e299


PHOENIX-3042 Using functional index expression in where statement for join 
query fails


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

Branch: refs/heads/4.x-HBase-1.1
Commit: 1ef00e299774cdff7c14bbc7943b03f344ae2e7f
Parents: 13ee5ca
Author: Thomas D'Silva 
Authored: Mon Jul 18 17:05:56 2016 -0700
Committer: Thomas D'Silva 
Committed: Mon Jul 18 17:05:56 2016 -0700

--
 .../end2end/index/IndexExpressionIT.java| 82 
 .../phoenix/compile/IndexStatementRewriter.java | 12 ++-
 .../apache/phoenix/compile/JoinCompiler.java| 17 ++--
 .../apache/phoenix/optimize/QueryOptimizer.java |  2 +-
 .../parse/IndexExpressionParseNodeRewriter.java |  8 +-
 .../phoenix/compile/QueryCompilerTest.java  | 41 +-
 6 files changed, 144 insertions(+), 18 deletions(-)
--


http://git-wip-us.apache.org/repos/asf/phoenix/blob/1ef00e29/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 2e55594..21da43a 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
@@ -1411,5 +1411,87 @@ public class IndexExpressionIT extends 
BaseHBaseManagedTimeIT {
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" +
+