Bug #4664 Views: View definition wih UNION returns bad error message

• Submitted: 21 Jul 2004 2:15
• Modified: 25 Aug 2004 9:01
• Reporter: Trudy Pelzer
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.1-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[21 Jul 2004 2:15] Trudy Pelzer

Description:
If one creates a view (e.g. v) with a UNION in the definition, MySQL returns the following error:
ERROR 1143 (42000): create view command denied to user 'root'@'localhost' for column 's1' in table 'v'

I suspect the problem is really that UNION may not yet be part of a view definition. If so, a less ambiguous error message would be nice. Suggestion:
View definition may not contain UNION

How to repeat:
create table t1 (s1 int);
create table t2 (s1 int);
create view v as select * from t1,t2 union all select * from t1,t2;

[23 Jul 2004 3:50] MySQL Verification Team

Take a look in your sample and at the bottom the column name
modified.

create table t1 (s1 int);
create table t2 (s2 int);
create view v as select * from t1,t2 union all select * from t1,t2;

mysql> create table t1 (s1 int);
Query OK, 0 rows affected (0.06 sec)

mysql> create table t2 (s1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> create view v as select * from t1,t2 union all select * from t1,t2;
ERROR 1060 (42S21): Duplicate column name 's1'

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (s2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create view v as select * from t1,t2 union all select * from t1,t2;
ERROR 1143 (42000): create view command denied to user 'root'@'localhost' for column 's1' in table 'v'

[12 Aug 2004 1:09] Enrico Schättin

It would be nice to have this feature really soon
Thanks

[24 Aug 2004 18:51] Oleksandr Byelkin

ChangeSet 
  1.1730 04/08/24 19:50:16 [e-mail] +3 -0 
  Fixed UNION support in view creation (BUG#4664)

[25 Aug 2004 9:01] Oleksandr Byelkin

Thank you for bug report!  Bug is fixed, patch is pushed in our source repository.