来自 5.7 的 mysql 8 查询回归(有什么问题？)

Question

5.7 中的查询在 8.0.23 中不再有效。
针对 windows 8.0.23 x64 和 linux docker 容器 8.0.23 进行了测试。
当前sql模式:
ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_ENGINE_SUBSTITUTION
这不是最聪明的 sql，但这不是重点。
TL;博士:
关键是“bob”行应该显示在最后一个查询中(如 5.7 中)，但不再显示在 8 中。
详情:
在 4 个表设置和插入后，查询会逐步构建，并使用之前的查询来证明错误。最里面的 select 应该什么也没找到。中间选择只是重复。最后一个查询将 uu 连接到那个 'nothing'，哦，惊奇的是，什么都没有! (这在最外面的 where 子句中失败了。
问题:
什么改变了？我们做错了什么？我们在 5.7 中假设的哪些不再正确？或者这是一个错误？
数据:

drop table if exists uu, ww, kk, aa;
CREATE TABLE uu (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(30),
  PRIMARY KEY (`id`)
);
CREATE TABLE ww (
  id int NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE kk (
  id int NOT NULL AUTO_INCREMENT,
  uid int,
  wid int,
  state int,
  PRIMARY KEY (`id`),
  CONSTRAINT `kk_uid` FOREIGN KEY (uid) REFERENCES uu (id),
  CONSTRAINT `kk_wid` FOREIGN KEY (wid) REFERENCES ww (id)
);
CREATE TABLE aa (
  id int NOT NULL AUTO_INCREMENT,
  uid int,
  wid int,
  PRIMARY KEY (`id`),
  CONSTRAINT `aa_uid` FOREIGN KEY (uid) REFERENCES uu (id),
  CONSTRAINT `aa_wid` FOREIGN KEY (wid) REFERENCES ww (id)
);
 
insert into uu (name) values ('alice'), ('bob'), ('chuck');
insert into ww (id) values (10),(11);
insert into kk (uid,wid,state) values (2,10,99);
insert into kk (uid,wid,state) values (2,11,99);
insert into aa (uid,wid) values (2,10), (2,11);
 
-- plain joins:
select u.*, "][", k.*, "][", a.*
     from uu u
    left join kk k on k.uid = u.id
    left join aa a on u.id = a.uid
    order by u.id,k.id;
 
select a2.*, '][', k3.* -- distinct a2.wid
                                     from aa a2
                                     left join kk k3 on a2.uid = k3.uid and a2.wid = k3.wid
                                     where k3.wid is null
;
SELECT a.*
                     FROM aa a
                     where a.wid in
                           (
                                select distinct a2.wid
                                     from aa a2
                                     left join kk k3 on a2.uid = k3.uid and a2.wid = k3.wid
                                     where k3.wid is null
                           )
;
 
select u.*, '][k', k.*, '][k1', k1.*,'][tmp', tmp.*
     FROM uu u
    LEFT JOIN kk k ON k.uid = u.id
    LEFT JOIN kk k1 ON k1.uid = u.id AND k1.state != 99
    LEFT JOIN
           (
                SELECT a.*
                     FROM aa a
                     where a.wid in
                           (
                                select distinct a2.wid
                                     from aa a2
                                     left join kk k3 on a2.uid = k3.uid and a2.wid = k3.wid
                                     where k3.wid is null
                           )
           ) tmp ON tmp.uid = u.id
-- ;
   WHERE tmp.wid IS NULL
           AND (
                k.uid IS NULL
                OR
            (k.state = 99 AND k1.id IS NULL)
           )
;

Answer 1

这绝对是一个错误。它似乎是在 8.0.17 中引入的。
请在 bugs.mysql.com 上报告或告诉我，我会输入它。
一种解决方法是设置这些优化器开关，如下所示:

set optimizer_switch='materialization=off';
set optimizer_switch='derived_merge=on';

感谢您使用 MySQL。