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

[1 Feb 2004 10:52] Florian Mueller
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.
[3 Feb 2004 11:47] Dean Ellis
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 );
[4 Feb 2004 5:29] Oleksandr Byelkin
ChangeSet 
  1.1697 04/02/04 15:26:41 bell@sanja.is.com.ua +5 -0 
  correct assignment of default limit (BUG#2600)
[20 Feb 2004 5:49] Oleksandr Byelkin
Thank you for bugreport. 
bugfix is pushed in our internal repository and will be present in next 
release