Bug #90773 Subquery freezes
Submitted: 6 May 2018 21:34 Modified: 8 May 2018 13:18
Reporter: Lance Olinger Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.7.22 OS:Ubuntu (16.04.1)
Assigned to: CPU Architecture:Any

[6 May 2018 21:34] Lance Olinger
On the newest version of mysql subqueries can hang indefinitely. This query takes 2 seconds to run on 5.6 and back but on 5.7 it hangs on creating sort. I did a dump of just the data, deleted the database, mysql server, config files,did a fresh install and then restored the data and had the same issue.

How to repeat:

SELECT customer.customer_id,job,business_name,DATE(install_completed) as install_date,DATE(audit_date) as audit_date, DATE(measure.updated) as updated, IFNULL(SUM(quantity),0) as quantity, CONCAT('$',FORMAT(IFNULL(SUM(incentive*quantity),0)-IFNULL(SUM(adjustment),0),2)) as incentive,CONCAT('$',FORMAT(IFNULL(audit,0),2)) as audit, CONCAT('$',FORMAT(IFNULL(copay,0),2)) as copay, CONCAT('$',FORMAT(IFNULL(copay_due,0),2)) as copay_due, CONCAT('$',FORMAT(IFNULL(city_approved,0),2)) as city_approved, CONCAT('$',FORMAT(IFNULL(write_off,0),2)) as write_off,' ' as available_funds,' ' as calculated_copay,CONCAT('$',FORMAT(IFNULL(SUM(sub_amt),0),2)) as sub_amt FROM customer 
LEFT JOIN (SELECT measure.customer_id,quantity,incentive,adjustment,sub_amt,measure.updated,install_completed FROM measure INNER JOIN measure_list on measure_list.measure_code = measure.measure_code INNER JOIN work_order ON work_order.work_order_id = measure.work_order_id WHERE status = 'Installed' AND  (invoice_id IS NULL || invoice_id = '') AND quantity <> 0 AND warranty != 1 AND paid_by_customer != 1  ORDER BY measure.updated DESC) as measure ON measure.customer_id = customer.customer_id 
LEFT JOIN (SELECT SUM(audit_cost) as audit,customer_id,audit_completed as audit_date FROM audit WHERE  (invoice_id IS NULL || invoice_id = '')  AND audit_completed >0  AND audit_cost >0 GROUP BY customer_id ) as audit ON audit.customer_id = customer.customer_id 
LEFT JOIN ( SELECT customer_id,sum(IF(payment_method LIKE 'Copay Due%',payment,0)) as copay,sum(IF(payment_method LIKE 'Copay Due%',payment,0))- sum(IF(payment_method NOT LIKE 'Copay Due%',payment,0)) as copay_due,sum(IF(payment_method ='City Approved',payment,0)) as city_approved,sum(IF(payment_method ='Write Off',payment,0)) as write_off FROM copay WHERE  (invoice_id IS NULL || invoice_id = '')  GROUP BY customer_id) as copay ON copay.customer_id = customer.customer_id
WHERE (incentive <>0 OR audit >0) 
              GROUP BY customer.customer_id
[7 May 2018 8:16] MySQL Verification Team
Thank you for the bug report. We need a complete test case a dump file with create tables, insert data and the queries, the query alone don`t allows to test the issue. Thanks.
[7 May 2018 23:25] Lance Olinger
The data is confidential. This issue has appeared with multiple databases the query posted was just one example of many, the only requirement is it needs to be a subquery with over 100 results. I had to move back to 5.6, after upgrading the server went from 4% average cpu usage to 80%, after removing as many subqueries as possible I got it down to 20%.

Here is the explain on the query:

	1	PRIMARY	customer		ALL	PRIMARY,customer_id_UNIQUE				18100	100.00	Using temporary; Using filesort
	1	PRIMARY	measure		ref_or_null	measure_code,work_order_id,invoice_id,customer_id,status	invoice_id	48	const	10	100.00	Using where
	1	PRIMARY	measure_list		ref	measure_code_UNIQUE,measure_code	measure_code	48	scppa.measure.measure_code	1	100.00	
	1	PRIMARY	work_order		eq_ref	PRIMARY,work_order_id_UNIQUE	PRIMARY	4	scppa.measure.work_order_id	1	100.00	
	1	PRIMARY	<derived3>		ref	<auto_key1>	<auto_key1>	4	scppa.customer.customer_id	17	100.00	Using where
	1	PRIMARY	<derived4>		ref	<auto_key0>	<auto_key0>	5	scppa.customer.customer_id	10	100.00	
	4	DERIVED	copay		ref_or_null	customer_id,invoice_id	invoice_id	48	const	2042	100.00	Using index condition; Using temporary; Using filesort
	3	DERIVED	audit		ref_or_null	PRIMARY,invoice_id,customer_id	invoice_id	48	const	2646	11.11	Using index condition; Using where; Using temporary; Using filesort
[8 May 2018 13:18] MySQL Verification Team

First and first of all, we need to be able to repeat the bug in order to verify it. You have not provided the data, which is why we can not work further on this report.

What is evident from the EXPLAIN is that you can improve a lot on making your query faster. First of all, `customer` table has to be scanned, since it is the left most table in the join. Next, you are not using nested queries. You are using quite complex derived tables.

Last, there were some bugs in the 5.6 that caused wrong results, which is why it was faster. Those bugs had to be fixed, hence a difference in the speed.