[
https://issues.apache.org/jira/browse/TRAFODION-2840?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16338480#comment-16338480
]
ASF GitHub Bot commented on TRAFODION-2840:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/trafodion/pull/1414
[TRAFODION-2840] Make [first n] with ORDER BY views non-updatable
JIRA TRAFODION-2822 attempted to make views containing [first n] in the
select list non-updatable. It missed one case, the case where both [first n]
and ORDER BY are present in the view definition.
The reason it missed that case is because of a design oddity in how [first
n] is implemented. When there is no ORDER BY clause, the Binder pass introduces
a FirstN node into the query tree. But if an ORDER BY is present, it defers
doing this to the Generator. That means any FirstN-related semantic checks in
earlier passes will not be applied when ORDER BY is present.
This set of changes refactors the FirstN implementation a little bit so
that the FirstN can be introduced in the Binder when ORDER BY is present. Then
the check added in JIRA TRAFODION-2822 will apply, and such views will be
marked non-updatable as desired.
The refactored design copies a bound and transformed version of the ORDER
BY clause into the FirstN node at Normalize time. (Aside: I tried copying the
unbound ORDER BY tree into the FirstN node at Bind time initially, but this
fails if there are expressions in the select list referenced by the ORDER BY;
different ValueIds get assigned to parts of the expressions than in the
original resulting in expression coverage check failures at Optimize time.)
The refactored design has been implemented for all code paths that are
involved in view definition, which is all that is required to satisfy this
JIRA. However, there is another code path that hasn't been addressed: If a
query uses output rowsets, has [first n] + ORDER BY, then the FirstN is still
added in the Generator. I took a look at fixing this one too, but it proved
more tedious than I wanted to tackle just now. A follow-on JIRA,
TRAFODION-2924, has been written to track that case.
In addition, I noticed some code paths that use [first n] today that do not
work correctly. The parser supports [first n] syntax on UPDATE and DELETE
statements. But I discovered (even before my changes) that UPDATE ignores its
[first n] specification. And if [first n] is specified for DELETE, the query
fails with a compiler error 2235 (cannot produce plan). So those evidently are
incomplete features, or features that broke sometime in the past and no-one
noticed until now.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/trafodion Trafodion2840
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1414.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 #1414
----
commit ebf7283de994729f898fa5a9f5e476fa03b40a4f
Author: Dave Birdsall <dbirdsall@...>
Date: 2018-01-25T00:16:08Z
[TRAFODION-2840] Make [first n] with ORDER BY views non-updatable
----
> ORDER BY clause on a view circumvents [first n] updatability check
> ------------------------------------------------------------------
>
> Key: TRAFODION-2840
> URL: https://issues.apache.org/jira/browse/TRAFODION-2840
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3
> Environment: All
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
>
> The following script fails:
> >>create table t1 (a int not null, b int, primary key (a));
> --- SQL operation complete.
> >>
> >>insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
> --- 6 row(s) inserted.
> >>
> >>create view v1 as select [first 5] * from t1 order by a;
> --- SQL operation complete.
> >>
> >>create view v2 as select [first 5] * from t1;
> --- SQL operation complete.
> >>
> >>update v1 set b = 6;
> --- 6 row(s) updated.
> >> -- should fail; v1 should be non-updatable
> >>
> >>update v2 set b = 7;
> *** ERROR[4028] Table or view TRAFODION.SEABASE.V2 is not updatable.
> *** ERROR[8822] The statement was not prepared.
> >>-- does fail; v2 is non-updatable (correctly)
> >>
> It seems the presence of the ORDER BY clause in the view definition
> circumvents the [first n] updatability check.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
