[PHOENIX-6960] Scan range is wrong when query desc columns

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 5.1.3
• Fix Version/s: 5.2.0, 5.1.4
• Assignee: Viraj Jasani
• Reporter: fanartoria
• Created: 19/May/23 03:28
• Updated: 05/Jun/24 02:12
• Resolved: 14/Sep/23 19:23

Description

Ways to reproduce

0: jdbc:phoenix:> create table sts(id integer primary key, name varchar, type integer, status integer);
No rows affected (1.259 seconds)
0: jdbc:phoenix:> create index sts_name_desc on sts(status, type desc, name desc);
^[[ANo rows affected (6.376 seconds)
0: jdbc:phoenix:> create index sts_name_asc on sts(type desc, name) include (status);
No rows affected (6.377 seconds)
0: jdbc:phoenix:> upsert into sts values(1, 'test10.txt', 1, 1);
1 row affected (0.026 seconds)
0: jdbc:phoenix:>
0: jdbc:phoenix:>
0: jdbc:phoenix:> explain select * from sts where type = 1 and name like 'test10%';
+------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
|                                                 PLAN                                                 | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER STS_NAME_ASC [~1,'test10'] - [~1,'test11'] | null           | null          | null        |
+------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
1 row selected (0.023 seconds)
0: jdbc:phoenix:> select * from sts where type = 1 and name like 'test10%';
+----+------------+------+--------+
| ID |    NAME    | TYPE | STATUS |
+----+------------+------+--------+
| 1  | test10.txt | 1    | 1      |
+----+------------+------+--------+
1 row selected (0.033 seconds)
0: jdbc:phoenix:> explain select * from sts where status = 1 and type = 1 and name like 'test10%';
+-------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
|                                                    PLAN                                                     | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+-------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER STS_NAME_DESC [1,~1,~'test10'] - [1,~1,~'test1/'] | null           | null          | null        |
|     SERVER FILTER BY FIRST KEY ONLY AND "NAME" LIKE 'test10%'                                               | null           | null          | null        |
+-------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
2 rows selected (0.022 seconds)
0: jdbc:phoenix:> select * from sts where status = 1 and type = 1 and name like 'test10%';
+----+------+------+--------+
| ID | NAME | TYPE | STATUS |
+----+------+------+--------+
+----+------+------+--------+
No rows selected (0.04 seconds)

Issue Links

• is related to: PHOENIX-6916 Cannot handle ranges where start is a prefix of end for desc columns
• links to: GitHub Pull Request #1663
• links to: GitHub Pull Request #1668