[PHOENIX-6669] RVC returns a wrong result

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.16.1
• Fix Version/s: 5.2.0, 5.1.4
• Assignee: Gokcen Iskender
• Reporter: Xinyi Yan
• Created: 16/Mar/22 17:29
• Updated: 24/Mar/23 16:58
• Resolved: 03/Oct/22 19:52

Description

CREATE TABLE IF NOT EXISTS DUMMY (
    PK1 VARCHAR NOT NULL,
    PK2 BIGINT NOT NULL,
    PK3 BIGINT NOT NULL,
    PK4 VARCHAR NOT NULL,
    COL1 BIGINT,
    COL2 INTEGER,
    COL3 VARCHAR,
    COL4 VARCHAR,    CONSTRAINT PK PRIMARY KEY
    (
        PK1,
        PK2,
        PK3,
        PK4
    )
);UPSERT INTO DUMMY (PK1, PK4, COL1, PK2, COL2, PK3, COL3, COL4)
            VALUES ('xx', 'xid1', 0, 7, 7, 7, 'INSERT', null);
 

The non-RVC query returns no row, but the RVC query returns a wrong result.

0: jdbc:phoenix:localhost> select PK2
. . . . . . . . . . . . .> from DUMMY
. . . . . . . . . . . . .> where PK1 ='xx'
. . . . . . . . . . . . .> and (PK1 > 'xx' AND PK1 <= 'xx')
. . . . . . . . . . . . .> and (PK2 > 5 AND PK2 <=5)
. . . . . . . . . . . . .> and (PK3 > 2 AND PK3 <=2);
+------------------------------------------+
|                   PK2                    |
+------------------------------------------+
+------------------------------------------+
 No rows selected (0.022 seconds)
0: jdbc:phoenix:localhost> select PK2
. . . . . . . . . . . . .> from DUMMY
. . . . . . . . . . . . .> where (PK1 = 'xx')
. . . . . . . . . . . . .> and (PK1, PK2, PK3) > ('xx', 5, 2)
. . . . . . . . . . . . .> and (PK1, PK2, PK3) <= ('xx', 5, 2);
+------------------------------------------+
|                   PK2                    |
+------------------------------------------+
| 7                                        |
+------------------------------------------+
1 row selected (0.033 seconds) 

0: jdbc:phoenix:localhost> EXPLAIN select PK2 from DUMMY where (PK1 = 'xx') and (PK1, PK2, PK3) > ('xx', 5, 2) and (PK1, PK2, PK3) <= ('xx', 5, 2);
+------------------------------------------+------------------------------------------+------------------------------------------+--+
|                   PLAN                   |              EST_BYTES_READ              |              EST_ROWS_READ               |  |
+------------------------------------------+------------------------------------------+------------------------------------------+--+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER DUMMY ['xx'] | null                                     | null          |
|     SERVER FILTER BY FIRST KEY ONLY      | null                                     | null                                     |  |
+------------------------------------------+------------------------------------------+------------------------------------------+--+
2 rows selected (0.024 seconds) 
0: jdbc:phoenix:localhost> explain select PK2 from DUMMY where PK1 ='xx' and (PK1 > 'xx' AND PK1 <= 'xx') and (PK2 > 5 AND PK2 <=5) and (PK3 > 2 AND PK3 <=2);
+------------------------------------------+------------------------------------------+------------------------------------------+--+
|                   PLAN                   |              EST_BYTES_READ              |              EST_ROWS_READ               |  |
+------------------------------------------+------------------------------------------+------------------------------------------+--+
| DEGENERATE SCAN OVER DUMMY               | null                                     | null                                     |  |
+------------------------------------------+------------------------------------------+------------------------------------------+--+
1 row selected (0.015 seconds)

Issue Links

• links to: GitHub Pull Request #1407