- developers - lists.mariadb.org

[Maria-developers] Please review MDEV-10030 sql_yacc.yy: Split table_expression and remove PROCEDURE from create_select, select_paren_derived, select_derived2, query_specification
by Alexander Barkov 09 May '16

09 May '16

Hi Sergei, Please review a patch for MDEV-10030, which is a part of MDEV-8909 union parser cleanup. Thanks!

2 4

[Maria-developers] GSoC 2016: Starting blog
by Галина Шалыгина 07 May '16

07 May '16

Hi everyone, I have created blog, where I'm going to write everything about my progress on project "Pushing conditions into non-mergeable views and derived tables in MariaDB". I've already written a post on what I've done, so if you are interested, you can check it out HERE <http://gsocmariadbshagalla.blogspot.ru/>. Best wishes, Galina Shalygina.

1 0

[Maria-developers] Joinable threads
by Sergey Vojtovich 06 May '16

06 May '16

Hi Monty, I vaguely recall you told me once that you like detached threads more than joinable, specifically for service threads. Could you remind me what were the reasons behind it? I need this to fix https://jira.mariadb.org/browse/MDEV-9994 properly. Thanks, Sergey

2 4

Re: [Maria-developers] [MariaDB/server] MDEV-9857: line cache size (to 10.1) (#169)
by Kristian Nielsen 06 May '16

06 May '16

Sergey Vojtovich <notifications(a)github.com> writes: > Since Alexey's patch wasn't yet accepted by MySQL we need his > confirmation that we can take it under BSD-new or MCA > terms. @akopytov, could you confirm? That _really_ is completely ridiculous! So here we have a patch. You will gladly accept it from MySQL/Oracle under GPL v2, without any special conditions. But the very same patch, submitted directly by the original author, you will not take??!? Now, obviously this is not something Sergey Vojtovich invented on his own, I doubt he even agrees personally. So Otto, you specifically asked me to be more vocal about MariaDB Foundation issue. Can we please have your personal opinions on this idea of BSD/contributer agreement for selected potential contributers? I have from the very start of MariaDB been strongly opposed to any form of contributer agreements or reciprocal licensing. Fortunately, we never really had it in practice, since all major contributions (MySQL@Oracle, XtraDB, oqgraph, sphinx, tokudb, galera, ...) were GPL only. But recently we have this message to potential contributers that the MariaDB Foundation "needs" to have BSD or MCA. This is extremely disrespectful for at least two reasons: - There clearly is no such "need", since the Foundation receives GPL rights only to a large part of new contributions to MariaDB - for example from Galera, MySQL@Oracle, Percona. - Selected contributers are except from this requirement. For example, according to my information, large parts of the MariaDB code to which the company SkySQL/MariaDB Corporation has full rights, they are _not_ required to provide BSD/MCA for to the MariaDB Foundation. Otto, I strongly encourage you to look to other successful Free Software projects such as the Linux Kernel, where an important part of the success is the deliberate choice of GPL-only _without_ any reciprocal licensing. I urge you to re-consider, and take a personal stance against this BSD/MCA nonsense, and work strongly for an official GPL-only contributer policy. Meanwhile, I encourage all contributers to _not_ accept this BSD/MCA requirement. If you disagree, be aware that there is no such strong requirement, and the major part of contributions to MariaDB are done without this requirement. If your contribution is accepted, but under condition of BSD/MCA, and you disagree, ask on the mailing list for someone else with commit rights to push your patch. I am sure there are several that will be willing to do so. - Kristian.

3 5

[Maria-developers] MDEV-9712 Performance degradation of nested NULLIF
by Alexander Barkov 05 May '16

05 May '16

Hello Sergei, Please have a look into a draft fix for MDEV-9712. The problem happens because in 10.1 execution time for various recursive stages (like walk, update_used_table and propagate_equal_fields) in NULLIF is O(recursion_level^2), because complexity is doubled on every recursion level when we copy args[0] to args[2]. This fix simplifies some stages to make them faster. It reduced execution time from 144 seconds to 12 seconds. The idea is just not to iterate through args[2] when it's just a copy of args[0] in these stages: - Item_func_nullif::walk - Item_func_nullif::update_used_tables I'm not fully sure that skipping args[2] is always correct though. For some transformers it may not be correct. <offtopic> Btw, walk() can be called from the partitioning code when arg_count is still 2 rather than 3. Looks suspicious. Perhaps there is a hidden bug behind that. </offtopic> The remaining heavy piece of the code is: Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond) { Context cmpctx(ANY_SUBST, cmp.compare_type(), cmp.compare_collation()); args[0]->propagate_equal_fields_and_change_item_tree(thd, cmpctx, cond, &args[0]); args[1]->propagate_equal_fields_and_change_item_tree(thd, cmpctx, cond, &args[1]); args[2]->propagate_equal_fields_and_change_item_tree(thd, Context_identity(), cond, &args[2]); return this; } We cannot use the same approach here, because args[0] and args[2] are propagated with a different context (ANY_SUBST vs IDENTITY_SUBST). Not sure what to do with this. Perhaps, data types could have an extra attribute to tell that ANY_SUBST and IDENITTY_SUBST are equal. So, for example, for non-ZEROFILL integers, ANY_SUBST and IDENTITY_SUBST do the same thing and thus propagation could be done one time only. But this would be a partial solution, for a limited number of data types. Another option is to leave propagation as is. Another option is do nothing at all. If we rewrite the reported query as CASE, it will look heavy indeed. So it's Ok to expect heavy execution complexity. Thanks.

