DROP DATABASE IF EXISTS json_overlaps_db;
CREATE DATABASE json_overlaps_db;
USE json_overlaps_db;
# ----------------------------------------------------------------------
# Test of JSON_OVERLAPS function.
# ----------------------------------------------------------------------
# Comparing scalar
CREATE TABLE t1(l1 LONGTEXT, l2 LONGTEXT) ENGINE = COLUMNSTORE;
INSERT INTO
t1
VALUES
('true', 'true'),
('false', 'false'),
('1', '1'),
('"string1"', '"string1"'),
('null', 'null'),
('true', 'false'),
('1', '"1"'),
('1', '0'),
('null', '0'),
('"string1"', '"string2"'),
('true', '["abc", 1, 2, true, false]'),
('true', '["abc", 1, 2, [true]]'),
('true', '{"A":true}');
SELECT
l1,
l2,
JSON_OVERLAPS(l1, l2) AS is_overlaps
FROM
t1;
l1	l2	is_overlaps
true	true	1
false	false	1
1	1	1
"string1"	"string1"	1
null	null	1
true	false	0
1	"1"	0
1	0	0
null	0	0
"string1"	"string2"	0
true	["abc", 1, 2, true, false]	1
true	["abc", 1, 2, [true]]	0
true	{"A":true}	0
# Testing non-scalar json data types
# Comparing object with object (non-nested)
CREATE TABLE t2(l1 LONGTEXT, l2 LONGTEXT) ENGINE = COLUMNSTORE;
INSERT INTO
t2
VALUES
('{"A":[1, 2, 3]}', '{}'),
('{"A": 1}', '{"A": 1}'),
('{"A": 1}', '{"B": 1}'),
(
'{"A": 1, "B": "string1"}',
'{"A": 2,"B": "string1"}'
  ),
(
'{"A": 1,"B": "string1"}',
'{"A": 2,"B": "string2"}'
  ),
(
'{"A": 1,"B": {"C":2}}',
'{"A": 2,"B": {"C":1}}'
  ),
(
'{"A": 1,"B": {"C":2}}',
'{"A": 2,"B": {"C":2}}'
  ),
(
'{"A": {"B": true}}',
'{"A": {"B": true,"C": false}}'
  ),
(
'{"A":1, "B":{"D":4, "E":5}}',
'{"C":3, "B":{"E":5, "D":4}}'
  ),
(
'{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "B":{"E":5, "D":4}}'
  ),
(
'{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "B":{"E":[5, 6, 7], "D":4}}'
  ),
(
'{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "B":{"E":[7, 6 ,5], "D":4}}'
  ),
(
'{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "F":{"E":[5, 6, 7], "D":4}}'
  ),
('[1, 2, true, false, null]', '[3, 4, 1]'),
('[1, 2, true, false, null]', '[3, 4, 5]'),
('[1,2,3]', '[]'),
('[1, 2, true, false, null]', '[3, 4, [1]]'),
(
'[1, 2, [true, false], null]',
'[[1], [true, false]]'
  ),
('[1, 2, 3, [4, 5, 6]]', '[7, 8, 9, [6, 5, 4]]'),
('[1, 2, true, false, null]', '{"A": 1}'),
(
'[1, 2, true, false, null, {"A":2}]',
'{"A": 1}'
  ),
('[1, {"A": 2}, {"A": 1}]', '{"A": 1}'),
(
'[1, 2, true, false, {"A": 1, "B": 2}]',
'{"A": 1, "B": 2}'
  ),
(
'[1, 2, true, false, {"A": 1, "B": 2}]',
'{"A": 1, "B": 3}'
  ),
(
'[1, 2, true, false, [{"A": 1, "B": 2}]]',
'{"A": 1, "B": 2}'
  ),
(
'[1, 2, true, false, [{"A": 1, "B": 2}]]',
'{"A": 1, "B": 3}'
  ),
(
'[1, 2, true, false, [{"A": 1, "B": 2}]]',
'{"A": 1}'
  ),
(
'[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
'{"A": 1, "B": {"C": 12}}'
  ),
(
'[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
'{"A": 1, "B": {"C": 12}}'
  ),
