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 Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.1 OS:Linux (Linux)
Assigned to: 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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.