MDEV-23143 Add JSON_EQUALS functionbb-10.7-mdev23143

This patch implements JSON_EQUALS SQL function.  The function takes
advantage of the json_normalize functionality and does the following:

norm_a = json_normalize(a)
norm_b = json_normalize(b)
return strcmp(norm_a, norm_b)

Co-authored-by: Vicențiu Ciorbaru <vicentiu@mariadb.org>

5 files changed, 268 insertions, 0 deletions

diff --git a/mysql-test/main/json_equals.result b/mysql-test/main/json_equals.result
new file mode 100644
index 00000000000..1fa13ec2cf8
--- /dev/null
+++ b/mysql-test/main/json_equals.result
@@ -0,0 +1,98 @@
+select json_equals("{}", "{}");
+json_equals("{}", "{}")
+1
+select json_equals("{}", "[]");
+json_equals("{}", "[]")
+0
+select json_equals("{}", NULL);
+json_equals("{}", NULL)
+NULL
+select json_equals("", "");
+json_equals("", "")
+NULL
+select json_equals("", 1);
+json_equals("", 1)
+NULL
+select json_equals(now(), now());
+json_equals(now(), now())
+NULL
+select json_equals('{"a":[1, 2, 3]}', '{"a":[1, 2, 3, 4]}');
+json_equals('{"a":[1, 2, 3]}', '{"a":[1, 2, 3, 4]}')
+0
+select json_equals('{"a":[1, 2, 3]}', '{"a":[1, 2, 3]}');
+json_equals('{"a":[1, 2, 3]}', '{"a":[1, 2, 3]}')
+1
+select json_equals('{"țanțoș":[1, 2, "ț", {"some uâ߀":"uâßr"}]}',
+'{"țanțoș":[1, 2, "ț", {"some uâ߀":"uâßr"}]}');
+json_equals('{"țanțoș":[1, 2, "ț", {"some uâ߀":"uâßr"}]}',
+'{"țanțoș":[1, 2, "ț", {"some uâ߀":"uâßr"}]}')
+1
+select json_equals('{"a" : [0.123456789123456789], "b" : [1, 2, 3]}',
+'{"b" : [1, 2, 3], "a" : [0.123456789123456789]}');
+json_equals('{"a" : [0.123456789123456789], "b" : [1, 2, 3]}',
+'{"b" : [1, 2, 3], "a" : [0.123456789123456789]}')
+1
+#
+# Test max json depth for json_equals.
+#
+with recursive rec_json (step, obj) as (
+select 1, cast('{"key":"value"}' as varchar(1000))
+union
+select r.step + 1, JSON_INSERT('{}', '$.obj', JSON_QUERY(r.obj, '$'))
+from rec_json r
+where r.step < 10
+)
+select step, obj, json_equals(obj, obj) from rec_json;
+step	obj	json_equals(obj, obj)
+1	{"key":"value"}	1
+2	{"obj": {"key": "value"}}	1
+3	{"obj": {"obj": {"key": "value"}}}	1
+4	{"obj": {"obj": {"obj": {"key": "value"}}}}	1
+5	{"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}	1
+6	{"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}	1
+7	{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}	1
+8	{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}	1
+9	{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}	1
+10	{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}	1
+#
+# 31 levels of nesting.
+#
+select json_equals('{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}',
+'{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}') as 31_levels;
+31_levels
+1
+#
+# 32 Levels of nesting. This should hit max json depth.
+#
+select json_equals('{"obj":{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}',
+'{"obj":{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}') as 32_levels;
+32_levels
+NULL
+#
+# test values from different charset
+# (UTF-8 two-bytes vs. latin1 single high-byte)
+#
+create table t1 (a varchar(200) character set latin1);
+create table t2 (a varchar(200) character set utf8);
+insert into t1 values (UNHEX('22CA22'));
+set names utf8;
+insert into t2 values (UNHEX('22C38A22'));
+select a from t1;
+a
+"Ê"
+select hex(a) from t1;
+hex(a)
+22CA22
+select a from t2;
+a
+"Ê"
+select hex(a) from t2;
+hex(a)
+22C38A22
+select t1.a, t2.a, t1.a = t2.a,
+json_valid(t1.a), json_valid(t2.a), json_equals(t1.a, t2.a)
+from t1, t2;
+a	a	t1.a = t2.a	json_valid(t1.a)	json_valid(t2.a)	json_equals(t1.a, t2.a)
+"Ê"	"Ê"	1	1	1	1
+drop table t1;
+drop table t2;
diff --git a/mysql-test/main/json_equals.test b/mysql-test/main/json_equals.test
new file mode 100644
index 00000000000..904d8a3f67d
--- /dev/null
+++ b/mysql-test/main/json_equals.test
@@ -0,0 +1,69 @@
+
+select json_equals("{}", "{}");
+
+select json_equals("{}", "[]");
+
+select json_equals("{}", NULL);
+
+select json_equals("", "");
+
+select json_equals("", 1);
+
+select json_equals(now(), now());
+
+select json_equals('{"a":[1, 2, 3]}', '{"a":[1, 2, 3, 4]}');
+
+select json_equals('{"a":[1, 2, 3]}', '{"a":[1, 2, 3]}');
+
+select json_equals('{"țanțoș":[1, 2, "ț", {"some uâ߀":"uâßr"}]}',
+                   '{"țanțoș":[1, 2, "ț", {"some uâ߀":"uâßr"}]}');
+
+select json_equals('{"a" : [0.123456789123456789], "b" : [1, 2, 3]}',
+                   '{"b" : [1, 2, 3], "a" : [0.123456789123456789]}');
+
+--echo #
+--echo # Test max json depth for json_equals.
+--echo #
+with recursive rec_json (step, obj) as (
+  select 1, cast('{"key":"value"}' as varchar(1000))
+  union
+  select r.step + 1, JSON_INSERT('{}', '$.obj', JSON_QUERY(r.obj, '$'))
+  from rec_json r
+  where r.step < 10
+)
+select step, obj, json_equals(obj, obj) from rec_json;
+
+--echo #
+--echo # 31 levels of nesting.
+--echo #
+select json_equals('{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}',
+'{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}') as 31_levels;
+
+--echo #
+--echo # 32 Levels of nesting. This should hit max json depth.
+--echo #
+select json_equals('{"obj":{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}',
+'{"obj":{"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"obj": {"key": "value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}') as 32_levels;
+
+
+--echo #
+--echo # test values from different charset
+--echo # (UTF-8 two-bytes vs. latin1 single high-byte)
+--echo #
+create table t1 (a varchar(200) character set latin1);
+create table t2 (a varchar(200) character set utf8);
+insert into t1 values (UNHEX('22CA22'));
+set names utf8;
+insert into t2 values (UNHEX('22C38A22'));
+
+select a from t1;
+select hex(a) from t1;
+select a from t2;
+select hex(a) from t2;
+
+select t1.a, t2.a, t1.a = t2.a,
+       json_valid(t1.a), json_valid(t2.a), json_equals(t1.a, t2.a)
+from t1, t2;
+
+drop table t1;
+drop table t2;
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 79f558ee844..e019ac6f8bb 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -915,6 +915,19 @@ protected:
 };
 
 
+class Create_func_json_equals : public Create_func_arg2
+{
+public:
+  virtual Item *create_2_arg(THD *thd, Item *arg1, Item *arg2);
+
+  static Create_func_json_equals s_singleton;
+
+protected:
+  Create_func_json_equals() {}
+  virtual ~Create_func_json_equals() {}
+};
+
+
 class Create_func_json_exists : public Create_func_arg2
 {
 public:
@@ -3619,6 +3632,16 @@ Create_func_json_normalize::create_1_arg(THD *thd, Item *arg1)
 }
 
 
+Create_func_json_equals Create_func_json_equals::s_singleton;
+
+Item*
+Create_func_json_equals::create_2_arg(THD *thd, Item *arg1, Item *arg2)
+{
+  status_var_increment(thd->status_var.feature_json);
+  return new (thd->mem_root) Item_func_json_equals(thd, arg1, arg2);
+}
+
+
 Create_func_json_exists Create_func_json_exists::s_singleton;
 
 Item*
@@ -5574,6 +5597,7 @@ Native_func_registry func_array[] =
   { { STRING_WITH_LEN("JSON_CONTAINS_PATH") }, BUILDER(Create_func_json_contains_path)},
   { { STRING_WITH_LEN("JSON_DEPTH") }, BUILDER(Create_func_json_depth)},
   { { STRING_WITH_LEN("JSON_DETAILED") }, BUILDER(Create_func_json_detailed)},
+  { { STRING_WITH_LEN("JSON_EQUALS") }, BUILDER(Create_func_json_equals)},
   { { 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)},
diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc
index 32043b8f6a7..2fd729ba469 100644
--- a/sql/item_jsonfunc.cc
+++ b/sql/item_jsonfunc.cc
@@ -393,6 +393,66 @@ longlong Item_func_json_valid::val_int()
 }
 
 
