关于数据库:MySQL一个关于derived-table的bug描述与规避

MySQL 对 derived table 的优化解决与应用限度

共事遇到一个有意思的语句，说一条 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 咱们须要理解一下。

降级到 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 的产生，不要对此心存恐怖，只有深刻理解它，就能取其长，避其短。

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

关于数据库:MySQL一个关于derived-table的bug描述与规避

关联文章：

一、Bug 形容

二、bug 躲避

三、总结

GreatSQL 社区：

技术交换群：