2 3

[Maria-developers] UNION + INTO OUTFILE is confusing
by Alexander Barkov 04 May '16

04 May '16

Hi Sergei, If I run this query: SELECT 1 UNION SELECT 2 INTO OUTFILE 'test.txt'; It creates a file 'test.txt' with this content: 1 2 Looks fine so far. Now if I do "rm test.txt" and execute another query (with parentheses): (SELECT 1) UNION (SELECT 2 INTO OUTFILE 'test.txt'); it still puts the same two records from both UNION parts! This looks confusing, as parentheses a kind of assume that only the right UNION part is to be exported. Exporting only one UNION part is not supported, IIRC. So the above query should probably return a syntax error. But now there is a new trouble. There is no syntax like this: (SELECT ...) UNION (SELECT ...) INTO OUTFILE 'test.txt'; So instead of: (SELECT ...) UNION (SELECT ... INTO OUTFILE 'test.txt'); one will have to write this: SELECT * INTO OUTFILE 'test.txt' FROM ((SELECT ...) UNION (SELECT ...)) AS t1; The latter looks longer, but at least it's not confusing as the former.

2 2

Re: [Maria-developers] [Commits] b1ddc7d: MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops with UNION in ALL subquery
by Sergey Petrunia 04 May '16

04 May '16

