summaryrefslogtreecommitdiff
path: root/mysql-test/main/json_table_mysql.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/json_table_mysql.test')
-rw-r--r--mysql-test/main/json_table_mysql.test1538
1 files changed, 1538 insertions, 0 deletions
diff --git a/mysql-test/main/json_table_mysql.test b/mysql-test/main/json_table_mysql.test
new file mode 100644
index 00000000000..7f01709b310
--- /dev/null
+++ b/mysql-test/main/json_table_mysql.test
@@ -0,0 +1,1538 @@
+# 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;
View Lorry Controller Status