summaryrefslogtreecommitdiff
path: root/mysql-test/r/select.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/select.result')
-rw-r--r--mysql-test/r/select.result109
1 files changed, 54 insertions, 55 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 96979d257f1..a893cb03a42 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -185,37 +185,37 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
fld1 fld3
-218401 faithful
+012001 flanking
+013602 foldout
+013606 fingerings
018007 fanatic
-228311 fated
018017 featherweight
-218022 feed
-088303 feminine
-058004 Fenton
-038017 fetched
018054 fetters
-208101 fiftieth
-238007 filial
-013606 fingerings
-218008 finishers
-038205 firearm
-188505 fitting
-202301 Fitzpatrick
-238008 fixedly
-012001 flanking
018103 flint
018104 flopping
+036002 funereal
+038017 fetched
+038205 firearm
+058004 Fenton
+088303 feminine
+186002 freakish
188007 flurried
-013602 foldout
+188505 fitting
+198006 furthermore
+202301 Fitzpatrick
+208101 fiftieth
+208113 freest
+218008 finishers
+218022 feed
+218401 faithful
226205 foothill
-232102 forgivably
+226209 furnishings
228306 forthcoming
-186002 freakish
-208113 freest
+228311 fated
231315 freezes
-036002 funereal
-226209 furnishings
-198006 furthermore
+232102 forgivably
+238007 filial
+238008 fixedly
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
fld3
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
@@ -1391,15 +1391,15 @@ id select_type table type possible_keys key key_len ref rows Extra
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
@@ -1415,15 +1415,15 @@ id select_type table type possible_keys key key_len ref rows Extra
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
@@ -2362,7 +2362,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2);
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 2 Using where
+1 SIMPLE t2 ref c,d d 5 const 2
1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer
select * from t1 left join t2 on a=c where d in (4);
a b c d
@@ -2370,7 +2370,7 @@ a b c d
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 2 Using where
+1 SIMPLE t2 ref c,d d 5 const 2
1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer
select * from t1 left join t2 on a=c where d = 4;
a b c d
@@ -2397,11 +2397,11 @@ INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
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 2
+1 SIMPLE t2 ref a a 23 test.t1.a 2 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 2
+1 SIMPLE t2 ref a a 23 test.t1.a 2 Using where
DROP TABLE t1, t2;
CREATE TABLE t1 ( city char(30) );
INSERT INTO t1 VALUES ('London');
@@ -2717,7 +2717,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
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;
@@ -3418,7 +3418,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
@@ -3456,7 +3456,7 @@ In next EXPLAIN, B.rows must be exactly 10:
explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using where
+1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Using MRR
1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10
drop table t1, t2;
CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
@@ -3470,13 +3470,13 @@ INSERT INTO t2 VALUES
EXPLAIN
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 3 Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where
+1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using MRR
+1 SIMPLE t2 ref c c 5 test.t1.a 2
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 b 5 NULL 3 Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where
+1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Using MRR
+1 SIMPLE t2 ref c c 5 test.t1.a 2
DROP TABLE t1, t2;
create table t1 (
a int unsigned not null auto_increment primary key,
@@ -3536,7 +3536,7 @@ WHERE t1.id=2;
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 3 Using where
+1 SIMPLE t3 ref idx1 idx1 5 const 3
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
@@ -3565,19 +3565,19 @@ EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
DROP TABLE t1,t2;
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
@@ -3588,7 +3588,6 @@ INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
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 b b 22 const 1 Using index
DROP TABLE t1,t2;
CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
@@ -3612,7 +3611,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
t3.a=t2.a AND t3.c IN ('bb','ee');
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 range si si 5 NULL 4 Using where
+1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
EXPLAIN
SELECT t3.a FROM t1,t2,t3
@@ -3620,7 +3619,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
t3.a=t2.a AND t3.c IN ('bb','ee') ;
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 range si,ai si 5 NULL 4 Using where
+1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
EXPLAIN
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
@@ -3628,7 +3627,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
t3.c IN ('bb','ee');
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 range si si 5 NULL 2 Using where
+1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
EXPLAIN
SELECT t3.a FROM t1,t2,t3
@@ -3636,7 +3635,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
t3.c IN ('bb','ee');
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 range si,ai si 5 NULL 2 Using where
+1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
@@ -3756,7 +3755,7 @@ AND t1.ts BETWEEN t2.dt1 AND t2.dt2
AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range ts ts 4 NULL 1 Using where
+1 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; Using where; Using MRR
Warnings:
Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
@@ -3858,7 +3857,7 @@ cc 3 7
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
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 name name 6 test.t1.name 2
+1 SIMPLE t2 ref name name 6 test.t1.name 2 Using where
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
name name n
ccc NULL NULL
@@ -3932,7 +3931,7 @@ cc 3 7
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
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 name name 6 test.t1.name 2
+1 SIMPLE t2 ref name name 6 test.t1.name 2 Using where
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
name name n
ccc NULL NULL
@@ -4381,12 +4380,12 @@ CREATE TABLE t1 (a INT KEY, b INT);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
DROP TABLE t1;
@@ -4401,7 +4400,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4411,7 +4410,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
View Lorry Controller Status