Bug #18765 query with subquery hangs server.
Submitted: 4 Apr 2006 5:00 Modified: 4 May 2006 10:58
Reporter: peter conza Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.19-nt OS:server 2003 or winXP
Assigned to: CPU Architecture:Any

[4 Apr 2006 5:00] peter conza 
Description:
query with subquery on the same table causes mysql task to pin cpu to 50% and bogs down server.
had to stop the service to regain control.
i tried the same query on 2 different servers running 5.0.19-nt with the same bad result. 

select * from banktrans where fitid in
(select fitid from banktrans group by bank_account,fitid having count(fitid)>1 and fitid>'')

How to repeat:
issue a query similar to the one above on a varchar column in one of your medium sized tables.
the subquery by itself runs fine and produces 4 rows.
when i stick the main query in front of it the server hangs.
[4 Apr 2006 10:58] Valeriy Kravchuk 
Thank you for a problem report. Please, send the SHOW CREATE TABLE and SHOW TABLE STATUS results fro your banktrans table.
[4 Apr 2006 15:39] peter conza 
show status and create table for banktrans

Attachment: banktrans.txt (text/plain), 3.10 KiB.
[4 May 2006 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".