MySQL Bugs: #7960: Lost Connection during SQL Select

Bug #7960	Lost Connection during SQL Select
Submitted:	17 Jan 2005 16:17	Modified:	19 Jan 2005 19:26
Reporter:	Terry Long	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.0-alpha	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
The following SQL with subqueries works fine on 4.1.7-beta and stops the server on 5.0.0-alpha:

SELECT p1.site_code, p1.prh_vend, ad.ad_name, ad_city, ad_state, p1.prh_receiver, p1.prh_part, pt_desc1, p1.prh_rcp_date, p1.prh_rcvd, p1.prh_pur_cost, p1.prh_rcvd * p1.prh_pur_cost, ( 
SELECT pp.prh_pur_cost
FROM service.prh_hist pp
WHERE pp.prh_part = service.p1.prh_part AND pp.site_code NOT 
IN (  '019',  '0335',  '0760' )
ORDER  BY pp.prh_rcp_date DESC 
LIMIT 1  )lst_prd_rcp_cost, ( 
SELECT min( sct_mtl_tl ) 
FROM sct_det
WHERE sct_part = p1.prh_part AND sct_sim
LIKE  'standard' AND sct_DET.site_code NOT 
IN (  '019',  '0335',  '0760' )
GROUP  BY sct_part )lst_prd_std_cost, ( 
SELECT min( pod_pur_cost ) 
FROM pod_det
WHERE p1.prh_part = pod_part AND pod_det.site_code NOT 
IN (  '019',  '0335',  '0760' )
GROUP  BY pod_part )lst_prd_po_cost
FROM service.prh_hist p1, service.ad_mstr ad
LEFT  JOIN pt_mstr pm ON ( pm.site_code = p1.site_code AND pm.pt_part = p1.prh_part ) 
WHERE p1.site_code
LIKE  '0760' AND p1.prh_rcp_date >= {d '2003-10-01'} AND p1.prh_rcp_date <= {d '2004-09-31'} AND ad.site_code = p1.site_code AND ad_addr = p1.prh_vend
ORDER  BY p1.site_code, ad_name, p1.prh_vend, p1.prh_part, p1.prh_rcp_date DESC 

How to repeat:
Started up 4.1.7 server and ran query fine.
Shutdown 4.1.7
Started up 5.0.0 server and ran query - the following error message occured:
#2013 - Lost connection to MySQL server during query 
Confirmed server had shutdown.   There are no server error messages created during this crash.

Suggested fix:
Unknown

Simplified Select to repeat error:

Will CRASH (with order by on a date field in subquery)

SELECT p1.prh_receiver, p1.prh_part, (
SELECT pp.prh_pur_cost
FROM prh_hist pp
WHERE pp.prh_part = p1.prh_part
ORDER BY pp.prh_rcp_date DESC 
LIMIT 1 
)fld1
FROM prh_hist p1
WHERE p1.site_code
LIKE '0760' 

WILL NOT CRASH (with an order by char field in the subquery)

SELECT p1.prh_receiver, p1.prh_part, (
SELECT pp.prh_pur_cost
FROM prh_hist pp
WHERE pp.prh_part = p1.prh_part
ORDER BY pp.prh_part DESC 
LIMIT 1 
)fld1
FROM prh_hist p1
WHERE p1.site_code LIKE '0760'

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.