- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] Rev 3497: MDEV-537 Make multi-column non-top level subqueries to be executed via index (index/unique subquery) in file:///home/tsk/mprog/src/10.0-md537-merge/
by Sergei Petrunia 18 Feb '13

18 Feb '13

Hi Timour, With the below patch, conditions are unnecessarily wraped into two Item_func_trigcond objects. See EXPLAIN EXTENDED ouput in .result files below - one object is wrapping the other. Debugging shows that they are triggered by the same flag get_cond_guard(i). Two wrappings are just a few code lines away, so it should be totally feasible to get rid of double wrapping. On Thu, Feb 07, 2013 at 01:33:35PM +0000, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/10.0-md537-merge/ > > ------------------------------------------------------------ > revno: 3497 > revision-id: timour(a)askmonty.org-20130207133324-qrcjga4z9heaezb9 > parent: sergii(a)pisem.net-20130205094326-4yeuozh5iemlqley > fixes bug(s): https://mariadb.atlassian.net/browse/MDEV-537 > committer: timour(a)askmonty.org > branch nick: 10.0-md537-merge > timestamp: Thu 2013-02-07 15:33:24 +0200 > message: > MDEV-537 Make multi-column non-top level subqueries to be executed via index (index/unique subquery) > instead of single_select_engine > > This task changes the IN-EXISTS rewrite for multi-column subqueries > "(a, b) IN (select b, c ...)" to work in the same way as for > single-column subqueries "a IN (select b ...) with respect to the > injection of NULL-rejecting predicates. > > More specifically, the method > Item_in_subselect::create_row_in_to_exists_cond() > adds Item_is_not_null_test and Item_func_trig_cond only if the left > IN operand can be NULL. Not having these predicates when not necessary, > makes it possible to rewrite the subquery into a "unique_subquery" or > "index_subquery" when there is a suitable index on the only > subquery table. > === modified file 'mysql-test/r/derived_view.result' > --- a/mysql-test/r/derived_view.result 2013-01-15 18:07:46 +0000 > +++ b/mysql-test/r/derived_view.result 2013-02-07 13:33:24 +0000 > @@ -1468,14 +1468,14 @@ WHERE (t2.a ,t1.b) NOT IN (SELECT DISTIN > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 > 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) > -2 DEPENDENT SUBQUERY t eq_ref PRIMARY,c PRIMARY 4 func 1 Using where > +2 DEPENDENT SUBQUERY t unique_subquery PRIMARY,c PRIMARY 4 func 1 Using where > EXPLAIN > SELECT * FROM t1 , t2 > WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 > 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) > -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,c PRIMARY 4 func 1 Using where > +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,c PRIMARY 4 func 1 Using where > SELECT * FROM t1 , t2 > WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t); > b a > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2012-12-17 00:49:19 +0000 > +++ b/mysql-test/r/subselect.result 2013-02-07 13:33:24 +0000 > @@ -2966,7 +2966,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > @@ -2979,7 +2979,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > DROP TABLE t1,t2; > set optimizer_switch=@tmp11867_optimizer_switch; > CREATE TABLE t1 (a char(5), b char(5)); > > === modified file 'mysql-test/r/subselect3.result' > --- a/mysql-test/r/subselect3.result 2012-10-25 12:50:10 +0000 > +++ b/mysql-test/r/subselect3.result 2013-02-07 13:33:24 +0000 > @@ -252,7 +252,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))))) AS `Z` from `test`.`t2` > +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))))) AS `Z` from `test`.`t2` > select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; > a b oref Z > NULL 1 100 0 > @@ -269,7 +269,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join) > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` > +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` > select a,b, oref, > (a,b) in (select a,b from t1,t4 where c=t2.oref) Z > from t2; > @@ -314,7 +314,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10)) > +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10)) > drop table t1, t2; > create table t1 (oref char(4), grp int, ie int); > insert into t1 (oref, grp, ie) values > @@ -584,7 +584,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` > +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` > drop table t1,t2; > create table t1 (oref char(4), grp int, ie int primary key); > insert into t1 (oref, grp, ie) values > > === modified file 'mysql-test/r/subselect3_jcl6.result' > --- a/mysql-test/r/subselect3_jcl6.result 2012-10-25 12:50:10 +0000 > +++ b/mysql-test/r/subselect3_jcl6.result 2013-02-07 13:33:24 +0000 > @@ -262,7 +262,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))))) AS `Z` from `test`.`t2` > +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))))) AS `Z` from `test`.`t2` > select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; > a b oref Z > NULL 1 100 0 > @@ -279,7 +279,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join) > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` > +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` > select a,b, oref, > (a,b) in (select a,b from t1,t4 where c=t2.oref) Z > from t2; > @@ -324,7 +324,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10)) > +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10)) > drop table t1, t2; > create table t1 (oref char(4), grp int, ie int); > insert into t1 (oref, grp, ie) values > @@ -594,7 +594,7 @@ id select_type table type possible_keys > 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key > Warnings: > Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 > -Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` > +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`))))))) AS `Z` from `test`.`t2` > drop table t1,t2; > create table t1 (oref char(4), grp int, ie int primary key); > insert into t1 (oref, grp, ie) values > > === modified file 'mysql-test/r/subselect4.result' > --- a/mysql-test/r/subselect4.result 2013-01-29 14:10:47 +0000 > +++ b/mysql-test/r/subselect4.result 2013-02-07 13:33:24 +0000 > @@ -303,7 +303,7 @@ EXPLAIN > SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where > -2 DEPENDENT SUBQUERY t2a eq_ref PRIMARY PRIMARY 8 const,test.t1.pk 1 Using where; Using index; Full scan on NULL key > +2 DEPENDENT SUBQUERY t2a unique_subquery PRIMARY PRIMARY 8 const,test.t1.pk 1 Using index; Using where; Full scan on NULL key > SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); > pk i > 0 10 > @@ -335,7 +335,7 @@ EXPLAIN > SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where > -2 DEPENDENT SUBQUERY t2c ref it2c it2c 8 const,test.t1.pk 2 Using where; Using index; Full scan on NULL key > +2 DEPENDENT SUBQUERY t2c index_subquery it2c it2c 8 const,test.t1.pk 2 Using index; Using where; Full scan on NULL key > SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); > pk i > 0 10 > > === modified file 'mysql-test/r/subselect_mat_cost.result' > --- a/mysql-test/r/subselect_mat_cost.result 2012-12-20 18:58:40 +0000 > +++ b/mysql-test/r/subselect_mat_cost.result 2013-02-07 13:33:24 +0000 > @@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country > AND Language IN ('English','Spanish'); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan > -2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition; Using where > +2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition > 2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index > select count(*) > from CountryLanguage > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2012-12-17 00:49:19 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2013-02-07 13:33:24 +0000 > @@ -2973,7 +2973,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > @@ -2985,7 +2985,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` > DROP TABLE t1,t2; > set optimizer_switch=@tmp11867_optimizer_switch; > CREATE TABLE t1 (a char(5), b char(5)); > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2012-12-17 00:49:19 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2013-02-07 13:33:24 +0000 > @@ -2969,7 +2969,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where > @@ -2981,7 +2981,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > DROP TABLE t1,t2; > set optimizer_switch=@tmp11867_optimizer_switch; > CREATE TABLE t1 (a char(5), b char(5)); > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2012-12-17 00:49:19 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2013-02-07 13:33:24 +0000 > @@ -2972,7 +2972,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > @@ -2985,7 +2985,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` > DROP TABLE t1,t2; > set optimizer_switch=@tmp11867_optimizer_switch; > CREATE TABLE t1 (a char(5), b char(5)); > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2013-01-29 14:10:47 +0000 > +++ b/sql/item_subselect.cc 2013-02-07 13:33:24 +0000 > @@ -2306,7 +2306,7 @@ Item_in_subselect::create_row_in_to_exis > ref_pointer_array+i, > (char *)"<no matter>", > (char *)"<list ref>")); > - if (!abort_on_null) > + if (!abort_on_null && select_lex->ref_pointer_array[i]->maybe_null) > { > Item *having_col_item= > new Item_is_not_null_test(this, > @@ -2325,10 +2325,6 @@ Item_in_subselect::create_row_in_to_exis > (char *)"<no matter>", > (char *)"<list ref>")); > item= new Item_cond_or(item, item_isnull); > - /* > - TODO: why we create the above for cases where the right part > - cant be NULL? > - */ > if (left_expr->element_index(i)->maybe_null) > { > if (!(item= new Item_func_trig_cond(item, get_cond_guard(i)))) > @@ -2339,6 +2335,11 @@ Item_in_subselect::create_row_in_to_exis > } > *having_item= and_items(*having_item, having_col_item); > } > + if (!abort_on_null && left_expr->element_index(i)->maybe_null) > + { > + if (!(item= new Item_func_trig_cond(item, get_cond_guard(i)))) > + DBUG_RETURN(true); > + } > *where_item= and_items(*where_item, item); > } > } > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Idea for parallel replication with statement-based binlogging
by Kristian Nielsen 17 Feb '13

