maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #01741
Rev 2738: DS-MRR backport: in file:///home/psergey/dev/maria-5.2-dsmrr/
At file:///home/psergey/dev/maria-5.2-dsmrr/
------------------------------------------------------------
revno: 2738
revision-id: psergey@xxxxxxxxxxxx-20091216092851-9ehou85ydmgziniy
parent: psergey@xxxxxxxxxxxx-20091215223739-f6xymmvanylqlrg9
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
branch nick: maria-5.2-dsmrr
timestamp: Wed 2009-12-16 12:28:51 +0300
message:
DS-MRR backport:
- Fix PBXT test results (PBXT doesn't support MRR or ICP, but we get result
diffs because we've also backported a fix that
- prints out "Using where" when the table has part of WHERE that it has
got from LEFT JOIN's ON expression
- Does a better job at removing equalities that are guaranteed to be true
by use of ref acccess.
=== modified file 'mysql-test/suite/pbxt/r/join_outer.result'
--- a/mysql-test/suite/pbxt/r/join_outer.result 2009-04-02 10:03:14 +0000
+++ b/mysql-test/suite/pbxt/r/join_outer.result 2009-12-16 09:28:51 +0000
@@ -630,7 +630,7 @@
explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index
-1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using index
+1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using where; Using index
select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
fooID barID fooID
10 1 NULL
@@ -688,8 +688,8 @@
explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t3 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
drop table t1, t2, t3;
create table t1 (
a int(11),
@@ -859,14 +859,14 @@
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
a1 a2 a3
1 NULL NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
a0 a1 a2 a3
@@ -875,7 +875,7 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 index PRIMARY PRIMARY 4 NULL 1 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a0 1 Using index
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES (0);
=== modified file 'mysql-test/suite/pbxt/r/order_by.result'
--- a/mysql-test/suite/pbxt/r/order_by.result 2009-04-02 10:03:14 +0000
+++ b/mysql-test/suite/pbxt/r/order_by.result 2009-12-16 09:28:51 +0000
@@ -514,7 +514,7 @@
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition
drop table t1,t2,t3;
CREATE TABLE t1 (
`titre` char(80) NOT NULL default '',
@@ -638,7 +638,7 @@
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 2 Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 2 Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -647,7 +647,7 @@
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 2 Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 2 Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
@@ -897,7 +897,7 @@
ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
-1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
SELECT t2.b as c FROM
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
ORDER BY c;
=== modified file 'mysql-test/suite/pbxt/r/partition_pruning.result'
--- a/mysql-test/suite/pbxt/r/partition_pruning.result 2009-08-17 15:57:58 +0000
+++ b/mysql-test/suite/pbxt/r/partition_pruning.result 2009-12-16 09:28:51 +0000
@@ -533,15 +533,15 @@
test.t2 analyze status OK
explain partitions select * from t2 where b = 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 5 Using where
+1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 5
explain extended select * from t2 where b = 6;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ref b b 5 const 5 100.00 Using where
+1 SIMPLE t2 ref b b 5 const 5 100.00
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6)
explain partitions select * from t2 where b = 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 5 Using where
+1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 5
explain extended select * from t2 where b in (1,3,5);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 range b b 5 NULL 15 100.00 Using where
=== modified file 'mysql-test/suite/pbxt/r/ps_1general.result'
--- a/mysql-test/suite/pbxt/r/ps_1general.result 2009-08-31 11:07:44 +0000
+++ b/mysql-test/suite/pbxt/r/ps_1general.result 2009-12-16 09:28:51 +0000
@@ -465,9 +465,9 @@
def key_len 253 4096 1 Y 0 31 8
def ref 253 2048 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 253 255 27 N 1 31 8
+def Extra 253 255 48 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using MRR; Using filesort
drop table if exists t2;
create table t2 (id smallint, name varchar(20)) ;
prepare stmt1 from ' insert into t2 values(?, ?) ' ;
=== modified file 'mysql-test/suite/pbxt/r/range.result'
--- a/mysql-test/suite/pbxt/r/range.result 2009-08-17 15:57:58 +0000
+++ b/mysql-test/suite/pbxt/r/range.result 2009-12-16 09:28:51 +0000
@@ -221,31 +221,31 @@
update t1 set y=x;
explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 1 Using where; Using join buffer
explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 1 Using where; Using join buffer
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 1 Using where; Using join buffer
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 1 Using where; Using join buffer
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 1 Using where; Using join buffer
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 1 Using where; Using join buffer
explain select count(*) from t1 where x in (1);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref x x 5 const 1 Using where; Using index
+1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
@@ -277,7 +277,7 @@
(33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a,b a 5 NULL 2 Using where
+1 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where; Using MRR
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
a b
DROP TABLE t1;
@@ -920,7 +920,7 @@
('A2','2005-12-01 08:00:00',1000);
EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where
+1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using index condition
Warnings:
Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1
=== modified file 'mysql-test/suite/pbxt/r/select.result'
--- a/mysql-test/suite/pbxt/r/select.result 2009-10-07 16:56:11 +0000
+++ b/mysql-test/suite/pbxt/r/select.result 2009-12-16 09:28:51 +0000
@@ -1397,15 +1397,15 @@
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 12
@@ -1421,15 +1421,15 @@
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
companynr companynr
37 36
@@ -2368,16 +2368,16 @@
insert into t2 values (1,3), (2,3), (3,4), (4,4);
explain select * from t1 left join t2 on a=c where d in (4);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref c,d d 5 const 1 Using where
-1 SIMPLE t1 ref a a 5 test.t2.c 1 Using where
+1 SIMPLE t2 ref c,d d 5 const 1
+1 SIMPLE t1 ref a a 5 test.t2.c 1
select * from t1 left join t2 on a=c where d in (4);
a b c d
3 2 3 4
4 2 4 4
explain select * from t1 left join t2 on a=c where d = 4;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref c,d d 5 const 1 Using where
-1 SIMPLE t1 ref a a 5 test.t2.c 1 Using where
+1 SIMPLE t2 ref c,d d 5 const 1
+1 SIMPLE t1 ref a a 5 test.t2.c 1
select * from t1 left join t2 on a=c where d = 4;
a b c d
3 2 3 4
@@ -2403,11 +2403,11 @@
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
-1 SIMPLE t2 ref a a 23 test.t1.a 1
+1 SIMPLE t2 ref a a 23 test.t1.a 1 Using where
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
-1 SIMPLE t2 ref a a 23 test.t1.a 1
+1 SIMPLE t2 ref a a 23 test.t1.a 1 Using where
DROP TABLE t1, t2;
CREATE TABLE t1 ( city char(30) );
INSERT INTO t1 VALUES ('London');
@@ -2722,7 +2722,7 @@
where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and
t2.b like '%%' order by t2.b limit 0,1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
@@ -3468,12 +3468,12 @@
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,b b 5 NULL 1 Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where
+1 SIMPLE t2 ref c c 5 test.t1.a 1
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,b PRIMARY 4 NULL 1 Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where
+1 SIMPLE t2 ref c c 5 test.t1.a 1
DROP TABLE t1, t2;
create table t1 (
a int unsigned not null auto_increment primary key,
@@ -3533,7 +3533,7 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 const idx1 NULL NULL NULL 1
-1 SIMPLE t3 ref idx1 idx1 5 const 1 Using where
+1 SIMPLE t3 ref idx1 idx1 5 const 1
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
WHERE t1.id=2;
id a b c d e
=== modified file 'mysql-test/suite/pbxt/r/select_safe.result'
--- a/mysql-test/suite/pbxt/r/select_safe.result 2009-08-17 15:57:58 +0000
+++ b/mysql-test/suite/pbxt/r/select_safe.result 2009-12-16 09:28:51 +0000
@@ -70,12 +70,12 @@
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL b NULL NULL NULL 21
-1 SIMPLE t2 ref b b 21 test.t1.b 1 Using where
+1 SIMPLE t2 ref b b 21 test.t1.b 1
set MAX_SEEKS_FOR_KEY=1;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL b NULL NULL NULL 21
-1 SIMPLE t2 ref b b 21 test.t1.b 1 Using where
+1 SIMPLE t2 ref b b 21 test.t1.b 1
SET MAX_SEEKS_FOR_KEY=DEFAULT;
drop table t1;
create table t1 (a int);
=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- a/mysql-test/suite/pbxt/r/subselect.result 2009-10-16 12:45:42 +0000
+++ b/mysql-test/suite/pbxt/r/subselect.result 2009-12-16 09:28:51 +0000
@@ -719,7 +719,7 @@
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -731,7 +731,7 @@
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
@@ -903,7 +903,7 @@
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
@@ -1333,9 +1333,9 @@
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; Using where
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
@@ -1353,8 +1353,8 @@
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 where; Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.b 1 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
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`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`))))
insert into t1 values (3,31);
=== modified file 'mysql-test/suite/pbxt/r/union.result'
--- a/mysql-test/suite/pbxt/r/union.result 2009-10-07 16:56:11 +0000
+++ b/mysql-test/suite/pbxt/r/union.result 2009-12-16 09:28:51 +0000
@@ -505,7 +505,7 @@
explain (select * from t1 where a=1) union (select * from t1 where b=1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1
-2 UNION t1 ref b b 5 const 1 Using where
+2 UNION t1 ref b b 5 const 1
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
drop table t1,t2;
create table t1 ( id int not null auto_increment, primary key (id) ,user_name text );
