select json_valid('[1, 2]');
select json_valid('"string"}');
select json_valid('{"key1":1, "key2":[2,3]}');
select json_valid('[false, true, null]');
select json_valid(repeat('[', 1000));
select json_valid(repeat('{"a":', 1000));

select json_value('{"key1":123}', '$.key2');
select json_value('{"key1":123}', '$.key1');
select json_value('{"key1":[1,2,3]}', '$.key1');
select json_value('{"key1": [1,2,3], "key1":123}', '$.key1');

#enable after fix MDEV-27871
--disable_view_protocol
select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z');
--enable_view_protocol

select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2');
select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1');
select json_query('{"key1": 1}', '$.key1');
select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1');
#enable after fix MDEV-27871
--disable_view_protocol
select json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000)));
--enable_view_protocol

select json_array();
select json_array(1);
#enable after fix MDEV-28649
--disable_view_protocol
select json_array(1, "text", false, null);

select json_array_append('["a", "b"]', '$', FALSE);
--enable_view_protocol
select json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2);
select json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2);

select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x');
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x');
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x');
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x');
#enable after fix MDEV-27871
--disable_view_protocol
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x');
--enable_view_protocol
select json_array_insert('true', '$', 1);
#enable after fix MDEV-27871
--disable_view_protocol
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y');
--enable_view_protocol

select json_contains('{"k1":123, "k2":345}', '123', '$.k1');
select json_contains('"you"', '"you"');
select json_contains('"youth"', '"you"');
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select json_contains('[1]', '[1]', '$', '$[0]');
select json_contains('', '', '$');
select json_contains('null', 'null', '$');
select json_contains('"10"', '"10"', '$');
select json_contains('"10"', '10', '$');
select json_contains('10.1', '10', '$');
select json_contains('10.0', '10', '$');
select json_contains('[1]', '1');
select json_contains('[2, 1]', '1');
select json_contains('[2, [2, 3], 1]', '1');
select json_contains('[4, [2, 3], 1]', '2');
select json_contains('[2, 1]', '[1, 2]');
select json_contains('[2, 1]', '[1, 0, 2]');
select json_contains('[2, 0, 3, 1]', '[1, 2]');
select json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}');
select json_contains('{"a":1}', '{}');
select json_contains('[1, {"a":1}]', '{}');
select json_contains('[1, {"a":1}]', '{"a":1}');
select json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]');
#enable after fix MDEV-27871
--disable_view_protocol
select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]');
--disable_view_protocol
select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}');

select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]");
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]");
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma");
select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1");
select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma");
select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma");
select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2");
select json_contains_path('{ "a": true }', NULL, '$.a' );
select json_contains_path('{ "a": true }', 'all', NULL );
select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*');
 
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1");
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY");
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2");
select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2");
select json_extract('{"key0":true, "key1":"qwe"}', "$.key1");
select json_extract(json_object('foo', 'foobar'),'$');
select json_extract('[10, 20, [30, 40]]', '$[2][*]');
select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]');
select json_extract('1', '$');
select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]');
select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]');
select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a');

select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word');
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3);
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2);
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word');

select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');

select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]');

set @j = '["a", ["b", "c"], "d"]';
select json_remove(@j, '$[0]');
select json_remove(@j, '$[1]');
select json_remove(@j, '$[2]');
set @j = '{"a": 1, "b": [2, 3]}';
select json_remove(@j, '$.b');
select json_remove(@j, '$.a');

select json_object();
select json_object("ki", 1, "mi", "ya");
create table t1 as select json_object('id', 87, 'name', 'carrot') as f;
show create table t1;
select * from t1;
drop table t1;

select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2");
select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]");
select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]");

select json_quote('"string"');
create table t1 as select json_quote('foo');
select * from t1;
show create table t1;
drop table t1;

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select json_merge('string');
select json_merge('string', 123);
select json_merge('"string"', 123);
select json_merge('[1, 2]', '[true, false]');
select json_merge('{"1": 2}', '{"true": false}');
select json_merge('{"1": 2}', '{"true": false}', '{"3": 4}');
select json_merge(NULL,json_object('foo', 1));
select json_merge('a','b');
select json_merge('{"a":"b"}','{"c":"d"}');
SELECT JSON_MERGE('[1, 2]', '{"id": 47}');

select json_type('{"k1":123, "k2":345}');
select json_type('[123, "k2", 345]');
select json_type("true");
select json_type('123');
select json_type('123.12');

select json_keys('{"a":{"c":1, "d":2}, "b":2}');
select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a");
select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b");
select json_keys('foo');
#
# mdev-12789 JSON_KEYS returns duplicate keys twice
#
select json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}');
select json_keys('{"c1": "value 1", "c1": "value 2"}');

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
select json_search(@j, 'one', 'abc');
select json_search(@j, 'all', 'abc');
select json_search(@j, 'all', 'abc', NULL, '$[2]');
select json_search(@j, 'all', 'abc', NULL, '$');
select json_search(@j, 'all', '10', NULL, '$');
select json_search(@j, 'all', '10', NULL, '$[*]');
select json_search(@j, 'all', '10', NULL, '$[*][0].k');
select json_search(@j, 'all', '10', NULL, '$**.k');
create table t1( json_col text );
insert into t1 values
('{ "a": "foobar" }'),
  ('{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }');
select json_search( json_col, 'all', 'foot' ) from t1;
drop table t1;


select json_unquote('"abc"');
select json_unquote('abc');
#
# MDEV-13703 Illegal mix of collations for operation 'json_object' on using JSON_UNQUOTE as an argument.
#
create table t1 (c VARCHAR(8)) DEFAULT CHARSET=latin1;
insert into t1 values ('abc'),('def');
 
select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1;
drop table t1;

select json_object("a", json_object("b", "abcd"));
select json_object("a", '{"b": "abcd"}');
select json_object("a", json_compact('{"b": "abcd"}'));

select json_compact(NULL);
select json_depth(json_compact(NULL));
select json_depth('[[], {}]');
select json_depth('[[[1,2,3],"s"], {}, []]');
select json_depth('[10, {"a": 20}]');

select json_length('');
select json_length('{}');
select json_length('[1, 2, {"a": 3}]');
select json_length('{"a": 1, "b": {"c": 30}}', '$.b');
select json_length('{"a": 1, "b": {"c": 30}}');
select json_length('{}{');

create table json (j INT);
show create table json;
drop table json;

select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' );
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' );
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' );
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' );
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' );
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' );
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' );
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' );
select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' );

select json_set('1', '$[0]', 100);
select json_set('1', '$[0][0]', 100);
select json_set('1', '$[1]', 100);
select json_set('{"a":12}', '$[0]', 100);
select json_set('{"a":12}', '$[0].a', 100);
select json_set('{"a":12}', '$[0][0].a', 100);
select json_set('{"a":12}', '$[0][1].a', 100);

select json_value('{"\\"key1":123}', '$."\\"key1"');
select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""');
select json_value('{"key 1":123}', '$."key 1"');

select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]");
select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]");

select json_extract( '[1]', '$[0][0]' );
select json_extract( '[1]', '$[1][0]' );
select json_extract( '[1]', '$**[0]' );
select json_extract( '[1]', '$**[0][0]' );

select json_insert('1', '$[0]', 4);
select json_replace('1', '$[0]', 4);
select json_set('1', '$[0]', 4);
select json_set('1', '$[1]', 4);
select json_replace('1', '$[1]', 4);
SELECT json_insert('[]', '$[0][0]', 100);
SELECT json_insert('1', '$[0][0]', 100);
SELECT json_replace('1', '$[0][0]', 100);
SELECT json_replace('[]', '$[0][0]', 100);
SELECT json_set('[]', '$[0][0]', 100);
SELECT json_set('[]', '$[0][0][0]', 100);

#
# MDEV-11857 json_search() shows "Out of memory" with empty key.
#
SELECT JSON_search( '{"": "a"}', "one", 'a');

#
# MDEV-11858 json_merge() concatenates instead of merging.
#

select json_merge('{"a":"b"}', '{"a":"c"}') ;
select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') ;
select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') ;
select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') ;

select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
select json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
select json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');

#
# MDEV-11856 json_search doesn't search for values with double quotes character (")
#

SELECT JSON_search( '{"x": "\\""}', "one", '"');
SELECT JSON_search( '{"x": "\\""}', "one", '\\"');

#
# MDEV-11833 JSON functions don't seem to respect max_allowed_packet.
#
set @save_max_allowed_packet=@@max_allowed_packet;
set @save_net_buffer_length=@@net_buffer_length;

set @@global.net_buffer_length=1024;
set @@global.max_allowed_packet=2048;
--connect (newconn, localhost, root,,)

show variables like 'net_buffer_length';
show variables like 'max_allowed_packet';
select json_array(repeat('a',1024),repeat('a',1024));
select json_object("a", repeat('a',1024),"b", repeat('a',1024));
--connection default

set @@global.max_allowed_packet = @save_max_allowed_packet;
set @@global.net_buffer_length = @save_net_buffer_length;
--disconnect newconn


#
# MDEV-12262 Assertion `!null_value' failed in virtual bool Item::send on JSON_REMOVE.
#
create table t1(j longtext, p longtext);
insert into t1 values
    ('{"a":1,"b":2,"c":3}','$.a'),
    ('{"a":1,"b":2,"c":3}','$.b'),
      ('{"a":1,"b":2,"c":3}','$.c');
select j, p, json_remove(j, p) from t1;
drop table t1;

#
# MDEV-12364 Server crashes in __memcpy_sse2_unaligned / String::copy on JSON_SEARCH with variables.
#
SET @str = 'bar', @path = '$';
SELECT JSON_SEARCH('{"foo":"bar"}', 'all' , @str, '%', @path);

#
# MDEV-12351 Assertion `cur_step->type & JSON_PATH_KEY' failed in json_find_path.
#

SELECT JSON_VALUE('[{"foo": 1},"bar"]', '$[*][0]');

#
# MDEV-12363 Assertion `0' failed in Type_handler_string_result::make_sort_key(uchar*, Item*, const SORT_FIELD_ATTR*, Sort_param*)      
#

CREATE TABLE t1 (f INT NOT NULL);
INSERT INTO t1 VALUES (0);
SELECT JSON_KEYS(f) FROM t1 ORDER BY 1;
DROP TABLE t1;

#
# MDEV-12324 Wrong result (phantom array value) on JSON_EXTRACT.
#
SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' );
SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*]' );

#
# MDEV-12604 Comparison of JSON_EXTRACT result differs with Mysql.
#

select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value';
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true;
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false;
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1;
select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"');

#
# MDEV-129892 JSON_EXTRACT returns data for invalid JSON
#
select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo');

