Repository: phoenix Updated Branches: refs/heads/4.x-HBase-1.2 a642a5577 -> 48d1df3dd
PHOENIX-4586 UPSERT SELECT doesn't take in account comparison operators for subqueries. Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/48d1df3d Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/48d1df3d Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/48d1df3d Branch: refs/heads/4.x-HBase-1.2 Commit: 48d1df3dd6b7316ed56b46d5a0471525dc8709e5 Parents: a642a55 Author: maryannxue <maryann....@gmail.com> Authored: Thu Feb 8 12:50:07 2018 -0800 Committer: maryannxue <maryann....@gmail.com> Committed: Thu Feb 8 12:50:07 2018 -0800 ---------------------------------------------------------------------- .../apache/phoenix/end2end/join/SubqueryIT.java | 41 ++++++++++++++++++++ .../apache/phoenix/compile/UpsertCompiler.java | 3 +- .../org/apache/phoenix/parse/ParseNode.java | 26 ++++++++++++- 3 files changed, 68 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/48d1df3d/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryIT.java index 604db63..1da98d2 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryIT.java @@ -744,6 +744,47 @@ public class SubqueryIT extends BaseJoinIT { } @Test + public void testNoncorrelatedSubqueryWithUpsert() throws Exception { + String tempTable = generateUniqueName(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + conn.setAutoCommit(true); + try { + conn.createStatement().execute("CREATE TABLE " + tempTable + + " (item_id varchar not null primary key, " + + " name varchar)"); + conn.createStatement().execute("UPSERT INTO " + tempTable + " VALUES('1', 'a')"); + conn.createStatement().execute("UPSERT INTO " + tempTable + " VALUES('2', 'b')"); + conn.createStatement().execute("UPSERT INTO " + tempTable + "(item_id, name)" + + " SELECT item_id, 'x' FROM " + tempTable + + " WHERE item_id < 'z' AND name > ALL (SELECT name FROM " + tempTable + ")"); + + String query = "SELECT name FROM " + tempTable + " ORDER BY item_id"; + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertTrue(rs.next()); + assertEquals("b", rs.getString(1)); + assertFalse(rs.next()); + + conn.createStatement().execute("UPSERT INTO " + tempTable + "(item_id, name)" + + " SELECT item_id, 'x' FROM " + tempTable + + " WHERE item_id < 'z' AND name > ANY (SELECT name FROM " + tempTable + ")"); + + statement = conn.prepareStatement(query); + rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertTrue(rs.next()); + assertEquals("x", rs.getString(1)); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + + @Test public void testSubqueryWithDelete() throws Exception { String tempTable = generateUniqueName(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); http://git-wip-us.apache.org/repos/asf/phoenix/blob/48d1df3d/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java index 08133a4..ba202c8 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java @@ -533,9 +533,10 @@ public class UpsertCompiler { // region space managed by region servers. So we bail out on executing on server side. // Disable running upsert select on server side if a table has global mutable secondary indexes on it boolean hasGlobalMutableIndexes = SchemaUtil.hasGlobalIndex(table) && !table.isImmutableRows(); + boolean hasWhereSubquery = select.getWhere() != null && select.getWhere().hasSubquery(); runOnServer = (sameTable || (serverUpsertSelectEnabled && !hasGlobalMutableIndexes)) && isAutoCommit && !table.isTransactional() && !(table.isImmutableRows() && !table.getIndexes().isEmpty()) - && !select.isJoin() && table.getRowTimestampColPos() == -1; + && !select.isJoin() && !hasWhereSubquery && table.getRowTimestampColPos() == -1; } // If we may be able to run on the server, add a hint that favors using the data table // if all else is equal. http://git-wip-us.apache.org/repos/asf/phoenix/blob/48d1df3d/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNode.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNode.java index b32674e..362e657 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNode.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNode.java @@ -56,6 +56,30 @@ public abstract class ParseNode { toSQL(null, buf); return buf.toString(); } - + + /** + * Returns whether this ParseNode is a SubqueryParseNode + * or contains any SubqueryParseNode descendant. + */ + public boolean hasSubquery() { + SubqueryFinder finder = new SubqueryFinder(); + try { + this.accept(finder); + } catch (SQLException e) { + // Not possible. + } + return finder.hasSubquery; + } + public abstract void toSQL(ColumnResolver resolver, StringBuilder buf); + + private static class SubqueryFinder extends StatelessTraverseAllParseNodeVisitor { + private boolean hasSubquery = false; + + @Override + public Void visit(SubqueryParseNode node) throws SQLException { + hasSubquery = true; + return null; + } + } }
