You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2015/08/27 15:50:47 UTC
[jira] [Commented] (TRAFODION-1171) LP Bug: 1444044 - Create view
with duplicate column names no longer returns error 8102
[ https://issues.apache.org/jira/browse/TRAFODION-1171?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14716691#comment-14716691 ]
Suresh Subbiah commented on TRAFODION-1171:
-------------------------------------------
Anoop Sharma (anoop-sharma) on 2015-04-14
Changed in trafodion:
assignee: nobody → Anoop Sharma (anoop-sharma)
Anoop Sharma (anoop-sharma) wrote on 2015-04-14: #1
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.
tags: added: sql-cmp
removed: sql-exe
Anoop Sharma (anoop-sharma) on 2015-04-14
Changed in trafodion:
status: New → In Progress
Anoop Sharma (anoop-sharma) on 2015-04-15
Changed in trafodion:
importance: High → Critical
Anoop Sharma (anoop-sharma) on 2015-04-17
Changed in trafodion:
status: In Progress → Fix Committed
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-04-21: #2
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.
Changed in trafodion:
status: Fix Committed → Fix Released
> LP Bug: 1444044 - Create view with duplicate column names no longer returns error 8102
> --------------------------------------------------------------------------------------
>
> Key: TRAFODION-1171
> URL: https://issues.apache.org/jira/browse/TRAFODION-1171
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Weishiun Tsai
> Assignee: Anoop Sharma
> Priority: Blocker
> Fix For: 1.1 (pre-incubation)
>
>
> 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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)