[
https://issues.apache.org/jira/browse/TRAFODION-1803?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Selvaganesan Govindarajan updated TRAFODION-1803:
-------------------------------------------------
Fix Version/s: (was: 2.3)
2.4
> 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
> Priority: Major
> Fix For: 2.4
>
>
> 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
(v8.3.4#803005)
