Suresh Subbiah created TRAFODION-1803: -----------------------------------------
Summary: 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.0-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)