Bug #118961 float in large value query no result

• Submitted: 9 Sep 3:43
• Modified: 10 Sep 6:45
• Reporter: david silva
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any
• Tags: compare, float in, scientific values

[9 Sep 3:43] david silva

Description:
The float field cannot be used in condition queries for scientific values such as -3.40282e38.

How to repeat:
create table t2(c1 float, index(c1));
insert into t2 values(1);
insert into t2 values(-3.40282e38);

-- in query no result
select * from t2 where c1 in (-3.40282e38);
Empty set (0.00 sec)

[9 Sep 12:03] MySQL Verification Team

Thanks for the report.

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.001 sec)

mysql> insert into t2 values(-3.40282e38);
Query OK, 1 row affected (0.000 sec)

mysql> select * from t2 where c1 in (-3.40282e38);
Empty set (0.000 sec)

mysql> select * from t2;
+-------------+
| c1          |
+-------------+
| -3.40282e38 |
|           1 |
+-------------+
2 rows in set (0.001 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

[10 Sep 6:45] Roy Lyseng

Posted by developer:
 
This is not a bug.
Floating point values are inexact, so you must expect some deviations on 
equality testing.