[
https://issues.apache.org/jira/browse/TRAFODION-1464?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14715451#comment-14715451
]
Suresh Subbiah commented on TRAFODION-1464:
-------------------------------------------
This change was merged today with this commit.
https://github.com/apache/incubator-trafodion/commit/5928f31a1e20c70a528101487def3db0ef0ade9c
> Upsert into identity by default table with index can cause inconsistency
> ------------------------------------------------------------------------
>
> Key: TRAFODION-1464
> URL: https://issues.apache.org/jira/browse/TRAFODION-1464
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.0-incubating
> Reporter: Suresh Subbiah
> Assignee: Suresh Subbiah
> Fix For: 2.0-incubating
>
> Original Estimate: 24h
> Remaining Estimate: 24h
>
> create table temp (a largeint generated by default as identity not null not
> droppable, b int, primary key(a)) ;
> create index tempidx1 on temp(b) ;
> upsert into temp values (1,1) ;
> upsert into temp(b) values (1) ;
> Index will now have 2 rows, while base table will only have one row, assuming
> that the identity value generated is 1.
> The problem was that in the compiler we assumed that for the second upsert
> statement, since the system was generating the identity value that this was
> equivalent to the syskey case and no duplicate value was possible. However as
> can be seen from this example this assumption is incorrect. The fix is is to
> treat identity as default column like any other user column. Identity always
> column can treated similar to syskey (i.e. upsert is not transformed to merge)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