17 Feb '13

Hi Xiaobin, I hope you are enjoying the Spring Festival! All the best wishes for you and your family for the coming year. I had an idea for your parallel replication work, inspired by my work on in-order commit. I wanted to hear your opinion on it, if you are interested. The idea is to make your patch work with statement-based replication. For row-based InnoDB events, you hash all the unique keys to check for conflicts, and if no conflict you can run transactions in parallel even against the same table. For MyISAM, I get deadlock issues in my in-order-commit patch, if I try to replicate in parallel against the same table. So I hash just the table names, so that two transactions on the same table always conflict. Then I can still replicate in parallel events against different tables. Now suppose that for statement-based events, we obtain the names of all tables used in the queries. We could add a new event to the binlog with the table names, or perhaps parse the statement on the slave, if that is not too expensive. Then we could hash just the names to check for conflicts, just like I did for MyISAM with row-based events. It seems to me that we could then safely replicate statement-based events in parallel on the slave if they are to different tables. This would be great for people that do not want to use row-based replication, it would surely be better than the MySQL 5.6 multi-threaded slave which can only do queries against different databases in parallel. What do you think? - Kristian.

2 1

[Maria-developers] implementing index condition pushdown in MariaDB 5.5
by Zardosht Kasheff 16 Feb '13

16 Feb '13

