MySQL Bugs: #2600: SQL_SELECT_LIMIT have influence on subqueries

Bug #2600	SQL_SELECT_LIMIT have influence on subqueries
Submitted:	1 Feb 2004 10:52	Modified:	20 Feb 2004 5:49
Reporter:	Florian Mueller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.2	OS:
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
It is impossible right now to run subqueries with MySQL CC.  It brings up 
this error:
> [local] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' <

I checked into this with the ASCII client.  There is no problem 
about using LIMIT for the total query but a LIMIT is not allowed within the subquery.
Unfortunately, MySQL CC seems to automatically add a LIMIT to the 
subquery :-(  It should be very easy to change that.

How to repeat:
Just start MySQL CC, MySQL 4.1 alpha, and enter any statement with a subselect, such as:
SELECT * FROM table1 WHERE id IN (SELECT id from table WHERE <whatever condition>)

You will get the error message.

Suggested fix:
MySQL CC should not auto-add any LIMIT to subselects.  It is usually fine that it adds them to the queries but it should only do so for the outer part of a nested select, not for the subselect.

This is not a MySQL Control Center issue.  You can circumvent the problem by changing your server configuration (in MySQLCC) to not place a limit on the number of results returned by SELECT statements.

However, SQL_SELECT_LIMIT is getting applied to subqueries, which would seem a bug:

SET SQL_SELECT_LIMIT=-1;
SELECT user FROM mysql.user WHERE user IN ( SELECT user FROM mysql.user );
SET SQL_SELECT_LIMIT=1;
SELECT user FROM mysql.user WHERE user IN ( SELECT user FROM mysql.user );

ChangeSet 
  1.1697 04/02/04 15:26:41 bell@sanja.is.com.ua +5 -0 
  correct assignment of default limit (BUG#2600)

Thank you for bugreport. 
bugfix is pushed in our internal repository and will be present in next 
release