This is an automated email from the ASF dual-hosted git repository.
vjasani 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 57b1f9f498 PHOENIX-7762 Incorrect scan boundary when slot span exceeds
actual bound in composite key (#2373)
57b1f9f498 is described below
commit 57b1f9f498d25f1737d3dc7c2fc5b9dc8069eb6c
Author: Viraj Jasani <[email protected]>
AuthorDate: Tue Feb 17 21:34:01 2026 -0800
PHOENIX-7762 Incorrect scan boundary when slot span exceeds actual bound in
composite key (#2373)
---
.../java/org/apache/phoenix/util/ScanUtil.java | 37 +-
.../phoenix/end2end/RowValueConstructorIT.java | 398 +++++++++++++++++++++
.../apache/phoenix/compile/QueryCompilerTest.java | 189 ++++++++++
3 files changed, 621 insertions(+), 3 deletions(-)
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 d668af16b4..5305408fbc 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
@@ -391,6 +391,34 @@ public class ScanUtil {
return getKey(schema, slots, slotSpan, Bound.UPPER);
}
+ private static Field getActualEndField(RowKeySchema schema, int
startFieldIndex, int slotSpan,
+ KeyRange range, Bound bound) {
+ if (slotSpan > 0) {
+ byte[] boundBytes = range.getRange(bound);
+ // For multi-span slots, the bound bytes might cover fewer fields than
the slotSpan. In such
+ // cases, we need to use the actual last field covered by the bytes for
separator logic.
+ // Otherwise, a variable-length field may incorrectly omit its separator
byte, producing an
+ // incorrect scan boundary.
+ if (boundBytes.length > 0 && boundBytes != KeyRange.UNBOUND) {
+ ImmutableBytesWritable spanPtr =
+ new ImmutableBytesWritable(boundBytes, 0, boundBytes.length);
+ if (!schema.position(spanPtr, startFieldIndex, startFieldIndex +
slotSpan)) {
+ // The bytes don't cover the full span. Find the actual last field
covered.
+ int actualEndFieldIdx = startFieldIndex;
+ for (int i = startFieldIndex + 1; i <= startFieldIndex + slotSpan;
i++) {
+ spanPtr = new ImmutableBytesWritable(boundBytes, 0,
boundBytes.length);
+ if (!schema.position(spanPtr, startFieldIndex, i)) {
+ break;
+ }
+ actualEndFieldIdx = i;
+ }
+ return schema.getField(actualEndFieldIdx);
+ }
+ }
+ }
+ return schema.getField(startFieldIndex + slotSpan);
+ }
+
private static byte[] getKey(RowKeySchema schema, List<List<KeyRange>>
slots, int[] slotSpan,
Bound bound) {
if (slots.isEmpty()) {
@@ -403,7 +431,9 @@ public class ScanUtil {
position[i] = bound == Bound.LOWER ? 0 : slots.get(i).size() - 1;
KeyRange range = slots.get(i).get(position[i]);
slotEndingFieldPos = slotEndingFieldPos + slotSpan[i] + 1;
- Field field = schema.getField(slotEndingFieldPos);
+ int startFieldPos = slotEndingFieldPos - slotSpan[i];
+ // Use the actual end field based on how many fields the bound bytes
cover.
+ Field field = getActualEndField(schema, startFieldPos, slotSpan[i],
range, bound);
int keyLength = range.getRange(bound).length;
if (!field.getDataType().isFixedWidth()) {
if (field.getDataType() != PVarbinaryEncoded.INSTANCE) {
@@ -471,8 +501,9 @@ public class ScanUtil {
// Build up the key by appending the bound of each key range
// from the current position of each slot.
KeyRange range = slots.get(i).get(position[i]);
- // Use last slot in a multi-span column to determine if fixed width
- field = schema.getField(fieldIndex + slotSpan[i]);
+ // Use last slot in a multi-span column to determine if fixed width.
+ // Use the actual end field based on how many fields the bound bytes
cover.
+ field = getActualEndField(schema, fieldIndex, slotSpan[i], range, bound);
boolean isFixedWidth = field.getDataType().isFixedWidth();
/*
* If the current slot is unbound then stop if: 1) setting the upper
bound. There's no value
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
index f2e4a2faa6..f4b03c7c5c 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
@@ -2098,6 +2098,404 @@ public class RowValueConstructorIT extends
ParallelStatsDisabledIT {
}
}
+ @Test
+ public void testRVCWithUpperBoundOnIndex() throws Exception {
+ Connection conn = nextConnection(getUrl());
+ String tableName = generateUniqueName();
+ String indexName = generateUniqueName();
+
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk VARCHAR NOT NULL, "
+ + "sk BIGINT NOT NULL, " + "category VARCHAR, " + "score DOUBLE, " +
"data VARCHAR, "
+ + "CONSTRAINT table_pk PRIMARY KEY (pk, sk))";
+ conn.createStatement().execute(ddl);
+
+ conn = nextConnection(getUrl());
+ conn.createStatement()
+ .execute("CREATE INDEX " + indexName + " ON " + tableName + " (category,
score)");
+
+ conn = nextConnection(getUrl());
+ PreparedStatement stmt = conn.prepareStatement(
+ "UPSERT INTO " + tableName + " (pk, sk, category, score, data) VALUES
(?, ?, ?, ?, ?)");
+ for (int i = 0; i < 20000; i++) {
+ stmt.setString(1, "pk_" + (i % 100));
+ stmt.setLong(2, i);
+ stmt.setString(3, "category_" + (i % 10));
+ stmt.setDouble(4, i);
+ stmt.setString(5, "data_" + i);
+ stmt.execute();
+ }
+ conn.commit();
+
+ conn = nextConnection(getUrl());
+ // category_0 rows have scores: 0, 10, 20, ..., 19990
+ // With score <= 5000 AND (score, pk, sk) > (4990, 'pk_90', 4990):
+ // score=4990 row is (pk_90, 4990) which is NOT > the RVC bound (exact
match)
+ // score=5000 row is (pk_0, 5000) which satisfies both conditions
+ // score=5010+ rows must be excluded by score <= 5000
+ String query =
+ "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score " + "FROM "
+ + tableName + " " + "WHERE category = 'category_0' " + "AND score <=
5000 "
+ + "AND (score, pk, sk) > (4990, 'pk_90', 4990) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_0", 5000, "category_0", 5000.0);
+ assertFalse(rs.next());
+
+ // Verify that score < 5000 returns no row
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' " + "AND
score < 5000 "
+ + "AND (score, pk, sk) > (4990, 'pk_90', 4990) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' " + "AND
score <= 5000 "
+ + "AND (score, pk, sk) > (4990, 'pk_90', 4990) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_0", 5000, "category_0", 5000.0);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' " + "AND
score < 5000 "
+ + "AND (score, pk, sk) > (4990, 'pk_90', 4990) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse("Expected no rows with score < 5000 and DESC ordering",
rs.next());
+
+ // score >= 4980 with RVC upper bound < (5010, 'pk_10', 5010).
+ // category_0 rows with score >= 4980:
+ // score=4980: (pk_80, 4980)
+ // score=4990: (pk_90, 4990)
+ // score=5000: (pk_0, 5000)
+ // score=5010: (pk_10, 5010) -> RVC equal, NOT strictly less -> excluded
+ // Expected 3 rows in ASC order.
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' " + "AND
score >= 4980 "
+ + "AND (score, pk, sk) < (5010, 'pk_10', 5010) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_80", 4980, "category_0", 4980.0);
+ assertRow(rs, "pk_90", 4990, "category_0", 4990.0);
+ assertRow(rs, "pk_0", 5000, "category_0", 5000.0);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' " + "AND
score >= 4980 "
+ + "AND (score, pk, sk) < (5010, 'pk_10', 5010) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_0", 5000, "category_0", 5000.0);
+ assertRow(rs, "pk_90", 4990, "category_0", 4990.0);
+ assertRow(rs, "pk_80", 4980, "category_0", 4980.0);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' "
+ + "AND (score, pk) <= (5000, 'pk_0') " + "AND (score, pk, sk) > (4970,
'pk_70', 4970) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_80", 4980, "category_0", 4980.0);
+ assertRow(rs, "pk_90", 4990, "category_0", 4990.0);
+ assertRow(rs, "pk_0", 5000, "category_0", 5000.0);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' "
+ + "AND (score, pk) <= (5000, 'pk_0') " + "AND (score, pk, sk) > (4970,
'pk_70', 4970) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_0", 5000, "category_0", 5000.0);
+ assertRow(rs, "pk_90", 4990, "category_0", 4990.0);
+ assertRow(rs, "pk_80", 4980, "category_0", 4980.0);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' "
+ + "AND (score, pk) < (5000, 'pk_0') " + "AND (score, pk, sk) > (4970,
'pk_70', 4970) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_80", 4980, "category_0", 4980.0);
+ assertRow(rs, "pk_90", 4990, "category_0", 4990.0);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_0' "
+ + "AND (score, pk) < (5000, 'pk_0') " + "AND (score, pk, sk) > (4970,
'pk_70', 4970) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_90", 4990, "category_0", 4990.0);
+ assertRow(rs, "pk_80", 4980, "category_0", 4980.0);
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testRVCWithAlternatingFixedVarWidthPK() throws Exception {
+ Connection conn = nextConnection(getUrl());
+ String tableName = generateUniqueName();
+
+ String ddl = "CREATE TABLE " + tableName + " (" + "a VARCHAR NOT NULL, " +
"b BIGINT NOT NULL, "
+ + "c VARCHAR NOT NULL, " + "d INTEGER NOT NULL, " + "e VARCHAR NOT NULL,
"
+ + "f BIGINT NOT NULL, " + "val VARCHAR, " + "CONSTRAINT pk PRIMARY KEY
(a, b, c, d, e, f))";
+ conn.createStatement().execute(ddl);
+
+ conn = nextConnection(getUrl());
+ PreparedStatement stmt = conn.prepareStatement(
+ "UPSERT INTO " + tableName + " (a, b, c, d, e, f, val) VALUES (?, ?, ?,
?, ?, ?, ?)");
+ for (int i = 0; i < 50000; i++) {
+ stmt.setString(1, "a_" + (i % 5));
+ stmt.setLong(2, i);
+ stmt.setString(3, "c_" + (i % 7));
+ stmt.setInt(4, i % 100);
+ stmt.setString(5, "e_" + (i % 3));
+ stmt.setLong(6, i);
+ stmt.setString(7, "val_" + i);
+ stmt.execute();
+ if (i % 5000 == 0) {
+ conn.commit();
+ }
+ }
+ conn.commit();
+
+ conn = nextConnection(getUrl());
+ String query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND b >= 24990 AND b <= 25010" + " ORDER BY a, b, c,
d, e, f";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 24990, "c_0", 90, "e_0", 24990);
+ assertRow(rs, "a_0", 24995, "c_5", 95, "e_2", 24995);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25010, "c_6", 10, "e_2", 25010);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName + " WHERE a = 'a_0'
AND b <= 25010"
+ + " AND (b > 24995 OR (b = 24995 AND c > 'c_5'))" + " ORDER BY a, b, c,
d, e, f LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25010, "c_6", 10, "e_2", 25010);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND b <= 25010 AND (b, c) > (24995, 'c_5')"
+ + " ORDER BY a, b, c, d, e, f LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25010, "c_6", 10, "e_2", 25010);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND b <= 25010 AND (b, c, d) > (25000, 'c_3', 0)"
+ + " ORDER BY a, b, c, d, e, f LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25010, "c_6", 10, "e_2", 25010);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND b <= 25010 AND (b, c, d, e) > (25000, 'c_3', 0,
'e_1')"
+ + " ORDER BY a, b, c, d, e, f LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25010, "c_6", 10, "e_2", 25010);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query =
+ "SELECT a, b, c, d, e, f FROM " + tableName + " WHERE a = 'a_0' AND (b,
c) <= (25005, 'c_1')"
+ + " AND (b, c, d) > (24995, 'c_5', 95)" + " ORDER BY a, b, c, d, e, f
LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND (b, c, d) <= (25005, 'c_1', 5)"
+ + " AND (b, c, d, e, f) > (24995, 'c_5', 95, 'e_2', 24995)"
+ + " ORDER BY a, b, c, d, e, f LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND b >= 24995 AND (b, c, d) < (25010, 'c_6', 10)"
+ + " ORDER BY a, b, c, d, e, f LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 24995, "c_5", 95, "e_2", 24995);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND b <= 25010 AND (b, c) > (24995, 'c_5')"
+ + " ORDER BY a ASC, b DESC, c DESC, d DESC, e DESC, f DESC LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25010, "c_6", 10, "e_2", 25010);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND (b, c) <= (25005, 'c_1')" + " AND (b, c, d) >
(24995, 'c_5', 95)"
+ + " ORDER BY a ASC, b DESC, c DESC, d DESC, e DESC, f DESC LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertFalse(rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT a, b, c, d, e, f FROM " + tableName
+ + " WHERE a = 'a_0' AND b >= 24995 AND (b, c, d) < (25010, 'c_6', 10)"
+ + " ORDER BY a ASC, b DESC, c DESC, d DESC, e DESC, f DESC LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "a_0", 25005, "c_1", 5, "e_0", 25005);
+ assertRow(rs, "a_0", 25000, "c_3", 0, "e_1", 25000);
+ assertRow(rs, "a_0", 24995, "c_5", 95, "e_2", 24995);
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testRVCWithBetweenOnIndex() throws Exception {
+ Connection conn = nextConnection(getUrl());
+ String tableName = generateUniqueName();
+ String indexName = generateUniqueName();
+
+ String ddl = "CREATE TABLE " + tableName + " (" + "pk VARCHAR NOT NULL, "
+ + "sk BIGINT NOT NULL, " + "category VARCHAR, " + "score DOUBLE, " +
"data VARCHAR, "
+ + "CONSTRAINT table_pk PRIMARY KEY (pk, sk))";
+ conn.createStatement().execute(ddl);
+
+ conn = nextConnection(getUrl());
+ conn.createStatement()
+ .execute("CREATE INDEX " + indexName + " ON " + tableName + " (category,
score)");
+
+ conn = nextConnection(getUrl());
+ PreparedStatement stmt = conn.prepareStatement(
+ "UPSERT INTO " + tableName + " (pk, sk, category, score, data) VALUES
(?, ?, ?, ?, ?)");
+ for (int i = 0; i < 20000; i++) {
+ stmt.setString(1, "pk_" + (i % 100));
+ stmt.setLong(2, i);
+ stmt.setString(3, "category_" + (i % 10));
+ stmt.setDouble(4, i);
+ stmt.setString(5, "data_" + i);
+ stmt.execute();
+ }
+ conn.commit();
+
+ // category_7 rows have scores: 7, 17, 27, ..., 19997
+ // Scores in [2000, 8000]: 2007, 2017, ..., 7997
+ // With (score, pk, sk) > (7997, 'pk_97', 7997):
+ // score=7997 row is (pk_97, 7997) which is NOT > the RVC bound (exact
match)
+ // Next category_7 score is 8007 which exceeds BETWEEN upper bound of 8000
+ conn = nextConnection(getUrl());
+ String query =
+ "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score " + "FROM "
+ + tableName + " " + "WHERE category = 'category_7' " + "AND score
BETWEEN 2000 AND 8000 "
+ + "AND (score, pk, sk) > (7997, 'pk_97', 7997) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ // Verify that using >= instead of > includes the exact match row (7997,
'pk_97', 7997).
+ conn = nextConnection(getUrl());
+ String queryGte =
+ "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score " + "FROM "
+ + tableName + " " + "WHERE category = 'category_7' " + "AND score
BETWEEN 2000 AND 8000 "
+ + "AND (score, pk, sk) >= (7997, 'pk_97', 7997) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(queryGte);
+ assertRow(rs, "pk_97", 7997, "category_7", 7997.0);
+ assertFalse("Expected only one row since next category_7 score (8007)
exceeds "
+ + "BETWEEN upper bound of 8000.", rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_7' "
+ + "AND score BETWEEN 2000 AND 8000 " + "AND (score, pk, sk) > (7997,
'pk_97', 7997) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse("Expected no rows with > RVC bound and DESC ordering",
rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_7' "
+ + "AND score BETWEEN 2000 AND 8000 " + "AND (score, pk, sk) >= (7997,
'pk_97', 7997) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_97", 7997, "category_7", 7997.0);
+ assertFalse("Expected only one row with >= RVC bound and DESC ordering",
rs.next());
+
+ // BETWEEN with RVC upper bound < (2037, 'pk_37', 2037).
+ // category_7 scores in [2000, 8000]: 2007, 2017, 2027, 2037, ...
+ // score=2007: (2007, 'pk_7', 2007) < (2037, 'pk_37', 2037)? Yes ->
included
+ // score=2017: (2017, 'pk_17', 2017) < (2037, 'pk_37', 2037)? Yes ->
included
+ // score=2027: (2027, 'pk_27', 2027) < (2037, 'pk_37', 2037)? Yes ->
included
+ // score=2037: (2037, 'pk_37', 2037) < (2037, 'pk_37', 2037)? No (equal)
-> excluded
+ // Expected 3 rows in ASC order.
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_7' "
+ + "AND score BETWEEN 2000 AND 8000 " + "AND (score, pk, sk) < (2037,
'pk_37', 2037) "
+ + "ORDER BY category ASC, score ASC, pk ASC, sk ASC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_7", 2007, "category_7", 2007.0);
+ assertRow(rs, "pk_17", 2017, "category_7", 2017.0);
+ assertRow(rs, "pk_27", 2027, "category_7", 2027.0);
+ assertFalse("Expected exactly 3 rows with BETWEEN and RVC < (2037,
'pk_37', 2037)", rs.next());
+
+ conn = nextConnection(getUrl());
+ query = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ pk, sk,
category, score "
+ + "FROM " + tableName + " " + "WHERE category = 'category_7' "
+ + "AND score BETWEEN 2000 AND 8000 " + "AND (score, pk, sk) < (2037,
'pk_37', 2037) "
+ + "ORDER BY category ASC, score DESC, pk DESC, sk DESC " + "LIMIT 100";
+ rs = conn.createStatement().executeQuery(query);
+ assertRow(rs, "pk_27", 2027, "category_7", 2027.0);
+ assertRow(rs, "pk_17", 2017, "category_7", 2017.0);
+ assertRow(rs, "pk_7", 2007, "category_7", 2007.0);
+ assertFalse("Expected exactly 3 rows (DESC) with BETWEEN and RVC < (2037,
'pk_37', 2037)",
+ rs.next());
+ }
+
+ private void assertRow(ResultSet rs, String pk, long sk, String category,
double score)
+ throws SQLException {
+ assertTrue(rs.next());
+ assertEquals(pk, rs.getString("pk"));
+ assertEquals(sk, rs.getLong("sk"));
+ assertEquals(category, rs.getString("category"));
+ assertEquals(score, rs.getDouble("score"), 0.000);
+ }
+
+ private void assertRow(ResultSet rs, String a, long b, String c, int d,
String e, long f)
+ throws SQLException {
+ assertTrue(rs.next());
+ assertEquals(a, rs.getString("a"));
+ assertEquals(b, rs.getLong("b"));
+ assertEquals(c, rs.getString("c"));
+ assertEquals(d, rs.getInt("d"));
+ assertEquals(e, rs.getString("e"));
+ assertEquals(f, rs.getLong("f"));
+ }
+
private StringBuilder generateQueryToTest(int numItemsInClause, String
fullViewName) {
StringBuilder querySb =
new StringBuilder("SELECT OBJECT_ID,OBJECT_DATA2,OBJECT_DATA FROM " +
fullViewName);
diff --git
a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
index 6ecfc7ff01..59bdac5ff6 100644
---
a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
+++
b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
@@ -99,6 +99,7 @@ import org.apache.phoenix.schema.TableRef;
import org.apache.phoenix.schema.types.PChar;
import org.apache.phoenix.schema.types.PDecimal;
import org.apache.phoenix.schema.types.PInteger;
+import org.apache.phoenix.schema.types.PLong;
import org.apache.phoenix.schema.types.PVarchar;
import org.apache.phoenix.util.ByteUtil;
import org.apache.phoenix.util.EnvironmentEdgeManager;
@@ -7878,4 +7879,192 @@ public class QueryCompilerTest extends
BaseConnectionlessQueryTest {
assertTrue(orderBy.getOrderByExpressions().get(3).toString().equals("VALID_CLICK_COUNT"));
}
}
+
+ @Test
+ public void testRVCScanBoundaries1() throws Exception {
+ String tableName = generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ conn.createStatement().execute(
+ "CREATE TABLE " + tableName + " (" + "category VARCHAR NOT NULL, score
DECIMAL NOT NULL, "
+ + "pk VARCHAR NOT NULL, sk BIGINT NOT NULL, "
+ + "CONSTRAINT table_pk PRIMARY KEY (category, score, pk, sk))");
+
+ byte[] cat0 = PVarchar.INSTANCE.toBytes("category_0");
+ byte[] cat7 = PVarchar.INSTANCE.toBytes("category_7");
+ byte[] SEP = new byte[] { QueryConstants.SEPARATOR_BYTE };
+
+ byte[] dec4970 = PDecimal.INSTANCE.toBytes(new BigDecimal("4970"));
+ byte[] dec4980 = PDecimal.INSTANCE.toBytes(new BigDecimal("4980"));
+ byte[] dec4990 = PDecimal.INSTANCE.toBytes(new BigDecimal("4990"));
+ byte[] dec5000 = PDecimal.INSTANCE.toBytes(new BigDecimal("5000"));
+ byte[] dec5010 = PDecimal.INSTANCE.toBytes(new BigDecimal("5010"));
+ byte[] dec2000 = PDecimal.INSTANCE.toBytes(new BigDecimal("2000"));
+ byte[] dec2037 = PDecimal.INSTANCE.toBytes(new BigDecimal("2037"));
+ byte[] dec7997 = PDecimal.INSTANCE.toBytes(new BigDecimal("7997"));
+ byte[] dec8000 = PDecimal.INSTANCE.toBytes(new BigDecimal("8000"));
+
+ byte[] pk0 = PVarchar.INSTANCE.toBytes("pk_0");
+ byte[] pk10 = PVarchar.INSTANCE.toBytes("pk_10");
+ byte[] pk37 = PVarchar.INSTANCE.toBytes("pk_37");
+ byte[] pk70 = PVarchar.INSTANCE.toBytes("pk_70");
+ byte[] pk90 = PVarchar.INSTANCE.toBytes("pk_90");
+ byte[] pk97 = PVarchar.INSTANCE.toBytes("pk_97");
+ byte[] long4970 = PLong.INSTANCE.toBytes(4970L);
+ byte[] long4990 = PLong.INSTANCE.toBytes(4990L);
+ byte[] long5010 = PLong.INSTANCE.toBytes(5010L);
+ byte[] long7997 = PLong.INSTANCE.toBytes(7997L);
+ byte[] long2037 = PLong.INSTANCE.toBytes(2037L);
+
+ String sql = "SELECT * FROM " + tableName + " WHERE category =
'category_0' AND score <= 5000"
+ + " AND (score, pk, sk) > (4990, 'pk_90', 4990)";
+ Scan scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(cat0, SEP, dec4990, SEP, pk90, SEP,
long4990)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(cat0, SEP, dec5000,
SEP)),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName + " WHERE category = 'category_0' AND
score < 5000"
+ + " AND (score, pk, sk) > (4990, 'pk_90', 4990)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(cat0, SEP, dec4990, SEP, pk90, SEP,
long4990)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.concat(cat0, SEP, dec5000),
scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName + " WHERE category = 'category_0' AND
score >= 4980"
+ + " AND (score, pk, sk) < (5010, 'pk_10', 5010)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.concat(cat0, SEP, dec4980, SEP),
scan.getStartRow());
+ assertArrayEquals(ByteUtil.concat(cat0, SEP, dec5010, SEP, pk10, SEP,
long5010),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName + " WHERE category = 'category_0'"
+ + " AND (score, pk) <= (5000, 'pk_0')" + " AND (score, pk, sk) >
(4970, 'pk_70', 4970)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(cat0, SEP, dec4970, SEP, pk70, SEP,
long4970)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(cat0, SEP, dec5000,
SEP, pk0, SEP)),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName + " WHERE category = 'category_0'"
+ + " AND (score, pk) < (5000, 'pk_0')" + " AND (score, pk, sk) > (4970,
'pk_70', 4970)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(cat0, SEP, dec4970, SEP, pk70, SEP,
long4970)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.concat(cat0, SEP, dec5000, SEP, pk0),
scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName
+ + " WHERE category = 'category_7' AND score BETWEEN 2000 AND 8000"
+ + " AND (score, pk, sk) > (7997, 'pk_97', 7997)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(cat7, SEP, dec7997, SEP, pk97, SEP,
long7997)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(cat7, SEP, dec8000,
SEP)),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName
+ + " WHERE category = 'category_7' AND score BETWEEN 2000 AND 8000"
+ + " AND (score, pk, sk) >= (7997, 'pk_97', 7997)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.concat(cat7, SEP, dec7997, SEP, pk97, SEP,
long7997),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(cat7, SEP, dec8000,
SEP)),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName
+ + " WHERE category = 'category_7' AND score BETWEEN 2000 AND 8000"
+ + " AND (score, pk, sk) < (2037, 'pk_37', 2037)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.concat(cat7, SEP, dec2000, SEP),
scan.getStartRow());
+ assertArrayEquals(ByteUtil.concat(cat7, SEP, dec2037, SEP, pk37, SEP,
long2037),
+ scan.getStopRow());
+ }
+ }
+
+ @Test
+ public void testRVCScanBoundaries2() throws Exception {
+ String tableName = generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ conn.createStatement()
+ .execute("CREATE TABLE " + tableName + " ("
+ + "a VARCHAR NOT NULL, b BIGINT NOT NULL, c VARCHAR NOT NULL, "
+ + "d INTEGER NOT NULL, e VARCHAR NOT NULL, f BIGINT NOT NULL, "
+ + "val VARCHAR, CONSTRAINT pk PRIMARY KEY (a, b, c, d, e, f))");
+
+ byte[] a0 = PVarchar.INSTANCE.toBytes("a_0");
+ byte[] SEP = new byte[] { QueryConstants.SEPARATOR_BYTE };
+ byte[] c5 = PVarchar.INSTANCE.toBytes("c_5");
+ byte[] c3 = PVarchar.INSTANCE.toBytes("c_3");
+ byte[] c1 = PVarchar.INSTANCE.toBytes("c_1");
+ byte[] c6 = PVarchar.INSTANCE.toBytes("c_6");
+ byte[] e1 = PVarchar.INSTANCE.toBytes("e_1");
+ byte[] e2 = PVarchar.INSTANCE.toBytes("e_2");
+
+ String sql = "SELECT * FROM " + tableName + " WHERE a = 'a_0' AND b >=
24990 AND b <= 25010";
+ Scan scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(24990L)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25010L))),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName
+ + " WHERE a = 'a_0' AND b <= 25010 AND (b, c) > (24995, 'c_5')";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(24995L), c5, SEP)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25010L))),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName
+ + " WHERE a = 'a_0' AND b <= 25010 AND (b, c, d) > (25000, 'c_3', 0)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25000L),
+ c3, SEP, PInteger.INSTANCE.toBytes(0))), scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25010L))),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName
+ + " WHERE a = 'a_0' AND b <= 25010 AND (b, c, d, e) > (25000, 'c_3',
0, 'e_1')";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25000L),
+ c3, SEP, PInteger.INSTANCE.toBytes(0), e1, SEP)), scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25010L))),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName + " WHERE a = 'a_0' AND (b, c) <=
(25005, 'c_1')"
+ + " AND (b, c, d) > (24995, 'c_5', 95)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(24995L),
+ c5, SEP, PInteger.INSTANCE.toBytes(95))), scan.getStartRow());
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25005L), c1, SEP)),
+ scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName + " WHERE a = 'a_0' AND (b, c, d) <=
(25005, 'c_1', 5)"
+ + " AND (b, c, d, e, f) > (24995, 'c_5', 95, 'e_2', 24995)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(
+ ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(24995L), c5, SEP,
+ PInteger.INSTANCE.toBytes(95), e2, SEP,
PLong.INSTANCE.toBytes(24995L))),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25005L),
+ c1, SEP, PInteger.INSTANCE.toBytes(5))), scan.getStopRow());
+
+ sql = "SELECT * FROM " + tableName
+ + " WHERE a = 'a_0' AND b >= 24995 AND (b, c, d) < (25010, 'c_6', 10)";
+ scan = TestUtil.getOptimizeQueryPlanNoIterator(conn,
sql).getContext().getScan();
+ assertArrayEquals(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(24995L)),
+ scan.getStartRow());
+ assertArrayEquals(ByteUtil.concat(a0, SEP,
PLong.INSTANCE.toBytes(25010L), c6, SEP,
+ PInteger.INSTANCE.toBytes(10)), scan.getStopRow());
+ }
+ }
+
}
