maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04071
mwl148 for review
=== modified file 'mysql-test/r/explain.result'
--- mysql-test/r/explain.result 2011-05-10 15:28:05 +0000
+++ mysql-test/r/explain.result 2011-05-10 20:17:04 +0000
@@ -176,11 +176,15 @@
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
-ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no
GROUP columns is illegal if there is no GROUP BY clause
+id select_type table type possible_keys key key_len
ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL
NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY t1 system NULL NULL NULL NULL 0
0.00 const row not found
+2 SUBQUERY t system NULL NULL NULL NULL 0
0.00 const row not found
+Warnings:
+Note 1003 select 1 AS `1` from `test`.`t1` where 0
SHOW WARNINGS;
Level Code Message
-Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with
no GROUP columns is illegal if there is no GROUP BY clause
-Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...))
+Note 1003 select 1 AS `1` from `test`.`t1` where 0
SET SESSION sql_mode=@old_sql_mode;
DROP TABLE t1;
End of 5.0 tests.
@@ -272,12 +276,12 @@
EXECUTE stmt;
id select_type table type possible_keys key
key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key
key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
@@ -288,12 +292,12 @@
EXECUTE stmt;
id select_type table type possible_keys key
key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key
key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
=== modified file 'mysql-test/r/subselect.result'
--- mysql-test/r/subselect.result 2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect.result 2011-05-10 20:17:04 +0000
@@ -273,7 +273,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(`test`.`t2`.`b`) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2);
a
7
@@ -1503,7 +1503,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select
max(NULL) from `test`.`t2`))))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
@@ -1511,7 +1511,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(NULL) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2 group by 1);
a
6
@@ -1522,7 +1522,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2
group by 1);
@@ -1530,7 +1530,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1538,7 +1538,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= any (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= any (select b from t2
group by 1);
@@ -1546,7 +1546,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
select * from t3 where NULL >= some (select b from t2);
a
explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1554,7 +1554,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= some (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= some (select b from t2
group by 1);
@@ -1562,7 +1562,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
@@ -1573,7 +1573,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4
100.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from
`test`.`t2` group by `test`.`t2`.`a`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select
max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment,
`taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL
default '0', `create_date` datetime NOT NULL default '0000-00-00
00:00:00', `last_update` datetime NOT NULL default '0000-00-00
00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1
AUTO_INCREMENT=3 ;
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`)
VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1,
21, now(), now());
@@ -1624,7 +1624,7 @@
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL
NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(('f' >
<min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`)))
+Note 1003 select 'e' AS `s1` from `test`.`t1` where
<nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union
select 'e' from `test`.`t1`))))
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM
CHARSET=latin1;
INSERT INTO t1 VALUES
('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- mysql-test/r/subselect_no_mat.result 2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect_no_mat.result 2011-05-10 20:17:04 +0000
@@ -277,7 +277,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(`test`.`t2`.`b`) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2);
a
7
@@ -1507,7 +1507,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select
max(NULL) from `test`.`t2`))))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
@@ -1515,7 +1515,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(NULL) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2 group by 1);
a
6
@@ -1526,7 +1526,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2
group by 1);
@@ -1534,7 +1534,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1542,7 +1542,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= any (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= any (select b from t2
group by 1);
@@ -1550,7 +1550,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
select * from t3 where NULL >= some (select b from t2);
a
explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1558,7 +1558,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= some (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= some (select b from t2
group by 1);
@@ -1566,7 +1566,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
@@ -1577,7 +1577,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4
100.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from
`test`.`t2` group by `test`.`t2`.`a`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select
max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment,
`taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL
default '0', `create_date` datetime NOT NULL default '0000-00-00
00:00:00', `last_update` datetime NOT NULL default '0000-00-00
00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1
AUTO_INCREMENT=3 ;
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`)
VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1,
21, now(), now());
@@ -1628,7 +1628,7 @@
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL
NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(('f' >
<min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`)))
+Note 1003 select 'e' AS `s1` from `test`.`t1` where
<nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union
select 'e' from `test`.`t1`))))
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM
CHARSET=latin1;
INSERT INTO t1 VALUES
('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- mysql-test/r/subselect_no_opts.result 2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect_no_opts.result 2011-05-10 20:17:04 +0000
@@ -274,7 +274,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(`test`.`t2`.`b`) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2);
a
7
@@ -1504,7 +1504,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select
max(NULL) from `test`.`t2`))))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
@@ -1512,7 +1512,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(NULL) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2 group by 1);
a
6
@@ -1523,7 +1523,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2
group by 1);
@@ -1531,7 +1531,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1539,7 +1539,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= any (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= any (select b from t2
group by 1);
@@ -1547,7 +1547,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
select * from t3 where NULL >= some (select b from t2);
a
explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1555,7 +1555,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= some (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= some (select b from t2
group by 1);
@@ -1563,7 +1563,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
@@ -1574,7 +1574,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4
100.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from
`test`.`t2` group by `test`.`t2`.`a`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select
max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment,
`taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL
default '0', `create_date` datetime NOT NULL default '0000-00-00
00:00:00', `last_update` datetime NOT NULL default '0000-00-00
00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1
AUTO_INCREMENT=3 ;
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`)
VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1,
21, now(), now());
@@ -1625,7 +1625,7 @@
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL
NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(('f' >
<min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`)))
+Note 1003 select 'e' AS `s1` from `test`.`t1` where
<nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union
select 'e' from `test`.`t1`))))
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM
CHARSET=latin1;
INSERT INTO t1 VALUES
('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- mysql-test/r/subselect_no_semijoin.result 2011-05-04 22:35:03 +0000
+++ mysql-test/r/subselect_no_semijoin.result 2011-05-10 20:17:04 +0000
@@ -274,7 +274,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(`test`.`t2`.`b`) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2);
a
7
@@ -1504,7 +1504,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select
max(NULL) from `test`.`t2`))))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
@@ -1512,7 +1512,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(NULL) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2 group by 1);
a
6
@@ -1523,7 +1523,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2
group by 1);
@@ -1531,7 +1531,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select
NULL from `test`.`t2` group by 1))))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1539,7 +1539,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= any (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= any (select b from t2
group by 1);
@@ -1547,7 +1547,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
select * from t3 where NULL >= some (select b from t2);
a
explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1555,7 +1555,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(NULL) from `test`.`t2`))))
select * from t3 where NULL >= some (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= some (select b from t2
group by 1);
@@ -1563,7 +1563,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 0
0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select NULL from `test`.`t2`
group by 1))))
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
@@ -1574,7 +1574,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4
100.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from
`test`.`t2` group by `test`.`t2`.`a`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select
max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment,
`taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL
default '0', `create_date` datetime NOT NULL default '0000-00-00
00:00:00', `last_update` datetime NOT NULL default '0000-00-00
00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1
AUTO_INCREMENT=3 ;
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`)
VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1,
21, now(), now());
@@ -1625,7 +1625,7 @@
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL
NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where <nop>(('f' >
<min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`)))
+Note 1003 select 'e' AS `s1` from `test`.`t1` where
<nop>(<in_optimizer>('f',('f' > <min>(select 'e' from `test`.`t1` union
select 'e' from `test`.`t1`))))
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM
CHARSET=latin1;
INSERT INTO t1 VALUES
('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- mysql-test/suite/pbxt/r/subselect.result 2011-04-20 19:55:29 +0000
+++ mysql-test/suite/pbxt/r/subselect.result 2011-05-10 20:17:04 +0000
@@ -270,7 +270,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(`test`.`t2`.`b`) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2);
a
7
@@ -1498,7 +1498,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < (select
max(`test`.`t2`.`b`) from `test`.`t2`))))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
@@ -1506,7 +1506,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= (select
min(`test`.`t2`.`b`) from `test`.`t2`))))
select * from t3 where a >= all (select b from t2 group by 1);
a
6
@@ -1517,7 +1517,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0
0.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` from `test`.`t2`
group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` < <max>(select
`test`.`t2`.`b` from `test`.`t2` group by 1))))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2
group by 1);
@@ -1525,7 +1525,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0
0.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` from `test`.`t2`
group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` >= <min>(select
`test`.`t2`.`b` from `test`.`t2` group by 1))))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1533,7 +1533,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(`test`.`t2`.`b`) from
`test`.`t2`))))
select * from t3 where NULL >= any (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= any (select b from t2
group by 1);
@@ -1541,7 +1541,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0
0.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select `test`.`t2`.`b` from
`test`.`t2` group by 1))))
select * from t3 where NULL >= some (select b from t2);
a
explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1549,7 +1549,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= (select min(`test`.`t2`.`b`) from
`test`.`t2`))))
select * from t3 where NULL >= some (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= some (select b from t2
group by 1);
@@ -1557,7 +1557,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 0
0.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((NULL >= <min>(select `test`.`t2`.`b` from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>(<in_optimizer>(NULL,(NULL >= <min>(select `test`.`t2`.`b` from
`test`.`t2` group by 1))))
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
@@ -1568,7 +1568,7 @@
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4
100.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from
`test`.`t2` group by `test`.`t2`.`a`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>(<in_optimizer>(`test`.`t3`.`a`,(`test`.`t3`.`a` <= <max>(select
max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))))
drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment,
`taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL
default '0', `create_date` datetime NOT NULL default '0000-00-00
00:00:00', `last_update` datetime NOT NULL default '0000-00-00
00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1
AUTO_INCREMENT=3 ;
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`)
VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1,
21, now(), now());
@@ -1619,7 +1619,7 @@
3 UNION t1 ALL NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL
NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where
<nop>(('f' > <min>(select `test`.`t1`.`s1` from `test`.`t1` union select
`test`.`t1`.`s1` from `test`.`t1`)))
+Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where
<nop>(<in_optimizer>('f',('f' > <min>(select `test`.`t1`.`s1` from
`test`.`t1` union select `test`.`t1`.`s1` from `test`.`t1`))))
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM
CHARSET=latin1;
INSERT INTO t1 VALUES
('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/t/explain.test'
--- mysql-test/t/explain.test 2011-05-05 01:08:44 +0000
+++ mysql-test/t/explain.test 2011-05-10 20:17:04 +0000
@@ -158,7 +158,10 @@
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE.
---error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+# Before moving max/min optimization to optimize phase this statement
+# generated error, but as far as original query do not contain aggregate
+# function user should not see error
+# --error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
SHOW WARNINGS;
=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc 2011-05-10 15:28:05 +0000
+++ sql/item_cmpfunc.cc 2011-05-10 20:17:04 +0000
@@ -1798,6 +1798,8 @@
{
THD *thd= (THD*) thd_arg;
DBUG_ENTER("Item_in_optimizer::expr_cache_insert_transformer");
+ if (args[1]->type() != Item::SUBSELECT_ITEM)
+ DBUG_RETURN(this); // MAX/MIN transformed => do nothing
List<Item*> &depends_on= ((Item_subselect *)args[1])->depends_on;
if (expr_cache)
@@ -1901,7 +1903,15 @@
DBUG_ASSERT(fixed == 1);
cache->store(args[0]);
cache->cache_value();
-
+
+ if (args[1]->type() != Item::SUBSELECT_ITEM)
+ {
+ /* MAX/MIN transformed => pass through */
+ longlong res= args[1]->val_int();
+ null_value= args[1]->null_value;
+ return (res);
+ }
+
if (cache->null_value)
{
/*
@@ -2050,24 +2060,35 @@
if ((*args) != new_item)
current_thd->change_item_tree(args, new_item);
- /*
- Transform the right IN operand which should be an Item_in_subselect
or a
- subclass of it. The left operand of the IN must be the same as the left
- operand of this Item_in_optimizer, so in this case there is no further
- transformation, we only make both operands the same.
- TODO: is it the way it should be?
- */
- DBUG_ASSERT((args[1])->type() == Item::SUBSELECT_ITEM &&
- (((Item_subselect*)(args[1]))->substype() ==
- Item_subselect::IN_SUBS ||
- ((Item_subselect*)(args[1]))->substype() ==
- Item_subselect::ALL_SUBS ||
- ((Item_subselect*)(args[1]))->substype() ==
- Item_subselect::ANY_SUBS));
-
- Item_in_subselect *in_arg= (Item_in_subselect*)args[1];
- in_arg->left_expr= args[0];
-
+ if (args[1]->type() != Item::SUBSELECT_ITEM)
+ {
+ /* MAX/MIN transformed => pass through */
+ new_item= args[1]->transform(transformer, argument);
+ if (!new_item)
+ return 0;
+ if (args[1] != new_item)
+ current_thd->change_item_tree(args, new_item);
+ }
+ else
+ {
+ /*
+ Transform the right IN operand which should be an
Item_in_subselect or a
+ subclass of it. The left operand of the IN must be the same as
the left
+ operand of this Item_in_optimizer, so in this case there is no
further
+ transformation, we only make both operands the same.
+ TODO: is it the way it should be?
+ */
+ DBUG_ASSERT((args[1])->type() == Item::SUBSELECT_ITEM &&
+ (((Item_subselect*)(args[1]))->substype() ==
+ Item_subselect::IN_SUBS ||
+ ((Item_subselect*)(args[1]))->substype() ==
+ Item_subselect::ALL_SUBS ||
+ ((Item_subselect*)(args[1]))->substype() ==
+ Item_subselect::ANY_SUBS));
+
+ Item_in_subselect *in_arg= (Item_in_subselect*)args[1];
+ in_arg->left_expr= args[0];
+ }
return (this->*transformer)(argument);
}
=== modified file 'sql/item_subselect.cc'
--- sql/item_subselect.cc 2011-04-28 14:15:05 +0000
+++ sql/item_subselect.cc 2011-05-10 20:17:04 +0000
@@ -33,12 +33,14 @@
Item_subselect::Item_subselect():
- Item_result_field(), value_assigned(0), thd(0), substitution(0),
+ Item_result_field(), value_assigned(0), thd(0), substitution(0),
expr_cache(0), engine(0), old_engine(0), used_tables_cache(0),
have_to_be_excluded(0), const_item_cache(1), inside_first_fix_fields(0),
done_first_fix_fields(FALSE), forced_const(FALSE), eliminated(FALSE),
engine_changed(0), changed(0), is_correlated(FALSE)
{
+ DBUG_ENTER("Item_subselect::Item_subselect");
+ DBUG_PRINT("enter", ("this: 0x%lx", (ulong) this));
with_subselect= 1;
reset();
/*
@@ -46,6 +48,7 @@
(i.e. some rows will be found returned)
*/
null_value= TRUE;
+ DBUG_VOID_RETURN;
}
@@ -58,7 +61,8 @@
*/
DBUG_ENTER("Item_subselect::init");
- DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex));
+ DBUG_PRINT("enter", ("select_lex: 0x%lx this: 0x%lx",
+ (ulong) select_lex, (ulong) this));
unit= select_lex->master_unit();
thd= unit->thd;
@@ -69,10 +73,12 @@
=> we do not copy old_engine here
*/
engine= unit->item->engine;
+ borrowed_engine= TRUE;
parsing_place= unit->item->parsing_place;
- unit->item->engine= 0;
- unit->item= this;
- engine->change_result(this, result);
+ //unit->item->engine= 0;
+ thd->change_item_tree((Item**)&unit->item, this);
+ //unit->item= this;
+ engine->change_result(this, result, TRUE);
}
else
{
@@ -97,6 +103,7 @@
/* The subquery is an expression cache candidate */
upper->expr_cache_may_be_used[upper->parsing_place]= TRUE;
}
+ DBUG_PRINT("info", ("engine: 0x%lx", (ulong)engine));
DBUG_VOID_RETURN;
}
@@ -156,8 +163,14 @@
Item_subselect::~Item_subselect()
{
- delete engine;
+ DBUG_ENTER("Item_subselect::~Item_subselect");
+ DBUG_PRINT("enter", ("this: 0x%lx", (ulong) this));
+ if (!borrowed_engine)
+ delete engine;
+ else
+ engine->cleanup();
engine= NULL;
+ DBUG_VOID_RETURN;
}
bool
@@ -1119,11 +1132,23 @@
void Item_exists_subselect::fix_length_and_dec()
{
- decimals= 0;
- max_length= 1;
- max_columns= engine->cols();
+ DBUG_ENTER("Item_exists_subselect::fix_length_and_dec");
+ decimals= 0;
+ max_length= 1;
+ max_columns= engine->cols();
/* We need only 1 row to determine existence */
unit->global_parameters->select_limit= new Item_int((int32) 1);
+ DBUG_PRINT("info", ("Set limit to 1"));
+ DBUG_VOID_RETURN;
+}
+
+void Item_in_subselect::fix_length_and_dec()
+{
+ DBUG_ENTER("Item_in_subselect::fix_length_and_dec");
+ decimals= 0;
+ max_length= 1;
+ max_columns= engine->cols();
+ DBUG_VOID_RETURN;
}
@@ -1388,88 +1413,6 @@
DBUG_RETURN(true);
}
- /*
- If this is an ALL/ANY single-value subselect, try to rewrite it with
- a MIN/MAX subselect. We can do that if a possible NULL result of the
- subselect can be ignored.
- E.g. SELECT * FROM t1 WHERE b > ANY (SELECT a FROM t2) can be rewritten
- with SELECT * FROM t1 WHERE b > (SELECT MAX(a) FROM t2).
- We can't check that this optimization is safe if it's not a top-level
- item of the WHERE clause (e.g. because the WHERE clause can contain IS
- NULL/IS NOT NULL functions). If so, we rewrite ALL/ANY with NOT EXISTS
- later in this method.
- */
- if ((abort_on_null || (upper_item && upper_item->top_level())) &&
- !select_lex->master_unit()->uncacheable && !func->eqne_op())
- {
- if (substitution)
- {
- /* It is second (third, ...) SELECT of UNION => All is done */
- DBUG_RETURN(false);
- }
-
- Item *subs;
- if (!select_lex->group_list.elements &&
- !select_lex->having &&
- !select_lex->with_sum_func &&
- !(select_lex->next_select()) &&
- select_lex->table_list.elements)
- {
- Item_sum_hybrid *item;
- nesting_map save_allow_sum_func;
- if (func->l_op())
- {
- /*
- (ALL && (> || =>)) || (ANY && (< || =<))
- for ALL condition is inverted
- */
- item= new Item_sum_max(*select_lex->ref_pointer_array);
- }
- else
- {
- /*
- (ALL && (< || =<)) || (ANY && (> || =>))
- for ALL condition is inverted
- */
- item= new Item_sum_min(*select_lex->ref_pointer_array);
- }
- if (upper_item)
- upper_item->set_sum_test(item);
- *select_lex->ref_pointer_array= item;
- {
- List_iterator<Item> it(select_lex->item_list);
- it++;
- it.replace(item);
- }
-
- save_allow_sum_func= thd->lex->allow_sum_func;
- thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
- /*
- Item_sum_(max|min) can't substitute other item => we can use 0 as
- reference, also Item_sum_(max|min) can't be fixed after
creation, so
- we do not check item->fixed
- */
- if (item->fix_fields(thd, 0))
- DBUG_RETURN(true);
- thd->lex->allow_sum_func= save_allow_sum_func;
- /* we added aggregate function => we have to change statistic */
- count_field_types(select_lex, &join->tmp_table_param,
join->all_fields,
- 0);
-
- subs= new Item_singlerow_subselect(select_lex);
- }
- else
- {
- Item_maxmin_subselect *item;
- subs= item= new Item_maxmin_subselect(thd, this, select_lex,
func->l_op());
- if (upper_item)
- upper_item->set_sub_test(item);
- }
- /* fix fields is already called for left expression */
- substitution= func->create(left_expr, subs);
- DBUG_RETURN(false);
- }
-
Item* join_having= join->having ? join->having : join->tmp_having;
if (!(join_having || select_lex->with_sum_func ||
select_lex->group_list.elements) &&
@@ -1503,7 +1446,6 @@
if (!substitution)
{
/* We're invoked for the 1st (or the only) SELECT in the subquery
UNION */
- SELECT_LEX_UNIT *master_unit= select_lex->master_unit();
substitution= optimizer;
SELECT_LEX *current= thd->lex->current_select;
@@ -1529,16 +1471,102 @@
(char *)"<no matter>",
(char *)in_left_expr_name);
+ }
+
+ DBUG_RETURN(false);
+}
+
+bool Item_allany_subselect::transform_allany(JOIN *join)
+{
+ DBUG_ENTER("Item_allany_subselect::transform_allany");
+ if (!(in_strategy & SUBS_MAXMIN))
+ DBUG_RETURN(0);
+ Item **place= optimizer->arguments() + 1;
+ THD *thd= join->thd;
+ SELECT_LEX *select_lex= join->select_lex;
+ Item *subs;
+
+ /*
+ If this is an ALL/ANY single-value subselect, try to rewrite it with
+ a MIN/MAX subselect. We can do that if a possible NULL result of the
+ subselect can be ignored.
+ E.g. SELECT * FROM t1 WHERE b > ANY (SELECT a FROM t2) can be rewritten
+ with SELECT * FROM t1 WHERE b > (SELECT MAX(a) FROM t2).
+ We can't check that this optimization is safe if it's not a top-level
+ item of the WHERE clause (e.g. because the WHERE clause can contain IS
+ NULL/IS NOT NULL functions). If so, we rewrite ALL/ANY with NOT EXISTS
+ later in this method.
+ */
+ DBUG_ASSERT(!substitution);
+
+ if (!select_lex->group_list.elements &&
+ !select_lex->having &&
+ !select_lex->with_sum_func &&
+ !(select_lex->next_select()) &&
+ select_lex->table_list.elements)
+ {
+ Item_sum_hybrid *item;
+ nesting_map save_allow_sum_func;
+ if (func->l_op())
+ {
+ /*
+ (ALL && (> || =>)) || (ANY && (< || =<))
+ for ALL condition is inverted
+ */
+ item= new Item_sum_max(*select_lex->ref_pointer_array);
+ }
+ else
+ {
+ /*
+ (ALL && (< || =<)) || (ANY && (> || =>))
+ for ALL condition is inverted
+ */
+ item= new Item_sum_min(*select_lex->ref_pointer_array);
+ }
+ if (upper_item)
+ upper_item->set_sum_test(item);
+ thd->change_item_tree(select_lex->ref_pointer_array, item);
+ {
+ List_iterator<Item> it(select_lex->item_list);
+ it++;
+ thd->change_item_tree(it.ref(), item);
+ }
+
+ save_allow_sum_func= thd->lex->allow_sum_func;
+ thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
/*
- The uncacheable property controls a number of actions, e.g.
whether to
- save/restore (via init_save_join_tab/restore_tmp) the original
JOIN for
- plans with a temp table where the original JOIN was overriden by
- make_simple_join. The UNCACHEABLE_EXPLAIN is ignored by EXPLAIN, thus
- non-correlated subqueries will not appear as such to EXPLAIN.
+ Item_sum_(max|min) can't substitute other item => we can use 0 as
+ reference, also Item_sum_(max|min) can't be fixed after creation, so
+ we do not check item->fixed
*/
- master_unit->uncacheable|= UNCACHEABLE_EXPLAIN;
- select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
- }
+ if (item->fix_fields(thd, 0))
+ DBUG_RETURN(true);
+ thd->lex->allow_sum_func= save_allow_sum_func;
+ /* we added aggregate function => we have to change statistic */
+ count_field_types(select_lex, &join->tmp_table_param,
join->all_fields,
+ 0);
+ if (join->prepare_stage2())
+ DBUG_RETURN(true);
+ subs= new Item_singlerow_subselect(select_lex);
+ }
+ else
+ {
+ Item_maxmin_subselect *item;
+ subs= item= new Item_maxmin_subselect(thd, this, select_lex,
func->l_op());
+ if (upper_item)
+ upper_item->set_sub_test(item);
+ }
+ /* fix fields is already called for left expression */
+ subs= func->create(left_expr, subs);
+ thd->change_item_tree(place, subs);
+ if (subs->fix_fields(thd, &subs))
+ DBUG_RETURN(1);
+ DBUG_ASSERT(subs == (*place)); // There was no substitutions
+
+ select_lex->master_unit()->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+ select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+ /* remove other strategies if there was (just to be safe) */
+ in_strategy= SUBS_MAXMIN;
DBUG_RETURN(false);
}
@@ -1556,6 +1584,16 @@
return fix_res;
}
+bool Item_allany_subselect::is_maxmin_applicable(JOIN *join)
+{
+ /*
+ Check if max/min optimization applicable: It is top item of
+ WHERE condition.
+ */
+ return (abort_on_null || (upper_item && upper_item->top_level())) &&
+ !join->select_lex->master_unit()->uncacheable && !func->eqne_op();
+}
+
/**
Create the predicates needed to transform a single-column IN/ALL/ANY
@@ -2028,7 +2066,7 @@
/*
The IN=>EXISTS transformation makes non-correlated subqueries
correlated.
*/
- join_arg->select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
+ join_arg->select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
if (left_expr->cols() == 1)
res= create_single_in_to_exists_cond(join_arg,
&(join_arg->in_to_exists_where),
@@ -2037,6 +2075,16 @@
res= create_row_in_to_exists_cond(join_arg,
&(join_arg->in_to_exists_where),
&(join_arg->in_to_exists_having));
+
+ /*
+ The uncacheable property controls a number of actions, e.g. whether to
+ save/restore (via init_save_join_tab/restore_tmp) the original JOIN for
+ plans with a temp table where the original JOIN was overriden by
+ make_simple_join. The UNCACHEABLE_EXPLAIN is ignored by EXPLAIN, thus
+ non-correlated subqueries will not appear as such to EXPLAIN.
+ */
+ join_arg->select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
+ join_arg->select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
return (res);
}
@@ -2107,6 +2155,9 @@
select_lex->having->top_level_item();
join_arg->having= select_lex->having;
}
+ join_arg->thd->change_item_tree(&unit->global_parameters->select_limit,
+ new Item_int((int32) 1));
+ unit->select_limit_cnt= 1;
DBUG_RETURN(false);
}
@@ -2407,7 +2458,8 @@
Item_allany_subselect::select_transformer(JOIN *join)
{
DBUG_ENTER("Item_allany_subselect::select_transformer");
- in_strategy= SUBS_IN_TO_EXISTS;
+ DBUG_ASSERT((in_strategy & ~(SUBS_MAXMIN | SUBS_IN_TO_EXISTS)) == 0);
+ in_strategy|= SUBS_IN_TO_EXISTS;
if (upper_item)
upper_item->show= 1;
DBUG_RETURN(select_in_like_transformer(join));
@@ -2458,6 +2510,7 @@
prepared= executed= 0;
join= 0;
result->cleanup();
+ select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
DBUG_VOID_RETURN;
}
@@ -2467,6 +2520,9 @@
DBUG_ENTER("subselect_union_engine::cleanup");
unit->reinit_exec_mechanism();
result->cleanup();
+ unit->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+ for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+ sl->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
DBUG_VOID_RETURN;
}
@@ -3466,11 +3522,16 @@
TRUE error
*/
-bool subselect_single_select_engine::change_result(Item_subselect *si,
-
select_result_interceptor *res)
+bool
+subselect_single_select_engine::change_result(Item_subselect *si,
+ select_result_interceptor
*res,
+ bool temp)
{
item= si;
- result= res;
+ if (temp)
+ thd->change_item_tree((Item**) &result, (Item*)res);
+ else
+ result= res;
return select_lex->join->change_result(result);
}
@@ -3488,11 +3549,15 @@
*/
bool subselect_union_engine::change_result(Item_subselect *si,
- select_result_interceptor *res)
+ select_result_interceptor *res,
+ bool temp)
{
item= si;
int rc= unit->change_result(res, result);
- result= res;
+ if (temp)
+ thd->change_item_tree((Item**) &result, (Item*)res);
+ else
+ result= res;
return rc;
}
@@ -3509,8 +3574,11 @@
TRUE error
*/
-bool subselect_uniquesubquery_engine::change_result(Item_subselect *si,
-
select_result_interceptor *res)
+bool
+subselect_uniquesubquery_engine::change_result(Item_subselect *si,
+
select_result_interceptor *res,
+ bool temp
+ __attribute__((unused)))
{
DBUG_ASSERT(0);
return TRUE;
@@ -4301,7 +4369,8 @@
}
bool subselect_hash_sj_engine::change_result(Item_subselect *si,
- select_result_interceptor
*res)
+ select_result_interceptor
*res,
+ bool temp
__attribute__((unused)))
{
DBUG_ASSERT(FALSE);
return TRUE;
=== modified file 'sql/item_subselect.h'
--- sql/item_subselect.h 2011-03-30 07:10:59 +0000
+++ sql/item_subselect.h 2011-05-10 20:17:04 +0000
@@ -32,7 +32,8 @@
class Item_subselect :public Item_result_field
{
- bool value_assigned; /* value already assigned to subselect */
+ bool value_assigned; /* value already assigned to subselect */
+ bool borrowed_engine; /* the engine was taken from other
Item_subselect */
protected:
/* thread handler, will be assigned in fix_fields only */
THD *thd;
@@ -356,6 +357,9 @@
/* Partial matching substrategies of MATERIALIZATION. */
#define SUBS_PARTIAL_MATCH_ROWID_MERGE 8
#define SUBS_PARTIAL_MATCH_TABLE_SCAN 16
+/* ALL/ANY will be transformed with max/min optimization */
+#define SUBS_MAXMIN 32
+
/**
Representation of IN subquery predicates of the form
@@ -486,6 +490,7 @@
bool test_limit(st_select_lex_unit *unit);
virtual void print(String *str, enum_query_type query_type);
bool fix_fields(THD *thd, Item **ref);
+ void fix_length_and_dec();
void fix_after_pullout(st_select_lex *new_parent, Item **ref);
void update_used_tables();
bool setup_mat_engine();
@@ -523,6 +528,8 @@
bool select_transformer(JOIN *join);
void create_comp_func(bool invert) { func= func_creator(invert); }
virtual void print(String *str, enum_query_type query_type);
+ bool is_maxmin_applicable(JOIN *join);
+ bool transform_allany(JOIN *join);
};
@@ -594,7 +601,8 @@
static table_map calc_const_tables(TABLE_LIST *);
virtual void print(String *str, enum_query_type query_type)= 0;
virtual bool change_result(Item_subselect *si,
- select_result_interceptor *result)= 0;
+ select_result_interceptor *result,
+ bool temp= FALSE)= 0;
virtual bool no_tables()= 0;
virtual bool is_executed() const { return FALSE; }
/* Check if subquery produced any rows during last query execution */
@@ -626,7 +634,9 @@
void exclude();
table_map upper_select_const_tables();
virtual void print (String *str, enum_query_type query_type);
- bool change_result(Item_subselect *si, select_result_interceptor
*result);
+ bool change_result(Item_subselect *si,
+ select_result_interceptor *result,
+ bool temp);
bool no_tables();
bool may_be_null();
bool is_executed() const { return executed; }
@@ -655,7 +665,9 @@
void exclude();
table_map upper_select_const_tables();
virtual void print (String *str, enum_query_type query_type);
- bool change_result(Item_subselect *si, select_result_interceptor
*result);
+ bool change_result(Item_subselect *si,
+ select_result_interceptor *result,
+ bool temp= FALSE);
bool no_tables();
bool is_executed() const;
bool no_rows();
@@ -707,11 +719,13 @@
void fix_length_and_dec(Item_cache** row);
int exec();
uint cols() { return 1; }
- uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
+ uint8 uncacheable() { return UNCACHEABLE_DEPENDENT_INJECTED; }
void exclude();
table_map upper_select_const_tables() { return 0; }
virtual void print (String *str, enum_query_type query_type);
- bool change_result(Item_subselect *si, select_result_interceptor
*result);
+ bool change_result(Item_subselect *si,
+ select_result_interceptor *result,
+ bool temp= FALSE);
bool no_tables();
int index_lookup(); /* TIMOUR: this method needs refactoring. */
int scan_table();
@@ -879,7 +893,9 @@
void fix_length_and_dec(Item_cache** row);//=>base class
void exclude(); //=>base class
//=>base class
- bool change_result(Item_subselect *si, select_result_interceptor
*result);
+ bool change_result(Item_subselect *si,
+ select_result_interceptor *result,
+ bool temp= FALSE);
bool no_tables();//=>base class
};
@@ -1106,7 +1122,9 @@
uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
void exclude() {}
table_map upper_select_const_tables() { return 0; }
- bool change_result(Item_subselect*, select_result_interceptor*)
+ bool change_result(Item_subselect*,
+ select_result_interceptor*,
+ bool temp= FALSE)
{ DBUG_ASSERT(FALSE); return false; }
bool no_tables() { return false; }
bool no_rows()
=== modified file 'sql/mysql_priv.h'
--- sql/mysql_priv.h 2011-05-10 15:28:05 +0000
+++ sql/mysql_priv.h 2011-05-10 20:17:04 +0000
@@ -679,14 +679,18 @@
#define CONTEXT_ANALYSIS_ONLY_DERIVED 4
// uncachable cause
-#define UNCACHEABLE_DEPENDENT 1
-#define UNCACHEABLE_RAND 2
-#define UNCACHEABLE_SIDEEFFECT 4
+#define UNCACHEABLE_DEPENDENT_GENERATED 1
+#define UNCACHEABLE_RAND 2
+#define UNCACHEABLE_SIDEEFFECT 4
/// forcing to save JOIN for explain
-#define UNCACHEABLE_EXPLAIN 8
+#define UNCACHEABLE_EXPLAIN 8
/* For uncorrelated SELECT in an UNION with some correlated SELECTs */
-#define UNCACHEABLE_UNITED 16
-#define UNCACHEABLE_CHECKOPTION 32
+#define UNCACHEABLE_UNITED 16
+#define UNCACHEABLE_CHECKOPTION 32
+#define UNCACHEABLE_DEPENDENT_INJECTED 64
+
+#define UNCACHEABLE_DEPENDENT (UNCACHEABLE_DEPENDENT_GENERATED | \
+ UNCACHEABLE_DEPENDENT_INJECTED)
/* Used to check GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */
#define UNDEF_POS (-1)
=== modified file 'sql/opt_subselect.cc'
--- sql/opt_subselect.cc 2011-05-02 18:59:16 +0000
+++ sql/opt_subselect.cc 2011-05-10 20:17:04 +0000
@@ -93,8 +93,19 @@
(subselect=
parent_unit->item)) // (2)
{
Item_in_subselect *in_subs= NULL;
- if (subselect->substype() == Item_subselect::IN_SUBS)
- in_subs= (Item_in_subselect*)subselect;
+ Item_allany_subselect *allany_subs= NULL;
+ switch (subselect->substype()) {
+ case Item_subselect::IN_SUBS:
+ in_subs= (Item_in_subselect *)subselect;
+ break;
+ case Item_subselect::ALL_SUBS:
+ case Item_subselect::ANY_SUBS:
+ allany_subs= (Item_allany_subselect *)subselect;
+ break;
+ default:
+ break;
+ }
+
/* Resolve expressions and perform semantic analysis for IN query */
if (in_subs != NULL)
@@ -257,12 +268,18 @@
}
}
+ /* Check if max/min optimization applicable */
+ if (allany_subs)
+ allany_subs->in_strategy|=
(allany_subs->is_maxmin_applicable(join) ?
+ SUBS_MAXMIN :
+ SUBS_IN_TO_EXISTS);
+
/*
Transform each subquery predicate according to its overloaded
transformer.
*/
if (subselect->select_transformer(join))
- DBUG_RETURN(-11);
+ DBUG_RETURN(-1);
}
}
DBUG_RETURN(0);
@@ -369,6 +386,21 @@
}
+/**
+ Apply max min optimization of all/any subselect
+*/
+
+bool convert_max_min_subquery(JOIN *join)
+{
+ DBUG_ENTER("convert_max_min_subquery");
+ Item_subselect *subselect= join->unit->item;
+ if (!subselect || (subselect->substype() != Item_subselect::ALL_SUBS &&
+ subselect->substype() != Item_subselect::ANY_SUBS))
+ DBUG_RETURN(0);
+ DBUG_RETURN(((Item_allany_subselect *)
subselect)->transform_allany(join));
+}
+
+
/*
Convert semi-join subquery predicates into semi-join join nests
@@ -3843,8 +3875,8 @@
restore_query_plan(&save_qep);
/* TODO: should we set/unset this flag for both select_lex and its
unit? */
- in_subs->unit->uncacheable&= ~UNCACHEABLE_DEPENDENT;
- select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT;
+ in_subs->unit->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
+ select_lex->uncacheable&= ~UNCACHEABLE_DEPENDENT_INJECTED;
/*
Reset the "LIMIT 1" set in
Item_exists_subselect::fix_length_and_dec.
@@ -3884,6 +3916,9 @@
if (in_subs->inject_in_to_exists_cond(this))
return TRUE;
+ in_subs->unit->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+ select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+ select_limit= 1;
}
else
DBUG_ASSERT(FALSE);
=== modified file 'sql/opt_subselect.h'
--- sql/opt_subselect.h 2010-12-11 07:23:34 +0000
+++ sql/opt_subselect.h 2011-05-10 20:17:04 +0000
@@ -6,6 +6,7 @@
int check_and_do_in_subquery_rewrites(JOIN *join);
bool convert_join_subqueries_to_semijoins(JOIN *join);
+bool convert_max_min_subquery(JOIN *join);
int pull_out_semijoin_tables(JOIN *join);
bool optimize_semijoin_nests(JOIN *join, table_map all_table_map);
=== modified file 'sql/sql_class.h'
--- sql/sql_class.h 2011-05-10 15:28:05 +0000
+++ sql/sql_class.h 2011-05-10 20:17:04 +0000
@@ -2727,7 +2727,12 @@
class select_result_interceptor: public select_result
{
public:
- select_result_interceptor() {} /* Remove gcc warning */
+ select_result_interceptor()
+ {
+ DBUG_ENTER("select_result_interceptor::select_result_interceptor");
+ DBUG_PRINT("enter", ("this 0x%lx", (ulong) this));
+ DBUG_VOID_RETURN;
+ } /* Remove gcc warning */
uint field_count(List<Item> &fields) const { return 0; }
bool send_fields(List<Item> &fields, uint flag) { return FALSE; }
};
=== modified file 'sql/sql_lex.cc'
--- sql/sql_lex.cc 2011-05-05 12:24:28 +0000
+++ sql/sql_lex.cc 2011-05-10 20:17:04 +0000
@@ -1942,18 +1942,19 @@
SELECT_LEX *s= this;
do
{
- if (!(s->uncacheable & UNCACHEABLE_DEPENDENT))
+ if (!(s->uncacheable & UNCACHEABLE_DEPENDENT_GENERATED))
{
// Select is dependent of outer select
s->uncacheable= (s->uncacheable & ~UNCACHEABLE_UNITED) |
- UNCACHEABLE_DEPENDENT;
+ UNCACHEABLE_DEPENDENT_GENERATED;
SELECT_LEX_UNIT *munit= s->master_unit();
munit->uncacheable= (munit->uncacheable & ~UNCACHEABLE_UNITED) |
- UNCACHEABLE_DEPENDENT;
+ UNCACHEABLE_DEPENDENT_GENERATED;
for (SELECT_LEX *sl= munit->first_select(); sl ; sl=
sl->next_select())
{
if (sl != s &&
- !(sl->uncacheable & (UNCACHEABLE_DEPENDENT |
UNCACHEABLE_UNITED)))
+ !(sl->uncacheable & (UNCACHEABLE_DEPENDENT_GENERATED |
+ UNCACHEABLE_UNITED)))
sl->uncacheable|= UNCACHEABLE_UNITED;
}
}
@@ -2178,17 +2179,6 @@
subs_type == Item_subselect::IN_SUBS ||
subs_type == Item_subselect::ALL_SUBS)
{
- DBUG_ASSERT(!item->fixed ||
- /*
- If not using materialization both:
- select_limit == 1, and there should be no offset_limit.
- */
- (((subs_type == Item_subselect::IN_SUBS) &&
- ((Item_in_subselect*)item)->in_strategy &
- SUBS_MATERIALIZATION) ?
- TRUE :
- (select_limit->val_int() == 1LL) &&
- offset_limit == 0));
return;
}
}
=== modified file 'sql/sql_lex.h'
--- sql/sql_lex.h 2011-05-02 18:59:16 +0000
+++ sql/sql_lex.h 2011-05-10 20:17:04 +0000
@@ -417,7 +417,8 @@
/*
result of this query can't be cached, bit field, can be :
- UNCACHEABLE_DEPENDENT
+ UNCACHEABLE_DEPENDENT_GENERATED
+ UNCACHEABLE_DEPENDENT_INJECTED
UNCACHEABLE_RAND
UNCACHEABLE_SIDEEFFECT
UNCACHEABLE_EXPLAIN
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2011-05-05 12:24:28 +0000
+++ sql/sql_select.cc 2011-05-10 20:17:04 +0000
@@ -736,11 +736,28 @@
if (!procedure && result && result->prepare(fields_list, unit_arg))
goto err; /* purecov: inspected */
+ unit= unit_arg;
+ if (prepare_stage2())
+ goto err;
+
+ DBUG_RETURN(0); // All OK
+
+err:
+ delete procedure; /* purecov: inspected */
+ procedure= 0;
+ DBUG_RETURN(-1); /* purecov: inspected */
+}
+
+
+bool JOIN::prepare_stage2()
+{
+ bool res= TRUE;
+ DBUG_ENTER("JOIN::prepare_stage2");
+
/* Init join struct */
count_field_types(select_lex, &tmp_table_param, all_fields, 0);
ref_pointer_array_size= all_fields.elements*sizeof(Item*);
this->group= group_list != 0;
- unit= unit_arg;
if (tmp_table_param.sum_func_count && !group_list)
implicit_grouping= TRUE;
@@ -757,12 +774,9 @@
if (alloc_func_list())
goto err;
- DBUG_RETURN(0); // All OK
-
+ res= FALSE;
err:
- delete procedure; /* purecov: inspected */
- procedure= 0;
- DBUG_RETURN(-1); /* purecov: inspected */
+ DBUG_RETURN(res); /* purecov: inspected */
}
@@ -795,7 +809,8 @@
set_allowed_join_cache_types();
/* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */
- if (convert_join_subqueries_to_semijoins(this))
+ if (convert_max_min_subquery(this) ||
+ convert_join_subqueries_to_semijoins(this))
DBUG_RETURN(1); /* purecov: inspected */
/* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */
@@ -8599,6 +8614,10 @@
void JOIN_TAB::cleanup()
{
+ DBUG_ENTER("JOIN_TAB::cleanup");
+ DBUG_PRINT("enter", ("table %s.%s",
+ (table ? table->s->db.str : "?"),
+ (table ? table->s->table_name.str : "?")));
delete select;
select= 0;
delete quick;
@@ -8620,6 +8639,7 @@
table->reginfo.join_tab= 0;
}
end_read_record(&read_record);
+ DBUG_VOID_RETURN;
}
@@ -8740,7 +8760,8 @@
Optimization: if not EXPLAIN and we are done with the JOIN,
free all tables.
*/
- bool full= (!select_lex->uncacheable && !thd->lex->describe);
+ bool full= (!(select_lex->uncacheable) &&
+ !thd->lex->describe);
bool can_unlock= full;
DBUG_ENTER("JOIN::join_free");
@@ -8804,6 +8825,7 @@
void JOIN::cleanup(bool full)
{
DBUG_ENTER("JOIN::cleanup");
+ DBUG_PRINT("enter", ("full %u", (uint) full));
if (table)
{
@@ -8829,7 +8851,11 @@
for (tab= join_tab, end= tab+tables; tab != end; tab++)
{
if (tab->table)
+ {
+ DBUG_PRINT("info", ("close index: %s.%s", tab->table->s->db.str,
+ tab->table->s->table_name.str));
tab->table->file->ha_index_or_rnd_end();
+ }
}
}
}
@@ -20307,6 +20333,7 @@
change select_result object of JOIN.
@param res new select_result object
+ @param temp temporary assignment
@retval
FALSE OK
=== modified file 'sql/sql_select.h'
--- sql/sql_select.h 2011-05-02 18:59:16 +0000
+++ sql/sql_select.h 2011-05-10 20:17:04 +0000
@@ -999,6 +999,7 @@
COND *conds, uint og_num, ORDER *order, ORDER *group,
Item *having, ORDER *proc_param, SELECT_LEX *select,
SELECT_LEX_UNIT *unit);
+ bool prepare_stage2();
int optimize();
int reinit();
int init_execution();
Follow ups
