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)
