[Commits] Rev 3676: 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:28 EEST 2013


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

------------------------------------------------------------
revno: 3676
revision-id: timour at askmonty.org-20130417090331-wha1ww0jgy9camug
parent: timour at askmonty.org-20130401103605-5o4in6ng6rd8bzbi
fixes bug: https://mariadb.atlassian.net/browse/MDEV-83
committer: timour at askmonty.org
branch nick: 10.0-md83-merge
timestamp: Wed 2013-04-17 12:03:31 +0300
message:
  MDEV-83  Cost-based choice for the pushdown of expensive predicates to joined tables
        
  Updated test results.
-------------- next part --------------
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/derived_view.result	2013-04-17 09:03:31 +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; Subselects: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 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
@@ -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; Subselects: 3
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 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-01 10:36:05 +0000
+++ b/mysql-test/r/func_misc.result	2013-04-17 09:03:31 +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; Subselects: 2; Using join buffer (flat, BNL join)
+1       PRIMARY tv      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where; Subqueries: 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; Subselects: 2; Using join buffer (flat, BNL join)
+1       PRIMARY tv      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where; Subqueries: 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/mysqld--help.result'
--- a/mysql-test/r/mysqld--help.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/mysqld--help.result	2013-04-17 09:03:31 +0000
@@ -1130,7 +1130,10 @@ skip-slave-start FALSE
 slave-compressed-protocol FALSE
 slave-exec-mode STRICT
 slave-max-allowed-packet 1073741824
-slave-net-timeout 3600
+slave-net-timeout Warning:    8 bytes lost, allocated by T at 0 at mysys/my_malloc.c:250, sql/keycaches.cc:39, sql/keycaches.cc:201, sql/keycaches.cc:219, sql/mysqld.cc:8000, mysys/my_getopt.c:1251, mysys/my_getopt.c:182, sql/mysqld.cc:8058
+Warning: 48 bytes lost, allocated by T at 0 at sql/sql_list.h:625, sql/keycaches.cc:201, sql/keycaches.cc:219, sql/mysqld.cc:8000, mysys/my_getopt.c:1251, mysys/my_getopt.c:182, sql/mysqld.cc:8058, sql/mysqld.cc:3733
+Memory lost: 56 bytes in 2 chunks
+ 3600
 slave-skip-errors (No default value)
 slave-sql-verify-checksum TRUE
 slave-transaction-retries 10

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect.result	2013-04-17 09:03:31 +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; Subselects: 2
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 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; Subselects: 4
+3       UNION   t4      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t6      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 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; Subselects: 2; Using index
+1       PRIMARY t2      index   NULL    id      5       NULL    2       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t1      ref     salary  salary  5       const   1       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY up      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 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
@@ -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; Subselects: 2
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 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; Subselects: 2
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 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-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect3.result	2013-04-17 09:03:31 +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; Subselects: 2
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 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,7 +711,7 @@ 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; Subselects: 2
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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:

=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2013-04-17 09:03:31 +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; Subselects: 2
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 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,7 +721,7 @@ 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; Subselects: 2
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 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:

=== modified file 'mysql-test/r/subselect_cache.result'
--- a/mysql-test/r/subselect_cache.result	2012-05-17 10:46:05 +0000
+++ b/mysql-test/r/subselect_cache.result	2013-04-17 09:03:31 +0000
@@ -3353,7 +3353,7 @@ set optimizer_switch='default,semijoin=o
 explain extended
 select * from t1 where a in (select b from t2);
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`b` from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`))))

=== modified file 'mysql-test/r/subselect_exists2in.result'
--- a/mysql-test/r/subselect_exists2in.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_exists2in.result	2013-04-17 09:03:31 +0000
@@ -64,7 +64,7 @@ SELECT * FROM t1 WHERE EXISTS ( SELECT a
 explain extended
 SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    aa      4       NULL    2       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    aa      4       NULL    2       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t3      ALL     bb      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
@@ -77,7 +77,7 @@ SELECT * FROM t1 WHERE EXISTS ( SELECT a
 explain extended
 SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    aa      4       NULL    2       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    aa      4       NULL    2       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -90,7 +90,7 @@ SELECT * FROM t1 WHERE EXISTS ( SELECT a
 explain extended
 SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    aa      4       NULL    2       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    aa      4       NULL    2       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t3      ALL     bb      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
@@ -309,7 +309,7 @@ SELECT * FROM t1 WHERE
 EXISTS ( SELECT * FROM t2 WHERE b = a )
 OR a > 0;
 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; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -396,7 +396,7 @@ c	c
 explain extended
 SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    aa      8       NULL    2       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    aa      8       NULL    2       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t3      index_subquery  bb      bb      8       func,func       2       100.00  Using index; Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -410,7 +410,7 @@ c	c
 explain extended
 SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    aa      8       NULL    2       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    aa      8       NULL    2       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t3      index   NULL    bb      8       NULL    2       100.00  Using index
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -424,7 +424,7 @@ c	c
 explain extended
 SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.a and t3.b1 = t1.a1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    aa      8       NULL    2       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    aa      8       NULL    2       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t3      ref     bb      bb      8       test.t1.a,test.t1.a1    2       100.00  Using index
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -460,7 +460,7 @@ SELECT * FROM t1, t2
 WHERE a <= v.d AND b = v.b 
 );
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
 Warnings:
@@ -538,7 +538,7 @@ WHERE a <= alias2.a AND c = alias1.b
 ) OR alias1 .a = 'foo';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    4       100.00  
-1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Using join buffer (flat, BNL join)
+1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 2; Using join buffer (flat, BNL join)
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Using join buffer (flat, BNL join)
 Warnings:
@@ -564,7 +564,7 @@ SELECT * FROM t1 WHERE NOT EXISTS ( SELE
 explain extended
 SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a );
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -603,7 +603,7 @@ explain extended
 SELECT * FROM t1 AS alias
 WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY alias   ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY alias   ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
@@ -656,7 +656,7 @@ SELECT 1 FROM t2 WHERE b = alias1.a AND
 ) OR a = 5;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    2       100.00  
-1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
+1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2; Using join buffer (flat, BNL join)
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.alias1.a' of SELECT #2 was resolved in SELECT #1

=== modified file 'mysql-test/r/subselect_exists_to_in.result'
--- a/mysql-test/r/subselect_exists_to_in.result	2013-07-17 19:24:29 +0000
+++ b/mysql-test/r/subselect_exists_to_in.result	2013-04-17 09:03:31 +0000
@@ -191,9 +191,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; Subqueries: 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; Subqueries: 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:
@@ -280,7 +280,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; Subqueries: 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`))))
@@ -757,7 +757,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; Subqueries: 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    
@@ -1375,7 +1375,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; Subqueries: 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`))
@@ -1641,7 +1641,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; Subqueries: 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`))))
@@ -1649,7 +1649,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; Subqueries: 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`))))
@@ -1660,7 +1660,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; Subqueries: 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`))))
@@ -1668,7 +1668,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; Subqueries: 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`))))
@@ -1711,7 +1711,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; Subqueries: 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`))))
@@ -1881,13 +1881,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; Subqueries: 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; Subqueries: 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
@@ -3101,7 +3101,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'
@@ -3113,7 +3113,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'
@@ -4443,7 +4443,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; Subqueries: 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/subselect_extra_no_semijoin.result'
--- a/mysql-test/r/subselect_extra_no_semijoin.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_extra_no_semijoin.result	2013-04-17 09:03:31 +0000
@@ -71,7 +71,7 @@ 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      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      x1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
@@ -83,7 +83,7 @@ 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      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      x1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1

=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_mat.result	2013-04-17 09:03:31 +0000
@@ -47,7 +47,7 @@ set @@optimizer_switch='materialization=
 explain extended
 select * from t1 where a1 in (select b1 from t2 where b1 > '0');
 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; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`))))))
@@ -58,7 +58,7 @@ a1	a2
 explain extended
 select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
 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; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`))))))
@@ -69,7 +69,7 @@ a1	a2
 explain extended
 select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
 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; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`))))))
@@ -80,7 +80,7 @@ a1	a2
 explain extended
 select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
 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; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using temporary
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`min(b2)`))))))
@@ -91,7 +91,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 t1i     index   NULL    _it1_idx        #       NULL    3       100.00  Using where; 
+1       PRIMARY t1i     index   NULL    _it1_idx        #       NULL    3       100.00  Using where; Subqueries: 2; 
 2       MATERIALIZED    t2i     index   it2i1,it2i3     it2i1   #       NULL    5       100.00  Using where; 
 Warnings:
 Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`b1`))))))
@@ -113,7 +113,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 t1i     index   NULL    _it1_idx        #       NULL    3       100.00  Using where; 
+1       PRIMARY t1i     index   NULL    _it1_idx        #       NULL    3       100.00  Using where; Subqueries: 2; 
 2       MATERIALIZED    t2i     index   it2i1,it2i3     it2i3   #       NULL    5       100.00  Using where; 
 Warnings:
 Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1i`.`a2` = `<subquery2>`.`b2`))))))
@@ -146,7 +146,7 @@ a1	a2
 explain extended
 select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
 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; Subqueries: 2
 2       MATERIALIZED    t2i     range   NULL    it2i3   9       NULL    3       100.00  Using index for group-by
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`max(b2)`))))))
@@ -175,7 +175,7 @@ a1	a2
 explain extended
 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
 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; Subqueries: 2
 2       MATERIALIZED    t2i     range   it2i1,it2i3     it2i3   18      NULL    3       100.00  Using where; Using index for group-by
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`min(b2)`))))))
@@ -222,7 +222,7 @@ set @@optimizer_switch=@save_optimizer_s
 explain extended
 select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
 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; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`))))))
@@ -233,7 +233,7 @@ a1	a2
 explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
+1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t2i     index   NULL    it2i3   18      NULL    5       100.00  Using index
 Warnings:
 Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1i`.`a2` = `<subquery2>`.`b2`))))))