#
# MDEV-13138 JSON_OBJECT returns null with strings containing backticks.
#
SELECT JSON_OBJECT('foo', '`');
SELECT JSON_OBJECT("foo", "bar`bar");

#
# MDEV-13324 JSON_SET returns NULL instead of object.
#
SELECT JSON_SET('{}', '$.age', 87);

#
# MDEV-13104 Json functions.
#
SELECT JSON_MERGE('[]', '{"c":"d"}');

#
# MDEV-12774 JSON_EXTRACT fails with some escaped unicode as key.
#

SET @str = "{\"\\u00e4\\u00f6\":\"yes\"}";
SET @path = "$.\"\\u00e4\\u00f6\"";
select @str, @path, JSON_EXTRACT(@str, @path);
SET @str = "{\"\\u00e4\":\"yes\"}";
SET @path = "$.\"\\u00e4\"";
select @str, @path, JSON_EXTRACT(@str, @path);

#
# MDEV-12877 Wrong result from JSON native function.
#
select json_array(5,json_query('[1,2]','$'));

#
# MDEV-13633 JSON_ARRAY() - bad output with some UTF8 characters.
#

SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data;
SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data;

#
# MDEV-12312 JSON_CONTAINS_PATH does not detect invalid path and returns TRUE.
#

select json_contains_path('{"foo":"bar"}', 'one', '$[]');

#
# MDEV-13971 crash in skip_num_constant.
#
select JSON_VALID(0x36f0c8dccd83c5eac156da);

#
# MDEV-13970 crash in Item_func_json_extract::read_json.
#
create table t1(a double not null);
insert into t1 values (2),(1);
select 1 from t1 where json_extract(a,'$','$[81]');
drop table t1;

#
# MDEV-15561 json_extract returns NULL with numbers in scientific notation.
#

select json_extract('{"test":8.437e-5}','$.test');

#
# MDEV-15905 select json_value('{"b":true}','$.b')=1 --> false with
#    "Truncated incorrect DOUBLE value: 'true'"
#
select json_value('{"b":true}','$.b')=1;

#
# MDEV-16209 JSON_EXTRACT in query crashes server.
#

CREATE TABLE t1 (c VARCHAR(8));
INSERT INTO t1 VALUES ('foo'),('bar');
SELECT * FROM t1 WHERE c IN (JSON_EXTRACT('{"a":"b"}', '$.*'));
DROP TABLE t1;

--echo #
--echo # MDEV-16814 CREATE TABLE SELECT JSON_QUOTE(multibyte_charset_expr) makes a field of a wrong length
--echo #

CREATE TABLE t1 AS SELECT
  JSON_QUOTE(_latin1'foo') AS c1,
  JSON_QUOTE(_utf8'foo') AS c2;
SHOW CREATE TABLE t1;
DROP TABLE t1;

--echo #
--echo # MDEV-16054 simple json functions flatline cpu on garbage input.
--echo #

select json_array(1,user(),compress(5.140264e+307));

--echo #
--echo # MDEV-16869 String functions don't respect character set of JSON_VALUE.
--echo #

create table t1(json_col TEXT) DEFAULT CHARSET=latin1;
insert into t1 values (_latin1 X'7B226B657931223A2253EC227D');
select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1;
select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' from t1;
drop table t1;

--echo #
--echo # MDEV-16750 JSON_SET mishandles unicode every second pair of arguments.
--echo #

SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6);
SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6);
SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6');

--echo #
--echo # MDEV-17121 JSON_ARRAY_APPEND
--echo #

select json_array_append('[             ]', '$', 'aue');

--echo #
--echo # MDEV-17018 JSON_SEARCH and User-Defined Variables.
--echo #

SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', @`value` := 'AB';
SELECT JSON_SEARCH(@`json`, 'one', @`value`);
SET @`json` := NULL, @`value` := NULL;

--echo #
--echo # MDEV-17001 JSON_MERGE returns nullwhen merging empty array.
--echo #

SELECT JSON_MERGE('[1]', '[]');

--echo #
--echo # MDEV-16174 Assertion `0' failed in Type_handler_string_result::
--echo #       make_sort_key(uchar*, Item*, const SORT_FIELD_ATTR*, Sort_param*)
--echo #

SET sql_mode='';
CREATE TABLE t1 (fld varchar(16) NOT NULL);
CREATE TABLE t2 SELECT JSON_ARRAY_INSERT(fld, '$.[0]', '0') FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;
SET sql_mode=default;

--echo #
--echo # MDEV-17454 JSON_VALID( '{"a":1]' ) evaluates to 1
--echo #

select JSON_VALID( '{"a":1]' );

--echo #
--echo # MDEV-18886 JSON_ARRAY() does not recognise JSON argument.
--echo #
SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket'));
SELECT CHARSET(JSON_ARRAY());
SELECT CHARSET(JSON_OBJECT());

--echo #
--echo # MDEV-13992 Implement JSON_MERGE_PATCH
--echo #

CREATE TABLE merge_t(
id INT PRIMARY KEY AUTO_INCREMENT,
target VARCHAR(100), patch VARCHAR(100)
);
INSERT INTO merge_t(target, patch) VALUES
('{"a":"b"}', '{"a":"c"}'),
('{"a":"b"}', '{"b":"c"}'),
('{"a":"b"}', '{"a":null}'),
('{"a":"b", "b":"c"}', '{"a":null}'),
('{"a":["b"]}', '{"a":"c"}'),
('{"a":"c"}', '{"a":["b"]}'),
('{"a": {"b":"c"}}', '{"a": {"b":"d", "c":null}}'),
('{"a":[{"b":"c"}]}', '{"a": [1]}'),
('["a","b"]', '["c","d"]'),
('{"a":"b"}', '["c"]'),
('{"a":"foo"}', 'null'),
('{"a":"foo"}', '"bar"'),
('{"e":null}', '{"a":1}'),
('[1,2]', '{"a":"b", "c":null}'),
('{}', '{"a":{"bb":{"ccc":null}}}'),
(NULL, '{}'),
('{}', NULL);
SELECT id, target, patch,
       JSON_MERGE_PATCH(target, patch) AS merged,
       JSON_EXTRACT(JSON_MERGE_PATCH(target, patch), '$.a') AS a
FROM merge_t ORDER BY id;
DROP TABLE merge_t;

SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}');
SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]');
SELECT JSON_MERGE_PATCH(NULL, 'a');
SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}');

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_MERGE_PATCH();
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_MERGE_PATCH('{}');
SELECT JSON_MERGE_PATCH('{', '[1,2,3]');
SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,');

--echo #
--echo # MDEV-22976 CAST(JSON_EXTRACT() AS DECIMAL) does not handle boolean values
--echo #

SELECT
  CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DOUBLE) AS cf,
  CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DECIMAL) AS cd;

SELECT
  CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DOUBLE) AS cf,
  CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DECIMAL) AS cd;


--echo #
--echo # MDEV-24585 Assertion `je->s.cs == nice_js->charset()' failed in json_nice.
--echo #

SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' );

--echo #
--echo # MDEV-18284 JSON casting using JSON_COMPACT doesn't always work
--echo #            with values from subqueries
--echo #

CREATE TABLE json_test(a JSON, b JSON);
INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}');
SELECT * FROM json_test;

SELECT json_object("a", json_compact(a), "b", b)
  FROM (SELECT * FROM json_test) AS json_test_values;
SELECT json_object("a", json_compact(a), "b", json_compact(b))
  FROM (SELECT * FROM json_test) AS json_test_values;
DROP TABLE json_test;

--echo #
--echo # End of 10.2 tests
--echo #

--echo #
--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
--echo #

--enable_metadata
--disable_ps_protocol

SELECT
  JSON_VALID('{"id": 1, "name": "Monty"}') AS json_valid,
  JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") AS json_exists,
  JSON_CONTAINS('{"A": 0, "B": {"C": 1}, "D": 2}', '2', '$.A') AS ison_contains,
  JSON_CONTAINS_PATH('{"A": 1, "B": [2], "C": [3, 4]}', 'one', '$.A', '$.D') AS json_contains_path;

SELECT
  JSON_LENGTH('{"a": 1, "b": {"c": 30}}') AS json_length,
  JSON_DEPTH('[10, {"a": 20}]') AS json_depnth;

--enable_ps_protocol
--disable_metadata

--echo #
--echo # MDEV-19670 json escaped unicode parse error
--echo #

SELECT json_valid('{"value":"\\ud83d\\ude0a"}');
SELECT json_valid('{"test": "\\ud83d\\ude0b"}');

--echo #
--echo # MDEV-19670 json escaped unicode parse error
--echo #

SELECT JSON_VALID('{"admin\\"": null}'), '{"admin\\"": null}'
    UNION
    SELECT JSON_VALID('{"\\"admin": null}'), '{"\\"admin": null}'
      UNION
      SELECT JSON_VALID('{"\\"": null}'), '{"\\"": null}';

--echo #
--echo # MDEV-29188: Crash in JSON_EXTRACT
--echo #

CREATE TABLE t1 (j JSON);

INSERT INTO t1 VALUES
  ('{"ID": "4", "Name": "Betty", "Age": 19}'),
  ('[10, 20, [30, 40]]');

SELECT * FROM t1 WHERE JSON_EXTRACT(j, '$.Age')=19;

drop table t1;

--echo #
--echo # MDEV-27151: JSON_VALUE() does not parse NULL properties properly
--echo #
--echo #
--echo # It is correct for JSON_EXTRACT() to give null instead of "NULL" because
--echo # it returns the json literal that is put inside json.
--echo # Hence it should return null as in 'null' string and not SQL NULL.
--echo # JSON_VALUE() returns the "VALUE" so it is correct for it to return SQl NULL
--echo #

SELECT NULL;
SELECT JSON_VALUE('{"nulltest": null}', '$.nulltest');
SELECT 1 + NULL;
SELECT 1 + JSON_VALUE('{"nulltest": null}', '$.nulltest');


SELECT NULL;
SELECT JSON_EXTRACT('{"a":null, "b":10, "c":"null"}', '$.a');

--echo #
--echo # End of 10.3 tests
--echo #

--echo #
--echo # Start of 10.4 tests
--echo #

--echo #
--echo # MDEV-16351 JSON_OBJECT() treats hybrid functions with boolean arguments as numbers
--echo #

--vertical_results
SELECT
  JSON_OBJECT("cond", true) AS j1,
  JSON_OBJECT("cond", COALESCE(true, false)) j2,
  JSON_OBJECT("cond", COALESCE(COALESCE(true, false))) j3;
--horizontal_results

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
SELECT JSON_OBJECT('x',(SELECT MAX(a)=4 FROM t1));
SELECT JSON_OBJECT('x',(SELECT MAX(a)=3 FROM t1));
SELECT JSON_OBJECT('x',(SELECT MAX(a)=2 FROM t1));

