- developers - lists.mariadb.org

[Maria-developers] Welcome to Google Summer of Code 2020 with MariaDB
by Vicențiu Ciorbaru 04 May '20

04 May '20

Hi! GSoC 2020 accepted proposals have just been announced and you are part of the successful applicants. This year we had a lot of very good proposals and so we have a one of the highest number of students (7) to mentor for GSoC. Congratulations on the effort to learn about the program, get involved and submit successful proposals! The fun part begins now! If you have not done so already, please reach out to your mentor and seek further advice about your project. Each one of your projects can be implemented in multiple ways, some more complex than others. I strongly advise you to create a concrete plan based off of your proposal with your mentor and aim to stick to it. The MariaDB Server codebase is a complex one with lots of different parts, however your mentor can guide you to ensure you have a successful GSoC. Remember that you are here to learn about Open Source, what it means to be part of an Open Source community, to interact with the community, all while becoming better programmers. Please take advantage of this opportunity and keep your technical discussions public, on Zulip https://mariadb.zulipchat.com, and on our developer mailing list https://launchpad.net/~maria-developers. By keeping your discussions public, you get the benefit of not only having your mentor see your questions but all of the MariaDB community. You never know when someone might lend a hand in case your mentor is not yet online. :) The mantra of our community should be that we are always welcoming to new people. Do not be afraid to ask questions. There are no bad or stupid or silly questions. The only key requirement is that you first make an effort to find the answer yourself and only afterwards seek outside help. If you can show a bit of involvement, the community will show you the same. I recommend that you agree with your mentor on some form of weekly progress reporting, via either a blog-post, an email, or something similar that can be publicly shared. I can not stress this enough: we are working on an open source project, our work must be done in the open and it is important to *show the world the good we are doing*. Hope all of you will have a great GSoC! Vicențiu MariaDB Foundation GSoC Admin

1 0

Re: [Maria-developers] d08860b28f3: MDEV-22374: VIEW with security definer require FILE privilege from definer not invoker in case of INTO OUTFILE
by Sergei Golubchik 04 May '20

04 May '20

Hi, Oleksandr! On May 04, Oleksandr Byelkin wrote: > revision-id: d08860b28f3 (mariadb-5.5.67-15-gd08860b28f3) > parent(s): ac2604f923f > author: Oleksandr Byelkin <sanja(a)mariadb.com> > committer: Oleksandr Byelkin <sanja(a)mariadb.com> > timestamp: 2020-04-28 09:16:33 +0200 > message: > > MDEV-22374: VIEW with security definer require FILE privilege from definer not invoker in case of INTO OUTFILE > > Check INTO OUTFILE clause always from invoker. > > --- > mysql-test/r/view_grant.result | 21 +++++++++++++++++++++ > mysql-test/t/view_grant.test | 33 +++++++++++++++++++++++++++++++++ > sql/sql_parse.cc | 18 +++++++++--------- > 3 files changed, 63 insertions(+), 9 deletions(-) > > diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result > index b2d3a0b8ca4..37dc6adc978 100644 > --- a/mysql-test/r/view_grant.result > +++ b/mysql-test/r/view_grant.result > @@ -1587,3 +1587,24 @@ USE test; > DROP DATABASE mysqltest1; > DROP USER 'mysqluser1'@'%'; > DROP USER 'mysqluser2'@'%'; > +# > +# MDEV-22374: VIEW with security definer require FILE privilege from definer > +# not invoker in case of INTO OUTFILE > +# > +create user test@localhost; > +grant select on test.* to test@localhost; > +create table t1 (a int); > +create definer=test@localhost sql security definer view v1 as select * from t1; > +# check that ot works without view typo "it" > +select * INTO OUTFILE '../..//tmp/test_out_txt' from t1; > +# check that ot works without file same why "//" ? > +select * from v1; > +a > +# rights for file should be taken from current user not view > +select * INTO OUTFILE '../../tmp/test_out_txt' from (select count(*) from v1) as dv1; > +select * INTO OUTFILE '../..//tmp/test_out_txt' from (select * from v1) as dv1; > +select * INTO OUTFILE '../..//tmp/test_out_txt' from v1; same > +drop view v1; please add reverse tests too: +create sql security definer view v1 as select * from t1; +connect(con1, localhost, test); +error 1045; +select * INTO OUTFILE '../..//tmp/test_out_txt' from t1; +select * from v1; +error 1045; +select * INTO OUTFILE '../../tmp/test_out_txt' from (select count(*) from v1) as dv1; +error 1045; +select * INTO OUTFILE '../..//tmp/test_out_txt' from (select * from v1) as dv1; +error 1045; +select * INTO OUTFILE '../..//tmp/test_out_txt' from v1; +connection default; > +drop table t1; > +drop user test@localhost; > +# End of 5.5 tests > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index ae5a6b4cd35..515990c879f 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2206,15 +2206,15 @@ mysql_execute_command(THD *thd) > lex->exchange != NULL implies SELECT .. INTO OUTFILE and this > requires FILE_ACL access. > */ > - ulong privileges_requested= lex->exchange ? SELECT_ACL | FILE_ACL : > - SELECT_ACL; > - > - if (all_tables) > - res= check_table_access(thd, > - privileges_requested, > - all_tables, FALSE, UINT_MAX, FALSE); > - else > - res= check_access(thd, privileges_requested, any_db, NULL, NULL, 0, 0); > + if (lex->exchange) > + res= check_access(thd, FILE_ACL, any_db, NULL, NULL, 0, 0); > + if (!res) > + { > + if (all_tables) > + res= check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE); > + else > + res= check_access(thd, SELECT_ACL, any_db, NULL, NULL, 0, 0); > + } No, I think this special check for a special SELECT ... INTO OUTFILE case is wrong. Why does the server even checks global privileges via views? It should not do it, I think, not for FILE not for any other global or db level privilege. I'd try something like diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -4699,7 +4699,7 @@ bool check_grant(THD *thd, ulong want_access, TABLE_LIST > Save a copy of the privileges without the SHOW_VIEW_ACL attribute. It will be checked during making view. */ - tl->grant.orig_want_privilege= (want_access & ~SHOW_VIEW_ACL); + tl->grant.orig_want_privilege= want_access & ~SHOW_VIEW_ACL & TABLE_ACLS; } mysql_rwlock_rdlock(&LOCK_grant); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5204,7 +5204,8 @@ check_table_access(THD *thd, ulong requirements,TABLE_LIS> Register access for view underlying table. Remove SHOW_VIEW_ACL, because it will be checked during making view */ - table_ref->grant.orig_want_privilege= (want_access & ~SHOW_VIEW_ACL); + table_ref->grant.orig_want_privilege= + want_access & ~SHOW_VIEW_ACL & TABLE_ACLS; if (table_ref->schema_table_reformed) { Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] 3ead2cea95c: MDEV-13266: Race condition in ANALYZE TABLE / statistics collection
by Sergey Petrunia 04 May '20

