MySQL Bugs: #46159: simple query that never returns

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

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

Thank you for the report.

Verified as described. Same result if remove any/all keys.

test case for the testsuite

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

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

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

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)

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.

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

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)

Noted in 5.1.40 changelog.

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)

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)

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)

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)