SELECT JSON_OBJECT('x',MAX(a=4)) FROM t1;
SELECT JSON_OBJECT('x',MAX(a=3)) FROM t1;
SELECT JSON_OBJECT('x',MAX(a=2)) FROM t1;

SELECT JSON_OBJECT('x',(SELECT MAX(a=4) FROM t1));
SELECT JSON_OBJECT('x',(SELECT MAX(a=3) FROM t1));
SELECT JSON_OBJECT('x',(SELECT MAX(a=2) FROM t1));

SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": true}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": false}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": true}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": false}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": true}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": false}' THEN a END;

SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": true}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": false}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": true}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": false}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": true}' THEN a END;
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": false}' THEN a END;


DROP TABLE t1;

-- echo #
-- echo # MDEV-16620 JSON_ARRAYAGG
-- echo #

-- echo #
-- echo # Integer aggregation
-- echo #
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 1),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(2, 2);
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
DROP TABLE t1;

-- echo #
-- echo # Real aggregation
-- echo #
CREATE TABLE t1 (a FLOAT, b DOUBLE, c DECIMAL(10, 2));
INSERT INTO t1 VALUES (1.0, 2.0, 3.0),(1.0, 3.0, 9.0),(1.0, 4.0, 16.0),(1.0, 5.0, 25.0);
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b), JSON_ARRAYAGG(c) FROM t1;
DROP TABLE t1;

-- echo #
-- echo # Boolean aggregation
-- echo #
CREATE TABLE t1 (a BOOLEAN, b BOOLEAN);
INSERT INTO t1 VALUES (TRUE, TRUE), (TRUE, FALSE), (FALSE, TRUE), (FALSE, FALSE);
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
SELECT JSON_ARRAYAGG(TRUE), JSON_ARRAYAGG(FALSE) FROM t1;
DROP TABLE t1;

-- echo #
-- echo # Aggregation of strings with quoted
-- echo #
CREATE TABLE t1 (a VARCHAR(80));
INSERT INTO t1 VALUES
       ('"double_quoted_value"'), ("'single_quoted_value'"),
       ('"double_quoted_value"'), ("'single_quoted_value'");
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
SELECT JSON_ARRAYAGG(a) FROM t1;
DROP TABLE t1;

-- echo #
-- echo # Strings and NULLs
-- echo #
CREATE TABLE t1 (a INT, b VARCHAR(80));
INSERT INTO t1 VALUES
       (1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL),
       (1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL);
SELECT JSON_VALID(JSON_ARRAYAGG(b)) FROM t1;
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY a;

-- echo #
-- echo # DISTINCT and LIMIT
-- echo #
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1;
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1;
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1 GROUP BY b;
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1 GROUP BY a;
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
SELECT JSON_ARRAYAGG(DISTINCT b) FROM t1;
SELECT JSON_ARRAYAGG(DISTINCT a LIMIT 2) FROM t1;
SELECT JSON_ARRAYAGG(DISTINCT b LIMIT 2) FROM t1;

-- echo #
-- echo # JSON aggregation
-- echo #
SELECT JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b))) FROM t1;
SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1;
SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1 GROUP BY a;

SELECT JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))) FROM t1;
SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1;
SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1 GROUP BY a;

-- echo #
-- echo # Error checks
-- echo #
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_ARRAYAGG(a, b) FROM t1;

--error  ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a, b)) FROM t1;

--error ER_INVALID_GROUP_FUNC_USE
SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a)) FROM t1;

-- echo #
-- echo # MDEV-16620 JSON_OBJECTAGG
-- echo #

SELECT JSON_OBJECTAGG(a, b) FROM t1;
--error ER_PARSE_ERROR
SELECT JSON_OBJECTAGG(a) FROM t1;

DROP TABLE t1;

--echo #
--echo # MDEV-19160 JSON_DETAILED output unnecessarily verbose
--echo #

create table t200 (a text);

insert into t200 values
('{
  "steps": [
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "rows_estimation": [
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1000,
                    "cost": 2e308
                  },
                  "potential_range_indexes": [
                    {
                      "index": "a_b",
                      "usable": true,
                      "key_parts": ["a", "b"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "a_b",
                    "cost": 52.195,
                    "chosen": true
                  },
                  "setup_range_conditions": [],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "no group by or distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "a_b",
                        "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"],
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 1.1752,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": [],
                    "test_one_line_array":["123"]
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "a_b",
                      "rows": 1,
                      "ranges": ["2 <= a <= 2 AND 4 <= b <= 4"]
                    },
                    "rows_for_plan": 1,
                    "cost_for_plan": 1.1752,
                    "chosen": true
                  }
                }
              },
              {
                "selectivity_for_indexes": [
                  {
                    "index_name": "a_b",
                    "selectivity_from_index": 0.001
                  }
                ],
                "selectivity_for_columns": [],
                "cond_selectivity": 0.001
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}');


select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200;
select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200;
select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200;
drop table t200;

--echo #
--echo # End of 10.4 tests
--echo #

-- echo #
-- echo # MDEV-16620 JSON_ARRAYAGG
-- echo #

CREATE TABLE t1 (a INT);
SELECT JSON_ARRAYAGG(a) FROM t1;
DROP TABLE t1;

-- echo #
-- echo # MDEV-21915 Server crashes in copy_fields,Item_func_group_concat::add
-- echo       while using json_arrayagg() as a window function
-- echo #

--error ER_NOT_SUPPORTED_YET
select json_arrayagg(a) over () from (select 1 a) t;

--error ER_NOT_SUPPORTED_YET
select json_objectagg(a, b) over () from (select 1 a, 2 b) t;

SELECT JSON_ARRAYAGG(NULL) FROM (SELECT 1 AS t) AS A;
SELECT JSON_ARRAYAGG("null") FROM (SELECT 1 AS t) AS A;

create view v as (select json_arrayagg(json_object("type", "permPeriod", "id", "asd")) as JSON_DATA);
select * from v; 
drop view v;

select json_arrayagg(a order by a asc) from (select 1 a union select 2 a) t;

select json_object('x', json_arrayagg(json_object('a', 1)));

--echo #
--echo # MDEV-22011: DISTINCT with JSON_ARRAYAGG gives wrong results
--echo #

CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);

SELECT JSON_ARRAYAGG(a) FROM t1;
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;

INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);

SELECT JSON_ARRAYAGG(a) FROM t1;
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1(a VARCHAR(10), b INT);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);

SELECT JSON_ARRAYAGG(a) FROM t1;
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;

INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);

SELECT JSON_ARRAYAGG(a) FROM t1;
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;

DROP TABLE t1;

--echo #
--echo # MDEV-22840: JSON_ARRAYAGG gives wrong results with NULL values and ORDER by clause
--echo #

CREATE TABLE t1(a VARCHAR(255));
INSERT INTO t1 VALUES ('red'),('blue');

SELECT JSON_ARRAYAGG(a) FROM t1;
SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1;
SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1;

INSERT INTO t1 VALUES (NULL);

SELECT JSON_ARRAYAGG(a) FROM t1;
SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1;
SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1;
DROP TABLE t1;

set group_concat_max_len=64;
create table t1 (a varchar(254));
insert into t1 values (concat('x64-', repeat('a', 60)));
insert into t1 values (concat('x64-', repeat('b', 60)));                                            insert into t1 values (concat('x64-', repeat('c', 60)));
select json_arrayagg(a) from t1; 
drop table t1;
SET group_concat_max_len= default;

create table t1 (col1 json);
insert into t1 values('{"color":"red", "size":1}' );
insert into t1 values('{"color":"blue", "size":2}' );
select JSON_ARRAYAGG(col1) from t1;
drop table t1;

--echo #
--echo # MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY
--echo #

CREATE TABLE t1 (e INT, a VARCHAR(255), v VARCHAR(255));
INSERT INTO t1 VALUES (0, 'a1', '1') , (0, 'a2', '2') , (1, 'b1', '3');

EXPLAIN SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e, B.a;
SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e, B.a;

CREATE VIEW v AS SELECT JSON_OBJECTAGG(a, e) FROM t1;
SELECT * FROM v;

DROP VIEW v;
DROP TABLE t1;

--echo #
--echo # MDEV-23004 When using GROUP BY with JSON_ARRAYAGG with joint table, the square brackets are not included.
--echo #

CREATE TABLE t1(id int primary key, name varchar(50));
CREATE TABLE t2(id int, owner_id int);
 
INSERT INTO t1 VALUES (1, "name1"), (2, "name2"), (3, "name3");
INSERT INTO t2 VALUES (1, 1), (2, 1), (3, 2), (4, 3);
 
SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id) ORDER BY t2.id) as materials
  from t1 LEFT JOIN t2 on t1.id = t2.owner_id
  GROUP BY t1.id ORDER BY id;

DROP TABLE t1;
DROP TABLE t2;

--echo #
--echo # MDEV-27018 IF and COALESCE lose "json" property
--echo #

SELECT json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f')));
SELECT json_object('a', coalesce(json_object('b', 'c')));

--echo #
--echo # MDEV-26392: Crash with json_get_path_next and 10.5.12
--echo #

CREATE TABLE arrNestTest (
    id VARCHAR(80) AS (JSON_COMPACT(JSON_EXTRACT(doc, "$._id"))) UNIQUE KEY,
    doc JSON, 
    CONSTRAINT id_not_null CHECK(id IS NOT NULL));
    
INSERT INTO test.arrNestTest (doc) VALUES ('{ "_id" : { "$oid" : "611c0a463b150154132f6636" }, "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : 1.0 } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] }');
SELECT * FROM arrNestTest;
DROP TABLE arrNestTest;

--echo #
--echo # MDEV-30412 JSON_OBJECTAGG doesn't escape double quote in key
--echo #

SELECT JSON_OBJECTAGG('"', 1);
SELECT JSON_OBJECTAGG('\"', 1);
SELECT JSON_OBJECTAGG('\\', 1);

--echo #
--echo # End of 10.5 tests
--echo #

--echo #
--echo # MDEV-26054 Server crashes in Item_func_json_arrayagg::get_str_from_field
--echo #

CREATE TABLE t (a VARCHAR(8));
CREATE VIEW v AS SELECT * FROM t;
INSERT INTO t VALUES ('foo'),('bar');
SELECT JSON_ARRAYAGG(a) AS f FROM v;
DROP VIEW v;
DROP TABLE t;

