ChinmaySKulkarni commented on a change in pull request #708: PHOENIX-5698
Phoenix Query with RVC IN list expression generates wron…
URL: https://github.com/apache/phoenix/pull/708#discussion_r385369711
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
##########
@@ -537,4 +539,267 @@ public void testInListExpressionWithDesc() throws
Exception {
}
}
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan() throws
Exception {
+ String fullTableName = generateUniqueName();
+ String fullViewName = generateUniqueName();
+ String tenantView = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ Statement stmt = conn.createStatement();
+ stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID
CHAR(15) NOT NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" +
+ " CONSTRAINT PK PRIMARY KEY (\n" + " TENANT_ID," + "
KEY_PREFIX" + ")) MULTI_TENANT=TRUE");
+ stmt.execute("CREATE VIEW " + fullViewName + "(\n" + " ID1 VARCHAR
NOT NULL,\n" + " ID2 VARCHAR NOT NULL,\n" + " EVENT_DATE DATE NOT NULL,\n" +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (\n" + " ID1, ID2
DESC, EVENT_DATE DESC\n" + ")) " +
+ "AS SELECT * FROM " + fullTableName + " WHERE KEY_PREFIX =
'0CY'");
+ }
+
+
+ try (Connection viewConn =
DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ Statement stmt = viewConn.createStatement();
+ stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS
SELECT * FROM " + fullViewName);
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView +
"(ID1, ID2, EVENT_DATE) VALUES " +
+ "('005xx000001Sv6o', '000000000000300', 1532458230000)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView +
"(ID1, ID2, EVENT_DATE) VALUES " +
+ "('005xx000001Sv6o', '000000000000400', 1532458240000)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView +
"(ID1, ID2, EVENT_DATE) VALUES " +
+ "('005xx000001Sv6o', '000000000000500', 1532458250000)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("EXPLAIN DELETE FROM " +
tenantView + " WHERE (ID1, ID2, EVENT_DATE) " +
Review comment:
@yanxinyi So here the where condition is basically on the full row key:
<TENANT_ID, KEY_PREFIX, ID1, ID2, EVENT_DATE> such that:
```sql
TENANT_ID=<fixed constant> AND
KEY_PREFIX= '0CY' AND
((ID1='005xx000001Sv6o' AND ID2='000000000000400' AND EVENT_DATE=
1532458240000) OR
(ID1='005xx000001Sv6o' AND ID2='000000000000300' AND EVENT_DATE=
1532458230000))
```
In this case, instead of a range scan, shouldn't the client be able to issue
2 deletes since this is basically 2 point lookups?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
