- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] f522fb9: MDEV-16934 Query with very large IN clause lists runs slowly
by Sergey Petrunia 17 Aug '18

17 Aug '18

Hello Igor, I am debugging with the patch and I see that PARAM::range_count is not reset after the eq_ranges_exceeds_limit() call. This causes different kinds of undesired effects. For example, here create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (pk int, kp1 int, kp2 int, kp3 int, col1 int, key(kp1,kp2,kp3), primary key(pk)); insert into t1 select A.a + 10*B.a+100* C.a, A.a, B.a , C.a, 1234 from ten A, ten B, ten C; alter table t1 add key3 int, add key2 int; update t1 set key3=kp1; update t1 set key2=kp1; alter table t1 add key(key2), add key(key3); set eq_range_index_dive_limit=10; explain format=json select 1 from t1 force index (key2, key3) where key3=1 or key2=1; index_merge will use Sort-Union instead of Union. I assume we are targeting only IN (...) cases? Both our and MySQL's fixes will still do many records_in_range calls for queries like keypart1 IN (1,2,3,...) AND keypart2 IN (1,2,3,...) AND keypart3 >= 10 (because here the ranges will not be equality ranges). I assume this is outside of the scope of this fix? If so, then the patch is ok to push after PARAM::range_count issue is resolved. On Wed, Aug 15, 2018 at 07:48:30PM -0700, IgorBabaev wrote: > revision-id: f522fb9f63f88195bccbbc0326a4a318cc2d3db0 (mariadb-10.2.16-50-gf522fb9) > parent(s): 3c141e319ab29afdfe843553da385fe5d981906e > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-08-15 19:48:30 -0700 > message: > > MDEV-16934 Query with very large IN clause lists runs slowly > > This patch introduces support for the system variable eq_range_index_dive_limit > that existed in MySQL starting from 5.6. The variable sets a limit for > index dives into equality ranges. Index dives are performed by optimizer > to estimate the number of rows in range scans. Index dives usually provide > good estimate but they are pretty expensive. To estimate the number of rows > in equality ranges statistical data on indexes can be employed. Its usage gives > not so good estimates but it's cheap. So if the number of equality dives > required by an index scan exceeds the set limit no dives for equality > ranges are performed by the optimizer for this index. > > As the new system variable is introduced in a stable version the default > value for it is set to a special value meaning there is no limit for the number > of index dives performed by the optimizer. > > The patch partially uses the MySQL code for WL 5957 > 'Statistics-based Range optimization for many ranges'. > > --- > mysql-test/r/mysqld--help.result | 6 +++ > mysql-test/r/range.result | 41 +++++++++++++++++ > mysql-test/r/range_mrr_icp.result | 41 +++++++++++++++++ > .../sys_vars/r/sysvars_server_embedded.result | 14 ++++++ > .../sys_vars/r/sysvars_server_notembedded.result | 14 ++++++ > mysql-test/t/range.test | 33 ++++++++++++++ > sql/multi_range_read.cc | 11 +++++ > sql/opt_range.cc | 27 +++++++++++ > sql/opt_range.h | 4 +- > sql/opt_range_mrr.cc | 53 +++++++++++++++------- > sql/sql_class.h | 1 + > sql/sql_statistics.h | 2 +- > sql/sys_vars.cc | 10 ++++ > 13 files changed, 238 insertions(+), 19 deletions(-) > > diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result > index 3b7d5e2..51ece33 100644 > --- a/mysql-test/r/mysqld--help.result > +++ b/mysql-test/r/mysqld--help.result > @@ -197,6 +197,11 @@ The following specify which files/extra groups are read (specified before remain > cache, etc) > --enforce-storage-engine=name > Force the use of a storage engine for new tables > + --eq-range-index-dive-limit=# > + The optimizer will use existing index statistics instead > + of doing index dives for equality ranges if the number of > + equality ranges for the index is larger than or equal to > + this number. If set to 0, index dives are always used. > --event-scheduler[=name] > Enable the event scheduler. Possible values are ON, OFF, > and DISABLED (keep the event scheduler completely > @@ -1259,6 +1264,7 @@ encrypt-binlog FALSE > encrypt-tmp-disk-tables FALSE > encrypt-tmp-files FALSE > enforce-storage-engine (No default value) > +eq-range-index-dive-limit 0 > event-scheduler OFF > expensive-subquery-limit 100 > expire-logs-days 0 > diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result > index 3a71d08..0c6be5e 100644 > --- a/mysql-test/r/range.result > +++ b/mysql-test/r/range.result > @@ -3002,5 +3002,46 @@ deallocate prepare stmt; > set optimizer_switch=@save_optimizer_switch; > drop table t1,t2,t3; > # > +# MDEV-16934: using system variable eq_range_index_dive_limit > +# to reduce the number of index dives > +# > +create table t1 (a int, b varchar(31), index idx(a)); > +insert into t1 values > +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), > +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); > +insert into t1 select a+10, concat(b,'zz') from t1; > +insert into t1 select a+15, concat(b,'yy') from t1; > +insert into t1 select a+100, concat(b,'xx') from t1; > +analyze table t1; > +Table Op Msg_type Msg_text > +test.t1 analyze status OK > +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; > +rec_per_key > +2 > +set eq_range_index_dive_limit=0; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +1 cc > +9 zzzzz > +15 ffffzz > +set eq_range_index_dive_limit=2; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +1 cc > +9 zzzzz > +15 ffffzz > +set eq_range_index_dive_limit=default; > +drop table t1; > +# > # End of 10.2 tests > # > diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result > index 799a299..93f414f 100644 > --- a/mysql-test/r/range_mrr_icp.result > +++ b/mysql-test/r/range_mrr_icp.result > @@ -3014,6 +3014,47 @@ deallocate prepare stmt; > set optimizer_switch=@save_optimizer_switch; > drop table t1,t2,t3; > # > +# MDEV-16934: using system variable eq_range_index_dive_limit > +# to reduce the number of index dives > +# > +create table t1 (a int, b varchar(31), index idx(a)); > +insert into t1 values > +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), > +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); > +insert into t1 select a+10, concat(b,'zz') from t1; > +insert into t1 select a+15, concat(b,'yy') from t1; > +insert into t1 select a+100, concat(b,'xx') from t1; > +analyze table t1; > +Table Op Msg_type Msg_text > +test.t1 analyze status OK > +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; > +rec_per_key > +2 > +set eq_range_index_dive_limit=0; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition; Rowid-ordered scan > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +9 zzzzz > +1 cc > +15 ffffzz > +set eq_range_index_dive_limit=2; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition; Rowid-ordered scan > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +9 zzzzz > +1 cc > +15 ffffzz > +set eq_range_index_dive_limit=default; > +drop table t1; > +# > # End of 10.2 tests > # > set optimizer_switch=@mrr_icp_extra_tmp; > diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result > index f7a5cf5..9d7fa10 100644 > --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result > +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result > @@ -765,6 +765,20 @@ NUMERIC_BLOCK_SIZE NULL > ENUM_VALUE_LIST NULL > READ_ONLY NO > COMMAND_LINE_ARGUMENT NULL > +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT > +SESSION_VALUE 0 > +GLOBAL_VALUE 0 > +GLOBAL_VALUE_ORIGIN COMPILE-TIME > +DEFAULT_VALUE 0 > +VARIABLE_SCOPE SESSION > +VARIABLE_TYPE INT UNSIGNED > +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. > +NUMERIC_MIN_VALUE 0 > +NUMERIC_MAX_VALUE 4294967295 > +NUMERIC_BLOCK_SIZE 1 > +ENUM_VALUE_LIST NULL > +READ_ONLY NO > +COMMAND_LINE_ARGUMENT REQUIRED > VARIABLE_NAME ERROR_COUNT > SESSION_VALUE 0 > GLOBAL_VALUE NULL > diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result > index 034195e..cada139 100644 > --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result > +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result > @@ -779,6 +779,20 @@ NUMERIC_BLOCK_SIZE NULL > ENUM_VALUE_LIST NULL > READ_ONLY NO > COMMAND_LINE_ARGUMENT NULL > +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT > +SESSION_VALUE 0 > +GLOBAL_VALUE 0 > +GLOBAL_VALUE_ORIGIN COMPILE-TIME > +DEFAULT_VALUE 0 > +VARIABLE_SCOPE SESSION > +VARIABLE_TYPE INT UNSIGNED > +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. > +NUMERIC_MIN_VALUE 0 > +NUMERIC_MAX_VALUE 4294967295 > +NUMERIC_BLOCK_SIZE 1 > +ENUM_VALUE_LIST NULL > +READ_ONLY NO > +COMMAND_LINE_ARGUMENT REQUIRED > VARIABLE_NAME ERROR_COUNT > SESSION_VALUE 0 > GLOBAL_VALUE NULL > diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test > index ab95180..ed68cfb 100644 > --- a/mysql-test/t/range.test > +++ b/mysql-test/t/range.test > @@ -2046,6 +2046,39 @@ set optimizer_switch=@save_optimizer_switch; > drop table t1,t2,t3; > > --echo # > +--echo # MDEV-16934: using system variable eq_range_index_dive_limit > +--echo # to reduce the number of index dives > +--echo # > + > +create table t1 (a int, b varchar(31), index idx(a)); > + > +insert into t1 values > + (7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), > + (4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); > +insert into t1 select a+10, concat(b,'zz') from t1; > +insert into t1 select a+15, concat(b,'yy') from t1; > +insert into t1 select a+100, concat(b,'xx') from t1; > + > +analyze table t1; > + > +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; > + > +let $q= > +select * from t1 where a in (8, 15, 31, 1, 9); > + > +set eq_range_index_dive_limit=0; > +eval explain $q; > +eval $q; > + > +set eq_range_index_dive_limit=2; > +eval explain $q; > +eval $q; > + > +set eq_range_index_dive_limit=default; > + > +drop table t1; > + > +--echo # > --echo # End of 10.2 tests > --echo # > > diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc > index 50918d8..94a96c2 100644 > --- a/sql/multi_range_read.cc > +++ b/sql/multi_range_read.cc > @@ -17,6 +17,7 @@ > #include <my_bit.h> > #include "sql_select.h" > #include "key.h" > +#include "sql_statistics.h" > > /**************************************************************************** > * Default MRR implementation (MRR to non-MRR converter) > @@ -67,6 +68,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, > /* Default MRR implementation doesn't need buffer */ > *bufsz= 0; > > + bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, > + seq_init_param); > + > seq_it= seq->init(seq_init_param, n_ranges, *flags); > while (!seq->next(seq_it, &range)) > { > @@ -87,8 +91,15 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, > min_endp= range.start_key.length? &range.start_key : NULL; > max_endp= range.end_key.length? &range.end_key : NULL; > } > + int keyparts_used= my_count_bits(range.start_key.keypart_map); > if ((range.range_flag & UNIQUE_RANGE) && !(range.range_flag & NULL_RANGE)) > rows= 1; /* there can be at most one row */ > + else if (use_statistics_for_eq_range && > + !(range.range_flag & NULL_RANGE) && > + (range.range_flag & EQ_RANGE) && > + table->key_info[keyno].actual_rec_per_key(keyparts_used - 1) > 0.5) > + rows= > + (ha_rows) table->key_info[keyno].actual_rec_per_key(keyparts_used - 1); > else > { > if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, > diff --git a/sql/opt_range.cc b/sql/opt_range.cc > index b011c01..c9c37a2 100644 > --- a/sql/opt_range.cc > +++ b/sql/opt_range.cc > @@ -14616,6 +14616,33 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, > } > > > +/* Check whether the number for equality ranges exceeds the set threshold */ > + > +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param) > +{ > + KEY_MULTI_RANGE range; > + range_seq_t seq_it; > + uint count = 0; > + PARAM *param= ((SEL_ARG_RANGE_SEQ*) seq_init_param)->param; > + uint limit= param->thd->variables.eq_range_index_dive_limit; > + > + if (limit == 0) > + { > + /* 'Statistics instead of index dives' feature is turned off */ > + return false; > + } > + seq_it= seq->init(seq_init_param, 0, 0); > + while (!seq->next(seq_it, &range)) > + { > + if ((range.range_flag & EQ_RANGE) && !(range.range_flag & NULL_RANGE)) > + { > + if (++count >= limit) > + return true; > + } > + } > + return false; > +} > + > #ifndef DBUG_OFF > > static void print_sel_tree(PARAM *param, SEL_TREE *tree, key_map *tree_map, > diff --git a/sql/opt_range.h b/sql/opt_range.h > index c1f7079..6698c98 100644 > --- a/sql/opt_range.h > +++ b/sql/opt_range.h > @@ -242,7 +242,7 @@ class SEL_ARG :public Sql_alloc > Number of children of this element in the RB-tree, plus 1 for this > element itself. > */ > - uint16 elements; > + uint32 elements; > /* > Valid only for elements which are RB-tree roots: Number of times this > RB-tree is referred to (it is referred by SEL_ARG::next_key_part or by > @@ -1664,6 +1664,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, > > bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); > > +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); > + > #ifdef WITH_PARTITION_STORAGE_ENGINE > bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); > #endif > diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc > index ace6208..7f80a3e 100644 > --- a/sql/opt_range_mrr.cc > +++ b/sql/opt_range_mrr.cc > @@ -272,25 +272,44 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) > key_info= NULL; > else > key_info= &seq->param->table->key_info[seq->real_keyno]; > - > + > /* > - Conditions below: > - (1) - range analysis is used for estimating condition selectivity > - (2) - This is a unique key, and we have conditions for all its > - user-defined key parts. > - (3) - The table uses extended keys, this key covers all components, > - and we have conditions for all key parts. > + This is an equality range (keypart_0=X and ... and keypart_n=Z) if > + (1) - There are no flags indicating open range (e.g., > + "keypart_x > y") or GIS. > + (2) - The lower bound and the upper bound of the range has the > + same value (min_key == max_key). > */ > - if (!(cur->min_key_flag & ~NULL_RANGE) && !cur->max_key_flag && > - (!key_info || // (1) > - ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) > - key_info->flags & HA_NOSAME) || // (2) > - ((key_info->flags & HA_EXT_NOSAME) && // (3) > - (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) > - ) && > - range->start_key.length == range->end_key.length && > - !memcmp(seq->param->min_key,seq->param->max_key,range->start_key.length)) > - range->range_flag= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); > + const uint is_open_range = > + (NO_MIN_RANGE | NO_MAX_RANGE | NEAR_MIN | NEAR_MAX | GEOM_FLAG); > + const bool is_eq_range_pred = > + !(cur->min_key_flag & is_open_range) && // (1) > + !(cur->max_key_flag & is_open_range) && // (1) > + range->start_key.length == range->end_key.length && // (2) > + !memcmp(seq->param->min_key, seq->param->max_key, // (2) > + range->start_key.length); > + > + if (is_eq_range_pred) > + { > + range->range_flag = EQ_RANGE; > + > + /* > + Conditions below: > + (1) - Range analysis is used for estimating condition selectivity > + (2) - This is a unique key, and we have conditions for all its > + user-defined key parts. > + (3) - The table uses extended keys, this key covers all components, > + and we have conditions for all key parts. > + */ > + if ( > + !key_info || // (1) > + ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) > + key_info->flags & HA_NOSAME) || // (2) > + ((key_info->flags & HA_EXT_NOSAME) && // (3) > + (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) > + ) > + range->range_flag |= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); > + } > > if (seq->param->is_ror_scan) > { > diff --git a/sql/sql_class.h b/sql/sql_class.h > index 6663d9e..d2f00b2d 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -569,6 +569,7 @@ typedef struct system_variables > ha_rows max_join_size; > ha_rows expensive_subquery_limit; > ulong auto_increment_increment, auto_increment_offset; > + uint eq_range_index_dive_limit; > ulong lock_wait_timeout; > ulong join_cache_level; > ulong max_allowed_packet; > diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h > index f465838..4ff85cd 100644 > --- a/sql/sql_statistics.h > +++ b/sql/sql_statistics.h > @@ -21,7 +21,7 @@ enum enum_use_stat_tables_mode > { > NEVER, > COMPLEMENTARY, > - PEFERABLY, > + PREFERABLY, > } Use_stat_tables_mode; > > typedef > diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc > index e6aec47..650b17a 100644 > --- a/sql/sys_vars.cc > +++ b/sql/sys_vars.cc > @@ -2578,6 +2578,16 @@ static Sys_var_ulong Sys_div_precincrement( > SESSION_VAR(div_precincrement), CMD_LINE(REQUIRED_ARG), > VALID_RANGE(0, DECIMAL_MAX_SCALE), DEFAULT(4), BLOCK_SIZE(1)); > > +static Sys_var_uint Sys_eq_range_index_dive_limit( > + "eq_range_index_dive_limit", > + "The optimizer will use existing index statistics instead of " > + "doing index dives for equality ranges if the number of equality " > + "ranges for the index is larger than or equal to this number. " > + "If set to 0, index dives are always used.", > + SESSION_VAR(eq_range_index_dive_limit), CMD_LINE(REQUIRED_ARG), > + VALID_RANGE(0, UINT_MAX32), DEFAULT(0), > + BLOCK_SIZE(1)); > + > static Sys_var_ulong Sys_range_alloc_block_size( > "range_alloc_block_size", > "Allocation block size for storing ranges during optimization", > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] adaafcf5184: MDEV-16930: Crash when VALUES in derived table contains expressions
by Sergei Golubchik 16 Aug '18