@@ -286,8 +286,8 @@ where (a1, a2) in (select b1, b2 from t2
 (a1, a2) in (select c1, c2 from t3
 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 t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
-3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2 3
+3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 4
 4       MATERIALIZED    t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 Warnings:
@@ -326,10 +326,10 @@ b2 in (select c2 from t3 where c2 LIKE '
 (a1, a2) in (select c1, c2 from t3
 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 t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
-5       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 5 2
+5       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 6
 6       MATERIALIZED    t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
-2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
+2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 3 4
 4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
@@ -350,10 +350,10 @@ b2 in (select c2 from t3 t3b where c2 LI
 (a1, a2) in (select c1, c2 from t3 t3c
 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 t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
-5       MATERIALIZED    t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 5 2
+5       MATERIALIZED    t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 6
 6       MATERIALIZED    t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
-2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 3 4
 4       MATERIALIZED    t3b     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 3       DEPENDENT SUBQUERY      t3a     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
@@ -416,8 +416,8 @@ where (a1, a2) in (select * from t1 wher
 (a1, a2) in (select c1, c2 from t3
 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 t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
-4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2 4
+4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 5
 5       MATERIALIZED    t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
@@ -438,9 +438,9 @@ where (a1, a2) in (select * from t1 wher
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
 a1 = c1;
 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 t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Using join buffer (flat, BNL join)
-4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 4; Using join buffer (flat, BNL join)
+4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 5
 5       MATERIALIZED    t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
@@ -463,7 +463,7 @@ explain extended
 select * from t3
 where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
@@ -483,10 +483,10 @@ b2 in (select c2 from t3 t3b where c2 LI
 (a1, a2) in (select c1, c2 from t3 t3c
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
 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
-5       DEPENDENT SUBQUERY      t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 5 2
+5       DEPENDENT SUBQUERY      t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Subqueries: 6
 6       DEPENDENT SUBQUERY      t2i     index_subquery  it2i1,it2i2,it2i3       it2i3   18      func,func       2       100.00  Using index; Using where
-2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 3 4
 4       MATERIALIZED    t3b     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 3       DEPENDENT SUBQUERY      t3a     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
@@ -496,7 +496,7 @@ Note	1003	select `test`.`t1`.`a1` AS `a1
 explain extended
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
@@ -506,7 +506,7 @@ a1	a2
 explain extended
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
@@ -595,7 +595,7 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`>(<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(select `test`.`t2_16`.`b1` from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`)))))
@@ -609,7 +609,7 @@ explain extended select left(a1,7), left
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`>(<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1`,`test`.`t2_16`.`b2` from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))))
@@ -623,7 +623,7 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`>(<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_16`.`a1` = `<subquery2>`.`substring(b1,1,16)`))))))
@@ -637,7 +637,7 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select group_concat(b1) from t2_16 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`>(<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2` having (<cache>(`test`.`t1_16`.`a1`) = <ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ','))))))
@@ -652,7 +652,7 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select group_concat(b1) from t2_16 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`>(<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2` ), <primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_16`.`a1` = `<subquery2>`.`group_concat(b1)`))))))
@@ -671,10 +671,10 @@ where (a1, a2) IN
 where t2.b2 = substring(t2_16.b2,1,6) and
 t2.b1 IN (select c1 from t3 where c2 > '0')));
 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
-2       DEPENDENT SUBQUERY      t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
+2       DEPENDENT SUBQUERY      t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 3
 3       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
-3       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using join buffer (flat, BNL join)
+3       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 4; Using join buffer (flat, BNL join)
 4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><concat(`test`.`t1`.`a1`,'x')>(<in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select left(`test`.`t1_16`.`a1`,8) from `test`.`t1_16` where (<expr_cache><`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`>(<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1`,`test`.`t2_16`.`b2` from `test`.`t2_16` join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and <expr_cache><`test`.`t2`.`b1`>(<in_optimizer>(`test`.`t2`.`b1`,`test`.`t2`.`b1` in ( <materialize> (select `test`.`t3`.`c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0') ), <primary_index_lookup>(`test`.`t2`.`b1` in <temporary table> on distinct_key where ((`test`.`t2`.`b1` = `<subquery4>`.`c1`)))))) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`))))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = 
 left(`test`.`t1_16`.`a1`,8))))))
@@ -708,7 +708,7 @@ explain extended select left(a1,7), left
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <expr_cache><`test`.`t1_512`.`a1`>(<in_optimizer>(`test`.`t1_512`.`a1`,<exists>(select `test`.`t2_512`.`b1` from `test`.`t2_512` where ((`test`.`t2_512`.`b1` > '0') and (<cache>(`test`.`t1_512`.`a1`) = `test`.`t2_512`.`b1`)))))
@@ -722,7 +722,7 @@ explain extended select left(a1,7), left
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <expr_cache><`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`>(<in_optimizer>((`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`),<exists>(select `test`.`t2_512`.`b1`,`test`.`t2_512`.`b2` from `test`.`t2_512` where ((`test`.`t2_512`.`b1` > '0') and (<cache>(`test`.`t1_512`.`a1`) = `test`.`t2_512`.`b1`) and (<cache>(`test`.`t1_512`.`a2`) = `test`.`t2_512`.`b2`)))))
@@ -736,7 +736,7 @@ explain extended select left(a1,7), left
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <expr_cache><`test`.`t1_512`.`a1`>(<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_512`.`a1` = `<subquery2>`.`substring(b1,1,512)`))))))
@@ -750,7 +750,7 @@ explain extended select left(a1,7), left
 from t1_512
 where a1 in (select group_concat(b1) from t2_512 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <expr_cache><`test`.`t1_512`.`a1`>(<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`))))))
@@ -763,7 +763,7 @@ explain extended select left(a1,7), left
 from t1_512
 where a1 in (select group_concat(b1) from t2_512 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <expr_cache><`test`.`t1_512`.`a1`>(<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`))))))
@@ -801,7 +801,7 @@ explain extended select left(a1,7), left
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <expr_cache><`test`.`t1_1024`.`a1`>(<in_optimizer>(`test`.`t1_1024`.`a1`,<exists>(select `test`.`t2_1024`.`b1` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`)))))
@@ -815,7 +815,7 @@ explain extended select left(a1,7), left
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <expr_cache><`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`>(<in_optimizer>((`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`),<exists>(select `test`.`t2_1024`.`b1`,`test`.`t2_1024`.`b2` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`) and (<cache>(`test`.`t1_1024`.`a2`) = `test`.`t2_1024`.`b2`)))))
@@ -829,7 +829,7 @@ explain extended select left(a1,7), left
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <expr_cache><`test`.`t1_1024`.`a1`>(<in_optimizer>(`test`.`t1_1024`.`a1`,<exists>(select substr(`test`.`t2_1024`.`b1`,1,1024) from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = substr(`test`.`t2_1024`.`b1`,1,1024))))))
@@ -843,7 +843,7 @@ explain extended select left(a1,7), left
 from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <expr_cache><`test`.`t1_1024`.`a1`>(<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), <primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`))))))
