You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by td...@apache.org on 2015/02/17 21:34:29 UTC
phoenix git commit: PHOENIX-1639 Enhance function/expression index
tests
Repository: phoenix
Updated Branches:
refs/heads/master b8c0559c0 -> 2e5a63089
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/master
Commit: 2e5a63089f98c21e8556fae0ec80988079e3ca55
Parents: b8c0559
Author: Thomas D'Silva <tw...@gmail.com>
Authored: Tue Feb 17 12:32:55 2015 -0800
Committer: Thomas D'Silva <tw...@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();
+ }
+ }
}