16 Aug '18

Hi, Oleksandr! On Aug 16, Oleksandr Byelkin wrote: > revision-id: adaafcf5184375369c0c4b3ccb1e82e846024ef8 (mariadb-10.3.7-137-gadaafcf5184) > parent(s): 8716bb3b72b7f5fed69b6dde413c2138f6d175b2 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2018-08-16 13:55:36 +0200 > message: > > MDEV-16930: Crash when VALUES in derived table contains expressions > > Give names to the value constructor columns as in SELECT-list. > > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index f915895a260..20cea4959eb 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -13306,15 +13306,20 @@ opt_values: > ; > > values: > - values ',' expr_or_default > + values ',' remember_name expr_or_default remember_end > { > - if (unlikely(Lex->insert_list->push_back($3, thd->mem_root))) > + if (unlikely(Lex->insert_list->push_back($4, thd->mem_root))) > MYSQL_YYABORT; > + // give some name in case of using in table value constuctor (TVC) > + $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); > + > } > - | expr_or_default > + | remember_name expr_or_default remember_end > { > - if (unlikely(Lex->insert_list->push_back($1, thd->mem_root))) > + if (unlikely(Lex->insert_list->push_back($2, thd->mem_root))) > MYSQL_YYABORT; > + // give some name in case of using in table value constuctor (TVC) > + $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); > } > ; Could you please create a special rule for that? Like named_value: remember_name expr_or_default remember_end { if (unlikely(Lex->insert_list->push_back($2, thd->mem_root))) MYSQL_YYABORT; // give some name in case of using in table value constuctor (TVC) $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); } values: values ',' named_value | named_value ; if it'll work, that is Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] 7fc57734575: MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset
by Oleksandr Byelkin 15 Aug '18

15 Aug '18

Hi! See small comment inline. (fix and OK to push) Am 15.08.2018 um 13:36 schrieb Sergey Petrunia: > MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset > > Item_subselect::is_expensive() used to return FALSE (Inexpensive) whenever > it saw that one of SELECTs in the Subquery's UNION is degenerate. It > ignored the fact that other parts of the UNION might not be inexpensive, > including the case where pther parts of the UNION have no query plan yet. > > For a subquery in form col >= ANY (SELECT 'foo' UNION SELECT 'bar') > this would cause the query to be considered inexpensive when there is > no query plan for the second part of the UNION, which in turn would > cause the SELECT 'foo' to compute and free itself while still inside > JOIN::optimize for that SELECT (See MDEV comment for full description). > > diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result > index 79bca38..200682b 100644 > --- a/mysql-test/r/subselect_extra_no_semijoin.result > +++ b/mysql-test/r/subselect_extra_no_semijoin.result > @@ -482,3 +482,22 @@ DROP TABLE t1,t2; > set optimizer_switch= @tmp_subselect_extra_derived; > set optimizer_switch= @subselect_extra_no_sj_tmp; > set @optimizer_switch_for_subselect_extra_test=null; > +# > +# MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset > +# > +SET NAMES tis620; > +set @tmp= @@global.slow_query_log; > +SET GLOBAL slow_query_log = 1; > +SET long_query_time = 0.000001; > +SET log_slow_verbosity = 'explain'; > +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); > +a > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); > +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' > +create table t2 (b int); > +insert into t2 values (1),(2),(3); > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); > +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' > +drop table t1,t2; > +SET GLOBAL slow_query_log=@tmp; > diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test > index 8aba3dd..41593fb 100644 > --- a/mysql-test/t/subselect_extra_no_semijoin.test > +++ b/mysql-test/t/subselect_extra_no_semijoin.test > @@ -6,4 +6,34 @@ set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loo > > set optimizer_switch= @subselect_extra_no_sj_tmp; > > -set @optimizer_switch_for_subselect_extra_test=null; > \ No newline at end of file > +set @optimizer_switch_for_subselect_extra_test=null; > + > +--echo # > +--echo # MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset > +--echo # > + > +## Using a separate client connection is easier than restoring state > +connect(con1,localhost,root,,); > + > +SET NAMES tis620; > +set @tmp= @@global.slow_query_log; > +SET GLOBAL slow_query_log = 1; > +SET long_query_time = 0.000001; > +SET log_slow_verbosity = 'explain'; > + please remove above space at the end of line and push. > +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); > +--error ER_CANT_AGGREGATE_2COLLATIONS > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); > + > +create table t2 (b int); > +insert into t2 values (1),(2),(3); > + > +--error ER_CANT_AGGREGATE_2COLLATIONS > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); > + > +drop table t1,t2; > +SET GLOBAL slow_query_log=@tmp; > +disconnect con1; > +connection default; > + > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index a8dfdff..14b2ccd 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -575,7 +575,7 @@ bool Item_subselect::is_expensive() > */ > if (cur_join->optimized && > (cur_join->zero_result_cause || !cur_join->tables_list)) > - return false; > + continue; > > /* > If a subquery is not optimized we cannot estimate its cost. A subquery is

