Bug #5972 views in mysql 5.0.1 using a union statement

• Submitted: 8 Oct 2004 7:52
• Modified: 8 Oct 2004 11:14
• Reporter: miriamr allou
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.1
• OS: Linux (Linux)
• CPU Architecture: Any

[8 Oct 2004 7:52] miriamr allou

Description:
i've tried to create a view in mysql 5.0.1 using a union statement in the select expression. unfortunately that doesn't seem to work . I have checked that I had the grants to create views, and that the columns I wanted to make a union on had the same structures

How to repeat:
I create two tables with the exact same structure:

mysql> create table my_table1(col1 int);
Query OK, 0 rows affected (0.12 sec)

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

If I try to create a view on one of these tables, this works, which indicates that I have the grants to create views:

mysql> create view my_view1 as select col1 from my_table1;
Query OK, 0 rows affected (0.00 sec)

However if I use a union statement in the select expression, I get an error:
mysql> create view my_view2 as select col1 from my_table1 union select col2 from my_table2;
ERROR 1143 (42000): create view command denied to user 'allouche'@'localhost' for column 'col1' in table 'my_view2'

even if the create view syntax does not impose to specify a column name for the view, I tried another syntax to be an unambigous as possible:
from a single table this works:

mysql> create view my_view_bis (col_view) as select col1 as col_view from my_table1;
Query OK, 0 rows affected (0.00 sec)

but from a union between two tables, this fails:

mysql> create view my_view2_bis (col_view) as select col1 as col_view from my_table1 union select col2 as col_view from my_table2;
ERROR 1143 (42000): create view command denied to user 'allouche'@'localhost' for column 'col_view' in table 'my_view2_bis'

I also have tested the select expression with the union statement itself to check if there    was a problem but the expression seem to be correct:

mysql> select col1 from my_table1 union select col2 from my_table2;
Empty set (0.00 sec)

[8 Oct 2004 9:00] MySQL Verification Team

Hi,

Thank you for the report!
Please, show me the output of:
SELECT CURRENT_USER();
and
SHOW GRANTS FOR 'allouche'@'localhost';

[8 Oct 2004 9:05] miriamr allou

here is the output of the commands 
SELECT CURRENT_USER();
and
SHOW GRANTS FOR 'allouche'@'localhost';

mysql> SELECT CURRENT_USER();
+--------------------+
| CURRENT_USER()     |
+--------------------+
| allouche@localhost |
+--------------------+
1 row in set (0.03 sec)

mysql> SHOW GRANTS FOR 'allouche'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for allouche@localhost                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'allouche'@'localhost' IDENTIFIED BY PASSWORD '*B097FE3F1EE86E26BC8B2886CDA4F9E77C1097C0' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[8 Oct 2004 11:14] MySQL Verification Team

Hi,

Thank you for the feedback.
I was alble to repeat it with version 5.0.1, but the above test case works fine with 5.0.2-alpha-debug-log:

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

mysql> create table my_table2(col2 int);
Query OK, 0 rows affected (0.07 sec)

mysql> create view my_view1 as select col1 from my_table1;
Query OK, 0 rows affected (0.00 sec)

mysql> create view my_view2 as select col1 from my_table1 union select col2 from
    -> my_table2;
Query OK, 0 rows affected (0.00 sec)

mysql> create view my_view_bis (col_view) as select col1 as col_view from
    -> my_table1;
Query OK, 0 rows affected (0.00 sec)

mysql> create view my_view2_bis (col_view) as select col1 as col_view from
    -> my_table1 union select col2 as col_view from my_table2;
Query OK, 0 rows affected (0.00 sec)

mysql> select col1 from my_table1 union select col2 from my_table2;
Empty set (0.00 sec)