--echo #
--echo # MDEV-29264 JSON functions overflow error based ON LONGTEXT field
--echo #
CREATE TABLE t(l1 LONGTEXT, l2 LONGTEXT, l3 LONGTEXT, l4 LONGTEXT);
INSERT INTO t VALUES('k1', 'v1', 'k2', 'v2');
SELECT JSON_ARRAY(l1, l2, l3, l4), JSON_OBJECT(l1, l2, l3, l4) from t;
SELECT JSON_ARRAY_APPEND(JSON_ARRAY(l1, l2, l3, l4), '$[0]', 'k3'), JSON_ARRAY_INSERT(JSON_ARRAY(l1, l2, l3, l4), '$[0]', 'k3') from t;
SELECT JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3'),JSON_SET(JSON_OBJECT(l1, l2, l3, l4), '$.k2', 'new v2'),JSON_REPLACE(JSON_OBJECT(l1, l2, l3, l4), '$.k2', 'new v2') from t;
DROP TABLE t;

--echo #
--echo # End of 10.6 tests
--echo #

--echo #
--echo # Beginning of 10.9 tests
--echo #
--echo # MDEV-27677: Implement JSON_OVERLAPS()
--echo #


--echo # Testing scalar json datatypes

--echo # Comparing scalar json datatypes with itself
SELECT JSON_OVERLAPS('true', 'true');
SELECT JSON_OVERLAPS('false', 'false');
SELECT JSON_OVERLAPS('1', '1');
SELECT JSON_OVERLAPS('"string1"', '"string1"');
SELECT JSON_OVERLAPS('null', 'null');

--echo # Comparing scalar json datatypes with other scalar datatype
SELECT JSON_OVERLAPS('true', 'false');
SELECT JSON_OVERLAPS('1', '"1"');
SELECT JSON_OVERLAPS('1', '0');
SELECT JSON_OVERLAPS('null', '0');
SELECT JSON_OVERLAPS('"string1"', '"string2"');

SELECT JSON_OVERLAPS('true','["abc", 1, 2, true, false]');
SELECT JSON_OVERLAPS('true','["abc", 1, 2, [true]]');
SELECT JSON_OVERLAPS('true','{"A":true}');


--echo # Testing non-scalar json data types

--echo # Comparing object with object (non-nested)
SELECT JSON_OVERLAPS('{"A":[1, 2, 3]}','{}');
SELECT JSON_OVERLAPS('{"A": 1}',
                     '{"A": 1}');
SELECT JSON_OVERLAPS('{"A": 1}',
                     '{"B": 1}');
SELECT JSON_OVERLAPS('{
                       "A": 1,
                       "B": "string1"
                      }',
                     '{
                        "A": 2,
                        "B": "string1"
                      }');
SELECT JSON_OVERLAPS('{
                       "A": 1,
                       "B": "string1"
                      }',
                     '{
                       "A": 2,
                       "B": "string2"
                      }');

--echo # Comparing nested object with other nested object
SELECT JSON_OVERLAPS('{
                        "A": 1,
                        "B": {"C":2}
                      }',
                     '{
                        "A": 2,
                        "B": {"C":1}
                      }');
SELECT JSON_OVERLAPS('{
                       "A": 1,
                       "B": {"C":2}
                      }',
                     '{
                        "A": 2,
                        "B": {"C":2}
                      }');
SELECT JSON_OVERLAPS('{
                       "A": {
                             "B": true
                            }
                      }',
                     '{
                       "A": {
                             "B": true,
                             "C": false
                            }
                      }');
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}',
                     '{"C":3, "B":{"E":5, "D":4}}');
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
                     '{"C":3, "B":{"E":5, "D":4}}');
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
                     '{"C":3, "B":{"E":[5, 6, 7], "D":4}}');
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
                     '{"C":3, "B":{"E":[7, 6 ,5], "D":4}}');
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
                     '{"C":3, "F":{"E":[5, 6, 7], "D":4}}');


--echo # Comparing array with array (non-nested)
SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
                     '[3, 4, 1]');
SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
                     '[3, 4, 5]');
SELECT JSON_OVERLAPS('[1,2,3]','[]');

--echo # Comparing nested arrays
SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
                     '[3, 4, [1]]');
SELECT JSON_OVERLAPS('[1, 2, [true, false], null]',
                     '[[1], [true, false]]');
SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]');


--echo # Comparing one non-scalar json datatypes with another non-scalar
--echo # json datatype

--echo # Comparing array with object
SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
                     '{"A": 1}');
SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]',
                     '{"A": 1}');
SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]',
                     '{"A": 1}');
SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
                     '{"A": 1, "B": 2}');
SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
                     '{"A": 1, "B": 3}');

-- echo # Comparing nested array with object
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
                     '{"A": 1, "B": 2}');
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
                     '{"A": 1, "B": 3}');
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
                     '{"A": 1}');

--echo # Comparing array with nested object
SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
                     '{"A": 1, "B": {"C": 12}}');
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
                      '{"A": 1, "B": {"C": 12}}');

--echo # Comparing nested array with nested objects
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
                     '{"A": 1, "B":{"C": 12}}');
SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
                     '{"A": 1, "B": {"C": 12}}');

--echo # Comparing object with array
SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
                     '[1, 2, true, false, {"A": 1, "B": 2}]');
SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
                     '[1, 2, true, false, {"A": 1, "B": 3}]');
SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
                     '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]');
SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}',
                     '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]');
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}]}]');
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}]}]');

--echo # Comparing object with nested array
SELECT JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]');

--echo # Checking errors and warnings

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_OVERLAPS('[1,2,{"A":B}]', '{"A":B}', '{"C":"string1"}');
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_OVERLAPS('[1,2,{"A":B}]');

--echo #
--echo # MDEV-27990: Incorrect behavior of JSON_OVERLAPS() on warning
--echo #
SELECT JSON_OVERLAPS('','');
SELECT JSON_OVERLAPS('true','tr');

--echo #
--echo # MDEV-22224: Support JSON Path negative index
--echo #

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, 13, {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5);
SELECT JSON_ARRAY_APPEND(@json, '$.A[last-1][last]', 5);

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, 13, {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5);
SELECT JSON_ARRAY_INSERT(@json, '$.A[last-1][last-1]', 5);

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, 13, {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_CONTAINS(@json, '15', '$.A[-2][-1]');
SELECT JSON_CONTAINS(@json, '15', '$.A[last-1][last]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, 13, {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]');
SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[last-1]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, 13, {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_EXISTS(@json, '$.A[-2][-1]');
SELECT JSON_EXISTS(@json, '$.A[last-1][last]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_EXTRACT(@json, '$.A[-8][1]');
SELECT JSON_EXTRACT(@json, '$.A[last-7][1]');

SET @json= '[{"A": 1, "B": 2, "C": {"D": 3}},{"A": 1, "B": 2, "C": {"D": 3}}]';
SELECT JSON_KEYS(@json, '$[-1].C');
SELECT JSON_KEYS(@json, '$[last].C');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_LENGTH(@json, '$.A[-2][-3]');
SELECT JSON_LENGTH(@json, '$.A[last-1][last-2]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_QUERY(@json, '$.A[-8]');
SELECT JSON_QUERY(@json, '$.A[last-7]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_REMOVE(@json, '$.A[-10]');
SELECT JSON_REMOVE(@json, '$.A[last-9]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_REPLACE(@json, '$.A[-1]', 4);
SELECT JSON_REPLACE(@json, '$.A[last]', 4);

SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]');
SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[last-1]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_SET(@json, '$.A[-4]', 100);
SELECT JSON_SET(@json, '$.A[last-3]', 100);

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":123},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_VALUE(@json, '$.A[-2][-2].key1');
SELECT JSON_VALUE(@json, '$.A[last-1][last-1].key1');

--echo #
--echo # MDEV-27972: Unexpected behavior with negative zero (-0) in JSON Path
--echo #

SET @json='{ "x": [0,1]}';
SELECT JSON_VALUE(@json,'$.x[last]');
SELECT JSON_VALUE(@json,'$.x[last-0]');
SELECT JSON_VALUE(@json,'$.x[-0]');
SELECT JSON_VALUE(@json,'$.x[0]');


--echo #
--echo # MDEV-27911: Implement range notation for json path
--echo #

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_EXISTS(@json, '$[3][3][-2 to last]');

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_SEARCH(@json, 'one', '12', NULL, '$[3][0 to 3]');

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [12, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20]
            ]';
SELECT JSON_VALUE(@json, '$[0][1 to 2].key1');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_QUERY(@json, '$.A[-2][-3 to -1]');

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_EXTRACT(@json, '$[0 to 3][2]');
SELECT JSON_EXTRACT(@json, '$[3][3][last-1 to last]');
SELECT JSON_EXTRACT(@json, '$[3][3][-2 to -1]');

--echo # Checking errors

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_CONTAINS_PATH(@json,'one', '$[3][0 to 3]');

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_CONTAINS(@json, '$[3][0 to 3]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, 13, {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_ARRAY_INSERT(@json, '$.A[0 to last-1]', 5);

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, 13, {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_ARRAY_APPEND(@json, '$.A[*]', 7);

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [12, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20]
            ]';
SELECT JSON_SET(@json, '$[0][1 to 2].key1', 1);

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_REPLACE(@json, '$[1][last-2 to last]', 4);

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_REMOVE(@json, '$[1][-6 to last-2]');

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';
SELECT JSON_KEYS(@json, '$.A[8][1 to 3]');

--echo #
--echo # MDEV-28075: JSON_VALUE returns first value from array not from range
--echo #

SET @json1= '[
             [{"key1": "value1"}, {"key2": "value2"}],
             [{"key3": "value3"}, {"key1": "value4"}],
             [{"key1": "value5"}, {"key4": "value6"}, {"key1": "value7"}]
            ]';
SELECT JSON_VALUE(@json1, '$[2][1 to 2].key1');


SET @json= '[
             [1.1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [11, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20]
            ]';
SELECT JSON_VALUE(@json, '$[*][0]');
SELECT JSON_VALUE(@json, '$[2 to 3][0]');

--echo #
--echo # MDEV-28072: JSON_EXTRACT has inconsistent behavior with '0' value in
--echo # json path (when range is used)
--echo #

SET @json= '[ 11, 22 , 33]';
SELECT JSON_EXTRACT(@json, '$[0 to 0]');
SELECT JSON_EXTRACT(@json, '$[0 to -0]');
SELECT JSON_EXTRACT(@json, '$[-0 to 0]');
SELECT JSON_EXTRACT(@json, '$[-0 to -0]');

--echo #
--echo # MDEV-28071: JSON_EXISTS returns always 1 if it is used range notation
--echo # for json path
--echo #

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_EXISTS(@json, '$[2][2][1 to 2]');
SELECT JSON_EXISTS(@json, '$[2][2][4 to 6]');
SELECT JSON_EXISTS(@json, '$[2][2][1 to 4]');


--echo #
--echo # MDEV-28326: Server crashes in json_path_parts_compare
--echo #

SELECT * FROM JSON_TABLE('{"foo":["bar","qux"]}','$**.*[0]' COLUMNS(col1 CHAR(8) PATH '$[0]')) AS jt;

--echo #
--echo # MDEV-29212: json_overlaps() does not check nested key-value pair correctly
--echo #

SET @json1 = '{"kk":{"k1":"v1","k2":"v2"}}';
SET @json2 = '{"kk":{"k1":"v1","k2":"v2","k3":"v3"}}';
SELECT JSON_OVERLAPS(@json2, @json1);
SELECT JSON_OVERLAPS(@json1, @json2);

--echo #
--echo # MDEV-30304: Json Range only affects first row of the result set
--echo #

CREATE TABLE t1 ( j JSON );
 
INSERT INTO t1 (j) VALUES ('[{"key1": 1, "key2": 1}, {"key3": 1, "key4": 1}]');
INSERT INTO t1 (j) VALUES ('[{"key1": 2, "key2": 2}, {"key3": 2, "key4": 2}, {"key5": 2, "key6": 2}]');
INSERT INTO t1 (j) VALUES ('[{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}, {"key5": 3}]');

SELECT JSON_EXTRACT(j, '$[0 to 1]')  FROM t1 ;
SELECT JSON_EXTRACT(j, '$[*]')  FROM t1 ;

DROP TABLE t1;

--echo #
--echo # MDEV-29381: JSON paths containing dashes are reported as syntax errors in procedures
--echo #

SELECT JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"');
SELECT JSON_EXTRACT('{ "my-key": 1 }', '$.my-key');

--echo #
--echo # End of 10.9 Test
--echo #

--echo # Beginning of 11.1 test

--echo #
--echo # MDEV-27128: Implement JSON Schema Validation FUNCTION
--echo #

--echo # Checking annotations

SET @schema_number= '{
                    "title" : "This is title 1",
                    "description":"this is description 1",
                    "$comment":"This is comment 1",
                    "type":"number",
                    "deprecated":true,
                    "readOnly":true,
                    "writeOnly":false,
                    "example":[2],
                    "default":4,
                    "$schema": "https://json-schema.org/draft/2019-09/json-schema-validation.html#rfc.section.9.5"
                  }';
SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"');
SELECT JSON_SCHEMA_VALID(@schema_number, '2');

--echo # Checking empty schema with empty json document

SET @schema= '{}';

SELECT JSON_SCHEMA_VALID(@schema, '');
SELECT JSON_SCHEMA_VALID(@schema, '{}');
SELECT JSON_SCHEMA_VALID(@schema, '[]');
SELECT JSON_SCHEMA_VALID(@schema, 'null');
SELECT JSON_SCHEMA_VALID(@schema, 'true');
SELECT JSON_SCHEMA_VALID(@schema, 'false');

--echo # Checking scalar against json schema

--echo # Checking boolean and null

SET @schema_true= '{ "type": "boolean"}';
SELECT JSON_SCHEMA_VALID(@schema_true, 'true');
SELECT JSON_SCHEMA_VALID(@schema_true, 'false');
SELECT JSON_SCHEMA_VALID(@schema_true, 'null');

SET @schema_true= '{ "type": "boolean",
                     "const":"false"}';
SELECT JSON_SCHEMA_VALID(@schema_true, 'true');
SET @schema_true= '{ "type": "boolean",
                     "enum":[true, null, false]}';
SELECT JSON_SCHEMA_VALID(@schema_true, 'true');
SET @schema_true= '{ "type": "boolean",
                     "enum": [null, false]}';
