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

Range scan used for point lookups if filter is not in order of primary keys

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Patch Available
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.13.1
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      DROP TABLE TEST;
      
      CREATE TABLE IF NOT EXISTS TEST (
          PK1 CHAR(1) NOT NULL,
          PK2 VARCHAR NOT NULL,
          PK3 VARCHAR NOT NULL,
          PK4 UNSIGNED_LONG NOT NULL,
          PK5 VARCHAR NOT NULL,
          V1 VARCHAR,
          V2 VARCHAR,
          V3 UNSIGNED_LONG
          CONSTRAINT state_pk PRIMARY KEY (
                PK1,
                PK2,
                PK3,
                PK4,
                PK5
          )
      );
      
      // Incorrect explain plan with un-ordered PKs
      EXPLAIN SELECT V1 FROM TEST WHERE (PK1, PK5, PK2, PK3, PK4) IN (('A', 'E', 'N', 'T', 3), ('A', 'Y', 'G', 'T', 4)); 
      
      +------------------------------------------+------------------------------------------+------------------------------------------+---------+
      |                   PLAN                   |              EST_BYTES_READ              |              EST_ROWS_READ               |         |
      +------------------------------------------+------------------------------------------+------------------------------------------+---------+
      | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST ['A'] | null                                     | null                   |
      |     SERVER FILTER BY (PK1, PK5, PK2, PK3, PK4) IN ([65,69,0,78,0,84,0,0,0,0,0,0,0,0,3],[65,89,0,71,0,84,0,0,0,0,0,0,0,0,4]) | null       |
      +------------------------------------------+------------------------------------------+------------------------------------------+---------+
      
      // Correct explain plan with PKs in order
      EXPLAIN SELECT V1 FROM TEST WHERE (PK1,PK2,PK3,PK4,PK5) IN (('A', 'E', 'N',3, 'T'),('A', 'Y', 'G', 4, 'T')); 
      
      +------------------------------------------+------------------------------------------+------------------------------------------+---------+
      |                   PLAN                   |              EST_BYTES_READ              |              EST_ROWS_READ               |         |
      +------------------------------------------+------------------------------------------+------------------------------------------+---------+
      | CLIENT 1-CHUNK 2 ROWS 712 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 2 KEYS OVER TEST | 712                                      | |
      +------------------------------------------+------------------------------------------+------------------------------------------+---------+
      
      

        Attachments

          Activity

            People

            • Assignee:
              xucang Xu Cang
              Reporter:
              mujtabachohan Mujtaba Chohan
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated: