mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-14 06:21:33 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/func_json.test
Rucha Deodhar 2a0fcd3757 MDEV-32854: Make JSON_DEPTH_LIMIT unlimited 
The initial hard capped limit on the depth was 32. It was implemented using
static arrays of relevant type and size 32.
Hence, to implement unlimited depth, dynamic array on mem_root was
implemented which grows by 3200 as needed. Relevant arrays were replaced
with this dynamic array.
2025-07-22 17:32:21 +05:30

4181 lines
150 KiB
Text
Raw Permalink Blame History

--source include/have_innodb.inc
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');
select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z') as exp;
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');
select json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000))) as exp;
select json_array();
select json_array(1);
#enable after fix MDEV-28649
--disable_view_protocol
select json_array(1, "text", false, null);
#enable after fix MDEV-31554
--disable_cursor_protocol
select json_array_append('["a", "b"]', '$', FALSE);
--enable_cursor_protocol
--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');
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x') as exp;
select json_array_insert('true', '$', 1);
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y') as exp;
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"]');
select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]') as exp;
select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}');
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]") as exp;
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]") as exp;
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma") as exp;
select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1") as exp;
select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma") as exp;
select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma") as exp;
select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2") as exp;
select json_contains_path('{ "a": true }', NULL, '$.a' ) as exp;
select json_contains_path('{ "a": true }', 'all', NULL ) as exp;
select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*') as exp;
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1") as exp;
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY") as exp;
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2") as exp;
select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2") as exp;
select json_extract('{"key0":true, "key1":"qwe"}', "$.key1") as exp;
select json_extract(json_object('foo', 'foobar'),'$') as exp;
select json_extract('[10, 20, [30, 40]]', '$[2][*]') as exp;
select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]') as exp;
select json_extract('1', '$') as exp;
select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]') as exp;
select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]') as exp;
select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a') as exp;
#enable after fix MDEV-31554
--disable_cursor_protocol
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word') as exp;
--enable_cursor_protocol
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3) as exp;
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2) as exp;
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word') as exp;
#enable after fix MDEV-31554
--disable_cursor_protocol
select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp;
--enable_cursor_protocol
select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp;
select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]') as exp;
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") as ex;
select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") as ex;
select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]") as ex;
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}');
#enable after fix MDEV-31554
--disable_cursor_protocol
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');
--enable_cursor_protocol
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}') as ex;
select json_keys('{"c1": "value 1", "c1": "value 2"}') as ex;
SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
select json_search(@j, 'one', 'abc') as ex;
select json_search(@j, 'all', 'abc') as ex;
select json_search(@j, 'all', 'abc', NULL, '$[2]') as ex;
select json_search(@j, 'all', 'abc', NULL, '$') as ex;
select json_search(@j, 'all', '10', NULL, '$') as ex;
select json_search(@j, 'all', '10', NULL, '$[*]') as ex;
select json_search(@j, 'all', '10', NULL, '$[*][0].k') as ex;
select json_search(@j, 'all', '10', NULL, '$**.k') as ex;
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' ) as ex from t1;
drop table t1;
#enable after fix MDEV-31554
--disable_cursor_protocol
select json_unquote('"abc"');
select json_unquote('abc');
--enable_cursor_protocol
#
# 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');
#enable after fix MDEV-31554
--disable_cursor_protocol
select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1;
select json_unquote(json_object('bar', c)) as c1 from t1;
select
collation(json_unquote(json_object('bar', c))) as coll_json_unquote,
coercibility(json_unquote(json_object('bar', c))) as coer_json_unquote,
coercibility('bar') as coer_literal
from t1 limit 1;
create table t2 as select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1 limit 0;
show create table t2;
drop table t2;
--enable_cursor_protocol
drop table t1;
select json_object("a", json_object("b", "abcd"));
#enable after fix MDEV-31554
--disable_cursor_protocol
select json_object("a", '{"b": "abcd"}');
--enable_cursor_protocol
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]' ) as ex;
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ) as ex;
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' ) as ex;
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' ) as ex;
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' ) as ex;
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' ) as ex;
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' ) as ex;
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' ) as ex;
select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' ) as ex;
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"') as ex;
select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""') as ex;
select json_value('{"key 1":123}', '$."key 1"') as ex;
select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]") as ex;
select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]") as ex;
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"}') as ex ;
select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') as ex ;
select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') as ex ;
select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') as ex ;
select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex;
#enable after fix MDEV-31554
--disable_cursor_protocol
select json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex;
select json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex;
--enable_cursor_protocol
#
# MDEV-11856 json_search doesn't search for values with double quotes character (")
#
SELECT JSON_search( '{"x": "\\""}', "one", '"') as ex;
SELECT JSON_search( '{"x": "\\""}', "one", '\\"') as ex;
#
# 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=4096;
--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)) as ex;
select json_object("a", repeat('a',1024),"b", repeat('a',1024)) as ex;
--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' as ex;
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true as ex;
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false as ex;
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1 as ex;
select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"') as ex;
#
# MDEV-129892 JSON_EXTRACT returns data for invalid JSON
#
select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo') as ex;
#
# MDEV-13138 JSON_OBJECT returns null with strings containing backticks.
#
SELECT JSON_OBJECT('foo', '`') as ex;
SELECT JSON_OBJECT("foo", "bar`bar") as ex;
#
# MDEV-13324 JSON_SET returns NULL instead of object.
#
#enable after MDEV-32454 fix
--disable_view_protocol
SELECT JSON_SET('{}', '$.age', 87);
--enable_view_protocol
#
# 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' as exp from t1;
drop table t1;
--echo #
--echo # MDEV-16750 JSON_SET mishandles unicode every second pair of arguments.
--echo #
#enable after fix MDEV-31554
--disable_cursor_protocol
SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6) as exp;
SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6) as exp;
SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') as exp;
--enable_cursor_protocol
--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')) as exp;
SELECT CHARSET(JSON_ARRAY()) as exp;
SELECT CHARSET(JSON_OBJECT()) as exp;
--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"}') as exp;
SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]') as exp;
SELECT JSON_MERGE_PATCH(NULL, 'a') as exp;
SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}') as exp;
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_MERGE_PATCH() as exp;
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_MERGE_PATCH('{}') as exp;
SELECT JSON_MERGE_PATCH('{', '[1,2,3]') as exp;
SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,') as exp;
--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
--disable_view_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_view_protocol
--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 # Start of 10.4 tests
--echo #
--echo #
--echo # MDEV-16351 JSON_OBJECT() treats hybrid functions with boolean arguments as numbers
--echo #
--vertical_results
#enable after fix MDEV-28649
--disable_view_protocol
SELECT
JSON_OBJECT("cond", true) AS j1,
JSON_OBJECT("cond", COALESCE(true, false)) j2,
JSON_OBJECT("cond", COALESCE(COALESCE(true, false))) j3;
--enable_view_protocol
--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;
#enable after MDEV-32454 fix
--disable_view_protocol
SELECT JSON_ARRAYAGG(TRUE), JSON_ARRAYAGG(FALSE) FROM t1;
--enable_view_protocol
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')) as exp from t200;
select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t200;
select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t200;
drop table t200;
--echo #
--echo # MDEV-24538: JSON_LENGTH does not return error upon wrong number of parameters
--echo #
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_LENGTH('{"a":"b"}','$','$', 'foo');
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT JSON_LENGTH();
--echo # MDEV-23187: Assorted assertion failures in json_find_path with certain collations
SET @old_collation_connection= @@COLLATION_CONNECTION;
SET COLLATION_CONNECTION= ucs2_unicode_ci;
SELECT JSON_VALUE('["foo"]', '$**[0]') AS f;
SET @@COLLATION_CONNECTION= @old_collation_connection;
--echo #
--echo # MDEV-32587 JSON_VALID fail to validate integer zero in scientific notation
--echo #
# Passing
select JSON_VALID(' {"number": 1E-4}');
select JSON_VALID(' {"number": 0E-4}');
select JSON_VALID(' {"number": 0.0}');
select JSON_VALID(' {"number": 0.1E-4}');
select JSON_VALID(' {"number": 0e-4}');
select JSON_VALID(' {"number": -0E-4}');
select JSON_VALUE(' {"number": 0E-4}', '$.number');
# Failing
select JSON_VALID(' {"number": 00E-4}');
select JSON_VALID(' {"number": 01E-4}');
select JSON_VALID(' {"number": 0E-4.0}');
--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)));
#enable after fix MDEV-31554
--disable_cursor_protocol
#enable after MDEV-32454 fix
--disable_view_protocol
--disable_ps2_protocol
select json_arrayagg(a) from t1;
--enable_ps2_protocol
--enable_view_protocol
--enable_cursor_protocol
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'))) as exp;
SELECT json_object('a', coalesce(json_object('b', 'c'))) as exp;
--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 # MDEV-24784 JSON_ARRAYAGG charset issue
--echo #
--disable_service_connection
set names utf8;
select json_arrayagg('ä'), json_objectagg(1, 'ä');
set names latin1;
select json_arrayagg('ä'), json_objectagg(1, 'ä');
--enable_service_connection
--echo #
--echo # MDEV-32287: JSON_EXTRACT not returning multiple values for same path
--echo #
select JSON_EXTRACT("[1, 2, [30, 40]]", '$[2][1]', '$[2][1]');
--echo #
--echo # MDEV-31402: SIGSEGV in json_get_path_next | Item_func_json_extract::read_json
--echo #
CREATE TABLE t (id CHAR AS (JSON_COMPACT (JSON_EXTRACT(doc,"$._id"))) UNIQUE KEY,doc JSON,CONSTRAINT notnu CHECK (id IS NOT NULL));
--error ER_DATA_TOO_LONG
INSERT INTO t (doc) VALUES ('{ "_id" : { "$oid" : "0ca0b0f0" },"a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" :0} ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] }');
DROP TABLE t;
--echo #
--echo # MDEV-19487: JSON_TYPE doesnt detect the type of String Values (returns NULL) and for Date/DateTime returns "INTEGER"
--echo #
SELECT JSON_TYPE(json_value(JSON_OBJECT("id", 1, "name", 'Monty', "date", Cast('2019-01-01' as Date) ), '$.date')) as x;
--echo #
--echo # MDEV-22141: JSON_REMOVE returns NULL on valid arguments
--echo #
SELECT JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C.D');
--echo #
--echo # MDEV-34143: Server crashes when executing JSON_EXTRACT after setting non-default collation_connection
--echo #
SET @save_collation_connection= @@collation_connection;
SET collation_connection='utf16_bin';
--disable_service_connection
SELECT JSON_EXTRACT('{"a": 1,"b": 2}','$.a');
--enable_service_connection
SET @@collation_connection= @save_collation_connection;
--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 # MDEV-27412: JSON_TABLE doesn't properly unquote strings
--echo #
SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';
SELECT
data
FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;
--echo #
--echo # MDEV-35614 JSON_UNQUOTE doesn't work with emojis
--echo #
SELECT HEX(JSON_UNQUOTE('"\\ud83d\\ude0a"')) as hex_smiley;
set names utf8mb4;
SELECT JSON_UNQUOTE('"\\ud83d\\ude0a"') as smiley;
SELECT JSON_UNQUOTE('"\\ud83d\\ude0a"') = JSON_UNQUOTE('"\\ud83d\\ude0a"') as equal_smileys;
SELECT JSON_UNQUOTE('"\\ud83d\\ude0a"') <= JSON_UNQUOTE('"\\ud83d\\ude0a"') as less_or_equal_smileys;
set @v='{ "color":"😊" }';
select @v as v, collation(@v) as collation_v;
select json_valid(@v) as valid;
select json_extract(@v,'$.color') as color_extraction, collation(json_extract(@v,'$.color')) as color_extraction_collation;
select json_unquote(json_extract(@v,'$.color')) as unquoted, collation(json_unquote(json_extract(@v,'$.color'))) as unquoted_collation;
SELECT JSON_UNQUOTE('"\\uc080\\ude0a"') as invalid_utf8mb4;
show warnings;
--echo #
--echo # End of 10.6 tests
--echo #
--echo #
--echo # MDEV-31147 json_normalize does not work correctly with MSAN build
--echo #
CREATE TABLE t1 (val JSON);
ALTER TABLE t1 ADD COLUMN normalized_json JSON AS (JSON_NORMALIZE(val));
INSERT INTO t1 (val) VALUES ('15');
SELECT * FROM t1;
DROP TABLE t1;
--echo #
--echo # End of 10.8 tests
--echo #
--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"
}') as exp;
SELECT JSON_OVERLAPS('{
"A": 1,
"B": "string1"
}',
'{
"A": 2,
"B": "string2"
}') as exp;
--echo # Comparing nested object with other nested object
SELECT JSON_OVERLAPS('{
"A": 1,
"B": {"C":2}
}',
'{
"A": 2,
"B": {"C":1}
}') as exp;
SELECT JSON_OVERLAPS('{
"A": 1,
"B": {"C":2}
}',
'{
"A": 2,
"B": {"C":2}
}') as exp;
SELECT JSON_OVERLAPS('{
"A": {
"B": true
}
}',
'{
"A": {
"B": true,
"C": false
}
}') as exp;
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}',
'{"C":3, "B":{"E":5, "D":4}}') as exp;
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "B":{"E":5, "D":4}}') as exp;
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "B":{"E":[5, 6, 7], "D":4}}') as exp;
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "B":{"E":[7, 6 ,5], "D":4}}') as exp;
SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
'{"C":3, "F":{"E":[5, 6, 7], "D":4}}') as exp;
--echo # Comparing array with array (non-nested)
SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
'[3, 4, 1]') as exp;
SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
'[3, 4, 5]');
SELECT JSON_OVERLAPS('[1,2,3]','[]') as exp;
--echo # Comparing nested arrays
SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
'[3, 4, [1]]') as exp;
SELECT JSON_OVERLAPS('[1, 2, [true, false], null]',
'[[1], [true, false]]') as exp;
SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]') as exp;
--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}') as exp;
SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]',
'{"A": 1}') as exp;
SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]',
'{"A": 1}') as exp;
SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
'{"A": 1, "B": 2}') as exp;
SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
'{"A": 1, "B": 3}') as exp;
-- echo # Comparing nested array with object
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
'{"A": 1, "B": 2}') as exp;
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
'{"A": 1, "B": 3}') as exp;
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
'{"A": 1}') as exp;
--echo # Comparing array with nested object
SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
'{"A": 1, "B": {"C": 12}}') as exp;
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
'{"A": 1, "B": {"C": 12}}') as exp;
--echo # Comparing nested array with nested objects
SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
'{"A": 1, "B":{"C": 12}}') as exp;
SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
'{"A": 1, "B": {"C": 12}}') as exp;
--echo # Comparing object with array
SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
'[1, 2, true, false, {"A": 1, "B": 2}]') as exp;
SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
'[1, 2, true, false, {"A": 1, "B": 3}]') as exp;
SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]') as exp;
SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}',
'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]') as exp;
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}]}]') as exp;
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}]}]') as exp;
--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}]]') as exp;
--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 # MDEV-23187: Assorted assertion failures in json_find_path with certain collations
--echo #
SET @save_collation_connection= @@collation_connection;
SET @json='{ "A": [ [{"k":"v"},[1]],true],"B": {"C": 1} }';
SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1');
SET @json='{ "A": [ [{"k":"v"},[1]],true],"B": {"C": 1} }';
SET collation_connection='ucs2_bin';
SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1');
SET @json='{ "A": [ [{"k":"v"},[15]],true],"B": {"C": 1} }';
SET sql_mode=0,character_set_connection=utf32;
SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1');
SET @json='{ "A": [ [{"k":"v"},[15]],true],"B": {"C": 1} }';
SET sql_mode=0,character_set_connection=utf32;
SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1');
SET @@collation_connection= @save_collation_connection;
--echo #
--echo # End of 10.9 Test
--echo #
--echo #
--echo # MDEV-32007: JSON_VALUE and JSON_EXTRACT doesn't handle dash (-)
--echo # as first character in key
--echo #
CREATE TEMPORARY TABLE IF NOT EXISTS jsonTest AS
SELECT '{ "-1234" : "something",
"12-34" : "else",
"1234-" : "and",
"1234" : "match" }' AS 'message';
SELECT JSON_SEARCH(message, 'one', 'something') AS t1_path,
JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'something'))) AS t1_result,
JSON_SEARCH(message, 'one', 'else') AS t2_path,
JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'else'))) AS t2_result,
JSON_SEARCH(message, 'one', 'and') AS t3_path,
JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'and'))) AS t3_result,
JSON_SEARCH(message, 'one', 'match') AS t4_path,
JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'match'))) AS t4_result
FROM jsonTest;
--echo # End of 11.0 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 # Removing 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, '[');
--disable_view_protocol
SELECT JSON_SCHEMA_VALID(repeat('[', 100), json_object());
--enable_view_protocol
SELECT JSON_SCHEMA_VALID(json_object(), repeat('[', 100));
--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 #
--echo # MDEV-31599: Assertion `0' failed in Item_param::can_return_value from Item::val_json,
--echo # UBSAN: member access within null pointer of type 'struct String' in
--echo # sql/item_jsonfunc.cc
--echo #
--error ER_JSON_NO_VARIABLE_SCHEMA
PREPARE s FROM 'SELECT JSON_SCHEMA_VALID (?,''{}'') FROM DUAL';
--echo #
--echo # MDEV-33015: Server crashes upon JSON_SCHEMA_VALID reading NULL from a user variable
--echo #
SET @a= NULL;
SELECT JSON_SCHEMA_VALID(@a,'{}');
--echo # End of 11.1 test
--echo # Beginning of 11.2
--echo #
--echo # MDEV-30145: JSON_TABLE: allow to retrieve the key when iterating on JSON objects
--echo #
--echo # Checking json table with NULL and empty json doc
SELECT jt.*
FROM JSON_TABLE(
NULL, '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
--echo # With Empty and NULL
SELECT JSON_KEY_VALUE(NULL, '$.a');
SELECT JSON_KEY_VALUE('', '$.a');
SELECT JSON_KEY_VALUE('[1,2,3]', '');
SELECT JSON_KEY_VALUE('[1,2,3]', NULL);
--echo # With scalars
SELECT JSON_KEY_VALUE('2', '$');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('2', '$'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('"some_string"', '$');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('"some_string"', '$'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('"some_string"', '$.a');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('"some_string"', '$.a'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('"some_string"', '$[0]');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('"some_string"', '$[0]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('false', '$[0]');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('false', '$[0]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
--echo # With non-scalar
--echo # With array
SELECT JSON_KEY_VALUE('[]', '[0]');
SELECT JSON_KEY_VALUE('[1, 2, 3]', '$[0]');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[1, 2, 3]', '$[0]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0]');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0].a');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0].a'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0][1]');
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0][1]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]',
'$[0][1]') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]',
'$[0][1].key1') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1].key1'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]',
'$[0][1]') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]', '$[0][1]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]',
'$[0][1][0]') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]', '$[0][1][0]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
--echo # With object
SELECT JSON_KEY_VALUE('{}', '$.key1');
#enable after fix MDEV-31554
--disable_cursor_protocol
#enable after MDEV-32454 fix
--disable_view_protocol
SELECT JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$') as exp;
--enable_view_protocol
--enable_cursor_protocol
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$'), '$[*]'
COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$.key1') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$.key1'), '$[*]'
COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b":2}, "key2":"val2"}', '$.key1') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('{"key1":{"a":1, "b":2}, "key2":"val2"}', '$.key1'), '$[*]'
COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, "key2":"val2"}', '$.key1.b[3]') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, "key2":"val2"}', '$.key1.b[3]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, "key2":"val2"}', '$.key1.b[0]') as exp;
SELECT jt.*
FROM JSON_TABLE(
JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, "key2":"val2"}', '$.key1.b[0]'), '$[*]'
COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt;
--echo # End of 11.2 test
--echo #
--echo # Beginning of 11.2 tests
--echo #
--echo # MDEV-26182: Implement json_array_intersect()
--echo #
--echo # JSON_ARRAY_INTERSECT()
--echo # Scalar as elements
SET @json1= '[1,2,3]';
SET @json2= '[1,2,3]';
SELECT json_array_intersect(@json1, @json2);
SET @json1= '[1,2,3]';
SET @json2= '[1,2,4]';
SELECT json_array_intersect(@json1, @json2);
SET @json1= '["abc","def","ghi"]';
SET @json2= '["xyz", "abc", "tuv"]';
SELECT json_array_intersect(@json1, @json2);
SET @obj1= '[true]';
SET @obj2= '[false, "true"]';
select json_array_intersect(@obj1, @obj2);
SET @obj1= '[true]';
SET @obj2= '[false, true]';
select json_array_intersect(@obj1, @obj2);
SET @obj1= '[null, true]';
SET @obj2= '[false, null]';
select json_array_intersect(@obj1, @obj2);
--echo # array as elements
SET @json1= '[6,6,6]';
SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]';
SELECT json_array_intersect(@json1, @json2);
SET @json1= '[[1,2,3],[4,5,6],[1,3,2]]';
SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]';
SELECT json_array_intersect(@json1, @json2);
SET @json1= '[[1,2,3],[4,5,6],[1,3,2]]';
SET @json2= '[[1,2,3],[4,5,6],[1,4,2]]';
SELECT json_array_intersect(@json1, @json2);
--echo # object as elements
SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2"}]';
SET @json2 = '[{"kkey1":"vvalue1"},{"k2":"v2","k1":"v1"}]';
SELECT json_array_intersect(@json1, @json2);
SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2","k1":"v1"}]';
SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"}]';
SELECT json_array_intersect(@json1, @json2);
--echo # multi type elements
SET @obj1= '[1,2,3, "abc", "xyz", {"key1":"val1"}, {"key2":"val2"}, [1,2,3]]';
SET @obj2= '[3.0, 4, 5, "abc", {"key1":"val1"}, [3,2,1]]';
select json_array_intersect(@obj1, @obj2);
SET @obj1= '[1, 2, 3, "abc", "xyz", {"key1": {"key2" : [1,2,3] } }, [4,5,6] ]';
SET @obj2= '[3.0, 4, 5, "abc", {"key1": {"key2" : [3,2,1]} }, {"key1": {"key2" : [1,2,3] } }, [4,5,6], [6,5,4] ]';
select json_array_intersect(@obj1, @obj2);
--echo # Checking duplicates
SET @obj1= '[1, 2, 3, 3, 3.0, "abc", true, true, {"key1":"val1"}]';
SET @obj2= '[3.0, 3, 5, "abc", "abc", true, {"key2":"val2"}, {"key1":"val1"}, {"key1":"val2"}]';
select json_array_intersect(@obj1, @obj2);
--echo # Checking Syntax error for JSON_ARRAY_INTERSECT()
SET @obj1= '[1, 2, 3, 3, 3.0, "abc", true, true, {"key1":"val1" ]';
SET @obj2= '[3.0, 3, 5, "abc", "abc", true, {"key2":"val2"}, {"key1":"val1"}, {"key1":"val2"}]';
select json_array_intersect(@obj1, @obj2);
--echo # Checking incorrect type for input
SET @obj1= '{"key1": "val1"}';
SET @arr1= '[ 1, 2, 3 ]';
SET @num1= '2';
SET @str1= '"abc"';
SET @bool1= 'true';
select json_array_intersect(@obj1, @arr1);
select json_array_intersect(@arr1, @obj1);
select json_array_intersect(@arr1, @num1);
select json_array_intersect(@num1, @bool1);
--echo # JSON_OBJECT_FILTER_KEYS()
SET @obj1= '{ "a": 1, "b": 2, "c": 3}';
SET @obj2= '{"b" : 10, "c": 20, "d": 30}';
SELECT JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_keys(@obj2)));
SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }';
SET @obj2= '{"b" : 10, "c": 20, "d": 30}';
SELECT JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_keys(@obj2)));
SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }';
SET @arr2='["x", "y", "z"]';
SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @arr2);
SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }';
SET @arr2='["key2", "key1", "b"]';
SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @arr1);
--echo # Incorrect type in input returns NULL
SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @obj1);
SELECT JSON_OBJECT_FILTER_KEYS(@arr1, @arr1);
SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }';
SET @scalar1='2';
SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @scalar1);
--echo # Checking syntax error
SET @obj1= '{ "a": 1, "b": 2, "c": 3}';
SET @obj2= '{"b" : 10, "c": 20, "d" 30}';
SELECT JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_keys(@obj2)));
SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }';
SET @arr2= '[ "key2", "key1", "b" ';
SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @arr1);
--echo # JSON_OBJECT_TO_ARRAY()
SET @obj1= '{ "a": [1, 2, 3], "b": { "key1":"val1", "key2": {"key3":"val3"} }, "c": 3, "d" : 1, "e": "xyz", "f": true, "g" : null}';
SELECT JSON_OBJECT_TO_ARRAY(@obj1);
SET @obj1= '{ "a": [1, 2, 3], "b": { "key1":"val1", "key2": [1, 2, 3] }, "c": 3, "d" : 1, "e": "xyz", "f": true, "g" : null}';
SELECT JSON_OBJECT_TO_ARRAY(@obj1);
--echo # Checking syntax error
SET @obj1= '{ "a": [1, 2, 3], "b": "key1": "val1", "key2": {"key3":"val3"} }, "c": 3, "d" : 1, "e": "xyz", "f": true, "g" : null}';
SELECT JSON_OBJECT_TO_ARRAY(@obj1);
--echo Checking incorrect type in argument
SET @arr1= '[1, 2, 3]';
SELECT JSON_OBJECT_TO_ARRAY(@arr1);
--echo #
--echo # MDEV-31411: JSON_ARRAY_INTERSECT/JSON_OBJECT_FILTER_KEYS should fetch
--echo # data from a table similar to other JSON functions
--echo #
CREATE TABLE t1 (
c1 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(c1)),
c2 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`c2`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
INSERT INTO t1 VALUES('[1,2,3]', '[2, 3, 4]'), ('[2 ,3, 4]', '[4, 5, 6]');
#check after fix MDEV-31554
--disable_cursor_protocol
SELECT JSON_ARRAY_INTERSECT(c1, c2) FROM t1;
--enable_cursor_protocol
DROP TABLE t1;
--echo #
--echo # MDEV-31543: ASAN heap-buffer-overflow in strncpy when fetching keys using JSON_OBJECT_FILTER_KEYS function
--echo #
SET @arr1='[1,2,"c"]';
SET character_set_database=ucs2;
SET CHARACTER SET utf8;
SET @obj1='{ "a": 1,"b": 2,"c": 3}';
SELECT JSON_OBJECT_FILTER_KEYS (@obj1,@arr1);
--echo #
--echo # End of 11.2 Test
--echo #
Reference in a new issue View git blame Copy permalink