This is an automated email from the ASF dual-hosted git repository. tkhurana pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push: new ceb10a5315 PHOENIX-7229 Addendum to handle cases where a child view extends the (#1885) ceb10a5315 is described below commit ceb10a53151a55720e8fe9571481be3c9015bd18 Author: tkhurana <khurana.ta...@gmail.com> AuthorDate: Wed May 1 10:51:49 2024 -0700 PHOENIX-7229 Addendum to handle cases where a child view extends the (#1885) * PHOENIX-7229 Addendum to handle cases where a child view extends the parent's PK but we do a point lookup using the parent's PK. * Handle Aggregate queries --------- Co-authored-by: Tanuj Khurana <tkhur...@apache.org> --- .../phoenix/iterate/BaseResultIterators.java | 11 +++- .../phoenix/end2end/TenantSpecificTablesDMLIT.java | 58 ++++++++++++++++++++++ .../apache/phoenix/compile/WhereOptimizerTest.java | 44 ++++++++++++++-- 3 files changed, 107 insertions(+), 6 deletions(-) diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java b/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java index b83c587dfa..e4658dae7e 100644 --- a/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java +++ b/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java @@ -943,9 +943,16 @@ public abstract class BaseResultIterators extends ExplainTable implements Result List<List<Scan>> parallelScans = Lists.newArrayListWithExpectedSize(1); List<Scan> scans = Lists.newArrayListWithExpectedSize(1); Scan scanFromContext = context.getScan(); - if (scanRanges.getPointLookupCount() == 1) { + Integer limit = plan.getLimit(); + boolean isAggregate = plan.getStatement().isAggregate(); + if (scanRanges.getPointLookupCount() == 1 && limit == null && !isAggregate) { // leverage bloom filter for single key point lookup by turning scan to - // Get Scan#isGetScan() + // Get Scan#isGetScan(). There should also be no limit on the point lookup query. + // The limit and the aggregate check is needed to handle cases where a child view + // extends the parent's PK and you insert data through the child but do a point + // lookup using the parent's PK. Since the parent's PK is only a prefix of the + // actual PK we can't do a Get but need to do a regular scan with the stop key + // set to the next key after the start key. try { scanFromContext = new Scan(context.getScan()); } catch (IOException e) { diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDMLIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDMLIT.java index 55fed3ab5b..3f406f72e3 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDMLIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDMLIT.java @@ -27,13 +27,17 @@ import static org.junit.Assert.fail; import java.sql.Connection; import java.sql.DriverManager; +import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.List; import java.util.Properties; +import org.apache.phoenix.jdbc.PhoenixResultSet; import org.apache.phoenix.query.KeyRange; import org.apache.phoenix.schema.TableNotFoundException; +import org.apache.phoenix.util.PhoenixRuntime; import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.QueryUtil; import org.junit.Test; import org.junit.experimental.categories.Category; @@ -47,6 +51,60 @@ public class TenantSpecificTablesDMLIT extends BaseTenantSpecificTablesIT { ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + TENANT_TABLE_NAME + " LIMIT 100"); while(rs.next()) {} } + + @Test + public void testPointLookupOnBaseTable() throws Exception { + final String tableName = "T_" + generateUniqueName(); + final String viewName = "V_" + generateUniqueName(); + final String tenantId = "tenant1"; + final String kp = "abc"; + String ddl = String.format("CREATE TABLE %s (ORG_ID CHAR(15) NOT NULL, KP CHAR(3) NOT NULL, V1 INTEGER, V2 VARCHAR," + + "CONSTRAINT PK PRIMARY KEY(ORG_ID, KP)) MULTI_TENANT=true", tableName); + int nRows = 16; + try (Connection conn = DriverManager.getConnection(getUrl())) { + conn.createStatement().execute(ddl); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); + try (Connection tconn = DriverManager.getConnection(getUrl(), props)) { + ddl = String.format("CREATE VIEW %s (PK1 VARCHAR PRIMARY KEY, V3 VARCHAR)" + + " AS SELECT * FROM %s WHERE KP='%s'", viewName, tableName, kp); + tconn.createStatement().execute(ddl); + tconn.commit(); + // upsert through the tenant + try(PreparedStatement ps = tconn.prepareStatement( + String.format("UPSERT INTO %s(V1,V2,PK1,V3) VALUES (?, ?, ?, ?)", viewName))) { + for (int i = 0; i < nRows; i++) { + ps.setInt(1, i); // V1 + ps.setString(2, "v2"); // V2 + ps.setString(3, "pk_" + i); // PK1 + ps.setString(4, "v3"); // V3 + ps.executeUpdate(); + } + tconn.commit(); + } + } + // Do a point lookup on the base table + String dql = String.format("SELECT * FROM %s where org_id='%s' AND kp='%s' LIMIT 1", + tableName, tenantId, kp); + try (ResultSet rs = conn.createStatement().executeQuery(dql)) { + PhoenixResultSet prs = rs.unwrap(PhoenixResultSet.class); + String explainPlan = QueryUtil.getExplainPlan(prs.getUnderlyingIterator()); + assertTrue(explainPlan.contains("POINT LOOKUP ON 1 KEY")); + assertTrue(rs.next()); + assertEquals(tenantId, rs.getString(1)); + assertEquals(kp, rs.getString(2)); + } + dql = String.format("SELECT count(*) FROM %s where org_id='%s' AND kp='%s'", + tableName, tenantId, kp); + try (ResultSet rs = conn.createStatement().executeQuery(dql)) { + PhoenixResultSet prs = rs.unwrap(PhoenixResultSet.class); + String explainPlan = QueryUtil.getExplainPlan(prs.getUnderlyingIterator()); + assertTrue(explainPlan.contains("POINT LOOKUP ON 1 KEY")); + assertTrue(rs.next()); + assertEquals(nRows, rs.getInt(1)); + } + } + } @Test public void testBasicUpsertSelect() throws Exception { diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java index c1584c19c5..2f58508443 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java @@ -2530,6 +2530,34 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { } } + @Test + public void testScanRangeForPointLookupWithLimit() throws SQLException { + String tenantId = "000000000000001"; + String entityId = "002333333333333"; + String query = String.format("select * from atable where organization_id='%s' " + + "and entity_id='%s' LIMIT 1", tenantId, entityId); + try (Connection conn = DriverManager.getConnection(getUrl())) { + QueryPlan optimizedPlan = TestUtil.getOptimizeQueryPlan(conn, query); + byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId)); + byte[] stopRow = ByteUtil.nextKey(startRow); + validateScanRangesForPointLookup(optimizedPlan, startRow, stopRow); + } + } + + @Test + public void testScanRangeForPointLookupAggregate() throws SQLException { + String tenantId = "000000000000001"; + String entityId = "002333333333333"; + String query = String.format("select count(*) from atable where organization_id='%s' " + + "and entity_id='%s'", tenantId, entityId); + try (Connection conn = DriverManager.getConnection(getUrl())) { + QueryPlan optimizedPlan = TestUtil.getOptimizeQueryPlan(conn, query); + byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId)); + byte[] stopRow = ByteUtil.nextKey(startRow); + validateScanRangesForPointLookup(optimizedPlan, startRow, stopRow); + } + } + private static void validateScanRangesForPointLookup(QueryPlan optimizedPlan, byte[] startRow, byte[] stopRow) { StatementContext context = optimizedPlan.getContext(); ScanRanges scanRanges = context.getScanRanges(); @@ -2546,10 +2574,18 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { assertEquals(1, scans.size()); assertEquals(1, scans.get(0).size()); Scan scanFromIterator = scans.get(0).get(0); - // scan from iterator has same start and stop row [start, start] i.e a Get - assertTrue(scanFromIterator.isGetScan()); - assertTrue(scanFromIterator.includeStartRow()); - assertTrue(scanFromIterator.includeStopRow()); + if (optimizedPlan.getLimit() == null && !optimizedPlan.getStatement().isAggregate()) { + // scan from iterator has same start and stop row [start, start] i.e a Get + assertTrue(scanFromIterator.isGetScan()); + assertTrue(scanFromIterator.includeStartRow()); + assertTrue(scanFromIterator.includeStopRow()); + } else { + // in case of limit scan range is same as the one in StatementContext + assertArrayEquals(startRow, scanFromIterator.getStartRow()); + assertTrue(scanFromIterator.includeStartRow()); + assertArrayEquals(stopRow, scanFromIterator.getStopRow()); + assertFalse(scanFromIterator.includeStopRow()); + } } private static StatementContext compileStatementTenantSpecific(String tenantId, String query, List<Object> binds) throws Exception {