From commits-return-21236-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Sun May 13 11:49:52 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id BBBF918077A for ; Sun, 13 May 2018 11:49:51 +0200 (CEST) Received: (qmail 45715 invoked by uid 500); 13 May 2018 09:49:50 -0000 Mailing-List: contact commits-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list commits@phoenix.apache.org Received: (qmail 45612 invoked by uid 99); 13 May 2018 09:49:50 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 13 May 2018 09:49:50 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 58F11F4E9D; Sun, 13 May 2018 09:49:50 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: pboado@apache.org To: commits@phoenix.apache.org Date: Sun, 13 May 2018 09:49:52 -0000 Message-Id: <1cf5f6b76b6341a29c1e13c333e58c02@git.apache.org> In-Reply-To: <387a7b620c2340dab660958fcb16b88e@git.apache.org> References: <387a7b620c2340dab660958fcb16b88e@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [3/5] phoenix git commit: PHOENIX-4734 SQL Query with an RVC expression lexographically higher than all values in an OR clause causes query to blow up PHOENIX-4734 SQL Query with an RVC expression lexographically higher than all values in an OR clause causes query to blow up Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/7856a001 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/7856a001 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/7856a001 Branch: refs/heads/4.x-cdh5.12 Commit: 7856a0017f5377ab02e882e964b32e93877d5456 Parents: 670e14f Author: Thomas D'Silva Authored: Fri May 11 00:30:36 2018 +0100 Committer: Pedro Boado Committed: Sun May 13 10:45:54 2018 +0100 ---------------------------------------------------------------------- .../phoenix/end2end/RowValueConstructorIT.java | 120 +++++++++++++++++++ .../org/apache/phoenix/compile/ScanRanges.java | 5 + 2 files changed, 125 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/7856a001/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java index df7603a..fb04261 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java @@ -48,6 +48,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; +import java.util.List; import java.util.Properties; import org.apache.phoenix.util.DateUtil; @@ -57,6 +58,9 @@ import org.apache.phoenix.util.QueryUtil; import org.apache.phoenix.util.SchemaUtil; import org.junit.Test; +import com.google.common.base.Joiner; +import com.google.common.collect.Lists; + public class RowValueConstructorIT extends ParallelStatsDisabledIT { @@ -1646,4 +1650,120 @@ public class RowValueConstructorIT extends ParallelStatsDisabledIT { assertEquals("value", rs.getString(3)); assertFalse(rs.next()); } + + @Test + /** + * Verifies that a query with a RVC expression lexographically higher than all values in an OR + * clause causes query works see PHOENIX-4734 + */ + public void testRVCWithAndClause() throws Exception { + final int numItemsInClause = 5; + Properties tenantProps = new Properties(); + tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, "tenant1"); + String fullTableName = SchemaUtil.getTableName("S", "T_" + generateUniqueName()); + String fullViewName = SchemaUtil.getTableName("S", "V_" + generateUniqueName()); + try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) { + tenantConn.setAutoCommit(false); + createBaseTableAndTenantView(tenantConn, fullTableName, fullViewName); + loadDataIntoTenantView(tenantConn, fullViewName); + List objectIdsList = + selectObjectIdsForInClause(tenantConn, fullViewName, numItemsInClause); + StringBuilder querySb = generateQueryToTest(numItemsInClause, fullViewName); + PreparedStatement ps = tenantConn.prepareStatement(querySb.toString()); + int numbBindVarsSet = 0; + String objectId = null; + for (int i = 0; i < numItemsInClause; i++) { + objectId = objectIdsList.get(i); + ps.setString((i + 1), objectId); + numbBindVarsSet++; + } + assertEquals(numItemsInClause, numbBindVarsSet); + assertEquals("v1000", objectId); + ps.setString(numItemsInClause + 1, "z00"); + ps.setString(numItemsInClause + 2, "v1000"); // This value must match or be + // lexographically higher than the highest + // value in the IN clause + // Query should execute and return 0 results + ResultSet rs = ps.executeQuery(); + assertFalse( + "Query should return no results as IN clause and RVC clause are disjoint sets", + rs.next()); + } + } + + private StringBuilder generateQueryToTest(int numItemsInClause, String fullViewName) { + StringBuilder querySb = + new StringBuilder("SELECT OBJECT_ID,OBJECT_DATA2,OBJECT_DATA FROM " + fullViewName); + querySb.append(" WHERE (("); + List orClauses = Lists.newArrayList(); + for (int i = 1; i < (numItemsInClause + 1); i++) { + orClauses.add("OBJECT_ID = ?"); + } + querySb.append(Joiner.on(" OR ").join(orClauses)); + querySb.append(") AND (KEY_PREFIX,OBJECT_ID) > (?,?)) ORDER BY OBJECT_ID LIMIT 125"); + System.out.println(querySb); + return querySb; + } + + private List selectObjectIdsForInClause(Connection tenantConn, String fullViewName, + int numItemsInClause) throws SQLException { + String sqlForObjIds = + "SELECT OBJECT_ID FROM " + fullViewName + " ORDER BY OBJECT_ID LIMIT " + + numItemsInClause; + PreparedStatement ps = tenantConn.prepareStatement(sqlForObjIds); + ResultSet rs = ps.executeQuery(); + List objectIdsList = Lists.newArrayList(); + System.out.println("ObjectIds: "); + while (rs.next()) { + System.out.println("Object Id: " + rs.getString("OBJECT_ID")); + objectIdsList.add(rs.getString("OBJECT_ID")); + } + assertEquals(numItemsInClause, objectIdsList.size()); + return objectIdsList; + } + + private void loadDataIntoTenantView(Connection tenantConn, String fullViewName) + throws SQLException { + for (int i = 0; i < 2000; i++) { + String objectId = "v" + i; + String upsert = + "UPSERT INTO " + fullViewName + + " (OBJECT_ID, OBJECT_DATA, OBJECT_DATA2) VALUES ('" + objectId + + "', 'data','data2')"; + PreparedStatement ps = tenantConn.prepareStatement(upsert); + ps.executeUpdate(); + } + tenantConn.commit(); + + // Validate Data was loaded correctly + PreparedStatement selectStatement = + tenantConn.prepareStatement( + "SELECT OBJECT_ID FROM " + fullViewName + " ORDER BY OBJECT_ID"); + ResultSet rs = selectStatement.executeQuery(); + int count = 0; + while (rs.next()) { + count++; + } + assertEquals(2000, count); + } + + private void createBaseTableAndTenantView(Connection tenantConn, String fulTableName, + String fullViewName) throws SQLException { + String ddl = + "CREATE TABLE IF NOT EXISTS " + fulTableName + " (TENANT_ID CHAR(15) NOT NULL," + + " KEY_PREFIX CHAR(3) NOT NULL," + " CREATED_DATE DATE," + + " CREATED_BY CHAR(15)," + " SYSTEM_MODSTAMP DATE" + + " CONSTRAINT PK PRIMARY KEY (TENANT_ID,KEY_PREFIX)" + + ") VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1"; + createTestTable(getUrl(), ddl); + + String tenantViewDdl = + "CREATE VIEW IF NOT EXISTS " + fullViewName + " (OBJECT_ID VARCHAR(18) NOT NULL, " + + "OBJECT_DATA VARCHAR(131072), " + "OBJECT_DATA2 VARCHAR(131072), " + + "CONSTRAINT PK PRIMARY KEY (OBJECT_ID)) " + "AS SELECT * FROM " + + fulTableName + " WHERE KEY_PREFIX = 'z00'"; + // Get tenant specific connection + PreparedStatement stmt2 = tenantConn.prepareStatement(tenantViewDdl); + stmt2.execute(); + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/7856a001/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java index 8c71248..019f15d 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java @@ -105,6 +105,11 @@ public class ScanRanges { keyRanges.add(KeyRange.getKeyRange(key)); } } + // while doing a point look up if after intersecting with the MinMaxrange there are + // no more keyranges left then just return + if (keyRanges.isEmpty()) { + return NOTHING; + } ranges = Collections.singletonList(keyRanges); useSkipScan = keyRanges.size() > 1; // Treat as binary if descending because we've got a separator byte at the end