- developers - lists.mariadb.org

[Maria-developers] my_hash_sort_bin
by MARK CALLAGHAN 20 Feb '13

20 Feb '13

Hello MariaDB, While working with Dmitry Lenev to understand why MySQL 5.6 was slower than 5.1 on one benchmark ( https://www.facebook.com/notes/mysql-at-facebook/my-mysql-is-faster-than-yo…) we realized that the hash function used in my_hash_sort_bin is lousy for this input: test.sbest1, test.sbtest2, ..., test.sbtest10. The problem is made worse when a small number of hash buckets is used because the hash function output doesn't do the right thing for the least significant bits so that all 10 inputs map to the same hash bucket. More details are at http://bugs.mysql.com/bug.php?id=66473 The InnoDB hash function is much better. Details for that and a test program are in the bug report. Does anyone remember why this hash function was chosen? strings/ctype-bin.c doesn't have any comments explaining why this hash function was selected. This is another peeve for me. Critical code like this should be explained if we expect anyone new to begin working on this code. I haven't done a formal test of the hash function, and http://burtleburtle.net/bob/hash/hashfaq.html would be a good reference for that. But I think it is very likely that my assertion that this hash function is lousy holds in general, rather than is limited to this input. And given the large number of machines that run MySQL, MariaDB and Percona Server (and the FB patch) around the world I suspect that we should figure this out. -- Mark Callaghan mdcallag(a)gmail.com

2 1

[Maria-developers] implementing multi range read at the storage engine in MariaDB 5.5
by Zardosht Kasheff 19 Feb '13

19 Feb '13

Hello all, Is there anything to implementing multi range read for storage engines? Or is there a default implementation that is likely good enough. Also, is there anything one can read to understand how the feature works and its benefits? Thanks -Zardosht

3 3

[Maria-developers] Using C++ features in MariaDB
by Christian Convey 19 Feb '13

19 Feb '13

I just read in Pachev's "MySQL Internals" book that C++ STL and exceptions aren't allowed in the MySQL code. Are those restrictions also in effect for MariaDB? If so, can anyone explain the reason for them? Thanks, Christian

2 8

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