MySQL Bugs: #8195: only 1000 rows are transferred when using insert into ... select

Bug #8195	only 1000 rows are transferred when using insert into ... select
Submitted:	29 Jan 2005 17:16	Modified:	30 Jan 2005 11:54
Reporter:	Peter Kovacs	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.23	OS:	Windows (windows xp)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
When loading data to a table using the results of a select, only 1000 rows are loaded into the target table. 

How to repeat:
Create a query with a result with more than 1000 rows and load them to a new table like here:
insert into newtable (n1, n2)
select n1, n2 from oldtable, oldtable2
where oldtable.id = oldtable2.id

Suggested fix:
The same works with the command line mysql client under linux, so simply cut and paste the query and execute it there.

what version of MySQL *server* is it ?

mysql> status;
--------------
mysql  Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386)

Connection id:          6385
Current database:
Current user:           kovacsp@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Server version:         4.0.23_Debian-3-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    latin1
Server characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 5 days 9 hours 7 min 30 sec

Threads: 1  Questions: 4957778  Slow queries: 35  Opens: 90  Flush tables: 1  Open tables: 24  Queries per second avg: 10.665
--------------

mysql>

The reason is that by default MySQLCC sets sql_select_limit to 1000:

SET SQL_SELECT_LIMIT=1000;

and it means that all select's imply automatically "LIMIT 1000".
In 4.1.2 the behaviour was changed - SQL_SELECT_LIMIT apply only to the top-level SELECT, but not to INSERT ... SELECT and not to subqueries.
This will not be backported to 4.0 - as it would change the behaviour of the stable version.

You can either upgrade the server to the latest 4.1.x, or disable SQL_SELECT_LIMIT in MySQLCC configuration pane. (or use our new GUI line - MySQL Administrator and MySQL Query Browser)