MySQL Bugs: #3389: INSERT...SELECT statement applies global "Automatically limit SELECT.." value

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:	None
Category:	MySQLCC	Severity:	S1 (Critical)
Version:	0.9.4	OS:	Linux (Fedora(core1) gnome 2.4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
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.

Hi!

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'.

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:).

it's fixed already in 4.1 as a part of the bug http://bugs.mysql.com/2600