| 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: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[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/
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.