MySQL Bugs: #96382: rs.getDouble() gives different results for float fields when useCursorFetch=true

Bug #96382	rs.getDouble() gives different results for float fields when useCursorFetch=true
Submitted:	31 Jul 2019 9:13	Modified:	22 Nov 2022 17:04
Reporter:	Slawomir Chodnicki	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Mysql Server: official docker image 8.0.17
Connector/J 8.0.17

With useCursorFetch=true, when retrieving values stored as FLOAT, resultSet.getDouble() returns a value that is far off from the closest Double representing the stored value. 

With useCursorFetch=false, the expected Double is returned from the call.

How to repeat:
CREATE TABLE my_floats (
  id varchar(10),
  x_float float,
  x_double double
);

INSERT INTO my_floats VALUES('pi', 3.141592653589793, 3.141592653589793);

-- The x_float column contains now a value ~3.14159.

Now select the data back:
SELECT * FROM my_floats WHERE id = 'pi';

With useCursorFetch=false you get:

{
  id: 'pi',
  x_float: 3.14159,
  x_double: 3.141592653589793,
}

both values were retrieved using rs.getDouble()

With useCursorFetch=true you get:

{
  id: 'pi',
  x_float: 3.1415927410125732,
  x_double: 3.141592653589793,
}

both values were retrieved using rs.getDouble()

Suggested fix:
The expected result is that both of these approaches return the first result, where a double value closest to the stored float was returned.

Hello Slawomir,

Thank you for the report.

regards,
Umesh

Posted by developer:
 
With useCursorFetch=true, Connector/J executes server-side prepared statements everywhere. So, the differences in the results observed are actually between text-based vs binary-based statements, i.e., standard statements vs prepared statements.

In MySQL, text-based statements (standard statements - also the way mysql client operates all the time) all data is transferred in text form, i.e., numbers such as the ones used in this test are simply encoded as numeric strings, namely, the two values are returned to the client/connector as "3.14159" and "3.141592653589793" respectively. Connector/J, in this case, simply takes such values and parses them as double or float as instructed in the getter method from the ResultSet class.

On the other hand, with binary-based statements (JDBC prepared statements when useCursorFetch=true or useServerPrepStmts=true is specified) numeric data is actually encoded in binary format and FLOAT and DOUBLE types are returned to the client/connector in floating point in IEEE 754 single precision format and floating point in IEEE 754 double precision format respectively. Connector/J decodes the values from the standard representation and constructs the corresponding float and double values. And this is why precision differs so much between the two mysql protocol modes.

Having said that, this is not a bug in MySQL Connector/J and there's nothing to fix.