Bug #119825 Inconsistent results between INNER JOIN and STRAIGHT_JOIN when comparing DOUBLE constant with INT UNIQUE column

• Submitted: 1 Feb 12:02
• Modified: 2 Feb 3:50
• Reporter: weipeng wang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.45, 8.4.8, 9.6
• OS: Any
• CPU Architecture: Any

[1 Feb 12:02] weipeng wang

Description:
I found an inconsistency in query results between INNER JOIN and STRAIGHT_JOIN under the same join predicate.
The two queries are semantically equivalent and differ only in the join type. However, they return different results.

How to repeat:
CREATE TABLE t0(c0 INT) ;
CREATE TABLE t1(c0 INT UNIQUE) ;
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(1);

SELECT * FROM t0 STRAIGHT_JOIN t1 WHERE  -(-0.9) = t1.c0;
/* returns 1 row: (1, 1) */
SELECT * FROM t0 INNER JOIN t1 WHERE  -(-0.9) = t1.c0;
/* returns empty set */

[2 Feb 3:50] Chaithra Marsur Gopala Reddy

Hi weipeng wang,

Thank you for the test case. Verified as described.