Bug #97589 JSON fields return altered data

• Submitted: 12 Nov 2019 0:49
• Modified: 15 Nov 2019 16:33
• Reporter: Andrew Brown
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 8.0.18, 5.7.28
• OS: Any
• Assigned to: Jon Stephens
• CPU Architecture: Any

[12 Nov 2019 0:49] Andrew Brown

Description:
I'm not sure if this qualifies as a bug per se, but the behavior seems inconsistent.

With a JSON field, the value returned from the query will always contain a space between the elements, even if the data was entered without a space.

If there is no "fix" for this, I feel like it should be documented.

How to repeat:
CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `emails` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `test` (`id`, `emails`)
VALUES (1, '[\"[e-mail]\",\"[e-mail]\",\"[e-mail]\"]')

SELECT `emails` FROM `test` WHERE `id` = 1

The query will return 

["[e-mail]", "[e-mail]", "[e-mail]"]

So even though we entered the data without spaces between the elements, it returned with the spaces.

Suggested fix:
It would be great if the data was returned exactly as it was entered, but I'm not sure how that affects the internal JSON handling of MySQL.

[12 Nov 2019 2:48] Tsubasa Tanaka

I found this at JSON chapter in document.

> MySQL also discards extra whitespace between keys, values, or elements in the original JSON document. To make lookups more efficient, it also sorts the keys of a JSON object

https://dev.mysql.com/doc/refman/8.0/en/json.html#json-normalization

From Japan MySQL User Group.

[12 Nov 2019 3:29] Andrew Brown

I saw that as well. Which seems like the opposite of what it's doing. So I figured that was for its internal handling.

[12 Nov 2019 6:12] MySQL Verification Team

Hello Andrew Brown,

Thank you for the report.

regards,
Umesh

[14 Nov 2019 17:38] Jon Stephens

Waiting on Dev to confirm that this behaviour is as intended (or not).

[15 Nov 2019 13:21] Knut Anders Hatlen

Posted by developer:
 
Yes, this is intentional. The spaces between the elements are not stored internally. However, when converting the JSON value to a string for presentation, a blank is added between each element to make the value easier to read. A blank is also added between after the colon that separates key names from values in JSON objects.

[15 Nov 2019 13:35] Jon Stephens

Hi Andrew,

Our Development staff have verified that this is intended/expected. We'll update the documentation to reflect this soon.

Thanks!

jon.

[15 Nov 2019 14:27] Jon Stephens

Fixed in mysqldoc rev 64194.

[15 Nov 2019 14:27] Jon Stephens

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

[15 Nov 2019 16:33] Andrew Brown

Thanks for the quick response on this guys, and for making the documentation updates.

I'm still a little torn on if this is the appropriate behavior or not, but at least for now I can handle this discrepancy on the server code.

Thanks!