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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.2-incubating
• Fix Version/s: 2.4
• Component/s: sql-cmp
• Assignee: Suresh Subbiah
• Reporter: Suresh Subbiah
• Created: 02/Feb/16 23:25
• Updated: 16/Apr/20 02:19

Description

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)