This is an automated email from the ASF dual-hosted git repository.
sanjeet 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 6a0149a8ed PHOENIX-7766: Generate point lookups while using IS NULL on
trailing PK columns (#2377)
6a0149a8ed is described below
commit 6a0149a8edb79cf2cee8354bb081e0cbab1f9d64
Author: sanjeet006py <[email protected]>
AuthorDate: Thu Mar 12 00:26:48 2026 +0530
PHOENIX-7766: Generate point lookups while using IS NULL on trailing PK
columns (#2377)
---
.../apache/phoenix/compile/RVCOffsetCompiler.java | 23 +-
.../org/apache/phoenix/compile/ScanRanges.java | 35 +-
.../java/org/apache/phoenix/util/ScanUtil.java | 27 +-
.../end2end/WhereOptimizerForArrayAnyIT.java | 36 +-
.../end2end/WhereOptimizerForArrayAnyITBase.java | 83 ++
.../WhereOptimizerForArrayAnyNullablePKIT.java | 1160 ++++++++++++++++++++
.../apache/phoenix/compile/WhereOptimizerTest.java | 36 +-
7 files changed, 1338 insertions(+), 62 deletions(-)
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
index decd25d313..739f7f019a 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
@@ -37,6 +37,7 @@ import org.apache.phoenix.parse.OffsetNode;
import org.apache.phoenix.parse.ParseNode;
import org.apache.phoenix.parse.RowValueConstructorParseNode;
import org.apache.phoenix.query.KeyRange;
+import org.apache.phoenix.query.QueryConstants;
import org.apache.phoenix.schema.PColumn;
import org.apache.phoenix.schema.PTable;
import org.apache.phoenix.schema.PTableType;
@@ -247,17 +248,19 @@ public class RVCOffsetCompiler {
// check to see if this was a single key expression
ScanRanges scanRanges = context.getScanRanges();
- // We do not generate a point lookup today in phoenix if the rowkey has a
trailing null, we
- // generate a range scan.
+ // Always generate point lookup for RVC Offset
if (!scanRanges.isPointLookup()) {
- // Since we use a range scan to guarantee we get only the null value and
the upper bound is
- // unset this suffices
- // sanity check
- if (!rowKeyColumnExpressionOutput.isTrailingNull()) {
- throw new RowValueConstructorOffsetNotCoercibleException(
- "RVC Offset must be a point lookup.");
- }
- key = scanRanges.getScanRange().getUpperRange();
+ throw new RowValueConstructorOffsetNotCoercibleException(
+ "RVC Offset must be a point lookup.");
+ }
+ if (rowKeyColumnExpressionOutput.isTrailingNull()) {
+ // Handle trailing nulls in RVC offset by appending null byte at the end
to generate immediate
+ // next key
+ key = scanRanges.getScanRange().getLowerRange();
+ byte[] keyCopy = new byte[key.length + 1];
+ System.arraycopy(key, 0, keyCopy, 0, key.length);
+ keyCopy[key.length] = QueryConstants.SEPARATOR_BYTE;
+ key = keyCopy;
} else {
RowKeySchema.RowKeySchemaBuilder builder =
new RowKeySchema.RowKeySchemaBuilder(columns.size());
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ScanRanges.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ScanRanges.java
index fc2176d6b2..2649473012 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ScanRanges.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ScanRanges.java
@@ -101,6 +101,9 @@ public class ScanRanges {
TimeRange rowTimestampRange = getRowTimestampColumnRange(ranges, schema,
rowTimestampColIndex);
boolean isPointLookup = isPointLookup(schema, ranges, slotSpan,
useSkipScan);
if (isPointLookup) {
+ // Do this before transforming the ranges into list of key ranges with
single slot
+ // Once the list is transformed into singleton list, IS_NULL_RANGE is no
longer retained.
+ boolean isPointLookupWithTrailingNulls =
isPointLookupWithTrailingNulls(ranges);
// TODO: consider keeping original to use for serialization as it would
be smaller?
List<byte[]> keys = ScanRanges.getPointKeys(ranges, slotSpan, schema,
nBuckets);
List<KeyRange> keyRanges =
Lists.newArrayListWithExpectedSize(keys.size());
@@ -117,7 +120,9 @@ public class ScanRanges {
useSkipScan = keyRanges.size() > 1;
// Treat as binary if descending because we've got a separator byte at
the end
// which is not part of the value.
- if (keys.size() > 1 || hasTrailingDescSeparatorByte(schema)) {
+ if (
+ keys.size() > 1 || hasTrailingDescSeparatorByte(schema) ||
isPointLookupWithTrailingNulls
+ ) {
schema = SchemaUtil.VAR_BINARY_SCHEMA;
slotSpan = ScanUtil.SINGLE_COLUMN_SLOT_SPAN;
} else {
@@ -618,9 +623,16 @@ public class ScanRanges {
return false;
}
for (KeyRange keyRange : orRanges) {
- // Special case for single trailing IS NULL. We cannot consider this
as a point key because
- // we strip trailing nulls when we form the key.
- if (!keyRange.isSingleKey() || (i == lastIndex && keyRange ==
KeyRange.IS_NULL_RANGE)) {
+ // COL IS NULL on trailing nullable PK column can be treated as a
point lookup
+ // because:
+ // 1. Trailing nulls are stripped when storing the key (no trailing
separator bytes)
+ // 2. getPointKeys() generates the correct key without trailing null
bytes
+ // 3. The generated key matches exactly what's stored for rows with
trailing NULL
+ // 4. Not handling legcay tables impacted via bug PHOENIX-2067
+ if (
+ !keyRange.isSingleKey() || lastIndex == i && keyRange ==
KeyRange.IS_NULL_RANGE
+ && !schema.rowKeyOrderOptimizable()
+ ) {
return false;
}
}
@@ -628,6 +640,17 @@ public class ScanRanges {
return true;
}
+ private static boolean isPointLookupWithTrailingNulls(List<List<KeyRange>>
ranges) {
+ int lastIndex = ranges.size() - 1;
+ List<KeyRange> lastRange = ranges.get(lastIndex);
+ for (KeyRange keyRange : lastRange) {
+ if (keyRange == KeyRange.IS_NULL_RANGE) {
+ return true;
+ }
+ }
+ return false;
+ }
+
private static boolean incrementKey(List<List<KeyRange>> slots, int[]
position) {
int idx = slots.size() - 1;
while (idx >= 0 && (position[idx] = (position[idx] + 1) %
slots.get(idx).size()) == 0) {
@@ -656,6 +679,10 @@ public class ScanRanges {
do {
length = ScanUtil.setKey(schema, ranges, slotSpan, position,
Bound.LOWER, key, offset, offset,
ranges.size(), offset);
+ // Handle case someone specify IS NULL on all PK columns.
+ if (length == 0) {
+ continue;
+ }
if (isSalted) {
key[0] = SaltingUtil.getSaltingByte(key, offset, length, bucketNum);
}
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/util/ScanUtil.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/util/ScanUtil.java
index 1aaf672ca8..2e4816fe58 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/util/ScanUtil.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/util/ScanUtil.java
@@ -484,13 +484,26 @@ public class ScanUtil {
slotEndIndex, slotStartIndex);
}
+ private static boolean doesSlotsCoverAllColumnsWithoutMultiSpan(RowKeySchema
schema,
+ List<List<KeyRange>> slots, int[] slotSpan) {
+ long slotSpanSum = 0;
+ for (int i = 0; i < slotSpan.length; i++) {
+ slotSpanSum += slotSpan[i];
+ }
+ return slotSpanSum == 0 && slots.size() == schema.getMaxFields();
+ }
+
public static int setKey(RowKeySchema schema, List<List<KeyRange>> slots,
int[] slotSpan,
int[] position, Bound bound, byte[] key, int byteOffset, int
slotStartIndex, int slotEndIndex,
int schemaStartIndex) {
int offset = byteOffset;
boolean lastInclusiveUpperSingleKey = false;
+ boolean allInclusiveLowerSingleKey = bound == Bound.LOWER;
boolean anyInclusiveUpperRangeKey = false;
boolean lastUnboundUpper = false;
+ boolean slotsCoverAllColumnsWithoutMultiSpan =
+ doesSlotsCoverAllColumnsWithoutMultiSpan(schema, slots, slotSpan);
+ int trailingNullCount = 0;
// The index used for slots should be incremented by 1,
// but the index for the field it represents in the schema
// should be incremented by 1 + value in the current slotSpan index
@@ -521,6 +534,13 @@ public class ScanUtil {
System.arraycopy(bytes, 0, key, offset, bytes.length);
offset += bytes.length;
+ if (bytes.length == 0) {
+ trailingNullCount++;
+ } else {
+ trailingNullCount = 0;
+ }
+ allInclusiveLowerSingleKey &= range.isSingleKey();
+
/*
* We must add a terminator to a variable length key even for the last
PK column if the lower
* key is non inclusive or the upper key is inclusive. Otherwise, we'd
be incrementing the key
@@ -637,10 +657,15 @@ public class ScanUtil {
// after the table has data, in which case there won't be a separator
// byte.
if (bound == Bound.LOWER) {
+ // Remove trailing separator bytes for DESC keys only if they are
trailing nulls for point
+ // lookups and schema is rowKeyOrderOptimizable.
while (
--i >= schemaStartIndex && offset > byteOffset
&& !(field =
schema.getField(--fieldIndex)).getDataType().isFixedWidth()
- && field.getSortOrder() == SortOrder.ASC && hasSeparatorBytes(key,
field, offset)
+ && hasSeparatorBytes(key, field, offset)
+ && (field.getSortOrder() == SortOrder.DESC &&
schema.rowKeyOrderOptimizable()
+ && slotsCoverAllColumnsWithoutMultiSpan &&
allInclusiveLowerSingleKey
+ && trailingNullCount-- > 0 || field.getSortOrder() ==
SortOrder.ASC)
) {
if (field.getDataType() != PVarbinaryEncoded.INSTANCE) {
offset--;
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
index dd47aefc5b..c9651f2113 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyIT.java
@@ -33,29 +33,21 @@ 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.ByteUtil;
import org.apache.phoenix.util.DateUtil;
-import org.apache.phoenix.util.ReadOnlyProps;
import org.apache.phoenix.util.TestUtil;
import org.bson.RawBsonDocument;
-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>()));
- }
+public class WhereOptimizerForArrayAnyIT extends
WhereOptimizerForArrayAnyITBase {
@Test
public void testArrayAnyComparisonForNonPkColumn() throws Exception {
@@ -784,37 +776,12 @@ public class WhereOptimizerForArrayAnyIT extends BaseTest
{
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)" + ")";
@@ -887,5 +854,4 @@ public class WhereOptimizerForArrayAnyIT extends BaseTest {
+ " \"attr_0\" : \"str_val_0\"\n" + "}";
return RawBsonDocument.parse(json);
}
-
}
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyITBase.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyITBase.java
new file mode 100644
index 0000000000..b33362e48d
--- /dev/null
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyITBase.java
@@ -0,0 +1,83 @@
+/*
+ * 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.assertNull;
+
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.HashMap;
+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.query.BaseTest;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.BeforeClass;
+
+/**
+ * Base class for WhereOptimizerForArrayAnyIT tests containing shared helper
methods.
+ */
+public abstract class WhereOptimizerForArrayAnyITBase extends BaseTest {
+
+ @BeforeClass
+ public static void setup() throws Exception {
+ setUpTestDriver(new ReadOnlyProps(new HashMap<String, String>()));
+ }
+
+ protected void assertPointLookupsAreGenerated(PreparedStatement stmt, int
noOfPointLookups)
+ throws SQLException {
+ QueryPlan queryPlan =
stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery();
+ assertPointLookupsAreGenerated(queryPlan, noOfPointLookups);
+ }
+
+ protected 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());
+ }
+
+ protected 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());
+ }
+
+ protected void assertRangeScanIsGenerated(PreparedStatement stmt) throws
SQLException {
+ QueryPlan queryPlan =
stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery();
+ ExplainPlan explain = queryPlan.getExplainPlan();
+ ExplainPlanAttributes planAttributes = explain.getPlanStepsAsAttributes();
+ String expectedScanType = "RANGE SCAN ";
+ assertEquals(expectedScanType, planAttributes.getExplainScanType());
+ }
+
+ protected void assertDegenerateScanIsGenerated(PreparedStatement stmt)
throws SQLException {
+ QueryPlan queryPlan =
stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery();
+ ExplainPlan explain = queryPlan.getExplainPlan();
+ ExplainPlanAttributes planAttributes = explain.getPlanStepsAsAttributes();
+ assertNull(planAttributes.getExplainScanType());
+ }
+}
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyNullablePKIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyNullablePKIT.java
new file mode 100644
index 0000000000..c96a985cec
--- /dev/null
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/WhereOptimizerForArrayAnyNullablePKIT.java
@@ -0,0 +1,1160 @@
+/*
+ * 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.assertNotEquals;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Array;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.util.Arrays;
+import java.util.Collection;
+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.junit.Assume;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+/**
+ * Integration tests for generating point lookups with trailing nulls.
+ * <p>
+ * Parameterized at class level with the following parameters:
+ * <ul>
+ * <li>{@code salted} - whether the table uses salt buckets</li>
+ * <li>{@code columnConfig} - primary column configuration (fixed-width,
variable-width
+ * ASC/DESC)</li>
+ * <li>{@code secondarySortDesc} - sort order for secondary columns
(true=DESC, false=ASC)</li>
+ * </ul>
+ * This covers all combinations of column types and sort orders across all
tests.
+ */
+@Category(NeedsOwnMiniClusterTest.class)
+@RunWith(Parameterized.class)
+public class WhereOptimizerForArrayAnyNullablePKIT extends
WhereOptimizerForArrayAnyITBase {
+
+ private static final BigDecimal PK3_VAL = new BigDecimal("100.5");
+ private static final int SALT_BUCKETS = 3;
+
+ /**
+ * Configuration for column type and sort order. Used for primary
configurable columns in tests.
+ */
+ public enum ColumnConfig {
+ /** Fixed-width CHAR(1) NOT NULL */
+ FIXED_WIDTH("CHAR(1) NOT NULL", "", false),
+ /** Variable-width VARCHAR with ASC sort order (default) */
+ VARWIDTH_ASC("VARCHAR", "", false),
+ /** Variable-width VARCHAR with DESC sort order */
+ VARWIDTH_DESC("VARCHAR", " DESC", true);
+
+ private final String dataType;
+ private final String sortOrderClause;
+ private final boolean isDesc;
+
+ ColumnConfig(String dataType, String sortOrderClause, boolean isDesc) {
+ this.dataType = dataType;
+ this.sortOrderClause = sortOrderClause;
+ this.isDesc = isDesc;
+ }
+
+ public String getDataType() {
+ return dataType;
+ }
+
+ public String getSortOrderClause() {
+ return sortOrderClause;
+ }
+
+ public boolean isDesc() {
+ return isDesc;
+ }
+
+ public boolean isFixedWidth() {
+ return this == FIXED_WIDTH;
+ }
+
+ /**
+ * Returns the data type with NOT NULL constraint for PK columns that must
be NOT NULL.
+ */
+ public String getNotNullDataType() {
+ if (this == FIXED_WIDTH) {
+ return dataType; // Already includes NOT NULL
+ }
+ return dataType + " NOT NULL";
+ }
+
+ @Override
+ public String toString() {
+ return name();
+ }
+ }
+
+ @Parameters(name = "salted={0}, columnConfig={1}, secondarySortDesc={2}")
+ public static Collection<Object[]> data() {
+ return Arrays.asList(new Object[][] { { false, ColumnConfig.FIXED_WIDTH,
false },
+ { false, ColumnConfig.FIXED_WIDTH, true }, { false,
ColumnConfig.VARWIDTH_ASC, false },
+ { false, ColumnConfig.VARWIDTH_ASC, true }, { false,
ColumnConfig.VARWIDTH_DESC, false },
+ { false, ColumnConfig.VARWIDTH_DESC, true }, { true,
ColumnConfig.FIXED_WIDTH, false },
+ { true, ColumnConfig.FIXED_WIDTH, true }, { true,
ColumnConfig.VARWIDTH_ASC, false },
+ { true, ColumnConfig.VARWIDTH_ASC, true }, { true,
ColumnConfig.VARWIDTH_DESC, false },
+ { true, ColumnConfig.VARWIDTH_DESC, true } });
+ }
+
+ private final boolean salted;
+ private final ColumnConfig columnConfig;
+ private final boolean secondarySortDesc;
+
+ public WhereOptimizerForArrayAnyNullablePKIT(boolean salted, ColumnConfig
columnConfig,
+ boolean secondarySortDesc) {
+ this.salted = salted;
+ this.columnConfig = columnConfig;
+ this.secondarySortDesc = secondarySortDesc;
+ }
+
+ /**
+ * Returns the SALT_BUCKETS clause if salted, otherwise empty string.
+ */
+ private String getSaltClause() {
+ return salted ? " SALT_BUCKETS=" + SALT_BUCKETS : "";
+ }
+
+ /**
+ * Returns the sort order clause for secondary columns based on
secondarySortDesc parameter.
+ */
+ private String getSecondarySortOrder() {
+ return secondarySortDesc ? " DESC" : "";
+ }
+
+ private String createTableAndInsertTestDataForNullablePKTests() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl =
+ "CREATE TABLE " + tableName + " (" + "PK1 VARCHAR NOT NULL, " + "PK2
VARCHAR NOT NULL, "
+ + "PK3 DECIMAL NOT NULL, " + "PK4 " +
columnConfig.getNotNullDataType() + ", "
+ + "PK5 DECIMAL, " + "COL1 VARCHAR, " + "COL2 VARCHAR, " + "COL3
VARCHAR, "
+ + "CONSTRAINT pk PRIMARY KEY (PK1, PK2, PK3, PK4" +
columnConfig.getSortOrderClause()
+ + ", PK5" + getSecondarySortOrder() + ")" + ")" + getSaltClause();
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+
+ String upsertStmt = "UPSERT INTO " + tableName + " (PK1, PK2, PK3, PK4,
PK5, COL1, COL2, COL3) "
+ + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ // Row 1: PK5 is NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "val1");
+ stmt.setString(7, "val2");
+ stmt.setString(8, "val3");
+ stmt.executeUpdate();
+
+ // Row 2: PK5 is NULL, different PK4
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "Y");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "val4");
+ stmt.setString(7, "val5");
+ stmt.setString(8, "val6");
+ stmt.executeUpdate();
+
+ // Row 3: PK5 is NOT NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setBigDecimal(5, new BigDecimal("1.0"));
+ stmt.setString(6, "val7");
+ stmt.setString(7, "val8");
+ stmt.setString(8, "val9");
+ stmt.executeUpdate();
+
+ // Row 4: PK5 is NULL, different PK4
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "Z");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "val10");
+ stmt.setString(7, "val11");
+ stmt.setString(8, "val12");
+ stmt.executeUpdate();
+
+ // Row 5: Different PK1
+ stmt.setString(1, "C");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "val13");
+ stmt.setString(7, "val14");
+ stmt.setString(8, "val15");
+ stmt.executeUpdate();
+
+ conn.commit();
+ }
+ }
+ return tableName;
+ }
+
+ private void assertQueryUsesIndex(PreparedStatement stmt, String indexName)
throws SQLException {
+ QueryPlan queryPlan =
stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery();
+ ExplainPlan explain = queryPlan.getExplainPlan();
+ ExplainPlanAttributes planAttributes = explain.getPlanStepsAsAttributes();
+ String tableName = planAttributes.getTableName();
+ System.out.println("Explain plan: " + explain.toString());
+ assertTrue("Expected query to use index " + indexName + " but used table "
+ tableName,
+ tableName != null && tableName.contains(indexName));
+ }
+
+ /**
+ * Test for single point lookup with IS NULL on single trailing nullable PK
column. Uses
+ * class-level parameters: - columnConfig for PK4 type and sort order -
secondarySortDesc for PK5
+ * sort order
+ */
+ @Test
+ public void testSinglePointLookupWithNullablePK() throws Exception {
+ String tableName = createTableAndInsertTestDataForNullablePKTests();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT COL1, COL2, PK4, COL3, PK5 FROM " + tableName
+ + " WHERE PK1 = ? AND PK2 = ? AND PK3 = ? " + "AND PK4 = ? AND PK5 IS
NULL";
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 1 row: PK4='X' with PK5 IS NULL
+ assertTrue(rs.next());
+ assertEquals("X", rs.getString("PK4"));
+ assertEquals("val1", rs.getString("COL1"));
+ assertEquals("val2", rs.getString("COL2"));
+ assertEquals("val3", rs.getString("COL3"));
+ assertNull(rs.getBigDecimal("PK5"));
+
+ // No more rows
+ assertFalse(rs.next());
+ }
+ // IS NULL on trailing nullable PK column generates single POINT LOOKUP
+ assertPointLookupsAreGenerated(stmt, 1);
+
+ stmt.setString(4, "Y");
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 1 row: PK4='Y' with PK5 IS NULL
+ assertTrue(rs.next());
+ assertEquals("Y", rs.getString("PK4"));
+ assertEquals("val4", rs.getString("COL1"));
+ assertEquals("val5", rs.getString("COL2"));
+ assertEquals("val6", rs.getString("COL3"));
+ assertNull(rs.getBigDecimal("PK5"));
+ }
+ // IS NULL on trailing nullable PK column generates single POINT LOOKUP
+ assertPointLookupsAreGenerated(stmt, 1);
+ }
+ }
+ }
+
+ /**
+ * Test for multi-point lookups with nullable PK. Uses class-level
parameters: - columnConfig for
+ * PK4 type and sort order - secondarySortDesc for PK5 sort order
+ */
+ @Test
+ public void testMultiPointLookupsWithNullablePK() throws Exception {
+ String tableName = createTableAndInsertTestDataForNullablePKTests();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT COL1, COL2, PK4, COL3, PK5 FROM " + tableName
+ + " WHERE PK1 = ? AND PK2 = ? AND PK3 = ? AND PK4 = ANY(?) AND PK5 IS
NULL";
+ Array pk4Arr = conn.createArrayOf("VARCHAR", new String[] { "X", "Y" });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setArray(4, pk4Arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 2 rows: PK4='X' and PK4='Y' with PK5 IS NULL
+ assertTrue(rs.next());
+ String pk4Val1 = rs.getString("PK4");
+ assertTrue("Y".equals(pk4Val1) || "X".equals(pk4Val1));
+ assertNull(rs.getBytes("PK5"));
+
+ assertTrue(rs.next());
+ String pk4Val2 = rs.getString("PK4");
+ assertTrue("X".equals(pk4Val2) || "Y".equals(pk4Val2));
+ assertNull(rs.getBigDecimal("PK5"));
+
+ // No more rows
+ assertFalse(rs.next());
+ assertNotEquals(pk4Val1, pk4Val2);
+ }
+ // IS NULL on trailing nullable PK column generates POINT LOOKUPS (2
keys in array)
+ assertPointLookupsAreGenerated(stmt, 2);
+ }
+ }
+ }
+
+ /**
+ * Test for querying index with trailing nulls after adding nullable PK
column. Uses class-level
+ * parameters: - columnConfig.isDesc() for PK3 sort order -
secondarySortDesc for PK4 sort order
+ * (added via ALTER TABLE) Note: columnConfig's fixed-width vs
variable-width aspect is not
+ * applicable here, so FIXED_WIDTH and VARWIDTH_ASC behave the same (both
use ASC sort order).
+ */
+ @Test
+ public void testQueryWithIndexAfterAddingNullablePKColumn() throws Exception
{
+ Assume.assumeFalse(columnConfig.isFixedWidth());
+ String tableName = generateUniqueName();
+ String indexName = "IDX_" + generateUniqueName();
+
+ String pk3SortOrder = columnConfig.isDesc() ? " DESC" : "";
+ String pk4SortOrder = secondarySortDesc ? " DESC" : "";
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ // Step 1: Create table with one nullable PK column (PK3) at the end
+ String createTableDdl = "CREATE TABLE " + tableName + " (" + "PK1
VARCHAR NOT NULL, "
+ + "PK2 VARCHAR NOT NULL, " + "PK3 VARCHAR, " // Nullable PK column at
end
+ + "COL1 VARCHAR, " + "COL2 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY
(PK1, PK2, PK3"
+ + pk3SortOrder + ")" + ")" + getSaltClause();
+ conn.createStatement().execute(createTableDdl);
+ conn.commit();
+
+ // Step 2: Create a covered global index on the data table
+ String createIndexDdl = "CREATE INDEX " + indexName + " ON " + tableName
+ + " (COL1) INCLUDE (COL2) " + getSaltClause();
+ conn.createStatement().execute(createIndexDdl);
+ conn.commit();
+
+ // Step 3: Insert initial data (before ALTER TABLE)
+ // Row 1: PK3 is NULL
+ String upsertSql =
+ "UPSERT INTO " + tableName + " (PK1, PK2, PK3, COL1, COL2) VALUES (?,
?, ?, ?, ?)";
+ try (PreparedStatement stmt = conn.prepareStatement(upsertSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setNull(3, Types.VARCHAR);
+ stmt.setString(4, "indexed_val1");
+ stmt.setString(5, "col2_val1");
+ stmt.executeUpdate();
+
+ // Row 2: PK3 has a value
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setString(3, "pk3_val1");
+ stmt.setString(4, "indexed_val2");
+ stmt.setString(5, "col2_val2");
+ stmt.executeUpdate();
+
+ // Row 3: Different PK prefix
+ stmt.setString(1, "C");
+ stmt.setString(2, "D");
+ stmt.setNull(3, Types.VARCHAR);
+ stmt.setString(4, "indexed_val3");
+ stmt.setString(5, "col2_val3");
+ stmt.executeUpdate();
+ }
+ conn.commit();
+
+ // Step 4: Add a new nullable PK column (PK4) via ALTER TABLE with
configured sort order
+ String alterTableDdl =
+ "ALTER TABLE " + tableName + " ADD PK4 VARCHAR PRIMARY KEY " +
pk4SortOrder;
+ conn.createStatement().execute(alterTableDdl);
+ conn.commit();
+
+ // Step 5: Insert more data with same PK prefix but different PK4 values
+ upsertSql =
+ "UPSERT INTO " + tableName + " (PK1, PK2, PK3, PK4, COL1, COL2) VALUES
(?, ?, ?, ?, ?, ?)";
+ try (PreparedStatement stmt = conn.prepareStatement(upsertSql)) {
+ // Row 4: Same prefix as Row 1 (A, B, NULL) but PK4 = 'X'
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setNull(3, Types.VARCHAR);
+ stmt.setString(4, "X");
+ stmt.setString(5, "indexed_val4");
+ stmt.setString(6, "col2_val4");
+ stmt.executeUpdate();
+
+ // Row 5: Same prefix as Row 1 (A, B, NULL) but PK4 = 'Y'
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setNull(3, Types.VARCHAR);
+ stmt.setString(4, "Y");
+ stmt.setString(5, "indexed_val5");
+ stmt.setString(6, "col2_val5");
+ stmt.executeUpdate();
+
+ // Row 6: Same prefix as Row 2 (A, B, pk3_val1) but PK4 = 'Y'
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setString(3, "pk3_val1");
+ stmt.setString(4, "Y");
+ stmt.setString(5, "indexed_val6");
+ stmt.setString(6, "col2_val6");
+ stmt.executeUpdate();
+
+ // Row 7: Same prefix as Row 2 (A, B, NULL) but PK4 = 'Z'
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setNull(3, Types.VARCHAR);
+ stmt.setString(4, "Z");
+ stmt.setString(5, "indexed_val7");
+ stmt.setString(6, "col2_val7");
+ stmt.executeUpdate();
+ }
+ conn.commit();
+
+ String selectSql = "SELECT /*+ INDEX(" + tableName + " " + indexName +
") */ "
+ + "PK1, PK2, PK3, PK4, COL1, COL2 FROM " + tableName
+ + " WHERE PK1 = ? AND PK2 = ? AND PK3 IS NULL AND (PK4 IS NULL OR PK4
= ANY(?)) AND COL1 = ANY(?)";
+ Array pk4Arr = conn.createArrayOf("VARCHAR", new String[] { "Z", "Y" });
+ Array col1Arr = conn.createArrayOf("VARCHAR",
+ new String[] { "indexed_val5", "indexed_val1", "indexed_val7",
"indexed_val4" });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setArray(3, pk4Arr);
+ stmt.setArray(4, col1Arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertTrue(rs.next());
+ String pk4Val = rs.getString("PK4");
+ assertNull(pk4Val);
+
+ assertTrue(rs.next());
+ pk4Val = rs.getString("PK4");
+ assertTrue("Y".equals(pk4Val));
+
+ assertTrue(rs.next());
+ pk4Val = rs.getString("PK4");
+ assertTrue("Z".equals(pk4Val));
+
+ // No more rows
+ assertFalse(rs.next());
+ }
+ // Should generate point lookups for the three PK4 values
+ assertPointLookupsAreGenerated(stmt, 12);
+ // Assert that the query uses the index table
+ assertQueryUsesIndex(stmt, indexName);
+ }
+ }
+ }
+
+ /**
+ * Test for multi-point lookups on view with nullable PK columns. Uses
class-level parameters: -
+ * columnConfig for VIEW_PK1 type and sort order - secondarySortDesc for
VIEW_PK2 sort order
+ */
+ @Test
+ public void testMultiPointLookupsOnViewWithNullablePKColumns() throws
Exception {
+ String tableName = generateUniqueName();
+ String viewName = "VW_" + generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ // Step 1: Create parent table with fixed-width NOT NULL last PK
+ // Using CHAR (fixed-width) for PK2 to allow view to add PK columns
+ String createTableDdl =
+ "CREATE TABLE " + tableName + " (" + "PK1 VARCHAR NOT NULL, " + "PK2
CHAR(10) NOT NULL, " // Fixed-width
+
// NOT
+
// NULL
+
// -
+
// allows
+
// view
+
// to
+
// add
+
// PKs
+ + "COL1 VARCHAR, " + "COL2 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY
(PK1, PK2)" + ")"
+ + getSaltClause();
+ conn.createStatement().execute(createTableDdl);
+ conn.commit();
+
+ // Step 2: Create view that adds two nullable PK columns with configured
types/sort orders
+ String createViewDdl = "CREATE VIEW " + viewName + " (" + "VIEW_PK1 "
+ + columnConfig.getDataType() + ", " + "VIEW_PK2 VARCHAR, " +
"VIEW_COL1 VARCHAR, "
+ + "CONSTRAINT view_pk PRIMARY KEY (VIEW_PK1" +
columnConfig.getSortOrderClause()
+ + ", VIEW_PK2" + getSecondarySortOrder() + ")" + ") AS SELECT * FROM "
+ tableName;
+ conn.createStatement().execute(createViewDdl);
+ conn.commit();
+
+ // Step 3: Insert data through the view with various combinations
+ String upsertSql = "UPSERT INTO " + viewName
+ + " (PK1, PK2, VIEW_PK1, VIEW_PK2, COL1, COL2, VIEW_COL1) VALUES (?,
?, ?, ?, ?, ?, ?)";
+ try (PreparedStatement stmt = conn.prepareStatement(upsertSql)) {
+ // Row 1: VIEW_PK1 = 'X', VIEW_PK2 is NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "BASE1");
+ stmt.setString(3, "X");
+ stmt.setNull(4, Types.VARCHAR);
+ stmt.setString(5, "col1_val2");
+ stmt.setString(6, "col2_val2");
+ stmt.setString(7, "view_col1_val2");
+ stmt.executeUpdate();
+
+ // Row 2: VIEW_PK1 = 'X', VIEW_PK2 = 'P'
+ stmt.setString(1, "A");
+ stmt.setString(2, "BASE1");
+ stmt.setString(3, "X");
+ stmt.setString(4, "P");
+ stmt.setString(5, "col1_val3");
+ stmt.setString(6, "col2_val3");
+ stmt.setString(7, "view_col1_val3");
+ stmt.executeUpdate();
+
+ // Row 3: VIEW_PK1 = 'X', VIEW_PK2 = 'Q'
+ stmt.setString(1, "A");
+ stmt.setString(2, "BASE1");
+ stmt.setString(3, "X");
+ stmt.setString(4, "Q");
+ stmt.setString(5, "col1_val4");
+ stmt.setString(6, "col2_val4");
+ stmt.setString(7, "view_col1_val4");
+ stmt.executeUpdate();
+
+ // Row 4: VIEW_PK1 = 'Y', VIEW_PK2 is NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "BASE1");
+ stmt.setString(3, "Y");
+ stmt.setNull(4, Types.VARCHAR);
+ stmt.setString(5, "col1_val5");
+ stmt.setString(6, "col2_val5");
+ stmt.setString(7, "view_col1_val5");
+ stmt.executeUpdate();
+
+ // Row 5: VIEW_PK1 = 'Y', VIEW_PK2 = 'Q'
+ stmt.setString(1, "A");
+ stmt.setString(2, "BASE1");
+ stmt.setString(3, "Y");
+ stmt.setString(4, "Q");
+ stmt.setString(5, "col1_val6");
+ stmt.setString(6, "col2_val6");
+ stmt.setString(7, "view_col1_val6");
+ stmt.executeUpdate();
+
+ // Row 6: Different base PK prefix
+ stmt.setString(1, "B");
+ stmt.setString(2, "BASE2");
+ stmt.setString(3, "X");
+ stmt.setString(4, "P");
+ stmt.setString(5, "col1_val7");
+ stmt.setString(6, "col2_val7");
+ stmt.setString(7, "view_col1_val7");
+ stmt.executeUpdate();
+ }
+ conn.commit();
+
+ String selectSql = "SELECT PK1, PK2, VIEW_PK1, VIEW_PK2, COL1, VIEW_COL1
FROM " + viewName
+ + " WHERE PK1 = ? AND PK2 = ? AND VIEW_PK1 = ANY(?) AND (VIEW_PK2 IS
NULL OR VIEW_PK2 = ANY(?))";
+ Array viewPk1Arr = conn.createArrayOf("VARCHAR", new String[] { "X", "Y"
});
+ Array viewPk2Arr = conn.createArrayOf("VARCHAR", new String[] { "P", "Q"
});
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "BASE1");
+ stmt.setArray(3, viewPk1Arr);
+ stmt.setArray(4, viewPk2Arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ int rowCount = 0;
+ while (rs.next()) {
+ rowCount++;
+ String pk2 = rs.getString("PK2");
+ String viewPk1 = rs.getString("VIEW_PK1");
+ String viewPk2 = rs.getString("VIEW_PK2");
+ assertEquals(pk2, "BASE1");
+ // Verify VIEW_PK1 is either X or Y
+ assertTrue("X".equals(viewPk1) || "Y".equals(viewPk1));
+ // Verify VIEW_PK2 is NULL, P, or Q
+ assertTrue(viewPk2 == null || "P".equals(viewPk2) ||
"Q".equals(viewPk2));
+ }
+ // Expected rows:
+ // (A, BASE1, X, NULL), (A, BASE1, X, P), (A, BASE1, X, Q),
+ // (A, BASE1, Y, NULL), (A, BASE1, Y, Q)
+ assertEquals(5, rowCount);
+ }
+ // Assert point lookups are generated
+ // VIEW_PK1 has 2 values (X, Y), VIEW_PK2 has 3 values (NULL, P, Q)
+ // Total combinations: 2 * 3 = 6 point lookups
+ assertPointLookupsAreGenerated(stmt, 6);
+ }
+ }
+ }
+
+ /**
+ * Test for degenerate scan with IS NULL check on all PK columns. Uses
class-level
+ * columnConfig.isDesc() for sort order of all PK columns. Note:
columnConfig's fixed-width vs
+ * variable-width aspect is not applicable here, so FIXED_WIDTH and
VARWIDTH_ASC behave the same
+ * (both use ASC sort order). The secondarySortDesc parameter is also not
applicable for this
+ * test.
+ */
+ @Test
+ public void testDegenerateScanWithIsNullCheckOnAllPKColumns() throws
Exception {
+ Assume.assumeFalse(columnConfig.isFixedWidth() || secondarySortDesc);
+ String tableName = generateUniqueName();
+ String sortOrder = columnConfig.isDesc() ? " DESC" : "";
+
+ // Create table with all nullable PK columns
+ String ddl = "CREATE TABLE " + tableName + " (" + "PK1 VARCHAR, " + "PK2
VARCHAR, "
+ + "PK3 DECIMAL, " + "COL1 VARCHAR, " + "COL2 VARCHAR, " + "CONSTRAINT pk
PRIMARY KEY (PK1 "
+ + sortOrder + ", PK2 " + sortOrder + ", PK3 " + sortOrder + ")" + ")" +
getSaltClause();
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+
+ // Insert test data with various null combinations
+ String upsertSql =
+ "UPSERT INTO " + tableName + " (PK1, PK2, PK3, COL1, COL2) VALUES (?, ?,
?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertSql)) {
+ // Row 1: PK1 has value, PK2 and PK3 are NULL
+ stmt.setString(1, "A");
+ stmt.setNull(2, Types.VARCHAR);
+ stmt.setNull(3, Types.DECIMAL);
+ stmt.setString(4, "val3");
+ stmt.setString(5, "val4");
+ stmt.executeUpdate();
+
+ // Row 2: PK1 and PK2 have values, PK3 is NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setNull(3, Types.DECIMAL);
+ stmt.setString(4, "val5");
+ stmt.setString(5, "val6");
+ stmt.executeUpdate();
+
+ // Row 3: All PKs have values (no nulls)
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "val7");
+ stmt.setString(5, "val8");
+ stmt.executeUpdate();
+
+ // Row 4: PK1 is NULL, PK2 has value, PK3 is NULL
+ stmt.setNull(1, Types.CHAR);
+ stmt.setString(2, "C");
+ stmt.setNull(3, Types.DECIMAL);
+ stmt.setString(4, "val9");
+ stmt.setString(5, "val10");
+ stmt.executeUpdate();
+
+ conn.commit();
+ }
+ }
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ // Query: All PKs are NULL - should match no rows
+ String selectSql = "SELECT COL1, COL2 FROM " + tableName
+ + " WHERE PK1 IS NULL AND PK2 IS NULL AND PK3 IS NULL";
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ try (ResultSet rs = stmt.executeQuery()) {
+ assertFalse(rs.next());
+ }
+ assertDegenerateScanIsGenerated(stmt);
+ }
+ }
+ }
+
+ /**
+ * Test for RANGE SCAN and SKIP SCAN with IS NULL on second-to-last nullable
PK column, without
+ * including the last PK column in the WHERE clause. Uses class-level
parameters: - salted for
+ * table salting - columnConfig for PK3 sort order (fixed-width vs
variable-width aspect not
+ * applicable for DECIMAL) - secondarySortDesc for PK4 sort order
+ */
+ @Test
+ public void testRangeScanAndSkipScanWithIsNullOnSecondToLastPK() throws
Exception {
+ Assume.assumeFalse(columnConfig.isFixedWidth());
+ String tableName = generateUniqueName();
+ String pk4SortOrder = secondarySortDesc ? " DESC" : "";
+
+ // Create table with last two PK columns (PK4, PK5) nullable
+ // PK3 uses columnConfig for sort order, PK4 uses secondarySortDesc
+ String ddl = "CREATE TABLE " + tableName + " (" + "PK1 VARCHAR NOT NULL, "
+ + "PK2 VARCHAR NOT NULL, " + "PK3 DECIMAL NOT NULL, " + "PK4 VARCHAR, "
+ "PK5 DECIMAL, "
+ + "COL1 VARCHAR, " + "COL2 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (PK1,
PK2, PK3"
+ + columnConfig.getSortOrderClause() + ", PK4" + pk4SortOrder + ", PK5)"
+ ")"
+ + getSaltClause();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.createStatement().execute(ddl);
+ conn.commit();
+ }
+
+ // Insert test data
+ String upsertStmt = "UPSERT INTO " + tableName
+ + " (PK1, PK2, PK3, PK4, PK5, COL1, COL2) VALUES (?, ?, ?, ?, ?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ // Row 1: PK4 IS NULL, PK5 IS NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setNull(4, Types.VARCHAR);
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "val1");
+ stmt.setString(7, "val2");
+ stmt.executeUpdate();
+
+ // Row 2: PK4 IS NULL, PK5 has value
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setNull(4, Types.VARCHAR);
+ stmt.setBigDecimal(5, new BigDecimal("2.0"));
+ stmt.setString(6, "val3");
+ stmt.setString(7, "val4");
+ stmt.executeUpdate();
+
+ // Row 3: PK4 has value, PK5 IS NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "val5");
+ stmt.setString(7, "val6");
+ stmt.executeUpdate();
+
+ // Row 4: PK4 has value, PK5 has value
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "Y");
+ stmt.setBigDecimal(5, new BigDecimal("3.0"));
+ stmt.setString(6, "val7");
+ stmt.setString(7, "val8");
+ stmt.executeUpdate();
+
+ // Row 5: Different PK3 value, PK4 IS NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, new BigDecimal("200.5"));
+ stmt.setNull(4, Types.VARCHAR);
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "val9");
+ stmt.setString(7, "val10");
+ stmt.executeUpdate();
+
+ conn.commit();
+ }
+ }
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ // Test 1: RANGE SCAN - equality on PK1-PK3, IS NULL on PK4, no
condition on PK5
+ // This should produce a RANGE SCAN because trailing PK5 is not
constrained
+ String selectSql = "SELECT COL1, COL2, PK4, PK5 FROM " + tableName
+ + " WHERE PK1 = ? AND PK2 = ? AND PK3 = ? AND PK4 IS NULL";
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 2 rows where PK4 IS NULL if secondarySortDesc is
false, otherwise 1 row
+ // due to bug involving DESC sort order and trailing IS NULL when
doing range scan.
+ // - Row 1: PK4 NULL, PK5 NULL (val1, val2)
+ // - Row 2: PK4 NULL, PK5=2.0 (val3, val4)
+ assertTrue(rs.next());
+ assertNull(rs.getString("PK4"));
+ String col1Val1 = rs.getString("COL1");
+ if (!secondarySortDesc) {
+ assertTrue("val1".equals(col1Val1) || "val3".equals(col1Val1));
+
+ assertTrue(rs.next());
+ assertNull(rs.getString("PK4"));
+ String col1Val2 = rs.getString("COL1");
+ assertTrue("val1".equals(col1Val2) || "val3".equals(col1Val2));
+
+ assertNotEquals(col1Val1, col1Val2);
+ } else {
+ assertTrue("val3".equals(col1Val1));
+ }
+ assertFalse(rs.next());
+ }
+ // Query plan should show RANGE SCAN since trailing PK5 is not
constrained
+ assertRangeScanIsGenerated(stmt);
+ }
+
+ // Test 2: SKIP SCAN - equality on PK1-PK2, =ANY on PK3, IS NULL on PK4,
no condition on PK5
+ // This should produce a SKIP SCAN due to =ANY on PK3
+ String selectSql2 = "SELECT COL1, COL2, PK3, PK4, PK5 FROM " + tableName
+ + " WHERE PK1 = ? AND PK2 = ? AND PK3 = ANY(?) AND PK4 IS NULL";
+ Array pk3Arr =
+ conn.createArrayOf("DECIMAL", new BigDecimal[] { PK3_VAL, new
BigDecimal("200.5") });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql2)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setArray(3, pk3Arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 3 rows where PK4 IS NULL but returns >=1 and <=5
rows due to existing bug
+ // inolving skip scan with IS NULL in where clause.
+ // - Row 1: PK3=100.5, PK4 NULL, PK5 NULL (val1, val2)
+ // - Row 2: PK3=100.5, PK4 NULL, PK5=2.0 (val3, val4)
+ // - Row 5: PK3=200.5, PK4 NULL, PK5 NULL (val9, val10)
+ int rowCount = 0;
+ while (rs.next()) {
+ rowCount++;
+ }
+ if (columnConfig == ColumnConfig.VARWIDTH_DESC &&
!secondarySortDesc) {
+ assertEquals(3, rowCount);
+ } else if (columnConfig == ColumnConfig.VARWIDTH_DESC &&
secondarySortDesc) {
+ assertEquals(2, rowCount);
+ } else if (salted && columnConfig == ColumnConfig.VARWIDTH_ASC &&
!secondarySortDesc) {
+ assertEquals(4, rowCount);
+ } else if (salted && columnConfig == ColumnConfig.VARWIDTH_ASC &&
secondarySortDesc) {
+ assertEquals(2, rowCount);
+ } else if (!salted && columnConfig == ColumnConfig.VARWIDTH_ASC &&
secondarySortDesc) {
+ assertEquals(1, rowCount);
+ } else {
+ assertEquals(5, rowCount);
+ }
+ }
+ if (salted) {
+ // 2 * no. of salt buckets = 2 * 3 = 6
+ assertSkipScanIsGenerated(stmt, 6);
+ } else {
+ assertSkipScanIsGenerated(stmt, 2);
+ }
+ }
+ }
+ }
+
+ /**
+ * Test for multi-point lookups with IS NULL on a fixed-width NOT NULL PK
column. This test
+ * verifies that degenerate scan is generated when using IS NULL on a
fixed-width column (INTEGER
+ * NOT NULL) in the primary key, and no rows are returned since a NOT NULL
column can never have
+ * NULL values. Uses class-level parameters: - salted for table salting -
columnConfig for PK3
+ * (nullable, variable-width) type and sort order - secondarySortDesc for
PK4 (INTEGER NOT NULL,
+ * fixed-width) sort order
+ */
+ @Test
+ public void testDegenerateScanWithIsNullOnFixedWidthPK() throws Exception {
+ String tableName = generateUniqueName();
+ String pk4SortOrder = secondarySortDesc ? " DESC" : "";
+ boolean isPK3FixedWidth = columnConfig.isFixedWidth();
+
+ String ddl = "CREATE TABLE " + tableName + " (" + "PK1 VARCHAR NOT NULL, "
+ + "PK2 VARCHAR NOT NULL, " + "PK3 " + columnConfig.getDataType() + ", "
// Nullable
+
// variable-width
+
// column
+ + "PK4 INTEGER NOT NULL, " // Fixed-width NOT NULL column
+ + "COL1 VARCHAR, " + "COL2 VARCHAR, " + "CONSTRAINT pk PRIMARY KEY (PK1,
PK2, PK3"
+ + columnConfig.getSortOrderClause() + ", PK4" + pk4SortOrder + ")" + ")"
+ getSaltClause();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.createStatement().execute(ddl);
+ conn.commit();
+ }
+
+ // Insert test data with various PK3 values (some NULL, some non-NULL)
+ // PK4 is always non-NULL since it's a fixed-width NOT NULL column
+ String upsertStmt =
+ "UPSERT INTO " + tableName + " (PK1, PK2, PK3, PK4, COL1, COL2) VALUES
(?, ?, ?, ?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ // Row 1: PK3='X', PK4=1
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setString(3, "X");
+ stmt.setInt(4, 1);
+ stmt.setString(5, "val1");
+ stmt.setString(6, "val2");
+ stmt.executeUpdate();
+
+ // Row 2: PK3='Y', PK4=2
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setString(3, "Y");
+ stmt.setInt(4, 2);
+ stmt.setString(5, "val3");
+ stmt.setString(6, "val4");
+ stmt.executeUpdate();
+
+ // Row 3: PK3 is NULL, PK4=1
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ if (isPK3FixedWidth) {
+ stmt.setString(3, "U");
+ } else {
+ stmt.setNull(3, Types.VARCHAR);
+ }
+ stmt.setInt(4, 1);
+ stmt.setString(5, "val5");
+ stmt.setString(6, "val6");
+ stmt.executeUpdate();
+
+ // Row 4: PK3='Z', PK4=3
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setString(3, "Z");
+ stmt.setInt(4, 3);
+ stmt.setString(5, "val7");
+ stmt.setString(6, "val8");
+ stmt.executeUpdate();
+
+ // Row 5: PK3 is NULL, PK4=2
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ if (isPK3FixedWidth) {
+ stmt.setString(3, "U");
+ } else {
+ stmt.setNull(3, Types.VARCHAR);
+ }
+ stmt.setInt(4, 2);
+ stmt.setString(5, "val9");
+ stmt.setString(6, "val10");
+ stmt.executeUpdate();
+
+ // Row 6: Different PK1, PK3='X', PK4=1
+ stmt.setString(1, "C");
+ stmt.setString(2, "B");
+ stmt.setString(3, "X");
+ stmt.setInt(4, 1);
+ stmt.setString(5, "val11");
+ stmt.setString(6, "val12");
+ stmt.executeUpdate();
+
+ conn.commit();
+ }
+ }
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT COL1, COL2, PK3, PK4 FROM " + tableName
+ + " WHERE PK1 = ? AND PK2 = ? AND PK3 = ANY(?) AND PK4 IS NULL";
+ Array pk3Arr = conn.createArrayOf("VARCHAR", new String[] { "X", "Y" });
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setArray(3, pk3Arr);
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 0 rows since PK4 is NOT NULL and cannot have NULL
values
+ assertFalse("Expected no rows since PK4 (INTEGER NOT NULL) cannot be
NULL", rs.next());
+ }
+ assertDegenerateScanIsGenerated(stmt);
+ }
+ }
+ }
+
+ private String
createTableAndInsertTestDataForMultipleTrailingNullablePKTests() throws
Exception {
+ String tableName = generateUniqueName();
+ String ddl =
+ "CREATE TABLE " + tableName + " (" + "PK1 VARCHAR NOT NULL, " + "PK2
VARCHAR NOT NULL, "
+ + "PK3 DECIMAL NOT NULL, " + "PK4 " +
columnConfig.getNotNullDataType() + ", "
+ + "PK5 DECIMAL, " + "PK6 VARCHAR, " + "COL1 VARCHAR, " + "COL2
VARCHAR, " + "COL3 VARCHAR, "
+ + "CONSTRAINT pk PRIMARY KEY (PK1, PK2, PK3, PK4" +
columnConfig.getSortOrderClause()
+ + ", PK5" + getSecondarySortOrder() + ", PK6" +
getSecondarySortOrder() + ")" + ")"
+ + getSaltClause();
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ stmt.execute(ddl);
+ conn.commit();
+ }
+ }
+
+ String upsertStmt = "UPSERT INTO " + tableName
+ + " (PK1, PK2, PK3, PK4, PK5, PK6, COL1, COL2, COL3) " + "VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?)";
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ try (PreparedStatement stmt = conn.prepareStatement(upsertStmt)) {
+ // Row 1: Both PK5 and PK6 are NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setNull(6, Types.VARCHAR);
+ stmt.setString(7, "val1");
+ stmt.setString(8, "val2");
+ stmt.setString(9, "val3");
+ stmt.executeUpdate();
+
+ // Row 2: PK5 is NOT NULL, PK6 is NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setBigDecimal(5, new BigDecimal("1.0"));
+ stmt.setNull(6, Types.VARCHAR);
+ stmt.setString(7, "val4");
+ stmt.setString(8, "val5");
+ stmt.setString(9, "val6");
+ stmt.executeUpdate();
+
+ // Row 3: PK5 is NULL, PK6 is NOT NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setString(6, "P");
+ stmt.setString(7, "val7");
+ stmt.setString(8, "val8");
+ stmt.setString(9, "val9");
+ stmt.executeUpdate();
+
+ // Row 4: Neither PK5 nor PK6 are NULL
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setBigDecimal(5, new BigDecimal("1.0"));
+ stmt.setString(6, "P");
+ stmt.setString(7, "val10");
+ stmt.setString(8, "val11");
+ stmt.setString(9, "val12");
+ stmt.executeUpdate();
+
+ // Row 5: Both PK5 and PK6 are NULL, different PK4
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "Y");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setNull(6, Types.VARCHAR);
+ stmt.setString(7, "val13");
+ stmt.setString(8, "val14");
+ stmt.setString(9, "val15");
+ stmt.executeUpdate();
+
+ // Row 6: Neither PK5 nor PK6 are NULL, different PK4
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "Y");
+ stmt.setBigDecimal(5, new BigDecimal("2.0"));
+ stmt.setString(6, "Q");
+ stmt.setString(7, "val16");
+ stmt.setString(8, "val17");
+ stmt.setString(9, "val18");
+ stmt.executeUpdate();
+
+ // Row 7: Both PK5 and PK6 are NULL, different PK4
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "Z");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setNull(6, Types.VARCHAR);
+ stmt.setString(7, "val19");
+ stmt.setString(8, "val20");
+ stmt.setString(9, "val21");
+ stmt.executeUpdate();
+
+ // Row 8: Both PK5 and PK6 are NULL, different PK1
+ stmt.setString(1, "C");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ stmt.setNull(5, Types.DECIMAL);
+ stmt.setNull(6, Types.VARCHAR);
+ stmt.setString(7, "val22");
+ stmt.setString(8, "val23");
+ stmt.setString(9, "val24");
+ stmt.executeUpdate();
+
+ conn.commit();
+ }
+ }
+ return tableName;
+ }
+
+ /**
+ * Test for single point lookup with multiple trailing IS NULL PK columns.
Uses class-level
+ * parameters: - columnConfig for PK4 type and sort order -
secondarySortDesc for both PK5 and PK6
+ * sort order (same sort order for both) Verifies that IS NULL on both
trailing nullable PK
+ * columns generates a single POINT LOOKUP.
+ */
+ @Test
+ public void testSinglePointLookupWithMultipleTrailingNullablePKs() throws
Exception {
+ String tableName =
createTableAndInsertTestDataForMultipleTrailingNullablePKTests();
+
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ String selectSql = "SELECT COL1, COL2, PK4, COL3, PK5, PK6 FROM " +
tableName
+ + " WHERE PK1 = ? AND PK2 = ? AND PK3 = ? " + "AND PK4 = ? AND PK5 IS
NULL AND PK6 IS NULL";
+ try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
+ stmt.setString(1, "A");
+ stmt.setString(2, "B");
+ stmt.setBigDecimal(3, PK3_VAL);
+ stmt.setString(4, "X");
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 1 row: PK4='X' with both PK5 and PK6 IS NULL
+ assertTrue(rs.next());
+ assertEquals("X", rs.getString("PK4"));
+ assertEquals("val1", rs.getString("COL1"));
+ assertEquals("val2", rs.getString("COL2"));
+ assertEquals("val3", rs.getString("COL3"));
+ assertNull(rs.getBigDecimal("PK5"));
+ assertNull(rs.getString("PK6"));
+
+ // No more rows
+ assertFalse(rs.next());
+ }
+ // IS NULL on both trailing nullable PK columns generates single POINT
LOOKUP
+ assertPointLookupsAreGenerated(stmt, 1);
+
+ stmt.setString(4, "Y");
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 1 row: PK4='Y' with both PK5 and PK6 IS NULL
+ assertTrue(rs.next());
+ assertEquals("Y", rs.getString("PK4"));
+ assertEquals("val13", rs.getString("COL1"));
+ assertEquals("val14", rs.getString("COL2"));
+ assertEquals("val15", rs.getString("COL3"));
+ assertNull(rs.getBigDecimal("PK5"));
+ assertNull(rs.getString("PK6"));
+
+ // No more rows
+ assertFalse(rs.next());
+ }
+ // IS NULL on both trailing nullable PK columns generates single POINT
LOOKUP
+ assertPointLookupsAreGenerated(stmt, 1);
+
+ stmt.setString(4, "Z");
+ try (ResultSet rs = stmt.executeQuery()) {
+ // Should return 1 row: PK4='Z' with both PK5 and PK6 IS NULL
+ assertTrue(rs.next());
+ assertEquals("Z", rs.getString("PK4"));
+ assertEquals("val19", rs.getString("COL1"));
+ assertEquals("val20", rs.getString("COL2"));
+ assertEquals("val21", rs.getString("COL3"));
+ assertNull(rs.getBigDecimal("PK5"));
+ assertNull(rs.getString("PK6"));
+
+ // No more rows
+ assertFalse(rs.next());
+ }
+ // IS NULL on both trailing nullable PK columns generates single POINT
LOOKUP
+ assertPointLookupsAreGenerated(stmt, 1);
+ }
+ }
+ }
+}
diff --git
a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
index 33f616f189..f38959c88d 100644
---
a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
+++
b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
@@ -2696,10 +2696,16 @@ public class WhereOptimizerTest extends
BaseConnectionlessQueryTest {
StatementContext context = compileStatement(query, Collections.<Object>
emptyList());
Scan scan = context.getScan();
Filter filter = scan.getFilter();
+ // With trailing IS NULL as point lookup, no filter is needed
assertNull(filter);
- assertArrayEquals(Bytes.toBytes("a"), scan.getStartRow());
- assertArrayEquals(ByteUtil.concat(Bytes.toBytes("a"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
- QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
+ // Point lookup for trailing IS NULL: startRow = "a", stopRow = "a\0"
+ // The separator is added to create an exclusive upper bound
+ byte[] expectedStartKey = Bytes.toBytes("a");
+ byte[] expectedStopKey = ByteUtil.concat(expectedStartKey,
QueryConstants.SEPARATOR_BYTE_ARRAY);
+ assertArrayEquals(expectedStartKey, scan.getStartRow());
+ assertArrayEquals(expectedStopKey, scan.getStopRow());
+ // Verify it's a point lookup
+ assertTrue(context.getScanRanges().isPointLookup());
}
@Test
@@ -2714,18 +2720,24 @@ public class WhereOptimizerTest extends
BaseConnectionlessQueryTest {
StatementContext context = compileStatement(query, Collections.<Object>
emptyList());
Scan scan = context.getScan();
Filter filter = scan.getFilter();
+ // With trailing IS NULL as point lookup, when combined with OR, it becomes
+ // a point lookup with 2 keys (one for NULL, one for 'b')
assertTrue(filter instanceof SkipScanFilter);
SkipScanFilter skipScan = (SkipScanFilter) filter;
List<List<KeyRange>> slots = skipScan.getSlots();
- assertEquals(2, slots.size());
- assertEquals(1, slots.get(0).size());
- assertEquals(2, slots.get(1).size());
- assertEquals(KeyRange.getKeyRange(Bytes.toBytes("a")),
slots.get(0).get(0));
- assertTrue(KeyRange.IS_NULL_RANGE == slots.get(1).get(0));
- assertEquals(KeyRange.getKeyRange(Bytes.toBytes("b")),
slots.get(1).get(1));
- assertArrayEquals(Bytes.toBytes("a"), scan.getStartRow());
- assertArrayEquals(ByteUtil.concat(Bytes.toBytes("a"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
- Bytes.toBytes("b"), QueryConstants.SEPARATOR_BYTE_ARRAY),
scan.getStopRow());
+ // Point lookup collapses to single slot with 2 keys
+ assertEquals(1, slots.size());
+ assertEquals(2, slots.get(0).size());
+ // Verify it's a point lookup
+ assertTrue(context.getScanRanges().isPointLookup());
+ // Key 1: "a" (for b IS NULL - trailing separator stripped)
+ byte[] key1 = Bytes.toBytes("a");
+ // Key 2: "a\0b" (for b = 'b' - trailing separator stripped for last
column)
+ byte[] key2 =
+ ByteUtil.concat(Bytes.toBytes("a"), QueryConstants.SEPARATOR_BYTE_ARRAY,
Bytes.toBytes("b"));
+ // Keys are sorted, so key1 comes before key2
+ assertEquals(KeyRange.getKeyRange(key1), slots.get(0).get(0));
+ assertEquals(KeyRange.getKeyRange(key2), slots.get(0).get(1));
}
@Test
