Bug #88230 MySQL normalizes decimal numbers with zero fractional part to integers
Submitted: 26 Oct 2017 9:15 Modified: 21 Nov 2017 23:17
Reporter: Saverio Miroddi Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7.18 OS:Any
Assigned to: CPU Architecture:Any

[26 Oct 2017 9:15] Saverio Miroddi
MySQL normalizes decimal numbers with zero fractional part to integers.

This is either a bug, or a lack of documentation.

I don't think MySQL should perform such normalization, since converting the number also changes its data type, with the result of storing a value in a certain form (decimal) and reading it in a different one (integer).

If it's decided that this is rather an advanced functionality, it should be documented in the normalization section of the documentation (https://dev.mysql.com/doc/refman/5.7/en/json.html#json-normalization).

How to repeat:
SELECT CAST('{"mykey": 5.0}' AS JSON);

| CAST('{"mykey": 5.0}' AS JSON) |
| {"mykey": 5}                   |

Suggested fix:
Change the behavior (preferred), or document it (boo!).
[26 Oct 2017 11:07] MySQL Verification Team
Thank you for the bug report.
[21 Nov 2017 23:17] Jon Stephens
Documented fix in the MySQL 8.0.4 changelog as follows:

    When a JSON document is converted to string representation,
    floating-point values that have no fractional part may be
    represented in a format indistinguishable from integers. When
    the string representation of such a JSON document was passed
    through the JSON parser again, the information that the numeric
    value was originally specified as a floating-point value was

    To rectify this problem, a fractional part is now added to the
    string representation of a floating-point value in a JSON
    document if the value has no fractional part and is not
    represented using scientific format. This makes the string
    representation of a floating-point value distinguishable from
    that of an integer, so that it continues to be treated as a
    floating-point number even if the string is parsed again.

    This fix also makes ST_GeomFromGeoJSON() use the same JSON
    parser as the other JSON functions rather than its own custom
    parser as had been the case since MySQL 5.7.8; this special
    handling was due to the fact that ST_AsGeoJSON() dropped the
    fractional part of negative zero (-0 instead of -0.0), causing
    the JSON parser to interpret -0 as integer 0, thus losing the
    distinction between positive and negative zero. Since
    ST_AsGeoJSON() now uses the standard JSON parser, it represents
    negative zero as correctly as -0.0, obviating any need for
    ST_GeomFromGeoJSON() to preserve negative zero explicitly when
    parsing the output from ST_AsGeoJSON().

    See also BUG#19504183.

[8 Dec 2017 11:34] Knut Anders Hatlen
Posted by developer:
Also reported as bug#88812.