Fun with Bugs #28 - regression bugs in MySQL 5.6

2013 was a great year for MySQL Community. New MySQL 5.6 GA release with its increased throughput, scalability and new features as well as more interaction and cooperation with MySQL Community from Oracle side brought us a lot of new perspectives and good feelings over the year.

Unfortunately new MySQL 5.6 GA release also reminded about old and well known problem with new MySQL versions. They all introduce new regression bugs. MySQL 5.6 had not become an exception.

Note that according to good old tradition (that I hope will be followed in 2014) bugs that demonstrate a regression (make some feature that previously worked stop functioning as intended in a new release) are marked with "regression" tag at http://bugs.mysql.com. So, it's easy to find them, and here is the list of regression bugs that affect MySQL 5.6 (sometimes regression bug happens in several major versions, as fixes also happens in several versions).

You'll see just 31 bugs in the list of active ones affecting 5.6. Do not become very optimistic because of this. Unfortunately good tradition to use "regression" tag is not followed by some Oracle engineers , so some bugs clearly demonstrating a regression are not tagged properly. Check well known Bug #69623, "since 5.5.32 & 5.6.12, innodb cant start with own multi-file tablespace", for example. The fact that it's a regression is clear from the phrase above even (synopsis), but still I do not see a "regression" tag.

So, regressions still happens (and sometimes it's hard to note/find them before you hit them), even though we all know that "Automated testing and well-written test cases can reduce the likelihood of a regression.". It seems we have reasons to suspect that either test cases development, or proper automated testing (or both) is still far from perfect in Oracle (and maybe only a bit better in other companies that provide MySQL builds or forks).




To give you some ideas of what kind of regression bugs were noted in MySQL 5.6 in 2013, let me recent 10 verified regression bugs:

• Bug #71244, "Wrong result computation using ALL() and GROUP BY". Older versions, like 5.6.11, do not have this problem. It can be easily workarounded by NOT using alias of aggregated column in HAVING clause, but still it's sad to see that this simple enough kind of query is not covered by regression tests.
• Bug #71220, "pow() function returns an error for bad values". It's a representative of an interesting class of "regression"-like bugs. New versions change something in good old MySQL behavior, to make it "better", more close to SQL Standard or "improve" it. But this improvement affects users who upgrade, and manual does not help them to be better prepared for this kind of "improvement", as this change is not listed as incompatible in any prominent place. Just a change to better that ends up as a regression from user's point of view.
• Bug #71095, "Wrong results with PARTITION BY LIST COLUMNS()". I've reported it based on customer issue after we found a workaround (different way to partition a table). Still, it was a very bad surprise (as most regression bugs) and it took notable efforts to reduce the problem to a simple test case. Bug got immediate attention and simple patch from Mattias Jonsson (patch solved the problem according to my test), so I hope to see the fix in 5.6.16.
• Bug #71055, "Using IF EXISTS(SELECT * ...) acquires a lock when using read uncommitted". READ UNCOMMITTED isolation level had never been widely used and thus one should not expect it to be well tested, but on the other hand locking SELECT is even less expected with this level. It seems READ UNCOMMITTED is not covered by regression tests properly, otherwise Oracle could not miss this regression/change of behavior comparing to MySQL 5.1.x.
• Bug #70819, "SHOW ENGINE INNODB MUTEX does NOT work with timed_mutex properly". I've noted this undocumented change in behavior of 5.6 vs 5.5 while working on my PERFORMANCE_SCHEMA-related presentation. It's clear that in MySQL 5.7+ PERFORMANCE_SCHEMA will probably replace not only SHOW PROFILE, but also some other SHOW statements. But while functionality is there officially (according to the manual), it should not just disappear, whatever good intention one may have or whatever corner case we speak about.
• Bug #70617, "Default persistent stats can cause unexpected long query times". This is a good example of visible performance regression related to a new feature and lack of details on how it works or how to use it properly. For poor users it's just like MySQL 5.6 started to work really slow on some queries in some cases. Then it take many hours (if not days) for several people to really find out what's wrong, fix real life case with a workaround and then reduce it to something that can be accepted as a verified bug. I had entire post about this specific case. It has a lot of details about the process of forcing Oracle to consider this case seriously...
• Bug #70598, "Premature expression evaluation prevents short-circuited conditionals". One more case when code that just worked in MySQL 5.1 stopped working after upgrade to 5.5, and none of the never versions had not helped. Workaround exists, but if user has to change the code just because of upgrade, and had no way to find out that is is going to be needed before he hit the bug, it's already bad.
• Bug #70491, "SELECT DISTINCT may return a wrong result if a join buffer is involved". One more case of the optimizer-related regressions, and ones that could be found by proper QA. It was reported by Igor Babaev from MariaDB. As a side note, too many bugs in 5.6 are related to all cases and kinds of statements involving aggregation. Something to think about.
• Bug #70466, "No results when filesorting with a correlation in subquery's HAVING clause". Again HAVING and regression in MySQL 5.6. Oracle started to change optimizer in MySQL 5.6, based on old MySQL 6.0 ideas and some new ones, but it's clear that new developments in this area are NOT properly supported by regression tests.
• Bug #70351, "ALTER TABLE ADD CONSTRAINT xxx FOREIGN KEY adds two constraints". Sometimes fixes/changes happen in all major MySQL versions. In cases like these one has to care a lot to test properly, as change or related regression may affect wide user base. This bug is just an example of a change that led to regression in all versions after just a minor upgrade.

I should stop at this stage, as it's time for a New Year wishes. I with all Oracle MySQL engineers to care about regression testing properly!

And to Oracle MySQL managers I wish to recognize the efforts of their colleagues who process bugs from community, and to award those who really work and care. As a hint, note who worked on the bugs mentioned above, who verified most of them (5 out of 10), who added more test cases and who contributed patches promptly. These are your most valuable assets, and I hope you will care about them properly!