Re: useCursorFetch=true impacts getFloat() values.
Posted by: Filipe Silva
Date: October 10, 2018 04:25PM

Hi Zhang,

This is so because of the way MySQL encodes numeric values in the MySQL protocol.

When you set `useCursorFetch=true`, `useServerPrepStmts` is consequently also set to `true` -- check the documentation for `useCursorFetch` in https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html.

Setting `useServerPrepStmts=true` internally causes the MySQL protocol to change from "Text protocol" to "Binary Protocol". Consult https://dev.mysql.com/doc/internals/en/text-protocol.html and https://dev.mysql.com/doc/internals/en/prepared-statements.html if you want to learn more about this.

So, in the "Text Protocol", all numeric values are returned to the client in decimal format, i.e., those values are returned as the data type definition dictates. In your example, FLOAT adjusts the fractional part according to number precision, a big number like 358294.648941 then becomes 358295 after rounding (the ".0" is added by Java). But in the "Binary Protocol" the same number is returned in binary format, using 4 Bytes, more precisely in IEEE 754 single precision format. As such, the client gets more information about the stored number and so being able to decode it up to 358294.66 as Java float.

You have some options to try to minimize such behavior with these big numbers. Here are some:
- Use DOUBLE instead of FLOAT.
- Use DECIMAL instead of FLOAT.
- Use FLOAT with higher binary precision, e.g., FLOAT(32)
- Use FLOAT with decimal precision, e.g., FLOAT(25,4)

Please consult the following pages to help you better understand this topic:
- https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
- https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html
- https://dev.mysql.com/doc/refman/8.0/en/precision-math.html

IHTH

Options: ReplyQuote


Subject
Written By
Posted
Re: useCursorFetch=true impacts getFloat() values.
October 10, 2018 04:25PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.