Bug #112296 Unexpected Result by NO_BNL

• Submitted: 8 Sep 2023 0:48
• Modified: 3 Jan 16:02
• Reporter: JINSHENG BA
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.1.0, 8.0.34
• OS: Any
• 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 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.