[jira] [Commented] (TRAFODION-2822) MERGE on a view defined using [first n] or [any n] does not work

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

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

David Wayne Birdsall commented on TRAFODION-2822:
-------------------------------------------------

There are two issues that come to mind with this particular case.

1. Should [first n] / [any n] views be updatable at all? One can make a strong case that they should not: INSERT has clear semantics though the row inserted might not show up in a subsequent select on the view. UPDATE and DELETE are non-deterministic. Their action depends on what rows make the lucky [first n] / [any n] cut. An industry survey suggests that updatable [first n] / [any n] views are not common, and when allowed have non-trivial restrictions. So, perhaps we should change Trafodion to make such views non-updatable.

2. The reason that the MERGE fails is that view composition is changing a "merge" query node into a tree (tuple_flow (topn (scan)) (merge)). That is, the scan activity is separated out from the merge node. The MERGE run-time implementation requires that the scan be done in the merge node itself; the not matched semantics are triggered from there. So, if the tuple_flow transformation happens in the compiler, the merge node never gets activated at run time when the scan is empty. So, the insert logic never gets triggered. So, the issue here is, are there other circumstances in the compiler (other than [first n] / [any n] view composition) that cause this transformation to take place? If so, those should be corrected.

> MERGE on a view defined using [first n] or [any n] does not work
> ----------------------------------------------------------------
>
>                 Key: TRAFODION-2822
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2822
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.3-incubating
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>
> The following script produces incorrect results:
> drop schema if exists mytest cascade;
> create schema mytest;
> set schema mytest;
> create table t (c1 int not null primary key, c2 int);
> insert into t values (1,1),(2,2),(3,3);
> create view v1 as select [first 10] * from t;
> create view v2 as select [any 10] * from t;
> prepare x1 from merge into v1 on c1=-1 when not matched then insert values (5,5);
> explain options 'f' x1;
> execute x1;
> prepare x2 from merge into v2 on c1=-1 when not matched then insert values (6,6);
> explain options 'f' x2;
> execute x2;
> prepare x3 from merge into t on c1=-1 when not matched then insert values (4,4);
> explain options 'f' x3;
> execute x3;
> select * from v1 order by 1;
> select * from v2 order by 1;
> select * from t order by 1;
> The SELECTs return rows (1,1), (2, 2), (3, 3), (4, 4), which suggests that the INSERT action of statements x1 and x2 did not happen when it should.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)