[
https://issues.apache.org/jira/browse/PHOENIX-6662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xinyi Yan updated PHOENIX-6662:
-------------------------------
Description:
Global connection to create a base table and view.
{code:java}
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'; {code}
Tenant connection to create a view and repro the issue
{code:java}
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
|
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
{code}
was:
{code:java}
0: jdbc:phoenix:localhost> CREATE TABLE DUMMY_THREE_PKS (PK1 VARCHAR(15), PK2
DOUBLE(10,3) NOT NULL, PK3 BIGINT NOT NULL CONSTRAINT PK PRIMARY KEY (PK1, PK2
DESC,PK3));
No rows affected (1.309 seconds)
0: jdbc:phoenix:localhost>
0: jdbc:phoenix:localhost> UPSERT INTO DUMMY_THREE_PKS VALUES
('00DXXXXXXXXXXXX',10.0,10);
1 row affected (0.033 seconds)
0: jdbc:phoenix:localhost> UPSERT INTO DUMMY_THREE_PKS VALUES
('00DXXXXXXXXXXXX',20.0,20);
1 row affected (0.003 seconds)
0: jdbc:phoenix:localhost>
0: jdbc:phoenix:localhost>
0: jdbc:phoenix:localhost> SELECT * FROM DUMMY_THREE_PKS;
+-----------------+------------------------------------------+------------------------------------------+
| PK1 | PK2 |
PK3 |
+-----------------+------------------------------------------+------------------------------------------+
| 00DXXXXXXXXXXXX | 20.0 | 20
|
| 00DXXXXXXXXXXXX | 10.0 | 10
|
+-----------------+------------------------------------------+------------------------------------------+
2 rows selected (0.027 seconds)
0: jdbc:phoenix:localhost> DELETE FROM DUMMY_THREE_PKS WHERE (PK1,PK2) IN
(('00DXXXXXXXXXXXX',10.0,10),('00DXXXXXXXXXXXX',20.0,20));
No rows affected (0.013 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM DUMMY_THREE_PKS;
+-----------------+------------------------------------------+------------------------------------------+
| PK1 | PK2 |
PK3 |
+-----------------+------------------------------------------+------------------------------------------+
| 00DXXXXXXXXXXXX | 20.0 | 20
|
| 00DXXXXXXXXXXXX | 10.0 | 10
|
+-----------------+------------------------------------------+------------------------------------------+
2 rows selected (0.012 seconds)
0: jdbc:phoenix:localhost> EXPLAIN DELETE FROM DUMMY_THREE_PKS WHERE (PK1,PK2)
IN (('00DXXXXXXXXXXXX',10.0,10),('00DXXXXXXXXXXXX',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_THREE_PKS
['00DXXXXXXXXXXXX',~20.0] - ['00DXXXXXXXXXXXX',~10.0] | null
| null |
| SERVER FILTER BY FIRST KEY ONLY AND (PK1, PK2) IN
([48,48,68,88,88,88,88,88,88,88,88,88,88,88,88,0,63,203,255,255,255,255,255,254,128,0,0,20],[48,48,68,88,88,88,88,88,88,88,88,88,88,88,88,0,63,
|
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
{code}
> Failed to delete rows when PK has one or more DESC column with IN clause
> ------------------------------------------------------------------------
>
> Key: PHOENIX-6662
> URL: https://issues.apache.org/jira/browse/PHOENIX-6662
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.16.1
> Reporter: Xinyi Yan
> Priority: Critical
>
> Global connection to create a base table and view.
> {code:java}
> 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'; {code}
> Tenant connection to create a view and repro the issue
> {code:java}
> 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
> |
> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
