[
https://issues.apache.org/jira/browse/PHOENIX-5570?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16973646#comment-16973646
]
Geoffrey Jacoby commented on PHOENIX-5570:
------------------------------------------
Phoenix has a perf optimization where if it thinks it has the complete PK, it
will issue deletes as HBase point Deletes directly rather than running the
query and deleting the Cells that get returned from the query.
My first guess would be that it's some buggy combination of that and treatment
of nulls.
> Delete fails to delete data with null value in last column of PK. (all
> columns are in PK)
> -----------------------------------------------------------------------------------------
>
> Key: PHOENIX-5570
> URL: https://issues.apache.org/jira/browse/PHOENIX-5570
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.15.1, 4.14.3
> Reporter: Xu Cang
> Priority: Major
>
> Phoenix delete fails to delete row in below scenario:
> All columns are in PK, last PK column has null value in row.
>
>
> {code:java}
> 1. create table:
> CREATE TABLE IF NOT EXISTS TEST.KINGDOMTABLEWITHNULLPK3 (
> TENANT_ID CHAR(15) NOT NULL,
> GLOBAL_PARTY_ID VARCHAR,
> GLOBAL_INPUT_ID VARCHAR,
> CONSTRAINT PK PRIMARY KEY
> (TENANT_ID,
> GLOBAL_PARTY_ID,
> GLOBAL_INPUT_ID DESC
> ) ) MULTI_TENANT=true;
>
> 2 upsert data
> UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK3 (TENANT_ID, GLOBAL_PARTY_ID)
> VALUES('00000000000DEL3','party1');
> 3. delete data
> DELETE from TEST.KINGDOMTABLEWITHNULLPK3 where TENANT_ID='00000000000DEL3'AND
> GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL ;
>
> 4. verify if data is deleted:(in this case, no)
> 0: > select * from TEST.KINGDOMTABLEWITHNULLPK3;
> -------------------------------------------------------------------------------------------------
> TENANT_ID GLOBAL_PARTY_ID
> GLOBAL_INPUT_ID
> -------------------------------------------------------------------------------------------------
> 00000000000DEL3 party1
>
> -------------------------------------------------------------------------------------------------
>
> {code}
> ===================
> While if I add another column in PK, it will work
> {code:java}
> 1. Create table
> CREATE TABLE IF NOT EXISTS TEST.KINGDOMTABLEWITHNULLPK4 (
> TENANT_ID CHAR(15) NOT NULL,
> GLOBAL_PARTY_ID VARCHAR,
> GLOBAL_INPUT_ID VARCHAR,
> TRAN_ID VARCHAR,
> CONSTRAINT PK PRIMARY KEY
> (TENANT_ID,
> GLOBAL_PARTY_ID,
> GLOBAL_INPUT_ID DESC,
> TRAN_ID
> ) ) MULTI_TENANT=true;
>
> 2. Upsert data
> UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK4 (TENANT_ID, GLOBAL_PARTY_ID,TRAN_ID)
> VALUES('00000000000DEL3','party1’,’1’);
>
> 3. Delete data
> delete from TEST.KINGDOMTABLEWITHNULLPK4 where TENANT_ID='00000000000DEL3'AND
> GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL and TRAN_ID=‘1’ ;
> 4. check if data is deleted, in this case, Yes
> select * from TEST.KINGDOMTABLEWITHNULLPK4;
> -----------------------------------------------------------------------------------------------------------------------------------------+
> TENANT_ID GLOBAL_PARTY_ID
> GLOBAL_INPUT_ID TRAN_ID
> -----------------------------------------------------------------------------------------------------------------------------------------+
> -----------------------------------------------------------------------------------------------------------------------------------------+
>
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
