revision-id: cd16d6d518761d144844f9f6294744f2aa42c715 (mariadb-10.2.24-25-gcd16d6d) parent(s): da6e55f022ffc85889f5d8d62cb1c3d4fe94a195 committer: Alexey Botchkov timestamp: 2019-05-17 11:53:58 +0400 message: MDEV-13992 Implement JSON_MERGE_PATCH. JSON_MERGE_PATCH implemented. Added JSON_MERGE_PRESERVE as a synonim for the JSON_MERGE. --- mysql-test/r/func_json.result | 75 ++++++- mysql-test/suite/json/r/json_no_table.result | 4 +- mysql-test/t/func_json.test | 43 ++++ sql/item_create.cc | 39 ++++ sql/item_jsonfunc.cc | 325 +++++++++++++++++++++++++++ sql/item_jsonfunc.h | 14 +- 6 files changed, 495 insertions(+), 5 deletions(-) diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index 752a745..b5f450f 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -306,7 +306,7 @@ select json_merge('string', 123); json_merge('string', 123) NULL Warnings: -Warning 4038 Syntax error in JSON text in argument 1 to function 'json_merge' at position 1 +Warning 4038 Syntax error in JSON text in argument 1 to function 'json_merge_preserve' at position 1 select json_merge('"string"', 123); json_merge('"string"', 123) ["string", 123] @@ -326,7 +326,7 @@ select json_merge('a','b'); json_merge('a','b') NULL Warnings: -Warning 4038 Syntax error in JSON text in argument 1 to function 'json_merge' at position 1 +Warning 4038 Syntax error in JSON text in argument 1 to function 'json_merge_preserve' at position 1 select json_merge('{"a":"b"}','{"c":"d"}'); json_merge('{"a":"b"}','{"c":"d"}') {"a": "b", "c": "d"} @@ -843,5 +843,76 @@ SELECT CHARSET(JSON_OBJECT()); CHARSET(JSON_OBJECT()) latin1 # +# MDEV-13992 Implement JSON_MERGE_PATCH +# +CREATE TABLE merge_t( +id INT PRIMARY KEY AUTO_INCREMENT, +target VARCHAR(100), patch VARCHAR(100) +); +INSERT INTO merge_t(target, patch) VALUES +('{"a":"b"}', '{"a":"c"}'), +('{"a":"b"}', '{"b":"c"}'), +('{"a":"b"}', '{"a":null}'), +('{"a":"b", "b":"c"}', '{"a":null}'), +('{"a":["b"]}', '{"a":"c"}'), +('{"a":"c"}', '{"a":["b"]}'), +('{"a": {"b":"c"}}', '{"a": {"b":"d", "c":null}}'), +('{"a":[{"b":"c"}]}', '{"a": [1]}'), +('["a","b"]', '["c","d"]'), +('{"a":"b"}', '["c"]'), +('{"a":"foo"}', 'null'), +('{"a":"foo"}', '"bar"'), +('{"e":null}', '{"a":1}'), +('[1,2]', '{"a":"b", "c":null}'), +('{}', '{"a":{"bb":{"ccc":null}}}'), +(NULL, '{}'), +('{}', NULL); +SELECT id, target, patch, +JSON_MERGE_PATCH(target, patch) AS merged, +JSON_EXTRACT(JSON_MERGE_PATCH(target, patch), '$.a') AS a +FROM merge_t ORDER BY id; +id target patch merged a +1 {"a":"b"} {"a":"c"} {"a": "c"} "c" +2 {"a":"b"} {"b":"c"} {"a": "b", "b": "c"} "b" +3 {"a":"b"} {"a":null} {} NULL +4 {"a":"b", "b":"c"} {"a":null} {"b": "c"} NULL +5 {"a":["b"]} {"a":"c"} {"a": "c"} "c" +6 {"a":"c"} {"a":["b"]} {"a": ["b"]} ["b"] +7 {"a": {"b":"c"}} {"a": {"b":"d", "c":null}} {"a": {"b": "d"}} {"b": "d"} +8 {"a":[{"b":"c"}]} {"a": [1]} {"a": [1]} [1] +9 ["a","b"] ["c","d"] ["c", "d"] NULL +10 {"a":"b"} ["c"] ["c"] NULL +11 {"a":"foo"} null null NULL +12 {"a":"foo"} "bar" "bar" NULL +13 {"e":null} {"a":1} {"e": null, "a": 1} 1 +14 [1,2] {"a":"b", "c":null} {"a": "b"} "b" +15 {} {"a":{"bb":{"ccc":null}}} {"a": {"bb": {}}} {"bb": {}} +16 NULL {} NULL NULL +17 {} NULL NULL NULL +DROP TABLE merge_t; +SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}'); +JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}') +NULL +SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]'); +JSON_MERGE_PATCH(NULL, '[1,2,3]') +[1, 2, 3] +SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}'); +JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}') +{"d": "e"} +SELECT JSON_MERGE_PATCH(); +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' +SELECT JSON_MERGE_PATCH('{}'); +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' +SELECT JSON_MERGE_PATCH('{', '[1,2,3]'); +JSON_MERGE_PATCH('{', '[1,2,3]') +NULL +Warnings: +Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge_patch' +SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,'); +JSON_MERGE_PATCH('{"a":"b"}', '[1,') +NULL +Warnings: +Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_merge_patch' +# # End of 10.2 tests # diff --git a/mysql-test/suite/json/r/json_no_table.result b/mysql-test/suite/json/r/json_no_table.result index 4115003..b8ac19b 100644 --- a/mysql-test/suite/json/r/json_no_table.result +++ b/mysql-test/suite/json/r/json_no_table.result @@ -821,13 +821,13 @@ select json_merge( '[1, 2]', '[3, 4' ); json_merge( '[1, 2]', '[3, 4' ) NULL Warnings: -Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_merge' +Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_merge_preserve' error ER_INVALID_JSON_TEXT_IN_PARAM select json_merge( '[1, 2', '[3, 4]' ); json_merge( '[1, 2', '[3, 4]' ) NULL Warnings: -Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge' +Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge_preserve' select json_merge( '1', '2' ); json_merge( '1', '2' ) [1, 2] diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index 8e924fb..5b77c2c 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -495,5 +495,48 @@ SELECT CHARSET(JSON_ARRAY()); SELECT CHARSET(JSON_OBJECT()); --echo # +--echo # MDEV-13992 Implement JSON_MERGE_PATCH +--echo # + +CREATE TABLE merge_t( +id INT PRIMARY KEY AUTO_INCREMENT, +target VARCHAR(100), patch VARCHAR(100) +); +INSERT INTO merge_t(target, patch) VALUES +('{"a":"b"}', '{"a":"c"}'), +('{"a":"b"}', '{"b":"c"}'), +('{"a":"b"}', '{"a":null}'), +('{"a":"b", "b":"c"}', '{"a":null}'), +('{"a":["b"]}', '{"a":"c"}'), +('{"a":"c"}', '{"a":["b"]}'), +('{"a": {"b":"c"}}', '{"a": {"b":"d", "c":null}}'), +('{"a":[{"b":"c"}]}', '{"a": [1]}'), +('["a","b"]', '["c","d"]'), +('{"a":"b"}', '["c"]'), +('{"a":"foo"}', 'null'), +('{"a":"foo"}', '"bar"'), +('{"e":null}', '{"a":1}'), +('[1,2]', '{"a":"b", "c":null}'), +('{}', '{"a":{"bb":{"ccc":null}}}'), +(NULL, '{}'), +('{}', NULL); +SELECT id, target, patch, + JSON_MERGE_PATCH(target, patch) AS merged, + JSON_EXTRACT(JSON_MERGE_PATCH(target, patch), '$.a') AS a +FROM merge_t ORDER BY id; +DROP TABLE merge_t; + +SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}'); +SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]'); +SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}'); + +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT JSON_MERGE_PATCH(); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT JSON_MERGE_PATCH('{}'); +SELECT JSON_MERGE_PATCH('{', '[1,2,3]'); +SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,'); + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_create.cc b/sql/item_create.cc index 6e98bcc..4b7400eb 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -2049,6 +2049,19 @@ class Create_func_json_merge : public Create_native_func }; +class Create_func_json_merge_patch : public Create_native_func +{ +public: + virtual Item *create_native(THD *thd, LEX_STRING name, List<Item> *item_list); + + static Create_func_json_merge_patch s_singleton; + +protected: + Create_func_json_merge_patch() {} + virtual ~Create_func_json_merge_patch() {} +}; + + class Create_func_json_quote : public Create_func_arg1 { public: @@ -5414,6 +5427,30 @@ Create_func_json_merge::create_native(THD *thd, LEX_STRING name, } +Create_func_json_merge_patch Create_func_json_merge_patch::s_singleton; + +Item* +Create_func_json_merge_patch::create_native(THD *thd, LEX_STRING name, + List<Item> *item_list) +{ + Item *func; + int arg_count; + + if (item_list == NULL || + (arg_count= item_list->elements) < 2) // json, json + { + my_error(ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT, MYF(0), name.str); + func= NULL; + } + else + { + func= new (thd->mem_root) Item_func_json_merge_patch(thd, *item_list); + } + + return func; +} + + Create_func_json_contains Create_func_json_contains::s_singleton; Item* @@ -6825,6 +6862,8 @@ static Native_func_registry func_array[] = { { C_STRING_WITH_LEN("JSON_LENGTH") }, BUILDER(Create_func_json_length)}, { { C_STRING_WITH_LEN("JSON_LOOSE") }, BUILDER(Create_func_json_loose)}, { { C_STRING_WITH_LEN("JSON_MERGE") }, BUILDER(Create_func_json_merge)}, + { { C_STRING_WITH_LEN("JSON_MERGE_PATCH") }, BUILDER(Create_func_json_merge_patch)}, + { { C_STRING_WITH_LEN("JSON_MERGE_PRESERVE") }, BUILDER(Create_func_json_merge)}, { { C_STRING_WITH_LEN("JSON_QUERY") }, BUILDER(Create_func_json_query)}, { { C_STRING_WITH_LEN("JSON_QUOTE") }, BUILDER(Create_func_json_quote)}, { { C_STRING_WITH_LEN("JSON_OBJECT") }, BUILDER(Create_func_json_object)}, diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc index e236010..4db4c56 100644 --- a/sql/item_jsonfunc.cc +++ b/sql/item_jsonfunc.cc @@ -2150,6 +2150,331 @@ String *Item_func_json_merge::val_str(String *str) } +static int copy_value_patch(String *str, json_engine_t *je) +{ + int first_key= 1; + + if (je->value_type != JSON_VALUE_OBJECT) + { + const uchar *beg, *end; + + beg= je->value_begin; + + if (!json_value_scalar(je)) + { + if (json_skip_level(je)) + return 1; + end= je->s.c_str; + } + else + end= je->value_end; + + if (append_simple(str, beg, end-beg)) + return 1; + + return 0; + } + /* JSON_VALUE_OBJECT */ + + if (str->append("{", 1)) + return 1; + while (json_scan_next(je) == 0 && je->state != JST_OBJ_END) + { + const uchar *key_start; + /* Loop through the Json_1 keys and compare with the Json_2 keys. */ + DBUG_ASSERT(je->state == JST_KEY); + key_start= je->s.c_str; + + if (json_read_value(je)) + return 1; + + if (je->value_type == JSON_VALUE_NULL) + continue; + + if (!first_key) + { + if (str->append(", ", 2)) + return 3; + } + else + first_key= 0; + + if (str->append("\"", 1) || + append_simple(str, key_start, je->value_begin - key_start) || + copy_value_patch(str, je)) + return 1; + } + if (str->append("}", 1)) + return 1; + + return 0; +} + + +static int do_merge_patch(String *str, json_engine_t *je1, json_engine_t *je2, + bool *empty_result) +{ + if (json_read_value(je1) || json_read_value(je2)) + return 1; + + if (je1->value_type == JSON_VALUE_OBJECT && + je2->value_type == JSON_VALUE_OBJECT) + { + json_engine_t sav_je1= *je1; + json_engine_t sav_je2= *je2; + + int first_key= 1; + json_string_t key_name; + size_t sav_len; + bool mrg_empty; + + *empty_result= FALSE; + json_string_set_cs(&key_name, je1->s.cs); + + if (str->append("{", 1)) + return 3; + while (json_scan_next(je1) == 0 && + je1->state != JST_OBJ_END) + { + const uchar *key_start, *key_end; + /* Loop through the Json_1 keys and compare with the Json_2 keys. */ + DBUG_ASSERT(je1->state == JST_KEY); + key_start= je1->s.c_str; + do + { + key_end= je1->s.c_str; + } while (json_read_keyname_chr(je1) == 0); + + if (je1->s.error) + return 1; + + sav_len= str->length(); + + if (!first_key) + { + if (str->append(", ", 2)) + return 3; + *je2= sav_je2; + } + + if (str->append("\"", 1) || + append_simple(str, key_start, key_end - key_start) || + str->append("\":", 2)) + return 3; + + while (json_scan_next(je2) == 0 && + je2->state != JST_OBJ_END) + { + int ires; + DBUG_ASSERT(je2->state == JST_KEY); + json_string_set_str(&key_name, key_start, key_end); + if (!json_key_matches(je2, &key_name)) + { + if (je2->s.error || json_skip_key(je2)) + return 2; + continue; + } + + /* Json_2 has same key as Json_1. Merge them. */ + if ((ires= do_merge_patch(str, je1, je2, &mrg_empty))) + return ires; + + if (mrg_empty) + str->length(sav_len); + else + first_key= 0; + + goto merged_j1; + } + + if (je2->s.error) + return 2; + + key_start= je1->s.c_str; + /* Just append the Json_1 key value. */ + if (json_skip_key(je1)) + return 1; + if (append_simple(str, key_start, je1->s.c_str - key_start)) + return 3; + first_key= 0; + +merged_j1: + continue; + } + + *je2= sav_je2; + /* + Now loop through the Json_2 keys. + Skip if there is same key in Json_1 + */ + while (json_scan_next(je2) == 0 && + je2->state != JST_OBJ_END) + { + const uchar *key_start, *key_end; + DBUG_ASSERT(je2->state == JST_KEY); + key_start= je2->s.c_str; + do + { + key_end= je2->s.c_str; + } while (json_read_keyname_chr(je2) == 0); + + if (je2->s.error) + return 1; + + *je1= sav_je1; + while (json_scan_next(je1) == 0 && + je1->state != JST_OBJ_END) + { + DBUG_ASSERT(je1->state == JST_KEY); + json_string_set_str(&key_name, key_start, key_end); + if (!json_key_matches(je1, &key_name)) + { + if (je1->s.error || json_skip_key(je1)) + return 2; + continue; + } + if (json_skip_key(je2) || + json_skip_level(je1)) + return 1; + goto continue_j2; + } + + if (je1->s.error) + return 2; + + + sav_len= str->length(); + + if (!first_key && str->append(", ", 2)) + return 3; + + if (str->append("\"", 1) || + append_simple(str, key_start, key_end - key_start) || + str->append("\":", 2)) + return 3; + + if (json_read_value(je2)) + return 1; + + if (je2->value_type == JSON_VALUE_NULL) + str->length(sav_len); + else + { + if (copy_value_patch(str, je2)) + return 1; + first_key= 0; + } + +continue_j2: + continue; + } + + if (str->append("}", 1)) + return 3; + } + else + { + if (!json_value_scalar(je1) && json_skip_level(je1)) + return 1; + + *empty_result= je2->value_type == JSON_VALUE_NULL; + if (!(*empty_result) && copy_value_patch(str, je2)) + return 1; + } + + return 0; +} + + +String *Item_func_json_merge_patch::val_str(String *str) +{ + DBUG_ASSERT(fixed == 1); + json_engine_t je1, je2; + String *js1= args[0]->val_json(&tmp_js1), *js2=NULL; + uint n_arg; + bool empty_result, merge_to_null; + + merge_to_null= args[0]->null_value; + + for (n_arg=1; n_arg < arg_count; n_arg++) + { + js2= args[n_arg]->val_json(&tmp_js2); + if (args[n_arg]->null_value) + { + merge_to_null= true; + goto cont_point; + } + + json_scan_start(&je2, js2->charset(),(const uchar *) js2->ptr(), + (const uchar *) js2->ptr() + js2->length()); + + if (merge_to_null) + { + if (json_read_value(&je2)) + goto error_return; + if (je2.value_type == JSON_VALUE_OBJECT) + { + merge_to_null= true; + goto cont_point; + } + merge_to_null= false; + str->set(js2->ptr(), js2->length(), js2->charset()); + goto cont_point; + } + + str->set_charset(js1->charset()); + str->length(0); + + + json_scan_start(&je1, js1->charset(),(const uchar *) js1->ptr(), + (const uchar *) js1->ptr() + js1->length()); + + if (do_merge_patch(str, &je1, &je2, &empty_result)) + goto error_return; + + if (empty_result) + str->append("null"); + +cont_point: + { + /* Swap str and js1. */ + if (str == &tmp_js1) + { + str= js1; + js1= &tmp_js1; + } + else + { + js1= str; + str= &tmp_js1; + } + } + } + + if (merge_to_null) + goto null_return; + + json_scan_start(&je1, js1->charset(),(const uchar *) js1->ptr(), + (const uchar *) js1->ptr() + js1->length()); + str->length(0); + str->set_charset(js1->charset()); + if (json_nice(&je1, str, Item_func_json_format::LOOSE)) + goto error_return; + + null_value= 0; + return str; + +error_return: + if (je1.s.error) + report_json_error(js1, &je1, 0); + if (je2.s.error) + report_json_error(js2, &je2, n_arg); +null_return: + null_value= 1; + return NULL; +} + + bool Item_func_json_length::fix_length_and_dec() { if (arg_count > 1) diff --git a/sql/item_jsonfunc.h b/sql/item_jsonfunc.h index f235816..d7f804a 100644 --- a/sql/item_jsonfunc.h +++ b/sql/item_jsonfunc.h @@ -291,12 +291,24 @@ class Item_func_json_merge: public Item_func_json_array Item_func_json_array(thd, list) {} String *val_str(String *); bool is_json_type() { return true; } - const char *func_name() const { return "json_merge"; } + const char *func_name() const { return "json_merge_preserve"; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return get_item_copy<Item_func_json_merge>(thd, mem_root, this); } }; +class Item_func_json_merge_patch: public Item_func_json_merge +{ +public: + Item_func_json_merge_patch(THD *thd, List<Item> &list): + Item_func_json_merge(thd, list) {} + const char *func_name() const { return "json_merge_patch"; } + String *val_str(String *); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy<Item_func_json_merge_patch>(thd, mem_root, this); } +}; + + class Item_func_json_length: public Item_int_func { protected: