MySQL Bugs: #79233: JSON_SEARCH does not search for non-string values

Bug #79233	JSON_SEARCH does not search for non-string values
Submitted:	11 Nov 2015 14:42	Modified:	17 Nov 2015 18:11
Reporter:	Georgi Kodinov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.7+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html says
"
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

Returns the path to the given scalar value within a JSON document. Returns NULL if any of the json_doc, search_str, or path arguments are NULL; no path exists within the document; or search_str is not found. An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, one_or_all is not 'one' or 'all', or escape_char is not a constant expression.

The one_or_all argument affects the search as follows:

    'one': The search terminates after the first match and returns one path string. It is undefined which match is considered first. 
"

But I can't find an that's not a string !

How to repeat:
mysql> SELECT JSON_SEARCH( '{"a": "1", "b": "2" }', 'one', 2);
+-------------------------------------------------+
| JSON_SEARCH( '{"a": "1", "b": "2" }', 'one', 2) |
+-------------------------------------------------+
| "$.b"                                           |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SEARCH( '{"a": "1", "b": 2 }', 'one', 2);
+-----------------------------------------------+
| JSON_SEARCH( '{"a": "1", "b": 2 }', 'one', 2) |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Make json_search adapt to the type of the SQL argument.

Thank you for the bug report.

mysql 5.7 >  SELECT JSON_SEARCH( '{"a": "1", "b": "2" }', 'one', 2);
+-------------------------------------------------+
| JSON_SEARCH( '{"a": "1", "b": "2" }', 'one', 2) |
+-------------------------------------------------+
| "$.b"                                           |
+-------------------------------------------------+
1 row in set (0.02 sec)

mysql 5.7 > SELECT JSON_SEARCH( '{"a": "1", "b": 2 }', 'one', 2);
+-----------------------------------------------+
| JSON_SEARCH( '{"a": "1", "b": 2 }', 'one', 2) |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.00 sec)

Changing category to documentation.

The specification at https://dev.mysql.com/worklog/task/?id=7909 says: "This function returns path(s) to the given string. The returned path(s) identify object members or array slots which are character strings."

So it seems that the intention of this function was to search for strings. The documentation should be updated to clarify that the function is for searching for string scalars, not for scalars in general.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Bug #79316 marked as duplicate of this

Filed a feature request for the need here: Bug #90085