[
https://issues.apache.org/jira/browse/TRAFODION-2822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16271225#comment-16271225
]
ASF GitHub Bot commented on TRAFODION-2822:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1316
[TRAFODION-2822] Make [first n] views non-updatable; prevent bad MERGE plans
This pull request contains two changes:
1. Views defined using [first n] or [any n] are now marked as not updatable
and not insertable. So, when a MERGE statement is attempted on a new view of
this type, we will get a compile time error that the view is not insertable or
updatable (instead of getting an incorrect result).
2. MERGE statements that have a WHEN NOT MATCHED INSERT action are
prevented from undergoing a tuple substitution transformation (that is, the
TSJRule and TSJFlowRule will not fire on a merge node possessing a WHEN NOT
MATCHED INSERT action). This is necessary because the run-time implementation
of insert actions takes place in the merge node itself; that is, scanning has
to happen in the merge node. These two rules would take the scanning out of the
merge node into a separate scan node.
Note: Existing [first n] / [any n] views remain marked as updatable and
insertable. (These attributes are calculated at CREATE VIEW time and stored in
the metadata.) The second change above will cause MERGE statements having WHEN
NOT MATCHED INSERT actions to fail at compile time against such views with an
error 2235 (Optimizer could not produce a plan for the statement). While this
is a non-obvious error from a user perspective it is far better than allowing
execution and getting an incorrect result.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion Trafodion2822
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1316.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1316
----
----
> 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)