@@ -856,7 +856,7 @@ explain extended select left(a1,7), left
 from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <expr_cache><`test`.`t1_1024`.`a1`>(<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), <primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`))))))
@@ -894,7 +894,7 @@ explain extended select left(a1,7), left
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <expr_cache><`test`.`t1_1025`.`a1`>(<in_optimizer>(`test`.`t1_1025`.`a1`,<exists>(select `test`.`t2_1025`.`b1` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`)))))
@@ -908,7 +908,7 @@ explain extended select left(a1,7), left
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <expr_cache><`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`>(<in_optimizer>((`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`),<exists>(select `test`.`t2_1025`.`b1`,`test`.`t2_1025`.`b2` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`) and (<cache>(`test`.`t1_1025`.`a2`) = `test`.`t2_1025`.`b2`)))))
@@ -922,7 +922,7 @@ explain extended select left(a1,7), left
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <expr_cache><`test`.`t1_1025`.`a1`>(<in_optimizer>(`test`.`t1_1025`.`a1`,<exists>(select substr(`test`.`t2_1025`.`b1`,1,1025) from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = substr(`test`.`t2_1025`.`b1`,1,1025))))))
@@ -936,7 +936,7 @@ explain extended select left(a1,7), left
 from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <expr_cache><`test`.`t1_1025`.`a1`>(<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), <primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`))))))
