Lately, I worked on several queries which started returning wrong
results after upgrading MySQL Server to version 5.7 The reason
for the failure was derived merge optimization which is one of
the default
optimizer_switch
options. Issues were solved, though at the price of
performance, when we turned it
OFF
. But, more importantly, we could not predict if any other
query would start returning incorrect data, to allow us to fix
the application before it was too late. Therefore I tried to find
reasons why
derived_merge
can fail. Analyzing the problem
In the first run, we turned SQL Mode
ONLY_FULL_GROUP_BY
on, and this removed most of the problematic queries. That said,
few of the queries that were successfully working with
ONLY_FULL_GROUP_BY
were affected.
A quick search in the …
[Read more]