Bug #9198 strange behaviour of UNION and parentheses
Submitted: 15 Mar 2005 14:00 Modified: 4 Apr 2006 18:19
Reporter: Matthias Leich Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:4.1 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[15 Mar 2005 14:00] Matthias Leich
Description:
The following staement is derived from the NIST tests:
SELECT PNUM,EMPNUM,HOURS FROM t1
WHERE HOURS=12
UNION ALL
(SELECT PNUM,EMPNUM,HOURS FROM t1
UNION
SELECT PNUM,EMPNUM,HOURS FROM t1
WHERE HOURS=80)
ORDER BY 2,1
It is legal SQL but MySQL gives
 ERROR 42000: You have an error in your SQL syntax; ....

Statement variations show that the server dislikes
- Only the second query is between parentheses
- Both queries are between parentheses, but the second 
  query contains UNION

 My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 4.1 ChangeSet@1.2112, 2005-03-14
        Version 5.0 ChangeSet@1.1801.1.18, 2005-03-15

How to repeat:
Please use the statements above or the attached test case 
ml47.test , copy it to mysql-test/t

  touch r/ml47.result     # Produce a dummy file with expected
                                  # results.
  ./mysql-test-run ml47
  inspect r/ml47.reject    # The protocol of the execution.
[15 Mar 2005 14:01] Matthias Leich
test case

Attachment: ml47.test (application/test, text), 832 bytes.

[30 Jun 2005 21:47] Roland Bouman
Hi, Bart Coelmont posted on the mysql forum for views, "Create view - union" on 06-29-2005. It seems that 

create view x as  (
    SELECT  c
    FROM     t
)

succeeds, wheras 

create view x as  (
    SELECT  c FROM     t
    UNION 
    SELECT  c FROM     t
)

fails with a 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 information_schema.schemata
)' at line 3
[4 Nov 2005 21:12] jason justman
This bug should be renamed to UNION in a view doens't work...  as its not a () bug, it doesn't work with valid sql and the docs say it should.
[4 Nov 2005 21:26] jason justman
sorry, let me restate, this bug is also present when attempting to write a view in the following formats:

--acceptable view creation

create view test_x as (
select * from test;
);

--fails

(select * from test
union 
select * from test);

--fails

create view test_x as (
( select * from test )
union 
(select * from test)
order by id;
);

-- but this is acceptable
create view test_x as
( select * from test )
union 
(select * from test)
order by id
;
[17 Feb 2008 6:29] sdsfce sdsfce
http://www.linuxlords.net/forum/