=== 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();