Bug #17510 Problems with parentheses in view syntax
Submitted: 17 Feb 2006 8:55 Modified: 17 Feb 2006 13:53
Reporter: Kristian Koehntopp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S4 (Feature request)
Version:5 OS:Linux (Linux, Windows)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[17 Feb 2006 8:55] Kristian Koehntopp
Description:
Oracle understands

  create view v as
  ( select * from a
    union
    select * from b )

We do not (remove the parens).

How to repeat:
Create the above view in Oracle, migrate, boom

Suggested fix:
This may not be a problem with MTK, but with the MySQL SQL syntax
[17 Feb 2006 13:53] Valeriy Kravchuk
Thank you for a problem report. I think, it is a server problem:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18-nt

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

mysql> create view v1 as (select 1);
Query OK, 0 rows affected (0.31 sec)

mysql> create view v2 as (select 1 union select 2);
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 2)' at line 1
mysql> create view v2 as select 1 union select 2;
Query OK, 0 rows affected (0.11 sec)

mysql> create view v3 as (select * from mysql.db);
Query OK, 0 rows affected (0.12 sec)

So, why we can put simple SELECT into parentheses and not SELECT with UNION? If it is a limitation "by design", we should change it. So, I mark it as a verified server feature request.
[4 Oct 2008 18:41] Konstantin Osipov
Peter, is this syntax standard?
[6 Oct 2008 20:57] Konstantin Osipov
Subject: Re: Syntax suggestion in Bug#17510

Hi Konstantin,

Konstantin Osipov wrote:
> Hello Peter,
>
> There is a syntax suggestion in Bug#17510 -- is it standard?
>
 
Yes.

But I don't think this is a bug in CREATE VIEW.
MySQL rejects any SELECT of this form.

"
mysql> (select 1 union select 2);
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 2)' at line 1
"