SELECT JSON_SCHEMA_VALID(@schema_true, 'true');
SET @schema_true= '{ "type": "boolean",
                     "enum": [null, true]}';
SELECT JSON_SCHEMA_VALID(@schema_true, 'true');
SET @schema_true= '{ "type": "boolean",
                     "const":"false"}';
SELECT JSON_SCHEMA_VALID(@schema_true, 'true');

--echo # Type can be more than one

SET @schema= '
              {
                "type":["string", "number","array"]
              }';
SELECT JSON_SCHEMA_VALID(@schema, '{"key1":"val1"}');
SELECT JSON_SCHEMA_VALID(@schema, '"abc"');
SELECT JSON_SCHEMA_VALID(@schema, '3.14');

--echo # Checking number

SET @schema_number= '{
  "maximum":7,
  "minimum": 3,
  "multipleOf":3
}';
SELECT JSON_SCHEMA_VALID(@schema_number, '2');

SET @schema_number= '{
  "type": "number",
  "maximum":13,
  "minimum": 4,
  "multipleOf":3,
  "exclusiveMaximum": 9,
  "exclusiveMinimum":4
}';

SELECT JSON_SCHEMA_VALID(@schema_number, '2');
SELECT JSON_SCHEMA_VALID(@schema_number, '6');
SELECT JSON_SCHEMA_VALID(@schema_number, '9');
SELECT JSON_SCHEMA_VALID(@schema_number, '5');

SET @schema_number= '{
  "type": "number",
  "maximum":100,
  "minimum": 0,
  "enum": [1, 2, "3", [4, 5, 6], {"key1":"val1"}]
}';
SELECT JSON_SCHEMA_VALID(@schema_number, 1);
SELECT JSON_SCHEMA_VALID(@schema_number, 3);

SET @schema_number= '{
                       "type":"number",
                       "maximum":10,
                       "const":2
                     }';
SELECT JSON_SCHEMA_VALID(@schema_number, '3');
SELECT JSON_SCHEMA_VALID(@schema_number, '2');

--echo # Checking string

--echo # checking format keyword. (not validating for now)

SET @schema_string= '{
  "type": "string",
  "format":"date-time"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date-time"');
SET @schema_string= '{
  "type": "string",
  "format":"date"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date"');
SET @schema_string= '{
  "type": "string",
  "format":"time"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_time"');
SET @schema_string= '{
  "type": "string",
  "format":"duration"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_duration"');
SET @schema_string= '{
  "type": "string",
  "format":"email"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_email"');
SET @schema_string= '{
  "type": "string",
  "format":"idn-email"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-email"');
SET @schema_string= '{
  "type": "string",
  "format":"hostname"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_hostname"');
SET @schema_string= '{
  "type": "string",
  "format":"idn-hostname"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-hostname"');
SET @schema_string= '{
  "type": "string",
  "format":"ipv4"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv4"');
SET @schema_string= '{
  "type": "string",
  "format":"ipv6"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv6"');
SET @schema_string= '{
  "type": "string",
  "format":"uri"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri"');
SET @schema_string= '{
  "type": "string",
  "format":"uri-reference"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri-reference"');
SET @schema_string= '{
  "type": "string",
  "format":"iri"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri"');
SET @schema_string= '{
  "type": "string",
  "format":"iri-reference"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri-reference"');
SET @schema_string= '{
  "type": "string",
  "format":"uuid"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uuid"');
SET @schema_string= '{
  "type": "string",
  "format":"json-pointer"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_json-pointer"');
SET @schema_string= '{
  "type": "string",
  "format":"relative-json-pointer"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_relative-json-pointer"');
SET @schema_string= '{
  "type": "string",
  "format":"regex"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"not_regex"');

--echo # Validating other string keywords

SET @schema_string= '{
  "type": "string",
  "maxLength":7,
  "minLength": 4
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"');

SET @schema_string= '{
  "type": "string",
  "maxLength": 10,
  "minLength": 8
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"');
SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"');

SET @schema_string= '{
  "type": "string",
  "maxLength": 10,
  "minLength": 3,
  "const": "foobar"
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"');

SET @schema_string= '{
  "type": "string",
  "enum": ["red", "green", "blue"]
}';
SELECT JSON_SCHEMA_VALID(@schema_string, '"green"');
SELECT JSON_SCHEMA_VALID(@schema_string, '"orange"');

SET @string_schema= '{
                       "type":"string",
                       "pattern":"ab+c"
                     }';
SELECT JSON_SCHEMA_VALID(@string_schema, '"abc"');

--echo # Validating non-scalar

--echo # Validating array

SET @schema_array= '{"type":"array"}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]');


SET @schema_array= '{"type":"array",
                     "maxItems": 4,
                     "minItems": 2}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1]');
SET @schema_array= '{"maxItems": 4,
                     "minItems": 2}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2]');

SET @schema_array= '{
                     "type":"array",
                     "items": {"type":"number"},
                     "maxItems": 4,
                     "minItems": 2}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, "foobar"]');

SET @schema_array= '{"type":"array",
                     "maxItems": 4,
                     "minItems": 2,
                     "const": [1, 2, 3, 4]}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, "foobar"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4]');

SET @schema_array= '{"type":"array",
                     "enum":[[1,2,3], [4,5,6], [7,8,9]]}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[4,5,6]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1,5,7]');

SET @schema_array= '{
                       "type": "array",
                       "uniqueItems":true
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 2.0]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4.0]');

SET @schema_array= '{
                      "type": "array",
                      "contains": {
                                    "type": "number"
                                  },
                      "minContains": 2,
                      "maxContains": 3
}';
SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3, 4]');
SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ]
                    }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[2, "string1",2]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "items":true
                    }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]');
SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "items":false
                    }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]');

--echo # Checking objects

SET @schema_object= '{"type":"object",
                       "properties":
                        {
                          "number1":{
                                      "type":"number",
                                      "maximum":12,
                                      "minimum":1
                                    },
                          "string1": {
                                      "type":"string",
                                      "maxLength":10,
                                      "minLength": 4
                                     },
                          "array1": {"type":"array",
                                     "maxItems": 4,
                                     "minItems": 2}
                        }
                     }';
SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3,4,5]}');

SET @schema_obj= '{
                       "type": "object",
                       "properties": {
                                       "number1":{"type":"number"},
                                       "string1":{"type":"string"},
                                       "array1":{"type":"array"}
                                     },
                        "dependentRequired": {
                                                "number1":["string1"]
                                              }
                     }';
SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2, "string1":"abc"}');
SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2}');

SET @schema_obj= '{"type":"object",
                       "properties":
                        {
                          "number1":{
                                      "type":"number",
                                      "maximum":12,
                                      "minimum":1
                                    },
                           "key1" : {
                                      "type":"object",
                                      "properties": {
                                                       "key2" :{
                                                                  "type":"string"
                                                               }
                                                    }
                                    }
                        },
                     "enum": [{"number1":3, "key1":{"key2":"string1"}}, {"number1":5, "key1":{"key2":"string3"}}, {"number1":7, "key1":{"key2":"string5"}}]
                     }';
SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":1}}');
SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string1"}}');
SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string7"}}');

