MySQL Bugs: #21614: UNION in Views

Bug #21614	UNION in Views
Submitted:	14 Aug 2006 7:42	Modified:	30 Mar 2007 16:24
Reporter:	Amit Narang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.24, 5.0 BK, 5.1 BK, 5.2-falcon	OS:	Linux (Linux, windows/fc5)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
I tried to create a view which uses UNION instruction as follows 

CREATE VIEW v_t1
	AS 
( 
	select * from t1
union  
	select * from t2
)
The above command doesnot work.

where as 

    select * from t1
union  
	select * from t2

command works very fine withou a view creation

How to repeat:
try creating a view as follows

CREATE VIEW v_t1
	AS 
( 
	select * from t1
union  
	select * from t2
)

it must ahve a UNION keyword in it.

Amit, please check http://bugs.mysql.com/how-to-report.php. What is the server version? I have tested on 5.0.25BK and it works as expected:
CREATE TABLE `a` (
  `a` varchar(20) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `b` (
  `a` varchar(20) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE VIEW `test`.`test21614` AS
  SELECT * FROM a
  UNION
  SELECT * FROM b;
SELECT * FROM test21614 t;

Currently I am using release version 5.0.24 

And in this version UNIONS are not working with VIEWS.

Looks like you have tested the bug with Version 5.0.25 which is not a released one .

Please give me information about if the feature is already present in 5.0.24 or will be released in next version and expected time of release.

Amit, since I found nothing in the changelog (http://dev.mysql.com/doc/refman/5.0/en/news-5-0-25.html) I tested with version 5.0.23 and a coleague tested 5.0.24. Both tests succede:
C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-log

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

mysql> use zztestview
Database changed
mysql> CREATE TABLE `a` (
    ->   `a` varchar(20) NOT NULL,
    ->   PRIMARY KEY  (`a`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `b` (
    ->   `a` varchar(20) NOT NULL,
    ->   PRIMARY KEY  (`a`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE VIEW `zztestview`.`test21614` AS
    ->   SELECT * FROM a
    ->   UNION
    ->   SELECT * FROM b;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM test21614 t;
Empty set (0.00 sec)

mysql>

You have not provided SQL script you used in your example so all I can do is to suggest checking your installation.

Thanks for the solution.
Looks like my mysql was not working properly.
I have tried installing a new one and the problem is resolved

We have duplicate bug #22044 and this bug is not fixed fully:

CREATE TABLE `a` (
  `a` varchar(20) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `b` (
  `a` varchar(20) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE VIEW `test`.`test21614` AS
  SELECT * FROM a
  UNION
  SELECT * FROM b;
SELECT * FROM test21614 t;

But:
CREATE VIEW `test`.`test21614` AS
(
  SELECT * FROM a
  UNION
  SELECT * FROM b
)
;
ERROR 1064 (42000): 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
  SELECT * FROM b
)' at line 4

All versions are affected

This bug is a duplicate of Bug#25734 "union and parenthesis"
A workaround is to remove the parenthesis from view definition.

The problem is not specific to VIEW, but to the usage of parenthesis
with UNION in sub-selects.

See related bug#25734