| 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 | |
[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
Hi, 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.
Description: 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