Bug #117572 The same query yields different results across different versions

• Submitted: 26 Feb 2:37
• Modified: 26 Feb 6:14
• Reporter: jinhui lai
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.41
• OS: Ubuntu (22.04)
• CPU Architecture: x86

[26 Feb 2:37] jinhui lai

Description:
The same query yields different results across different versions.It is a logic bug.

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

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 INT);
INSERT INTO t0(c0) VALUES(1), (2);
INSERT INTO t2(c0) VALUES(3);

SELECT * FROM t0 LEFT JOIN t1 ON FALSE RIGHT JOIN t2 ON (t1.c0 IS NULL) IN (t2.c0 = 3);
+------+------+------+
| c0   | c0   | c0   |
+------+------+------+
| NULL | NULL |    3 |
+------+------+------+
1 row in set (0.00 sec)

docker run -it --name mysql-9.2.0 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:9.2.0

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 INT);
INSERT INTO t0(c0) VALUES(1), (2);
INSERT INTO t2(c0) VALUES(3);

SELECT * FROM t0 LEFT JOIN t1 ON FALSE RIGHT JOIN t2 ON (t1.c0 IS NULL) IN (t2.c0 = 3);
+------+------+------+
| c0   | c0   | c0   |
+------+------+------+
|    2 | NULL |    3 |
|    1 | NULL |    3 |
+------+------+------+

[26 Feb 6:14] MySQL Verification Team

Hello jinhui lai,

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

regards,
Umesh