+bool Item_func_json_equals::fix_length_and_dec()
+{
+  if (Item_bool_func::fix_length_and_dec())
+    return TRUE;
+  set_maybe_null();
+  return FALSE;
+}
+
+
+longlong Item_func_json_equals::val_int()
+{
+  longlong result= 0;
+
+  String a_tmp, b_tmp;
+
+  String *a= args[0]->val_json(&a_tmp);
+  String *b= args[1]->val_json(&b_tmp);
+
+  DYNAMIC_STRING a_res;
+  if (init_dynamic_string(&a_res, NULL, 0, 0))
+  {
+    null_value= 1;
+    return 1;
+  }
+
+  DYNAMIC_STRING b_res;
+  if (init_dynamic_string(&b_res, NULL, 0, 0))
+  {
+    dynstr_free(&a_res);
+    null_value= 1;
+    return 1;
+  }
+
+  if ((null_value= args[0]->null_value || args[1]->null_value))
+  {
+    null_value= 1;
+    goto end;
+  }
+
+  if (json_normalize(&a_res, a->c_ptr(), a->length(), a->charset()))
+  {
+    null_value= 1;
+    goto end;
+  }
+
+  if (json_normalize(&b_res, b->c_ptr(), b->length(), b->charset()))
+  {
+    null_value= 1;
+    goto end;
+  }
+
+  result= strcmp(a_res.str, b_res.str) ? 0 : 1;
+
+end:
+  dynstr_free(&b_res);
+  dynstr_free(&a_res);
+  return result;
+}
+
+
 bool Item_func_json_exists::fix_length_and_dec()
 {
   if (Item_bool_func::fix_length_and_dec())
diff --git a/sql/item_jsonfunc.h b/sql/item_jsonfunc.h
index 04d06a0ebf7..518a98d95df 100644
--- a/sql/item_jsonfunc.h
+++ b/sql/item_jsonfunc.h
@@ -107,6 +107,23 @@ public:
 };
 
 
+class Item_func_json_equals: public Item_bool_func
+{
+public:
+  Item_func_json_equals(THD *thd, Item *a, Item *b):
+    Item_bool_func(thd, a, b) {}
+  LEX_CSTRING func_name_cstring() const override
+  {
+    static LEX_CSTRING name= {STRING_WITH_LEN("json_equals") };
+    return name;
+  }
+  bool fix_length_and_dec() override;
+  Item *get_copy(THD *thd) override
+  { return get_item_copy<Item_func_json_equals>(thd, this); }
+  longlong val_int() override;
+};
+
+
 class Item_func_json_exists: public Item_bool_func
 {
 protected: