maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Alexey,

I have discovered another issue: 

JSON_TABLE's dependencies are not updated after derived merge. 

An example of failing query can be found below.

The problem scenario is:

* Table_function_json_table::setup() is called. It computes dep_tables.

* The derived table is merged, mysql_derived_merge() is called.
The tables are moved into the parent select and their table_map bits change.

* SELECT_LEX::update_used_tables() is called to update the attributes after 
the change. This does nothing to the JSON_TABLE, though.

* Execution proceeds with incorrect bitmap.

I see that for other tables, TABLE_LIST::dep_tables is computed in
simplify_joins(). simplify_joins() can remove table dependencies if it
converts an outer join into inner.
Can it somehow remove a dependency of JSON_TABLE(...) on its argument?
I haven't been able to construct such case.
(An obvious reason one cannot easily do this is that JSON_TABLE() doesn't
allow to declare NOT NULL columns. I'm not sure if this a fundamental 
limitation or something random).
I think the very least that should be done is to make a comment about 
this in simplify_joins().

=== Testcase ==

create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 (js json, b int);

insert into t2 
select '[1,2,3]',A.a from t1 A, t1 B;

explain
select * 
from
  t1,
  (select * 
   from 
     t2,
     json_table(t2.js, '$[*]' columns (o for ordinality)) as jt
   ) as TT2
where 
  1;

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 10   |                                    |
|    1 | SIMPLE      | jt    | ALL  | NULL          | NULL | NULL    | NULL | 40   | Table function: json_table         |
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 100  | Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+

Note the incorrect join order: jt has t2.js as a parameter but it precedes t2.

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net