Bug #75892 Json replace function does not seem to work
Submitted: 13 Feb 2015 10:40 Modified: 26 Feb 2015 17:50
Reporter: Evandro Machado Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: JSON User-defined function ( UDF ) Severity:S1 (Critical)
Version:033 OS:Windows
Assigned to: CPU Architecture:Any

[13 Feb 2015 10:40] Evandro Machado
Description:
Json replace function does not seem to work.
I have json string valid but returns empty document!

How to repeat:
JSON_STRING: {"upload_extensions":"bmp,csv,doc,gif,ico,jpg,jpeg,odg,odp,ods,odt,pdf,png,ppt,swf,txt,xcf,xls,BMP,CSV,DOC,GIF,ICO,JPG,JPEG,ODG,ODP,ODS,ODT,PDF,PNG,PPT,SWF,TXT,XCF,XLS","upload_maxsize":"10","file_path":"images/widesysn/images","image_path":"images/widesysn/images","restrict_uploads":"1","check_mime":"1","image_extensions":"bmp,gif,jpg,png","ignore_extensions":"","upload_mime":"image/jpeg,image/gif,image/png,image/bmp,application/x-shockwave-flash,application/msword,application/excel,application/pdf,application/powerpoint,text/plain,application/x-zip","upload_mime_illegal":"text/html"}

------------------------------------------------------------------------------

select json_contains_key(params, "file_path") from extensions where element = 'com_media'; /* result is 1 */

select json_replace(params, "file_path", "My New Value") as params from extensions where element = 'com_media'; /* return empty! */
[26 Feb 2015 17:50] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is not a bug: JSON_REPLACE checks new value for being valid JSON document. Since "New Value" does not contain quotes it is treated as invalid JSON document. If you compile JSON functions with option -DVERBOSE_LEVEL=error you will find following in the error log file:

Post-processing error: New value is not valid JSON document

Correct syntax is 

select json_replace(params, "file_path", '"My New Value"') as params from extensions where element = 'com_media';

or

select json_replace(params, "file_path", "\"My New Value\"") as params from extensions where element = 'com_media';

This limitation exists, because MySQL does not provide authors of UDFs a way to distinguish JSON objects, arrays and strings (MySQL has not JSON data type). Therefore functions require value to be valid JSON document already.