[
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)
