[jira] [Commented] (TRAFODION-1465) MERGE with select or tuple list on source side, with index causes inconsistency

["Suresh Subbiah (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-1465?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14715452#comment-14715452 ] 

Suresh Subbiah commented on TRAFODION-1465:
-------------------------------------------

This change was merged today with this commit.
https://github.com/apache/incubator-trafodion/commit/5928f31a1e20c70a528101487def3db0ef0ade9c

> MERGE with select or tuple list on source side, with index causes inconsistency
> -------------------------------------------------------------------------------
>
>                 Key: TRAFODION-1465
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1465
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-general
>    Affects Versions: 0.6 (pre-incubation)
>            Reporter: Suresh Subbiah
>            Assignee: Suresh Subbiah
>             Fix For: 2.0-incubating
>
>   Original Estimate: 48h
>  Remaining Estimate: 48h
>
> -- Merge with non veg on source side and merge goes to not matched clause
> -- does not affect upsert since we coded around it by breaking VEG
> merge into test1 using (values (1,1),(2,2)) z(a,b) on test1.a = z.a
> when matched then update set b = z.b
> when not matched then insert values (z.a,z.b) ;
> set parserflags 1
> select * from table(index_table idx1) ; 
> -- index should have corrupt values for column a.
> merge into upsertx using
> (select 10*num, 10*num+200, 10*num+200
> from (select e0
>       from (values (0)) seed(s)
>       transpose 0,1,2,3,4,5,6,7,8,9 as e0) t(num)) z(id,a,b)
> on upsertx.id = z.id
> when matched then update set (a,b) = (z.a,z.b)
> when not matched then insert values (z.id, z.a, z.b) ;
> -- index should have corrupt values for column id.
> --Tables needed
> CREATE TABLE TRAFODION.SEABASE.TEST1
>   (
>     A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
>   , B                                INT DEFAULT NULL
>   , PRIMARY KEY (A ASC)
>   ) 
> ;
> CREATE INDEX IDX1 ON TRAFODION.SEABASE.TEST1
>   (
>     B ASC
>   )
> ;
> CREATE TABLE TRAFODION.SEABASE.UPSERTX
>   (
>     ID                               INT NO DEFAULT NOT NULL NOT DROPPABLE
>   , A                                INT DEFAULT NULL 
>   , B                                INT DEFAULT NULL
>   , PRIMARY KEY (ID ASC)
>   )
> ;
> CREATE INDEX UPSERTIX1 ON TRAFODION.SEABASE.UPSERTX
>   (
>     A ASC
>   )
> ;
> Problem has been resolved by breaking VEG formation between OLD and NEW values. The issue was that in fetchUpdateExpr for merge we would get the OLD values from the tupp and place it in up queue for index to consume. But if the merge went through the insert branch (when not matched) there is no OLD value, only a NEW value.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)