@@ -949,7 +949,7 @@ explain extended select left(a1,7), left
 from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <expr_cache><`test`.`t1_1025`.`a1`>(<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), <primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`))))))
@@ -970,7 +970,7 @@ explain extended select bin(a1), bin(a2)
 from t1bit
 where (a1, a2) in (select b1, b2 from t2bit);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1bit   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1bit   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2bit   ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
 Note    1003    select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` where <expr_cache><`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`>(<in_optimizer>((`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`),(`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`) in ( <materialize> (select `test`.`t2bit`.`b1`,`test`.`t2bit`.`b2` from `test`.`t2bit` ), <primary_index_lookup>(`test`.`t1bit`.`a1` in <temporary table> on distinct_key where ((`test`.`t1bit`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1bit`.`a2` = `<subquery2>`.`b2`))))))
@@ -993,7 +993,7 @@ explain extended select bin(a1), a2
 from t1bb
 where (a1, a2) in (select b1, b2 from t2bb);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1bb    ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1bb    ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2bb    ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 Warnings:
 Note    1003    select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` where <expr_cache><`test`.`t1bb`.`a1`,`test`.`t1bb`.`a2`>(<in_optimizer>((`test`.`t1bb`.`a1`,`test`.`t1bb`.`a2`),<exists>(select `test`.`t2bb`.`b1`,`test`.`t2bb`.`b2` from `test`.`t2bb` where ((<cache>(`test`.`t1bb`.`a1`) = `test`.`t2bb`.`b1`) and (<cache>(`test`.`t1bb`.`a2`) = `test`.`t2bb`.`b2`)))))
@@ -1041,7 +1041,7 @@ insert into t3 values (30);
 explain extended
 select a from t1 where a in (select c from t2 where d >= 20);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`c`))))))
@@ -1055,7 +1055,7 @@ create index it1a on t1(a);
 explain extended
 select a from t1 where a in (select c from t2 where d >= 20);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`c`))))))
@@ -1069,7 +1069,7 @@ insert into t2 values (1,10);
 explain extended
 select a from t1 where a in (select c from t2 where d >= 20);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using where; Using index
+1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`c`))))))
@@ -1107,7 +1107,7 @@ select a from t1 group by a
 having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      index   NULL    iab     8       NULL    7       100.00  Using index
-2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where; Subqueries: 3
 3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
@@ -1121,8 +1121,8 @@ explain extended
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    iab     8       NULL    7       100.00  Using where; Using index
-2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
+1       PRIMARY t1      index   NULL    iab     8       NULL    7       100.00  Using where; Subqueries: 2; Using index
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where; Subqueries: 3
 3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1

=== modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
--- a/mysql-test/r/subselect_mat_cost_bugs.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_mat_cost_bugs.result	2013-04-17 09:03:31 +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; Subselects: 2
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where; Subqueries: 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-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2013-04-17 09:03:31 +0000
@@ -194,9 +194,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; Subqueries: 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; Subqueries: 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:
@@ -283,7 +283,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; Subqueries: 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`))))
@@ -344,7 +344,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; Subqueries: 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
@@ -760,7 +760,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; Subqueries: 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    
@@ -1378,7 +1378,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; Subqueries: 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`))
@@ -1644,7 +1644,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; Subqueries: 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`))))
@@ -1652,7 +1652,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; Subqueries: 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`))))
@@ -1663,7 +1663,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; Subqueries: 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`))))
@@ -1671,7 +1671,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; Subqueries: 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`))))
@@ -1714,7 +1714,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; Subqueries: 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`))))
@@ -1884,13 +1884,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; Subqueries: 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; Subqueries: 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
@@ -2426,7 +2426,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; Subqueries: 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
@@ -4432,7 +4432,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; Subqueries: 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
@@ -4441,7 +4441,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; Subqueries: 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/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2013-04-17 09:03:31 +0000
@@ -190,9 +190,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; Subqueries: 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; Subqueries: 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:
@@ -279,7 +279,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; Subqueries: 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`))))
@@ -340,7 +340,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; Subqueries: 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
@@ -756,7 +756,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; Subqueries: 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    
@@ -1374,7 +1374,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; Subqueries: 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`))
@@ -1436,7 +1436,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))
@@ -1446,7 +1446,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
@@ -1456,7 +1456,7 @@ select * from t2 where t2.a in (select t
 3
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       func    1       100.00  Using where
 2       DEPENDENT SUBQUERY      t3      eq_ref  PRIMARY PRIMARY 4       test.t1.b       1       100.00  Using index
 Warnings:
@@ -1479,7 +1479,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))
@@ -1489,7 +1489,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index; Using where
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
@@ -1499,7 +1499,7 @@ select * from t2 where t2.a in (select t
 3
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t3      index   a       a       5       NULL    3       100.00  Using where; Using index
 2       DEPENDENT SUBQUERY      t1      ref     a       a       10      func,test.t3.a  1167    100.00  Using index
 Warnings:
@@ -1516,7 +1516,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index; Using where
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
@@ -1640,7 +1640,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; Subqueries: 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`))))
@@ -1648,7 +1648,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; Subqueries: 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`))))
@@ -1659,7 +1659,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; Subqueries: 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`))))
@@ -1667,7 +1667,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; Subqueries: 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`))))
@@ -1710,7 +1710,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; Subqueries: 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`))))
@@ -1880,14 +1880,14 @@ 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
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subqueries: 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(<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       58.33   Using where; Using index
+1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subqueries: 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
 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))))
@@ -2422,7 +2422,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; Subqueries: 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
@@ -2972,7 +2972,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
 explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
