New subject: [Maria-developers] Review request for: [Commits] Rev 3403: Fix for bug lp:944706, task MDEV-193 in file:///home/tsk/mprog/src/5.5-lpb944706/

14 May 2012

On Sun, May 13, 2012 at 02:11:15PM +0300, Timour Katchaounov wrote:
...
Sergey,
At the optimizer call this Tuesday Igor suggested that you
should review the patch for this bug. The commit message
contains a description of the patch. Let me know if you believe
a more detailed description is needed.
The patch is also pushed into the following tree:
lp:~maria-captains/maria/5.5-timour
Thanks,
Timour
------------------------------------------------------------
revno: 3403
revision-id: timour@askmonty.org-20120511152503-zbjewctjclx0sajt
parent: knielsen@knielsen-hq.org-20120508122744-v5okh33kuolot3kb
fixes bug(s): https://launchpad.net/bugs/944706
committer: timour@askmonty.org
branch nick: 5.5-lpb944706
timestamp: Fri 2012-05-11 18:25:03 +0300
message:
  Fix for bug lp:944706, task MDEV-193
The patch enables back constant subquery execution during
  query optimization after it was disabled during the development
  of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION).
The main idea is that constant subqueries are allowed to be executed
  during optimization if their execution is not expensive.
The approach is as follows:
  - Constant subqueries are recursively optimized in the beginning of
    JOIN::optimize of the outer query. This is done by the new method
    JOIN::optimize_constant_subqueries(). This is done so that the cost
    of executing these queries can be estimated.
  - Optimization of the outer query proceeds normally. During this phase
    the optimizer may request execution of non-expensive constant subqueries.
    Each place where the optimizer may potentially execute an expensive
    expression is guarded with the predicate Item::is_expensive().
  - The implementation of Item_subselect::is_expensive has been extended
    to use the number of examined rows (estimated by the optimizer) as a
    way to determine whether the subquery is expensive or not.
  - The new system variable "expensive_subquery_limit" controls how many
    examined rows are considered to be not expensive. The default is 100.
In addition, multiple changes were needed to make this solution work
  in the light of the changes made by MWL#89. These changes were needed
  to fix various crashes and wrong results, and legacy bugs discovered
  during development.
...
=== modified --- a/mysql-test/ +++ b/mysql-test/ @@ -260,7 +260,7 @@ FLUSH TABLES; EXPLAIN id      select_type 1       PRIMARY NULL -2       SUBQUERY +2       SUBQUERY DROP TABLE t1, t2; # # Bug #48573: @@ -287,7 id      select_type 1       PRIMARY t1 2       SUBQUERY -2       SUBQUERY +2       SUBQUERY PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE @@ -297,12 +297,12 @@ EXECUTE stmt; id      select_type 1       PRIMARY t1 2       SUBQUERY -2       SUBQUERY +2       SUBQUERY EXECUTE stmt; id      select_type 1       PRIMARY t1 2       SUBQUERY -2       SUBQUERY +2       SUBQUERY DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 @@ -313,12 +313,12 @@ EXECUTE stmt; id      select_type 1       PRIMARY t1 2       SUBQUERY -2       SUBQUERY +2       SUBQUERY EXECUTE stmt; id      select_type 1       PRIMARY t1 2       SUBQUERY -2       SUBQUERY +2       SUBQUERY DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests.

file 'mysql-test/r/explain.result' r/explain.result	2012-01-13 14:50:02 +0000 r/explain.result	2012-05-11 15:25:03 +0000 SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t1 t JOIN t2 WHERE b <= 1 AND t.a); table   type    possible_keys   key     key_len ref     rows    Extra NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table difference of index selection between rpm binary and +287,7 @@ WHERE t1.f1 GROUP BY t1.f1)); table   type    possible_keys   key     key_len ref     rows    Extra system  NULL    NULL    NULL    NULL    1 a       system  NULL    NULL    NULL    NULL    1 t1      fulltext        f1_2,f1 f1      0               1       Using where t1      fulltext        f1_2,f1 f1      0               1 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a table   type    possible_keys   key     key_len ref     rows    Extra system  NULL    NULL    NULL    NULL    1 a       system  NULL    NULL    NULL    NULL    1 t1      fulltext        f1_2,f1 f1      0               1       Using where t1      fulltext        f1_2,f1 f1      0               1 table   type    possible_keys   key     key_len ref     rows    Extra system  NULL    NULL    NULL    NULL    1 a       system  NULL    NULL    NULL    NULL    1 t1      fulltext        f1_2,f1 f1      0               1       Using where t1      fulltext        f1_2,f1 f1      0               1 table   type    possible_keys   key     key_len ref     rows    Extra system  NULL    NULL    NULL    NULL    1 a       system  NULL    NULL    NULL    NULL    1 t1      fulltext        f1_2,f1 f1      0               1       Using where t1      fulltext        f1_2,f1 f1      0               1 table   type    possible_keys   key     key_len ref     rows    Extra system  NULL    NULL    NULL    NULL    1 a       system  NULL    NULL    NULL    NULL    1 t1      fulltext        f1_2,f1 f1      0               1       Using where t1      fulltext        f1_2,f1 f1      0               1
=== modified file 'mysql-test/r/group_min_max.result'

--- a/mysql-test/r/group_min_max.result	2012-03-01 22:22:22 +0000
+++ b/mysql-test/r/group_min_max.result	2012-05-11 15:25:03 +0000
@@ -2398,12 +2398,12 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1_outer        index   NULL    a       10      NULL    15      Using index
-2       SUBQUERY        t1      index   NULL    a       10      NULL    1       Using index
+2       SUBQUERY        t1      index   NULL    a       10      NULL    15      Using index
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1_outer        index   NULL    a       10      NULL    15      Using index
-2       SUBQUERY        t1      range   NULL    a       5       NULL    8       Using index for group-by
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        t1      index   NULL    a       10      NULL    15      Using index
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
@@ -2419,9 +2419,9 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JO
 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 
 AND t1_outer1.b = t1_outer2.b;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1_outer1       ref     a       a       5       const   2       Using where; Using index
+1       PRIMARY t1_outer1       ref     a       a       5       const   1       Using where; Using index
 1       PRIMARY t1_outer2       index   NULL    a       10      NULL    15      Using where; Using index; Using join buffer (flat, BNL join)
-2       SUBQUERY        t1      range   NULL    a       5       NULL    8       Using index for group-by
+2       SUBQUERY        t1      index   NULL    a       10      NULL    15      Using index
 EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
 FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
@@ -2756,8 +2756,8 @@ NULL
 EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-x       x       x       x       x       x       x       x       x       Using where; Using index
-x       x       x       x       x       x       x       x       x       Using where; Using index
+x       x       x       x       x       x       x       x       x       Impossible WHERE noticed after reading const tables
+x       x       x       x       x       x       x       x       x       Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
@@ -2828,8 +2828,8 @@ NULL
 EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-x       x       x       x       x       x       x       x       x       Using where; Using index
-x       x       x       x       x       x       x       x       x       Using where; Using index
+x       x       x       x       x       x       x       x       x       Impossible WHERE noticed after reading const tables
+x       x       x       x       x       x       x       x       x       Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
@@ -2907,8 +2907,8 @@ NULL
 EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-x       x       x       x       x       x       x       x       x       Using where; Using index
-x       x       x       x       x       x       x       x       x       Using where; Using index
+x       x       x       x       x       x       x       x       x       Impossible WHERE noticed after reading const tables
+x       x       x       x       x       x       x       x       x       Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2012-04-07 13:58:46 +0000
+++ b/mysql-test/r/join_outer.result	2012-05-11 15:25:03 +0000
@@ -1871,10 +1871,10 @@ EXPLAIN EXTENDED
 SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
-Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where 1
+Note    1003    select NULL AS `a` from `test`.`t2` where 1
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON
=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2012-04-07 13:58:46 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2012-05-11 15:25:03 +0000
@@ -1882,10 +1882,10 @@ EXPLAIN EXTENDED
 SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
-Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where 1
+Note    1003    select NULL AS `a` from `test`.`t2` where 1
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON
=== modified file 'mysql-test/r/key.result'
--- a/mysql-test/r/key.result	2011-10-19 19:45:18 +0000
+++ b/mysql-test/r/key.result	2012-05-11 15:25:03 +0000
@@ -598,8 +598,8 @@ VALUES
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1_outer        index   NULL    a       10      NULL    15      Using index
-2       SUBQUERY        t1      range   NULL    a       5       NULL    8       Using index for group-by
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        t1      index   NULL    a       10      NULL    15      Using index
 SELECT 1 as RES FROM t1 AS t1_outer WHERE 
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 RES
=== modified file 'mysql-test/r/limit_rows_examined.result'
--- a/mysql-test/r/limit_rows_examined.result	2012-03-11 22:45:18 +0000
+++ b/mysql-test/r/limit_rows_examined.result	2012-05-11 15:25:03 +0000
@@ -679,7 +679,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       Using temporary
 1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    3       Distinct
 2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    3       
-2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       Using where; Using join buffer (flat, BNL join)
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       
 SELECT DISTINCT a AS field1 FROM t1, t2 
 WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) 
 HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2012-02-25 15:13:24 +0000
+++ b/mysql-test/r/myisam_mrr.result	2012-05-11 15:25:03 +0000
@@ -349,10 +349,10 @@ WHERE t2.int_key IS NULL
 GROUP BY t2.pk
 );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 2       SUBQUERY        t2      ref     int_key int_key 5       const   1       100.00  Using index condition
 Warnings:
-Note    1003    select min(1) AS `MIN(t1.pk)` from dual where exists(select `test`.`t2`.`pk` from `test`.`t2` where isnull(`test`.`t2`.`int_key`))
+Note    1003    select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
 DROP TABLE t1, t2;
 #
 # BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation
=== modified file 'mysql-test/r/mysqld--help.result'
--- a/mysql-test/r/mysqld--help.result	2012-04-10 06:28:13 +0000
+++ b/mysql-test/r/mysqld--help.result	2012-05-11 15:25:03 +0000
@@ -159,6 +159,9 @@
  Enable the event scheduler. Possible values are ON, OFF,
  and DISABLED (keep the event scheduler completely
  deactivated, it cannot be activated run-time)
+ --expensive-subquery-limit=# 
+ The maximum number of rows a subquery examines in order
+ to be considered non-expensive
  --expire-logs-days=# 
  If non-zero, binary logs will be purged after
  expire_logs_days days; possible purges happen at startup
@@ -885,6 +888,7 @@ delayed-queue-size 1000
 div-precision-increment 4
 engine-condition-pushdown FALSE
 event-scheduler OFF
+expensive-subquery-limit 100
 expire-logs-days 0
 external-locking FALSE
 extra-max-connections 1
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-05-04 05:16:38 +0000
+++ b/mysql-test/r/subselect.result	2012-05-11 15:25:03 +0000
@@ -371,12 +371,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where; Using index
+1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 4       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
-2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where
+2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  
 3       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 Warnings:
-Note    1003    select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))
+Note    1003    select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 t8 WHERE pseudo='joce');
 ERROR 21000: Operand should contain 1 column(s)
@@ -517,6 +517,7 @@ ERROR 21000: Subquery returns more than
 show warnings;
 Level   Code    Message
 Error   1242    Subquery returns more than 1 row
+Error   1028    Sort aborted: Subquery returns more than 1 row
 drop table t1;
 create table t1 (a int);
 insert into t1 values (1),(2),(3);
@@ -547,11 +548,7 @@ SELECT numreponse, (SELECT numeropost FR
 numreponse      (SELECT numeropost FROM t1 HAVING numreponse=1)
 INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
-id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
-2       SUBQUERY        t1      ref     PRIMARY PRIMARY 3       const   2       100.00  Using index
-Warnings:
-Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+ERROR 21000: Subquery returns more than 1 row
 SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 ERROR 21000: Subquery returns more than 1 row
 EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
@@ -1675,34 +1672,34 @@ select * from t3 where NULL >= any (sele
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1764,7 +1761,7 @@ id	select_type	table	type	possible_keys
 3       UNION   t1      system  NULL    NULL    NULL    NULL    1       100.00  
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 Warnings:
-Note    1003    select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f')))
+Note    1003    select 'e' AS `s1` from dual where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
@@ -3099,7 +3096,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3111,7 +3108,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3727,8 +3724,8 @@ explain select * from t1 where not exist
 ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-2       SUBQUERY        t11     system  NULL    NULL    NULL    NULL    0       const row not found
-3       UNION   t12     system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
+3       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
@@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a));
 INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ref     a       a       5       const   0       Using where; Using index
-2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
+1       PRIMARY t1      ref     a       a       5       const   1       Using where; Using index
+2       SUBQUERY        internal_tmp_table      ALL     group_key       NULL    NULL    NULL    1       Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -4626,7 +4623,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5960,7 +5957,7 @@ id	select_type	table	type	possible_keys
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1       
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6121,7 +6118,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1
DROP TABLE t1;
 #
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2012-04-07 13:58:46 +0000
+++ b/mysql-test/r/subselect3.result	2012-05-11 15:25:03 +0000
@@ -1479,7 +1479,7 @@ id	select_type	table	type	possible_keys
 3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       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 (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`))))))))
+Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
 SELECT * FROM t1
 WHERE (
 ( SELECT a FROM t2 WHERE a = 9 ),
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2012-04-07 13:58:46 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2012-05-11 15:25:03 +0000
@@ -1489,7 +1489,7 @@ id	select_type	table	type	possible_keys
 3       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       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 (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`))))))))
+Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
 SELECT * FROM t1
 WHERE (
 ( SELECT a FROM t2 WHERE a = 9 ),
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2012-03-01 22:22:22 +0000
+++ b/mysql-test/r/subselect4.result	2012-05-11 15:25:03 +0000
@@ -562,7 +562,7 @@ WHERE f3 = (
 SELECT t1.f3 FROM t1
 WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ref     f3      f3      5       const   0       Using where
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       
 2       SUBQUERY        <subquery3>     eq_ref  distinct_key    distinct_key    4       func    1       
 3       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
@@ -577,7 +577,7 @@ WHERE f3 = (
 SELECT f3 FROM t1
 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ref     f3      f3      5       const   0       Using where
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       
 2       SUBQUERY        <subquery3>     eq_ref  distinct_key    distinct_key    8       func,func       1       
 3       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
@@ -1220,6 +1220,13 @@ id	select_type	table	type	possible_keys
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using where
 3       SUBQUERY        SQ1_t1  index   NULL    f4      5       NULL    2       Using index; Using temporary
 3       SUBQUERY        SQ1_t3  index   f4      f4      5       NULL    2       Using where; Using index; Using join buffer (flat, BNL join)
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+WHERE f4 <= ALL
+(SELECT max(SQ1_t1.f4)
+FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+GROUP BY SQ1_t1.f4));
+ERROR 21000: Subquery returns more than 1 row
 drop table t1, t2, t3;
 #
 # BUG#52317: Assertion failing in Field_varstring::store() 
@@ -1250,8 +1257,7 @@ FROM t2 JOIN t1 ON t1.f3
 WHERE ('v') IN (SELECT f4 FROM t2)
 GROUP BY f9;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       
 SELECT COUNT(t2.f3),
@@ -1267,8 +1273,7 @@ FROM t2 JOIN t1 ON t1.f3
 WHERE ('v') IN (SELECT f4 FROM t2)
 ORDER BY f9;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       
 SELECT COUNT(t2.f3),
@@ -1277,7 +1282,7 @@ FROM t2 JOIN t1 ON t1.f3
 WHERE ('v') IN (SELECT f4 FROM t2)
 ORDER BY f9;
 COUNT(t2.f3)    f9
-0       2
+0       NULL
 EXPLAIN
 SELECT COUNT(t2.f3),
 (SELECT t2.f1 FROM t1 limit 1) AS f9
@@ -1285,8 +1290,7 @@ FROM t2 JOIN t1
 WHERE ('v') IN (SELECT f4 FROM t2)
 GROUP BY f9;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       
 SELECT COUNT(t2.f3),
@@ -1302,8 +1306,7 @@ FROM t2 JOIN t1
 WHERE ('v') IN (SELECT f4 FROM t2)
 ORDER BY f9;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       
 SELECT COUNT(t2.f3),
@@ -1325,7 +1328,7 @@ EXPLAIN
 SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       
-2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
 field1
 NULL
@@ -1333,7 +1336,7 @@ EXPLAIN
 SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       
-2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
 field1
 NULL
@@ -1631,7 +1634,7 @@ SET SESSION optimizer_switch='in_to_exis
 EXPLAIN
 SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 2       DEPENDENT SUBQUERY      t3      system  NULL    NULL    NULL    NULL    1       
 2       DEPENDENT SUBQUERY      t2      ref_or_null     f10     f10     10      const,const     2       Using where; Using index
 SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10);
@@ -1852,8 +1855,8 @@ GROUP BY 1, 2;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       Using temporary; Using filesort
 1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using where
-2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-3       MATERIALIZED    t1      system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
+3       MATERIALIZED    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 PREPARE st1 FROM "
 SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
 FROM t2 JOIN t3 ON t3.f4 = t2.f4
=== modified file 'mysql-test/r/subselect_cache.result'
--- a/mysql-test/r/subselect_cache.result	2012-02-15 17:08:08 +0000
+++ b/mysql-test/r/subselect_cache.result	2012-05-11 15:25:03 +0000
@@ -486,9 +486,9 @@ Handler_read_key	7
 Handler_read_last       0
 Handler_read_next       0
 Handler_read_prev       0
-Handler_read_rnd        10
+Handler_read_rnd        0
 Handler_read_rnd_deleted        0
-Handler_read_rnd_next   42
+Handler_read_rnd_next   31
 set optimizer_switch='subquery_cache=off';
 flush status;
 select a from t1 ORDER BY (select d from t2 where b=c);
@@ -514,9 +514,9 @@ Handler_read_key	0
 Handler_read_last       0
 Handler_read_next       0
 Handler_read_prev       0
-Handler_read_rnd        10
+Handler_read_rnd        0
 Handler_read_rnd_deleted        0
-Handler_read_rnd_next   72
+Handler_read_rnd_next   61
 set optimizer_switch='subquery_cache=on';
 #single value subquery test (distinct ORDER BY)
 flush status;
=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2012-01-13 14:50:02 +0000
+++ b/mysql-test/r/subselect_innodb.result	2012-05-11 15:25:03 +0000
@@ -272,8 +272,8 @@ FROM t2
 WHERE (SELECT DISTINCT b FROM t3) > 0);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       Using where; Using index
-2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       
-3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    1       Using temporary
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+3       SUBQUERY        internal_tmp_table      ALL     group_key       NULL    NULL    NULL    0       Using temporary
 SELECT *
 FROM t1
 WHERE t1.a = (
@@ -301,7 +301,7 @@ GROUP BY 1
 );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    1       
 SELECT MAX( f1 ) FROM t2
 WHERE f2 >= (
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2012-03-17 08:26:58 +0000
+++ b/mysql-test/r/subselect_mat.result	2012-05-11 15:25:03 +0000
@@ -1152,8 +1152,8 @@ create table t2 (b1 int);
 insert into t1 values (5);
 explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       MATERIALIZED    t2      system  NULL    NULL    NULL    NULL    0       const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       MATERIALIZED    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 min(a1)
 NULL
@@ -1162,8 +1162,8 @@ set @@optimizer_switch=@optimizer_switch
 set @@optimizer_switch='materialization=off,in_to_exists=on';
 explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t2      system  NULL    NULL    NULL    NULL    0       const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 min(a1)
 NULL
@@ -1171,8 +1171,8 @@ set @@optimizer_switch=@optimizer_switch
 set @@optimizer_switch='semijoin=off';
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       MATERIALIZED    t2      system  NULL    NULL    NULL    NULL    0       const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       MATERIALIZED    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select b1 from t2);
 min(a1)
 NULL
@@ -1181,16 +1181,16 @@ set @@optimizer_switch='materialization=
 # with MariaDB and MWL#90, this particular case is solved:
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select b1 from t2);
 min(a1)
 NULL
 # but when we go around MWL#90 code, the problem still shows up:
 explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
 min(a1)
 NULL
