Bug #119683 Different results for INNER JOIN and STRAIGHT_JOIN with GREATEST() IN predicate

• Submitted: 14 Jan 15:35
• Modified: 15 Jan 12:57
• Reporter: weipeng wang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.44
• OS: Linux
• CPU Architecture: x86

[14 Jan 15:35] weipeng wang

Description:
Two semantically equivalent queries using INNER JOIN and STRAIGHT_JOIN return different result sets when evaluating an IN predicate involving a GREATEST().

INNER JOIN and STRAIGHT_JOIN differ only in join order enforcement and must not affect the logical result of the query. However, in the example below, changing only the join type results in different cardinalities.

How to repeat:
CREATE TABLE t0(c1 INT UNIQUE KEY);
CREATE TABLE t1(c1 INT UNIQUE KEY);

INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES (0);

mysql> SELECT * FROM t0 STRAIGHT_JOIN t1 WHERE GREATEST('', 0.1) IN (t1.c1);
+------+------+
| c1   | c1   |
+------+------+
|    0 |    0 |
+------+------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t0 INNER JOIN t1 WHERE GREATEST('', 0.1) IN (t1.c1);
Empty set (0.00 sec)

[15 Jan 3:15] weipeng wang

I found that the issue is not specific to GREATEST().
The similar behavior can be reproduced using COALESCE().

mysql> SELECT * FROM t0 STRAIGHT_JOIN t1 WHERE ((COALESCE(0.1, 0.2)) IN (t1.c1));
+------+------+
| c1   | c1   |
+------+------+
|    0 |    0 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t0 INNER JOIN t1 WHERE (((COALESCE(0.1, 0.2)) IN (t1.c1)));
Empty set (0.00 sec)

[15 Jan 12:57] Roy Lyseng

Thank you for the bug report.
Verified as described.