Bug #8023 limit on UNION with from DUAL, causes syntax error
Submitted: 19 Jan 2005 22:20 Modified: 21 Feb 2005 19:28
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.XX/5.0.XX OS:Linux (Slackware Linux)
Assigned to: Timour Katchaounov

[19 Jan 2005 22:20] Martin Friebe
Description:
see example below, the unions would all return 2 rows, so the limits are working against the union not against the 2nd query.

however, if the 2nd query ends with FROM DUAL, thelimit causes an error.

It works fine again, if the FROM DUAL is left out

How to repeat:
create table t1 (a integer); insert into t1 values (1);

select 1 as a from t1 union all select 1 from t1  limit 1;
+---+
| a |
+---+
| 1 |
+---+

select 1 as a from DUAL union all select 1 from t1  limit 1;
+---+
| a |
+---+
| 1 |
+---+

select 1 as a from t1 union all select 1 from DUAL  limit 1;
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 'limit 1' at line 1

select 1 as a from t1 union all select 1   limit 1;
+---+
| a |
+---+
| 1 |
+---+

Suggested fix:
-
[20 Jan 2005 8:32] Miguel Solorzano
Thank you for the bug report.
[18 Feb 2005 8:58] Timour Katchaounov
Fixed in versions 4.1.10 and 5.0.3.
[21 Feb 2005 19:28] Paul Dubois
Mentioned in 4.1.10, 5.0.3 change notes.