04 May '20

Hi Varun, On Sun, Apr 12, 2020 at 09:08:37PM +0530, Varun wrote: > revision-id: 3ead2cea95c32b7ceaf6e6ec81f7afbd9137cfe9 (mariadb-10.2.31-103-g3ead2cea95c) > parent(s): d565895bbd8e2a163be48b9bac51fccbf3949c80 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2020-04-12 21:05:36 +0530 > message: > > MDEV-13266: Race condition in ANALYZE TABLE / statistics collection > > Fixing a race condition while collecting the engine independent statistics. > The issue here was when the statistics was collected on specific indexes > then because of some race condition the statistics for indexes was not > collected. The TABLE::keys_in_use_for_query was set to 0 in such cases. > > This happens when the table is opened from TABLE_SHARE instead of the > table share stored in table_cache. It would be nice to write down the race condition that we've caught (in case we need this info later) Is my understanding correct: the error scenario is as follows: Thread1> start running "ANALYZE TABLE t PERISTENT FOR COLUMNS (..) INDEXES ($list)" Thread1> Walk through $list and save it in TABLE::keys_in_use_for_query Thread1> Close/re-open tables Thread2> Make some use of table t. This involves taking table t from Thread2> the table cache, and putting it back (with TABLE::keys_in_use_for_query reset to 0) Thread1> continue collecting EITS stats. Since TABLE::keys_in_use_for_query we will not collect statistics for indexes in $list. Please confirm (and if not, describe the race condition). The patch also introduces this behaviour: analyze table ... persistent for ... indexes(no_such_index); will now cause engine statistics to be still collected. Before the patch it exited with an error. But then, this is consistent with what happens for analyze table ... persistent for columns(no_such_column) ... So I guess this is ok. > --- > sql/sql_admin.cc | 46 +++++++++++++++++++++------------------------- > 1 file changed, 21 insertions(+), 25 deletions(-) > > diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc > index ab95fdc340c..0613495f202 100644 > --- a/sql/sql_admin.cc > +++ b/sql/sql_admin.cc > @@ -769,31 +769,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, > (table->table->s->table_category == TABLE_CATEGORY_USER && > (get_use_stat_tables_mode(thd) > NEVER || > lex->with_persistent_for_clause)); > - > - > - if (!lex->index_list) > - { > - tab->keys_in_use_for_query.init(tab->s->keys); > - } > - else > - { > - int pos; > - LEX_STRING *index_name; > - List_iterator_fast<LEX_STRING> it(*lex->index_list); > - > - tab->keys_in_use_for_query.clear_all(); > - while ((index_name= it++)) > - { > - if (tab->s->keynames.type_names == 0 || > - (pos= find_type(&tab->s->keynames, index_name->str, > - index_name->length, 1)) <= 0) > - { > - compl_result_code= result_code= HA_ADMIN_INVALID; > - break; > - } > - tab->keys_in_use_for_query.set_bit(--pos); > - } > - } > } > > if (result_code == HA_ADMIN_OK) > @@ -878,6 +853,27 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, > } > tab->file->column_bitmaps_signal(); > } > + if (!lex->index_list) > + tab->keys_in_use_for_query.init(tab->s->keys); > + else > + { > + int pos; > + LEX_STRING *index_name; > + List_iterator_fast<LEX_STRING> it(*lex->index_list); > + > + tab->keys_in_use_for_query.clear_all(); > + while ((index_name= it++)) > + { > + if (tab->s->keynames.type_names == 0 || > + (pos= find_type(&tab->s->keynames, index_name->str, > + index_name->length, 1)) <= 0) > + { > + compl_result_code= result_code= HA_ADMIN_INVALID; > + break; > + } > + tab->keys_in_use_for_query.set_bit(--pos); > + } > + } > if (!(compl_result_code= > alloc_statistics_for_table(thd, table->table)) && > !(compl_result_code= > _______________________________________________ > 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

2 2

[Maria-developers] Igor please review: MDEV-22377: Subquery in an [UPDATE] query uses full scan instead of range
by Sergey Petrunia 29 Apr '20

29 Apr '20

