Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
-
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" | |
Attachments
Activity
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.
Another example for similar problem with JSON_EXTRACT :
| JE || JE || 1 |