关于 MySQL bug 91418 一些看法

转载

叶金荣 2021-07-14 15:55:02

文章标签 MySQL 文章分类 MySQL 数据库

今天给大家发一下我的之前写的一篇文章，是关于 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    ;

关于 MySQL bug 91418 一些看法_MySQL

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     | <derived2>       | NULL       | ref  | <auto_key0>   | <auto_key0> | 33      | test.TST.BID |    2 |   100.00 | NULL            ||  2 | DERIVED     | <derived3>       | 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)