PHOENIX-6045 Delete that should qualify for index path does not use index when multiple indexes are available.
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java
index 0bb201c..f6f99d9 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java
@@ -18,6 +18,7 @@
package org.apache.phoenix.end2end;
import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.apache.phoenix.util.TestUtil.printResultSet;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotEquals;
import static org.junit.Assert.assertTrue;
@@ -881,6 +882,67 @@
}
}
}
+
+ @Test
+ public void testDeleteFilterWithMultipleIndexes() throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE IF NOT EXISTS " + tableName +
+ " (PKEY1 CHAR(15) NOT NULL, PKEY2 CHAR(15) NOT NULL, VAL1 CHAR(15), VAL2 CHAR(15)," +
+ " CONSTRAINT PK PRIMARY KEY (PKEY1, PKEY2)) ";
+ String indexName1 = generateUniqueName();
+ String indexDdl1 = "CREATE INDEX IF NOT EXISTS " + indexName1 + " ON " + tableName + " (VAL1)";
+ String indexName2 = generateUniqueName();
+ String indexDdl2 = "CREATE INDEX IF NOT EXISTS " + indexName2 + " ON " + tableName + " (VAL2)";
+ String delete = "DELETE FROM " + tableName + " WHERE VAL1 = '000000000000000' limit 1";
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try(Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ try (Statement statement = conn.createStatement()) {
+ statement.execute(ddl);
+ }
+ conn.createStatement().execute("upsert into " + tableName +" values ('PKEY1', 'PKEY2', '000000000000000', 'VAL2')");
+ conn.commit();
+ try (Statement statement = conn.createStatement()) {
+ statement.execute(indexDdl1);
+ }
+ }
+ try(Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ try (Statement statement = conn.createStatement()) {
+ ResultSet rs = statement.executeQuery("EXPLAIN " + delete);
+ String explainPlan = QueryUtil.getExplainPlan(rs);
+ // Verify index is used for the delete query
+ IndexToolIT.assertExplainPlan(false, explainPlan, tableName, indexName1);
+ }
+ // Created the second index
+ try (Statement statement = conn.createStatement()) {
+ statement.execute(indexDdl2);
+ }
+ try (Statement statement = conn.createStatement()) {
+ ResultSet rs = statement.executeQuery("EXPLAIN " + delete);
+ String explainPlan = QueryUtil.getExplainPlan(rs);
+ // Verify index is used for the delete query
+ IndexToolIT.assertExplainPlan(false, explainPlan, tableName, indexName1);
+ statement.executeUpdate(delete);
+ // Count the number of rows
+ String query = "SELECT COUNT(*) from " + tableName;
+ // There should be no rows on the data table
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ query = "SELECT COUNT(*) from " + indexName1;
+ // There should be no rows on any of the index tables
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ query = "SELECT COUNT(*) from " + indexName2;
+ // There should be no rows on any of the index tables
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
}
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java
index a03b786..141dfc0 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java
@@ -89,12 +89,14 @@
import org.apache.phoenix.transaction.PhoenixTransactionProvider.Feature;
import org.apache.phoenix.util.ByteUtil;
import org.apache.phoenix.util.IndexUtil;
+import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.ScanUtil;
import com.google.common.base.Preconditions;
import com.google.common.collect.Lists;
import edu.umd.cs.findbugs.annotations.NonNull;
+import org.apache.phoenix.util.SchemaUtil;
public class DeleteCompiler {
private static ParseNodeFactory FACTORY = new ParseNodeFactory();
@@ -196,6 +198,12 @@
// The data table is always the last one in the list if it's
// not chosen as the best of the possible plans.
dataTable = otherTableRefs.get(otherTableRefs.size()-1).getTable();
+ if (!isMaintainedOnClient(table)) {
+ // dataTable is a projected table and may not include all the indexed columns and so we need to get
+ // the actual data table
+ dataTable = PhoenixRuntime.getTable(connection,
+ SchemaUtil.getTableName(dataTable.getSchemaName().getString(), dataTable.getTableName().getString()));
+ }
scannedIndexMaintainer = IndexMaintainer.create(dataTable, table, connection);
}
maintainers = new IndexMaintainer[otherTableRefs.size()];
@@ -487,19 +495,22 @@
projectedColumns.add(column);
aliasedNodes.add(FACTORY.aliasedNode(null, FACTORY.column(null, '"' + column.getName().getString() + '"', null)));
}
- // Project all non PK indexed columns so that we can do the proper index maintenance
+ // Project all non PK indexed columns so that we can do the proper index maintenance on the indexes for which
+ // mutations are generated on the client side. Indexed columns are needed to identify index rows to be deleted
for (PTable index : table.getIndexes()) {
- IndexMaintainer maintainer = index.getIndexMaintainer(table, connection);
- // Go through maintainer as it handles functional indexes correctly
- for (Pair<String,String> columnInfo : maintainer.getIndexedColumnInfo()) {
- String familyName = columnInfo.getFirst();
- if (familyName != null) {
- String columnName = columnInfo.getSecond();
- boolean hasNoColumnFamilies = table.getColumnFamilies().isEmpty();
- PColumn column = hasNoColumnFamilies ? table.getColumnForColumnName(columnName) : table.getColumnFamily(familyName).getPColumnForColumnName(columnName);
- if(!projectedColumns.contains(column)) {
- projectedColumns.add(column);
- aliasedNodes.add(FACTORY.aliasedNode(null, FACTORY.column(hasNoColumnFamilies ? null : TableName.create(null, familyName), '"' + columnName + '"', null)));
+ if (isMaintainedOnClient(index)) {
+ IndexMaintainer maintainer = index.getIndexMaintainer(table, connection);
+ // Go through maintainer as it handles functional indexes correctly
+ for (Pair<String, String> columnInfo : maintainer.getIndexedColumnInfo()) {
+ String familyName = columnInfo.getFirst();
+ if (familyName != null) {
+ String columnName = columnInfo.getSecond();
+ boolean hasNoColumnFamilies = table.getColumnFamilies().isEmpty();
+ PColumn column = hasNoColumnFamilies ? table.getColumnForColumnName(columnName) : table.getColumnFamily(familyName).getPColumnForColumnName(columnName);
+ if (!projectedColumns.contains(column)) {
+ projectedColumns.add(column);
+ aliasedNodes.add(FACTORY.aliasedNode(null, FACTORY.column(hasNoColumnFamilies ? null : TableName.create(null, familyName), '"' + columnName + '"', null)));
+ }
}
}
}
@@ -970,4 +981,4 @@
table.getTransactionProvider().getTransactionProvider().isUnsupported(Feature.MAINTAIN_LOCAL_INDEX_ON_SERVER) ) );
}
-}
\ No newline at end of file
+}