Hello, Is there any documentation for what a storage engine needs to do to implement index condition pushdown in MariaDB 5.5? I see some related things, such as handler_index_cond_check, HA_DO_INDEX_COND_PUSHDOWN, and idx_cond_push, but I don't understand how these all interact with each other. Thanks -Zardosht

2 6

[Maria-developers] Help with running a transaction inside the server
by Kristian Nielsen 14 Feb '13

14 Feb '13

Hi Serg, Can you give me a tip for doing the following (for MDEV-26, global transaction ID): I need to run a transaction inside the server. This is in the slave I/O thread, when it connects to the master it receives the current replication position as a GTID, and I want to store it in the mysql.rpl_slave_state so that next reconnect of the slave can use the GTID automatically. So I basically need to run the equivalent of something like this: BEGIN; INSERT INTO mysql.rpl_slave_state VALUES (...); DELETE FROM mysql.rpl_slave_state WHERE id=...; COMMIT; I think I already have everything except the "BEGIN" part (if you want, you can check the code in rpl_slave_state::record_gtid() in my branch lp:~maria-captains/maria/10.0-mdev26/ on Launchpad). So how do I do the equivalent of BEGIN inside the server code? Can you think of any existing code I could use as example? - Kristian.

3 7

[Maria-developers] Fwd: debugging how query plans are made
by Zardosht Kasheff 12 Feb '13

12 Feb '13

