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 Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.33, 8.1.0, 8.0.35 OS:Any
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.