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 Email Updates:
Status: Closed Impact on me:
None 
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 bell@sanja.is.com.ua +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.