← Back to team overview

maria-developers team mailing list archive

mwl148 for review

 

=== modified file 'mysql-test/r/explain.result'
--- mysql-test/r/explain.result    2011-05-10 15:28:05 +0000
+++ mysql-test/r/explain.result    2011-05-10 20:17:04 +0000
@@ -176,11 +176,15 @@
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
 EXPLAIN EXTENDED SELECT 1 FROM t1
 WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
-ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 SUBQUERY t system NULL NULL NULL NULL 0 0.00 const row not found
+Warnings:
+Note    1003    select 1 AS `1` from `test`.`t1` where 0
 SHOW WARNINGS;
 Level    Code    Message
-Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
-Note    1003    select 1 AS `1` from `test`.`t1` where <not>(<exists>(...))
+Note    1003    select 1 AS `1` from `test`.`t1` where 0
 SET SESSION sql_mode=@old_sql_mode;
 DROP TABLE t1;
 End of 5.0 tests.
@@ -272,12 +276,12 @@
 EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
 1    PRIMARY    t1    system    NULL    NULL    NULL    NULL    1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2    SUBQUERY    a    system    NULL    NULL    NULL    NULL    1
 2    SUBQUERY    t1    fulltext    f1    f1    0        1    Using where
 EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
 1    PRIMARY    t1    system    NULL    NULL    NULL    NULL    1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2    SUBQUERY    a    system    NULL    NULL    NULL    NULL    1
 2    SUBQUERY    t1    fulltext    f1    f1    0        1    Using where
 DEALLOCATE PREPARE stmt;
 PREPARE stmt FROM
@@ -288,12 +292,12 @@
 EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
 1    PRIMARY    t1    system    NULL    NULL    NULL    NULL    1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2    SUBQUERY    a    system    NULL    NULL    NULL    NULL    1
 2    SUBQUERY    t1    fulltext    f1    f1    0        1    Using where
 EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
 1    PRIMARY    t1    system    NULL    NULL    NULL    NULL    1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2    SUBQUERY    a    system    NULL    NULL    NULL    NULL    1
 2    SUBQUERY    t1    fulltext    f1    f1    0        1    Using where
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;

=== modified file 'mysql-test/r/subselect.result'
--- mysql-test/r/subselect.result    2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect.result    2011-05-10 20:17:04 +0000
@@ -273,7 +273,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    3    100.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1503,7 +1503,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1511,7 +1511,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1522,7 +1522,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1530,7 +1530,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1538,7 +1538,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1546,7 +1546,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1554,7 +1554,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1562,7 +1562,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 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
@@ -1573,7 +1573,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
 drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
