Bug #46159 simple query that never returns
Submitted: 14 Jul 2009 0:16 Modified: 19 Dec 2009 0:09
Reporter: Ryan Huddleston Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.34, 4.1, 5.0, 5.1, azalea bzr OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any

[14 Jul 2009 0:16] Ryan Huddleston
Description:
a simple query hangs indefinitely when run on this data set. Appears to be an optimizer issues is my guess. Something to do with the having clause and and the distinct both working with NULL values.

When running it chews 100% CPU until the query is killed which can be a DoS issue.

How to repeat:
Steps to repeat:

1) load dump at ftp://ftp.mysql.com/pub/mysql/upload/mysql_bug_090713.dump2.gz
2) select distinct data_alt1 from data group by data having count(*) > 1;

The above data has 35,000 rows and the sql will never return. Doesn't matter if it's innodb or myisam. 

Suggested fix:
fix code
[14 Jul 2009 6:55] Sveta Smirnova
Thank you for the report.

Verified as described. Same result if remove any/all keys.
[14 Jul 2009 6:56] Sveta Smirnova
test case for the testsuite

Attachment: bug46159.test (application/octet-stream, text), 196.05 KiB.

[14 Jul 2009 7:16] Sveta Smirnova
Azalea crash o Linux:

Thread 2 (process 1822):
#0  0x08316bd0 in remove_dup_with_compare (thd=0x96eb0f0, table=0x9730d18, first_field=0x9731630, offset=252, having=0x96e7c40) at sql_select.cc:19253
#1  0x0831765e in remove_duplicates (join=0x97dc3e8, entry=0x9730d18, fields=@0x97e1160, having=0x96e7c40) at sql_select.cc:19223
#2  0x08324fa6 in JOIN::exec (this=0x97dc3e8) at sql_select.cc:2691
#3  0x08326002 in mysql_select (thd=0x96eb0f0, rref_pointer_array=0x96ec438, tables=0x96e75d0, wild_num=0, fields=@0x96ec3c8, conds=0x0, og_num=1, order=0x0, group=0x96e7aa8, having=0x96e7c40, 
    proc_param=0x0, select_options=2147764737, result=0x96e7d90, unit=0x96ec098, select_lex=0x96ec334) at sql_select.cc:3091
#4  0x08326320 in handle_select (thd=0x96eb0f0, lex=0x96ec03c, result=0x96e7d90, setup_tables_done_option=0) at sql_select.cc:306
#5  0x08282d71 in execute_sqlcom_select (thd=0x96eb0f0, all_tables=0x96e75d0) at sql_parse.cc:4927
#6  0x08288b0a in mysql_execute_command (thd=0x96eb0f0) at sql_parse.cc:2112
#7  0x08291ec5 in mysql_parse (thd=0x96eb0f0, inBuf=0x96e7200 "select distinct data_alt1 from data group by data having count(*) > 1", length=69, found_semicolon=0xb745df20) at sql_parse.cc:5942
#8  0x08292937 in dispatch_command (command=COM_QUERY, thd=0x96eb0f0, packet=0x97bf389 "select distinct data_alt1 from data group by data having count(*) > 1", packet_length=69) at sql_parse.cc:1061
#9  0x08293c55 in do_command (thd=0x96eb0f0) at sql_parse.cc:743
#10 0x08280e51 in handle_one_connection (arg=0x96eb0f0) at sql_connect.cc:1158
#11 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#12 0x003b74fe in clone () from /lib/libc.so.6
[5 Sep 2009 20:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82530

3087 Alexey Kopytov	2009-09-06
      Bug #46159: simple query that never returns 
       
      The external 'for' loop in remove_dup_with_compare() handled 
      HA_ERR_RECORD_DELETED by just starting over without advancing 
      to the next record which caused an infinite loop. 
       
      This condition could be triggered on certain data by a SELECT 
      query containing DISTINCT, GROUP BY and HAVING clauses. 
      
      Fixed remove_dup_with_compare() so that we always advance to 
      the next record when receiving HA_ERR_RECORD_DELETED from 
      rnd_next(). 
     @ mysql-test/r/distinct.result
        Added a test case for bug #46159.
     @ mysql-test/t/distinct.test
        Added a test case for bug #46159.
     @ sql/sql_select.cc
        Fixed remove_dup_with_compare() so that we always advance to 
        the next record when receiving HA_ERR_RECORD_DELETED from 
        rnd_next().
[14 Sep 2009 16:04] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[23 Sep 2009 20:27] Paul DuBois
Noted in 5.4.4 changelogs.

Certain SELECT statements containing DISTINCT, GROUP BY, and HAVING
clauses could hang in an infinite loop.

Setting report to NDI pending push into 5.1.x.
[2 Oct 2009 1:13] Paul DuBois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[6 Oct 2009 9:01] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)
[7 Oct 2009 1:02] Paul DuBois
Noted in 5.1.40 changelog.
[18 Dec 2009 10:33] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:49] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:04] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:18] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)