path: root/mysql-test/suite/json/r/json_table_mysql.result

# 
# 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,
    "nested_loop": [
      {
        "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 COLLATE=latin1_swedish_ci
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 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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 utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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;
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