This is an automated email from the ASF dual-hosted git repository.
sanjeet pushed a commit to branch 5.2
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/5.2 by this push:
new ec30ea2921 PHOENIX-7659: Leverage = ANY() instead of big IN list to do
huge number of point lookups in a single query (#2254)
ec30ea2921 is described below
commit ec30ea292146f131a2ada5edc173f17403a82ff9
Author: sanjeet006py <[email protected]>
AuthorDate: Fri Aug 1 17:14:54 2025 +0530
PHOENIX-7659: Leverage = ANY() instead of big IN list to do huge number of
point lookups in a single query (#2254)
---
.../org/apache/phoenix/compile/WhereOptimizer.java | 156 +++-
.../end2end/WhereOptimizerForArrayAnyIT.java | 796 +++++++++++++++++++++
2 files changed, 951 insertions(+), 1 deletion(-)
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
index 7da2c62c41..1ae9d4c5f0 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
@@ -55,6 +55,8 @@ import org.apache.phoenix.expression.LiteralExpression;
import org.apache.phoenix.expression.OrExpression;
import org.apache.phoenix.expression.RowKeyColumnExpression;
import org.apache.phoenix.expression.RowValueConstructorExpression;
+import org.apache.phoenix.expression.function.ArrayAnyComparisonExpression;
+import org.apache.phoenix.expression.function.ArrayElemRefExpression;
import
org.apache.phoenix.expression.function.FunctionExpression.OrderPreserving;
import org.apache.phoenix.expression.function.ScalarFunction;
import org.apache.phoenix.expression.visitor.ExpressionVisitor;
@@ -75,10 +77,12 @@ import org.apache.phoenix.schema.types.PChar;
import org.apache.phoenix.schema.types.PDataType;
import org.apache.phoenix.schema.types.PVarbinary;
import org.apache.phoenix.schema.types.PVarchar;
+import org.apache.phoenix.schema.types.PhoenixArray;
import org.apache.phoenix.util.ByteUtil;
import org.apache.phoenix.util.ScanUtil;
import org.apache.phoenix.util.SchemaUtil;
-
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
import org.apache.phoenix.thirdparty.com.google.common.base.Optional;
import org.apache.phoenix.thirdparty.com.google.common.collect.Iterators;
import org.apache.phoenix.thirdparty.com.google.common.collect.Lists;
@@ -97,6 +101,7 @@ import edu.umd.cs.findbugs.annotations.NonNull;
* @since 0.1
*/
public class WhereOptimizer {
+ private static final Logger LOGGER =
LoggerFactory.getLogger(WhereOptimizer.class);
private static final List<KeyRange> EVERYTHING_RANGES =
Collections.<KeyRange> singletonList(KeyRange.EVERYTHING_RANGE);
private static final List<KeyRange> SALT_PLACEHOLDER =
@@ -1749,6 +1754,155 @@ public class WhereOptimizer {
return newKeyParts(childSlot, node, new
ArrayList<KeyRange>(ranges));
}
+ /**
+ * If {@link ArrayAnyComparisonExpression} is of the form:
+ *
+ * <pre>
+ * COL = ANY(ARR)
+ * </pre>
+ *
+ * then we can extract the scan ranges for the COL, given COL is a PK
column. This
+ * syntactical pattern can be used as a replacement for a IN
expression. So, instead of
+ * following IN expression:
+ *
+ * <pre>
+ * COL IN (VAL1, VAL2, ... VALN)
+ * </pre>
+ *
+ * we can use the following ANY expression:
+ *
+ * <pre>
+ * try (Connection conn = DriverManager.getConnection(url)) {
+ * conn.createArrayOf("CHAR", new String[] {"VAL1", "VAL2", ...
"VALN"});
+ * try (PreparedStatement stmt = conn.prepareStatement(
+ * "SELECT ... FROM TABLE WHERE COL = ANY(?)")) {
+ * stmt.setArray(1, arr);
+ * ResultSet rs = stmt.executeQuery();
+ * }
+ * }
+ * </pre>
+ *
+ * This will help in saving the query parsing time as on using IN list
query parsing time
+ * increases with the size of IN list but in case of ANY expression it
is constant. Below we
+ * account for cases where COL is on the LHS or RHS of the comparison
expression.
+ * @param node {@link ArrayAnyComparisonExpression} node for
which scan ranges are
+ * to be extracted
+ * @param keyExpressions {@link RowKeyColumnExpression} for the PK
column for which scan
+ * ranges are to be extracted
+ * @return true if the scan ranges can be extracted, false otherwise
+ */
+ private boolean
shouldExtractKeyRangesForArrayAnyExpr(ArrayAnyComparisonExpression node,
+ List<Expression> keyExpressions) {
+ // {@link ArrayAnyComparisonExpression} has two children, and the
second child is
+ // comparison expression
+ Expression childExpr = node.getChildren().get(1);
+ if (!(childExpr instanceof ComparisonExpression)) {
+ return false;
+ }
+ ComparisonExpression comparisonExpr = (ComparisonExpression)
childExpr;
+
+ // Replacing IN() with =ANY() is only valid if the comparison
operator is EQUAL
+ if (comparisonExpr.getFilterOp() != CompareOperator.EQUAL) {
+ return false;
+ }
+
+ // {@link ComparisonExpression} will have two children in this
case, we need to make
+ // sure that one of them is a {@link RowKeyColumnExpression} and
the other is a {@link
+ // ArrayElemRefExpression}. Further, the first child of {@link
ArrayElemRefExpression}
+ // must be a {@link LiteralExpression}. The first child of {@link
+ // ArrayElemRefExpression} is same as the first child of {@link
+ // ArrayAnyComparisonExpression}.
+ Expression lhs = comparisonExpr.getChildren().get(0);
+ Expression rhs = comparisonExpr.getChildren().get(1);
+ if (lhs instanceof RowKeyColumnExpression && rhs instanceof
ArrayElemRefExpression) {
+ ArrayElemRefExpression arrayElemRefExpr =
(ArrayElemRefExpression) rhs;
+ if (!(arrayElemRefExpr.getChildren().get(0) instanceof
LiteralExpression)) {
+ return false;
+ }
+ // Capture {@link RowKeyColumnExpression} for the generation
of key slots.
+ keyExpressions.add(lhs);
+
+ } else if (
+ lhs instanceof ArrayElemRefExpression && rhs instanceof
RowKeyColumnExpression
+ ) {
+ ArrayElemRefExpression arrayElemRefExpr =
(ArrayElemRefExpression) lhs;
+ if (!(arrayElemRefExpr.getChildren().get(0) instanceof
LiteralExpression)) {
+ return false;
+ }
+ // Capture {@link RowKeyColumnExpression} for the generation
of key slots.
+ keyExpressions.add(rhs);
+ } else {
+ return false;
+ }
+ return true;
+ }
+
+ @Override
+ public Iterator<Expression> visitEnter(ArrayAnyComparisonExpression
node) {
+ ArrayList<Expression> keyExpressions = new ArrayList<>();
+ if (shouldExtractKeyRangesForArrayAnyExpr(node, keyExpressions)) {
+ return keyExpressions.iterator();
+ }
+ // If the scan ranges cannot be extracted, we return an empty
iterator
+ return Collections.emptyIterator();
+ }
+
+ @Override
+ public KeySlots visitLeave(ArrayAnyComparisonExpression node,
List<KeySlots> childParts) {
+ if (childParts == null || childParts.isEmpty()) {
+ return null;
+ }
+ // Doing type casting is safe here as we won't have reached here
unless the expression
+ // tree is of the form expected by the method
shouldExtractKeyRangesForArrayAnyExpr.
+ Expression arrayExpr = node.getChildren().get(0);
+ PhoenixArray arr = (PhoenixArray) ((LiteralExpression)
arrayExpr).getValue();
+ int numElements = arr.getDimensions();
+
+ ComparisonExpression comparisonExpr = (ComparisonExpression)
node.getChildren().get(1);
+ Expression lhsExpr = comparisonExpr.getChildren().get(0);
+ Expression rhsExpr = comparisonExpr.getChildren().get(1);
+ ArrayElemRefExpression arrayElemRefExpr;
+ if (lhsExpr instanceof ArrayElemRefExpression) {
+ arrayElemRefExpr = (ArrayElemRefExpression) lhsExpr;
+ } else {
+ arrayElemRefExpr = (ArrayElemRefExpression) rhsExpr;
+ }
+
+ KeySlots childSlots = childParts.get(0);
+ KeySlot childSlot = childSlots.getSlots().get(0);
+ KeyPart childPart = childSlot.getKeyPart();
+ PColumn column = childPart.getColumn();
+
+ List<KeyRange> keyRanges = new ArrayList<>();
+ try {
+ Expression coerceExpr =
CoerceExpression.create(arrayElemRefExpr,
+ column.getDataType(), column.getSortOrder(),
column.getMaxLength());
+ for (int i = 1; i <= numElements; i++) {
+ arrayElemRefExpr.setIndex(i);
+ KeyRange keyRange =
childPart.getKeyRange(CompareOperator.EQUAL, coerceExpr);
+ if (
+ keyRange == null || keyRange == KeyRange.EMPTY_RANGE
+ || keyRange == KeyRange.IS_NULL_RANGE
+ ) {
+ // Skip null range along with empty range as null
check is done via IS NULL
+ // as
+ // per SQL standards
+ continue;
+ }
+ keyRanges.add(keyRange);
+ }
+ } catch (Exception e) {
+ LOGGER.warn(
+ "Failed to wrap ArrayElemRefExpression with
CoerceExpression for column: {} and type: {}",
+ column.getName().getString(),
column.getDataType().getSqlTypeName(), e);
+ return super.visitLeave(node, childParts);
+ }
+ if (keyRanges.isEmpty()) {
+ return super.visitLeave(node, childParts);
+ }
+ return newKeyParts(childSlot, node, keyRanges);
+ }
+
@Override
public Iterator<Expression> visitEnter(IsNullExpression node) {
return Iterators.singletonIterator(node.getChildren().get(0));
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyIT.java
new file mode 100644
index 0000000000..00cc8a8b1c
--- /dev/null
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyIT.java
@@ -0,0 +1,796 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Array;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.sql.Types;
+import java.util.HashMap;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.phoenix.compile.ExplainPlan;
+import org.apache.phoenix.compile.ExplainPlanAttributes;
+import org.apache.phoenix.compile.QueryPlan;
+import org.apache.phoenix.jdbc.PhoenixPreparedStatement;
+import org.apache.phoenix.jdbc.PhoenixStatement;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.util.DateUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+@Category(NeedsOwnMiniClusterTest.class)
+public class WhereOptimizerForArrayAnyIT extends BaseTest {
+ @BeforeClass
+ public static void setup() throws Exception {
+ setUpTestDriver(new ReadOnlyProps(new HashMap<String, String>()));
+ }
+
+ @Test
+ public void testArrayAnyComparisonForNonPkColumn() throws Exception {
+ String tableName = generateUniqueName();
+ createTableASCPkColumns(tableName);
+ insertData(tableName, 1, "x", "a");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE col1 = ANY(?)";
+ Array arr = conn.createArrayOf("VARCHAR", new String[] { "a", "b" });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("x", rs.getString(2));
+ assertEquals("a", rs.getString(3));
+ }
+ assertPointLookupsAreNotGenerated(stmt);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithInequalityOperator() throws Exception {
+ String tableName = generateUniqueName();
+ createTableASCPkColumns(tableName);
+ insertData(tableName, 2, "x", "a");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 > ANY(?)";
+ Array arr = conn.createArrayOf("INTEGER", new Integer[] { 1, 2, 3 });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("x", rs.getString(2));
+ assertEquals("a", rs.getString(3));
+ }
+ assertPointLookupsAreNotGenerated(stmt);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparsionWithBindVariable() throws Exception {
+ String tableName = generateUniqueName();
+ createTableASCPkColumns(tableName);
+ insertData(tableName, 1, "x", "a");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?)
AND pk2 = 'x'";
+ Array arr = conn.createArrayOf("INTEGER", new Integer[] { 1, 2 });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("x", rs.getString(2));
+ assertEquals("a", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 2);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithLiteralArray() throws Exception {
+ String tableName = generateUniqueName();
+ createTableASCPkColumns(tableName);
+ insertData(tableName, 1, "x", "a");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql =
+ "SELECT * FROM " + tableName + " WHERE pk1 = 1 AND pk2 =
ANY(ARRAY['x', 'y'])";
+ try (Statement stmt = conn.createStatement()) {
+ try (ResultSet rs = stmt.executeQuery(selectSql)) {
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("x", rs.getString(2));
+ assertEquals("a", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, selectSql, 2);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithDoubleToFloatConversion() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl =
+ "CREATE TABLE " + tableName + " (" + "pk1 FLOAT NOT NULL, " + "pk2
VARCHAR(3) NOT NULL, "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1 DESC, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ stmt.execute("UPSERT INTO " + tableName + " VALUES (2.2, 'y', 'b')");
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?)
AND pk2 = 'y'";
+ Array arr = conn.createArrayOf("DOUBLE", new Double[] { 4.4d, 2.2d, 0d
});
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("y", rs.getString(2));
+ assertEquals("b", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithLongToIntegerConversion() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl =
+ "CREATE TABLE " + tableName + " (" + "pk1 INTEGER NOT NULL, " + "pk2
VARCHAR(3) NOT NULL, "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1 DESC, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ insertData(tableName, 2, "y", "b");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?)
AND pk2 = 'y'";
+ Array arr = conn.createArrayOf("BIGINT", new Long[] { 4L, 2L, 0L });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("y", rs.getString(2));
+ assertEquals("b", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithNullInArray() throws Exception {
+ String tableName = generateUniqueName();
+ createTableASCPkColumns(tableName);
+ insertData(tableName, 1, null, "a");
+ insertData(tableName, 2, "y", "b");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = 1 AND
pk2 = ANY(?)";
+ Array arr = conn.createArrayOf("VARCHAR", new String[] { "y", "z", null
});
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertFalse(rs.next());
+ }
+ // 2 point lookups are generated instead of 3 as the null is not
considered as a value for
+ // VARCHAR type column
+ assertPointLookupsAreGenerated(stmt, 2);
+ }
+
+ selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = 2 AND pk2 =
ANY(?)";
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("y", rs.getString(2));
+ assertEquals("b", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 2);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithDescPKAndNullInArray() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 INTEGER NOT NULL, "
+ "pk2 VARCHAR(3), "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1, pk2 DESC)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ insertData(tableName, 1, null, "a");
+ insertData(tableName, 2, "y", "b");
+ insertData(tableName, 3, "z", null);
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = 1 AND
pk2 = ANY(?)";
+ Array arr = conn.createArrayOf("VARCHAR", new String[] { "y", "z", null
});
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertFalse(rs.next());
+ }
+ // 2 point lookups are generated instead of 3 as the null is not
considered as a value for
+ // VARCHAR type column
+ assertPointLookupsAreGenerated(stmt, 2);
+ }
+
+ selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = 2 AND pk2 =
ANY(?)";
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("y", rs.getString(2));
+ assertEquals("b", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 2);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonForDescCharPKWithPadding() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 CHAR(3) NOT NULL, "
+ "pk2 VARCHAR(3), "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1 DESC, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ String upsertStmt = "UPSERT INTO " + tableName + " VALUES (?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ stmt.setString(1, "a");
+ stmt.setString(2, "b");
+ stmt.setString(3, "c");
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?)
AND pk2 = 'b'";
+ Array arr = conn.createArrayOf("VARCHAR", new String[] { "a", "c", null
});
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals("a", rs.getString(1));
+ assertEquals("b", rs.getString(2));
+ assertEquals("c", rs.getString(3));
+ }
+ // 3 point lookups are generated though one of the array values is
null as CHAR type pads it
+ // and the value is a string consisting only of pad characters
+ assertPointLookupsAreGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithDecimalArray() throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 DECIMAL(10, 2) NOT
NULL, "
+ + "pk2 VARCHAR(3), " + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY
(pk1, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ String upsertStmt = "UPSERT INTO " + tableName + " VALUES (?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ stmt.setBigDecimal(1, new BigDecimal("1.23"));
+ stmt.setString(2, "x");
+ stmt.setString(3, "a");
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?)
AND pk2 = 'x'";
+ Array arr = conn.createArrayOf("DECIMAL", new BigDecimal[] { new
BigDecimal("1.230"),
+ new BigDecimal("2.340"), new BigDecimal("3.450") });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(new BigDecimal("1.23"), rs.getBigDecimal(1));
+ assertEquals("x", rs.getString(2));
+ assertEquals("a", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void
testArrayAnyComparisonWithDataTypeAndSortOrderCoercionForDecimalColumn()
+ throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 DECIMAL(10, 2) NOT
NULL, "
+ + "pk2 VARCHAR(3), " + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY
(pk1 DESC, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ String upsertStmt = "UPSERT INTO " + tableName + " VALUES (?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ stmt.setBigDecimal(1, new BigDecimal("1.23"));
+ stmt.setString(2, "x");
+ stmt.setString(3, "a");
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?)
AND pk2 = 'x'";
+ Array arr = conn.createArrayOf("DOUBLE", new Double[] { 1.230d, 2.340d,
3.450d });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(new BigDecimal("1.23"), rs.getBigDecimal(1));
+ assertEquals("x", rs.getString(2));
+ assertEquals("a", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonForDateTimeColumn() throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 TIMESTAMP NOT NULL,
"
+ + "pk2 TIME NOT NULL, " + "pk3 DATE NOT NULL, " + "col1 VARCHAR, "
+ + "CONSTRAINT pk PRIMARY KEY (pk1, pk2, pk3)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ String pk1Value = "2025-07-18 10:00:00";
+ String pk2Value = "2025-07-18 11:00:00";
+ String pk3Value = "2025-07-18 12:00:00";
+ String upsertStmt = "UPSERT INTO " + tableName + " VALUES (?, ?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ stmt.setTimestamp(1, DateUtil.parseTimestamp(pk1Value));
+ stmt.setTime(2, DateUtil.parseTime(pk2Value));
+ stmt.setDate(3, DateUtil.parseDate(pk3Value));
+ stmt.setString(4, "a");
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+
+ // Use arrays as bind variables to test the point lookup optimization.
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?) "
+ "AND pk2 = ANY(?) "
+ + "AND pk3 = ANY(?)";
+ Array timestampArr = conn.createArrayOf("TIMESTAMP",
+ new Timestamp[] { DateUtil.parseTimestamp(pk1Value),
+ DateUtil.parseTimestamp("2025-07-19 10:00:00"),
+ DateUtil.parseTimestamp("2025-07-17 10:00:00"), });
+ Array timeArr = conn.createArrayOf("TIME", new Time[] {
DateUtil.parseTime(pk2Value),
+ DateUtil.parseTime("2025-07-19 11:00:00"),
DateUtil.parseTime("2025-07-17 11:00:00"), });
+ Array dateArr = conn.createArrayOf("DATE", new Date[] {
DateUtil.parseDate(pk3Value),
+ DateUtil.parseDate("2025-07-19 12:00:00"),
DateUtil.parseDate("2025-07-17 12:00:00"), });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, timestampArr);
+ stmt.setArray(2, timeArr);
+ stmt.setArray(3, dateArr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(DateUtil.parseTimestamp(pk1Value), rs.getTimestamp(1));
+ assertEquals(DateUtil.parseTime(pk2Value), rs.getTime(2));
+ assertEquals(DateUtil.parseDate(pk3Value), rs.getDate(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 3 * 3 * 3);
+ }
+
+ // Use literal arrays to test the point lookup optimization.
+ String timestampLiteralArr = "ARRAY[" + "TO_TIMESTAMP('" + pk1Value +
"'), "
+ + "TO_TIMESTAMP('" + pk2Value + "'), " + "TO_TIMESTAMP('" + pk3Value +
"')]";
+ String timeLiteralArr = "ARRAY[" + "TO_TIME('" + pk1Value + "'), " +
"TO_TIME('" + pk2Value
+ + "'), " + "TO_TIME('" + pk3Value + "')]";
+ String dateLiteralArr = "ARRAY[" + "TO_DATE('" + pk1Value + "'), " +
"TO_DATE('" + pk2Value
+ + "'), " + "TO_DATE('" + pk3Value + "')]";
+ selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(" +
timestampLiteralArr + ") "
+ + "AND pk2 = ANY(" + timeLiteralArr + ")" + "AND pk3 = ANY(" +
dateLiteralArr + ")";
+ try (Statement stmt = conn.createStatement()) {
+ try (ResultSet rs = stmt.executeQuery(selectSql)) {
+ assertTrue(rs.next());
+ assertEquals(DateUtil.parseTimestamp(pk1Value), rs.getTimestamp(1));
+ assertEquals(DateUtil.parseTime(pk2Value), rs.getTime(2));
+ assertEquals(DateUtil.parseDate(pk3Value), rs.getDate(3));
+ }
+ assertPointLookupsAreGenerated(stmt, selectSql, 3 * 3 * 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithTimestampToDateCoercion() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl =
+ "CREATE TABLE " + tableName + " (" + "pk1 TIMESTAMP NOT NULL, " + "pk2
DATE NOT NULL, "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1, pk2 DESC)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ String pk1Value = "2025-07-18 10:00:00";
+ String pk2Value = "2025-07-18 11:00:00";
+ String pk3Value = "2025-07-18 12:00:00";
+ String upsertStmt = "UPSERT INTO " + tableName + " VALUES (?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ stmt.setTimestamp(1, DateUtil.parseTimestamp(pk1Value));
+ stmt.setDate(2, DateUtil.parseDate(pk2Value));
+ stmt.setString(3, "a");
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ // Use literal arrays to test the point lookup optimization.
+ String timestampLiteralArr = "ARRAY[" + "TO_TIMESTAMP('" + pk1Value +
"'), "
+ + "TO_TIMESTAMP('" + pk2Value + "'), " + "TO_TIMESTAMP('" + pk3Value +
"')]";
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(" +
timestampLiteralArr
+ + ") " + "AND pk2 = ANY(" + timestampLiteralArr + ")";
+ try (Statement stmt = conn.createStatement()) {
+ try (ResultSet rs = stmt.executeQuery(selectSql)) {
+ assertTrue(rs.next());
+ assertEquals(DateUtil.parseTimestamp(pk1Value), rs.getTimestamp(1));
+ assertEquals(DateUtil.parseDate(pk2Value), rs.getDate(2));
+ assertEquals("a", rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, selectSql, 3 * 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonForBinaryColumn() throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 BINARY(3) NOT NULL,
"
+ + "pk2 VARBINARY(3) NOT NULL, " + "col1 VARCHAR, "
+ + "CONSTRAINT pk PRIMARY KEY (pk1, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ String pk1Value = "a";
+ String pk2Value = "b";
+ String col1Value = "d";
+ String upsertStmt = "UPSERT INTO " + tableName + " VALUES (?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ stmt.setBytes(1, pk1Value.getBytes());
+ stmt.setBytes(2, pk2Value.getBytes());
+ stmt.setString(3, col1Value);
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ TestUtil.dumpTable(conn, TableName.valueOf(tableName));
+ String selectSql =
+ "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?) AND pk2 = ANY(?)";
+ byte[][] nativeByteArr =
+ new byte[][] { pk1Value.getBytes(), pk2Value.getBytes() };
+ Array binaryArr = conn.createArrayOf("BINARY", nativeByteArr);
+ Array varbinaryArr = conn.createArrayOf("VARBINARY", nativeByteArr);
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, binaryArr);
+ stmt.setArray(2, varbinaryArr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertBinaryValue(pk1Value.getBytes(), rs.getBytes(1));
+ assertBinaryValue(pk2Value.getBytes(), rs.getBytes(2));
+ assertEquals(col1Value, rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 2 * 2);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonForBinaryColumnWithCoercion() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 BINARY(3) NOT NULL,
"
+ + "pk2 VARBINARY(3) NOT NULL, " + "col1 VARCHAR, "
+ + "CONSTRAINT pk PRIMARY KEY (pk1 DESC, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ String pk1Value = "a";
+ String pk2Value = "b";
+ String col1Value = "d";
+ String upsertStmt = "UPSERT INTO " + tableName + " VALUES (?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ stmt.setBytes(1, pk1Value.getBytes());
+ stmt.setBytes(2, pk2Value.getBytes());
+ stmt.setString(3, col1Value);
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ TestUtil.dumpTable(conn, TableName.valueOf(tableName));
+ String selectSql =
+ "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?) AND pk2 = ANY(?)";
+ byte[][] nativeByteArr =
+ new byte[][] { pk1Value.getBytes(), pk2Value.getBytes() };
+ Array binaryArr = conn.createArrayOf("BINARY", nativeByteArr);
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, binaryArr);
+ stmt.setArray(2, binaryArr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertBinaryValue(pk1Value.getBytes(), rs.getBytes(1));
+ assertBinaryValue(pk2Value.getBytes(), rs.getBytes(2));
+ assertEquals(col1Value, rs.getString(3));
+ }
+ assertPointLookupsAreGenerated(stmt, 2 * 2);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonInGroupedAggregateQuery() throws Exception
{
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 INTEGER NOT NULL, "
+ + "pk2 VARCHAR NOT NULL, " + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY
KEY (pk1, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ insertData(tableName, 1, "a11", "b11");
+ insertData(tableName, 1, "a12", "b12");
+ insertData(tableName, 2, "a21", "b21");
+ insertData(tableName, 2, "a22", "b22");
+ insertData(tableName, 2, "a23", "b23");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql =
+ "SELECT pk1, COUNT(*) FROM " + tableName + " WHERE pk1 = 2 AND pk2 =
ANY(?) GROUP BY pk1";
+ Array arr = conn.createArrayOf("VARCHAR", new String[] { "a11", "a21",
"a23" });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals(2, rs.getInt(2));
+ }
+ assertPointLookupsAreGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithIndexPKColumn() throws Exception {
+ String tableName = generateUniqueName();
+ String createTableDdl = "CREATE TABLE " + tableName + " (" + "pk1 INTEGER
NOT NULL, "
+ + "pk2 VARCHAR(3), " + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY
(pk1, pk2)" + ")";
+ String createIndexDdl = "CREATE INDEX idx_pk1 ON " + tableName + " (col1)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(createTableDdl);
+ conn.commit();
+ stmt.execute(createIndexDdl);
+ conn.commit();
+ }
+ }
+ insertData(tableName, 1, "a", "b");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE col1 = ANY(?)";
+ Array arr = conn.createArrayOf("VARCHAR", new String[] { "a", "b", "c"
});
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("a", rs.getString(2));
+ assertEquals("b", rs.getString(3));
+ }
+ assertSkipScanIsGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void testArrayAnyComparisonWithRowKeyPrefix() throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 INTEGER NOT NULL, "
+ "pk2 VARCHAR(3), "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ insertData(tableName, 1, "a", "b");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT * FROM " + tableName + " WHERE pk1 = ANY(?)";
+ Array arr = conn.createArrayOf("INTEGER", new Integer[] { 1, 2, 3 });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("a", rs.getString(2));
+ assertEquals("b", rs.getString(3));
+ }
+ assertSkipScanIsGenerated(stmt, 3);
+ }
+ }
+ }
+
+ @Test
+ public void
testArrayAnyComparisonWhenRowKeyColumnExpressionIsNotTopLevelExpression()
+ throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 INTEGER NOT NULL, "
+ "pk2 VARCHAR(3), "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ insertData(tableName, 1, "a", "b");
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql =
+ "SELECT * FROM " + tableName + " WHERE CAST (pk1 as BIGINT) = ANY(?)
AND pk2 = 'a'";
+ Array arr = conn.createArrayOf("BIGINT", new Long[] { 1L, 2L, 3L });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setArray(1, arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("a", rs.getString(2));
+ assertEquals("b", rs.getString(3));
+ }
+ assertPointLookupsAreNotGenerated(stmt);
+ }
+ }
+ }
+
+ private void assertBinaryValue(byte[] expected, byte[] actual) {
+ int expectedLength = expected.length;
+ for (int i = 0; i < expectedLength; i++) {
+ assertEquals(expected[i], actual[i]);
+ }
+ }
+
+ private void assertPointLookupsAreNotGenerated(PreparedStatement stmt)
throws SQLException {
+ ExplainPlan explain =
+
stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery().getExplainPlan();
+ ExplainPlanAttributes planAttributes = explain.getPlanStepsAsAttributes();
+ assertEquals("FULL SCAN ", planAttributes.getExplainScanType());
+ }
+
+ private void assertPointLookupsAreGenerated(PreparedStatement stmt, int
noOfPointLookups)
+ throws SQLException {
+ QueryPlan queryPlan =
stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery();
+ assertPointLookupsAreGenerated(queryPlan, noOfPointLookups);
+ }
+
+ private void assertPointLookupsAreGenerated(Statement stmt, String selectSql,
+ int noOfPointLookups) throws SQLException {
+ QueryPlan queryPlan =
stmt.unwrap(PhoenixStatement.class).optimizeQuery(selectSql);
+ assertPointLookupsAreGenerated(queryPlan, noOfPointLookups);
+ }
+
+ private void assertSkipScanIsGenerated(PreparedStatement stmt, int
skipListSize)
+ throws SQLException {
+ QueryPlan queryPlan =
stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery();
+ ExplainPlan explain = queryPlan.getExplainPlan();
+ ExplainPlanAttributes planAttributes = explain.getPlanStepsAsAttributes();
+ String expectedScanType =
+ "SKIP SCAN ON " + skipListSize + " KEY" + (skipListSize > 1 ? "S " : "
");
+ assertEquals(expectedScanType, planAttributes.getExplainScanType());
+ }
+
+ private void assertPointLookupsAreGenerated(QueryPlan queryPlan, int
noOfPointLookups)
+ throws SQLException {
+ ExplainPlan explain = queryPlan.getExplainPlan();
+ ExplainPlanAttributes planAttributes = explain.getPlanStepsAsAttributes();
+ String expectedScanType =
+ "POINT LOOKUP ON " + noOfPointLookups + " KEY" + (noOfPointLookups > 1 ?
"S " : " ");
+ assertEquals(expectedScanType, planAttributes.getExplainScanType());
+ }
+
+ private void createTableASCPkColumns(String tableName) throws SQLException {
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk1 INTEGER NOT NULL, "
+ "pk2 VARCHAR(3), "
+ + "col1 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (pk1, pk2)" + ")";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+ }
+
+ private void insertData(String tableName, int pk1, String pk2, String col1)
throws SQLException {
+ String ddl = "UPSERT INTO " + tableName + " VALUES (?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(ddl)) {
+ stmt.setInt(1, pk1);
+ if (pk2 != null) {
+ stmt.setString(2, pk2);
+ } else {
+ stmt.setNull(2, Types.VARCHAR);
+ }
+ if (col1 != null) {
+ stmt.setString(3, col1);
+ } else {
+ stmt.setNull(3, Types.VARCHAR);
+ }
+ stmt.executeUpdate();
+ conn.commit();
+ }
+ }
+ }
+
+}
