MySQL Bugs: #79294: Query with double nested subquery much slower in 5.7

Bug #79294	Query with double nested subquery much slower in 5.7
Submitted:	16 Nov 2015 15:31	Modified:	18 Dec 2015 18:46
Reporter:	Jonathan Corwin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.9, 5.7.11	OS:	Windows (2008 x64 / 8.1 x64)
Assigned to:		CPU Architecture:	Any

Description:
A query containing double nested subqueries in my application takes 0.014seconds to run in 5.1/5.5/5.6, however in 5.7 it is taking over 40 seconds.

The original query and dataset were very large, so I've reduced it to the following query for purposes of reproduction. 
So now whilst it is still instant on 5.6, this version takes about 8 seconds in 5.7

The query contains double nested subqueries.

The corresponding explains for 5.6 and 5.7. 
5.6 EXPLAIN:
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, PRIMARY, JCDESPATCH, index, , INVDATE, 6, , 1018, Using index
1, PRIMARY, <derived2>, ALL, , , , , 12, Using where; Using join buffer (Block Nested Loop)
2, DERIVED, CURR, range, END_DATE, END_DATE, 3, , 12, Using where; Using index
3, DEPENDENT SUBQUERY, PREV, index, END_DATE, END_DATE, 3, , 1, Range checked for each record (index map: 0x2); Using index

5.7 EXPLAIN:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, JCDESPATCH, , index, , INVDATE, 6, , 1018, 100.00, Using index
1, PRIMARY, CURR, , index, END_DATE, END_DATE, 3, , 252, 100.00, Using where; Using index; Using join buffer (Block Nested Loop)
3, DEPENDENT SUBQUERY, PREV, , index, END_DATE, END_DATE, 3, , 1, 33.33, Using where; Using index

How to repeat:
Import the attached data

Run this query on 5.6, it is quick. Run it on 5.7 it is slower.

SELECT
 DES_INV_DATE
FROM
 DESPATCH
LEFT JOIN
 (SELECT
   CURR.PER_END_DATE As EndDate,
   (SELECT PREV.PER_END_DATE
    FROM PERIOD PREV
    WHERE PREV.PER_END_DATE < CURR.PER_END_DATE
    ORDER BY PREV.PER_END_DATE DESC LIMIT 1)
   AS PrevEndDate
  FROM PERIOD CURR
  WHERE CURR.PER_END_DATE >= 's:1' ) PERIODS
ON
 PERIODS.PREVENDDATE < DES_INV_DATE
 AND PERIODS.ENDDATE >= DES_INV_DATE

Suggested fix:
Make it run quick again please!

Table definitions and data

Attachment: slow.sql (application/octet-stream, text), 24.50 KiB.

MySQL config file

Attachment: my.ini (application/octet-stream, text), 886 bytes.

Thank you for the report and test case.

Thanks,
Umesh

Noted in 5.7.11, 5.8.0 changelogs.

Some queries with derived tables perform better with materialization
than when merged into the outer query. The optimizer no longer uses
merging by default for derived tables containing dependent subqueries
in the select list.