Fun with Bugs #23 - more on Optimizer bugs in MySQL 5.6

When I've sent CV to MySQL AB back in 2004 (or early 2005) I had actually wanted to become a developer there. As a person who just started to use MySQL on a regular basis and, at the same time, had to explain dozens of engineers per month how optimizers works in Oracle and Informix RDBMSes, and optimize queries for them from time to time, I was naturally interested in adding missing (but well known to me) features to MySQL optimizer (from hash joins to stored outlines, histograms and tracing, all things I've noted as extremely useful for many real life cases)... So, I wanted to work on MySQL optimizer specifically, if something related to MySQL at all.

It happened so that MySQL Support had somehow noted my CV before anybody else, so in few months and after some serious tests and long screening talks (the longest of them was with new, at the time, optimizer developer there) I've ended up in Support and (lucky I am) I've stuck in Bugs Verification Team there, and I had never regret about this... But interest to MySQL optimizer remained. So, soon enough, I've ended up as a person who worked on more optimizer bug reports than anybody else in Support and, later, I've become an "Optimizer Support Coordinator", whatever that meant. Among other things it meant very hot discussions with Optimizer team in development (now all of them whom I worked with are working on MariaDB...), that started with statements like "there are no bugs in Optimizer other than crashes" but ended as a useful cooperation, and mutual understanding (and maybe even some understanding from my side why optimizer really works the way it works, not much...)

Time flies, but I am still interested in MySQL optimizer improvements (it's still so far from what I took as granted in other databases even before 2005). That's why I was surprised and even felt deeply insulted to see just a dozen of visitors on a great session on optimizer development by MariaDB engineers at PLMCE 2013... That's also why I devote this issue of "Fun with Bugs" to recent optimizer bugs, again (just 2 months after previous issue on this topic).

So, make sure you do not miss these recently verified optimizer bugs affecting MySQL 5.6:

• Bug #70247 - "Using many WHERE conditions makes range scan disabled". We have a hardcoded limit in this case, nothing related to number of rows in the table or even less cost. Take care if you use some software that creates long list of values to compare against with IN - you can easily end up with full table scan.
• Bug #70236 - some note on debug builds, why should we even care? Well, when the most famous and productive bug reporter of all times reports something, we should pay attention. Impact is not clear, but this kind of bugs surely affects at least any reasonable and regular QA efforts. Also note version affected, 5.6.15 - it means that MySQL 5.6.14 is probably already cloned off, and the problem we see here will affect this release. So, whatever the problem is, it will be with us for 2-3 months for sure.
• Bug #70220 - "Grouping with a view may report 'Invalid use of group function'". Yet another example of public bug report by Oracle engineer. This is great and what I've asked for in this blog more than once. Everybody should report any MySQL bug (with some exceptions for security bugs and bugs having customer-sensitive data) in public, at http://bugs.mysql.com, no matter what company he works for. I am happy to see this happening!
• Bug #70038 - "Wrong select count distinct with a field included in two-column unique key". Take care while adding UNIQUE keys to InnoDB tables in MySQL 5.5 and 5.6 - this may lead to wrong results for some queries.
• Bug #70021 - "Poor execution of a plan with unnecessary "range checked for each record". Looks like all versions may have problems with queries like this:
  
  

  select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
  

  That is, you've just added new index on key2 column, or added additional condition on indexed column, and get notably worse performance. Unexpected and weird, but this happens. Note also yet another example of a great report from MariaDB engineer here (hardly anybody in the world knows more about the way MySQL optimizer works, by the way). You may think whatever you want about Monty and his interviews about MariaDB, Oracle or MySQL future, but engineers working on MariaDB contribute a lot, especially in area of Optimizer. Do respect this, please.
• Bug #70014 - "MySQL crashes on explain with JSON formatting (debug builds)". Again, any regular QA efforts are affected by this kind of bugs, so they should not be silently ignored because of low impact.
• Bug #69841 - "SELECT COUNT(DISTINCT a,b) incorrectly counts rows containing NULL". Any good list of bugs should contain regression ones. Here we have a regression comparing to MySQL 5.1, that returns correct results. This is NOT the first optimizer regression comparing to 5.1, and surely not the first bug with DISTINCT. So, take extra care if you upgrade from 5.1 to latest and greatest MySQL 5.6 (or well tested MySQL 5.5, for that matter...) I am still going to proudly wear my MySQL 5.1 T-shirt on any public conference - speaking about quality of GA release, MySQL 5.1 was not better than MySQL 5.6, but we still see cases where it works better than any newer GA releases (unfortunately).

Let's stop at this point. Now I am back from vacation and celebrations of one year outside Oracle, so I plan to make regular posts about MySQL bugs again. Stay tuned!