[MDEV-11586] UNION of FLOAT type results in erroneous precision Created: 2016-12-16  Updated: 2017-10-06  Resolved: 2017-10-06

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5.53, 10.0.27, 10.1.20, 10.2.2
Fix Version/s: 10.2.10

Type: Bug Priority: Major
Reporter: Nirbhay Choubey (Inactive) Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description    

MariaDB [test]> create table t1(f float);
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MariaDB [test]> insert into t1 values(1.1);
 
Query OK, 1 row affected (0.02 sec)
 
 
 
MariaDB [test]> select f from t1 union select 1;
 
+------+
 
| f    |
 
+------+
 
|  1.1 |
 
|    1 |
 
+------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select 1 union select f from t1;
 
+-------------------+
 
| 1                 |
 
+-------------------+
 
|                 1 |
 
| 1.100000023841858 |
 
+-------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select "foo" union select f from t1;
 
+--------------+
 
| foo          |
 
+--------------+
 
| foo          |
 
| 1.1000000238 |
 
+--------------+
 
2 rows in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2016-12-17 ]

Is precision ever guaranteed for float values?

Assigning to bar to decide if there is a bug here.

Comment by Alexander Barkov [ 2017-10-06 ]

The difference between "f union 1" and "1 union f" happens because of an asymmetry in the array field_types_merge_rules[][].

1. FLOAT + INT = DOUBLE
2. FLOAT + BIGINT = FLOAT
3. INT + FLOAT = DOUBLE
4. BIGINT + FLOAT = DOUBLE

Notice, #2 gives a different result.

In 10.2 we can change #2 to return DOUBLE instead.
So both these queries:

select f from t1 union select 1;
 
select 1 union select f from t1;

will return:

+-------------------+
 
| 1                 |
 
+-------------------+
 
|                 1 |
 
| 1.100000023841858 |
 
+-------------------+

But I guess the desired result would be to return:

+------+
 
| f    |
 
+------+
 
|  1.1 |
 
|    1 |
 
+------+

in both queries.
This is something we cannot do in 10.2.
In 10.3 we have done a refactoring in data type handling, so doing this would be easier in 10.3.

Comment by Alexander Barkov [ 2017-10-06 ]

This script demonstrates that the above asymmetry causes a serious data loss:

SELECT f FROM t1 UNION SELECT 2147483647;


+------------+
 
| f          |
 
+------------+
 
|        1.1 |
 
| 2147480000 |
 
+------------+


SELECT 2147483647 UNION SELECT f FROM t1;


+-------------------+
 
| 2147483647        |
 
+-------------------+
 
|        2147483647 |
 
| 1.100000023841858 |
 
+-------------------+

Notice, 2147483647 was changed to 2147480000 in the first query.

The same problem is repeatable with hybrid function, such as CASE:

SELECT CASE WHEN 0 THEN (SELECT f FROM t1) ELSE 2147483647 END AS c1,
 
       CASE WHEN 1 THEN 2147483647 ELSE (SELECT f FROM t1) END AS c2;


+------------+------------+
 
| c1         | c2         |
 
+------------+------------+
 
| 2147483648 | 2147483647 |
 
+------------+------------+

Generated at Thu Feb 08 07:51:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.