You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by twdsilva <gi...@git.apache.org> on 2015/02/14 00:09:34 UTC
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
GitHub user twdsilva opened a pull request:
https://github.com/apache/phoenix/pull/36
PHOENIX-1639 Enhance function/expression index tests
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/twdsilva/phoenix func_tests
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/phoenix/pull/36.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #36
----
commit 5aa273fee30aaef6ae768f7d8f119cfd2816cd2e
Author: Thomas <td...@salesforce.com>
Date: 2015-02-07T01:19:44Z
Enhance expression index tests
commit 3ace0d9b3ada0eafb937d3cfa7b7c38ec2634770
Author: Thomas D'Silva <tw...@gmail.com>
Date: 2015-02-10T22:25:02Z
minor
commit 22703191ce97f6d55775e0ac7560c2327ce6d54e
Author: Thomas D'Silva <tw...@gmail.com>
Date: 2015-02-10T22:49:33Z
Merge remote-tracking branch 'upstream/master' into func_tests
commit 0629f89fad10d9faba9a6d835b40e88abe7a129c
Author: Thomas D'Silva <tw...@gmail.com>
Date: 2015-02-10T22:54:47Z
merged
commit 35cb312a238573378f19fde66141357d6ad86881
Author: Thomas D'Silva <tw...@gmail.com>
Date: 2015-02-11T19:44:54Z
minor
commit cb7834bd38c472387280917380b10596c29db02b
Author: Thomas <td...@salesforce.com>
Date: 2015-02-12T02:17:28Z
more tests
commit 8de3b45aee52d682c04abbb1099c76af717434f3
Author: Thomas D'Silva <tw...@gmail.com>
Date: 2015-02-13T17:41:01Z
added view tests
commit 5e30c7dc253ef74e2e044cba70ed2656aafe6eed
Author: Thomas D'Silva <tw...@gmail.com>
Date: 2015-02-13T18:36:00Z
added negative tests
commit e835386439c37a045083ad1bbdeed00f444b2293
Author: Thomas <td...@salesforce.com>
Date: 2015-02-13T19:56:25Z
minor
commit 3dacd74c3bd67ddd572a818f5fb716f79053ec19
Author: Thomas <td...@salesforce.com>
Date: 2015-02-13T22:25:40Z
minor
commit 4363d5d4466bccfdf700c4c0bc0bcca9dfa0c800
Author: Thomas <td...@salesforce.com>
Date: 2015-02-13T22:31:22Z
Merge remote-tracking branch 'upstream/master' into func_tests
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24709209
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java ---
@@ -435,6 +435,9 @@ private boolean addIndexesFromPhysicalTable(MetaDataMutationResult result) throw
boolean containsAllReqdCols = true;
// Ensure that all indexed columns from index on physical table
// exist in the view too (since view columns may be removed)
+ // TODO
--- End diff --
When the col represents an expression, how should this be handled?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by JamesRTaylor <gi...@git.apache.org>.
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.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by JamesRTaylor <gi...@git.apache.org>.
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708175
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java ---
@@ -154,8 +154,9 @@ public SQLException newException(SQLExceptionInfo info) {
/**
* 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, "42898", "Stateless expression not allowed in an index"),
--- End diff --
Create a unique value for second argument. "42898" -> "42899"
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708956
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---
@@ -193,52 +197,79 @@ protected void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) thro
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) {
--- End diff --
This doesn't make sense for immutable indexes, as we are not supposed to mutate existing rows, so I modified this test to only test mutable indexes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708879
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java ---
@@ -346,16 +347,31 @@ public void testDropCoveredColumn() throws Exception {
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());
+
--- End diff --
The test was not verify the index rows , so I added this.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708828
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java ---
@@ -154,8 +154,9 @@ public SQLException newException(SQLExceptionInfo info) {
/**
* 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, "42898", "Stateless expression not allowed in an index"),
--- End diff --
Sorry, copy paste error, I have fixed this.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by JamesRTaylor <gi...@git.apache.org>.
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24836686
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---
@@ -123,7 +126,7 @@ protected void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) thro
+ (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)),"
--- End diff --
Just curious about the prefixing of the column name with "b". Was that required, or were you just testing that this wouldn't be a problem?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708974
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---
@@ -737,7 +708,7 @@ protected void helpTestIndexWithCaseSensitiveCols(boolean mutable, boolean local
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')\""));
--- End diff --
Added column family
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24804592
--- 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 --
For some reason the index on the data table (i2) is used only if there is an index on the view (vi1)
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24709007
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---
@@ -809,58 +833,432 @@ 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)");
-
- 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());
+ 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;
+
+ 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;
+
+ 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 {
--- End diff --
This test currently fails see comments in MetaDataClient.java
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24804626
--- 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)");
+
+ //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
--- End diff --
Same here, i1 is used only if there is an index on the view (vi2)
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by JamesRTaylor <gi...@git.apache.org>.
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708326
--- Diff: phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java ---
@@ -1493,5 +1493,77 @@ public void testMultiCFProjection() throws Exception {
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 (SUM())");
+ fail();
+ } catch (SQLException e) {
+ assertEquals(SQLExceptionCode.STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(), e.getErrorCode());
--- End diff --
This should throw the AGGREGATION not allowed exception. An example of stateless would be if a constant value was used. I guess this is an error condition.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by JamesRTaylor <gi...@git.apache.org>.
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24711078
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java ---
@@ -435,6 +435,9 @@ private boolean addIndexesFromPhysicalTable(MetaDataMutationResult result) throw
boolean containsAllReqdCols = true;
// Ensure that all indexed columns from index on physical table
// exist in the view too (since view columns may be removed)
+ // TODO
--- End diff --
You'll want to try to compile the expression and detect if it fails.
>
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708850
--- Diff: phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java ---
@@ -1493,5 +1493,77 @@ public void testMultiCFProjection() throws Exception {
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 (SUM())");
+ fail();
+ } catch (SQLException e) {
+ assertEquals(SQLExceptionCode.STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX.getErrorCode(), e.getErrorCode());
--- End diff --
I changed this to a constant expression.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva closed the pull request at:
https://github.com/apache/phoenix/pull/36
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24804498
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java ---
@@ -433,14 +433,34 @@ private boolean addIndexesFromPhysicalTable(MetaDataMutationResult result) throw
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);
--- End diff --
I used the index maintainer and physicalTable.getPKColumns() to get the columns required to build the index.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708917
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java ---
@@ -109,6 +109,11 @@ public void testNonSaltedUpdatableViewWithIndex() throws Exception {
}
@Test
+ public void testNonSaltedUpdatableViewWithLocalIndex() throws Exception {
--- End diff --
This test condition was missing so I added it
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by JamesRTaylor <gi...@git.apache.org>.
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24837706
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java ---
@@ -993,6 +1013,9 @@ public MutationState createIndex(CreateIndexStatement statement, byte[][] splits
if (expression.getDeterminism() != Determinism.ALWAYS) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException();
}
+ if (expression.isStateless()) {
+ throw new SQLExceptionInfo.Builder(SQLExceptionCode.STATELESS_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException();
+ }
// true for any constant (including a view constant), as we don't need these in the index
--- End diff --
Remove this if, as it'll never execute.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24708905
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java ---
@@ -130,7 +130,7 @@ protected void testUpdatableViewIndex(Integer saltBuckets, boolean localIndex) t
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"
--- End diff --
Added this so that I could add testNonSaltedUpdatableViewWithLocalIndex to ViewIT
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1639 Enhance function/expression ind...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/36#discussion_r24837161
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---
@@ -123,7 +126,7 @@ protected void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) thro
+ (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)),"
--- End diff --
This was not required, I was just testing that it worked with the column family name.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---