@@ -1624,7 +1624,7 @@
 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 `test`.`t1` where <nop>(('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))) +Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))))
 drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- mysql-test/r/subselect_no_mat.result    2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect_no_mat.result    2011-05-10 20:17:04 +0000
@@ -277,7 +277,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    3    100.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1507,7 +1507,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1515,7 +1515,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1526,7 +1526,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1534,7 +1534,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1542,7 +1542,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1550,7 +1550,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1558,7 +1558,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1566,7 +1566,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 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
@@ -1577,7 +1577,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
 drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
@@ -1628,7 +1628,7 @@
 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 `test`.`t1` where <nop>(('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))) +Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))))
 drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- mysql-test/r/subselect_no_opts.result    2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect_no_opts.result    2011-05-10 20:17:04 +0000
@@ -274,7 +274,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    3    100.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1504,7 +1504,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1512,7 +1512,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1523,7 +1523,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1531,7 +1531,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1539,7 +1539,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1547,7 +1547,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1555,7 +1555,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1563,7 +1563,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 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
@@ -1574,7 +1574,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
 drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
@@ -1625,7 +1625,7 @@
 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 `test`.`t1` where <nop>(('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))) +Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))))
 drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- mysql-test/r/subselect_no_semijoin.result    2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect_no_semijoin.result    2011-05-10 20:17:04 +0000
@@ -274,7 +274,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    3    100.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1504,7 +1504,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1512,7 +1512,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1523,7 +1523,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1531,7 +1531,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1539,7 +1539,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1547,7 +1547,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1555,7 +1555,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
 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);
@@ -1563,7 +1563,7 @@
 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
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2` group by 1))))
 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
@@ -1574,7 +1574,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
 drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
@@ -1625,7 +1625,7 @@
 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 `test`.`t1` where <nop>(('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))) +Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`))))
 drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- mysql-test/suite/pbxt/r/subselect.result    2011-04-20 19:55:29 +0000
+++ mysql-test/suite/pbxt/r/subselect.result    2011-05-10 20:17:04 +0000
@@ -270,7 +270,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    3    100.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1498,7 +1498,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    0    0.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`))))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1506,7 +1506,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    0    0.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1517,7 +1517,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` from `test`.`t2` group by 1))))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1525,7 +1525,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1))))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1533,7 +1533,7 @@
 1    PRIMARY    t3    ALL    NULL    NULL    NULL    NULL    3    100.00
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    0    0.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 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);
@@ -1541,7 +1541,7 @@
 1    PRIMARY    t3    ALL    NULL    NULL    NULL    NULL    3    100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1))))
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1549,7 +1549,7 @@
 1    PRIMARY    t3    ALL    NULL    NULL    NULL    NULL    3    100.00
 2    SUBQUERY    t2    ALL    NULL    NULL    NULL    NULL    0    0.00
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`))))
 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);
@@ -1557,7 +1557,7 @@
 1    PRIMARY    t3    ALL    NULL    NULL    NULL    NULL    3    100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((NULL >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(NULL >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1))))
 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
@@ -1568,7 +1568,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary
 Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
 drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
@@ -1619,7 +1619,7 @@
 3    UNION    t1    ALL    NULL    NULL    NULL    NULL    1    100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
 Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where <nop>(('f' > <min>(select `test`.`t1`.`s1` from `test`.`t1` union select `test`.`t1`.`s1` from `test`.`t1`))) +Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where <nop>(<in_optimizer>('f',('f' > <min>(select `test`.`t1`.`s1` from `test`.`t1` union select `test`.`t1`.`s1` from `test`.`t1`))))
 drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

=== modified file 'mysql-test/t/explain.test'
--- mysql-test/t/explain.test    2011-05-05 01:08:44 +0000
+++ mysql-test/t/explain.test    2011-05-10 20:17:04 +0000
@@ -158,7 +158,10 @@
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';

 # EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE.
---error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+# Before moving max/min optimization to optimize phase this statement
+# generated error, but as far as original query do not contain aggregate
+# function user should not see error
+#  --error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
 EXPLAIN EXTENDED SELECT 1 FROM t1
                           WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
 SHOW WARNINGS;

=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc    2011-05-10 15:28:05 +0000
+++ sql/item_cmpfunc.cc    2011-05-10 20:17:04 +0000
@@ -1798,6 +1798,8 @@
 {
   THD *thd= (THD*) thd_arg;
   DBUG_ENTER("Item_in_optimizer::expr_cache_insert_transformer");
+  if (args[1]->type() != Item::SUBSELECT_ITEM)
+    DBUG_RETURN(this); // MAX/MIN transformed => do nothing
   List<Item*> &depends_on= ((Item_subselect *)args[1])->depends_on;

   if (expr_cache)
@@ -1901,7 +1903,15 @@
   DBUG_ASSERT(fixed == 1);
   cache->store(args[0]);
   cache->cache_value();
-
+
+  if (args[1]->type() != Item::SUBSELECT_ITEM)
+  {
+    /* MAX/MIN transformed => pass through */
+    longlong res= args[1]->val_int();
+    null_value= args[1]->null_value;
+    return (res);
+  }
+
   if (cache->null_value)
   {
     /*
@@ -2050,24 +2060,35 @@
   if ((*args) != new_item)
     current_thd->change_item_tree(args, new_item);

-  /*
- Transform the right IN operand which should be an Item_in_subselect or a
-    subclass of it. The left operand of the IN must be the same as the left
-    operand of this Item_in_optimizer, so in this case there is no further
-    transformation, we only make both operands the same.
-    TODO: is it the way it should be?
-  */
-  DBUG_ASSERT((args[1])->type() == Item::SUBSELECT_ITEM &&
-              (((Item_subselect*)(args[1]))->substype() ==
-               Item_subselect::IN_SUBS ||
-               ((Item_subselect*)(args[1]))->substype() ==
-               Item_subselect::ALL_SUBS ||
-               ((Item_subselect*)(args[1]))->substype() ==
-               Item_subselect::ANY_SUBS));
-
-  Item_in_subselect *in_arg= (Item_in_subselect*)args[1];
-  in_arg->left_expr= args[0];
-
+  if (args[1]->type() != Item::SUBSELECT_ITEM)
+  {
+    /* MAX/MIN transformed => pass through */
+    new_item= args[1]->transform(transformer, argument);
+    if (!new_item)
+      return 0;
+    if (args[1] != new_item)
+      current_thd->change_item_tree(args, new_item);
+  }
+  else
+  {
+    /*
+ Transform the right IN operand which should be an Item_in_subselect or a + subclass of it. The left operand of the IN must be the same as the left + operand of this Item_in_optimizer, so in this case there is no further
+      transformation, we only make both operands the same.
+      TODO: is it the way it should be?
+    */
+    DBUG_ASSERT((args[1])->type() == Item::SUBSELECT_ITEM &&
+                (((Item_subselect*)(args[1]))->substype() ==
+                 Item_subselect::IN_SUBS ||
+                 ((Item_subselect*)(args[1]))->substype() ==
+                 Item_subselect::ALL_SUBS ||
+                 ((Item_subselect*)(args[1]))->substype() ==
+                 Item_subselect::ANY_SUBS));
+
+    Item_in_subselect *in_arg= (Item_in_subselect*)args[1];
+    in_arg->left_expr= args[0];
+  }
   return (this->*transformer)(argument);
 }


=== modified file 'sql/item_subselect.cc'
--- sql/item_subselect.cc    2011-04-28 14:15:05 +0000
+++ sql/item_subselect.cc    2011-05-10 20:17:04 +0000
@@ -33,12 +33,14 @@


 Item_subselect::Item_subselect():
-  Item_result_field(), value_assigned(0), thd(0), substitution(0),
+  Item_result_field(), value_assigned(0),  thd(0), substitution(0),
   expr_cache(0), engine(0), old_engine(0), used_tables_cache(0),
   have_to_be_excluded(0), const_item_cache(1), inside_first_fix_fields(0),
   done_first_fix_fields(FALSE), forced_const(FALSE), eliminated(FALSE),
   engine_changed(0), changed(0), is_correlated(FALSE)
 {
+  DBUG_ENTER("Item_subselect::Item_subselect");
+  DBUG_PRINT("enter", ("this: 0x%lx", (ulong) this));
   with_subselect= 1;
   reset();
   /*
@@ -46,6 +48,7 @@
     (i.e. some rows will be found returned)
   */
   null_value= TRUE;
+  DBUG_VOID_RETURN;
 }


@@ -58,7 +61,8 @@
   */

   DBUG_ENTER("Item_subselect::init");
-  DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex));
+  DBUG_PRINT("enter", ("select_lex: 0x%lx  this: 0x%lx",
+                       (ulong) select_lex, (ulong) this));
   unit= select_lex->master_unit();
   thd= unit->thd;

@@ -69,10 +73,12 @@
       => we do not copy old_engine here
     */
     engine= unit->item->engine;
+    borrowed_engine= TRUE;
     parsing_place= unit->item->parsing_place;
-    unit->item->engine= 0;
-    unit->item= this;
-    engine->change_result(this, result);
+    //unit->item->engine= 0;
+    thd->change_item_tree((Item**)&unit->item, this);
+    //unit->item= this;
+    engine->change_result(this, result, TRUE);
   }
   else
   {
@@ -97,6 +103,7 @@
     /* The subquery is an expression cache candidate */
     upper->expr_cache_may_be_used[upper->parsing_place]= TRUE;
   }
+  DBUG_PRINT("info", ("engine: 0x%lx", (ulong)engine));
   DBUG_VOID_RETURN;
 }

@@ -156,8 +163,14 @@

 Item_subselect::~Item_subselect()
 {
-  delete engine;
+  DBUG_ENTER("Item_subselect::~Item_subselect");
+  DBUG_PRINT("enter", ("this: 0x%lx", (ulong) this));
+  if (!borrowed_engine)
+    delete engine;
+  else
+    engine->cleanup();
   engine= NULL;
+  DBUG_VOID_RETURN;
 }

 bool
@@ -1119,11 +1132,23 @@

 void Item_exists_subselect::fix_length_and_dec()
 {
-   decimals= 0;
-   max_length= 1;
-   max_columns= engine->cols();
+  DBUG_ENTER("Item_exists_subselect::fix_length_and_dec");
+  decimals= 0;
+  max_length= 1;
+  max_columns= engine->cols();
   /* We need only 1 row to determine existence */
   unit->global_parameters->select_limit= new Item_int((int32) 1);
+  DBUG_PRINT("info", ("Set limit to 1"));
+  DBUG_VOID_RETURN;
+}
+
+void Item_in_subselect::fix_length_and_dec()
+{
+  DBUG_ENTER("Item_in_subselect::fix_length_and_dec");
+  decimals= 0;
+  max_length= 1;
+  max_columns= engine->cols();
+  DBUG_VOID_RETURN;
 }


@@ -1388,88 +1413,6 @@
     DBUG_RETURN(true);
   }

-  /*
-    If this is an ALL/ANY single-value subselect, try to rewrite it with
-    a MIN/MAX subselect. We can do that if a possible NULL result of the
-    subselect can be ignored.
-    E.g. SELECT * FROM t1 WHERE b > ANY (SELECT a FROM t2) can be rewritten
-    with SELECT * FROM t1 WHERE b > (SELECT MAX(a) FROM t2).
-    We can't check that this optimization is safe if it's not a top-level
-    item of the WHERE clause (e.g. because the WHERE clause can contain IS
-    NULL/IS NOT NULL functions). If so, we rewrite ALL/ANY with NOT EXISTS
-    later in this method.
-  */
-  if ((abort_on_null || (upper_item && upper_item->top_level())) &&
-      !select_lex->master_unit()->uncacheable && !func->eqne_op())
-  {
-    if (substitution)
-    {
-      /* It is second (third, ...) SELECT of UNION => All is done */
-      DBUG_RETURN(false);
-    }
-
-    Item *subs;
-    if (!select_lex->group_list.elements &&
-        !select_lex->having &&
-    !select_lex->with_sum_func &&
-    !(select_lex->next_select()) &&
-        select_lex->table_list.elements)
-    {
-      Item_sum_hybrid *item;
-      nesting_map save_allow_sum_func;
-      if (func->l_op())
-      {
-    /*
-      (ALL && (> || =>)) || (ANY && (< || =<))
-      for ALL condition is inverted
-    */
-    item= new Item_sum_max(*select_lex->ref_pointer_array);
-      }
-      else
-      {
-    /*
-      (ALL && (< || =<)) || (ANY && (> || =>))
-      for ALL condition is inverted
-    */
-    item= new Item_sum_min(*select_lex->ref_pointer_array);
-      }
-      if (upper_item)
-        upper_item->set_sum_test(item);
-      *select_lex->ref_pointer_array= item;
-      {
-    List_iterator<Item> it(select_lex->item_list);
-    it++;
-    it.replace(item);
-      }
-
-      save_allow_sum_func= thd->lex->allow_sum_func;
-      thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
-      /*
-    Item_sum_(max|min) can't substitute other item => we can use 0 as
- reference, also Item_sum_(max|min) can't be fixed after creation, so
-        we do not check item->fixed
-      */
-      if (item->fix_fields(thd, 0))
-    DBUG_RETURN(true);
-      thd->lex->allow_sum_func= save_allow_sum_func;
-      /* we added aggregate function => we have to change statistic */
- count_field_types(select_lex, &join->tmp_table_param, join->all_fields,
-                        0);
-
-      subs= new Item_singlerow_subselect(select_lex);
-    }
-    else
-    {
-      Item_maxmin_subselect *item;
- subs= item= new Item_maxmin_subselect(thd, this, select_lex, func->l_op());
-      if (upper_item)
-        upper_item->set_sub_test(item);
-    }
-    /* fix fields is already called for  left expression */
-    substitution= func->create(left_expr, subs);
-    DBUG_RETURN(false);
-  }
-
   Item* join_having= join->having ? join->having : join->tmp_having;
   if (!(join_having || select_lex->with_sum_func ||
         select_lex->group_list.elements) &&
@@ -1503,7 +1446,6 @@
   if (!substitution)
   {
/* We're invoked for the 1st (or the only) SELECT in the subquery UNION */
-    SELECT_LEX_UNIT *master_unit= select_lex->master_unit();
     substitution= optimizer;

     SELECT_LEX *current= thd->lex->current_select;
@@ -1529,16 +1471,102 @@
                   (char *)"<no matter>",
                   (char *)in_left_expr_name);

+  }
+
+  DBUG_RETURN(false);
+}
+
+bool Item_allany_subselect::transform_allany(JOIN *join)
+{
+  DBUG_ENTER("Item_allany_subselect::transform_allany");
+  if (!(in_strategy & SUBS_MAXMIN))
+    DBUG_RETURN(0);
+  Item **place= optimizer->arguments() + 1;
+  THD *thd= join->thd;
+  SELECT_LEX *select_lex= join->select_lex;
+  Item *subs;
+
+  /*
+    If this is an ALL/ANY single-value subselect, try to rewrite it with
+    a MIN/MAX subselect. We can do that if a possible NULL result of the
+    subselect can be ignored.
+    E.g. SELECT * FROM t1 WHERE b > ANY (SELECT a FROM t2) can be rewritten
+    with SELECT * FROM t1 WHERE b > (SELECT MAX(a) FROM t2).
+    We can't check that this optimization is safe if it's not a top-level
+    item of the WHERE clause (e.g. because the WHERE clause can contain IS
+    NULL/IS NOT NULL functions). If so, we rewrite ALL/ANY with NOT EXISTS
+    later in this method.
+  */
+  DBUG_ASSERT(!substitution);
+
+  if (!select_lex->group_list.elements &&
+      !select_lex->having &&
+      !select_lex->with_sum_func &&
+      !(select_lex->next_select()) &&
+      select_lex->table_list.elements)
+  {
+    Item_sum_hybrid *item;
+    nesting_map save_allow_sum_func;
+    if (func->l_op())
+    {
+      /*
+        (ALL && (> || =>)) || (ANY && (< || =<))
+        for ALL condition is inverted
+      */
+      item= new Item_sum_max(*select_lex->ref_pointer_array);
+    }
+    else
+    {
+      /*
+        (ALL && (< || =<)) || (ANY && (> || =>))
+        for ALL condition is inverted
+      */
+      item= new Item_sum_min(*select_lex->ref_pointer_array);
+    }
+    if (upper_item)
+      upper_item->set_sum_test(item);
+    thd->change_item_tree(select_lex->ref_pointer_array, item);
+    {
+      List_iterator<Item> it(select_lex->item_list);
+      it++;
+      thd->change_item_tree(it.ref(), item);
+    }
+
+    save_allow_sum_func= thd->lex->allow_sum_func;
+    thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
     /*
- The uncacheable property controls a number of actions, e.g. whether to - save/restore (via init_save_join_tab/restore_tmp) the original JOIN for
-      plans with a temp table where the original JOIN was overriden by
-      make_simple_join. The UNCACHEABLE_EXPLAIN is ignored by EXPLAIN, thus
-      non-correlated subqueries will not appear as such to EXPLAIN.
+      Item_sum_(max|min) can't substitute other item => we can use 0 as
+      reference, also Item_sum_(max|min) can't be fixed after creation, so
+      we do not check item->fixed
     */
-    master_unit->uncacheable|= UNCACHEABLE_EXPLAIN;
-    select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
-  }
+    if (item->fix_fields(thd, 0))
+      DBUG_RETURN(true);
+    thd->lex->allow_sum_func= save_allow_sum_func;
+    /* we added aggregate function => we have to change statistic */
+ count_field_types(select_lex, &join->tmp_table_param, join->all_fields,
+                      0);
+    if (join->prepare_stage2())
+      DBUG_RETURN(true);
+    subs= new Item_singlerow_subselect(select_lex);
+  }
+  else
+  {
+    Item_maxmin_subselect *item;
+ subs= item= new Item_maxmin_subselect(thd, this, select_lex, func->l_op());
+    if (upper_item)
+      upper_item->set_sub_test(item);
+  }
+  /* fix fields is already called for  left expression */
+  subs= func->create(left_expr, subs);
+  thd->change_item_tree(place, subs);
+  if (subs->fix_fields(thd, &subs))
+    DBUG_RETURN(1);
+  DBUG_ASSERT(subs == (*place)); // There was no substitutions
+
+  select_lex->master_unit()->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+  select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+  /* remove other strategies if there was (just to be safe) */
+  in_strategy= SUBS_MAXMIN;

   DBUG_RETURN(false);
 }
@@ -1556,6 +1584,16 @@
   return fix_res;
 }

+bool Item_allany_subselect::is_maxmin_applicable(JOIN *join)
+{
+  /*
+    Check if max/min optimization applicable: It is top item of
+    WHERE condition.
+  */
+  return (abort_on_null || (upper_item && upper_item->top_level())) &&
+      !join->select_lex->master_unit()->uncacheable && !func->eqne_op();
+}
+

 /**
   Create the predicates needed to transform a single-column IN/ALL/ANY
@@ -2028,7 +2066,7 @@
   /*
The IN=>EXISTS transformation makes non-correlated subqueries correlated.
   */
-  join_arg->select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
+  join_arg->select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
   if (left_expr->cols() == 1)
     res= create_single_in_to_exists_cond(join_arg,
&(join_arg->in_to_exists_where),
@@ -2037,6 +2075,16 @@
     res= create_row_in_to_exists_cond(join_arg,
&(join_arg->in_to_exists_where),
&(join_arg->in_to_exists_having));
+
+  /*
+    The uncacheable property controls a number of actions, e.g. whether to
+    save/restore (via init_save_join_tab/restore_tmp) the original JOIN for
+    plans with a temp table where the original JOIN was overriden by
+    make_simple_join. The UNCACHEABLE_EXPLAIN is ignored by EXPLAIN, thus
+    non-correlated subqueries will not appear as such to EXPLAIN.
+  */
+  join_arg->select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
+  join_arg->select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
   return (res);
 }

@@ -2107,6 +2155,9 @@
     select_lex->having->top_level_item();
     join_arg->having= select_lex->having;
   }
+  join_arg->thd->change_item_tree(&unit->global_parameters->select_limit,
+                                  new Item_int((int32) 1));
+  unit->select_limit_cnt= 1;

   DBUG_RETURN(false);
 }
@@ -2407,7 +2458,8 @@
 Item_allany_subselect::select_transformer(JOIN *join)
 {
   DBUG_ENTER("Item_allany_subselect::select_transformer");
-  in_strategy= SUBS_IN_TO_EXISTS;
+  DBUG_ASSERT((in_strategy & ~(SUBS_MAXMIN | SUBS_IN_TO_EXISTS)) == 0);
+  in_strategy|= SUBS_IN_TO_EXISTS;
   if (upper_item)
     upper_item->show= 1;
   DBUG_RETURN(select_in_like_transformer(join));
@@ -2458,6 +2510,7 @@
   prepared= executed= 0;
   join= 0;
   result->cleanup();
+  select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
   DBUG_VOID_RETURN;
 }

@@ -2467,6 +2520,9 @@
   DBUG_ENTER("subselect_union_engine::cleanup");
   unit->reinit_exec_mechanism();
   result->cleanup();
+  unit->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+  for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+    sl->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
   DBUG_VOID_RETURN;
 }

@@ -3466,11 +3522,16 @@
     TRUE  error
 */

-bool subselect_single_select_engine::change_result(Item_subselect *si,
- select_result_interceptor *res)
+bool
+subselect_single_select_engine::change_result(Item_subselect *si,
+ select_result_interceptor *res,
+                                              bool temp)
 {
   item= si;
-  result= res;
+  if (temp)
+    thd->change_item_tree((Item**) &result, (Item*)res);
+  else
+    result= res;
   return select_lex->join->change_result(result);
 }

@@ -3488,11 +3549,15 @@
 */

 bool subselect_union_engine::change_result(Item_subselect *si,
-                                           select_result_interceptor *res)
+                                           select_result_interceptor *res,
+                                           bool temp)
 {
   item= si;
   int rc= unit->change_result(res, result);
-  result= res;
+  if (temp)
+    thd->change_item_tree((Item**) &result, (Item*)res);
+  else
+    result= res;
   return rc;
 }

@@ -3509,8 +3574,11 @@
     TRUE  error
 */

-bool subselect_uniquesubquery_engine::change_result(Item_subselect *si,
- select_result_interceptor *res)
+bool
+subselect_uniquesubquery_engine::change_result(Item_subselect *si,
+ select_result_interceptor *res,
+                                               bool temp
+                                               __attribute__((unused)))
 {
   DBUG_ASSERT(0);
   return TRUE;
@@ -4301,7 +4369,8 @@
 }

 bool subselect_hash_sj_engine::change_result(Item_subselect *si,
- select_result_interceptor *res) + select_result_interceptor *res, + bool temp __attribute__((unused)))
 {
   DBUG_ASSERT(FALSE);
   return TRUE;

=== modified file 'sql/item_subselect.h'
--- sql/item_subselect.h    2011-03-30 07:10:59 +0000
+++ sql/item_subselect.h    2011-05-10 20:17:04 +0000
@@ -32,7 +32,8 @@

 class Item_subselect :public Item_result_field
 {
-  bool value_assigned;         /* value already assigned to subselect */
+  bool value_assigned;   /* value already assigned to subselect */
+ bool borrowed_engine; /* the engine was taken from other Item_subselect */
 protected:
   /* thread handler, will be assigned in fix_fields only */
   THD *thd;
@@ -356,6 +357,9 @@
 /* Partial matching substrategies of MATERIALIZATION. */
 #define SUBS_PARTIAL_MATCH_ROWID_MERGE 8
 #define SUBS_PARTIAL_MATCH_TABLE_SCAN 16
+/* ALL/ANY will be transformed with max/min optimization */
+#define SUBS_MAXMIN 32
+

 /**
   Representation of IN subquery predicates of the form
@@ -486,6 +490,7 @@
   bool test_limit(st_select_lex_unit *unit);
   virtual void print(String *str, enum_query_type query_type);
   bool fix_fields(THD *thd, Item **ref);
+  void fix_length_and_dec();
   void fix_after_pullout(st_select_lex *new_parent, Item **ref);
   void update_used_tables();
   bool setup_mat_engine();
@@ -523,6 +528,8 @@
   bool select_transformer(JOIN *join);
   void create_comp_func(bool invert) { func= func_creator(invert); }
   virtual void print(String *str, enum_query_type query_type);
+  bool is_maxmin_applicable(JOIN *join);
+  bool transform_allany(JOIN *join);
 };


@@ -594,7 +601,8 @@
   static table_map calc_const_tables(TABLE_LIST *);
   virtual void print(String *str, enum_query_type query_type)= 0;
   virtual bool change_result(Item_subselect *si,
-                             select_result_interceptor *result)= 0;
+                             select_result_interceptor *result,
+                             bool temp= FALSE)= 0;
   virtual bool no_tables()= 0;
   virtual bool is_executed() const { return FALSE; }
   /* Check if subquery produced any rows during last query execution */
@@ -626,7 +634,9 @@
   void exclude();
   table_map upper_select_const_tables();
   virtual void print (String *str, enum_query_type query_type);
- bool change_result(Item_subselect *si, select_result_interceptor *result);
+  bool change_result(Item_subselect *si,
+                     select_result_interceptor *result,
+                     bool temp);
   bool no_tables();
   bool may_be_null();
   bool is_executed() const { return executed; }
@@ -655,7 +665,9 @@
   void exclude();
   table_map upper_select_const_tables();
   virtual void print (String *str, enum_query_type query_type);
- bool change_result(Item_subselect *si, select_result_interceptor *result);
+  bool change_result(Item_subselect *si,
+                     select_result_interceptor *result,
+                     bool temp= FALSE);
   bool no_tables();
   bool is_executed() const;
   bool no_rows();
@@ -707,11 +719,13 @@
   void fix_length_and_dec(Item_cache** row);
   int exec();
   uint cols() { return 1; }
-  uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
+  uint8 uncacheable() { return UNCACHEABLE_DEPENDENT_INJECTED; }
   void exclude();
   table_map upper_select_const_tables() { return 0; }
   virtual void print (String *str, enum_query_type query_type);
- bool change_result(Item_subselect *si, select_result_interceptor *result);
+  bool change_result(Item_subselect *si,
+                     select_result_interceptor *result,
+                     bool temp= FALSE);
   bool no_tables();
   int index_lookup(); /* TIMOUR: this method needs refactoring. */
   int scan_table();
@@ -879,7 +893,9 @@
   void fix_length_and_dec(Item_cache** row);//=>base class
   void exclude(); //=>base class
   //=>base class
- bool change_result(Item_subselect *si, select_result_interceptor *result);
+  bool change_result(Item_subselect *si,
+                     select_result_interceptor *result,
+                     bool temp= FALSE);
   bool no_tables();//=>base class
 };

@@ -1106,7 +1122,9 @@
   uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
   void exclude() {}
   table_map upper_select_const_tables() { return 0; }
-  bool change_result(Item_subselect*, select_result_interceptor*)
+  bool change_result(Item_subselect*,
+                     select_result_interceptor*,
+                     bool temp= FALSE)
   { DBUG_ASSERT(FALSE); return false; }
   bool no_tables() { return false; }
   bool no_rows()

=== modified file 'sql/mysql_priv.h'
--- sql/mysql_priv.h    2011-05-10 15:28:05 +0000
+++ sql/mysql_priv.h    2011-05-10 20:17:04 +0000
@@ -679,14 +679,18 @@
 #define CONTEXT_ANALYSIS_ONLY_DERIVED 4

 // uncachable cause
-#define UNCACHEABLE_DEPENDENT   1
-#define UNCACHEABLE_RAND        2
-#define UNCACHEABLE_SIDEEFFECT    4
+#define UNCACHEABLE_DEPENDENT_GENERATED 1
+#define UNCACHEABLE_RAND                2
+#define UNCACHEABLE_SIDEEFFECT            4
 /// forcing to save JOIN for explain
-#define UNCACHEABLE_EXPLAIN     8
+#define UNCACHEABLE_EXPLAIN             8
 /* For uncorrelated SELECT in an UNION with some correlated SELECTs */
-#define UNCACHEABLE_UNITED     16
-#define UNCACHEABLE_CHECKOPTION 32
+#define UNCACHEABLE_UNITED              16
+#define UNCACHEABLE_CHECKOPTION         32
+#define UNCACHEABLE_DEPENDENT_INJECTED  64
+
+#define UNCACHEABLE_DEPENDENT (UNCACHEABLE_DEPENDENT_GENERATED | \
+                               UNCACHEABLE_DEPENDENT_INJECTED)

 /* Used to check GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */
 #define UNDEF_POS (-1)

=== modified file 'sql/opt_subselect.cc'
--- sql/opt_subselect.cc    2011-05-02 18:59:16 +0000
+++ sql/opt_subselect.cc    2011-05-10 20:17:04 +0000
@@ -93,8 +93,19 @@
(subselect= parent_unit->item)) // (2)
   {
     Item_in_subselect *in_subs= NULL;
-    if (subselect->substype() == Item_subselect::IN_SUBS)
-      in_subs= (Item_in_subselect*)subselect;
+    Item_allany_subselect *allany_subs= NULL;
+    switch (subselect->substype()) {
+    case Item_subselect::IN_SUBS:
+      in_subs= (Item_in_subselect *)subselect;
+      break;
+    case Item_subselect::ALL_SUBS:
+    case Item_subselect::ANY_SUBS:
+      allany_subs= (Item_allany_subselect *)subselect;
+      break;
+    default:
+      break;
+    }
+

     /* Resolve expressions and perform semantic analysis for IN query */
     if (in_subs != NULL)
@@ -257,12 +268,18 @@
         }
       }

+      /* Check if max/min optimization applicable */
+      if (allany_subs)
+ allany_subs->in_strategy|= (allany_subs->is_maxmin_applicable(join) ?
+                                    SUBS_MAXMIN :
+                                    SUBS_IN_TO_EXISTS);
+
       /*
         Transform each subquery predicate according to its overloaded
         transformer.
       */
       if (subselect->select_transformer(join))
-        DBUG_RETURN(-11);
+        DBUG_RETURN(-1);
     }
   }
   DBUG_RETURN(0);
@@ -369,6 +386,21 @@
 }


+/**
+  Apply max min optimization of all/any subselect
+*/
+
+bool convert_max_min_subquery(JOIN *join)
+{
+  DBUG_ENTER("convert_max_min_subquery");
+  Item_subselect *subselect= join->unit->item;
+  if (!subselect || (subselect->substype() != Item_subselect::ALL_SUBS &&
+                     subselect->substype() != Item_subselect::ANY_SUBS))
+    DBUG_RETURN(0);
+ DBUG_RETURN(((Item_allany_subselect *) subselect)->transform_allany(join));
+}
+
+
 /*
   Convert semi-join subquery predicates into semi-join join nests

@@ -3843,8 +3875,8 @@
       restore_query_plan(&save_qep);

/* TODO: should we set/unset this flag for both select_lex and its unit? */
-    in_subs->unit->uncacheable&= ~UNCACHEABLE_DEPENDENT;
-    select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT;
+    in_subs->unit->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+    select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;

     /*
Reset the "LIMIT 1" set in Item_exists_subselect::fix_length_and_dec.
@@ -3884,6 +3916,9 @@

     if (in_subs->inject_in_to_exists_cond(this))
       return TRUE;
+    in_subs->unit->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+    select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+    select_limit= 1;
   }
   else
     DBUG_ASSERT(FALSE);

=== modified file 'sql/opt_subselect.h'
--- sql/opt_subselect.h    2010-12-11 07:23:34 +0000
+++ sql/opt_subselect.h    2011-05-10 20:17:04 +0000
@@ -6,6 +6,7 @@

 int check_and_do_in_subquery_rewrites(JOIN *join);
 bool convert_join_subqueries_to_semijoins(JOIN *join);
+bool convert_max_min_subquery(JOIN *join);
 int pull_out_semijoin_tables(JOIN *join);
 bool optimize_semijoin_nests(JOIN *join, table_map all_table_map);


=== modified file 'sql/sql_class.h'
--- sql/sql_class.h    2011-05-10 15:28:05 +0000
+++ sql/sql_class.h    2011-05-10 20:17:04 +0000
@@ -2727,7 +2727,12 @@
 class select_result_interceptor: public select_result
 {
 public:
-  select_result_interceptor() {}              /* Remove gcc warning */
+  select_result_interceptor()
+  {
+    DBUG_ENTER("select_result_interceptor::select_result_interceptor");
+    DBUG_PRINT("enter", ("this 0x%lx", (ulong) this));
+    DBUG_VOID_RETURN;
+  }              /* Remove gcc warning */
   uint field_count(List<Item> &fields) const { return 0; }
   bool send_fields(List<Item> &fields, uint flag) { return FALSE; }
 };

=== modified file 'sql/sql_lex.cc'
--- sql/sql_lex.cc    2011-05-05 12:24:28 +0000
+++ sql/sql_lex.cc    2011-05-10 20:17:04 +0000
@@ -1942,18 +1942,19 @@
   SELECT_LEX *s= this;
   do
   {
-    if (!(s->uncacheable & UNCACHEABLE_DEPENDENT))
+    if (!(s->uncacheable & UNCACHEABLE_DEPENDENT_GENERATED))
     {
       // Select is dependent of outer select
       s->uncacheable= (s->uncacheable & ~UNCACHEABLE_UNITED) |
-                       UNCACHEABLE_DEPENDENT;
+                       UNCACHEABLE_DEPENDENT_GENERATED;
       SELECT_LEX_UNIT *munit= s->master_unit();
       munit->uncacheable= (munit->uncacheable & ~UNCACHEABLE_UNITED) |
-                       UNCACHEABLE_DEPENDENT;
+                       UNCACHEABLE_DEPENDENT_GENERATED;
for (SELECT_LEX *sl= munit->first_select(); sl ; sl= sl->next_select())
       {
         if (sl != s &&
- !(sl->uncacheable & (UNCACHEABLE_DEPENDENT | UNCACHEABLE_UNITED)))
+            !(sl->uncacheable & (UNCACHEABLE_DEPENDENT_GENERATED |
+                                 UNCACHEABLE_UNITED)))
           sl->uncacheable|= UNCACHEABLE_UNITED;
       }
     }
@@ -2178,17 +2179,6 @@
         subs_type == Item_subselect::IN_SUBS ||
         subs_type == Item_subselect::ALL_SUBS)
     {
-      DBUG_ASSERT(!item->fixed ||
-                  /*
-                    If not using materialization both:
-                    select_limit == 1, and there should be no offset_limit.
-                  */
-                  (((subs_type == Item_subselect::IN_SUBS) &&
-                    ((Item_in_subselect*)item)->in_strategy &
-                    SUBS_MATERIALIZATION) ?
-                   TRUE :
-                   (select_limit->val_int() == 1LL) &&
-                   offset_limit == 0));
       return;
     }
   }

=== modified file 'sql/sql_lex.h'
--- sql/sql_lex.h    2011-05-02 18:59:16 +0000
+++ sql/sql_lex.h    2011-05-10 20:17:04 +0000
@@ -417,7 +417,8 @@

   /*
     result of this query can't be cached, bit field, can be :
-      UNCACHEABLE_DEPENDENT
+      UNCACHEABLE_DEPENDENT_GENERATED
+      UNCACHEABLE_DEPENDENT_INJECTED
       UNCACHEABLE_RAND
       UNCACHEABLE_SIDEEFFECT
       UNCACHEABLE_EXPLAIN

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc    2011-05-05 12:24:28 +0000
+++ sql/sql_select.cc    2011-05-10 20:17:04 +0000
@@ -736,11 +736,28 @@
   if (!procedure && result && result->prepare(fields_list, unit_arg))
     goto err;                    /* purecov: inspected */

+  unit= unit_arg;
+  if (prepare_stage2())
+    goto err;
+
+  DBUG_RETURN(0); // All OK
+
+err:
+  delete procedure;                /* purecov: inspected */
+  procedure= 0;
+  DBUG_RETURN(-1);                /* purecov: inspected */
+}
+
+
+bool JOIN::prepare_stage2()
+{
+  bool res= TRUE;
+  DBUG_ENTER("JOIN::prepare_stage2");
+
   /* Init join struct */
   count_field_types(select_lex, &tmp_table_param, all_fields, 0);
   ref_pointer_array_size= all_fields.elements*sizeof(Item*);
   this->group= group_list != 0;
-  unit= unit_arg;

   if (tmp_table_param.sum_func_count && !group_list)
     implicit_grouping= TRUE;
@@ -757,12 +774,9 @@
   if (alloc_func_list())
     goto err;

-  DBUG_RETURN(0); // All OK
-
+  res= FALSE;
 err:
-  delete procedure;                /* purecov: inspected */
-  procedure= 0;
-  DBUG_RETURN(-1);                /* purecov: inspected */
+  DBUG_RETURN(res);                /* purecov: inspected */
 }


@@ -795,7 +809,8 @@
   set_allowed_join_cache_types();

   /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */
-  if (convert_join_subqueries_to_semijoins(this))
+  if (convert_max_min_subquery(this) ||
+      convert_join_subqueries_to_semijoins(this))
     DBUG_RETURN(1); /* purecov: inspected */
   /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */

@@ -8599,6 +8614,10 @@

 void JOIN_TAB::cleanup()
 {
+  DBUG_ENTER("JOIN_TAB::cleanup");
+  DBUG_PRINT("enter", ("table %s.%s",
+                       (table ? table->s->db.str : "?"),
+                       (table ? table->s->table_name.str : "?")));
   delete select;
   select= 0;
   delete quick;
@@ -8620,6 +8639,7 @@
     table->reginfo.join_tab= 0;
   }
   end_read_record(&read_record);
+  DBUG_VOID_RETURN;
 }


@@ -8740,7 +8760,8 @@
     Optimization: if not EXPLAIN and we are done with the JOIN,
     free all tables.
   */
-  bool full= (!select_lex->uncacheable && !thd->lex->describe);
+  bool full= (!(select_lex->uncacheable) &&
+              !thd->lex->describe);
   bool can_unlock= full;
   DBUG_ENTER("JOIN::join_free");

@@ -8804,6 +8825,7 @@
 void JOIN::cleanup(bool full)
 {
   DBUG_ENTER("JOIN::cleanup");
+  DBUG_PRINT("enter", ("full %u", (uint) full));

   if (table)
   {
@@ -8829,7 +8851,11 @@
       for (tab= join_tab, end= tab+tables; tab != end; tab++)
       {
     if (tab->table)
+        {
+          DBUG_PRINT("info", ("close index: %s.%s", tab->table->s->db.str,
+                              tab->table->s->table_name.str));
           tab->table->file->ha_index_or_rnd_end();
+        }
       }
     }
   }
@@ -20307,6 +20333,7 @@
   change select_result object of JOIN.

   @param res        new select_result object
+  @param temp           temporary assignment

   @retval
     FALSE   OK

=== modified file 'sql/sql_select.h'
--- sql/sql_select.h    2011-05-02 18:59:16 +0000
+++ sql/sql_select.h    2011-05-10 20:17:04 +0000
@@ -999,6 +999,7 @@
           COND *conds, uint og_num, ORDER *order, ORDER *group,
           Item *having, ORDER *proc_param, SELECT_LEX *select,
           SELECT_LEX_UNIT *unit);
+  bool prepare_stage2();
   int optimize();
   int reinit();
   int init_execution();



Follow ups