MySQL Bugs: #88230: MySQL normalizes decimal numbers with zero fractional part to integers

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:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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!).

Thank you for the bug report.

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
    lost.

    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.

Closed.

Posted by developer:
 
Also reported as bug#88812.