Re: [Maria-developers] MDEV-17399: JSON_TABLE, commit 85757ecbef44484270e385a8d52998c67f72d11a
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 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 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
Re: [Maria-developers] MDEV-17399: JSON_TABLE, commit 85757ecbef44484270e385a8d52998c67f72d11a
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 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
Re: [Maria-developers] MDEV-17399: JSON_TABLE, commit 85757ecbef44484270e385a8d52998c67f72d11a
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 ___ 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