Bug #113298 Unexpected Results when Using IN for Floating-Point

• Submitted: 1 Dec 2023 4:41
• Modified: 1 Dec 2023 4:51
• Reporter: Suyang Zhong
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33, 8.1.0, 8.0.35
• OS: Any
• CPU Architecture: Any

[1 Dec 2023 4:41] Suyang Zhong

Description:
Consider the test case below. It is unexpected that both queries return the row, since the expression `1/1.5 IN (c1)` cannot be evaluated to `TRUE` at the same time.

```sql
CREATE TABLE t0( c1 INTEGER);
INSERT INTO t0 (c1) VALUES (1);
CREATE INDEX i0 ON t0(c1  );

SELECT * FROM t0 WHERE (1/1.5 IN (c1)); -- 1
SELECT * FROM t0 WHERE (NOT (1/1.5 IN (c1))); -- 1
```

Could be reproduced in 8.0.33, 8.1.0, 8.0.35-debian docker images.

How to repeat:
As mentioned above.

[1 Dec 2023 4:51] MySQL Verification Team

Hello Suyang Zhong,

Thank you for the report and test case.

regards,
Umesh