# # WL#8867: Add JSON_TABLE table function # 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; id jpath jexst 1 3 0 2 2 0 3 NULL 1 4 0 0 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b')) `tt` explain format=tree 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; EXPLAIN -> Materialize table function 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; ERROR 42000: Invalid default value for 'jpath' 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; id jpath jsn_path jexst 1 33 {"x": 33} 0 2 2 2 0 3 33 {"x": 33} 1 4 0 0 0 5 66 [1, 2] 0 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 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')) `tt` 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; id jpath_i jpath_r jsn_path jexst 1 3 3 "3" 0 2 2 2 2 0 3 33 33.3 {"x": 33} 1 4 0 0.33 0.33 0 5 66 77.7 "asd" 0 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath_i` AS `jpath_i`,`tt`.`jpath_r` AS `jpath_r`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` 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` double 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')) `tt` 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; id jpath jsn_path jexst 1 33 {"x": 33} 0 2 2 2 0 3 33 {"x": 33} 1 4 0 0 0 5 66 [1, 2] 0 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 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')) `tt` 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; id jpath json_path jexst 1 3 "3" 0 2 2 2 0 3 NULL NULL 1 4 0 0 0 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; id jpath json_path jexst 3 NULL NULL 1 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ref 9 const 1 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`json_path` AS `json_path`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `json_path` json path '$.a', `jexst` int exists path '$.b')) `tt` where (`tt`.`id` = 3) 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 22035: Missing value for JSON_TABLE column 'jpath' 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 2203F: Can't store an array or an object in the scalar JSON_TABLE column 'jpath' select * from json_table( '!#@$!@#$', '$[*]' columns (id for ordinality, jpath varchar(100) path '$.a', jexst int exists path '$.b') ) as tt; ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0. 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 42000: Invalid JSON path expression. The error is around character position 1. 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 42000: Invalid JSON path expression. The error is around character position 1. select * from json_table( '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (id for ordinality, id for ordinality) ) as tt; ERROR 42S21: Duplicate column name 'id' select * from json_table( '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (id for ordinality, _id for ordinality) ) as tt; id _id 1 1 2 2 3 3 4 4 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; id jpath jexst id_n jpath_n 1 3 0 1 1 2 2 0 1 1 3 NULL 1 1 1 4 0 0 1 1 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n` from json_table('[\n {"a":"3", "n": { "l": 1}},\n {"a":2, "n": { "l": 1}},\n {"b":1, "n": { "l": 1}},\n {"a":0, "n": { "l": 1}}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'))) `tt` explain format=json 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; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2.72" }, "table": { "table_name": "tt", "access_type": "ALL", "rows_examined_per_scan": 2, "rows_produced_per_join": 2, "filtered": "100.00", "table_function": "json_table", "using_temporary_table": true, "cost_info": { "read_cost": "2.52", "eval_cost": "0.20", "prefix_cost": "2.73", "data_read_per_join": "1K" }, "used_columns": [ "id", "jpath", "jexst", "id_n", "jpath_n" ] } } } Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n` from json_table('[\n {"a":"3", "n": { "l": 1}},\n {"a":2, "n": { "l": 1}},\n {"b":1, "n": { "l": 1}},\n {"a":0, "n": { "l": 1}}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'))) `tt` 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; id jpath id_n jpath_n id_m jpath_m 1 2 1 1 NULL NULL 1 2 2 11 NULL NULL 1 2 NULL NULL 1 1 1 2 NULL NULL 2 11 2 1 1 2 NULL NULL 2 1 2 22 NULL NULL 2 1 NULL NULL 1 2 2 1 NULL NULL 2 22 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n`,`tt`.`id_m` AS `id_m`,`tt`.`jpath_m` AS `jpath_m` from json_table('[\n {"a":2, "n": [{ "l": 1}, {"l": 11}]},\n {"a":1, "n": [{ "l": 2}, {"l": 22}]}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(50) character set utf8mb4 path '$.a', nested path '$.n[*]' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'), nested path '$.n[*]' columns (`id_m` for ordinality, `jpath_m` varchar(50) character set utf8mb4 path '$.l'))) `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_n for ordinality, jpath_n varchar(50) path '$') ) ) as tt; id jpath jexst id_n jpath_n 1 3 0 1 b 1 3 0 2 a 1 3 0 3 c 2 2 0 1 1 2 2 0 2 2 3 NULL 1 1 zzz 4 0 0 1 0.1 4 0 0 2 0.02 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; id jpath jexst id_n1 jpath_n1 id_n2 jpath_n2 1 3 0 1 b NULL NULL 1 3 0 2 a NULL NULL 1 3 0 3 c NULL NULL 1 3 0 NULL NULL 1 b 1 3 0 NULL NULL 2 a 1 3 0 NULL NULL 3 c 2 2 0 1 1 NULL NULL 2 2 0 2 2 NULL NULL 2 2 0 NULL NULL 1 1 2 2 0 NULL NULL 2 2 3 NULL 1 1 zzz NULL NULL 3 NULL 1 NULL NULL 1 zzz 4 0 0 1 0.1 NULL NULL 4 0 0 2 0.02 NULL NULL 4 0 0 NULL NULL 1 0.1 4 0 0 NULL NULL 2 0.02 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; id1 jpath jexst id2 id3 jpath_3 id4 jpath_4 1 3 0 1 1 b1 NULL NULL 1 3 0 1 2 b2 NULL NULL 1 3 0 1 3 b3 NULL NULL 1 3 0 2 1 a1 NULL NULL 1 3 0 2 2 a2 NULL NULL 1 3 0 3 1 c NULL NULL 1 3 0 NULL NULL NULL 1 {"ll": ["b1", "b2", "b3"]} 1 3 0 NULL NULL NULL 2 {"ll": ["a1", "a2"]} 1 3 0 NULL NULL NULL 3 {"ll": ["c"]} 2 2 0 1 1 1 NULL NULL 2 2 0 1 2 11 NULL NULL 2 2 0 1 3 111 NULL NULL 2 2 0 2 1 2 NULL NULL 2 2 0 NULL NULL NULL 1 {"ll": [1, 11, 111]} 2 2 0 NULL NULL NULL 2 {"ll": [2]} 3 NULL 1 1 1 zzz NULL NULL 3 NULL 1 NULL NULL NULL 1 {"ll": ["zzz"]} 4 0 0 1 1 0.1 NULL NULL 4 0 0 1 2 0.01 NULL NULL 4 0 0 2 1 0.02 NULL NULL 4 0 0 2 2 0.002 NULL NULL 4 0 0 2 3 0.0002 NULL NULL 4 0 0 NULL NULL NULL 1 {"ll": [0.1, 0.01]} 4 0 0 NULL NULL NULL 2 {"ll": [0.02, 0.002, 0.0002]} explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`id1` AS `id1`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id2` AS `id2`,`tt`.`id3` AS `id3`,`tt`.`jpath_3` AS `jpath_3`,`tt`.`id4` AS `id4`,`tt`.`jpath_4` AS `jpath_4` from json_table('[\n {"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},\n {"ll":["c"]} ]},\n {"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},\n {"b":1, "n": [{"ll":["zzz"]}]},\n {"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}\n ]', '$[*]' columns (`id1` for ordinality, `jpath` varchar(100) character set utf8mb4 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) character set utf8mb4 path '$')), nested path '$.n[*]' columns (`id4` for ordinality, `jpath_4` json path '$'))) `tt` ord should be 1,1,1,2, which tells that first two values of 'l' are from the same object, and next two are from different objects 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; apath bpath ord lpath a_val c_val 1 1 a_val c_val 1 2 a_val c_val 1 11 a_val c_val 2 22 explain 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `jt`.`apath` AS `apath`,`jt`.`bpath` AS `bpath`,`jt`.`ord` AS `ord`,`jt`.`lpath` AS `lpath` from json_table('[{"a": "a_val",\n "b": [\n {"c": "c_val",\n "l": [1,2]}\n ]\n }, {"a": "a_val",\n "b": [\n {"c": "c_val",\n "l": [11]},\n {"c": "c_val",\n "l": [22]}\n ]\n }]', '$[*]' columns (`apath` varchar(10) character set utf8mb4 path '$.a', nested path '$.b[*]' columns (`bpath` varchar(10) character set utf8mb4 path '$.c', `ord` for ordinality, nested path '$.l[*]' columns (`lpath` varchar(10) character set utf8mb4 path '$')))) `jt` CREATE TABLE jt( i JSON ); SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt WHERE a=1; i a EXPLAIN SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt WHERE a=1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 1 100.00 NULL 1 SIMPLE tt NULL ref 5 const 1 100.00 Table function: json_table; Using temporary; Using index Warnings: Note 1003 /* select#1 */ select `test`.`jt`.`i` AS `i`,`tt`.`a` AS `a` from `test`.`jt` join json_table(`test`.`jt`.`i`, '$' columns (`a` int path '$')) `tt` where (`tt`.`a` = 1) SELECT * FROM ( SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt WHERE a=1) AS ttt; i a EXPLAIN SELECT * FROM ( SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt WHERE a=1) AS ttt; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 1 100.00 NULL 1 SIMPLE tt NULL ref 5 const 1 100.00 Table function: json_table; Using temporary; Using index Warnings: Note 1003 /* select#1 */ select `test`.`jt`.`i` AS `i`,`tt`.`a` AS `a` from `test`.`jt` join json_table(`test`.`jt`.`i`, '$' columns (`a` int path '$')) `tt` where (`tt`.`a` = 1) DROP TABLE jt; SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON), '$' COLUMNS (dt DATE PATH '$')) as tt; dt NULL SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON), '$' COLUMNS (dt TIME PATH '$')) as tt; dt 11:22:33 SELECT * FROM JSON_TABLE(CAST(CAST("2001.02.03" AS DATE) AS JSON), '$' COLUMNS (dt DATE PATH '$')) as tt; dt 2001-02-03 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; num 123 123 123 SHOW CREATE VIEW v; View Create View character_set_client collation_connection v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`num` AS `num` from json_table('[1,2,3]', '$[*]' columns (`num` int path '$.a' default '123' on empty default '456' on error)) `jt` utf8mb4 utf8mb4_0900_ai_ci DROP VIEW v; SELECT * FROM JSON_TABLE('"asdf"', '$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt; ERROR 22018: Invalid JSON value for CAST to INTEGER from column a at row 1 SELECT * FROM JSON_TABLE('[{"a":1},{"a":2}]', '$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt; ERROR 2203F: Can't store an array or an object in the scalar JSON_TABLE column 'a' SELECT * FROM JSON_TABLE('[{"a":1},{"a":2}]', '$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt; a [1, 2] SELECT * FROM JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt; ERROR 22003: Value is out of range for JSON_TABLE's column 'a' SELECT * FROM JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt; a NULL 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; ERROR 42000: More than supported 16 NESTED PATHs were found in JSON_TABLE '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; id jid val 1 1 1 1 2 3 1 3 5 2 1 2 2 2 4 2 3 6 SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.* FROM t1, JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY, val INT PATH '$')) AS jt; id jid val 1 1 1 1 2 3 1 3 5 2 1 2 2 2 4 2 3 6 EXPLAIN SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.* FROM t1, JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY, val INT PATH '$')) AS jt; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt` 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; id id jid val 1 1 1 1 1 1 2 3 1 1 3 5 1 2 1 1 1 2 2 3 1 2 3 5 2 1 1 2 2 1 2 4 2 1 3 6 2 2 1 2 2 2 2 4 2 2 3 6 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join) Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt` join `test`.`t1` `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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join) 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t2`,`jt`) */ `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt` join `test`.`t1` `t2` SELECT * FROM t1 WHERE id IN (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS (id INT PATH '$')) AS jt); id jd 1 [1, 3, 5] 2 [2, 4, 6] EXPLAIN SELECT * FROM t1 WHERE id IN (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS (id INT PATH '$')) AS jt); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE jt NULL ref 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jd` AS `jd` from `test`.`t1` semi join (json_table('[1,2]', '$[*]' columns (`id` int path '$')) `jt`) where (`jt`.`id` = `test`.`t1`.`id`) SELECT * FROM t1 WHERE id IN (SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS (id INT PATH '$')) AS jt); id jd 1 [1, 3, 5] 2 [2, 4, 6] EXPLAIN SELECT * FROM t1 WHERE id IN (SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS (id INT PATH '$')) AS jt); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE jt NULL ref 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index; FirstMatch(t1) Warnings: Note 1276 Field or reference 'test.t1.jd' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jd` AS `jd` from `test`.`t1` semi join (json_table(`test`.`t1`.`jd`, '$[*]' columns (`id` int path '$')) `jt`) where (`jt`.`id` = `test`.`t1`.`id`) SELECT id, jt1.*, jt2.* FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1, JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2; id data1 id2 1 [1, 3, 5] 1 1 [1, 3, 5] 3 1 [1, 3, 5] 5 2 [2, 4, 6] 2 2 [2, 4, 6] 4 2 [2, 4, 6] 6 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 1 SIMPLE jt1 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary 1 SIMPLE jt2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`jt1`.`data1` AS `data1`,`jt2`.`id2` AS `id2` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$' columns (`data1` json path '$')) `jt1` join json_table(`jt1`.`data1`, '$[*]' columns (`id2` int path '$')) `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; tm dt i f d NULL NULL NULL NULL NULL SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON EMPTY)) jt' at line 2 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON ERROR)) jt' at line 2 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt; ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression' SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt; ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression' SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x DATE PATH '$.x' DEFAULT DATE'2020-01-01' ON EMPTY)) jt; ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression' SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x DATE PATH '$.x' DEFAULT DATE'2020-01-01' ON ERROR)) jt; ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression' # # Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF # SELECT a FROM JSON_TABLE(abc, '$[*]' COLUMNS ( a int path '$.a')) AS jt; ERROR 42S22: Unknown column 'abc' in 'a table function argument' # # Bug#25420680: ASSERTION `THD->IS_ERROR()' FAILED IN SQL/SQL_SELECT.CC # SELECT * FROM JSON_TABLE('{"a":"2017-11-1"}', '$' COLUMNS (jpath DATE PATH '$.a')) AS jt; jpath 2017-11-01 # # Bug#25413826: ASSERTION `TABLE_LIST->ALIAS' FAILED # SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]', '$[*]' COLUMNS ( a int path '$.b')); ERROR 42000: Every table function must have an alias # # Bug#25421464: ASSERTION `!STRCMP(TABLE_REF->TABLE_NAME, ... # CREATE VIEW v1 AS SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]', '$[*]' COLUMNS ( a INT PATH '$.b')) AS jt; SELECT * FROM v1; a 2 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`a` AS `a` from json_table('[{"a": 1, "b": 2}]', '$[*]' columns (`a` int path '$.b')) `jt` utf8mb4 utf8mb4_0900_ai_ci DROP VIEW v1; # # Bug#25427457: ASSERTION `!((*REG_FIELD)->FLAGS & 16)' # SELECT * FROM JSON_TABLE('{"a":"1"}', '$' COLUMNS (jpath JSON PATH '$.a', o FOR ORDINALITY)) AS jt WHERE o = 1; jpath o "1" 1 # # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H # SELECT je,o FROM JSON_TABLE('{"a":"1"}', '$' COLUMNS (o FOR ORDINALITY, je BIGINT EXISTS PATH '$.a')) AS jt GROUP BY je; ERROR 42000: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jt.o' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT je,COUNT(o) FROM JSON_TABLE('{"a":"1"}', '$' COLUMNS (o FOR ORDINALITY, je BIGINT EXISTS PATH '$.a')) AS jt GROUP BY je; je COUNT(o) 1 1 # # Bug#25413194: ASSERTION `!(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE # CREATE TABLE t1 (j JSON); SELECT * FROM t1,JSON_TABLE(t1.j, '$[*]' COLUMNS ( a int path '$.b')) AS jt; j a DROP TABLE t1; # # Bug#25460537:SIG 11 IN NEXT_FAST AT SQL/SQL_LIST.H # 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; id jpath jexst 1 3 0 2 2 0 3 NULL 1 4 0 0 EXECUTE STMT; id jpath jexst 1 3 0 2 2 0 3 NULL 1 4 0 0 DEALLOCATE PREPARE stmt; # # Bug#25522353: SIG 11 IN JOIN::MAKE_JOIN_PLAN | SQL/SQL_OPTIMIZER.CC # CREATE TABLE t1 (id INT, jc JSON); SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; ERROR 42S02: Unknown table 't1' in a table function argument SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id; ERROR 42S02: Unknown table 't1' in a table function argument SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id; id id jc SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; id jc id EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join json_table(`test`.`t1`.`jc`, '$' columns (`id` for ordinality)) `jt` on((`test`.`t1`.`jc` = `jt`.`id`)) where true 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; id jc id jc 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL 1 SIMPLE t1o NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join) 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1o`.`id` AS `id`,`test`.`t1o`.`jc` AS `jc`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join `test`.`t1` `t1o` on((`test`.`t1o`.`id` = `test`.`t1`.`id`)) left join json_table(`test`.`t1`.`jc`, '$' columns (`id` for ordinality)) `jt` on((`test`.`t1`.`jc` = `jt`.`id`)) where true 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 42S22: Unknown column 't1o.jc' in 'a table function argument' 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 42S02: Unknown table 't1o' in a table function argument 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 42S02: Unknown table 't1' in a table function argument 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 42S02: Unknown table 't1' in a table function argument 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 42S22: Unknown column 't1o.jc' in 'a table function argument' 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 42S02: Unknown table 't1o' in a table function argument INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3"); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id INT PATH '$')) as jt ON t1.id=jt.id; id jc id 1 1 1 2 4 NULL 3 3 3 EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id INT PATH '$')) as jt ON t1.id=jt.id; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 1 SIMPLE jt NULL ref 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join json_table(`test`.`t1`.`jc`, '$' columns (`id` int path '$')) `jt` on((`jt`.`id` = `test`.`t1`.`id`)) where true 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; ERROR 42S02: Unknown table 'jt' in a table function argument DROP TABLE t1; 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; c1 c2 c3 c4 c5 c6 c7 c8 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 10 10 10 10 10 10 10 10 11 11 11 11 11 11 11 11 12 12 12 12 12 12 12 12 13 13 13 13 13 13 13 13 14 14 14 14 14 14 14 14 15 15 15 15 15 15 15 15 16 16 16 16 16 16 16 16 17 17 17 17 17 17 17 17 18 18 18 18 18 18 18 18 19 19 19 19 19 19 19 19 20 20 20 20 20 20 20 20 SHOW STATUS LIKE '%tmp%'; Variable_name Value Created_tmp_disk_tables 1 Created_tmp_files 0 Created_tmp_tables 1 set @@max_heap_table_size= @save_heap_size; set @@internal_tmp_mem_storage_engine= @save_mem_se; # # Bug#25504063: EXPLAIN OF JSON_TABLE QUERY USES INTERNAL TEMP TABLES # FLUSH STATUS; SELECT * FROM JSON_TABLE( '[{"a":"3"}]', '$[*]' COLUMNS (id FOR ORDINALITY) ) AS tt; id 1 SHOW STATUS LIKE '%tmp%'; Variable_name Value Created_tmp_disk_tables 0 Created_tmp_files 0 Created_tmp_tables 1 # # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC # SELECT * FROM JSON_TABLE ( ( SELECT a ) , '$.*' COLUMNS (col1 FOR ORDINALITY) ) AS alias1; ERROR 42S22: Unknown column 'a' in 'field list' SELECT * FROM JSON_TABLE ( ( SELECT 1 ) , '$.*' COLUMNS (col1 FOR ORDINALITY) ) AS alias1; ERROR HY000: Incorrect arguments to JSON_TABLE SELECT * FROM JSON_TABLE ( ( SUM(1) ) , '$.*' COLUMNS (col1 FOR ORDINALITY) ) AS alias1; ERROR HY000: Invalid use of group function # # Bug# #25472875: ERROR SHOULD BE THROWN FOR INCORRECT VALUES # SELECT * FROM JSON_TABLE('{"a":"1993-01-01"}', '$' COLUMNS (jp DATE PATH '$.b' DEFAULT '1000' ON EMPTY)) AS jt; ERROR 22003: Value is out of range for JSON_TABLE's column 'jp' # # Bug#25532429: INVALID JSON ERROR NOT THROWN WITH EMPTY TABLES JOIN # CREATE TABLE t1(j JSON); SELECT * FROM t1, JSON_TABLE( 'dqwfjqjf' , '$[*]' COLUMNS (col5 FOR ORDINALITY) ) AS alias7; ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0. DROP TABLE t1; # # Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063 # EXPLAIN SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 CHAR(70) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` char(70) character set utf8mb4 path '$')) `alias2` SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 CHAR(70) PATH '$') ) AS alias2; col18 3.14159 # Too short field causes truncation, error and triggers ON ERROR clause EXPLAIN SELECT * FROM JSON_TABLE ('["3.14159"]', '$[*]' COLUMNS (col18 CHAR(6) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('["3.14159"]', '$[*]' columns (`col18` char(6) character set utf8mb4 path '$')) `alias2` SELECT * FROM JSON_TABLE ('["3.14159"]', '$[*]' COLUMNS (col18 CHAR(6) PATH '$') ) AS alias2; col18 NULL #Truncated space doesn't trigger ON ERROR EXPLAIN SELECT * FROM JSON_TABLE ('["3.14159 "]', '$[*]' COLUMNS (col18 CHAR(7) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('["3.14159 "]', '$[*]' columns (`col18` char(7) character set utf8mb4 path '$')) `alias2` SELECT * FROM JSON_TABLE ('["3.14159 "]', '$[*]' COLUMNS (col18 CHAR(7) PATH '$') ) AS alias2; col18 3.14159 EXPLAIN SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 CHAR(255) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` char(255) character set utf8mb4 path '$')) `alias2` SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 CHAR(255) PATH '$') ) AS alias2; col18 3.14159 EXPLAIN SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 VARCHAR(70) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` varchar(70) character set utf8mb4 path '$')) `alias2` SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 VARCHAR(70) PATH '$') ) AS alias2; col18 3.14159 EXPLAIN SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 VARCHAR(255) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` varchar(255) character set utf8mb4 path '$')) `alias2` SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 VARCHAR(255) PATH '$') ) AS alias2; col18 3.14159 EXPLAIN SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 FLOAT PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` float path '$')) `alias2` SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 FLOAT PATH '$') ) AS alias2; col18 3.14159 EXPLAIN SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 DOUBLE PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` double path '$')) `alias2` SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 DOUBLE PATH '$') ) AS alias2; col18 3.14159 EXPLAIN SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` decimal(3,3) path '$')) `alias2` SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$') ) AS alias2; col18 NULL SELECT * FROM JSON_TABLE ('[3.14159]', '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR) ) AS alias2; ERROR 22003: Value is out of range for JSON_TABLE's column 'col18' EXPLAIN SELECT * FROM JSON_TABLE ('[0.9]', '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$') ) AS alias2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[0.9]', '$[*]' columns (`col18` decimal(3,3) path '$')) `alias2` SELECT * FROM JSON_TABLE ('[0.9]', '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$') ) AS alias2; col18 0.900 SELECT * FROM JSON_TABLE ('["asdf","ghjk"]', '$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$' DEFAULT "3.14159" ON ERROR) ) AS alias2; col18 3.142 3.142 Warnings: Note 1265 Data truncated for column 'col18' at row 1 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; jd col18 ["asdf"] 3.142 ["ghjk"] 3.142 Warnings: Note 1265 Data truncated for column 'col18' at row 1 DROP TABLE t1; # # Bug#25540027: SIG 11 IN FIND_FIELD_IN_TABLE | SQL/SQL_BASE.CC # CREATE TABLE t1(c1 JSON); UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 SET jt1.a=1; ERROR HY000: The target table jt1 of the UPDATE is not updatable DELETE JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 FROM t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 FROM t1' at line 1 DELETE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 USING t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1 USING t1' at line 1 DROP TABLE t1; # # Bug#25547244: ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET( # 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; field1 DROP TABLE t1; # # Bug#25540675: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED # 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; j col11 [1, 2, 3] NULL [1, 2, 4] NULL DROP TABLE j1; # # Bug#25584335: SIG 11 IN TABLE_LIST::FETCH_NUMBER_OF_ROWS # 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; field1 DEALLOCATE PREPARE stmt; DROP TABLE t1; # # Bug#25604048: COLUMN NAMES WITH SAME 33-CHAR PREFIX ARE EQUAL # 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; column_name_is_thirty_four_or_more column_name_is_thirty_four_or_more_yes_indeed test test SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS( `column_name_is_thirty_four_or_more ` VARCHAR(17) PATH '$.key1' ) ) AS alias1; ERROR 42000: Incorrect column name 'column_name_is_thirty_four_or_more ' # # Bug#25604404: JSON_TABLE MORE RESTRICTIVE WITH IDENTIFIERS THAN # CREATE TABLE # SELECT * FROM JSON_TABLE( '[1, 2]', '$' COLUMNS( one INT PATH '$[0]', two INT PATH '$[1]' )) AS jt; one two 1 2 # # Bug#25588450: SIG 6 IN JSON_WRAPPER::SEEK|SQL/JSON_DOM.CC # CREATE TABLE t1(c VARCHAR(10)) ENGINE=MEMORY; INSERT INTO t1 VALUES('fiheife'); SELECT * FROM `t1` AS alias1, JSON_TABLE ( `c` , '$[*]' COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2 WHERE jtcol1 <= 'kjfh'; ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 1. DROP TABLE t1; # # Bug#25587754: ASSERTION `FIXED == 0 || BASIC_CONST_ITEM()' FAILED # PREPARE stmt FROM "SELECT * FROM JSON_TABLE ( '[1,2]', '$[*]' COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2"; EXECUTE stmt; jtcol1 NULL NULL DEALLOCATE PREPARE stmt; # # Bug#25584593: UNABLE TO USE JSON_TABLE() ON TEXT/BLOB JSON DATA # SELECT * FROM JSON_TABLE (NULL, '$.k' COLUMNS (id FOR ORDINALITY)) AS aLias; id SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias; id SET @myjson = '{"k": 42}'; SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias; id 1 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; id text SELECT alias.* FROM t1, JSON_TABLE (t1.ty, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias; id tinytext SELECT alias.* FROM t1, JSON_TABLE (t1.tm, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias; id mediumtext SELECT alias.* FROM t1, JSON_TABLE (t1.tl, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias; id longtext SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TEXT PATH '$')) AS alias; id text SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TINYTEXT PATH '$')) AS alias; id text SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id MEDIUMTEXT PATH '$')) AS alias; id text SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id LONGTEXT PATH '$')) AS alias; id text SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias; blb asd123 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` blob path '$')) `alias` EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` tinyblob path '$')) `alias` EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` mediumblob path '$')) `alias` EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` longblob path '$')) `alias` EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` text character set utf8mb4 path '$')) `alias` EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` tinytext character set utf8mb4 path '$')) `alias` EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` mediumtext character set utf8mb4 path '$')) `alias` EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` longtext character set utf8mb4 path '$')) `alias` SELECT * FROM (SELECT CAST(blb AS JSON) jf FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) AS jti) AS dt, JSON_TABLE (jf, '$' COLUMNS (blb BLOB PATH '$')) AS jto; ERROR HY000: Incorrect arguments to JSON_TABLE DROP TABLE t1; # # Bug#26500384: ASSERT FAILURE IN QUERY WITH WINDOW FUNCTION AND # JSON_TABLE # CREATE TABLE t (x INT); INSERT INTO t VALUES (1), (2), (3); SELECT MAX(t.x) OVER () m, jt.* FROM t, JSON_TABLE(JSON_ARRAY(m), '$[*]' COLUMNS (i INT PATH '$')) jt; ERROR 42S22: Unknown column 'm' in 'a table function argument' DROP TABLE t; # # Bug#26583283: ASSERTION `!THD->IS_ERROR()' FAILED IN SQL_RESOLVER.CC # EXPLAIN SELECT * FROM JSON_TABLE('null', '$' COLUMNS(AA DECIMAL PATH '$')) tt; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `tt`.`AA` AS `AA` from json_table('null', '$' columns (`AA` decimal(10,0) path '$')) `tt` CREATE VIEW v1 AS SELECT * FROM JSON_TABLE ( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt; SELECT * FROM v1; c1 NULL EXPLAIN SELECT * FROM v1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `jt`.`c1` AS `c1` from json_table('null', '$' columns (`c1` decimal(10,0) path '$')) `jt` DROP VIEW v1; # # Bug#25822408: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED # PREPARE stmt FROM "SELECT * FROM JSON_TABLE('{\"a\":1}','$' COLUMNS (c1 CHAR(20) PATH '$.b' ERROR ON EMPTY)) jt"; EXECUTE stmt; ERROR 22035: Missing value for JSON_TABLE column 'c1' EXECUTE stmt; ERROR 22035: Missing value for JSON_TABLE column 'c1' # # Bug#25594571: CRASH AT ITEM::CONST_ITEM|SQL/ITEM.H # 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; i i i 1 1 1 2 2 2 3 3 3 4 4 NULL 5 5 NULL 6 6 NULL 7 7 NULL EXECUTE stmt; i i i 1 1 1 2 2 2 3 3 3 4 4 NULL 5 5 NULL 6 6 NULL 7 7 NULL DROP TABLE t1; # # Bug#26648617: ASSERTION `IS_VIEW_OR_DERIVED() && # USES_MATERIALIZATION()' FAILED. # CREATE TABLE t1 ( col_varchar_key varchar(1) DEFAULT NULL ) ENGINE=InnoDB; 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); col_varchar_key 4 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); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY innr1 NULL index_subquery 43 func 4 100.00 Table function: json_table; Using temporary; Using where; Using index; Full scan on NULL key Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (`test`.`t1`.`col_varchar_key`,(((`test`.`t1`.`col_varchar_key`) in innr1 on checking NULL where (outer_field_is_not_null, (((`test`.`t1`.`col_varchar_key`) = `innr1`.`col_varchar_key`) or (`innr1`.`col_varchar_key` is null)), true) having (outer_field_is_not_null, (`innr1`.`col_varchar_key`), true))) is false) 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); col_varchar_key 1 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); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE innr1 NULL ref 43 test.t1.col_varchar_key 2 100.00 Table function: json_table; Using temporary; Using where; Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` semi join (json_table('[{"col_key": 1},{"col_key": 2}]', '$[*]' columns (`col_varchar_key` varchar(10) character set utf8mb4 path '$.col_key')) `innr1`) where (`test`.`t1`.`col_varchar_key` = `innr1`.`col_varchar_key`) DROP TABLE t1; # # Bug#26711551: WL8867:CONDITIONAL JUMP IN JSON_TABLE_COLUMN::CLEANUP # 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; x y 1 2 DROP TABLE t; # # Bug#26679671: SIG 11 IN JSON_BINARY::PARSE_BINARY() # 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 OK ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SELECT * FROM t1 as jj1, (SELECT tt2.* FROM t1 as tt2, JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt; id f1 id f1 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 2 {"1": 2} 1 {"1": 1} 3 {"1": 3} 1 {"1": 1} 4 {"1": 4} 1 {"1": 1} 5 {"1": 5} 1 {"1": 1} 6 {"1": 6} 2 {"1": 2} 1 {"1": 1} 2 {"1": 2} 2 {"1": 2} 2 {"1": 2} 3 {"1": 3} 2 {"1": 2} 4 {"1": 4} 2 {"1": 2} 5 {"1": 5} 2 {"1": 2} 6 {"1": 6} 3 {"1": 3} 1 {"1": 1} 3 {"1": 3} 2 {"1": 2} 3 {"1": 3} 3 {"1": 3} 3 {"1": 3} 4 {"1": 4} 3 {"1": 3} 5 {"1": 5} 3 {"1": 3} 6 {"1": 6} 4 {"1": 4} 1 {"1": 1} 4 {"1": 4} 2 {"1": 2} 4 {"1": 4} 3 {"1": 3} 4 {"1": 4} 4 {"1": 4} 4 {"1": 4} 5 {"1": 5} 4 {"1": 4} 6 {"1": 6} 5 {"1": 5} 1 {"1": 1} 5 {"1": 5} 2 {"1": 2} 5 {"1": 5} 3 {"1": 3} 5 {"1": 5} 4 {"1": 4} 5 {"1": 5} 5 {"1": 5} 5 {"1": 5} 6 {"1": 6} 6 {"1": 6} 1 {"1": 1} 6 {"1": 6} 2 {"1": 2} 6 {"1": 6} 3 {"1": 3} 6 {"1": 6} 4 {"1": 4} 6 {"1": 6} 5 {"1": 5} 6 {"1": 6} 6 {"1": 6} EXPLAIN SELECT * FROM t1 as jj1, (SELECT tt2.* FROM t1 as tt2, JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE jj1 NULL ALL NULL NULL NULL NULL 6 100.00 NULL 1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join) 1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`jj1`.`id` AS `id`,`test`.`jj1`.`f1` AS `f1`,`test`.`tt2`.`id` AS `id`,`test`.`tt2`.`f1` AS `f1` from `test`.`t1` `jj1` join `test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`id` for ordinality)) `tbl` 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; id f1 id f1 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 1 {"1": 1} 2 {"1": 2} 1 {"1": 1} 2 {"1": 2} 1 {"1": 1} 2 {"1": 2} 1 {"1": 1} 2 {"1": 2} 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; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE jj1 NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join) 1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary 1 SIMPLE tt3 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join) Warnings: Note 1003 /* select#1 */ select `test`.`jj1`.`id` AS `id`,`test`.`jj1`.`f1` AS `f1`,`test`.`tt2`.`id` AS `id`,`test`.`tt2`.`f1` AS `f1` from `test`.`t1` `jj1` join `test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`id` for ordinality)) `tbl` straight_join `test`.`t1` `tt3` order by `test`.`jj1`.`id`,`test`.`tt2`.`id` limit 11 SELECT * FROM t1 WHERE id IN (SELECT id FROM t1 as tt2, JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl); id f1 1 {"1": 1} 2 {"1": 2} 3 {"1": 3} 4 {"1": 4} 5 {"1": 5} 6 {"1": 6} EXPLAIN SELECT * FROM t1 WHERE id IN (SELECT id FROM t1 as tt2, JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (hash join) 1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; End temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` semi join (`test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`jf` for ordinality)) `tbl`) where (`test`.`t1`.`id` = `test`.`tt2`.`id`) DROP TABLE t1; # # Bug#26760811: WL#8867: MEMORY LEAK REPORTED BY ASAN AND VALGRIND # 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; COUNT(*) 34 PREPARE ps FROM 'SELECT COUNT(*) FROM t, JSON_TABLE(j, ''$[*]'' COLUMNS (i INT PATH ''$'')) AS jt'; EXECUTE ps; COUNT(*) 34 EXECUTE ps; COUNT(*) 34 DROP PREPARE ps; DROP TABLE t; # # Bug #26781759: NON-UNIQUE ALIAS ERROR NOT BEING THROWN # SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias, JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias; ERROR 42000: Not unique table/alias: 'jalias' # # Bug #26761470: WL#8867: JOIN::MAKE_JOIN_PLAN(): # ASSERTION `SELECT_LEX->IS_RECURSIVE()' FAILED # CREATE TABLE t1 (x INT); INSERT INTO t1 VALUES (1); CREATE TABLE t2 (j JSON); INSERT INTO t2 (j) VALUES ('[1,2,3]'); 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); x o 1 1 NULL 2 NULL 3 DROP TABLE t1, t2; # # Bug#27152428 JSON_TABLE + PREPARED STATEMENT + VIEW HAS PROBLEM IN DURING RESOLUTION # 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; b DROP TABLE t1; DROP VIEW v2; # # Bug#27189940: CREATE VIEW FAILS ON JSON_TABLE() IN SCHEMA-LESS CONNECTIONS # BUG#27217897: JSON_TABLE() FAILS IF NO DATABASE SELECTED # 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; num 1 2 3 use test; SHOW CREATE VIEW test.v; View Create View character_set_client collation_connection v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`num` AS `num` from json_table('[1,2,3]', '$[*]' columns (`num` int path '$[0]')) `jt` utf8mb4 utf8mb4_0900_ai_ci SELECT * FROM test.v; num 1 2 3 DROP VIEW test.v; # # Bug#27729112 JSON_TABLE SHOULD DECODE BASE64-ENCODED STRINGS # SELECT v FROM JSON_TABLE(JSON_OBJECT('foo', _binary'bar'), '$' COLUMNS(v VARCHAR(255) PATH '$.foo')) tbl; v bar 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; Table Op Msg_type Msg_text test.t1 histogram status Histogram statistics created for column 'col1'. test.t1 histogram status Histogram statistics created for column '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"; value cumulfreq @ 0.25 å 0.5 æ 0.75 ø 1 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"; value cumulfreq ø 0.25 ハ 0.5 バ 0.75 パ 1 DROP TABLE t1; # # Bug#28254268: JSON_TABLE() FUNCTION REJECTS SELECT PERMISSIONS # 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; USE db2; SELECT t1.c FROM t1; c [1, 2, 3] SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt; num 1 2 3 DROP USER user1@localhost; DROP DATABASE db2; # # Bug#27856835 JSON_TABLE RETURNS WRONG DATATYPE WHEN INT-VALUE IS GRATER # THAN (2^31-1) # SELECT id FROM JSON_TABLE('[{"id":"9223372036854775807"}]', '$[*]' COLUMNS (id BIGINT PATH '$.id')) AS json; id 9223372036854775807 # As we currently have no way of telling if a JSON string value is # signed or unsigned, this value will overflow. SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS (id BIGINT PATH '$.id')) AS json; id -9223372036854775808 # Here the JSON value is a NUMERIC value, and we thus know if the value # is signed or unsigned. SELECT id FROM JSON_TABLE('[{"id":9223372036854775808}]', '$[*]' COLUMNS (id BIGINT PATH '$.id')) AS json; id NULL # If we tell the JSON table column to be unsigned, we get to store the # full value correctly. SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id')) AS json; id 9223372036854775808 SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS (id INT UNSIGNED PATH '$.id')) AS json; id 2147483648 # 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; Table Create Table t1 CREATE TABLE `t1` ( `id` bigint unsigned DEFAULT NULL, `value` bigint DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t1; # # Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED # CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt; SELECT * FROM test.v; num 1 2 3 DROP VIEW test.v; # Check that a user without access to the schema 'foo' cannot query # 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; SELECT * FROM foo.v; ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v' DROP USER foo@localhost; DROP SCHEMA foo; # Check that a user with access to the schema 'foo' can do a SELECT with # a JSON_TABLE function. CREATE SCHEMA foo; CREATE USER foo@localhost; GRANT EXECUTE ON foo.* TO foo@localhost; SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt; 1 1 1 1 DROP USER foo@localhost; DROP SCHEMA foo; # # Bug#27923406 ERROR 1142 (42000) WHEN USING JSON_TABLE # CREATE SCHEMA my_schema; CREATE USER foo@localhost; GRANT EXECUTE ON my_schema.* TO foo@localhost; 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; xval yval 2 8 3 7 4 6 DROP USER foo@localhost; DROP SCHEMA my_schema; # # Bug#28538315: JSON_TABLE() COLUMN TYPES DON'T SUPPORT COLLATE CLAUSE # CREATE TABLE t1 SELECT * FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10) CHARSET utf8mb4 PATH '$')) AS jt1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `x` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE t2 SELECT * FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_bin PATH '$')) AS jt1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `x` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE t3 AS SELECT * FROM JSON_TABLE('"a"', '$' COLUMNS (a VARCHAR(10) COLLATE ascii_bin PATH '$')) jt; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( `a` varchar(10) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t1, t2, t3; # # Bug#28643862 JSON_TABLE'S "COLUMNS" CLAUSE USES # GLOBAL.CHARACTER_SET_RESULTS DEFAULT CHARSET # 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; Table Create Table t1 CREATE TABLE `t1` ( `col` varchar(10) CHARACTER SET ascii DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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; Table Create Table t2 CREATE TABLE `t2` ( `col` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t1, t2; SET @@SESSION.character_set_connection = DEFAULT; # # Bug#28851656: JSON_TABLE RETURN "UNKNOWN DATABASE ''" FROM A FUNCTION # 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; CNT 2 SELECT FN_COUNT_ROWS('[1, 2, 3]') CNT; CNT 3 SELECT FN_COUNT_ROWS('[1, 2, 3, 4]') CNT; CNT 4 DROP FUNCTION FN_COUNT_ROWS; # # Bug#30310265: VIEWS LOSE THE CHARACTER SET OF JSON_TABLE'S # PATH ARGUMENTS # CREATE VIEW v1 AS SELECT * FROM JSON_TABLE('{"å":{"å":1}}', '$.å' COLUMNS (x INT PATH '$.å')) t; SELECT * FROM v1; x 1 SET NAMES latin1; SELECT * FROM v1; x 1 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 v2; View Create View character_set_client collation_connection v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t`.`x` AS `x` from json_table('{}', '$' columns (`x` varchar(10) character set utf8mb4 path '$.a' default '"isn\'t here"' on empty)) `t` utf8mb4 utf8mb4_0900_ai_ci SELECT * FROM v2; x isn't here DROP VIEW v2; 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; y NULL # # Bug#30382156: STORED PROCEDURE, JSON_TABLE AND "CONST" JOIN TYPE # 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(); id j x 1 {"x": 1} 1 CALL p(); id j x 1 {"x": 1} 1 CALL p(); id j x 1 {"x": 1} 1 DROP PROCEDURE p; DROP TABLE t; # # WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax # SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' DEFAULT '"a"' ON ERROR DEFAULT '"b"' ON EMPTY)) jt; x b Warnings: Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead. SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' NULL ON ERROR NULL ON EMPTY)) jt; x NULL Warnings: Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead. SELECT * FROM JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' ERROR ON ERROR ERROR ON EMPTY)) jt; x c Warnings: Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead. # # Bug#30628330: INSERT WITH JSON_TABLE FAILS DUE TO WARNING INVOKED # WITH ON ERROR CLAUSE # CREATE TABLE t(id INT, a TINYINT, b VARCHAR(3), c DATE, d DECIMAL); 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; id a b c d 1 1 abc NULL NULL 2 2 ERR NULL NULL 3 111 xyz NULL NULL 4 NULL NULL 2001-01-01 999 DROP TABLE t; # # Bug#30263373: INCORRECT OUTPUT FROM TABLE_FUNCTION_JSON::PRINT() # CREATE VIEW v AS SELECT * FROM JSON_TABLE('[123]', '$[*]' COLUMNS (`name with space 1` INT PATH '$', `name with space 2` FOR ORDINALITY)) jt; SELECT * FROM v; name with space 1 name with space 2 123 1 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; SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; VIEW_DEFINITION select hex(`jt`.`x`) AS `HEX(x)`,hex(`jt`.`y`) AS `HEX(y)` from json_table('["abc"]', '$[*]' columns (`x` binary(10) path '$', `y` varbinary(10) path '$')) `jt` SELECT * FROM v; HEX(x) HEX(y) 61626300000000000000 616263 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; SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; VIEW_DEFINITION select `jt`.`ti` AS `ti`,`jt`.`si` AS `si`,`jt`.`mi` AS `mi`,`jt`.`i` AS `i`,`jt`.`bi` AS `bi`,`jt`.`tiu` AS `tiu`,`jt`.`siu` AS `siu`,`jt`.`miu` AS `miu`,`jt`.`iu` AS `iu`,`jt`.`biu` AS `biu` 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` SELECT * FROM v; ti si mi i bi tiu siu miu iu biu 123 123 123 123 123 123 123 123 123 123 DROP VIEW v; CREATE VIEW v AS SELECT * FROM JSON_TABLE('[]', '$[*]' COLUMNS (g GEOMETRY PATH '$', ls LINESTRING PATH '$')) AS jt; SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; VIEW_DEFINITION select `jt`.`g` AS `g`,`jt`.`ls` AS `ls` from json_table('[]', '$[*]' columns (`g` geometry path '$', `ls` linestring path '$')) `jt` SELECT * FROM v; g ls DROP VIEW v; 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; SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v'; VIEW_DEFINITION select `jt`.`x` AS `x`,`jt`.`y` AS `y`,`jt`.`z` AS `z` from json_table('["abc"]', '$[*]' columns (`x` varchar(10) character set latin1 path '$', `y` varchar(10) character set utf8mb4 collate utf8mb4_de_pb_0900_ai_ci path '$', `z` longtext character set utf8mb4 collate utf8mb4_bin path '$')) `jt` SELECT x, y, z, COLLATION(x) c_x, COLLATION(y) c_y, COLLATION(z) c_z FROM v; x y z c_x c_y c_z abc abc abc latin1_swedish_ci utf8mb4_de_pb_0900_ai_ci utf8mb4_bin DROP VIEW v; # # Bug#31345503 JSON_TABLE SHOULD RETURN SQL NULL FOR JSON NULL # 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; top_ord c1path c2path ord lpath_c lpath_i c3path 1 NULL c_val 1 1 1 NULL 1 NULL c_val 1 2 2 NULL 2 true c_val 1 11 11 1 2 true c_val 2 false 0 1 3 false NULL 1 true 1 0 3 false c_val 2 NULL NULL 0 # # Bug #31327187 UBSAN: JSON_TABLE: NULL POINTER PASSED AS ARGUMENT 2, # WHICH IS DECLARED TO NEVER BE NULL # SELECT HEX(a) FROM JSON_TABLE( '[{"E":{"e":true,"~":1,"S3":"sTa"},"r":3,"":6.7},"",6.5]', '$' COLUMNS(a BINARY(5) PATH '$[1]' NULL ON EMPTY) ) e; HEX(a) 0000000000