Ok, fixed that as you said. See the last commit to the bb-10.6-mdev17399-hf for details
Best regards. HF On Tue, Feb 23, 2021 at 3:57 PM Sergey Petrunia <ser...@mariadb.com> wrote: > On Mon, Feb 22, 2021 at 09:59:54PM +0300, Sergey Petrunia wrote: > > == On table dependencies: table elimination == > > > > create table t20 (a int not null); > > create table t21 (a int not null primary key, js varchar(100)); > > > > insert into t20 values (1),(2); > > insert into t21 values (1, '{"a":100}'); > > > > explain > > select > > t20.a, jt1.ab > > from > > t20 > > left join t21 on t20.a=t21.a > > join JSON_TABLE(t21.js,'$' COLUMNS (ab INT PATH '$.a')) AS jt1; > > > > > +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+ > > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > > > +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+ > > | 1 | SIMPLE | t20 | ALL | NULL | NULL | NULL | > NULL | 2 | | > > | 1 | SIMPLE | jt1 | ALL | NULL | NULL | NULL | > NULL | 40 | Table function: json_table | > > > +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+ > > > > Here we can see an apparently invalid query plan: table t21 was > eleminated > > even if JSON_TABLE uses it. > > We can do with a simple rule: "do not eliminate tables that are used as > argument for any table function". > > I think this should be achieved as follows: in eliminate_tables(), look > at the code that collects a bitmap of tables used in the select list (to > prevent them from being eliminated). > > /* Add tables referred to from the select list */ > List_iterator<Item> it(join->fields_list); > while ((item= it++)) > used_tables |= item->used_tables(); > > Right below this, add a loop which does the same for table functions. > > > BR > Sergei > -- > Sergei Petrunia, Software Developer > MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net > > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp