尴尬的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.
