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 <[email protected]>
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 <[email protected]>
---
.../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 {