SET @schema_obj= '{"type":"object",
                       "properties":
                        {
                          "number1":{
                                      "type":"number",
                                      "maximum":12,
                                      "minimum":1
                                    },
                           "obj1" : {
                                      "type":"object",
                                      "properties": {
                                                       "obj1_1":{
                                                                  "type":"string"
                                                                },
                                                       "obj1_2": {
                                                                   "type":"array"
                                                                 }
                                                    }
                                    },
                           "obj2" : {
                                      "type":"object",
                                      "properties" : {
                                                       "obj2_1":{
                                                                  "type":"number"
                                                                }
                                                     }
                                    }
                        },
                        "required":["number1", "obj2"]
                     }';
SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}');
SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}');

SET @schema_obj= '{"type":"object",
                       "properties":
                        {
                          "number1":{
                                      "type":"number",
                                      "maximum":12,
                                      "minimum":1
                                    },
                           "obj1" : {
                                      "type":"object",
                                      "properties": {
                                                       "obj1_1":{
                                                                  "type":"string"
                                                                },
                                                       "obj1_2": {
                                                                   "type":"array"
                                                                 }
                                                    }
                                    },
                           "obj2" : {
                                      "type":"object",
                                      "properties" : {
                                                       "obj2_1":{
                                                                  "type":"number"
                                                                }
                                                     }
                                    }
                        },
                        "required":["number1", "obj2"],
                        "const": {"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}');
SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}');

SET @schema_obj= '{"type":"object",
                       "properties":
                        {
                          "number1":{
                                      "type":"number",
                                      "maximum":12,
                                      "minimum":1
                                    },
                           "obj1" : {
                                      "type":"object",
                                      "properties": {
                                                       "obj1_1":{
                                                                  "type":"string"
                                                                },
                                                       "obj1_2": {
                                                                   "type":"array"
                                                                 }
                                                    }
                                    },
                           "obj2" : {
                                      "type":"object",
                                      "properties" : {
                                                       "obj2_1":{
                                                                  "type":"number"
                                                                }
                                                     }
                                    }
                        },
                        "maxProperties": 5,
                        "minProperties":2
                  }';
SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}');
SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "number4":4, "number5":5, "number6":6}');

SET @schema_obj= '{"type":"object",
                       "properties":
                        {
                          "number1":{
                                      "type":"number",
                                      "maximum":12,
                                      "minimum":1
                                    },
                           "obj1" : {
                                      "type":"object",
                                      "properties": {
                                                       "obj1_1":{
                                                                  "type":"string"
                                                                },
                                                       "obj1_2": {
                                                                   "type":"array"
                                                                 }
                                                    }
                                    }
                        },
                        "maxProperties": 3,
                        "minProperties":1,
                        "additionalProperties":false
                    }';
SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":"string2"}');

--echo # Demonstrating that irrelavent keywords for a type and irrelavent type
--echo # are ignored, and none of the keywords are mandatory, including "type".

SET @schema_properties= '{
                           "properties" : {
                                            "number1":{ "maximum":10 },
                                            "string1" : { "maxLength": 3}
                                          }
                         }';
SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":25, "string1":"ab" }');
SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":10, "string1":"ab" }');

SET @schema_properties= '{
                           "properties" : {
                                            "number1":{ "maximum":10 },
                                            "string1" : { "maxLength": 3},
                                            "obj1" : {
                                                       "properties":{
                                                                      "number2": {"minimum":8},
                                                                      "array2": {"uniqueItems":true}
                                                                    }
                                                     }
                                          }
                         }';
SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":2, "array2":[1,2,3]} }');
SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3]} }');
SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"array2":[1,2,3]} }');
SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3,2]} }');

SET @schema_num= '{
                   "maximum":10,
                   "minimum":2
                  }';
SELECT JSON_SCHEMA_VALID(@schema_num, '5');
SELECT JSON_SCHEMA_VALID(@schema_num, '"abc"');

SET @schema_str= '{
                   "maxLength":5,
                   "minLength":2,
                   "pattern":"a+bc"
                  }';
SELECT JSON_SCHEMA_VALID(@schema_str, '"abc"');
SELECT JSON_SCHEMA_VALID(@schema_str, '"abcadef"');
SELECT JSON_SCHEMA_VALID(@schema_str, '"bc"');

SET @schema_arr= '{
                    "uniqueItems":true,
                    "items":{"type":"string"},
                    "maximum":10
                  }';
SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "cde"]');
SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "abc"]');
SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", 1]');

SET @schema_const1= '{"const":2}';
SELECT JSON_SCHEMA_VALID(@schema_const1, '2');
SELECT JSON_SCHEMA_VALID(@schema_const1, '"abc"');

SET @schema_const2= '{"const":true}';
SELECT JSON_SCHEMA_VALID(@schema_const2,'true');
SELECT JSON_SCHEMA_VALID(@schema_const2,'false');

SET @schema_enum='{"enum":[1,2,3,"abc", [4,5,6]]}';
SELECT JSON_SCHEMA_VALID(@schema_enum,'[4,5,6]');
SELECT JSON_SCHEMA_VALID(@schema_enum,'4');
SELECT JSON_SCHEMA_VALID(@schema_enum,'"abc"');

SET @schema_required='{"required":["num1","str1", "arr1"]}';
SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "arr1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@schema_required,'"abcd"');

SET @schema_dep_required='{
                            "dependentRequired": {
                                                   "num1":["num2","num3"],
                                                   "str1":["str2","str3"]
                                                 }
                          }';
SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "str1":"abcd"}');
SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "arr1":[1,2,3]}');

--echo # Checking syntax error
SET @invalid_schema= '{"type":"object"
                           "properties":{
                                          "number1": {"type":"number"},
                                          "obj2": {"type":"object",
                                                   "properties": {
                                                                  "key1": {"type":"number"}
                                                                 }
                                                   }
                                        }
                           }';
SELECT JSON_SCHEMA_VALID(@invalid_schema, '{"number1":3, "obj2":{"key1":3}}');

SET @invalid_json= '{"type":"array",
                     "maxItems": 4,
                     "minItems": 2,
                     "const": [1, 2, 3, 4]}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4');

SET @schema_string= '{
                       "type": "string",
                       "maxLength":-2
                    }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_string, '"abcxyz"');

SET @schema_number= '{
                       "type": "number",
                       "multipleOf":-3
                    }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_number, '3');

SET @schema_object= '{
                       "type": "object",
                       "properties":{"num1":{"type":"number"}},
                       "required":{}
                    }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_object, '{"num1":2}');

SET @schema_string= '{
                       "type": "string",
                       "maxLength":-10
                    }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_string, '"str1"');

SET @schema_number= '{"type":"numberz"}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"');

--echo # Using JSON_SCHEMA_VALID() as a constraint validation to insert into table

CREATE TABLE str_table (val_str JSON CHECK(JSON_SCHEMA_VALID('{
                                                                "type":"string",
                                                                "maxLength":5,
                                                                "minLength":2,
                                                                "enum":["ab", "cd", "abc", "def", "abcdef"]
                                                              }', val_str)));
CREATE TABLE num_table(val_num JSON CHECK(JSON_SCHEMA_VALID('{
                                                              "type":"number",
                                                              "minimum":10,
                                                              "maximum":30,
                                                              "exclusiveMinimum":11,
                                                              "exclusiveMaximum":29,
                                                              "multipleOf":5,
                                                              "const":15
                                                              }', val_num)));
CREATE TABLE true_table(val_true JSON CHECK(JSON_SCHEMA_VALID('{
                                                                "type":"boolean",
                                                                "enum":[true, false, null]
                                                               }', val_true)));
CREATE TABLE false_table (val_false JSON CHECK(JSON_SCHEMA_VALID('{
                                                                   "type":"boolean"
                                                                  }', val_false)));
CREATE TABLE null_table (val_null JSON CHECK(JSON_SCHEMA_VALID('{
                                                                 "type":"null"
                                                                }', val_null)));
CREATE TABLE arr_table (val_arr JSON CHECK(JSON_SCHEMA_VALID('{
                                                               "type":"array",
                                                               "uniqueItems":true,
                                                               "maxItems":5,
                                                               "minItems":1,
                                                               "items":true,
                                                               "prefixItems":[{"type":"number"}]
                                                               }', val_arr)));
CREATE TABLE obj_table(val_obj JSON CHECK(JSON_SCHEMA_VALID('{
                                                               "type":"object",
                                                               "properties": {
                                                                               "number1":{
                                                                                            "type":"number",
                                                                                            "maximum":5,
                                                                                            "const":4
                                                                                          },
                                                                                "string1":{
                                                                                            "type":"string",
                                                                                            "maxLength":5,
                                                                                            "minLength":3
                                                                                          },
                                                                                "object1":{
                                                                                             "type":"object",
                                                                                             "properties":{
                                                                                                            "key1": {"type":"string"},
                                                                                                            "key2":{"type":"array"},
                                                                                                            "key3":{"type":"number", "minimum":3}
                                                                                                          },
                                                                                              "dependentRequired": { "key1":["key3"] }
                                                                                          }
                                                                             },
                                                               "required":["number1","object1"]
                                                             }', val_obj)));

INSERT INTO str_table VALUES ('"ab"'), ('"cd"'), ('"abc"'), ('"def"');
--error ER_CONSTRAINT_FAILED
INSERT INTO str_table VALUES ('"feb"');
--error ER_CONSTRAINT_FAILED
INSERT INTO str_table VALUES('"abcdef"');
--error ER_CONSTRAINT_FAILED
INSERT INTO str_table VALUES('"fedcba"');
SELECT * FROM str_table;


INSERT INTO num_table values('15');
--error ER_CONSTRAINT_FAILED
INSERT INTO num_table values('25');
SELECT * FROM num_table;

INSERT INTO true_table VALUES ('true');
SELECT * FROM true_table;

INSERT INTO false_table VALUES('false');
SELECT * FROM false_table;

INSERT INTO arr_table VALUES ('[10, 2, "abc"]');
INSERT INTO arr_table VALUES('[100]');
--error ER_CONSTRAINT_FAILED
INSERT INTO arr_table VALUES ('["str1", 2, "abc", 2.0]');
SELECT * FROM arr_table;

INSERT INTO obj_table VALUES('{"number1":4, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}');
--error ER_CONSTRAINT_FAILED
INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}');
--error ER_CONSTRAINT_FAILED
INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd"}');
--error ER_CONSTRAINT_FAILED
INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"]}');
SELECT * FROM obj_table;

DROP TABLE str_table, num_table, true_table, false_table, null_table, arr_table, obj_table;

--echo # array validation

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "additionalItems" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"},
                        "items":{"type":"array"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "2", "string"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], [1,2]]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "additionalItems" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"},
                        "items": true
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "additionalItems" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"},
                        "items": false
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "additionalItems" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"},
                        "items": [{"type":"string"}]
                        }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]');

--echo # Removing items

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "additionalItems" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "additionalItems" : true,
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "additionalItems" : false,
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]');

--echo # Using items in place of additionalItems

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "items" : false,
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]');
SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "items" : true,
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "items" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "items" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]');

--echo # Removing items and additionalItems both

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "unevaluatedItems": {"type":"number"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2]');


SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "unevaluatedItems": true
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]');

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ],
                        "unevaluatedItems": false
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]');

--echo # Removing items, additionalItems, unevaluatedItems

SET @schema_array= '{
                       "type": "array",
                       "prefixItems": [
                                       { "type": "number", "maximum": 10, "minimum":3},
                                       { "type": "string" }
                                       ]
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]');


--echo # Removing prefixItems


SET @schema_array= '{
                       "type": "array",
                       "items": { "type": "number", "maximum": 10, "minimum":3},
                        "additionalItems" : {"type":"number"},
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]');


SET @schema_array= '{
                       "type": "array",
                       "items": { "type": "number", "maximum": 10, "minimum":3},
                        "additionalItems" : {"type":"string"},
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]');

SET @schema_array= '{
                       "type": "array",
                       "items": true,
                        "additionalItems" : {"type":"string"},
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]');