1 0

[Maria-developers] MDEV-16768 and MDEV-16703 patch review
by Ming Lin 10 Aug '18

10 Aug '18

Hi Sergey, Could you help to review below 2 patches please? MDEV-16768: https://github.com/MariaDB/server/pull/837 MDEV-16703: https://github.com/MariaDB/server/pull/839 Thanks, Ming

1 0

Re: [Maria-developers] 6df4e4a855f: MDEV-16217: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_num::get_date
by Sergei Golubchik 09 Aug '18

09 Aug '18

Hi, Oleksandr! See questions below. There are few changes that I'm not quite sure about. On Aug 08, Oleksandr Byelkin wrote: > revision-id: 6df4e4a855fe223988c12681f8caec6c49b2f629 (mariadb-10.2.16-66-g6df4e4a855f) > parent(s): 4ddcb4eb46c62cf459936554d43351db740ba14d > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2018-08-08 12:28:15 +0200 > message: > > MDEV-16217: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_num::get_date > > - clean up DEFAULT() to work only with default value and correctly print > itself. > - fix of DBUG_ASSERT about fields read/write > - fix of firld marking for write based really on the thd->mark_used_columns flag > > --- > mysql-test/r/func_default.result | 2 +- > mysql-test/r/func_time.result | 28 ++++++++++++++++++++++++++++ > mysql-test/t/func_time.test | 21 +++++++++++++++++++++ > sql/field.cc | 21 +++++++++++++++++++-- > sql/field.h | 1 + > sql/item.cc | 25 ++++++++++++++++++++++++- > sql/item.h | 5 +++++ > sql/sql_base.cc | 2 +- > 8 files changed, 100 insertions(+), 5 deletions(-) > > diff --git a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result > index 535be10da86..1f331af287c 100644 > --- a/mysql-test/r/func_default.result > +++ b/mysql-test/r/func_default.result > @@ -8,7 +8,7 @@ explain extended select default(str), default(strnull), default(intg), default(r > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 > Warnings: > -Note 1003 select default('') AS `default(str)`,default('') AS `default(strnull)`,default(0) AS `default(intg)`,default(0) AS `default(rel)` from dual > +Note 1003 select default(`str`) AS `default(str)`,default(`strnull`) AS `default(strnull)`,default(`intg`) AS `default(intg)`,default(`rel`) AS `default(rel)` from dual This looks rather fishy. There can be no columns str and strnull in dual. Please add a test case with a view, to make sure you only get this effect (column names and dual) in explain extended (where it doesn't matter), but not in a view (where it does). > select * from t1 where str <> default(str); > str strnull intg rel > 0 0 > diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result > index 55c2c93eb56..51f51820efc 100644 > --- a/mysql-test/r/func_time.result > +++ b/mysql-test/r/func_time.result > @@ -3260,3 +3260,31 @@ DROP TABLE t1,t2; > # > # End of 10.1 tests > # > +# > +# MDEV-16217: Assertion `!table || (!table->read_set || > +# bitmap_is_set(table->read_set, field_index))' > +# failed in Field_num::get_date > +# > +CREATE TABLE t1 (pk int default 0, a1 date); > +INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL); > +CREATE VIEW v1 AS > +SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1; > +SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1; > +a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) > +0 > +NULL > +NULL > +NULL > +SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1; > +a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) > +0 > +NULL > +NULL > +NULL > +Warnings: > +Warning 1292 Incorrect datetime value: '18446744073709551615' > +DROP VIEW v1; > +DROP TABLE t1; > +# > +# End of 10.2 tests > +# > diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test > index 3503b6d5bc6..c8859feee93 100644 > --- a/mysql-test/t/func_time.test > +++ b/mysql-test/t/func_time.test > @@ -1857,3 +1857,24 @@ DROP TABLE t1,t2; > --echo # > --echo # End of 10.1 tests > --echo # > + > +--echo # > +--echo # MDEV-16217: Assertion `!table || (!table->read_set || > +--echo # bitmap_is_set(table->read_set, field_index))' > +--echo # failed in Field_num::get_date > +--echo # > +CREATE TABLE t1 (pk int default 0, a1 date); > +INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL); > + > +CREATE VIEW v1 AS > +SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1; > + > +SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1; > +SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1; > + > +DROP VIEW v1; > +DROP TABLE t1; > + > +--echo # > +--echo # End of 10.2 tests > +--echo # > diff --git a/sql/field.cc b/sql/field.cc > index 9ca9663f066..e12adbf47b3 100644 > --- a/sql/field.cc > +++ b/sql/field.cc > @@ -70,8 +70,25 @@ const char field_separator=','; > #define BLOB_PACK_LENGTH_TO_MAX_LENGH(arg) \ > ((ulong) ((1LL << MY_MIN(arg, 4) * 8) - 1)) > > -#define ASSERT_COLUMN_MARKED_FOR_READ DBUG_ASSERT(!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))) > -#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(is_stat_field || !table || (!table->write_set || bitmap_is_set(table->write_set, field_index) || (table->vcol_set && bitmap_is_set(table->vcol_set, field_index)))) > +// Column marked for read or the field set to read out or record[0] or [1] > +#define ASSERT_COLUMN_MARKED_FOR_READ \ > + DBUG_ASSERT(!table || \ > + (!table->read_set || \ > + bitmap_is_set(table->read_set, field_index) || \ > + (!(ptr >= table->record[0] && \ > + ptr < table->record[0] + table->s->reclength) && \ > + !(ptr >= table->record[1] && \ > + ptr < table->record[1] + table->s->reclength)))) why did you add record[1] to the assert? I think, having just record[0] would've been enough > + > +#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED \ > + DBUG_ASSERT(is_stat_field || !table || \ > + (!table->write_set || \ > + bitmap_is_set(table->write_set, field_index) || \ > + (!(ptr >= table->record[0] && \ > + ptr < table->record[0] + table->s->reclength) && \ > + !(ptr >= table->record[1] && \ > + ptr < table->record[1] + table->s->reclength))) || \ > + (table->vcol_set && bitmap_is_set(table->vcol_set, field_index))) > > #define FLAGSTR(S,F) ((S) & (F) ? #F " " : "") > > diff --git a/sql/field.h b/sql/field.h > index 22c276478b6..55c3ed4c4bd 100644 > --- a/sql/field.h > +++ b/sql/field.h > @@ -630,6 +630,7 @@ class Virtual_column_info: public Sql_alloc > bool utf8; /* Already in utf8 */ > Item *expr; > LEX_STRING name; /* Name of constraint */ > + /* see VCOL_* (VCOL_FIELD_REF, ...) */ > uint flags; > > Virtual_column_info() > diff --git a/sql/item.cc b/sql/item.cc > index 58d2b7dbfc0..91fe7ff03aa 100644 > --- a/sql/item.cc > +++ b/sql/item.cc > @@ -8917,8 +8917,19 @@ bool Item_default_value::fix_fields(THD *thd, Item **items) > fixed= 1; > return FALSE; > } > + > + /* > + DEFAULT() do not need table field so should not ask handler to bring > + field value (mark column for read) > + */ > + enum_mark_columns save_mark_used_columns= thd->mark_used_columns; > + thd->mark_used_columns= MARK_COLUMNS_NONE; > if (!arg->fixed && arg->fix_fields(thd, &arg)) > + { > + thd->mark_used_columns= save_mark_used_columns; > goto error; > + } > + thd->mark_used_columns= save_mark_used_columns; Hmm, I thought that the field wasn't marked in read_set, and that failed the assert. Now you're saying that it was? > real_arg= arg->real_item(); > @@ -8938,12 +8949,16 @@ bool Item_default_value::fix_fields(THD *thd, Item **items) > goto error; > memcpy((void *)def_field, (void *)field_arg->field, > field_arg->field->size_of()); > - IF_DBUG(def_field->is_stat_field=1,); // a hack to fool ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED ah, good. thanks! > + // If non-constant default value expression > if (def_field->default_value && def_field->default_value->flags) > { > uchar *newptr= (uchar*) thd->alloc(1+def_field->pack_length()); > if (!newptr) > goto error; > + /* > + Even if DEFAULT() do not read tables fields, the default value > + expression can do it. > + */ > fix_session_vcol_expr_for_read(thd, def_field, def_field->default_value); > if (thd->mark_used_columns != MARK_COLUMNS_NONE) > def_field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0); better call update_used_tables() from here. > @@ -8970,6 +8985,14 @@ void Item_default_value::print(String *str, enum_query_type query_type) > return; > } > str->append(STRING_WITH_LEN("default(")); > + /* > + We take DEFAULT from a field so do not need it value in case of const > + tables but its name so we set QT_NO_DATA_EXPANSION (as we print for > + table definition, also we do not need table and database name) > + */ > + query_type= (enum_query_type) (query_type | QT_NO_DATA_EXPANSION | > + QT_ITEM_IDENT_SKIP_DB_NAMES | > + QT_ITEM_IDENT_SKIP_TABLE_NAMES); in a view definition you might need table and database names. For example, a view that joins two tables from different databases, and they both have a column named `a`. And the query uses DEFAULT(db1.t1.a). > arg->print(str, query_type); > str->append(')'); > } > diff --git a/sql/item.h b/sql/item.h > index 8fad8dadf22..d252a256f76 100644 > --- a/sql/item.h > +++ b/sql/item.h > @@ -5316,6 +5316,11 @@ class Item_default_value : public Item_field > return false; > } > table_map used_tables() const; > + virtual void update_used_tables() > + { > + if (field && field->default_value) > + field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0); > + } > Field *get_tmp_table_field() { return 0; } > Item *get_tmp_table_item(THD *thd) { return this; } > Item_field *field_for_view_update() { return 0; } Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 2

