[jira] [Updated] (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:all-tabpanel ]

David Wayne Birdsall updated TRAFODION-2822:
--------------------------------------------
    Description: 
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.


> 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)