[
https://issues.apache.org/jira/browse/TRAFODION-14?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14605030#comment-14605030
]
Selvaganesan edited comment on TRAFODION-14 at 6/29/15 1:56 AM:
----------------------------------------------------------------
Yes. The plan for either upsert or merge is given below:
>>prepare s2 from upsert into test1 select * from test2;
--- SQL command prepared.
>>
>>
>>explain options 'f' s2 ;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
7 . 8 root x 2.00E+002
1 6 7 tuple_flow 2.00E+002
2 5 6 nested_join 2.00E+000
3 4 5 blocked_union 2.00E+000
. . 4 trafodion_insert IDX1 1.00E+000
. . 3 trafodion_delete IDX1 1.00E+000
. . 2 trafodion_merge TEST1 1.00E+000
. . 1 trafodion_scan TEST2 1.00E+002
--- SQL operation complete.
Blocked_union operator broadcasts the queue entries to both left and right
child. So, any child can be taken up for execution in any order. It is
important to pass the time stamp from the selected row to the delete operator.
Also, it would enable to extend the async operation to these kind of operators
also.
When the Trafodion row is created from hbase key values for a given row id,
take the most recent time stamp of any key value and pass it to the delete
operator.
was (Author: selvag):
Yes. The plan for either upsert or merge is given below:
>>prepare s2 from upsert into test1 select * from test2;
--- SQL command prepared.
>>
>>
>>explain options 'f' s2 ;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
7 . 8 root x 2.00E+002
1 6 7 tuple_flow 2.00E+002
2 5 6 nested_join 2.00E+000
3 4 5 blocked_union 2.00E+000
. . 4 trafodion_insert IDX1 1.00E+000
. . 3 trafodion_delete IDX1 1.00E+000
. . 2 trafodion_merge TEST1 1.00E+000
. . 1 trafodion_scan TEST2 1.00E+002
--- SQL operation complete.
Blocked_union operator sends broadcasts the queue entries to both left and
right child. So, any child can be taken up for execution in any order. It is
important to pass the time stamp from the selected row to the delete operator.
Also, it would enable to extend the async operation to these kind of operators
also.
When the Trafodion row is created from hbase key values for a given row id,
take the most recent time stamp of any key value and pass it to the delete
operator.
> upsert or merge into a table with indexes can result in inconsistency between
> index and table
> ---------------------------------------------------------------------------------------------
>
> Key: TRAFODION-14
> URL: https://issues.apache.org/jira/browse/TRAFODION-14
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Selvaganesan
> Priority: Critical
>
> Steps to reproduce this problem
> set schema seabase ;
> 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.TEST2
> (
> A INT NO DEFAULT NOT NULL NOT DROPPABLE
> , B INT DEFAULT NULL
> , PRIMARY KEY (A ASC)
> )
> ;
> insert into test2 values
> (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10) ;
> prepare s2 from upsert into test1 select * from test2;
> 1) execute s2;
> 2) select * from test1 ;
> If you repeat steps 1 and 2 more than once, at times fewer than 10 rows are
> selected from the step 2.
> In this particular case select * from test1 involves index scan plan.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
