At file:///home/psergey/dev/maria-5.3-subqueries-r7/
------------------------------------------------------------
revno: 2768
revision-id: psergey@askmonty.org-20100223092202-sc3huivw9yymnj5d
parent: psergey@askmonty.org-20100221075312-fc08qgn72dnbudd5
committer: Sergey Petrunya
branch nick: maria-5.3-subqueries-r7
timestamp: Tue 2010-02-23 11:22:02 +0200
message:
Subquery backport: update pbxt suite test results (checked).
=== modified file 'mysql-test/suite/pbxt/r/group_min_max.result'
--- a/mysql-test/suite/pbxt/r/group_min_max.result 2009-08-17 15:57:58 +0000
+++ b/mysql-test/suite/pbxt/r/group_min_max.result 2010-02-23 09:22:02 +0000
@@ -2257,7 +2257,7 @@
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index
+2 SUBQUERY t1 index NULL a 10 NULL 15 Using index
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- a/mysql-test/suite/pbxt/r/subselect.result 2009-12-16 09:28:51 +0000
+++ b/mysql-test/suite/pbxt/r/subselect.result 2010-02-23 09:22:02 +0000
@@ -1293,31 +1293,31 @@
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,<exists>((<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,<exists>((<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1332,31 +1332,31 @@
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,<exists>((<cache>(`test`.`t2`.`a`) in t1 on a)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,<exists>((<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ref a a 5 func 1 100.00 Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.b 1 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index
+1 PRIMARY t3 ref a a 5 test.t1.b 1 100.00 Using index; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1369,10 +1369,10 @@
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a`,<exists>((<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
drop table t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
@@ -2823,10 +2823,10 @@
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond((`test`.`t2`.`one`)) and trigcond((`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where ((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
@@ -3412,7 +3412,7 @@
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
a b
@@ -3423,7 +3423,7 @@
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
@@ -4213,8 +4213,8 @@
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where
+1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
+1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using where
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
@@ -4223,15 +4223,15 @@
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 1 Using index; Using where
+1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
+1 PRIMARY t2 ref I2 I2 13 test.t2.a 1 Using where
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where
+1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
+1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using where
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
DROP TABLE t1,t2;
Sergey Petrunya