@@ -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; Subqueries: 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; Subqueries: 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/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2013-04-17 09:03:31 +0000
@@ -193,9 +193,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; Subqueries: 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; Subqueries: 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:
@@ -282,7 +282,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; Subqueries: 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`))))
@@ -343,7 +343,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; Subqueries: 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
@@ -759,7 +759,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; Subqueries: 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    
@@ -1377,7 +1377,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; Subqueries: 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`))
@@ -1643,7 +1643,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; Subqueries: 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`))))
@@ -1651,7 +1651,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; Subqueries: 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`))))
@@ -1662,7 +1662,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; Subqueries: 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`))))
@@ -1670,7 +1670,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; Subqueries: 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`))))
@@ -1713,7 +1713,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; Subqueries: 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`))))
@@ -1883,14 +1883,14 @@ 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
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subqueries: 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(<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       58.33   Using where; Using index
+1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subqueries: 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
 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))))
@@ -2425,7 +2425,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; Subqueries: 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
@@ -4434,7 +4434,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; Subqueries: 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
@@ -4443,7 +4443,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; Subqueries: 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/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2013-04-17 09:03:31 +0000
@@ -190,9 +190,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; Subqueries: 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; Subqueries: 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:
@@ -279,7 +279,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; Subqueries: 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`))))
@@ -340,7 +340,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; Subqueries: 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
@@ -756,7 +756,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; Subqueries: 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    
@@ -1374,7 +1374,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; Subqueries: 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`))
@@ -1436,7 +1436,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t1      index   PRIMARY PRIMARY 4       NULL    4       100.00  Using index
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `<subquery2>`.`a`))))))
@@ -1446,7 +1446,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t1      ALL     PRIMARY NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `<subquery2>`.`a`))))))
@@ -1456,7 +1456,7 @@ select * from t2 where t2.a in (select t
 3
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       MATERIALIZED    t3      index   PRIMARY PRIMARY 4       NULL    3       100.00  Using index
 2       MATERIALIZED    t1      ALL     PRIMARY NULL    NULL    NULL    4       100.00  Using where; Using join buffer (flat, BNL join)
 Warnings:
@@ -1479,7 +1479,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a))))
@@ -1489,7 +1489,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index; Using where
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))))
@@ -1499,7 +1499,7 @@ select * from t2 where t2.a in (select t
 3
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t3      index   a       a       5       NULL    3       100.00  Using where; Using index
 2       DEPENDENT SUBQUERY      t1      ref     a       a       10      func,test.t3.a  1167    100.00  Using index
 Warnings:
@@ -1516,7 +1516,7 @@ select * from t2 where t2.a in (select a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index; Using where
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))))
@@ -1640,7 +1640,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; Subqueries: 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`))))
@@ -1648,7 +1648,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; Subqueries: 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`))))
@@ -1659,7 +1659,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; Subqueries: 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`))))
@@ -1667,7 +1667,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; Subqueries: 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`))))
@@ -1710,7 +1710,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; Subqueries: 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`))))
@@ -1880,14 +1880,14 @@ 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; Subqueries: 2
 2       MATERIALIZED    t1      range   PRIMARY PRIMARY 4       NULL    7       100.00  Using where; Using index
 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`,`test`.`t1`.`id` in ( <materialize> (select `test`.`t1`.`id` from `test`.`t1` where (`test`.`t1`.`id` < 8) ), <primary_index_lookup>(`test`.`t1`.`id` in <temporary table> on distinct_key where ((`test`.`t1`.`id` = `<subquery2>`.`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       58.33   Using where; Using index
+1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where; Subqueries: 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
 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)))))
@@ -2422,7 +2422,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; Subqueries: 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
@@ -2972,7 +2972,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key where ((`test`.`t1`.`one` = `<subquery2>`.`one`) and (`test`.`t1`.`two` = `<subquery2>`.`two`)))))) AS `test` from `test`.`t1`
 explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Using where; Subqueries: 2
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where
 Warnings:
 Note    1003    select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key where ((`test`.`t1`.`one` = `<subquery2>`.`one`) and (`test`.`t1`.`two` = `<subquery2>`.`two`))))))
@@ -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; Subqueries: 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; Subqueries: 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/subselect_partial_match.result'
--- a/mysql-test/r/subselect_partial_match.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_partial_match.result	2013-04-17 09:03:31 +0000
@@ -780,7 +780,7 @@ EXPLAIN EXTENDED
 SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    2       100.00  
