virajjasani commented on code in PR #1851:
URL: https://github.com/apache/phoenix/pull/1851#discussion_r1516856703
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDDLIT.java:
##########
@@ -616,6 +621,52 @@ public void testTableMetadataScan() throws Exception {
conn.close();
}
}
+
+ @Test
+ public void testIndexHintWithTenantView() throws Exception {
+ String schemaName = generateUniqueName();
+ String dataTableName = generateUniqueName();
+ String fullDataTableName = SchemaUtil.getTableName(schemaName,
dataTableName);
+ String viewName = generateUniqueName();
+ String fullViewName = SchemaUtil.getTableName(schemaName, viewName);
+ String viewIndexName = generateUniqueName();
+ try(Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ String createDataTable = "create table " + fullDataTableName + "
(orgid varchar(10) not null, "
+ + "id1 varchar(10) not null, id2 varchar(10) not null, id3
integer not null, "
+ + "val1 varchar(10), val2 varchar(10) " +
+ "CONSTRAINT PK PRIMARY KEY (orgid, id1, id2, id3))
MULTI_TENANT=true";
+ stmt.execute(createDataTable);
+ stmt.execute("create view " + fullViewName + " as select * from "
+ fullDataTableName);
+ stmt.execute("create index " + viewIndexName + " on " +
fullViewName + "(id3, id2, id1) include (val1, val2)");
+ }
+ try(Connection conn =
DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
+ Statement stmt = conn.createStatement()) {
+ String grandChildViewName = generateUniqueName();
+ String fullGrandChildViewName =
SchemaUtil.getTableName(schemaName, grandChildViewName);
+ stmt.execute("create view " + fullGrandChildViewName + " as select
* from " + fullViewName);
+ PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class);
+ pconn.getTableNoCache(pconn.getTenantId(), fullGrandChildViewName);
+ stmt.execute("upsert into " + fullGrandChildViewName + " values
('a1', 'a2', 3, 'a4', 'a5')");
+ conn.commit();
+ stmt.execute("upsert into " + fullGrandChildViewName + " values
('b1', 'b2', 3, 'b4', 'b5')");
+ conn.commit();
+ String physicalViewIndexTableName =
MetaDataUtil.getViewIndexPhysicalName(fullDataTableName);
+ TableName viewIndexHBaseTable =
TableName.valueOf(physicalViewIndexTableName);
+ TestUtil.assertRawRowCount(conn, viewIndexHBaseTable, 2);
+ String sql = "SELECT /*+ INDEX(" + fullGrandChildViewName + " " +
viewIndexName + ")*/ "
+ + "val2, id2, val1, id3, id1 FROM " +
fullGrandChildViewName
+ + " WHERE id2 = 'a2' AND (id1 = 'a1' OR id1 = 'b1') AND
id3 = 3";
+ ResultSet rs = stmt.executeQuery("EXPLAIN " + sql);
+ String actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ String expectedQueryPlan = "CLIENT PARALLEL 1-WAY POINT LOOKUP ON
2 KEYS OVER "
+ + physicalViewIndexTableName;
Review Comment:
Could you please also use the same query without hint and assert that the
Explain plan now uses 1-WAY POINT LOOKUP on the base table?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