@@ -1932,7 +1932,7 @@ WHERE s2.d = s1.e AND s1.e = (SELECT MAX
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
 1       PRIMARY t2      index   NULL    c       5       NULL    8       Using where; Using index
-2       MATERIALIZED    s2      ref     d       d       4       const   1       Using where; Using index
+2       MATERIALIZED    s2      ref     d       d       4       const   2       Using where; Using index
 2       MATERIALIZED    s1      ALL     NULL    NULL    NULL    NULL    8       Using where; Using join buffer (flat, BNL join)
 3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    8       
 SELECT a, c FROM t1, t2
@@ -1951,8 +1951,8 @@ WHERE s2.d = s1.e AND s1.e = (SELECT MAX
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
 1       PRIMARY t2      index   NULL    c       5       NULL    8       Using where; Using index
-2       MATERIALIZED    s2      ref     d       d       4       const   1       Using where; Using index
-2       MATERIALIZED    s1      hash_ALL        NULL    #hash#$hj       5       test.s2.d       8       Using where; Using join buffer (flat, BNLH join)
+2       MATERIALIZED    s2      ref     d       d       4       const   2       Using where; Using index
+2       MATERIALIZED    s1      ALL     NULL    NULL    NULL    NULL    8       Using where; Using join buffer (flat, BNL join)
 3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    8       
 SELECT a, c FROM t1, t2
 WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
@@ -2153,7 +2153,7 @@ set @@optimizer_switch='materialization=
 EXPLAIN
 SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 3       MATERIALIZED    t1      ALL     NULL    NULL    NULL    NULL    2       
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
@@ -2222,10 +2222,10 @@ NULL
 EXPLAIN EXTENDED
 SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    b       5       NULL    2       100.00  Using index
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
-Note    1003    select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where <expr_cache><9>(<in_optimizer>(9,9 in ( <materialize> (select `test`.`t2`.`a` from `test`.`t2` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery2>`.`a`))))))
+Note    1003    select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0
 set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
 SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2);
 max_res
@@ -2233,10 +2233,10 @@ NULL
 EXPLAIN EXTENDED
 SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      index   NULL    b       5       NULL    2       100.00  Using index
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
-Note    1003    select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where <expr_cache><9>(<in_optimizer>(9,<exists>(select `test`.`t2`.`a` from `test`.`t2` where (9 = `test`.`t2`.`a`))))
+Note    1003    select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0
 DROP TABLE t1,t2;
 #
 # LPBUG#825095: Wrong result with materialization and NOT IN with 2 expressions
=== modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
--- a/mysql-test/r/subselect_mat_cost_bugs.result	2012-01-13 14:50:02 +0000
+++ b/mysql-test/r/subselect_mat_cost_bugs.result	2012-05-11 15:25:03 +0000
@@ -124,7 +124,7 @@ FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1
 WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 SELECT t1.*
 FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1
 WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 );
@@ -148,7 +148,7 @@ FROM t2 GROUP BY f1
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    1       
-3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
+3       SUBQUERY        internal_tmp_table      ALL     group_key       NULL    NULL    NULL    1       Using temporary; Using filesort
 drop table t1, t2, t3;
 #
 # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-05-04 05:16:38 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-05-11 15:25:03 +0000
@@ -378,12 +378,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where; Using index
+1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 4       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
-2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where
+2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  
 3       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 Warnings:
-Note    1003    select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))
+Note    1003    select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 t8 WHERE pseudo='joce');
 ERROR 21000: Operand should contain 1 column(s)
@@ -524,6 +524,7 @@ ERROR 21000: Subquery returns more than
 show warnings;
 Level   Code    Message
 Error   1242    Subquery returns more than 1 row
+Error   1028    Sort aborted: Subquery returns more than 1 row
 drop table t1;
 create table t1 (a int);
 insert into t1 values (1),(2),(3);
@@ -554,11 +555,7 @@ SELECT numreponse, (SELECT numeropost FR
 numreponse      (SELECT numeropost FROM t1 HAVING numreponse=1)
 INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
-id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
-2       SUBQUERY        t1      ref     PRIMARY PRIMARY 3       const   2       100.00  Using index
-Warnings:
-Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+ERROR 21000: Subquery returns more than 1 row
 SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 ERROR 21000: Subquery returns more than 1 row
 EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
@@ -1682,34 +1679,34 @@ select * from t3 where NULL >= any (sele
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1771,7 +1768,7 @@ id	select_type	table	type	possible_keys
 3       UNION   t1      system  NULL    NULL    NULL    NULL    1       100.00  
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 Warnings:
-Note    1003    select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f')))
+Note    1003    select 'e' AS `s1` from dual where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
@@ -3105,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3117,7 +3114,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3731,8 +3728,8 @@ explain select * from t1 where not exist
 ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-2       SUBQUERY        t11     system  NULL    NULL    NULL    NULL    0       const row not found
-3       UNION   t12     system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
+3       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
@@ -4202,8 +4199,8 @@ CREATE TABLE t1 (a int, b int, KEY (a));
 INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ref     a       a       5       const   0       Using where; Using index
-2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
+1       PRIMARY t1      ref     a       a       5       const   1       Using where; Using index
+2       SUBQUERY        internal_tmp_table      ALL     group_key       NULL    NULL    NULL    1       Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -4531,13 +4528,13 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using temporary
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY 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  
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using temporary
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))))
+Note    1003    select 1 AS `1` from `test`.`t1` where 0
 SET join_cache_level=@save_join_cache_level;
 DROP TABLE t1;
 #
@@ -4628,7 +4625,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5961,7 +5958,7 @@ id	select_type	table	type	possible_keys
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1       
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6120,7 +6117,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1
DROP TABLE t1;
 #
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-05-04 05:16:38 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-05-11 15:25:03 +0000
@@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where; Using index
+1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 4       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
-2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where
+2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  
 3       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 Warnings:
-Note    1003    select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))
+Note    1003    select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 t8 WHERE pseudo='joce');
 ERROR 21000: Operand should contain 1 column(s)
@@ -520,6 +520,7 @@ ERROR 21000: Subquery returns more than
 show warnings;
 Level   Code    Message
 Error   1242    Subquery returns more than 1 row
+Error   1028    Sort aborted: Subquery returns more than 1 row
 drop table t1;
 create table t1 (a int);
 insert into t1 values (1),(2),(3);
@@ -550,11 +551,7 @@ SELECT numreponse, (SELECT numeropost FR
 numreponse      (SELECT numeropost FROM t1 HAVING numreponse=1)
 INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
-id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
-2       SUBQUERY        t1      ref     PRIMARY PRIMARY 3       const   2       100.00  Using index
-Warnings:
-Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+ERROR 21000: Subquery returns more than 1 row
 SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 ERROR 21000: Subquery returns more than 1 row
 EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
@@ -1678,34 +1675,34 @@ select * from t3 where NULL >= any (sele
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1767,7 +1764,7 @@ id	select_type	table	type	possible_keys
 3       UNION   t1      system  NULL    NULL    NULL    NULL    1       100.00  
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 Warnings:
-Note    1003    select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f')))
+Note    1003    select 'e' AS `s1` from dual where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
@@ -3101,7 +3098,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3113,7 +3110,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3727,8 +3724,8 @@ explain select * from t1 where not exist
 ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-2       SUBQUERY        t11     system  NULL    NULL    NULL    NULL    0       const row not found
-3       UNION   t12     system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
+3       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
@@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a));
 INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ref     a       a       5       const   0       Using where; Using index
-2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
+1       PRIMARY t1      ref     a       a       5       const   1       Using where; Using index
+2       SUBQUERY        internal_tmp_table      ALL     group_key       NULL    NULL    NULL    1       Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -4527,13 +4524,13 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using temporary
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY 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  
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
 2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using temporary
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))
+Note    1003    select 1 AS `1` from `test`.`t1` where 0
 SET join_cache_level=@save_join_cache_level;
 DROP TABLE t1;
 #
@@ -4624,7 +4621,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5957,7 +5954,7 @@ id	select_type	table	type	possible_keys
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1       
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6116,7 +6113,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1
DROP TABLE t1;
 #
=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-05-04 05:16:38 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-05-11 15:25:03 +0000
@@ -377,12 +377,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where; Using index
+1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 4       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
-2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where
+2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  
 3       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 Warnings:
-Note    1003    select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))
+Note    1003    select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 t8 WHERE pseudo='joce');
 ERROR 21000: Operand should contain 1 column(s)
@@ -523,6 +523,7 @@ ERROR 21000: Subquery returns more than
 show warnings;
 Level   Code    Message
 Error   1242    Subquery returns more than 1 row
+Error   1028    Sort aborted: Subquery returns more than 1 row
 drop table t1;
 create table t1 (a int);
 insert into t1 values (1),(2),(3);
@@ -553,11 +554,7 @@ SELECT numreponse, (SELECT numeropost FR
 numreponse      (SELECT numeropost FROM t1 HAVING numreponse=1)
 INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
-id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
-2       SUBQUERY        t1      ref     PRIMARY PRIMARY 3       const   2       100.00  Using index
-Warnings:
-Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+ERROR 21000: Subquery returns more than 1 row
 SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 ERROR 21000: Subquery returns more than 1 row
 EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
@@ -1681,34 +1678,34 @@ select * from t3 where NULL >= any (sele
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1770,7 +1767,7 @@ id	select_type	table	type	possible_keys
 3       UNION   t1      system  NULL    NULL    NULL    NULL    1       100.00  
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 Warnings:
-Note    1003    select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f')))
+Note    1003    select 'e' AS `s1` from dual where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
@@ -3105,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3117,7 +3114,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3733,8 +3730,8 @@ explain select * from t1 where not exist
 ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-2       SUBQUERY        t11     system  NULL    NULL    NULL    NULL    0       const row not found
-3       UNION   t12     system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
+3       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
@@ -4204,8 +4201,8 @@ CREATE TABLE t1 (a int, b int, KEY (a));
 INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ref     a       a       5       const   0       Using where; Using index
-2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
+1       PRIMARY t1      ref     a       a       5       const   1       Using where; Using index
+2       SUBQUERY        internal_tmp_table      ALL     group_key       NULL    NULL    NULL    1       Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -4632,7 +4629,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5966,7 +5963,7 @@ id	select_type	table	type	possible_keys
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1       
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6127,7 +6124,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1
DROP TABLE t1;
 #
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-05-04 05:16:38 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-05-11 15:25:03 +0000
@@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where; Using index
+1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 4       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
-2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using where
+2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  
 3       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 Warnings:
-Note    1003    select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))
+Note    1003    select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 t8 WHERE pseudo='joce');
 ERROR 21000: Operand should contain 1 column(s)
@@ -520,6 +520,7 @@ ERROR 21000: Subquery returns more than
 show warnings;
 Level   Code    Message
 Error   1242    Subquery returns more than 1 row
+Error   1028    Sort aborted: Subquery returns more than 1 row
 drop table t1;
 create table t1 (a int);
 insert into t1 values (1),(2),(3);
@@ -550,11 +551,7 @@ SELECT numreponse, (SELECT numeropost FR
 numreponse      (SELECT numeropost FROM t1 HAVING numreponse=1)
 INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
-id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
-2       SUBQUERY        t1      ref     PRIMARY PRIMARY 3       const   2       100.00  Using index
-Warnings:
-Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+ERROR 21000: Subquery returns more than 1 row
 SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 ERROR 21000: Subquery returns more than 1 row
 EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
@@ -1678,34 +1675,34 @@ select * from t3 where NULL >= any (sele
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= 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  
-2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1767,7 +1764,7 @@ id	select_type	table	type	possible_keys
 3       UNION   t1      system  NULL    NULL    NULL    NULL    1       100.00  
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 Warnings:
-Note    1003    select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f')))
+Note    1003    select 'e' AS `s1` from dual where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
@@ -3101,7 +3098,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3113,7 +3110,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 2       DEPENDENT 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'
@@ -3727,8 +3724,8 @@ explain select * from t1 where not exist
 ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-2       SUBQUERY        t11     system  NULL    NULL    NULL    NULL    0       const row not found
-3       UNION   t12     system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
+3       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
@@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a));
 INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      ref     a       a       5       const   0       Using where; Using index
-2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
+1       PRIMARY t1      ref     a       a       5       const   1       Using where; Using index
+2       SUBQUERY        internal_tmp_table      ALL     group_key       NULL    NULL    NULL    1       Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -4525,15 +4522,15 @@ SET join_cache_level=0;
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY 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  
-2       MATERIALIZED    t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using temporary
+2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using temporary
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`))))))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY 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  
-2       MATERIALIZED    t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using temporary
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using temporary
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`))))))
+Note    1003    select 1 AS `1` from `test`.`t1` where 0
 SET join_cache_level=@save_join_cache_level;
 DROP TABLE t1;
 #
@@ -4624,7 +4621,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       100.00  
 Warnings:
-Note    1003    select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
+Note    1003    select 1 AS `1` from `test`.`t1` where 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5957,7 +5954,7 @@ id	select_type	table	type	possible_keys
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1       
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6116,7 +6113,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
-2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+2       SUBQUERY        t1      ref     a       a       5       const   1
DROP TABLE t1;
 #
=== modified file 'mysql-test/r/subselect_sj_mat.result'
--- a/mysql-test/r/subselect_sj_mat.result	2012-04-07 13:58:46 +0000
+++ b/mysql-test/r/subselect_sj_mat.result	2012-05-11 15:25:03 +0000
@@ -1197,8 +1197,8 @@ set @@optimizer_switch=@optimizer_switch
 set @@optimizer_switch='materialization=off,in_to_exists=on';
 explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t2      system  NULL    NULL    NULL    NULL    0       const row not found
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 min(a1)
 NULL
@@ -1206,8 +1206,8 @@ set @@optimizer_switch=@optimizer_switch
 set @@optimizer_switch='semijoin=off';
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select b1 from t2);
 min(a1)
 NULL
@@ -1223,8 +1223,8 @@ NULL
 # but when we go around MWL#90 code, the problem still shows up:
 explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
 min(a1)
 NULL
@@ -1971,7 +1971,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
 1       PRIMARY t2      index   c       c       5       NULL    8       Using index
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    8       func,func       1       
-2       MATERIALIZED    s2      ref     d       d       4       const   1       Using where; Using index
+2       MATERIALIZED    s2      ref     d       d       4       const   2       Using where; Using index
 2       MATERIALIZED    s1      ALL     c       NULL    NULL    NULL    8       Using where; Using join buffer (flat, BNL join)
 3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    8       
 SELECT a, c FROM t1, t2
@@ -1991,8 +1991,8 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
 1       PRIMARY t2      index   c       c       5       NULL    8       Using index
 1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    8       func,func       1       
-2       MATERIALIZED    s2      ref     d       d       4       const   1       Using where; Using index
-2       MATERIALIZED    s1      hash_ALL        c       #hash#$hj       10      const,test.s2.d 8       Using where; Using join buffer (flat, BNLH join)
+2       MATERIALIZED    s2      ref     d       d       4       const   2       Using where; Using index
+2       MATERIALIZED    s1      hash_ALL        c       #hash#$hj       5       const   8       Using where; Using join buffer (flat, BNLH join)
 3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    8       
 SELECT a, c FROM t1, t2
 WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
=== modified file 'mysql-test/suite/sys_vars/r/all_vars.result'
--- a/mysql-test/suite/sys_vars/r/all_vars.result	2012-03-09 07:06:59 +0000
+++ b/mysql-test/suite/sys_vars/r/all_vars.result	2012-05-11 15:25:03 +0000
@@ -10,5 +10,6 @@ where length(variable_name) > 50;
 select distinct variable_name as `there should be *no* variables listed below:` from t2
 left join t1 on variable_name=test_name where test_name is null;
 there should be *no* variables listed below:
+expensive_subquery_limit
 drop table t1;
 drop table t2;
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2012-05-04 05:16:38 +0000
+++ b/mysql-test/t/subselect.test	2012-05-11 15:25:03 +0000
@@ -304,6 +304,7 @@ SELECT (SELECT numeropost FROM t1 HAVING
 SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
 SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
 INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
+-- error ER_SUBQUERY_NO_1_ROW
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 -- error ER_SUBQUERY_NO_1_ROW
 SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2012-03-01 22:22:22 +0000
+++ b/mysql-test/t/subselect4.test	2012-05-11 15:25:03 +0000
@@ -977,6 +977,14 @@ SELECT * FROM t1 WHERE
         FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
         GROUP BY SQ1_t1.f4));
+--error ER_SUBQUERY_NO_1_ROW
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+ WHERE f4 <= ALL
+       (SELECT max(SQ1_t1.f4)
+        FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+        GROUP BY SQ1_t1.f4));
+
 drop table t1, t2, t3;
--echo #
=== modified file 'sql/item.h'
--- a/sql/item.h	2012-04-10 06:28:13 +0000
+++ b/sql/item.h	2012-05-11 15:25:03 +0000
@@ -1395,21 +1395,21 @@ class Item {
   {
     return cmp_context == IMPOSSIBLE_RESULT || item->cmp_context == cmp_context;
   }
-  /*
+  /**
     Test whether an expression is expensive to compute. Used during
     optimization to avoid computing expensive expressions during this
     phase. Also used to force temp tables when sorting on expensive
     functions.
-    TODO:
+    @todo
     Normally we should have a method:
       cost Item::execution_cost(),
     where 'cost' is either 'double' or some structure of various cost
     parameters.
-    NOTE
-      This function is now used to prevent evaluation of materialized IN
-      subquery predicates before it is allowed. grep for 
-      DontEvaluateMaterializedSubqueryTooEarly to see the uses.
+    @note
+      This function is now used to prevent evaluation of expensive subquery
+      predicates during the optimization phase. It also prevents evaluation
+      of predicates that are not computable at this moment (infinite cost).
Could you elaborate more on the "infinite cost" part? (let's discuss it on irc)
...
*/
   virtual bool is_expensive()
   {
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2012-03-28 18:25:31 +0000
+++ b/sql/item_cmpfunc.cc	2012-05-11 15:25:03 +0000
@@ -5539,7 +5539,8 @@ void Item_equal::add_const(Item *c, Item
   else
   {
     Item_func_eq *func= new Item_func_eq(c, const_item);
-    func->set_cmp_func();
+    if(func->set_cmp_func())
+      return;
Please add space after "if",
Please add a comment about what kind of condition can cause the problem. As
far as I understand, this is a kind of error?
...
func->quick_fix_field();
     cond_false= !func->val_int();
   }
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2012-05-04 05:16:38 +0000
+++ b/sql/item_cmpfunc.h	2012-05-11 15:25:03 +0000
@@ -370,9 +370,9 @@ class Item_bool_func2 :public Item_int_f
   Item_bool_func2(Item *a,Item *b)
     :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1), abort_on_null(FALSE) {}
   void fix_length_and_dec();
-  void set_cmp_func()
+  int set_cmp_func()
   {
-    cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, TRUE);
+    return cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, TRUE);
   }
   optimize_type select_optimize() const { return OPTIMIZE_OP; }
   virtual enum Functype rev_functype() const { return UNKNOWN_FUNC; }
=== modified file 'sql/item_strfunc.h'
--- a/sql/item_strfunc.h	2012-03-06 19:46:07 +0000
+++ b/sql/item_strfunc.h	2012-05-11 15:25:03 +0000
@@ -829,7 +829,7 @@ class Item_func_conv_charset :public Ite
   {
     DBUG_ASSERT(args[0]->fixed);
     conv_charset= cs;
-    if (cache_if_const && args[0]->const_item() && !args[0]->with_subselect)
+    if (cache_if_const && args[0]->const_item() && !args[0]->is_expensive())
     {
       uint errors= 0;
       String tmp, *str= args[0]->val_str(&tmp);
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-05-04 05:16:38 +0000
+++ b/sql/item_subselect.cc	2012-05-11 15:25:03 +0000
@@ -522,6 +522,48 @@ void Item_subselect::recalc_used_tables(
   */
 }
+
+/**
+  Determine if a subquery is expensive to execute during query optimization.
+
+  @details The cost of execution of a subquery is estimated based on an
+  estimate of the number of rows the subquery will access during execution.
+  This measure is used instead of JOIN::read_time, because it is considered
+  to be much more reliable than the cost estimate.
+
+  @return true if the subquery is expensive
+  @return false otherwise
+*/
+bool Item_subselect::is_expensive()
+{
+  double examined_rows= 0;
+
+  for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+  {
+    JOIN *cur_join= sl->join;
+    if (!cur_join)
+      continue;
+
+    /* If a subquery is not optimized we cannot estimate its cost. */
+    if (!cur_join->join_tab)
+      return true;
+
+    if (sl->first_inner_unit())
+    {
+      /*
+        Subqueries that contain subqueries are considered expensive.
+        @todo: accumulate the cost of subqueries.
+      */
+      return true;
+    }
+
+    examined_rows+= cur_join->get_examined_rows();
+  }
+
+  return (examined_rows > thd->variables.expensive_subquery_limit);
+}
+
+
 bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
                           uchar *argument)
 {
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2012-05-04 05:16:38 +0000
+++ b/sql/item_subselect.h	2012-05-11 15:25:03 +0000
@@ -191,6 +191,7 @@ class Item_subselect :public Item_result
   table_map used_tables() const;
   table_map not_null_tables() const { return 0; }
   bool const_item() const;
+  virtual bool const_pred() const { return const_item(); }
What is this function for? It seems to have the same semantics as const_item(),
but it is a separate function, which implies there is some difference. Could
you add a comment about this?
...
inline table_map get_used_tables_cache() { return used_tables_cache; }
   Item *get_tmp_table_item(THD *thd);
   void update_used_tables();
@@ -209,7 +210,7 @@ class Item_subselect :public Item_result
   */
   bool is_evaluated() const;
   bool is_uncacheable() const;
-  bool is_expensive() { return TRUE; }
+  bool is_expensive();
/*
     Used by max/min subquery to initialize value presence registration
@@ -235,7 +236,7 @@ class Item_subselect :public Item_result
     @retval TRUE  if the predicate is expensive
     @retval FALSE otherwise
   */
-  bool is_expensive_processor(uchar *arg) { return TRUE; }
+  bool is_expensive_processor(uchar *arg) { return is_expensive(); }
/**
     Get the SELECT_LEX structure associated with this Item.
@@ -581,6 +582,7 @@ class Item_in_subselect :public Item_exi
   bool fix_fields(THD *thd, Item **ref);
   void fix_length_and_dec();
   void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  bool const_pred() const { return const_item() && left_expr->const_item(); }
   void update_used_tables();
   bool setup_mat_engine();
   bool init_left_expr_cache();
=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-05-04 05:16:38 +0000
+++ b/sql/opt_subselect.cc	2012-05-11 15:25:03 +0000
@@ -4872,7 +4872,43 @@ static void remove_subq_pushed_predicate
bool JOIN::optimize_unflattened_subqueries()
 {
-  return select_lex->optimize_unflattened_subqueries();
+  return select_lex->optimize_unflattened_subqueries(false);
+}
+
+/**
+  Optimize all constant subqueries of a query that were not flattened into
+  a semijoin.
+
+  @details
+  Similar to other constant conditions, constant subqueries can be used in
+  various constant optimizations. Having optimized constant subqueries before
+  these constant optimizations, makes it possible to estimate if a subquery
+  is "cheap" enough to be executed during the optimization phase.
+
+  Constant subqueries can be optimized and evaluated independent of the outer
+  query, therefore if const_only = true, this method can be called early in
+  the optimization phase of the outer query.
+
+  @return Operation status
+  @retval FALSE     success.
+  @retval TRUE      error occurred.
+*/
+ 
+bool JOIN::optimize_constant_subqueries()
+{
+  ulonglong save_options= select_lex->options;
+  bool res;
+  /*
+    Constant subqueries may be executed during the optimization phase.
+    In EXPLAIN mode the optimizer doesn't initialize many of the data structures
+    needed for execution. In order to make it possible to execute subqueries
+    during optimization, constant subqueries must be optimized for execution,
+    not for EXPLAIN.
+  */
+  select_lex->options&= ~SELECT_DESCRIBE;
+  res= select_lex->optimize_unflattened_subqueries(true);
+  select_lex->options= save_options;
+  return res;
 }
@@ -5273,7 +5309,14 @@ bool JOIN::choose_subquery_plan(table_ma
       by the IN predicate.
     */
     outer_join= unit->outer_select() ? unit->outer_select()->join : NULL;
-    if (outer_join && outer_join->table_count > 0)
+    /*
+      Get the cost of the outer join if:
+      (1) It has at least one table, and
+      (2) It has been already optimized (if there is no join_tab, then the
+          outer join has not been optimized yet).
+    */
+    if (outer_join && outer_join->table_count > 0 && // (1)
+        outer_join->join_tab)                        // (2)
     {
       /*
         TODO:
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-03-11 22:45:18 +0000
+++ b/sql/sql_class.h	2012-05-11 15:25:03 +0000
@@ -482,6 +482,7 @@ typedef struct system_variables
   ulonglong group_concat_max_len;
   ha_rows select_limit;
   ha_rows max_join_size;
+  ha_rows expensive_subquery_limit;
   ulong auto_increment_increment, auto_increment_offset;
   ulong lock_wait_timeout;
   ulong join_cache_level;
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2012-03-24 17:21:22 +0000
+++ b/sql/sql_delete.cc	2012-05-11 15:25:03 +0000
@@ -120,7 +120,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   }
/* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */
-  if (select_lex->optimize_unflattened_subqueries())
+  if (select_lex->optimize_unflattened_subqueries(false))
     DBUG_RETURN(TRUE);
const_cond= (!conds || conds->const_item());
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-05-04 05:16:38 +0000
+++ b/sql/sql_lex.cc	2012-05-11 15:25:03 +0000
@@ -3405,7 +3405,23 @@ bool st_select_lex::add_index_hint (THD
 }
-bool st_select_lex::optimize_unflattened_subqueries()
+/**
+  Optimize all subqueries that have not been flattened into semi-joins.
+
+  @details
+  This functionality is a method of SELECT_LEX instead of JOIN because
+  SQL statements as DELETE/UPDATE do not have a corresponding JOIN object.
+
+  @see JOIN::optimize_unflattened_subqueries
+
+  @param const_only  Restrict subquery optimization to constant subqueries
+
+  @return Operation status
+  @retval FALSE     success.
+  @retval TRUE      error occurred.
+*/
+
+bool st_select_lex::optimize_unflattened_subqueries(bool const_only)
 {
   for (SELECT_LEX_UNIT *un= first_inner_unit(); un; un= un->next_unit())
   {
@@ -3415,11 +3431,17 @@ bool st_select_lex::optimize_unflattened
     {
       if (subquery_predicate->substype() == Item_subselect::IN_SUBS)
       {
-        Item_in_subselect *in_subs=(Item_in_subselect*)subquery_predicate;
+        Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate;
         if (in_subs->is_jtbm_merged)
           continue;
       }
+      if (const_only && !subquery_predicate->const_pred())
+      {
+        /* Skip non-constant subqueries if the caller asked so. */
+        continue;
+      }
+
       bool empty_union_result= true;
       /*
         If the subquery is a UNION, optimize all the subqueries in the UNION. If
=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2012-04-10 06:28:13 +0000
+++ b/sql/sql_lex.h	2012-05-11 15:25:03 +0000
@@ -997,12 +997,7 @@ class st_select_lex: public st_select_le
void clear_index_hints(void) { index_hints= NULL; }
   bool is_part_of_union() { return master_unit()->is_union(); }
-  /*
-    Optimize all subqueries that have not been flattened into semi-joins.
-    This functionality is a method of SELECT_LEX instead of JOIN because
-    some SQL statements as DELETE do not have a corresponding JOIN object.
-  */
-  bool optimize_unflattened_subqueries();
+  bool optimize_unflattened_subqueries(bool const_only);
   /* Set the EXPLAIN type for this subquery. */
   void set_explain_type();
   bool handle_derived(LEX *lex, uint phases);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-05-04 05:16:38 +0000
+++ b/sql/sql_select.cc	2012-05-11 15:25:03 +0000
@@ -986,7 +986,10 @@ JOIN::optimize()
   }
eval_select_list_used_tables();
-  
+
+  if (optimize_constant_subqueries())
+    DBUG_RETURN(1);
+
   table_count= select_lex->leaf_tables.elements;
if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
@@ -1273,6 +1276,12 @@ JOIN::optimize()
   {
     conds= substitute_for_best_equal_field(NO_PARTICULAR_TAB, conds, 
                                            cond_equal, map2table);
+    if (thd->is_error())
+    {
+      error= 1;
+      DBUG_PRINT("error",("Error from substitute_for_best_equal"));
+      DBUG_RETURN(1);
+    }
     conds->update_used_tables();
     DBUG_EXECUTE("where",
                  print_where(conds,
@@ -1293,6 +1302,12 @@ JOIN::optimize()
                                                          *tab->on_expr_ref,
                                                          tab->cond_equal,
                                                          map2table);
+      if (thd->is_error())
+      {
+        error= 1;
+        DBUG_PRINT("error",("Error from substitute_for_best_equal"));
+        DBUG_RETURN(1);
+      }
       (*tab->on_expr_ref)->update_used_tables();
     }
   }
@@ -6592,6 +6607,36 @@ void JOIN::get_prefix_cost_and_fanout(ui
/**
+  Estimate the number of rows that query execution will read.
+
+  @todo This is a very pessimistic upper bound. Use join selectivity
+  when available to produce a more realistic number.
+*/
+
+double JOIN::get_examined_rows()
+{
+  /* Each constant table examines one row, and the result is at most one row. */
+  ha_rows examined_rows= const_tables;
+  uint i= const_tables;
+  double prev_fanout;
+
+  if (table_count == const_tables)
+    return examined_rows;
+
+  examined_rows+= join_tab[i++].get_examined_rows();
+  for (; i < table_count ; i++)
+  {
+    if (join_tab[i].type == JT_EQ_REF)
+      prev_fanout= 1;
+    else
+      prev_fanout= best_positions[i-1].records_read;
This looks wrong. Declaration of POSITION::records_read has this comment:

  /*
    The "fanout": number of output rows that will be produced (after
    pushed down selection condition is applied) per each row combination of
    previous tables.
  */

note the "PER EACH ROW COMBINATION .." part.  I would expect that this function
would calculate a product of records_read values.
...
+    examined_rows+= join_tab[i].get_examined_rows() * prev_fanout;
+  }
+  return examined_rows;
+}
+
+
+/**
   Find a good, possibly optimal, query execution plan (QEP) by a possibly
   exhaustive search.
@@ -8011,36 +8056,15 @@ JOIN::make_simple_join(JOIN *parent, TAB
   row_limit= unit->select_limit_cnt;
   do_send_rows= row_limit ? 1 : 0;
-  join_tab->use_join_cache= FALSE;
-  join_tab->cache=0;                            /* No caching */
+  bzero(join_tab, sizeof(JOIN_TAB));
   join_tab->table=temp_table;
-  join_tab->cache_select= 0;
-  join_tab->select=0;
-  join_tab->select_cond= 0;                     // Avoid valgrind warning
   join_tab->set_select_cond(NULL, __LINE__);
-  join_tab->quick=0;
   join_tab->type= JT_ALL;                       /* Map through all records */
   join_tab->keys.init();
   join_tab->keys.set_all();                     /* test everything in quick */
-  join_tab->info=0;
-  join_tab->on_expr_ref=0;
-  join_tab->last_inner= 0;
-  join_tab->first_unmatched= 0;
   join_tab->ref.key = -1;
-  join_tab->not_used_in_distinct=0;
   join_tab->read_first_record= join_init_read_record;
-  join_tab->preread_init_done= FALSE;
   join_tab->join= this;
-  join_tab->ref.key_parts= 0;
-  join_tab->keep_current_rowid= FALSE;
-  join_tab->flush_weedout_table= join_tab->check_weed_out_table= NULL;
-  join_tab->do_firstmatch= NULL;
-  join_tab->loosescan_match_tab= NULL;
-  join_tab->emb_sj_nest= NULL;
-  join_tab->pre_idx_push_select_cond= NULL;
-  join_tab->bush_root_tab= NULL;
-  join_tab->bush_children= NULL;
-  join_tab->last_leaf_in_bush= FALSE;
   bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
   temp_table->status=0;
   temp_table->null_row=0;
@@ -10225,6 +10249,51 @@ double JOIN_TAB::scan_time()
   return res;
 }
+
+/**
+  Estimate the number of rows that a an access method will read from a table.
+
+  @todo: why not use JOIN_TAB::found_records
+*/
+
+ha_rows JOIN_TAB::get_examined_rows()
+{
+  ha_rows examined_rows;
+
+  if (select && select->quick)
+    examined_rows= select->quick->records;
+  else if (type == JT_NEXT || type == JT_ALL ||
+           type == JT_HASH || type ==JT_HASH_NEXT)
+  {
+    if (limit)
+    {
+      /*
+        @todo This estimate is wrong, a LIMIT query may examine much more rows
+        than the LIMIT itself.
+      */
+      examined_rows= limit;
+    }
+    else
+    {
+      if (table->is_filled_at_execution())
+        examined_rows= records;
+      else
+      {
+        /*
+          handler->info(HA_STATUS_VARIABLE) has been called in
+          make_join_statistics()
+        */
+        examined_rows= table->file->stats.records;
+      }
+    }
+  }
+  else
+    examined_rows= (ha_rows) records_read; 
+
+  return examined_rows;
+}
+
+
 /**
   Initialize the join_tab before reading.
   Currently only derived table/view materialization is done here.
@@ -11204,9 +11273,9 @@ static bool check_simple_equality(Item *
         if (!item)
         {
           Item_func_eq *eq_item;
-          if ((eq_item= new Item_func_eq(orig_left_item, orig_right_item)))
+          if (!(eq_item= new Item_func_eq(orig_left_item, orig_right_item)) ||
+              eq_item->set_cmp_func())
             return FALSE;
-          eq_item->set_cmp_func();
           eq_item->quick_fix_field();
           item= eq_item;
         }  
@@ -11299,9 +11368,9 @@ static bool check_row_equality(THD *thd,
     if (!is_converted)
     {
       Item_func_eq *eq_item;
-      if (!(eq_item= new Item_func_eq(left_item, right_item)))
+      if (!(eq_item= new Item_func_eq(left_item, right_item)) ||
+          eq_item->set_cmp_func())
         return FALSE;
-      eq_item->set_cmp_func();
       eq_item->quick_fix_field();
       eq_list->push_back(eq_item);
     }
@@ -11987,9 +12056,8 @@ Item *eliminate_item_equal(COND *cond, C
eq_item= new Item_func_eq(field_item->real_item(), head_item);
-      if (!eq_item)
+      if (!eq_item || eq_item->set_cmp_func())
         return 0;
-      eq_item->set_cmp_func();
       eq_item->quick_fix_field();
     }
     current_sjm= field_sjm;
@@ -12076,7 +12144,7 @@ Item *eliminate_item_equal(COND *cond, C
        Item_equal::get_first() for details.
@return
-    The transformed condition
+    The transformed condition, or NULL in case of error
 */
static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab,
@@ -18544,6 +18612,7 @@ check_reverse_order:
           tab->ref.key_parts= 0;
           if (select_limit < table->file->stats.records)
             tab->limit= select_limit;
+          table->disable_keyread();
^^^ This change looks weird. Let's discuss it.
...
}
       }
       else if (tab->type != JT_ALL)
@@ -21269,10 +21338,17 @@ static void select_describe(JOIN *join,
       }
       else
       {
-        TABLE_LIST *real_table= table->pos_in_table_list; 
-        item_list.push_back(new Item_string(real_table->alias,
-                                            strlen(real_table->alias),
-                                            cs));
+        TABLE_LIST *real_table= table->pos_in_table_list;
+        /*
+          Internal temporary tables have no corresponding table reference
+          object. Such a table may appear in EXPLAIN when a subquery that needs
+          a temporary table has been executed, and JOIN::exec replaced the
+          original JOIN with a plan to access the data in the temp table
+          (made by JOIN::make_simple_join).
+        */
+        const char *tab_name= real_table ? real_table->alias :
+                                           "internal_tmp_table";
+        item_list.push_back(new Item_string(tab_name, strlen(tab_name), cs));
       }
       /* "partitions" column */
       if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
@@ -21430,32 +21506,8 @@ static void select_describe(JOIN *join,
       }
       else
       {
-        ha_rows examined_rows;
-        if (tab->select && tab->select->quick)
-          examined_rows= tab->select->quick->records;
-        else if (tab->type == JT_NEXT || tab->type == JT_ALL || is_hj)
-        {
-          if (tab->limit)
-            examined_rows= tab->limit;
-          else
-          {
-            if (tab->table->is_filled_at_execution())
-            {
-              examined_rows= tab->records;
-            }
-            else
-            {
-              /*
-                handler->info(HA_STATUS_VARIABLE) has been called in
-                make_join_statistics()
-              */
-              examined_rows= tab->table->file->stats.records;
-            }
-          }
-        }
-        else
-          examined_rows=(ha_rows)tab->records_read; 
- 
+        ha_rows examined_rows= tab->get_examined_rows();
+
         item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, 
                                          MY_INT64_NUM_DECIMAL_DIGITS));
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-05-04 05:16:38 +0000
+++ b/sql/sql_select.h	2012-05-11 15:25:03 +0000
@@ -512,6 +512,7 @@ typedef struct st_join_table {
     return (is_hash_join_key_no(key) ? hj_key : table->key_info+key);
   }
   double scan_time();
+  ha_rows get_examined_rows();
   bool preread_init();
bool is_sjm_nest() { return test(bush_children); }
@@ -1281,6 +1282,7 @@ class JOIN :public Sql_alloc
   bool alloc_func_list();
   bool flatten_subqueries();
   bool optimize_unflattened_subqueries();
+  bool optimize_constant_subqueries();
   bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields,
                           bool before_group_by, bool recompute= FALSE);
@@ -1380,6 +1382,7 @@ class JOIN :public Sql_alloc
   void get_prefix_cost_and_fanout(uint n_tables, 
                                   double *read_time_arg,
                                   double *record_count_arg);
+  double get_examined_rows();
   /* defined in opt_subselect.cc */
   bool transform_max_min_subquery();
   /* True if this JOIN is a subquery under an IN predicate. */
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2012-05-04 05:16:38 +0000
+++ b/sql/sql_update.cc	2012-05-11 15:25:03 +0000
@@ -368,7 +368,7 @@ int mysql_update(THD *thd,
   }
/* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */
-  if (select_lex->optimize_unflattened_subqueries())
+  if (select_lex->optimize_unflattened_subqueries(false))
     DBUG_RETURN(TRUE);
if (select_lex->inner_refs_list.elements &&
=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2012-04-19 14:00:13 +0000
+++ b/sql/sys_vars.cc	2012-05-11 15:25:03 +0000
@@ -3780,4 +3780,9 @@ static Sys_var_ulong Sys_debug_binlog_fs
        CMD_LINE(REQUIRED_ARG),
        VALID_RANGE(0, UINT_MAX), DEFAULT(0), BLOCK_SIZE(1));
 #endif
-
+static Sys_var_harows Sys_expensive_subquery_limit(
+       "expensive_subquery_limit",
+       "The maximum number of rows a subquery examines in order to be "
+       "considered non-expensive",
+       SESSION_VAR(expensive_subquery_limit), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(0, HA_POS_ERROR), DEFAULT(100), BLOCK_SIZE(1));
=== modified file 'sql/table.cc'
--- a/sql/table.cc	2012-05-04 05:16:38 +0000
+++ b/sql/table.cc	2012-05-11 15:25:03 +0000
@@ -5985,7 +5985,8 @@ void TABLE::use_index(int key_to_save)
bool TABLE::is_filled_at_execution()
 { 
-  return test(pos_in_table_list->jtbm_subselect || 
+  return test(!pos_in_table_list ||
+              pos_in_table_list->jtbm_subselect || 
               pos_in_table_list->is_active_sjm());
Could you please add a comment for pos_in_table_list that it can be NULL for
some tables? (I assume you still remember what kind of tables have
pos_in_table_list==NULL?)
...
}
A general question: was this expected to work:

MariaDB [j3]> explain extended select * from t1 where 33 in (select b from
five) or c > 11;
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id   | select_type        | table | type | possible_keys | key  | key_len |
ref  | rows | filtered | Extra       |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    |
NULL |   10 |   100.00 | Using where |
|    2 | DEPENDENT SUBQUERY | five  | ALL  | NULL          | NULL | NULL    |
NULL |    5 |   100.00 | Using where |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (22 min 25.44 sec)

The subuqery is constant, table `five` has 5 records, and none of them has
b=33.  When debugging, I see JOIN::get_examined_rows() to be invoked and it
returns 5 rows,  but EXPLAIN still doesn't show "Impossible WHERE"? 

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog

    

Re: [Maria-developers] Review request for: [Commits] Rev 3403: Fix for bug lp:944706, task MDEV-193 in file:///home/tsk/mprog/src/5.5-lpb944706/

Sergei Petrunia

Sergei Petrunia

tags

participants (1)