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

[14 Aug 2006 7:42] Amit Narang
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.
[14 Aug 2006 8:30] Tonci Grgin
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;
[14 Aug 2006 11:26] Amit Narang
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.
[14 Aug 2006 12:02] Tonci Grgin
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.
[14 Aug 2006 12:33] Amit Narang
Thanks for the solution.
Looks like my mysql was not working properly.
I have tried installing a new one and the problem is resolved
[6 Sep 2006 9:38] Sveta Smirnova
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
[22 Mar 2007 9:06] Sveta Smirnova
All versions are affected
[30 Mar 2007 16:24] Konstantin Osipov
This bug is a duplicate of Bug#25734 "union and parenthesis"
A workaround is to remove the parenthesis from view definition.
[30 Mar 2007 16:24] Marc Alff
The problem is not specific to VIEW, but to the usage of parenthesis
with UNION in sub-selects.

See related bug#25734