Create view with duplicate column names no longer returns error 8102
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
Fix Released
|
Critical
|
Anoop Sharma |
Bug 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.
-- 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.
TRAFODION.
-- 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.
Changed in trafodion: | |
assignee: | nobody → Anoop Sharma (anoop-sharma) |
Changed in trafodion: | |
status: | New → In Progress |
Changed in trafodion: | |
importance: | High → Critical |
Changed in trafodion: | |
status: | In Progress → Fix Committed |
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.