[
https://issues.apache.org/jira/browse/PHOENIX-1639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14324564#comment-14324564
]
ASF GitHub Bot commented on PHOENIX-1639:
-----------------------------------------
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24837057
--- Diff:
phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java
---
@@ -809,58 +832,439 @@ public void helpTestDropIndexedColumn(boolean
mutable, boolean local) throws Exc
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)");
--- End diff --
Sounds like a bug. Please file a followup JIRA for this and I'll take a
quick look.
> Enhance function/expression index tests
> ---------------------------------------
>
> Key: PHOENIX-1639
> URL: https://issues.apache.org/jira/browse/PHOENIX-1639
> Project: Phoenix
> Issue Type: Test
> Reporter: Thomas D'Silva
> Assignee: Thomas D'Silva
>
> Add tests for views, negative scenarios, sort order of expression.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
