Bug #112264 Unexpected Result by block_nested_loop

• Submitted: 5 Sep 2023 11:47
• Modified: 5 Sep 2023 12:14
• Reporter: JINSHENG BA
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.1.0
• OS: Any
• CPU Architecture: Any

[5 Sep 2023 11:47] JINSHENG BA

Description:
CREATE TABLE t0(c0 FLOAT);
CREATE TABLE t1(c0 FLOAT);
CREATE TABLE t2(c0 DECIMAL);
CREATE TABLE t3(c0 FLOAT) ;
INSERT INTO t0(c0) VALUES('0.1');
INSERT INTO t2(c0) VALUES(1);

SET SESSION optimizer_switch = 'block_nested_loop=off';
SELECT * FROM t3 INNER JOIN t1 ON NULL RIGHT JOIN t2 ON TRUE INNER JOIN t0 ON IFNULL(t1.c0, '') = (t0.c0 IS FALSE); -- {NULL|NULL|1|0.1}
SET SESSION optimizer_switch = 'block_nested_loop=on';
SELECT * FROM t3 INNER JOIN t1 ON NULL RIGHT JOIN t2 ON TRUE INNER JOIN t0 ON IFNULL(t1.c0, '') = (t0.c0 IS FALSE); -- {}

Both equivalent queries return different results.

How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.1.0

Then execute the above test case.

[5 Sep 2023 12:14] MySQL Verification Team

Hello Jinsheng Ba,

Thank you for the report and test case.

regards,
Umesh