[PHOENIX-6916] Cannot handle ranges where start is a prefix of end for desc columns

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 5.2.0, 5.1.3
• Fix Version/s: 5.2.0, 5.1.4
• Component/s: core
• Release Note: Phoenix now handles ranges where the start key is a prefix of the desc key for descending columns.
• Assignee: Istvan Toth
• Reporter: Istvan Toth
• Created: 24/Mar/23 09:20
• Updated: 23/May/23 04:33
• Resolved: 24/Apr/23 05:21

Description

For some non-empty timestamp ranges, Phoenix generates a query plan with a degenerate scan if DESC index is created on the timestamp field:

create table data (id varchar primary key, ts timestamp);
create index data_idx on data(ts desc);

0: jdbc:phoenix:> explain select id, ts from data where ts >= TIMESTAMP '2023-02-23 13:30:00'  and ts < TIMESTAMP '2023-02-23 13:40:00'; 
+-------------------------------+----------------+---------------+-------------+
|             PLAN              | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+-------------------------------+----------------+---------------+-------------+
| DEGENERATE SCAN OVER DATA_IDX | null           | null          | null        |
+-------------------------------+----------------+---------------+-------------+
1 row selected (0.012 seconds)

0: jdbc:phoenix:> explain select id, ts from data where ts >= TIMESTAMP '2023-02-23 13:30:00'  and ts < TIMESTAMP '2023-02-23 13:50:00';
+----------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
|                                                      PLAN                                                      | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+----------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER DATA_IDX [~1,677,160,200,000] - [~1,677,159,000,000] | null           | null          | null        |
|     SERVER FILTER BY FIRST KEY ONLY                                                                            | null           | null          | null        |
+----------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
2 rows selected (0.009 seconds)

0: jdbc:phoenix:localhost:59231> explain select /*+NO_INDEX*/ id, ts from data where ts >= TIMESTAMP '2023-02-23 13:30:00'  and ts < TIMESTAMP '2023-02-23 13:40:00';
+---------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
|                                                     PLAN                                                      | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+---------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER DATA                                                 | null           | null          | null        |
|     SERVER FILTER BY (TS >= TIMESTAMP '2023-02-23 13:30:00.000' AND TS < TIMESTAMP '2023-02-23 13:40:00.000') | null           | null          | null        |
+---------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+

Actually, the problem is much more basic:

create table ascpk (k varchar primary key); 
create table descpk (k varchar primary key desc);
upsert into ascpk values ('a');
upsert into ascpk values ('aa');
upsert into ascpk values ('aaa');
upsert into ascpk values ('aaab');
//Same for descpk 
select * from ascpk;
+------+
|  K   |
+------+
| a    |
| aa   |
| aaa  |
| aaab |
+------+
4 rows selected (0.035 seconds)
select * from descpk;
+------+
|  K   |
+------+
| aaab |
| aaa  |
| aa   |
| a    |
+------+
select * from ascpk where k between 'a' and 'aaa';
+-----+
|  K  |
+-----+
| a   |
| aa  |
| aaa |
+-----+  
3 rows selected (0.026 seconds) select * from descpk where k between 'a' and 'aaa';
+---+
| K |
+---+
+---+
No rows selected (0.022 seconds) 

The inversion logic for descending keys completely breaks down when we try to use it for keyranges where start key is a prefix of the end key.

Issue Links

• is related to: PHOENIX-2067 Sort order incorrect for variable length DESC columns
• relates to: PHOENIX-6960 Scan range is wrong when query desc columns
• links to: GitHub Pull Request #1588