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

• Submitted: 31 Jul 2019 9:13
• Modified: 22 Nov 17:04
• Reporter: Slawomir Chodnicki
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.0.17
• OS: Any
• CPU Architecture: Any

[31 Jul 2019 9:13] Slawomir Chodnicki

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.

[31 Jul 2019 10:39] MySQL Verification Team

Hello Slawomir,

Thank you for the report.

regards,
Umesh

[22 Nov 17:04] Filipe Silva

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.