[Maria-developers] 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();
Hi Sanja, Please find below my review of MWL#148. All my comments are marked with the label 'timour'. I have no comments that require any major changes. There are two more things I'd like to look at a bit more, but I didn't manage to complete today: - is it possible to do without borrowed_engine (or the 'own_engine' I suggested)? For materialization, I had a similar problem where one engine uses an engine it doesn't own, have to check how I handled deletion/cleanup. - analyze in detail Item_allany_subselect::transform_allany. Timour === 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), timour: remove space above 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); timour: Remove the two commented lines below. + //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 */ timour: add the following comment (or better): /* Any EXISTS that is not an IN always requires LIMIT 1. */ 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(); timour: add the following comment (or better): /* Unlike Item_exists_subselect, LIMIT 1 is set later for Item_in_subselect, depending on the chosen strategy. */ + DBUG_VOID_RETURN; } timour: - create a protected method Item_exists_subselect::init_length_and_dec, then use it in both fix_length_and_dec methods below. @@ -1388,88 +1413,6 @@ DBUG_RETURN(true); } timour: Change the comment of the method Item_in_subselect::single_value_transformer(JOIN *join) to reflect its simpler functionality. - /* - 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); +} + timour: Please add a proper comment for this new method. Possibly use the relevant part of the comment for Item_in_subselect::single_value_transformer, and the first big comment inside the method below. Also IMO a better name of the method would be: Item_allany_subselect::transform_max_min (or transform_min_max, just be consistent everywhere). +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; timour: I am definitely agains such "just to be safe" assignments. It is good to determine and set the strategy in one place. From this method is totally unclear why we need to set the MAX_MIN strategy one more time. Potentially, if this method could fail, the caller or this method should revert to some other strategy if possible, but this is not what you do here. + /* 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. */ timour: Below you have added UNCACHEABLE_EXPLAIN *after* we call create_XYZ_in_to_exists_cond(). Is there any meaning that the UNCACHEABLE flags are set in such manner? If not, please set all flags in one place, or even consider removing the ones that are not necessary. - 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 */ timour: The method comment needs to updated with the new parameter. -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; timour: Below this is an ungly trick to reuse the change_item_tree mechanism, right? Please add a comment why we cast a select_result* into an Item*. + 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 */ timour: rename 'borrowed_engine' to 'own_engine', and reverse its values and tests. + 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 @@ } timour: Few things to notice. If you want to keep the below as a separate function, please, please, do not follow Sergey's style of having method-like function. This is a clear method of class JOIN. Sergey agreed to fix his functions to methods after the release is out, but yours is new code, lets do it right from the start. Also, please use consisten naming. Is it "convert" or "transform"? Is it "min_max" or "max_min" ? This will make it much easier to find thing with grep or text search. +/** + 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; timour: It is very unclear why we set these flags in so many places. You already set these flags in intem_subselect.cc, why there is need to do it again? Please either remove these flags, or add a comment in all places, or consolidate them in one place. + 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 timour: Add comment for the two new flags. + 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 */ +} + + timour: Missing method comment. What happens in this method, that makes it necessary to be a separate method? I suppose it is needed so that it can be delayed, but why? This method looks very artificial to me, I cannot figure out if it represents any meaningful unit of work. +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 timour: Good, but this parameter was added to another method, not this one. + @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();
participants (2)
-
Oleksandr Byelkin -
Timour Katchaounov