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/17 22:52:10 UTC
[GitHub] [phoenix] yanxinyi opened a new pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
yanxinyi opened a new pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
URL: https://github.com/apache/phoenix/pull/736
…g scan with non-pk ordered pks
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396235080
##########
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 {
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
[GitHub] [phoenix] dbwong commented on issue #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
dbwong commented on issue #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
URL: https://github.com/apache/phoenix/pull/736#issuecomment-602409968
Maybe @comnetwork would be interested as well.
----------------------------------------------------------------
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
[GitHub] [phoenix] ChinmaySKulkarni commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
ChinmaySKulkarni 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_r397362633
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
##########
@@ -586,4 +601,932 @@ 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 testPkDescOrderedTenantViewOnGlobalViewWithRightQueryPlan() 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.
Review comment:
nit: Mark this comment as a "TODO" so its easier to keep track of
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396238695
##########
File path: phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
##########
@@ -62,4 +70,68 @@ public void testHashCode() throws Exception {
+ hashCode, firstHashCode, hashCode);
}
}
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListNotPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(false);
+ }
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(true);
+ }
+
+ private void testGetSortedRowValueConstructorExpressionList(boolean isPkOrder) {
Review comment:
Why don't we break this up more? One we aren't testing the code in InListColumnKeyValuePair. Two everything is being tested as part of the constructor. I'd test it in 2 parts, 1 a InListRVCRewritte class/method that takes a array of indexes and a RVC and returns a new RVC with the new order. 2 a method that constructs an order array given a RVC of PKs. Then its easy to test some of the scenarios like if we have less/more values than the array. Or another way were we can test the indiviudal logic that I know you put in there based on the ITs.
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396235113
##########
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 {
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
[GitHub] [phoenix] ChinmaySKulkarni commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
ChinmaySKulkarni 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_r397363979
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
##########
@@ -330,4 +342,98 @@ public String toString() {
public InListExpression clone(List<Expression> l) {
return new InListExpression(l, this.rowKeyOrderOptimizable);
}
+
+ public static List<InListColumnKeyValuePair> getSortedInListColumnKeyValuePair(List<Expression> children,
Review comment:
nit: Add comment header to method.
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396235284
##########
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 {
+ 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 + "(DOUBLE1 DOUBLE NOT NULL, INT1 BIGINT NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(12.0, 8)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(13.0, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan3() 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();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ 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.getInt(1));
+ stmt.execute("DELETE FROM " + tenantView);
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan4() 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 + "(DOUBLE1 VARCHAR NOT NULL, INT1 VARCHAR NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "('12.0', '8')");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "('13.0', '9')");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "(('8', '12.0')," +
+ "('9', '13.0'))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "(('8', '12.0')," +
+ "('9', '13.0'))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan5() 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 + " (ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY " + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1, ID5) IN " +
+ "((12, 7)," +
+ "(12, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID4, ID1) IN " +
+ "((9, 13)," +
+ "(12, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "((8, 12)," +
+ "(9, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "((8, 12)," +
+ "(9, 13))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithFunction() 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 + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1 + 1, ID5) IN " +
+ "((13, 7)," +
+ "(13, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID4 - 1, ID1) IN " +
+ "((8, 13)," +
+ "(11, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2 + 1 , ID1 - 1) IN " +
+ "((9, 11)," +
+ "(10, 12))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2 -1, ID1 + 1) IN " +
+ "((7, 13)," +
+ "(8, 14))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithFunctionAndIndex() throws Exception {
+ String tenantView = generateUniqueName();
+ String tenantIndexView = 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 + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ stmt.execute("CREATE INDEX " + tenantIndexView + " ON " + tenantView + " (ID5) INCLUDE (ID4, ID1)");
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1 + 1, ID5) IN " +
+ "((13, 7)," +
+ "(13, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5 - 1, ID1) IN " +
+ "((6, 13)," +
+ "(12, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5 + 1 , ID1 - 1) IN " +
+ "((8, 11)," +
+ "(14, 12))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID5 -1, ID1 + 1) IN " +
+ "((6, 13)," +
+ "(12, 14))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithIndex() throws Exception {
+ String tenantView = generateUniqueName();
+ String tenantIndexView = 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 + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ stmt.execute("CREATE INDEX " + tenantIndexView + " ON " + tenantView + " (ID5) INCLUDE (ID4)");
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5, ID1) IN " +
+ "((7, 12)," +
+ "(7, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2, ID5) IN " +
+ "((8, 13)," +
+ "(9, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5, ID4) IN " +
+ "((7, 6)," +
+ "(13, 9))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, ID5) IN " +
+ "((12, 7)," +
+ "(13, 13))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithGlobalViewAndFunction() throws Exception {
+ String tenantView = generateUniqueName();
+ String globalView = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE VIEW " + globalView + " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+ }
+ }
+
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ try (Statement stmt = viewConn.createStatement()) {
+ stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + globalView);
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1 + 1, ID5) IN " +
+ "((13, 7)," +
+ "(13, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID4 - 1, ID1) IN " +
+ "((8, 13)," +
+ "(11, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2 + 1 , ID1 - 1) IN " +
+ "((9, 11)," +
+ "(10, 12))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2 -1, ID1 + 1) IN " +
+ "((7, 13)," +
+ "(8, 14))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithGlobalViewAndFunction2() 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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396235035
##########
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 {
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396234941
##########
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 {
+ 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 + "(DOUBLE1 DOUBLE NOT NULL, INT1 BIGINT NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(12.0, 8)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(13.0, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan3() 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
[GitHub] [phoenix] yanxinyi closed pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
yanxinyi closed pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
URL: https://github.com/apache/phoenix/pull/736
----------------------------------------------------------------
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
[GitHub] [phoenix] yanxinyi commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
yanxinyi 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_r397434968
##########
File path: phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
##########
@@ -62,4 +70,68 @@ public void testHashCode() throws Exception {
+ hashCode, firstHashCode, hashCode);
}
}
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListNotPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(false);
+ }
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(true);
+ }
+
+ private void testGetSortedRowValueConstructorExpressionList(boolean isPkOrder) {
Review comment:
It used to pass a list of expression that does sorting and reconstructing rvc.
the new implementation takes a list of `InListColumnKeyValuePair` instead so that I can test sorting logic and construction logic in a method and test reconstructed a new rvc in this method.
----------------------------------------------------------------
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
[GitHub] [phoenix] yanxinyi commented on issue #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
yanxinyi commented on issue #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
URL: https://github.com/apache/phoenix/pull/736#issuecomment-600953010
added unit test for PK ordered as well as non-pk ordered position cases.
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r397430016
##########
File path: phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
##########
@@ -62,4 +70,68 @@ public void testHashCode() throws Exception {
+ hashCode, firstHashCode, hashCode);
}
}
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListNotPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(false);
+ }
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(true);
+ }
+
+ private void testGetSortedRowValueConstructorExpressionList(boolean isPkOrder) {
Review comment:
@yanxinyi don't like that particular refactoring? I think it will make the code a lot cleaner?
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396235302
##########
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 {
+ 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 + "(DOUBLE1 DOUBLE NOT NULL, INT1 BIGINT NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(12.0, 8)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(13.0, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan3() 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();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ 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.getInt(1));
+ stmt.execute("DELETE FROM " + tenantView);
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan4() 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 + "(DOUBLE1 VARCHAR NOT NULL, INT1 VARCHAR NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "('12.0', '8')");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "('13.0', '9')");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "(('8', '12.0')," +
+ "('9', '13.0'))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "(('8', '12.0')," +
+ "('9', '13.0'))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan5() 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 + " (ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY " + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1, ID5) IN " +
+ "((12, 7)," +
+ "(12, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID4, ID1) IN " +
+ "((9, 13)," +
+ "(12, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "((8, 12)," +
+ "(9, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "((8, 12)," +
+ "(9, 13))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithFunction() 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 + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1 + 1, ID5) IN " +
+ "((13, 7)," +
+ "(13, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID4 - 1, ID1) IN " +
+ "((8, 13)," +
+ "(11, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2 + 1 , ID1 - 1) IN " +
+ "((9, 11)," +
+ "(10, 12))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2 -1, ID1 + 1) IN " +
+ "((7, 13)," +
+ "(8, 14))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithFunctionAndIndex() throws Exception {
+ String tenantView = generateUniqueName();
+ String tenantIndexView = 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 + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ stmt.execute("CREATE INDEX " + tenantIndexView + " ON " + tenantView + " (ID5) INCLUDE (ID4, ID1)");
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1 + 1, ID5) IN " +
+ "((13, 7)," +
+ "(13, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5 - 1, ID1) IN " +
+ "((6, 13)," +
+ "(12, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5 + 1 , ID1 - 1) IN " +
+ "((8, 11)," +
+ "(14, 12))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID5 -1, ID1 + 1) IN " +
+ "((6, 13)," +
+ "(12, 14))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithIndex() throws Exception {
+ String tenantView = generateUniqueName();
+ String tenantIndexView = 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 + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ stmt.execute("CREATE INDEX " + tenantIndexView + " ON " + tenantView + " (ID5) INCLUDE (ID4)");
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5, ID1) IN " +
+ "((7, 12)," +
+ "(7, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2, ID5) IN " +
+ "((8, 13)," +
+ "(9, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID5, ID4) IN " +
+ "((7, 6)," +
+ "(13, 9))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, ID5) IN " +
+ "((12, 7)," +
+ "(13, 13))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithGlobalViewAndFunction() throws Exception {
+ String tenantView = generateUniqueName();
+ String globalView = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE VIEW " + globalView + " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+ }
+ }
+
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ try (Statement stmt = viewConn.createStatement()) {
+ stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + "(ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + globalView);
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(12, 8, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(13, 9, 13, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID1 + 1, ID5) IN " +
+ "((13, 7)," +
+ "(13, 13))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID4 - 1, ID1) IN " +
+ "((8, 13)," +
+ "(11, 13))");
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (ID2 + 1 , ID1 - 1) IN " +
+ "((9, 11)," +
+ "(10, 12))");
+ assertTrue(rs.next());
+ assertEquals(12, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(rs.next());
+ assertEquals(13, rs.getInt(1));
+ assertEquals(9, rs.getInt(2));
+ assertEquals(13, rs.getInt(3));
+ assertEquals(9, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2 -1, ID1 + 1) IN " +
+ "((7, 13)," +
+ "(8, 14))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithGlobalViewAndFunction2() throws Exception {
+ String tenantView = generateUniqueName();
+ String globalView = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE VIEW " + globalView + " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+ }
+ }
+
+ try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ viewConn.setAutoCommit(true);
+ try (Statement stmt = viewConn.createStatement()) {
+ stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + "(ID1 DOUBLE NOT NULL, ID2 DOUBLE NOT NULL, ID4 BIGINT " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (ID1, ID2)) " +
+ " AS SELECT * FROM " + globalView);
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(9, 2, 7, 6)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(2, 9, 13, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + tenantView + " WHERE (POWER(ID2, 2), ID1) IN " +
+ "((4.0, 9)," +
+ "(10, 12))");
+ assertTrue(rs.next());
+ assertEquals(9, rs.getInt(1));
+ assertEquals(2, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (POWER(ID1,2), ID2) IN " +
+ "((81, 2)," +
+ "(4, 9))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testBaseTableAndIndexTableHaveReversePKOrder() throws Exception {
+ String view = generateUniqueName();
+ String index = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
+ conn.setAutoCommit(true);
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE VIEW " + view + " (ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, ID4 BIGINT " +
+ "CONSTRAINT PKVIEW PRIMARY KEY (ID1, ID2)) AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ conn.createStatement().execute("UPSERT INTO " + view + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(9, 2, 7, 6)");
+ conn.createStatement().execute("UPSERT INTO " + view + "(ID1, ID2, ID5, ID4) VALUES " +
+ "(2, 9, 13, 9)");
+ conn.commit();
+
+ stmt.execute("CREATE INDEX " + index + " ON " + view + " (ID2, ID1) INCLUDE (ID5, ID4)");
+
+ // TESTING for optimized scan
+ PreparedStatement preparedStmt = conn.prepareStatement("SELECT * FROM " + view + " WHERE (ID1, ID2) IN " +
+ "((1, 1)," +
+ "(2, 2))");
+ QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(new Long(2), queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+
+ preparedStmt = conn.prepareStatement("SELECT * FROM " + view + " WHERE (ID2, ID1) IN ((1, 1),(2, 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(new Long(2), queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+
+
+ preparedStmt = conn.prepareStatement("SELECT * FROM " + view + " WHERE (ID2, ID1) IN ((1, 1),(2, 2))");
+ queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ assertEquals(new Long(2), queryPlan.getEstimatedRowsToScan());
+ assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+
+
+ ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, ID5, ID4 FROM " + view + " WHERE (POWER(ID2, 2), ID1) IN " +
+ "((4.0, 9)," +
+ "(10, 12))");
+ assertTrue(rs.next());
+ assertEquals(9, rs.getInt(1));
+ assertEquals(2, rs.getInt(2));
+ assertEquals(7, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertTrue(!rs.next());
+
+ stmt.execute("DELETE FROM " + view + " WHERE (POWER(ID1,2), ID2) IN " +
+ "((81, 2)," +
+ "(4, 9))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + view);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testBaseTableAndIndexTable2() 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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396235219
##########
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 {
+ 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 + "(DOUBLE1 DOUBLE NOT NULL, INT1 BIGINT NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(12.0, 8)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(13.0, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan3() 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();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ 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.getInt(1));
+ stmt.execute("DELETE FROM " + tenantView);
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan4() 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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396238695
##########
File path: phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
##########
@@ -62,4 +70,68 @@ public void testHashCode() throws Exception {
+ hashCode, firstHashCode, hashCode);
}
}
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListNotPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(false);
+ }
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionListPkOrder() {
+ testGetSortedRowValueConstructorExpressionList(true);
+ }
+
+ private void testGetSortedRowValueConstructorExpressionList(boolean isPkOrder) {
Review comment:
Why don't we break this up more? One we aren't testing the code in InListColumnKeyValuePair. Two everything is being tested as part of the constructor. I'd test it in 2 parts, 1 a InListRVCRewritte class/method that takes a array of indexes and a RVC and returns a new RVC with the new order. 2 a method that constructs an order array given a RVC of PKs. Then its easy to test some of the scenarios like if we have less/more values than the array.
----------------------------------------------------------------
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
[GitHub] [phoenix] yanxinyi commented on issue #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
yanxinyi commented on issue #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
URL: https://github.com/apache/phoenix/pull/736#issuecomment-600909570
Updated JIRA with future work, covered cases.
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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
[GitHub] [phoenix] ChinmaySKulkarni commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
ChinmaySKulkarni 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_r397362816
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
##########
@@ -586,4 +601,932 @@ 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 testPkDescOrderedTenantViewOnGlobalViewWithRightQueryPlan() 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 testColumnDescOrderedTenantViewOnGlobalViewWithStringValue() 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 testInListExpressionWithRightQueryPlanForTenantViewOnGlobalView() 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.
Review comment:
Same here
----------------------------------------------------------------
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
[GitHub] [phoenix] dbwong commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
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_r396235192
##########
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 {
+ 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 + "(DOUBLE1 DOUBLE NOT NULL, INT1 BIGINT NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(12.0, 8)");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "(13.0, 9)");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "((8, 12.0)," +
+ "(9, 13.0))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithDescOrderWithRightQueryPlan3() 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();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (ID2, ID1) IN " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300','foo'))");
+
+ 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.getInt(1));
+ stmt.execute("DELETE FROM " + tenantView);
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan4() 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 + "(DOUBLE1 VARCHAR NOT NULL, INT1 VARCHAR NOT NULL " +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (DOUBLE1, INT1)) " +
+ " AS SELECT * FROM " + tableName + " WHERE KEY_PREFIX = 'ABC'");
+
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "('12.0', '8')");
+ viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(DOUBLE1, INT1) VALUES " +
+ "('13.0', '9')");
+ viewConn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "(('8', '12.0')," +
+ "('9', '13.0'))");
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+
+ stmt.execute("DELETE FROM " + tenantView + " WHERE (INT1, DOUBLE1) IN " +
+ "(('8', '12.0')," +
+ "('9', '13.0'))");
+
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tenantView);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ }
+ }
+ }
+
+ @Test
+ public void testInListExpressionWithRightQueryPlan5() 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
[GitHub] [phoenix] ChinmaySKulkarni commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
ChinmaySKulkarni 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_r397365328
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
##########
@@ -330,4 +342,98 @@ public String toString() {
public InListExpression clone(List<Expression> l) {
return new InListExpression(l, this.rowKeyOrderOptimizable);
}
+
+ public static List<InListColumnKeyValuePair> getSortedInListColumnKeyValuePair(List<Expression> children,
+ boolean isStateless) {
+ List<InListColumnKeyValuePair> inListColumnKeyValuePairList = new ArrayList<>();
+ int numberOfColumns = 0;
+
+ for (int i = 0; i < children.size(); i++) {
+ Expression child = children.get(i);
+ if (i == 0) {
+ numberOfColumns = child.getChildren().size();
+ for (int j = 0; j < child.getChildren().size(); j++) {
+ if (child.getChildren().get(j) instanceof RowKeyColumnExpression) {
+ RowKeyColumnExpression rowKeyColumnExpression =
+ (RowKeyColumnExpression)child.getChildren().get(j);
+ InListColumnKeyValuePair inListColumnKeyValuePair =
+ new InListColumnKeyValuePair(rowKeyColumnExpression);
+ inListColumnKeyValuePairList.add(inListColumnKeyValuePair);
+ } else {
Review comment:
Add comment here indicating that since at least one of the columns is not part of the PK, we ignore
----------------------------------------------------------------
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
[GitHub] [phoenix] ChinmaySKulkarni commented on a change in pull request #736: PHOENIX-5698 Phoenix Query with RVC IN list expression generates wron…
Posted by GitBox <gi...@apache.org>.
ChinmaySKulkarni 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_r397381557
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
##########
@@ -330,4 +342,98 @@ public String toString() {
public InListExpression clone(List<Expression> l) {
return new InListExpression(l, this.rowKeyOrderOptimizable);
}
+
+ public static List<InListColumnKeyValuePair> getSortedInListColumnKeyValuePair(List<Expression> children,
+ boolean isStateless) {
+ List<InListColumnKeyValuePair> inListColumnKeyValuePairList = new ArrayList<>();
+ int numberOfColumns = 0;
+
+ for (int i = 0; i < children.size(); i++) {
+ Expression child = children.get(i);
+ if (i == 0) {
+ numberOfColumns = child.getChildren().size();
+ for (int j = 0; j < child.getChildren().size(); j++) {
+ if (child.getChildren().get(j) instanceof RowKeyColumnExpression) {
+ RowKeyColumnExpression rowKeyColumnExpression =
+ (RowKeyColumnExpression)child.getChildren().get(j);
+ InListColumnKeyValuePair inListColumnKeyValuePair =
+ new InListColumnKeyValuePair(rowKeyColumnExpression);
+ inListColumnKeyValuePairList.add(inListColumnKeyValuePair);
+ } else {
+ return null;
+ }
+ }
+ } else {
+ if (numberOfColumns != child.getChildren().size()) {
+ // if the number of the PK columns doesn't match number of values,
+ // it should not sort it in PK position.
+ return null;
+ }
+
+ for (int j = 0; j < child.getChildren().size(); j++) {
+ LiteralExpression literalExpression = (LiteralExpression) child.getChildren().get(j);
+ inListColumnKeyValuePairList.get(j).addToLiteralExpressionList(literalExpression);
+ }
+ }
+ }
+ Collections.sort(inListColumnKeyValuePairList);
+ return inListColumnKeyValuePairList;
+
+
+ }
+
+ public static List<Expression> getSortedRowValueConstructorExpressionList(
Review comment:
Same here, let's add a comment header for this method.
----------------------------------------------------------------
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