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, [email protected] wrote: > At file:///home/tsk/mprog/src/10.0-md537-merge/ > > ------------------------------------------------------------ > revno: 3497 > revision-id: [email protected] > parent: [email protected] > fixes bug(s): https://mariadb.atlassian.net/browse/MDEV-537 > committer: [email protected] > 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 > [email protected] > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
