MySQL Bugs: #86213: JSON_INSERT does not handle auto-wrapping

Bug #86213	JSON_INSERT does not handle auto-wrapping
Submitted:	8 May 2017 11:46	Modified:	8 Aug 2017 15:35
Reporter:	Knut Anders Hatlen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
WL#7909 says: "If a pathExpression identifies a non-array value, then pathExpression[ 0 ] evaluates to the same value as pathExpression." The JSON_INSERT function doesn't follow this rule.

mysql> select json_insert('{"a":1}', '$.a[1]', 123) as c1,
    ->        json_insert('{"a":1}', '$[0].a[1]', 123) as c2,
    ->        json_insert('{"a":1}', '$.a[0][1]', 123) as c3;
+-----------------+----------+----------+
| c1              | c2       | c3       |
+-----------------+----------+----------+
| {"a": [1, 123]} | {"a": 1} | {"a": 1} |
+-----------------+----------+----------+
1 row in set (0,00 sec)

Here, c2 and c3 should have had the same value as c1. Since '$' is a non-array, '$[0].a[1]' should be equivalent to '$.a[1]'. Similarly, '$.a' is a non-array, so '$.a[0][1]' should be equivalent to '$.a[1]'.

How to repeat:
select json_insert('{"a":1}', '$.a[1]', 123) as c1,
       json_insert('{"a":1}', '$[0].a[1]', 123) as c2,
       json_insert('{"a":1}', '$.a[0][1]', 123) as c3;

Suggested fix:
Maybe the implementation of JSON_INSERT could call clone_without_autowrapping() to normalize the paths, like JSON_SET and JSON_REPLACE do.

Posted by developer:
 
Similar problem in JSON_ARRAY_INSERT:

mysql> select json_array_insert('{"a":[1]}', '$.a[1]', 123) as c1,
    ->        json_array_insert('{"a":[1]}', '$[0].a[1]', 123) as c2;
+-----------------+------------+
| c1              | c2         |
+-----------------+------------+
| {"a": [1, 123]} | {"a": [1]} |
+-----------------+------------+
1 row in set (0,00 sec)

Documented fix in the MySQL 8.0.3 changelog as follows:

    When a path_expression identified a nonarray value, the
    JSON_INSERT() and JSON_ARRAY_INSERT() functions failed to
    evaluate path_expression[0] as being equal to path_expression.

Closed.