MySQL optimizer bugsLately, 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 MySQL bugs database gave me a not-so-short list of open bugs:

At first glance, the reported queries do not follow any pattern, and we still cannot quickly identify which would break and which would not.

Then I took a second look by running all of the provided test cases in my environment and found that for four bugs, the optimizer rewrote the query. For three of the bugs, it rewrote in both 5.7 and 8.0, and one case it rewrote in 8.0 only.

The remaining three buggy queries (Bug #85117, Bug #91418, Bug #91878) have things in common. Let’s first look at them:

  1. Bug #85117
  2. Bug #91418
  3. Bug #91878

Two of the queries use DISTINCT  or GROUP BY , one uses ORDER BY  clause. The cases do not have not the same clause in common—which is what I’d expect to see—and so, surprisingly, these are not the cause of the failure. However, all three queries use generated values: a constant in the first one; UUID()  and COUNT()  functions in the second and third respectively. This similarity is something we need to investigate.

To find out why derived_merge  might work incorrectly for these queries we need to understand how this optimization works and why it was introduced.

The intent behind derived_merge

First I recommend checking the official MySQL User Reference Manual and MariaDB knowledge base. It is correct to use both manuals: even if low-level implementations vary, the high-level architecture and the purpose of this optimization are the same.

In short: derived_merge  is used for queries that have subqueries in the  FROM  clause,  also called “derived tables” and practically converts them into JOIN queries. This optimization allows avoiding unnecessary materialization (creating internal temporary tables to hold results). Virtually this is the same thing as a manual rewrite of a query with a subquery into a query that has JOIN clause(s) only. The only difference is that when we rewrite queries manually, we can compare the expected and actual result, then adjust the resulting query if needed. The MySQL optimizer has to do a correct rewrite at the first attempt. And sometimes this effort fails.

Let’s check why this happens for these particular queries, reported in the MySQL Bugs Database.

Case Study 1: a Query from Bug #85117

Original query

was rewritten to:

You can always find a query that the optimizer converts the original one to in the SHOW WARNINGS output following EXPLAIN [EXTENDED] for the query.

In this case, the original query asks to return all rows from the table table1 , but selects only the generated field from the subquery. The subquery selects the only row with table1id=1 .

Avoiding derived merge optimization is practically the same as joining table table1 with a table with one row. You can see how it works in this code snippet:

However, when the optimizer uses derived-merge optimization, it completely ignores the fact that the resulting table has one row, and that the calculated value would be either NULL  or 1 depending if a row corresponding to table1  exists in the table. That it prints select 1 AS `sel`  in the EXPLAIN  output while uses select NULL AS `sel`  does not change anything: both are wrong. The correct query without a subquery should look like:

This report is the easiest of the bugs we will discuss in this post, and is also fixed in MariaDB.

Case Study 2: a Query from Bug #91418

This query should create a unique DIST_UID  for each unique BID name. But, instead, it generates a unique ID  for each row.

First, let’s split the query into a couple of queries using temporary tables, to confirm our assumption that it was written correctly in the first place:

With temporary tables, we have precisely four unique DIST_UID  values unlike the six values that our original query returned.

Let’s check how the original query was rewritten:

You can see that the optimizer did not wholly remove the subquery here. Let’s run this modified query, and run a test with a temporary table one more time:

This time the changed query result is no different to the one we received from the original one. Let’s manually replace the subquery with temporary tables, and check if it affects the result again.

In this case, the temporary table contains the correct number of rows: 4, but the outer query calculates a  UUID  value for all rows in the table TEST_SUB_PROBLEM . It does not take into account that the user initially asks for a unique UUID  for each unique BID  and not each unique UID . Instead, it just moves a call of UUID()  function into the outer query, which creates a unique value for each row in the table TEST_SUB_PROBLEM . It does not take into account that the temporary table contains only four rows. In this case, it would not be easy to build an effective query that generates distinct UUID  values for rows with different BID ‘s and the same UUID  values for rows with the same BID .

Case Study 3: a Query from Bug #91878

This query is supposed to calculate a number of rows based on complex conditions:

However, it returns no rows when it should return 22 (check the bug report for the full test case).

To find out why this happens let’s perform a temporary table check first.

The rewritten query returned the correct result, as we expected.

To identify why the original query fails, let’s check how the optimizer rewrote it:

Interestingly, when I run this query on the original tables it returned all 18722 rows that exist in table t2 .

This output means that we cannot entirely rely on the  EXPLAIN  output. But still we can see the same symptoms:

  • Subquery uses a function to generate a value
  • Subquery in the FROM  clause is converted into a  JOIN, and its values are accessible by an outer subquery

We also see that the query has GROUP BY  and HAVING  clauses, thus adding a complication.

The query is almost correct, but in this case, the optimizer mixed aliases: it uses the same alias in the internal query as in the external one. If you change the alias from t  to t2  in the subquery, the rewritten query starts returning correct results:

While the calculated value is not the reason why this query returns incorrect results, it is similar to the previous examples because the optimizer does not take in account that the value of `test`.`t`.`f1`  in the outer query is not necessarily equal to 731834939448428685.

Is also interesting that neither Oracle nor PostgreSQL accept such a query, and instead complain of improper use of the  GROUP BY clause. Meanwhile, MySQL accepts this query even with SQL Mode set to ONLY_FULL_GROUP_BY . Reported as bug #92020.

Conclusion and recommendations

While derived_merge  is a very effective optimization, it can rewrite queries destructively. Safety measures when using this optimization are:

  1. Make sure that you use the latest version of MySQL/Percona/MariaDB servers which include all of the new bug fixes.
  2. Generated values for the subquery results either constant or returned values of functions is the red flag.
  3. Relaxing SQL Mode ONLY_FULL_GROUP_BY  is always dangerous and should not be used together with derived_merge .

As a last resort, you can consider rewriting queries to JOIN  manually or turning derived_merge  optimization OFF .

 

3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Leo

Hi Sveta Smirnova,

Which Mariadb version fix the Bug #85117 ?

thanks.

0
Sveta Smirnova

I tested with 10.3.9

0
Alexandre Guerra

Hi Sveta. Really thanks for digging into the issue. There are a lot of quirks when migrating from 5.5 to newer versions that are often overlooked. Sure that some ugly mysql practices should be banned, but its a pain just to push too many fixes over the ‘agile’ world.

0