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>

2 files changed, 167 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;