Bug #3389 INSERT...SELECT statement applies global "Automatically limit SELECT.." value
Submitted: 5 Apr 2004 2:13 Modified: 21 May 2004 17:41
Reporter: Ronan O'Connor Email Updates:
Status: Duplicate Impact on me:
Category:MySQLCC Severity:S1 (Critical)
Version:0.9.4 OS:Linux (Fedora(core1) gnome 2.4)
Assigned to: Bugs System CPU Architecture:Any

[5 Apr 2004 2:13] Ronan O'Connor
When performing an INSERT...SELECT statement, MYSQLCC applies the "Automatically  limit SELECT queries to" value to the select statement. 
For instance if you want to copy all of the data from one table to the another with a:

INSERT INTO newtable SELECT * FROM oldtable;

will only copy up to the limit value (default 1000).

How to repeat:
Run query:

INSERT INTO newtable SELECT * FROM oldtable;

Where oldtable has more rows than the "Automatically limit SELECT queries to" value.
[20 May 2004 17:55] Jorge del Conde

This is not a MySQLCC bug.  MySQLCC calls SET LIMIT=X when launched and its because of this that you're seeing this behaviour.

Please modify the settings and change the "Automatically Limit Queries" value to 'Unlimited'.
[21 May 2004 15:14] Ronan O'Connor
I'm not sure I agree :). I don't think this should be treated like bug #1089. That's a GUI feature, "I only want to see X rows". However setting the limit to X, should *not* affect valid SQL.
In my opinion #1089 is a 'feature'. But this is a genuine bug. I may only want to see 1000 rows for a SELECT statement. But this should not change what I mean when I do an INSERT...SELECT statement. (I don't only want to change the first 1000 rows matched by the SELECT part, I want to change all matched rows :)
Changing the "Automatically limit SELECT queries to" value, in order to make INSERT...SELECT queries perform correctly is a work around of bug (in my opinion:).
[21 May 2004 17:41] Sergei Golubchik
it's fixed already in 4.1 as a part of the bug http://bugs.mysql.com/2600