[
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}
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;
UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK3 (TENANT_ID, GLOBAL_PARTY_ID)
VALUES('00000000000DEL3','party1');
DELETE from TEST.KINGDOMTABLEWITHNULLPK3 where TENANT_ID='00000000000DEL3'AND
GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL ;
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf> select * from
TEST.KINGDOMTABLEWITHNULLPK3;
-------------------------------------------------------------------------------------------------
TENANT_ID GLOBAL_PARTY_ID
GLOBAL_INPUT_ID
-------------------------------------------------------------------------------------------------
00000000000DEL3 party1
-------------------------------------------------------------------------------------------------
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf>
===================
But if there is one column after the GLOBAL_INPUT_ID column, delete works, as
shown below.
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;
UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK4 (TENANT_ID, GLOBAL_PARTY_ID,TRAN_ID)
VALUES('00000000000DEL3','party1’,’1’);
delete from TEST.KINGDOMTABLEWITHNULLPK4 where TENANT_ID='00000000000DEL3'AND
GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL and TRAN_ID=‘1’ ;
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf> select * from
TEST.KINGDOMTABLEWITHNULLPK4;
-----------------------------------------------------------------------------------------------------------------------------------------+
TENANT_ID GLOBAL_PARTY_ID
GLOBAL_INPUT_ID TRAN_ID
-----------------------------------------------------------------------------------------------------------------------------------------+
-----------------------------------------------------------------------------------------------------------------------------------------+
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf>
{code}
was:
Phoenix delete fails to delete row in below scenario:
All columns are in PK, last PK column has null value in row.
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;
UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK3 (TENANT_ID, GLOBAL_PARTY_ID)
VALUES('00000000000DEL3','party1');
delete from TEST.KINGDOMTABLEWITHNULLPK3 where TENANT_ID='00000000000DEL3'AND
GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL ;
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf> select * from
TEST.KINGDOMTABLEWITHNULLPK3;
+-----------------+------------------------------------------+------------------------------------------+
| TENANT_ID | GLOBAL_PARTY_ID |
GLOBAL_INPUT_ID |
+-----------------+------------------------------------------+------------------------------------------+
| 00000000000DEL3 | party1 |
|
+-----------------+------------------------------------------+------------------------------------------+
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf>
===================
But if there is one column after the GLOBAL_INPUT_ID column, delete works, as
shown below.
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;
UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK4 (TENANT_ID, GLOBAL_PARTY_ID,TRAN_ID)
VALUES('00000000000DEL3','party1’,’1’);
delete from TEST.KINGDOMTABLEWITHNULLPK4 where TENANT_ID='00000000000DEL3'AND
GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL and TRAN_ID=‘1’ ;
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf> select * from
TEST.KINGDOMTABLEWITHNULLPK4;
+-----------------+------------------------------------------+------------------------------------------+------------------------------------------+
| TENANT_ID | GLOBAL_PARTY_ID |
GLOBAL_INPUT_ID | TRAN_ID |
+-----------------+------------------------------------------+------------------------------------------+------------------------------------------+
+-----------------+------------------------------------------+------------------------------------------+------------------------------------------+
0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf>
> 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}
> 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;
>
> UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK3 (TENANT_ID, GLOBAL_PARTY_ID)
> VALUES('00000000000DEL3','party1');
> DELETE from TEST.KINGDOMTABLEWITHNULLPK3 where TENANT_ID='00000000000DEL3'AND
> GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL ;
>
> 0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf> select * from
> TEST.KINGDOMTABLEWITHNULLPK3;
> -------------------------------------------------------------------------------------------------
> TENANT_ID GLOBAL_PARTY_ID
> GLOBAL_INPUT_ID
> -------------------------------------------------------------------------------------------------
> 00000000000DEL3 party1
>
> -------------------------------------------------------------------------------------------------
> 0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf>
>
>
> ===================
> But if there is one column after the GLOBAL_INPUT_ID column, delete works, as
> shown below.
>
> 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;
>
> UPSERT INTO TEST.KINGDOMTABLEWITHNULLPK4 (TENANT_ID, GLOBAL_PARTY_ID,TRAN_ID)
> VALUES('00000000000DEL3','party1’,’1’);
>
> delete from TEST.KINGDOMTABLEWITHNULLPK4 where TENANT_ID='00000000000DEL3'AND
> GLOBAL_PARTY_ID='party1' AND GLOBAL_INPUT_ID is NULL and TRAN_ID=‘1’ ;
> 0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf> select * from
> TEST.KINGDOMTABLEWITHNULLPK4;
> -----------------------------------------------------------------------------------------------------------------------------------------+
> TENANT_ID GLOBAL_PARTY_ID
> GLOBAL_INPUT_ID TRAN_ID
> -----------------------------------------------------------------------------------------------------------------------------------------+
> -----------------------------------------------------------------------------------------------------------------------------------------+
> 0: jdbc:phoenix:perf1hdaas-mnds2-1-prd.eng.sf>
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
