Bug #83788 Float/Double to binary conversion does not show underlying data representation

• Submitted: 11 Nov 2016 13:27
• Reporter: Eric Herman (OCA)
• Status: Open
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 5.7.15
• OS: Any
• CPU Architecture: Any

[11 Nov 2016 13:27] Eric Herman

Description:
In order to extract and re-insert floating point values, it would be very useful to be able to extract and insert the data in raw form (IEEE 754).

I do not see any built-in functions to do this, nor does it seem a cast to binary does what I might expect.

How to repeat:
test > CREATE TABLE t1 ( id int unsigned NOT NULL AUTO_INCREMENT, foo float NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.02 sec)

test > INSERT INTO t1 VALUES (NULL, 1.0/3.0);
Query OK, 1 row affected (0.01 sec)

test >  SELECT id, foo, HEX(CAST(foo AS binary)) FROM t1;
+----+----------+--------------------------+
| id | foo      | HEX(CAST(foo AS binary)) |
+----+----------+--------------------------+
|  1 | 0.333333 | 302E333333333333         |
+----+----------+--------------------------+
1 row in set (0.00 sec)

test > 

Suggested fix:
Please provide a function to retrieve the raw bytes of the data type, also please provide a function which can be used to update/insert from these same bytes.