From: Jorgen Loland Date: February 17 2011 1:25pm Subject: bzr push into mysql-trunk branch (jorgen.loland:3668 to 3669) List-Archive: http://lists.mysql.com/commits/131547 Message-Id: <20110217132524.A7C3E95F@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3669 Jorgen Loland 2011-02-17 Enabled new optimizer features by mistake. Reverting. modified: mysql-test/r/group_by.result mysql-test/r/group_min_max.result mysql-test/r/index_merge_myisam.result mysql-test/r/join_cache_jcl1.result mysql-test/r/join_cache_jcl2.result mysql-test/r/join_cache_jcl3.result mysql-test/r/join_cache_jcl4.result mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/r/subselect_innodb.result mysql-test/suite/innodb/r/innodb_mysql.result mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result mysql-test/suite/sys_vars/r/optimizer_switch_basic.result sql/sql_priv.h 3668 Jorgen Loland 2011-02-17 [merge] Automerge opt-team -> trunk. No conflicts modified: mysql-test/include/subquery_mat.inc mysql-test/r/group_by.result mysql-test/r/group_min_max.result mysql-test/r/index_merge_myisam.result mysql-test/r/innodb_icp.result mysql-test/r/innodb_icp_all.result mysql-test/r/innodb_icp_none.result mysql-test/r/join_cache_jcl1.result mysql-test/r/join_cache_jcl2.result mysql-test/r/join_cache_jcl3.result mysql-test/r/join_cache_jcl4.result mysql-test/r/myisam_icp_all.result mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/r/subquery_all.result mysql-test/r/subquery_all_jcl6.result mysql-test/r/subquery_mat.result mysql-test/r/subquery_mat_all.result mysql-test/r/subquery_mat_none.result mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_nomat_nosj_jcl6.result mysql-test/r/subquery_none.result mysql-test/r/subquery_none_jcl6.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_innodb_none.result mysql-test/r/subquery_sj_innodb_none_jcl6.result mysql-test/r/subquery_sj_innodb_none_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result mysql-test/r/subselect_innodb.result mysql-test/suite/innodb/r/innodb_mysql.result mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result mysql-test/suite/sys_vars/r/optimizer_switch_basic.result sql/sql_priv.h sql/sql_select.cc === modified file 'mysql-test/r/group_by.result' --- a/mysql-test/r/group_by.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/group_by.result 2011-02-17 13:25:01 +0000 @@ -1543,8 +1543,8 @@ id select_type table type possible_keys EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers) +1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; @@ -1556,8 +1556,8 @@ id select_type table type possible_keys EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 4 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2); Using join buffer (BNL, incremental buffers) +1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where SHOW VARIABLES LIKE 'old'; Variable_name Value old OFF === modified file 'mysql-test/r/group_min_max.result' --- a/mysql-test/r/group_min_max.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/group_min_max.result 2011-02-17 13:25:01 +0000 @@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra === modified file 'mysql-test/r/index_merge_myisam.result' --- a/mysql-test/r/index_merge_myisam.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/index_merge_myisam.result 2011-02-17 13:25:01 +0000 @@ -1492,19 +1492,19 @@ DROP TABLE t1,t2; # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on set optimizer_switch=4; set optimizer_switch=NULL; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL' @@ -1530,21 +1530,21 @@ set optimizer_switch=default; set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on # # Check index_merge's @@optimizer_switch flags # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, filler char(100), @@ -1654,5 +1654,5 @@ id select_type table type possible_keys set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on drop table t0, t1; === modified file 'mysql-test/r/join_cache_jcl1.result' --- a/mysql-test/r/join_cache_jcl1.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/join_cache_jcl1.result 2011-02-17 13:25:01 +0000 @@ -634,8 +634,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -941,8 +941,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; === modified file 'mysql-test/r/join_cache_jcl2.result' --- a/mysql-test/r/join_cache_jcl2.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/join_cache_jcl2.result 2011-02-17 13:25:01 +0000 @@ -634,8 +634,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -941,8 +941,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; === modified file 'mysql-test/r/join_cache_jcl3.result' --- a/mysql-test/r/join_cache_jcl3.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/join_cache_jcl3.result 2011-02-17 13:25:01 +0000 @@ -634,8 +634,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -941,8 +941,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; === modified file 'mysql-test/r/join_cache_jcl4.result' --- a/mysql-test/r/join_cache_jcl4.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/join_cache_jcl4.result 2011-02-17 13:25:01 +0000 @@ -634,8 +634,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -941,8 +941,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where +1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where +2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; === modified file 'mysql-test/r/mysqld--help-notwin.result' --- a/mysql-test/r/mysqld--help-notwin.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/mysqld--help-notwin.result 2011-02-17 13:25:01 +0000 @@ -423,8 +423,7 @@ The following options may be given as th is one of {index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, engine_condition_pushdown, index_condition_pushdown, mrr, - mrr_cost_based, materialization, semijoin, loosescan, - firstmatch} and val is one of {on, off, default} + mrr_cost_based} and val is one of {on, off, default} --performance-schema Enable the performance schema. --performance-schema-events-waits-history-long-size=# @@ -883,7 +882,7 @@ old-style-user-limits FALSE optimizer-join-cache-level 4 optimizer-prune-level 1 optimizer-search-depth 62 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on performance-schema FALSE performance-schema-events-waits-history-long-size 10000 performance-schema-events-waits-history-size 10 === modified file 'mysql-test/r/mysqld--help-win.result' --- a/mysql-test/r/mysqld--help-win.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/mysqld--help-win.result 2011-02-17 13:25:01 +0000 @@ -423,8 +423,7 @@ The following options may be given as th is one of {index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, engine_condition_pushdown, index_condition_pushdown, mrr, - mrr_cost_based, materialization, semijoin, loosescan, - firstmatch} and val is one of {on, off, default} + mrr_cost_based} and val is one of {on, off, default} --performance-schema Enable the performance schema. --performance-schema-events-waits-history-long-size=# @@ -887,7 +886,7 @@ old-style-user-limits FALSE optimizer-join-cache-level 4 optimizer-prune-level 1 optimizer-search-depth 62 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on performance-schema FALSE performance-schema-events-waits-history-long-size 10000 performance-schema-events-waits-history-size 10 === modified file 'mysql-test/r/subselect_innodb.result' --- a/mysql-test/r/subselect_innodb.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/r/subselect_innodb.result 2011-02-17 13:25:01 +0000 @@ -279,13 +279,13 @@ id select_type table type possible_keys 1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 100.00 Using where 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 100.00 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 100.00 -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 100.00 Using where -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where +3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where +4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where +5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where +6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTY PEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and (`test`.`t2`.`FOLDERID`,(select 1 from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` where ((`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and ((`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`))))) +Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTY PEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and (`test`.`t2`.`FOLDERID`,(((`test`.`t2`.`FOLDERID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (`test`.`t3`.`PARENTID`,(((`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`PARENTID`,(((`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`PARENTID`,(((`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`PARENTID`,(((`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21 ddc49089') and ((`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and ((`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and ((`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and ((`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and ((`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`)))))) drop table t1, t2, t3, t4; CREATE TABLE t1 ( school_name varchar(45) NOT NULL, === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result' --- a/mysql-test/suite/innodb/r/innodb_mysql.result 2011-02-16 09:12:52 +0000 +++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2011-02-17 13:25:01 +0000 @@ -2780,9 +2780,9 @@ set optimizer_join_cache_level=1; EXPLAIN SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Start temporary -1 PRIMARY t2 index NULL PRIMARY 4 NULL 1 Using index; Using join buffer (BNL, regular buffers) -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, regular buffers) +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers) +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 1 Using where; Using index SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); b 1 === modified file 'mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result' --- a/mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/suite/sys_vars/r/optimizer_join_cache_level_basic.result 2011-02-17 13:25:01 +0000 @@ -30,7 +30,7 @@ Warnings: Warning 1292 Truncated incorrect optimizer_join_cache_level value: '9' select @@session.optimizer_join_cache_level; @@session.optimizer_join_cache_level -8 +4 set global optimizer_join_cache_level=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_join_cache_level' set global optimizer_join_cache_level=1e1; === modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_basic.result' --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2011-02-10 09:42:51 +0000 +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2011-02-17 13:25:01 +0000 @@ -1,57 +1,57 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off set global optimizer_switch=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_switch' set global optimizer_switch=1e1; @@ -63,4 +63,4 @@ ERROR 42000: Variable 'optimizer_switch' SET @@global.optimizer_switch = @start_global_value; SELECT @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on === modified file 'sql/sql_priv.h' --- a/sql/sql_priv.h 2011-02-10 09:42:51 +0000 +++ b/sql/sql_priv.h 2011-02-17 13:25:01 +0000 @@ -176,7 +176,7 @@ If OPTIMIZER_SWITCH_ALL is defined, optimizer_switch flags for newer optimizer features (semijoin) will be available. */ -#define OPTIMIZER_SWITCH_ALL 1 +#undef OPTIMIZER_SWITCH_ALL /* The following must be kept in sync with optimizer_switch string in No bundle (reason: useless for push emails).