[jira] [Created] (PHOENIX-7762) Incorrect index scan range for RVC with overlapping constraints

[Palash Chauhan (Jira)]

Palash Chauhan created PHOENIX-7762:
---------------------------------------

             Summary: Incorrect index scan range for RVC with overlapping 
constraints
                 Key: PHOENIX-7762
                 URL: https://issues.apache.org/jira/browse/PHOENIX-7762
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 5.3.0
            Reporter: Palash Chauhan


 

Create table and index
{code:java}
CREATE TABLE test_table (
    "pk VARCHAR NOT NULL,
    "sk BIGINT NOT NULL,
    "category VARCHAR,
    "score DOUBLE,
    "data VARCHAR,
    "CONSTRAINT table_pk PRIMARY KEY (pk, sk));

CREATE INDEX test_index on test_table (category, score);{code}
 

 

Upsert data (pk_i%100, i, category_i%10, i, data_i)
{code:java}
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(); {code}

Scan index from ('category_0', 4990, 'pk_90', 4990) with upper bound of 5000 on 
score, this kind of query can show up if client is paginating on rows with 
category_0 and score<=5000 in chunks. 
{code:java}
SELECT /*+ INDEX(N000001 N000001_IDX) */ pk, sk, category, score 
FROM N000001 
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{code}
This query should return only 1 row ('category_0', 5000, 'pk_0', 5000) but 
instead returns 10 rows. 
{code:java}
(category_0, 5000.0, pk_0, 5000)
(category_0, 5010.0, pk_10, 5010)
(category_0, 5020.0, pk_20, 5020)
(category_0, 5030.0, pk_30, 5030)
(category_0, 5040.0, pk_40, 5040)
(category_0, 5050.0, pk_50, 5050)
(category_0, 5060.0, pk_60, 5060)
(category_0, 5070.0, pk_70, 5070)
(category_0, 5080.0, pk_80, 5080)
(category_0, 5090.0, pk_90, 5090){code}
 

 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)