Hello Igor, Could you please review the below: commit 793b100cc01228f01ead841d6c45f9e2b07786f9 Author: Sergei Petrunia <psergey(a)askmonty.org> Date: Thu Apr 30 01:25:11 2020 +0300 MDEV-22377: Subquery in an [UPDATE] query uses full scan instead of range When doing IN->EXISTS rewrite, Item_in_subselect::inject_in_to_exists_cond injects equalities into subquery's WHERE condition. The problem is that build_equal_items() has already been called for the subquery's WHERE, and tampering with the WHERE condition can prevent equality propagation from working. If the subquery's WHERE is an Item_cond_and with multiple equalities: - Item_equal objects form a suffix sub-list of the list in WHERE's Item_cond_and::list. The suffix is stored in JOIN::cond_equal->current_level. - Item_cond_and::m_cond_equal must also be preserved. This patch makes inject_in_to_exists_cond() not to break these properties diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index a3d7fda7abc..c54d635230f 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -262,7 +262,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2c ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where (`test`.`t2b`.`m` <> `test`.`t1`.`a` or `test`.`t2b`.`m` = `test`.`t2a`.`m`) and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2` or `test`.`t2a`.`c2` is null) and `test`.`t2c`.`c2` = `test`.`t2b`.`c2` and `test`.`t2b`.`n` = `test`.`t2a`.`m` having trigcond(`test`.`t2a`.`c2` is null)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where `test`.`t2c`.`c2` = `test`.`t2b`.`c2` and `test`.`t2b`.`n` = `test`.`t2a`.`m` and (`test`.`t2b`.`m` <> `test`.`t1`.`a` or `test`.`t2b`.`m` = `test`.`t2a`.`m`) and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2` or `test`.`t2a`.`c2` is null) having trigcond(`test`.`t2a`.`c2` is null)))) DROP TABLE t1,t2; # # MDEV-614, also MDEV-536, also LP:1050806: diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index 5c4544a1b05..0034f61ac23 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -169,7 +169,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) and `test`.`t2`.`a` = `test`.`t1`.`b` having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); insert into t1 values @@ -197,7 +197,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) and `test`.`t2`.`a` = `test`.`t1`.`b`))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); insert into t1 (oref, grp) values diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 4260676cc37..b7b18bf80e0 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -172,7 +172,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) and `test`.`t2`.`a` = `test`.`t1`.`b` having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); insert into t1 values @@ -200,7 +200,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) and `test`.`t2`.`a` = `test`.`t1`.`b`))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); insert into t1 (oref, grp) values diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 3dc019cc6c1..e355cfc60f0 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2349,7 +2349,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select 3 AS `f` from dual where !<expr_cache><3>(<in_optimizer>(3,<exists>(/* select#2 */ select `test`.`t1`.`b` from `test`.`t1` where (`test`.`t1`.`c` = 'USA' or `test`.`t1`.`c` <> 'USA') and trigcond(<cache>(3) = `test`.`t1`.`b` or `test`.`t1`.`b` is null) and `test`.`t1`.`b` = `test`.`t1`.`a` having trigcond(`test`.`t1`.`b` is null)))) +Note 1003 /* select#1 */ select 3 AS `f` from dual where !<expr_cache><3>(<in_optimizer>(3,<exists>(/* select#2 */ select `test`.`t1`.`b` from `test`.`t1` where `test`.`t1`.`b` = `test`.`t1`.`a` and (`test`.`t1`.`c` = 'USA' or `test`.`t1`.`c` <> 'USA') and trigcond(<cache>(3) = `test`.`t1`.`b` or `test`.`t1`.`b` is null) having trigcond(`test`.`t1`.`b` is null)))) SELECT * FROM t2 WHERE f NOT IN (SELECT b FROM t1 WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b); @@ -2582,3 +2582,29 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 100 drop table t0, t1, t2; +# +# MDEV-22377: Subquery in an UPDATE query uses full scan instead of range +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(nkey1 int, col1 int); +insert into t1 select A.a+10*B.a+100*C.a, A.a+10*B.a+100*C.a from t0 A, t0 B, t0 C; +create table t2(key1 int, col1 int, key(key1)); +insert into t2 select A.a+10*B.a, A.a+10*B.a from t0 A, t0 B; +alter table t0 add b int; +# This must use the join order of t0,t2,t1 +# And table t2 must use range access, not full scan: +explain +select * +from t0 +where +a not in (SELECT t1.col1 +FROM t1 JOIN t2 ON t2.key1 = t1.nkey1 +WHERE +t1.nkey1 IN (1,2,3,4,5) and t2.col1>t0.b +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY t2 range key1 key1 5 NULL 5 Using index condition; Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1000 Using where; Using join buffer (flat, BNL join) +drop table t0, t1, t2; diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index bc8db41c505..e3d8b5be832 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2115,6 +2115,32 @@ where exists (select * from t2 where t2.a=t1.a order by t2.b limit 2,3); explain select * from t1 where t1.a in (select t2.a from t2 order by t2.b); +drop table t0, t1, t2; + +--echo # +--echo # MDEV-22377: Subquery in an UPDATE query uses full scan instead of range +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(nkey1 int, col1 int); +insert into t1 select A.a+10*B.a+100*C.a, A.a+10*B.a+100*C.a from t0 A, t0 B, t0 C; + +create table t2(key1 int, col1 int, key(key1)); +insert into t2 select A.a+10*B.a, A.a+10*B.a from t0 A, t0 B; + +alter table t0 add b int; +--echo # This must use the join order of t0,t2,t1 +--echo # And table t2 must use range access, not full scan: +explain +select * +from t0 +where + a not in (SELECT t1.col1 + FROM t1 JOIN t2 ON t2.key1 = t1.nkey1 + WHERE + t1.nkey1 IN (1,2,3,4,5) and t2.col1>t0.b + ); drop table t0, t1, t2; + diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index a18c5e608f1..755f1e2ad13 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -100,7 +100,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1` and `test`.`t2`.`c3` = `test`.`t1b`.`c4`))) +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t2`.`c3` = `test`.`t1b`.`c4` and `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))) SELECT pk FROM t1 WHERE c1 IN @@ -364,7 +364,7 @@ AND a = SOME (SELECT b FROM t5)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t5 index c c 10 NULL 2 Using where; Using index; Start temporary -2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using index condition; Using where; End temporary +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using where; End temporary SELECT * FROM t3 WHERE t3.b > ALL ( diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 736dfbd33b4..726c58e49a2 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2704,9 +2704,28 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg) and_args= ((Item_cond*) join_arg->conds)->argument_list(); if (join_arg->cond_equal) and_args->disjoin((List<Item> *) &join_arg->cond_equal->current_level); + + /* + If subquery's WHERE is an Item_cond_and, add the injected conditions + into it manually. If we just called and_items(...) that could + potentially replace the top-level Item_cond_and and current top-level + Item_cond_and::m_cond_equal will be lost. + */ + if (where_item->type() == Item::COND_ITEM && + ((Item_cond*)where_item)->functype() == Item_func::COND_AND_FUNC) + { + List_iterator<Item> it(*((Item_cond*)where_item)->argument_list()); + while (Item *item=it++) + and_args->push_back(item); + } + else + and_args->push_back(where_item); + + where_item= join_arg->conds; } + else + where_item= and_items(thd, join_arg->conds, where_item); - where_item= and_items(thd, join_arg->conds, where_item); if (where_item->fix_fields_if_needed(thd, 0)) DBUG_RETURN(true); // TIMOUR TODO: call optimize_cond() for the new where clause @@ -2719,12 +2738,15 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg) { /* The argument list of the top-level AND may change after fix fields. */ and_args= ((Item_cond*) join_arg->conds)->argument_list(); - List_iterator<Item_equal> li(join_arg->cond_equal->current_level); - Item_equal *elem; - while ((elem= li++)) - { - and_args->push_back(elem, thd->mem_root); - } + + /* + Note that Item_equal objects must be present in this form: + Item_cond::conds list must have the end of the list to be the + same linked list as join_arg->cond_equal->current_level. + The code in substitute_for_best_equal_field depends on it (see the + disjoin() call). + */ + and_args->append((List<Item> *)&join_arg->cond_equal->current_level); } } BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] 96aee3229a2: MENT-703: Disable rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master, rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist tests in ES 10.2 and above
by sujatha 29 Apr '20

