Description:
A query in 5.7 no longer works in 8.0.23. Tested against a windows 8.0.23 x64 and a linux docker container 8.0.23.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=521bf8b04d63c9e2b9e4de377485fa92
Current sql mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
It's not the smartest sql, but that's not the point.
TL;DR: The point is that the 'bob' rows SHOULD show in the last query (as in 5.7) but no longer do in 8.
Details:
After the 4 tables setup and inserts, the query is built progressively with earlier queries to prove the bug. The inner most select should find nothing. The middle select merely repeats that. The last query joins the uu to that 'nothing' and, oh surprise, nothing becomes something! (which fails the outer most where clause.
https://stackoverflow.com/questions/66556215/mysql-8-query-regression-coming-from-5-7-what...
How to repeat:
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)
)
;
Suggested fix:
set optimizer_switch='derived_merge=off'; will work when the subquery is executed but this is not a well crafted solution.. This exists on 8.0.23 as well
