MDEV-27677: Implement JSON_OVERLAPS()

1) When at least one of the two json documents is of scalar type:
     1.a) If value and json document both are scalar, then return true
          if they have same type and value.
     1.b) If json document is scalar but other is array (or vice versa),
          then return true if array has at least one element of same type
          and value as scalar.
     1.c) If one is scalar and other is object, then return false because
          it can't be compared.

  2) When both arguments are of non-scalar type and below conditons
      are satisfied then return true:
      2.a) When both arguments are arrays:
           Iterate over the value and json document. If there exists at
           least one element in other array of same type and value as
           that of element in value.
      2.b) If both arguments are objects:
           Iterate over value and json document and if there exists at least
           one key-value pair common between two objects.
      2.c) If either of json document or value is array and other is object:
           Iterate over the array, if an element of type object is found,
           then compare it with the object (which is the other arguemnt).
           If the entire object matches i.e all they key value pairs match.

1 files changed, 320 insertions, 0 deletions

diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result
index f9845357d51..54cabeaa2c3 100644
--- a/mysql-test/main/func_json.result
+++ b/mysql-test/main/func_json.result
@@ -1428,3 +1428,323 @@ DROP TABLE t;
 #
 # End of 10.5 tests
 #
+#
+# Beginning of 10.9 tests
+#
+# MDEV-27677: Implement JSON_OVERLAPS()
+#
+# Testing scalar json datatypes
+# Comparing scalar json datatypes with itself
+SELECT JSON_OVERLAPS('true', 'true');
+JSON_OVERLAPS('true', 'true')
+1
+SELECT JSON_OVERLAPS('false', 'false');
+JSON_OVERLAPS('false', 'false')
+1
+SELECT JSON_OVERLAPS('1', '1');
+JSON_OVERLAPS('1', '1')
+1
+SELECT JSON_OVERLAPS('"string1"', '"string1"');
+JSON_OVERLAPS('"string1"', '"string1"')
+1
+SELECT JSON_OVERLAPS('null', 'null');
+JSON_OVERLAPS('null', 'null')
+1
+# Comparing scalar json datatypes with other scalar datatype
+SELECT JSON_OVERLAPS('true', 'false');
+JSON_OVERLAPS('true', 'false')
+0
+SELECT JSON_OVERLAPS('1', '"1"');
+JSON_OVERLAPS('1', '"1"')
+0
+SELECT JSON_OVERLAPS('1', '0');
+JSON_OVERLAPS('1', '0')
+0
+SELECT JSON_OVERLAPS('null', '0');
+JSON_OVERLAPS('null', '0')
+0
+SELECT JSON_OVERLAPS('"string1"', '"string2"');
+JSON_OVERLAPS('"string1"', '"string2"')
+0
+SELECT JSON_OVERLAPS('true','["abc", 1, 2, true, false]');
+JSON_OVERLAPS('true','["abc", 1, 2, true, false]')
+1
+SELECT JSON_OVERLAPS('true','["abc", 1, 2, [true]]');
+JSON_OVERLAPS('true','["abc", 1, 2, [true]]')
+0
+SELECT JSON_OVERLAPS('true','{"A":true}');
+JSON_OVERLAPS('true','{"A":true}')
+0
+# Testing non-scalar json data types
+# Comparing object with object (non-nested)
+SELECT JSON_OVERLAPS('{"A":[1, 2, 3]}','{}');
+JSON_OVERLAPS('{"A":[1, 2, 3]}','{}')
+0
+SELECT JSON_OVERLAPS('{"A": 1}',
+'{"A": 1}');
+JSON_OVERLAPS('{"A": 1}',
+'{"A": 1}')
+1
+SELECT JSON_OVERLAPS('{"A": 1}',
+'{"B": 1}');
+JSON_OVERLAPS('{"A": 1}',
+'{"B": 1}')
+0
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                        "A": 2,
+                        "B": "string1"
+                      }');
+JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                        "A": 2,
+                        "B": "string1"
+                      }')
+1
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                       "A": 2,
+                       "B": "string2"
+                      }');
+JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                       "A": 2,
+                       "B": "string2"
+                      }')
+0
+# Comparing nested object with other nested object
+SELECT JSON_OVERLAPS('{
+                        "A": 1,
+                        "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":1}
+                      }');
+JSON_OVERLAPS('{
+                        "A": 1,
+                        "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":1}
+                      }')
+0
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":2}
+                      }');
+JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":2}
+                      }')
+1
+SELECT JSON_OVERLAPS('{
+                       "A": {
+                             "B": true
+                            }
+                      }',
+'{
+                       "A": {
+                             "B": true,
+                             "C": false
+                            }
+                      }');
+JSON_OVERLAPS('{
+                       "A": {
+                             "B": true
+                            }
+                      }',
+'{
+                       "A": {
+                             "B": true,
+                             "C": false
+
+0
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}',
+'{"C":3, "B":{"E":5, "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}',
+'{"C":3, "B":{"E":5, "D":4}}')
+1
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":5, "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":5, "D":4}}')
+0
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[5, 6, 7], "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[5, 6, 7], "D":4}}')
+1
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[7, 6 ,5], "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[7, 6 ,5], "D":4}}')
+0
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "F":{"E":[5, 6, 7], "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "F":{"E":[5, 6, 7], "D":4}}')
+0
+# Comparing array with array (non-nested)
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 1]');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 1]')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 5]');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 5]')
+0
+SELECT JSON_OVERLAPS('[1,2,3]','[]');
+JSON_OVERLAPS('[1,2,3]','[]')
+0
+# Comparing nested arrays
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, [1]]');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, [1]]')
+0
+SELECT JSON_OVERLAPS('[1, 2, [true, false], null]',
+'[[1], [true, false]]');
+JSON_OVERLAPS('[1, 2, [true, false], null]',
+'[[1], [true, false]]')
+1
+SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]');
+JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]')
+0
+# Comparing one non-scalar json datatypes with another non-scalar
+# json datatype
+# Comparing array with object
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'{"A": 1}')
+0
+SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]',
+'{"A": 1}')
+0
+SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]',
+'{"A": 1}')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 2}');
+JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 2}')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 3}');
+JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 3}')
+0
+# Comparing nested array with object
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 2}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 2}')
+0
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 3}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 3}')
+0
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1}')
+0
+# Comparing array with nested object
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
+'{"A": 1, "B": {"C": 12}}');
+JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
+'{"A": 1, "B": {"C": 12}}')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}')
+0
+# Comparing nested array with nested objects
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B":{"C": 12}}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B":{"C": 12}}')
+0
+SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}');
+JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}')
+0
+# Comparing object with array
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}]');
+JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}]')
+0
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 3}]');
+JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 3}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]');
+JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]');
+JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A":1, "B":[1, 2, {"C": 3, "D": 5}]}]');
+JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A":1, "B":[1, 2, {"C": 3, "D": 5}]}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2},{"A": 1, "B": [1, 2, {"C": 3, "D": 4}]}]');
+JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2},{"A": 1, "B": [1, 2, {"C": 3, "D": 4}]}]')
+0
+# Comparing object with nested array
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]');
+JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]')
+0
+# Checking errors and warnings
+SELECT JSON_OVERLAPS('[1,2,{"A":B}]', '{"A":B}', '{"C":"string1"}');
+ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS'
+SELECT JSON_OVERLAPS('[1,2,{"A":B}]');
+ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS'
+SELECT JSON_OVERLAPS('','');
+JSON_OVERLAPS('','')
+1
+Warnings:
+Warning	4037	Unexpected end of JSON text in argument 1 to function 'json_overlaps'
+#
+# End of 10.9 test
+#