Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-1171

LP Bug: 1444044 - Create view with duplicate column names no longer returns error 8102

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.1 (pre-incubation)
    • Component/s: sql-cmp
    • Labels:
      None

      Description

      Up to the v0327 build, creating a view with 2 or more columns with the same name would return an 8102 error:

          • ERROR[8102] The operation is prevented by a unique constraint.

      This behavior was changed sometime after the v0327 build. Currently, if the user creates a view with columns using the same name, the operation would go through. Showddl would show that the view was created. But the invocation of the view would return a perplexing 4016 error. Such an error should be caught at the view creation time as before.

      This is seen on the r1.1.0rc0 (v0410) build.

      ------------------------

      Here is entire script to reproduce this problem. It shows 2 views created in this manner:

      create schema mytest;
      set schema mytest;

      create table t (a int, b int default null, c int default null);

      create view v1 as select a, a from t;
      showddl v1;
      select * from v1;

      create view v2 as select a as MYCOL, b as MYCOL from t;
      showddl v2;
      select * from v2;

      drop schema mytest cascade;

      ------------------------

      Here is the execution output:

      >>create schema mytest;

      — SQL operation complete.
      >>set schema mytest;

      — SQL operation complete.
      >>
      >>create table t (a int, b int default null, c int default null);

      — SQL operation complete.
      >>
      >>create view v1 as select a, a from t;

      — SQL operation complete.
      >>showddl v1;

      CREATE VIEW TRAFODION.MYTEST.V1 AS
      SELECT TRAFODION.MYTEST.T.A, TRAFODION.MYTEST.T.A FROM TRAFODION.MYTEST.T ;

      – GRANT SELECT, REFERENCES ON TRAFODION.MYTEST.V1 TO DB__ROOT WITH GRANT OPTION;

      — SQL operation complete.
      >>select * from v1;

          • ERROR[4016] The number of derived columns (1) must equal the degree of the derived table (2).
          • ERROR[8822] The statement was not prepared.

      >>
      >>create view v2 as select a as MYCOL, b as MYCOL from t;

      — SQL operation complete.
      >>showddl v2;

      CREATE VIEW TRAFODION.MYTEST.V2 AS
      SELECT TRAFODION.MYTEST.T.A AS MYCOL, TRAFODION.MYTEST.T.B AS MYCOL FROM
      TRAFODION.MYTEST.T ;

      – GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION.MYTEST.V2 TO DB__ROOT WITH GRANT OPTION;

      — SQL operation complete.
      >>select * from v2;

          • ERROR[4016] The number of derived columns (1) must equal the degree of the derived table (2).
          • ERROR[8822] The statement was not prepared.

      >>
      >>drop schema mytest cascade;

      — SQL operation complete.

        Attachments

          Activity

            People

            • Assignee:
              anoopsharma Anoop Sharma
              Reporter:
              WTsai Weishiun Tsai
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: