Bug #117573 Equivalent inner-join queries return different results

• Submitted: 26 Feb 4:47
• Modified: 21 Mar 7:12
• Reporter: jinhui lai
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.2.0, 8.0.41, 8.4.4
• OS: Ubuntu (22.04)
• CPU Architecture: x86

[26 Feb 4:47] jinhui lai

Description:
Equivalent inner-join queries return different results, and it can be exposed by SET SESSION optimizer_switch = 'subquery_to_derived=on'.

How to repeat:
CREATE TABLE t0(c0 INT ZEROFILL UNIQUE);
CREATE TABLE t1(c1 INT);
SET SESSION optimizer_switch = 'subquery_to_derived=on';

INSERT INTO t0(c0) VALUES(1.01);
INSERT INTO t1(c1) VALUES(1);

SELECT * FROM t0 INNER JOIN t1 ON t0.c0 IN (COALESCE(1.01)); --wrong
+------------+------+
| c0         | c1   |
+------------+------+
| 0000000001 |    1 |
+------------+------+
1 row in set (0.00 sec)

SELECT * FROM t0,t1 WHERE t0.c0 IN (1.01); --right
Empty set (0.00 sec)

The above two equivalent inner-join queries return different results. Obviously, the explicit inner-join query result is wrong and the implicit inner-join query is right, which also can be confirmed by the follow LEFT JOIN result

SELECT * FROM t0 LEFT JOIN t1 ON t0.c0 IN (COALESCE(1.01));
+------------+------+
| c0         | c1   |
+------------+------+
| 0000000001 | NULL |
+------------+------+
1 row in set (0.00 sec)

When SET SESSION optimizer_switch = 'subquery_to_derived=off',it get right reslut.

[26 Feb 6:38] MySQL Verification Team

Hello jinhui lai,

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

regards,
Umesh

[21 Mar 7:12] jinhui lai

No need to reply. Thank you for your patience. In order to facilitate your review and save your time, I will provide a minimal and reproducible example. This way, you can quickly understand the issue.

CREATE TABLE t0(c0 INT UNIQUE);
CREATE TABLE t1(c1 INT);
SET SESSION optimizer_switch = 'subquery_to_derived=on';
INSERT INTO t0(c0) VALUES(0.1);
INSERT INTO t1(c1) VALUES(0);
SELECT t0.c0 FROM t0, t1 WHERE t0.c0 IN (COALESCE(0.1)); -- {0}
SELECT t0.c0 FROM t0, t1 WHERE t0.c0 IN (0.1); -- Empty set