29 Apr '20

revision-id: 96aee3229a288dae52c457ca808966092da6d821 (mariadb-10.4.5-669-g96aee3229a2) parent(s): a284ea1909d66e4284a964a4ca7d45b75c450ed7 author: Sujatha committer: Sujatha timestamp: 2020-04-29 13:28:23 +0530 message: MENT-703: Disable rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master, rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist tests in ES 10.2 and above Problem: ======= Disable following tests in ES 10.2 and above, till MDEV-13258 and MDEV-14203 are fixed. rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master, rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist --- mysql-test/suite/rpl/disabled.def | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/mysql-test/suite/rpl/disabled.def b/mysql-test/suite/rpl/disabled.def index 77b01bd8d1d..5a04f4ccd15 100644 --- a/mysql-test/suite/rpl/disabled.def +++ b/mysql-test/suite/rpl/disabled.def @@ -19,3 +19,7 @@ rpl_parallel2 : fails after MDEV-16172 rpl_semi_sync_after_sync : fails after MDEV-16172 rpl_slave_grp_exec: MDEV-10514 rpl_auto_increment_update_failure : disabled for now +rpl_skip_replication : MDEV-13258 +rpl_set_statement_default_master : MDEV-13258 +rpl_extra_col_master_myisam : MDEV-14203 +rpl_slave_load_tmpdir_not_exist : MDEV-14203

1 0

[Maria-developers] 7f8dd7b2044: MENT-703: Disable rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master, rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist tests in ES 10.2 and above
by sujatha 29 Apr '20

29 Apr '20

revision-id: 7f8dd7b20443e0247ca478cbfd98c43749acdae0 (mariadb-10.2.24-722-g7f8dd7b2044) parent(s): 52cb9e87e1d23aeb0958966807050a6a843c181c author: Sujatha committer: Sujatha timestamp: 2020-04-29 11:33:41 +0530 message: MENT-703: Disable rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master, rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist tests in ES 10.2 and above Problem: ======= Disable following tests in ES 10.2 and above, till MDEV-13258 and MDEV-14203 are fixed. rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master, rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist --- mysql-test/suite/rpl/disabled.def | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/mysql-test/suite/rpl/disabled.def b/mysql-test/suite/rpl/disabled.def index bdefb1660bd..951f8a6aa0f 100644 --- a/mysql-test/suite/rpl/disabled.def +++ b/mysql-test/suite/rpl/disabled.def @@ -16,3 +16,7 @@ rpl_partition_archive : MDEV-5077 2013-09-27 svoj Cannot exchange partition rpl_row_binlog_max_cache_size : MDEV-11092 rpl_row_index_choice : MDEV-11666 rpl_slave_grp_exec: MDEV-10514 +rpl_skip_replication : MDEV-13258 +rpl_set_statement_default_master : MDEV-13258 +rpl_extra_col_master_myisam : MDEV-14203 +rpl_slave_load_tmpdir_not_exist : MDEV-14203

1 0

Re: [Maria-developers] [Commits] 85b116d1ab9: MDEV-22317: SIGSEGV in my_free/delete_dynamic in optimized builds (ARIA)
by sujatha 28 Apr '20

28 Apr '20