-2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 3
 3       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
 Note    1003    select `table1`.`a1` AS `a1`,`table1`.`a2` AS `a2` from (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery3>`.`b2`)))))))) `table1`

=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2013-07-16 05:22:17 +0000
+++ b/mysql-test/r/subselect_sj.result	2013-04-17 09:03:31 +0000
@@ -81,7 +81,7 @@ select * from t1 left join (t2 A, t2 B)
 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 A       ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
-1       PRIMARY B       ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY B       ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; SuBqueries: 2
 2       MATERIALIZED    t10     index   PRIMARY PRIMARY 4       NULL    10      100.00  Using index
 Warnings:
 Note    1003    select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `<suBquery2>`.`pk`))))))) where 1
@@ -90,7 +90,7 @@ explAin extended
 select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
 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 t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; SuBqueries: 2
 2       MATERIALIZED    t10     index   PRIMARY PRIMARY 4       NULL    10      100.00  Using index
 Warnings:
 Note    1003    select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where ((`test`.`t1`.`A` = `<suBquery2>`.`pk`))))))) where 1
@@ -761,7 +761,7 @@ select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    7       100.00  
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; FirstMatch(t1)
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; Subqueries: 3; FirstMatch(t1)
 3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1

=== modified file 'mysql-test/r/subselect_sj2_mat.result'
--- a/mysql-test/r/subselect_sj2_mat.result	2013-07-17 19:24:29 +0000
+++ b/mysql-test/r/subselect_sj2_mat.result	2013-04-17 09:03:31 +0000
@@ -1369,7 +1369,7 @@ set optimizer_switch='materialization=of
 EXPLAIN EXTENDED 
 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       DEPENDENT SUBQUERY      t3      hash_ALL        NULL    #hash#$hj       5       func    3       100.00  Using where; Using join buffer (flat, BNLH join)
 Warnings:

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2013-07-16 05:22:17 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2013-04-17 09:03:31 +0000
@@ -94,7 +94,7 @@ select * from t1 left join (t2 A, t2 B)
 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 A       ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join Buffer (flAt, BNL join)
-1       PRIMARY B       ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join Buffer (incrementAl, BNL join)
+1       PRIMARY B       ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; SuBqueries: 2; Using join Buffer (incrementAl, BNL join)
 2       MATERIALIZED    t10     index   PRIMARY PRIMARY 4       NULL    10      100.00  Using index
 Warnings:
 Note    1003    select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `<suBquery2>`.`pk`))))))) where 1
@@ -103,7 +103,7 @@ explAin extended
 select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
 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 t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join Buffer (flAt, BNL join)
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; SuBqueries: 2; Using join Buffer (flAt, BNL join)
 2       MATERIALIZED    t10     index   PRIMARY PRIMARY 4       NULL    10      100.00  Using index
 Warnings:
 Note    1003    select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where ((`test`.`t1`.`A` = `<suBquery2>`.`pk`))))))) where 1
@@ -774,7 +774,7 @@ select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    7       100.00  
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; Subqueries: 3; FirstMatch(t1); Using join buffer (flat, BNL join)
 3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1

=== modified file 'mysql-test/r/subselect_sj_mat.result'
--- a/mysql-test/r/subselect_sj_mat.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect_sj_mat.result	2013-04-17 09:03:31 +0000
@@ -345,7 +345,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    16      func,func       1       100.00  
 5       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 5       MATERIALIZED    t2i     index   it2i1,it2i2,it2i3       it2i3   18      NULL    5       80.00   Using where; Using index; Using join buffer (flat, BNL join)
-2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
+2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 3 4
 4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
@@ -368,7 +368,7 @@ where (c1, c2) in (select b1, b2 from t2
 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 <subquery5>     eq_ref  distinct_key    distinct_key    16      func,func       1       100.00  
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 3 4; Start temporary; End temporary; Using join buffer (flat, BNL join)
 5       MATERIALIZED    t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 5       MATERIALIZED    t2i     index   it2i1,it2i2,it2i3       it2i3   18      NULL    5       80.00   Using where; Using index; Using join buffer (flat, BNL join)
 4       MATERIALIZED    t3b     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
@@ -435,7 +435,7 @@ where (a1, a2) in (select * from t1 wher
 (a1, a2) in (select c1, c2 from t3
 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 t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 1       PRIMARY <subquery4>     eq_ref  distinct_key    distinct_key    16      func,func       1       100.00  
 4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 4       MATERIALIZED    t2i     index   it2i1,it2i2,it2i3       it2i3   18      NULL    5       80.00   Using where; Using index; Using join buffer (flat, BNL join)
@@ -458,7 +458,7 @@ where (a1, a2) in (select * from t1 wher
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
 a1 = c1;
 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; Subqueries: 2
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Using join buffer (flat, BNL join)
 1       PRIMARY <subquery4>     eq_ref  distinct_key    distinct_key    16      func,func       1       100.00  
 4       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
@@ -484,7 +484,7 @@ explain extended
 select * from t3
 where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
@@ -507,7 +507,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 1       PRIMARY t2i     ref     it2i1,it2i2,it2i3       it2i3   18      test.t1.a1,test.t1.a2   2       100.00  Using index; Start temporary
 1       PRIMARY t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; End temporary; Using join buffer (flat, BNL join)
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Subqueries: 3 4; Start temporary; End temporary; Using join buffer (flat, BNL join)
 4       MATERIALIZED    t3b     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 3       DEPENDENT SUBQUERY      t3a     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
@@ -517,7 +517,7 @@ Note	1003	select `test`.`t1`.`a1` AS `a1
 explain extended
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
@@ -527,7 +527,7 @@ a1	a2
 explain extended
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
 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; Subqueries: 2
 2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
 Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01')))))
@@ -659,7 +659,7 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select group_concat(b1) from t2_16 group by b2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 Warnings:
 Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`>(<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2` having (<cache>(`test`.`t1_16`.`a1`) = <ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ','))))))
@@ -1141,7 +1141,7 @@ select a from t1 group by a
 having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      index   NULL    iab     8       NULL    7       100.00  Using index
-2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where; Subqueries: 3
 3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
@@ -1156,7 +1156,7 @@ select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 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  Start temporary
-1       PRIMARY t1      ref     it1a,iab        iab     4       test.t2.c       1       100.00  Using where; Using index; End temporary
+1       PRIMARY t1      ref     it1a,iab        iab     4       test.t2.c       1       100.00  Using where; Subqueries: 3; Using index; End temporary
 3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1

=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result	2012-05-21 18:54:41 +0000
+++ b/mysql-test/r/table_elim.result	2013-04-17 09:03:31 +0000
@@ -138,7 +138,7 @@ Note	1003	select `f`.`id` AS `id` from `
 This should use facts, a2 and its subquery:
 explain extended select id from v1 where attr2 between 12 and 14;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       SIMPLE  a2      range   PRIMARY,attr2   attr2   5       NULL    5       100.00  Using index condition; Using where
+1       SIMPLE  a2      range   PRIMARY,attr2   attr2   5       NULL    5       100.00  Using index condition; Using where; Subqueries: 3
 1       SIMPLE  f       eq_ref  PRIMARY PRIMARY 4       test.a2.id      1       100.00  Using index
 3       DEPENDENT SUBQUERY      t2      ref     PRIMARY PRIMARY 4       test.a2.id      2       100.00  Using index
 Warnings:
@@ -167,7 +167,7 @@ Note	1003	select `f`.`id` AS `id` from `
 explain extended select id from v2 where attr2 between 12 and 14;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       SIMPLE  a2      range   PRIMARY,attr2   attr2   5       NULL    5       100.00  Using index condition
-1       SIMPLE  f       eq_ref  PRIMARY PRIMARY 4       test.a2.id      1       100.00  Using where; Using index
+1       SIMPLE  f       eq_ref  PRIMARY PRIMARY 4       test.a2.id      1       100.00  Using where; Subqueries: 3; Using index
 3       DEPENDENT SUBQUERY      t2      ref     PRIMARY PRIMARY 4       test.f.id       2       100.00  Using index
 Warnings:
 Note    1276    Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
@@ -623,7 +623,7 @@ WHERE b IN (
 SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a
 );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2      index   NULL    b       5       NULL    2       100.00  Using where; Using index
+1       PRIMARY t2      index   NULL    b       5       NULL    2       100.00  Using where; Subqueries: 2; Using index
 2       DEPENDENT SUBQUERY      t1      system  NULL    NULL    NULL    NULL    1       100.00  
 Warnings:
 Note    1003    select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select sum(1) from dual where 1 having (<cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1))))))

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2013-06-17 17:33:36 +0000
+++ b/mysql-test/r/view.result	2013-04-17 09:03:31 +0000
@@ -4502,7 +4502,7 @@ WHERE t2.a NOT IN (SELECT t3.b FROM t3 R
 WHERE t4.a >= t1.a);
 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      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t3      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 2       DEPENDENT SUBQUERY      t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
@@ -4518,7 +4518,7 @@ WHERE t2.a NOT IN (SELECT t3.b FROM t3 R
 WHERE t4.a >= v1.a);
 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      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t3      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 2       DEPENDENT SUBQUERY      t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
@@ -4549,7 +4549,7 @@ WHERE t4.c <= (SELECT t2.e FROM t2 LEFT
 WHERE t2.b > t4.b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2      system  NULL    NULL    NULL    NULL    1       100.00  
 2       DEPENDENT SUBQUERY      t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
@@ -4566,7 +4566,7 @@ WHERE v4.c <= (SELECT t2.e FROM t2 LEFT
 WHERE t2.b > v4.b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Subqueries: 2
 2       DEPENDENT SUBQUERY      t2      system  NULL    NULL    NULL    NULL    1       100.00  
 2       DEPENDENT SUBQUERY      t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:

=== modified file 'mysql-test/suite/maria/ps_maria.result'
--- a/mysql-test/suite/maria/ps_maria.result	2013-06-17 17:33:36 +0000
+++ b/mysql-test/suite/maria/ps_maria.result	2013-04-17 09:03:31 +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 ;



More information about the commits mailing list