[jira] [Updated] (TRAFODION-1803) Range delete on tables with nullable key columns deletes fewer rows

["Hans Zeller (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-1803?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Hans Zeller updated TRAFODION-1803:
-----------------------------------
    Fix Version/s:     (was: 2.0-incubating)
                   2.1-incubating

> Range delete on tables with nullable key columns deletes fewer rows 
> --------------------------------------------------------------------
>
>                 Key: TRAFODION-1803
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1803
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 1.2-incubating
>            Reporter: Suresh Subbiah
>            Assignee: Suresh Subbiah
>             Fix For: 2.1-incubating
>
>
> When a table has nullable columns in the primary/store by key and these columns have null values, delete and update statements may affect fewer rows than intended.
> For example
> >>cqd allow_nullable_unique_key_constraint 'on' ;
> --- SQL operation complete.
> CREATE TABLE TRAFODION.JIRA.T1
>   (
>     A                                INT DEFAULT NULL SERIALIZED
>   , B                                INT DEFAULT NULL SERIALIZED
>   , PRIMARY KEY (A ASC, B ASC)
>   )
> ;
> --- SQL operation complete.
> >>insert into t1 values (1, null) ;
> --- 1 row(s) inserted.
> >>delete from t1 where a = 1 ;
> --- 0 row(s) deleted.
> >>delete from t1 ;
> --- 0 row(s) deleted.
> >>delete from t1 where a =1 and b is null ;
> --- 1 row(s) deleted.
> >>explain delete from t1 where a =1  ;
> TRAFODION_DELETE ==========================  SEQ_NO 2        NO CHILDREN
> TABLE_NAME ............... TRAFODION.JIRA.T1
> REQUESTS_IN ............. 10
> ROWS/REQUEST ............. 1
> EST_OPER_COST ............ 0.17
> EST_TOTAL_COST ........... 0.17
> DESCRIPTION
>   max_card_est .......... 99
>   fragment_id ............ 0
>   parent_frag ............ (none)
>   fragment_type .......... master
>   iud_type ............... trafodion_delete TRAFODION.JIRA.T1
>   predicate .............. (A = %(1)) and (B = B)
>   begin_key .............. (A = %(1)) and (B = B)
>   end_key ................ (A = %(1)) and (B = B)
>  Similar issue can be seen for update statements too
>  
>  >>CREATE TABLE TRAFODION.JIRA.T2
>   (
>     A                                INT DEFAULT NULL SERIALIZED
>   , B                                INT DEFAULT NULL SERIALIZED
>   , C                                INT DEFAULT NULL SERIALIZED
>   , PRIMARY KEY (A ASC, B ASC)
>   )
> ;+>+>+>+>+>+>+>
> --- SQL operation complete.
> >>
> >>
> >>insert into t2 values (1, null, 3) ;
> --- 1 row(s) inserted.
> >>update t2 set c = 30 where a = 1 ;
> --- 0 row(s) updated.
>  
> TRAFODION_UPDATE ==========================  SEQ_NO 2        NO CHILDREN
> TABLE_NAME ............... TRAFODION.JIRA.T2
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 1
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 0
> DESCRIPTION
>   max_card_est .......... 99
>   fragment_id ............ 0
>   parent_frag ............ (none)
>   fragment_type .......... master
>   iud_type ............... trafodion_update TRAFODION.JIRA.T2
>   new_rec_expr ........... (C assign %(30))
>   predicate .............. (A = %(1)) and (B = B)
>   begin_key .............. (A = %(1)) and (B = B)
>   end_key ................ (A = %(1)) and (B = B)



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)