Hello Andrei, Good morning. Thank you for the review comments. Please find my replies inline. On 27/04/20 11:26 pm, Andrei Elkin wrote: > Sujatha, howdy. > > Thanks for a prompt checking the case! > The patch looks good, only > >> - if (!wild_do_table.elements) >> + if (status && wild_do_table_inited) > I would suggest to reorder the && arg:s to be then > in temporal wrt their evaluation order. Sure Andrei. I have refined changes as shown below. diff --git a/sql/rpl_filter.cc b/sql/rpl_filter.cc index 635a0f4e2d6..8035763bf35 100644 --- a/sql/rpl_filter.cc +++ b/sql/rpl_filter.cc @@ -416,10 +416,13 @@ Rpl_filter::set_wild_do_table(const char* table_spec) status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_do_table); - if (!wild_do_table.elements) + if (wild_do_table_inited && status) { - delete_dynamic(&wild_do_table); - wild_do_table_inited= 0; + if (!wild_do_table.elements) + { + delete_dynamic(&wild_do_table); + wild_do_table_inited= 0; + } } > >> revision-id: 85b116d1ab9ea85dcef63d259b8f6366466e2750 (mariadb-10.5.2-185-g85b116d1ab9) >> parent(s): fbe2712705d464bf8488df249c36115e2c1f63f7 >> author: Sujatha >> committer: Sujatha >> timestamp: 2020-04-27 17:43:51 +0530 >> message: >> >> MDEV-22317: SIGSEGV in my_free/delete_dynamic in optimized builds (ARIA) >> >> Problem: >> ======= >> SET @@GLOBAL.replicate_wild_ignore_table=''; >> SET @@GLOBAL.replicate_wild_do_table=''; >> >> Reports following valgrind error. >> >> Conditional jump or move depends on uninitialised value(s) >> Rpl_filter::set_wild_ignore_table(char const*) (rpl_filter.cc:439) >> >> Conditional jump or move depends on uninitialised value(s) >> at 0xF60390: delete_dynamic (array.c:304) >> by 0x74F3F2: Rpl_filter::set_wild_do_table(char const*) (rpl_filter.cc:421) >> >> Analysis: >> ======== >> List of values provided for options "wild_do_table" and "wild_ignore_table" are >> stored in DYNAMIC_ARRAYS. When an empty list is provided these dynamic arrays >> are not initialized. > Correct. And that's 'cos > > Rpl_filter::Rpl_filter() : > parallel_mode(SLAVE_PARALLEL_CONSERVATIVE), > table_rules_on(0), > do_table_inited(0), ignore_table_inited(0), > wild_do_table_inited(0), wild_ignore_table_inited(0) > { > do_db.empty(); > ignore_db.empty(); > rewrite_db.empty(); > } > > does not initialize the two members of your patch's interest with > empty()-ing. > I wonder if > > + wild_do_table.empty() > + wild_ignore_table.empty() > ? > > > that could work out as well which looks as more consistent solution to > me. [It feels like binlog_filter object might object this idea though]. 'do_db' and 'ignore_db' are of type list iterators. Hence they have 'empty' defined for them. 'empty' is not a member of DYNAMIC_ARRAY. I_List<i_string> do_db; I_List<i_string> ignore_db; /home/sujatha/bug_repo/MDEV-22317-10.5/sql/rpl_filter.cc:36:17: error: ‘DYNAMIC_ARRAY {aka struct st_dynamic_array}’ has no member named ‘empty’ wild_do_table.empty(); ^~~~~ /home/sujatha/bug_repo/MDEV-22317-10.5/sql/rpl_filter.cc:37:21: error: ‘DYNAMIC_ARRAY {aka struct st_dynamic_array}’ has no member named ‘empty’ wild_ignore_table.empty(); ^~~~~ "wild_do_table" and "wild_ignore_table" arrays get initialized as part of "add_wild_do_table" and "add_wild_ignore_table". The initialization is done through following calls. status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_do_table); status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_ignore_table); When default empty string is provided for replicate_wild_ignore_table='' and replicate_wild_do_table='' the dynamic array is not initialized. 'wild_do_table.elements' will be '0'. No cleanup is required in this case. Hence we should do the cleanup only when 'wild_do_table_inited' is true. Please let me know your thoughts. Thank you S.Sujatha > > Cheers, > > Andrei > > >> Existing code treats empty element list as an error and >> tries to clean the uninitialized list. This results in above valgrind issue. >> >> Fix: >> === >> The clean up should be initiated only when there is an error while parsing the >> 'wild_do_table' or 'wild_ignore_table' list and the dynamic_array is in >> initialized state. Otherwise for empty list it should simply return success. >> >> --- >> mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result | 2 ++ >> mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test | 2 ++ >> sql/rpl_filter.cc | 4 ++-- >> 3 files changed, 6 insertions(+), 2 deletions(-) >> >> diff --git a/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result >> index 47cd362a549..fe0b283fc7c 100644 >> --- a/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result >> +++ b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result >> @@ -7,6 +7,8 @@ SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; >> ERROR HY000: This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first >> connection slave; >> include/stop_slave.inc >> +SET @@GLOBAL.replicate_wild_do_table=""; >> +SET @@GLOBAL.replicate_wild_ignore_table=""; >> SET @@GLOBAL.replicate_wild_do_table="test.a%"; >> SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; >> include/start_slave.inc >> diff --git a/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test >> index 6db61927eec..657a95cec15 100644 >> --- a/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test >> +++ b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test >> @@ -13,6 +13,8 @@ SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; >> >> connection slave; >> source include/stop_slave.inc; >> +SET @@GLOBAL.replicate_wild_do_table=""; >> +SET @@GLOBAL.replicate_wild_ignore_table=""; >> SET @@GLOBAL.replicate_wild_do_table="test.a%"; >> SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; >> source include/start_slave.inc; >> diff --git a/sql/rpl_filter.cc b/sql/rpl_filter.cc >> index 635a0f4e2d6..49b498d3568 100644 >> --- a/sql/rpl_filter.cc >> +++ b/sql/rpl_filter.cc >> @@ -416,7 +416,7 @@ Rpl_filter::set_wild_do_table(const char* table_spec) >> >> status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_do_table); >> >> - if (!wild_do_table.elements) >> + if (status && wild_do_table_inited) >> { >> delete_dynamic(&wild_do_table); >> wild_do_table_inited= 0; >> @@ -436,7 +436,7 @@ Rpl_filter::set_wild_ignore_table(const char* table_spec) >> >> status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_ignore_table); >> >> - if (!wild_ignore_table.elements) >> + if (status && wild_ignore_table_inited) >> { >> delete_dynamic(&wild_ignore_table); >> wild_ignore_table_inited= 0; >> _______________________________________________ >> commits mailing list >> commits(a)mariadb.org >> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] 85b116d1ab9: MDEV-22317: SIGSEGV in my_free/delete_dynamic in optimized builds (ARIA)
by sujatha 27 Apr '20

