Bug #93253 JSON_OBJECT() casts YEAR values to base64
Submitted: 20 Nov 2018 2:56 Modified: 27 Nov 2018 12:27
Reporter: Alfredo Kojima Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0.14, 5.7.24, 8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[20 Nov 2018 2:56] Alfredo Kojima
Description:
Calling JSON_OBJECT() (and possibly other JSON functions) on a column of type YEAR() causes that value to be treated as binary data instead of integer.

How to repeat:
mysql> create table y (y year(4));
Query OK, 0 rows affected (0.0076 sec)

mysql> insert into y values (1999);
Query OK, 1 row affected (0.0063 sec)

mysql> select json_object('y', y) from y;
+---------------------------------+
| json_object('y', y)             |
+---------------------------------+
| {"y": "base64:type13:MTk5OQ=="} |
+---------------------------------+
1 row in set (0.0004 sec)

Expected:

{"y": 1999}
[20 Nov 2018 5:32] MySQL Verification Team
Hello Alfredo,

Thank you for the report.

regards,
Umesh
[27 Nov 2018 12:27] Jon Stephens
Documented fix as follows in the MySQL 8.0.14 changelog:

    YEAR values were stored as opaque data in JSON; when JSON
    documents containing YEAR values were converted to text, the
    YEAR values were shown as base64-encoded strings. To resolve
    this issue, YEAR values are now stored as unsigned integers,
    which are shown as numbers when converted to text. An additional
    benefit of this fix is that less storage space is now required
    for YEAR values within JSON documents.

Closed.