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

关联文章：

• 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的使用限制。

三、总结

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