diff options
| -rw-r--r-- | mysql-test/main/func_json.result | 288 | ||||
| -rw-r--r-- | mysql-test/main/func_json.test | 209 | ||||
| -rw-r--r-- | mysql-test/main/json_debug_nonembedded.result | 2 | ||||
| -rw-r--r-- | mysql-test/main/json_debug_nonembedded.test | 4 | ||||
| -rw-r--r-- | sql/item_create.cc | 23 | ||||
| -rw-r--r-- | sql/item_jsonfunc.cc | 826 | ||||
| -rw-r--r-- | sql/item_jsonfunc.h | 61 | ||||
| -rw-r--r-- | sql/share/errmsg-utf8.txt | 4 |
8 files changed, 1417 insertions, 0 deletions
diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 375432bb052..264fad093ea 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -2350,3 +2350,291 @@ JSON_OVERLAPS(@json1, @json2) # # End of 10.9 Test # +# +# Beginning of 10.11 tests +# +# MDEV-26182: Implement JSON_INTERSECT() +# +# scalar(number) intersect scalar(number) +SET @json1 = '1'; +SET @json2 = '1'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +1 +SET @json1 = '1'; +SET @json2 = '2'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +SET @json1 = '1.0'; +SET @json2 = '1.00'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +1.0 +# scalar intersect scalar array +SET @json1 = '3'; +SET @json2 = '[1,2,3,4,5]'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +3 +SET @json1 = '6'; +SET @json2 = '[1,2,3,4,5]'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +# scalar(string) intersect scalar(string) +SET @json1 = '"a"'; +SET @json2 = '"a"'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +"a" +SET @json1 = '"a"'; +SET @json2 = '"b"'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +# scalar(string) intersect scalar(string) array +SET @json1 = '"z"'; +SET @json2 = '["a","b","cd","ef","f"]'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +# sclar intersect object +SET @json1 = '"z"'; +SET @json2 = '{"key1":"value1"}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +SET @json1 = '3'; +SET @json2 = '{"key1":"value1"}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +# object intersect object +SET @json1 = '{"key1":"value1","key2":"value2"}'; +SET @json2 = '{"key2":"value2","key1":"value1"}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +{"key1": "value1", "key2": "value2"} +SET @json1 = '{"key1":"value1"}'; +SET @json2 = '{"key2":"value2"}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +# object intersect object array +SET @json1 = '{"key1":"value1"}'; +SET @json2 = '[{"key1":"value1"},{"key2":"value2"}]'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +{"key1": "value1"} +# object inersect scalar +SET @json1 = '{"key1":"value1"}'; +SET @json2 = '1'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +# nested object intersect nested object +SET @json1 = '{"key1":{"kkey1":"vvalue1"}}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"}}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +{"key1": {"kkey1": "vvalue1"}} +SET @json1= '{"key1":{"key1":{"key1":"value1"}}}'; +SET @json2= '{"key1":{"key1":{"key1":"value1"}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"key1": {"key1": {"key1": "value1"}}} +SET @json1 = '{"key1":{"kkey1":"vvalue1","kkey2":"vvalue2"}}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"}}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +SET @json1 = '{"key1":{"kkey1":"vvalue1","kkey2":"vvalue2"},"key2":{"kkey2":"vvalue2","kkey3":"vvalue3"},"key3":{"kkey3":{"kkkey3":"vvvalue3"}},"key5":"value3"}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"},"key2":{"kkey2":"vvalue2"},"key3":{"kkey3":{"kkkey3":"vvvalue3"}},"key4":"value3"}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +{"key3": {"kkey3": {"kkkey3": "vvvalue3"}}} +SET @json1 = '{"key1":[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"}}'; +SELECT JSON_INTERSECT(@json1,@json2); +JSON_INTERSECT(@json1,@json2) +NULL +SET @json1= '{"key1":"val1", "key2":"val2", "key3":{"key7":"val7"}}'; +SET @json2= '{"key4":"val4", "key2":"val2", "key5":"val5", "key3":"val3", "key6":"val6"}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"key2": "val2"} +# object have duplicate keys +SET @json1= '{"key1":"val1", "key2":"val2", "key3":"val3"}'; +SET @json2= '{"key3":"val3", "key2":"val2", "key4":"val4", "key3":"val3", "key5":"val5"}'; +SELECT JSON_INTERSECT(@json1, @json2); +ERROR HY000: Duplicate keys in the same object +SELECT JSON_INTERSECT(@json2, @json1); +ERROR HY000: Duplicate keys in the same object +SET @json1 = '{"k1":"v1","k2":"v2","k3":{"kk1":"v1","kk1":"v2"}}'; +SET @json2 = '{"k1":"v1","k2":"v2","k3":{"kk1":"v1","kk1":"v2"}}'; +SELECT JSON_INTERSECT(@json1, @json2); +ERROR HY000: Duplicate keys in the same object +# syntax error +SET @json1= '{"key1":"value1"'; +SET @json2= '{"key1":"val3", "key2":"val2", "key4":"val4", "key3":"val3", "key5":"val5"}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +NULL +Warnings: +Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_intersect' +SET @json1= '[1,2,3,4'; +SET @json2= '[1,2,3,4]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +NULL +Warnings: +Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_intersect' +SET @json1= '[{"key1":"value1"}]'; +SET @json2= '["key1":"value1"]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +NULL +Warnings: +Warning 4038 Syntax error in JSON text in argument 2 to function 'json_intersect' at position 8 +# array intersect array +SET @json1= '[1,2,3]'; +SET @json2= '[1,2,3]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[1, 2, 3] +SET @json1= '[1,2,3]'; +SET @json2= '[1,2,4]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[1, 2] +# object(with array value)intersect object +SET @json1= '{"key1":[1,2,3]}'; +SET @json2= '{"key1":[1,2,3]}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"key1": [1, 2, 3]} +SET @json1= '{"key1":[1,2,3]}'; +SET @json2= '{"key1":[1,2,4]}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +NULL +# object array intersect object array +SET @json1 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SET @json2 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[{"kkey1": "vvalue1"}, {"kkey2": "vvalue2"}] +SET @json1 = '[{"kkey1":"vvalue1"},{"kkey3":"vvalue2"}]'; +SET @json2 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[{"kkey1": "vvalue1"}] +# object array inersect object +SET @json1 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SET @json2 = '{"kkey2":"vvalue2"}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"kkey2": "vvalue2"} +# deeply nested object intersect object +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SET @json2 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"key1": {"kkey1": {"kkkey1": {"kkkkey1": "value1"}}}} +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SET @json2 = '{"key1":{"kkey1":{"kkkey2":{"kkkkey1":"value1"}}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +NULL +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SET @json2 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1_not"}}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +NULL +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}},"key2":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}},"key3":[{"kkey3":"vvalue3"},4,"666"]}'; +SET @json2 = '{"key2":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}} ,"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1_not"}}}, "key3":[{"kkey3":"vvalue3"},4,"666"]}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"key2": {"kkey1": {"kkkey1": {"kkkkey1": "value1"}}}, "key3": [{"kkey3": "vvalue3"}, 4, "666"]} +SET @json1 = '{"k1":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv5"}},"k2":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv4"}},"k3":[{"kk1":{"kk1":"vv1","kk2":"vv2"}},{"kk1":"vv1"}],"k4":[{"kk1":"vv1"},{"kk1":{"kk1":"vv1","kk2":"vv2"}}]}'; +SET @json2 = '{"k1":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv5"}},"k2":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv5"}},"k3":[{"kk1":{"kk1":"vv1","kk2":"vv2"}},{"kk1":"vv1"}],"k4":[{"kk1":{"kk1":"vv1","kk2":"vv2"}},{"kk1":"vv1"}]}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"k1": {"kk1": "vv1", "kk2": "vv2", "kk3": {"kkk4": "vvv4", "kkk5": "vvv5"}}, "k3": [{"kk1": {"kk1": "vv1", "kk2": "vv2"}}, {"kk1": "vv1"}]} +# nested array intersect array +SET @json1= '[1,2,3]'; +SET @json2= '[1.0,2.0,3.0]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[1.0, 2.0, 3.0] +SET @json1= '[6,6,6]'; +SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +NULL +SET @json1= '[[1,2,3],[4,5,6],[1,3,2]]'; +SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[[1, 2, 3], [4, 5, 6], [1, 3, 2]] +# array intersect array(with the same KV pair.) +SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2"}]'; +SET @json2 = '[{"kkey1":"vvalue1"},{"k2":"v2","k1":"v1"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[{"k2": "v2", "k1": "v1"}] +SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[{"k1": "v1", "k2": "v2"}, {"k1": "v1", "k2": "v2"}] +SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[{"k1": "v1", "k2": "v2"}, {"k1": "v1", "k2": "v2"}] +SET @json1 = '[{"k1":"v1","k2":[1,2,3]},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":[1,2,3]},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[{"k1": "v1", "k2": "v2"}, {"k1": "v1", "k2": [1, 2, 3]}] +SET @json1 = '[{"k1":"v1","k2":[1,2,3]},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":[3,2,1]},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +[{"k1": "v1", "k2": "v2"}] +SET @json1 = '{"k1":"v1","k2":"v2","k3":{"kk1":"v1","kk3":"v2"}}'; +SET @json2 = '{"k1":"v1","k2":"v2","k3":{"k1":"v1","k2":"v2"}}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"k1": "v1", "k2": "v2"} +# multi type nesting +SET @json1 = '{"k2":{"1":"2"},"k1":{"k1":"v1","k2":"v2"}}'; +SET @json2 = '{"k1":{"k1":"v1","k2":"v2"},"k2":{"1":"2"}}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"k2": {"1": "2"}, "k1": {"k1": "v1", "k2": "v2"}} +SET @json1 = '{"k1":1,"k2":{"kk1":"vv1"},"k3":3}'; +SET @json2 = '{"k3":3,"k2":{"kk1":"vv1"},"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"k2": {"kk1": "vv1"}, "k3": 3} +SET @json1 = '{"k1":1,"k2":[1,2,3],"k3":3}'; +SET @json2 = '{"k3":3,"k2":[1,2,3],"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"k2": [1, 2, 3], "k3": 3} +SET @json1 = '{"k1":1,"k2":{"kk2":"vv2","kk1":"vv1"},"k3":3}'; +SET @json2 = '{"k3":3,"k2":{"kk1":"vv1","kk2":"vv2"},"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"k2": {"kk2": "vv2", "kk1": "vv1"}, "k3": 3} +SET @json1 = '{"k1":1,"k2":{"kk2":"vv2","kk1":{"k1":"v1"}},"k3":3}'; +SET @json2 = '{"k3":3,"k2":{"kk1":"vv1","kk2":{"k1":"v1"}},"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); +JSON_INTERSECT(@json1, @json2) +{"k3": 3} +# +# End of 10.11 Test +# diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 65fdc859add..c040d64e863 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -1589,3 +1589,212 @@ SELECT JSON_OVERLAPS(@json1, @json2); --echo # --echo # End of 10.9 Test --echo # + +--echo # +--echo # Beginning of 10.11 tests +--echo # +--echo # MDEV-26182: Implement JSON_INTERSECT() +--echo # + +--echo # scalar(number) intersect scalar(number) +SET @json1 = '1'; +SET @json2 = '1'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '1'; +SET @json2 = '2'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '1.0'; +SET @json2 = '1.00'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # scalar intersect scalar array +SET @json1 = '3'; +SET @json2 = '[1,2,3,4,5]'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '6'; +SET @json2 = '[1,2,3,4,5]'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # scalar(string) intersect scalar(string) +SET @json1 = '"a"'; +SET @json2 = '"a"'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '"a"'; +SET @json2 = '"b"'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # scalar(string) intersect scalar(string) array +SET @json1 = '"z"'; +SET @json2 = '["a","b","cd","ef","f"]'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # sclar intersect object +SET @json1 = '"z"'; +SET @json2 = '{"key1":"value1"}'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '3'; +SET @json2 = '{"key1":"value1"}'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # object intersect object +SET @json1 = '{"key1":"value1","key2":"value2"}'; +SET @json2 = '{"key2":"value2","key1":"value1"}'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '{"key1":"value1"}'; +SET @json2 = '{"key2":"value2"}'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # object intersect object array +SET @json1 = '{"key1":"value1"}'; +SET @json2 = '[{"key1":"value1"},{"key2":"value2"}]'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # object inersect scalar +SET @json1 = '{"key1":"value1"}'; +SET @json2 = '1'; +SELECT JSON_INTERSECT(@json1,@json2); + +--echo # nested object intersect nested object +SET @json1 = '{"key1":{"kkey1":"vvalue1"}}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"}}'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1= '{"key1":{"key1":{"key1":"value1"}}}'; +SET @json2= '{"key1":{"key1":{"key1":"value1"}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"key1":{"kkey1":"vvalue1","kkey2":"vvalue2"}}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"}}'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '{"key1":{"kkey1":"vvalue1","kkey2":"vvalue2"},"key2":{"kkey2":"vvalue2","kkey3":"vvalue3"},"key3":{"kkey3":{"kkkey3":"vvvalue3"}},"key5":"value3"}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"},"key2":{"kkey2":"vvalue2"},"key3":{"kkey3":{"kkkey3":"vvvalue3"}},"key4":"value3"}'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1 = '{"key1":[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]}'; +SET @json2 = '{"key1":{"kkey1":"vvalue1"}}'; +SELECT JSON_INTERSECT(@json1,@json2); +SET @json1= '{"key1":"val1", "key2":"val2", "key3":{"key7":"val7"}}'; +SET @json2= '{"key4":"val4", "key2":"val2", "key5":"val5", "key3":"val3", "key6":"val6"}'; +SELECT JSON_INTERSECT(@json1, @json2); + + + +--echo # object have duplicate keys +SET @json1= '{"key1":"val1", "key2":"val2", "key3":"val3"}'; +SET @json2= '{"key3":"val3", "key2":"val2", "key4":"val4", "key3":"val3", "key5":"val5"}'; +--error ER_JSON_NON_UNIQUE_KEY_FOUND +SELECT JSON_INTERSECT(@json1, @json2); +--error ER_JSON_NON_UNIQUE_KEY_FOUND +SELECT JSON_INTERSECT(@json2, @json1); +SET @json1 = '{"k1":"v1","k2":"v2","k3":{"kk1":"v1","kk1":"v2"}}'; +SET @json2 = '{"k1":"v1","k2":"v2","k3":{"kk1":"v1","kk1":"v2"}}'; +--error ER_JSON_NON_UNIQUE_KEY_FOUND +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # syntax error +SET @json1= '{"key1":"value1"'; +SET @json2= '{"key1":"val3", "key2":"val2", "key4":"val4", "key3":"val3", "key5":"val5"}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1= '[1,2,3,4'; +SET @json2= '[1,2,3,4]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1= '[{"key1":"value1"}]'; +SET @json2= '["key1":"value1"]'; +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # array intersect array +SET @json1= '[1,2,3]'; +SET @json2= '[1,2,3]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1= '[1,2,3]'; +SET @json2= '[1,2,4]'; +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # object(with array value)intersect object +SET @json1= '{"key1":[1,2,3]}'; +SET @json2= '{"key1":[1,2,3]}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1= '{"key1":[1,2,3]}'; +SET @json2= '{"key1":[1,2,4]}'; +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # object array intersect object array +SET @json1 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SET @json2 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '[{"kkey1":"vvalue1"},{"kkey3":"vvalue2"}]'; +SET @json2 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # object array inersect object +SET @json1 = '[{"kkey1":"vvalue1"},{"kkey2":"vvalue2"}]'; +SET @json2 = '{"kkey2":"vvalue2"}'; +SELECT JSON_INTERSECT(@json1, @json2); + + +--echo # deeply nested object intersect object +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SET @json2 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SET @json2 = '{"key1":{"kkey1":{"kkkey2":{"kkkkey1":"value1"}}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}}}'; +SET @json2 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1_not"}}}}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}},"key2":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}},"key3":[{"kkey3":"vvalue3"},4,"666"]}'; +SET @json2 = '{"key2":{"kkey1":{"kkkey1":{"kkkkey1":"value1"}}} ,"key1":{"kkey1":{"kkkey1":{"kkkkey1":"value1_not"}}}, "key3":[{"kkey3":"vvalue3"},4,"666"]}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"k1":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv5"}},"k2":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv4"}},"k3":[{"kk1":{"kk1":"vv1","kk2":"vv2"}},{"kk1":"vv1"}],"k4":[{"kk1":"vv1"},{"kk1":{"kk1":"vv1","kk2":"vv2"}}]}'; +SET @json2 = '{"k1":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv5"}},"k2":{"kk1":"vv1","kk2":"vv2","kk3":{"kkk4":"vvv4","kkk5":"vvv5"}},"k3":[{"kk1":{"kk1":"vv1","kk2":"vv2"}},{"kk1":"vv1"}],"k4":[{"kk1":{"kk1":"vv1","kk2":"vv2"}},{"kk1":"vv1"}]}'; +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # nested array intersect array +SET @json1= '[1,2,3]'; +SET @json2= '[1.0,2.0,3.0]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1= '[6,6,6]'; +SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1= '[[1,2,3],[4,5,6],[1,3,2]]'; +SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]'; +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # array intersect array(with the same KV pair.) +SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2"}]'; +SET @json2 = '[{"kkey1":"vvalue1"},{"k2":"v2","k1":"v1"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '[{"k1":"v1","k2":[1,2,3]},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":[1,2,3]},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '[{"k1":"v1","k2":[1,2,3]},{"k2":"v2","k1":"v1"}]'; +SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":[3,2,1]},{"k1":"v1","k2":"v2"}]'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"k1":"v1","k2":"v2","k3":{"kk1":"v1","kk3":"v2"}}'; +SET @json2 = '{"k1":"v1","k2":"v2","k3":{"k1":"v1","k2":"v2"}}'; +SELECT JSON_INTERSECT(@json1, @json2); + +--echo # multi type nesting +SET @json1 = '{"k2":{"1":"2"},"k1":{"k1":"v1","k2":"v2"}}'; +SET @json2 = '{"k1":{"k1":"v1","k2":"v2"},"k2":{"1":"2"}}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"k1":1,"k2":{"kk1":"vv1"},"k3":3}'; +SET @json2 = '{"k3":3,"k2":{"kk1":"vv1"},"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"k1":1,"k2":[1,2,3],"k3":3}'; +SET @json2 = '{"k3":3,"k2":[1,2,3],"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"k1":1,"k2":{"kk2":"vv2","kk1":"vv1"},"k3":3}'; +SET @json2 = '{"k3":3,"k2":{"kk1":"vv1","kk2":"vv2"},"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); +SET @json1 = '{"k1":1,"k2":{"kk2":"vv2","kk1":{"k1":"v1"}},"k3":3}'; +SET @json2 = '{"k3":3,"k2":{"kk1":"vv1","kk2":{"k1":"v1"}},"k4":4}'; +SELECT JSON_INTERSECT(@json1, @json2); + + +--echo # +--echo # End of 10.11 Test +--echo # diff --git a/mysql-test/main/json_debug_nonembedded.result b/mysql-test/main/json_debug_nonembedded.result index e759cafa38b..e80885d325f 100644 --- a/mysql-test/main/json_debug_nonembedded.result +++ b/mysql-test/main/json_debug_nonembedded.result @@ -7,6 +7,8 @@ SET @json1= '{"key1":"val1"}'; SET @json2= '{"key1":"val1"}'; SELECT JSON_OVERLAPS(@json1, @json2); ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Consider increasing the thread_stack system variable. +SELECT JSON_INTERSECT(@json1, @json2); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Consider increasing the thread_stack system variable. SET @@debug_dbug= @saved_dbug; # # End of 10.9 test diff --git a/mysql-test/main/json_debug_nonembedded.test b/mysql-test/main/json_debug_nonembedded.test index 5813b46e556..8671a5a2568 100644 --- a/mysql-test/main/json_debug_nonembedded.test +++ b/mysql-test/main/json_debug_nonembedded.test @@ -15,6 +15,10 @@ SET @json2= '{"key1":"val1"}'; --error ER_STACK_OVERRUN_NEED_MORE SELECT JSON_OVERLAPS(@json1, @json2); +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_INTERSECT(@json1, @json2); + SET @@debug_dbug= @saved_dbug; --echo # diff --git a/sql/item_create.cc b/sql/item_create.cc index 6deb2e9f400..f56fdc1e235 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -1339,6 +1339,17 @@ protected: virtual ~Create_func_json_overlaps() {} }; +class Create_func_json_intersect : public Create_func_arg2 +{ +public: + virtual Item *create_2_arg(THD *thd, Item *arg1, Item *arg2); + + static Create_func_json_intersect s_singleton; + +protected: + Create_func_json_intersect() {} + virtual ~Create_func_json_intersect() {} +}; class Create_func_last_day : public Create_func_arg1 { @@ -4158,6 +4169,17 @@ Create_func_json_length::create_native(THD *thd, const LEX_CSTRING *name, return func; } +Create_func_json_intersect Create_func_json_intersect::s_singleton; +Item* +Create_func_json_intersect::create_2_arg(THD *thd, Item *arg1, Item *arg2) +{ + if (unlikely( ( !arg1 || !arg2 ) )) // json, json + { + my_error(ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT, MYF(0)); + } + status_var_increment(thd->status_var.feature_json); + return new (thd->mem_root) Item_func_json_intersect(thd, arg1, arg2); +} Create_func_json_merge Create_func_json_merge::s_singleton; @@ -5766,6 +5788,7 @@ Native_func_registry func_array[] = { { STRING_WITH_LEN("JSON_EXISTS") }, BUILDER(Create_func_json_exists)}, { { STRING_WITH_LEN("JSON_EXTRACT") }, BUILDER(Create_func_json_extract)}, { { STRING_WITH_LEN("JSON_INSERT") }, BUILDER(Create_func_json_insert)}, + { { STRING_WITH_LEN("JSON_INTERSECT") }, BUILDER(Create_func_json_intersect)}, { { STRING_WITH_LEN("JSON_KEYS") }, BUILDER(Create_func_json_keys)}, { { STRING_WITH_LEN("JSON_LENGTH") }, BUILDER(Create_func_json_length)}, { { STRING_WITH_LEN("JSON_LOOSE") }, BUILDER(Create_func_json_loose)}, diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc index aaee8a99294..415cc5f0718 100644 --- a/sql/item_jsonfunc.cc +++ b/sql/item_jsonfunc.cc @@ -4709,3 +4709,829 @@ bool Item_func_json_overlaps::fix_length_and_dec(THD *thd) return Item_bool_func::fix_length_and_dec(thd); } + +bool json_intersect_arr_and_obj(String *str, json_engine_t *js, + json_engine_t *value) +{ + json_engine_t loc_val= *value; + + while (json_scan_next(js) == 0 && js->state == JST_VALUE) + { + if (json_read_value(js)) + return TRUE; + if (js->value_type == JSON_VALUE_OBJECT) + { + if (!json_find_intersect_with_object(str, js, value, TRUE)) + return FALSE; + *value= loc_val; + } + } + return TRUE; +} + +/* + Get the hash table from JSON_VALUE_OBJECT.Insert each key value-pair + in the object into the hash table. + RETURN + FALSE - The function was successfully completed without errors. + TRUE - An error occurred while running. +*/ +bool get_object_hash_from_json(json_engine_t *value, HASH &property_hash) +{ + const uchar *key_start= NULL, *key_end= NULL; + const uchar *value_start= NULL; + size_t value_len= 0; + LEX_CSTRING_KEYVALUE *new_entry= NULL; + uchar *search_result= NULL; + if (my_hash_init(PSI_INSTRUMENT_ME, &property_hash, value->s.cs, 0, 0, 0, + LEX_CSTRING_KEYVALUE::get_hash_key, + LEX_CSTRING_KEYVALUE::hash_free, HASH_UNIQUE)) + goto error; + + while (json_scan_next(value) == 0 && value->state == JST_KEY) + { + key_start= value->s.c_str; + do + { + key_end= value->s.c_str; + } while (json_read_keyname_chr(value) == 0); + + if (unlikely(value->s.error)) + goto error; + + if (get_value_from_json(value, value_start, value_len)) + goto error; + + if (create_kv_pair_and_search_in_hash(new_entry, property_hash, + search_result, value_start, value_len, + key_start, key_end - key_start)) + goto error; + + if (search_result) + goto error; + + if (my_hash_insert(&property_hash, (const uchar *) new_entry)) + goto error; + } + return FALSE; +error: + return TRUE; +} + +/* + Get the hash table from JSON_VALUE_ARRAY.The values under each + index in the array are normalized and inserted into the hash table. + RETURN + FALSE - The function was successfully completed without errors. + TRUE - An error occurred while running. +*/ +bool get_array_hash_from_json(json_engine_t *value, HASH &property_hash) +{ + const uchar *value_start= NULL; + size_t value_len= 0; + LEX_CSTRING_KEYVALUE *new_entry= NULL; + uchar *search_result= NULL; + DYNAMIC_STRING norm_js; + if (my_hash_init(PSI_INSTRUMENT_ME, &property_hash, value->s.cs, 0, 0, 0, + LEX_CSTRING_KEYVALUE::get_hash_key, + LEX_CSTRING_KEYVALUE::hash_free, HASH_UNIQUE)) + goto error; + + while (json_scan_next(value) == 0 && value->state == JST_VALUE) + { + if (get_value_from_json(value, value_start, value_len)) + goto error; + + if (init_dynamic_string(&norm_js, NULL, 0, 0)) + goto error; + + if (json_normalize(&norm_js, (const char*) value_start, + value_len, value->s.cs)) + { + dynstr_free(&norm_js); + goto error; + } + + if (create_kv_pair_and_search_in_hash(new_entry, property_hash, + search_result, + (const uchar *) norm_js.str, + norm_js.length, NULL, 0)) + goto error; + + dynstr_free(&norm_js); + + /* Maintains the number of occurrences of each value. */ + if (!search_result) + { + new_entry->count= 1; + if (my_hash_insert(&property_hash, (const uchar *) new_entry)) + { + my_free(new_entry); + goto error; + } + } + else + { + ( (LEX_CSTRING_KEYVALUE*) search_result)->count+= 1; + my_free(new_entry); + if (my_hash_update(&property_hash, (uchar*) search_result, + (uchar*) (( (LEX_CSTRING_KEYVALUE*) search_result)->key.str), + ( (LEX_CSTRING_KEYVALUE*) search_result)->key.length)) + goto error; + } + } + return FALSE; +error: + my_free(&property_hash); + return TRUE; +} + +/* + Get the hash table from json_engine_t. + RETURN + FALSE - The function was successfully completed without errors. + TRUE - An error occurred while running. +*/ +bool get_hash_from_json(json_engine_t *value, HASH &property_hash) +{ + if (value->value_type == JSON_VALUE_OBJECT) + return get_object_hash_from_json(value, property_hash); + else if (value->value_type == JSON_VALUE_ARRAY) + return get_array_hash_from_json(value, property_hash); + return TRUE; +} + +/* + Get the starting pointer and length of the value of the current layer. + RETURN + FALSE - The function was successfully completed without errors. + TRUE - An error occurred while running. +*/ +bool get_value_from_json(json_engine_t *js, const uchar *&value_start, + size_t &value_len) +{ + value_start= js->s.c_str; + if (json_read_value(js)) + return TRUE; + if (json_value_scalar(js)) + { + value_start= js->value; + value_len= js->value_len; + if (js->value_type == JSON_VALUE_STRING) + { + value_start-= 1; + value_len+= 2; + } + } + else + { + if (json_skip_level(js)) + return TRUE; + value_len= js->s.c_str - value_start; + } + return FALSE; +} + +/* + Allocate space for entry and search in the hash table. + Parameter + new_entry[output]: Used to get the address of the entry. + property_hash: Searched HASH. + search_result[output]: Search result. + value_start: Start pointer of value to be inserted. + value_len: The length of the value to be inserted. + key_start: Start pointer of key to be inserted. + If it is not inserted in the form of key-value pair, + it needs to be set to NULL. + key_len: The length of the key to be inserted. + If it is not inserted in the form of key-value pair, + it needs to be set to 0. + RETURN + FALSE - The function was successfully completed without errors. + TRUE - An error occurred while running. +*/ +bool create_kv_pair_and_search_in_hash(LEX_CSTRING_KEYVALUE *&new_entry, + HASH &property_hash, uchar *&search_result, + const uchar *value_start, size_t value_len, + const uchar *key_start, size_t key_len) +{ + + if (!key_len) + { + char *new_entry_key_buf= NULL; + if (!my_multi_malloc(PSI_INSTRUMENT_ME, MYF(0), + &new_entry, sizeof(LEX_CSTRING_KEYVALUE), + &new_entry_key_buf, value_len, + NullS)) + return TRUE; + memcpy(new_entry_key_buf, value_start, value_len); + new_entry->key.str= new_entry_key_buf; + new_entry->key.length= value_len; + } + else + { + char *new_entry_key_buf= NULL; + char *new_entry_value_buf= NULL; + if (!my_multi_malloc(PSI_INSTRUMENT_ME, MYF(0), + &new_entry, sizeof(LEX_CSTRING_KEYVALUE), + &new_entry_key_buf, key_len, + &new_entry_value_buf, value_len, + NullS)) + return TRUE; + memcpy(new_entry_key_buf, key_start, key_len); + memcpy(new_entry_value_buf, value_start, value_len); + new_entry->key.str= new_entry_key_buf; + new_entry->key.length= key_len; + new_entry->value.str= new_entry_value_buf; + new_entry->value.length= value_len; + } + /* + search_result is a reference.It is used to + store the results of the search + */ + search_result= my_hash_search(&property_hash, + (const uchar *) new_entry->key.str, + new_entry->key.length); + return FALSE; +} + + +bool json_find_intersect_with_object(String *str, json_engine_t *js, + json_engine_t *value, bool compare_whole) +{ + if (value->value_type == JSON_VALUE_OBJECT) + { + /* Two objects are required to be identical. */ + if (compare_whole) + { + json_engine_t tmp_js= *js; + const uchar *object_js_start= js->value_begin; + if (json_skip_level(js)) + return TRUE; + const uchar *object_js_end= js->s.c_str; + *js= tmp_js; + if (compare_nested_object(js, value)) + { + if (str->append( (const char*) object_js_start, + object_js_end - object_js_start)) + return TRUE; + return FALSE; + } + return TRUE; + } + + const uchar *key_start= NULL, *key_end= NULL; + bool have_item= FALSE; + HASH property_hash; + const uchar *value_start= NULL; + size_t value_len= 0; + LEX_CSTRING_KEYVALUE *new_entry= NULL; + uchar* search_result= NULL; + DYNAMIC_STRING norm_js, norm_value; + if (get_hash_from_json(value, property_hash)) + goto error; + /* + Scan js's key-value pair. If there is the same key in the hash table, + take out the value of the key in the hash table. After normalizing both + values. Compare them and add them to the results if they are the same. + */ + while (json_scan_next(js) == 0 && js->state == JST_KEY) + { + key_start= js->s.c_str; + do + { + key_end= js->s.c_str; + } while (json_read_keyname_chr(js) == 0); + + if (unlikely(js->s.error)) + goto error; + + if (get_value_from_json(js, value_start, value_len)) + goto error; + + if (create_kv_pair_and_search_in_hash(new_entry, property_hash, + search_result, value_start, + value_len, key_start, + key_end - key_start)) + goto error; + + if (!search_result) + my_free(new_entry); + else + { + bool equal= FALSE; + if (js->value_type == JSON_VALUE_STRING) + equal= new_entry->value.length == + ( (LEX_CSTRING_KEYVALUE*) search_result)->value.length && + !strncmp(new_entry->value.str, + ( (LEX_CSTRING_KEYVALUE*) search_result)->value.str, + new_entry->value.length); + else + { + if (init_dynamic_string(&norm_js, NULL, 0, 0) || + init_dynamic_string(&norm_value, NULL, 0, 0)) + goto error_free_space; + + if (json_normalize(&norm_js, (const char*) new_entry->value.str, + new_entry->value.length, value->s.cs) || + json_normalize(&norm_value, + ( (LEX_CSTRING_KEYVALUE*) search_result)->value.str, + ( (LEX_CSTRING_KEYVALUE*) search_result)->value.length, + value->s.cs)) + goto error_free_space; + + equal= norm_js.length == norm_value.length && + !strncmp(norm_js.str, norm_value.str, norm_js.length); + dynstr_free(&norm_js); + dynstr_free(&norm_value); + } + + if (equal) + { + if (have_item) + { + if (str->append(',')) + goto string_append_error; + } + else + { + if (str->append('{')) + goto string_append_error; + have_item= TRUE; + } + if (str->append('"') || + str->append(new_entry->key.str, new_entry->key.length) || + str->append("\":",2) || + str->append(new_entry->value.str, new_entry->value.length)) + goto string_append_error; + } + my_free(new_entry); + } + } + my_hash_free(&property_hash); + if (have_item && str->append('}')) + return TRUE; + return !have_item; + error_free_space: + dynstr_free(&norm_js); + dynstr_free(&norm_value); + string_append_error: + my_free(new_entry); + error: + my_hash_free(&property_hash); + return TRUE; + } + else if (value->value_type == JSON_VALUE_ARRAY) + { + if (compare_whole) + { + json_skip_current_level(js, value); + return TRUE; + } + return json_intersect_arr_and_obj(str, value, js); + } + return TRUE; +} + +/* + If the outermost layer of JSON is an array, + the intersection of arrays is independent of order. + All common values are obtained. + + RETURN + FALSE - if two array documents have intersection + TRUE - If two array documents do not have intersection +*/ +bool json_intersect_between_arrays(String *str, json_engine_t *js, + json_engine_t *value) +{ + bool have_item= FALSE; + HASH property_hash; + const uchar *value_start= NULL; + size_t value_len= 0; + uchar* search_result= NULL; + LEX_CSTRING_KEYVALUE *new_entry= NULL; + DYNAMIC_STRING norm_val; + /* + Put the value in js into the hash table. Scan value uses + the hash table to get the intersection of two arrays. + */ + if (get_hash_from_json(js, property_hash)) + goto error; + while (json_scan_next(value) == 0 && value->state == JST_VALUE) + { + if (get_value_from_json(value, value_start, value_len)) + goto error; + if (init_dynamic_string(&norm_val, NULL, 0, 0)) + goto error; + if (value->value_type == JSON_VALUE_STRING) + { + if (dynstr_append_mem(&norm_val, (char*) value_start, value_len)) + { + dynstr_free(&norm_val); + goto error; + } + } + else + { + if (json_normalize(&norm_val, (const char*) value_start, + value_len, value->s.cs)) + { + dynstr_free(&norm_val); + goto error; + } + } + + if (create_kv_pair_and_search_in_hash(new_entry, property_hash, + search_result, (uchar*) norm_val.str, + norm_val.length, NULL, 0)) + goto error; + + dynstr_free(&norm_val); + + /* + If the same value is found in the hash table, add + that value to str. At the same time, update the number + of times the value appears in the hash table. + */ + if (!search_result) + my_free(new_entry); + else + { + if (have_item) + { + if (str->append(',')) + goto string_append_error; + } + else + { + if (str->append('[')) + goto string_append_error; + have_item= TRUE; + } + if (str->append( (const char*) value_start, value_len)) + goto string_append_error; + new_entry->count= ( (LEX_CSTRING_KEYVALUE*) search_result)->count - 1; + if (new_entry->count == 0) + { + my_free(new_entry); + if (my_hash_delete(&property_hash, search_result)) + goto error; + } + else + { + ( (LEX_CSTRING_KEYVALUE*) search_result)->count-= 1; + my_free(new_entry); + if (my_hash_update(&property_hash, (uchar*) search_result, + (uchar*) (( (LEX_CSTRING_KEYVALUE*) search_result)->key.str), + ( (LEX_CSTRING_KEYVALUE*) search_result)->key.length)) + goto error; + } + } + } + + if (have_item && str->append(']')) + return TRUE; + my_hash_free(&property_hash); + return !have_item; +string_append_error: + my_free(new_entry); +error: + my_hash_free(&property_hash); + return TRUE; +} + +bool json_find_intersect_with_array(String *str, json_engine_t *js, + json_engine_t *value, bool compare_whole) +{ + if (value->value_type == JSON_VALUE_ARRAY) + { + if (!compare_whole) + { + return json_intersect_between_arrays(str, js, value); + } + + json_engine_t tmp_js= *js; + const uchar *value_start= tmp_js.value; + if (json_skip_level(&tmp_js)) + return TRUE; + const uchar *value_end= tmp_js.s.c_str; + + if (json_compare_arrays_in_order(js, value)) + { + if (str->append( (const char*) value_start, value_end - value_start)) + return TRUE; + return FALSE; + } + return TRUE; + } + else if (value->value_type == JSON_VALUE_OBJECT) + { + if (compare_whole) + { + json_skip_current_level(js, value); + return TRUE; + } + return json_intersect_arr_and_obj(str, js, value); + } + else + return check_intersect(str, value, js, compare_whole); +} + +/* + Find two json's intersection. + + SYNOPSIS + check_intersect() + js - json document1(json1) + value - json document2(json2) + str - result string + compare_whole - If compare_whole is true(When the array or object or scalar + is not at the outermost layer, compare_whole is true.), + it means that the two jsons being compared, whether they + are scalars, arrays or objects, must be exactly equal to + have an intersection. Different types indicate that there is + no intersection between jsons. If two scalars are compared, + there is an intersection between them, which means that the + two scalars are exactly the same. If two arrays are compared, + there is an intersection between them, which means that the + values at all indices of the two arrays are equal. If two + objects are compared, there is an intersection between them, + which means that all KV pairs of the two objects are equal. + if compare_whole is false. When a value is a subset of + another value, there is an intersection. For example, taking + the intersection of two objects is taking their common KV + pairs. The intersection of array and array is to take their + common elements. An object and a scalar take an intersection + with an array. As long as the object or scalar exists in the + array, the intersection is the object or the scalar. + + IMPLEMENTATION + When comparing between json1 and json2, there can be following cases(When + there is an intersection, the intersection will be added to str): + 1 When at least one of the two json documents is of scalar type: + 1.1 If json1 and json2 both are scalar and they have same type + and value, indicate that there is an intersection between them. The + intersection is this scalar. Else, there is no intersection. + 1.2 If json1 is scalar but other is array (or vice versa): + 1.2.1 if compare_whole = true: + Indicate that there is no intersection between them. + 1.2.2 if compare_whole = false: + If the array has at least one element of same type and value as scalar, + indicate that there is an intersection between them. The intersection + is this scalar. Else there is no intersection between them. + 1.3 If one is scalar and other is object, indicate that there is no + intersection between them, because they can not be compared. + + 2 When json1 and json2 are non-scalar type: + 2.1 If both jsons are arrays: + Use hash to store all the normalized values in the json1 array + and count the number of the same values. + Scan the values in json2 and normalize them, find out whether there + are equal values in the hash. Every time the same value is found in + the hash table, the count in the hash table need to be updated. + 2.1.1 compare_whole = true: + If the values of all indices in the two arrays are exactly equal, + there is an intersection between them, and the intersection is any + one of these two arrays. Else there is no intersection between them. + 2.1.2 compare_whole = false: + If there are equal values at any index of two arrays, then there + is an intersection between them. The intersection is the longest + array that can be obtained by matching equal values between two + arrays. Else there is no intersection between them. + 2.2 If both jsons are objects: + Use hash to store all the key-value pairs in the json1 object. + Scan the key-value pair in json2. Find out if there is the same key + in the hash. If yes, take out the value in the corresponding key and + normalize it. Then compare it with the value(normalization is also + required) in the json2's key-value pair. + 2.2.1 compare_whole = true: + If all KV pairs of two objects are equal, it means that there is an + intersection between them, and the intersection is any one of these + two objects. Else, there is no intersection between them. + 2.2.2 compare_whole = false: + If there are equal KV pairs for two objects, it means that there is + an intersection between them, and the intersection is all the equal + KV pairs of these two objects. Else, there is no intersection + between them. + 2.3 if one of the jsons is an object and the other is an array: + 2.3.1 compare_whole = true: + Indicate that there is no intersection between them. + 2.3.2 compare_whole = false: + Iterate over the array, if an element of type object is found, + then compare it with the object. If the entire object matches + i.e all they key value pairs match, indicates that there is an + intersection between them, and the intersection is this object. + Else, there is no intersection between them. + + RETURN + FALSE - if two json documents have intersection. + TRUE - If two json documents do not have intersection. +*/ +bool check_intersect(String *str, json_engine_t *js, + json_engine_t *value, bool compare_whole) +{ + DBUG_EXECUTE_IF("json_check_min_stack_requirement", + { + long arbitrary_var; + long stack_used_up= (available_stack_size(current_thd->thread_stack, &arbitrary_var)); + ALLOCATE_MEM_ON_STACK(my_thread_stack_size-stack_used_up-STACK_MIN_SIZE); + }); + if (check_stack_overrun(current_thd, STACK_MIN_SIZE , NULL)) + return TRUE; + switch (js->value_type) + { + case JSON_VALUE_OBJECT: + return json_find_intersect_with_object(str, js, value, compare_whole); + case JSON_VALUE_ARRAY: + return json_find_intersect_with_array(str, js, value, compare_whole); + default: + if (!json_find_overlap_with_scalar(js, value)) + return TRUE; + if (js->value_type == JSON_VALUE_NUMBER) + { + if (str->append( (const char *) js->value,js->value_len)) + return TRUE; + } + else if (js->value_type == JSON_VALUE_STRING) + { + if (str->append('"') || + str->append( (const char *) js->value, js->value_len) || + str->append('"')) + return TRUE; + } + return FALSE; + } +} + +bool check_unique_key_in_object(json_engine_t *js) +{ + const uchar *key_start= NULL, *key_end= NULL; + HASH key_hash; + LEX_CSTRING_KEYVALUE *new_entry= NULL; + uchar* search_result= NULL; + + if (my_hash_init(PSI_INSTRUMENT_ME, &key_hash, js->s.cs,0, 0, 0, + LEX_CSTRING_KEYVALUE::get_hash_key, + LEX_CSTRING_KEYVALUE::hash_free, HASH_UNIQUE)) + goto error; + /* + Scan js and insert the key into the hash table + to determine whether there are duplicate keys. + */ + while (json_scan_next(js) == 0 && js->state == JST_KEY) + { + key_start= js->s.c_str; + do + { + key_end= js->s.c_str; + } while (json_read_keyname_chr(js) == 0); + + if (unlikely(js->s.error)) + goto error; + if (json_read_value(js)) + goto error; + if (check_unique_key_in_js(js)) + goto error; + + if (create_kv_pair_and_search_in_hash(new_entry, key_hash, search_result, + key_start, key_end - key_start, + NULL, 0)) + goto error; + + if (search_result) + { + my_free(new_entry); + goto error; + } + if (my_hash_insert(&key_hash, (const uchar *) new_entry)) + goto error; + } + + my_hash_free(&key_hash); + return FALSE; +error: + my_hash_free(&key_hash); + return TRUE; +} + +/* + Confirm whether there are duplicate keys in the same object. + Return + FALSE - No duplicate keys in the same object. + TRUE - Have duplicate keys in the same object. +*/ +bool check_unique_key_in_js(json_engine_t *js) +{ + DBUG_EXECUTE_IF("json_check_min_stack_requirement", + { + long arbitrary_var; + long stack_used_up= (available_stack_size(current_thd->thread_stack, &arbitrary_var)); + ALLOCATE_MEM_ON_STACK(my_thread_stack_size-stack_used_up-STACK_MIN_SIZE); + }); + if (check_stack_overrun(current_thd, STACK_MIN_SIZE , NULL)) + return TRUE; + if (js->value_type == JSON_VALUE_OBJECT) + { + return check_unique_key_in_object(js); + } + else if (js->value_type == JSON_VALUE_ARRAY) + { + while (json_scan_next(js) == 0 && js->state == JST_VALUE) + { + if (json_read_value(js)) + return TRUE; + if (check_unique_key_in_js(js)) + return TRUE; + } + } + return FALSE; +} + +String* Item_func_json_intersect::val_str(String *str) +{ + DBUG_ASSERT(fixed()); + json_engine_t je1, je2, tmp_jse; + String *js1= args[0]->val_json(&tmp_js1), *js2= NULL; + if (args[0]->null_value) + goto null_return; + + str->set_charset(js1->charset()); + str->length(0); + + js2= args[1]->val_json(&tmp_js2); + if (args[1]->null_value) + goto null_return; + + json_scan_start(&je1, js1->charset(), (const uchar *) js1->ptr(), + (const uchar *) js1->ptr() + js1->length()); + json_scan_start(&je2, js2->charset(), (const uchar *) js2->ptr(), + (const uchar *) js2->ptr() + js2->length()); + /* + Duplicate keys are not allowed in the same object in json, otherwise it will + lead to ambiguity. We will use the check_unique_key_in_js to check the json + and return an error if the same object has duplicate keys. + */ + tmp_jse= je1; + json_read_value(&je1); + if (unlikely(je1.s.error)) + goto error_return; + if (check_unique_key_in_js(&je1)) + { + if (unlikely(je1.s.error)) + goto error_return; + my_error(ER_JSON_NON_UNIQUE_KEY_FOUND, MYF(0)); + goto error_return; + } + if (unlikely(je1.s.error)) + goto error_return; + je1= tmp_jse; + + tmp_jse= je2; + json_read_value(&je2); + if (unlikely(je2.s.error)) + goto error_return; + if (check_unique_key_in_js(&je2)) + { + if (unlikely(je2.s.error)) + goto error_return; + my_error(ER_JSON_NON_UNIQUE_KEY_FOUND, MYF(0)); + goto error_return; + } + if (unlikely(je2.s.error)) + goto error_return; + je2= tmp_jse; + + if (json_read_value(&je1) || json_read_value(&je2)) + goto error_return; + + if (check_intersect(str, &je1, &je2, FALSE)) + goto error_return; + + json_scan_start(&je1, str->charset(), (const uchar *) str->ptr(), + (const uchar *) str->ptr() + str->length()); + str= &tmp_js1; + 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, 1); +null_return: + null_value= 1; + return NULL; +} + +bool Item_func_json_intersect::fix_length_and_dec(THD *thd) +{ + max_length= (args[0]->max_length < args[1]->max_length) ? + args[0]->max_length : args[1]->max_length; + + set_maybe_null(); + return FALSE; +} diff --git a/sql/item_jsonfunc.h b/sql/item_jsonfunc.h index 82efddb5a5a..1ba9a70648b 100644 --- a/sql/item_jsonfunc.h +++ b/sql/item_jsonfunc.h @@ -59,7 +59,48 @@ int json_find_overlap_with_array(json_engine_t *js, json_engine_t *value, bool compare_whole); +struct LEX_CSTRING_KEYVALUE +{ + LEX_CSTRING key; + LEX_CSTRING value; + int count= 0; + + static uchar * + get_hash_key(const uchar *data, size_t *len_ret, + my_bool __attribute__((unused))) + { + LEX_CSTRING_KEYVALUE *e= (LEX_CSTRING_KEYVALUE *) data; + *len_ret= e->key.length; + return (uchar *) e->key.str; + } + + static void hash_free(void *ptr) + { + my_free(ptr); + } +}; +bool check_intersect(String *str, json_engine_t *js, + json_engine_t *value, bool compare_whole); +bool json_find_intersect_with_object(String *str, json_engine_t *js, + json_engine_t *value, bool compare_whole); +bool check_unique_key_in_object(json_engine_t *js); +bool json_find_intersect_with_array(String *str, json_engine_t *js, + json_engine_t *value, bool compare_whole); +bool check_unique_key_in_js(json_engine_t *js); +bool get_hash_from_json(json_engine_t *value, HASH &property_hash); +bool get_object_hash_from_json(json_engine_t *value, HASH &property_hash); +bool get_array_hash_from_json(json_engine_t *value, HASH &property_hash); +bool create_kv_pair_and_search_in_hash(LEX_CSTRING_KEYVALUE *&new_entry, + HASH &property_hash, uchar *&search_result, + const uchar *value_start, size_t value_len, + const uchar *key_start, size_t key_len); +bool get_value_from_json(json_engine_t *js, const uchar *&value_start, + size_t &value_len); +bool json_intersect_arr_and_obj(String *str, json_engine_t *js, + json_engine_t *value); +bool json_intersect_between_arrays(String *str, json_engine_t *js, + json_engine_t *value); class Json_engine_scan: public json_engine_t { @@ -791,4 +832,24 @@ public: { return get_item_copy<Item_func_json_overlaps>(thd, this); } }; +class Item_func_json_intersect: public Item_str_func +{ +protected: + String tmp_js1, tmp_js2; +public: + Item_func_json_intersect(THD *thd, Item *a, Item *b): + Item_str_func(thd, a, b) {} + String *val_str(String *) override; + bool fix_length_and_dec(THD *thd) override; + LEX_CSTRING func_name_cstring() const override + { + static LEX_CSTRING name= {STRING_WITH_LEN("json_intersect") }; + return name; + } + Item *get_copy(THD *thd) override + { return get_item_copy<Item_func_json_intersect>(thd, this); } +}; + + + #endif /* ITEM_JSONFUNC_INCLUDED */ diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index b5731eee4b4..66e71f0709e 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -10079,3 +10079,7 @@ ER_CM_OPTION_MISSING_REQUIREMENT eng "CHANGE MASTER TO option '%s=%s' is missing requirement %s" ER_SLAVE_STATEMENT_TIMEOUT 70100 eng "Slave log event execution was interrupted (slave_max_statement_time exceeded)" +ER_JSON_NON_UNIQUE_KEY_FOUND + chi "同一对象中有重复的键" + eng "Duplicate keys in the same object" + spa "Duplicar claves en el mismo objeto" |