On Mon, Feb 22, 2021 at 09:59:54PM +0300, Sergey Petrunia wrote:
== On table dependencies: regular subqueries ==
But it doesn't work for regular subqueries either (and this is unexpected):
insert into t21 values (3, '{"a":300}');
explain select a, (select jt1.ab from JSON_TABLE(t21.js,'$' COLUMNS (ab INT PATH '$.a')) as jt1 ) from t21;
+------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+ | 1 | PRIMARY | t21 | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | SUBQUERY | jt1 | ALL | NULL | NULL | NULL | NULL | 40 | Table function: json_table | +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
Note the 'SUBQUERY', that is, the subquery is considered uncorrelated? This needs to be investigated.
Ok, I see that the subquery is first marked as correlated but then marked as non-correlated here: #0 st_select_lex::update_correlated_cache #1 st_select_lex::optimize_unflattened_subqueries #2 JOIN::optimize_unflattened_subqueries #3 JOIN::optimize_stage2 #4 JOIN::optimize_inner Apparently, st_select_lex::update_correlated_cache doesn't yet account for Table Functions. Please fix. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net