Bug #117573 Equivalent inner-join queries return different results
Submitted: 26 Feb 4:47 Modified: 21 Mar 7:12
Reporter: jinhui lai Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:9.2.0, 8.0.41, 8.4.4 OS:Ubuntu (22.04)
Assigned to: 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