maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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