MySQL Bugs: #44745: Creating a View that makes a UNION to two Views

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

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
);

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)

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".