[PHOENIX-6662] Failed to delete rows when PK has one or more DESC column with IN clause

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 4.16.1
• Fix Version/s: 5.2.0, 5.1.3
• Assignee: Gokcen Iskender
• Reporter: Xinyi Yan
• Created: 04/Mar/22 02:09
• Updated: 03/Oct/22 19:58
• Resolved: 15/Mar/22 03:31

Description

Global connection to create a base table and view.

CREATE TABLE IF NOT EXISTS DUMMY.BASE (TETNANT_ID CHAR(15) NOT NULL, PREFIX CHAR(3) NOT NULL, COL1 DATE, COL2 CHAR(15), COL3 DATE, COL4 CHAR(15), COL5 DATE CONSTRAINT PK PRIMARY KEY ( TETNANT_ID, PREFIX ) ) MULTI_TENANT=true;

CREATE VIEW IF NOT EXISTS DUMMY.GLOBAL_VIEW  (PK1 DECIMAL(12, 3) NOT NULL, PK2 BIGINT NOT NULL, COL6 CHAR(15) , COL7 DATE, COL8 BOOLEAN, COL9 CHAR(15), COL10 VARCHAR, COL11 VARCHAR CONSTRAINT PKVIEW PRIMARY KEY (PK1 DESC, PK2)) AS SELECT * FROM DUMMY.BASE WHERE PREFIX = '01A'; 

Tenant connection to create a view and repro the issue

0: jdbc:phoenix:localhost> CREATE VIEW DUMMY."0ph" AS SELECT * FROM DUMMY.GLOBAL_VIEW;
No rows affected (0.055 seconds)
0: jdbc:phoenix:localhost> UPSERT INTO DUMMY."0ph" (PK1,PK2) VALUES (10.0,10);
1 row affected (0.038 seconds)
0: jdbc:phoenix:localhost> UPSERT INTO DUMMY."0ph" (PK1,PK2) VALUES (20.0,20);
1 row affected (0.008 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM DUMMY."0ph";
+--------+-------------------------+-----------------+-------------------------+-----------------+-------------------------+--------------+------------------------------------------+-----------------+-------------------------+------------------------------------------+-----------------+----------------------+
| PREFIX |          COL1           |      COL2       |          COL3           |      COL4       |          COL5           |     PK1      |                   PK2                    |      COL6       |          COL7           |                   COL8                   |      COL9       |                  COL |
+--------+-------------------------+-----------------+-------------------------+-----------------+-------------------------+--------------+------------------------------------------+-----------------+-------------------------+------------------------------------------+-----------------+----------------------+
| 01A    | null                    |                 | null                    |                 | null                    | 2E+1         | 20                                       |                 | null                    |                                          |                 |                      |
| 01A    | null                    |                 | null                    |                 | null                    | 1E+1         | 10                                       |                 | null                    |                                          |                 |                      |
+--------+-------------------------+-----------------+-------------------------+-----------------+-------------------------+--------------+------------------------------------------+-----------------+-------------------------+------------------------------------------+-----------------+----------------------+
2 rows selected (0.035 seconds)
0: jdbc:phoenix:localhost> DELETE FROM DUMMY."0ph" WHERE (PK1,PK2) IN ((10.0,10),(20.0,20));
No rows affected (0.024 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM DUMMY."0ph";
+--------+-------------------------+-----------------+-------------------------+-----------------+-------------------------+--------------+------------------------------------------+-----------------+-------------------------+------------------------------------------+-----------------+----------------------+
| PREFIX |          COL1           |      COL2       |          COL3           |      COL4       |          COL5           |     PK1      |                   PK2                    |      COL6       |          COL7           |                   COL8                   |      COL9       |                  COL |
+--------+-------------------------+-----------------+-------------------------+-----------------+-------------------------+--------------+------------------------------------------+-----------------+-------------------------+------------------------------------------+-----------------+----------------------+
| 01A    | null                    |                 | null                    |                 | null                    | 2E+1         | 20                                       |                 | null                    |                                          |                 |                      |
| 01A    | null                    |                 | null                    |                 | null                    | 1E+1         | 10                                       |                 | null                    |                                          |                 |                      |
+--------+-------------------------+-----------------+-------------------------+-----------------+-------------------------+--------------+------------------------------------------+-----------------+-------------------------+------------------------------------------+-----------------+----------------------+
2 rows selected (0.021 seconds)
0: jdbc:phoenix:localhost> EXPLAIN DELETE FROM DUMMY."0ph" WHERE (PK1,PK2) IN ((10.0,10),(20.0,20));
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
|                   PLAN                   |              EST_BYTES_READ              |              EST_ROWS_READ               |               EST_INFO_TS                |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
| DELETE ROWS SERVER SELECT                | null                                     | null                                     | null                                     |
| CLIENT 1-CHUNK PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER DUMMY.BASE ['00DXXXXXXXXXXXX','01A',~19.977] - ['00DXXXXXXXXXXXX','01A',~9.977] | null                                     | null                                     | null                                     |
|     SERVER FILTER BY FIRST KEY ONLY      | null                                     | null                                     | null                                     |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+ 

Issue Links

• links to: GitHub Pull Request #1402
• links to: GitHub Pull Request #1509