Bug #118625 index makes query generate incorrect result

• Submitted: 10 Jul 3:04
• Modified: 6 Aug 12:47
• Reporter: chi zhang
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 9.3.0
• OS: Any
• CPU Architecture: Any

[10 Jul 3:04] chi zhang

Description:
Hi,

In the following test case, the table `t0` has only one row `0`, so the predicate of the SELECT should be false. However, the query returns one row.

```
CREATE TABLE t0(c0 BIGINT(33)  ) ;
CREATE INDEX i0 ON t0(c0);
INSERT INTO t0(c0) VALUES(0);
SELECT 1 AS ref0 FROM t0 WHERE NOT ((t0.c0) != (CAST(0.4 AS DOUBLE)));
```

When I remove the `NOT` keyword from the query, like `SELECT 1 AS ref0 FROM t0 WHERE ((t0.c0) != (CAST(0.4 AS DOUBLE)));`, it also returns one row.

How to repeat:
```
CREATE TABLE t0(c0 BIGINT(33)  ) ;
CREATE INDEX i0 ON t0(c0);
INSERT INTO t0(c0) VALUES(0);
SELECT 1 AS ref0 FROM t0 WHERE NOT ((t0.c0) != (CAST(0.4 AS DOUBLE)));
```

[10 Jul 5:03] chi zhang

If I remove the index, the query can generate the correct result.

[6 Aug 12:47] MySQL Verification Team

Hello Chi Zhang,

Thank you for the bug report.
Imho this is duplicate of Bug #118092, please see Bug #118092.

Regards,
Ashwini Patil