[ https://issues.apache.org/jira/browse/PHOENIX-5570?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Xu Cang updated PHOENIX-5570: ----------------------------- Description: 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} was: 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} > 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 > 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)