This is an automated email from the ASF dual-hosted git repository.
yanxinyi pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push:
new 14a17d8 PHOENIX-5698 Phoenix Query with RVC IN list expression
generates wrong scan with non-pk ordered pks
14a17d8 is described below
commit 14a17d8f9b23089bea4c2a910430a73c669bc0fb
Author: Xinyi Yan <x...@salesforce.com>
AuthorDate: Mon Mar 16 16:59:25 2020 -0700
PHOENIX-5698 Phoenix Query with RVC IN list expression generates wrong scan
with non-pk ordered pks
Signed-off-by: Xinyi Yan <yanxi...@apache.org>
---
.../java/org/apache/phoenix/end2end/InListIT.java | 1140 ++++++++++++++++++--
.../phoenix/expression/InListExpression.java | 118 +-
.../org/apache/phoenix/iterate/ExplainTable.java | 3 +-
.../phoenix/expression/InListExpressionTest.java | 207 +++-
4 files changed, 1350 insertions(+), 118 deletions(-)
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
index 7e0a7a5..ba4a511 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
@@ -29,18 +29,21 @@ import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
+import java.sql.PreparedStatement;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Properties;
+import org.apache.phoenix.compile.QueryPlan;
+import org.apache.phoenix.iterate.ExplainTable;
import org.apache.phoenix.schema.SortOrder;
import org.apache.phoenix.schema.TypeMismatchException;
import org.apache.phoenix.schema.types.PDataType;
import org.apache.phoenix.schema.types.PInteger;
-import org.apache.phoenix.schema.types.PLong;
import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Before;
import org.junit.Test;
import com.google.common.base.Function;
@@ -49,8 +52,19 @@ import com.google.common.collect.Lists;
public class InListIT extends ParallelStatsDisabledIT {
-
- private final String TENANT_SPECIFIC_URL1 = getUrl() + ';' +
TENANT_ID_ATTRIB + "=tenant1";
+ private static final String TENANT_SPECIFIC_URL1 = getUrl() + ';' +
TENANT_ID_ATTRIB + "=tenant1";
+ String tableName;
+ String descViewName;
+ String ascViewName;
+
+ @Before
+ public void setup() throws Exception {
+ tableName = generateUniqueName();
+ descViewName = generateUniqueName();
+ ascViewName = generateUniqueName();
+ buildSchema(tableName, generateUniqueName(), true);
+ buildSchema(generateUniqueName(), generateUniqueName(), false);
+ }
@Test
public void testLeadingPKWithTrailingRVC() throws Exception {
@@ -71,7 +85,7 @@ public class InListIT extends ParallelStatsDisabledIT {
assertTrue(rs.next());
assertEquals("c", rs.getString(1));
assertFalse(rs.next());
-
+
conn.close();
}
@@ -90,10 +104,10 @@ public class InListIT extends ParallelStatsDisabledIT {
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
-
+
conn.close();
}
-
+
/**
* Builds the DDL statement that will create a table with the given
properties.
* Assumes 5 pk columns of the given type.
@@ -107,7 +121,7 @@ public class InListIT extends ParallelStatsDisabledIT {
private static String createTableDDL(String tableName, PDataType pkType,
int saltBuckets, boolean isMultiTenant) {
StringBuilder ddlBuilder = new StringBuilder();
ddlBuilder.append("CREATE TABLE ").append(tableName).append(" ( ");
-
+
// column declarations
if(isMultiTenant) {
ddlBuilder.append("tenantId VARCHAR(5) NOT NULL, ");
@@ -116,14 +130,14 @@ public class InListIT extends ParallelStatsDisabledIT {
ddlBuilder.append("pk").append(i + 1).append("
").append(pkType.getSqlTypeName()).append(" NOT NULL, ");
}
ddlBuilder.append("nonPk VARCHAR ");
-
+
// primary key constraint declaration
ddlBuilder.append("CONSTRAINT pk PRIMARY KEY (");
if(isMultiTenant) {
ddlBuilder.append("tenantId, ");
}
ddlBuilder.append("pk1, pk2, pk3, pk4, pk5) ) ");
-
+
// modifier declarations
if(saltBuckets != 0) {
ddlBuilder.append("SALT_BUCKETS = ").append(saltBuckets);
@@ -134,10 +148,10 @@ public class InListIT extends ParallelStatsDisabledIT {
if(isMultiTenant) {
ddlBuilder.append("MULTI_TENANT=true");
}
-
+
return ddlBuilder.toString();
}
-
+
/**
* Creates a table with the given properties and returns its name. If the
table is multi-tenant,
* also creates a tenant view for that table and returns the name of the
view instead.
@@ -149,32 +163,32 @@ public class InListIT extends ParallelStatsDisabledIT {
* @return the table or view name that should be used to access the
created table
*/
private static String initializeAndGetTable(Connection baseConn,
Connection conn, boolean isMultiTenant, PDataType pkType, int saltBuckets)
throws SQLException {
- String tableName = generateUniqueName() + "in_test" +
pkType.getSqlTypeName() + saltBuckets + (isMultiTenant ? "_multi" : "_single");
- String tableDDL = createTableDDL(tableName, pkType, saltBuckets,
isMultiTenant);
- baseConn.createStatement().execute(tableDDL);
-
- // if requested, create a tenant specific view and return the view
name instead
- if(isMultiTenant) {
- String viewName = tableName + "_view";
- String viewDDL = "CREATE VIEW " + viewName + " AS SELECT *
FROM " + tableName;
- conn.createStatement().execute(viewDDL);
- return viewName;
- }
- else {
- return tableName;
- }
+ String tableName = generateUniqueName() + "in_test" +
pkType.getSqlTypeName() + saltBuckets + (isMultiTenant ? "_multi" : "_single");
+ String tableDDL = createTableDDL(tableName, pkType, saltBuckets,
isMultiTenant);
+ baseConn.createStatement().execute(tableDDL);
+
+ // if requested, create a tenant specific view and return the view
name instead
+ if(isMultiTenant) {
+ String viewName = tableName + "_view";
+ String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM "
+ tableName;
+ conn.createStatement().execute(viewDDL);
+ return viewName;
+ }
+ else {
+ return tableName;
+ }
}
-
+
private static final String TENANT_ID = "ABC";
private static final String TENANT_URL = getUrl() + ";" +
PhoenixRuntime.TENANT_ID_ATTRIB + '=' + TENANT_ID;
-
+
// the different combinations to check each test against
private static final List<Boolean> TENANCIES = Arrays.asList(false, true);
private static final List<? extends PDataType> INTEGER_TYPES =
Arrays.asList(PInteger.INSTANCE);
private static final List<Integer> SALT_BUCKET_NUMBERS = Arrays.asList(0,
4);
private static final List<String> HINTS = Arrays.asList("/*+ SKIP_SCAN
*/", "/*+ RANGE_SCAN */");
-
+
/**
* Tests the given where clause against the given upserts by comparing
against the list of
* expected result strings.
@@ -187,9 +201,9 @@ public class InListIT extends ParallelStatsDisabledIT {
// test single and multitenant tables
for(boolean isMultiTenant : TENANCIES) {
Connection baseConn = DriverManager.getConnection(getUrl());
- Connection conn = isMultiTenant ?
DriverManager.getConnection(TENANT_URL)
- : baseConn;
-
+ Connection conn = isMultiTenant ?
DriverManager.getConnection(TENANT_URL)
+ : baseConn;
+
try {
// test each combination of types and salting
for(PDataType pkType : INTEGER_TYPES) {
@@ -197,7 +211,7 @@ public class InListIT extends ParallelStatsDisabledIT {
// use a different table with a unique name for each
variation
String tableName = initializeAndGetTable(baseConn,
conn, isMultiTenant, pkType, saltBuckets);
- // upsert the given data
+ // upsert the given data
for(String upsertBody : upsertBodies) {
conn.createStatement().execute("UPSERT INTO " +
tableName + " " + upsertBody);
}
@@ -228,224 +242,224 @@ public class InListIT extends ParallelStatsDisabledIT {
}
}
}
-
+
List<List<Object>> DEFAULT_UPSERTS =
Arrays.asList(Arrays.<Object>asList(1, 2, 4, 5, 6, "row1"),
-
Arrays.<Object>asList(2, 3, 4, 5, 6, "row2"),
-
Arrays.<Object>asList(2, 3, 6, 4, 5, "row3"),
-
Arrays.<Object>asList(6, 5, 4, 3, 2, "row4"));
-
+ Arrays.<Object>asList(2, 3, 4, 5, 6, "row2"),
+ Arrays.<Object>asList(2, 3, 6, 4, 5, "row3"),
+ Arrays.<Object>asList(6, 5, 4, 3, 2, "row4"));
+
List<String> DEFAULT_UPSERT_BODIES = Lists.transform(DEFAULT_UPSERTS, new
Function<List<Object>, String>() {
@Override
public String apply(List<Object> input) {
List<Object> pks = input.subList(0, 5);
Object nonPk = input.get(5);
-
+
return "(pk1, pk2, pk3, pk4, pk5, nonPk) VALUES ( "
- + Joiner.on(", ").join(pks) + ", '" + nonPk + "')";
+ + Joiner.on(", ").join(pks) + ", '" + nonPk + "')";
}
});
-
+
// test variations used:
// 1. queries with no results
// 2. queries with fully qualified row keys
// 3. queries with partiall qualified row keys, starting from the beginning
// 4. queries with partially qualified row keys, but not the beginning
// 5. queries with partially qualified row keys with a "hole slot" in the
middle
-
+
@Test
public void testPlainRVCNoResults() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3, pk4, pk5) IN ((1, 2, 3, 4,
5), (1, 2, 4, 5, 3))";
List<String> expecteds = Collections.<String>emptyList();
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testPlainRVCFullyQualified() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3, pk4, pk5) IN ((1, 2, 3, 4,
5), (1, 2, 4, 5, 6))";
List<String> expecteds = singletonList("row1");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testPlainRVCPartiallyQualifiedBegin() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3, pk4) IN ((2, 3, 4, 5), (1,
2, 4, 5))";
List<String> expecteds = Arrays.asList("row1", "row2");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testPlainRVCPartiallyQualifiedEnd() throws Exception {
String whereClause = "WHERE (pk2, pk3, pk4, pk5) IN ((2, 3, 4, 5), (2,
4, 5, 6))";
List<String> expecteds = singletonList("row1");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testPlainRVCSlotHole() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk4, pk5) IN ((1, 2, 4, 5), (6,
5, 3, 2))";
List<String> expecteds = singletonList("row4");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingPKWithTrailingRVCNoResults() throws Exception {
String whereClause = "WHERE pk1 != 2 AND (pk3, pk4, pk5) IN ((6, 4,
5), (5, 6, 4))";
List<String> expecteds = Collections.<String>emptyList();
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingPKWithTrailingRVCFullyQualified() throws Exception {
String whereClause = "WHERE pk1 = 2 AND (pk2, pk3, pk4, pk5) IN ((2,
4, 5, 6), (3, 4, 5, 6))";
List<String> expecteds = singletonList("row2");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingPKWithTrailingRVCPartiallyQualifiedBegin() throws
Exception {
String whereClause = "WHERE pk1 = 2 AND (pk2, pk3) IN ((3, 6), (5,
4))";
List<String> expecteds = singletonList("row3");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingPKWithTrailingRVCPartiallyQualifiedEnd() throws
Exception {
String whereClause = "WHERE pk2 = 2 AND (pk3, pk4, pk5) IN ((4, 5, 6),
(5, 6, 4))";
List<String> expecteds = singletonList("row1");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingPKWithTrailingRVCSlotHole() throws Exception {
String whereClause = "WHERE pk1 = 2 AND (pk3, pk4, pk5) IN ((4, 5, 6),
(5, 6, 4))";
List<String> expecteds = singletonList("row2");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingRVCWithTrailingPKNoResults() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3) IN ((2, 3, 4), (2, 3, 6))
AND pk4 = 3";
List<String> expecteds = Collections.<String>emptyList();
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingRVCWithTrailingPKFullyQualified() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3, pk4) IN ((1, 2, 4, 5), (2,
3, 4, 5)) AND pk5 = 6";
List<String> expecteds = Arrays.asList("row1", "row2");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingRVCWithTrailingPKPartiallyQualifiedBegin() throws
Exception {
String whereClause = "WHERE (pk1, pk2, pk3) IN ((2, 3, 4), (2, 3, 6))
AND pk4 = 4";
List<String> expecteds = singletonList("row3");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingRVCWithTrailingPKPartiallyQualifiedEnd() throws
Exception {
String whereClause = "WHERE (pk2, pk3, pk4) IN ((3, 4, 5), (3, 6, 4))
AND pk5 = 5";
List<String> expecteds = singletonList("row3");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testLeadingRVCWithTrailingPKSlotHole() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3) IN ((2, 3, 4), (2, 3, 6))
AND pk5 = 5";
List<String> expecteds = singletonList("row3");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndPKNoResults() throws Exception {
String whereClause = "WHERE (pk1, pk2) IN ((1, 2), (2, 3)) AND pk2 =
4";
List<String> expecteds = Collections.<String>emptyList();
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndPKFullyQualified() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3, pk4, pk5) IN ((1, 2, 4, 5,
6), (2, 3, 4, 5, 6)) AND pk1 = 2";
List<String> expecteds = singletonList("row2");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndPKPartiallyQualifiedBegin() throws
Exception {
String whereClause = "WHERE (pk1, pk2, pk3) IN ((1, 2, 4), (2, 3, 6))
AND pk3 = 4";
List<String> expecteds = singletonList("row1");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndPKPartiallyQualifiedEnd() throws
Exception {
String whereClause = "WHERE (pk3, pk4, pk5) IN ((4, 5, 6), (4, 3, 2))
AND pk5 = 2";
List<String> expecteds = singletonList("row4");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndRVCNoResults() throws Exception {
String whereClause = "WHERE (pk1, pk2) IN ((1, 2), (2, 3)) AND (pk2,
pk3) IN ((4, 4), (4, 6))";
List<String> expecteds = Collections.<String>emptyList();
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndRVCFullyQualified() throws Exception {
String whereClause = "WHERE (pk1, pk2, pk3) IN ((2, 3, 6), (2, 3, 4))
AND (pk3, pk4, pk5) IN ((4, 5, 6), (4, 3, 2))";
List<String> expecteds = singletonList("row2");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndRVCPartiallyQualifiedBegin() throws
Exception {
String whereClause = "WHERE (pk1, pk2) IN ((1, 2), (2, 3)) AND (pk2,
pk3) IN ((3, 4), (3, 6))";
List<String> expecteds = Arrays.asList("row2", "row3");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testOverlappingRVCAndRVCPartiallyQualifiedEnd() throws
Exception {
String whereClause = "WHERE (pk3, pk4) IN ((4, 5), (4, 3)) AND (pk4,
pk5) IN ((3, 2), (4, 5))";
List<String> expecteds = singletonList("row4");
-
+
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
-
+
@Test
public void testWithFixedLengthDescPK() throws Exception {
testWithFixedLengthPK(SortOrder.DESC);
}
-
+
@Test
public void testWithFixedLengthAscPK() throws Exception {
- testWithFixedLengthPK(SortOrder.ASC);
+ testWithFixedLengthPK(SortOrder.ASC);
}
-
+
@Test
public void testWithFixedLengthKV() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
@@ -463,7 +477,7 @@ public class InListIT extends ParallelStatsDisabledIT {
assertTrue(rs.next());
assertEquals("bb", rs.getString(1));
assertFalse(rs.next());
-
+
conn.close();
}
@@ -483,7 +497,7 @@ public class InListIT extends ParallelStatsDisabledIT {
assertTrue(rs.next());
assertEquals(sortOrder == SortOrder.ASC ? "bb" : "aa",
rs.getString(1));
assertFalse(rs.next());
-
+
conn.close();
}
@@ -534,7 +548,7 @@ public class InListIT extends ParallelStatsDisabledIT {
assertEquals(0, rs.getInt(1));
}
}
-
+
@Test
public void testInListExpressionWithDesc() throws Exception {
String fullTableName = generateUniqueName();
@@ -543,38 +557,38 @@ public class InListIT extends ParallelStatsDisabledIT {
// create base table and global view using global connection
try (Connection conn = DriverManager.getConnection(getUrl())) {
Statement stmt = conn.createStatement();
- stmt.execute("CREATE TABLE " + fullTableName + "(\n" +
- " TENANT_ID CHAR(15) NOT NULL,\n" +
- " KEY_PREFIX CHAR(3) NOT NULL,\n" +
- " CREATED_DATE DATE,\n" +
- " CREATED_BY CHAR(15),\n" +
- " SYSTEM_MODSTAMP DATE\n" +
- " CONSTRAINT PK PRIMARY KEY (\n" +
- " TENANT_ID," +
- " KEY_PREFIX" +
+ stmt.execute("CREATE TABLE " + fullTableName + "(\n" +
+ " TENANT_ID CHAR(15) NOT NULL,\n" +
+ " KEY_PREFIX CHAR(3) NOT NULL,\n" +
+ " CREATED_DATE DATE,\n" +
+ " CREATED_BY CHAR(15),\n" +
+ " SYSTEM_MODSTAMP DATE\n" +
+ " CONSTRAINT PK PRIMARY KEY (\n" +
+ " TENANT_ID," +
+ " KEY_PREFIX" +
")) MULTI_TENANT=TRUE");
-
- stmt.execute("CREATE VIEW " + fullViewName + "(\n" +
- " MODEL VARCHAR NOT NULL,\n" +
- " MILEAGE BIGINT NOT NULL,\n" +
- " MILES_DRIVEN BIGINT NOT NULL,\n" +
- " MAKE VARCHAR,\n" +
- " CONSTRAINT PKVIEW PRIMARY KEY\n" +
- " (\n" +
- " MODEL, MILEAGE DESC, MILES_DRIVEN\n" +
+
+ stmt.execute("CREATE VIEW " + fullViewName + "(\n" +
+ " MODEL VARCHAR NOT NULL,\n" +
+ " MILEAGE BIGINT NOT NULL,\n" +
+ " MILES_DRIVEN BIGINT NOT NULL,\n" +
+ " MAKE VARCHAR,\n" +
+ " CONSTRAINT PKVIEW PRIMARY KEY\n" +
+ " (\n" +
+ " MODEL, MILEAGE DESC, MILES_DRIVEN\n" +
")) AS SELECT * FROM " + fullTableName + " WHERE
KEY_PREFIX = '0CY'");
-
+
}
-
+
// create and use a tenant specific view to write data
- try (Connection viewConn =
DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) {
+ try (Connection viewConn =
DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) {
Statement stmt = viewConn.createStatement();
stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS
SELECT * FROM " + fullViewName );
viewConn.createStatement().execute("UPSERT INTO " + tenantView +
"(CREATED_BY, CREATED_DATE, SYSTEM_MODSTAMP, MODEL, MILEAGE, MILES_DRIVEN,
MAKE) VALUES ('005xx000001Sv6o', 1532458254819, 1532458254819, 'a5', 23, 10000,
'AUDI')");
viewConn.createStatement().execute("UPSERT INTO " + tenantView +
"(CREATED_BY, CREATED_DATE, SYSTEM_MODSTAMP, MODEL, MILEAGE, MILES_DRIVEN,
MAKE) VALUES ('005xx000001Sv6o', 1532458254819, 1532458254819, 'a4', 27, 30000,
'AUDI')");
viewConn.createStatement().execute("UPSERT INTO " + tenantView +
"(CREATED_BY, CREATED_DATE, SYSTEM_MODSTAMP, MODEL, MILEAGE, MILES_DRIVEN,
MAKE) VALUES ('005xx000001Sv6o', 1532458254819, 1532458254819, '328i', 32,
40000, 'BMW')");
viewConn.commit();
-
+
ResultSet rs = stmt.executeQuery("SELECT Make, Model FROM " +
tenantView + " WHERE MILEAGE IN (32, 27)");
assertTrue(rs.next());
assertEquals("BMW", rs.getString(1));
@@ -603,4 +617,900 @@ public class InListIT extends ParallelStatsDisabledIT {
}
}
+ 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));
+ }
+ }
+ }
+
+ @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 " +
+ "(('000000000000400', 'bar')," +
+ "('000000000000300', 'foo'))");
+
+ 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.getInt(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);
+ }
+
+ @Test
+ public void testInListExpressionGeneratesRightScanForDesc() throws
Exception {
+ testFullPkListPlan(this.descViewName);
+ testPartialPkListPlan(this.descViewName);
+ testPartialPkPlusNonPkListPlan(this.descViewName);
+ testNonPkListPlan(this.descViewName);
+ }
+
+ 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 testInListExpressionWithRightQueryPlanForNumericalValue()
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
testQueryPlanForPkDescOrderedTenantViewOnGlobalViewForStringValue() 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 testQueryPlanForTenantViewOnBaseTableWithVarcharValue() 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 testQueryPlanForTenantViewOnBaseTableWithNumericalValue()
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 testGlobalViewWithPowerFunction() 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 testDeletionFromTenantViewAndViewIndex() 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 + " (ID4,
ID2) INCLUDE (ID1, ID5)");
+
+ // TESTING for optimized scan
+ PreparedStatement preparedStmt = conn.prepareStatement("SELECT
* FROM " + view + " WHERE (ID4, ID2) IN " +
+ "((1, 1)," +
+ "(2, 2))");
+ QueryPlan queryPlan =
PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+
assertTrue(queryPlan.getExplainPlan().toString().contains("RANGE SCAN"));
+
+ preparedStmt = conn.prepareStatement("SELECT ID1,ID5 FROM " +
view + " WHERE (ID1, ID2) 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 testBaseTableAndIndexTableHaveRightScan() throws Exception {
+ String index = generateUniqueName();
+ String fullTableName = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + fullTableName + "(ID1 BIGINT
NOT NULL, ID2 BIGINT NOT NULL, " +
+ "VAL1 BIGINT, VAL2 BIGINT CONSTRAINT PK PRIMARY KEY
(ID1,ID2))");
+ stmt.execute("CREATE INDEX " + index + " ON " + fullTableName
+ " (ID2, ID1) INCLUDE (VAL2)");
+ }
+
+ PreparedStatement preparedStmt = conn.prepareStatement("SELECT
VAL2 FROM " + fullTableName +
+ " WHERE (ID2, ID1) IN ((1, 1),(2, 2))");
+ QueryPlan queryPlan =
PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
+ queryPlan.getTableRef().getTable().getType();
+
assertTrue(queryPlan.getExplainPlan().toString().contains(ExplainTable.POINT_LOOKUP_ON_STRING));
+ }
+ }
}
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
b/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
index fc276c8..900350d 100644
---
a/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
+++
b/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
@@ -26,7 +26,7 @@ import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
-import java.util.stream.Collectors;
+import java.util.ArrayList;
import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
@@ -85,12 +85,24 @@ public class InListExpression extends BaseSingleExpression {
// In case of after removing nulls there is no remaining element
in the IN list
return LiteralExpression.newConstant(false);
}
+
+ if (firstChild instanceof RowValueConstructorExpression) {
+ List<InListColumnKeyValuePair> inListColumnKeyValuePairList =
+ getSortedInListColumnKeyValuePair(childrenWithoutNulls);
+ if (inListColumnKeyValuePairList != null) {
+ childrenWithoutNulls =
getSortedRowValueConstructorExpressionList(
+ inListColumnKeyValuePairList,
firstChild.isStateless(),children.size() - 1);
+ firstChild = childrenWithoutNulls.get(0);
+ }
+ }
+
boolean nullInList = children.size() != childrenWithoutNulls.size();
if (childrenWithoutNulls.size() == 2 && !nullInList) {
return ComparisonExpression.create(isNegate ? CompareOp.NOT_EQUAL
: CompareOp.EQUAL,
childrenWithoutNulls, ptr, rowKeyOrderOptimizable);
}
+
SQLException sqlE = null;
List<Expression> coercedKeyExpressions =
Lists.newArrayListWithExpectedSize(childrenWithoutNulls.size());
coercedKeyExpressions.add(firstChild);
@@ -334,4 +346,108 @@ public class InListExpression extends
BaseSingleExpression {
public InListExpression clone(List<Expression> l) {
return new InListExpression(l, this.rowKeyOrderOptimizable);
}
+
+ /**
+ * get list of InListColumnKeyValuePair with a PK ordered structure
+ * @param children children from rvc
+ * @return the list of InListColumnKeyValuePair
+ */
+ public static List<InListColumnKeyValuePair>
getSortedInListColumnKeyValuePair(List<Expression> children) {
+ 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 {
+ // if one of the columns is not part of the pk, we
ignore.
+ 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;
+ }
+
+ /**
+ * get a PK ordered Expression RowValueConstructor
+ * @param inListColumnKeyValuePairList the object stores
RowKeyColumnExpression and List of LiteralExpression
+ * @param isStateless
+ * @param numberOfRows number of literalExpressions
+ * @return the new RowValueConstructorExpression with PK ordered
expressions
+ */
+ public static List<Expression> getSortedRowValueConstructorExpressionList(
+ List<InListColumnKeyValuePair> inListColumnKeyValuePairList,
boolean isStateless, int numberOfRows) {
+ List<Expression> l = new ArrayList<>();
+ //reconstruct columns
+ List<Expression> keyExpressions = new ArrayList<>();
+ for (int i = 0; i < inListColumnKeyValuePairList.size(); i++) {
+
keyExpressions.add(inListColumnKeyValuePairList.get(i).getRowKeyColumnExpression());
+ }
+ l.add(new RowValueConstructorExpression(keyExpressions,isStateless));
+
+ //reposition to corresponding values
+ List<List<Expression>> valueExpressionsList = new ArrayList<>();
+
+ for (int j = 0; j < inListColumnKeyValuePairList.size(); j++) {
+ List<LiteralExpression> valueList =
inListColumnKeyValuePairList.get(j).getLiteralExpressionList();
+ for (int i = 0; i < numberOfRows; i++) {
+ if (j == 0) {
+ valueExpressionsList.add(new ArrayList<Expression>());
+ }
+ valueExpressionsList.get(i).add(valueList.get(i));
+ }
+ }
+ for (List<Expression> valueExpressions: valueExpressionsList) {
+ l.add(new RowValueConstructorExpression(valueExpressions,
isStateless));
+ }
+ return l;
+ }
+
+ public static class InListColumnKeyValuePair implements
Comparable<InListColumnKeyValuePair> {
+ RowKeyColumnExpression rowKeyColumnExpression;
+ List<LiteralExpression> literalExpressionList;
+
+ public InListColumnKeyValuePair(RowKeyColumnExpression
rowKeyColumnExpression) {
+ this.rowKeyColumnExpression = rowKeyColumnExpression;
+ this.literalExpressionList = new ArrayList<>();
+ }
+
+ public RowKeyColumnExpression getRowKeyColumnExpression() {
+ return this.rowKeyColumnExpression;
+ }
+
+ public void addToLiteralExpressionList(LiteralExpression
literalExpression) {
+ this.literalExpressionList.add(literalExpression);
+ }
+
+ public List<LiteralExpression> getLiteralExpressionList() {
+ return this.literalExpressionList;
+ }
+
+ @Override
+ public int compareTo(InListColumnKeyValuePair o) {
+ return rowKeyColumnExpression.getPosition() -
o.getRowKeyColumnExpression().getPosition();
+ }
+ }
}
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java
b/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java
index d8a0de9..1aaa3f9 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java
@@ -54,6 +54,7 @@ import org.apache.phoenix.util.StringUtil;
public abstract class ExplainTable {
private static final List<KeyRange> EVERYTHING =
Collections.singletonList(KeyRange.EVERYTHING_RANGE);
+ public static final String POINT_LOOKUP_ON_STRING = "POINT LOOKUP ON ";
protected final StatementContext context;
protected final TableRef tableRef;
protected final GroupBy groupBy;
@@ -81,7 +82,7 @@ public abstract class ExplainTable {
ScanRanges scanRanges = context.getScanRanges();
if (scanRanges.isPointLookup()) {
int keyCount = scanRanges.getPointLookupCount();
- buf.append("POINT LOOKUP ON " + keyCount + " KEY" + (keyCount > 1
? "S " : " "));
+ buf.append(POINT_LOOKUP_ON_STRING + keyCount + " KEY" + (keyCount
> 1 ? "S " : " "));
} else if (scanRanges.useSkipScanFilter()) {
buf.append("SKIP SCAN ");
int count = 1;
diff --git
a/phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
index 3d765ab..2cbc02b 100644
---
a/phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
+++
b/phoenix-core/src/test/java/org/apache/phoenix/expression/InListExpressionTest.java
@@ -19,13 +19,22 @@ package org.apache.phoenix.expression;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;
+import static org.mockito.Mockito.when;
import com.google.common.collect.Lists;
+
+import java.nio.ByteBuffer;
import java.util.ArrayList;
import java.util.List;
+
+
+import org.apache.phoenix.schema.types.PInteger;
+import org.junit.Test;
+import org.mockito.Mockito;
+
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.phoenix.hbase.index.util.ImmutableBytesPtr;
-import org.junit.Test;
+
public class InListExpressionTest {
@@ -62,4 +71,200 @@ public class InListExpressionTest {
+ hashCode, firstHashCode, hashCode);
}
}
+
+ @Test
+ public void testGetSortedInListColumnKeyValuePairWithNoPkOrder() {
+ testGetSortedInListColumnKeyValuePair(false);
+ }
+
+ @Test
+ public void testGetSortedInListColumnKeyValuePairWithPkOrder() {
+ testGetSortedInListColumnKeyValuePair(true);
+ }
+
+ private void testGetSortedInListColumnKeyValuePair(boolean isPkOrder) {
+ // mock literal
+ List<Expression> expressionList = new ArrayList<>();
+ LiteralExpression literalChild1 =
Mockito.mock(LiteralExpression.class);
+ List<Expression> literalExpressions = new ArrayList<>();
+ when(literalChild1.getDataType()).thenReturn(PInteger.INSTANCE);
+ when(literalChild1.getBytes()).thenReturn(null);
+ when(literalChild1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+ literalExpressions.add(literalChild1);
+ literalExpressions.add(literalChild1);
+
+ // mock row key column
+ List<Expression> expressionChildren = new ArrayList<>();
+ RowKeyColumnExpression rowKeyColumnExpressionMock1 =
Mockito.mock(RowKeyColumnExpression.class);
+ RowKeyColumnExpression rowKeyColumnExpressionMock2 =
Mockito.mock(RowKeyColumnExpression.class);
+
+ when(rowKeyColumnExpressionMock1.getPosition()).thenReturn(1);
+
when(rowKeyColumnExpressionMock1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+ when(rowKeyColumnExpressionMock2.getPosition()).thenReturn(2);
+
when(rowKeyColumnExpressionMock2.getDeterminism()).thenReturn(Determinism.ALWAYS);
+
when(rowKeyColumnExpressionMock1.getChildren()).thenReturn(expressionChildren);
+
when(rowKeyColumnExpressionMock2.getChildren()).thenReturn(literalExpressions);
+
+ // mock row key column PK order position
+ if (isPkOrder) {
+ expressionChildren.add(rowKeyColumnExpressionMock1);
+ expressionChildren.add(rowKeyColumnExpressionMock2);
+
+ } else {
+ expressionChildren.add(rowKeyColumnExpressionMock2);
+ expressionChildren.add(rowKeyColumnExpressionMock1);
+
+ }
+
+ RowValueConstructorExpression rvc1 = new
RowValueConstructorExpression(expressionChildren, true);
+ RowValueConstructorExpression rvc2 = new
RowValueConstructorExpression(literalExpressions, true);
+ expressionList.add(rvc1);
+ expressionList.add(rvc2);
+
+ if (isPkOrder) {
+ assertEquals(1,
((RowKeyColumnExpression)expressionList.get(0).getChildren().get(0)).getPosition());
+ assertEquals(2,
((RowKeyColumnExpression)expressionList.get(0).getChildren().get(1)).getPosition());
+ } else {
+ assertEquals(2,
((RowKeyColumnExpression)expressionList.get(0).getChildren().get(0)).getPosition());
+ assertEquals(1,
((RowKeyColumnExpression)expressionList.get(0).getChildren().get(1)).getPosition());
+ }
+
+ List<InListExpression.InListColumnKeyValuePair>
inListColumnKeyValuePairList =
+
InListExpression.getSortedInListColumnKeyValuePair(expressionList);
+
+ assertEquals(1,
inListColumnKeyValuePairList.get(0).getRowKeyColumnExpression().getPosition());
+ assertEquals(2,
inListColumnKeyValuePairList.get(1).getRowKeyColumnExpression().getPosition());
+ }
+
+ @Test
+ public void
testGetSortedInListColumnKeyValuePairWithLessValueThanPkColumns() {
+ List<Expression> expressionList = new ArrayList<>();
+ LiteralExpression literalChild1 =
Mockito.mock(LiteralExpression.class);
+ List<Expression> literalExpressions = new ArrayList<>();
+ when(literalChild1.getDataType()).thenReturn(PInteger.INSTANCE);
+ when(literalChild1.getBytes()).thenReturn(null);
+ when(literalChild1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+ literalExpressions.add(literalChild1);
+ literalExpressions.add(literalChild1);
+
+ // mock row key column
+ List<Expression> expressionChildren = new ArrayList<>();
+ RowKeyColumnExpression rowKeyColumnExpressionMock1 =
Mockito.mock(RowKeyColumnExpression.class);
+
+ when(rowKeyColumnExpressionMock1.getPosition()).thenReturn(1);
+
when(rowKeyColumnExpressionMock1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+
when(rowKeyColumnExpressionMock1.getChildren()).thenReturn(expressionChildren);
+
+ expressionChildren.add(rowKeyColumnExpressionMock1);
+
+ RowValueConstructorExpression rvc1 = new
RowValueConstructorExpression(expressionChildren, true);
+ RowValueConstructorExpression rvc2 = new
RowValueConstructorExpression(literalExpressions, true);
+ expressionList.add(rvc1);
+ expressionList.add(rvc2);
+
+ List<InListExpression.InListColumnKeyValuePair>
inListColumnKeyValuePairList =
+
InListExpression.getSortedInListColumnKeyValuePair(expressionList);
+
+ assertEquals(null, inListColumnKeyValuePairList);
+ }
+
+ @Test
+ public void
testGetSortedInListColumnKeyValuePairWithMoreValueThanPkColumn() {
+ List<Expression> expressionList = new ArrayList<>();
+ LiteralExpression literalChild1 =
Mockito.mock(LiteralExpression.class);
+ List<Expression> literalExpressions = new ArrayList<>();
+ when(literalChild1.getDataType()).thenReturn(PInteger.INSTANCE);
+ when(literalChild1.getBytes()).thenReturn(null);
+ when(literalChild1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+ literalExpressions.add(literalChild1);
+
+ // mock row key column
+ List<Expression> expressionChildren = new ArrayList<>();
+ RowKeyColumnExpression rowKeyColumnExpressionMock1 =
Mockito.mock(RowKeyColumnExpression.class);
+ when(rowKeyColumnExpressionMock1.getPosition()).thenReturn(1);
+
when(rowKeyColumnExpressionMock1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+
when(rowKeyColumnExpressionMock1.getChildren()).thenReturn(expressionChildren);
+
+ expressionChildren.add(rowKeyColumnExpressionMock1);
+ expressionChildren.add(rowKeyColumnExpressionMock1);
+
+ RowValueConstructorExpression rvc1 = new
RowValueConstructorExpression(expressionChildren, true);
+ RowValueConstructorExpression rvc2 = new
RowValueConstructorExpression(literalExpressions, true);
+ expressionList.add(rvc1);
+ expressionList.add(rvc2);
+
+ List<InListExpression.InListColumnKeyValuePair>
inListColumnKeyValuePairList =
+
InListExpression.getSortedInListColumnKeyValuePair(expressionList);
+
+ assertEquals(null, inListColumnKeyValuePairList);
+ }
+
+ @Test
+ public void testInListColumnKeyValuePairClass() {
+ RowKeyColumnExpression rowKeyColumnExpression =
Mockito.mock(RowKeyColumnExpression.class);
+ LiteralExpression literalChild = Mockito.mock(LiteralExpression.class);
+
+ InListExpression.InListColumnKeyValuePair inListColumnKeyValuePair =
+ new
InListExpression.InListColumnKeyValuePair(rowKeyColumnExpression);
+ inListColumnKeyValuePair.addToLiteralExpressionList(literalChild);
+
+ assertEquals(rowKeyColumnExpression,
inListColumnKeyValuePair.getRowKeyColumnExpression());
+ assertEquals(literalChild,
inListColumnKeyValuePair.getLiteralExpressionList().get(0));
+ }
+
+ @Test
+ public void testGetSortedRowValueConstructorExpressionList() {
+ byte[] bytesValueOne = ByteBuffer.allocate(4).putInt(1).array();
+ byte[] bytesValueTwo = ByteBuffer.allocate(4).putInt(1).array();
+ // mock literal
+ List<Expression> literalExpressions = new ArrayList<>();
+ LiteralExpression literalChild1 =
Mockito.mock(LiteralExpression.class);
+ when(literalChild1.getDataType()).thenReturn(PInteger.INSTANCE);
+ when(literalChild1.getBytes()).thenReturn(bytesValueOne);
+ when(literalChild1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+ literalExpressions.add(literalChild1);
+
+ LiteralExpression literalChild2 =
Mockito.mock(LiteralExpression.class);
+ when(literalChild2.getDataType()).thenReturn(PInteger.INSTANCE);
+ when(literalChild2.getBytes()).thenReturn(bytesValueTwo);
+ when(literalChild2.getDeterminism()).thenReturn(Determinism.ALWAYS);
+ literalExpressions.add(literalChild2);
+
+ List<Expression> expressionChildren = new ArrayList<>();
+ RowKeyColumnExpression rowKeyColumnExpressionMock1 =
Mockito.mock(RowKeyColumnExpression.class);
+ RowKeyColumnExpression rowKeyColumnExpressionMock2 =
Mockito.mock(RowKeyColumnExpression.class);
+ expressionChildren.add(rowKeyColumnExpressionMock1);
+ expressionChildren.add(rowKeyColumnExpressionMock2);
+
+ when(rowKeyColumnExpressionMock1.getPosition()).thenReturn(1);
+
when(rowKeyColumnExpressionMock1.getDeterminism()).thenReturn(Determinism.ALWAYS);
+ when(rowKeyColumnExpressionMock2.getPosition()).thenReturn(2);
+
when(rowKeyColumnExpressionMock2.getDeterminism()).thenReturn(Determinism.ALWAYS);
+
when(rowKeyColumnExpressionMock1.getChildren()).thenReturn(expressionChildren);
+
when(rowKeyColumnExpressionMock2.getChildren()).thenReturn(literalExpressions);
+
+ //construct sorted InListColumnKeyValuePair list
+ List<InListExpression.InListColumnKeyValuePair> children = new
ArrayList<>();
+ InListExpression.InListColumnKeyValuePair rvc1 =
+ new
InListExpression.InListColumnKeyValuePair(rowKeyColumnExpressionMock1);
+ rvc1.addToLiteralExpressionList(literalChild1);
+ children.add(rvc1);
+ InListExpression.InListColumnKeyValuePair rvc2 =
+ new
InListExpression.InListColumnKeyValuePair(rowKeyColumnExpressionMock2);
+ rvc2.addToLiteralExpressionList(literalChild2);
+ children.add(rvc2);
+
+ List<Expression> result =
InListExpression.getSortedRowValueConstructorExpressionList(
+ children,true, 1);
+
+ assertTrue(result.get(0).getChildren().get(0) instanceof
RowKeyColumnExpression);
+ assertTrue(result.get(0).getChildren().get(1) instanceof
RowKeyColumnExpression);
+ assertEquals(1,
((RowKeyColumnExpression)result.get(0).getChildren().get(0)).getPosition());
+ assertEquals(2,
((RowKeyColumnExpression)result.get(0).getChildren().get(1)).getPosition());
+
+ assertTrue(result.get(1).getChildren().get(0) instanceof
LiteralExpression);
+ assertTrue(result.get(1).getChildren().get(1) instanceof
LiteralExpression);
+ assertEquals(bytesValueOne,
((LiteralExpression)result.get(1).getChildren().get(0)).getBytes());
+ assertEquals(bytesValueTwo,
((LiteralExpression)result.get(1).getChildren().get(1)).getBytes());
+ }
}
