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)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.XX/5.0.XX
• OS: Linux (Slackware Linux)
• Assigned to: Timour Katchaounov
• CPU Architecture: Any

[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] MySQL Verification Team

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.