maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #12466
Re: MDEV-17399 New patch for JSON_TABLE
Also attaching the test files, original and the one I have modified to get it
to finish. Note the added --disable_parsing.
The .result is the unmodified original
On Mon, Nov 16, 2020 at 01:53:39PM +0300, Sergey Petrunia wrote:
> Hi Alexey,
>
> Input on the latest patch for MDEV-17399.
>
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
# For stable statistics
#--source include/have_innodb_16k.inc
--source include/have_innodb.inc
--echo #
--echo # WL#8867: Add JSON_TABLE table function
--echo #
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '\$.a',
jexst int exists path '\$.b')
) as tt;
#--error ER_INVALID_DEFAULT
select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' default '[99]' on error,
jexst int exists path '$.b')
) as tt;
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;
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
'$[*]' columns (id for ordinality,
jpath_i int path '$.a'
default '33' on empty
default '66' on error,
jpath_r real path '$.a'
default '33.3' on empty
default '77.7' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
#eval $query;
#eval explain $query;
select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a'
default '33' on empty
default '66' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
#eval $query;
#eval explain $query;
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
json_path json path '$.a',
jexst int exists path '$.b')
) as tt;
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
json_path json path '$.a',
jexst int exists path '$.b')
) as tt
where id = 3;
#eval $query;
#eval explain $query;
--error 4174
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' error on empty,
jexst int exists path '$.b')
) as tt;
--error 4174
select * from
json_table(
'[{"a":"3"},{"a":2},{"a":1},{"a":[0,1]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' error on error,
jexst int exists path '$.b')
) as tt;
--error ER_JSON_SYNTAX
select * from
json_table(
'!#@$!@#$',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
--error ER_JSON_SYNTAX
# psergey-done: CRASH1
--disable_parsing
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;
--enable_parsing
--error ER_JSON_SYNTAX
--disable_parsing
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path "!@#$!@#$",
jexst int exists path '$.b')
) as tt;
--enable_parsing
--error ER_DUP_FIELDNAME
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
id for ordinality)
) as tt;
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
_id for ordinality)
) as tt;
select * from
json_table(
'[
{"a":"3", "n": { "l": 1}},
{"a":2, "n": { "l": 1}},
{"b":1, "n": { "l": 1}},
{"a":0, "n": { "l": 1}}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l')
)
) as tt;
#eval $query;
#eval explain $query;
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;
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;
select * from json_table(
'[
{"a":"3", "n": ["b","a","c"]},
{"a":2, "n": [1,2]},
{"b":1, "n": ["zzz"]},
{"a":0, "n": [0.1, 0.02]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id_n for ordinality,
jpath_n varchar(50) path '$')
)
) as tt;
select * from json_table(
'[
{"a":"3", "n": ["b","a","c"]},
{"a":2, "n": [1,2]},
{"b":1, "n": ["zzz"]},
{"a":0, "n": [0.1, 0.02]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id_n1 for ordinality,
jpath_n1 varchar(50) path '$') ,
nested path '$.n[*]' columns (
id_n2 for ordinality,
jpath_n2 varchar(50) path '$')
)
) as tt;
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;
--echo ord should be 1,1,1,2, which tells that first two values of 'l' are
--echo from the same object, and next two are from different objects
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;
CREATE TABLE jt( i JSON );
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1;
EXPLAIN SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1;
SELECT * FROM (
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1) AS ttt;
EXPLAIN SELECT * FROM (
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1) AS ttt;
DROP TABLE jt;
--disable_parsing
SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
'$' COLUMNS (dt DATE PATH '$')) as tt;
SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
'$' COLUMNS (dt TIME PATH '$')) as tt;
SELECT * FROM JSON_TABLE(CAST(CAST("2001.02.03" AS DATE) AS JSON),
'$' COLUMNS (dt DATE PATH '$')) as tt;
--enable_parsing
CREATE VIEW v AS
SELECT * FROM JSON_TABLE('[1,2,3]',
'$[*]' COLUMNS (num INT PATH '$.a'
DEFAULT '123' ON EMPTY
DEFAULT '456' ON ERROR)) AS jt;
SELECT * FROM v;
SHOW CREATE VIEW v;
DROP VIEW v;
#--error ER_INVALID_JSON_VALUE_FOR_CAST
SELECT * FROM JSON_TABLE('"asdf"',
'$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
#--error ER_WRONG_JSON_TABLE_VALUE
--error ER_JSON_TABLE_ERROR_ON_FIELD
SELECT * FROM
JSON_TABLE('[{"a":1},{"a":2}]',
'$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
# psergey-added:
--error ER_JSON_TABLE_ERROR_ON_FIELD
SELECT * FROM
JSON_TABLE('[{"a":1},{"a":2}]',
'$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
--error ER_JSON_TABLE_ERROR_ON_FIELD
SELECT * FROM
JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
SELECT * FROM
JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt;
#--error ER_JT_MAX_NESTED_PATH
SELECT * FROM
JSON_TABLE('{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{}}}}}}}}}}}}}}}}}}}',
'$' COLUMNS (i0 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i1 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i2 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i3 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i4 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i5 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i6 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i7 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i8 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i9 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i10 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i11 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i12 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i13 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i14 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i15 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i16 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i17 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i18 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i19 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i20 INT PATH '$.a'
)))))))))))))))))))))) jt;
CREATE TABLE t1(id int, jd JSON);
INSERT INTO t1 values (1, '[1,3,5]'),(2,'[2,4,6]');
SELECT id, jt.* FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
EXPLAIN SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
--sorted_result
SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
EXPLAIN SELECT t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
EXPLAIN SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
--disable_parsing
(psergey:!)
# JSON_TABLE referring outer scope
SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
--enable_parsing
# JSON_TABLE referring another JSON_TABLE
--disable_parsing
(psergey:!)
SELECT id, jt1.*, jt2.*
FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
EXPLAIN SELECT id, jt1.*, jt2.*
FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
--enable_parsing
DROP TABLE t1;
SELECT * FROM JSON_TABLE('"asdf"', '$' COLUMNS(
tm TIME PATH '$',
dt DATE PATH '$',
i INT PATH '$',
f FLOAT PATH '$',
d DECIMAL PATH '$')) AS jt;
# DEFAULT NULL is not accepted syntax.
--error ER_PARSE_ERROR
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt;
--error ER_PARSE_ERROR
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt;
# The DEFAULT value must be a string on JSON format for now.
--error 1064
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt;
--error 1064
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
--error 1064
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x DATE
PATH '$.x'
DEFAULT DATE'2020-01-01' ON EMPTY)) jt;
--error 1064
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x DATE
PATH '$.x'
DEFAULT DATE'2020-01-01' ON ERROR)) jt;
--echo #
--echo # Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF
--echo #
--error ER_BAD_FIELD_ERROR
SELECT a FROM JSON_TABLE(abc, '$[*]' COLUMNS ( a int path '$.a')) AS jt;
--echo #
--echo # Bug#25420680: ASSERTION `THD->IS_ERROR()' FAILED IN SQL/SQL_SELECT.CC
--echo #
SELECT * FROM JSON_TABLE('{"a":"2017-11-1"}',
'$' COLUMNS (jpath DATE PATH '$.a')) AS jt;
--echo #
--echo # Bug#25413826: ASSERTION `TABLE_LIST->ALIAS' FAILED
--echo #
#--error ER_TF_MUST_HAVE_ALIAS
--error ER_PARSE_ERROR
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a int path '$.b'));
--echo #
--echo # Bug#25421464: ASSERTION `!STRCMP(TABLE_REF->TABLE_NAME, ...
--echo #
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
SELECT * FROM v1;
SHOW CREATE VIEW v1;
DROP VIEW v1;
--echo #
--echo # Bug#25427457: ASSERTION `!((*REG_FIELD)->FLAGS & 16)'
--echo #
SELECT * FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (jpath JSON PATH '$.a',
o FOR ORDINALITY)) AS jt
WHERE o = 1;
--echo #
--echo # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H
--echo #
#--error ER_WRONG_FIELD_WITH_GROUP
SELECT je,o FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (o FOR ORDINALITY,
je BIGINT EXISTS PATH '$.a')) AS jt
GROUP BY je;
SELECT je,COUNT(o) FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (o FOR ORDINALITY,
je BIGINT EXISTS PATH '$.a')) AS jt
GROUP BY je;
--echo #
--echo # Bug#25413194: ASSERTION `!(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE
--echo #
CREATE TABLE t1 (j JSON);
SELECT * FROM t1,JSON_TABLE(t1.j, '$[*]' COLUMNS ( a int path '$.b')) AS jt;
DROP TABLE t1;
--echo #
--echo # Bug#25460537:SIG 11 IN NEXT_FAST AT SQL/SQL_LIST.H
--echo #
PREPARE STMT FROM
"SELECT * FROM JSON_TABLE(
\'[{\"a\":\"3\"},{\"a\":2},{\"b\":1},{\"a\":0}]\',
\'$[*]\' COLUMNS (id
FOR ORDINALITY,
jpath VARCHAR(100) PATH \'$.a\',
jexst INT EXISTS PATH \'$.b\')
) as tt";
EXECUTE STMT;
EXECUTE STMT;
DEALLOCATE PREPARE stmt;
--echo #
--echo # Bug#25522353: SIG 11 IN JOIN::MAKE_JOIN_PLAN | SQL/SQL_OPTIMIZER.CC
--echo #
CREATE TABLE t1 (id INT, jc JSON);
# psergey!
#--error ER_UNKNOWN_TABLE
SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
# psergey!
#--error ER_UNKNOWN_TABLE
SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id;
SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id;
SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
# Error code can be ER_UNKNOWN_TABLE or ER_BAD_FIELD_ERROR
# (see find_field_in_tables()), both are acceptable.
# psergey:
#--error ER_BAD_FIELD_ERROR
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;
# psergey:
#--error ER_UNKNOWN_TABLE
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;
# psergey:
#--error ER_UNKNOWN_TABLE
WITH qn AS
(SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
SELECT * from qn;
#--error ER_UNKNOWN_TABLE
WITH qn AS
(SELECT 1 UNION
SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
SELECT * from qn;
#--error ER_BAD_FIELD_ERROR
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
#--error ER_UNKNOWN_TABLE
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3");
ANALYZE TABLE t1;
SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id INT PATH '$')) as jt ON t1.id=jt.id;
EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id INT PATH '$')) as jt ON t1.id=jt.id;
#--error ER_UNKNOWN_TABLE
--error ER_BAD_FIELD_ERROR
SELECT * FROM t1
LEFT JOIN
JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id
RIGHT JOIN
JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id;
DROP TABLE t1;
# Test that tmp table can overflow to disk
#set @save_mem_se= @@internal_tmp_mem_storage_engine;
#set @@internal_tmp_mem_storage_engine=MEMORY;
#set @save_heap_size= @@max_heap_table_size;
#set @@max_heap_table_size= 16384;
FLUSH STATUS;
SELECT * FROM JSON_TABLE(
'[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]',
'$[*]' COLUMNS (
c1 CHAR(255) PATH '$',
c2 CHAR(255) PATH '$',
c3 CHAR(255) PATH '$',
c4 CHAR(255) PATH '$',
c5 CHAR(255) PATH '$',
c6 CHAR(255) PATH '$',
c7 CHAR(255) PATH '$',
c8 CHAR(255) PATH '$')) AS jt;
SHOW STATUS LIKE '%tmp%';
#set @@max_heap_table_size= @save_heap_size;
#set @@internal_tmp_mem_storage_engine= @save_mem_se;
--echo #
--echo # Bug#25504063: EXPLAIN OF JSON_TABLE QUERY USES INTERNAL TEMP TABLES
--echo #
FLUSH STATUS;
SELECT * FROM
JSON_TABLE(
'[{"a":"3"}]',
'$[*]' COLUMNS (id FOR ORDINALITY)
) AS tt;
SHOW STATUS LIKE '%tmp%';
--echo #
--echo # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC
--echo #
--error ER_BAD_FIELD_ERROR
SELECT * FROM JSON_TABLE( ( SELECT a ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
SELECT * FROM JSON_TABLE( ( SELECT 1 ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
# psergey: check this
--error ER_INVALID_GROUP_FUNC_USE
SELECT * FROM JSON_TABLE( ( SUM(1) ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
--echo #
--echo # Bug# #25472875: ERROR SHOULD BE THROWN FOR INCORRECT VALUES
--echo #
#--error ER_JT_VALUE_OUT_OF_RANGE
SELECT *
FROM JSON_TABLE('{"a":"1993-01-01"}',
'$' COLUMNS (jp DATE PATH '$.b' DEFAULT '1000' ON EMPTY))
AS jt;
--echo #
--echo # Bug#25532429: INVALID JSON ERROR NOT THROWN WITH EMPTY TABLES JOIN
--echo #
CREATE TABLE t1(j JSON);
#--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT * FROM t1,
JSON_TABLE( 'dqwfjqjf' , '$[*]' COLUMNS (col5 FOR ORDINALITY) ) AS alias7;
DROP TABLE t1;
--echo #
--echo # Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063
--echo #
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(70) PATH '$')
) AS alias2;
--echo # Too short field causes truncation, error and triggers ON ERROR clause
SELECT * FROM
JSON_TABLE('["3.14159"]',
'$[*]' COLUMNS (col18 CHAR(6) PATH '$')
) AS alias2;
--echo #Truncated space doesn't trigger ON ERROR
SELECT * FROM
JSON_TABLE('["3.14159 "]',
'$[*]' COLUMNS (col18 CHAR(7) PATH '$')
) AS alias2;
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(255) PATH '$')
) AS alias2;
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
) AS alias2;
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
) AS alias2;
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 FLOAT PATH '$')
) AS alias2;
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 DOUBLE PATH '$')
) AS alias2;
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
#--error ER_JT_VALUE_OUT_OF_RANGE
--error ER_JSON_TABLE_ERROR_ON_FIELD
SELECT * FROM
JSON_TABLE('[3.14159]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)
) AS alias2;
SELECT * FROM
JSON_TABLE('[0.9]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
SELECT * FROM
JSON_TABLE('["asdf","ghjk"]',
'$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
DEFAULT "3.14159" ON ERROR)
) AS alias2;
CREATE TABLE t1(jd JSON);
INSERT INTO t1 VALUES('["asdf"]'),('["ghjk"]');
SELECT * FROM t1,
JSON_TABLE(jd,
'$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
DEFAULT "3.14159" ON ERROR)
) AS alias2;
DROP TABLE t1;
--echo #
--echo # Bug#25540027: SIG 11 IN FIND_FIELD_IN_TABLE | SQL/SQL_BASE.CC
--echo #
CREATE TABLE t1(c1 JSON);
--error ER_NON_UPDATABLE_TABLE
UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
SET jt1.a=1;
--error ER_PARSE_ERROR
DELETE JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
FROM t1;
--error ER_PARSE_ERROR
DELETE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
USING t1;
DROP TABLE t1;
--echo #
--echo # Bug#25547244: ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(
--echo #
CREATE TABLE t1(i INT);
INSERT INTO t1 VALUES(1);
--disable_parsing
# Crash #3 (not reproducible anymore?)
WITH cte_query AS
(SELECT * FROM t1, JSON_TABLE( JSON_OBJECT('ISSKF',i) ,
'$[*]' COLUMNS (jtcol1 INT EXISTS PATH '$[*]') ) AS alias2)
SELECT jtcol1 AS field1 FROM cte_query;
--enable_parsing
DROP TABLE t1;
--echo #
--echo # Bug#25540675: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
--echo #
CREATE TABLE j1(j JSON);
INSERT INTO j1 VALUES('[1,2,3]'),('[1,2,4]');
SELECT * FROM j1,
JSON_TABLE( JSON_OBJECT('key1', j) ,
'$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (col11 FOR ORDINALITY))) AS alias2;
DROP TABLE j1;
--echo #
--echo # Bug#25584335: SIG 11 IN TABLE_LIST::FETCH_NUMBER_OF_ROWS
--echo #
CREATE TABLE t1(i INT);
PREPARE stmt FROM "SELECT alias1.i AS field1 FROM (
t1 AS alias1,
(SELECT * FROM
JSON_TABLE('[1,2,3]' ,
'$[*]' COLUMNS (`col_varchar` FOR ORDINALITY)) AS SQ1_alias1
) AS alias2 )";
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
--echo #
--echo # Bug#25604048: COLUMN NAMES WITH SAME 33-CHAR PREFIX ARE EQUAL
--echo #
SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
column_name_is_thirty_four_or_more VARCHAR(17) PATH '$.key1',
column_name_is_thirty_four_or_more_yes_indeed VARCHAR(17) PATH '$.key1'
) ) AS alias1;
#--error ER_WRONG_COLUMN_NAME
SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
`column_name_is_thirty_four_or_more ` VARCHAR(17) PATH '$.key1'
) ) AS alias1;
--echo #
--echo # Bug#25604404: JSON_TABLE MORE RESTRICTIVE WITH IDENTIFIERS THAN
--echo # CREATE TABLE
--echo #
SELECT * FROM JSON_TABLE( '[1, 2]', '$' COLUMNS(
one INT PATH '$[0]', two INT PATH '$[1]'
)) AS jt;
--echo #
--echo # Bug#25588450: SIG 6 IN JSON_WRAPPER::SEEK|SQL/JSON_DOM.CC
--echo #
CREATE TABLE t1(c VARCHAR(10)) ENGINE=MEMORY;
INSERT INTO t1 VALUES('fiheife');
#--error ER_INVALID_JSON_TEXT_IN_PARAM
--error ER_JSON_SYNTAX
SELECT * FROM `t1` AS alias1, JSON_TABLE( `c` , '$[*]' COLUMNS (jtcol1 JSON
PATH '$.*')) AS alias2 WHERE jtcol1 <= 'kjfh';
DROP TABLE t1;
--echo #
--echo # Bug#25587754: ASSERTION `FIXED == 0 || BASIC_CONST_ITEM()' FAILED
--echo #
PREPARE stmt FROM
"SELECT * FROM JSON_TABLE( '[1,2]', '$[*]'
COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2";
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
--echo #
--echo # Bug#25584593: UNABLE TO USE JSON_TABLE() ON TEXT/BLOB JSON DATA
--echo #
SELECT * FROM JSON_TABLE(NULL, '$.k' COLUMNS (id FOR ORDINALITY)) AS aLias;
SELECT * FROM JSON_TABLE(@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
SET @myjson = '{"k": 42}';
SELECT * FROM JSON_TABLE(@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
CREATE TABLE t1(
txt TEXT, ty TINYTEXT, tm MEDIUMTEXT, tl LONGTEXT);
INSERT INTO t1 values (
'{"k": "text"}','{"k": "tinytext"}','{"k": "mediumtext"}','{"k": "longtext"}');
SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE(t1.ty, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE(t1.tm, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE(t1.tl, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id TEXT PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id TINYTEXT PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id MEDIUMTEXT PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id LONGTEXT PATH '$')) AS alias;
# BLOB can store data from JSON
SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
# Check that type is printed correctly
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias;
# But can't be used as a data source
#--error ER_WRONG_ARGUMENTS
--error ER_PARSE_ERROR
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;
DROP TABLE t1;
--echo #
--echo # Bug#26500384: ASSERT FAILURE IN QUERY WITH WINDOW FUNCTION AND
--echo # JSON_TABLE
--echo #
CREATE TABLE t (x INT);
INSERT INTO t VALUES (1), (2), (3);
--error ER_BAD_FIELD_ERROR
SELECT MAX(t.x) OVER () m, jt.* FROM t,
JSON_TABLE(JSON_ARRAY(m), '$[*]' COLUMNS (i INT PATH '$')) jt;
DROP TABLE t;
--echo #
--echo # Bug#26583283: ASSERTION `!THD->IS_ERROR()' FAILED IN SQL_RESOLVER.CC
--echo #
EXPLAIN SELECT * FROM JSON_TABLE('null', '$' COLUMNS(AA DECIMAL PATH '$')) tt;
CREATE VIEW v1 AS SELECT * FROM
JSON_TABLE( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt;
SELECT * FROM v1;
EXPLAIN SELECT * FROM v1;
DROP VIEW v1;
--echo #
--echo # Bug#25822408: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
--echo #
PREPARE stmt FROM "SELECT * FROM
JSON_TABLE('{\"a\":1}','$' COLUMNS (c1 CHAR(20) PATH '$.b' ERROR ON EMPTY)) jt";
--error 4174
EXECUTE stmt;
--error 4174
EXECUTE stmt;
--echo #
--echo # Bug#25594571: CRASH AT ITEM::CONST_ITEM|SQL/ITEM.H
--echo #
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(7);
PREPARE stmt FROM "SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2
LEFT JOIN JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (i FOR ORDINALITY )) AS
alias3 ON alias2 . `i` = alias3 . `i` ON alias1 . `i` = alias2 . `i`";
EXECUTE stmt;
EXECUTE stmt;
DROP TABLE t1;
--echo #
--echo # Bug#26648617: ASSERTION `IS_VIEW_OR_DERIVED() &&
--echo # USES_MATERIALIZATION()' FAILED.
--echo #
CREATE TABLE t1 (
col_varchar_key varchar(1) DEFAULT NULL
) 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);
EXPLAIN SELECT * FROM t1 WHERE col_varchar_key NOT IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
SELECT * FROM t1 WHERE col_varchar_key IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
EXPLAIN SELECT * FROM t1 WHERE col_varchar_key IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
DROP TABLE t1;
--echo #
--echo # Bug#26711551: WL8867:CONDITIONAL JUMP IN JSON_TABLE_COLUMN::CLEANUP
--echo #
CREATE TABLE t(x int, y int);
INSERT INTO t(x) VALUES (1);
UPDATE t t1, JSON_TABLE('[2]', '$[*]' COLUMNS (x INT PATH '$')) t2
SET t1.y = t2.x;
SELECT * FROM t;
DROP TABLE t;
--echo #
--echo # Bug#26679671: SIG 11 IN JSON_BINARY::PARSE_BINARY()
--echo #
CREATE TABLE t1(id INT, f1 JSON);
INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'),
(4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}');
ANALYZE TABLE t1;
ANALYZE TABLE t1;
--sorted_result
SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
EXPLAIN SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
# psergey:name resolution
--error ER_NON_UNIQ_ERROR
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;
# psergey:name resolution
--error ER_NON_UNIQ_ERROR
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;
SELECT * FROM t1 WHERE id IN
(SELECT id FROM t1 as tt2,
JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT id FROM t1 as tt2,
JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
DROP TABLE t1;
--echo #
--echo # Bug#26760811: WL#8867: MEMORY LEAK REPORTED BY ASAN AND VALGRIND
--echo #
CREATE TABLE t (j JSON);
INSERT INTO t VALUES
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'),
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]');
SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt;
PREPARE ps FROM
'SELECT COUNT(*) FROM t, JSON_TABLE(j, ''$[*]'' COLUMNS (i INT PATH ''$'')) AS jt';
EXECUTE ps;
EXECUTE ps;
DROP PREPARE ps;
DROP TABLE t;
--echo #
--echo # Bug #26781759: NON-UNIQUE ALIAS ERROR NOT BEING THROWN
--echo #
--error ER_NONUNIQ_TABLE
SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias,
JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias;
--echo #
--echo # Bug #26761470: WL#8867: JOIN::MAKE_JOIN_PLAN():
--echo # ASSERTION `SELECT_LEX->IS_RECURSIVE()' FAILED
--echo #
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (j JSON);
INSERT INTO t2 (j) VALUES ('[1,2,3]');
--sorted_result
# psergey:name resolution
--error ER_WRONG_OUTER_JOIN
SELECT * FROM t1 RIGHT JOIN
(SELECT o FROM t2, JSON_TABLE(j, '$[*]' COLUMNS (o FOR ORDINALITY)) AS jt) AS t3
ON (t3.o = t1.x);
DROP TABLE t1, t2;
--echo #
--echo # Bug#27152428 JSON_TABLE + PREPARED STATEMENT + VIEW HAS PROBLEM IN DURING RESOLUTION
--echo #
CREATE TABLE t1 (a INT, b INT);
CREATE VIEW v2 AS SELECT * FROM t1 LIMIT 2;
--disable_parsing
# psergey-done: crash in name resolution:
SELECT b
FROM (SELECT * FROM v2) vq1,
JSON_TABLE(CONCAT(vq1.b,'[{\"a\":\"3\"}]'),
'$[*]' COLUMNS (id FOR ORDINALITY,
jpath VARCHAR(100) PATH '$.a',
JEXST INT EXISTS PATH '$.b')
) AS dt;
--enable_parsing
DROP TABLE t1;
DROP VIEW v2;
--echo #
--echo # Bug#27189940: CREATE VIEW FAILS ON JSON_TABLE() IN SCHEMA-LESS CONNECTIONS
--echo # BUG#27217897: JSON_TABLE() FAILS IF NO DATABASE SELECTED
--echo #
# Connect without a schema name:
connect (conn1,localhost,root,,*NO-ONE*);
connection conn1;
CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
connection default;
disconnect conn1;
use test;
SHOW CREATE VIEW test.v;
SELECT * FROM test.v;
DROP VIEW test.v;
--echo #
--echo # Bug#27729112 JSON_TABLE SHOULD DECODE BASE64-ENCODED STRINGS
--echo #
SELECT v
FROM JSON_TABLE(JSON_OBJECT('foo', _binary'bar'), '$'
COLUMNS(v VARCHAR(255) PATH '$.foo')) tbl;
--disable_parsing
# not supported
CREATE TABLE t1 (col1 VARCHAR(255) CHARACTER SET ucs2,
col2 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
utf8mb4_unicode_cs);
INSERT INTO t1 VALUES ("æ", "ハ"), ("å", "ø"), ("ø", "パ"), ("@", "バ");
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2;
SELECT v value, c cumulfreq
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
JSON_TABLE(histogram->'$.buckets', '$[*]'
COLUMNS(v VARCHAR(255) CHARACTER SET ucs2 PATH '$[0]',
c double PATH '$[1]')) hist
WHERE column_name = "col1";
SELECT v value, c cumulfreq
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
JSON_TABLE(histogram->'$.buckets', '$[*]'
COLUMNS(v VARCHAR(255) CHARACTER SET utf8mb4 PATH '$[0]',
c double PATH '$[1]')) hist
WHERE column_name = "col2";
DROP TABLE t1;
--enable_parsing
--echo #
--echo # Bug#28254268: JSON_TABLE() FUNCTION REJECTS SELECT PERMISSIONS
--echo #
CREATE DATABASE db2;
USE db2;
CREATE TABLE t1 (c JSON);
INSERT INTO t1 VALUES('[1,2,3]');
CREATE USER user1@localhost;
GRANT SELECT ON db2.t1 TO user1@localhost;
connect (conn1,localhost,user1,,);
connection conn1;
USE db2;
SELECT t1.c FROM t1;
SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]'))
AS jt;
disconnect conn1;
connection default;
DROP USER user1@localhost;
DROP DATABASE db2;
--echo #
--echo # Bug#27856835 JSON_TABLE RETURNS WRONG DATATYPE WHEN INT-VALUE IS GRATER
--echo # THAN (2^31-1)
--echo #
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775807"}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
--echo # As we currently have no way of telling if a JSON string value is
--echo # signed or unsigned, this value will overflow.
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
--echo # Here the JSON value is a NUMERIC value, and we thus know if the value
--echo # is signed or unsigned.
SELECT id FROM JSON_TABLE('[{"id":9223372036854775808}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
--echo # If we tell the JSON table column to be unsigned, we get to store the
--echo # full value correctly.
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
(id BIGINT UNSIGNED PATH '$.id')) AS json;
SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS
(id INT UNSIGNED PATH '$.id')) AS json;
--echo # Check that we preserve the signedness of the columns.
USE test;
CREATE TABLE t1 AS SELECT id, value FROM
JSON_TABLE('[{"id":9223372036854775808, "value":9223372036854775807}]',
'$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id',
value BIGINT PATH '$.value'))
AS json;
SHOW CREATE TABLE t1;
DROP TABLE t1;
--echo #
--echo # Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED
--echo #
connect (conn1,localhost,root,,*NO-ONE*);
CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
SELECT * FROM test.v;
DROP VIEW test.v;
--echo # Check that a user without access to the schema 'foo' cannot query
--echo # a JSON_TABLE view in that schema.
CREATE SCHEMA foo;
CREATE VIEW foo.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
CREATE USER foo@localhost;
connect (con1,localhost,foo,,);
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM foo.v;
connection default;
disconnect con1;
DROP USER foo@localhost;
DROP SCHEMA foo;
--echo # Check that a user with access to the schema 'foo' can do a SELECT with
--echo # a JSON_TABLE function.
CREATE SCHEMA foo;
CREATE USER foo@localhost;
GRANT EXECUTE ON foo.* TO foo@localhost;
connect (con1,localhost,foo,,foo);
SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt;
connection default;
disconnect con1;
DROP USER foo@localhost;
DROP SCHEMA foo;
--echo #
--echo # Bug#27923406 ERROR 1142 (42000) WHEN USING JSON_TABLE
--echo #
CREATE SCHEMA my_schema;
CREATE USER foo@localhost;
GRANT EXECUTE ON my_schema.* TO foo@localhost;
connect (con1,localhost,foo,,my_schema);
SELECT
*
FROM
JSON_TABLE(
'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
"$[*]" COLUMNS(
xval VARCHAR(100) PATH "$.x",
yval VARCHAR(100) PATH "$.y"
)
) AS jt1;
connection default;
disconnect con1;
DROP USER foo@localhost;
DROP SCHEMA my_schema;
--echo #
--echo # Bug#28538315: JSON_TABLE() COLUMN TYPES DON'T SUPPORT COLLATE CLAUSE
--echo #
CREATE TABLE t1 SELECT *
FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
CHARSET utf8mb4
PATH '$')) AS jt1;
SHOW CREATE TABLE t1;
--disable_parsing
CREATE TABLE t2 SELECT *
FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
CHARSET utf8mb4 COLLATE utf8mb4_bin
PATH '$')) AS jt1;
SHOW CREATE TABLE t2;
CREATE TABLE t3 AS SELECT *
FROM JSON_TABLE('"a"', '$' COLUMNS (a VARCHAR(10)
COLLATE ascii_bin
PATH '$')) jt;
SHOW CREATE TABLE t3;
--enable_parsing
DROP TABLE t1;
--echo #
--echo # Bug#28643862 JSON_TABLE'S "COLUMNS" CLAUSE USES
--echo # GLOBAL.CHARACTER_SET_RESULTS DEFAULT CHARSET
--echo #
SET @@SESSION.character_set_connection = ascii;
CREATE TABLE t1 SELECT a.col
FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
SHOW CREATE TABLE t1;
SET @@SESSION.collation_connection = latin1_bin;
CREATE TABLE t2 SELECT a.col
FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;
SET @@SESSION.character_set_connection = DEFAULT;
--echo #
--echo # Bug#28851656: JSON_TABLE RETURN "UNKNOWN DATABASE ''" FROM A FUNCTION
--echo #
CREATE FUNCTION FN_COUNT_ROWS(X JSON)
RETURNS INT DETERMINISTIC
RETURN (
SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der
);
SELECT FN_COUNT_ROWS('[1, 2]') CNT;
SELECT FN_COUNT_ROWS('[1, 2, 3]') CNT;
SELECT FN_COUNT_ROWS('[1, 2, 3, 4]') CNT;
DROP FUNCTION FN_COUNT_ROWS;
--echo #
--echo # Bug#30310265: VIEWS LOSE THE CHARACTER SET OF JSON_TABLE'S
--echo # PATH ARGUMENTS
--echo #
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('{"å":{"å":1}}', '$.å' COLUMNS (x INT PATH '$.å')) t;
SELECT * FROM v1;
SET NAMES latin1;
# Used to return zero rows.
SELECT * FROM v1;
SET NAMES DEFAULT;
DROP VIEW v1;
CREATE VIEW v2 AS
SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (
x VARCHAR(10) PATH '$.a' DEFAULT '"isn''t here"' ON EMPTY)
) t;
# SHOW CREATE VIEW and SELECT from the view used to fail with a syntax error.
--disable_parsing
# psergey-done
SHOW CREATE VIEW v2;
SELECT * FROM v2;
--enable_parsing
DROP VIEW v2;
# The string literals in JSON_TABLE didn't accept character set
# introducers. Verify that they are accepted now.
--disable_parsing
# psergey: it's a bug!
SELECT * FROM
JSON_TABLE(JSON_OBJECT(),
_utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' COLUMNS
(y INT PATH _utf8mb4'$.y'
DEFAULT _utf8mb4'1' ON EMPTY
DEFAULT _utf8mb4'2' ON ERROR))) jt;
--enable_parsing
--echo #
--echo # Bug#30382156: STORED PROCEDURE, JSON_TABLE AND "CONST" JOIN TYPE
--echo #
CREATE TABLE t (id INT PRIMARY KEY, j JSON);
INSERT INTO t VALUES (1, '{"x":1}');
CREATE PROCEDURE p()
SELECT * FROM t, JSON_TABLE(j, '$' COLUMNS (x INT PATH '$.x')) jt
WHERE id = 1;
CALL p();
CALL p();
CALL p();
DROP PROCEDURE p;
DROP TABLE t;
--echo #
--echo # WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax
--echo #
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
DEFAULT '"a"' ON ERROR DEFAULT '"b"' ON EMPTY)) jt;
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
NULL ON ERROR NULL ON EMPTY)) jt;
SELECT * FROM
JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
ERROR ON ERROR ERROR ON EMPTY)) jt;
--echo #
--echo # Bug#30628330: INSERT WITH JSON_TABLE FAILS DUE TO WARNING INVOKED
--echo # WITH ON ERROR CLAUSE
--echo #
CREATE TABLE t(id INT, a TINYINT, b VARCHAR(3), c DATE, d DECIMAL);
# This statement used to fail with "data too long".
INSERT INTO t SELECT * FROM
JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
JSON_OBJECT('a', 2, 'b', 'abcd'),
JSON_OBJECT('a', 1000, 'b', 'xyz'),
JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
'$[*]' COLUMNS (id FOR ORDINALITY,
a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
b VARCHAR(3) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
) AS jt;
SELECT * FROM t ORDER BY id;
DROP TABLE t;
--echo #
--echo # Bug#30263373: INCORRECT OUTPUT FROM TABLE_FUNCTION_JSON::PRINT()
--echo #
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[123]', '$[*]' COLUMNS (`name with space 1` INT PATH '$',
`name with space 2` FOR ORDINALITY)) jt;
# Used to fail with a syntax error, due to unquoted column names in
# the view definition.
SELECT * FROM v;
DROP VIEW v;
CREATE VIEW v AS SELECT HEX(x), HEX(y) FROM
JSON_TABLE('["abc"]', '$[*]' COLUMNS (x BINARY(10) PATH '$',
y VARBINARY(10) PATH '$')) jt;
# Used to say CHAR(10) and VARCHAR(10) instead of BINARY(10) and VARBINARY(10).
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
SELECT * FROM v;
DROP VIEW v;
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[123]', '$[*]' COLUMNS(ti TINYINT PATH '$',
si SMALLINT PATH '$',
mi MEDIUMINT PATH '$',
i INT PATH '$',
bi BIGINT PATH '$',
tiu TINYINT UNSIGNED PATH '$',
siu SMALLINT UNSIGNED PATH '$',
miu MEDIUMINT UNSIGNED PATH '$',
iu INT UNSIGNED PATH '$',
biu BIGINT UNSIGNED PATH '$')) jt;
# Used to lack the UNSIGNED attribute for the unsigned columns.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
SELECT * FROM v;
DROP VIEW v;
--disable_parsing
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[]', '$[*]' COLUMNS (g GEOMETRY PATH '$',
ls LINESTRING PATH '$')) AS jt;
# Used to say GEOMETRY for both columns.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
SELECT * FROM v;
DROP VIEW v;
--enable_parsing
--disable_parsing
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('["abc"]', '$[*]' COLUMNS
(x VARCHAR(10) CHARSET latin1 PATH '$',
y VARCHAR(10) COLLATE utf8mb4_de_pb_0900_ai_ci PATH '$',
z LONGTEXT COLLATE utf8mb4_bin PATH '$')) jt;
# Character set and collation information wasn't included.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
# Used to return the default collation instead of the collation
# specified in the JSON_TABLE column definitions.
SELECT x, y, z, COLLATION(x) c_x, COLLATION(y) c_y, COLLATION(z) c_z FROM v;
DROP VIEW v;
--enable_parsing
--echo #
--echo # Bug#31345503 JSON_TABLE SHOULD RETURN SQL NULL FOR JSON NULL
--echo #
SELECT *
FROM
JSON_TABLE(
'[
{"c1": null,
"c2": [{"c": "c_val", "l": [1,2]}],
"c3": null},
{"c1": true,
"c2": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [false]}],
"c3": true},
{"c1": false,
"c2": [{"c": null,"l": [true]}, {"c": "c_val", "l": [null]}],
"c3": false}
]',
'$[*]' COLUMNS(
top_ord FOR ORDINALITY,
c1path VARCHAR(10) PATH '$.c1' ERROR ON ERROR,
NESTED PATH '$.c2[*]' COLUMNS (
c2path VARCHAR(10) PATH '$.c' ERROR ON ERROR,
ord FOR ORDINALITY,
NESTED PATH '$.l[*]' COLUMNS (lpath_c VARCHAR(10) PATH '$' ERROR ON ERROR,
lpath_i INT PATH '$' ERROR ON ERROR)
),
c3path INT PATH '$.c3' ERROR ON ERROR
)
) as jt;
--echo #
--echo # Bug #31327187 UBSAN: JSON_TABLE: NULL POINTER PASSED AS ARGUMENT 2,
--echo # WHICH IS DECLARED TO NEVER BE NULL
--echo #
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;
# For stable statistics
--source include/have_innodb_16k.inc
--echo #
--echo # WL#8867: Add JSON_TABLE table function
--echo #
let $query=select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
eval $query;
eval explain $query;
eval explain format=tree $query; # Tests printing of MaterializedTableFunctionIterator.
--error ER_INVALID_DEFAULT
select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' default '[99]' on error,
jexst int exists path '$.b')
) as tt;
let $query= select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a'
default '33' on empty
default '66' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
eval $query;
eval explain $query;
let $query= select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
'$[*]' columns (id for ordinality,
jpath_i int path '$.a'
default '33' on empty
default '66' on error,
jpath_r real path '$.a'
default '33.3' on empty
default '77.7' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
eval $query;
eval explain $query;
let $query= select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a'
default '33' on empty
default '66' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
eval $query;
eval explain $query;
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
json_path json path '$.a',
jexst int exists path '$.b')
) as tt;
let $query= select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
json_path json path '$.a',
jexst int exists path '$.b')
) as tt
where id = 3;
eval $query;
eval explain $query;
--error ER_MISSING_JSON_TABLE_VALUE
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' error on empty,
jexst int exists path '$.b')
) as tt;
--error ER_WRONG_JSON_TABLE_VALUE
select * from
json_table(
'[{"a":"3"},{"a":2},{"a":1},{"a":[0,1]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' error on error,
jexst int exists path '$.b')
) as tt;
--error ER_INVALID_JSON_TEXT_IN_PARAM
select * from
json_table(
'!#@$!@#$',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
--error ER_INVALID_JSON_PATH
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
"!@#$!@#$" columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
--error ER_INVALID_JSON_PATH
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path "!@#$!@#$",
jexst int exists path '$.b')
) as tt;
--error ER_DUP_FIELDNAME
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
id for ordinality)
) as tt;
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
_id for ordinality)
) as tt;
let $query= select * from
json_table(
'[
{"a":"3", "n": { "l": 1}},
{"a":2, "n": { "l": 1}},
{"b":1, "n": { "l": 1}},
{"a":0, "n": { "l": 1}}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l')
)
) as tt;
eval $query;
eval explain $query;
eval explain format=json $query;
let $query= select * from
json_table(
'[
{"a":2, "n": [{ "l": 1}, {"l": 11}]},
{"a":1, "n": [{ "l": 2}, {"l": 22}]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(50) path '$.a',
nested path '$.n[*]' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l'),
nested path '$.n[*]' columns (
id_m for ordinality,
jpath_m varchar(50) path '$.l')
)
) as tt;
eval $query;
eval explain $query;
select * from json_table(
'[
{"a":"3", "n": ["b","a","c"]},
{"a":2, "n": [1,2]},
{"b":1, "n": ["zzz"]},
{"a":0, "n": [0.1, 0.02]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id_n for ordinality,
jpath_n varchar(50) path '$')
)
) as tt;
select * from json_table(
'[
{"a":"3", "n": ["b","a","c"]},
{"a":2, "n": [1,2]},
{"b":1, "n": ["zzz"]},
{"a":0, "n": [0.1, 0.02]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id_n1 for ordinality,
jpath_n1 varchar(50) path '$') ,
nested path '$.n[*]' columns (
id_n2 for ordinality,
jpath_n2 varchar(50) path '$')
)
) as tt;
let $query= select * from json_table(
'[
{"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},
{"ll":["c"]} ]},
{"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},
{"b":1, "n": [{"ll":["zzz"]}]},
{"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}
]',
'$[*]' columns (
id1 for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id2 for ordinality,
nested path '$.ll[*]' columns (
id3 for ordinality,
jpath_3 varchar(50) path '$')
),
nested path '$.n[*]' columns (
id4 for ordinality,
jpath_4 json path '$')
)
) as tt;
eval $query;
eval explain $query;
--echo ord should be 1,1,1,2, which tells that first two values of 'l' are
--echo from the same object, and next two are from different objects
let $query= SELECT *
FROM JSON_TABLE(
'[{"a": "a_val",
"b": [
{"c": "c_val",
"l": [1,2]}
]
}, {"a": "a_val",
"b": [
{"c": "c_val",
"l": [11]},
{"c": "c_val",
"l": [22]}
]
}]',
'$[*]' COLUMNS (
apath VARCHAR(10) PATH '$.a',
NESTED PATH '$.b[*]' COLUMNS (
bpath VARCHAR(10) PATH '$.c',
ord FOR ORDINALITY,
NESTED PATH '$.l[*]' COLUMNS (
lpath varchar(10) PATH '$'
)
)
)) as jt;
eval $query;
eval explain $query;
CREATE TABLE jt( i JSON );
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1;
EXPLAIN SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1;
SELECT * FROM (
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1) AS ttt;
EXPLAIN SELECT * FROM (
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1) AS ttt;
DROP TABLE jt;
SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
'$' COLUMNS (dt DATE PATH '$')) as tt;
SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
'$' COLUMNS (dt TIME PATH '$')) as tt;
SELECT * FROM JSON_TABLE(CAST(CAST("2001.02.03" AS DATE) AS JSON),
'$' COLUMNS (dt DATE PATH '$')) as tt;
CREATE VIEW v AS
SELECT * FROM JSON_TABLE('[1,2,3]',
'$[*]' COLUMNS (num INT PATH '$.a'
DEFAULT '123' ON EMPTY
DEFAULT '456' ON ERROR)) AS jt;
SELECT * FROM v;
SHOW CREATE VIEW v;
DROP VIEW v;
--error ER_INVALID_JSON_VALUE_FOR_CAST
SELECT * FROM JSON_TABLE('"asdf"',
'$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
--error ER_WRONG_JSON_TABLE_VALUE
SELECT * FROM
JSON_TABLE('[{"a":1},{"a":2}]',
'$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
SELECT * FROM
JSON_TABLE('[{"a":1},{"a":2}]',
'$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
--error ER_JT_VALUE_OUT_OF_RANGE
SELECT * FROM
JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
SELECT * FROM
JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt;
--error ER_JT_MAX_NESTED_PATH
SELECT * FROM
JSON_TABLE('{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{}}}}}}}}}}}}}}}}}}}',
'$' COLUMNS (i0 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i1 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i2 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i3 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i4 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i5 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i6 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i7 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i8 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i9 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i10 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i11 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i12 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i13 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i14 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i15 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i16 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i17 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i18 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i19 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i20 INT PATH '$.a'
)))))))))))))))))))))) jt;
CREATE TABLE t1(id int, jd JSON);
INSERT INTO t1 values (1, '[1,3,5]'),(2,'[2,4,6]');
SELECT id, jt.* FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
EXPLAIN SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
--sorted_result
SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
EXPLAIN SELECT t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
EXPLAIN SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
# JSON_TABLE referring outer scope
SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
# JSON_TABLE referring another JSON_TABLE
SELECT id, jt1.*, jt2.*
FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
EXPLAIN SELECT id, jt1.*, jt2.*
FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
DROP TABLE t1;
SELECT * FROM JSON_TABLE ('"asdf"', '$' COLUMNS(
tm TIME PATH '$',
dt DATE PATH '$',
i INT PATH '$',
f FLOAT PATH '$',
d DECIMAL PATH '$')) AS jt;
# DEFAULT NULL is not accepted syntax.
--error ER_PARSE_ERROR
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt;
--error ER_PARSE_ERROR
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt;
# The DEFAULT value must be a string on JSON format for now.
--error ER_NOT_SUPPORTED_YET
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt;
--error ER_NOT_SUPPORTED_YET
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
--error ER_NOT_SUPPORTED_YET
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x DATE
PATH '$.x'
DEFAULT DATE'2020-01-01' ON EMPTY)) jt;
--error ER_NOT_SUPPORTED_YET
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x DATE
PATH '$.x'
DEFAULT DATE'2020-01-01' ON ERROR)) jt;
--echo #
--echo # Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF
--echo #
--error ER_BAD_FIELD_ERROR
SELECT a FROM JSON_TABLE(abc, '$[*]' COLUMNS ( a int path '$.a')) AS jt;
--echo #
--echo # Bug#25420680: ASSERTION `THD->IS_ERROR()' FAILED IN SQL/SQL_SELECT.CC
--echo #
SELECT * FROM JSON_TABLE('{"a":"2017-11-1"}',
'$' COLUMNS (jpath DATE PATH '$.a')) AS jt;
--echo #
--echo # Bug#25413826: ASSERTION `TABLE_LIST->ALIAS' FAILED
--echo #
--error ER_TF_MUST_HAVE_ALIAS
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a int path '$.b'));
--echo #
--echo # Bug#25421464: ASSERTION `!STRCMP(TABLE_REF->TABLE_NAME, ...
--echo #
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
SELECT * FROM v1;
SHOW CREATE VIEW v1;
DROP VIEW v1;
--echo #
--echo # Bug#25427457: ASSERTION `!((*REG_FIELD)->FLAGS & 16)'
--echo #
SELECT * FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (jpath JSON PATH '$.a',
o FOR ORDINALITY)) AS jt
WHERE o = 1;
--echo #
--echo # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H
--echo #
--error ER_WRONG_FIELD_WITH_GROUP
SELECT je,o FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (o FOR ORDINALITY,
je BIGINT EXISTS PATH '$.a')) AS jt
GROUP BY je;
SELECT je,COUNT(o) FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (o FOR ORDINALITY,
je BIGINT EXISTS PATH '$.a')) AS jt
GROUP BY je;
--echo #
--echo # Bug#25413194: ASSERTION `!(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE
--echo #
CREATE TABLE t1 (j JSON);
SELECT * FROM t1,JSON_TABLE(t1.j, '$[*]' COLUMNS ( a int path '$.b')) AS jt;
DROP TABLE t1;
--echo #
--echo # Bug#25460537:SIG 11 IN NEXT_FAST AT SQL/SQL_LIST.H
--echo #
PREPARE STMT FROM
"SELECT * FROM JSON_TABLE(
\'[{\"a\":\"3\"},{\"a\":2},{\"b\":1},{\"a\":0}]\',
\'$[*]\' COLUMNS (id
FOR ORDINALITY,
jpath VARCHAR(100) PATH \'$.a\',
jexst INT EXISTS PATH \'$.b\')
) as tt";
EXECUTE STMT;
EXECUTE STMT;
DEALLOCATE PREPARE stmt;
--echo #
--echo # Bug#25522353: SIG 11 IN JOIN::MAKE_JOIN_PLAN | SQL/SQL_OPTIMIZER.CC
--echo #
CREATE TABLE t1 (id INT, jc JSON);
--error ER_UNKNOWN_TABLE
SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
--error ER_UNKNOWN_TABLE
SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id;
SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id;
SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
# Error code can be ER_UNKNOWN_TABLE or ER_BAD_FIELD_ERROR
# (see find_field_in_tables()), both are acceptable.
--error ER_BAD_FIELD_ERROR
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
--error ER_UNKNOWN_TABLE
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
--error ER_UNKNOWN_TABLE
WITH qn AS
(SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
SELECT * from qn;
--error ER_UNKNOWN_TABLE
WITH qn AS
(SELECT 1 UNION
SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
SELECT * from qn;
--error ER_BAD_FIELD_ERROR
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
--error ER_UNKNOWN_TABLE
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3");
ANALYZE TABLE t1;
SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id INT PATH '$')) as jt ON t1.id=jt.id;
EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id INT PATH '$')) as jt ON t1.id=jt.id;
--error ER_UNKNOWN_TABLE
SELECT * FROM t1
LEFT JOIN
JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id
RIGHT JOIN
JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id;
DROP TABLE t1;
# Test that tmp table can overflow to disk
set @save_mem_se= @@internal_tmp_mem_storage_engine;
set @@internal_tmp_mem_storage_engine=MEMORY;
set @save_heap_size= @@max_heap_table_size;
set @@max_heap_table_size= 16384;
FLUSH STATUS;
SELECT * FROM JSON_TABLE(
'[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]',
'$[*]' COLUMNS (
c1 CHAR(255) PATH '$',
c2 CHAR(255) PATH '$',
c3 CHAR(255) PATH '$',
c4 CHAR(255) PATH '$',
c5 CHAR(255) PATH '$',
c6 CHAR(255) PATH '$',
c7 CHAR(255) PATH '$',
c8 CHAR(255) PATH '$')) AS jt;
SHOW STATUS LIKE '%tmp%';
set @@max_heap_table_size= @save_heap_size;
set @@internal_tmp_mem_storage_engine= @save_mem_se;
--echo #
--echo # Bug#25504063: EXPLAIN OF JSON_TABLE QUERY USES INTERNAL TEMP TABLES
--echo #
FLUSH STATUS;
SELECT * FROM
JSON_TABLE(
'[{"a":"3"}]',
'$[*]' COLUMNS (id FOR ORDINALITY)
) AS tt;
SHOW STATUS LIKE '%tmp%';
--echo #
--echo # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC
--echo #
--error ER_BAD_FIELD_ERROR
SELECT * FROM JSON_TABLE ( ( SELECT a ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
--error ER_WRONG_ARGUMENTS
SELECT * FROM JSON_TABLE ( ( SELECT 1 ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
--error ER_INVALID_GROUP_FUNC_USE
SELECT * FROM JSON_TABLE ( ( SUM(1) ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
--echo #
--echo # Bug# #25472875: ERROR SHOULD BE THROWN FOR INCORRECT VALUES
--echo #
--error ER_JT_VALUE_OUT_OF_RANGE
SELECT *
FROM JSON_TABLE('{"a":"1993-01-01"}',
'$' COLUMNS (jp DATE PATH '$.b' DEFAULT '1000' ON EMPTY))
AS jt;
--echo #
--echo # Bug#25532429: INVALID JSON ERROR NOT THROWN WITH EMPTY TABLES JOIN
--echo #
CREATE TABLE t1(j JSON);
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT * FROM t1,
JSON_TABLE( 'dqwfjqjf' , '$[*]' COLUMNS (col5 FOR ORDINALITY) ) AS alias7;
DROP TABLE t1;
--echo #
--echo # Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063
--echo #
let $query=
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(70) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
--echo # Too short field causes truncation, error and triggers ON ERROR clause
let $query=
SELECT * FROM
JSON_TABLE ('["3.14159"]',
'$[*]' COLUMNS (col18 CHAR(6) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
--echo #Truncated space doesn't trigger ON ERROR
let $query=
SELECT * FROM
JSON_TABLE ('["3.14159 "]',
'$[*]' COLUMNS (col18 CHAR(7) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(255) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 FLOAT PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DOUBLE PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
--error ER_JT_VALUE_OUT_OF_RANGE
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)
) AS alias2;
let $query=
SELECT * FROM
JSON_TABLE ('[0.9]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
eval EXPLAIN $query;
eval $query;
SELECT * FROM
JSON_TABLE ('["asdf","ghjk"]',
'$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
DEFAULT "3.14159" ON ERROR)
) AS alias2;
CREATE TABLE t1(jd JSON);
INSERT INTO t1 VALUES('["asdf"]'),('["ghjk"]');
SELECT * FROM t1,
JSON_TABLE (jd,
'$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
DEFAULT "3.14159" ON ERROR)
) AS alias2;
DROP TABLE t1;
--echo #
--echo # Bug#25540027: SIG 11 IN FIND_FIELD_IN_TABLE | SQL/SQL_BASE.CC
--echo #
CREATE TABLE t1(c1 JSON);
--error ER_NON_UPDATABLE_TABLE
UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
SET jt1.a=1;
--error ER_PARSE_ERROR
DELETE JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
FROM t1;
--error ER_PARSE_ERROR
DELETE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
USING t1;
DROP TABLE t1;
--echo #
--echo # Bug#25547244: ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(
--echo #
CREATE TABLE t1(i INT);
INSERT INTO t1 VALUES(1);
WITH cte_query AS
(SELECT * FROM t1, JSON_TABLE ( JSON_OBJECT('ISSKF',i) ,
'$[*]' COLUMNS (jtcol1 INT EXISTS PATH '$[*]') ) AS alias2)
SELECT jtcol1 AS field1 FROM cte_query;
DROP TABLE t1;
--echo #
--echo # Bug#25540675: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
--echo #
CREATE TABLE j1(j JSON);
INSERT INTO j1 VALUES('[1,2,3]'),('[1,2,4]');
SELECT * FROM j1,
JSON_TABLE ( JSON_OBJECT('key1', j) ,
'$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (col11 FOR ORDINALITY))) AS alias2;
DROP TABLE j1;
--echo #
--echo # Bug#25584335: SIG 11 IN TABLE_LIST::FETCH_NUMBER_OF_ROWS
--echo #
CREATE TABLE t1(i INT);
PREPARE stmt FROM "SELECT alias1.i AS field1 FROM (
t1 AS alias1,
(SELECT * FROM
JSON_TABLE ('[1,2,3]' ,
'$[*]' COLUMNS (`col_varchar` FOR ORDINALITY)) AS SQ1_alias1
) AS alias2 )";
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
--echo #
--echo # Bug#25604048: COLUMN NAMES WITH SAME 33-CHAR PREFIX ARE EQUAL
--echo #
SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
column_name_is_thirty_four_or_more VARCHAR(17) PATH '$.key1',
column_name_is_thirty_four_or_more_yes_indeed VARCHAR(17) PATH '$.key1'
) ) AS alias1;
--error ER_WRONG_COLUMN_NAME
SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
`column_name_is_thirty_four_or_more ` VARCHAR(17) PATH '$.key1'
) ) AS alias1;
--echo #
--echo # Bug#25604404: JSON_TABLE MORE RESTRICTIVE WITH IDENTIFIERS THAN
--echo # CREATE TABLE
--echo #
SELECT * FROM JSON_TABLE( '[1, 2]', '$' COLUMNS(
one INT PATH '$[0]', two INT PATH '$[1]'
)) AS jt;
--echo #
--echo # Bug#25588450: SIG 6 IN JSON_WRAPPER::SEEK|SQL/JSON_DOM.CC
--echo #
CREATE TABLE t1(c VARCHAR(10)) ENGINE=MEMORY;
INSERT INTO t1 VALUES('fiheife');
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT * FROM `t1` AS alias1, JSON_TABLE ( `c` , '$[*]' COLUMNS (jtcol1 JSON
PATH '$.*')) AS alias2 WHERE jtcol1 <= 'kjfh';
DROP TABLE t1;
--echo #
--echo # Bug#25587754: ASSERTION `FIXED == 0 || BASIC_CONST_ITEM()' FAILED
--echo #
PREPARE stmt FROM
"SELECT * FROM JSON_TABLE ( '[1,2]', '$[*]'
COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2";
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
--echo #
--echo # Bug#25584593: UNABLE TO USE JSON_TABLE() ON TEXT/BLOB JSON DATA
--echo #
SELECT * FROM JSON_TABLE (NULL, '$.k' COLUMNS (id FOR ORDINALITY)) AS aLias;
SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
SET @myjson = '{"k": 42}';
SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
CREATE TABLE t1(
txt TEXT, ty TINYTEXT, tm MEDIUMTEXT, tl LONGTEXT);
INSERT INTO t1 values (
'{"k": "text"}','{"k": "tinytext"}','{"k": "mediumtext"}','{"k": "longtext"}');
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE (t1.ty, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE (t1.tm, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE (t1.tl, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TEXT PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TINYTEXT PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id MEDIUMTEXT PATH '$')) AS alias;
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id LONGTEXT PATH '$')) AS alias;
# BLOB can store data from JSON
SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
# Check that type is printed correctly
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias;
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias;
# But can't be used as a data source
--error ER_WRONG_ARGUMENTS
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;
DROP TABLE t1;
--echo #
--echo # Bug#26500384: ASSERT FAILURE IN QUERY WITH WINDOW FUNCTION AND
--echo # JSON_TABLE
--echo #
CREATE TABLE t (x INT);
INSERT INTO t VALUES (1), (2), (3);
--error ER_BAD_FIELD_ERROR
SELECT MAX(t.x) OVER () m, jt.* FROM t,
JSON_TABLE(JSON_ARRAY(m), '$[*]' COLUMNS (i INT PATH '$')) jt;
DROP TABLE t;
--echo #
--echo # Bug#26583283: ASSERTION `!THD->IS_ERROR()' FAILED IN SQL_RESOLVER.CC
--echo #
EXPLAIN SELECT * FROM JSON_TABLE('null', '$' COLUMNS(AA DECIMAL PATH '$')) tt;
CREATE VIEW v1 AS SELECT * FROM
JSON_TABLE ( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt;
SELECT * FROM v1;
EXPLAIN SELECT * FROM v1;
DROP VIEW v1;
--echo #
--echo # Bug#25822408: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
--echo #
PREPARE stmt FROM "SELECT * FROM
JSON_TABLE('{\"a\":1}','$' COLUMNS (c1 CHAR(20) PATH '$.b' ERROR ON EMPTY)) jt";
--error ER_MISSING_JSON_TABLE_VALUE
EXECUTE stmt;
--error ER_MISSING_JSON_TABLE_VALUE
EXECUTE stmt;
--echo #
--echo # Bug#25594571: CRASH AT ITEM::CONST_ITEM|SQL/ITEM.H
--echo #
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(7);
PREPARE stmt FROM "SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2
LEFT JOIN JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (i FOR ORDINALITY )) AS
alias3 ON alias2 . `i` = alias3 . `i` ON alias1 . `i` = alias2 . `i`";
EXECUTE stmt;
EXECUTE stmt;
DROP TABLE t1;
--echo #
--echo # Bug#26648617: ASSERTION `IS_VIEW_OR_DERIVED() &&
--echo # USES_MATERIALIZATION()' FAILED.
--echo #
CREATE TABLE t1 (
col_varchar_key varchar(1) DEFAULT NULL
) 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);
EXPLAIN SELECT * FROM t1 WHERE col_varchar_key NOT IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
SELECT * FROM t1 WHERE col_varchar_key IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
EXPLAIN SELECT * FROM t1 WHERE col_varchar_key IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
DROP TABLE t1;
--echo #
--echo # Bug#26711551: WL8867:CONDITIONAL JUMP IN JSON_TABLE_COLUMN::CLEANUP
--echo #
CREATE TABLE t(x int, y int);
INSERT INTO t(x) VALUES (1);
UPDATE t t1, JSON_TABLE('[2]', '$[*]' COLUMNS (x INT PATH '$')) t2
SET t1.y = t2.x;
SELECT * FROM t;
DROP TABLE t;
--echo #
--echo # Bug#26679671: SIG 11 IN JSON_BINARY::PARSE_BINARY()
--echo #
CREATE TABLE t1(id INT, f1 JSON);
INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'),
(4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}');
ANALYZE TABLE t1;
ANALYZE TABLE t1;
--sorted_result
SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
EXPLAIN SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
t1 AS tt3) dt ORDER BY 1,3 LIMIT 10;
EXPLAIN SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
t1 AS tt3) dt ORDER BY 1,3 LIMIT 11;
SELECT * FROM t1 WHERE id IN
(SELECT id FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT id FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
DROP TABLE t1;
--echo #
--echo # Bug#26760811: WL#8867: MEMORY LEAK REPORTED BY ASAN AND VALGRIND
--echo #
CREATE TABLE t (j JSON);
INSERT INTO t VALUES
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'),
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]');
SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt;
PREPARE ps FROM
'SELECT COUNT(*) FROM t, JSON_TABLE(j, ''$[*]'' COLUMNS (i INT PATH ''$'')) AS jt';
EXECUTE ps;
EXECUTE ps;
DROP PREPARE ps;
DROP TABLE t;
--echo #
--echo # Bug #26781759: NON-UNIQUE ALIAS ERROR NOT BEING THROWN
--echo #
--error ER_NONUNIQ_TABLE
SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias,
JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias;
--echo #
--echo # Bug #26761470: WL#8867: JOIN::MAKE_JOIN_PLAN():
--echo # ASSERTION `SELECT_LEX->IS_RECURSIVE()' FAILED
--echo #
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (j JSON);
INSERT INTO t2 (j) VALUES ('[1,2,3]');
--sorted_result
SELECT * FROM t1 RIGHT JOIN
(SELECT o FROM t2, JSON_TABLE(j, '$[*]' COLUMNS (o FOR ORDINALITY)) AS jt) AS t3
ON (t3.o = t1.x);
DROP TABLE t1, t2;
--echo #
--echo # Bug#27152428 JSON_TABLE + PREPARED STATEMENT + VIEW HAS PROBLEM IN DURING RESOLUTION
--echo #
CREATE TABLE t1 (a INT, b INT);
CREATE VIEW v2 AS SELECT * FROM t1 LIMIT 2;
SELECT b
FROM (SELECT * FROM v2) vq1,
JSON_TABLE(CONCAT(vq1.b,'[{\"a\":\"3\"}]'),
'$[*]' COLUMNS (id FOR ORDINALITY,
jpath VARCHAR(100) PATH '$.a',
JEXST INT EXISTS PATH '$.b')
) AS dt;
DROP TABLE t1;
DROP VIEW v2;
--echo #
--echo # Bug#27189940: CREATE VIEW FAILS ON JSON_TABLE() IN SCHEMA-LESS CONNECTIONS
--echo # BUG#27217897: JSON_TABLE() FAILS IF NO DATABASE SELECTED
--echo #
# Connect without a schema name:
connect (conn1,localhost,root,,*NO-ONE*);
connection conn1;
CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
connection default;
disconnect conn1;
use test;
SHOW CREATE VIEW test.v;
SELECT * FROM test.v;
DROP VIEW test.v;
--echo #
--echo # Bug#27729112 JSON_TABLE SHOULD DECODE BASE64-ENCODED STRINGS
--echo #
SELECT v
FROM JSON_TABLE(JSON_OBJECT('foo', _binary'bar'), '$'
COLUMNS(v VARCHAR(255) PATH '$.foo')) tbl;
CREATE TABLE t1 (col1 VARCHAR(255) CHARACTER SET ucs2,
col2 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs);
INSERT INTO t1 VALUES ("æ", "ハ"), ("å", "ø"), ("ø", "パ"), ("@", "バ");
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2;
SELECT v value, c cumulfreq
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
JSON_TABLE(histogram->'$.buckets', '$[*]'
COLUMNS(v VARCHAR(255) CHARACTER SET ucs2 PATH '$[0]',
c double PATH '$[1]')) hist
WHERE column_name = "col1";
SELECT v value, c cumulfreq
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
JSON_TABLE(histogram->'$.buckets', '$[*]'
COLUMNS(v VARCHAR(255) CHARACTER SET utf8mb4 PATH '$[0]',
c double PATH '$[1]')) hist
WHERE column_name = "col2";
DROP TABLE t1;
--echo #
--echo # Bug#28254268: JSON_TABLE() FUNCTION REJECTS SELECT PERMISSIONS
--echo #
CREATE DATABASE db2;
USE db2;
CREATE TABLE t1 (c JSON);
INSERT INTO t1 VALUES('[1,2,3]');
CREATE USER user1@localhost;
GRANT SELECT ON db2.t1 TO user1@localhost;
connect (conn1,localhost,user1,,);
connection conn1;
USE db2;
SELECT t1.c FROM t1;
SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]'))
AS jt;
disconnect conn1;
connection default;
DROP USER user1@localhost;
DROP DATABASE db2;
--echo #
--echo # Bug#27856835 JSON_TABLE RETURNS WRONG DATATYPE WHEN INT-VALUE IS GRATER
--echo # THAN (2^31-1)
--echo #
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775807"}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
--echo # As we currently have no way of telling if a JSON string value is
--echo # signed or unsigned, this value will overflow.
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
--echo # Here the JSON value is a NUMERIC value, and we thus know if the value
--echo # is signed or unsigned.
SELECT id FROM JSON_TABLE('[{"id":9223372036854775808}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
--echo # If we tell the JSON table column to be unsigned, we get to store the
--echo # full value correctly.
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
(id BIGINT UNSIGNED PATH '$.id')) AS json;
SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS
(id INT UNSIGNED PATH '$.id')) AS json;
--echo # Check that we preserve the signedness of the columns.
USE test;
CREATE TABLE t1 AS SELECT id, value FROM
JSON_TABLE('[{"id":9223372036854775808, "value":9223372036854775807}]',
'$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id',
value BIGINT PATH '$.value'))
AS json;
SHOW CREATE TABLE t1;
DROP TABLE t1;
--echo #
--echo # Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED
--echo #
connect (conn1,localhost,root,,*NO-ONE*);
CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
SELECT * FROM test.v;
DROP VIEW test.v;
--echo # Check that a user without access to the schema 'foo' cannot query
--echo # a JSON_TABLE view in that schema.
CREATE SCHEMA foo;
CREATE VIEW foo.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
CREATE USER foo@localhost;
connect (con1,localhost,foo,,);
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM foo.v;
connection default;
disconnect con1;
DROP USER foo@localhost;
DROP SCHEMA foo;
--echo # Check that a user with access to the schema 'foo' can do a SELECT with
--echo # a JSON_TABLE function.
CREATE SCHEMA foo;
CREATE USER foo@localhost;
GRANT EXECUTE ON foo.* TO foo@localhost;
connect (con1,localhost,foo,,foo);
SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt;
connection default;
disconnect con1;
DROP USER foo@localhost;
DROP SCHEMA foo;
--echo #
--echo # Bug#27923406 ERROR 1142 (42000) WHEN USING JSON_TABLE
--echo #
CREATE SCHEMA my_schema;
CREATE USER foo@localhost;
GRANT EXECUTE ON my_schema.* TO foo@localhost;
connect (con1,localhost,foo,,my_schema);
SELECT
*
FROM
JSON_TABLE(
'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
"$[*]" COLUMNS(
xval VARCHAR(100) PATH "$.x",
yval VARCHAR(100) PATH "$.y"
)
) AS jt1;
connection default;
disconnect con1;
DROP USER foo@localhost;
DROP SCHEMA my_schema;
--echo #
--echo # Bug#28538315: JSON_TABLE() COLUMN TYPES DON'T SUPPORT COLLATE CLAUSE
--echo #
CREATE TABLE t1 SELECT *
FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
CHARSET utf8mb4
PATH '$')) AS jt1;
SHOW CREATE TABLE t1;
CREATE TABLE t2 SELECT *
FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
CHARSET utf8mb4 COLLATE utf8mb4_bin
PATH '$')) AS jt1;
SHOW CREATE TABLE t2;
CREATE TABLE t3 AS SELECT *
FROM JSON_TABLE('"a"', '$' COLUMNS (a VARCHAR(10)
COLLATE ascii_bin
PATH '$')) jt;
SHOW CREATE TABLE t3;
DROP TABLE t1, t2, t3;
--echo #
--echo # Bug#28643862 JSON_TABLE'S "COLUMNS" CLAUSE USES
--echo # GLOBAL.CHARACTER_SET_RESULTS DEFAULT CHARSET
--echo #
SET @@SESSION.character_set_connection = ascii;
CREATE TABLE t1 SELECT a.col
FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
SHOW CREATE TABLE t1;
SET @@SESSION.collation_connection = latin1_bin;
CREATE TABLE t2 SELECT a.col
FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;
SET @@SESSION.character_set_connection = DEFAULT;
--echo #
--echo # Bug#28851656: JSON_TABLE RETURN "UNKNOWN DATABASE ''" FROM A FUNCTION
--echo #
CREATE FUNCTION FN_COUNT_ROWS(X JSON)
RETURNS INT DETERMINISTIC
RETURN (
SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der
);
SELECT FN_COUNT_ROWS('[1, 2]') CNT;
SELECT FN_COUNT_ROWS('[1, 2, 3]') CNT;
SELECT FN_COUNT_ROWS('[1, 2, 3, 4]') CNT;
DROP FUNCTION FN_COUNT_ROWS;
--echo #
--echo # Bug#30310265: VIEWS LOSE THE CHARACTER SET OF JSON_TABLE'S
--echo # PATH ARGUMENTS
--echo #
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('{"å":{"å":1}}', '$.å' COLUMNS (x INT PATH '$.å')) t;
SELECT * FROM v1;
SET NAMES latin1;
# Used to return zero rows.
SELECT * FROM v1;
SET NAMES DEFAULT;
DROP VIEW v1;
CREATE VIEW v2 AS
SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (
x VARCHAR(10) PATH '$.a' DEFAULT '"isn''t here"' ON EMPTY)
) t;
# SHOW CREATE VIEW and SELECT from the view used to fail with a syntax error.
SHOW CREATE VIEW v2;
SELECT * FROM v2;
DROP VIEW v2;
# The string literals in JSON_TABLE didn't accept character set
# introducers. Verify that they are accepted now.
SELECT * FROM
JSON_TABLE(JSON_OBJECT(),
_utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' COLUMNS
(y INT PATH _utf8mb4'$.y'
DEFAULT _utf8mb4'1' ON EMPTY
DEFAULT _utf8mb4'2' ON ERROR))) jt;
--echo #
--echo # Bug#30382156: STORED PROCEDURE, JSON_TABLE AND "CONST" JOIN TYPE
--echo #
CREATE TABLE t (id INT PRIMARY KEY, j JSON);
INSERT INTO t VALUES (1, '{"x":1}');
CREATE PROCEDURE p()
SELECT * FROM t, JSON_TABLE(j, '$' COLUMNS (x INT PATH '$.x')) jt
WHERE id = 1;
CALL p();
CALL p();
CALL p();
DROP PROCEDURE p;
DROP TABLE t;
--echo #
--echo # WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax
--echo #
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
DEFAULT '"a"' ON ERROR DEFAULT '"b"' ON EMPTY)) jt;
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
NULL ON ERROR NULL ON EMPTY)) jt;
SELECT * FROM
JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
ERROR ON ERROR ERROR ON EMPTY)) jt;
--echo #
--echo # Bug#30628330: INSERT WITH JSON_TABLE FAILS DUE TO WARNING INVOKED
--echo # WITH ON ERROR CLAUSE
--echo #
CREATE TABLE t(id INT, a TINYINT, b VARCHAR(3), c DATE, d DECIMAL);
# This statement used to fail with "data too long".
INSERT INTO t SELECT * FROM
JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
JSON_OBJECT('a', 2, 'b', 'abcd'),
JSON_OBJECT('a', 1000, 'b', 'xyz'),
JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
'$[*]' COLUMNS (id FOR ORDINALITY,
a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
b VARCHAR(3) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
) AS jt;
SELECT * FROM t ORDER BY id;
DROP TABLE t;
--echo #
--echo # Bug#30263373: INCORRECT OUTPUT FROM TABLE_FUNCTION_JSON::PRINT()
--echo #
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[123]', '$[*]' COLUMNS (`name with space 1` INT PATH '$',
`name with space 2` FOR ORDINALITY)) jt;
# Used to fail with a syntax error, due to unquoted column names in
# the view definition.
SELECT * FROM v;
DROP VIEW v;
CREATE VIEW v AS SELECT HEX(x), HEX(y) FROM
JSON_TABLE('["abc"]', '$[*]' COLUMNS (x BINARY(10) PATH '$',
y VARBINARY(10) PATH '$')) jt;
# Used to say CHAR(10) and VARCHAR(10) instead of BINARY(10) and VARBINARY(10).
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
SELECT * FROM v;
DROP VIEW v;
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[123]', '$[*]' COLUMNS(ti TINYINT PATH '$',
si SMALLINT PATH '$',
mi MEDIUMINT PATH '$',
i INT PATH '$',
bi BIGINT PATH '$',
tiu TINYINT UNSIGNED PATH '$',
siu SMALLINT UNSIGNED PATH '$',
miu MEDIUMINT UNSIGNED PATH '$',
iu INT UNSIGNED PATH '$',
biu BIGINT UNSIGNED PATH '$')) jt;
# Used to lack the UNSIGNED attribute for the unsigned columns.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
SELECT * FROM v;
DROP VIEW v;
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[]', '$[*]' COLUMNS (g GEOMETRY PATH '$',
ls LINESTRING PATH '$')) AS jt;
# Used to say GEOMETRY for both columns.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
SELECT * FROM v;
DROP VIEW v;
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('["abc"]', '$[*]' COLUMNS
(x VARCHAR(10) CHARSET latin1 PATH '$',
y VARCHAR(10) COLLATE utf8mb4_de_pb_0900_ai_ci PATH '$',
z LONGTEXT COLLATE utf8mb4_bin PATH '$')) jt;
# Character set and collation information wasn't included.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
# Used to return the default collation instead of the collation
# specified in the JSON_TABLE column definitions.
SELECT x, y, z, COLLATION(x) c_x, COLLATION(y) c_y, COLLATION(z) c_z FROM v;
DROP VIEW v;
--echo #
--echo # Bug#31345503 JSON_TABLE SHOULD RETURN SQL NULL FOR JSON NULL
--echo #
SELECT *
FROM
JSON_TABLE(
'[
{"c1": null,
"c2": [{"c": "c_val", "l": [1,2]}],
"c3": null},
{"c1": true,
"c2": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [false]}],
"c3": true},
{"c1": false,
"c2": [{"c": null,"l": [true]}, {"c": "c_val", "l": [null]}],
"c3": false}
]',
'$[*]' COLUMNS(
top_ord FOR ORDINALITY,
c1path VARCHAR(10) PATH '$.c1' ERROR ON ERROR,
NESTED PATH '$.c2[*]' COLUMNS (
c2path VARCHAR(10) PATH '$.c' ERROR ON ERROR,
ord FOR ORDINALITY,
NESTED PATH '$.l[*]' COLUMNS (lpath_c VARCHAR(10) PATH '$' ERROR ON ERROR,
lpath_i INT PATH '$' ERROR ON ERROR)
),
c3path INT PATH '$.c3' ERROR ON ERROR
)
) as jt;
--echo #
--echo # Bug #31327187 UBSAN: JSON_TABLE: NULL POINTER PASSED AS ARGUMENT 2,
--echo # WHICH IS DECLARED TO NEVER BE NULL
--echo #
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;
#
# WL#8867: Add JSON_TABLE table function
#
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
id jpath jexst
1 3 0
2 2 0
3 NULL 1
4 0 0
explain select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b')) `tt`
explain format=tree select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
EXPLAIN
-> Materialize table function
select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' default '[99]' on error,
jexst int exists path '$.b')
) as tt;
ERROR 42000: Invalid default value for 'jpath'
select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a'
default '33' on empty
default '66' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
id jpath jsn_path jexst
1 33 {"x": 33} 0
2 2 2 0
3 33 {"x": 33} 1
4 0 0 0
5 66 [1, 2] 0
explain select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a'
default '33' on empty
default '66' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a' default '33' on empty default '66' on error, `jsn_path` json path '$.a' default '{"x":33}' on empty, `jexst` int exists path '$.b')) `tt`
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
'$[*]' columns (id for ordinality,
jpath_i int path '$.a'
default '33' on empty
default '66' on error,
jpath_r real path '$.a'
default '33.3' on empty
default '77.7' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
id jpath_i jpath_r jsn_path jexst
1 3 3 "3" 0
2 2 2 2 0
3 33 33.3 {"x": 33} 1
4 0 0.33 0.33 0
5 66 77.7 "asd" 0
explain select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
'$[*]' columns (id for ordinality,
jpath_i int path '$.a'
default '33' on empty
default '66' on error,
jpath_r real path '$.a'
default '33.3' on empty
default '77.7' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath_i` AS `jpath_i`,`tt`.`jpath_r` AS `jpath_r`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]', '$[*]' columns (`id` for ordinality, `jpath_i` int path '$.a' default '33' on empty default '66' on error, `jpath_r` double path '$.a' default '33.3' on empty default '77.7' on error, `jsn_path` json path '$.a' default '{"x":33}' on empty, `jexst` int exists path '$.b')) `tt`
select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a'
default '33' on empty
default '66' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
id jpath jsn_path jexst
1 33 {"x": 33} 0
2 2 2 0
3 33 {"x": 33} 1
4 0 0 0
5 66 [1, 2] 0
explain select * from
json_table(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a'
default '33' on empty
default '66' on error,
jsn_path json path '$.a' default '{"x":33}' on empty,
jexst int exists path '$.b')
) as tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a' default '33' on empty default '66' on error, `jsn_path` json path '$.a' default '{"x":33}' on empty, `jexst` int exists path '$.b')) `tt`
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
json_path json path '$.a',
jexst int exists path '$.b')
) as tt;
id jpath json_path jexst
1 3 "3" 0
2 2 2 0
3 NULL NULL 1
4 0 0 0
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
json_path json path '$.a',
jexst int exists path '$.b')
) as tt
where id = 3;
id jpath json_path jexst
3 NULL NULL 1
explain select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
json_path json path '$.a',
jexst int exists path '$.b')
) as tt
where id = 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ref <auto_key0> <auto_key0> 9 const 1 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`json_path` AS `json_path`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `json_path` json path '$.a', `jexst` int exists path '$.b')) `tt` where (`tt`.`id` = 3)
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' error on empty,
jexst int exists path '$.b')
) as tt;
ERROR 22035: Missing value for JSON_TABLE column 'jpath'
select * from
json_table(
'[{"a":"3"},{"a":2},{"a":1},{"a":[0,1]}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a' error on error,
jexst int exists path '$.b')
) as tt;
ERROR 2203F: Can't store an array or an object in the scalar JSON_TABLE column 'jpath'
select * from
json_table(
'!#@$!@#$',
'$[*]' columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0.
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
"!@#$!@#$" columns (id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b')
) as tt;
ERROR 42000: Invalid JSON path expression. The error is around character position 1.
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
jpath varchar(100) path "!@#$!@#$",
jexst int exists path '$.b')
) as tt;
ERROR 42000: Invalid JSON path expression. The error is around character position 1.
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
id for ordinality)
) as tt;
ERROR 42S21: Duplicate column name 'id'
select * from
json_table(
'[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
'$[*]' columns (id for ordinality,
_id for ordinality)
) as tt;
id _id
1 1
2 2
3 3
4 4
select * from
json_table(
'[
{"a":"3", "n": { "l": 1}},
{"a":2, "n": { "l": 1}},
{"b":1, "n": { "l": 1}},
{"a":0, "n": { "l": 1}}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l')
)
) as tt;
id jpath jexst id_n jpath_n
1 3 0 1 1
2 2 0 1 1
3 NULL 1 1 1
4 0 0 1 1
explain select * from
json_table(
'[
{"a":"3", "n": { "l": 1}},
{"a":2, "n": { "l": 1}},
{"b":1, "n": { "l": 1}},
{"a":0, "n": { "l": 1}}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l')
)
) as tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n` from json_table('[\n {"a":"3", "n": { "l": 1}},\n {"a":2, "n": { "l": 1}},\n {"b":1, "n": { "l": 1}},\n {"a":0, "n": { "l": 1}}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'))) `tt`
explain format=json select * from
json_table(
'[
{"a":"3", "n": { "l": 1}},
{"a":2, "n": { "l": 1}},
{"b":1, "n": { "l": 1}},
{"a":0, "n": { "l": 1}}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l')
)
) as tt;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.72"
},
"table": {
"table_name": "tt",
"access_type": "ALL",
"rows_examined_per_scan": 2,
"rows_produced_per_join": 2,
"filtered": "100.00",
"table_function": "json_table",
"using_temporary_table": true,
"cost_info": {
"read_cost": "2.52",
"eval_cost": "0.20",
"prefix_cost": "2.73",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"jpath",
"jexst",
"id_n",
"jpath_n"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n` from json_table('[\n {"a":"3", "n": { "l": 1}},\n {"a":2, "n": { "l": 1}},\n {"b":1, "n": { "l": 1}},\n {"a":0, "n": { "l": 1}}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'))) `tt`
select * from
json_table(
'[
{"a":2, "n": [{ "l": 1}, {"l": 11}]},
{"a":1, "n": [{ "l": 2}, {"l": 22}]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(50) path '$.a',
nested path '$.n[*]' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l'),
nested path '$.n[*]' columns (
id_m for ordinality,
jpath_m varchar(50) path '$.l')
)
) as tt;
id jpath id_n jpath_n id_m jpath_m
1 2 1 1 NULL NULL
1 2 2 11 NULL NULL
1 2 NULL NULL 1 1
1 2 NULL NULL 2 11
2 1 1 2 NULL NULL
2 1 2 22 NULL NULL
2 1 NULL NULL 1 2
2 1 NULL NULL 2 22
explain select * from
json_table(
'[
{"a":2, "n": [{ "l": 1}, {"l": 11}]},
{"a":1, "n": [{ "l": 2}, {"l": 22}]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(50) path '$.a',
nested path '$.n[*]' columns (
id_n for ordinality,
jpath_n varchar(50) path '$.l'),
nested path '$.n[*]' columns (
id_m for ordinality,
jpath_m varchar(50) path '$.l')
)
) as tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n`,`tt`.`id_m` AS `id_m`,`tt`.`jpath_m` AS `jpath_m` from json_table('[\n {"a":2, "n": [{ "l": 1}, {"l": 11}]},\n {"a":1, "n": [{ "l": 2}, {"l": 22}]}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(50) character set utf8mb4 path '$.a', nested path '$.n[*]' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'), nested path '$.n[*]' columns (`id_m` for ordinality, `jpath_m` varchar(50) character set utf8mb4 path '$.l'))) `tt`
select * from json_table(
'[
{"a":"3", "n": ["b","a","c"]},
{"a":2, "n": [1,2]},
{"b":1, "n": ["zzz"]},
{"a":0, "n": [0.1, 0.02]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id_n for ordinality,
jpath_n varchar(50) path '$')
)
) as tt;
id jpath jexst id_n jpath_n
1 3 0 1 b
1 3 0 2 a
1 3 0 3 c
2 2 0 1 1
2 2 0 2 2
3 NULL 1 1 zzz
4 0 0 1 0.1
4 0 0 2 0.02
select * from json_table(
'[
{"a":"3", "n": ["b","a","c"]},
{"a":2, "n": [1,2]},
{"b":1, "n": ["zzz"]},
{"a":0, "n": [0.1, 0.02]}
]',
'$[*]' columns (
id for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id_n1 for ordinality,
jpath_n1 varchar(50) path '$') ,
nested path '$.n[*]' columns (
id_n2 for ordinality,
jpath_n2 varchar(50) path '$')
)
) as tt;
id jpath jexst id_n1 jpath_n1 id_n2 jpath_n2
1 3 0 1 b NULL NULL
1 3 0 2 a NULL NULL
1 3 0 3 c NULL NULL
1 3 0 NULL NULL 1 b
1 3 0 NULL NULL 2 a
1 3 0 NULL NULL 3 c
2 2 0 1 1 NULL NULL
2 2 0 2 2 NULL NULL
2 2 0 NULL NULL 1 1
2 2 0 NULL NULL 2 2
3 NULL 1 1 zzz NULL NULL
3 NULL 1 NULL NULL 1 zzz
4 0 0 1 0.1 NULL NULL
4 0 0 2 0.02 NULL NULL
4 0 0 NULL NULL 1 0.1
4 0 0 NULL NULL 2 0.02
select * from json_table(
'[
{"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},
{"ll":["c"]} ]},
{"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},
{"b":1, "n": [{"ll":["zzz"]}]},
{"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}
]',
'$[*]' columns (
id1 for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id2 for ordinality,
nested path '$.ll[*]' columns (
id3 for ordinality,
jpath_3 varchar(50) path '$')
),
nested path '$.n[*]' columns (
id4 for ordinality,
jpath_4 json path '$')
)
) as tt;
id1 jpath jexst id2 id3 jpath_3 id4 jpath_4
1 3 0 1 1 b1 NULL NULL
1 3 0 1 2 b2 NULL NULL
1 3 0 1 3 b3 NULL NULL
1 3 0 2 1 a1 NULL NULL
1 3 0 2 2 a2 NULL NULL
1 3 0 3 1 c NULL NULL
1 3 0 NULL NULL NULL 1 {"ll": ["b1", "b2", "b3"]}
1 3 0 NULL NULL NULL 2 {"ll": ["a1", "a2"]}
1 3 0 NULL NULL NULL 3 {"ll": ["c"]}
2 2 0 1 1 1 NULL NULL
2 2 0 1 2 11 NULL NULL
2 2 0 1 3 111 NULL NULL
2 2 0 2 1 2 NULL NULL
2 2 0 NULL NULL NULL 1 {"ll": [1, 11, 111]}
2 2 0 NULL NULL NULL 2 {"ll": [2]}
3 NULL 1 1 1 zzz NULL NULL
3 NULL 1 NULL NULL NULL 1 {"ll": ["zzz"]}
4 0 0 1 1 0.1 NULL NULL
4 0 0 1 2 0.01 NULL NULL
4 0 0 2 1 0.02 NULL NULL
4 0 0 2 2 0.002 NULL NULL
4 0 0 2 3 0.0002 NULL NULL
4 0 0 NULL NULL NULL 1 {"ll": [0.1, 0.01]}
4 0 0 NULL NULL NULL 2 {"ll": [0.02, 0.002, 0.0002]}
explain select * from json_table(
'[
{"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},
{"ll":["c"]} ]},
{"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},
{"b":1, "n": [{"ll":["zzz"]}]},
{"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}
]',
'$[*]' columns (
id1 for ordinality,
jpath varchar(100) path '$.a',
jexst int exists path '$.b',
nested path '$.n[*]' columns (
id2 for ordinality,
nested path '$.ll[*]' columns (
id3 for ordinality,
jpath_3 varchar(50) path '$')
),
nested path '$.n[*]' columns (
id4 for ordinality,
jpath_4 json path '$')
)
) as tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`id1` AS `id1`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id2` AS `id2`,`tt`.`id3` AS `id3`,`tt`.`jpath_3` AS `jpath_3`,`tt`.`id4` AS `id4`,`tt`.`jpath_4` AS `jpath_4` from json_table('[\n {"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},\n {"ll":["c"]} ]},\n {"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},\n {"b":1, "n": [{"ll":["zzz"]}]},\n {"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}\n ]', '$[*]' columns (`id1` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n[*]' columns (`id2` for ordinality, nested path '$.ll[*]' columns (`id3` for ordinality, `jpath_3` varchar(50) character set utf8mb4 path '$')), nested path '$.n[*]' columns (`id4` for ordinality, `jpath_4` json path '$'))) `tt`
ord should be 1,1,1,2, which tells that first two values of 'l' are
from the same object, and next two are from different objects
SELECT *
FROM JSON_TABLE(
'[{"a": "a_val",
"b": [
{"c": "c_val",
"l": [1,2]}
]
}, {"a": "a_val",
"b": [
{"c": "c_val",
"l": [11]},
{"c": "c_val",
"l": [22]}
]
}]',
'$[*]' COLUMNS (
apath VARCHAR(10) PATH '$.a',
NESTED PATH '$.b[*]' COLUMNS (
bpath VARCHAR(10) PATH '$.c',
ord FOR ORDINALITY,
NESTED PATH '$.l[*]' COLUMNS (
lpath varchar(10) PATH '$'
)
)
)) as jt;
apath bpath ord lpath
a_val c_val 1 1
a_val c_val 1 2
a_val c_val 1 11
a_val c_val 2 22
explain SELECT *
FROM JSON_TABLE(
'[{"a": "a_val",
"b": [
{"c": "c_val",
"l": [1,2]}
]
}, {"a": "a_val",
"b": [
{"c": "c_val",
"l": [11]},
{"c": "c_val",
"l": [22]}
]
}]',
'$[*]' COLUMNS (
apath VARCHAR(10) PATH '$.a',
NESTED PATH '$.b[*]' COLUMNS (
bpath VARCHAR(10) PATH '$.c',
ord FOR ORDINALITY,
NESTED PATH '$.l[*]' COLUMNS (
lpath varchar(10) PATH '$'
)
)
)) as jt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `jt`.`apath` AS `apath`,`jt`.`bpath` AS `bpath`,`jt`.`ord` AS `ord`,`jt`.`lpath` AS `lpath` from json_table('[{"a": "a_val",\n "b": [\n {"c": "c_val",\n "l": [1,2]}\n ]\n }, {"a": "a_val",\n "b": [\n {"c": "c_val",\n "l": [11]},\n {"c": "c_val",\n "l": [22]}\n ]\n }]', '$[*]' columns (`apath` varchar(10) character set utf8mb4 path '$.a', nested path '$.b[*]' columns (`bpath` varchar(10) character set utf8mb4 path '$.c', `ord` for ordinality, nested path '$.l[*]' columns (`lpath` varchar(10) character set utf8mb4 path '$')))) `jt`
CREATE TABLE jt( i JSON );
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1;
i a
EXPLAIN SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 1 100.00 NULL
1 SIMPLE tt NULL ref <auto_key0> <auto_key0> 5 const 1 100.00 Table function: json_table; Using temporary; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`jt`.`i` AS `i`,`tt`.`a` AS `a` from `test`.`jt` join json_table(`test`.`jt`.`i`, '$' columns (`a` int path '$')) `tt` where (`tt`.`a` = 1)
SELECT * FROM (
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1) AS ttt;
i a
EXPLAIN SELECT * FROM (
SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
WHERE a=1) AS ttt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 1 100.00 NULL
1 SIMPLE tt NULL ref <auto_key0> <auto_key0> 5 const 1 100.00 Table function: json_table; Using temporary; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`jt`.`i` AS `i`,`tt`.`a` AS `a` from `test`.`jt` join json_table(`test`.`jt`.`i`, '$' columns (`a` int path '$')) `tt` where (`tt`.`a` = 1)
DROP TABLE jt;
SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
'$' COLUMNS (dt DATE PATH '$')) as tt;
dt
NULL
SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
'$' COLUMNS (dt TIME PATH '$')) as tt;
dt
11:22:33
SELECT * FROM JSON_TABLE(CAST(CAST("2001.02.03" AS DATE) AS JSON),
'$' COLUMNS (dt DATE PATH '$')) as tt;
dt
2001-02-03
CREATE VIEW v AS
SELECT * FROM JSON_TABLE('[1,2,3]',
'$[*]' COLUMNS (num INT PATH '$.a'
DEFAULT '123' ON EMPTY
DEFAULT '456' ON ERROR)) AS jt;
SELECT * FROM v;
num
123
123
123
SHOW CREATE VIEW v;
View Create View character_set_client collation_connection
v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`num` AS `num` from json_table('[1,2,3]', '$[*]' columns (`num` int path '$.a' default '123' on empty default '456' on error)) `jt` utf8mb4 utf8mb4_0900_ai_ci
DROP VIEW v;
SELECT * FROM JSON_TABLE('"asdf"',
'$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
ERROR 22018: Invalid JSON value for CAST to INTEGER from column a at row 1
SELECT * FROM
JSON_TABLE('[{"a":1},{"a":2}]',
'$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
ERROR 2203F: Can't store an array or an object in the scalar JSON_TABLE column 'a'
SELECT * FROM
JSON_TABLE('[{"a":1},{"a":2}]',
'$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
a
[1, 2]
SELECT * FROM
JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
ERROR 22003: Value is out of range for JSON_TABLE's column 'a'
SELECT * FROM
JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt;
a
NULL
SELECT * FROM
JSON_TABLE('{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{}}}}}}}}}}}}}}}}}}}',
'$' COLUMNS (i0 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i1 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i2 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i3 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i4 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i5 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i6 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i7 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i8 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i9 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i10 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i11 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i12 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i13 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i14 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i15 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i16 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i17 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i18 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i19 INT PATH '$.a',
NESTED PATH '$.b' COLUMNS (i20 INT PATH '$.a'
)))))))))))))))))))))) jt;
ERROR 42000: More than supported 16 NESTED PATHs were found in JSON_TABLE 'jt'
CREATE TABLE t1(id int, jd JSON);
INSERT INTO t1 values (1, '[1,3,5]'),(2,'[2,4,6]');
SELECT id, jt.* FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
id jid val
1 1 1
1 2 3
1 3 5
2 1 2
2 2 4
2 3 6
SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
id jid val
1 1 1
1 2 3
1 3 5
2 1 2
2 2 4
2 3 6
EXPLAIN SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt`
SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
id id jid val
1 1 1 1
1 1 2 3
1 1 3 5
1 2 1 1
1 2 2 3
1 2 3 5
2 1 1 2
2 1 2 4
2 1 3 6
2 2 1 2
2 2 2 4
2 2 3 6
EXPLAIN SELECT t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt` join `test`.`t1` `t2`
EXPLAIN SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
FROM t1,
JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt,
t1 AS t2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join)
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t2`,`jt`) */ `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt` join `test`.`t1` `t2`
SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
id jd
1 [1, 3, 5]
2 [2, 4, 6]
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1 SIMPLE jt NULL ref <auto_key0> <auto_key0> 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index; FirstMatch(t1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jd` AS `jd` from `test`.`t1` semi join (json_table('[1,2]', '$[*]' columns (`id` int path '$')) `jt`) where (`jt`.`id` = `test`.`t1`.`id`)
SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
id jd
1 [1, 3, 5]
2 [2, 4, 6]
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
(id INT PATH '$')) AS jt);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1 SIMPLE jt NULL ref <auto_key0> <auto_key0> 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.jd' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jd` AS `jd` from `test`.`t1` semi join (json_table(`test`.`t1`.`jd`, '$[*]' columns (`id` int path '$')) `jt`) where (`jt`.`id` = `test`.`t1`.`id`)
SELECT id, jt1.*, jt2.*
FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
id data1 id2
1 [1, 3, 5] 1
1 [1, 3, 5] 3
1 [1, 3, 5] 5
2 [2, 4, 6] 2
2 [2, 4, 6] 4
2 [2, 4, 6] 6
EXPLAIN SELECT id, jt1.*, jt2.*
FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
1 SIMPLE jt1 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1 SIMPLE jt2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`jt1`.`data1` AS `data1`,`jt2`.`id2` AS `id2` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$' columns (`data1` json path '$')) `jt1` join json_table(`jt1`.`data1`, '$[*]' columns (`id2` int path '$')) `jt2`
DROP TABLE t1;
SELECT * FROM JSON_TABLE ('"asdf"', '$' COLUMNS(
tm TIME PATH '$',
dt DATE PATH '$',
i INT PATH '$',
f FLOAT PATH '$',
d DECIMAL PATH '$')) AS jt;
tm dt i f d
NULL NULL NULL NULL NULL
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON EMPTY)) jt' at line 2
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON ERROR)) jt' at line 2
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt;
ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x DATE
PATH '$.x'
DEFAULT DATE'2020-01-01' ON EMPTY)) jt;
ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x DATE
PATH '$.x'
DEFAULT DATE'2020-01-01' ON ERROR)) jt;
ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
#
# Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF
#
SELECT a FROM JSON_TABLE(abc, '$[*]' COLUMNS ( a int path '$.a')) AS jt;
ERROR 42S22: Unknown column 'abc' in 'a table function argument'
#
# Bug#25420680: ASSERTION `THD->IS_ERROR()' FAILED IN SQL/SQL_SELECT.CC
#
SELECT * FROM JSON_TABLE('{"a":"2017-11-1"}',
'$' COLUMNS (jpath DATE PATH '$.a')) AS jt;
jpath
2017-11-01
#
# Bug#25413826: ASSERTION `TABLE_LIST->ALIAS' FAILED
#
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a int path '$.b'));
ERROR 42000: Every table function must have an alias
#
# Bug#25421464: ASSERTION `!STRCMP(TABLE_REF->TABLE_NAME, ...
#
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
SELECT * FROM v1;
a
2
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`a` AS `a` from json_table('[{"a": 1, "b": 2}]', '$[*]' columns (`a` int path '$.b')) `jt` utf8mb4 utf8mb4_0900_ai_ci
DROP VIEW v1;
#
# Bug#25427457: ASSERTION `!((*REG_FIELD)->FLAGS & 16)'
#
SELECT * FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (jpath JSON PATH '$.a',
o FOR ORDINALITY)) AS jt
WHERE o = 1;
jpath o
"1" 1
#
# Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H
#
SELECT je,o FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (o FOR ORDINALITY,
je BIGINT EXISTS PATH '$.a')) AS jt
GROUP BY je;
ERROR 42000: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jt.o' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT je,COUNT(o) FROM JSON_TABLE('{"a":"1"}',
'$' COLUMNS (o FOR ORDINALITY,
je BIGINT EXISTS PATH '$.a')) AS jt
GROUP BY je;
je COUNT(o)
1 1
#
# Bug#25413194: ASSERTION `!(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE
#
CREATE TABLE t1 (j JSON);
SELECT * FROM t1,JSON_TABLE(t1.j, '$[*]' COLUMNS ( a int path '$.b')) AS jt;
j a
DROP TABLE t1;
#
# Bug#25460537:SIG 11 IN NEXT_FAST AT SQL/SQL_LIST.H
#
PREPARE STMT FROM
"SELECT * FROM JSON_TABLE(
\'[{\"a\":\"3\"},{\"a\":2},{\"b\":1},{\"a\":0}]\',
\'$[*]\' COLUMNS (id
FOR ORDINALITY,
jpath VARCHAR(100) PATH \'$.a\',
jexst INT EXISTS PATH \'$.b\')
) as tt";
EXECUTE STMT;
id jpath jexst
1 3 0
2 2 0
3 NULL 1
4 0 0
EXECUTE STMT;
id jpath jexst
1 3 0
2 2 0
3 NULL 1
4 0 0
DEALLOCATE PREPARE stmt;
#
# Bug#25522353: SIG 11 IN JOIN::MAKE_JOIN_PLAN | SQL/SQL_OPTIMIZER.CC
#
CREATE TABLE t1 (id INT, jc JSON);
SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
ERROR 42S02: Unknown table 't1' in a table function argument
SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id;
ERROR 42S02: Unknown table 't1' in a table function argument
SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id;
id id jc
SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
id jc id
EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join json_table(`test`.`t1`.`jc`, '$' columns (`id` for ordinality)) `jt` on((`test`.`t1`.`jc` = `jt`.`id`)) where true
SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
id jc id jc id
EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
1 SIMPLE t1o NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1o`.`id` AS `id`,`test`.`t1o`.`jc` AS `jc`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join `test`.`t1` `t1o` on((`test`.`t1o`.`id` = `test`.`t1`.`id`)) left join json_table(`test`.`t1`.`jc`, '$' columns (`id` for ordinality)) `jt` on((`test`.`t1`.`jc` = `jt`.`id`)) where true
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
ERROR 42S22: Unknown column 't1o.jc' in 'a table function argument'
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
ERROR 42S02: Unknown table 't1o' in a table function argument
WITH qn AS
(SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
SELECT * from qn;
ERROR 42S02: Unknown table 't1' in a table function argument
WITH qn AS
(SELECT 1 UNION
SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
SELECT * from qn;
ERROR 42S02: Unknown table 't1' in a table function argument
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
ERROR 42S22: Unknown column 't1o.jc' in 'a table function argument'
SELECT * FROM t1 AS t1o RIGHT JOIN
(t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
(id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
ON t1o.id=t1i.id;
ERROR 42S02: Unknown table 't1o' in a table function argument
INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3");
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id INT PATH '$')) as jt ON t1.id=jt.id;
id jc id
1 1 1
2 4 NULL
3 3 3
EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
(id INT PATH '$')) as jt ON t1.id=jt.id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
1 SIMPLE jt NULL ref <auto_key0> <auto_key0> 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join json_table(`test`.`t1`.`jc`, '$' columns (`id` int path '$')) `jt` on((`jt`.`id` = `test`.`t1`.`id`)) where true
SELECT * FROM t1
LEFT JOIN
JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id
RIGHT JOIN
JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id;
ERROR 42S02: Unknown table 'jt' in a table function argument
DROP TABLE t1;
set @save_mem_se= @@internal_tmp_mem_storage_engine;
set @@internal_tmp_mem_storage_engine=MEMORY;
set @save_heap_size= @@max_heap_table_size;
set @@max_heap_table_size= 16384;
FLUSH STATUS;
SELECT * FROM JSON_TABLE(
'[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]',
'$[*]' COLUMNS (
c1 CHAR(255) PATH '$',
c2 CHAR(255) PATH '$',
c3 CHAR(255) PATH '$',
c4 CHAR(255) PATH '$',
c5 CHAR(255) PATH '$',
c6 CHAR(255) PATH '$',
c7 CHAR(255) PATH '$',
c8 CHAR(255) PATH '$')) AS jt;
c1 c2 c3 c4 c5 c6 c7 c8
1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2
3 3 3 3 3 3 3 3
4 4 4 4 4 4 4 4
5 5 5 5 5 5 5 5
6 6 6 6 6 6 6 6
7 7 7 7 7 7 7 7
8 8 8 8 8 8 8 8
9 9 9 9 9 9 9 9
10 10 10 10 10 10 10 10
11 11 11 11 11 11 11 11
12 12 12 12 12 12 12 12
13 13 13 13 13 13 13 13
14 14 14 14 14 14 14 14
15 15 15 15 15 15 15 15
16 16 16 16 16 16 16 16
17 17 17 17 17 17 17 17
18 18 18 18 18 18 18 18
19 19 19 19 19 19 19 19
20 20 20 20 20 20 20 20
SHOW STATUS LIKE '%tmp%';
Variable_name Value
Created_tmp_disk_tables 1
Created_tmp_files 0
Created_tmp_tables 1
set @@max_heap_table_size= @save_heap_size;
set @@internal_tmp_mem_storage_engine= @save_mem_se;
#
# Bug#25504063: EXPLAIN OF JSON_TABLE QUERY USES INTERNAL TEMP TABLES
#
FLUSH STATUS;
SELECT * FROM
JSON_TABLE(
'[{"a":"3"}]',
'$[*]' COLUMNS (id FOR ORDINALITY)
) AS tt;
id
1
SHOW STATUS LIKE '%tmp%';
Variable_name Value
Created_tmp_disk_tables 0
Created_tmp_files 0
Created_tmp_tables 1
#
# Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC
#
SELECT * FROM JSON_TABLE ( ( SELECT a ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
ERROR 42S22: Unknown column 'a' in 'field list'
SELECT * FROM JSON_TABLE ( ( SELECT 1 ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
ERROR HY000: Incorrect arguments to JSON_TABLE
SELECT * FROM JSON_TABLE ( ( SUM(1) ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
AS alias1;
ERROR HY000: Invalid use of group function
#
# Bug# #25472875: ERROR SHOULD BE THROWN FOR INCORRECT VALUES
#
SELECT *
FROM JSON_TABLE('{"a":"1993-01-01"}',
'$' COLUMNS (jp DATE PATH '$.b' DEFAULT '1000' ON EMPTY))
AS jt;
ERROR 22003: Value is out of range for JSON_TABLE's column 'jp'
#
# Bug#25532429: INVALID JSON ERROR NOT THROWN WITH EMPTY TABLES JOIN
#
CREATE TABLE t1(j JSON);
SELECT * FROM t1,
JSON_TABLE( 'dqwfjqjf' , '$[*]' COLUMNS (col5 FOR ORDINALITY) ) AS alias7;
ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0.
DROP TABLE t1;
#
# Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063
#
EXPLAIN SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(70) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` char(70) character set utf8mb4 path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(70) PATH '$')
) AS alias2;
col18
3.14159
# Too short field causes truncation, error and triggers ON ERROR clause
EXPLAIN SELECT * FROM
JSON_TABLE ('["3.14159"]',
'$[*]' COLUMNS (col18 CHAR(6) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('["3.14159"]', '$[*]' columns (`col18` char(6) character set utf8mb4 path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('["3.14159"]',
'$[*]' COLUMNS (col18 CHAR(6) PATH '$')
) AS alias2;
col18
NULL
#Truncated space doesn't trigger ON ERROR
EXPLAIN SELECT * FROM
JSON_TABLE ('["3.14159 "]',
'$[*]' COLUMNS (col18 CHAR(7) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('["3.14159 "]', '$[*]' columns (`col18` char(7) character set utf8mb4 path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('["3.14159 "]',
'$[*]' COLUMNS (col18 CHAR(7) PATH '$')
) AS alias2;
col18
3.14159
EXPLAIN SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(255) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` char(255) character set utf8mb4 path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 CHAR(255) PATH '$')
) AS alias2;
col18
3.14159
EXPLAIN SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` varchar(70) character set utf8mb4 path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
) AS alias2;
col18
3.14159
EXPLAIN SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` varchar(255) character set utf8mb4 path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
) AS alias2;
col18
3.14159
EXPLAIN SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 FLOAT PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` float path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 FLOAT PATH '$')
) AS alias2;
col18
3.14159
EXPLAIN SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DOUBLE PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` double path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DOUBLE PATH '$')
) AS alias2;
col18
3.14159
EXPLAIN SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` decimal(3,3) path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
col18
NULL
SELECT * FROM
JSON_TABLE ('[3.14159]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)
) AS alias2;
ERROR 22003: Value is out of range for JSON_TABLE's column 'col18'
EXPLAIN SELECT * FROM
JSON_TABLE ('[0.9]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[0.9]', '$[*]' columns (`col18` decimal(3,3) path '$')) `alias2`
SELECT * FROM
JSON_TABLE ('[0.9]',
'$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
) AS alias2;
col18
0.900
SELECT * FROM
JSON_TABLE ('["asdf","ghjk"]',
'$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
DEFAULT "3.14159" ON ERROR)
) AS alias2;
col18
3.142
3.142
Warnings:
Note 1265 Data truncated for column 'col18' at row 1
CREATE TABLE t1(jd JSON);
INSERT INTO t1 VALUES('["asdf"]'),('["ghjk"]');
SELECT * FROM t1,
JSON_TABLE (jd,
'$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
DEFAULT "3.14159" ON ERROR)
) AS alias2;
jd col18
["asdf"] 3.142
["ghjk"] 3.142
Warnings:
Note 1265 Data truncated for column 'col18' at row 1
DROP TABLE t1;
#
# Bug#25540027: SIG 11 IN FIND_FIELD_IN_TABLE | SQL/SQL_BASE.CC
#
CREATE TABLE t1(c1 JSON);
UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
SET jt1.a=1;
ERROR HY000: The target table jt1 of the UPDATE is not updatable
DELETE JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
FROM t1' at line 1
DELETE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
USING t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
USING t1' at line 1
DROP TABLE t1;
#
# Bug#25547244: ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(
#
CREATE TABLE t1(i INT);
INSERT INTO t1 VALUES(1);
WITH cte_query AS
(SELECT * FROM t1, JSON_TABLE ( JSON_OBJECT('ISSKF',i) ,
'$[*]' COLUMNS (jtcol1 INT EXISTS PATH '$[*]') ) AS alias2)
SELECT jtcol1 AS field1 FROM cte_query;
field1
DROP TABLE t1;
#
# Bug#25540675: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
#
CREATE TABLE j1(j JSON);
INSERT INTO j1 VALUES('[1,2,3]'),('[1,2,4]');
SELECT * FROM j1,
JSON_TABLE ( JSON_OBJECT('key1', j) ,
'$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (col11 FOR ORDINALITY))) AS alias2;
j col11
[1, 2, 3] NULL
[1, 2, 4] NULL
DROP TABLE j1;
#
# Bug#25584335: SIG 11 IN TABLE_LIST::FETCH_NUMBER_OF_ROWS
#
CREATE TABLE t1(i INT);
PREPARE stmt FROM "SELECT alias1.i AS field1 FROM (
t1 AS alias1,
(SELECT * FROM
JSON_TABLE ('[1,2,3]' ,
'$[*]' COLUMNS (`col_varchar` FOR ORDINALITY)) AS SQ1_alias1
) AS alias2 )";
EXECUTE stmt;
field1
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
#
# Bug#25604048: COLUMN NAMES WITH SAME 33-CHAR PREFIX ARE EQUAL
#
SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
column_name_is_thirty_four_or_more VARCHAR(17) PATH '$.key1',
column_name_is_thirty_four_or_more_yes_indeed VARCHAR(17) PATH '$.key1'
) ) AS alias1;
column_name_is_thirty_four_or_more column_name_is_thirty_four_or_more_yes_indeed
test test
SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
`column_name_is_thirty_four_or_more ` VARCHAR(17) PATH '$.key1'
) ) AS alias1;
ERROR 42000: Incorrect column name 'column_name_is_thirty_four_or_more '
#
# Bug#25604404: JSON_TABLE MORE RESTRICTIVE WITH IDENTIFIERS THAN
# CREATE TABLE
#
SELECT * FROM JSON_TABLE( '[1, 2]', '$' COLUMNS(
one INT PATH '$[0]', two INT PATH '$[1]'
)) AS jt;
one two
1 2
#
# Bug#25588450: SIG 6 IN JSON_WRAPPER::SEEK|SQL/JSON_DOM.CC
#
CREATE TABLE t1(c VARCHAR(10)) ENGINE=MEMORY;
INSERT INTO t1 VALUES('fiheife');
SELECT * FROM `t1` AS alias1, JSON_TABLE ( `c` , '$[*]' COLUMNS (jtcol1 JSON
PATH '$.*')) AS alias2 WHERE jtcol1 <= 'kjfh';
ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 1.
DROP TABLE t1;
#
# Bug#25587754: ASSERTION `FIXED == 0 || BASIC_CONST_ITEM()' FAILED
#
PREPARE stmt FROM
"SELECT * FROM JSON_TABLE ( '[1,2]', '$[*]'
COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2";
EXECUTE stmt;
jtcol1
NULL
NULL
DEALLOCATE PREPARE stmt;
#
# Bug#25584593: UNABLE TO USE JSON_TABLE() ON TEXT/BLOB JSON DATA
#
SELECT * FROM JSON_TABLE (NULL, '$.k' COLUMNS (id FOR ORDINALITY)) AS aLias;
id
SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
id
SET @myjson = '{"k": 42}';
SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
id
1
CREATE TABLE t1(
txt TEXT, ty TINYTEXT, tm MEDIUMTEXT, tl LONGTEXT);
INSERT INTO t1 values (
'{"k": "text"}','{"k": "tinytext"}','{"k": "mediumtext"}','{"k": "longtext"}');
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
id
text
SELECT alias.* FROM t1, JSON_TABLE (t1.ty, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
id
tinytext
SELECT alias.* FROM t1, JSON_TABLE (t1.tm, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
id
mediumtext
SELECT alias.* FROM t1, JSON_TABLE (t1.tl, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
id
longtext
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TEXT PATH '$')) AS alias;
id
text
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TINYTEXT PATH '$')) AS alias;
id
text
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id MEDIUMTEXT PATH '$')) AS alias;
id
text
SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id LONGTEXT PATH '$')) AS alias;
id
text
SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
blb
asd123
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` blob path '$')) `alias`
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` tinyblob path '$')) `alias`
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` mediumblob path '$')) `alias`
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` longblob path '$')) `alias`
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` text character set utf8mb4 path '$')) `alias`
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` tinytext character set utf8mb4 path '$')) `alias`
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` mediumtext character set utf8mb4 path '$')) `alias`
EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` longtext character set utf8mb4 path '$')) `alias`
SELECT * FROM
(SELECT CAST(blb AS JSON) jf FROM
JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) AS jti) AS dt,
JSON_TABLE (jf, '$' COLUMNS (blb BLOB PATH '$')) AS jto;
ERROR HY000: Incorrect arguments to JSON_TABLE
DROP TABLE t1;
#
# Bug#26500384: ASSERT FAILURE IN QUERY WITH WINDOW FUNCTION AND
# JSON_TABLE
#
CREATE TABLE t (x INT);
INSERT INTO t VALUES (1), (2), (3);
SELECT MAX(t.x) OVER () m, jt.* FROM t,
JSON_TABLE(JSON_ARRAY(m), '$[*]' COLUMNS (i INT PATH '$')) jt;
ERROR 42S22: Unknown column 'm' in 'a table function argument'
DROP TABLE t;
#
# Bug#26583283: ASSERTION `!THD->IS_ERROR()' FAILED IN SQL_RESOLVER.CC
#
EXPLAIN SELECT * FROM JSON_TABLE('null', '$' COLUMNS(AA DECIMAL PATH '$')) tt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `tt`.`AA` AS `AA` from json_table('null', '$' columns (`AA` decimal(10,0) path '$')) `tt`
CREATE VIEW v1 AS SELECT * FROM
JSON_TABLE ( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt;
SELECT * FROM v1;
c1
NULL
EXPLAIN SELECT * FROM v1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `jt`.`c1` AS `c1` from json_table('null', '$' columns (`c1` decimal(10,0) path '$')) `jt`
DROP VIEW v1;
#
# Bug#25822408: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
#
PREPARE stmt FROM "SELECT * FROM
JSON_TABLE('{\"a\":1}','$' COLUMNS (c1 CHAR(20) PATH '$.b' ERROR ON EMPTY)) jt";
EXECUTE stmt;
ERROR 22035: Missing value for JSON_TABLE column 'c1'
EXECUTE stmt;
ERROR 22035: Missing value for JSON_TABLE column 'c1'
#
# Bug#25594571: CRASH AT ITEM::CONST_ITEM|SQL/ITEM.H
#
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(7);
PREPARE stmt FROM "SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2
LEFT JOIN JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (i FOR ORDINALITY )) AS
alias3 ON alias2 . `i` = alias3 . `i` ON alias1 . `i` = alias2 . `i`";
EXECUTE stmt;
i i i
1 1 1
2 2 2
3 3 3
4 4 NULL
5 5 NULL
6 6 NULL
7 7 NULL
EXECUTE stmt;
i i i
1 1 1
2 2 2
3 3 3
4 4 NULL
5 5 NULL
6 6 NULL
7 7 NULL
DROP TABLE t1;
#
# Bug#26648617: ASSERTION `IS_VIEW_OR_DERIVED() &&
# USES_MATERIALIZATION()' FAILED.
#
CREATE TABLE t1 (
col_varchar_key varchar(1) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1),(4);
SELECT * FROM t1 WHERE col_varchar_key NOT IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
col_varchar_key
4
EXPLAIN SELECT * FROM t1 WHERE col_varchar_key NOT IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
2 DEPENDENT SUBQUERY innr1 NULL index_subquery <auto_key0> <auto_key0> 43 func 4 100.00 Table function: json_table; Using temporary; Using where; Using index; Full scan on NULL key
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`col_varchar_key`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`col_varchar_key`) in innr1 on <auto_key0> checking NULL where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`col_varchar_key`) = `innr1`.`col_varchar_key`) or (`innr1`.`col_varchar_key` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`innr1`.`col_varchar_key`), true))) is false)
SELECT * FROM t1 WHERE col_varchar_key IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
col_varchar_key
1
EXPLAIN SELECT * FROM t1 WHERE col_varchar_key IN (
SELECT col_varchar_key FROM JSON_TABLE(
'[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
(col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1 SIMPLE innr1 NULL ref <auto_key0> <auto_key0> 43 test.t1.col_varchar_key 2 100.00 Table function: json_table; Using temporary; Using where; Using index; FirstMatch(t1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` semi join (json_table('[{"col_key": 1},{"col_key": 2}]', '$[*]' columns (`col_varchar_key` varchar(10) character set utf8mb4 path '$.col_key')) `innr1`) where (`test`.`t1`.`col_varchar_key` = `innr1`.`col_varchar_key`)
DROP TABLE t1;
#
# Bug#26711551: WL8867:CONDITIONAL JUMP IN JSON_TABLE_COLUMN::CLEANUP
#
CREATE TABLE t(x int, y int);
INSERT INTO t(x) VALUES (1);
UPDATE t t1, JSON_TABLE('[2]', '$[*]' COLUMNS (x INT PATH '$')) t2
SET t1.y = t2.x;
SELECT * FROM t;
x y
1 2
DROP TABLE t;
#
# Bug#26679671: SIG 11 IN JSON_BINARY::PARSE_BINARY()
#
CREATE TABLE t1(id INT, f1 JSON);
INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'),
(4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}');
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
id f1 id f1
1 {"1": 1} 1 {"1": 1}
1 {"1": 1} 2 {"1": 2}
1 {"1": 1} 3 {"1": 3}
1 {"1": 1} 4 {"1": 4}
1 {"1": 1} 5 {"1": 5}
1 {"1": 1} 6 {"1": 6}
2 {"1": 2} 1 {"1": 1}
2 {"1": 2} 2 {"1": 2}
2 {"1": 2} 3 {"1": 3}
2 {"1": 2} 4 {"1": 4}
2 {"1": 2} 5 {"1": 5}
2 {"1": 2} 6 {"1": 6}
3 {"1": 3} 1 {"1": 1}
3 {"1": 3} 2 {"1": 2}
3 {"1": 3} 3 {"1": 3}
3 {"1": 3} 4 {"1": 4}
3 {"1": 3} 5 {"1": 5}
3 {"1": 3} 6 {"1": 6}
4 {"1": 4} 1 {"1": 1}
4 {"1": 4} 2 {"1": 2}
4 {"1": 4} 3 {"1": 3}
4 {"1": 4} 4 {"1": 4}
4 {"1": 4} 5 {"1": 5}
4 {"1": 4} 6 {"1": 6}
5 {"1": 5} 1 {"1": 1}
5 {"1": 5} 2 {"1": 2}
5 {"1": 5} 3 {"1": 3}
5 {"1": 5} 4 {"1": 4}
5 {"1": 5} 5 {"1": 5}
5 {"1": 5} 6 {"1": 6}
6 {"1": 6} 1 {"1": 1}
6 {"1": 6} 2 {"1": 2}
6 {"1": 6} 3 {"1": 3}
6 {"1": 6} 4 {"1": 4}
6 {"1": 6} 5 {"1": 5}
6 {"1": 6} 6 {"1": 6}
EXPLAIN SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jj1 NULL ALL NULL NULL NULL NULL 6 100.00 NULL
1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join)
1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
Warnings:
Note 1003 /* select#1 */ select `test`.`jj1`.`id` AS `id`,`test`.`jj1`.`f1` AS `f1`,`test`.`tt2`.`id` AS `id`,`test`.`tt2`.`f1` AS `f1` from `test`.`t1` `jj1` join `test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`id` for ordinality)) `tbl`
SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
t1 AS tt3) dt ORDER BY 1,3 LIMIT 10;
id f1 id f1
1 {"1": 1} 1 {"1": 1}
1 {"1": 1} 1 {"1": 1}
1 {"1": 1} 1 {"1": 1}
1 {"1": 1} 1 {"1": 1}
1 {"1": 1} 1 {"1": 1}
1 {"1": 1} 1 {"1": 1}
1 {"1": 1} 2 {"1": 2}
1 {"1": 1} 2 {"1": 2}
1 {"1": 1} 2 {"1": 2}
1 {"1": 1} 2 {"1": 2}
EXPLAIN SELECT * FROM t1 as jj1,
(SELECT tt2.* FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
t1 AS tt3) dt ORDER BY 1,3 LIMIT 11;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jj1 NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort
1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join)
1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1 SIMPLE tt3 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join)
Warnings:
Note 1003 /* select#1 */ select `test`.`jj1`.`id` AS `id`,`test`.`jj1`.`f1` AS `f1`,`test`.`tt2`.`id` AS `id`,`test`.`tt2`.`f1` AS `f1` from `test`.`t1` `jj1` join `test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`id` for ordinality)) `tbl` straight_join `test`.`t1` `tt3` order by `test`.`jj1`.`id`,`test`.`tt2`.`id` limit 11
SELECT * FROM t1 WHERE id IN
(SELECT id FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
id f1
1 {"1": 1}
2 {"1": 2}
3 {"1": 3}
4 {"1": 4}
5 {"1": 5}
6 {"1": 6}
EXPLAIN SELECT * FROM t1 WHERE id IN
(SELECT id FROM t1 as tt2,
JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (hash join)
1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; End temporary
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` semi join (`test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`jf` for ordinality)) `tbl`) where (`test`.`t1`.`id` = `test`.`tt2`.`id`)
DROP TABLE t1;
#
# Bug#26760811: WL#8867: MEMORY LEAK REPORTED BY ASAN AND VALGRIND
#
CREATE TABLE t (j JSON);
INSERT INTO t VALUES
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'),
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]');
SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt;
COUNT(*)
34
PREPARE ps FROM
'SELECT COUNT(*) FROM t, JSON_TABLE(j, ''$[*]'' COLUMNS (i INT PATH ''$'')) AS jt';
EXECUTE ps;
COUNT(*)
34
EXECUTE ps;
COUNT(*)
34
DROP PREPARE ps;
DROP TABLE t;
#
# Bug #26781759: NON-UNIQUE ALIAS ERROR NOT BEING THROWN
#
SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias,
JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias;
ERROR 42000: Not unique table/alias: 'jalias'
#
# Bug #26761470: WL#8867: JOIN::MAKE_JOIN_PLAN():
# ASSERTION `SELECT_LEX->IS_RECURSIVE()' FAILED
#
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (j JSON);
INSERT INTO t2 (j) VALUES ('[1,2,3]');
SELECT * FROM t1 RIGHT JOIN
(SELECT o FROM t2, JSON_TABLE(j, '$[*]' COLUMNS (o FOR ORDINALITY)) AS jt) AS t3
ON (t3.o = t1.x);
x o
1 1
NULL 2
NULL 3
DROP TABLE t1, t2;
#
# Bug#27152428 JSON_TABLE + PREPARED STATEMENT + VIEW HAS PROBLEM IN DURING RESOLUTION
#
CREATE TABLE t1 (a INT, b INT);
CREATE VIEW v2 AS SELECT * FROM t1 LIMIT 2;
SELECT b
FROM (SELECT * FROM v2) vq1,
JSON_TABLE(CONCAT(vq1.b,'[{\"a\":\"3\"}]'),
'$[*]' COLUMNS (id FOR ORDINALITY,
jpath VARCHAR(100) PATH '$.a',
JEXST INT EXISTS PATH '$.b')
) AS dt;
b
DROP TABLE t1;
DROP VIEW v2;
#
# Bug#27189940: CREATE VIEW FAILS ON JSON_TABLE() IN SCHEMA-LESS CONNECTIONS
# BUG#27217897: JSON_TABLE() FAILS IF NO DATABASE SELECTED
#
CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
num
1
2
3
use test;
SHOW CREATE VIEW test.v;
View Create View character_set_client collation_connection
v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`num` AS `num` from json_table('[1,2,3]', '$[*]' columns (`num` int path '$[0]')) `jt` utf8mb4 utf8mb4_0900_ai_ci
SELECT * FROM test.v;
num
1
2
3
DROP VIEW test.v;
#
# Bug#27729112 JSON_TABLE SHOULD DECODE BASE64-ENCODED STRINGS
#
SELECT v
FROM JSON_TABLE(JSON_OBJECT('foo', _binary'bar'), '$'
COLUMNS(v VARCHAR(255) PATH '$.foo')) tbl;
v
bar
CREATE TABLE t1 (col1 VARCHAR(255) CHARACTER SET ucs2,
col2 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs);
INSERT INTO t1 VALUES ("æ", "ハ"), ("å", "ø"), ("ø", "パ"), ("@", "バ");
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2;
Table Op Msg_type Msg_text
test.t1 histogram status Histogram statistics created for column 'col1'.
test.t1 histogram status Histogram statistics created for column 'col2'.
SELECT v value, c cumulfreq
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
JSON_TABLE(histogram->'$.buckets', '$[*]'
COLUMNS(v VARCHAR(255) CHARACTER SET ucs2 PATH '$[0]',
c double PATH '$[1]')) hist
WHERE column_name = "col1";
value cumulfreq
@ 0.25
å 0.5
æ 0.75
ø 1
SELECT v value, c cumulfreq
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
JSON_TABLE(histogram->'$.buckets', '$[*]'
COLUMNS(v VARCHAR(255) CHARACTER SET utf8mb4 PATH '$[0]',
c double PATH '$[1]')) hist
WHERE column_name = "col2";
value cumulfreq
ø 0.25
ハ 0.5
バ 0.75
パ 1
DROP TABLE t1;
#
# Bug#28254268: JSON_TABLE() FUNCTION REJECTS SELECT PERMISSIONS
#
CREATE DATABASE db2;
USE db2;
CREATE TABLE t1 (c JSON);
INSERT INTO t1 VALUES('[1,2,3]');
CREATE USER user1@localhost;
GRANT SELECT ON db2.t1 TO user1@localhost;
USE db2;
SELECT t1.c FROM t1;
c
[1, 2, 3]
SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]'))
AS jt;
num
1
2
3
DROP USER user1@localhost;
DROP DATABASE db2;
#
# Bug#27856835 JSON_TABLE RETURNS WRONG DATATYPE WHEN INT-VALUE IS GRATER
# THAN (2^31-1)
#
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775807"}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
id
9223372036854775807
# As we currently have no way of telling if a JSON string value is
# signed or unsigned, this value will overflow.
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
id
-9223372036854775808
# Here the JSON value is a NUMERIC value, and we thus know if the value
# is signed or unsigned.
SELECT id FROM JSON_TABLE('[{"id":9223372036854775808}]', '$[*]' COLUMNS
(id BIGINT PATH '$.id')) AS json;
id
NULL
# If we tell the JSON table column to be unsigned, we get to store the
# full value correctly.
SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
(id BIGINT UNSIGNED PATH '$.id')) AS json;
id
9223372036854775808
SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS
(id INT UNSIGNED PATH '$.id')) AS json;
id
2147483648
# Check that we preserve the signedness of the columns.
USE test;
CREATE TABLE t1 AS SELECT id, value FROM
JSON_TABLE('[{"id":9223372036854775808, "value":9223372036854775807}]',
'$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id',
value BIGINT PATH '$.value'))
AS json;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` bigint unsigned DEFAULT NULL,
`value` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
#
# Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED
#
CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
SELECT * FROM test.v;
num
1
2
3
DROP VIEW test.v;
# Check that a user without access to the schema 'foo' cannot query
# a JSON_TABLE view in that schema.
CREATE SCHEMA foo;
CREATE VIEW foo.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
CREATE USER foo@localhost;
SELECT * FROM foo.v;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v'
DROP USER foo@localhost;
DROP SCHEMA foo;
# Check that a user with access to the schema 'foo' can do a SELECT with
# a JSON_TABLE function.
CREATE SCHEMA foo;
CREATE USER foo@localhost;
GRANT EXECUTE ON foo.* TO foo@localhost;
SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt;
1
1
1
1
DROP USER foo@localhost;
DROP SCHEMA foo;
#
# Bug#27923406 ERROR 1142 (42000) WHEN USING JSON_TABLE
#
CREATE SCHEMA my_schema;
CREATE USER foo@localhost;
GRANT EXECUTE ON my_schema.* TO foo@localhost;
SELECT
*
FROM
JSON_TABLE(
'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
"$[*]" COLUMNS(
xval VARCHAR(100) PATH "$.x",
yval VARCHAR(100) PATH "$.y"
)
) AS jt1;
xval yval
2 8
3 7
4 6
DROP USER foo@localhost;
DROP SCHEMA my_schema;
#
# Bug#28538315: JSON_TABLE() COLUMN TYPES DON'T SUPPORT COLLATE CLAUSE
#
CREATE TABLE t1 SELECT *
FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
CHARSET utf8mb4
PATH '$')) AS jt1;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`x` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE t2 SELECT *
FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
CHARSET utf8mb4 COLLATE utf8mb4_bin
PATH '$')) AS jt1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`x` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE t3 AS SELECT *
FROM JSON_TABLE('"a"', '$' COLUMNS (a VARCHAR(10)
COLLATE ascii_bin
PATH '$')) jt;
SHOW CREATE TABLE t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` varchar(10) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1, t2, t3;
#
# Bug#28643862 JSON_TABLE'S "COLUMNS" CLAUSE USES
# GLOBAL.CHARACTER_SET_RESULTS DEFAULT CHARSET
#
SET @@SESSION.character_set_connection = ascii;
CREATE TABLE t1 SELECT a.col
FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`col` varchar(10) CHARACTER SET ascii DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SET @@SESSION.collation_connection = latin1_bin;
CREATE TABLE t2 SELECT a.col
FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`col` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1, t2;
SET @@SESSION.character_set_connection = DEFAULT;
#
# Bug#28851656: JSON_TABLE RETURN "UNKNOWN DATABASE ''" FROM A FUNCTION
#
CREATE FUNCTION FN_COUNT_ROWS(X JSON)
RETURNS INT DETERMINISTIC
RETURN (
SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der
);
SELECT FN_COUNT_ROWS('[1, 2]') CNT;
CNT
2
SELECT FN_COUNT_ROWS('[1, 2, 3]') CNT;
CNT
3
SELECT FN_COUNT_ROWS('[1, 2, 3, 4]') CNT;
CNT
4
DROP FUNCTION FN_COUNT_ROWS;
#
# Bug#30310265: VIEWS LOSE THE CHARACTER SET OF JSON_TABLE'S
# PATH ARGUMENTS
#
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('{"å":{"å":1}}', '$.å' COLUMNS (x INT PATH '$.å')) t;
SELECT * FROM v1;
x
1
SET NAMES latin1;
SELECT * FROM v1;
x
1
SET NAMES DEFAULT;
DROP VIEW v1;
CREATE VIEW v2 AS
SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (
x VARCHAR(10) PATH '$.a' DEFAULT '"isn''t here"' ON EMPTY)
) t;
SHOW CREATE VIEW v2;
View Create View character_set_client collation_connection
v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t`.`x` AS `x` from json_table('{}', '$' columns (`x` varchar(10) character set utf8mb4 path '$.a' default '"isn\'t here"' on empty)) `t` utf8mb4 utf8mb4_0900_ai_ci
SELECT * FROM v2;
x
isn't here
DROP VIEW v2;
SELECT * FROM
JSON_TABLE(JSON_OBJECT(),
_utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' COLUMNS
(y INT PATH _utf8mb4'$.y'
DEFAULT _utf8mb4'1' ON EMPTY
DEFAULT _utf8mb4'2' ON ERROR))) jt;
y
NULL
#
# Bug#30382156: STORED PROCEDURE, JSON_TABLE AND "CONST" JOIN TYPE
#
CREATE TABLE t (id INT PRIMARY KEY, j JSON);
INSERT INTO t VALUES (1, '{"x":1}');
CREATE PROCEDURE p()
SELECT * FROM t, JSON_TABLE(j, '$' COLUMNS (x INT PATH '$.x')) jt
WHERE id = 1;
CALL p();
id j x
1 {"x": 1} 1
CALL p();
id j x
1 {"x": 1} 1
CALL p();
id j x
1 {"x": 1} 1
DROP PROCEDURE p;
DROP TABLE t;
#
# WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax
#
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
DEFAULT '"a"' ON ERROR DEFAULT '"b"' ON EMPTY)) jt;
x
b
Warnings:
Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead.
SELECT * FROM
JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
NULL ON ERROR NULL ON EMPTY)) jt;
x
NULL
Warnings:
Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead.
SELECT * FROM
JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
ERROR ON ERROR ERROR ON EMPTY)) jt;
x
c
Warnings:
Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead.
#
# Bug#30628330: INSERT WITH JSON_TABLE FAILS DUE TO WARNING INVOKED
# WITH ON ERROR CLAUSE
#
CREATE TABLE t(id INT, a TINYINT, b VARCHAR(3), c DATE, d DECIMAL);
INSERT INTO t SELECT * FROM
JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
JSON_OBJECT('a', 2, 'b', 'abcd'),
JSON_OBJECT('a', 1000, 'b', 'xyz'),
JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
'$[*]' COLUMNS (id FOR ORDINALITY,
a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
b VARCHAR(3) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
) AS jt;
SELECT * FROM t ORDER BY id;
id a b c d
1 1 abc NULL NULL
2 2 ERR NULL NULL
3 111 xyz NULL NULL
4 NULL NULL 2001-01-01 999
DROP TABLE t;
#
# Bug#30263373: INCORRECT OUTPUT FROM TABLE_FUNCTION_JSON::PRINT()
#
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[123]', '$[*]' COLUMNS (`name with space 1` INT PATH '$',
`name with space 2` FOR ORDINALITY)) jt;
SELECT * FROM v;
name with space 1 name with space 2
123 1
DROP VIEW v;
CREATE VIEW v AS SELECT HEX(x), HEX(y) FROM
JSON_TABLE('["abc"]', '$[*]' COLUMNS (x BINARY(10) PATH '$',
y VARBINARY(10) PATH '$')) jt;
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
VIEW_DEFINITION
select hex(`jt`.`x`) AS `HEX(x)`,hex(`jt`.`y`) AS `HEX(y)` from json_table('["abc"]', '$[*]' columns (`x` binary(10) path '$', `y` varbinary(10) path '$')) `jt`
SELECT * FROM v;
HEX(x) HEX(y)
61626300000000000000 616263
DROP VIEW v;
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[123]', '$[*]' COLUMNS(ti TINYINT PATH '$',
si SMALLINT PATH '$',
mi MEDIUMINT PATH '$',
i INT PATH '$',
bi BIGINT PATH '$',
tiu TINYINT UNSIGNED PATH '$',
siu SMALLINT UNSIGNED PATH '$',
miu MEDIUMINT UNSIGNED PATH '$',
iu INT UNSIGNED PATH '$',
biu BIGINT UNSIGNED PATH '$')) jt;
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
VIEW_DEFINITION
select `jt`.`ti` AS `ti`,`jt`.`si` AS `si`,`jt`.`mi` AS `mi`,`jt`.`i` AS `i`,`jt`.`bi` AS `bi`,`jt`.`tiu` AS `tiu`,`jt`.`siu` AS `siu`,`jt`.`miu` AS `miu`,`jt`.`iu` AS `iu`,`jt`.`biu` AS `biu` from json_table('[123]', '$[*]' columns (`ti` tinyint path '$', `si` smallint path '$', `mi` mediumint path '$', `i` int path '$', `bi` bigint path '$', `tiu` tinyint unsigned path '$', `siu` smallint unsigned path '$', `miu` mediumint unsigned path '$', `iu` int unsigned path '$', `biu` bigint unsigned path '$')) `jt`
SELECT * FROM v;
ti si mi i bi tiu siu miu iu biu
123 123 123 123 123 123 123 123 123 123
DROP VIEW v;
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('[]', '$[*]' COLUMNS (g GEOMETRY PATH '$',
ls LINESTRING PATH '$')) AS jt;
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
VIEW_DEFINITION
select `jt`.`g` AS `g`,`jt`.`ls` AS `ls` from json_table('[]', '$[*]' columns (`g` geometry path '$', `ls` linestring path '$')) `jt`
SELECT * FROM v;
g ls
DROP VIEW v;
CREATE VIEW v AS SELECT * FROM
JSON_TABLE('["abc"]', '$[*]' COLUMNS
(x VARCHAR(10) CHARSET latin1 PATH '$',
y VARCHAR(10) COLLATE utf8mb4_de_pb_0900_ai_ci PATH '$',
z LONGTEXT COLLATE utf8mb4_bin PATH '$')) jt;
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
VIEW_DEFINITION
select `jt`.`x` AS `x`,`jt`.`y` AS `y`,`jt`.`z` AS `z` from json_table('["abc"]', '$[*]' columns (`x` varchar(10) character set latin1 path '$', `y` varchar(10) character set utf8mb4 collate utf8mb4_de_pb_0900_ai_ci path '$', `z` longtext character set utf8mb4 collate utf8mb4_bin path '$')) `jt`
SELECT x, y, z, COLLATION(x) c_x, COLLATION(y) c_y, COLLATION(z) c_z FROM v;
x y z c_x c_y c_z
abc abc abc latin1_swedish_ci utf8mb4_de_pb_0900_ai_ci utf8mb4_bin
DROP VIEW v;
#
# Bug#31345503 JSON_TABLE SHOULD RETURN SQL NULL FOR JSON NULL
#
SELECT *
FROM
JSON_TABLE(
'[
{"c1": null,
"c2": [{"c": "c_val", "l": [1,2]}],
"c3": null},
{"c1": true,
"c2": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [false]}],
"c3": true},
{"c1": false,
"c2": [{"c": null,"l": [true]}, {"c": "c_val", "l": [null]}],
"c3": false}
]',
'$[*]' COLUMNS(
top_ord FOR ORDINALITY,
c1path VARCHAR(10) PATH '$.c1' ERROR ON ERROR,
NESTED PATH '$.c2[*]' COLUMNS (
c2path VARCHAR(10) PATH '$.c' ERROR ON ERROR,
ord FOR ORDINALITY,
NESTED PATH '$.l[*]' COLUMNS (lpath_c VARCHAR(10) PATH '$' ERROR ON ERROR,
lpath_i INT PATH '$' ERROR ON ERROR)
),
c3path INT PATH '$.c3' ERROR ON ERROR
)
) as jt;
top_ord c1path c2path ord lpath_c lpath_i c3path
1 NULL c_val 1 1 1 NULL
1 NULL c_val 1 2 2 NULL
2 true c_val 1 11 11 1
2 true c_val 2 false 0 1
3 false NULL 1 true 1 0
3 false c_val 2 NULL NULL 0
#
# Bug #31327187 UBSAN: JSON_TABLE: NULL POINTER PASSED AS ARGUMENT 2,
# WHICH IS DECLARED TO NEVER BE NULL
#
SELECT HEX(a) FROM JSON_TABLE(
'[{"E":{"e":true,"~":1,"S3":"sTa"},"r":3,"":6.7},"",6.5]',
'$'
COLUMNS(a BINARY(5) PATH '$[1]' NULL ON EMPTY)
) e;
HEX(a)
0000000000
References
