Uploaded image for project: 'Apache Trafodion (Retired)'
  1. Apache Trafodion (Retired)
  2. TRAFODION-2840

ORDER BY clause on a view circumvents [first n] updatability check

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 2.3
    • 2.3
    • sql-cmp
    • None
    • All

    Description

      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.

      Attachments

        Activity

          People

            dbirdsall Dave Birdsall
            dbirdsall Dave Birdsall
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: