diff options
Diffstat (limited to 'mysql-test/suite/json')
| -rw-r--r-- | mysql-test/suite/json/r/json_table.result | 371 | ||||
| -rw-r--r-- | mysql-test/suite/json/r/json_table2.result | 266 | ||||
| -rw-r--r-- | mysql-test/suite/json/t/json_table.test | 284 | ||||
| -rw-r--r-- | mysql-test/suite/json/t/json_table2.test | 1563 |
4 files changed, 2484 insertions, 0 deletions
diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result new file mode 100644 index 00000000000..762b2ce4216 --- /dev/null +++ b/mysql-test/suite/json/r/json_table.result @@ -0,0 +1,371 @@ +select * from json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; +a +1 +2 +select * from JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +1 11 +1 111 +2 22 +2 222 +3 NULL +SELECT * FROM JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'), NESTED PATH '$.b[*]' COLUMNS (c INT PATH '$') ) ) jt; +a b c +1 11 NULL +1 111 NULL +1 NULL 11 +1 NULL 111 +2 22 NULL +2 222 NULL +2 NULL 22 +2 NULL 222 +3 NULL NULL +create table t1 (id varchar(5), json varchar(1024)); +insert into t1 values ('j1', '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]'); +insert into t1 values ('j2', '[{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}]'); +select id, json, a from t1, json_table(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a')) as tt; +id json a +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 4 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 5 +select * from t1, JSON_TABLE(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt; +id json js_id a l_js_id b +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 1 11 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 2 111 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 1 22 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 2 222 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 1 11 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 2 111 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 1 22 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 2 222 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 1 22 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 2 222 +select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt; +ERROR 42S22: Unknown column 't1.no_field' in 'JSON_TABLE argument' +select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, a INT PATH '$'))) as jt; +ERROR 42S21: Duplicate column name 'a' +DROP TABLE t1; +create table t1 (item_name varchar(32), item_props varchar(1024)); +insert into t1 values ('Laptop', '{"color": "black", "price": 1000}'); +insert into t1 values ('Jeans', '{"color": "blue", "price": 50}'); +select * from t1 left join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; +item_name item_props color +Laptop {"color": "black", "price": 1000} black +Jeans {"color": "blue", "price": 50} blue +select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; +ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions +DROP TABLE t1; +select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +101 11 +101 111 +2 22 +2 222 +3 NULL +select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +NULL 11 +NULL 111 +2 22 +2 222 +3 NULL +select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +NULL 11 +NULL 111 +2 22 +2 222 +3 NULL +select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +202 11 +202 111 +2 22 +2 222 +3 NULL +select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 65 +select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'. +select * from json_table('{"a":0}',"$" columns(a decimal(1,1) path '$.a')) foo; +a +0.0 +connect con1,localhost,root,,; +select a from json_table('{"a":0}',"$" columns(a for ordinality)) foo; +a +1 +connection default; +disconnect con1; +create database db; +use db; +create table t (a text); +insert into t values ('{"foo":"bar"}'); +create user u@localhost; +grant select (a) on db.t to u@localhost; +connect con1,localhost,u,,db; +select a from t; +a +{"foo":"bar"} +select * from t, json_table(t.a, '$' columns(f varchar(20) path '$.foo')) as jt; +a f +{"foo":"bar"} bar +connection default; +disconnect con1; +drop user u@localhost; +drop database db; +use test; +create table t1 ( +color varchar(32), +price int +); +insert into t1 values ("red", 100), ("blue", 50); +insert into t1 select * from t1; +insert into t1 select * from t1; +set optimizer_switch='firstmatch=off'; +select * from +json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}]', +'$[*]' columns( color varchar(100) path '$.color', +price text path '$.price' + ) +) as T +where +T.color in (select color from t1 where t1.price=T.price); +color price +blue 50 +red 100 +drop table t1; +select * from +json_table(' [ {"color": "blue", "sizes": [1,2,3,4], "prices" : [10,20]}, + {"color": "red", "sizes": [10,11,12,13,14], "prices" : [100,200,300]} ]', +'$[*]' columns( +color varchar(4) path '$.color', +seq0 for ordinality, +nested path '$.sizes[*]' + columns (seq1 for ordinality, +size int path '$'), +nested path '$.prices[*]' + columns (seq2 for ordinality, +price int path '$') +) +) as T; +color seq0 seq1 size seq2 price +blue 1 1 1 NULL NULL +blue 1 2 2 NULL NULL +blue 1 3 3 NULL NULL +blue 1 4 4 NULL NULL +blue 1 NULL NULL 1 10 +blue 1 NULL NULL 2 20 +red 2 1 10 NULL NULL +red 2 2 11 NULL NULL +red 2 3 12 NULL NULL +red 2 4 13 NULL NULL +red 2 5 14 NULL NULL +red 2 NULL NULL 1 100 +red 2 NULL NULL 2 200 +red 2 NULL NULL 3 300 +select * from json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}, + {"color": "rojo", "price": 10.0}, + {"color": "blanco", "price": 11.0}]', +'$[*]' columns( color varchar(100) path '$.color', +price text path '$.price', seq for ordinality)) as T order by color desc; +color price seq +rojo 10.0 3 +red 100 2 +blue 50 1 +blanco 11.0 4 +create view v as select * from json_table('{"as":"b", "x":123}',"$" columns(a varchar(8) path '$.a' default '-' on empty, x int path '$.x')) x; +select * from v; +a x +- 123 +show create table v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `x`.`a` AS `a`,`x`.`x` AS `x` from JSON_TABLE('{"as":"b", "x":123}', '$' COLUMNS (`a` varchar(8) PATH '$.a' DEFAULT '-' ON EMPTY, `x` int(11) PATH '$.x')) `x` latin1 latin1_swedish_ci +drop view v; +select * from json_table('{"as":"b", "x":123}', +"$" columns(a varchar(8) path '$.a' default '-' on empty null on error null on empty, x int path '$.x')) x; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null on empty, x int path '$.x')) x' at line 2 +select * from json_table('{"a":"foo","b":"bar"}', '$' + columns (v varchar(20) path '$.*')) as jt; +v +NULL +select * from json_table('{"a":"foo","b":"bar"}', '$' + columns (v varchar(20) path '$.*' default '-' on error)) as jt; +v +- +select * from json_table('{"b":"bar"}', '$' + columns (v varchar(20) path '$.*' default '-' on error)) as jt; +v +bar +create table t1 (a varchar(100)); +insert into t1 values ('1'); +select * from t1 as T, json_table(T.a, '$[*]' columns(color varchar(100) path '$.nonexistent', seq for ordinality)) as T; +ERROR 42000: Not unique table/alias: 'T' +drop table t1; +prepare s from 'select * from +json_table(?, + \'$[*]\' columns( color varchar(100) path \'$.color\', + price text path \'$.price\', + seq for ordinality)) as T +order by color desc; '; +execute s using '[{"color": "red", "price":1}, {"color":"brown", "price":2}]'; +color price seq +red 1 1 +brown 2 2 +deallocate prepare s; +create view v2 as select * from json_table('[{"co\\\\lor": "blue", "price": 50}]', '$[*]' columns( color varchar(100) path '$.co\\\\lor') ) as T; +select * from v2; +color +blue +drop view v2; +explain format=json select * from +json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "tt", + "access_type": "ALL", + "rows": 40, + "filtered": 100, + "table_function": "json_table" + } + } +} +explain select * from +json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tt ALL NULL NULL NULL NULL 40 Table function: json_table +create view v1 as select * from +json_table('[{"color": "blue", "price": 50}]', +'$[*]' columns(color text path '$.nonexistent', +seq for ordinality)) as `ALIAS NOT QUOTED`; +select * from v1; +color seq +NULL 1 +drop view v1; +create view v1 as select * from +json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}]', +'$[*]' columns( +color text path "$.QUOTES \" HERE \"", +color1 text path '$.QUOTES " HERE "', +color2 text path "$.QUOTES ' HERE '", +seq for ordinality)) as T; +select * from v1; +color color1 color2 seq +NULL NULL NULL 1 +NULL NULL NULL 2 +drop view v1; +CREATE TABLE t1 (x INT); +INSERT INTO t1 VALUES (1), (2), (3); +SELECT t1.x*2 m, jt.* FROM t1, +JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt; +ERROR 42S22: Unknown column 'm' in 'JSON_TABLE argument' +DROP TABLE t1; +select * from json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; +ERROR 42S22: Unknown column 'JS3.size' in 'JSON_TABLE argument' +create table t1 (json varchar(100) character set utf8); +insert into t1 values ('{"value":"АБВ"}'); +create table tj1 as +select T.value +from t1, json_table(t1.json, '$' columns (value varchar(32) PATH '$.value')) T; +show create table tj1; +Table Create Table +tj1 CREATE TABLE `tj1` ( + `value` varchar(32) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +drop table tj1; +CREATE TABLE t1(id INT, f1 JSON); +INSERT INTO t1 VALUES +(1, '{\"1\": 1}'), +(2, '{\"1\": 2}'), +(3, '{\"1\": 3}'), +(4, '{\"1\": 4}'), +(5, '{\"1\": 5}'), +(6, '{\"1\": 6}'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 'f1' +test.t1 analyze status OK +SELECT * FROM JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3; +ERROR HY000: Incorrect usage of JSON_TABLE and argument +SELECT * FROM t1 as jj1, +(SELECT tt2.* +FROM +t1 as tt2, +JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl +STRAIGHT_JOIN +t1 AS tt3 +) dt +ORDER BY 1,3 LIMIT 10; +ERROR HY000: Incorrect usage of JSON_TABLE and argument +drop table t1; +select collation(x) from +JSON_TABLE('["abc"]', '$[*]' COLUMNS (x VARCHAR(10) CHARSET latin1 PATH '$')) tbl; +collation(x) +latin1_swedish_ci +SELECT * FROM JSON_TABLE('{"x":1, "y":2}', _utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' + COLUMNS(y INT PATH _utf8mb4'$.y' DEFAULT _utf8mb4'1' ON EMPTY DEFAULT _utf8mb4'2' ON ERROR))) jt; +y +1 +select * from json_table( +'{"name":"t-shirt", "colors": ["yellow", "blue"],"sizes": ["small", "medium", "large"]}', +'$' columns(name varchar(32) path '$.name', +nested path '$.colors[*]' columns ( +color varchar(32) path '$', +nested path '$.sizes[*]' columns ( +size varchar(32) path '$' +)))) as t; +name color size +t-shirt yellow NULL +t-shirt blue NULL +SELECT x, length(x) FROM +JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' DEFAULT 'abcdefg' ON EMPTY)) jt; +x length(x) +abcdefg 7 +select * from +json_table('[{"a":"aa"}, {"b":null}]', '$[*]' + columns (col1 int path '$.b' default '456' on empty)) as tt; +col1 +456 +NULL +select * from +json_table('[{"a":"aa"}, {"b":true}]', '$[*]' + columns (col1 int path '$.b' default '456' on empty)) as tt; +col1 +456 +1 +select * from +json_table('[{"a":"aa"}, {"b":false}]', '$[*]' + columns (col1 int path '$.b' default '456' on empty)) as tt; +col1 +456 +0 +select * from +json_table('[{"a":"aa"}, {"b":null}]', '$[*]' + columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt; +col1 +456 +NULL +select * from +json_table('[{"a":"aa"}, {"b":true}]', '$[*]' + columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt; +col1 +456 +true +select * from +json_table('[{"a":"aa"}, {"b":false}]', '$[*]' + columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt; +col1 +456 +false +# +# End of 10.5 tests +# diff --git a/mysql-test/suite/json/r/json_table2.result b/mysql-test/suite/json/r/json_table2.result new file mode 100644 index 00000000000..e6d94278e06 --- /dev/null +++ b/mysql-test/suite/json/r/json_table2.result @@ -0,0 +1,266 @@ +select * from json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; +a +1 +2 +select * from JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +1 11 +1 111 +2 22 +2 222 +SELECT * FROM JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'), NESTED PATH '$.b[*]' COLUMNS (c INT PATH '$') ) ) jt; +a b c +1 11 NULL +1 111 NULL +1 NULL 11 +1 NULL 111 +2 22 NULL +2 222 NULL +2 NULL 22 +2 NULL 222 +create table t1 (id varchar(5), json varchar(1024)); +insert into t1 values ('j1', '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]'); +insert into t1 values ('j2', '[{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}]'); +select id, json, a from t1, json_table(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a')) as tt; +id json a +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 4 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 5 +select * from t1, JSON_TABLE(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt; +id json js_id a l_js_id b +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 1 11 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 2 111 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 1 22 +j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 2 222 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 1 11 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 2 111 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 1 22 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 2 222 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 1 22 +j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 2 222 +select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt; +ERROR 42S22: Unknown column 't1.no_field' in 'JSON_TABLE argument' +select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, a INT PATH '$'))) as jt; +ERROR 42S21: Duplicate column name 'a' +DROP TABLE t1; +create table t1 (item_name varchar(32), item_props varchar(1024)); +insert into t1 values ('Laptop', '{"color": "black", "price": 1000}'); +insert into t1 values ('Jeans', '{"color": "blue", "price": 50}'); +select * from t1 left join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; +item_name item_props color +Laptop {"color": "black", "price": 1000} black +Jeans {"color": "blue", "price": 50} blue +select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; +ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions +DROP TABLE t1; +select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +101 11 +101 111 +2 22 +2 222 +select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +NULL 11 +NULL 111 +2 22 +2 222 +select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +NULL 11 +NULL 111 +2 22 +2 222 +select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +a b +202 11 +202 111 +2 22 +2 222 +select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 65 +select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; +ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'. +select * from json_table('{"a":0}',"$" columns(a decimal(1,1) path '$.a')) foo; +a +0.0 +connect con1,localhost,root,,; +select a from json_table('{"a":0}',"$" columns(a for ordinality)) foo; +a +1 +connection default; +disconnect con1; +create database db; +use db; +create table t (a text); +insert into t values ('{"foo":"bar"}'); +create user u@localhost; +grant select (a) on db.t to u@localhost; +connect con1,localhost,u,,db; +select a from t; +a +{"foo":"bar"} +select * from t, json_table(t.a, '$' columns(f varchar(20) path '$.foo')) as jt; +a f +{"foo":"bar"} bar +connection default; +disconnect con1; +drop user u@localhost; +drop database db; +use test; +create table t1 ( +color varchar(32), +price int +); +insert into t1 values ("red", 100), ("blue", 50); +insert into t1 select * from t1; +insert into t1 select * from t1; +set optimizer_switch='firstmatch=off'; +select * from +json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}]', +'$[*]' columns( color varchar(100) path '$.color', +price text path '$.price' + ) +) as T +where +T.color in (select color from t1 where t1.price=T.price); +color price +blue 50 +red 100 +drop table t1; +select * from +json_table(' [ {"color": "blue", "sizes": [1,2,3,4], "prices" : [10,20]}, + {"color": "red", "sizes": [10,11,12,13,14], "prices" : [100,200,300]} ]', +'$[*]' columns( +color varchar(4) path '$.color', +seq0 for ordinality, +nested path '$.sizes[*]' + columns (seq1 for ordinality, +size int path '$'), +nested path '$.prices[*]' + columns (seq2 for ordinality, +price int path '$') +) +) as T; +color seq0 seq1 size seq2 price +blue 1 1 1 NULL NULL +blue 1 2 2 NULL NULL +blue 1 3 3 NULL NULL +blue 1 4 4 NULL NULL +blue 1 NULL NULL 1 10 +blue 1 NULL NULL 2 20 +red 2 1 10 NULL NULL +red 2 2 11 NULL NULL +red 2 3 12 NULL NULL +red 2 4 13 NULL NULL +red 2 5 14 NULL NULL +red 2 NULL NULL 1 100 +red 2 NULL NULL 2 200 +red 2 NULL NULL 3 300 +select * from json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}, + {"color": "rojo", "price": 10.0}, + {"color": "blanco", "price": 11.0}]', +'$[*]' columns( color varchar(100) path '$.color', +price text path '$.price', seq for ordinality)) as T order by color desc; +color price seq +rojo 10.0 3 +red 100 2 +blue 50 1 +blanco 11.0 4 +create view v as select * from json_table('{"as":"b", "x":123}',"$" columns(a varchar(8) path '$.a' default '-' on empty, x int path '$.x')) x; +select * from v; +a x +- 123 +show create table v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `x`.`a` AS `a`,`x`.`x` AS `x` from JSON_TABLE('{"as":"b", "x":123}', '$' COLUMNS (`a` varchar(8) PATH '$.a' DEFAULT '-' ON EMPTY, `x` int(11) PATH '$.x')) `x` latin1 latin1_swedish_ci +drop view v; +select * from json_table('{"as":"b", "x":123}', +"$" columns(a varchar(8) path '$.a' default '-' on empty null on error null on empty, x int path '$.x')) x; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null on empty, x int path '$.x')) x' at line 2 +select * from json_table('{"a":"foo","b":"bar"}', '$' + columns (v varchar(20) path '$.*')) as jt; +v +NULL +select * from json_table('{"a":"foo","b":"bar"}', '$' + columns (v varchar(20) path '$.*' default '-' on error)) as jt; +v +- +select * from json_table('{"b":"bar"}', '$' + columns (v varchar(20) path '$.*' default '-' on error)) as jt; +v +bar +create table t1 (a varchar(100)); +insert into t1 values ('1'); +select * from t1 as T, json_table(T.a, '$[*]' columns(color varchar(100) path '$.nonexistent', seq for ordinality)) as T; +ERROR 42000: Not unique table/alias: 'T' +drop table t1; +prepare s from 'select * from +json_table(?, + \'$[*]\' columns( color varchar(100) path \'$.color\', + price text path \'$.price\', + seq for ordinality)) as T +order by color desc; '; +execute s using '[{"color": "red", "price":1}, {"color":"brown", "price":2}]'; +color price seq +red 1 1 +brown 2 2 +deallocate prepare s; +create view v2 as select * from json_table('[{"co\\\\lor": "blue", "price": 50}]', '$[*]' columns( color varchar(100) path '$.co\\\\lor') ) as T; +select * from v2; +color +blue +drop view v2; +explain format=json select * from +json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "tt", + "access_type": "ALL", + "rows": 40, + "filtered": 100, + "table_function": "json_table" + } + } +} +explain select * from +json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tt ALL NULL NULL NULL NULL 40 Table function: json_table +create view v1 as select * from +json_table('[{"color": "blue", "price": 50}]', +'$[*]' columns(color text path '$.nonexistent', +seq for ordinality)) as `ALIAS NOT QUOTED`; +select * from v1; +color seq +NULL 1 +drop view v1; +create view v1 as select * from +json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}]', +'$[*]' columns( +color text path "$.QUOTES \" HERE \"", +color1 text path '$.QUOTES " HERE "', +color2 text path "$.QUOTES ' HERE '", +seq for ordinality)) as T; +select * from v1; +color color1 color2 seq +NULL NULL NULL 1 +NULL NULL NULL 2 +drop view v1; +CREATE TABLE t1 (x INT); +INSERT INTO t1 VALUES (1), (2), (3); +SELECT t1.x*2 m, jt.* FROM t1, +JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt; +ERROR 42S22: Unknown column 'm' in 'JSON_TABLE argument' +DROP TABLE t1; +# +# End of 10.5 tests +# diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test new file mode 100644 index 00000000000..8c011038331 --- /dev/null +++ b/mysql-test/suite/json/t/json_table.test @@ -0,0 +1,284 @@ +select * from json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; + +select * from JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; + +SELECT * FROM JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'), NESTED PATH '$.b[*]' COLUMNS (c INT PATH '$') ) ) jt; + +create table t1 (id varchar(5), json varchar(1024)); +insert into t1 values ('j1', '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]'); +insert into t1 values ('j2', '[{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}]'); +select id, json, a from t1, json_table(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a')) as tt; +select * from t1, JSON_TABLE(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt; +--error ER_BAD_FIELD_ERROR +select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt; + +--error ER_DUP_FIELDNAME +select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, a INT PATH '$'))) as jt; + +DROP TABLE t1; + +create table t1 (item_name varchar(32), item_props varchar(1024)); +insert into t1 values ('Laptop', '{"color": "black", "price": 1000}'); +insert into t1 values ('Jeans', '{"color": "blue", "price": 50}'); + +select * from t1 left join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; + +--error ER_WRONG_OUTER_JOIN +select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; + +DROP TABLE t1; + +select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; + +select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; + +select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; + +select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; + +--error ER_JSON_SYNTAX +select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; + +--error ER_JSON_TABLE_ERROR_ON_FIELD +select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; + +# +# MDEV-22290 JSON_TABLE: Decimal type with M equal D causes Assertion +# `scale <= precision' failure +# +select * from json_table('{"a":0}',"$" columns(a decimal(1,1) path '$.a')) foo; + + +# +# MDEV-22291 JSON_TABLE: SELECT from json_table does not work without default database +# +connect (con1,localhost,root,,); +select a from json_table('{"a":0}',"$" columns(a for ordinality)) foo; +connection default; +disconnect con1; + +# +# MDEV-22302 JSON_TABLE: Column privilege is insufficient for query with json_table +# + +create database db; +use db; +create table t (a text); +insert into t values ('{"foo":"bar"}'); +create user u@localhost; +grant select (a) on db.t to u@localhost; + +--connect (con1,localhost,u,,db) +select a from t; +select * from t, json_table(t.a, '$' columns(f varchar(20) path '$.foo')) as jt; + +connection default; +disconnect con1; + +drop user u@localhost; +drop database db; + +use test; +create table t1 ( + color varchar(32), + price int + ); +insert into t1 values ("red", 100), ("blue", 50); +insert into t1 select * from t1; +insert into t1 select * from t1; + +set optimizer_switch='firstmatch=off'; +select * from + json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}]', + '$[*]' columns( color varchar(100) path '$.color', + price text path '$.price' + ) + ) as T + where + T.color in (select color from t1 where t1.price=T.price); + +drop table t1; + +select * from +json_table(' [ {"color": "blue", "sizes": [1,2,3,4], "prices" : [10,20]}, + {"color": "red", "sizes": [10,11,12,13,14], "prices" : [100,200,300]} ]', + '$[*]' columns( + color varchar(4) path '$.color', + seq0 for ordinality, + nested path '$.sizes[*]' + columns (seq1 for ordinality, + size int path '$'), + nested path '$.prices[*]' + columns (seq2 for ordinality, + price int path '$') + ) + ) as T; + + +select * from json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}, + {"color": "rojo", "price": 10.0}, + {"color": "blanco", "price": 11.0}]', + '$[*]' columns( color varchar(100) path '$.color', + price text path '$.price', seq for ordinality)) as T order by color desc; + +create view v as select * from json_table('{"as":"b", "x":123}',"$" columns(a varchar(8) path '$.a' default '-' on empty, x int path '$.x')) x; +select * from v; +show create table v; +drop view v; + +--error ER_PARSE_ERROR +select * from json_table('{"as":"b", "x":123}', + "$" columns(a varchar(8) path '$.a' default '-' on empty null on error null on empty, x int path '$.x')) x; + +select * from json_table('{"a":"foo","b":"bar"}', '$' + columns (v varchar(20) path '$.*')) as jt; + +select * from json_table('{"a":"foo","b":"bar"}', '$' + columns (v varchar(20) path '$.*' default '-' on error)) as jt; + +select * from json_table('{"b":"bar"}', '$' + columns (v varchar(20) path '$.*' default '-' on error)) as jt; + +create table t1 (a varchar(100)); +insert into t1 values ('1'); +--error ER_NONUNIQ_TABLE +select * from t1 as T, json_table(T.a, '$[*]' columns(color varchar(100) path '$.nonexistent', seq for ordinality)) as T; + +drop table t1; + +prepare s from 'select * from +json_table(?, + \'$[*]\' columns( color varchar(100) path \'$.color\', + price text path \'$.price\', + seq for ordinality)) as T +order by color desc; '; + +execute s using '[{"color": "red", "price":1}, {"color":"brown", "price":2}]'; +deallocate prepare s; + +create view v2 as select * from json_table('[{"co\\\\lor": "blue", "price": 50}]', '$[*]' columns( color varchar(100) path '$.co\\\\lor') ) as T; +select * from v2; +drop view v2; + +explain format=json select * from + json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; +explain select * from + json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt; + +create view v1 as select * from + json_table('[{"color": "blue", "price": 50}]', + '$[*]' columns(color text path '$.nonexistent', + seq for ordinality)) as `ALIAS NOT QUOTED`; +select * from v1; +drop view v1; +create view v1 as select * from + json_table('[{"color": "blue", "price": 50}, + {"color": "red", "price": 100}]', + '$[*]' columns( + color text path "$.QUOTES \" HERE \"", + color1 text path '$.QUOTES " HERE "', + color2 text path "$.QUOTES ' HERE '", + seq for ordinality)) as T; +select * from v1; +drop view v1; + +CREATE TABLE t1 (x INT); +INSERT INTO t1 VALUES (1), (2), (3); +--error ER_BAD_FIELD_ERROR +SELECT t1.x*2 m, jt.* FROM t1, + JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt; +DROP TABLE t1; + +--error ER_BAD_FIELD_ERROR +select * from json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; + +create table t1 (json varchar(100) character set utf8); +insert into t1 values ('{"value":"АБВ"}'); +create table tj1 as +select T.value + from t1, json_table(t1.json, '$' columns (value varchar(32) PATH '$.value')) T; +show create table tj1; +drop table t1; +drop table tj1; + +CREATE TABLE t1(id INT, f1 JSON); +INSERT INTO t1 VALUES + (1, '{\"1\": 1}'), + (2, '{\"1\": 2}'), + (3, '{\"1\": 3}'), + (4, '{\"1\": 4}'), + (5, '{\"1\": 5}'), + (6, '{\"1\": 6}'); +ANALYZE TABLE t1; + +--error ER_WRONG_USAGE +SELECT * FROM JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3; +--error ER_WRONG_USAGE +SELECT * FROM t1 as jj1, + (SELECT tt2.* + FROM + t1 as tt2, + JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl + STRAIGHT_JOIN + t1 AS tt3 + ) dt +ORDER BY 1,3 LIMIT 10; + +drop table t1; + +select collation(x) from + JSON_TABLE('["abc"]', '$[*]' COLUMNS (x VARCHAR(10) CHARSET latin1 PATH '$')) tbl; + +SELECT * FROM JSON_TABLE('{"x":1, "y":2}', _utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' + COLUMNS(y INT PATH _utf8mb4'$.y' DEFAULT _utf8mb4'1' ON EMPTY DEFAULT _utf8mb4'2' ON ERROR))) jt; + +select * from json_table( +'{"name":"t-shirt", "colors": ["yellow", "blue"],"sizes": ["small", "medium", "large"]}', + '$' columns(name varchar(32) path '$.name', + nested path '$.colors[*]' columns ( + color varchar(32) path '$', + nested path '$.sizes[*]' columns ( + size varchar(32) path '$' +)))) as t; + +SELECT x, length(x) FROM + JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' DEFAULT 'abcdefg' ON EMPTY)) jt; + +# check how conversion works for JSON NULL, TRUE and FALSE + +select * from + json_table('[{"a":"aa"}, {"b":null}]', '$[*]' + columns (col1 int path '$.b' default '456' on empty)) as tt; + +select * from + json_table('[{"a":"aa"}, {"b":true}]', '$[*]' + columns (col1 int path '$.b' default '456' on empty)) as tt; + +select * from + json_table('[{"a":"aa"}, {"b":false}]', '$[*]' + columns (col1 int path '$.b' default '456' on empty)) as tt; + +select * from + json_table('[{"a":"aa"}, {"b":null}]', '$[*]' + columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt; + +select * from + json_table('[{"a":"aa"}, {"b":true}]', '$[*]' + columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt; + +select * from + json_table('[{"a":"aa"}, {"b":false}]', '$[*]' + columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt; + + +select * from + json_table( '[{"a":"asd"}, {"a":123}, {"a":[]}, {"a":{}} ]', '$[*]' + columns (id for ordinality, + intcol int path '$.a' default '1234' on empty default '5678' on error) + ) as tt; + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/mysql-test/suite/json/t/json_table2.test b/mysql-test/suite/json/t/json_table2.test new file mode 100644 index 00000000000..767542f0cbd --- /dev/null +++ b/mysql-test/suite/json/t/json_table2.test @@ -0,0 +1,1563 @@ +# For stable statistics +#--source include/have_innodb_16k.inc + + +--echo # +--echo # WL#8867: Add JSON_TABLE table function +--echo # +let $query= +select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a', + jexst int exists path '\$.b') + ) as tt; +eval $query; +eval explain $query; +#eval explain format=tree $query; # Tests printing of MaterializedTableFunctionIterator. + +#--error ER_INVALID_DEFAULT +select * from + json_table( + '[{"x":"3"},{"a":2},{"b":1},{"a":0}]', + '$[*]' columns (id for ordinality, + jpath varchar(100) path '$.a' default '[99]' on error, + jexst int exists path '$.b') + ) as tt; + +let $query= select * from + json_table( + '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a' + default '33' on empty + default '66' on error, + jsn_path json path '\$.a' default '{"x":33}' on empty, + jexst int exists path '\$.b') + ) as tt; +eval $query; +eval explain $query; + +let $query= select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]', + '\$[*]' columns (id for ordinality, + jpath_i int path '\$.a' + default '33' on empty + default '66' on error, + jpath_r real path '\$.a' + default '33.3' on empty + default '77.7' on error, + jsn_path json path '\$.a' default '{"x":33}' on empty, + jexst int exists path '\$.b') + ) as tt; +eval $query; +eval explain $query; + +let $query= select * from + json_table( + '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a' + default '33' on empty + default '66' on error, + jsn_path json path '\$.a' default '{"x":33}' on empty, + jexst int exists path '\$.b') + ) as tt; +eval $query; +eval explain $query; + +select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a', + json_path json path '\$.a', + jexst int exists path '\$.b') + ) as tt; + +let $query= select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a', + json_path json path '\$.a', + jexst int exists path '\$.b') + ) as tt + where id = 3; +eval $query; +eval explain $query; + +#--error ER_MISSING_JSON_TABLE_VALUE +--error 4174 +select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a' error on empty, + jexst int exists path '\$.b') + ) as tt; + +#--error ER_WRONG_JSON_TABLE_VALUE +--error 4174 +select * from + json_table( + '[{"a":"3"},{"a":2},{"a":1},{"a":[0,1]}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a' error on error, + jexst int exists path '\$.b') + ) as tt; + +#--error ER_INVALID_JSON_TEXT_IN_PARAM +--error 4038 +select * from + json_table( + '!#@\$!@#\$', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path '\$.a', + jexst int exists path '\$.b') + ) as tt; + +#--error ER_INVALID_JSON_PATH +--error 4042 +select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + "!@#\$!@#\$" columns (id for ordinality, + jpath varchar(100) path '\$.a', + jexst int exists path '\$.b') + ) as tt; + +#--error ER_INVALID_JSON_PATH +--error 4042 +select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + '\$[*]' columns (id for ordinality, + jpath varchar(100) path "!@#\$!@#\$", + jexst int exists path '\$.b') + ) as tt; + +--error ER_DUP_FIELDNAME +select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + '$[*]' columns (id for ordinality, + id for ordinality) + ) as tt; + +select * from + json_table( + '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', + '$[*]' columns (id for ordinality, + _id for ordinality) + ) as tt; + +let $query= select * from + json_table( + '[ + {"a":"3", "n": { "l": 1}}, + {"a":2, "n": { "l": 1}}, + {"b":1, "n": { "l": 1}}, + {"a":0, "n": { "l": 1}} + ]', + '\$[*]' columns ( + id for ordinality, + jpath varchar(100) path '\$.a', + jexst int exists path '\$.b', + nested path '\$.n' columns ( + id_n for ordinality, + jpath_n varchar(50) path '\$.l') + ) + ) as tt; + +eval $query; +eval explain $query; +eval explain format=json $query; + +let $query= select * from + json_table( + '[ + {"a":2, "n": [{ "l": 1}, {"l": 11}]}, + {"a":1, "n": [{ "l": 2}, {"l": 22}]} + ]', + '\$[*]' columns ( + id for ordinality, + jpath varchar(50) path '\$.a', + nested path '\$.n[*]' columns ( + id_n for ordinality, + jpath_n varchar(50) path '\$.l'), + nested path '\$.n[*]' columns ( + id_m for ordinality, + jpath_m varchar(50) path '\$.l') + ) + ) as tt; + +eval $query; +eval explain $query; + +select * from json_table( + '[ + {"a":"3", "n": ["b","a","c"]}, + {"a":2, "n": [1,2]}, + {"b":1, "n": ["zzz"]}, + {"a":0, "n": [0.1, 0.02]} + ]', + '$[*]' columns ( + id for ordinality, + jpath varchar(100) path '$.a', + jexst int exists path '$.b', + nested path '$.n[*]' columns ( + id_n for ordinality, + jpath_n varchar(50) path '$') + ) + ) as tt; + +select * from json_table( + '[ + {"a":"3", "n": ["b","a","c"]}, + {"a":2, "n": [1,2]}, + {"b":1, "n": ["zzz"]}, + {"a":0, "n": [0.1, 0.02]} + ]', + '$[*]' columns ( + id for ordinality, + jpath varchar(100) path '$.a', + jexst int exists path '$.b', + nested path '$.n[*]' columns ( + id_n1 for ordinality, + jpath_n1 varchar(50) path '$') , + nested path '$.n[*]' columns ( + id_n2 for ordinality, + jpath_n2 varchar(50) path '$') + ) + ) as tt; + +let $query= select * from json_table( + '[ + {"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]}, + {"ll":["c"]} ]}, + {"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]}, + {"b":1, "n": [{"ll":["zzz"]}]}, + {"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]} + ]', + '\$[*]' columns ( + id1 for ordinality, + jpath varchar(100) path '\$.a', + jexst int exists path '\$.b', + nested path '\$.n[*]' columns ( + id2 for ordinality, + nested path '\$.ll[*]' columns ( + id3 for ordinality, + jpath_3 varchar(50) path '\$') + ), + nested path '\$.n[*]' columns ( + id4 for ordinality, + jpath_4 json path '\$') + ) + ) as tt; + +eval $query; +eval explain $query; + +--echo ord should be 1,1,1,2, which tells that first two values of 'l' are +--echo from the same object, and next two are from different objects +let $query= SELECT * +FROM JSON_TABLE( + '[{"a": "a_val", + "b": [ + {"c": "c_val", + "l": [1,2]} + ] + }, {"a": "a_val", + "b": [ + {"c": "c_val", + "l": [11]}, + {"c": "c_val", + "l": [22]} + ] + }]', + '\$[*]' COLUMNS ( + apath VARCHAR(10) PATH '\$.a', + NESTED PATH '\$.b[*]' COLUMNS ( + bpath VARCHAR(10) PATH '\$.c', + ord FOR ORDINALITY, + NESTED PATH '\$.l[*]' COLUMNS ( + lpath varchar(10) PATH '\$' + ) + ) + )) as jt; +eval $query; +eval explain $query; + +CREATE TABLE jt( i JSON ); +SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt + WHERE a=1; +EXPLAIN SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt + WHERE a=1; + +SELECT * FROM ( + SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt + WHERE a=1) AS ttt; +EXPLAIN SELECT * FROM ( + SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt + WHERE a=1) AS ttt; +DROP TABLE jt; + +--disable_parsing +SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON), + '$' COLUMNS (dt DATE PATH '$')) as tt; +SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON), + '$' COLUMNS (dt TIME PATH '$')) as tt; +SELECT * FROM JSON_TABLE(CAST(CAST("2001.02.03" AS DATE) AS JSON), + '$' COLUMNS (dt DATE PATH '$')) as tt; +--enable_parsing +SELECT * FROM JSON_TABLE(NULL, + '$' COLUMNS (dt DATE PATH '$')) as tt; + +CREATE VIEW v AS + SELECT * FROM JSON_TABLE('[1,2,3]', + '$[*]' COLUMNS (num INT PATH '$.a' + DEFAULT '123' ON EMPTY + DEFAULT '456' ON ERROR)) AS jt; +SELECT * FROM v; +SHOW CREATE VIEW v; +DROP VIEW v; + +#--error ER_INVALID_JSON_VALUE_FOR_CAST +SELECT * FROM JSON_TABLE('"asdf"', + '$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt; +#--error ER_WRONG_JSON_TABLE_VALUE +--error 4174 +SELECT * FROM + JSON_TABLE('[{"a":1},{"a":2}]', + '$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt; +--error 4174 +SELECT * FROM + JSON_TABLE('[{"a":1},{"a":2}]', + '$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt; +#--error ER_JT_VALUE_OUT_OF_RANGE +--error 4174 +SELECT * FROM + JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt; +SELECT * FROM + JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt; + +#--error ER_JT_MAX_NESTED_PATH +SELECT * FROM + JSON_TABLE('{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{}}}}}}}}}}}}}}}}}}}', + '$' COLUMNS (i0 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i1 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i2 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i3 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i4 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i5 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i6 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i7 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i8 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i9 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i10 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i11 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i12 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i13 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i14 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i15 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i16 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i17 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i18 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i19 INT PATH '$.a', + NESTED PATH '$.b' COLUMNS (i20 INT PATH '$.a' + )))))))))))))))))))))) jt; + +CREATE TABLE t1(id int, jd JSON); +INSERT INTO t1 values (1, '[1,3,5]'),(2,'[2,4,6]'); +SELECT id, jt.* FROM t1, + JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY, + val INT PATH '$')) AS jt; +SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.* + FROM t1, + JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY, + val INT PATH '$')) AS jt; + +EXPLAIN SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.* + FROM t1, + JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY, + val INT PATH '$')) AS jt; + +--sorted_result +SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.* + FROM t1, + JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY, + val INT PATH '$')) AS jt, + t1 AS t2; + +EXPLAIN SELECT t1.id, t2.id, jt.* + FROM t1, + JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY, + val INT PATH '$')) AS jt, + t1 AS t2; + +EXPLAIN SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.* + FROM t1, + JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY, + val INT PATH '$')) AS jt, + t1 AS t2; + +SELECT * FROM t1 WHERE id IN + (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS + (id INT PATH '$')) AS jt); +EXPLAIN SELECT * FROM t1 WHERE id IN + (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS + (id INT PATH '$')) AS jt); + +# JSON_TABLE referring outer scope +SELECT * FROM t1 WHERE id IN + (SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS + (id INT PATH '$')) AS jt); + +EXPLAIN SELECT * FROM t1 WHERE id IN + (SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS + (id INT PATH '$')) AS jt); +# JSON_TABLE referring another JSON_TABLE +SELECT id, jt1.*, jt2.* + FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1, + JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2; + +EXPLAIN SELECT id, jt1.*, jt2.* + FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1, + JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2; + +DROP TABLE t1; + +SELECT * FROM JSON_TABLE('"asdf"', '$' COLUMNS( + tm TIME PATH '$', + dt DATE PATH '$', + i INT PATH '$', + f FLOAT PATH '$', + d DECIMAL PATH '$')) AS jt; + +# DEFAULT NULL is not accepted syntax. +--error ER_PARSE_ERROR +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt; +--error ER_PARSE_ERROR +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt; + +# The DEFAULT value must be a string on JSON format for now. +--error ER_PARSE_ERROR +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt; + +--error ER_PARSE_ERROR +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt; +--error ER_PARSE_ERROR +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x DATE + PATH '$.x' + DEFAULT DATE'2020-01-01' ON EMPTY)) jt; +--error ER_PARSE_ERROR +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x DATE + PATH '$.x' + DEFAULT DATE'2020-01-01' ON ERROR)) jt; + +--echo # +--echo # Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF +--echo # +--error ER_BAD_FIELD_ERROR +SELECT a FROM JSON_TABLE(abc, '$[*]' COLUMNS ( a int path '$.a')) AS jt; + +--echo # +--echo # Bug#25420680: ASSERTION `THD->IS_ERROR()' FAILED IN SQL/SQL_SELECT.CC +--echo # +SELECT * FROM JSON_TABLE('{"a":"2017-11-1"}', + '$' COLUMNS (jpath DATE PATH '$.a')) AS jt; +--echo # +--echo # Bug#25413826: ASSERTION `TABLE_LIST->ALIAS' FAILED +--echo # +#--error ER_TF_MUST_HAVE_ALIAS +--error ER_PARSE_ERROR +SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]', + '$[*]' COLUMNS ( a int path '$.b')); + +--echo # +--echo # Bug#25421464: ASSERTION `!STRCMP(TABLE_REF->TABLE_NAME, ... +--echo # +CREATE VIEW v1 AS + SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]', + '$[*]' COLUMNS ( a INT PATH '$.b')) AS jt; +SELECT * FROM v1; +SHOW CREATE VIEW v1; +DROP VIEW v1; + +--echo # +--echo # Bug#25427457: ASSERTION `!((*REG_FIELD)->FLAGS & 16)' +--echo # +SELECT * FROM JSON_TABLE('{"a":"1"}', + '$' COLUMNS (jpath JSON PATH '$.a', + o FOR ORDINALITY)) AS jt +WHERE o = 1; + +--echo # +--echo # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H +--echo # +#--error ER_WRONG_FIELD_WITH_GROUP +SELECT je,o FROM JSON_TABLE('{"a":"1"}', + '$' COLUMNS (o FOR ORDINALITY, + je BIGINT EXISTS PATH '$.a')) AS jt +GROUP BY je; +SELECT je,COUNT(o) FROM JSON_TABLE('{"a":"1"}', + '$' COLUMNS (o FOR ORDINALITY, + je BIGINT EXISTS PATH '$.a')) AS jt +GROUP BY je; + +--echo # +--echo # Bug#25413194: ASSERTION `!(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE +--echo # +CREATE TABLE t1 (j JSON); +SELECT * FROM t1,JSON_TABLE(t1.j, '$[*]' COLUMNS ( a int path '$.b')) AS jt; +DROP TABLE t1; + +--echo # +--echo # Bug#25460537:SIG 11 IN NEXT_FAST AT SQL/SQL_LIST.H +--echo # +PREPARE STMT FROM + "SELECT * FROM JSON_TABLE( + \'[{\"a\":\"3\"},{\"a\":2},{\"b\":1},{\"a\":0}]\', + \'$[*]\' COLUMNS (id + FOR ORDINALITY, + jpath VARCHAR(100) PATH \'$.a\', + jexst INT EXISTS PATH \'$.b\') + ) as tt"; +EXECUTE STMT; +EXECUTE STMT; +DEALLOCATE PREPARE stmt; + +--echo # +--echo # Bug#25522353: SIG 11 IN JOIN::MAKE_JOIN_PLAN | SQL/SQL_OPTIMIZER.CC +--echo # +CREATE TABLE t1 (id INT, jc JSON); + +#psergey: hmm --error ER_TF_FORBIDDEN_JOIN_TYPE +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; + +# psergey: wow: --error ER_UNKNOWN_TABLE +SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id; + +#--error ER_UNKNOWN_TABLE psergey-todo: WHYYY? +SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id; + +SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; + +EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; + +SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id + LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; + +EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id + LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; + +#--error ER_TF_FORBIDDEN_JOIN_TYPE +SELECT * FROM t1 AS t1o RIGHT JOIN + (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) + ON t1o.id=t1i.id; + +#--error ER_TF_FORBIDDEN_JOIN_TYPE +SELECT * FROM t1 AS t1o RIGHT JOIN + (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) + ON t1o.id=t1i.id; + +#--error ER_TF_FORBIDDEN_JOIN_TYPE +WITH qn AS + (SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) + SELECT * from qn; + +#--error ER_TF_FORBIDDEN_JOIN_TYPE +WITH qn AS + (SELECT 1 UNION + SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) + SELECT * from qn; + +#--error ER_TF_FORBIDDEN_JOIN_TYPE +SELECT * FROM t1 AS t1o RIGHT JOIN + (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) + ON t1o.id=t1i.id; + +#--error ER_TF_FORBIDDEN_JOIN_TYPE +SELECT * FROM t1 AS t1o RIGHT JOIN + (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS + (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) + ON t1o.id=t1i.id; + +INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3"); + +ANALYZE TABLE t1; + +SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id INT PATH '$')) as jt ON t1.id=jt.id; + +EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS + (id INT PATH '$')) as jt ON t1.id=jt.id; + +#--error ER_TF_FORBIDDEN_JOIN_TYPE +--error 1120 +SELECT * FROM t1 + LEFT JOIN + JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id + RIGHT JOIN + JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id; + +DROP TABLE t1; + +# Test that tmp table can overflow to disk +#set @save_mem_se= @@internal_tmp_mem_storage_engine; +#set @@internal_tmp_mem_storage_engine=MEMORY; + +set @save_heap_size= @@max_heap_table_size; +set @@max_heap_table_size= 16384; + +FLUSH STATUS; +SELECT * FROM JSON_TABLE( + '[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]', + '$[*]' COLUMNS ( + c1 CHAR(255) PATH '$', + c2 CHAR(255) PATH '$', + c3 CHAR(255) PATH '$', + c4 CHAR(255) PATH '$', + c5 CHAR(255) PATH '$', + c6 CHAR(255) PATH '$', + c7 CHAR(255) PATH '$', + c8 CHAR(255) PATH '$')) AS jt; +SHOW STATUS LIKE '%tmp%'; +set @@max_heap_table_size= @save_heap_size; +#set @@internal_tmp_mem_storage_engine= @save_mem_se; + +--echo # +--echo # Bug#25504063: EXPLAIN OF JSON_TABLE QUERY USES INTERNAL TEMP TABLES +--echo # +FLUSH STATUS; +SELECT * FROM + JSON_TABLE( + '[{"a":"3"}]', + '$[*]' COLUMNS (id FOR ORDINALITY) + ) AS tt; +SHOW STATUS LIKE '%tmp%'; + +--echo # +--echo # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC +--echo # +#--error ER_BAD_FIELD_ERROR +--error 1054 +SELECT * FROM JSON_TABLE( ( SELECT a ) , '$.*' COLUMNS (col1 FOR ORDINALITY) ) +AS alias1; + +## psergey: why does it succeed? +#--error 1064 +SELECT * FROM JSON_TABLE( ( SELECT 1 ) , '$.*' COLUMNS (col1 FOR ORDINALITY) ) +AS alias1; + +--error 1111 +SELECT * FROM JSON_TABLE( ( SUM(1) ) , '$.*' COLUMNS (col1 FOR ORDINALITY) ) +AS alias1; + +--echo # +--echo # Bug# #25472875: ERROR SHOULD BE THROWN FOR INCORRECT VALUES +--echo # +#--error ER_JT_VALUE_OUT_OF_RANGE psergey! +SELECT * + FROM JSON_TABLE('{"a":"1993-01-01"}', + '$' COLUMNS (jp DATE PATH '$.b' DEFAULT '1000' ON EMPTY)) + AS jt; + +--echo # +--echo # Bug#25532429: INVALID JSON ERROR NOT THROWN WITH EMPTY TABLES JOIN +--echo # +CREATE TABLE t1(j JSON); +#--error ER_INVALID_JSON_TEXT_IN_PARAM psergey! +SELECT * FROM t1, + JSON_TABLE( 'dqwfjqjf' , '$[*]' COLUMNS (col5 FOR ORDINALITY) ) AS alias7; +DROP TABLE t1; + + +--echo # +--echo # Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063 +--echo # +let $query= + SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 CHAR(70) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +--echo # Too short field causes truncation, error and triggers ON ERROR clause +let $query= + SELECT * FROM + JSON_TABLE('["3.14159"]', + '\$[*]' COLUMNS (col18 CHAR(6) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +--echo #Truncated space doesn't trigger ON ERROR +let $query= + SELECT * FROM + JSON_TABLE('["3.14159 "]', + '\$[*]' COLUMNS (col18 CHAR(7) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +let $query= +SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 CHAR(255) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +let $query= + SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 VARCHAR(70) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +let $query= + SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 VARCHAR(255) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +let $query= + SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 FLOAT PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +let $query= + SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 DOUBLE PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +let $query= + SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +#--error ER_JT_VALUE_OUT_OF_RANGE +--error 4174 +SELECT * FROM + JSON_TABLE('[3.14159]', + '\$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '\$' ERROR ON ERROR) + ) AS alias2; + +let $query= + SELECT * FROM + JSON_TABLE('[0.9]', + '\$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '\$') + ) AS alias2; +eval EXPLAIN $query; +eval $query; + +SELECT * FROM + JSON_TABLE('["asdf","ghjk"]', + '$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$' + DEFAULT "3.14159" ON ERROR) + ) AS alias2; + +CREATE TABLE t1(jd JSON); +INSERT INTO t1 VALUES('["asdf"]'),('["ghjk"]'); +SELECT * FROM t1, + JSON_TABLE(jd, + '$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$' + DEFAULT "3.14159" ON ERROR) + ) AS alias2; +DROP TABLE t1; +--echo # +--echo # Bug#25540027: SIG 11 IN FIND_FIELD_IN_TABLE | SQL/SQL_BASE.CC +--echo # +CREATE TABLE t1(c1 JSON); +--error ER_NON_UPDATABLE_TABLE +UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 + SET jt1.a=1; +--error ER_PARSE_ERROR +DELETE JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 + FROM t1; +--error ER_PARSE_ERROR +DELETE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 + USING t1; + +DROP TABLE t1; + +--echo # +--echo # Bug#25547244: ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET( +--echo # +--disable_parsing +## psergey: CRASH! +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES(1); +WITH cte_query AS + (SELECT * FROM t1, JSON_TABLE( JSON_OBJECT('ISSKF',i) , + '$[*]' COLUMNS (jtcol1 INT EXISTS PATH '$[*]') ) AS alias2) +SELECT jtcol1 AS field1 FROM cte_query; +DROP TABLE t1; +--enable_parsing + +--echo # +--echo # Bug#25540675: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED +--echo # +CREATE TABLE j1(j JSON); +INSERT INTO j1 VALUES('[1,2,3]'),('[1,2,4]'); +SELECT * FROM j1, + JSON_TABLE( JSON_OBJECT('key1', j) , + '$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (col11 FOR ORDINALITY))) AS alias2; +DROP TABLE j1; + +--echo # +--echo # Bug#25584335: SIG 11 IN TABLE_LIST::FETCH_NUMBER_OF_ROWS +--echo # +CREATE TABLE t1(i INT); + +PREPARE stmt FROM "SELECT alias1.i AS field1 FROM ( + t1 AS alias1, + (SELECT * FROM + JSON_TABLE('[1,2,3]' , + '$[*]' COLUMNS (`col_varchar` FOR ORDINALITY)) AS SQ1_alias1 + ) AS alias2 )"; + +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + +--echo # +--echo # Bug#25604048: COLUMN NAMES WITH SAME 33-CHAR PREFIX ARE EQUAL +--echo # +SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS( + column_name_is_thirty_four_or_more VARCHAR(17) PATH '$.key1', + column_name_is_thirty_four_or_more_yes_indeed VARCHAR(17) PATH '$.key1' +) ) AS alias1; + +#--error ER_WRONG_COLUMN_NAME +SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS( + `column_name_is_thirty_four_or_more ` VARCHAR(17) PATH '$.key1' +) ) AS alias1; + +--echo # +--echo # Bug#25604404: JSON_TABLE MORE RESTRICTIVE WITH IDENTIFIERS THAN +--echo # CREATE TABLE +--echo # +SELECT * FROM JSON_TABLE( '[1, 2]', '$' COLUMNS( + one INT PATH '$[0]', two INT PATH '$[1]' +)) AS jt; + +--echo # +--echo # Bug#25588450: SIG 6 IN JSON_WRAPPER::SEEK|SQL/JSON_DOM.CC +--echo # +CREATE TABLE t1(c VARCHAR(10)) ENGINE=MEMORY; +INSERT INTO t1 VALUES('fiheife'); +#--error ER_INVALID_JSON_TEXT_IN_PARAM +--error 4038 +SELECT * FROM `t1` AS alias1, JSON_TABLE( `c` , '$[*]' COLUMNS (jtcol1 JSON +PATH '$.*')) AS alias2 WHERE jtcol1 <= 'kjfh'; +DROP TABLE t1; + +--echo # +--echo # Bug#25587754: ASSERTION `FIXED == 0 || BASIC_CONST_ITEM()' FAILED +--echo # +PREPARE stmt FROM + "SELECT * FROM JSON_TABLE( '[1,2]', '$[*]' + COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2"; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # +--echo # Bug#25584593: UNABLE TO USE JSON_TABLE() ON TEXT/BLOB JSON DATA +--echo # +SELECT * FROM JSON_TABLE(NULL, '$.k' COLUMNS (id FOR ORDINALITY)) AS aLias; +SELECT * FROM JSON_TABLE(@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias; +SET @myjson = '{"k": 42}'; +SELECT * FROM JSON_TABLE(@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias; + +CREATE TABLE t1( + txt TEXT, ty TINYTEXT, tm MEDIUMTEXT, tl LONGTEXT); +INSERT INTO t1 values ( + '{"k": "text"}','{"k": "tinytext"}','{"k": "mediumtext"}','{"k": "longtext"}'); +SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias; +SELECT alias.* FROM t1, JSON_TABLE(t1.ty, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias; +SELECT alias.* FROM t1, JSON_TABLE(t1.tm, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias; +SELECT alias.* FROM t1, JSON_TABLE(t1.tl, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias; +SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id TEXT PATH '$')) AS alias; +SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id TINYTEXT PATH '$')) AS alias; +SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id MEDIUMTEXT PATH '$')) AS alias; +SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id LONGTEXT PATH '$')) AS alias; + +# BLOB can store data from JSON +SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias; +# Check that type is printed correctly +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias; +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias; +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias; +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias; +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias; +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias; +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias; +EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias; +# But can't be used as a data source +#--error ER_WRONG_ARGUMENTS +--error 4038 +SELECT * FROM + (SELECT blb as jf + FROM + JSON_TABLE('"asd123"', + '$' COLUMNS (blb BLOB PATH '$') + ) AS jti + ) AS dt, + JSON_TABLE(jf, '$' COLUMNS (blb BLOB PATH '$')) AS jto; + +DROP TABLE t1; + +--echo # +--echo # Bug#26500384: ASSERT FAILURE IN QUERY WITH WINDOW FUNCTION AND +--echo # JSON_TABLE +--echo # +--disable_parsing +## psergey: crash! +CREATE TABLE t (x INT); +INSERT INTO t VALUES (1), (2), (3); +--error ER_BAD_FIELD_ERROR +SELECT MAX(t.x) OVER () m, jt.* FROM t, + JSON_TABLE(JSON_ARRAY(m), '$[*]' COLUMNS (i INT PATH '$')) jt; +DROP TABLE t; +--enable_parsing + +--echo # +--echo # Bug#26583283: ASSERTION `!THD->IS_ERROR()' FAILED IN SQL_RESOLVER.CC +--echo # +EXPLAIN SELECT * FROM JSON_TABLE('null', '$' COLUMNS(AA DECIMAL PATH '$')) tt; + +CREATE VIEW v1 AS SELECT * FROM + JSON_TABLE( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt; +SELECT * FROM v1; +EXPLAIN SELECT * FROM v1; +DROP VIEW v1; + +--echo # +--echo # Bug#25822408: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED +--echo # +PREPARE stmt FROM "SELECT * FROM + JSON_TABLE('{\"a\":1}','$' COLUMNS (c1 CHAR(20) PATH '$.b' ERROR ON EMPTY)) jt"; +#--error ER_MISSING_JSON_TABLE_VALUE +--error 4174 +EXECUTE stmt; +#--error ER_MISSING_JSON_TABLE_VALUE +--error 4174 +EXECUTE stmt; + +--echo # +--echo # Bug#25594571: CRASH AT ITEM::CONST_ITEM|SQL/ITEM.H +--echo # +CREATE TABLE t1 (i INT); + +INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(7); + +PREPARE stmt FROM "SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2 +LEFT JOIN JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (i FOR ORDINALITY )) AS +alias3 ON alias2 . `i` = alias3 . `i` ON alias1 . `i` = alias2 . `i`"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1; + +--echo # +--echo # Bug#26648617: ASSERTION `IS_VIEW_OR_DERIVED() && +--echo # USES_MATERIALIZATION()' FAILED. +--echo # +CREATE TABLE t1 ( + col_varchar_key varchar(1) DEFAULT NULL +) ; + +INSERT INTO t1 VALUES(1),(4); + +SELECT * FROM t1 WHERE col_varchar_key NOT IN ( + SELECT col_varchar_key FROM JSON_TABLE( + '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS + (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1); + +EXPLAIN SELECT * FROM t1 WHERE col_varchar_key NOT IN ( + SELECT col_varchar_key FROM JSON_TABLE( + '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS + (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1); + +SELECT * FROM t1 WHERE col_varchar_key IN ( + SELECT col_varchar_key FROM JSON_TABLE( + '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS + (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1); + +EXPLAIN SELECT * FROM t1 WHERE col_varchar_key IN ( + SELECT col_varchar_key FROM JSON_TABLE( + '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS + (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1); + +DROP TABLE t1; + +--echo # +--echo # Bug#26711551: WL8867:CONDITIONAL JUMP IN JSON_TABLE_COLUMN::CLEANUP +--echo # +CREATE TABLE t(x int, y int); +INSERT INTO t(x) VALUES (1); +UPDATE t t1, JSON_TABLE('[2]', '$[*]' COLUMNS (x INT PATH '$')) t2 +SET t1.y = t2.x; +SELECT * FROM t; +DROP TABLE t; + +--echo # +--echo # Bug#26679671: SIG 11 IN JSON_BINARY::PARSE_BINARY() +--echo # +CREATE TABLE t1(id INT, f1 JSON); +INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'), +(4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}'); +ANALYZE TABLE t1; + +ANALYZE TABLE t1; + +--sorted_result +SELECT * FROM t1 as jj1, + (SELECT tt2.* FROM t1 as tt2, + JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt; +EXPLAIN SELECT * FROM t1 as jj1, + (SELECT tt2.* FROM t1 as tt2, + JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt; + +--disable_parsing +# psergey: odd name resolution rules? +SELECT * FROM t1 as jj1, + (SELECT tt2.* + FROM + t1 as tt2, + JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN + t1 AS tt3) dt + ORDER BY 1,3 LIMIT 10; + +EXPLAIN SELECT * FROM t1 as jj1, + (SELECT tt2.* FROM t1 as tt2, + JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN + t1 AS tt3) dt ORDER BY 1,3 LIMIT 11; +--enable_parsing + +--disable_parsing +# psergey: crash +SELECT * FROM t1 WHERE id IN + (SELECT id FROM t1 as tt2, + JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl); +EXPLAIN SELECT * FROM t1 WHERE id IN + (SELECT id FROM t1 as tt2, + JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl); +--enable_parsing + +DROP TABLE t1; + +--echo # +--echo # Bug#26760811: WL#8867: MEMORY LEAK REPORTED BY ASAN AND VALGRIND +--echo # +CREATE TABLE t (j JSON); +INSERT INTO t VALUES +('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'), +('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'); +SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt; +PREPARE ps FROM +'SELECT COUNT(*) FROM t, JSON_TABLE(j, ''$[*]'' COLUMNS (i INT PATH ''$'')) AS jt'; +EXECUTE ps; +EXECUTE ps; +DROP PREPARE ps; +DROP TABLE t; + +--echo # +--echo # Bug #26781759: NON-UNIQUE ALIAS ERROR NOT BEING THROWN +--echo # +--error ER_NONUNIQ_TABLE +SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias, +JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias; + +--disable_parsing +# psergey: odd error ! +--echo # +--echo # Bug #26761470: WL#8867: JOIN::MAKE_JOIN_PLAN(): +--echo # ASSERTION `SELECT_LEX->IS_RECURSIVE()' FAILED +--echo # +CREATE TABLE t1 (x INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (j JSON); +INSERT INTO t2 (j) VALUES ('[1,2,3]'); +--sorted_result +SELECT * FROM + t1 RIGHT JOIN + (SELECT o + FROM + t2, + JSON_TABLE(j, '$[*]' COLUMNS (o FOR ORDINALITY)) AS jt + ) AS t3 + ON (t3.o = t1.x); +DROP TABLE t1, t2; +--enable_parsing + +--echo # +--echo # Bug#27152428 JSON_TABLE + PREPARED STATEMENT + VIEW HAS PROBLEM IN DURING RESOLUTION +--echo # + +CREATE TABLE t1 (a INT, b INT); + +CREATE VIEW v2 AS SELECT * FROM t1 LIMIT 2; + +SELECT b + FROM (SELECT * FROM v2) vq1, + JSON_TABLE(CONCAT(vq1.b,'[{\"a\":\"3\"}]'), + '$[*]' COLUMNS (id FOR ORDINALITY, + jpath VARCHAR(100) PATH '$.a', + JEXST INT EXISTS PATH '$.b') + ) AS dt; + +DROP TABLE t1; +DROP VIEW v2; + + +--echo # +--echo # Bug#27189940: CREATE VIEW FAILS ON JSON_TABLE() IN SCHEMA-LESS CONNECTIONS +--echo # BUG#27217897: JSON_TABLE() FAILS IF NO DATABASE SELECTED +--echo # + + +# Connect without a schema name: +connect (conn1,localhost,root,,*NO-ONE*); +connection conn1; + +CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt; +SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt; + +connection default; +disconnect conn1; +use test; + +SHOW CREATE VIEW test.v; +SELECT * FROM test.v; +DROP VIEW test.v; + +--echo # +--echo # Bug#27729112 JSON_TABLE SHOULD DECODE BASE64-ENCODED STRINGS +--echo # +--disable_parsing +SELECT v +FROM JSON_TABLE(JSON_OBJECT('foo', _binary'bar'), '$' + COLUMNS(v VARCHAR(255) PATH '$.foo')) tbl; + +CREATE TABLE t1 (col1 VARCHAR(255) CHARACTER SET ucs2, + col2 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs); +INSERT INTO t1 VALUES ("æ", "ハ"), ("å", "ø"), ("ø", "パ"), ("@", "バ"); + +ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2; +SELECT v value, c cumulfreq +FROM INFORMATION_SCHEMA.COLUMN_STATISTICS, + JSON_TABLE(histogram->'$.buckets', '$[*]' + COLUMNS(v VARCHAR(255) CHARACTER SET ucs2 PATH '$[0]', + c double PATH '$[1]')) hist +WHERE column_name = "col1"; + +SELECT v value, c cumulfreq +FROM INFORMATION_SCHEMA.COLUMN_STATISTICS, + JSON_TABLE(histogram->'$.buckets', '$[*]' + COLUMNS(v VARCHAR(255) CHARACTER SET utf8mb4 PATH '$[0]', + c double PATH '$[1]')) hist +WHERE column_name = "col2"; +DROP TABLE t1; +--enable_parsing + +--echo # +--echo # Bug#28254268: JSON_TABLE() FUNCTION REJECTS SELECT PERMISSIONS +--echo # +CREATE DATABASE db2; +USE db2; +CREATE TABLE t1 (c JSON); +INSERT INTO t1 VALUES('[1,2,3]'); + +CREATE USER user1@localhost; +GRANT SELECT ON db2.t1 TO user1@localhost; + +connect (conn1,localhost,user1,,); +connection conn1; +USE db2; +SELECT t1.c FROM t1; +SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]')) +AS jt; + +disconnect conn1; +connection default; + +DROP USER user1@localhost; +DROP DATABASE db2; + +--echo # +--echo # Bug#27856835 JSON_TABLE RETURNS WRONG DATATYPE WHEN INT-VALUE IS GRATER +--echo # THAN (2^31-1) +--echo # +SELECT id FROM JSON_TABLE('[{"id":"9223372036854775807"}]', '$[*]' COLUMNS +(id BIGINT PATH '$.id')) AS json; + +--echo # As we currently have no way of telling if a JSON string value is +--echo # signed or unsigned, this value will overflow. +SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS +(id BIGINT PATH '$.id')) AS json; + +--echo # Here the JSON value is a NUMERIC value, and we thus know if the value +--echo # is signed or unsigned. +SELECT id FROM JSON_TABLE('[{"id":9223372036854775808}]', '$[*]' COLUMNS +(id BIGINT PATH '$.id')) AS json; + +--echo # If we tell the JSON table column to be unsigned, we get to store the +--echo # full value correctly. +SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS +(id BIGINT UNSIGNED PATH '$.id')) AS json; + +SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS +(id INT UNSIGNED PATH '$.id')) AS json; + +--echo # Check that we preserve the signedness of the columns. +USE test; +CREATE TABLE t1 AS SELECT id, value FROM + JSON_TABLE('[{"id":9223372036854775808, "value":9223372036854775807}]', + '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id', + value BIGINT PATH '$.value')) + AS json; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED +--echo # +connect (conn1,localhost,root,,*NO-ONE*); +CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt; +SELECT * FROM test.v; +DROP VIEW test.v; + +--echo # Check that a user without access to the schema 'foo' cannot query +--echo # a JSON_TABLE view in that schema. +CREATE SCHEMA foo; +CREATE VIEW foo.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt; + +CREATE USER foo@localhost; +connect (con1,localhost,foo,,); +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM foo.v; + +connection default; +disconnect con1; +DROP USER foo@localhost; +DROP SCHEMA foo; + +--echo # Check that a user with access to the schema 'foo' can do a SELECT with +--echo # a JSON_TABLE function. +CREATE SCHEMA foo; +CREATE USER foo@localhost; +GRANT EXECUTE ON foo.* TO foo@localhost; +connect (con1,localhost,foo,,foo); +SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt; +connection default; +disconnect con1; +DROP USER foo@localhost; +DROP SCHEMA foo; + +--echo # +--echo # Bug#27923406 ERROR 1142 (42000) WHEN USING JSON_TABLE +--echo # +CREATE SCHEMA my_schema; + +CREATE USER foo@localhost; +GRANT EXECUTE ON my_schema.* TO foo@localhost; +connect (con1,localhost,foo,,my_schema); + +SELECT + * +FROM + JSON_TABLE( + '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', + "$[*]" COLUMNS( + xval VARCHAR(100) PATH "$.x", + yval VARCHAR(100) PATH "$.y" + ) + ) AS jt1; + +connection default; +disconnect con1; +DROP USER foo@localhost; +DROP SCHEMA my_schema; + +--echo # +--echo # Bug#28538315: JSON_TABLE() COLUMN TYPES DON'T SUPPORT COLLATE CLAUSE +--echo # + +--disable_parsing +# psergey: COLLATE is not supported? +CREATE TABLE t1 SELECT * + FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10) + CHARSET utf8mb4 + PATH '$')) AS jt1; +SHOW CREATE TABLE t1; + +CREATE TABLE t2 SELECT * + FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10) + CHARSET utf8mb4 COLLATE utf8mb4_bin + PATH '$')) AS jt1; +SHOW CREATE TABLE t2; + +CREATE TABLE t3 AS SELECT * + FROM JSON_TABLE('"a"', '$' COLUMNS (a VARCHAR(10) + COLLATE ascii_bin + PATH '$')) jt; +SHOW CREATE TABLE t3; +DROP TABLE t1, t2, t3; +--enable_parsing + + +--echo # +--echo # Bug#28643862 JSON_TABLE'S "COLUMNS" CLAUSE USES +--echo # GLOBAL.CHARACTER_SET_RESULTS DEFAULT CHARSET +--echo # + +SET @@SESSION.character_set_connection = ascii; + +CREATE TABLE t1 SELECT a.col + FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a; + +SHOW CREATE TABLE t1; + +SET @@SESSION.collation_connection = latin1_bin; + +CREATE TABLE t2 SELECT a.col + FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a; + +SHOW CREATE TABLE t2; + +DROP TABLE t1, t2; + +SET @@SESSION.character_set_connection = DEFAULT; + +--echo # +--echo # Bug#28851656: JSON_TABLE RETURN "UNKNOWN DATABASE ''" FROM A FUNCTION +--echo # +CREATE FUNCTION FN_COUNT_ROWS(X JSON) +RETURNS INT DETERMINISTIC + RETURN ( + SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der + ); + +SELECT FN_COUNT_ROWS('[1, 2]') CNT; +SELECT FN_COUNT_ROWS('[1, 2, 3]') CNT; +SELECT FN_COUNT_ROWS('[1, 2, 3, 4]') CNT; + +DROP FUNCTION FN_COUNT_ROWS; + +--echo # +--echo # Bug#30310265: VIEWS LOSE THE CHARACTER SET OF JSON_TABLE'S +--echo # PATH ARGUMENTS +--echo # + +CREATE VIEW v1 AS + SELECT * FROM JSON_TABLE('{"å":{"å":1}}', '$.å' COLUMNS (x INT PATH '$.å')) t; +SELECT * FROM v1; +SET NAMES latin1; +# Used to return zero rows. +SELECT * FROM v1; +SET NAMES DEFAULT; +DROP VIEW v1; + +CREATE VIEW v2 AS + SELECT * FROM JSON_TABLE('{}', '$' COLUMNS ( + x VARCHAR(10) PATH '$.a' DEFAULT '"isn''t here"' ON EMPTY) + ) t; +# SHOW CREATE VIEW and SELECT from the view used to fail with a syntax error. +--disable_parsing +# psergey: print-out quoting +SHOW CREATE VIEW v2; +SELECT * FROM v2; +--enable_parsing +DROP VIEW v2; + +--disable_parsing +# psergey: don't work +# The string literals in JSON_TABLE didn't accept character set +# introducers. Verify that they are accepted now. +SELECT * FROM + JSON_TABLE(JSON_OBJECT(), + _utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' COLUMNS + (y INT PATH _utf8mb4'$.y' + DEFAULT _utf8mb4'1' ON EMPTY + DEFAULT _utf8mb4'2' ON ERROR))) jt; +--enable_parsing + +--echo # +--echo # Bug#30382156: STORED PROCEDURE, JSON_TABLE AND "CONST" JOIN TYPE +--echo # +CREATE TABLE t (id INT PRIMARY KEY, j JSON); +INSERT INTO t VALUES (1, '{"x":1}'); +CREATE PROCEDURE p() + SELECT * FROM t, JSON_TABLE(j, '$' COLUMNS (x INT PATH '$.x')) jt + WHERE id = 1; +CALL p(); +CALL p(); +CALL p(); +DROP PROCEDURE p; +DROP TABLE t; + +--echo # +--echo # WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax +--echo # +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' + DEFAULT '"a"' ON ERROR DEFAULT '"b"' ON EMPTY)) jt; +SELECT * FROM + JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' + NULL ON ERROR NULL ON EMPTY)) jt; +SELECT * FROM + JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' + ERROR ON ERROR ERROR ON EMPTY)) jt; +--echo # +--echo # Bug#30628330: INSERT WITH JSON_TABLE FAILS DUE TO WARNING INVOKED +--echo # WITH ON ERROR CLAUSE +--echo # +CREATE TABLE t(id INT, a TINYINT, b VARCHAR(3), c DATE, d DECIMAL); +# This statement used to fail with "data too long". +INSERT INTO t SELECT * FROM + JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'), + JSON_OBJECT('a', 2, 'b', 'abcd'), + JSON_OBJECT('a', 1000, 'b', 'xyz'), + JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)), + '$[*]' COLUMNS (id FOR ORDINALITY, + a TINYINT PATH '$.a' DEFAULT '111' ON ERROR, + b VARCHAR(3) PATH '$.b' DEFAULT '"ERR"' ON ERROR, + c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR, + d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR) + ) AS jt; +SELECT * FROM t ORDER BY id; +DROP TABLE t; + +--echo # +--echo # Bug#30263373: INCORRECT OUTPUT FROM TABLE_FUNCTION_JSON::PRINT() +--echo # + +CREATE VIEW v AS SELECT * FROM + JSON_TABLE('[123]', '$[*]' COLUMNS (`name with space 1` INT PATH '$', + `name with space 2` FOR ORDINALITY)) jt; +# Used to fail with a syntax error, due to unquoted column names in +# the view definition. +SELECT * FROM v; +DROP VIEW v; + +CREATE VIEW v AS SELECT HEX(x), HEX(y) FROM + JSON_TABLE('["abc"]', '$[*]' COLUMNS (x BINARY(10) PATH '$', + y VARBINARY(10) PATH '$')) jt; +# Used to say CHAR(10) and VARCHAR(10) instead of BINARY(10) and VARBINARY(10). +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS + WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; +SELECT * FROM v; +DROP VIEW v; + +CREATE VIEW v AS SELECT * FROM + JSON_TABLE('[123]', '$[*]' COLUMNS(ti TINYINT PATH '$', + si SMALLINT PATH '$', + mi MEDIUMINT PATH '$', + i INT PATH '$', + bi BIGINT PATH '$', + tiu TINYINT UNSIGNED PATH '$', + siu SMALLINT UNSIGNED PATH '$', + miu MEDIUMINT UNSIGNED PATH '$', + iu INT UNSIGNED PATH '$', + biu BIGINT UNSIGNED PATH '$')) jt; +# Used to lack the UNSIGNED attribute for the unsigned columns. +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS + WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; +SELECT * FROM v; +DROP VIEW v; + +--disable_parsing +CREATE VIEW v AS SELECT * FROM + JSON_TABLE('[]', '$[*]' COLUMNS (g GEOMETRY PATH '$', + ls LINESTRING PATH '$')) AS jt; +# Used to say GEOMETRY for both columns. +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS + WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; +SELECT * FROM v; +DROP VIEW v; +--enable_parsing + +--disable_parsing +CREATE VIEW v AS SELECT * FROM + JSON_TABLE('["abc"]', '$[*]' COLUMNS + (x VARCHAR(10) CHARSET latin1 PATH '$', + y VARCHAR(10) COLLATE utf8mb4_de_pb_0900_ai_ci PATH '$', + z LONGTEXT COLLATE utf8mb4_bin PATH '$')) jt; +# Character set and collation information wasn't included. +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS + WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; +# Used to return the default collation instead of the collation +# specified in the JSON_TABLE column definitions. +SELECT x, y, z, COLLATION(x) c_x, COLLATION(y) c_y, COLLATION(z) c_z FROM v; +DROP VIEW v; +--enable_parsing + +--echo # +--echo # Bug#31345503 JSON_TABLE SHOULD RETURN SQL NULL FOR JSON NULL +--echo # + +SELECT * +FROM +JSON_TABLE( +'[ + {"c1": null, + "c2": [{"c": "c_val", "l": [1,2]}], + "c3": null}, + {"c1": true, + "c2": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [false]}], + "c3": true}, + {"c1": false, + "c2": [{"c": null,"l": [true]}, {"c": "c_val", "l": [null]}], + "c3": false} + ]', + '$[*]' COLUMNS( + top_ord FOR ORDINALITY, + c1path VARCHAR(10) PATH '$.c1' ERROR ON ERROR, + NESTED PATH '$.c2[*]' COLUMNS ( + c2path VARCHAR(10) PATH '$.c' ERROR ON ERROR, + ord FOR ORDINALITY, + NESTED PATH '$.l[*]' COLUMNS (lpath_c VARCHAR(10) PATH '$' ERROR ON ERROR, + lpath_i INT PATH '$' ERROR ON ERROR) + ), + c3path INT PATH '$.c3' ERROR ON ERROR + ) +) as jt; + |