尴尬的bug:一条查询语句让MySQL崩溃

这是学习笔记的第 2028 篇文章

前几天睡觉前接到前同事的一个信息，说有个奇怪的SQL问题，想让我帮忙看看，给点建议，我以为是一种非常复杂的SQL,他的反馈能让MySQL崩溃。

我简单看了下，感觉不大可能啊，于是在自己的环境做了测试。

相关的SQL会关联两张表,我们就称为t10和t10_sub吧。

建表语句如下:

CREATE TABLE `t10` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`pid` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8；

CREATE TABLE `t10_sub` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`pid` varchar(10) DEFAULT NULL,

`cid` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8；

接下来我们直接进入正题，首先先来做一个查询，因为表里没有数据，所以查询的过程还是快的，显然是返回一个空集。

mysql> (select sub_tmp.cid, (select count(*) from t10 where pid = sub_tmp.pid ) as new_t10 from t10_sub as sub_tmp order by new_t10 + 1) union (select sub_tmp.id,sub_tmp.id from t10_sub as sub_tmp);

Empty set (0.00 sec)

而奇怪的是如果查看执行计划，则整条语句会导致当前会话崩溃。

mysql> explain (select sub_tmp.cid, (select count(*) from t10 where pid = sub_tmp.pid ) as new_t10 from t10_sub as sub_tmp order by new_t10 + 1) union (select sub_tmp.id,sub_tmp.id from t10_sub as sub_tmp);

ERROR 2013 (HY000): Lost connection to MySQL server during query

当然这个操作是可以复现，通过这个

写入1条数据

mysql> insert into t10(pid) values('test');

mysql> insert into t10_sub(pid,cid) values('test','test100');

数据也没什么特别之处，我们来继续执行查询。

mysql> (select sub_tmp.cid, (select count(*) from t10 where pid = sub_tmp.pid ) as new_t10 from t10_sub as sub_tmp order by new_t10 + 1) union (select sub_tmp.id,sub_tmp.id from t10_sub as sub_tmp);

ERROR 2013 (HY000): Lost connection to MySQL server during query

可以看到这次结果比较统一，执行计划解析和查询操作都会导致会话崩溃。

那目前有什么好的解决办法吗?

经过测试，目前有两种，一种是去除union的子句，一种是对于order by的部分做下调整，原来是order by new_t10+1,现在修改为order by new_t10.

mysql> explain (select sub_tmp.cid, (select count(*) from t10 where pid = sub_tmp.pid ) as new_t10 from t10_sub as sub_tmp order by new_t10 ) union (select sub_tmp.id,sub_tmp.id from t10_sub as sub_tmp);

+----+--------------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

| 1 | PRIMARY | sub_tmp | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |

| 2 | DEPENDENT SUBQUERY | t10 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

| 3 | UNION | sub_tmp | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |

| NULL | UNION RESULT | <union1,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |

+----+--------------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

4 rows in set, 2 warnings (0.00 sec)

当然在这里也是抛砖引玉，想看看大家有什么好的想法和调试办法。

毫无疑问，这是一个bug.