(
'[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
'{"A": 1, "B":{"C": 12}}'
  ),
(
'[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
'{"A": 1, "B": {"C": 12}}'
  ),
(
'{"A": 1, "B": 3}',
'[1, 2, true, false, {"A": 1, "B": 2}]'
  ),
(
'{"A": 1, "B": 3}',
'[1, 2, true, false, {"A": 1, "B": 3}]'
  ),
(
'{"A": 1, "B": 3}',
'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]'
  ),
(
'{"A": 1, "B": [1, 2, 3]}',
'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]'
  ),
(
'{"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}]}]'
  ),
(
'{"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}]}]'
  ),
(
'{"A": 1, "B": 3}',
'[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]'
  );
SELECT
l1,
l2,
JSON_OVERLAPS(l1, l2) AS is_overlaps
FROM
t2;
l1	l2	is_overlaps
{"A":[1, 2, 3]}	{}	0
{"A": 1}	{"A": 1}	1
{"A": 1}	{"B": 1}	0
{"A": 1, "B": "string1"}	{"A": 2,"B": "string1"}	1
{"A": 1,"B": "string1"}	{"A": 2,"B": "string2"}	0
{"A": 1,"B": {"C":2}}	{"A": 2,"B": {"C":1}}	0
{"A": 1,"B": {"C":2}}	{"A": 2,"B": {"C":2}}	1
{"A": {"B": true}}	{"A": {"B": true,"C": false}}	0
{"A":1, "B":{"D":4, "E":5}}	{"C":3, "B":{"E":5, "D":4}}	1
{"A":1, "B":{"D":4, "E":[5, 6, 7]}}	{"C":3, "B":{"E":5, "D":4}}	0
{"A":1, "B":{"D":4, "E":[5, 6, 7]}}	{"C":3, "B":{"E":[5, 6, 7], "D":4}}	1
{"A":1, "B":{"D":4, "E":[5, 6, 7]}}	{"C":3, "B":{"E":[7, 6 ,5], "D":4}}	0
{"A":1, "B":{"D":4, "E":[5, 6, 7]}}	{"C":3, "F":{"E":[5, 6, 7], "D":4}}	0
[1, 2, true, false, null]	[3, 4, 1]	1
[1, 2, true, false, null]	[3, 4, 5]	0
[1,2,3]	[]	0
[1, 2, true, false, null]	[3, 4, [1]]	0
[1, 2, [true, false], null]	[[1], [true, false]]	1
[1, 2, 3, [4, 5, 6]]	[7, 8, 9, [6, 5, 4]]	0
[1, 2, true, false, null]	{"A": 1}	0
[1, 2, true, false, null, {"A":2}]	{"A": 1}	0
[1, {"A": 2}, {"A": 1}]	{"A": 1}	1
[1, 2, true, false, {"A": 1, "B": 2}]	{"A": 1, "B": 2}	1
[1, 2, true, false, {"A": 1, "B": 2}]	{"A": 1, "B": 3}	0
[1, 2, true, false, [{"A": 1, "B": 2}]]	{"A": 1, "B": 2}	0
[1, 2, true, false, [{"A": 1, "B": 2}]]	{"A": 1, "B": 3}	0
[1, 2, true, false, [{"A": 1, "B": 2}]]	{"A": 1}	0
[1, 2, true, false, {"A": 1, "B": {"C": 12}}]	{"A": 1, "B": {"C": 12}}	1
[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]	{"A": 1, "B": {"C": 12}}	0
[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]	{"A": 1, "B":{"C": 12}}	0
[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]	{"A": 1, "B": {"C": 12}}	0
{"A": 1, "B": 3}	[1, 2, true, false, {"A": 1, "B": 2}]	0
{"A": 1, "B": 3}	[1, 2, true, false, {"A": 1, "B": 3}]	1
{"A": 1, "B": 3}	[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]	1
{"A": 1, "B": [1, 2, 3]}	[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]	1
{"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
{"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
{"A": 1, "B": 3}	[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]	0
DROP TABLE t2;
DROP TABLE t1;
DROP DATABASE json_overlaps_db;