Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.53, 10.0.27, 10.1.20, 10.2.2
-
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)
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Affects Version/s | 10.1.20 [ 22112 ] |
| Affects Version/s | 5.5.53 [ 22106 ] | |
| Affects Version/s | 10.0.27 [ 22017 ] |
| Component/s | Data types [ 13906 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Assignee | Alexander Barkov [ bar ] |
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.
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 |
|
+------------+------------+
|
| Status | Open [ 1 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2017-10-06 09:31:43.0 | 2017-10-06 09:31:43.834 |
| Fix Version/s | 10.2.10 [ 22615 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 78799 ] | MariaDB v4 [ 151389 ] |
Is precision ever guaranteed for float values?
Assigning to bar to decide if there is a bug here.