[MDEV-14402] JSON_VALUE doesn't escape quote Created: 2017-11-15  Updated: 2017-11-16  Resolved: 2017-11-16

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2.10
Fix Version/s: 10.2.11

Type: Bug Priority: Major
Reporter: Lu Do Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

Using official mariadb docker image


 Description   

Based on official example : 

SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }';
 
SELECT @json, JSON_VALUE(@json,'$.z');

return : 

@json                                           JSON_VALUE(@json,'$.z')  
----------------------------------------------  -------------------------
 
{ "x": [0,1], "y": "[0,1]", "z": "Mon\"t\"y" }  Mon\"t\"y

as you can see Mon\"t\"y is not escape as it must be



 Comments   
Comment by Alice Sherepa [ 2017-11-16 ]

It works as expected. According to https://mariadb.com/kb/en/library/differences-between-json_query-and-json_value/
JSON_VALUE shoud return string literal from "Mon\\\"t\\\"y"

MariaDB [test]> select "Mon\\\"t\\\"y";
 
+-----------+
 
| Mon\"t\"y |
 
+-----------+
 
| Mon\"t\"y |
 
+-----------+
 
1 row in set (0.00 sec)

After rules about string literals https://mariadb.com/kb/en/library/string-literals/ 

"Mon\\\"t\\\"y" -> Mon\(\\)\"t\\\"y -> Mon\"(\")t\\\"y -> Mon\"t\(\\)\"y  -> Mon\"t\"(\")y -> Mon\"t\"y

Comment by Lu Do [ 2017-11-16 ]

I think you don't understand the bug.
Let me explain it more : 

CREATE TABLE `test`.`test`( `test` TEXT ); 
INSERT INTO test (test) VALUES ('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }');
 
SELECT * FROM test ;


test                                            
------------------------------------------------
 
{ "x": [0,1], "y": "[0,1]", "z": "Mon\"t\"y" }

As you can see the \" \" is escape needed by Json to be a valid json.

But when I extract a value the escaped is not needed and must be remove to have the valid string.

SELECT JSON_VALUE(@json,'$.z') FROM test;


JSON_VALUE(@json,'$.z')  
-------------------------
 
Mon\"t\"y

In my case, I insert a value Mon"t"y and I expect to extract the same value not an escape one Mon\"t\"y

Another example : 

TRUNCATE TABLE test;
 
INSERT INTO test (test) VALUES ('[0]');
 
UPDATE test SET test=JSON_ARRAY_APPEND(test, '$', "Mon\"t\"y")
 
SELECT "Mon\"t\"y", JSON_VALUE(test, '$[1]'), JSON_EXTRACT(test, '$[1]') FROM test


Mon"t"y  JSON_VALUE(test, '$[1]')  JSON_EXTRACT(test, '$[1]')  
-------  ------------------------  ----------------------------
 
Mon"t"y  Mon\"t\"y                 "Mon\"t\"y"

As you can see I append to the array : Mon"t"y and when I query with JSON_VALUE I have : Mon\"t\"y
Still the escape is not wanted with JSON_VALUE

with JSON_EXTRACT, my software can do json_decode for example and will received the right value.
with JSON_VALUE, my software can't do json_decode since it's not a json value, and I don't have the right value

Comment by Alice Sherepa [ 2017-11-16 ]

yes, I admit I was wrong, JSON_VALUE should probably return Mon"t"y
I reopen this bug

Comment by Alexey Botchkov [ 2017-11-16 ]

http://lists.askmonty.org/pipermail/commits/2017-November/011666.html

Generated at Thu Feb 08 08:13:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.