SET @schema_array= '{
                       "type": "array",
                       "items": false,
                        "additionalItems" : {"type":"string"},
                        "unevaluatedItems": {"type":"string"}
                        }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[]');

--echo # Removing prefixItems and additionalItems

SET @schema_array= '{
                      "type": "array",
                      "items": {"type":"string"},
                       "unevaluatedItems": {"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]');
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]');

SET @schema_array= '{
                      "type": "array",
                      "items": {"type":"string"},
                       "unevaluatedItems": {"type":"number"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]');

--echo # removing prefixItems, additionalItems and unevaluatedItems

SET @schema_array= '{
                      "type": "array",
                      "items": {"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]');
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]');

--echo # Checking that additionalItems alone has no effect on schema without items/prefixItems
--echo # regardless existence of unevaluatedItems

SET @schema_array= '{
                      "type": "array",
                      "additionalItems": {"type":"string"},
                       "unevaluatedItems": {"type":"number"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]');

SET @schema_array= '{
                      "type": "array",
                      "additionalItems": true,
                       "unevaluatedItems": {"type":"number"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]');

SET @schema_array= '{
                      "type": "array",
                      "additionalItems": false,
                       "unevaluatedItems": {"type":"number"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]');


--echo # checking that unevaluatedItems alone can have effect on schema validation

SET @schema_array= '{
                      "type": "array",
                       "unevaluatedItems": {"type":"number"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1]');

SET @schema_array= '{
                      "type": "array",
                       "unevaluatedItems": {"type":"number"}
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]');

SET @schema_array= '{
                      "type": "array",
                       "unevaluatedItems": false
                     }';
SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[]');
SELECT JSON_SCHEMA_VALID(@schema_array, '[1,2,3]');


--echo # Object validation

SET @property_names= '{
                       "PropertyNames":{
                                          "pattern": "^I_"
                                       }
                    }';
SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "O_ob1":{"key1":"val1"}}');
SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "I_ob1":{"key1":"val1"}}');

--echo # checking that when a match is found in properties or patternProperties, it must validate and
--echo # validation result affects the schema. If key is not found in properties or patternProperties, and
--echo # additionalProperties exists, it must validate regardless of existence or value for unevaluatedProperties
--echo # and the result of validation with additionalProperties affects result of whole schema

SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "additionalProperties":{
                                                 "type":"array",
                                                 "maxItems":5
                                                },
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"string1"}');

--echo # removing patternProperties to check that validation falls back on additionalProperties and
--echo # existence of unevaluatedProperties still does not change anything because of existence of additional
--echo # properties


SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                        "additionalProperties":{
                                                 "type":"array",
                                                 "maxItems":5
                                                },
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}');

--echo # Remvoing additionalProperties to check that validation falls back on unevaluatedProperties

SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":"str"}');

--echo # Removing unevaluatedProperties has no effect on result when  additionalProperties is present

SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "additionalProperties":{
                                                 "type":"array",
                                                 "maxItems":5
                                                }
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"str1"}');

SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "additionalProperties": false
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');

SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "additionalProperties": true
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');


--echo # Checking that in absence of additionalProperties, validation falls back on evaluatedProperties

SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":"str1"}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');

SET @object_schema= '{
                       "type":"object",
                       "properties": {
                                        "key1":{"type":"string"},
                                        "key2":{"type":"number", "maximum":50}
                                     },
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":"str2", "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');

--echo # Properties to check if patternProperties get evaluated but keys not found in patternProperties get evaluated against
--echo # additionalProperties regardless of existence of unevaluatedProperperties

SET @object_schema= '{
                       "type":"object",
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "additionalProperties":{
                                                 "type":"array",
                                                 "maxItems":5
                                                },
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');

SET @object_schema= '{
                       "type":"object",
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                        "additionalProperties":{
                                                 "type":"array",
                                                 "maxItems":5
                                                }
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');

--echo # Checking that in absence of additionalProperties and properties, the keys not found in patternProperties are
--echo # evaluated against unevaluatedProperties
SET @object_schema= '{
                       "type":"object",
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            },
                       "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, "S_":"abc", "some_prop1":"str1"}');

--echo # checking that in absence of properties, additionalProperties and unevaluatedPropoerties, the keys that are
--echo # not found are considered validated.

SET @object_schema= '{
                       "type":"object",
                       "patternProperties": {
                                              "^I_": {"type":"number", "maximum":100},
                                              "^S_" : {"type":"string", "maxLength":4}
                                            }
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');

--echo # checking that additionalProperties are evaluated in absence of patternProperties and properties, regardless
--echo # of presence of unevaluatedProperties

SET @object_schema= '{
                       "type":"object",
                        "additionalProperties":{
                                                 "type":"array",
                                                 "maxItems":5
                                                },
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}');

SET @object_schema= '{
                       "type":"object",
                        "additionalProperties":{
                                                 "type":"array",
                                                 "maxItems":5
                                                }
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}');

--echo # Checking that in absence of properties, patternProperties and additionalProperties, validation falls back on unevaluatedProperties

SET @object_schema= '{
                       "type":"object",
                        "unevaluatedProperties":{"type":"string"}
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}');

SET @object_schema= '{
                       "type":"object",
                        "unevaluatedProperties": false
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{}');

SET @object_schema= '{
                       "type":"object",
                        "unevaluatedProperties": true
                     }';
SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}');
SELECT JSON_SCHEMA_VALID(@object_schema, '{}');

SET @json_schema_dependent_schemas='{
  "type": "object",
  "properties": {
    "str1": { "type": "string" },
    "num1": { "type": "number" }
  },

  "required": ["str1"],

  "dependentSchemas": {
    "num1": {
      "properties": {
        "str2": { "type": "string" }
      },
      "required": ["str2"]
    }
  }
}';
SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str", "num1":4}');
SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str"}');

--echo # Validating logic

SET @not_schema= '{
                       "not":{
                              "maximum": 4
                              }
                    }';
SELECT JSON_SCHEMA_VALID(@not_schema, '{"I_int1":3, "O_ob1":{"key1":"val1"}}');
SELECT JSON_SCHEMA_VALID(@not_schema, '3');
SELECT JSON_SCHEMA_VALID(@not_schema, '10');

SET @not_schema= '{
                       "not":{
                              "properties": {
                                              "num1" : {"type":"number", "maximum":5},
                                              "string1" : { "maxLength": 3}
                                            }
                              }
                    }';
SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":10, "string2":"abcd"}');
SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":2, "string2":"abcd"}');

SET @any_of_schema= '{
                       "anyOf":[{
                                  "properties": {
                                                "num1" : {"type":"number", "maximum":5},
                                                "string1" : { "maxLength": 3}
                                                }
                                },
                                {
                                  "properties":{
                                                 "num1" : {"type":"number", "maximum": 1},
                                                 "string1" : { "maxLength":5}
                                               }
                                }
                               ]
                    }';
SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abcd"}');
SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abc"}');

SET @any_of_schema= '{
                      "anyOf": [
                                 {"type":"number", "maximum":5},
                                 {"type":"string"}
                               ]
                    }';
SELECT JSON_SCHEMA_VALID(@any_of_schema, '2');
SELECT JSON_SCHEMA_VALID(@any_of_schema, '6');
SELECT JSON_SCHEMA_VALID(@any_of_schema, '[1, 2, 3]');

SET @one_of_schema= '{
                       "oneOf":[{
                                  "properties": {
                                                "num1" : {"type":"number", "maximum":5},
                                                "string1" : { "maxLength": 3}
                                                }
                                },
                                {
                                  "properties":{
                                                 "num1" : {"type":"number", "maximum": 1},
                                                 "string1" : { "maxLength":5}
                                               }
                                }
                               ]
                    }';
SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abcd"}');
SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abc"}');

SET @one_of_schema= '{
                      "oneOf": [
                                 {"type":"number", "maximum":5},
                                 {"type":"number", "maximum":3}
                               ]
                    }';
SELECT JSON_SCHEMA_VALID(@one_of_schema, '2');
SELECT JSON_SCHEMA_VALID(@one_of_schema, '4');

SET @all_of_schema= '{
                       "allOf":[{
                                  "properties": {
                                                "num1" : {"type":"number", "maximum":5},
                                                "string1" : { "maxLength": 3}
                                                }
                                },
                                {
                                  "properties":{
                                                 "num1" : {"type":"number", "maximum": 1},
                                                 "string1" : { "maxLength":5}
                                               }
                                }
                               ]
                    }';
SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":2, "string1":"abcd"}');

SET @all_of_schema= '{
                       "allOf":[
                                 {
                                    "properties":{
                                                   "num1": {"type":"number"},
                                                   "string1": {"type":"string"}
                                                 }
                                 },
                                 {
                                    "properties":{
                                                   "num1": {"maximum":10},
                                                   "string1": {"maxLength":5}
                                                 }
                                 }
                               ]
                     }';
SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"abc"}');
SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"foobar"}');

SET @condition_schema= '{
                          "if" : {"maximum":30, "multipleOf":3},
                          "then":{"minimum":5},
                          "else":{"minimum":10}
                        }';
SELECT JSON_SCHEMA_VALID(@condition_schema, '6');
SELECT JSON_SCHEMA_VALID(@condition_schema, '4');
SELECT JSON_SCHEMA_VALID(@condition_schema, '13');

SET @condition_schema= '{
                          "if" : {"maximum":30, "multipleOf":3}
                        }';
