Create view with duplicate column names no longer returns error 8102

[Weishiun Tsai]

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.

[Anoop Sharma (anoop-sharma)]

This issue of no error msg on dup cols will show up for both
view and table creation.

Adding fix to detect and return a better err msg.

It will now show up as:

>>create view v as select a,a from t011t5;

*** ERROR[1080] The DDL request has duplicate references to column A.

--- SQL operation failed with errors.

>>create table t011t5 (a int, b int, a int);

*** ERROR[1080] The DDL request has duplicate references to column A.

--- SQL operation failed with errors.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v1.1.0 rc1 (v0417) build. This problem has been fixed:

>>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;

*** ERROR[1080] The DDL request has duplicate references to column A.

--- SQL operation failed with errors.
>>showddl v1;

*** ERROR[4082] Object TRAFODION.MYTEST.V1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>select * from v1;

*** ERROR[4082] Object TRAFODION.MYTEST.V1 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

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

*** ERROR[1080] The DDL request has duplicate references to column MYCOL.

--- SQL operation failed with errors.
>>showddl v2;

*** ERROR[4082] Object TRAFODION.MYTEST.V2 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>select * from v2;

*** ERROR[4082] Object TRAFODION.MYTEST.V2 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>
>>drop schema mytest cascade;

--- SQL operation complete.