Hello Igor, A followup to yesterday's discussion: == Short == MySQL-8 does store JSON_TABLE's rows into a temporary table. It doesn't seem to allow any "caching", neither when JSON_TABLE's argument depends on some table, nor when it is independent. == Long == First, construct the example you've described: create table t1 (a int, js json); insert into t1 values (1, '[1,2,3,4,5,6,7,8,9,10]'); insert into t1 select * from t1; insert into t1 select * from t1; select count(*) from t1; 4 create table t2 (a int, key(a)); insert into t2 values (0),(0),(0),(1),(1),(1),(2),(2),(2); analyze table t2; explain select * from t1 join t2 on t2.a=t1.a, json_table(t1.js, '$[*]' COLUMNS(a 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 | 4 | 100.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | j8.t1.a | 1 | 100.00 | Using index | | 1 | SIMPLE | jt | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Table function: json_table; Using temporary | +----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+---------------------------------------------+ Running the SELECT produces 120 rows in output ( 4 rows in table t1, t2 has 3 matching rows, JSON_TABLE produces 10 matches) In debugger one can see that: - Table_function::empty_table() is called 12 times. - Table_function::write_row() is called 120 times. There's no caching. == Independent table == set optimizer_switch='hash_join=off,block_nested_loop=off'; explain select * from t1, json_table('[1,2,3,4,5,6,7,8,9,10]', '$[*]' COLUMNS(a 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 | 4 | 100.00 | NULL | | 1 | SIMPLE | jt | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Table function: json_table; Using temporary; Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------+ Running the select, I get 40 output rows. I see 4 calls to Table_function::empty_table and 40 calls to Table_function::write_row(). BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net