[MDEV-12197] Weird floating point arithmetics issue Created: 2017-03-07  Updated: 2022-12-12

Status: Confirmed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.1.21, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.3

Type: Bug Priority: Major
Reporter: Jan Kunzmann (Inactive) Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

MariaDB binary from Alpine Linux 3.5, running in a docker container on an Intel Xeon CPU (E5-2640 stepping 7, 2.50GHz)


 Description   

H know that floating point maths isn't precise, but this particular query on a regular FLOAT field in an InnoDB table can't be explained with precision errors:

MariaDB [...]> SELECT total, ROUND(`total`, 0) FROM table;
 
+---------+-------------------+
 
| total   | ROUND(`total`, 0) |
 
+---------+-------------------+
 
| 5233510 |           5233512 |
 
+---------+-------------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2017-03-09 ]

Please paste the output of SHOW CREATE TABLE `table`.

Comment by Jan Kunzmann (Inactive) [ 2017-03-09 ]

With all the uninvolved columns removed, the create table would simply look like this:

CREATE TABLE `table` (
 
  `id` int(11) NOT NULL,
 
  `total` float unsigned NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Comment by Elena Stepanova [ 2017-03-12 ]

Thank you. Reproducible easily, not sure what went wrong on my first attempt that made me to request the table structure.

MariaDB [test]> CREATE TABLE `table` (
 
    ->   `total` float
 
    -> );
 
Query OK, 0 rows affected (3.18 sec)
 
 
 
MariaDB [test]> INSERT `table` VALUES (5233510),(5233512);
 
Query OK, 2 rows affected (1.54 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT total, ROUND(`total`, 0) FROM `table`;
 
+---------+-------------------+
 
| total   | ROUND(`total`, 0) |
 
+---------+-------------------+
 
| 5233510 |           5233510 |
 
| 5233510 |           5233512 |
 
+---------+-------------------+
 
2 rows in set (0.33 sec)

Same on MariaDB 5.5-10.2, MySQL 5.7.
Assigning to bar to determine whether it's a bug or some obscure feature.

Comment by Alexander Barkov [ 2022-04-04 ]

Looks like a bug. The precision loss happens in Field_float::val_str() during float-to-string conversion. Note, `total + 0` returns a correct result:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (total FLOAT);
 
INSERT INTO t1 VALUES (5233510),(5233512);
 
SELECT total, total + 0 FROM t1;


+---------+-----------+
 
| total   | total + 0 |
 
+---------+-----------+
 
| 5233510 |   5233510 | -- both colulms OK
 
| 5233510 |   5233512 | -- total is wrong, total+0 is OK
 
+---------+-----------+

Other observations:

  • The problem happens with both FLOAT UNSIGNED (as in the original report) and just FLOAT (signed)
  • The problem happens with all engines (at least with InnoDB, MyISAM, HEAP)
Generated at Thu Feb 08 07:55:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.