Hi Sanja, I have one comment: the patch introduces JOIN::in_optimize which can have values of 1) not initialized and 2) false. This is clearly redundant. Ok to push after the above is addressed. On Sun, Feb 21, 2016 at 10:12:25PM +0100, OleksandrByelkin wrote: > revision-id: b1ddc7d546e6b147838af72dd03f86a8b272fdf0 (mariadb-10.1.11-18-gb1ddc7d) > parent(s): fd8e846a3b049903706267d58e6d8e61eea97df8 > committer: Oleksandr Byelkin > timestamp: 2016-02-21 22:12:25 +0100 > message: > > MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops with UNION in ALL subquery > > Do not mark subquery as inexpensive when it is not optimized. > > --- > mysql-test/r/derived_view.result | 6 +++--- > mysql-test/r/subselect.result | 20 +++++++++++++++++--- > mysql-test/r/subselect_no_exists_to_in.result | 20 +++++++++++++++++--- > mysql-test/r/subselect_no_mat.result | 20 +++++++++++++++++--- > mysql-test/r/subselect_no_opts.result | 20 +++++++++++++++++--- > mysql-test/r/subselect_no_scache.result | 20 +++++++++++++++++--- > mysql-test/r/subselect_no_semijoin.result | 20 +++++++++++++++++--- > mysql-test/r/type_year.result | 1 + > mysql-test/t/subselect.test | 11 +++++++++++ > sql/item_subselect.cc | 20 ++++++++++++++++---- > sql/sql_select.h | 2 ++ > 11 files changed, 135 insertions(+), 25 deletions(-) > > diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result > index 639942f..5783247 100644 > --- a/mysql-test/r/derived_view.result > +++ b/mysql-test/r/derived_view.result > @@ -1101,7 +1101,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 > 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used > Warnings: > -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) > +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where 1 > SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 > WHERE (t.a,t.b) NOT IN (SELECT 7, 5); > a b > @@ -1115,7 +1115,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 > 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used > Warnings: > -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) > +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where 1 > SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 > WHERE (t.a,t.b) NOT IN (SELECT 7, 5); > a b > @@ -1129,7 +1129,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 > 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used > Warnings: > -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) > +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where 1 > DROP VIEW v1; > DROP TABLE t1,t2,t3; > # > diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result > index 75c8597..90d8f9f 100644 > --- a/mysql-test/r/subselect.result > +++ b/mysql-test/r/subselect.result > @@ -6805,7 +6805,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR alias1.a = 'y' > HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) AS field > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > @@ -6817,8 +6819,8 @@ SELECT MAX( alias2.a ) > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index > -1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) > @@ -7133,3 +7135,15 @@ sq > NULL > drop view v2; > drop table t1,t2; > +# > +# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops > +# with UNION in ALL subquery > +# > +SET NAMES utf8; > +CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('foo'); > +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); > +f > +foo > +drop table t1; > +SET NAMES default; > diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result > index e6238af..dcceb61 100644 > --- a/mysql-test/r/subselect_no_exists_to_in.result > +++ b/mysql-test/r/subselect_no_exists_to_in.result > @@ -6805,7 +6805,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR alias1.a = 'y' > HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) AS field > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > @@ -6817,8 +6819,8 @@ SELECT MAX( alias2.a ) > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index > -1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) > @@ -7133,6 +7135,18 @@ sq > NULL > drop view v2; > drop table t1,t2; > +# > +# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops > +# with UNION in ALL subquery > +# > +SET NAMES utf8; > +CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('foo'); > +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); > +f > +foo > +drop table t1; > +SET NAMES default; > set optimizer_switch=default; > select @@optimizer_switch like '%exists_to_in=off%'; > @@optimizer_switch like '%exists_to_in=off%' > diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result > index 70edc64..4e9f750 100644 > --- a/mysql-test/r/subselect_no_mat.result > +++ b/mysql-test/r/subselect_no_mat.result > @@ -6800,7 +6800,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR alias1.a = 'y' > HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) AS field > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > @@ -6812,8 +6814,8 @@ SELECT MAX( alias2.a ) > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index > -1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) > @@ -7126,6 +7128,18 @@ sq > NULL > drop view v2; > drop table t1,t2; > +# > +# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops > +# with UNION in ALL subquery > +# > +SET NAMES utf8; > +CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('foo'); > +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); > +f > +foo > +drop table t1; > +SET NAMES default; > set optimizer_switch=default; > select @@optimizer_switch like '%materialization=on%'; > @@optimizer_switch like '%materialization=on%' > diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result > index c89fd13..4b09fd2 100644 > --- a/mysql-test/r/subselect_no_opts.result > +++ b/mysql-test/r/subselect_no_opts.result > @@ -6796,7 +6796,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR alias1.a = 'y' > HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) AS field > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > @@ -6808,8 +6810,8 @@ SELECT MAX( alias2.a ) > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index > -1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) > @@ -7124,4 +7126,16 @@ sq > NULL > drop view v2; > drop table t1,t2; > +# > +# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops > +# with UNION in ALL subquery > +# > +SET NAMES utf8; > +CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('foo'); > +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); > +f > +foo > +drop table t1; > +SET NAMES default; > set @optimizer_switch_for_subselect_test=null; > diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result > index b12bf21..322e64d 100644 > --- a/mysql-test/r/subselect_no_scache.result > +++ b/mysql-test/r/subselect_no_scache.result > @@ -6811,7 +6811,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR alias1.a = 'y' > HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) AS field > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > @@ -6823,8 +6825,8 @@ SELECT MAX( alias2.a ) > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index > -1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) > @@ -7139,6 +7141,18 @@ sq > NULL > drop view v2; > drop table t1,t2; > +# > +# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops > +# with UNION in ALL subquery > +# > +SET NAMES utf8; > +CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('foo'); > +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); > +f > +foo > +drop table t1; > +SET NAMES default; > set optimizer_switch=default; > select @@optimizer_switch like '%subquery_cache=on%'; > @@optimizer_switch like '%subquery_cache=on%' > diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result > index 54f145d..d380528 100644 > --- a/mysql-test/r/subselect_no_semijoin.result > +++ b/mysql-test/r/subselect_no_semijoin.result > @@ -6796,7 +6796,9 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR alias1.a = 'y' > HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) AS field > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > @@ -6808,8 +6810,8 @@ SELECT MAX( alias2.a ) > FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 > WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index > -1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index > +1 PRIMARY alias1 index a a 19 NULL 11 Using index > +1 PRIMARY alias2 index a a 19 NULL 11 Using where; Using index; Using join buffer (flat, BNL join) > 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where > SELECT MAX( alias2.a ) > @@ -7124,5 +7126,17 @@ sq > NULL > drop view v2; > drop table t1,t2; > +# > +# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops > +# with UNION in ALL subquery > +# > +SET NAMES utf8; > +CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('foo'); > +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); > +f > +foo > +drop table t1; > +SET NAMES default; > set @optimizer_switch_for_subselect_test=null; > set @join_cache_level_for_subselect_test=NULL; > diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result > index 842a16e..204cec2 100644 > --- a/mysql-test/r/type_year.result > +++ b/mysql-test/r/type_year.result > @@ -387,6 +387,7 @@ a > 00 > select a from t1 where a=(select 2000 from dual where 1); > a > +00 > select a from t1 where a=y2k(); > a > 00 > diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test > index a862870..f71c215 100644 > --- a/mysql-test/t/subselect.test > +++ b/mysql-test/t/subselect.test > @@ -5989,3 +5989,14 @@ SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; > > drop view v2; > drop table t1,t2; > + > +--echo # > +--echo # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops > +--echo # with UNION in ALL subquery > +--echo # > +SET NAMES utf8; > +CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('foo'); > +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); > +drop table t1; > +SET NAMES default; > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index d4e3e6c..f45d83d 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -561,22 +561,34 @@ bool Item_subselect::is_expensive() > for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) > { > JOIN *cur_join= sl->join; > + > + /* not optimized subquery */ > if (!cur_join) > - continue; > + return true; > + > + /* very simple subquery */ > + if (!cur_join->tables_list && !sl->first_inner_unit()) > + return false; > + > + /* > + If the subquery is not optimised or in the process of optimization > + it supposed to be expensive > + */ > + if (!cur_join->optimized || cur_join->in_optimize) > + return true; > > /* > Subqueries whose result is known after optimization are not expensive. > Such subqueries have all tables optimized away, thus have no join plan. > */ > - if (cur_join->optimized && > - (cur_join->zero_result_cause || !cur_join->tables_list)) > + if ((cur_join->zero_result_cause || !cur_join->tables_list)) > return false; > > /* > If a subquery is not optimized we cannot estimate its cost. A subquery is > considered optimized if it has a join plan. > */ > - if (!(cur_join->optimized && cur_join->join_tab)) > + if (!cur_join->join_tab) > return true; > > if (sl->first_inner_unit()) > diff --git a/sql/sql_select.h b/sql/sql_select.h > index 9f90473..e57844fd 100644 > --- a/sql/sql_select.h > +++ b/sql/sql_select.h > @@ -1292,6 +1292,7 @@ class JOIN :public Sql_alloc > OPTIMIZATION_IN_PROGRESS=1, > OPTIMIZATION_DONE=2}; > bool optimized; ///< flag to avoid double optimization in EXPLAIN > + bool in_optimize; > bool initialized; ///< flag to avoid double init_execution calls > > Explain_select *explain; > @@ -1380,6 +1381,7 @@ class JOIN :public Sql_alloc > ref_pointer_array_size= 0; > zero_result_cause= 0; > optimized= 0; > + in_optimize= 0; > have_query_plan= QEP_NOT_PRESENT_YET; > initialized= 0; > cleaned= 0; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

