Bug #112296 Unexpected Result by NO_BNL
Submitted: 8 Sep 2023 0:48 Modified: 3 Jan 2024 16:02
Reporter: JINSHENG BA Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.1.0, 8.0.34 OS:Any
Assigned to: CPU Architecture:Any

[8 Sep 2023 0:48] JINSHENG BA 
Description:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 INT);
REPLACE INTO t0(c0) VALUES(1), (2);
INSERT INTO t2(c0) VALUES(3);

SELECT t0.c0 FROM t0 LEFT JOIN t1 ON FALSE RIGHT JOIN t2 ON (t1.c0 IS NOT NULL) IN (t2.c0 = 1000); -- {NULL}
SELECT /*+ NO_BNL(t0)*/ t0.c0 FROM t0 LEFT JOIN t1 ON FALSE RIGHT JOIN t2 ON (t1.c0 IS NOT NULL) IN (t2.c0 = 1000); -- {1}, {2}

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.
[8 Sep 2023 6:46] MySQL Verification Team 
Hello Jinsheng Ba,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[3 Jan 2024 16:02] Jon Stephens 
Documented fix as follows in the MySQL 8.3.0 changelog:

    The same query returned different results using the NO_BNL
    optimizer hint.

Closed.