diff options
| -rw-r--r-- | mysql-test/r/ndb_subquery.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 52 | ||||
| -rw-r--r-- | mysql-test/r/subselect2.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/subselect3.result | 505 | ||||
| -rw-r--r-- | mysql-test/t/subselect3.test | 353 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 34 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 1 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 294 | ||||
| -rw-r--r-- | sql/item_subselect.h | 92 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 2 | ||||
| -rw-r--r-- | sql/mysqld.cc | 3 | ||||
| -rw-r--r-- | sql/sql_lex.h | 2 | ||||
| -rw-r--r-- | sql/sql_select.cc | 190 | ||||
| -rw-r--r-- | sql/sql_select.h | 42 |
14 files changed, 1309 insertions, 273 deletions
diff --git a/mysql-test/r/ndb_subquery.result b/mysql-test/r/ndb_subquery.result index f2c9972f774..62d40a2199c 100644 --- a/mysql-test/r/ndb_subquery.result +++ b/mysql-test/r/ndb_subquery.result @@ -12,7 +12,7 @@ insert into t4 values (1,10), (2,10), (3,30), (4, 30); explain select * from t2 where p NOT IN (select p from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func # Using index +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func # select * from t2 where p NOT IN (select p from t1) order by p; p u o 4 4 4 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ae828cf06fc..5582cf48f00 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -744,7 +744,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -892,7 +892,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); INSERT INTO t3 VALUES (1),(2),(3); SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; @@ -907,7 +907,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -1303,9 +1303,9 @@ a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30)))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1460,29 +1460,29 @@ a1 0 a2 1 a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2')))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; create table t2 (a int, b int); create table t3 (a int); @@ -2816,19 +2816,19 @@ 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 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` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <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` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'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` 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 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` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; 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 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort 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` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <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` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (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`))) and 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; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); @@ -3009,7 +3009,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 75aa339fb29..27ec0ccfc0f 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -126,11 +126,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 Using where 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 -2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using index; Using where +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using where drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 10cc2729f3e..0b88b78198d 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -15,9 +15,8 @@ insert into t2 values (4, NULL), (2, NULL); select a, oref, a in (select max(ie) -from t1 where oref=t2.oref group by grp) from t2; -a oref a in (select max(ie) -from t1 where oref=t2.oref group by grp) +from t1 where oref=t2.oref group by grp) Z from t2; +a oref Z 1 1 1 2 2 0 3 3 NULL @@ -25,14 +24,13 @@ NULL 4 0 NULL 2 NULL explain extended select a, oref, a in (select max(ie) -from t1 where oref=t2.oref group by grp) from t2; +from t1 where oref=t2.oref group by grp) Z from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) -from t1 where oref=t2.oref group by grp)` from `test`.`t2` +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2` explain extended select a, oref from t2 where a in (select max(ie) from t1 where oref=t2.oref group by grp); @@ -42,6 +40,16 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))) +select a, oref, a in ( +select max(ie) from t1 where oref=t2.oref group by grp union +select max(ie) from t1 where oref=t2.oref group by grp +) Z from t2; +a oref Z +1 1 1 +2 2 0 +3 3 NULL +NULL 4 0 +NULL 2 NULL create table t3 (a int); insert into t3 values (NULL), (NULL); flush status; @@ -79,12 +87,12 @@ oref a Z 4 NULL 0 explain extended select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using index; Using where +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2` +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2` flush status; select oref, a from t2 where a in (select a from t1 where oref=t2.oref); oref a @@ -143,11 +151,480 @@ explain extended select a, oref, t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z from t3; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where Warnings: Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; +create table t1 (a int NOT NULL, b int NOT NULL, key(a)); +insert into t1 values +(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +create table t2 like t1; +insert into t2 select * from t1; +update t2 set b=1; +create table t3 (a int, oref int); +insert into t3 values (1, 1), (NULL,1), (NULL,0); +select a, oref, +t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; +a oref Z +1 1 1 +NULL 1 NULL +NULL 0 0 +This must show a trig_cond: +explain extended +select a, oref, +t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where +Warnings: +Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3` +drop table t1,t2,t3; +create table t1 (oref int, grp int); +insert into t1 (oref, grp) values +(1, 1), +(1, 1); +create table t2 (oref int, a int); +insert into t2 values +(1, NULL), +(2, NULL); +select a, oref, +a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; +a oref Z +NULL 1 NULL +NULL 2 0 +This must show a trig_cond: +explain extended +select a, oref, +a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2` +drop table t1, t2; +create table t1 (a int, b int, primary key (a)); +insert into t1 values (1,1), (3,1),(100,1); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); +select a,b, a in (select a from t1 where t1.b = t2.b union select a from +t1 where t1.b = t2.b) Z from t2 ; +a b Z +1 1 1 +2 1 0 +NULL 1 NULL +NULL 0 0 +select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; +a b Z +1 1 1 +2 1 0 +NULL 1 NULL +NULL 0 0 +drop table t1, t2; +create table t3 (a int); +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, b int, oref int); +insert into t2 values (NULL,1, 100), (NULL,2, 100); +create table t1 (a int, b int, c int, key(a,b)); +insert into t1 select 2*A, 2*A, 100 from t3; +explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key +Warnings: +Note 1276 Field or reference '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`,<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` +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 +NULL 2 100 NULL +create table t4 (x int); +insert into t4 select A.a + 10*B.a from t1 A, t1 B; +explain extended +select a,b, oref, +(a,b) in (select a,b from t1,t4 where c=t2.oref) Z +from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 Using where +Warnings: +Note 1276 Field or reference '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`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `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` +select a,b, oref, +(a,b) in (select a,b from t1,t4 where c=t2.oref) Z +from t2; +a b oref Z +NULL 1 100 0 +NULL 2 100 NULL +drop table t1,t2,t3,t4; +create table t1 (oref char(4), grp int, ie1 int, ie2 int); +insert into t1 (oref, grp, ie1, ie2) values +('aa', 10, 2, 1), +('aa', 10, 1, 1), +('aa', 20, 2, 1), +('bb', 10, 3, 1), +('cc', 10, 4, 2), +('cc', 20, 3, 2), +('ee', 10, 2, 1), +('ee', 10, 1, 2), +('ff', 20, 2, 2), +('ff', 20, 1, 2); +create table t2 (oref char(4), a int, b int); +insert into t2 values +('ee', NULL, 1), +('bb', 2, 1), +('ff', 2, 2), +('cc', 3, NULL), +('bb', NULL, NULL), +('aa', 1, 1), +('dd', 1, NULL); +alter table t1 add index idx(ie1,ie2); +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ; +oref a b Z +cc 3 NULL NULL +insert into t2 values ('new1', 10,10); +insert into t1 values ('new1', 1234, 10, NULL); +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; +oref a b Z +new1 10 10 NULL +explain extended +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where +2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key +Warnings: +Note 1276 Field or reference '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`,<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)) +drop table t1, t2; +create table t1 (oref char(4), grp int, ie int); +insert into t1 (oref, grp, ie) values +('aa', 10, 2), +('aa', 10, 1), +('aa', 20, NULL), +('bb', 10, 3), +('cc', 10, 4), +('cc', 20, NULL), +('ee', 10, NULL), +('ee', 10, NULL), +('ff', 20, 2), +('ff', 20, 1); +create table t2 (oref char(4), a int); +insert into t2 values +('ee', NULL), +('bb', 2), +('ff', 2), +('cc', 3), +('aa', 1), +('dd', NULL), +('bb', NULL); +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +oref a Z +ee NULL NULL +bb 2 0 +ff 2 1 +cc 3 NULL +aa 1 1 +dd NULL 0 +bb NULL NULL +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); +oref a +ff 2 +aa 1 +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); +oref a +bb 2 +dd NULL +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; +oref a Z +ee NULL NULL +bb 2 0 +ff 2 0 +cc 3 NULL +aa 1 1 +dd NULL 0 +bb NULL NULL +select oref, a from t2 where +a in (select min(ie) from t1 where oref=t2.oref group by grp); +oref a +aa 1 +select oref, a from t2 where +a not in (select min(ie) from t1 where oref=t2.oref group by grp); +oref a +bb 2 +ff 2 +dd NULL +update t1 set ie=3 where oref='ff' and ie=1; +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by +grp) Z from t2; +oref a Z +ee NULL NULL +bb 2 0 +ff 2 1 +cc 3 NULL +aa 1 1 +dd NULL 0 +bb NULL NULL +select oref, a from t2 where a in (select min(ie) from t1 where +oref=t2.oref group by grp); +oref a +ff 2 +aa 1 +select oref, a from t2 where a not in (select min(ie) from t1 where +oref=t2.oref group by grp); +oref a +bb 2 +dd NULL +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by +grp having min(ie) > 1) Z from t2; +oref a Z +ee NULL 0 +bb 2 0 +ff 2 1 +cc 3 0 +aa 1 0 +dd NULL 0 +bb NULL NULL +select oref, a from t2 where a in (select min(ie) from t1 where +oref=t2.oref group by grp having min(ie) > 1); +oref a +ff 2 +select oref, a from t2 where a not in (select min(ie) from t1 where +oref=t2.oref group by grp having min(ie) > 1); +oref a +ee NULL +bb 2 +cc 3 +aa 1 +dd NULL +alter table t1 add index idx(ie); +explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +oref a Z +ee NULL NULL +bb 2 0 +ff 2 1 +cc 3 NULL +aa 1 1 +dd NULL 0 +bb NULL NULL +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); +oref a +ff 2 +aa 1 +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); +oref a +bb 2 +dd NULL +alter table t1 drop index idx; +alter table t1 add index idx(oref,ie); +explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +oref a Z +ee NULL NULL +bb 2 0 +ff 2 1 +cc 3 NULL +aa 1 1 +dd NULL 0 +bb NULL NULL +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); +oref a +ff 2 +aa 1 +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); +oref a +bb 2 +dd NULL +explain +select oref, a, +a in (select min(ie) from t1 where oref=t2.oref +group by grp having min(ie) > 1) Z +from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort +select oref, a, +a in (select min(ie) from t1 where oref=t2.oref +group by grp having min(ie) > 1) Z +from t2; +oref a Z +ee NULL 0 +bb 2 0 +ff 2 1 +cc 3 0 +aa 1 0 +dd NULL 0 +bb NULL NULL +select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref +group by grp having min(ie) > 1); +oref a +ff 2 +select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref +group by grp having min(ie) > 1); +oref a +ee NULL +bb 2 +cc 3 +aa 1 +dd NULL +drop table t1,t2; +create table t1 (oref char(4), grp int, ie1 int, ie2 int); +insert into t1 (oref, grp, ie1, ie2) values +('aa', 10, 2, 1), +('aa', 10, 1, 1), +('aa', 20, 2, 1), +('bb', 10, 3, 1), +('cc', 10, 4, 2), +('cc', 20, 3, 2), +('ee', 10, 2, 1), +('ee', 10, 1, 2), +('ff', 20, 2, 2), +('ff', 20, 1, 2); +create table t2 (oref char(4), a int, b int); +insert into t2 values +('ee', NULL, 1), +('bb', 2, 1), +('ff', 2, 2), +('cc', 3, NULL), +('bb', NULL, NULL), +('aa', 1, 1), +('dd', 1, NULL); +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; +oref a b Z +ee NULL 1 NULL +bb 2 1 0 +ff 2 2 1 +cc 3 NULL NULL +bb NULL NULL NULL +aa 1 1 1 +dd 1 NULL 0 +select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); +oref a b +ff 2 2 +aa 1 1 +select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); +oref a b +bb 2 1 +dd 1 NULL +select oref, a, b, +(a,b) in (select min(ie1),max(ie2) from t1 +where oref=t2.oref group by grp) Z +from t2; +oref a b Z +ee NULL 1 0 +bb 2 1 0 +ff 2 2 0 +cc 3 NULL NULL +bb NULL NULL NULL +aa 1 1 1 +dd 1 NULL 0 +select oref, a, b from t2 where +(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); +oref a b +aa 1 1 +select oref, a, b from t2 where +(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); +oref a b +ee NULL 1 +bb 2 1 +ff 2 2 +dd 1 NULL +alter table t1 add index idx(ie1,ie2); +explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; +oref a b Z +ee NULL 1 NULL +bb 2 1 0 +ff 2 2 1 +cc 3 NULL NULL +bb NULL NULL NULL +aa 1 1 1 +dd 1 NULL 0 +select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); +oref a b +ff 2 2 +aa 1 1 +select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); +oref a b +bb 2 1 +dd 1 NULL +explain extended +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key +Warnings: +Note 1276 Field or reference '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`,<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` +drop table t1,t2; +create table t1 (oref char(4), grp int, ie int primary key); +insert into t1 (oref, grp, ie) values +('aa', 10, 2), +('aa', 10, 1), +('bb', 10, 3), +('cc', 10, 4), +('cc', 20, 5), +('cc', 10, 6); +create table t2 (oref char(4), a int); +insert into t2 values +('ee', NULL), +('bb', 2), +('cc', 5), +('cc', 2), +('cc', NULL), +('aa', 1), +('bb', NULL); +explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +oref a Z +ee NULL 0 +bb 2 0 +cc 5 1 +cc 2 0 +cc NULL NULL +aa 1 1 +bb NULL NULL +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); +oref a +cc 5 +aa 1 +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); +oref a +ee NULL +bb 2 +cc 2 +explain +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; +oref a Z +ee NULL 0 +bb 2 0 +cc 5 1 +cc 2 0 +cc NULL NULL +aa 1 1 +bb NULL NULL +drop table t1,t2; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index f7fbafdd17f..23d78721dbe 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -16,13 +16,14 @@ insert into t1 (oref, grp, ie) values (3, 1, 4), (3, 2, NULL); -# Ok, for +# Ok, for # select max(ie) from t1 where oref=PARAM group by grp # we'll have: -# 1 -> (1, NULL) matching + NULL -# 2 -> (3) non-matching -# 3 -> (3, NULL) non-matching + NULL -# 4 -> () nothing. +# PARAM subquery result +# 1 -> {(1), (NULL)} matching + NULL +# 2 -> {(3)} non-matching +# 3 -> {(3), (NULL)} non-matching + NULL +# 4 -> {} empty set create table t2 (oref int, a int); insert into t2 values @@ -34,18 +35,21 @@ insert into t2 values # true, false, null, false, null select a, oref, a in (select max(ie) - from t1 where oref=t2.oref group by grp) from t2; + from t1 where oref=t2.oref group by grp) Z from t2; # This must have a trigcond explain extended select a, oref, a in (select max(ie) - from t1 where oref=t2.oref group by grp) from t2; + from t1 where oref=t2.oref group by grp) Z from t2; # This must not have a trigcond: explain extended select a, oref from t2 where a in (select max(ie) from t1 where oref=t2.oref group by grp); - +select a, oref, a in ( + select max(ie) from t1 where oref=t2.oref group by grp union + select max(ie) from t1 where oref=t2.oref group by grp + ) Z from t2; # Non-correlated subquery, 2 NULL evaluations create table t3 (a int); @@ -135,3 +139,336 @@ from t3; drop table t1, t2, t3; + +# +# BUG#24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)" +# + +# case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc +create table t1 (a int NOT NULL, b int NOT NULL, key(a)); +insert into t1 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); + +create table t2 like t1; +insert into t2 select * from t1; +update t2 set b=1; + +create table t3 (a int, oref int); +insert into t3 values (1, 1), (NULL,1), (NULL,0); +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; + +--echo This must show a trig_cond: +explain extended +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; +drop table t1,t2,t3; + + +# case 2: NULL IN (SELECT not_null_val FROM) where SELECT has GROUP BY +create table t1 (oref int, grp int); +insert into t1 (oref, grp) values + (1, 1), + (1, 1); + +# Ok, for +# select count(*) from t1 group by grp having grp=PARAM +# we'll have: +# PARAM subuqery result +# 1 -> {(2)} +# 2 -> {} - empty set +create table t2 (oref int, a int); +insert into t2 values + (1, NULL), + (2, NULL); + +select a, oref, + a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; + +--echo This must show a trig_cond: +explain extended +select a, oref, + a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2; + +drop table t1, t2; + +create table t1 (a int, b int, primary key (a)); +insert into t1 values (1,1), (3,1),(100,1); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); + +select a,b, a in (select a from t1 where t1.b = t2.b union select a from +t1 where t1.b = t2.b) Z from t2 ; +select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; +drop table t1, t2; + + +# +# BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side. +# +create table t3 (a int); +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, b int, oref int); +insert into t2 values (NULL,1, 100), (NULL,2, 100); + +create table t1 (a int, b int, c int, key(a,b)); +insert into t1 select 2*A, 2*A, 100 from t3; + +# First test index subquery engine +explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; +select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; + +# Then check that we do turn off 'ref' scans in the subquery +create table t4 (x int); +insert into t4 select A.a + 10*B.a from t1 A, t1 B; +explain extended + select a,b, oref, + (a,b) in (select a,b from t1,t4 where c=t2.oref) Z + from t2; +select a,b, oref, + (a,b) in (select a,b from t1,t4 where c=t2.oref) Z +from t2; + +drop table t1,t2,t3,t4; + +# More tests for tricky multi-column cases, where some of pushed-down +# equalities are used for index lookups and some arent. +create table t1 (oref char(4), grp int, ie1 int, ie2 int); +insert into t1 (oref, grp, ie1, ie2) values + ('aa', 10, 2, 1), + ('aa', 10, 1, 1), + ('aa', 20, 2, 1), + ('bb', 10, 3, 1), + ('cc', 10, 4, 2), + ('cc', 20, 3, 2), + + ('ee', 10, 2, 1), + ('ee', 10, 1, 2), + + ('ff', 20, 2, 2), + ('ff', 20, 1, 2); +create table t2 (oref char(4), a int, b int); +insert into t2 values + ('ee', NULL, 1), + ('bb', 2, 1), + ('ff', 2, 2), + ('cc', 3, NULL), + ('bb', NULL, NULL), + ('aa', 1, 1), + ('dd', 1, NULL); +alter table t1 add index idx(ie1,ie2); + +--cc 3 NULL NULL +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ; +insert into t2 values ('new1', 10,10); +insert into t1 values ('new1', 1234, 10, NULL); +-- new1, 10, 10, NULL, +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; +explain extended +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; +drop table t1, t2; + +# Now test different column types: +create table t1 (oref char(4), grp int, ie int); +insert into t1 (oref, grp, ie) values + ('aa', 10, 2), + ('aa', 10, 1), + ('aa', 20, NULL), + + ('bb', 10, 3), + + ('cc', 10, 4), + ('cc', 20, NULL), + + ('ee', 10, NULL), + ('ee', 10, NULL), + + ('ff', 20, 2), + ('ff', 20, 1); + +create table t2 (oref char(4), a int); +insert into t2 values + ('ee', NULL), + ('bb', 2), + ('ff', 2), + ('cc', 3), + ('aa', 1), + ('dd', NULL), + ('bb', NULL); + +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; + +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); + +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); + + +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; + +select oref, a from t2 where + a in (select min(ie) from t1 where oref=t2.oref group by grp); + +select oref, a from t2 where + a not in (select min(ie) from t1 where oref=t2.oref group by grp); + +# +update t1 set ie=3 where oref='ff' and ie=1; + +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by +grp) Z from t2; + + +select oref, a from t2 where a in (select min(ie) from t1 where +oref=t2.oref group by grp); + +select oref, a from t2 where a not in (select min(ie) from t1 where +oref=t2.oref group by grp); + +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by +grp having min(ie) > 1) Z from t2; + +select oref, a from t2 where a in (select min(ie) from t1 where +oref=t2.oref group by grp having min(ie) > 1); + +select oref, a from t2 where a not in (select min(ie) from t1 where +oref=t2.oref group by grp having min(ie) > 1); + +# +alter table t1 add index idx(ie); + +explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; + +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; + +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); + +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); + + +alter table t1 drop index idx; +alter table t1 add index idx(oref,ie); + +explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; + +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; + +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); + +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); + +explain +select oref, a, + a in (select min(ie) from t1 where oref=t2.oref + group by grp having min(ie) > 1) Z +from t2; + +select oref, a, + a in (select min(ie) from t1 where oref=t2.oref + group by grp having min(ie) > 1) Z +from t2; + +select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref + group by grp having min(ie) > 1); + +select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref + group by grp having min(ie) > 1); + +drop table t1,t2; + +create table t1 (oref char(4), grp int, ie1 int, ie2 int); +insert into t1 (oref, grp, ie1, ie2) values + ('aa', 10, 2, 1), + ('aa', 10, 1, 1), + ('aa', 20, 2, 1), + + ('bb', 10, 3, 1), + + ('cc', 10, 4, 2), + ('cc', 20, 3, 2), + + ('ee', 10, 2, 1), + ('ee', 10, 1, 2), + + ('ff', 20, 2, 2), + ('ff', 20, 1, 2); + +create table t2 (oref char(4), a int, b int); +insert into t2 values + ('ee', NULL, 1), + ('bb', 2, 1), + ('ff', 2, 2), + ('cc', 3, NULL), + ('bb', NULL, NULL), + ('aa', 1, 1), + ('dd', 1, NULL); + +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; + +select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); + +select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); + +select oref, a, b, + (a,b) in (select min(ie1),max(ie2) from t1 + where oref=t2.oref group by grp) Z +from t2; + +select oref, a, b from t2 where + (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); + +select oref, a, b from t2 where + (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp); + +alter table t1 add index idx(ie1,ie2); + +explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; + +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; + +select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); + +select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); + +explain extended +select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2; + +drop table t1,t2; + +create table t1 (oref char(4), grp int, ie int primary key); +insert into t1 (oref, grp, ie) values + ('aa', 10, 2), + ('aa', 10, 1), + + ('bb', 10, 3), + + ('cc', 10, 4), + ('cc', 20, 5), + ('cc', 10, 6); + +create table t2 (oref char(4), a int); +insert into t2 values + ('ee', NULL), + ('bb', 2), + ('cc', 5), + ('cc', 2), + ('cc', NULL), + ('aa', 1), + ('bb', NULL); + +explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; + +select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; + +select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); + +select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); + +explain +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; + +select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2; + +drop table t1,t2; + diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 3a887e6dda6..4e31ef1ee4d 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -877,11 +877,35 @@ longlong Item_in_optimizer::val_int() We disable the predicates we've pushed down into subselect, run the subselect and see if it has produced any rows. */ - ((Item_in_subselect*)args[1])->enable_pushed_conds= FALSE; - longlong tmp= args[1]->val_bool_result(); - result_for_null_param= null_value= - !((Item_in_subselect*)args[1])->engine->no_rows(); - ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE; + Item_in_subselect *item_subs=(Item_in_subselect*)args[1]; + if (cache->cols() == 1) + { + item_subs->set_cond_guard_var(0, FALSE); + longlong tmp= args[1]->val_bool_result(); + result_for_null_param= null_value= !item_subs->engine->no_rows(); + item_subs->set_cond_guard_var(0, TRUE); + } + else + { + uint i; + uint ncols= cache->cols(); + /* + Turn off the predicates that are based on column compares for + which the left part is currently NULL + */ + for (i= 0; i < ncols; i++) + { + if (cache->el(i)->null_value) + item_subs->set_cond_guard_var(i, FALSE); + } + + longlong tmp= args[1]->val_bool_result(); + result_for_null_param= null_value= !item_subs->engine->no_rows(); + + /* Turn all predicates back on */ + for (i= 0; i < ncols; i++) + item_subs->set_cond_guard_var(i, TRUE); + } } } return 0; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 899d29d4786..5f109f29e8b 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -314,6 +314,7 @@ public: enum Functype functype() const { return TRIG_COND_FUNC; }; const char *func_name() const { return "trigcond"; }; bool const_item() const { return FALSE; } + bool *get_trig_var() { return trig_var; } }; class Item_func_not_all :public Item_func_not diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index f3f840bdaa9..e131072d9bf 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -234,16 +234,16 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, } -bool Item_subselect::exec(bool full_scan) +bool Item_subselect::exec() { int res; - res= engine->exec(full_scan); + res= engine->exec(); if (engine_changed) { engine_changed= 0; - return exec(full_scan); + return exec(); } return (res); } @@ -491,13 +491,13 @@ bool Item_singlerow_subselect::null_inside() void Item_singlerow_subselect::bring_value() { - exec(FALSE); + exec(); } double Item_singlerow_subselect::val_real() { DBUG_ASSERT(fixed == 1); - if (!exec(FALSE) && !value->null_value) + if (!exec() && !value->null_value) { null_value= 0; return value->val_real(); @@ -512,7 +512,7 @@ double Item_singlerow_subselect::val_real() longlong Item_singlerow_subselect::val_int() { DBUG_ASSERT(fixed == 1); - if (!exec(FALSE) && !value->null_value) + if (!exec() && !value->null_value) { null_value= 0; return value->val_int(); @@ -526,7 +526,7 @@ longlong Item_singlerow_subselect::val_int() String *Item_singlerow_subselect::val_str(String *str) { - if (!exec(FALSE) && !value->null_value) + if (!exec() && !value->null_value) { null_value= 0; return value->val_str(str); @@ -541,7 +541,7 @@ String *Item_singlerow_subselect::val_str(String *str) my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { - if (!exec(FALSE) && !value->null_value) + if (!exec() && !value->null_value) { null_value= 0; return value->val_decimal(decimal_value); @@ -556,7 +556,7 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) bool Item_singlerow_subselect::val_bool() { - if (!exec(FALSE) && !value->null_value) + if (!exec() && !value->null_value) { null_value= 0; return value->val_bool(); @@ -608,7 +608,7 @@ bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit) Item_in_subselect::Item_in_subselect(Item * left_exp, st_select_lex *select_lex): Item_exists_subselect(), optimizer(0), transformed(0), - enable_pushed_conds(TRUE), upper_item(0) + pushed_cond_guards(NULL), upper_item(0) { DBUG_ENTER("Item_in_subselect::Item_in_subselect"); left_expr= left_exp; @@ -653,7 +653,7 @@ void Item_exists_subselect::fix_length_and_dec() double Item_exists_subselect::val_real() { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec()) { reset(); return 0; @@ -664,7 +664,7 @@ double Item_exists_subselect::val_real() longlong Item_exists_subselect::val_int() { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec()) { reset(); return 0; @@ -675,7 +675,7 @@ longlong Item_exists_subselect::val_int() String *Item_exists_subselect::val_str(String *str) { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec()) { reset(); return 0; @@ -688,7 +688,7 @@ String *Item_exists_subselect::val_str(String *str) my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec()) { reset(); return 0; @@ -701,7 +701,7 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) bool Item_exists_subselect::val_bool() { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec()) { reset(); return 0; @@ -719,7 +719,7 @@ double Item_in_subselect::val_real() DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec()) { reset(); null_value= 1; @@ -740,7 +740,7 @@ longlong Item_in_subselect::val_int() DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec()) { reset(); null_value= 1; @@ -761,7 +761,7 @@ String *Item_in_subselect::val_str(String *str) DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec()) { reset(); null_value= 1; @@ -781,7 +781,7 @@ bool Item_in_subselect::val_bool() { DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec()) { reset(); null_value= 1; @@ -801,7 +801,7 @@ my_decimal *Item_in_subselect::val_decimal(my_decimal *decimal_value) DBUG_ASSERT(0); null_value= 0; DBUG_ASSERT(fixed == 1); - if (exec(!enable_pushed_conds)) + if (exec()) { reset(); null_value= 1; @@ -965,7 +965,7 @@ Item_in_subselect::single_value_transformer(JOIN *join, if (!substitution) { - //first call for this unit + /* We're invoked for the 1st (or the only) SELECT in the subquery UNION */ SELECT_LEX_UNIT *unit= select_lex->master_unit(); substitution= optimizer; @@ -991,19 +991,14 @@ Item_in_subselect::single_value_transformer(JOIN *join, unit->uncacheable|= UNCACHEABLE_DEPENDENT; } + if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards) + { + if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool)))) + DBUG_RETURN(RES_ERROR); + pushed_cond_guards[0]= TRUE; + } select_lex->uncacheable|= UNCACHEABLE_DEPENDENT; - /* - Add the left part of a subselect to a WHERE or HAVING clause of - the right part, e.g. - - SELECT 1 IN (SELECT a FROM t1) => - - SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1) - - HAVING is used only if the right part contains a SUM function, a GROUP - BY or a HAVING clause. - */ if (join->having || select_lex->with_sum_func || select_lex->group_list.elements) { @@ -1015,13 +1010,13 @@ Item_in_subselect::single_value_transformer(JOIN *join, ref_pointer_array, (char *)"<ref>", this->full_name())); - if (!abort_on_null && ((Item*)select_lex->item_list.head())->maybe_null) + if (!abort_on_null && left_expr->maybe_null) { /* We can encounter "NULL IN (SELECT ...)". Wrap the added condition - within a trigger. + within a trig_cond. */ - item= new Item_func_trig_cond(item, &enable_pushed_conds); + item= new Item_func_trig_cond(item, get_cond_guard(0)); } /* @@ -1030,6 +1025,8 @@ Item_in_subselect::single_value_transformer(JOIN *join, argument (reference) to fix_fields() */ select_lex->having= join->having= and_items(join->having, item); + if (join->having == item) + item->name= (char*)in_having_cond; select_lex->having_fix_field= 1; /* we do not check join->having->fixed, because Item_and (from and_items) @@ -1056,14 +1053,19 @@ Item_in_subselect::single_value_transformer(JOIN *join, item= func->create(expr, item); if (!abort_on_null && orig_item->maybe_null) { - having= - new Item_func_trig_cond(new Item_is_not_null_test(this, having), - &enable_pushed_conds); + having= new Item_is_not_null_test(this, having); + if (left_expr->maybe_null) + { + if (!(having= new Item_func_trig_cond(having, + get_cond_guard(0)))) + DBUG_RETURN(RES_ERROR); + } /* Item_is_not_null_test can't be changed during fix_fields() we can assign select_lex->having here, and pass 0 as last argument (reference) to fix_fields() */ + having->name= (char*)in_having_cond; select_lex->having= join->having= having; select_lex->having_fix_field= 1; /* @@ -1075,17 +1077,25 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->having_fix_field= 0; if (tmp) DBUG_RETURN(RES_ERROR); - /* - NOTE: It is important that we add this "IS NULL" here, even when - orig_item can't be NULL. This is needed so that this predicate is - only used by ref[_or_null] analyzer (and, e.g. is not used by const - propagation). - */ item= new Item_cond_or(item, new Item_func_isnull(orig_item)); - item= new Item_func_trig_cond(item, &enable_pushed_conds); } + /* + If we may encounter NULL IN (SELECT ...) and care whether subquery + result is NULL or FALSE, wrap condition in a trig_cond. + */ + if (!abort_on_null && left_expr->maybe_null) + { + if (!(item= new Item_func_trig_cond(item, get_cond_guard(0)))) + DBUG_RETURN(RES_ERROR); + } + /* + TODO: figure out why the following is done here in + single_value_transformer but there is no corresponding action in + row_value_transformer? + */ item->name= (char *)in_additional_cond; + /* AND can't be changed during fix_fields() we can assign select_lex->having here, and pass 0 as last @@ -1116,10 +1126,16 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->ref_pointer_array, (char *)"<no matter>", (char *)"<result>")); - new_having= new Item_func_trig_cond(new_having, &enable_pushed_conds); + if (!abort_on_null && left_expr->maybe_null) + { + if (!(new_having= new Item_func_trig_cond(new_having, + get_cond_guard(0)))) + DBUG_RETURN(RES_ERROR); + } + new_having->name= (char*)in_having_cond; select_lex->having= join->having= new_having; - select_lex->having_fix_field= 1; + /* we do not check join->having->fixed, because comparison function (from func->create) can't be fixed after creation @@ -1189,6 +1205,15 @@ Item_in_subselect::row_value_transformer(JOIN *join) thd->lex->current_select= current; unit->uncacheable|= UNCACHEABLE_DEPENDENT; + + if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards) + { + if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool) * + left_expr->cols()))) + DBUG_RETURN(RES_ERROR); + for (uint i= 0; i < cols_num; i++) + pushed_cond_guards[i]= TRUE; + } } select_lex->uncacheable|= UNCACHEABLE_DEPENDENT; @@ -1205,6 +1230,7 @@ Item_in_subselect::row_value_transformer(JOIN *join) is_not_null_test(v3)) where is_not_null_test used to register nulls in case if we have not found matching to return correct NULL value + TODO: say here explicitly if the order of AND parts matters or not. */ Item *item_having_part2= 0; for (uint i= 0; i < cols_num; i++) @@ -1233,21 +1259,28 @@ Item_in_subselect::row_value_transformer(JOIN *join) (char *)"<no matter>", (char *)"<list ref>") ); - having_item= - and_items(having_item, - new Item_cond_or(item_eq, item_isnull)); - item_having_part2= - and_items(item_having_part2, - new - Item_is_not_null_test(this, - new - Item_ref(&select_lex->context, - select_lex-> - ref_pointer_array + i, - (char *)"<no matter>", - (char *)"<list ref>") - ) - ); + Item *col_item= new Item_cond_or(item_eq, item_isnull); + if (!abort_on_null && left_expr->el(i)->maybe_null) + { + if (!(col_item= new Item_func_trig_cond(col_item, get_cond_guard(i)))) + DBUG_RETURN(RES_ERROR); + } + having_item= and_items(having_item, col_item); + + Item *item_nnull_test= + new Item_is_not_null_test(this, + new Item_ref(&select_lex->context, + select_lex-> + ref_pointer_array + i, + (char *)"<no matter>", + (char *)"<list ref>")); + if (!abort_on_null && left_expr->el(i)->maybe_null) + { + if (!(item_nnull_test= + new Item_func_trig_cond(item_nnull_test, get_cond_guard(i)))) + DBUG_RETURN(RES_ERROR); + } + item_having_part2= and_items(item_having_part2, item_nnull_test); item_having_part2->top_level_item(); } having_item= and_items(having_item, item_having_part2); @@ -1296,18 +1329,15 @@ Item_in_subselect::row_value_transformer(JOIN *join) ); if (!abort_on_null) { - having_item= - and_items(having_item, - new - Item_is_not_null_test(this, - new - Item_ref(&select_lex->context, - select_lex-> - ref_pointer_array + i, - (char *)"<no matter>", - (char *)"<list ref>") - ) - ); + Item *having_col_item= + new Item_is_not_null_test(this, + new + Item_ref(&select_lex->context, + select_lex->ref_pointer_array + i, + (char *)"<no matter>", + (char *)"<list ref>")); + + item_isnull= new Item_func_isnull(new Item_direct_ref(&select_lex->context, @@ -1316,14 +1346,23 @@ Item_in_subselect::row_value_transformer(JOIN *join) (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->el(i)->maybe_null) + { + if (!(item= new Item_func_trig_cond(item, get_cond_guard(i)))) + DBUG_RETURN(RES_ERROR); + if (!(having_col_item= + new Item_func_trig_cond(having_col_item, get_cond_guard(i)))) + DBUG_RETURN(RES_ERROR); + } + having_item= and_items(having_item, having_col_item); } - where_item= and_items(where_item, item); } - if (where_item) - where_item= new Item_func_trig_cond(where_item, &enable_pushed_conds); /* AND can't be changed during fix_fields() we can assign select_lex->where here, and pass 0 as last @@ -1337,9 +1376,9 @@ Item_in_subselect::row_value_transformer(JOIN *join) if (having_item) { bool res; - having_item= new Item_func_trig_cond(having_item, &enable_pushed_conds); - select_lex->having= join->having= and_items(join->having, having_item); + if (having_item == select_lex->having) + having_item->name= (char*)in_having_cond; select_lex->having->top_level_item(); /* AND can't be changed during fix_fields() @@ -1725,7 +1764,7 @@ int init_read_record_seq(JOIN_TAB *tab); int join_read_always_key_or_null(JOIN_TAB *tab); int join_read_next_same_or_null(READ_RECORD *info); -int subselect_single_select_engine::exec(bool full_scan) +int subselect_single_select_engine::exec() { DBUG_ENTER("subselect_single_select_engine::exec"); char const *save_where= thd->where; @@ -1763,9 +1802,13 @@ int subselect_single_select_engine::exec(bool full_scan) if (!executed) { item->reset_value_registration(); - if (full_scan) + bool have_changed_access= FALSE; + JOIN_TAB *changed_tabs[MAX_TABLES]; + JOIN_TAB **last_changed_tab= changed_tabs; + if (item->have_guarded_conds()) { /* + For at least one of the pushed predicates the following is true: We should not apply optimizations based on the condition that was pushed down into the subquery. Those optimizations are ref[_or_null] acceses. Change them to be full table scans. @@ -1773,32 +1816,36 @@ int subselect_single_select_engine::exec(bool full_scan) for (uint i=join->const_tables ; i < join->tables ; i++) { JOIN_TAB *tab=join->join_tab+i; - if (tab->keyuse && tab->keyuse->outer_ref) + if (tab && tab->keyuse) { - tab->read_first_record= init_read_record_seq; - tab->read_record.record= tab->table->record[0]; - tab->read_record.thd= join->thd; - tab->read_record.ref_length= tab->table->file->ref_length; + for (uint i= 0; i < tab->ref.key_parts; i++) + { + bool *cond_guard= tab->ref.cond_guards[i]; + if (cond_guard && !*cond_guard) + { + /* Change the access method to full table scan */ + tab->read_first_record= init_read_record_seq; + tab->read_record.record= tab->table->record[0]; + tab->read_record.thd= join->thd; + tab->read_record.ref_length= tab->table->file->ref_length; + *(last_changed_tab++)= tab; + break; + } + } } } } join->exec(); - if (full_scan) + /* Enable the optimizations back */ + for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++) { - /* Enable the optimizations back */ - for (uint i=join->const_tables ; i < join->tables ; i++) - { - JOIN_TAB *tab=join->join_tab+i; - if (tab->keyuse && tab->keyuse->outer_ref) - { - tab->read_record.record= 0; - tab->read_record.ref_length= 0; - tab->read_first_record= join_read_always_key_or_null; - tab->read_record.read_record= join_read_next_same_or_null; - } - } + JOIN_TAB *tab= *ptab; + tab->read_record.record= 0; + tab->read_record.ref_length= 0; + tab->read_first_record= join_read_always_key_or_null; + tab->read_record.read_record= join_read_next_same_or_null; } executed= 1; thd->where= save_where; @@ -1810,13 +1857,9 @@ int subselect_single_select_engine::exec(bool full_scan) DBUG_RETURN(0); } -int subselect_union_engine::exec(bool full_scan) +int subselect_union_engine::exec() { char const *save_where= thd->where; - /* - Ignore the full_scan parameter: the pushed down predicates are only used - for filtering, and the caller has disabled them if necessary. - */ int res= unit->exec(); thd->where= save_where; return res; @@ -1824,7 +1867,7 @@ int subselect_union_engine::exec(bool full_scan) /* - Search for at least on row satisfying select condition + Search for at least one row satisfying select condition SYNOPSIS subselect_uniquesubquery_engine::scan_table() @@ -1833,8 +1876,8 @@ int subselect_union_engine::exec(bool full_scan) Scan the table using sequential access until we find at least one row satisfying select condition. - The result of this function (info about whether a row was found) is - stored in this->empty_result_set. + The caller must set this->empty_result_set=FALSE before calling this + function. This function will set it to TRUE if it finds a matching row. RETURN FALSE - OK @@ -1846,7 +1889,6 @@ int subselect_uniquesubquery_engine::scan_table() int error; TABLE *table= tab->table; DBUG_ENTER("subselect_uniquesubquery_engine::scan_table"); - empty_result_set= TRUE; if (table->file->inited) table->file->ha_index_end(); @@ -1939,10 +1981,13 @@ bool subselect_uniquesubquery_engine::copy_ref_key() - FALSE otherwise. In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE) - the caller doesn't distinguish between NULL and FALSE result and we just + the caller doesn't distinguish between NULL and FALSE result and we just return FALSE. - Otherwise we make a full table scan to see if there is at least one matching row. - + Otherwise we make a full table scan to see if there is at least one + matching row. + + The result of this function (info about whether a row was found) is + stored in this->empty_result_set. NOTE RETURN @@ -1950,11 +1995,12 @@ bool subselect_uniquesubquery_engine::copy_ref_key() TRUE - an error occured while scanning */ -int subselect_uniquesubquery_engine::exec(bool full_scan) +int subselect_uniquesubquery_engine::exec() { DBUG_ENTER("subselect_uniquesubquery_engine::exec"); int error; TABLE *table= tab->table; + empty_result_set= TRUE; /* TODO: change to use of 'full_scan' here? */ if (copy_ref_key()) @@ -1975,9 +2021,13 @@ int subselect_uniquesubquery_engine::exec(bool full_scan) { error= 0; table->null_row= 0; - ((Item_in_subselect *) item)->value= (!table->status && - (!cond || cond->val_int()) ? 1 : - 0); + if (!table->status && (!cond || cond->val_int())) + { + ((Item_in_subselect *) item)->value= 1; + empty_result_set= FALSE; + } + else + ((Item_in_subselect *) item)->value= 0; } DBUG_RETURN(error != 0); @@ -2043,7 +2093,7 @@ subselect_uniquesubquery_engine::~subselect_uniquesubquery_engine() 1 */ -int subselect_indexsubquery_engine::exec(bool full_scan) +int subselect_indexsubquery_engine::exec() { DBUG_ENTER("subselect_indexsubquery_engine::exec"); int error; @@ -2084,8 +2134,9 @@ int subselect_indexsubquery_engine::exec(bool full_scan) table->null_row= 0; if (!table->status) { - if (!cond || cond->val_int()) + if ((!cond || cond->val_int()) && (!having || having->val_int())) { + empty_result_set= FALSE; if (null_finding) ((Item_in_subselect *) item)->was_null= 1; else @@ -2228,11 +2279,16 @@ void subselect_indexsubquery_engine::print(String *str) str->append(key_info->name); if (check_null) str->append(STRING_WITH_LEN(" checking NULL")); - if (cond) + if (cond) { str->append(STRING_WITH_LEN(" where ")); cond->print(str); } + if (having) + { + str->append(STRING_WITH_LEN(" having ")); + having->print(str); + } str->append(')'); } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 5a0b2788678..aa7b6a3aeb4 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -94,7 +94,7 @@ public: return null_value; } bool fix_fields(THD *thd, Item **ref); - virtual bool exec(bool full_scan); + virtual bool exec(); virtual void fix_length_and_dec(); table_map used_tables() const; table_map not_null_tables() const { return 0; } @@ -104,6 +104,7 @@ public: Item *get_tmp_table_item(THD *thd); void update_used_tables(); void print(String *str); + virtual bool have_guarded_conds() { return FALSE; } bool change_engine(subselect_engine *eng) { old_engine= engine; @@ -250,13 +251,21 @@ protected: bool transformed; public: /* Used to trigger on/off conditions that were pushed down to subselect */ - bool enable_pushed_conds; + bool *pushed_cond_guards; + + bool *get_cond_guard(int i) + { + return pushed_cond_guards ? pushed_cond_guards + i : NULL; + } + void set_cond_guard_var(int i, bool v) { pushed_cond_guards[i]= v; } + bool have_guarded_conds() { return test(pushed_cond_guards); } + Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery Item_in_subselect(Item * left_expr, st_select_lex *select_lex); Item_in_subselect() :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0), - enable_pushed_conds(TRUE), upper_item(0) + pushed_cond_guards(NULL), upper_item(0) {} subs_type substype() { return IN_SUBS; } @@ -341,23 +350,22 @@ public: SYNOPSIS exec() - full_scan TRUE - Pushed-down predicates are disabled, the engine - must disable made based on those predicates. - FALSE - Pushed-down predicates are in effect. + DESCRIPTION Execute the engine. The result of execution is subquery value that is either captured by previously set up select_result-based 'sink' or stored somewhere by the exec() method itself. - A required side effect: if full_scan==TRUE, subselect_engine->no_rows() - should return correct result. + A required side effect: If at least one pushed-down predicate is + disabled, subselect_engine->no_rows() must return correct result after + the exec() call. RETURN 0 - OK - 1 - Either an execution error, or the engine was be "changed", and + 1 - Either an execution error, or the engine was "changed", and the caller should call exec() again for the new engine. */ - virtual int exec(bool full_scan)= 0; + virtual int exec()= 0; virtual uint cols()= 0; /* return number of columns in select */ virtual uint8 uncacheable()= 0; /* query is uncacheable */ enum Item_result type() { return res_type; } @@ -392,7 +400,7 @@ public: void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(bool full_scan); + int exec(); uint cols(); uint8 uncacheable(); void exclude(); @@ -416,7 +424,7 @@ public: void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(bool full_scan); + int exec(); uint cols(); uint8 uncacheable(); void exclude(); @@ -430,11 +438,30 @@ public: struct st_join_table; + + +/* + A subquery execution engine that evaluates the subquery by doing one index + lookup in a unique index. + + This engine is used to resolve subqueries in forms + + outer_expr IN (SELECT tbl.unique_key FROM tbl WHERE subq_where) + + or, tuple-based: + + (oe1, .. oeN) IN (SELECT uniq_key_part1, ... uniq_key_partK + FROM tbl WHERE subqwhere) + + i.e. the subquery is a single table SELECT without GROUP BY, aggregate + functions, etc. +*/ + class subselect_uniquesubquery_engine: public subselect_engine { protected: st_join_table *tab; - Item *cond; + Item *cond; /* The WHERE condition of subselect */ /* TRUE<=> last execution produced empty set. Valid only when left expression is NULL. @@ -454,7 +481,7 @@ public: void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(bool full_scan); + int exec(); uint cols() { return 1; } uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } void exclude(); @@ -472,16 +499,47 @@ class subselect_indexsubquery_engine: public subselect_uniquesubquery_engine { /* FALSE for 'ref', TRUE for 'ref-or-null'. */ bool check_null; + /* + The "having" clause. This clause (further reffered to as "artificial + having") was inserted by subquery transformation code. It contains + Item(s) that have a side-effect: they record whether the subquery has + produced a row with NULL certain components. We need to use it for cases + like + (oe1, oe2) IN (SELECT t.key, t.no_key FROM t1) + where we do index lookup on t.key=oe1 but need also to check if there + was a row such that t.no_key IS NULL. + + NOTE: This is currently here and not in the uniquesubquery_engine. Ideally + it should have been in uniquesubquery_engine in order to allow execution of + subqueries like + + (oe1, oe2) IN (SELECT primary_key, non_key_maybe_null_field FROM tbl) + + We could use uniquesubquery_engine for the first component and let + Item_is_not_null_test( non_key_maybe_null_field) to handle the second. + + However, subqueries like the above are currently not handled by index + lookup-based subquery engines, the engine applicability check misses + them: it doesn't switch the engine for case of artificial having and + [eq_]ref access (only for artifical having + ref_or_null or no having). + The above example subquery is handled as a full-blown SELECT with eq_ref + access to one table. + + Due to this limitation, the "artificial having" currently needs to be + checked by only in indexsubquery_engine. + */ + Item *having; public: // constructor can assign THD because it will be called after JOIN::prepare subselect_indexsubquery_engine(THD *thd, st_join_table *tab_arg, Item_subselect *subs, Item *where, - bool chk_null) + Item *having_arg, bool chk_null) :subselect_uniquesubquery_engine(thd, tab_arg, subs, where), - check_null(chk_null) + check_null(chk_null), + having(having_arg) {} - int exec(bool full_scan); + int exec(); void print (String *str); }; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 56a7403b372..742efc71e63 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -1532,7 +1532,7 @@ extern const LEX_STRING command_name[]; extern const char *first_keyword, *my_localhost, *delayed_user, *binary_keyword; extern const char **errmesg; /* Error messages */ extern const char *myisam_recover_options_str; -extern const char *in_left_expr_name, *in_additional_cond; +extern const char *in_left_expr_name, *in_additional_cond, *in_having_cond; extern const char * const triggers_file_ext; extern const char * const trigname_file_ext; extern Eq_creator eq_creator; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index bcf5263d06c..85a9fd3218a 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -524,10 +524,13 @@ char *mysqld_unix_port, *opt_mysql_tmpdir; const char **errmesg; /* Error messages */ const char *myisam_recover_options_str="OFF"; const char *myisam_stats_method_str="nulls_unequal"; + /* name of reference on left espression in rewritten IN subquery */ const char *in_left_expr_name= "<left expr>"; /* name of additional condition */ const char *in_additional_cond= "<IN COND>"; +const char *in_having_cond= "<IN HAVING>"; + my_decimal decimal_zero; /* classes for comparation parsing/processing */ Eq_creator eq_creator; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 247c35ca73a..8632ab0f675 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -504,7 +504,7 @@ public: void set_thd(THD *thd_arg) { thd= thd_arg; } friend void lex_start(THD *thd, const uchar *buf, uint length); - friend int subselect_union_engine::exec(bool); + friend int subselect_union_engine::exec(); List<Item> *get_unit_column_types(); }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 65bfd8b5acb..1b939a31eb5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -506,72 +506,88 @@ err: /* - test if it is known for optimisation IN subquery + Remove the predicates pushed down into the subquery SYNOPSIS - JOIN::test_in_subselect() - where - pointer for variable in which conditions should be - stored if subquery is known + JOIN::remove_subq_pushed_predicates() + where IN Must be NULL + OUT The remaining WHERE condition, or NULL - RETURN - 1 - known - 0 - unknown + DESCRIPTION + Given that this join will be executed using (unique|index)_subquery, + without "checking NULL", remove the predicates that were pushed down + into the subquery. + + We can remove the equalities that will be guaranteed to be true by the + fact that subquery engine will be using index lookup. + + If the subquery compares scalar values, we can remove the condition that + was wrapped into trig_cond (it will be checked when needed by the subquery + engine) + + If the subquery compares row values, we need to keep the wrapped + equalities in the WHERE clause: when the left (outer) tuple has both NULL + and non-NULL values, we'll do a full table scan and will rely on the + equalities corresponding to non-NULL parts of left tuple to filter out + non-matching records. */ -bool JOIN::test_in_subselect(Item **where) +void JOIN::remove_subq_pushed_predicates(Item **where) { if (conds->type() == Item::FUNC_ITEM && ((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC && ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM && ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM) { - join_tab->info= "Using index"; *where= 0; - return 1; + return; } if (conds->type() == Item::COND_ITEM && ((class Item_func *)this->conds)->functype() == Item_func::COND_AND_FUNC) { - if ((*where= remove_additional_cond(conds))) - join_tab->info= "Using index; Using where"; - else - join_tab->info= "Using index"; - return 1; + *where= remove_additional_cond(conds); } - return 0; } /* - Check if the passed HAVING clause is a clause added by subquery optimizer + Index lookup-based subquery: save some flags for EXPLAIN output SYNOPSIS - is_having_subq_predicates() - having Having clause + save_index_subquery_explain_info() + join_tab Subquery's join tab (there is only one as index lookup is + only used for subqueries that are single-table SELECTs) + where Subquery's WHERE clause - RETURN - TRUE The passed HAVING clause was added by the subquery optimizer - FALSE Otherwise + DESCRIPTION + For index lookup-based subquery (i.e. one executed with + subselect_uniquesubquery_engine or subselect_indexsubquery_engine), + check its EXPLAIN output row should contain + "Using index" (TAB_INFO_FULL_SCAN_ON_NULL) + "Using Where" (TAB_INFO_USING_WHERE) + "Full scan on NULL key" (TAB_INFO_FULL_SCAN_ON_NULL) + and set appropriate flags in join_tab->packed_info. */ -bool is_having_subq_predicates(Item *having) +static void save_index_subquery_explain_info(JOIN_TAB *join_tab, Item* where) { - if (having->type() == Item::FUNC_ITEM) + join_tab->packed_info= TAB_INFO_HAVE_VALUE; + if (join_tab->table->used_keys.is_set(join_tab->ref.key)) + join_tab->packed_info |= TAB_INFO_USING_INDEX; + if (where) + join_tab->packed_info |= TAB_INFO_USING_WHERE; + for (uint i = 0; i < join_tab->ref.key_parts; i++) { - if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) - return TRUE; - if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC) + if (join_tab->ref.cond_guards[i]) { - having= ((Item_func*)having)->arguments()[0]; - if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) - return TRUE; + join_tab->packed_info |= TAB_INFO_FULL_SCAN_ON_NULL; + break; } - return TRUE; } - return FALSE; } + /* global select optimisation. return 0 - success @@ -1027,51 +1043,47 @@ JOIN::optimize() if (join_tab[0].type == JT_EQ_REF && join_tab[0].ref.items[0]->name == in_left_expr_name) { - if (test_in_subselect(&where)) - { - join_tab[0].type= JT_UNIQUE_SUBQUERY; - error= 0; - DBUG_RETURN(unit->item-> - change_engine(new - subselect_uniquesubquery_engine(thd, - join_tab, - unit->item, - where))); - } + remove_subq_pushed_predicates(&where); + save_index_subquery_explain_info(join_tab, where); + join_tab[0].type= JT_UNIQUE_SUBQUERY; + error= 0; + DBUG_RETURN(unit->item-> + change_engine(new + subselect_uniquesubquery_engine(thd, + join_tab, + unit->item, + where))); } else if (join_tab[0].type == JT_REF && join_tab[0].ref.items[0]->name == in_left_expr_name) { - if (test_in_subselect(&where)) - { - join_tab[0].type= JT_INDEX_SUBQUERY; - error= 0; - DBUG_RETURN(unit->item-> - change_engine(new - subselect_indexsubquery_engine(thd, - join_tab, - unit->item, - where, - 0))); - } + remove_subq_pushed_predicates(&where); + save_index_subquery_explain_info(join_tab, where); + join_tab[0].type= JT_INDEX_SUBQUERY; + error= 0; + DBUG_RETURN(unit->item-> + change_engine(new + subselect_indexsubquery_engine(thd, + join_tab, + unit->item, + where, + NULL, + 0))); } } else if (join_tab[0].type == JT_REF_OR_NULL && join_tab[0].ref.items[0]->name == in_left_expr_name && - is_having_subq_predicates(having)) + having->name == in_having_cond) { join_tab[0].type= JT_INDEX_SUBQUERY; error= 0; - - if ((conds= remove_additional_cond(conds))) - join_tab->info= "Using index; Using where"; - else - join_tab->info= "Using index"; - + conds= remove_additional_cond(conds); + save_index_subquery_explain_info(join_tab, conds); DBUG_RETURN(unit->item-> change_engine(new subselect_indexsubquery_engine(thd, join_tab, unit->item, conds, + having, 1))); } @@ -2585,9 +2597,7 @@ typedef struct key_field_t { // Used when finding key fields when val IS NULL. */ bool null_rejecting; - - /* TRUE<=> This ref access is an outer subquery reference access */ - bool outer_ref; + bool *cond_guard; /* See KEYUSE::cond_guard */ } KEY_FIELD; /* Values in optimize */ @@ -2889,7 +2899,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond, cond->functype() == Item_func::MULT_EQUAL_FUNC) && ((*value)->type() == Item::FIELD_ITEM) && ((Item_field*)*value)->field->maybe_null()); - (*key_fields)->outer_ref= FALSE; + (*key_fields)->cond_guard= NULL; (*key_fields)++; } @@ -2986,8 +2996,9 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, } /* - Subquery optimization: check if the encountered condition is one - added by condition push down into subquery. + Subquery optimization: Conditions that are pushed down into subqueries + are wrapped into Item_func_trig_cond. We process the wrapped condition + but need to set cond_guard for KEYUSE elements generated from it. */ { if (cond->type() == Item::FUNC_ITEM && @@ -3004,7 +3015,7 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, sargables); // Indicate that this ref access candidate is for subquery lookup: for (; save != *key_fields; save++) - save->outer_ref= TRUE; + save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var(); } return; } @@ -3184,7 +3195,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field) keyuse.used_tables=key_field->val->used_tables(); keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; keyuse.null_rejecting= key_field->null_rejecting; - keyuse.outer_ref= key_field->outer_ref; + keyuse.cond_guard= key_field->cond_guard; VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } } @@ -5144,7 +5155,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, if (!(j->ref.key_buff= (byte*) thd->calloc(ALIGN_SIZE(length)*2)) || !(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) * (keyparts+1)))) || - !(j->ref.items= (Item**) thd->alloc(sizeof(Item*)*keyparts))) + !(j->ref.items= (Item**) thd->alloc(sizeof(Item*)*keyparts)) || + !(j->ref.cond_guards= (bool**) thd->alloc(sizeof(uint*)*keyparts))) { DBUG_RETURN(TRUE); } @@ -5159,6 +5171,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, if (ftkey) { j->ref.items[0]=((Item_func*)(keyuse->val))->key_item(); + /* Predicates pushed down into subquery can't be used FT access */ + j->ref.cond_guards[0]= NULL; if (keyuse->used_tables) DBUG_RETURN(TRUE); // not supported yet. SerG @@ -5175,6 +5189,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, uint maybe_null= test(keyinfo->key_part[i].null_bit); j->ref.items[i]=keyuse->val; // Save for cond removal + j->ref.cond_guards[i]= keyuse->cond_guard; if (keyuse->null_rejecting) j->ref.null_rejecting |= 1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; @@ -7832,7 +7847,7 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list, SYNOPSIS remove_additional_cond() - conds - condition for processing + conds Condition for processing RETURN VALUES new conditions @@ -11145,7 +11160,9 @@ int rr_sequential(READ_RECORD *info); int init_read_record_seq(JOIN_TAB *tab) { tab->read_record.read_record= rr_sequential; - return tab->read_record.file->ha_rnd_init(1); + if (tab->read_record.file->ha_rnd_init(1)) + return 1; + return (*tab->read_record.read_record)(&tab->read_record); } static int @@ -15160,6 +15177,24 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, if (tab->info) item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs)); + else if (tab->packed_info & TAB_INFO_HAVE_VALUE) + { + if (tab->packed_info & TAB_INFO_USING_INDEX) + extra.append(STRING_WITH_LEN("; Using index")); + if (tab->packed_info & TAB_INFO_USING_WHERE) + extra.append(STRING_WITH_LEN("; Using where")); + if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL) + extra.append(STRING_WITH_LEN("; Full scan on NULL key")); + /* Skip initial "; "*/ + const char *str= extra.ptr(); + uint32 len= extra.length(); + if (len) + { + str += 2; + len -= 2; + } + item_list.push_back(new Item_string(str, len, cs)); + } else { if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || @@ -15218,6 +15253,15 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, } if (distinct & test_all_bits(used_tables,thd->used_tables)) extra.append(STRING_WITH_LEN("; Distinct")); + + for (uint part= 0; part < tab->ref.key_parts; part++) + { + if (tab->ref.cond_guards[part]) + { + extra.append(STRING_WITH_LEN("; Full scan on NULL key")); + break; + } + } /* Skip initial "; "*/ const char *str= extra.ptr(); diff --git a/sql/sql_select.h b/sql/sql_select.h index 6ab4463605b..1d1fa666c60 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -35,8 +35,17 @@ typedef struct keyuse_t { satisfied if val has NULL 'value'. */ bool null_rejecting; - /* TRUE<=> This ref access is an outer subquery reference access */ - bool outer_ref; + /* + !NULL - This KEYUSE was created from an equality that was wrapped into + an Item_func_trig_cond. This means the equality (and validity of + this KEYUSE element) can be turned on and off. The on/off state + is indicted by the pointed value: + *cond_guard == TRUE <=> equality condition is on + *cond_guard == FALSE <=> equality condition is off + + NULL - Otherwise (the source equality can't be turned off) + */ + bool *cond_guard; } KEYUSE; class store_key; @@ -51,6 +60,18 @@ typedef struct st_table_ref byte *key_buff2; // key_buff+key_length store_key **key_copy; // Item **items; // val()'s for each keypart + /* + Array of pointers to trigger variables. Some/all of the pointers may be + NULL. The ref access can be used iff + + for each used key part i, (!cond_guards[i] || *cond_guards[i]) + + This array is used by subquery code. The subquery code may inject + triggered conditions, i.e. conditions that can be 'switched off'. A ref + access created from such condition is not valid when at least one of the + underlying conditions is switched off (see subquery code for more details) + */ + bool **cond_guards; /* (null_rejecting & (1<<i)) means the condition is '=' and no matching rows will be produced if items[i] IS NULL (see add_not_null_conds()) @@ -99,6 +120,13 @@ enum enum_nested_loop_state NESTED_LOOP_QUERY_LIMIT= 3, NESTED_LOOP_CURSOR_LIMIT= 4 }; + +/* Values for JOIN_TAB::packed_info */ +#define TAB_INFO_HAVE_VALUE 1 +#define TAB_INFO_USING_INDEX 2 +#define TAB_INFO_USING_WHERE 4 +#define TAB_INFO_FULL_SCAN_ON_NULL 8 + typedef enum_nested_loop_state (*Next_select_func)(JOIN *, struct st_join_table *, bool); typedef int (*Read_record_func)(struct st_join_table *tab); @@ -120,7 +148,15 @@ typedef struct st_join_table { st_join_table *last_inner; /* last table table for embedding outer join */ st_join_table *first_upper; /* first inner table for embedding outer join */ st_join_table *first_unmatched; /* used for optimization purposes only */ + + /* Special content for EXPLAIN 'Extra' column or NULL if none */ const char *info; + /* + Bitmap of TAB_INFO_* bits that encodes special line for EXPLAIN 'Extra' + column, or 0 if there is no info. + */ + uint packed_info; + Read_record_func read_first_record; Next_select_func next_select; READ_RECORD read_record; @@ -425,7 +461,7 @@ public: Item_sum ***func); int rollup_send_data(uint idx); int rollup_write_data(uint idx, TABLE *table); - bool test_in_subselect(Item **where); + void remove_subq_pushed_predicates(Item **where); /* Release memory and, if possible, the open tables held by this execution plan (and nested plans). It's used to release some tables before |