[Maria-developers] Spelling errors in MariaDB sources
by Otto Kekäläinen 31 Jul '18

31 Jul '18

Hello! I recall I've made several big patches on MariaDB that fixes spelling errors. Somehow the same spelling errors seem to creep back in every now and then. I notice them while uploading stuff to Debian and using the Debian QA tool "Lintian". I haven't done git blame and looked deeply in it. I suspect it is related to how we merge old branches to new branches and occasionally loose stuff. Would somebody like to look into this? It could be an relatively easy task for some new contributor? $ grep spelling-error lintian-22e620c534.log I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_chk noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_chk thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_chk theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_dump_log noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_dump_log thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_dump_log theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_ftdump noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_ftdump thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_ftdump theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_pack noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_pack thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_pack theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_read_log noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_read_log thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/aria_read_log theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisam_ftdump noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisam_ftdump thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisam_ftdump theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisamchk noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisamchk thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisamchk theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisamlog noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisamlog thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisamlog theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisampack noone no one I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisampack thats that's I: mariadb-server-10.1: spelling-error-in-binary usr/bin/myisampack theres there's I: mariadb-server-10.1: spelling-error-in-binary usr/lib/x86_64-linux-gnu/mariadb18/plugin/ha_federated.so Allows to Allows one to I: mariadb-server-10.1: spelling-error-in-binary usr/lib/x86_64-linux-gnu/mariadb18/plugin/ha_federatedx.so Allows to Allows one to I: mariadb-server-10.1: spelling-error-in-binary usr/lib/x86_64-linux-gnu/mariadb18/plugin/ha_innodb.so allow to allow one to I: libmariadbd18: spelling-error-in-binary usr/lib/x86_64-linux-gnu/libmysqld.so.18 extentions extensions I: libmariadbd18: spelling-error-in-binary usr/lib/x86_64-linux-gnu/libmysqld.so.18 noone no one I: libmariadbd18: spelling-error-in-binary usr/lib/x86_64-linux-gnu/libmysqld.so.18 thats that's I: libmariadbd18: spelling-error-in-binary usr/lib/x86_64-linux-gnu/libmysqld.so.18 theres there's I: libmariadbd18: spelling-error-in-binary usr/lib/x86_64-linux-gnu/libmysqld.so.18 allow to allow one to I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup yuR your I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup occured occurred I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup accessable accessible I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup extentions extensions I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup reseting resetting I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup succeded succeeded I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup settting setting I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup postion position I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup noone no one I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup thats that's I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup theres there's I: mariadb-client-10.1: spelling-error-in-binary usr/bin/mariabackup allow to allow one to I: mariadb-client-10.1: spelling-error-in-manpage usr/share/man/man1/innotop.1.gz Additonal Additional I: mariadb-plugin-tokudb: spelling-error-in-binary usr/bin/tokuftdump dont don't I: mariadb-plugin-tokudb: spelling-error-in-binary usr/lib/x86_64-linux-gnu/mariadb18/plugin/ha_tokudb.so dont don't I: mariadb-client-core-10.1: spelling-error-in-binary usr/bin/mysql_embedded extentions extensions I: mariadb-client-core-10.1: spelling-error-in-binary usr/bin/mysql_embedded noone no one I: mariadb-client-core-10.1: spelling-error-in-binary usr/bin/mysql_embedded thats that's I: mariadb-client-core-10.1: spelling-error-in-binary usr/bin/mysql_embedded theres there's I: mariadb-client-core-10.1: spelling-error-in-binary usr/bin/mysql_embedded allow to allow one to I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld extentions extensions I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld reseting resetting I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld succeded succeeded I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld settting setting I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld postion position I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld noone no one I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld thats that's I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld theres there's I: mariadb-server-core-10.1: spelling-error-in-binary usr/sbin/mysqld allow to allow one to I: mariadb-plugin-connect: spelling-error-in-binary usr/lib/x86_64-linux-gnu/mariadb18/plugin/ha_connect.so Mutiple Multiple I: mariadb-plugin-connect: spelling-error-in-binary usr/lib/x86_64-linux-gnu/mariadb18/plugin/ha_connect.so Openning Opening

