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

[19 Jan 2005 23: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 9:32] Miguel Solorzano 
Thank you for the bug report.
[18 Feb 2005 9:58] Timour Katchaounov 
Fixed in versions 4.1.10 and 5.0.3.
[21 Feb 2005 20:28] Paul DuBois 
Mentioned in 4.1.10, 5.0.3 change notes.