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