Bug #44745 Creating a View that makes a UNION to two Views
Submitted: 8 May 2009 11:31 Modified: 8 Jun 2009 12:54
Reporter: Myles Kadusale Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.51b OS:Any
Assigned to: CPU Architecture:Any
Tags: UNION, Views

[8 May 2009 11:31] Myles Kadusale
Description:
I have two views and I want to create another view to combine the two views and I used UNION ALL but when I try to create it I get an error.

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL
select * from mySQLviews2
)' at line 7

How to repeat:

CREATE
    ALGORITHM = UNDEFINED
    VIEW `track`.`test` 
    AS
(
   SELECT * FROM mySQLviews1
   UNION ALL
   SELECT * FROM mySQLviews2
);
[8 May 2009 12:54] Valeriy Kravchuk
Thank you for the problem report. Please, check with a newer version, 5.0.81. Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.79-enterprise-gpl-nt MySQL Enterprise Server - Pro Edition (
GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create view mv1 as select 1 as a;
Query OK, 0 rows affected (0.08 sec)

mysql> create view mv2 as select 2 as b;
Query OK, 0 rows affected (0.06 sec)

mysql> create view mv3 as select * from mv1 union all select * from mv2;
Query OK, 0 rows affected (0.55 sec)

mysql> select * from mv3;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.08 sec)
[8 Jun 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".