Bug #119708 INNER JOIN and STRAIGHT_JOIN produce different results with FLOAT UNIQUE column on MEMORY table

• Submitted: 16 Jan 14:58
• Modified: 17 Jan 5:22
• Reporter: weipeng wang
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.44
• OS: Linux
• CPU Architecture: Any

[16 Jan 14:58] weipeng wang

Description:
STRAIGHT_JOIN and INNER JOIN produce different results despite being semantically equivalent and using identical WHERE predicates.

In the example below, the STRAIGHT_JOIN query returns a non-empty result set, while the corresponding INNER JOIN query returns an empty result set.

The difference is reproducible when using a MEMORY table with a FLOAT column defined as UNIQUE. Replacing the storage engine or removing the UNIQUE constraint prevents the issue from reproducing.

How to repeat:
CREATE TABLE t0(c0 FLOAT UNIQUE) ENGINE = MEMORY;
CREATE TABLE t1(c0 FLOAT ) ; 

INSERT INTO t0(c0) VALUES(1), (0);
INSERT INTO t1(c0) VALUES(0);

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

[17 Jan 5:22] weipeng wang

I found another minimal example that shows the same inconsistency.

In this case, the issue occurs with a non-UNIQUE index on a FLOAT column, where INNER JOIN incorrectly returns an empty result set, while STRAIGHT_JOIN returns the expected row.

```
CREATE TABLE t0(c0 FLOAT) ENGINE = MEMORY;
CREATE INDEX i0 ON t0(c0);
CREATE TABLE t1(c0 FLOAT);

INSERT INTO t0(c0) VALUES(1), (0);
INSERT INTO t1(c0) VALUES(0);

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