SELECT JSON_SCHEMA_VALID(@condition_schema, '6');
SELECT JSON_SCHEMA_VALID(@condition_schema, '7');

SET @condition_schema= '{
                          "then":{"minimum":5},
                          "else":{"minimum":10}
                        }';
SELECT JSON_SCHEMA_VALID(@condition_schema, '4');
SELECT JSON_SCHEMA_VALID(@condition_schema, '11');

--echo # Checking unevaluatedProperperties with logical properties

SET @all_of_unevaluated='{
  "allOf": [
    {
      "type": "object",
      "properties": {
        "street_address": { "type": "string" },
        "city": { "type": "string" },
        "state": { "type": "string" }
      },
      "required": ["street_address", "city", "state"]
    }
  ],

  "properties": {
    "type": { "enum": ["residential", "business"] }
  },
  "required": ["type"],
  "unevaluatedProperties": false
}';
SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{
   "street_address": "1600 Pennsylvania Avenue NW",
   "city": "Washington",
   "state": "DC",
   "type": "business"
}');

SET @all_of_unevaluated='{
  "allOf": [
    {
      "type": "object",
      "properties": {
        "street_address": { "type": "string" },
        "city": { "type": "string" },
        "state": { "type": "string" }
      },
      "required": ["street_address", "city", "state"]
    }
  ],

  "unevaluatedProperties": {"type":"number"}
}';
SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{
   "street_address": "1600 Pennsylvania Avenue NW",
   "city": "Washington",
   "state": "DC",
   "type": "business"
}');

SET @any_of_unevaluated='{
  "anyOf": [
    {
      "type": "object",
      "properties": {
        "street_address": { "type": "string" },
        "city": { "type": "string" },
        "state": { "type": "string" }
      },
      "required": ["street_address", "city", "state"]
    }
  ],

  "unevaluatedProperties": {"type":"number"}
}';
SELECT JSON_SCHEMA_VALID(@any_of_unevaluated, '{
   "street_address": "1600 Pennsylvania Avenue NW",
   "city": "Washington",
   "state": "DC",
   "type": "business"
}');

SET @all_of_unevaluated='{
  "allOf": [
    {
      "type": "array"
    },
    {
      "maxItems":10
    },
    {
      "prefixItems": [ {"type":"number"}, {"type":"string"}],
      "additionalItems":{"type":"array"}
    }
  ],

  "unevaluatedItems": {"type":"number"}
}';
SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]');

SET @all_of_unevaluated='{
  "anyOf": [
    {
      "type": "array"
    },
    {
      "maxItems":10
    },
    {
      "prefixItems": [ {"type":"number"}, {"type":"string"}],
      "additionalItems":{"type":"array"}
    }
  ],

  "unevaluatedItems": {"type":"number"}
}';
SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]');

SET @all_of_unevaluated='{
  "oneOf": [
    {
      "type": "array"
    },
    {
      "maxItems":10
    },
    {
      "prefixItems": [ {"type":"number"}, {"type":"string"}],
      "additionalItems":{"type":"array"}
    }
  ],

  "unevaluatedItems": {"type":"number"}
}';
SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]');

--echo # Media string

SET @schema_media_string= '{
  "type": "string",
  "contentMediaType": "text/html"
}';
SELECT JSON_SCHEMA_VALID(@schema_media_string, '"str1"');

SET @schema_reference= '{"$ref": "http://example.com/custom-email-validator.json#"}';
--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED
SELECT JSON_SCHEMA_VALID(@schema_reference, '{}');

SET @schema_reference= '{"$id": "http://example.com/custom-email-validator.json#"}';
--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED
SELECT JSON_SCHEMA_VALID(@schema_reference, '{}');

SET @schema_reference= '{"$anchor": "http://example.com/custom-email-validator.json#"}';
--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED
SELECT JSON_SCHEMA_VALID(@schema_reference, '{}');

SET @schema_reference= '{"$defs": "http://example.com/custom-email-validator.json#"}';
--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED
SELECT JSON_SCHEMA_VALID(@schema_reference, '{}');



--echo #
--echo # MDEV-30795: JSON_SCHEMA_VALID bugs mentioned in comment
--echo #
SET @schema= '{
                     "type":"array",
                     "uniqueItems":true
              }';
SELECT JSON_SCHEMA_VALID(@schema, '[null, null]');

SET @schema_max_items= '{"maxItems":-1}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_max_items, '[]');

SET @schema_min_items= '{"minItems":-1}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_min_items, '[]');

SET @schema_max_properties= '{"maxProperties":-1}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_max_properties, '{}');

SET @schema_min_properties= '{"minProperties":-1}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_min_properties, '{}');

SET @schema_multiple_of= '{"multipleOf":-1}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_multiple_of, '2');

SET @schema_max_contains= '{"maxContains":-1}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_max_contains, '[]');

SET @schema_min_contains= '{"minContains":-1}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_min_contains, '[]');

SET @schema_required='{"type":"object","required":[1,"str1", "str1"]}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}');

--echo #
--echo # MDEV-30977: Additional key values are not validating properly when using
--echo # unevaluatedProperties with properties declared in subschemas
--echo #

SET @unevaluatedProperties_schema= '{
  "allOf": [
    {
      "type": "object",
      "properties": {
        "name": { "type": "string" }
      }
    }
  ],
  "properties": {
    "type": { "enum": ["residential", "business"] }
  },
  "required": ["type"],
  "unevaluatedProperties": false
}';
SELECT JSON_SCHEMA_VALID(@unevaluatedProperties_schema, '{"name": "joe", "type": "business", "dummy" : "hello" }');

--echo #
--echo # MDEV-30995: JSON_SCHEMA_VALID is not validating case sensitive when using regex
--echo #

SET @schema_pattern='{
        "type": "string",
        "pattern": "[A-Z]"
     }';
SELECT JSON_SCHEMA_VALID(@schema_pattern, '"a"');

SET @schema_property_names='{
                       "PropertyNames":{
                                          "pattern": "^I_"
                                       }
                    }';
SELECT JSON_SCHEMA_VALID(@schema_property_names, '{"I_num":4}');
SELECT JSON_SCHEMA_VALID(@schema_property_names, '{"i_num":4}');

SET @schema_pattern_properties= '{
                             "patternProperties": {
                                                    "^I_": {"type":"number", "maximum":100},
                                                    "^S_" : {"type":"string", "maxLength":4}
                                                  }
                          }';
SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"I_": 50}');
SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"I_": 150}');
SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"i_": 50}');
SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"i_": 150}');

--echo #
--echo # MDEV-30690: Server crashed on function JSON_SCHEMA_VALID with incorrect input json schema
--echo #

SET @schema = '{""}';
SELECT JSON_SCHEMA_VALID(@schema, '1');

SET @schema = '{
  "type": "string",
  "format"
  }';
SELECT JSON_SCHEMA_VALID(@schema, '1');

SET @invalid_schema= '{"type":"object"
                           "properties":{
                                          "number1": {"type":"number"},
                                          "obj2": {"type":"object",
                                                   "properties": {
                                                                  "key1": {"type":"number"}
                                                                 }
                                                   }
                                        }
                           }';
SELECT JSON_SCHEMA_VALID(@invalid_schema, '{"number1":3, "obj2":{"key1":3}}');

--echo #
--echo # MDEV-30703: JSON_SCHEMA_VALID : Enum array must have at least one value
--echo #

SET @schema = '{
                 "type":"array",
                  "enum": []
                }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema = '{
                 "type":"number",
                  "enum": [2, 2]
                }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');


--echo #
--echo # MDEV-30704: JSON_SCHEMA_VALID: multipleOf must be greater than zero
--echo #

SET @schema = '{
                  "multipleOf": 0
                }';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema= '{ "maxLength" : -3}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema= '{ "minLength" : -3}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema= '{ "maxProperties" : -3}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema= '{ "minProperties" : -3}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema= '{ "maxItems" : -3}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema= '{ "minItems" : -3}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '2');

SET @schema= '{ "items" : ["str1"]}';
--error ER_JSON_INVALID_VALUE_FOR_KEYWORD
SELECT JSON_SCHEMA_VALID(@schema, '[]');


--echo #
--echo # MDEV-30705: JSON_SCHEMA_VALID: schema with multipleOf for big value always return 1
--echo #
SET @schema = '{
                  "multipleOf": 2
                }';
SELECT JSON_SCHEMA_VALID(@schema, '9007900000000001');
SELECT JSON_SCHEMA_VALID(@schema, '9007900000000060');
SELECT JSON_SCHEMA_VALID(@schema, '9007900000000061');

--echo #
--echo # MDEV-31032: UBSAN|downcast of address X which does not point to an object of type 
--echo #             Item_string' in sql/json_schema.cc
--echo #

SET @old_sql_mode= @@sql_mode;

SET @schema='{ "type":"object","patternProperties": { "^I_": {"type":"number"},"^S_" : {"type":"string"} } }';
SET SESSION sql_mode='empty_string_is_null';
SELECT JSON_SCHEMA_VALID (@schema,'{"key1":"val0","key2":0,"I_int":0,"S_":"abc","prop0":"str0"}');

SET @@sql_mode= @old_sql_mode;

SET @property_names='{ "PropertyNames":{ "pattern": "^I_" } }';
SET GLOBAL sql_mode=17179869183;
SET @@sql_mode=DEFAULT;
SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "I_ob1":{"key1":"val1"}}');

SET @@sql_mode= @old_sql_mode;
set global sql_mode=default;

--echo #
--echo # MDEV-30287: JSON_SCHEMA_VALID returns incorrect result for type=number
--echo #

SET @schema= '{"type":"number"}';

SELECT JSON_SCHEMA_VALID(@schema, '3.14');
SELECT JSON_SCHEMA_VALID(@schema, '0zzzz');
SELECT JSON_SCHEMA_VALID(@schema, '-#');

--echo #
--echo # MDEV-30689: JSON_SCHEMA_VALID for type=array return 1 for any string that starts with '['
--echo #


SET @schema_array= '{"type":"array"}';
SELECT JSON_SCHEMA_VALID(@schema_array, '[');

SELECT JSON_SCHEMA_VALID(repeat('[', 100000), json_object());

SELECT JSON_SCHEMA_VALID(json_object(), repeat('[', 10000000));

--echo #
--echo # MDEV-30677: Incorrect result for "SELECT JSON_SCHEMA_VALID('{}', NULL)"
--echo #
SELECT JSON_SCHEMA_VALID('{}', NULL);
SELECT JSON_SCHEMA_VALID(NULL, '{}');
SELECT JSON_SCHEMA_VALID(NULL, NULL);

--echo # End of 11.1 test