MySQL Bugs: #2661: Using same key on distinct and group by seems to create a busy-loop in mysql

Bug #2661	Using same key on distinct and group by seems to create a busy-loop in mysql
Submitted:	6 Feb 2004 2:59	Modified:	24 Feb 2004 14:35
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.15	OS:	Solaris (Solaris 8 SPARC)
Assigned to:	Timothy Smith	CPU Architecture:	Any

Description:
1) create table stuff (id int not null, data int not null, index(id));
2) create table stuffcount (id int not null, stuffcount int not null, index(id))
2) insert lots of rows into stuff
3) insert into stuffcount select distinct(id), count(*) from stuff group by id;

The query will get stuck onto phase "removing duplicates" or at least the query takes at least two orders 
of magnitude more time than executing query "insert into stuffcount select id, count(*) from stuff group 
by id". i just ran a similar SQL construct on one of our servers and the first query was killed after it had 
run for 45 minutes, the second query ran in 50 seconds.

The "stuff" table had about 2 million rows with about 1 million unique id's.

I know this is semantically the same query, I'm reporting just because it seems to trigger a problem.

How to repeat:
See above.

Suggested fix:
Check why the query is acting differently in the parser? Explain gives exact same data for the queries 
for it's quite apparent the queries trigger different bahavior even though the end result of both would 
be exactly the same.

I did try to repeat this, on a sunfire100 running solaris 8, using MySQL 4.0.18.  Unfortunately, it worked just fine.  Can you try it with 4.0.18; or, can you upload your data to ftp.mysql.com:/pub/mysql/Incoming/bug2661.tar.gz?  Please include all the files needed to repeat this.

Here's an example of what I tried, after creating the tables you described.

mysql> load data local infile '../data.txt' into table stuff (id);
Query OK, 2000100 rows affected (24.99 sec)
Records: 2000100  Deleted: 0  Skipped: 0  Warnings: 0

mysql> insert into stuffcount select distinct(id), count(*) from stuff group by id;
Query OK, 1999200 rows affected (25.10 sec)
Records: 1999200  Duplicates: 0  Warnings: 0

mysql> Bye
mysqldev@sunfire100c:~/tsmith/x/mysql-standard-4.0.18-sun-solaris2.8-sparc-64bit> uname -a
SunOS sunfire100c 5.8 Generic_108528-26 sun4u sparc SUNW,UltraAX-i2
mysqldev@sunfire100c:~/tsmith/x/mysql-standard-4.0.18-sun-solaris2.8-sparc-64bit>

I'll try to replicate the bug with 4.0.18 and report the results as soon as I have them.