summaryrefslogtreecommitdiff
path: root/mysql-test/suite/json/r/json_table_mysql.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/json/r/json_table_mysql.result')
-rw-r--r--mysql-test/suite/json/r/json_table_mysql.result1662
1 files changed, 1662 insertions, 0 deletions
diff --git a/mysql-test/suite/json/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result
new file mode 100644
index 00000000000..da7aa70be2b
--- /dev/null
+++ b/mysql-test/suite/json/r/json_table_mysql.result
@@ -0,0 +1,1662 @@
+#
+# 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
+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;
+id jpath jexst
+1 NULL 0
+2 2 0
+3 NULL 1
+4 0 0
+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
+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 0 0 "asd" 0
+Warnings:
+Warning 1366 Incorrect integer value: 'asd' for column ``.`(temporary)`.`jpath_i` at row 5
+Warning 1366 Incorrect double value: 'asd' for column ``.`(temporary)`.`jpath_r` at row 5
+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
+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
+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 HY000: Field 'jpath' can't be set for JSON_TABLE 'tt'.
+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 HY000: Can't store an array or an object in the scalar column 'jpath' of JSON_TABLE 'tt'.
+select * from
+json_table(
+'!#@$!@#$',
+'$[*]' columns (id for ordinality,
+jpath varchar(100) path '$.a',
+jexst int exists path '$.b')
+) as tt;
+ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at 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 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,
+ "table": {
+ "table_name": "tt",
+ "access_type": "ALL",
+ "rows": 40,
+ "filtered": 100,
+ "table_function": "json_table"
+ }
+ }
+}
+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
+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]}
+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
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP TABLE jt;
+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(11) PATH '$.a' DEFAULT '123' ON EMPTY DEFAULT '456' ON ERROR)) `jt` latin1 latin1_swedish_ci
+DROP VIEW v;
+SELECT * FROM JSON_TABLE('"asdf"',
+'$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
+a
+0
+Warnings:
+Warning 1366 Incorrect integer value: 'asdf' for column ``.`(temporary)`.`a` at row 1
+SELECT * FROM
+JSON_TABLE('[{"a":1},{"a":2}]',
+'$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
+ERROR HY000: Can't store multiple matches of the path in the column 'a' of JSON_TABLE 'jt'.
+SELECT * FROM
+JSON_TABLE('[{"a":1},{"a":2}]',
+'$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
+ERROR HY000: Can't store multiple matches of the path in the column 'a' of JSON_TABLE 'jt'.
+SELECT * FROM
+JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
+a
+9.9
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM
+JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt;
+a
+9.9
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+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;
+i0 i1 i2 i3 i4 i5 i6 i7 i8 i9 i10 i11 i12 i13 i14 i15 i16 i17 i18 i19 i20
+1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL NULL
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
+1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
+1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
+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 type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED jt ALL NULL NULL NULL NULL 40 Table function: json_table
+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
+00:00:00 0000-00-00 0 0 0
+Warnings:
+Warning 1265 Data truncated for column 'tm' at row 1
+Warning 1265 Data truncated for column 'dt' at row 1
+Warning 1366 Incorrect integer value: 'asdf' for column ``.`(temporary)`.`i` at row 1
+Warning 1366 Incorrect double value: 'asdf' for column ``.`(temporary)`.`f` at row 1
+Warning 1366 Incorrect decimal value: 'asdf' for column ``.`(temporary)`.`d` at row 1
+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 MariaDB 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 MariaDB 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;
+x
+0
+SELECT * FROM
+JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
+x
+NULL
+SELECT * FROM
+JSON_TABLE('{}', '$' COLUMNS (x DATE
+PATH '$.x'
+ DEFAULT DATE'2020-01-01' ON EMPTY)) jt;
+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 'DATE'2020-01-01' ON EMPTY)) jt' at line 4
+SELECT * FROM
+JSON_TABLE('{}', '$' COLUMNS (x DATE
+PATH '$.x'
+ DEFAULT DATE'2020-01-01' ON ERROR)) jt;
+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 'DATE'2020-01-01' ON ERROR)) jt' at line 4
+#
+# 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 'JSON_TABLE 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 HY000: 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(11) PATH '$.b')) `jt` latin1 latin1_swedish_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;
+je o
+1 1
+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 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument'
+SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
+(id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id;
+ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE 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 type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+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 'JSON_TABLE 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 42S22: Unknown column 't1o.jc' in 'JSON_TABLE 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 42S22: Unknown column 't1.jc' in 'JSON_TABLE 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 42S22: Unknown column 't1.jc' in 'JSON_TABLE 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 'JSON_TABLE 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 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument'
+INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3");
+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 'jc'
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where
+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 42S22: Unknown column 'jt.id' in 'JSON_TABLE argument'
+DROP TABLE t1;
+#
+# 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;
+col1
+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;
+jp
+0000-00-00
+Warnings:
+Warning 1265 Data truncated for column 'jp' at row 1
+#
+# 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;
+j col5
+DROP TABLE t1;
+#
+# Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063
+#
+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
+SELECT * FROM
+JSON_TABLE('["3.14159"]',
+'$[*]' COLUMNS (col18 CHAR(6) PATH '$')
+) AS alias2;
+col18
+3.1415
+Warnings:
+Warning 1265 Data truncated for column 'col18' at row 1
+#Truncated space doesn't trigger ON ERROR
+SELECT * FROM
+JSON_TABLE('["3.14159 "]',
+'$[*]' COLUMNS (col18 CHAR(7) PATH '$')
+) AS alias2;
+col18
+3.14159
+SELECT * FROM
+JSON_TABLE('[3.14159]',
+'$[*]' COLUMNS (col18 CHAR(255) PATH '$')
+) AS alias2;
+col18
+3.14159
+SELECT * FROM
+JSON_TABLE('[3.14159]',
+'$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
+) AS alias2;
+col18
+3.14159
+SELECT * FROM
+JSON_TABLE('[3.14159]',
+'$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
+) AS alias2;
+col18
+3.14159
+SELECT * FROM
+JSON_TABLE('[3.14159]',
+'$[*]' COLUMNS (col18 FLOAT PATH '$')
+) AS alias2;
+col18
+3.14159
+SELECT * FROM
+JSON_TABLE('[3.14159]',
+'$[*]' COLUMNS (col18 DOUBLE PATH '$')
+) AS alias2;
+col18
+3.14159
+SELECT * FROM
+JSON_TABLE('[3.14159]',
+'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
+) AS alias2;
+col18
+0.999
+Warnings:
+Warning 1264 Out of range value for column 'col18' at row 1
+SELECT * FROM
+JSON_TABLE('[3.14159]',
+'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)
+) AS alias2;
+col18
+0.999
+Warnings:
+Warning 1264 Out of range value for column 'col18' at row 1
+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
+0.000
+0.000
+Warnings:
+Warning 1366 Incorrect decimal value: 'asdf' for column ``.`(temporary)`.`col18` at row 1
+Warning 1366 Incorrect decimal value: 'ghjk' for column ``.`(temporary)`.`col18` at row 2
+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"] 0.000
+["ghjk"] 0.000
+Warnings:
+Warning 1366 Incorrect decimal value: 'asdf' for column ``.`(temporary)`.`col18` at row 1
+Warning 1366 Incorrect decimal value: 'ghjk' for column ``.`(temporary)`.`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 MariaDB server version for the right syntax to use near '(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 MariaDB server version for the right syntax to use near '(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);
+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;
+column_name_is_thirty_four_or_more
+test
+#
+# 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 HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' 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 type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
+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 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 'JSON) jf FROM
+JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) AS jti)...' at line 2
+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 'JSON_TABLE 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 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( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt;
+SELECT * FROM v1;
+c1
+NULL
+EXPLAIN SELECT * FROM v1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
+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 HY000: Field 'c1' can't be set for JSON_TABLE 'jt'.
+EXECUTE stmt;
+ERROR HY000: Field 'c1' can't be set for JSON_TABLE 'jt'.
+#
+# 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 type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY innr1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where
+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 type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY innr1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+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 Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 'f1'
+test.t1 analyze status OK
+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 Table is already up to date
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE jj1 ALL NULL NULL NULL NULL 6
+1 SIMPLE tt2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
+1 SIMPLE tbl ALL NULL NULL NULL NULL 40 Table function: json_table
+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 type possible_keys key key_len ref rows Extra
+1 SIMPLE jj1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE tt2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
+1 SIMPLE tbl ALL NULL NULL NULL NULL 40 Table function: json_table
+1 SIMPLE tt3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
+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 type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 6
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED tt2 ALL NULL NULL NULL NULL 6
+2 MATERIALIZED tbl ALL NULL NULL NULL NULL 40 Table function: json_table
+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#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;
+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
+#
+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;
+num
+1
+2
+3
+connection default;
+disconnect conn1;
+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(11) PATH '$[0]')) `jt` latin1 latin1_swedish_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
+#
+# 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;
+connect conn1,localhost,user1,,;
+connection conn1;
+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
+disconnect conn1;
+connection default;
+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
+9223372036854775807
+Warnings:
+Warning 1264 Out of range value for column 'id' at row 1
+# 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
+9223372036854775807
+Warnings:
+Warning 1264 Out of range value for column 'id' at row 1
+# 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(20) unsigned DEFAULT NULL,
+ `value` bigint(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED
+#
+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;
+num
+1
+2
+3
+DROP VIEW test.v;
+# 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;
+connect con1,localhost,foo,,foo;
+SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt;
+1
+1
+1
+1
+connection default;
+disconnect con1;
+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;
+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;
+xval yval
+2 8
+3 7
+4 6
+connection default;
+disconnect con1;
+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) CHARACTER SET utf8mb4 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# 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 utf8mb4 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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 utf8mb4 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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;
+DROP VIEW v2;
+#
+# 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"
+SELECT * FROM
+JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
+ NULL ON ERROR NULL ON EMPTY)) jt;
+x
+NULL
+SELECT * FROM
+JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
+ ERROR ON ERROR ERROR ON EMPTY)) jt;
+x
+c
+#
+# 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;
+Warnings:
+Warning 1265 Data truncated for column 'b' at row 2
+Warning 1264 Out of range value for column 'a' at row 3
+Warning 1265 Data truncated for column 'd' at row 4
+SELECT * FROM t ORDER BY id;
+id a b c d
+1 1 abc NULL NULL
+2 2 abc NULL NULL
+3 127 xyz NULL NULL
+4 NULL NULL 2012-00-00 12
+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(4) PATH '$', `si` smallint(6) PATH '$', `mi` mediumint(9) PATH '$', `i` int(11) PATH '$', `bi` bigint(20) PATH '$', `tiu` tinyint(3) unsigned PATH '$', `siu` smallint(5) unsigned PATH '$', `miu` mediumint(8) unsigned PATH '$', `iu` int(10) unsigned PATH '$', `biu` bigint(20) 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;
+#
+# 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
View Lorry Controller Status