database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-04-21 19:45:56 +03:00
Code
mariadb-columnstore-engine/mysql-test/columnstore/basic/r/func_json_overlaps.result
Ziy1-Tan cdd41f05f3 MCOL-785 Implement DISTRIBUTED JSON functions 
The following functions are created:
Create function JSON_VALID and test cases
Create function JSON_DEPTH and test cases
Create function JSON_LENGTH and test cases
Create function JSON_EQUALS and test cases
Create function JSON_NORMALIZE and test cases
Create function JSON_TYPE and test cases
Create function JSON_OBJECT and test cases
Create function JSON_ARRAY and test cases
Create function JSON_KEYS and test cases
Create function JSON_EXISTS and test cases
Create function JSON_QUOTE/JSON_UNQUOTE and test cases
Create function JSON_COMPACT/DETAILED/LOOSE and test cases
Create function JSON_MERGE and test cases
Create function JSON_MERGE_PATCH and test cases
Create function JSON_VALUE and test cases
Create function JSON_QUERY and test cases
Create function JSON_CONTAINS and test cases
Create function JSON_ARRAY_APPEND and test cases
Create function JSON_ARRAY_INSERT and test cases
Create function JSON_INSERT/REPLACE/SET and test cases
Create function JSON_REMOVE and test cases
Create function JSON_CONTAINS_PATH and test cases
Create function JSON_OVERLAPS and test cases
Create function JSON_EXTRACT and test cases
Create function JSON_SEARCH and test cases

Note:
Some functions output differs from MDB because session variables that affects functions output,e.g JSON_QUOTE/JSON_UNQUOTE
This depends on MCOL-5212
2022-08-30 22:22:23 +08:00

221 lines
5.8 KiB
Plaintext
Raw Permalink Blame History

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;
View Git Blame Copy Permalink