MySQL Bugs: #113198: MySQL 8 version

Bug #113198	MySQL 8 version \| double datatype issue
Submitted:	23 Nov 2023 14:15	Modified:	23 Nov 2023 18:08
Reporter:	Supriya Chauhan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Any (20.04)
Assigned to:		CPU Architecture:	Any
Tags:	Bugs at Mysql, datatype, double

Description:
MySQL select query does not give correct output in MySQL 8.0.31 version graviton processor after upgrading it from MySQL 5.7 version AMD processor for datatype double. 

How to repeat:
Steps:
1. Install MySQL 5.7 version on AMD processor.
2. Create a table with column datatype double (11,5).
3. Insert some value and select on the both server.
4. Stop MySQL service and take snapshot through AWS snapshot feature.
5. Install MySQL 8.0.31 version on graviton processor server (EC2).
6. Create Volume from snapshot step 4 and attached into the MySQL 8 version server created in step 5.
7. Start MySQL service on both the server that is on 5.7 and version 8.0.31.
8. Select data on the server MySQL 5.7 is giving correct result but on 8 version with where clause(float column > 125.00000 and id = 1) result is not correct for some records.

Table structure on both versions:-

CREATE TABLE `testing` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `amt` double(11,5) NOT NULL DEFAULT '0.00000',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

data for id 1 in both tables.

mysql> select * from testing where id = 1;
+----+-----------------+
| id | current_due_amt |
+----+-----------------+
|  1 |         0.00000 |         
+----+-----------------+
1 row in set (0.00 sec)

query what we are executing on both versions.
select * from testing where amt > 0 and id=1; 

result on version 5.7 which is right:
mysql> select * from testing where amt = 0 and id=1;
+----+-----------------+
| id | current_due_amt |
+----+-----------------+
|  1 |         0.00000 |         
+----+-----------------+
1 row in set (0.00 sec)

mysql> select * from testing where amt > 0 and id=1;
Empty set (0.00 sec)

result on version 8 which is wrong:
mysql> select * from testing where amt = 0 and id=1;
Empty set (0.00 sec)

mysql> select * from testing where amt > 0 and id=1;
+----+-----------------+
| id | current_due_amt |
+----+-----------------+
|  1 |         0.00000 |         
+----+-----------------+
1 row in set (0.00 sec)

Suggested fix:
No Idea.

Hi Mr. Chauhan,

Thank you for your bug report.

Sorry, but this is not a bug.

When you are dealing with IEEE floating point numbers, you cannot compare them with the equality sign.

You can only compare within a range.

When MySQL server is compiled, all expressions with  floating point numbers are compiled to be dealt with the FPU part of the CPU.

Hence, how big a range is necessary, is up to the precision that FPU can deliver and the one you need.

But, using equality sign with IEEE floating point numbers is not recommended since it is unreliable, considering the precisions defined by IEEE. . This is described in our Reference Manual.

Not a bug.

One additional note.

If you would like to have precision with real numbers, use fixed point type. In SQL standard and MySQL that type is DECIMAL. Not FLOAT, nor DOUBLE nor LONG DOUBLE.

If you do only basic arithmetics and basic expressions , it is ideal

However, this data type is not recommended for the complex operations, like trigonometry, GIS functionality and similar.

Hi Team,

Thanks for the clarification but may you please guide me that why it is working fine with MySQL 5.7 version.

Hi,

We have received many reports with 5.7 as well. It depends on the CPU used.

But, every single CPU and every other software will not return correct results when comparing floating point values for identity. It is just a question of which values exactly are compared for identity.

Hence, floating point numbers should be always compared within a range, a very small range.

Not a bug !!!!!!!