Bug #117527 Left-join produce wrong result

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

[20 Feb 7:44] jinhui lai

Description:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c1 FLOAT);
CREATE INDEX i0 USING HASH ON t0(c0) INVISIBLE;
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
INSERT INTO t0(c0) VALUES(COALESCE(0.99));
INSERT INTO t1(c1) VALUES(COALESCE(1.00));

SELECT * FROM t0 LEFT JOIN t1 ON COALESCE(0.99) IN (t0.c0);
+------+------+
| c0   | c1   |
+------+------+
|    1 | NULL |
+------+------+

SELECT * FROM t0 INNER JOIN t1 ON COALESCE(0.99) IN (t0.c0);
+------+------+
| c0   | c1   |
+------+------+
|    1 |    1 |
+------+------+

SELECT * FROM t0 RIGHT JOIN t1 ON COALESCE(0.99) IN (t0.c0);
+------+------+
| c0   | c1   |
+------+------+
|    1 |    1 |
+------+------+

select * from t0;
+------+
| c0   |
+------+
|    1 |
+------+

select * from t1;
+------+
| c1   |
+------+
|    1 |
+------+

By comparing the results of the above queries, the result returned by LEFT JOIN is obviously wrong. The results of LEFT JOIN and INNER are contradictory.

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 12:23] MySQL Verification Team

Hi Mr. lai,

Thank you for your bug report.

However, this is not a bug.

Results that you got are totally correct, since you inserted floating point value into an integer, used a hash index and used COALESCE() function.

This is all explained in our Reference Manual.

Not a bug.