phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Xu Cang (Jira)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-5570) Delete fails to delete data with null value in last column of PK. (all columns are in PK)
Date Wed, 13 Nov 2019 19:46:00 GMT

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

Mime
View raw message