At file:///home/psergey/dev/maria-5.2-dsmrr/

------------------------------------------------------------
revno: 2738
revision-id: pser...@askmonty.org-20091216092851-9ehou85ydmgziniy
parent: pser...@askmonty.org-20091215223739-f6xymmvanylqlrg9
committer: Sergey Petrunya <pser...@askmonty.org>
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 );


_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to     : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Reply via email to