关于 MySQL bug 91418 一些看法

导读：

郑松华，知数堂SQL 优化班老师
现任 CCmediaService DBA，主要负责数据库优化相关工作
擅长SQL优化，数据核对

想阅读更多内容请点击订阅专栏

大家好，我是知数堂SQL 优化班老师网名：骑龟的兔子

今天给大家发一下我的之前写的一篇文章，是关于 MySQL bug 91418

我们看bug 和学习处理方式的一个重要原因是以后碰到类似问题，不仅仅在MySQL。

在别的数据库或者语言中也可以触类旁通增加自己能力。

下面是bug链接

https://bugs.mysql.com/bug.php?id=91418

create table TEST_SUB_PROBLEM(UID integer PRIMARY KEY AUTO_INCREMENT,BID VARCHAR(10),THING_NAME VARCHAR(100),OTHER_IFO VARCHAR(100));
insert into TEST_SUB_PROBLEM(BID,THING_NAME,OTHER_IFO)values ('thing1','name1','look a chicken'),( 'thing1','name1','look an airplane'),('thing2','name2','look a mouse'),('thing3','name3','look a taperecorder'),('thing3','name3','look an explosion'),('thing4','name4','look at the stars');
 select    TST.UID,TST.BID,TST.THING_NAME,TST.OTHER_IFO,vw2.DIST_UIDfrom TEST_SUB_PROBLEM TSTjoin (    select uuid() as DIST_UID, vw.*    from (    select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM    ) vw    ) vw2 on vw2.BID = TST.BID    ;

bug 的问题就是

select uuid() as DIST_UID, vw.*    from (


    
    select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM    ) vw    +--------------------------------------+--------+------------+| DIST_UID                             | BID    | THING_NAME |+--------------------------------------+--------+------------+| 00a993b0-cc29-11e8-9fff-080027158a34 | thing1 | name1      || 00a993d6-cc29-11e8-9fff-080027158a34 | thing2 | name2      || 00a993df-cc29-11e8-9fff-080027158a34 | thing3 | name3      || 00a993e5-cc29-11e8-9fff-080027158a34 | thing4 | name4      |+--------------------------------------+--------+------------+  
这里 DIST_UID 应该是4个 值 但是 原来的SQL 出现了6个不同的值我查看了下原因 如下 root@mysql3308.sock>[test]>show warnings\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* select#1 */ select `test`.`TST`.`UID` AS `UID`,`test`.`TST`.`BID` AS `BID`,`test`.`TST`.`THING_NAME` AS `THING_NAME`,`test`.`TST`.`OTHER_IFO` AS `OTHER_IFO`,uuid() AS `DIST_UID` from `test`.`TEST_SUB_PROBLEM` `TST` join (/* select#3 */ select distinct `test`.`TEST_SUB_PROBLEM`.`BID` AS `BID`,`test`.`TEST_SUB_PROBLEM`.`THING_NAME` AS `THING_NAME` from `test`.`TEST_SUB_PROBLEM`) `vw` where (`vw`.`BID` = `test`.`TST`.`BID`)1 row in set (0.00 sec)

从上面的show warnings 可以发现

uuid() AS `DIST_UID` 这个部分经过MySQL 转换之后挪到了最上层所以导致最终有六个不同的值

