You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by GitBox <gi...@apache.org> on 2020/03/23 06:31:51 UTC
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
URL: https://github.com/apache/phoenix/pull/736#discussion_r396234905
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
##########
@@ -586,4 +601,930 @@ public void testInListExpressionWithDesc() throws Exception {
}
}
+ private void buildSchema(String fullTableName, String fullViewName, boolean isDecOrder) throws Exception {
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15) NOT NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL, ID5 BIGINT \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);
+ 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));
+ }
+
+ 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));
+ }
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('bar', '005xx000001Sv6o'))");
+
+ ResultSet rs = stmt.executeQuery("SELECT ID2 FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals("000000000000500", rs.getString(1));
+ }
+ }
+ }
+
+ // InListExpression generates a range scan instead of a point lookup if we have PK in DESC oder.
+ // Please remove ignore tag after PHOENIX-5752 has been fixed.
+ @Ignore
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan2() throws Exception {
+ String tenantView = generateUniqueName();
+
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ 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 " +
+ "('bar', '000000000000400')");
+ 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')," +
+ "('bar', '000000000000400')," +
+ "('foo', '000000000000300'))")) {
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
+
+ try (PreparedStatement preparedStmt = viewConn.prepareStatement(
+ "SELECT * FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('bar', '005xx000001Sv6o')," +
+ "('foo', '005xx000001Sv6o'))")) {
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('bar', '005xx000001Sv6o')," +
+ "('foo', '005xx000001Sv6o'))");
+
+ ResultSet rs = stmt.executeQuery("SELECT ID2 FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals("000000000000500", rs.getString(1));
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getString(1));
+ stmt.execute("DELETE * FROM " + tenantView);
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan() throws Exception {
+ String tenantView = generateUniqueName();
+
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ try (Statement stmt = viewConn.createStatement()) {
+ stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * FROM " + ascViewName);
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2) VALUES " +
+ "('005xx000001Sv6o', '000000000000300')");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2) VALUES " +
+ "('005xx000001Sv6o', '000000000000400')");
+ viewConn.commit();
+
+ try (PreparedStatement preparedStmt = viewConn.prepareStatement(
+ "SELECT * FROM " + tenantView + " WHERE (ID1, ID2) IN " +
+ "(('005xx000001Sv6o', '000000000000500')," +
+ "('005xx000001Sv6o', '000000000000400')," +
+ "('005xx000001Sv6o', '000000000000300'))")) {
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
+
+ try (PreparedStatement preparedStmt = viewConn.prepareStatement(
+ "SELECT * FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', '005xx000001Sv6o')," +
+ "('000000000000300', '005xx000001Sv6o'))")) {
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', '005xx000001Sv6o')," +
+ "('000000000000300', '005xx000001Sv6o'))");
+ assertTrue(rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000300', '005xx000001Sv6o'))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionGeneratesRightScanForAsc() throws Exception {
+ testFullPkListPlan(this.ascViewName);
+ testPartialPkListPlan(this.ascViewName);
+ testPartialPkPlusNonPkListPlan(this.ascViewName);
+ testNonPkListPlan(this.ascViewName);
+ }
+
+ // InListExpression generates a range scan instead of a point lookup if we have PK in DESC oder.
+ // Please remove ignore tag after PHOENIX-5752 has been fixed.
+ @Ignore
+ @Test
+ public void testInListExpressionGeneratesRightScanForDesc() throws Exception {
+ testFullPkListPlan(this.descViewName);
+ testPartialPkListPlan(this.descViewName);
+ testPartialPkPlusNonPkListPlan(this.descViewName);
+ testNonPkListPlan(this.descViewName);
+ }
+
+ private void testFullPkPlusNonPkInListPlan(String tenantView) throws Exception {
+ Long numberOfRowsToScan = new Long(2);
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) {
+ PreparedStatement preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID1, ID2, ID3) IN " +
+ "(('005xx000001Sv6o', '000000000000500', 1)," +
+ "('005xx000001Sv6o', '000000000000400', 2))");
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(numberOfRowsToScan, queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+
+ preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID2, ID1, ID3) IN " +
+ "(('000000000000500','005xx000001Sv6o', 1)," +
+ "('000000000000400','005xx000001Sv6o', 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(numberOfRowsToScan, queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+
+ viewConn.prepareStatement("DELETE FROM " + tenantView + " WHERE (ID1, ID2) IN " +
+ "(('005xx000001Sv6o', '000000000000500', 1)," +
+ "('005xx000001Sv6o', '000000000000400', 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(numberOfRowsToScan, queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
+ }
+
+ private void testFullPkListPlan(String tenantView) throws Exception {
+ Long numberOfRowsToScan = new Long(2);
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) {
+ PreparedStatement preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID1, ID2) IN " +
+ "(('005xx000001Sv6o', '000000000000500')," +
+ "('005xx000001Sv6o', '000000000000400'))");
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(numberOfRowsToScan, queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+
+ viewConn.prepareStatement("DELETE FROM " + tenantView + " WHERE (ID1, ID2) IN " +
+ "(('005xx000001Sv6o', '000000000000500')," +
+ "('005xx000001Sv6o', '000000000000400'))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(numberOfRowsToScan, queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
+ }
+
+ private void testPartialPkListPlan(String tenantView) throws Exception {
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) {
+ PreparedStatement preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID1) IN " +
+ "(('005xx000001Sv6o')," +
+ "('005xx000001Sv6o'))");
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+
+ viewConn.prepareStatement("DELETE FROM " + tenantView + " WHERE (ID1) IN " +
+ "(('005xx000001Sv6o')," +
+ "('005xx000001Sv6o'))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+
+ preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID2) IN " +
+ "(('000000000000500')," +
+ "('000000000000400'))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+
+ viewConn.prepareStatement("DELETE FROM " + tenantView + " WHERE (ID2) IN " +
+ "(('000000000000500')," +
+ "('000000000000400'))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+ }
+ }
+
+ private void testPartialPkPlusNonPkListPlan(String tenantView) throws Exception {
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) {
+ PreparedStatement preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID1, ID3) IN " +
+ "(('005xx000001Sv6o', 1)," +
+ "('005xx000001Sv6o', 2))");
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+
+ viewConn.prepareStatement("DELETE FROM " + tenantView + " WHERE (ID1, ID3) IN " +
+ "(('005xx000001Sv6o', 1)," +
+ "('005xx000001Sv6o', 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+
+ preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID2, ID3) IN " +
+ "(('000000000000500', 1)," +
+ "('000000000000400', 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+
+ viewConn.prepareStatement("DELETE FROM " + tenantView + " WHERE (ID2, ID3) IN " +
+ "(('000000000000500', 1)," +
+ "('000000000000400', 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+ }
+ }
+
+ private void testNonPkListPlan(String tenantView) throws Exception {
+ // Tenant connection should generate a range scan because tenant id is the leading PK.
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) {
+ PreparedStatement preparedStmt = viewConn.prepareStatement("SELECT * FROM " + tenantView + " WHERE (ID3, ID4) IN " +
+ "((1, 1)," +
+ "(2, 2))");
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+
+ viewConn.prepareStatement("DELETE FROM " + tenantView + " WHERE (ID3, ID4) IN " +
+ "((1, 1)," +
+ "(2, 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertTrue(queryPlan.getExplainPlan().toString().contains("CLIENT PARALLEL 1-WAY RANGE SCAN OVER"));
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan2() throws Exception {
Review comment:
nit rename test based on query plan you are expecting/scenario
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services