Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-6916

Cannot handle ranges where start is a prefix of end for desc columns

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Critical
    • Resolution: Fixed
    • 5.2.0, 5.1.3
    • 5.2.0, 5.1.4
    • core
    • None
    • Phoenix now handles ranges where the start key is a prefix of the desc key for descending columns.

    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.

      Attachments

        Issue Links

          Activity

            People

              stoty Istvan Toth
              stoty Istvan Toth
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: