[Commits] Rev 3675: MDEV-83 Cost-based choice for the pushdown of expensive predicates to joined tables in file:///home/tsk/mprog/src/10.0-md83/

timour at askmonty.org timour at askmonty.org
Fri Aug 9 15:53:14 EEST 2013


At file:///home/tsk/mprog/src/10.0-md83/

------------------------------------------------------------
revno: 3675
revision-id: timour at askmonty.org-20130401103605-5o4in6ng6rd8bzbi
parent: igor at askmonty.org-20130426020530-bwwnztq78sckb2vk
fixes bug: https://mariadb.atlassian.net/browse/MDEV-83
committer: timour at askmonty.org
branch nick: 10.0-md83-lp
timestamp: Mon 2013-04-01 13:36:05 +0300
message:
  MDEV-83  Cost-based choice for the pushdown of expensive predicates to joined tables
      
  Prototype implementation that uses selectivity estimates from MWL#253.
  The implementation is based on MWL#253. This patch in addition implements
    MDEV-402 Take into account the cost of subqueries during optimization
  in order to properly compute the cost of various subquery strategies.
      
  The pushdown of subqueries is controlled by the following new
  optimizer_switch: expensive_pred_static_pushdown=[on|off].
  
  EXPLAIN EXTENDED shows where a subquery was attached in the join plan
  by printing the subquery numbers in the Extra column as: "Subqueries #q1 #q2 ..." 
      
  The main idea is that for each complete (not necessarily optimal) plan,
  the optimizer chooses to attach each subquery to the join with the least
  partial join size. This choice affects the cost of the current plan
  because the cost of the subquery is taken into account depending on its
  position in the plan.
      
  Disabling this feature affects both the choice of join order, and the
  subsequent pushdown of subquery predicates.
-------------- next part --------------
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2013-03-27 22:41:02 +0000
+++ b/mysql-test/r/derived_view.result	2013-04-01 10:36:05 +0000
@@ -802,7 +802,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t3
 WHERE t3.a IN (SELECT v1.a FROM v1, t2 WHERE t2.a = v1.b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      <derived3>      ref     key1    key1    5       func    2       100.00  
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer (flat, BNL join)
 3       DERIVED t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using temporary; Using filesort
@@ -1099,9 +1099,9 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t       system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
-Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((6 = 7) or isnull(7))) and trigcond(((5 = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 a       b
@@ -1113,9 +1113,9 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
-Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((6 = 7) or isnull(7))) and trigcond(((5 = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 a       b
@@ -1127,9 +1127,9 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
-Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((6 = 7) or isnull(7))) and trigcond(((5 = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 #
@@ -1283,7 +1283,7 @@ SELECT * FROM (SELECT * FROM t1) AS t
 WHERE EXISTS (SELECT t2.a FROM t3 RIGHT JOIN t2 ON (t3.a = t2.a)
 WHERE t2.b < t.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subselects: 3
 3       DEPENDENT SUBQUERY      t2      system  NULL    NULL    NULL    NULL    1       100.00  
 3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:

=== modified file 'mysql-test/r/func_misc.result'
--- a/mysql-test/r/func_misc.result	2013-04-12 13:06:51 +0000
+++ b/mysql-test/r/func_misc.result	2013-04-01 10:36:05 +0000
@@ -322,7 +322,7 @@ explain extended
 select a from t1 left join v_merge on (a=e) where last_value(NULL,e) not in (select last_value(NULL,e) from vm);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
-1       PRIMARY tv      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where; Using join buffer (flat, BNL join)
+1       PRIMARY tv      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where; Subselects: 2; Using join buffer (flat, BNL join)
 2       DEPENDENT SUBQUERY      tv      system  NULL    NULL    NULL    NULL    1       100.00  
 Warnings:
 Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`tv`) on((`test`.`tv`.`e` = `test`.`t1`.`a`)) where (not(<expr_cache><last_value(NULL,`test`.`tv`.`e`)>(<in_optimizer>(last_value(NULL,`test`.`tv`.`e`),<exists>(select last_value(NULL,'1') from dual where trigcond((<cache>(last_value(NULL,`test`.`tv`.`e`)) = last_value(NULL,'1'))))))))
@@ -330,7 +330,7 @@ explain extended
 select a from t1 left join v_merge on (a=e) where e not in (select last_value(NULL,e) from vm);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
-1       PRIMARY tv      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where; Using join buffer (flat, BNL join)
+1       PRIMARY tv      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where; Subselects: 2; Using join buffer (flat, BNL join)
 2       DEPENDENT SUBQUERY      tv      system  NULL    NULL    NULL    NULL    1       100.00  
 Warnings:
 Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`tv`) on((`test`.`tv`.`e` = `test`.`t1`.`a`)) where (not(<expr_cache><`test`.`tv`.`e`>(<in_optimizer>(`test`.`tv`.`e`,<exists>(select last_value(NULL,'1') from dual where trigcond((<cache>(`test`.`tv`.`e`) = last_value(NULL,'1'))))))))

=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2013-02-28 21:47:29 +0000
+++ b/mysql-test/r/having.result	2013-04-01 10:36:05 +0000
@@ -629,11 +629,10 @@ FROM t1 , t2
 WHERE t2.f2 IN ( SELECT f3 FROM t3 )
 HAVING field1 < 's';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
-1       PRIMARY t1      index   NULL    f10     4       NULL    2       100.00  Using index
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
-Note    1003    select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` where <expr_cache><7>(<in_optimizer>(7,<exists>(select `test`.`t3`.`f3` from `test`.`t3` where (<cache>(7) = `test`.`t3`.`f3`)))) having (<cache>(`field1`) < 's')
+Note    1003    select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` where <in_optimizer>(7,<exists>(select `test`.`t3`.`f3` from `test`.`t3` where (7 = `test`.`t3`.`f3`))) having (<cache>(`field1`) < 's')
 set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 End of 5.2 tests

=== modified file 'mysql-test/r/mysqld--help.result'
--- a/mysql-test/r/mysqld--help.result	2013-05-28 11:28:31 +0000
+++ b/mysql-test/r/mysqld--help.result	2013-04-01 10:36:05 +0000
@@ -519,7 +519,8 @@
  outer_join_with_cache, partial_match_rowid_merge,
  partial_match_table_scan, semijoin, semijoin_with_cache,
  subquery_cache, table_elimination, extended_keys,
- exists_to_in } and val is one of {on, off, default}
+ exists_to_in, expensive_pred_static_pushdown} and val is
+ one of {on, off, default}
  --optimizer-use-condition-selectivity=# 
  Controls selectivity of which conditions the optimizer
  takes into account to calculate cardinality of a partial

=== modified file 'mysql-test/r/ps_2myisam.result'
--- a/mysql-test/r/ps_2myisam.result	2013-06-17 17:33:36 +0000
+++ b/mysql-test/r/ps_2myisam.result	2013-04-01 10:36:05 +0000
@@ -775,6 +775,9 @@ prepare stmt1 from 'select c4 FROM t9 wh
     c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
 execute stmt1 using @arg01, @arg02;
 c4
+Warnings:
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
 prepare stmt1 from ' select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b ) order by a ';
 execute stmt1 ;

=== modified file 'mysql-test/r/ps_3innodb.result'
--- a/mysql-test/r/ps_3innodb.result	2013-06-17 17:33:36 +0000
+++ b/mysql-test/r/ps_3innodb.result	2013-04-01 10:36:05 +0000
@@ -775,6 +775,9 @@ prepare stmt1 from 'select c4 FROM t9 wh
     c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
 execute stmt1 using @arg01, @arg02;
 c4
+Warnings:
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
 prepare stmt1 from ' select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b ) order by a ';
 execute stmt1 ;

=== modified file 'mysql-test/r/ps_4heap.result'
--- a/mysql-test/r/ps_4heap.result	2013-06-17 17:33:36 +0000
+++ b/mysql-test/r/ps_4heap.result	2013-04-01 10:36:05 +0000
@@ -776,6 +776,9 @@ prepare stmt1 from 'select c4 FROM t9 wh
     c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
 execute stmt1 using @arg01, @arg02;
 c4
+Warnings:
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
 prepare stmt1 from ' select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b ) order by a ';
 execute stmt1 ;

=== modified file 'mysql-test/r/ps_5merge.result'
--- a/mysql-test/r/ps_5merge.result	2013-06-17 17:33:36 +0000
+++ b/mysql-test/r/ps_5merge.result	2013-04-01 10:36:05 +0000
@@ -818,6 +818,9 @@ prepare stmt1 from 'select c4 FROM t9 wh
     c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
 execute stmt1 using @arg01, @arg02;
 c4
+Warnings:
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
 prepare stmt1 from ' select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b ) order by a ';
 execute stmt1 ;
@@ -4172,6 +4175,9 @@ prepare stmt1 from 'select c4 FROM t9 wh
     c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
 execute stmt1 using @arg01, @arg02;
 c4
+Warnings:
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
+Warning 1292    Truncated incorrect DOUBLE value: '123456789a123456789b123456789c'
 prepare stmt1 from ' select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b ) order by a ';
 execute stmt1 ;

=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2013-05-08 09:36:17 +0000
+++ b/mysql-test/r/select.result	2013-04-01 10:36:05 +0000
@@ -3454,10 +3454,9 @@ insert into t2 select A.a, B.a, C.a, C.a
 analyze table t2;
 Table   Op      Msg_type        Msg_text
 test.t2 analyze status  OK
-select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
-Z
 In next EXPLAIN, B.rows must be exactly 10:
-explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
+Straight join is used because this is not the most efficient plan
+explain select * from t2 A straight_join t2 B where A.a=5 and A.b=5 and A.C<5
 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where

=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result	2013-05-08 09:36:17 +0000
+++ b/mysql-test/r/select_jcl6.result	2013-04-01 10:36:05 +0000
@@ -3465,10 +3465,9 @@ insert into t2 select A.a, B.a, C.a, C.a
 analyze table t2;
 Table   Op      Msg_type        Msg_text
 test.t2 analyze status  OK
-select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
-Z
 In next EXPLAIN, B.rows must be exactly 10:
-explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
+Straight join is used because this is not the most efficient plan
+explain select * from t2 A straight_join t2 B where A.a=5 and A.b=5 and A.C<5
 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where; Rowid-ordered scan

=== modified file 'mysql-test/r/select_pkeycache.result'
--- a/mysql-test/r/select_pkeycache.result	2013-05-08 09:36:17 +0000
+++ b/mysql-test/r/select_pkeycache.result	2013-04-01 10:36:05 +0000
@@ -3454,10 +3454,9 @@ insert into t2 select A.a, B.a, C.a, C.a
 analyze table t2;
 Table   Op      Msg_type        Msg_text
 test.t2 analyze status  OK
-select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
-Z
 In next EXPLAIN, B.rows must be exactly 10:
-explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
+Straight join is used because this is not the most efficient plan
+explain select * from t2 A straight_join t2 B where A.a=5 and A.b=5 and A.C<5
 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where

=== modified file 'mysql-test/r/selectivity.result'
--- a/mysql-test/r/selectivity.result	2013-04-26 02:05:30 +0000
+++ b/mysql-test/r/selectivity.result	2013-04-01 10:36:05 +0000
@@ -69,7 +69,7 @@ s_acctbal desc, n_name, s_name, p_partke
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     0.31    Using where; Using temporary; Using filesort
 1       PRIMARY region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where; Using join buffer (flat, BNL join)
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2
 1       PRIMARY supplier        ALL     PRIMARY,i_s_nationkey   NULL    NULL    NULL    10      80.00   Using where; Using join buffer (flat, BNL join)
 1       PRIMARY nation  eq_ref  PRIMARY,i_n_regionkey   PRIMARY 4       dbt3_s001.supplier.s_nationkey  1       100.00  Using where
 2       DEPENDENT SUBQUERY      region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where
@@ -110,7 +110,7 @@ s_acctbal desc, n_name, s_name, p_partke
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where; Using temporary; Using filesort
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     2.08    Using where; Using join buffer (flat, BNL join)
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2
 1       PRIMARY supplier        ALL     PRIMARY,i_s_nationkey   NULL    NULL    NULL    10      80.00   Using where; Using join buffer (flat, BNL join)
 1       PRIMARY nation  eq_ref  PRIMARY,i_n_regionkey   PRIMARY 4       dbt3_s001.supplier.s_nationkey  1       100.00  Using where
 2       DEPENDENT SUBQUERY      region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where
@@ -138,7 +138,7 @@ and total_revenue = (select max(total_re
 order by s_suppkey;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using filesort
-1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where
+1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where; Subqueries: 2
 3       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    268     100.00  Using where; Using temporary; Using filesort
 2       SUBQUERY        <derived4>      ALL     NULL    NULL    NULL    NULL    268     100.00  
 4       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    268     100.00  Using where; Using temporary; Using filesort
@@ -159,7 +159,7 @@ and total_revenue = (select max(total_re
 order by s_suppkey;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using filesort
-1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where
+1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where; Subqueries: 2
 3       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    268     100.00  Using where; Using temporary; Using filesort
 2       SUBQUERY        <derived4>      ALL     NULL    NULL    NULL    NULL    268     100.00  
 4       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    268     100.00  Using where; Using temporary; Using filesort
@@ -188,7 +188,7 @@ group by p_brand, p_type, p_size
 order by supplier_cnt desc, p_brand, p_type, p_size;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     100.00  Using where; Using temporary; Using filesort
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Using index
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using where
 Warnings:
 Note    1003    select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`part`.`p_brand` <> 'Brand#11') and (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where (`dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_s
 uppkey`))))))))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size`
@@ -232,7 +232,7 @@ group by p_brand, p_type, p_size
 order by supplier_cnt desc, p_brand, p_type, p_size;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     16.67   Using where; Using temporary; Using filesort
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Using index
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using where
 Warnings:
 Note    1003    select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`part`.`p_brand` <> 'Brand#11') and (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where (`dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_s
 uppkey`))))))))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size`
@@ -276,7 +276,7 @@ group by p_brand, p_type, p_size
 order by supplier_cnt desc, p_brand, p_type, p_size;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     16.67   Using where; Using temporary; Using filesort
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Using index
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using where
 Warnings:
 Note    1003    select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`part`.`p_brand` <> 'Brand#11') and (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where (`dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_s
 uppkey`))))))))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size`
@@ -393,7 +393,7 @@ and not exists (select * from orders whe
 group by cntrycode
 order by cntrycode;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Using temporary; Using filesort
+1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Subqueries: 4 3; Using temporary; Using filesort
 4       DEPENDENT SUBQUERY      orders  ref     i_o_custkey     i_o_custkey     5       dbt3_s001.customer.c_custkey    15      100.00  Using index
 3       SUBQUERY        customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where
 Warnings:
@@ -434,7 +434,7 @@ and not exists (select * from orders whe
 group by cntrycode
 order by cntrycode;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Using temporary; Using filesort
+1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Subqueries: 4 3; Using temporary; Using filesort
 4       DEPENDENT SUBQUERY      orders  ref     i_o_custkey     i_o_custkey     5       dbt3_s001.customer.c_custkey    15      100.00  Using index
 3       SUBQUERY        customer        ALL     NULL    NULL    NULL    NULL    150     91.00   Using where
 Warnings:
@@ -484,7 +484,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       100.00  
 1       PRIMARY nation  eq_ref  PRIMARY PRIMARY 4       dbt3_s001.supplier.s_nationkey  1       100.00  Using where
 2       MATERIALIZED    part    ALL     PRIMARY NULL    NULL    NULL    200     100.00  Using where
-2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -538,7 +538,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       100.00  
 2       MATERIALIZED    part    ALL     PRIMARY NULL    NULL    NULL    200     4.17    Using where
-2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       15.14   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -592,7 +592,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY nation  ALL     PRIMARY NULL    NULL    NULL    25      4.00    Using where; Using temporary; Using filesort
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     7.03    Using where
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; FirstMatch(supplier)
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4; FirstMatch(supplier)
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       15.14   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -646,7 +646,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY nation  ALL     PRIMARY NULL    NULL    NULL    25      4.00    Using where; Using temporary; Using filesort
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     7.81    Using where
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; FirstMatch(supplier)
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4; FirstMatch(supplier)
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       15.14   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -700,7 +700,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY nation  ALL     PRIMARY NULL    NULL    NULL    25      4.00    Using where; Using temporary; Using filesort
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     7.81    Using where
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; FirstMatch(supplier)
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4; FirstMatch(supplier)
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       15.14   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -973,7 +973,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       100.00  
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using where; Using join buffer (flat, BNL join)
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using where; Subqueries: 2; Using join buffer (flat, BNL join)
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    7       100.00  
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using join buffer (flat, BNL join)
 Warnings:

=== modified file 'mysql-test/r/selectivity_innodb.result'
--- a/mysql-test/r/selectivity_innodb.result	2013-04-26 02:05:30 +0000
+++ b/mysql-test/r/selectivity_innodb.result	2013-04-01 10:36:05 +0000
@@ -72,7 +72,7 @@ s_acctbal desc, n_name, s_name, p_partke
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     0.31    Using where; Using temporary; Using filesort
 1       PRIMARY region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where; Using join buffer (flat, BNL join)
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2
 1       PRIMARY supplier        ALL     PRIMARY,i_s_nationkey   NULL    NULL    NULL    10      80.00   Using where; Using join buffer (flat, BNL join)
 1       PRIMARY nation  eq_ref  PRIMARY,i_n_regionkey   PRIMARY 4       dbt3_s001.supplier.s_nationkey  1       100.00  Using where
 2       DEPENDENT SUBQUERY      region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where
@@ -113,7 +113,7 @@ s_acctbal desc, n_name, s_name, p_partke
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where; Using temporary; Using filesort
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     2.08    Using where; Using join buffer (flat, BNL join)
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2
 1       PRIMARY supplier        ALL     PRIMARY,i_s_nationkey   NULL    NULL    NULL    10      80.00   Using where; Using join buffer (flat, BNL join)
 1       PRIMARY nation  eq_ref  PRIMARY,i_n_regionkey   PRIMARY 4       dbt3_s001.supplier.s_nationkey  1       100.00  Using where
 2       DEPENDENT SUBQUERY      region  ALL     PRIMARY NULL    NULL    NULL    5       20.00   Using where
@@ -141,7 +141,7 @@ and total_revenue = (select max(total_re
 order by s_suppkey;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY supplier        index   PRIMARY PRIMARY 4       NULL    10      100.00  
-1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where
+1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where; Subqueries: 2
 3       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    228     100.00  Using where; Using temporary; Using filesort
 2       SUBQUERY        <derived4>      ALL     NULL    NULL    NULL    NULL    228     100.00  
 4       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    228     100.00  Using where; Using temporary; Using filesort
@@ -162,7 +162,7 @@ and total_revenue = (select max(total_re
 order by s_suppkey;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY supplier        index   PRIMARY PRIMARY 4       NULL    10      100.00  
-1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where
+1       PRIMARY <derived3>      ref     key0    key0    5       dbt3_s001.supplier.s_suppkey    10      100.00  Using where; Subqueries: 2
 3       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    228     100.00  Using where; Using temporary; Using filesort
 2       SUBQUERY        <derived4>      ALL     NULL    NULL    NULL    NULL    228     100.00  
 4       DERIVED lineitem        range   i_l_shipdate    i_l_shipdate    4       NULL    228     100.00  Using where; Using temporary; Using filesort
@@ -191,7 +191,7 @@ group by p_brand, p_type, p_size
 order by supplier_cnt desc, p_brand, p_type, p_size;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     100.00  Using where; Using temporary; Using filesort
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Using index
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using where
 Warnings:
 Note    1003    select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`part`.`p_brand` <> 'Brand#11') and (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where (`dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_s
 uppkey`))))))))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size`
@@ -235,7 +235,7 @@ group by p_brand, p_type, p_size
 order by supplier_cnt desc, p_brand, p_type, p_size;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     16.67   Using where; Using temporary; Using filesort
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Using index
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using where
 Warnings:
 Note    1003    select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`part`.`p_brand` <> 'Brand#11') and (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where (`dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_s
 uppkey`))))))))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size`
@@ -279,7 +279,7 @@ group by p_brand, p_type, p_size
 order by supplier_cnt desc, p_brand, p_type, p_size;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     16.67   Using where; Using temporary; Using filesort
-1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Using index
+1       PRIMARY partsupp        ref     PRIMARY,i_ps_partkey    i_ps_partkey    4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    supplier        ALL     PRIMARY NULL    NULL    NULL    10      100.00  Using where
 Warnings:
 Note    1003    select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`part`.`p_brand` <> 'Brand#11') and (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where (`dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_s
 uppkey`))))))))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size`
@@ -396,7 +396,7 @@ and not exists (select * from orders whe
 group by cntrycode
 order by cntrycode;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Using temporary; Using filesort
+1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Subqueries: 4 3; Using temporary; Using filesort
 4       DEPENDENT SUBQUERY      orders  ref     i_o_custkey     i_o_custkey     5       dbt3_s001.customer.c_custkey    15      100.00  Using index
 3       SUBQUERY        customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where
 Warnings:
@@ -437,7 +437,7 @@ and not exists (select * from orders whe
 group by cntrycode
 order by cntrycode;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Using temporary; Using filesort
+1       SIMPLE  customer        ALL     NULL    NULL    NULL    NULL    150     100.00  Using where; Subqueries: 4 3; Using temporary; Using filesort
 4       DEPENDENT SUBQUERY      orders  ref     i_o_custkey     i_o_custkey     5       dbt3_s001.customer.c_custkey    15      100.00  Using index
 3       SUBQUERY        customer        ALL     NULL    NULL    NULL    NULL    150     91.00   Using where
 Warnings:
@@ -487,7 +487,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       100.00  
 1       PRIMARY nation  eq_ref  PRIMARY PRIMARY 4       dbt3_s001.supplier.s_nationkey  1       100.00  Using where
 2       MATERIALIZED    part    ALL     PRIMARY NULL    NULL    NULL    200     100.00  Using where
-2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -541,7 +541,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       100.00  
 2       MATERIALIZED    part    ALL     PRIMARY NULL    NULL    NULL    200     4.17    Using where
-2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       14.37   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -596,7 +596,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       100.00  
 2       MATERIALIZED    part    ALL     PRIMARY NULL    NULL    NULL    200     7.03    Using where
-2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       14.37   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -651,7 +651,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       100.00  
 2       MATERIALIZED    part    ALL     PRIMARY NULL    NULL    NULL    200     7.81    Using where
-2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       14.37   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -706,7 +706,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       100.00  
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       100.00  
 2       MATERIALIZED    part    ALL     PRIMARY NULL    NULL    NULL    200     7.81    Using where
-2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where
+2       MATERIALIZED    partsupp        ref     PRIMARY,i_ps_partkey,i_ps_suppkey       PRIMARY 4       dbt3_s001.part.p_partkey        3       100.00  Using where; Subqueries: 4
 4       DEPENDENT SUBQUERY      lineitem        ref     i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey        i_l_suppkey_partkey     10      dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey     8       14.37   Using where
 Warnings:
 Note    1276    Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
@@ -981,7 +981,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       100.00  
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using where; Using join buffer (flat, BNL join)
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using where; Subqueries: 2; Using join buffer (flat, BNL join)
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    7       100.00  
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using join buffer (flat, BNL join)
 Warnings:

=== modified file 'mysql-test/r/show_explain.result'
--- a/mysql-test/r/show_explain.result	2013-04-02 16:23:08 +0000
+++ b/mysql-test/r/show_explain.result	2013-04-01 10:36:05 +0000
@@ -445,7 +445,7 @@ id	select_type	table	type	possible_keys
 2       SUBQUERY        t2      const   PRIMARY PRIMARY 4       const   1       
 3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 set @show_explain_probe_select_id=1;
-set debug_dbug='+d,show_explain_probe_do_select';
+set debug_dbug='+d,show_explain_probe_do_select_const';
 SELECT * FROM t2 WHERE a = 
 (SELECT MAX(a) FROM t2 
 WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
@@ -494,7 +494,7 @@ WHERE a1 IN ( SELECT a1 FROM t2, t4 )
 );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      index   NULL    a1      5       NULL    20      Using where; Using index
-1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    87      Using join buffer (flat, BNL join)
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    87      Using where; Using join buffer (flat, BNL join)
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 set @show_explain_probe_select_id=1;
 set debug_dbug='+d,show_explain_probe_do_select';
@@ -506,7 +506,7 @@ WHERE a1 IN ( SELECT a1 FROM t2, t4 )
 show explain for $thr2;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      index   NULL    a1      5       NULL    20      Using where; Using index
-1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    87      Using join buffer (flat, BNL join)
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    87      Using where; Using join buffer (flat, BNL join)
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
 Note    1003    SELECT count(*) FROM t2, t3
@@ -695,7 +695,7 @@ SELECT * FROM t1, ( SELECT * FROM t2 ) A
 WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    20      Using where
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    20      
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    20      Using where
 3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    20      
 3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    20      Using where
 set @show_explain_probe_select_id=1;
@@ -705,7 +705,7 @@ WHERE a < ALL ( SELECT b FROM t1, t2 WHE
 show explain for $thr2;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    20      Using where
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    20      
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    20      Using where
 3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    20      
 3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    20      Using where
 Warnings:

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2013-07-17 19:24:29 +0000
+++ b/mysql-test/r/subselect.result	2013-04-01 10:36:05 +0000
@@ -187,9 +187,9 @@ a	b
 3       8
 explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subselects: 2
 2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
-3       UNION   t4      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+3       UNION   t4      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 4
 4       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 NULL    UNION RESULT    <union1,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 Warnings:
@@ -276,7 +276,7 @@ select * from t3 where a >= any (select
 7
 explain extended select * from t3 where a >= any (select b from t2);
 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  Using where
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
 Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
@@ -337,7 +337,7 @@ patient_uq	clinic_uq
 2       2
 explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t6      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t6      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t7      eq_ref  PRIMARY PRIMARY 4       test.t6.clinic_uq       1       100.00  Using index
 Warnings:
 Note    1276    Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
@@ -753,7 +753,7 @@ Note	1249	Select 2 was reduced during op
 Note    1003    select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    id      5       NULL    2       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    id      5       NULL    2       100.00  Using where; Subselects: 2; Using index
 2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 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    
@@ -1371,7 +1371,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ref     salary  salary  5       const   1       100.00  Using where
+1       PRIMARY t1      ref     salary  salary  5       const   1       100.00  Using where; Subselects: 2
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -1637,7 +1637,7 @@ select * from t3 where a >= all (select
 3
 explain extended select * from t3 where a >= all (select b from t2);
 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  Using where
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
 Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
@@ -1645,7 +1645,7 @@ select * from t3 where a >= some (select
 a
 explain extended select * from t3 where a >= some (select b from t2);
 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  Using where
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
 Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
@@ -1656,7 +1656,7 @@ select * from t3 where a >= all (select
 3
 explain extended select * from t3 where a >= all (select b from t2 group by 1);
 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  Using where
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
 Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
@@ -1664,7 +1664,7 @@ select * from t3 where a >= some (select
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
 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  Using where
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
 Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
@@ -1707,7 +1707,7 @@ select * from t3 where a > all (select m
 7
 explain extended select * from t3 where a > all (select max(b) from t2 group by a);
 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  Using where
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary
 Warnings:
 Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`))))
@@ -1877,13 +1877,13 @@ id	text
 12      text12
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
+1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
@@ -2419,7 +2419,7 @@ a	b
 3       4
 explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY up      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY up      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
@@ -3096,7 +3096,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -3108,7 +3108,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -4428,7 +4428,7 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1
 EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -4437,7 +4437,7 @@ EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2013-03-27 22:41:02 +0000
+++ b/mysql-test/r/subselect3.result	2013-04-01 10:36:05 +0000
@@ -38,7 +38,7 @@ explain extended
 select a, oref from t2 
 where a in (select max(ie) from t1 where oref=t2.oref group by grp);
 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  Using where
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; Using temporary
 Warnings:
 Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
@@ -711,11 +711,11 @@ INSERT INTO t2 VALUES (3), (1),(2), (5),
 EXPLAIN EXTENDED 
 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 1       PRIMARY t2      eq_ref  PRIMARY PRIMARY 4       test.t1.a       1       100.00  Using index
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
-Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))))))
+Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`)))))))
 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
 a
 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));

=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2013-03-27 22:41:02 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2013-04-01 10:36:05 +0000
@@ -48,7 +48,7 @@ explain extended
 select a, oref from t2 
 where a in (select max(ie) from t1 where oref=t2.oref group by grp);
 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  Using where
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; Using temporary
 Warnings:
 Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
@@ -721,11 +721,11 @@ INSERT INTO t2 VALUES (3), (1),(2), (5),
 EXPLAIN EXTENDED 
 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subselects: 2
 1       PRIMARY t2      eq_ref  PRIMARY PRIMARY 4       test.t1.a       1       100.00  Using index
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
-Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))))))
+Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`)))))))
 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
 a
 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));

=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2013-03-31 22:18:55 +0000
+++ b/mysql-test/r/subselect4.result	2013-04-01 10:36:05 +0000
@@ -1179,8 +1179,7 @@ SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
 WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t2      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      t1      index_subquery  f2      f2      4       func    2       Using index
 SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
@@ -1193,8 +1192,7 @@ SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
 WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t2      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       MATERIALIZED    t1      index   NULL    f2      4       NULL    2       Using index
 SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
@@ -2044,9 +2042,7 @@ EXPLAIN SELECT t1.f4
 FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
 WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
 SELECT t1.f4
 FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
@@ -2057,9 +2053,7 @@ EXPLAIN SELECT t1.f4
 FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
 WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
 SELECT t1.f4
 FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3

=== modified file 'mysql-test/r/subselect_exists2in.result'
--- a/mysql-test/r/subselect_exists2in.result	2013-03-27 22:41:02 +0000
+++ b/mysql-test/r/subselect_exists2in.result	2013-04-01 10:36:05 +0000
@@ -330,7 +330,7 @@ id	select_type	table	type	possible_keys
 3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2
-Note    1003    select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in ( <materialize> (select `test`.`t3`.`c` from `test`.`t3` where (`test`.`t3`.`c` is not null) ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1`
+Note    1003    select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1`
 SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
 ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )
 1
@@ -344,7 +344,7 @@ id	select_type	table	type	possible_keys
 3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2
-Note    1003    select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in ( <materialize> (select `test`.`t3`.`c` from `test`.`t3` where (`test`.`t3`.`c` is not null) ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1`
+Note    1003    select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1`
 SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
 ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )
 1

=== modified file 'mysql-test/r/subselect_extra_no_semijoin.result'
--- a/mysql-test/r/subselect_extra_no_semijoin.result	2012-05-29 21:18:53 +0000
+++ b/mysql-test/r/subselect_extra_no_semijoin.result	2013-04-01 10:36:05 +0000
@@ -45,11 +45,11 @@ explain extended
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 Warnings:
 Note    1276    Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note    1003    select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t1` `x1` where 0)))
+Note    1003    select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from dual where <in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t1` `x1` where 0))
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id      cur_date
@@ -57,11 +57,11 @@ explain extended
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 Warnings:
 Note    1276    Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note    1003    select 1 AS `id`,'2007-04-25' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t2` `x1` where 0)))
+Note    1003    select 1 AS `id`,'2007-04-25' AS `cur_date` from dual where <in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t2` `x1` where 0))
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id      cur_date

=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2013-05-02 20:10:43 +0000
+++ b/mysql-test/r/subselect_mat.result	2013-04-01 10:36:05 +0000
@@ -1374,8 +1374,7 @@ INSERT INTO t2 VALUES (10,0),(11,0);
 explain SELECT * FROM t1 JOIN t2 USING (f1)
 WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       MATERIALIZED    t1      system  NULL    NULL    NULL    NULL    1       
 SELECT * FROM t1 JOIN t2 USING (f1)
 WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
@@ -1795,7 +1794,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
-Note    1003    select 8 AS `a` from dual where <expr_cache><8>(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` having (<cache>(8) = <ref_null_helper>(min(`test`.`t1`.`a`))))))
+Note    1003    select 8 AS `a` from dual where <expr_cache><8>(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` having (8 = <ref_null_helper>(min(`test`.`t1`.`a`))))))
 DROP TABLE t1;
 #
 # BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON
@@ -2057,7 +2056,7 @@ set @@optimizer_switch='semijoin=off';
 EXPLAIN
 SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       MATERIALIZED    t2      system  NULL    NULL    NULL    NULL    1       
 SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
 c1_sum
@@ -2065,7 +2064,7 @@ NULL
 EXPLAIN
 SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       MATERIALIZED    t2      system  NULL    NULL    NULL    NULL    1       
 SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;
 c1_sum

=== modified file 'mysql-test/r/subselect_mat_cost.result'
--- a/mysql-test/r/subselect_mat_cost.result	2013-02-07 13:33:24 +0000
+++ b/mysql-test/r/subselect_mat_cost.result	2013-04-01 10:36:05 +0000
@@ -282,16 +282,18 @@ EXPLAIN
 SELECT Country.Name
 FROM Country, CountryLanguage 
 WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
-AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish'
+       OR CountryLanguage.Language = 'Russian')
 AND Code = Country;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY CountryLanguage range   PRIMARY,Language        Language        30      NULL    45      Using index condition; Using where; Rowid-ordered scan
+1       PRIMARY CountryLanguage range   PRIMARY,Language        Language        30      NULL    60      Using index condition; Using where; Rowid-ordered scan
 1       PRIMARY Country eq_ref  PRIMARY PRIMARY 3       world.CountryLanguage.Country   1       Using where
 2       MATERIALIZED    CountryLanguage ref     PRIMARY,Language        Language        30      const   47      Using index condition
 SELECT Country.Name
 FROM Country, CountryLanguage 
 WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
-AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish'
+       OR CountryLanguage.Language = 'Russian')
 AND Code = Country;
 Name
 Andorra
@@ -316,20 +318,37 @@ France
 Saint Pierre and Miquelon
 Uruguay
 Venezuela
+Russian Federation
+Azerbaijan
 Belgium
 Burundi
+Georgia
 Guadeloupe
 Haiti
+Svalbard and Jan Mayen
+Kazakstan
+Kyrgyzstan
+Latvia
+Lithuania
 Madagascar
 Martinique
 Mayotte
+Moldova
 French Polynesia
 Rwanda
 Sao Tome and Principe
 Switzerland
+Ukraine
 New Caledonia
+Uzbekistan
+Belarus
+Estonia
+Israel
 Lebanon
 Mauritius
+Finland
+Tajikistan
+Turkmenistan
 Andorra
 Italy
 Luxembourg

=== modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
--- a/mysql-test/r/subselect_mat_cost_bugs.result	2013-03-27 22:41:02 +0000
+++ b/mysql-test/r/subselect_mat_cost_bugs.result	2013-04-01 10:36:05 +0000
@@ -94,7 +94,7 @@ FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEF
 t1a ON (t1a.c2 = t1b.pk AND 2)
 WHERE t1.pk) ;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where; Subselects: 2
 2       DEPENDENT SUBQUERY      t1b     ALL     NULL    NULL    NULL    NULL    9       100.00  
 2       DEPENDENT SUBQUERY      t1a     ref     c2      c2      5       test.t1b.pk     1       100.00  Using where
 2       DEPENDENT SUBQUERY      t2      index   c3      c3      9       NULL    2       100.00  Using where; Using index; Using join buffer (flat, BNL join)

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2013-07-17 19:24:29 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2013-04-01 10:36:05 +0000
@@ -3102,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -3114,7 +3114,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2013-07-17 19:24:29 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2013-04-01 10:36:05 +0000
@@ -1881,13 +1881,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
@@ -3098,7 +3098,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -3110,7 +3110,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -6035,7 +6035,7 @@ SELECT col_int_nokey FROM ot
 WHERE col_varchar_nokey IN 
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY ot      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      it1     index_subquery  idx_cvk_cik     idx_cvk_cik     9       func,const      2       Using index; Using where
 SELECT col_int_nokey FROM ot 
 WHERE col_varchar_nokey IN 
@@ -6047,7 +6047,7 @@ SELECT col_int_nokey FROM ot
 WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY ot      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      it2     index_subquery  idx_cvk_cvk2_cik,idx_cvk_cik    idx_cvk_cvk2_cik        8       func,const      1       Using index; Using where
 SELECT col_int_nokey FROM ot
 WHERE (col_varchar_nokey, 'x') IN

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2013-07-17 19:24:29 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2013-04-01 10:36:05 +0000
@@ -1884,13 +1884,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
@@ -3102,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -3114,7 +3114,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2013-07-17 19:24:29 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2013-04-01 10:36:05 +0000
@@ -1887,7 +1887,7 @@ Note	1003	select `test`.`t1`.`id` AS `id
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))
@@ -3098,7 +3098,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -3110,7 +3110,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       SUBQUERY        t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/r/subselect_partial_match.result'
--- a/mysql-test/r/subselect_partial_match.result	2012-06-06 19:26:40 +0000
+++ b/mysql-test/r/subselect_partial_match.result	2013-04-01 10:36:05 +0000
@@ -69,6 +69,7 @@ insert into t1 values ('c',  NULL, 'a');
 insert into t2 values (NULL,  'x', NULL);
 insert into t2 values (NULL,  'y', NULL);
 insert into t2 values ('o',   'z', 'p');
