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: | |
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
[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