Fun with Bugs #11 - Top 10 Optimizer Regression Bugs in MySQL 5.6

I've got a question from colleague last night on what bugs should users take into account if they plan to upgrade to MySQL 5.6 now. Simple answer is: it depends. If one of the new features or scalability improvements are really important, then bugs in other features or clearly identified problematic use cases may be just ignored or avoided.

But to be on a safe side users should at least check if they are (or may be) affected by a known regression bugs, when new version is slower or produce wrong results or crashes in cases that worked without problems before.

List of bugs in MySQL 5.6 that can be formally considered as regressions comparing to previous major versions would be long enough for a single post. So I'd like to concentrate on regression bugs in Optimizer here:

• Bug #69471 - "UNION of derived tables returns wrong results with "1=0/false"-clauses". Even current code of 5.6.13 is affected. Unfortunately all kinds of programs that generate SQL based on user input often add these "1=0" or "1=1" clauses to the list of conditions, and MyDQL 5.6 may start producing wrong results for queries with default settings.
• Bug #69410 - it's not the first time when queries with LIMIT clause perform actually worse that queries without LIMIT, especially for InnoDB tables with many indexes defined and ORDER BY clause in the query. Here index condition pushdown optimization comes into play and leads to slower execution comparing to 5.5.x. Work in progress already, so maybe MySQL 5.6.13 will fix this.
• Bug #68979 (with recent Bug #69390 considered a duplicate by Miguel but no way for us to check or confirm as user's schema is private). New feature, "Delayed materialization of derived tables", may lead to different plan comparing to 5.5.x and performance regressions. Looks like users that use derived tables a lot in their SQL statements should be very careful while testing with MySQL 5.6.
• Bug #69350 - Shane had found that stored procedures doing things as simple as select 1 into `j`; in a loop perform notably worse in MySQL 5.6 comparing to MySQL 5.5. I am not sure if "Optimizer" is a proper category for this bug, but still this is something to take into account if this use case is typical for your application.
• Bug #69268 - another public bug report from Oracle engineer. It seems that simple query like SELECT * FROM a LEFT JOIN b ON a.id = b.id GROUP BY a.id; may start to return different results in 5.6 comparing to 5.5. One may speculate on how correct is it to rely on extended GROUP BY feature of MySQL, or can this be considered an extended GROUP BY if the right table has only one row, but changes in results for simple queries are always surprising...
• Bug #69233 - this bug is probably related to fraction of seconds now supported for date/time in 5.6 (this was a problem for Percona data recovery tools as well, by the way) and may affect ODBC-based applications. Anyway, inconsistent results of queries are never good.
• Bug #69219 - I am happy to see Oracle MySQL engineers still reporting bugs at public bugs database, but hardly users are happy when statement like CREATE TEMPORARY TABLE ... SELECT is 4+ times slower on 5.6.12 than on their good old 5.1.
•  Bug #69005 - check this if you use ORDER BY LOWER(column) in any query. It may produce wrong results in MySQL 5.6.x easily. As simple as that, and still not fixed.  
•  Bug #68919 - yet another great case when new optimizer feature (DS-MRR in this case) leads to performance regression, again LIMIT involved. Fortunately you can just disable this feature with optimizer_switch="mrr=off";
•  Bug #68897 - one more case of wrong results in MySQL 5.6 when user variables, derived tables, GROUP BY and ORDER BY are involved.

I think we can stop now, to be able to add nice "Top 10" clause to the title.

To summarize, if you plan to upgrade to 5.6 now and use derived tables, user variables, GROUP BY, ORDER BY or LIMIT clauses in your queries, please, review bug reports above, other active optimizer bugs affecting 5.6 (I hope you know how to find them) and text your applications carefully. You should expect both performance regressions and wrong results from MySQL 5.6.12 in these cases, and not all of them are easy to workaround. You may have to disable new optimizations or rewire your queries.