3 2

Re: [Maria-developers] c75cbc1: MDEV-717 LP:1003679 - Wrong binlog order on concurrent DROP schema and
by Sergei Golubchik 04 May '16

04 May '16

Hi, Alexey! convention below: when I start a comment with "btw" it means that you don't need to change anything, it's just a thought I had when looking at that line in the patch. On May 03, Alexey Botchkov wrote: > revision-id: c75cbc19806223be8e750c27ba0bd4b17ef61f54 (mariadb-10.1.13-24-gc75cbc1) > parent(s): 94cd0f6c9b3b04db67501ef29d470f32527ceda2 > committer: Alexey Botchkov > timestamp: 2016-05-03 13:20:12 +0400 > message: > > MDEV-717 LP:1003679 - Wrong binlog order on concurrent DROP schema and > CREATE function. > > Test case added. That required setting some DEBUG_SYNC points. > > diff --git a/mysql-test/suite/binlog/t/binlog_mdev717.test b/mysql-test/suite/binlog/t/binlog_mdev717.test > new file mode 100644 > index 0000000..5e8cce6 > --- /dev/null > +++ b/mysql-test/suite/binlog/t/binlog_mdev717.test > @@ -0,0 +1,49 @@ > +# MDEV-717 LP:1003679 - Wrong binlog order on concurrent DROP schema and CREATE function. > + > +--source include/have_log_bin.inc > +RESET MASTER; > + > +disable_warnings; > +DROP DATABASE IF EXISTS mysqltest; > +enable_warnings; btw, generally this isn't necessary anymore, after-test checks verify that every test cleans up after itself. > +connect(con1,localhost,root,,); btw, this works also without extra commas: connect(con1,localhost,root); > +connection default; > + > +CREATE DATABASE mysqltest; > +SET DEBUG_SYNC= "wait_drop_db_name_locked SIGNAL locked WAIT_FOR release"; > +--send DROP DATABASE mysqltest; > +connection con1; > +SET DEBUG_SYNC= "now WAIT_FOR locked"; Hm... If the default connection signals "locked" before you start waiting for it now - will your test still work? You can test it by adding a sleep right after "connection con1". (don't commit the sleep, of course, it's just to way to verify whether the test case is valid) Alternatively, in many places tests wait for a connection to reach the certain debug_sync point with, like: let $wait_condition= select count(*) = 1 from information_schema.processlist where state like "debug sync point: wait_drop_db_name_locked%"; source include/wait_condition.inc; > +SET DEBUG_SYNC= "sp_create_routine_started SIGNAL release"; > +--error 0,ER_BAD_DB_ERROR why? Do you mean that even with your sync points the specific execution order if not guaranteed? > +CREATE FUNCTION mysqltest.f1() RETURNS INT RETURN 1; > +connection default; > +--reap > + > +CREATE DATABASE mysqltest; > +SET DEBUG_SYNC= "wait_drop_db_name_locked SIGNAL locked1 WAIT_FOR release1"; > +--send DROP DATABASE mysqltest; > +connection con1; > +SET DEBUG_SYNC= "now WAIT_FOR locked1"; > +SET DEBUG_SYNC= "create_event_started SIGNAL release1"; > +--error 0,ER_BAD_DB_ERROR > +CREATE EVENT mysqltest.e1 ON SCHEDULE EVERY 15 MINUTE DO BEGIN END; > +connection default; > +--reap > + > +CREATE DATABASE mysqltest; > +CREATE EVENT mysqltest.e1 ON SCHEDULE EVERY 15 MINUTE DO BEGIN END; > +SET DEBUG_SYNC= "wait_drop_db_name_locked SIGNAL locked1 WAIT_FOR release1"; > +--send DROP DATABASE mysqltest; > +connection con1; > +SET DEBUG_SYNC= "now WAIT_FOR locked1"; > +SET DEBUG_SYNC= "update_event_started SIGNAL release1"; > +--error 0,ER_BAD_DB_ERROR > +ALTER EVENT mysqltest.e1 ON SCHEDULE EVERY 20 MINUTE DO BEGIN END; > +connection default; > +--reap > + > +SET DEBUG_SYNC= "RESET"; > +--source include/show_binlog_events.inc > + > diff --git a/sql/events.cc b/sql/events.cc > index 77dbb8b..3c50d6e 100644 > diff --git a/sql/sp.cc b/sql/sp.cc > index a518b52..c2e3fd2 100644 > --- a/sql/sp.cc > +++ b/sql/sp.cc > @@ -1043,6 +1043,8 @@ sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) > DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || > type == TYPE_ENUM_FUNCTION); > > + DEBUG_SYNC(thd, "sp_create_routine_started"); > + could you use "before_wait_locked_pname" sync point instead? It's at the beginning of lock_object_name(). > /* Grab an exclusive MDL lock. */ > if (lock_object_name(thd, mdl_type, sp->m_db.str, sp->m_name.str)) > { > diff --git a/sql/sql_db.cc b/sql/sql_db.cc > index 2ba67cb..fdb49f2 100644 > --- a/sql/sql_db.cc > +++ b/sql/sql_db.cc > @@ -837,6 +837,8 @@ mysql_rm_db_internal(THD *thd,char *db, bool if_exists, bool silent) > if (lock_schema_name(thd, dbnorm)) > DBUG_RETURN(true); > > + DEBUG_SYNC(thd, "wait_drop_db_name_locked"); > + Could you use "after_wait_locked_schema_name" sync point instead? It's the one at the end of lock_schema_name(). > length= build_table_filename(path, sizeof(path) - 1, db, "", "", 0); > strmov(path+length, MY_DB_OPT_FILE); // Append db option file name > del_dbopt(path); // Remove dboption hash entry Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 3

