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

Scan range is wrong when query desc columns

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Critical
    • Resolution: Fixed
    • 5.1.3
    • 5.2.0, 5.1.4
    • None
    • None

    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)
      

      Attachments

        Issue Links

          Activity

            People

              vjasani Viraj Jasani
              fanyunbojerry fanartoria
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: