MySQL Bugs: #62524: MySQL Workbench does not allow LIMIT statements at the end of UNION selects

Bug #62524	MySQL Workbench does not allow LIMIT statements at the end of UNION selects
Submitted:	23 Sep 2011 21:25	Modified:	3 Feb 2012 1:37
Reporter:	Brandon Johnson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.34, 5.2.35, 5.2.36, 5.2.37	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Select Queries with both a UNION and ending with a LIMIT statement will run through the MySQL command line interface, the (older) Query Browser, or several other MySQL Engine tools such as SQLyog, but not the MySQL Workbench.

They will always return 

Error Code: 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 'LIMIT 0, 1000' at line 3

How to repeat:
Run this query:

select COUNT(*) from mysql.user 
UNION select count(*) from mysql.db LIMIT 0,500;

Thank you for the bug report. Verified just as described with recent 5.2.35 on Windows XP.

This only occurs when the Preferences option of "Limit Rows" is enabled, and a query has a union with a trailing limit.

This query works:

select COUNT(*) from mysql.user 
UNION (select count(*) from mysql.db LIMIT 0,500)
UNION select count(*) from mysql.host;

This query doesn't:

select COUNT(*) from mysql.user 
UNION (select count(*) from mysql.db LIMIT 0,500)
UNION select count(*) from mysql.host LIMIT 0,500;

Fixed as of 5.2.38:

Queries containing a "UNION" are now excluded
from the automatic addition of the "LIMIT"
clause, when the "Limit Rows" preference is
enabled.