MySQL Bugs: #42666: coalesce changes value of float fields

Bug #42666	coalesce changes value of float fields
Submitted:	6 Feb 2009 21:10	Modified:	4 Nov 2019 14:46
Reporter:	Tim Johnson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	Ver 14.12 Distrib 5.0.67, 4.1, 5.0, 5.1 6.0 bzr	OS:	Linux (amd64 on Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	built-in functions

Description:
Coalesce sometimes changes the value of float fields. It happens every time but only for certain values. It only happens when selecting the value from a table, not with literals. The bug still occurs even if you change the value to a value that works and back again. It doesn't matter which arg position the float is and the other value seems to have no effect (I didn't test with more than 2 args). This was with InnoDB tables.

How to repeat:
mysql> select tax_rate, coalesce(tax_rate, NULL), coalesce(NULL, tax_rate) from customers;
+----------+--------------------------+--------------------------+
| tax_rate | coalesce(tax_rate, NULL) | coalesce(NULL, tax_rate) |
+----------+--------------------------+--------------------------+
|      7.5 |                      7.5 |                      7.5 | 
|      7.5 |                      7.5 |                      7.5 | 
|      7.2 |          7.1999998092651 |          7.1999998092651 | 
|      7.5 |                      7.5 |                      7.5 | 
+----------+--------------------------+--------------------------+
4 rows in set (0.00 sec)

compare to:

mysql> select coalesce(NULL, tax_rate), coalesce(NULL, 7.2) from customers;
+--------------------------+---------------------+
| coalesce(NULL, tax_rate) | coalesce(NULL, 7.2) |
+--------------------------+---------------------+
|                      7.5 |                 7.2 | 
|                      7.5 |                 7.2 | 
|          7.1999998092651 |                 7.2 | 
|                      7.5 |                 7.2 | 
+--------------------------+---------------------+
4 rows in set (0.00 sec)

table definition with extra fields removed:

mysql> show create table customers;
CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tax_rate` float default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

Suggested fix:
It looks like the value is being cast and then converted back to a float. If at all possible, the data shouldn't be modified at all by coalesce.

Thank you for the report.

Verified as described.

The bug is still there, 5.5.30:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `customers` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `tax_rate` float default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.33 sec)

mysql> insert into customers(tax_rate) values (7.5), (7.5), (7.2), (7.5);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select tax_rate, coalesce(tax_rate, NULL), coalesce(NULL, tax_rate) from
customers;
+----------+--------------------------+--------------------------+
| tax_rate | coalesce(tax_rate, NULL) | coalesce(NULL, tax_rate) |
+----------+--------------------------+--------------------------+
|      7.5 |                      7.5 |                      7.5 |
|      7.5 |                      7.5 |                      7.5 |
|      7.2 |        7.199999809265137 |        7.199999809265137 |
|      7.5 |                      7.5 |                      7.5 |
+----------+--------------------------+--------------------------+
4 rows in set (0.08 sec)

I do not think this is a bug,
The result of COALESCE applied to a FLOAT expression is a column of type DOUBLE. This makes sense as long as all internal arithmetic of floating-point data types are carried out in double precision.
The value reported is an approximation of the 7.2 value stored in the float column.

You will also see the same value in e.g.

  select coalesce(NULL, tax_rate), tax_rate*1.0 from customers;

As for the comparison with 

  select coalesce(NULL, tax_rate), coalesce(NULL, 7.2) from customers,

the literal value 7.2 is a DECIMAL value that has different conversion rules than floating-point  values.

Posted by developer:
 
Most likely fixed by 25123839.
This bug fix adjusted resolved types to be aggregated properly for all CASE-derived functions.

Posted by developer:
 
Fixed in 8.0.4.

COALESCE() could change the value of FLOAT fields.