[jira] [Commented] (TRAFODION-2840) ORDER BY clause on a view circumvents [first n] updatability check

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2840?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16366202#comment-16366202 ] 

ASF GitHub Bot commented on TRAFODION-2840:
-------------------------------------------

GitHub user DaveBirdsall opened a pull request:

    https://github.com/apache/trafodion/pull/1446

    [TRAFODION-2840] Update expected results for test hive/TEST018

    A side effect of https://github.com/apache/trafodion/pull/1414 is that [first n] + ORDER BY queries are now considered non-cacheable. (Queries with [first n] but without ORDER BY have always been non-cacheable.) This caused a failure in hive/TEST018, as a query that was formerly cacheable is now not.
    
    This change updates the expected results for hive/TEST018.

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/DaveBirdsall/trafodion hiveTest018

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/trafodion/pull/1446.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 #1446
    
----
commit e13919aa7360e2ddd9c630f77c49ea1f13ca465e
Author: Dave Birdsall <db...@...>
Date:   2018-02-15T20:00:46Z

    [TRAFODION-2840] Update expected results for test hive/TEST018

----


> 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
>             Fix For: 2.3
>
>
> 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)