Is there something similar in MariaDB? ---------- Forwarded message ---------- From: Øystein Grøvlen <oystein.grovlen(a)oracle.com> Date: Tue, Feb 12, 2013 at 9:11 AM Subject: Re: debugging how query plans are made To: Zardosht Kasheff <zardosht(a)gmail.com> Cc: "internals(a)lists.mysql.com" <internals(a)lists.mysql.com> On 02/12/13 02:54 PM, Zardosht Kasheff wrote: > > Hello all, > > In MySQL and/or MariaDB, are there any hooks or methods one can use to > learn why a query plan is chosen over alternatives? We sometimes > notice that a different query plan is chosen for us than InnoDB (which > may be fine, given we are a different storage engine), and we would > like to better understand why. Is there any debug parameter that gets > MySQL to output decisions made in the optimizer? MySQL 5.6 has optimizer tracing which outputs the decisions made by the optimizer. See http://dev.mysql.com/doc/internals/en/optimizer-tracing.html Hope this helps, -- Øystein

2 1

Re: [Maria-developers] Files under /etc/my.cnf.d/ read by default
by Sergei Golubchik 12 Feb '13

12 Feb '13

Hi, Honza! On Feb 05, Honza Horak wrote: > Hi guys, > > while we were packaging MariaDB into Fedora (finally) we used the > upstream's cnf files structure, which means /etc/my.cnf, that includes > all files from directory /etc/my.cnf.d/* using statement !includedir. > > That works fine when user installs (not updates) the MariaDB packages > without having them installed before. The problem is if user has changed > /etc/my.cnf before updating -- then /etc/my.cnf doesn't get updated > (!includedir won't be added) and we end with files under /etc/my.cnf.d/* > but nothing what includes them. That is quite confusing for users that > may change something in /etc/my.cnf.d/client.cnf but nothing happens. Agree. Especially as we've started to use /etc/my.cnf.d/ now - e.g. CassandraSE comes in a separate rpm, that (besides the plugin itself) drops a file into /etc/my.cnf.d/ to enable the engine in the server. I expect that more separately-packed plugins will start doing the same. > Since files under /etc/my.cnf.d/* are empty by default it shouldn't do > any harm if all files corresponding with /etc/my.cnf.d/*.cnf template > get loaded even without !includedir in /etc/my.cnf. Actually I believe > that is what users expect to happen when they see files under > /etc/my.cnf.d.. > > So, that's basically what I'm proposing -- read files under > /etc/my.cnf.d/* by default, without a need to use !includedir. What do > you think? What if we add !includedir to the /etc/my.cnf from the post-inst script? Something like grep -q '!includedir /etc/my.cnf.d' /etc/my.cnf || \ (echo; echo '!includedir /etc/my.cnf.d') >> /etc/my.cnf Would that be enough? Regards, Sergei

2 1

[Maria-developers] No Doxygen?
by Christian Convey 11 Feb '13

11 Feb '13

I was kind of surprised to not find anything in MariaDB's build system for using Doxygen to create HTML docs. Have people decided this isn't a worthwhile thing to do? Or has just no one gotten around to it yet? - Christian

3 4

Re: [Maria-developers] Question
by Michael Widenius 10 Feb '13

10 Feb '13

Hi! First, the MariaDB developers list is the right place to ask questions like this. >>>>> "Aamir" == Aamir Abbas <Abbas> writes: Aamir> Hi Monty, Aamir> I have researched this question about MySQL without luck and if you cannot answer it then nobody can. On some MySQL servers the version number returns a "-log" suffixed to the version number. For example, 5.0.45-log. What does the "-log" mean or signify? This is not a standard version number as far as I can tell. Should it not simply return 5.0.45? I have posted the question to a couple of forums but nobody's able to answer. I have just updated the documentation at https://kb.askmonty.org/en/version/ to answer this questions in detail. Here is a copy of the new documentation: The VERSION() string may have one or more of the following suffixes: Suffix Description -embedded The server is an embedded server (libmysqld). -log General logging, slow logging or binary (replication) logging is enabled. -debug The server is compiled for debugging. -valgrind The server is compiled to be instrumented with valgrind. Regards, Monty

1 0

[Maria-developers] Coverity scan results
by Christian Convey 07 Feb '13

07 Feb '13

Hey guys, For those of you who missed it, I volunteered to try gettin MariaDB scanned as part of the Coverity Scan service. I just got the trunk scanned for the first time, and a lot of potential-problem reports came up. I did a spot check on one of them, and the report looks like a true-positive. Coverity Scan reports: 178 high-impact problems 1020 medium-impact problems 47 low-impact problems. Any suggestions for how to get these looked at by the appropriate developers? It seems crazy for me to enter 1000+ bug reports into the bug tracker. I believe I have the power to create new user accounts in Coverity Scan's MariaDB project, for any MariaDB developer who wants to see the results. Thanks, Christian

3 12

Re: [Maria-developers] Fwd: [Commits] Rev 3622: Fix for bug MDEV-765 (LP:825075) in file:///home/tsk/mprog/src/5.3-md765/
by Sergei Petrunia 07 Feb '13

07 Feb '13

Hi Timour, On Mon, Feb 04, 2013 at 05:39:02PM +0200, Timour Katchaounov wrote: > > Sergey, > > Could you please review the following patch. The fix implements > your suggestion from your previous review: > https://lists.launchpad.net/maria-developers/msg04597.html > Ok to push. > Timour > > ------------------------------------------------------------ > revno: 3622 > revision-id: timour(a)askmonty.org-20130204153548-njv08hcdskv6ttjk > parent: sergii(a)pisem.net-20130128081223-mp9rsd3t9soz8lly > fixes bug: https://mariadb.atlassian.net/browse/MDEV-765 > committer: timour(a)askmonty.org > branch nick: 5.3-md765 > timestamp: Mon 2013-02-04 17:35:48 +0200 > message: > Fix for bug MDEV-765 (LP:825075) > > Analys: > The cause for the wrong result was that the optimizer > incorrectly chose min/max loose scan when it is not > applicable. The applicability test missed the case when > a condition on the MIN/MAX argument was OR-ed with a > condition on some other field. In this case, the MIN/MAX > condition cannot be used for loose scan. > > Solution: > Extend the test check_group_min_max_predicates() to check > that the WHERE clause is of the form: "cond1 AND cond2" > where > cond1 - does not use min_max_column at all. > cond2 - is an AND/OR tree with leaves in form "min_max_column $CMP$ const" > or $CMP$ is one of the functions between, is [not] null > > > === modified file 'mysql-test/r/group_min_max.result' > --- a/mysql-test/r/group_min_max.result 2013-01-28 08:12:23 +0000 > +++ b/mysql-test/r/group_min_max.result 2013-02-04 15:35:48 +0000 > @@ -3186,3 +3186,61 @@ a b > 109 19 > drop table t1; > End of 5.1 tests > +# > +# MDEV-765: LP:825075 - Wrong result with GROUP BY + multipart key + MIN/MAX loose scan > +# > +CREATE TABLE t1 (a varchar(1), b varchar(1), KEY (b,a)); > +INSERT INTO t1 VALUES > +('0',NULL),('9',NULL),('8','c'),('4','d'),('7','d'),(NULL,'f'), > +('7','f'),('8','g'),(NULL,'j'); > +explain > +SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index > +SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b; > +max(a) b > +NULL f > +NULL j > +explain > +SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index > +SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b; > +b min(a) > +d 7 > +f 7 > +explain > +SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index > +SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b; > +b min(a) > +NULL 0 > +d 4 > +explain > +SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ref b b 4 const 1 Using where; Using index > +SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b; > +b min(a) > +explain > +SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 index NULL b 8 NULL 9 Using where; Using index > +SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b; > +b min(a) > +d 7 > +f 7 > +explain > +SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index > +SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b; > +b min(a) > +NULL 9 > +c 8 > +d 4 > +f 7 > +g 8 > +drop table t1; > +End of 5.3 tests > > === modified file 'mysql-test/t/group_min_max.test' > --- a/mysql-test/t/group_min_max.test 2013-01-28 08:12:23 +0000 > +++ b/mysql-test/t/group_min_max.test 2013-02-04 15:35:48 +0000 > @@ -1203,3 +1203,40 @@ WHERE EXISTS ( SELECT DISTINCT b FROM t1 > drop table t1; > > --echo End of 5.1 tests > + > +--echo # > +--echo # MDEV-765: LP:825075 - Wrong result with GROUP BY + multipart key + MIN/MAX loose scan > +--echo # > + > +CREATE TABLE t1 (a varchar(1), b varchar(1), KEY (b,a)); > +INSERT INTO t1 VALUES > +('0',NULL),('9',NULL),('8','c'),('4','d'),('7','d'),(NULL,'f'), > +('7','f'),('8','g'),(NULL,'j'); > + > +explain > +SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b; > +SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b; > + > +explain > +SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b; > +SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b; > + > +explain > +SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b; > +SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b; > + > +explain > +SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b; > +SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b; > + > +explain > +SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b; > +SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b; > + > +explain > +SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b; > +SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b; > + > +drop table t1; > + > +--echo End of 5.3 tests > > === modified file 'sql/opt_range.cc' > --- a/sql/opt_range.cc 2013-01-28 08:12:23 +0000 > +++ b/sql/opt_range.cc 2013-02-04 15:35:48 +0000 > @@ -11485,7 +11485,8 @@ static bool get_constant_key_infix(KEY * > KEY_PART_INFO **first_non_infix_part); > static bool > check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item, > - Field::imagetype image_type); > + Field::imagetype image_type, > + bool *has_min_max_fld, bool *has_other_fld); > > static void > cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, > @@ -12005,10 +12006,12 @@ get_best_group_min_max(PARAM *param, SEL > DBUG_RETURN(NULL); > > /* Check (SA3) for the where clause. */ > + bool has_min_max_fld= false, has_other_fld= false; > if (join->conds && min_max_arg_item && > !check_group_min_max_predicates(join->conds, min_max_arg_item, > (index_info->flags & HA_SPATIAL) ? > - Field::itMBR : Field::itRAW)) > + Field::itMBR : Field::itRAW, > + &has_min_max_fld, &has_other_fld)) > DBUG_RETURN(NULL); > > /* The query passes all tests, so construct a new TRP object. */ > @@ -12043,16 +12046,24 @@ get_best_group_min_max(PARAM *param, SEL > > SYNOPSIS > check_group_min_max_predicates() > - cond tree (or subtree) describing all or part of the WHERE > - clause being analyzed > - min_max_arg_item the field referenced by the MIN/MAX function(s) > - min_max_arg_part the keypart of the MIN/MAX argument if any > + cond [in] the expression tree being analyzed > + min_max_arg [in] the field referenced by the MIN/MAX function(s) > + image_type [in] > + has_min_max_arg [out] true if the subtree being analyzed references min_max_arg > + has_other_arg [out] true if the subtree being analyzed references a column > + other min_max_arg > > DESCRIPTION > The function walks recursively over the cond tree representing a WHERE > clause, and checks condition (SA3) - if a field is referenced by a MIN/MAX > aggregate function, it is referenced only by one of the following > - predicates: {=, !=, <, <=, >, >=, between, is null, is not null}. > + predicates $FUNC$: > + {=, !=, <, <=, >, >=, between, is [not] null, multiple equal}. > + In addition the function checks that the WHERE condition is equivalent to > + "cond1 AND cond2" where : > + cond1 - does not use min_max_column at all. > + cond2 - is an AND/OR tree with leaves in form > + "$FUNC$(min_max_column[, const])". > > RETURN > TRUE if cond passes the test > @@ -12061,7 +12072,8 @@ get_best_group_min_max(PARAM *param, SEL > > static bool > check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item, > - Field::imagetype image_type) > + Field::imagetype image_type, > + bool *has_min_max_arg, bool *has_other_arg) > { > DBUG_ENTER("check_group_min_max_predicates"); > DBUG_ASSERT(cond && min_max_arg_item); > @@ -12073,12 +12085,24 @@ check_group_min_max_predicates(Item *con > DBUG_PRINT("info", ("Analyzing: %s", ((Item_func*) cond)->func_name())); > List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list()); > Item *and_or_arg; > + Item_func::Functype func_type= ((Item_cond*) cond)->functype(); > + bool has_min_max= false, has_other= false; > while ((and_or_arg= li++)) > { > - if (!check_group_min_max_predicates(and_or_arg, min_max_arg_item, > - image_type)) > + /* > + The WHERE clause doesn't pass the condition if: > + (1) any subtree doesn't pass the condition or > + (2) the subtree passes the test, but it is an OR and it references both > + the min/max argument and other columns. > + */ > + if (!check_group_min_max_predicates(and_or_arg, min_max_arg_item, //1 > + image_type, > + &has_min_max, &has_other) || > + (func_type == Item_func::COND_OR_FUNC && has_min_max && has_other))//2 > DBUG_RETURN(FALSE); > } > + *has_min_max_arg= has_min_max || *has_min_max_arg; > + *has_other_arg= has_other || *has_other_arg; > DBUG_RETURN(TRUE); > } > > @@ -12112,6 +12136,10 @@ check_group_min_max_predicates(Item *con > if (cond_type == Item::FIELD_ITEM) > { > DBUG_PRINT("info", ("Analyzing: %s", cond->full_name())); > + if (min_max_arg_item->eq((Item_field*)cond, 1)) > + *has_min_max_arg= true; > + else > + *has_other_arg= true; > DBUG_RETURN(TRUE); > } > > @@ -12120,9 +12148,33 @@ check_group_min_max_predicates(Item *con > > /* Test if cond references only group-by or non-group fields. */ > Item_func *pred= (Item_func*) cond; > + Item_func::Functype pred_type= pred->functype(); > + DBUG_PRINT("info", ("Analyzing: %s", pred->func_name())); > + if (pred_type == Item_func::MULT_EQUAL_FUNC) > + { > + /* > + Check that each field in a multiple equality is either a constant or > + it is a reference to the min/max argument, or it doesn't contain the > + min/max argument at all. > + */ > + Item_equal_fields_iterator eq_it(*((Item_equal*)pred)); > + Item *eq_item; > + bool has_min_max= false, has_other= false; > + while ((eq_item= eq_it++)) > + { > + if (min_max_arg_item->eq(eq_item->real_item(), 1)) > + has_min_max= true; > + else > + has_other= true; > + } > + *has_min_max_arg= has_min_max || *has_min_max_arg; > + *has_other_arg= has_other || *has_other_arg; > + DBUG_RETURN(!(has_min_max && has_other)); > + } > + > Item **arguments= pred->arguments(); > Item *cur_arg; > - DBUG_PRINT("info", ("Analyzing: %s", pred->func_name())); > + bool has_min_max= false, has_other= false; > for (uint arg_idx= 0; arg_idx < pred->argument_count (); arg_idx++) > { > cur_arg= arguments[arg_idx]->real_item(); > @@ -12131,11 +12183,11 @@ check_group_min_max_predicates(Item *con > { > if (min_max_arg_item->eq(cur_arg, 1)) > { > - /* > - If pred references the MIN/MAX argument, check whether pred is a range > - condition that compares the MIN/MAX argument with a constant. > - */ > - Item_func::Functype pred_type= pred->functype(); > + has_min_max= true; > + /* > + If pred references the MIN/MAX argument, check whether pred is a range > + condition that compares the MIN/MAX argument with a constant. > + */ > if (pred_type != Item_func::EQUAL_FUNC && > pred_type != Item_func::LT_FUNC && > pred_type != Item_func::LE_FUNC && > @@ -12175,11 +12227,13 @@ check_group_min_max_predicates(Item *con > min_max_arg_item->field->cmp_type() != args[1]->result_type()))) > DBUG_RETURN(FALSE); > } > + else > + has_other= true; > } > else if (cur_arg->type() == Item::FUNC_ITEM) > { > - if (!check_group_min_max_predicates(cur_arg, min_max_arg_item, > - image_type)) > + if (!check_group_min_max_predicates(cur_arg, min_max_arg_item, image_type, > + &has_min_max, &has_other)) > DBUG_RETURN(FALSE); > } > else if (cur_arg->const_item()) > @@ -12192,7 +12246,11 @@ check_group_min_max_predicates(Item *con > } > else > DBUG_RETURN(FALSE); > + if(has_min_max && has_other) > + DBUG_RETURN(FALSE); > } > + *has_min_max_arg= has_min_max || *has_min_max_arg; > + *has_other_arg= has_other || *has_other_arg; > > DBUG_RETURN(TRUE); > } > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0