Bug #83788 Float/Double to binary conversion does not show underlying data representation
Submitted: 11 Nov 2016 13:27
Reporter: Eric Herman (OCA) Email Updates:
Status: Open Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:5.7.15 OS:Any
Assigned to: CPU Architecture:Any

[11 Nov 2016 13:27] Eric Herman
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.