1 0

[Maria-developers] Missing man pages
by Otto Kekäläinen 31 Jul '18

31 Jul '18

Hello Ian and others! While working on uploading latest MariaDB 10.1 into Debian I noticed it complains about quite a few missing man pages: $ grep manpage lintian-22e620c534.log W: mariadb-server-10.1: binary-without-manpage usr/bin/wsrep_sst_mariabackup W: mariadb-server-10.1: binary-without-manpage usr/bin/wsrep_sst_rsync_wan I: mariadb-client-10.1: spelling-error-in-manpage usr/share/man/man1/innotop.1.gz Additonal Additional W: mariadb-client-10.1: binary-without-manpage usr/bin/mariabackup W: mariadb-client-10.1: binary-without-manpage usr/bin/mbstream W: mariadb-client-10.1: binary-without-manpage usr/bin/mytop W: mariadb-plugin-tokudb: binary-without-manpage usr/bin/tokuft_logprint W: mariadb-client-core-10.1: binary-without-manpage usr/bin/mysql_embedded Could you Ian check if we have these on some branch already and make sure they and up an all the same branches and releases as the binaries themselves? And those who make new binaries, please at least write a stub for your new binary. It makes downstream work much faster when the man page in the source explains directly what the new binaries are and hints how they should be packaged. - Otto

