summaryrefslogtreecommitdiff
path: root/mysql-test/r/range.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/range.result')
-rw-r--r--mysql-test/r/range.result70
1 files changed, 35 insertions, 35 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 1c6845ef07e..a6b48403029 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -221,27 +221,27 @@ 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
-1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join)
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
-1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join)
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
-1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using join buffer (flat, BNL join)
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
-1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using join buffer (flat, BNL join)
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
-1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join)
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
-1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join)
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 index
@@ -276,7 +276,7 @@ INSERT INTO t1 VALUES
(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
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
a b
DROP TABLE t1;
@@ -421,19 +421,19 @@ test.t1 analyze status OK
test.t2 analyze status Table is already up to date
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
id name uid id name uid
@@ -615,13 +615,13 @@ INSERT INTO t1 (a) VALUES
('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
explain select * from t1 where a='aaa';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 11 const 2 Using where
+1 SIMPLE t1 ref a a 11 const 2 Using index condition
explain select * from t1 where a=binary 'aaa';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 11 NULL 2 Using where
+1 SIMPLE t1 range a a 11 NULL 2 Using index condition
explain select * from t1 where a='aaa' collate latin1_bin;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 11 NULL 2 Using where
+1 SIMPLE t1 range a a 11 NULL 2 Using index condition
explain select * from t1 where a='aaa' collate latin1_german1_ci;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL a NULL NULL NULL 9 Using where
@@ -716,7 +716,7 @@ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
s.oxleft > v.oxleft AND s.oxleft < v.oxright;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where
+1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition
1 SIMPLE s ALL OXLEFT NULL NULL NULL 12 Range checked for each record (index map: 0x4)
SELECT s.oxid FROM t1 v, t1 s
WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
@@ -890,10 +890,10 @@ INSERT INTO t1 VALUES
(55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C');
EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 11 Using where
+1 SIMPLE t1 range status status 23 NULL 11 Using index condition
EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 11 Using where
+1 SIMPLE t1 range status status 23 NULL 11 Using index condition
SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
id status
53 C
@@ -922,10 +922,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 11 Using where; Using index
EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using where
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using where
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1026,20 +1026,20 @@ create table t2 (a varchar(10), filler char(200), key(a));
insert into t2 select * from t1;
explain select * from t1 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition
select a, hex(filler) from t1 where a not between 'b' and 'b';
a hex(filler)
a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
@@ -1083,7 +1083,7 @@ id b c
0 3 4
EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where
+1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using index condition; Using where
EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where
@@ -1115,7 +1115,7 @@ INSERT INTO t1 VALUES
('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
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
item started price
Warnings:
@@ -1153,7 +1153,7 @@ INSERT INTO t1 VALUES
This must use range access:
explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range dateval dateval 4 NULL 2 Using where
+1 SIMPLE t1 range dateval dateval 4 NULL 2 Using index condition
drop table t1;
CREATE TABLE t1 (
a varchar(32), index (a)
@@ -1219,7 +1219,7 @@ Z
In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
explain select * from t2 where a=1000 and b<11;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 5 const 502 Using where
+1 SIMPLE t2 ref a a 5 const 502 Using index condition
drop table t1, t2;
CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
CREATE TABLE t2( a INT, b INT, KEY( a, b ) );
@@ -1690,7 +1690,7 @@ pk i4
EXPLAIN
SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using where
+1 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using index condition
SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
pk i4
1 10
@@ -1699,7 +1699,7 @@ pk i4
EXPLAIN
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where
+1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using index condition
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
pk i4
1 10
@@ -1733,7 +1733,7 @@ pk i4
EXPLAIN
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using where
+1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using index condition
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
pk i4
1 10
@@ -1748,7 +1748,7 @@ pk i4
EXPLAIN
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where
+1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using index condition
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
pk i4
1 10
@@ -1757,14 +1757,14 @@ EXPLAIN
SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using index condition
SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
pk i4 pk i4
EXPLAIN
SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using index condition
SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
pk i4 pk i4
DROP TABLE t1;
@@ -1793,7 +1793,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using where
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
View Lorry Controller Status