27 Apr '20

revision-id: 85b116d1ab9ea85dcef63d259b8f6366466e2750 (mariadb-10.5.2-185-g85b116d1ab9) parent(s): fbe2712705d464bf8488df249c36115e2c1f63f7 author: Sujatha committer: Sujatha timestamp: 2020-04-27 17:43:51 +0530 message: MDEV-22317: SIGSEGV in my_free/delete_dynamic in optimized builds (ARIA) Problem: ======= SET @@GLOBAL.replicate_wild_ignore_table=''; SET @@GLOBAL.replicate_wild_do_table=''; Reports following valgrind error. Conditional jump or move depends on uninitialised value(s) Rpl_filter::set_wild_ignore_table(char const*) (rpl_filter.cc:439) Conditional jump or move depends on uninitialised value(s) at 0xF60390: delete_dynamic (array.c:304) by 0x74F3F2: Rpl_filter::set_wild_do_table(char const*) (rpl_filter.cc:421) Analysis: ======== List of values provided for options "wild_do_table" and "wild_ignore_table" are stored in DYNAMIC_ARRAYS. When an empty list is provided these dynamic arrays are not initialized. Existing code treats empty element list as an error and tries to clean the uninitialized list. This results in above valgrind issue. Fix: === The clean up should be initiated only when there is an error while parsing the 'wild_do_table' or 'wild_ignore_table' list and the dynamic_array is in initialized state. Otherwise for empty list it should simply return success. --- mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result | 2 ++ mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test | 2 ++ sql/rpl_filter.cc | 4 ++-- 3 files changed, 6 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result index 47cd362a549..fe0b283fc7c 100644 --- a/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result +++ b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result @@ -7,6 +7,8 @@ SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; ERROR HY000: This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first connection slave; include/stop_slave.inc +SET @@GLOBAL.replicate_wild_do_table=""; +SET @@GLOBAL.replicate_wild_ignore_table=""; SET @@GLOBAL.replicate_wild_do_table="test.a%"; SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; include/start_slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test index 6db61927eec..657a95cec15 100644 --- a/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test +++ b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test @@ -13,6 +13,8 @@ SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; connection slave; source include/stop_slave.inc; +SET @@GLOBAL.replicate_wild_do_table=""; +SET @@GLOBAL.replicate_wild_ignore_table=""; SET @@GLOBAL.replicate_wild_do_table="test.a%"; SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; source include/start_slave.inc; diff --git a/sql/rpl_filter.cc b/sql/rpl_filter.cc index 635a0f4e2d6..49b498d3568 100644 --- a/sql/rpl_filter.cc +++ b/sql/rpl_filter.cc @@ -416,7 +416,7 @@ Rpl_filter::set_wild_do_table(const char* table_spec) status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_do_table); - if (!wild_do_table.elements) + if (status && wild_do_table_inited) { delete_dynamic(&wild_do_table); wild_do_table_inited= 0; @@ -436,7 +436,7 @@ Rpl_filter::set_wild_ignore_table(const char* table_spec) status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_ignore_table); - if (!wild_ignore_table.elements) + if (status && wild_ignore_table_inited) { delete_dynamic(&wild_ignore_table); wild_ignore_table_inited= 0;

1 0

Re: [Maria-developers] 8a990ad1774: MDEV-18319 BIGINT UNSIGNED Performance issue
by Sergei Golubchik 27 Apr '20

27 Apr '20

Hi, Alexander! On Apr 26, Alexander Barkov wrote: > revision-id: 8a990ad1774 > author: Alexander Barkov <bar(a)mariadb.com> > committer: Alexander Barkov <bar(a)mariadb.com> > timestamp: 2019-03-25 19:19:48 +0400 > message: MDEV-18319 BIGINT UNSIGNED Performance issue > > diff --git a/mysql-test/main/errors.result b/mysql-test/main/errors.result > index ba05a2b37d45..96ad96395aab 100644 > --- a/mysql-test/main/errors.result > +++ b/mysql-test/main/errors.result > @@ -32,16 +32,21 @@ set sql_mode=default; > CREATE TABLE t1 (a INT); > SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0))); > a > +Warnings: > +Warning 1365 Division by 0 Interesting. There was no warning before. > INSERT INTO t1 VALUES(1); > SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0))); > a > 1 > diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result > index 65313148bf80..59822ae1049c 100644 > --- a/mysql-test/main/func_in.result > +++ b/mysql-test/main/func_in.result > @@ -489,6 +489,7 @@ SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); > id > Warnings: > Warning 1365 Division by 0 > +Warning 1365 Division by 0 This is not very nice. May be we should use Item_cache in these cases? > DROP TABLE t1; > End of 5.0 tests > create table t1(f1 char(1)); > @@ -909,3 +910,71 @@ Warnings: > Warning 1292 Truncated incorrect time value: '' > Warning 1292 Truncated incorrect time value: '' > Warning 1292 Truncated incorrect time value: '' > +# > +# MDEV-18319 BIGINT UNSIGNED Performance issue > +# > +CREATE TABLE t1 ( > +id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY > +); > +FOR i IN 0..255 > +DO > +INSERT INTO t1 VALUES (); > +END FOR > +$$ > +SELECT MIN(id), MAX(id), COUNT(*) FROM t1; > +MIN(id) MAX(id) COUNT(*) > +1 256 256 interesting. why it's min=1 and max=255, if your loop is in 0..255? > +EXPLAIN SELECT id FROM t1 WHERE id IN (1,2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806, 9223372036854775807); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807, 9223372036854775808); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (1.0,2.0); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806.0, 9223372036854775807.0); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +# Cannot compare this as INT (yet) > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807.0, 9223372036854775808.0); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 256 Using where; Using index > +DROP TABLE t1; > +CREATE TABLE t1 ( > +id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY > +); > +FOR i IN 0..255 > +DO > +INSERT INTO t1 VALUES (); > +END FOR > +$$ > +SELECT MIN(id), MAX(id), COUNT(*) FROM t1; > +MIN(id) MAX(id) COUNT(*) > +1 256 256 > +EXPLAIN SELECT id FROM t1 WHERE id IN (-1,-2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806, -9223372036854775807); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807, -9223372036854775808); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (-1.0,-2.0); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806.0, -9223372036854775807.0); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807.0, -9223372036854775808.0); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index > +# Cannot compare this as INT (yet) > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775808.0, -9223372036854775809.0); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 256 Using where; Using index > +DROP TABLE t1; > diff --git a/mysql-test/main/func_in.test b/mysql-test/main/func_in.test > index b99fad159c22..04b0b328c27b 100644 > --- a/mysql-test/main/func_in.test > +++ b/mysql-test/main/func_in.test > @@ -690,3 +690,51 @@ SELECT > TIME'00:00:00'='' AS c1_true, > TIME'00:00:00' IN ('', TIME'10:20:30') AS c2_true, > TIME'00:00:00' NOT IN ('', TIME'10:20:30') AS c3_false; > + > +--echo # > +--echo # MDEV-18319 BIGINT UNSIGNED Performance issue > +--echo # > + > +CREATE TABLE t1 ( > + id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY > +); > +DELIMITER $$; > +FOR i IN 0..255 > +DO > + INSERT INTO t1 VALUES (); > +END FOR > +$$ > +DELIMITER ;$$ A couple of one-liners instead: insert t1 select seq from seq_1_to_256; or, in pure SQL insert t1 with recursive g(i) as (select 1 union select i+1 from g where i < 257) select * from g; > +SELECT MIN(id), MAX(id), COUNT(*) FROM t1; > +EXPLAIN SELECT id FROM t1 WHERE id IN (1,2); > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806, 9223372036854775807); > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807, 9223372036854775808); > + > +EXPLAIN SELECT id FROM t1 WHERE id IN (1.0,2.0); > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806.0, 9223372036854775807.0); > +--echo # Cannot compare this as INT (yet) > +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807.0, 9223372036854775808.0); > +DROP TABLE t1; > + > + > +CREATE TABLE t1 ( > + id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY > +); > +DELIMITER $$; > +FOR i IN 0..255 > +DO > + INSERT INTO t1 VALUES (); > +END FOR > +$$ > +DELIMITER ;$$ > +SELECT MIN(id), MAX(id), COUNT(*) FROM t1; > +EXPLAIN SELECT id FROM t1 WHERE id IN (-1,-2); > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806, -9223372036854775807); > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807, -9223372036854775808); > + > +EXPLAIN SELECT id FROM t1 WHERE id IN (-1.0,-2.0); > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806.0, -9223372036854775807.0); > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807.0, -9223372036854775808.0); > +--echo # Cannot compare this as INT (yet) > +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775808.0, -9223372036854775809.0); > +DROP TABLE t1; > diff --git a/sql/item.h b/sql/item.h > index 883cc791f384..9f215c74864b 100644 > --- a/sql/item.h > +++ b/sql/item.h > @@ -70,6 +70,11 @@ class Value: public st_value > bool is_temporal() const { return m_type == DYN_COL_DATETIME; } > bool is_string() const { return m_type == DYN_COL_STRING; } > bool is_decimal() const { return m_type == DYN_COL_DECIMAL; } > + Longlong_hybrid to_longlong_hybrid_native() const > + { > + DBUG_ASSERT(is_longlong()); > + return Longlong_hybrid(value.m_longlong, m_type == DYN_COL_UINT); > + } > }; > > > diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc > index b2fa753f2bd2..c7176df43d6a 100644 > --- a/sql/item_cmpfunc.cc > +++ b/sql/item_cmpfunc.cc > @@ -3747,11 +3748,45 @@ bool Predicant_to_list_comparator::add_value(const char *funcname, > Item *tmpargs[2]; > tmpargs[0]= args->arguments()[m_predicant_index]; > tmpargs[1]= args->arguments()[value_index]; > - if (tmp.aggregate_for_comparison(funcname, tmpargs, 2, true)) > + if (tmp.aggregate_for_comparison(funcname, tmpargs, 2, false)) How would that work? Are you going to compare signed to unsigned as longlongs? > { > DBUG_ASSERT(current_thd->is_error()); > return true; > } > + /* > + Try to compare using type handler of the predicant when possible, > + as this can use indexes for conditions like: > + WHERE field IN (const1, .. constN) > + */ > + if (prefer_predicant_type_handler && > + args->arguments()[value_index]->const_item() && > + !args->arguments()[value_index]->is_expensive() && > + tmp.type_handler()->cmp_type() == DECIMAL_RESULT && > + args->arguments()[m_predicant_index]->cmp_type() == INT_RESULT) > + { > + /* > + For now we catch only one special case: > + an INT predicant can be compared to a DECIMAL constant > + using Longlong_hybrid comparison, when the DECIMAL constant: > + a. has no significant fractional digits, and > + b. is within the signed longlong range > + For DECIMAL constants in the range LONGLONG_MAX..ULONGLONG_MAX > + we cannot call to_longlong_hybrid() safely, because DECIMAL type > + Items usually set their "unsigned_flag" to "false", so val_int() > + will truncate such constants to LONGLONG_MAX (instead of ULONGLONG_MAX). > + TODO: fix to_longlong_hybrid() for DECIMAL LONGLONG_MAX..ULONGLONG_MAX > + TODO: move this code to Type_handler (will be done by MDEV-18898) > + TODO: skip constants outside of LONGLONG_MIN..ULONGLONG_MAX, as > + such conditon can never be true, e.g.: > + WHERE int_expr IN (.. -9223372036854775809 ..) > + WHERE int_expr IN (.. 18446744073709551616 ..) > + */ > + my_decimal buf, *dec= args->arguments()[value_index]->val_decimal(&buf); > + longlong res; > + if (dec && decimal_actual_fraction(dec) == 0 && > + my_decimal2int(0, dec, false /*SIGNED*/, &res) == 0) > + tmp.set_handler(&type_handler_longlong); why not to replace the item with Item_int or Item_cache_int here? Then you'll be able to handle decimals up to ULONGLONG_MAX, that's your first TODO. For the last TODO, you can simply remove the always-false element from the arguments[] array. > + } > m_comparators[m_comparator_count].m_handler= tmp.type_handler(); > m_comparators[m_comparator_count].m_arg_index= value_index; > m_comparator_count++; > diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h > index 06f15503258a..4e84c3008578 100644 > --- a/sql/item_cmpfunc.h > +++ b/sql/item_cmpfunc.h > @@ -1588,29 +1588,28 @@ class cmp_item_sort_string :public cmp_item_string > > class cmp_item_int : public cmp_item_scalar > { > - longlong value; > + Longlong_hybrid m_value; > public: > - cmp_item_int() {} /* Remove gcc warning */ > + cmp_item_int(): m_value(0, false) {} > void store_value(Item *item) > { > - value= item->val_int(); > + m_value= item->to_longlong_hybrid(); I wonder if to_longlong_hybrid() returns a Longlong_hybrid like that, where the compiler will allocate it? Could you check it, please? > m_null_value= item->null_value; > } Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] 00f9df29224: MDEV-20787: Script dgcov.pl does not work
by Sergei Golubchik 23 Apr '20