我的环境是8.0 可以使用如下hint 就可以解决 
select   /*+ set_var(optimizer_switch = 'derived_merge=off' ) */TST.UID,TST.BID,TST.THING_NAME,TST.OTHER_IFO,vw2.DIST_UIDfrom TEST_SUB_PROBLEM TSTjoin (    select uuid() as DIST_UID, vw.*    from (    select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM    ) vw    ) vw2 on vw2.BID = TST.BID    ;   +-----+--------+------------+---------------------+--------------------------------------+| UID | BID    | THING_NAME | OTHER_IFO           | DIST_UID                             |+-----+--------+------------+---------------------+--------------------------------------+|   1 | thing1 | name1      | look a chicken      | 3d65599a-cc26-11e8-9fff-080027158a34 ||   2 | thing1 | name1      | look an airplane    | 3d65599a-cc26-11e8-9fff-080027158a34 ||   3 | thing2 | name2      | look a mouse        | 3d655a0b-cc26-11e8-9fff-080027158a34 ||   4 | thing3 | name3      | look a taperecorder | 3d655a1a-cc26-11e8-9fff-080027158a34 ||   5 | thing3 | name3      | look an explosion   | 3d655a1a-cc26-11e8-9fff-080027158a34 |


    
|   6 | thing4 | name4      | look at the stars   | 3d655a25-cc26-11e8-9fff-080027158a34 |+-----+--------+------------+---------------------+--------------------------------------+6 rows in set (0.00 sec)
+----+-------------+------------------+------------+------+---------------+-------------+---------+--------------+------+----------+-----------------+| id | select_type | table            | partitions | type | possible_keys | key         | key_len | ref          | rows | filtered | Extra           |+----+-------------+------------------+------------+------+---------------+-------------+---------+--------------+------+----------+-----------------+|  1 | PRIMARY     | TST              | NULL       | ALL  | NULL          | NULL        | NULL    | NULL         |    6 |   100.00 | Using where     ||  1 | PRIMARY     |        | NULL       | ref  |    |  | 33      | test.TST.BID |    2 |   100.00 | NULL            ||  2 | DERIVED     |        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL         |    6 |   100.00 | NULL            ||  3 | DERIVED     | TEST_SUB_PROBLEM | NULL       | ALL  | NULL          | NULL        | NULL    | NULL         |    6 |   100.00 | Using temporary |+----+-------------+------------------+------------+------+---------------+-------------+---------+--------------+------+----------+-----------------+  从 show warnings 部分可以看出  uuid() AS `DIST_UID 在id =2 部分 运行出结果了 所以没有结果有4个值！
root@mysql3308.sock>[test]>show warnings\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* select#1 */ select /*+ SET_VAR(optimizer_switch='derived_merge=off') */ `test`.`TST`.`UID` AS `UID`,`test`.`TST`.`BID` AS `BID`,`test`.`TST`.`THING_NAME` AS `THING_NAME`,`test`.`TST`.`OTHER_IFO` AS `OTHER_IFO`,`vw2`.`DIST_UID` AS `DIST_UID` from `test`.`TEST_SUB_PROBLEM` `TST` join (/* select#2 */ select uuid() AS `DIST_UID`,`vw`.`BID` AS `BID`,`vw`.`THING_NAME` AS `THING_NAME` from (/* select#3 */ select distinct `test`.`TEST_SUB_PROBLEM`.`BID` AS `BID`,`test`.`TEST_SUB_PROBLEM`.`THING_NAME` AS `THING_NAME` from `test`.`TEST_SUB_PROBLEM`) `vw`) `vw2` where (`vw2`.`BID` = `test`.`TST`.`BID`)1 row in set (0.00 sec) s/82991850

上面的问题是因为MySQL5.7 开始的视图合并功能引起的，如果是MySQL5.6 角度看的话确实是bug，因为跟5.6 结果是不同的。

但是从另一个角度上说，这个也是无法解决的Bug。

因为这个问题的根本原因有一方面是视图合并，但更重要的是类似uuid() 这样的随着时间或者次数不停变化的函数特性，导致这种问题必然存在。

类似的函数还有sysdate()

root@mysql3308.sock>[test]>select        -> TST.UID    -> ,TST.BID    -> ,TST.THING_NAME    -> ,TST.OTHER_IFO    -> ,vw2.DIST_UID     -> from TEST_SUB_PROBLEM TST    -> join (    ->     select sysdate() as DIST_UID, vw.*  ,sleep(1) s    ->     from (    ->     select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM    ->     ) vw    ->     ) vw2 on vw2.BID = TST.BID    ->     ;+-----+--------+------------+---------------------+---------------------+| UID | BID    | THING_NAME | OTHER_IFO           | DIST_UID            |+-----+--------+------------+---------------------+---------------------+|   1 | thing1 | name1      | look a chicken      | 2018-10-10 10:21:42 ||   2 | thing1 | name1      | look an airplane    | 2018-10-10 10:21:42 ||   3 | thing2 | name2      | look a mouse        | 2018-10-10 10:21:42 ||   4 | thing3 | name3      | look a taperecorder | 2018-10-10 10:21:42 ||   5 | thing3 | name3      | look an explosion   | 2018-10-10 10:21:42 ||   6 | thing4 | name4      | look at the stars   | 2018-10-10 10:21:42 |+-----+--------+------------+---------------------+---------------------+6 rows in set (0.00 sec)
root@mysql3308.sock>[test]>select   /*+ set_var(optimizer_switch = 'derived_merge=off' ) */    -> TST.UID    -> ,TST.BID    -> ,TST.THING_NAME    -> ,TST.OTHER_IFO    -> ,vw2.DIST_UID     -> from TEST_SUB_PROBLEM TST    -> join (    ->     select sysdate() as DIST_UID, vw.* ,sleep(1) s    ->     from (    ->     select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM    ->     ) vw    ->     ) vw2 on vw2.BID = TST.BID    ->     ;
+-----+--------+------------+---------------------+---------------------+| UID | BID    | THING_NAME | OTHER_IFO           | DIST_UID            |+-----+--------+------------+---------------------+---------------------+|   1 | thing1 | name1      | look a chicken      | 2018-10-10 10:22:04 ||   2 | thing1 | name1      | look an airplane    | 2018-10-10 10:22:04 ||   3 | thing2 | name2      | look a mouse        | 2018-10-10 10:22:05 ||   4 | thing3 | name3      | look a taperecorder | 2018-10


    
-10 10:22:06 ||   5 | thing3 | name3      | look an explosion   | 2018-10-10 10:22:06 ||   6 | thing4 | name4      | look at the stars   | 2018-10-10 10:22:07 |+-----+--------+------------+---------------------+---------------------+6 rows in set (4.00 sec)

谢谢大家~ 欢迎转发

如有关于SQL优化方面疑问需要交流的，请加入QQ群（579036588），并@骑兔子的龟 就可与我联系

END

点击下图小程序订阅

《SQL优化专栏》

get更多优化技能

扫码加入MySQL技术Q群
（群号：579036588）
   