Bug #117529 Both right-join and inner-join produce wrong result

• Submitted: 20 Feb 2025 8:54
• Modified: 20 Feb 2025 12:18
• Reporter: jinhui lai
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.2.0, 9.1.0
• OS: Ubuntu (22.04)
• CPU Architecture: x86

[20 Feb 2025 8:54] jinhui lai

Description:
CREATE TABLE t0(c0 FLOAT);
CREATE TABLE t1(c1 FLOAT);
CREATE TABLE t2(c2 FLOAT);
INSERT INTO t0(c0) VALUES('1.0');
INSERT INTO t2(c2) VALUES(1);

SELECT * FROM t0 LEFT JOIN t1 ON TRUE RIGHT JOIN t2 ON t2.c2 = t0.c0;
+------+------+------+
| c0   | c1   | c2   |
+------+------+------+
|    1 | NULL |    1 |
+------+------+------+
SELECT * FROM t0 RIGHT JOIN t1 ON TRUE RIGHT JOIN t2 ON t2.c2 = t0.c0; -- wrong reult
+------+------+------+
| c0   | c1   | c2   |
+------+------+------+
| NULL | NULL |    1 |
+------+------+------+
SELECT * FROM t0 INNER JOIN t1 ON TRUE RIGHT JOIN t2 ON t2.c2 = t0.c0; -- wrong reult
+------+------+------+
| c0   | c1   | c2   |
+------+------+------+
| NULL | NULL |    1 |
+------+------+------+
select * from t0;
+------+
| c0   |
+------+
|    1 |
+------+
select * from t2;
+------+
| c2   |
+------+
|    1 |
+------+

Both right-join and inner-join produce wrong result, and they shold return:
+------+------+------+
| c0   | c1   | c2   |
+------+------+------+
|    1 | NULL |    1 |
+------+------+------+

How to repeat:
docker run -it --name mysql-9.2.0 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.2.0
docker exec -it mysql-9.2.0 mysql -uroot -p

Then execute the above test case.

[20 Feb 2025 12:18] MySQL Verification Team

Hi Mr. lai,

Thank you for your bug report.

However, this is not a bug.

The results that are obtained are 100 % correct, because table t1 is empty. Hence, second and third query return NULL from the first join.

Not a bug.