+set @@expensive_subquery_limit=0;
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
@@ -173,6 +174,7 @@ id	select_type	table	type	possible_keys
 SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
 a1      a2      a3
 set @@optimizer_switch = @test_default_opt_switch;
+set @@expensive_subquery_limit=default;
 drop table t1, t2;
 -------------------------------------------------------------------------
 There is only one column in the subquery to complement the NULLs in the
@@ -183,6 +185,7 @@ create table t2 (b1 char(1), b2 char(1),
 insert into t1 values (NULL,  'g', NULL);
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
+set @@expensive_subquery_limit=0;
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
@@ -232,6 +235,7 @@ id	select_type	table	type	possible_keys
 SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
 a1      a2      a3
 set @@optimizer_switch = @test_default_opt_switch;
+set @@expensive_subquery_limit=default;
 drop table t1, t2;
 -------------------------------------------------------------------------
 The intersection of the NULL bitmaps is empty because the ranges
@@ -242,6 +246,7 @@ create table t2 (b1 char(1), b2 char(1),
 insert into t1 values ('b',  'g', NULL);
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
+set @@expensive_subquery_limit=0;
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
@@ -293,6 +298,7 @@ id	select_type	table	type	possible_keys
 SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
 a1      a2      a3
 b       g       NULL
+set @@expensive_subquery_limit=default;
 drop table t1, t2;
 -------------------------------------------------------------------------
 The intersection of the NULL bitmaps is non-empty, and there is a
@@ -304,6 +310,7 @@ insert into t1 values ('a',  'g', 'x', N
 insert into t2 values ('z',  NULL, 'y', 'x');
 insert into t2 values (NULL, NULL, 'x', 'y');
 insert into t2 values ('x',  'w', 'z', NULL);
+set @@expensive_subquery_limit=0;
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
@@ -352,6 +359,7 @@ id	select_type	table	type	possible_keys
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 a1      a2      a3      a4
+set @@expensive_subquery_limit=default;
 drop table t1, t2;
 -------------------------------------------------------------------------
 Value match in one row, but the NULL complement match in another.

=== modified file 'mysql-test/r/subselect_sj_mat.result'
--- a/mysql-test/r/subselect_sj_mat.result	2013-06-06 15:51:28 +0000
+++ b/mysql-test/r/subselect_sj_mat.result	2013-04-01 10:36:05 +0000
@@ -94,7 +94,7 @@ a1	a2
 explain extended
 select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2i     index   it2i1,it2i3     it2i1   #       NULL    5       40.00   Using where; Using index; LooseScan
+1       PRIMARY t2i     index   it2i1,it2i3     it2i1   #       NULL    5       50.00   Using where; Using index; LooseScan
 1       PRIMARY t1i     ref     _it1_idx        _it1_idx        #       _ref_   1       100.00  
 Warnings:
 Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
@@ -117,7 +117,7 @@ a1	a2
 explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2i     index   it2i1,it2i2,it2i3       it2i3   #       NULL    5       40.00   Using where; Using index; LooseScan
+1       PRIMARY t2i     index   it2i1,it2i2,it2i3       it2i3   #       NULL    5       50.00   Using where; Using index; LooseScan
 1       PRIMARY t1i     ref     _it1_idx        _it1_idx        #       _ref_   1       100.00  
 Warnings:
 Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` > '0'))
@@ -319,7 +319,7 @@ where (a1, a2) in (select b1, b2 from t2
 (a1, a2) in (select c1, c2 from t3i
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2i     index   it2i1,it2i2,it2i3       #       #       #       5       40.00   #
+1       PRIMARY t2i     index   it2i1,it2i2,it2i3       #       #       #       5       50.00   #
 1       PRIMARY t1i     ref     it1i1,it1i2,it1i3       #       #       #       1       100.00  #
 1       PRIMARY t3i     ref     it3i1,it3i2,it3i3       #       #       #       1       100.00  #
 1       PRIMARY t2i     ref     it2i1,it2i2,it2i3       #       #       #       2       100.00  #
@@ -407,7 +407,7 @@ id	select_type	table	type	possible_keys
 2       MATERIALIZED    t2      ALL     NULL    #       #       #       5       100.00  #
 4       MATERIALIZED    t3      ALL     NULL    #       #       #       4       100.00  #
 3       MATERIALIZED    t3      ALL     NULL    #       #       #       4       100.00  #
-7       UNION   t2i     index   it2i1,it2i2,it2i3       #       #       #       5       40.00   #
+7       UNION   t2i     index   it2i1,it2i2,it2i3       #       #       #       5       50.00   #
 7       UNION   t1i     ref     it1i1,it1i2,it1i3       #       #       #       1       100.00  #
 7       UNION   t3i     ref     it3i1,it3i2,it3i3       #       #       #       1       100.00  #
 7       UNION   t2i     ref     it2i1,it2i2,it2i3       #       #       #       2       100.00  #

=== modified file 'mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result'
--- a/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result	2013-02-25 23:20:17 +0000
+++ b/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result	2013-04-01 10:36:05 +0000
@@ -9,7 +9,7 @@ SELECT @global_start_value;
 select @old_session_opt_switch:=@@session.optimizer_switch,
 @old_global_opt_switch:=@@global.optimizer_switch;
 @old_session_opt_switch:=@@session.optimizer_switch     @old_global_opt_switch:=@@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=
 off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_tabl
 e_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 '#--------------------FN_DYNVARS_028_01------------------------#'
 SET @@session.engine_condition_pushdown = 0;
 Warnings:
@@ -212,7 +212,7 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@session.engine_condition_pushdown = TRUE;
 Warnings:
 Warning 1287    '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
@@ -220,7 +220,7 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-1       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off     index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+1       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off  index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@session.engine_condition_pushdown = FALSE;
 Warnings:
 Warning 1287    '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
@@ -228,7 +228,7 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@global.engine_condition_pushdown = TRUE;
 Warnings:
 Warning 1287    '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
@@ -236,7 +236,7 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       1       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquer
 y_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       1       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,par
 tial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@global.engine_condition_pushdown = FALSE;
 Warnings:
 Warning 1287    '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
@@ -244,31 +244,31 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@session.optimizer_switch = "engine_condition_pushdown=on";
 select @@session.engine_condition_pushdown,
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-1       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off     index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+1       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off  index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@session.optimizer_switch = "engine_condition_pushdown=off";
 select @@session.engine_condition_pushdown,
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@global.optimizer_switch = "engine_condition_pushdown=on";
 select @@session.engine_condition_pushdown,
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       1       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquer
 y_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       1       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,par
 tial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set @@global.optimizer_switch = "engine_condition_pushdown=off";
 select @@session.engine_condition_pushdown,
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 SET @@session.engine_condition_pushdown = @session_start_value;
 Warnings:
 Warning 1287    '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
@@ -287,4 +287,4 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off

=== modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result	2013-02-25 23:20:17 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result	2013-04-01 10:36:05 +0000
@@ -1,61 +1,61 @@
 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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+optimizer_switch        index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+optimizer_switch        index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+OPTIMIZER_SWITCH        index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+OPTIMIZER_SWITCH        index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+optimizer_switch        index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+optimizer_switch        index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+OPTIMIZER_SWITCH        index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+OPTIMIZER_SWITCH        index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off
 set optimizer_switch = replace(@@optimizer_switch, '=off', '=on');
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,expensive_pred_static_pushdown=on
 set global optimizer_switch=1.1;
 ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
 set global optimizer_switch=1e1;
@@ -67,4 +67,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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off

=== modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result	2013-02-25 23:20:17 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result	2013-04-01 10:36:05 +0000
@@ -2,4 +2,4 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-1       1       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off     index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquer
 y_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+1       1       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off  index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,par
 tial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off

=== modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result	2013-02-25 23:20:17 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result	2013-04-01 10:36:05 +0000
@@ -2,4 +2,4 @@ select @@session.engine_condition_pushdo
 @@global.engine_condition_pushdown,
 @@session.optimizer_switch, @@global.optimizer_switch;
 @@session.engine_condition_pushdown     @@global.engine_condition_pushdown      @@session.optimizer_switch      @@global.optimizer_switch
-0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subque
 ry_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
+0       0       index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,pa
 rtial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off,expensive_pred_static_pushdown=off

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2013-05-08 09:36:17 +0000
+++ b/mysql-test/t/select.test	2013-04-01 10:36:05 +0000
@@ -2954,9 +2954,10 @@ insert into t1 values (0),(1),(2),(3),(4
 create table t2 (a int, b int, c int, e int, primary key(a,b,c));
 insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;
 analyze table t2;
-select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
+--echo In next EXPLAIN, B.rows must be exactly 10:
+--echo Straight join is used because this is not the most efficient plan
 
-explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
+explain select * from t2 A straight_join t2 B where A.a=5 and A.b=5 and A.C<5
           and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 drop table t1, t2;
 

=== modified file 'mysql-test/t/show_explain.test'
--- a/mysql-test/t/show_explain.test	2013-04-02 16:23:08 +0000
+++ b/mysql-test/t/show_explain.test	2013-04-01 10:36:05 +0000
@@ -467,7 +467,7 @@ explain SELECT * FROM t2 WHERE a =
        );
 
 set @show_explain_probe_select_id=1;
-set debug_dbug='+d,show_explain_probe_do_select';
+set debug_dbug='+d,show_explain_probe_do_select_const';
 send SELECT * FROM t2 WHERE a = 
        (SELECT MAX(a) FROM t2 
         WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)

=== modified file 'mysql-test/t/subselect_mat_cost.test'
--- a/mysql-test/t/subselect_mat_cost.test	2011-11-22 17:04:38 +0000
+++ b/mysql-test/t/subselect_mat_cost.test	2013-04-01 10:36:05 +0000
@@ -209,13 +209,15 @@ EXPLAIN
 SELECT Country.Name
 FROM Country, CountryLanguage 
 WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
-  AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+  AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish'
+       OR CountryLanguage.Language = 'Russian')
   AND Code = Country;
 
 SELECT Country.Name
 FROM Country, CountryLanguage 
 WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
-  AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+  AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish'
+       OR CountryLanguage.Language = 'Russian')
   AND Code = Country;
 
 -- echo

=== modified file 'mysql-test/t/subselect_partial_match.test'
--- a/mysql-test/t/subselect_partial_match.test	2011-11-26 22:23:00 +0000
+++ b/mysql-test/t/subselect_partial_match.test	2013-04-01 10:36:05 +0000
@@ -80,6 +80,8 @@ insert into t2 values (NULL,  'x', NULL)
 insert into t2 values (NULL,  'y', NULL);
 insert into t2 values ('o',   'z', 'p');
 
+set @@expensive_subquery_limit=0;
+
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
@@ -140,6 +142,7 @@ SELECT * from t1 where (a1, a2, a3) NOT
 SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
 
 set @@optimizer_switch = @test_default_opt_switch;
+set @@expensive_subquery_limit=default;
 
 drop table t1, t2;
 
@@ -155,6 +158,8 @@ insert into t1 values (NULL,  'g', NULL)
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
 
+set @@expensive_subquery_limit=0;
+
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
@@ -180,6 +185,7 @@ SELECT * from t1 where (a1, a2, a3) NOT
 SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
 
 set @@optimizer_switch = @test_default_opt_switch;
+set @@expensive_subquery_limit=default;
 
 drop table t1, t2;
 
@@ -195,6 +201,8 @@ insert into t1 values ('b',  'g', NULL);
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
 
+set @@expensive_subquery_limit=0;
+
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
@@ -219,6 +227,8 @@ EXPLAIN
 SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
 SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
 
+set @@expensive_subquery_limit=default;
+
 drop table t1, t2;
 
 --echo -------------------------------------------------------------------------
@@ -234,6 +244,8 @@ insert into t2 values ('z',  NULL, 'y',
 insert into t2 values (NULL, NULL, 'x', 'y');
 insert into t2 values ('x',  'w', 'z', NULL);
 
+set @@expensive_subquery_limit=0;
+
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
@@ -258,6 +270,8 @@ EXPLAIN
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 
+set @@expensive_subquery_limit=default;
+
 drop table t1, t2;
 
 --echo -------------------------------------------------------------------------

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2013-07-17 19:24:29 +0000
+++ b/sql/item.cc	2013-04-01 10:36:05 +0000
@@ -465,7 +465,7 @@ int Item::save_str_value_in_field(Field
   with_subselect= 0;
   cmp_context= IMPOSSIBLE_RESULT;
    /* Initially this item is not attached to any JOIN_TAB. */
-  join_tab_idx= MAX_TABLES;
+  join_tab_idx= cur_join_tab_idx= -1;
 
   /* Put item in free list so that we can free all items at end */
   THD *thd= current_thd;
@@ -495,6 +495,7 @@ int Item::save_str_value_in_field(Field
 */
 Item::Item(THD *thd, Item *item):
   join_tab_idx(item->join_tab_idx),
+  cur_join_tab_idx(item->cur_join_tab_idx),
   is_expensive_cache(-1),
   rsize(0),
   str_value(item->str_value),
@@ -582,7 +583,7 @@ void Item::cleanup()
   DBUG_PRINT("enter", ("this: %p", this));
   fixed=0;
   marker= 0;
-  join_tab_idx= MAX_TABLES;
+  join_tab_idx= cur_join_tab_idx= -1;
   if (orig_name)
     name= orig_name;
   DBUG_VOID_RETURN;
@@ -604,6 +605,19 @@ bool Item::cleanup_processor(uchar *arg)
 
 
 /**
+  Add the cost of an item to the cost of previous items in the
+  containing expression tree.
+
+  @param arg  the cost of the previously visited items in the exression tree
+*/
+
+bool Item::sum_cost_processor(uchar *arg)
+{
+  *((double*)arg)= *((double*)arg) + get_cost();
+  return false;
+}
+
+/**
   rename item (used for views, cleanup() return original name).
 
   @param new_name       new name of item;
@@ -9648,7 +9662,7 @@ table_map Item_ref_null_helper::used_tab
 #ifndef DBUG_OFF
 
 /* Debugger help function */
-static char dbug_item_print_buf[256];
+static char dbug_item_print_buf[2048];
 
 const char *dbug_print_item(Item *item)
 {

=== modified file 'sql/item.h'
--- a/sql/item.h	2013-07-17 19:24:29 +0000
+++ b/sql/item.h	2013-04-01 10:36:05 +0000
@@ -578,10 +578,12 @@ class Item {
     make_cond_for_table procedure. During query execution, this item is
     evaluated when the join loop reaches the corresponding JOIN_TAB.
 
-    If the value of join_tab_idx >= MAX_TABLES, this means that there is no
-    corresponding JOIN_TAB.
+    If join_tab_idx < 0, this means that there is no corresponding JOIN_TAB.
+
+    cur_join_tab_idx is used during join optimization to determine the best
+    position where to push an expression.
   */
-  uint join_tab_idx;
+  int join_tab_idx, cur_join_tab_idx;
 
 public:
   static void *operator new(size_t size) throw ()
@@ -1172,12 +1174,14 @@ class Item {
   virtual bool remove_fixed(uchar * arg) { fixed= 0; return 0; }
   virtual bool cleanup_processor(uchar *arg);
   virtual bool collect_item_field_processor(uchar * arg) { return 0; }
+  virtual bool collect_item_subselect_processor(uchar * arg) { return 0; }
   virtual bool add_field_to_set_processor(uchar * arg) { return 0; }
   virtual bool find_item_in_field_list_processor(uchar *arg) { return 0; }
   virtual bool find_item_processor(uchar *arg);
   virtual bool change_context_processor(uchar *context) { return 0; }
   virtual bool reset_query_id_processor(uchar *query_id_arg) { return 0; }
   virtual bool is_expensive_processor(uchar *arg) { return 0; }
+  virtual bool sum_cost_processor(uchar *arg);
   virtual bool register_field_in_read_map(uchar *arg) { return 0; }
   virtual bool register_field_in_write_map(uchar *arg) { return 0; }
   virtual bool enumerate_field_refs_processor(uchar *arg) { return 0; }
@@ -1450,6 +1454,14 @@ class Item {
       is_expensive_cache= walk(&Item::is_expensive_processor, 0, (uchar*)0);
     return test(is_expensive_cache);
   }
+  /**
+    The default cost to evaluate a 'cheap' predicate.
+    TODO:
+    Notice that this also adds the cost of accessing Fields (via Item_field).
+    Perhaps this cost should be smaller or 0.
+  */
+  virtual double get_cost() { return (1 / (double)TIME_FOR_COMPARE); }
+
   virtual Field::geometry_type get_geometry_type() const
     { return Field::GEOM_GEOMETRY; };
   String *check_well_formed_result(String *str, bool send_error= 0);
@@ -1485,15 +1497,20 @@ class Item {
   virtual Item_equal *find_item_equal(COND_EQUAL *cond_equal) { return NULL; }
   /**
     Set the join tab index to the minimal (left-most) JOIN_TAB to which this
-    Item is attached. The number is an index is depth_first_tab() traversal
+    Item is attached. The number is an index in depth_first_tab() traversal
     order.
   */
-  virtual void set_join_tab_idx(uint join_tab_idx_arg)
+  virtual void set_join_tab_idx(int join_tab_idx_arg)
   {
-    if (join_tab_idx_arg < join_tab_idx)
+    if (join_tab_idx < 0 || join_tab_idx_arg < join_tab_idx)
       join_tab_idx= join_tab_idx_arg;
   }
-  virtual uint get_join_tab_idx() { return join_tab_idx; }
+  virtual int get_join_tab_idx() { return join_tab_idx; }
+  virtual void set_cur_join_tab_idx(int join_tab_idx_arg)
+  {
+    cur_join_tab_idx= join_tab_idx_arg;
+  }
+  virtual int get_cur_join_tab_idx() { return cur_join_tab_idx; }
 
   table_map view_used_tables(TABLE_LIST *view)
   {

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2013-07-17 19:24:29 +0000
+++ b/sql/item_cmpfunc.cc	2013-04-01 10:36:05 +0000
@@ -461,7 +461,17 @@ static bool convert_const_to_int(THD *th
       field_item->field_type() != MYSQL_TYPE_YEAR)
     return 1;
 
-  if ((*item)->const_item() && !(*item)->is_expensive())
+  /*
+    An item can be converted to an int if:
+    - is constant, and
+    - it is not a subquery (or contains a subquery), and
+    - it is cheap to compute.
+    The reason for the second requirement is to prevent subquery
+    optimization/execution during the JOIN::prepare phase, because this
+    function is called during the prepare phase as well.
+  */
+  if ((*item)->const_item() &&
+      !(*item)->with_subselect && !(*item)->is_expensive())
   {
     TABLE *table= field->table;
     ulonglong orig_sql_mode= thd->variables.sql_mode;
@@ -1478,7 +1488,8 @@ bool Item_in_optimizer::fix_left(THD *th
   eval_not_null_tables(NULL);
   with_sum_func= args[0]->with_sum_func;
   with_field= args[0]->with_field;
-  if ((const_item_cache= args[0]->const_item()))
+  if (!(args[0]->with_subselect || args[0]->is_expensive()) &&
+      (const_item_cache= args[0]->const_item()))
   {
     cache->store(args[0]);
     cache->cache_value();

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2013-07-17 19:24:29 +0000
+++ b/sql/item_cmpfunc.h	2013-04-01 10:36:05 +0000
@@ -263,8 +263,10 @@ class Item_in_optimizer: public Item_boo
   virtual Item *expr_cache_insert_transformer(uchar *thd_arg);
   bool is_expensive_processor(uchar *arg);
   bool is_expensive();
-  void set_join_tab_idx(uint join_tab_idx_arg)
+  virtual void set_join_tab_idx(int join_tab_idx_arg)
   { args[1]->set_join_tab_idx(join_tab_idx_arg); }
+  virtual int get_join_tab_idx()
+  { return args[1]->get_join_tab_idx(); }
   virtual void get_cache_parameters(List<Item> &parameters);
   bool is_top_level_item();
   bool eval_not_null_tables(uchar *opt_arg);

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-07-17 19:24:29 +0000
+++ b/sql/item_subselect.cc	2013-04-01 10:36:05 +0000
@@ -49,8 +49,8 @@ int check_and_do_in_subquery_rewrites(JO
 
 Item_subselect::Item_subselect():
   Item_result_field(), value_assigned(0), own_engine(0), thd(0), old_engine(0), 
-  used_tables_cache(0), have_to_be_excluded(0), const_item_cache(1),
-  inside_first_fix_fields(0), done_first_fix_fields(FALSE), 
+  used_tables_cache(0), pushdown_tables(0), have_to_be_excluded(0),
+  const_item_cache(1), inside_first_fix_fields(0), done_first_fix_fields(FALSE),
   expr_cache(0), forced_const(FALSE), substitution(0), engine(0), eliminated(FALSE),
   changed(0), is_correlated(FALSE)
 {
@@ -400,6 +400,21 @@ bool Item_subselect::set_fake_select_as_
 }
 
 
+bool Item_subselect::collect_item_subselect_processor(uchar * arg)
+{
+  List<Item_subselect> *subs_list= (List<Item_subselect>*) arg;
+  List_iterator<Item_subselect> subs_list_it(*subs_list);
+  Item_subselect *cur_subs;
+  while ((cur_subs= subs_list_it++))
+  {
+    if (cur_subs->eq(this, 1))
+      return false; /* Already in the set. */
+  }
+  subs_list->push_back(this);
+  return false;
+}
+
+
 bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select, 
                                        Item *item)
 {
@@ -551,7 +566,7 @@ void Item_subselect::recalc_used_tables(
 */
 bool Item_subselect::is_expensive()
 {
-  double examined_rows= 0;
+  double examined_rows= 0, cur_examined_rows= 0;
 
   for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
   {
@@ -560,19 +575,20 @@ bool Item_subselect::is_expensive()
       continue;
 
     /*
-      Subqueries whose result is known after optimization are not expensive.
-      Such subqueries have all tables optimized away, thus have no join plan.
+      The cost of a subquery is known if it was optimized, and it is either
+      executable, or was already executed and cleaned up.
     */
-    if (cur_join->optimized &&
-        (cur_join->zero_result_cause || !cur_join->tables_list))
-      return false;
+    if (cur_join->have_query_plan != JOIN::QEP_EXECUTABLE &&
+        cur_join->have_query_plan != JOIN::QEP_DELETED)
+      return true;
 
     /*
-      If a subquery is not optimized we cannot estimate its cost. A subquery is
-      considered optimized if it has a join plan.
+      If JOIN::optimize determined that the subquery has an empty result, or
+      this is a table-less subquery, then then subquery is cheap.
     */
-    if (!(cur_join->optimized && cur_join->join_tab))
-      return true;
+    if (!cur_join->table_count || !cur_join->tables_list ||
+        cur_join->zero_result_cause)
+      return false;
 
     if (sl->first_inner_unit())
     {
@@ -583,13 +599,64 @@ bool Item_subselect::is_expensive()
       return true;
     }
 
-    examined_rows+= cur_join->get_examined_rows();
+    if ((cur_examined_rows= cur_join->get_examined_rows_estimate()) < 0)
+      return true;
+    examined_rows+= cur_examined_rows;
   }
 
   return (examined_rows > thd->variables.expensive_subquery_limit);
 }
 
 
+/**
+  Compute the cost of a subquery and all its subqueries.
+
+  @return  total subquery cost in terms of JOIN::best_read
+*/
+
+double Item_subselect::get_cost()
+{
+  double total_cost= 0;
+  for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+  {
+    JOIN *inner_join= sl->join;
+    total_cost+=
+      inner_join->best_read + /* The cost of this subquery. */
+      /* The cost of the subqueries of the subquery. */
+      inner_join->total_subquery_cost();
+  }
+  return total_cost;
+}
+
+
+/**
+  Check if the subquery predicate has only one possible execution strategy.
+
+  @details
+  If there is only one possible execution strategy for a subquery predicate,
+  the optimizer doesn't need to know the number of times the subquery will be
+  executed in order to choose the better strategy. In this case the underlying
+  subquery can be optimized before optimizing the outer query.
+
+  @retval FALSE     more than one strategies are possible
+  @retval TRUE      only one strategy is possible
+*/
+
+bool Item_subselect::has_single_strategy() const
+{
+  if (!is_in_predicate())
+    return true;
+  else
+  {
+    Item_in_subselect *in_subs= (Item_in_subselect*) this;
+    if (!in_subs->test_strategy(SUBS_MATERIALIZATION) ||
+        !in_subs->test_strategy(SUBS_IN_TO_EXISTS))
+      return true;
+  }
+  return false;
+}
+
+
 bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
                           uchar *argument)
 {
@@ -888,6 +955,12 @@ void Item_subselect::update_used_tables(
       if (!(used_tables_cache & ~engine->upper_select_const_tables()))
         const_item_cache= 1;
     }
+    /*
+      TODO: when merged with dynamic pushdown, pushdown_tables should not be
+      added to the used tables. Instead, dynamic pushdown could use this table
+      as the initial dynamic position, or it may ignore this estimate altogether.
+    */
+    used_tables_cache|= pushdown_tables;
   }
 }
 
@@ -5045,7 +5118,7 @@ double get_fanout_with_deps(JOIN *join,
         !tab->emb_sj_nest && 
         tab->records_read != 0)
     {
-      fanout *= rows2double(tab->records_read);
+      fanout *= tab->records_read;
     }
   } 
   return fanout;

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2013-03-27 22:41:02 +0000
+++ b/sql/item_subselect.h	2013-04-01 10:36:05 +0000
@@ -55,6 +55,8 @@ class Item_subselect :public Item_result
   subselect_engine *old_engine;
   /* cache of used external tables */
   table_map used_tables_cache;
+  /* tables in the QEP where this predicate should be pushed to */
+  table_map pushdown_tables;
   /* allowed number of columns (1 for single value subqueries) */
   uint max_columns;
   /* where subquery is placed */
@@ -133,13 +135,14 @@ class Item_subselect :public Item_result
 
   Item_subselect();
 
-  virtual subs_type substype() { return UNKNOWN_SUBS; }
-  bool is_in_predicate()
+  virtual subs_type substype() const { return UNKNOWN_SUBS; }
+  bool is_in_predicate() const
   {
     return (substype() == Item_subselect::IN_SUBS ||
             substype() == Item_subselect::ALL_SUBS ||
             substype() == Item_subselect::ANY_SUBS);
   }
+  bool has_single_strategy() const;
 
   /*
     We need this method, because some compilers do not allow 'this'
@@ -182,6 +185,7 @@ class Item_subselect :public Item_result
   void make_const()
   { 
     used_tables_cache= 0;
+    pushdown_tables= 0;
     const_item_cache= 0;
     forced_const= TRUE; 
   }
@@ -190,6 +194,7 @@ class Item_subselect :public Item_result
   table_map not_null_tables() const { return 0; }
   bool const_item() const;
   inline table_map get_used_tables_cache() { return used_tables_cache; }
+  void add_pushdown_table(table_map map) { pushdown_tables |= map; }
   Item *get_tmp_table_item(THD *thd);
   void update_used_tables();
   virtual void print(String *str, enum_query_type query_type);
@@ -234,6 +239,8 @@ class Item_subselect :public Item_result
     @retval FALSE otherwise
   */
   bool is_expensive_processor(uchar *arg) { return is_expensive(); }
+  virtual double get_cost();
+  bool collect_item_subselect_processor(uchar * arg);
 
   /**
     Get the SELECT_LEX structure associated with this Item.
@@ -261,6 +268,8 @@ class Item_subselect :public Item_result
   friend bool convert_join_subqueries_to_semijoins(JOIN *join);
 };
 
+typedef bool (Item_subselect::*subselect_filter)() const;
+
 /* single value subselect */
 
 class Item_cache;
@@ -274,7 +283,7 @@ class Item_singlerow_subselect :public I
   {}
 
   void cleanup();
-  subs_type substype() { return SINGLEROW_SUBS; }
+  subs_type substype() const { return SINGLEROW_SUBS; }
 
   void reset();
   void no_rows_in_result();
@@ -370,7 +379,7 @@ class Item_exists_subselect :public Item
     emb_on_expr_nest(NULL), optimizer(0), exists_transformed(0)
   {}
 
-  subs_type substype() { return EXISTS_SUBS; }
+  subs_type substype() const { return EXISTS_SUBS; }
   void reset() 
   {
     eliminated= FALSE;
@@ -577,7 +586,7 @@ class Item_in_subselect :public Item_exi
     pushed_cond_guards(NULL), func(NULL), is_jtbm_merged(FALSE),
     is_jtbm_const_tab(FALSE), upper_item(0) {}
   void cleanup();
-  subs_type substype() { return IN_SUBS; }
+  subs_type substype() const { return IN_SUBS; }
   void reset() 
   {
     eliminated= FALSE;
@@ -717,7 +726,7 @@ class Item_allany_subselect :public Item
 
   void cleanup();
   // only ALL subquery has upper not
-  subs_type substype() { return all?ALL_SUBS:ANY_SUBS; }
+  subs_type substype() const { return all?ALL_SUBS:ANY_SUBS; }
   bool select_transformer(JOIN *join);
   void create_comp_func(bool invert) { func= func_creator(invert); }
   virtual void print(String *str, enum_query_type query_type);

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2013-07-17 19:24:29 +0000
+++ b/sql/opt_subselect.cc	2013-04-01 10:36:05 +0000
@@ -4907,7 +4907,7 @@ static void remove_subq_pushed_predicate
 
 
 /**
-  Optimize all subqueries of a query that were not flattened into a semijoin.
+  Optimize all non-IN subqueries that were not flattened into a semijoin.
 
   @details
   Optimize all immediate children subqueries of a query.
@@ -4924,7 +4924,12 @@ static void remove_subq_pushed_predicate
 
 bool JOIN::optimize_unflattened_subqueries()
 {
-  return select_lex->optimize_unflattened_subqueries(false);
+  return select_lex->optimize_subqueries(&Item_subselect::has_single_strategy);
+}
+
+bool JOIN::optimize_in_subqueries()
+{
+  return select_lex->optimize_subqueries(NULL);
 }
 
 /**
@@ -4958,7 +4963,7 @@ bool JOIN::optimize_constant_subqueries(
     not for EXPLAIN.
   */
   select_lex->options&= ~SELECT_DESCRIBE;
-  res= select_lex->optimize_unflattened_subqueries(true);
+  res= select_lex->optimize_subqueries(&Item_subselect::const_item);
   select_lex->options= save_options;
   return res;
 }

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2013-08-06 20:33:18 +0000
+++ b/sql/sql_delete.cc	2013-04-01 10:36:05 +0000
@@ -126,7 +126,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   }
 
   /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */
-  if (select_lex->optimize_unflattened_subqueries(false))
+  if (select_lex->optimize_subqueries(NULL))
     DBUG_RETURN(TRUE);
 
   const_cond= (!conds || conds->const_item());

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2013-07-17 19:24:29 +0000
+++ b/sql/sql_lex.cc	2013-04-01 10:36:05 +0000
@@ -3461,7 +3461,7 @@ bool st_select_lex::add_index_hint (THD
   @retval TRUE      error occurred.
 */
 
-bool st_select_lex::optimize_unflattened_subqueries(bool const_only)
+bool st_select_lex::optimize_subqueries(subselect_filter filter)
 {
   for (SELECT_LEX_UNIT *un= first_inner_unit(); un; un= un->next_unit())
   {
@@ -3476,11 +3476,8 @@ bool st_select_lex::optimize_unflattened
           continue;
       }
 
-      if (const_only && !subquery_predicate->const_item())
-      {
-        /* Skip non-constant subqueries if the caller asked so. */
+      if (filter && !((subquery_predicate->*filter)()))
         continue;
-      }
 
       bool empty_union_result= true;
       bool is_correlated_unit= false;
@@ -4203,7 +4200,9 @@ int st_select_lex::print_explain(select_
                                  bool *printed_anything)
 {
   int res;
-  if (join && join->have_query_plan == JOIN::QEP_AVAILABLE)
+  if (join &&
+      (join->have_query_plan == JOIN::QEP_EXPLAINABLE ||
+       join->have_query_plan == JOIN::QEP_EXECUTABLE))
   {
     /*
       There is a number of reasons join can be marked as degenerate, so all

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2013-07-17 19:24:29 +0000
+++ b/sql/sql_lex.h	2013-04-01 10:36:05 +0000
@@ -1026,7 +1026,7 @@ class st_select_lex: public st_select_le
 
   void clear_index_hints(void) { index_hints= NULL; }
   bool is_part_of_union() { return master_unit()->is_union(); }
-  bool optimize_unflattened_subqueries(bool const_only);
+  bool optimize_subqueries(subselect_filter filter);
   /* Set the EXPLAIN type for this subquery. */
   void set_explain_type(bool on_the_fly);
   bool handle_derived(LEX *lex, uint phases);

=== modified file 'sql/sql_priv.h'
--- a/sql/sql_priv.h	2013-07-17 19:24:29 +0000
+++ b/sql/sql_priv.h	2013-04-01 10:36:05 +0000
@@ -228,7 +228,8 @@ template <class T> bool valid_buffer_ran
 #define OPTIMIZER_SWITCH_TABLE_ELIMINATION         (1ULL << 26)
 #define OPTIMIZER_SWITCH_EXTENDED_KEYS             (1ULL << 27)
 #define OPTIMIZER_SWITCH_EXISTS_TO_IN              (1ULL << 28)
-#define OPTIMIZER_SWITCH_USE_CONDITION_SELECTIVITY (1ULL << 29)
+#define OPTIMIZER_SWITCH_EXPENSIVE_PRED_STATIC_PUSHDOWN (1ULL << 29)
+#define OPTIMIZER_SWITCH_USE_CONDITION_SELECTIVITY (1ULL << 30)
 
 #define OPTIMIZER_SWITCH_DEFAULT   (OPTIMIZER_SWITCH_INDEX_MERGE | \
                                     OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
@@ -250,7 +251,7 @@ template <class T> bool valid_buffer_ran
                                     OPTIMIZER_SWITCH_SUBQUERY_CACHE | \
                                     OPTIMIZER_SWITCH_SEMIJOIN | \
                                     OPTIMIZER_SWITCH_FIRSTMATCH | \
-                                    OPTIMIZER_SWITCH_LOOSE_SCAN )
+                                    OPTIMIZER_SWITCH_LOOSE_SCAN)
 /*
   Replication uses 8 bytes to store SQL_MODE in the binary log. The day you
   use strictly more than 64 bits by adding one more define above, you should

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-07-17 19:24:29 +0000
+++ b/sql/sql_select.cc	2013-04-01 10:36:05 +0000
@@ -1013,9 +1013,16 @@ int JOIN::optimize()
     short-circuit because optimized==TRUE.
   */
   if (!res && have_query_plan != QEP_DELETED)
-    have_query_plan= QEP_AVAILABLE;
+  {
+    if (have_query_plan == QEP_INCOMPLETE)
+      have_query_plan= QEP_EXPLAINABLE;
+    else
+      have_query_plan= QEP_EXECUTABLE;
+  }
   return res;
 }
+
+
 /**
   global select optimisation.
 
@@ -1046,6 +1053,7 @@ JOIN::optimize_inner()
 
   set_allowed_join_cache_types();
   need_distinct= TRUE;
+  examined_rows_estimate= -1;
 
   /* Run optimize phase for all derived tables/views used in this SELECT. */
   if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE))
@@ -1730,12 +1738,18 @@ TODO: make view to decide if it is possi
   if (!(select_options & SELECT_DESCRIBE))
     init_ftfuncs(thd, select_lex, test(order));
 
-  if (optimize_unflattened_subqueries())
+  if (optimize_in_subqueries())
     DBUG_RETURN(1);
   
   int res;
   if ((res= rewrite_to_index_subquery_engine(this)) != -1)
+  {
+    /*
+      This subquery has been rewritten to direct index access via handler calls
+      instead of going through JOIN::exec.
+    */
     DBUG_RETURN(res);
+  }
   if (setup_subquery_caches())
     DBUG_RETURN(-1);
 
@@ -1863,7 +1877,7 @@ TODO: make view to decide if it is possi
     Even with zero matching rows, subqueries in the HAVING clause may
     need to be evaluated if there are aggregate functions in the query.
   */
-  if (optimize_unflattened_subqueries())
+  if (optimize_unflattened_subqueries() || optimize_in_subqueries())
     DBUG_RETURN(1);
   error= 0;
 
@@ -3884,6 +3898,9 @@ make_join_statistics(JOIN *join, List<TA
 
   if (join->const_tables != join->table_count)
     optimize_keyuse(join, keyuse_array);
+
+  if (join->optimize_unflattened_subqueries())
+    DBUG_RETURN(1);
    
   if (optimize_semijoin_nests(join, all_table_map))
     DBUG_RETURN(TRUE); /* purecov: inspected */
@@ -6879,28 +6896,57 @@ void JOIN::get_prefix_cost_and_fanout(ui
 
 
 /**
+  Compute the total cost of all subqueries of a (sub)query.
+
+  @return  the cost of all subqueries
+*/
+
+double JOIN::total_subquery_cost()
+{
+  double total_cost= 0;
+  for (SELECT_LEX_UNIT *un= select_lex->first_inner_unit();
+       un; un= un->next_unit())
+  {
+    Item_subselect *subquery_predicate= un->item;
+    if (!subquery_predicate)
+      continue;
+
+    total_cost+= subquery_predicate->get_cost();
+  }
+  return total_cost;
+}
+
+
+/**
   Estimate the number of rows that query execution will read.
 
   @todo This is a very pessimistic upper bound. Use join selectivity
   when available to produce a more realistic number.
 */
 
-double JOIN::get_examined_rows()
+double JOIN::get_examined_rows_estimate()
 {
-  ha_rows examined_rows;
-  double prev_fanout= 1;
-  JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS);
-  JOIN_TAB *prev_tab= tab;
+  if (examined_rows_estimate >= 0)
+    return examined_rows_estimate;
+  else if (!(have_query_plan == QEP_EXPLAINABLE ||
+             have_query_plan == QEP_EXECUTABLE))
+    return -1;
+  else
+  {
+    double prev_fanout= 1;
+    JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS);
+    JOIN_TAB *prev_tab= tab;
 
-  examined_rows= tab->get_examined_rows();
+    examined_rows_estimate= tab->get_examined_rows_estimate();
 
-  while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab)))
-  {
-    prev_fanout *= prev_tab->records_read;
-    examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout);
-    prev_tab= tab;
+    while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab)))
+    {
+      prev_fanout *= prev_tab->records_read;
+      examined_rows_estimate+= tab->get_examined_rows_estimate() * prev_fanout;
+      prev_tab= tab;
+    }
+    return examined_rows_estimate;
   }
-  return examined_rows;
 }
 
 
@@ -7374,15 +7420,15 @@ best_extension_by_limited_search(JOIN
                                                           remaining_tables &
                                                           ~real_table_bit);
       join->positions[idx].cond_selectivity= pushdown_cond_selectivity;
-      double partial_join_cardinality= current_record_count *
-                                        pushdown_cond_selectivity;
+      join->positions[idx].partial_join_cardinality= current_record_count *
+                                                     pushdown_cond_selectivity;
       if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) & allowed_tables )
       { /* Recursively expand the current partial plan */
         swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
         if (best_extension_by_limited_search(join,
                                              remaining_tables & ~real_table_bit,
                                              idx + 1,
-                                             partial_join_cardinality,
+                                             join->positions[idx].partial_join_cardinality,
                                              current_read_time,
                                              search_depth - 1,
                                              prune_level,
@@ -7400,12 +7446,100 @@ best_extension_by_limited_search(JOIN
             join->positions[join->const_tables].table->table)
           /* We have to make a temp table */
           current_read_time+= current_record_count;
+
+        /*
+          For each subquery find the partial join with the least cardinality
+          in order to minimize the number of subquery evaluations.
+        */
+        if (optimizer_flag(thd, OPTIMIZER_SWITCH_EXPENSIVE_PRED_STATIC_PUSHDOWN) &&
+            join->conds && join->conds->with_subselect
+            /* && join->conds->is_expensive()*/)
+        {
+          /*
+            Find and store the POSITION with the least partial result cardinality
+            between the current and last POSITION in the query plan.
+          */
+          POSITION *min_pos= NULL;
+          for (POSITION *p_pos= join->positions + idx,
+                 *p_end= join->positions + join->const_tables;
+               p_pos >= p_end ;
+               p_pos--)
+          {
+            if (!min_pos ||
+                p_pos->partial_join_cardinality <=
+                min_pos->partial_join_cardinality)
+              min_pos= p_pos;
+            p_pos->least_card_pos= min_pos;
+          }
+
+          /*
+            Make subquery predicates be dependent on the table that is in the
+            partial join with the least cardinality.
+
+            TODO: this is a non-generic solution that works only for subquery
+            predicates, but not for other potentially expensive predicates.
+            However it is fast, because it traversers a list of all subqueries
+            instead of traversing the WHERE clause expression tree.
+          */
+          for (SELECT_LEX_UNIT *un= join->select_lex->first_inner_unit(); un;
+               un= un->next_unit())
+          {
+            Item_subselect *subs_predicate= un->item;
+            table_map subs_tables;
+            POSITION *last_pos;
+
+            if (!subs_predicate)
+              continue;
+
+            subs_tables= subs_predicate->used_tables();
+            last_pos= NULL;
+            /* Find the last query plan node on which the subquery depends. */
+            for (POSITION *p_pos= join->positions + idx,
+                   *p_end= join->positions + join->const_tables;
+                 p_pos >= p_end ;
+                 p_pos--)
+            {
+              if (p_pos->table->table->map & subs_tables)
+                last_pos= p_pos;
+            }
+            /*
+              Remember the position where the subquery is pushed,
+              update the cost of the POSITION where the subquery is pushed to,
+              recompute the total cost of the query plan.
+            */
+            if (last_pos && last_pos->least_card_pos)
+            {
+              double subs_cost;
+              uint least_card_idx= last_pos->least_card_pos - join->positions;
+              subs_predicate->set_cur_join_tab_idx(least_card_idx);
+              subs_cost= last_pos->least_card_pos->partial_join_cardinality *
+                         subs_predicate->get_cost();
+              last_pos->least_card_pos->read_time+= subs_cost;
+              current_read_time+= subs_cost;
+            }
+          }
+        }
+
         if (current_read_time < join->best_read)
         {
           memcpy((uchar*) join->best_positions, (uchar*) join->positions,
                  sizeof(POSITION) * (idx + 1));
-          join->record_count= partial_join_cardinality;
+          join->record_count= join->positions[idx].partial_join_cardinality;
           join->best_read= current_read_time - 0.001;
+          /*
+            Store the current best choice of pushdown POSITIONs for all subqueries
+          */
+          for (SELECT_LEX_UNIT *un= join->select_lex->first_inner_unit(); un;
+               un= un->next_unit())
+          {
+            Item_subselect *subs_predicate= un->item;
+            if (subs_predicate)
+            {
+              /* reset the value first so it can be set */
+              subs_predicate->set_join_tab_idx(-1);
+              subs_predicate->set_join_tab_idx(subs_predicate->get_cur_join_tab_idx());
+            }
+          }
         }
         DBUG_EXECUTE("opt", print_plan(join, idx+1,
                                        current_record_count,
@@ -7415,6 +7549,14 @@ best_extension_by_limited_search(JOIN
       }
       restore_prev_nj_state(s);
       restore_prev_sj_state(remaining_tables, s, idx);
+      /* Restore the table dependencies of all subquery predicates. */
+      for (SELECT_LEX_UNIT *un= join->select_lex->first_inner_unit(); un;
+           un= un->next_unit())
+      {
+        Item_subselect *subs_predicate= un->item;
+        if (subs_predicate)
+          subs_predicate->set_cur_join_tab_idx(-1);
+      }
     }
   }
   DBUG_RETURN(FALSE);
@@ -8008,6 +8150,26 @@ get_best_combination(JOIN *join)
 
   used_tables= OUTER_REF_TABLE_BIT;             // Outer row is already read
 
+  if (optimizer_flag(thd, OPTIMIZER_SWITCH_EXPENSIVE_PRED_STATIC_PUSHDOWN))
+  {
+    for (SELECT_LEX_UNIT *un= join->select_lex->first_inner_unit(); un;
+         un= un->next_unit())
+    {
+      Item_subselect *subs_predicate= un->item;
+      int idx;
+      table_map map;
+      if (!subs_predicate)
+        continue;
+      idx= subs_predicate->get_join_tab_idx();
+      if (idx >= 0)
+      {
+        DBUG_ASSERT((uint)idx < join->table_count);
+        map= join->best_positions[idx].table->table->map;
+        subs_predicate->add_pushdown_table(map);
+      }
+    }
+  }
+
   fix_semijoin_strategies_for_picked_join_order(join);
   
   JOIN_TAB_RANGE *root_range;
@@ -8048,7 +8210,8 @@ get_best_combination(JOIN *join)
            sub-order
       */
       SJ_MATERIALIZATION_INFO *sjm= cur_pos->table->emb_sj_nest->sj_mat_info;
-      j->records= j->records_read= (ha_rows)(sjm->is_sj_scan? sjm->rows : 1);
+      j->records_read= sjm->is_sj_scan? sjm->rows : 1;
+      j->records= (ha_rows) j->records_read;
       j->cond_selectivity= 1.0;
       JOIN_TAB *jt;
       JOIN_TAB_RANGE *jt_range;
@@ -8112,7 +8275,7 @@ get_best_combination(JOIN *join)
       Save records_read in JOIN_TAB so that select_describe()/etc don't have
       to access join->best_positions[]. 
     */
-    j->records_read= (ha_rows)join->best_positions[tablenr].records_read;
+    j->records_read= join->best_positions[tablenr].records_read;
     j->cond_selectivity= join->best_positions[tablenr].cond_selectivity;
     join->map2table[j->table->tablenr]= j;
 
@@ -10202,6 +10365,8 @@ uint check_join_cache_usage(JOIN_TAB *ta
     if ((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache)) &&
         ((options & SELECT_DESCRIBE) || !tab->cache->init()))
     {
+      if (options & SELECT_DESCRIBE)
+        join->have_query_plan= JOIN::QEP_INCOMPLETE;
       tab->icp_other_tables_ok= FALSE;
       return (2-test(!prev_cache));
     }
@@ -10237,6 +10402,8 @@ uint check_join_cache_usage(JOIN_TAB *ta
       if ((tab->cache= new JOIN_CACHE_BNLH(join, tab, prev_cache)) &&
           ((options & SELECT_DESCRIBE) || !tab->cache->init()))
       {
+        if (options & SELECT_DESCRIBE)
+          join->have_query_plan= JOIN::QEP_INCOMPLETE;
         tab->icp_other_tables_ok= FALSE;        
         return (4-test(!prev_cache));
       }
@@ -10257,7 +10424,11 @@ uint check_join_cache_usage(JOIN_TAB *ta
           prev_cache= 0;
         if ((tab->cache= new JOIN_CACHE_BKA(join, tab, flags, prev_cache)) &&
             ((options & SELECT_DESCRIBE) || !tab->cache->init()))
+        {
+          if (options & SELECT_DESCRIBE)
+            join->have_query_plan= JOIN::QEP_INCOMPLETE;
           return (6-test(!prev_cache));
+        }
         goto no_join_cache;
       }
       else
@@ -10267,7 +10438,9 @@ uint check_join_cache_usage(JOIN_TAB *ta
         if ((tab->cache= new JOIN_CACHE_BKAH(join, tab, flags, prev_cache)) &&
             ((options & SELECT_DESCRIBE) || !tab->cache->init()))
         {
-         tab->idx_cond_fact_out= FALSE;
+          if (options & SELECT_DESCRIBE)
+            join->have_query_plan= JOIN::QEP_INCOMPLETE;
+          tab->idx_cond_fact_out= FALSE;
           return (8-test(!prev_cache));
         }
         goto no_join_cache;
@@ -10811,9 +10984,9 @@ double JOIN_TAB::scan_time()
   @todo: why not use JOIN_TAB::found_records
 */
 
-ha_rows JOIN_TAB::get_examined_rows()
+double JOIN_TAB::get_examined_rows_estimate()
 {
-  ha_rows examined_rows;
+  double examined_rows;
 
   if (select && select->quick && use_quick != 2)
     examined_rows= select->quick->records;
@@ -10843,7 +11016,7 @@ ha_rows JOIN_TAB::get_examined_rows()
     }
   }
   else
-    examined_rows= (ha_rows) records_read; 
+    examined_rows= records_read;
 
   return examined_rows;
 }
@@ -16459,6 +16632,13 @@ do_select(JOIN *join,List<Item> *fields,
       sufficient to check only the condition pseudo_bits_cond.
     */
     DBUG_ASSERT(join->outer_ref_cond == NULL);
+    DBUG_EXECUTE_IF("show_explain_probe_do_select_const", 
+                    if (dbug_user_var_equals_int(join->thd, 
+                                                 "show_explain_probe_select_id", 
+                                                 join->select_lex->select_number))
+                          dbug_serve_apcs(join->thd, 1);
+                   );
+
     if (!join->pseudo_bits_cond || join->pseudo_bits_cond->val_int())
     {
       error= (*end_select)(join, 0, 0);
@@ -18549,6 +18729,22 @@ make_cond_for_table(THD *thd, Item *cond
 }
 
 
+static int compare_items_by_cost(Item* it1, Item* it2, void *arg)
+{
+  double cost1= 0, cost2= 0;
+
+  it1->walk(&Item::sum_cost_processor, false, (uchar*) &cost1);
+  it2->walk(&Item::sum_cost_processor, false, (uchar*) &cost2);
+
+  if (cost1 == cost2)
+    return 0;
+  if (cost1 > cost2)
+    return -1;
+  else
+    return 1;
+}
+
+
 static Item *
 make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
                               table_map tables, table_map used_table,
@@ -18596,6 +18792,11 @@ make_cond_for_table_from_pred(THD *thd,
         new_cond->used_tables_cache=
           ((Item_cond_and*) cond)->used_tables_cache &
           tables;
+        /*
+          Move expensive conditions to the end of the AND list to reduce the
+          number of times they are evaluated.
+        */
+        bubble_sort<Item>(new_cond->argument_list(), compare_items_by_cost, NULL);
         return new_cond;
       }
     }
@@ -18625,6 +18826,11 @@ make_cond_for_table_from_pred(THD *thd,
       new_cond->fix_fields(thd, 0);
       new_cond->used_tables_cache= ((Item_cond_or*) cond)->used_tables_cache;
       new_cond->top_level_item();
+      /*
+        Move expensive conditions to the end of the AND list to reduce the
+        number of times they are evaluated.
+      */
+      bubble_sort<Item>(new_cond->argument_list(), compare_items_by_cost, NULL);
       return new_cond;
     }
   }
@@ -22289,7 +22495,8 @@ int JOIN::print_explain(select_result_si
   DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
                       (ulong)join->select_lex, join->select_lex->type,
                       message ? message : "NULL"));
-  DBUG_ASSERT(on_the_fly? have_query_plan == QEP_AVAILABLE: TRUE);
+  DBUG_ASSERT(on_the_fly? (have_query_plan == QEP_EXPLAINABLE ||
+                           have_query_plan == QEP_EXECUTABLE) : TRUE);
   /* Don't log this into the slow query log */
 
   if (!on_the_fly)
@@ -22585,7 +22792,7 @@ int JOIN::print_explain(select_result_si
       }
       else
       {
-        ha_rows examined_rows= tab->get_examined_rows();
+        double examined_rows= tab->get_examined_rows_estimate();
 
         item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, 
                                          MY_INT64_NUM_DECIMAL_DIGITS));
@@ -22598,7 +22805,7 @@ int JOIN::print_explain(select_result_si
           {
             double pushdown_cond_selectivity= tab->cond_selectivity;          
             if (pushdown_cond_selectivity == 1.0)
-              f= (float) (100.0 * tab->records_read / examined_rows);
+              f= (100.0 * (float)tab->records_read) / examined_rows;
             else
               f= (float) (100.0 * pushdown_cond_selectivity);
           }
@@ -22689,6 +22896,24 @@ int JOIN::print_explain(select_result_si
             }
             else
               extra.append(STRING_WITH_LEN("; Using where"));
+            /* Add the id's of the subselects to the table they are pushed to. */
+            if (explain_flags & DESCRIBE_EXTENDED)
+            {
+              List<Item_subselect> subs_list;
+              tab->select_cond->walk(&Item::collect_item_subselect_processor, 0,
+                                     (uchar*) &subs_list);
+              if (subs_list.elements)
+              {
+                List_iterator_fast<Item_subselect> subs_it(subs_list);
+                Item_subselect *subs;
+                extra.append(STRING_WITH_LEN("; Subqueries:"));
+                while ((subs= subs_it++))
+                {
+                  extra.append(STRING_WITH_LEN(" "));
+                  extra.append_ulonglong(subs->get_select_lex()->select_number);
+                }
+              }
+            }
           }
         }
         if (table_list /* SJM bushes don't have table_list */ &&

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2013-07-17 19:24:29 +0000
+++ b/sql/sql_select.h	2013-04-01 10:36:05 +0000
@@ -289,8 +289,8 @@ typedef struct st_join_table {
   */
   double        read_time;
   
-  /* psergey-todo: make the below have type double, like POSITION::records_read? */
-  ha_rows       records_read;
+  /* Copy of POSITION::records_read, set by get_best_combination() */
+  double       records_read;
   
   /* The selectivity of the conditions that can be pushed to the table */ 
   double        cond_selectivity;  
@@ -522,7 +522,7 @@ typedef struct st_join_table {
     return (is_hash_join_key_no(key) ? hj_key : table->key_info+key);
   }
   double scan_time();
-  ha_rows get_examined_rows();
+  double get_examined_rows_estimate();
   bool preread_init();
 
   bool is_sjm_nest() { return test(bush_children); }
@@ -767,6 +767,8 @@ typedef struct st_position :public Sql_a
 {
   /* The table that's put into join order */
   JOIN_TAB *table;
+  /*  The JOIN_TAB after this one with the least partial join cardinality */
+  st_position *least_card_pos;
 
   /*
     The "fanout": number of output rows that will be produced (after
@@ -777,6 +779,7 @@ typedef struct st_position :public Sql_a
 
   /* The selectivity of the pushed down conditions */
   double cond_selectivity; 
+  double partial_join_cardinality;
 
   /* 
     Cost accessing the table in course of the entire complete join execution,
@@ -917,6 +920,7 @@ class JOIN :public Sql_alloc
   void restore_query_plan(Join_plan_state *restore_from);
   /* Choose a subquery plan for a table-less subquery. */
   bool choose_tableless_subquery_plan();
+  double examined_rows_estimate;
 
 public:
   JOIN_TAB *join_tab, **best_ref;
@@ -1214,13 +1218,20 @@ class JOIN :public Sql_alloc
   
   bool union_part; ///< this subselect is part of union 
 
-  enum join_optimization_state { NOT_OPTIMIZED=0,
-                                 OPTIMIZATION_IN_PROGRESS=1,
-                                 OPTIMIZATION_DONE=2};
   bool optimized; ///< flag to avoid double optimization in EXPLAIN
   bool initialized; ///< flag to avoid double init_execution calls
   
-  enum { QEP_NOT_PRESENT_YET, QEP_AVAILABLE, QEP_DELETED} have_query_plan;
+  enum {QEP_NOT_PRESENT_YET,
+        /* A query plan exists. It is being optimized, but is still incomplete. */
+        QEP_INCOMPLETE,
+        /*
+          A query plan exists, but it is usable only for EXPLAIN because
+          some of its structures are incomplete.
+        */
+        QEP_EXPLAINABLE,
+        /* A query plan exists, and is ready for execution. */
+        QEP_EXECUTABLE,
+        QEP_DELETED} have_query_plan;
 
   /*
     Additional WHERE and HAVING predicates to be considered for IN=>EXISTS
@@ -1274,6 +1285,7 @@ class JOIN :public Sql_alloc
     found_records= 0;
     fetch_limit= HA_POS_ERROR;
     examined_rows= 0;
+    examined_rows_estimate= -1;
     exec_tmp_table1= 0;
     exec_tmp_table2= 0;
     sortorder= 0;
@@ -1352,6 +1364,7 @@ class JOIN :public Sql_alloc
   bool alloc_func_list();
   bool flatten_subqueries();
   bool optimize_unflattened_subqueries();
+  bool optimize_in_subqueries();
   bool optimize_constant_subqueries();
   bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields,
                           bool before_group_by, bool recompute= FALSE);
@@ -1452,7 +1465,9 @@ class JOIN :public Sql_alloc
   void get_prefix_cost_and_fanout(uint n_tables, 
                                   double *read_time_arg,
                                   double *record_count_arg);
-  double get_examined_rows();
+  double get_examined_rows_estimate();
+  double total_subquery_cost();
+
   /* defined in opt_subselect.cc */
   bool transform_max_min_subquery();
   /* True if this JOIN is a subquery under an IN predicate. */

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2013-07-17 19:24:29 +0000
+++ b/sql/sql_update.cc	2013-04-01 10:36:05 +0000
@@ -358,7 +358,7 @@ int mysql_update(THD *thd,
   }
 
   /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */
-  if (select_lex->optimize_unflattened_subqueries(false))
+  if (select_lex->optimize_subqueries(NULL))
     DBUG_RETURN(TRUE);
 
   if (select_lex->inner_refs_list.elements &&

=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2013-07-17 19:24:29 +0000
+++ b/sql/sys_vars.cc	2013-04-01 10:36:05 +0000
@@ -1739,6 +1739,7 @@ export const char *optimizer_switch_name
   "table_elimination",
   "extended_keys",
   "exists_to_in",
+  "expensive_pred_static_pushdown",
   "default", NullS
 };
 /** propagates changes to @@engine_condition_pushdown */
@@ -1781,7 +1782,8 @@ static Sys_var_flagset Sys_optimizer_swi
         "subquery_cache, "
         "table_elimination, "
         "extended_keys, "
-        "exists_to_in "
+       "exists_to_in, "
+       "expensive_pred_static_pushdown"
        "} and val is one of {on, off, default}",
        SESSION_VAR(optimizer_switch), CMD_LINE(REQUIRED_ARG),
        optimizer_switch_names, DEFAULT(OPTIMIZER_SWITCH_DEFAULT),



More information about the commits mailing list