Palash Chauhan created PHOENIX-7770:
---------------------------------------
Summary: Incorrect index scan range when using greater-than RVC
and equality on leading key in RVC
Key: PHOENIX-7770
URL: https://issues.apache.org/jira/browse/PHOENIX-7770
Project: Phoenix
Issue Type: Bug
Affects Versions: 5.3.0
Reporter: Palash Chauhan
{code:java}
String ddl = "CREATE TABLE " + tableName
+ " (HK VARBINARY_ENCODED NOT NULL, "
+ " SK VARBINARY_ENCODED NOT NULL, "
+ " GSI_HK VARBINARY_ENCODED, "
+ " GSI_SK VARBINARY_ENCODED, "
+ " DATA VARCHAR, "
+ " CONSTRAINT pk PRIMARY KEY(HK, SK))";{code}
{code:java}
String indexDdl = "CREATE UNCOVERED INDEX " + indexName + " ON " + tableName +
" (GSI_HK, GSI_SK)";
{code}
{code:java}
// Insert 3 rows with same GSI_HK and GSI_SK
"UPSERT INTO " + tableName + " (HK, SK, GSI_HK, GSI_SK, DATA) VALUES (?, ?, ?,
?, ?)");
// Common values
byte[] gsiHk = new byte[]{0x00, 0x00, 0x00, 0x0A};
byte[] gsiSk = "1".getBytes();
// Row 1: HK=[0x0B,0x01], SK="1" - query should start from this row excluded
byte[] hk1 = new byte[]{0x0B, 0x01};
byte[] sk1 = "1".getBytes();
upsert.setBytes(1, hk1);
upsert.setBytes(2, sk1);
upsert.setBytes(3, gsiHk);
upsert.setBytes(4, gsiSk);
upsert.setString(5, "row1-should-be-excluded");
upsert.execute();
// Row 2: HK=[0x0B,0x01], SK="2" - should be INCLUDED (SK "2" > "1")
byte[] hk2 = new byte[]{0x0B, 0x01};
byte[] sk2 = "2".getBytes();
upsert.setBytes(1, hk2);
upsert.setBytes(2, sk2);
upsert.setBytes(3, gsiHk);
upsert.setBytes(4, gsiSk);
upsert.setString(5, "row2-include");
upsert.execute();
// Row 3: HK=[0x0B,0x02], SK="1" - should be INCLUDED (HK [0x0B,0x02] >
[0x0B,0x01])
byte[] hk3 = new byte[]{0x0B, 0x02};
byte[] sk3 = "1".getBytes();
upsert.setBytes(1, hk3);
upsert.setBytes(2, sk3);
upsert.setBytes(3, gsiHk);
upsert.setBytes(4, gsiSk);
upsert.setString(5, "row3-include");
upsert.execute();
conn.commit();{code}
{code:java}
String q = "SELECT /*+ INDEX(" + tableName + " " + indexName + ") */ "
+ "HK, SK, DATA FROM " + tableName
+ " WHERE GSI_HK = ?"
+ " AND GSI_SK = ?"
+ " AND (GSI_SK, HK, SK) > (?, ?, ?)"
+ " ORDER BY GSI_SK, HK, SK";
PreparedStatement stmt1 = conn.prepareStatement(q);
stmt1.setBytes(1, gsiHk);
stmt1.setBytes(2, gsiSk);
stmt1.setBytes(3, gsiSk);
stmt1.setBytes(4, hk1);
stmt1.setBytes(5, sk1);
ResultSet rs1 = stmt1.executeQuery(); {code}
This query should skip the first row due to the RVC constraint but it does not.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
