Bug #97880 Precision loss when using float & double arguments
Submitted: 4 Dec 2019 15:57 Modified: 5 Dec 2019 22:23
Reporter: Jared Williams Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Document Store: X Plugin Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: double, FLOAT, X, xprotocol

[4 Dec 2019 15:57] Jared Williams

When passing in float or double arguments there is some data loss. I presume this is due to the float/double to string conversion within the X plugin, as a simple round trip using SELECT ?, ? returns truncated values.

How to repeat:

Mysqlx.Sql.StmtExecute {
  stmt: "SELECT ?, ?"
  args {
    type: SCALAR
    scalar {
      type: V_FLOAT
      v_float: 3.40282346638528859811704183484516925440e+38

  args {
    type: SCALAR
    scalar: {
      type: V_DOUBLE
      v_double: 1.797693134862315708145274237317043567981e+308
[5 Dec 2019 19:59] MySQL Verification Team
>      type: V_FLOAT
>      v_float: 3.40282346638528859811704183484516925440e+38
>      type: V_DOUBLE
>      v_double: 1.797693134862315708145274237317043567981e+308


"A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column."

You can read about encoding here: 

so for FLOAT you can expct 24 bits of significant digits to be stored, your number have much more then 24 significant digit so rounding will have to happen.

same with DOUBLE (53 bits)

So from your FLOAT, your mantissa is 340282346638528859811704183484516925440 that is waaaaaaaaaaaaay bigger than 24 bit number. Closest you can do with 24 bits is 340282 (remember its signed) ... and as for your DOUBLE your mantissa 1797693134862315708145274237317043567981 is way bigger than 53bits and best you can put in double is 17976931348623157 ... 

So there's no bug and no precision loss in the X plugin, it's just how data types work.
[5 Dec 2019 21:01] Jared Williams
They are the largest finite values representable in float32 and float64 bits respectively.

The client is written in go and both the values are consts, math.MaxFloat32 and math.MaxFloat64, and was testing marshalling/unmarshalling showed they are being truncated.

[5 Dec 2019 21:47] Jared Williams
Go program proving the values can be written and serialised in 4 and 8 bytes, read back and compared. This should be reopened.

package main

import (

func main() {

	var b4 [4]byte
	var b8 [8]byte

	binary.LittleEndian.PutUint32(b4[:], math.Float32bits(3.40282346638528859811704183484516925440e+38))
	binary.LittleEndian.PutUint64(b8[:], math.Float64bits(1.797693134862315708145274237317043567981e+308))

	f32 := math.Float32frombits(binary.LittleEndian.Uint32(b4[:]))
	f64 := math.Float64frombits(binary.LittleEndian.Uint64(b8[:]))

	if f32 != 3.40282346638528859811704183484516925440e+38 {
		fmt.Println("f32 failed")
	if f64 != 1.797693134862315708145274237317043567981e+308 {
		fmt.Println("f64 failed")

[5 Dec 2019 22:23] Jared Williams
No, closing this was fine, ignore me.
[5 Dec 2019 22:24] MySQL Verification Team
I'll revisit this in the morning Jared, too late to continue today