[
https://issues.apache.org/jira/browse/TRAFODION-2822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16288227#comment-16288227
]
ASF GitHub Bot commented on TRAFODION-2822:
-------------------------------------------
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1316#discussion_r156487426
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -6995,11 +6995,6 @@ NABoolean RelRoot::isUpdatableBasic(NABoolean isView,
// QSTUFF
{
- // if child is a FirstN node, skip it.
- if ((child(0)->castToRelExpr()->getOperatorType() == REL_FIRST_N) &&
- (child(0)->child(0)))
- scan = (Scan *)child(0)->child(0)->castToRelExpr();
- else
scan = (Scan *)child(0)->castToRelExpr();
--- End diff --
Thanks, Suresh, for the pointer. While trying to go through the
RelRoot::preCodeGen() logic and cause a FirstN node to be inserted, I ran into
this bug:
1. I created a table t1 with six rows.
2. I did "create view v1 as select [first 5] * from t1 order by a;" (I
wanted to see if the order by clause was permitted and if so would it
circumvent the updatable view check. It did!)
3. I did "update v1 set b = 6". It updated 6 rows.
So, it appears that the updatable view test is not as complete as it needs
to be.
I have written a new JIRA,
https://issues.apache.org/jira/browse/TRAFODION-2840, to track this problem.
> 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
> Fix For: 2.3-incubating
>
>
> 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)