1 0

[Maria-developers] Please review MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively
by Alexander Barkov 30 Jul '18

30 Jul '18

Hello Sanja, Please review a patch for MDEV-16584. Thanks!

2 1

Re: [Maria-developers] [Commits] 108af298032: MDEV-15454: Nested SELECT IN returns wrong results
by Sergey Petrunia 25 Jul '18

25 Jul '18

On Fri, May 11, 2018 at 03:08:29AM +0530, Varun wrote: > revision-id: 108af298032cb4cea316a08c304c306268a2badf (mariadb-5.5.56-213-g108af298032) > parent(s): 318097bb8f6e12c546b5dcd287416158209dbb39 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-05-11 02:56:33 +0530 > message: > > MDEV-15454: Nested SELECT IN returns wrong results > > In this case we are setting the field Item_func_eq::in_eqaulity_no for the semi-join equalities. > This helps us to remove these equalites as the inner tables are not available during parent select execution > while the outer tables are not available during materialization phase. > We only have it set for the equalites for the fields involved with the IN subquery > and reset it for the equalities which do not belong to the IN subquery. > > For example in case of nested IN subqueries: > > SELECT t1.a FROM t1 WHERE t1.a IN > (SELECT t2.a FROM t2 where t2.b IN > (select t3.b from t3 where t3.c=27 )) > > there are two equalites involving the fields of the IN subquery > > 1) t2.b = t3.b : the field Item_func_eq::in_eqaulity_no is set when we merge the grandchild select into the child select > 2) t1.a = t2.a : the field Item_func_eq::in_eqaulity_no is set when we merge the child select into the parent select > > But when we perform case 2) we should ensure that we reset the equalities in the child's WHERE clause. > > --- > mysql-test/r/subselect_sj_mat.result | 46 +++++++++++++++++++++++++ > mysql-test/t/subselect_sj_mat.test | 49 ++++++++++++++++++++++++++ > sql/opt_subselect.cc | 66 ++++++++++++++++++++++++++++++++++++ > 3 files changed, 161 insertions(+) > ... > diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc > index a7edd64e68b..31213a5cf1d 100644 > --- a/sql/opt_subselect.cc > +++ b/sql/opt_subselect.cc > @@ -1481,6 +1543,7 @@ static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, > TRUE Out of memory error > */ > > + > static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) > { > SELECT_LEX *parent_lex= parent_join->select_lex; > @@ -1713,6 +1776,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) > > if (subq_pred->left_expr->cols() == 1) > { > + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); > /* add left = select_list_element */ > nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr); > /* > @@ -1735,6 +1799,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) > } > else if (subq_pred->left_expr->type() == Item::ROW_ITEM) > { > + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); > /* > disassemple left expression and add > left1 = select_list_element1 and left2 = select_list_element2 ... > @@ -1759,6 +1824,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) > } > else > { > + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); > /* > add row operation > left = (select_list_element1, select_list_element2, ...) So, the code here is if (...) { variant1; } else if (...) { variant2; } else { variant3; } and there is the same call added to all three: > + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); Can you move it out of the "if" and make it one call instead of three? Otherwise, the patch is ok, ok to push after the above is addressed. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] d277c4682e4: MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference
by Sergey Petrunia 24 Jul '18

