[PHOENIX-4476] Range scan used for point lookups if filter is not in order of primary keys

• Type: Bug
• Status: Patch Available
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.13.1
• Labels: SFDC
• Assignee: Xu Cang
• Reporter: Mujtaba Chohan
• Created: 19/Dec/17 23:38
• Updated: 08/Apr/19 17:18

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                                      | |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+