[
https://issues.apache.org/jira/browse/PHOENIX-7762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Palash Chauhan updated PHOENIX-7762:
------------------------------------
Description:
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}
was:
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}
> 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
> Priority: Major
>
> 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)
