[MDEV-11856] json_search doesn't search for values with double quotes character (") Created: 2017-01-20  Updated: 2017-03-15  Resolved: 2017-03-14

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2
Fix Version/s: 10.2.5

Type: Bug Priority: Major
Reporter: Andrii Nikitin (Inactive) Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Sprint: 10.2.5-1

 Description   

E.g. while searching for values with single quote character - everything works as expected:

SELECT JSON_search( '{"x": "\'a\'"}', "one", '\'a\'');
 
| "$.x"                                          |
 
SELECT JSON_search( '{"x": "\'"}', "one", '\'');
 
| "$.x"                                    |

But "the same" search for double quotes returns no result:

SELECT JSON_search( '{"x": "\\"a\\""}', "one", '"a"');
 
| NULL                                           |
 
SELECT JSON_search( '{"x": "\\""}', "one", '"');
 
| NULL                                     |

In MySQL 5.7:

 SELECT JSON_search( '{"x": "\\""}', "one", '"');
 
| "$.x"                                    |



 Comments   
Comment by Andrii Nikitin (Inactive) [ 2017-01-21 ]

Another example for similar problem with JSON_EXTRACT :

MariaDB > SELECT JSON_EXTRACT(JSON_OBJECT('"', 1), '$."\\""') AS JE;
 
+----+
 
| JE |
 
+----+
 
| NULL |
 
+----+


mysql> SELECT JSON_EXTRACT(JSON_OBJECT('"', 1), '$."\\""') AS JE;
 
+------+
 
| JE   |
 
+------+
 
| 1    |
 
+------+

Comment by Andrii Nikitin (Inactive) [ 2017-01-21 ]

Actually extra-escaping of search string in first example looks helping, but it doesn't sound correct as we pass string value to sql function, not json-string ?

MariaDB [test]> SELECT JSON_search( '{"x": "\\""}', "one", '\\"');
 
+--------------------------------------------+
 
| JSON_search( '{"x": "\\""}', "one", '\\"') |
 
+--------------------------------------------+
 
| "$.x"                                      |
 
+--------------------------------------------+

And removing quotes in second example helps as well! (MySQL 5.7 shows error "Invalid JSON path expression" though on it).

MariaDB [test]> SELECT JSON_EXTRACT(JSON_OBJECT('"', 1), '$."') AS JE;
 
+----+
 
| JE |
 
+----+
 
| 1  |
 
+----+

This still doesn't sound logically correct. Both functions should either expect valid SQL string (probably with extra escaping for JSON) or just valid JSON string. It looks that currently JSON_search() requires JSON string according to diagram at http://json.org/ , just without wrapping quotes. And JSON_EXTRACT() expects kind of SQL string.

Comment by Alexey Botchkov [ 2017-03-14 ]

http://lists.askmonty.org/pipermail/commits/2017-March/010855.html

Generated at Thu Feb 08 07:53:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.