23 Apr '20

Hi, Anel! On Apr 23, Anel Husakovic wrote: > revision-id: 00f9df29224 (mariadb-10.2.29-23-g00f9df29224) > parent(s): 6718d3bc324 > author: Anel Husakovic <anel(a)mariadb.org> > committer: Anel Husakovic <anel(a)mariadb.org> > timestamp: 2019-11-19 15:00:48 +0100 > message: > > MDEV-20787: Script dgcov.pl does not work > > Let's change CMakeList with `--coverage` flag as an alias for > `-fprofile-arcs -ftest-coverage -lgcov` in addition. > When the server is compiled with `-DENABLE_GCOV=ON`, from object files are generated > `.gcno` and `.gcda` files. > `./mtr --gcov is_check_constraint` is invoking the script calls > `./dgcov.pl --purge`, `./mtr is_check_constraint`, > `./dgcov.pl --generate>/var/last_changes.dgcov`. > The `purge` flag is clearing `.gcda` files (and others extensions), > while running the test new `.gcda` files are obtained. > With `generate` flag, `gcov -i` (`intermediate format`) is called > on obtained `<object-files-name>.gcda` files (`dbug.c.gcda` e.g.). > The patch is tested on `gcov 6.3` and `gcov 7.4` versions > and can be seen that resulting `.gcov` file for `6.3` creates > `<full path>.gcov` (`dbug.c.gcda.gcov` e.g.) file, > where `gcov 7.4` is still creating `object-file-names.gcov`(`dbug.c.gcov`) files > as `gcov` in general is doing. > > diff --git a/CMakeLists.txt b/CMakeLists.txt > index 0dcc2a75587..af025a0312f 100644 > --- a/CMakeLists.txt > +++ b/CMakeLists.txt > @@ -249,7 +249,7 @@ SET(CMAKE_C_FLAGS_DEBUG "${CMAKE_C_FLAGS_DEBUG} -DENABLED_DEBUG_SYNC") > > OPTION(ENABLE_GCOV "Enable gcov (debug, Linux builds only)" OFF) > IF (ENABLE_GCOV) > - MY_CHECK_AND_SET_COMPILER_FLAG("-fprofile-arcs -ftest-coverage -lgcov" DEBUG) > + MY_CHECK_AND_SET_COMPILER_FLAG("--coverage" DEBUG) Why? Since what version does gcc support it? > ENDIF() > > MY_CHECK_AND_SET_COMPILER_FLAG(-ggdb3 DEBUG) > diff --git a/mysql-test/dgcov.pl b/mysql-test/dgcov.pl > index fbc5540e697..47ffaca04ef 100755 > --- a/mysql-test/dgcov.pl > +++ b/mysql-test/dgcov.pl > @@ -68,8 +68,11 @@ if ($opt_purge) > system($cmd)==0 or die "system($cmd): $? $!"; > exit 0; > } > - > +my $gcov_vers= `gcov -v`; > +$gcov_vers=~ s/\D//g; > +$gcov_vers= substr($gcov_vers, 0, 1); my gcov is 9.3.0. gcc version 10 is already out. You get just the first digit, 10 will become 1. Better to something like $gcov_vers ~= s/^.*(\d+\.\d+\.\d+).*$/\1/s; > find(\&gcov_one_file, $root); > +undef $gcov_vers; why? > find(\&write_coverage, $root) if $opt_generate; > exit 0 if $opt_only_gcov; > > @@ -162,7 +165,16 @@ sub gcov_one_file { > } > > # now, read the generated file > - open FH, '<', "$_.gcov" or die "open(<$_.gcov): $!"; > + if($gcov_vers<7) > + { > + open FH, '<', "$_.gcov" or die "open(<$_.gcov): $!"; > + } > + else > + { > + my $f=substr $_, 0, -5; > + open FH, '<', "$f.gcov" or die "open(<$f.gcov): $!"; > + undef $f; > + } I'd rather write it more compact, in two lines like my $f = $gcov_vers < 7 ? $_ : substr $_, 0, -5; open FH, '<', "$f.gcov" or die "open(<$f.gcov): $!"; > my $fname; > while (<FH>) { > chomp; > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0