24 Jul '18

On Sun, Jul 22, 2018 at 03:56:37PM +0530, Varun wrote: > revision-id: d277c4682e4c6bb0e708296e6ae520c149dd78f4 (mariadb-5.5.60-47-gd277c4682e4) > parent(s): 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-07-22 15:56:25 +0530 > message: > > MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference > with join_cache_level>2 > > During muliple equality propagation for a query in which we have an IN subquery, the items in the select list of the > subquery may not be part of the multiple equality because there might be another occurence of the same field in the > where clause of the subquery. > So we keyuse_is_valid_for_access_in_chosen_plan function which expects the items in the select list of the subquery to > be same to the ones in the multiple equality (through these multiple equalities we create keyuse array). > The solution would be that we expect the same field not the same Item because when we have SEMI JOIN MATERIALIZATION SCAN, > we use copy back technique to copies back the materialised table fields to the original fields of the base tables. > > --- > mysql-test/r/subselect_mat.result | 35 +++++++++++++++++++++++++++++++++++ > mysql-test/r/subselect_sj_mat.result | 35 +++++++++++++++++++++++++++++++++++ > mysql-test/t/subselect_sj_mat.test | 25 +++++++++++++++++++++++++ > sql/sql_select.cc | 4 +++- > 4 files changed, 98 insertions(+), 1 deletion(-) ... > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 6a64a0e9952..af6f63a55f1 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -7438,7 +7438,9 @@ bool JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan(JOIN *join, > st_select_lex *sjm_sel= emb_sj_nest->sj_subq_pred->unit->first_select(); > for (uint i= 0; i < sjm_sel->item_list.elements; i++) > { > - if (sjm_sel->ref_pointer_array[i] == keyuse->val) > + DBUG_ASSERT(keyuse->val->type() == Item::FIELD_ITEM); ^^(1) > + Field *field = ((Item_field*)sjm_sel->ref_pointer_array[i])->field; ^^(2) > + if (field->eq(((Item_field*)keyuse->val)->field)) > return true; > } > return false; As for typecast (2): As far as I understand, it is valid because of the following: - We get to this point in this function only when Semi-Join-Materialization-Scan is used. - SJM-Scan is only used when the subquery's select list has Item_field objects (This is checked in subquery_types_allow_materialization()). * Please add a comment in subquery_types_allow_materialization() about this function relying on that logic. * Please add here an assert that sjm_sel->ref_pointer_array[i] is an Item_field. The logic is complex and can be potentially broken. As for typecast (1): What if keyuse->val is not an Item_field? If we get to this point in this function, it means it's an expression that only depends on the SJ-Inner table. This cannot just happen due to limitations in subquery_types_allow_materialization(), and I wasn't able to construct an example where it would happen (tried playing with multiple-equalities, but substitute_for_best_equal_item is called after get_best_combination(), tried playing with constant tables but then SJ-Materialization is not used, etc. ) I think we will be safer by changing the assert to be this check: if (keyuse->val->type() == Item::FIELD_ITEM) { ... everything else } The idea is that a KEYUSE that satisfies the requirement will still be available, and we just want to filter out any extra KEYUSE that might pop up due to some optimization. what do you think? Ok to push after the above is addressed. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0