Re: [Maria-developers] [Commits] a6fb98f: MDEV-6368: assertion xid_seqno > trx_sys_cur_xid_seqno
by Jan Lindström 03 May '16

03 May '16

Hi Nirbhay, I'm not expert on this code area but some questions, comments below. On Tue, May 3, 2016 at 1:26 AM, Nirbhay Choubey <nirbhay(a)mariadb.com> wrote: > revision-id: a6fb98fd74ab6f14fab0c1ef4630b010ff28880f > (mariadb-10.1.13-6-ga6fb98f) > parent(s): f8cdb9e7e8fb692f8eb3b9bb4792cd60653e0eb8 > author: Nirbhay Choubey > committer: Nirbhay Choubey > timestamp: 2016-05-02 18:26:58 -0400 > message: > > MDEV-6368: assertion xid_seqno > trx_sys_cur_xid_seqno > > - Validate the specified wsrep_start_position value by also > checking the return status of wsrep->sst_received. This also > ensures that changes in wsrep_start_position is not allowed > when the node is not in JOINING state. > - Do not allow decrease in seqno within same UUID. > - The initial checkpoint in SEs should be [0...:-1]. > > > diff --git a/sql/wsrep_sst.cc b/sql/wsrep_sst.cc > index 4f6cc51..44fdd03 100644 > --- a/sql/wsrep_sst.cc > +++ b/sql/wsrep_sst.cc > @@ -264,42 +264,91 @@ void wsrep_sst_complete (const wsrep_uuid_t* > sst_uuid, > mysql_mutex_unlock (&LOCK_wsrep_sst); > } > > -void wsrep_sst_received (wsrep_t* const wsrep, > +/* > + If wsrep provider is loaded, inform that the new state snapshot > + has been received. Also update the local checkpoint. > + > + @param wsrep [IN] wsrep handle > + @param uuid [IN] Initial state UUID > + @param seqno [IN] Initial state sequence number > + @param state [IN] Always NULL, also ignored by wsrep > provider (?) > + @param state_len [IN] Always 0, also ignored by wsrep > provider (?) > + @param implicit [IN] Whether invoked implicitly due to > SST > + (true) or explicitly because if > change > + in wsrep_start_position by user > (false). > + @return false Success > + true Error > + > +*/ > +bool wsrep_sst_received (wsrep_t* const wsrep, > const wsrep_uuid_t& uuid, > wsrep_seqno_t const seqno, > const void* const state, > - size_t const state_len) > Why not const size_t state_len ? Use of const here is confusing. > + size_t const state_len, > + bool const implicit) > { > - wsrep_get_SE_checkpoint(local_uuid, local_seqno); > + /* > + To keep track of whether the local uuid:seqno should be updated. > Also, note > + that local state (uuid:seqno) is updated/checkpointed only after we > get an > + OK from wsrep provider. By doing so, the values remain consistent > across > + the server & wsrep provider. > + */ > + bool do_update= false; > + > + // Get the locally stored uuid:seqno. > + wsrep_get_SE_checkpoint(local_uuid, local_seqno); > What if this fails, no error handling ? > > - if (memcmp(&local_uuid, &uuid, sizeof(wsrep_uuid_t)) || > - local_seqno < seqno || seqno < 0) > + if (memcmp(&local_uuid, &uuid, sizeof(wsrep_uuid_t)) || > + local_seqno < seqno) > + { > + do_update= true; > + } > + else if (local_seqno > seqno) > + { > + WSREP_WARN("SST position can't be set in past. Requested: %lld, > Current: " > + " %lld.", (long long)seqno, (long long)local_seqno); > + /* > + If we are here because of SET command, simply return true (error) > instead of > + aborting. > + */ > + if (implicit) > { > - wsrep_set_SE_checkpoint(uuid, seqno); > - local_uuid = uuid; > - local_seqno = seqno; > + WSREP_WARN("Can't continue."); > + unireg_abort(1); > } > - else if (local_seqno > seqno) > + else > { > - WSREP_WARN("SST postion is in the past: %lld, current: %lld. " > - "Can't continue.", > - (long long)seqno, (long long)local_seqno); > - unireg_abort(1); > + return true; > } > + } > > #ifdef GTID_SUPPORT > - wsrep_init_sidno(uuid); > + wsrep_init_sidno(uuid); > #endif /* GTID_SUPPORT */ > > - if (wsrep) > - { > - int const rcode(seqno < 0 ? seqno : 0); > - wsrep_gtid_t const state_id = { > - uuid, (rcode ? WSREP_SEQNO_UNDEFINED : seqno) > - }; > + if (wsrep) > + { > + int const rcode(seqno < 0 ? seqno : 0); > When seqno can be < 0 ? > + wsrep_gtid_t const state_id= {uuid, > + (rcode ? WSREP_SEQNO_UNDEFINED : seqno)}; > + > + wsrep_status_t ret= wsrep->sst_received(wsrep, &state_id, state, > + state_len, rcode); > > - wsrep->sst_received(wsrep, &state_id, state, state_len, rcode); > + if (ret != WSREP_OK) > + { > + return true; > } > + } > + > + // Now is the good time to update the local state and checkpoint. > + if (do_update) { > + wsrep_set_SE_checkpoint(uuid, seqno); > Again, I would add error handling. > + local_uuid= uuid; > + local_seqno= seqno; > + } > + > + return false; > } > > // Let applier threads to continue > @@ -308,7 +357,7 @@ void wsrep_sst_continue () > if (sst_needed) > { > WSREP_INFO("Signalling provider to continue."); > - wsrep_sst_received (wsrep, local_uuid, local_seqno, NULL, 0); > + wsrep_sst_received (wsrep, local_uuid, local_seqno, NULL, 0, true); > You should check the return code. > } > } > > diff --git a/sql/wsrep_var.cc b/sql/wsrep_var.cc > index 9c01e54..bd94190 100644 > > > bool wsrep_start_position_update (sys_var *self, THD* thd, enum_var_type > type) > { > - WSREP_INFO ("wsrep_start_position var submitted: '%s'", > - wsrep_start_position); > - // since this value passed wsrep_start_position_check, don't check > anything > - // here > - wsrep_set_local_position (wsrep_start_position, true); > - return 0; > + // Print a confirmation that wsrep_start_position has been updated. > + WSREP_INFO ("wsrep_start_position set to '%s'", wsrep_start_position); > + return false; > Hmm, normally I thought that system variable set would go like if (variable_check()) variable_update() but now update does not do nothing ? > } > void wsrep_start_position_init (const char* val) > @@ -164,7 +194,7 @@ void wsrep_start_position_init (const char* val) > return; > } > > - wsrep_set_local_position (val, false); > + wsrep_set_local_position (val, strlen(val), false); > Add error handling. > } > > static bool refresh_provider_options() > diff --git a/storage/innobase/trx/trx0sys.cc > b/storage/innobase/trx/trx0sys.cc > index 76c8613..1625e8d 100644 > --- a/storage/innobase/trx/trx0sys.cc > +++ b/storage/innobase/trx/trx0sys.cc > @@ -349,10 +349,10 @@ void read_wsrep_xid_uuid(const XID* xid, unsigned > char* buf) > unsigned char xid_uuid[16]; > long long xid_seqno = read_wsrep_xid_seqno(xid); > read_wsrep_xid_uuid(xid, xid_uuid); > - if (!memcmp(xid_uuid, trx_sys_cur_xid_uuid, 8)) > + if (!memcmp(xid_uuid, trx_sys_cur_xid_uuid, 16) && > + (xid_seqno > -1)) > Does this mean that you ignore uuid if it is exactly the same, or is there error handling on else? R: Jan

2 1

[Maria-developers] GSoC 2016 GTID support for MariaDB MHA
by Charles Dirk 03 May '16

03 May '16

Hi everyone, I have created a wordpress blog HERE <https://mariadbmhagtidgsoc2016.wordpress.com/> on which I will post regularly about my progress with this project. Feel free to check it out. Cheers, Charles.

1 0