Below is the list of changes that have just been committed into a local
5.1 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-01-25 10:00:09+03:00, sergefp@stripped +3 -0
BUG#24127: merge: update tests results
mysql-test/r/ndb_subquery.result@stripped, 2007-01-25 10:00:05+03:00, sergefp@stripped +2 -2
BUG#24127: merge: update tests results
mysql-test/r/subselect.result@stripped, 2007-01-25 10:00:05+03:00, sergefp@stripped +15 -15
BUG#24127: merge: update tests results
mysql-test/r/subselect3.result@stripped, 2007-01-25 10:00:05+03:00, sergefp@stripped +27 -26
BUG#24127: merge: update tests results
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: sergefp
# Host: pylon.mylan
# Root: /home/psergey/mysql-5.1-bug8804-r12-merge
--- 1.162/mysql-test/r/subselect.result 2007-01-25 10:00:15 +03:00
+++ 1.163/mysql-test/r/subselect.result 2007-01-25 10:00:15 +03:00
@@ -1303,9 +1303,9 @@
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 Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where
+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
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1460,27 +1460,27 @@
a2 1
a3 1
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
--- 1.3/mysql-test/r/subselect3.result 2007-01-25 10:00:15 +03:00
+++ 1.4/mysql-test/r/subselect3.result 2007-01-25 10:00:15 +03:00
@@ -87,9 +87,9 @@
4 NULL 0
explain extended
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 4
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
@@ -151,9 +151,10 @@
select a, oref,
t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
from t3;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
+2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
@@ -178,10 +179,10 @@
select a, oref,
t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
from t3;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
+2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
@@ -203,9 +204,9 @@
explain extended
select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
@@ -235,9 +236,9 @@
create table t1 (a int, b int, c int, key(a,b));
insert into t1 select 2*A, 2*A, 100 from t3;
explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
@@ -251,10 +252,10 @@
select a,b, oref,
(a,b) in (select a,b from t1,t4 where c=t2.oref) Z
from t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
-2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -297,9 +298,9 @@
new1 10 10 NULL
explain extended
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where
-2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 8 100.00 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
@@ -567,9 +568,9 @@
dd 1 NULL
explain extended
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00
+2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
--- 1.6/mysql-test/r/ndb_subquery.result 2007-01-25 10:00:15 +03:00
+++ 1.7/mysql-test/r/ndb_subquery.result 2007-01-25 10:00:15 +03:00
@@ -20,7 +20,7 @@
explain select * from t2 where p NOT IN (select u from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where
-2 DEPENDENT SUBQUERY t1 unique_subquery u u 4 func # Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery u u 4 func #
select * from t2 where p NOT IN (select u from t1) order by p;
p u o
4 4 4
@@ -28,7 +28,7 @@
explain select * from t2 where p NOT IN (select o from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where
-2 DEPENDENT SUBQUERY t1 index_subquery o o 4 func # Using index
+2 DEPENDENT SUBQUERY t1 index_subquery o o 4 func #
select * from t2 where p NOT IN (select o from t1) order by p;
p u o
4 4 4
| Thread |
|---|
| • bk commit into 5.1 tree (sergefp:1.2408) BUG#24127 | Sergey Petrunia | 25 Jan |
