WL#9692: Add JSON_MERGE_PATCH, rename JSON_MERGE to JSON_MERGE_PRESERVE

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Currently JSON_MERGE merges two JSON documents by including duplicates:

    JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'); # returns { "a": [1,3], "b":2, "c": 4}

The semantics of JavaScript (and other scripting languages) would be to use 
precedence of the second document.  This is proposed in BUG#81283 as a new 
function.

To support different use cases and minimize confusion among users, the following
steps are proposed:

1. Create a new JSON_MERGE_PATCH() function that supports behavior familiar to
PHP users and implementors of REST services, and is specified by
<https://tools.ietf.org/html/rfc7396>

2. Introduce a new JSON_MERGE_PRESERVE() function that is an alias to the 
existing JSON_MERGE() function

3. Deprecate the existing JSON_MERGE() function in MySQL 8.0, to remove ambiguity 
for the merge operation.
## Functional requirements

F-1: When the JSON_MERGE function is used in a query, a deprecation warning
should be raised when the query is prepared.

F-2: When the function JSON_MERGE_PRESERVE is called, it should behave the same
way as JSON_MERGE, except that it doesn't raise a deprecation warning.

F-3: If JSON_MERGE_PATCH is called with less than two arguments, an error should
be raised.

F-4: If one of the arguments to JSON_MERGE_PATCH is NULL (the SQL unknown
value), and all of the arguments after the last NULL argument are JSON objects,
the result of the function should be NULL.

F-5: If one of the arguments to JSON_MERGE_PATCH is not of type JSON and is not
a valid JSON text, an error should be raised.

F-6: If JSON_MERGE_PATCH is called with more than two arguments, it should merge
the first two arguments, and then successively merge the next argument into the
result of the previous merge. For example, JSON_MERGE_PATCH(arg1, arg2, arg3,
arg4) is equivalent to JSON_MERGE_PATCH(JSON_MERGE_PATCH(JSON_MERGE_PATCH(arg1,
arg2), arg3), arg4).

F-7: If JSON_MERGE_PATCH is called with two non-NULL arguments that are valid
JSON values, it should return the value given by the algorithm described in RFC
7396:

    define MergePatch(Target, Patch):
      if Patch is an Object:
        if Target is not an Object:
          Target = {} # Ignore the contents and set it to an empty Object
        for each Key/Value pair in Patch:
          if Value is null:
            if Key exists in Target:
              remove the Key/Value pair from Target
          else:
            Target[Key] = MergePatch(Target[Key], Value)
        return Target
      else:
        return Patch
Changes in the JSON_MERGE function
----------------------------------

JSON_MERGE is changed to raise a warning when the statement is prepared,
suggesting that JSON_MERGE is deprecated, and that JSON_MERGE_PRESERVE or
JSON_MERGE_PATCH should be used instead.

New function: JSON_MERGE_PRESERVE
---------------------------------

The new function JSON_MERGE_PRESERVE behaves the same way as JSON_MERGE
currently does. The only difference between JSON_MERGE and JSON_MERGE_PRESERVE
is that the former will raise a deprecation warning.

A note about the name "JSON_MERGE_PRESERVE": The original proposal was
"JSON_MERGE_RECURSIVE". But JSON_MERGE_PATCH also uses a recursive algorithm, so
it is not very descriptive. The "PRESERVE" suffix is meant to signal that the
result always preserves all data from the original documents. This is in
contrast to JSON_MERGE_PATCH, which could replace or remove some of the original
data.

New function: JSON_MERGE_PATCH
------------------------------

This function merges two or more json documents into a single document, using
the merge algorithm specified in RFC 7396.

    JSON JSON_MERGE_PATCH(ANY_JSON doc, ANY_JSON doc2 [ (, ANY_JSON otherdocs )* ])

Returns:

- *unknown* - Returns NULL if any of the arguments are NULL and all of the arguments after the last NULL argument are JSON objects.

- *document* - Otherwise returns a sensible JSON value produced by merging the documents as described below.

Behavior:

- *invalid* - Raises an error if any of the documents isn't valid.

- If the first argument is not an object, the merge result is the same as if
  an empty object had been merged with the second argument.

- If the second argument is not an object, the merge result is the second
  argument.

- If both arguments are objects, the merge result is an object with the
  following members:

  - all members of the first object which do not have a corresponding member
    with the same key in the second object.

  - all members of the second object that don't have a corresponding key in
    the first object, and whose value is not the JSON null literal.

  - all members with a key that exists in both the first and the second
    object, and whose value in the second object is not the JSON null
    literal. The values of these members are the results of recursively
    merging the value in the first object with the value in the second object.

For example:

    # Returns {"a":"c"}
    SELECT JSON_MERGE_PATCH('{"a":"b"}', '{"a":"c"}');

    # Returns {"a":"b", "b":"c"}
    SELECT JSON_MERGE_PATCH('{"a":"b"}', '{"b":"c"}');

    # Returns {}
    SELECT JSON_MERGE_PATCH('{"a":"b"}', '{"a":null}');

    # Returns {"b":"c"}
    SELECT JSON_MERGE_PATCH('{"a":"b", "b":"c"}', '{"a":null}');

    # Returns {"a":"c"}
    SELECT JSON_MERGE_PATCH('{"a":["b"]}', '{"a":"c"}');

    # Returns {"a":["b"]}
    SELECT JSON_MERGE_PATCH('{"a":"c"}', '{"a":["b"]}');

    # Returns {"a": {"b":"d"}}
    SELECT JSON_MERGE_PATCH('{"a": {"b":"c"}}',
                            '{"a": {"b":"d", "c":null}}');

    # Returns {"a": [1]}
    SELECT JSON_MERGE_PATCH('{"a":[{"b":"c"}]}', '{"a": [1]}');

    # Returns ["c","d"]
    SELECT JSON_MERGE_PATCH('["a","b"]', '["c","d"]');

    # Returns ["c"]
    SELECT JSON_MERGE_PATCH('{"a":"b"}', '["c"]');

    # Returns the JSON null literal
    SELECT JSON_MERGE_PATCH('{"a":"foo"}', 'null');

    # Returns "bar"
    SELECT JSON_MERGE_PATCH('{"a":"foo"}', '"bar"');

    # Returns {"e":null, "a":1}
    SELECT JSON_MERGE_PATCH('{"e":null}', '{"a":1}');

    # Returns {"a":"b"}
    SELECT JSON_MERGE_PATCH('[1,2]', '{"a":"b", "c":null}');

    # Returns {"a": {"bb":{}}}
    SELECT JSON_MERGE_PATCH('{}', '{"a":{"bb":{"ccc":null}}}');

    # Returns UNKNOWN
    SELECT JSON_MERGE_PATCH(NULL, '{}');

    # Returns UNKNOWN
    SELECT JSON_MERGE_PATCH('{}', NULL);

    # Returns UNKNOWN
    SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}');

    # Returns [1,2,3]
    SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]');

    # Returns {"d":"e"}
    SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}');
The Item_func_json_merge class should be renamed to Item_func_merge_preserve,
and should be wired into the parser by adding it to func_array in item_create.cc.

A new class named Item_func_json_merge should be added. It should inherit from
Item_func_json_merge_preserve and not override any methods. Its constructor
should raise an ER_WARN_DEPRECATED_SYNTAX warning.

A new class named Item_func_json_merge_patch should be added, whose val_json()
method implements the JSON_MERGE_PATCH function. The class should be wired into
the parser by adding it to func_array in item_create.cc.