Working on query optimizer bugs can be a rather frustrating experience. First, as soon as some query doesn't run as fast it theoretically could people will consider it a bug. On one hand that's great, you get a constant stream of user input, but on the other hand you end up with a whole pile of "bugs" which you can't hope to finish.

What's more frustrating is that even if you manage to create a fix for an optimizer bug, there are chances it won't be allowed into next GA (currently 5.0.70) or approaching-GA (currently 5.1.30) release (GA is our term for "stable" or "release").

The reason behind this is that most optimizer bugfixes cause the optimizer to pick different query plans, and there's no way to guarantee that the fix will be a change for the better for absolutely everyone. Experience shows that it is possible to have a query that hits two optimizer bugs/deficiencies at once in such a way that they cancel each other out, and …

We (me coding, Igor and Evgen reviewing) have fixed BUG#32198. According to Support/Bugs team, the bug was causing pain to a number of people, so I thought it deserved a post. The problem was as follows:

• Before MySQL 5.0.42, comparisons in form

    date_column CMP datetime_const

  were interpreted as comparisons of DATEs (CMP is one of =, <, > , <=, or >=). The time part of datetime_const was ignored.

• In 5.0.42 we've fixed it to perform in a way that is closer to the SQL standard: the comparisons are now resolved by comparing values as DATETIMEs. Our fault was that "date_column CMP datetime_const" stopped being sargable. It turned out there quite a few people who had queries like

        SELECT ... WHERE date_column < NOW() ... 

  and those queries became awfully slow.

  …