Bug #76601 MySQL JSON UDFs: json_valid is reporting a valid JSON string as invalid
Submitted: 6 Apr 2015 21:44 Modified: 7 Apr 2015 6:38
Reporter: Jason Baumgartner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON User-defined function ( UDF ) Severity:S3 (Non-critical)
Version:0.4.0 OS:Linux (Ubuntu)
Assigned to: CPU Architecture:Any
Tags: json, json_valid, UDFs

[6 Apr 2015 21:44] Jason Baumgartner
Description:
json_valid is returning 0 for a valid JSON string.  I am working with reddit comment data and this is the JSON block that is should report as valid.

{"gilded":0,"author_flair_css_class":null,"id":"n5","link_id":"t3_7k62w","subreddit":"WTF","downs":0,"ups":1,"edited":false,"score":1,"author":"[deleted]","subreddit_id":"t5_2qh61","body":"Maybe it's because involuntary imprisonment isn't natural. And [punishment](http://en.wikipedia.org/wiki/Punishment_(psychology\\)#Types_of_punishment) is the least effective behavior modifier.\n\nJust sayin'","retrieved_on":1428217161,"author_flair_text":null,"parent_id":"t1_c06vqpp","score_hidden":false,"controversiality":0,"name":"t1_n5","distinguished":null,"archived":true,"created_utc":"1229583186"}

You can use http://json.parser.online.fr/ and copy and paste that string to confirm that it is indeed valid.

How to repeat:
(I've escaped single quotes within this JSON in the following example)

SELECT json_valid('{"gilded":0,"author_flair_css_class":null,"id":"n5","link_id":"t3_7k62w","subreddit":"WTF","downs":0,"ups":1,"edited":false,"score":1,"author":"[deleted]","subreddit_id":"t5_2qh61","body":"Maybe it\'s because involuntary imprisonment isn\'t natural. And [punishment](http://en.wikipedia.org/wiki/Punishment_(psychology\\)#Types_of_punishment) is the least effective behavior modifier.\n\nJust sayin\'","retrieved_on":1428217161,"author_flair_text":null,"parent_id":"t1_c06vqpp","score_hidden":false,"controversiality":0,"name":"t1_n5","distinguished":null,"archived":true,"created_utc":"1229583186"}');
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_valid('{"gilded":0,"author_flair_css_class":null,"id":"n5","link_id":"t3_7k62w","subreddit":"WTF","downs":0,"ups":1,"edited":false,"score":1,"author":"[deleted]","subreddit_id":"t5_2qh61","body":"Maybe it\'s because involuntary imprisonment isn\'t nat |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                                0 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Suggested fix:
I haven't narrowed down what part of this JSON is giving the JSON UDF an issue, but hopefully the developer can narrow it down.
[6 Apr 2015 21:47] Jason Baumgartner
I've narrowed it down -- it is the two backslashes in the original JSON that is giving the UDF an issue.  It's still a valid bug.
[6 Apr 2015 22:20] Jason Baumgartner
The root cause of this issue may be related to bug #76598 in how the function is unescaping internally -- perhaps there is a mis-sequence of events internally between the MySQL unescaping and JSON spec unescaping.  

Just some ideas for Sveta if he is the one that will be looking at these bugs.

Thanks!
[7 Apr 2015 6:38] MySQL Verification Team
Hello Jason Baumgartner,

Thank you for the report.

Thanks,
Umesh