dbwong 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_r385941012
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
##########
@@ -539,151 +551,180 @@ public void testInListExpressionWithDesc() throws
Exception {
}
}
- @Test
- public void testInListExpressionWithDescOrderWithRightQueryPlan() throws
Exception {
- String fullTableName = generateUniqueName();
- String fullViewName = generateUniqueName();
- String tenantView = generateUniqueName();
-
+ private void buildSchema(String fullTableName, String fullViewName,
boolean isDecOrder) throws Exception {
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 (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");
+ if (isDecOrder) {
+ stmt.execute("CREATE VIEW " + fullViewName + "(\n" + " ID1
VARCHAR NOT NULL,\n" + " ID2 VARCHAR NOT NULL,\n" + " ID3 BIGINT, ID4 BIGINT
\n" +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (\n" + "
ID1, ID2 DESC\n" + ")) " +
+ "AS SELECT * FROM " + fullTableName + " WHERE
KEY_PREFIX = '0CY'");
+ try (Connection viewConn =
DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ try (Statement tenantStmt =
viewConn.createStatement()) {
+ tenantStmt.execute("CREATE VIEW IF NOT EXISTS " +
this.descViewName + " AS SELECT * FROM " + fullViewName);
+ }
+ }
+ } else {
+ stmt.execute("CREATE VIEW " + fullViewName + "(\n" + " ID1
VARCHAR NOT NULL,\n" + " ID2 VARCHAR NOT NULL,\n" + " ID3 BIGINT, ID4 BIGINT
\n" +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2))
" +
+ "AS SELECT * FROM " + fullTableName + " WHERE
KEY_PREFIX = '0CY'");
+ try (Connection viewConn =
DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ try (Statement tenantStmt =
viewConn.createStatement()) {
+ tenantStmt.execute("CREATE VIEW IF NOT EXISTS " +
this.ascViewName + " AS SELECT * FROM " + fullViewName);
+ }
+ }
+ }
+ }
}
+ }
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan1() throws
Exception {
+ String tenantView = generateUniqueName();
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();
+ try (Statement stmt = viewConn.createStatement()) {
+ stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS
SELECT * FROM " + descViewName);
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView
+ "(ID1, ID2) VALUES " +
+ "('foo', '000000000000300')");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView
+ "(ID1, ID2) VALUES " +
+ "('005xx000001Sv6o', '000000000000500')");
+ viewConn.commit();
+
+ try (PreparedStatement preparedStmt =
viewConn.prepareStatement(
+ "SELECT * FROM " + tenantView + " WHERE (ID1, ID2) " +
+ "IN (('005xx000001Sv6o',
'000000000000500'))")) {
+ QueryPlan queryPlan =
PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+
assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
- ResultSet rs = stmt.executeQuery("EXPLAIN DELETE FROM " +
tenantView + " WHERE (ID1, ID2, EVENT_DATE) " +
- "IN (('005xx000001Sv6o', '000000000000400',
1532458240000)," +
- "('005xx000001Sv6o', '000000000000300', 1532458230000))");
- assertTrue(getExplainPlanString(rs).contains("CLIENT PARALLEL
1-WAY RANGE SCAN"));
+ try (PreparedStatement preparedStmt =
viewConn.prepareStatement(
+ "SELECT * FROM " + tenantView + " WHERE (ID2, ID1) " +
+ "IN (('000000000000500',
'005xx000001Sv6o'))")) {
+ QueryPlan queryPlan =
PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+
assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
- rs = stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + "
WHERE (ID1, EVENT_DATE, ID2) IN " +
- "(('005xx000001Sv6o', 1532458240000, '000000000000400')," +
- "('005xx000001Sv6o', 1532458230000, '000000000000300'))");
- assertTrue(getExplainPlanString(rs).contains("CLIENT PARALLEL
1-WAY RANGE SCAN"));
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1)
IN " +
+ "(('bar', '005xx000001Sv6o'))");
- rs = stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + "
WHERE (ID2, EVENT_DATE, ID1) IN " +
- "(('000000000000400', 1532458240000, '005xx000001Sv6o')," +
- "('000000000000300', 1532458230000, '005xx000001Sv6o'))");
- assertTrue(getExplainPlanString(rs).contains("CLIENT PARALLEL
1-WAY RANGE SCAN"));
+ ResultSet rs = stmt.executeQuery("SELECT ID2 FROM " +
tenantView);
+ assertTrue(rs.next());
+ assertEquals("000000000000500", rs.getString(1));
+ }
+ }
+ }
- rs = stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + "
WHERE (EVENT_DATE, ID2, ID1) IN " +
- "((1532458240000, '000000000000400', '005xx000001Sv6o')," +
- "(1532458230000, '000000000000300', '005xx000001Sv6o'))");
- assertTrue(getExplainPlanString(rs).contains("CLIENT PARALLEL
1-WAY RANGE SCAN"));
+ @Ignore
Review comment:
Please comment why we are ignoring.
----------------------------------------------------------------
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
