[
https://issues.apache.org/jira/browse/PHOENIX-7762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Viraj Jasani updated PHOENIX-7762:
----------------------------------
Fix Version/s: 5.4.0
5.3.1
> Incorrect scan boundary when slot span exceeds actual bound in composite key
> ----------------------------------------------------------------------------
>
> 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
> Assignee: Viraj Jasani
> Priority: Major
> Fix For: 5.4.0, 5.3.1
>
>
> 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}
> Query 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(test_table test_index) */ pk, sk, category, score
> FROM test_table
> 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}
> Query index from ('category_7', 7997, 'pk_97', 7997) with lower bound of 2000
> and upper bound of 8000 on score
> {code:java}
> SELECT /*+ INDEX(test_table test_index) */ pk, sk, category, score
> FROM test_table
> 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{code}
> This query should not return anything but returns 10 rows.
> {code:java}
> (category_7, 8007.0, pk_7, 8007)
> (category_7, 8017.0, pk_17, 8017)
> (category_7, 8027.0, pk_27, 8027)
> (category_7, 8037.0, pk_37, 8037)
> (category_7, 8047.0, pk_47, 8047)
> (category_7, 8057.0, pk_57, 8057)
> (category_7, 8067.0, pk_67, 8067)
> (category_7, 8077.0, pk_77, 8077)
> (category_7, 8087.0, pk_87, 8087)
> (category_7, 8097.0, pk_97, 8097) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
