MySQL 一个关于 derived table 的 bug 描述与规避

2023-06-21
福建
本文字数：3630 字
阅读完需：约 12 分钟

关联文章：

MySQL对derived table的优化处理与使用限制

一、Bug 描述

同事遇到一个有意思的语句，说一条 SQL 在 MySQL8.0.25 版本运行出的结果明显与给定的 where 条件不符，而在 8.0.26 版本上是正常的，语句上加了一个无关的用户变量后在 8.0.25 版本上结果才是正确的，想不通这是怎么回事，这么有意思的事情自然引起了我的兴趣，借此机会深入了解了一下 MySQL 关于 derived table 的优化。为了方便演示效果，让小伙伴们关注到现象的本质，我将语句进行了简化处理。

下面是模拟的表结构与数据。

create table t1(id int,c1 varchar(100));
insert into t1 values(1,'gg张三');insert into t1 values(2,'bb李四');insert into t1 values(3,'cc王五');insert into t1 values(4,'dd刘麻子');insert into t1 values(1,'gg张三');insert into t1 values(2,'bb李四');

复制代码

SQL 语句：

SELECT temp.type  FROM (SELECT  SUBSTRING(t.type, 3) type          FROM (SELECT distinct t1.c1 type                  FROM test.t1                 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;

复制代码

在 MySQL8.0.25 版本的运行结果如下：

mysql> SELECT temp.type    ->   FROM (SELECT  SUBSTRING(t.type, 3) type    ->           FROM (SELECT distinct t1.c1 type    ->                   FROM test.t1    ->                  ORDER BY t1.c1) t) temp    ->  WHERE temp.type='张三'    ->  ORDER BY temp.type DESC;+--------+| type   |+--------+| 李四   |+--------+1 rows in set (0.01 sec)

复制代码

在 MySQL8.0.26 版本的运行结果如下：

mysql> SELECT temp.type    ->   FROM (SELECT  SUBSTRING(t.type, 3) type    ->           FROM (SELECT distinct t1.c1 type    ->                   FROM test.t1    ->                  ORDER BY t1.c1) t) temp    ->  WHERE temp.type='张三'    ->  ORDER BY temp.type DESC;+--------+| type   |+--------+| 张三   |+--------+1 rows in set (0.00 sec)

复制代码

很明显，这个语句在 8.0.25 版本运行出的结果与我们给定 where 条件不符，我们要查询关于“张三”的记录，结果返回的结果是”李四“的，很明显的一个 bug，但是到 8.0.26 版本这个问题得到了修正。

怀着对各版本对此语句执行情况的好奇，我先是往前追溯，查看了 8.0.24，8.0.23，8.0.22，8.0.21，5.7.39 版本上做了测试，发现在 8.0.24，8.0.23，8.0.22 版本结果与 8.0.25 相同，都是错误结果，而在 8.0.21 版本上运行结果是正确的，5.7 版本上结果也是正确的的。往后追溯，8.0.26，8.0.32 版本也都是正确的，因此判断此问题在 8.0.22~8.0.25 版本上存在此问题。

这个语句最大的特点就是运用了派生表（derived table）,MySQL 在 8.0.22 版本上引入了一个关于派生表的优化器开关derived_condition_pushdown, 默认设置为 on。我们看一下关于这个特性在官方文档中的描述：

MySQL 8.0.22 and later supports derived condition pushdown for eligible subqueries. For a query such as SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant, it is possible in many cases to push the outer WHERE condition down to the derived table, in this case resulting in SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt.

在 8.0.26 版本中修复的 bug 中发现一个与此问题相关的 bug。描述如下：

When a condition is pushed down to a materialized derived table, a clone of the derived table expression replaces the column (from the outer query block) in the condition. When the cloned item included a FULLTEXT function, it was added to the outer query block instead of the derived table query block, which led to problems. To fix this, we now use the derived query block to clone such items. (Bug #32820437)

看到这里我们可以确定，就是 8.0.22 版本时这个新特性的引入，导致了此问题的产生，庆幸的是这个问题在 8.0.26 版本中已得到解决。

文章开头说的问题语句跟这个 bug 的描述是吻合的，派生表 temp 外部的过滤条件 temp.type=’张三' 其实是substring(t.type,3)='张三'，应该就是对应 bug 描述中的”the cloned item included a FULLTEXT function“不管 substring 函数是不是 fulltext 函数，总之这个问题伴随着这个 bug 的修复也修复了。这种语句结构下，很多函数都有这个问题，比如 trim，replace 等。MySQL 内部如何处理得到的错误结果我们就不去深究了，但是如何规避这个 bug 我们需要了解一下。

二、bug 规避

升级到 8.0.26 及以上的版本问题自然就解决了，如果不想升级也是有很多方式来规避此问题的。这个 bug 的产生主要是因为新特性derived_condition_pushdown的引入，关闭此特性，在这几个版本中就不会出现这个问题。

mysql> set optimizer_switch="derived_condition_pushdown=off";Query OK, 0 rows affected (0.00 sec)
mysql> SELECT temp.type    ->   FROM (SELECT  SUBSTRING(t.type, 3) type    ->           FROM (SELECT distinct t1.c1 type    ->                   FROM test.t1    ->                  ORDER BY t1.c1) t) temp    ->  WHERE temp.type='张三'    ->  ORDER BY temp.type DESC;+--------+| type   |+--------+| 张三   |+--------+1 rows in set (0.00 sec)

复制代码

优化器开关里还有一个派生表相关的开关，就是 derived_merge，是否进行派生表合并。关闭这个 derived_merge，结果也是正确的。

mysql> set optimizer_switch="derived_merge=off,derived_condition_pushdown=on";Query OK, 0 rows affected (0.00 sec)
mysql> SELECT temp.type    ->   FROM (SELECT  SUBSTRING(t.type, 3) type    ->           FROM (SELECT distinct t1.c1 type    ->                   FROM test.t1    ->                  ORDER BY t1.c1) t) temp    ->  WHERE temp.type='张三'    ->  ORDER BY temp.type DESC;+--------+| type   |+--------+| 张三   |+--------+1 rows in set (0.00 sec)

复制代码

也就是说当派生表条件下推撞上派生表合并时，数据库做的处理不对，导致了问题的产生。

因此只要控制不发生合并，或者不发生条件下推，就能规避此 bug。除了关闭优化器开关，在语句级别我们还有很多方式来规避，下面列举几个。

1.使用 NO_MERGE 的 hint 来阻止 derived table 合并。

SELECT  /*+ NO_MERGE(temp) */ temp.type  FROM (SELECT substring(t.type,3) type          FROM (SELECT distinct t1.c1 type                  FROM test.t1                 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;

复制代码

2.使用NO_DERIVED_CONDITION_PUSHDOWN的 hint 阻止条件下推。

SELECT  temp.type  FROM (SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(t) */ substring(t.type,3) type          FROM (SELECT  distinct t1.c1 type                  FROM test.t1                 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;

复制代码

3.使用 limit 子句，能同时阻止合并与条件下推。

例如：

SELECT temp.type  FROM (SELECT substring(t.type,3) type          FROM (SELECT distinct t1.c1 type                  FROM test.t1                 ORDER BY t1.c1) t limit 100000000000) temp WHERE temp.type='张三' ORDER BY temp.type DESC;

复制代码

4.分配用户变量，阻止 derived table 合并。

例如：

SELECT temp.type  FROM (SELECT (@i:=0) as num, substring(t.type,3) type          FROM (SELECT distinct t1.c1 type                  FROM test.t1                 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;

复制代码

这种方式就是前文提到的，为什么加了一个与业务逻辑无关的用户变量，结果就正确的原因。

5.使用 union all 来阻止 derived table 合并

SELECT temp.type  FROM (SELECT substring(t.type,3) type          FROM (SELECT distinct t1.c1 type                  FROM test.t1                 ORDER BY t1.c1) t                 union all                  select '1') temp WHERE temp.type='张三' ORDER BY temp.type DESC;

复制代码

这些方法主要是依据优化器使用 hint 灵活控制优化器的开关，以及derive_merge与derived_condition_pushdown的使用限制。

三、总结

MySQL8.0.22~MySQL8.0.25 因为优化器新特性derived_condition_pushdown带来的 bug，可以通过 derived merge 与 derived_condition_pushdown 的使用限制以及优化器开关 hint 来有效规避 bug，当然升级到高版本更好。
如果想让新特性derived_condition_pushdown发挥作用，就要避开它的使用限制。
一个新特性的出现，不可避免会伴随着一些 bug 的产生，不要对此心存恐惧，只要深入了解它，就能取其长，避其短。

发布于: 刚刚阅读数: 2

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

发布

暂无评论

创作场景

MySQL 一个关于 derived table 的 bug 描述与规避

关联文章：

一、Bug 描述

二、bug 规避

三、总结

GreatSQL

评论