MySQL derived_merge优化的bug 导致查询结果不正确 【官方已确认Bug】

1、重现问题

当派生表中含有非确定性函数时，MySQL自动优化的派生表合并，将导致查询的结果不及预期
请看下面SQL

with a as (
select sysdate() as val
from (values row(1)) a)
select  
a.val,sleep(1),a.val
from a;

with a as (
select uuid() as val
from (values row(1)) a)
select  
a.val,a.val
from a;

with a as (
select rand() as val
from (values row(1)) a)
select 
a.val,a.val
from a;
复制

上面的语句都将导致查询的最终结果 两个val值并不相等
如下图

按语义两个val值是应该相等的

2.解决问题

我添加hint后

with a as (
select sysdate() as val
from (values row(1)) a)
select  /*+ set_var(optimizer_switch='derived_merge=off')*/
a.val,sleep(1),a.val
from a;

with a as (
select uuid() as val
from (values row(1)) a)
select  /*+ set_var(optimizer_switch='derived_merge=off')*/
a.val,a.val
from a;

with a as (
select rand() as val
from (values row(1)) a)
select /*+ set_var(optimizer_switch='derived_merge=off')*/
a.val,a.val
from a;

复制

三个结果的val值均相等了。

我在8.0.13 与 8.0.32 版本上均做了验证。都有此问题存在
8.0.13不支持 vaules 这种写法。将values row(1) 替换为select 1 即可

我认为官方应该修复此bug，当派生表中有不确定函数时，即使derived_merge=on,也需要阻止派生表的合并
我给官方提了一个bug,已被官方确认。117155