summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/mrr_icp_extra.result25
-rw-r--r--mysql-test/suite/maria/r/maria.result8
-rw-r--r--mysql-test/suite/pbxt/r/null_key.result2
-rw-r--r--mysql-test/suite/pbxt/r/order_by.result2
-rw-r--r--mysql-test/suite/pbxt/r/ps_1general.result4
-rw-r--r--mysql-test/suite/pbxt/r/range.result4
-rw-r--r--mysql-test/suite/vcol/r/vcol_misc.result4
-rw-r--r--mysql-test/suite/vcol/r/vcol_select_innodb.result14
-rw-r--r--mysql-test/suite/vcol/r/vcol_select_myisam.result18
-rw-r--r--mysql-test/t/mrr_icp_extra.test14
10 files changed, 38 insertions, 57 deletions
diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result
index 5b0a0eccdac..3014ec948f8 100644
--- a/mysql-test/r/mrr_icp_extra.result
+++ b/mysql-test/r/mrr_icp_extra.result
@@ -811,21 +811,6 @@ Percentage float(3,1) NOT NULL default '0.0',
PRIMARY KEY (Country, Language),
INDEX (Percentage)
);
-explain
-SELECT * FROM City
-WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
-(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City index_merge Population,Country Country,Population 3,4 NULL 419 Using sort_union(Country,Population); Using where
-SELECT * FROM City
-WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
-(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
-ID Name Country Population
-65 Abu Dhabi ARE 398695
-168 Pabna BGD 103277
-189 Parakou BEN 103577
-750 Paarl ZAF 105768
-2865 Pak Pattan PAK 107800
SELECT * FROM City
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
AND (Population > 101000 AND Population < 102000);
@@ -839,13 +824,13 @@ SELECT * FROM City
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
AND (Population > 101000 AND Population < 102000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Country Population 4 NULL 42 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City range Population,Country Population 4 NULL # Using index condition; Using where; Rowid-ordered scan
explain
SELECT * FROM City
WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City index_merge Population,Country Country,Population 3,4 NULL 419 Using sort_union(Country,Population); Using where
+1 SIMPLE City index_merge Population,Country Country,Population 3,4 NULL # Using sort_union(Country,Population); Using where
SELECT * FROM City
WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
@@ -864,12 +849,12 @@ explain
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 32 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City range Population Population 4 NULL # Using index condition; Using where; Rowid-ordered scan
explain
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Country Population 4 NULL 520 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City range Population,Country # # NULL # Using index condition; Using where; Rowid-ordered scan
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
ID Name Country Population
@@ -889,7 +874,7 @@ explain
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Country Population 4 NULL 520 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City range Population,Country # # NULL # Using index condition; Using where; Rowid-ordered scan
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
ID Name Country Population
diff --git a/mysql-test/suite/maria/r/maria.result b/mysql-test/suite/maria/r/maria.result
index 63b354952ca..077cf58f41f 100644
--- a/mysql-test/suite/maria/r/maria.result
+++ b/mysql-test/suite/maria/r/maria.result
@@ -400,10 +400,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 4 test.t2.a 3
explain select * from t1 where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Rowid-ordered scan
+1 SIMPLE t1 range a a 4 NULL 4 Using where
explain select * from t1 force index (a) where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Rowid-ordered scan
+1 SIMPLE t1 range a a 4 NULL 4 Using where
explain select * from t1 where c=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c,c_2 c 5 const 1
@@ -1142,7 +1142,7 @@ qq
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition
+1 SIMPLE t1 ref v,v_2 # 13 const # Using where
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
@@ -1318,7 +1318,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v v 303 const # Using index condition
+1 SIMPLE t1 ref v v 303 const # Using where
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
diff --git a/mysql-test/suite/pbxt/r/null_key.result b/mysql-test/suite/pbxt/r/null_key.result
index f7b1a496e80..7c9a278b581 100644
--- a/mysql-test/suite/pbxt/r/null_key.result
+++ b/mysql-test/suite/pbxt/r/null_key.result
@@ -258,7 +258,7 @@ INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4
INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
explain select id from t1 where uniq_id is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 5 Using index condition
+1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where
explain select id from t1 where uniq_id =1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const idx1 idx1 5 const 1
diff --git a/mysql-test/suite/pbxt/r/order_by.result b/mysql-test/suite/pbxt/r/order_by.result
index 4f56f433169..4fc7abec96c 100644
--- a/mysql-test/suite/pbxt/r/order_by.result
+++ b/mysql-test/suite/pbxt/r/order_by.result
@@ -514,7 +514,7 @@ id select_type table type possible_keys key key_len ref rows Extra
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 index condition
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where
drop table t1,t2,t3;
CREATE TABLE t1 (
`titre` char(80) NOT NULL default '',
diff --git a/mysql-test/suite/pbxt/r/ps_1general.result b/mysql-test/suite/pbxt/r/ps_1general.result
index d8a04d48acf..baa944eebab 100644
--- a/mysql-test/suite/pbxt/r/ps_1general.result
+++ b/mysql-test/suite/pbxt/r/ps_1general.result
@@ -465,9 +465,9 @@ def key 253 64 7 Y 0 31 8
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 57 N 1 31 8
+def Extra 253 255 27 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 index condition; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort
drop table if exists t2;
create table t2 (id smallint, name varchar(20)) ;
prepare stmt1 from ' insert into t2 values(?, ?) ' ;
diff --git a/mysql-test/suite/pbxt/r/range.result b/mysql-test/suite/pbxt/r/range.result
index 7d2d17bdc1e..368fd6b946c 100644
--- a/mysql-test/suite/pbxt/r/range.result
+++ b/mysql-test/suite/pbxt/r/range.result
@@ -277,7 +277,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 index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a,b a 5 NULL 2 Using where
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
a b
DROP TABLE t1;
@@ -922,7 +922,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 index condition
+1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
item started price
Warnings:
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index de4d4dcbb93..58bd048ec85 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -144,12 +144,12 @@ explain
select * from t1,t2 where t1.b=t2.c and d <= 100;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
-1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where
select * from t1,t2 where t1.b=t2.c and d <= 100;
a b c d v
+3 30 30 100 101
4 20 20 100 101
1 20 20 100 101
-3 30 30 100 101
set join_cache_level=default;
drop table t1, t2;
create table t1 (a bigint, b bigint as (a > '2'));
diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result
index 89a79b1acf8..7f7b1e40269 100644
--- a/mysql-test/suite/vcol/r/vcol_select_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result
@@ -44,7 +44,7 @@ a b c
1 -1 -1
explain select * from t3 where c>=-1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Rowid-ordered scan
+1 SIMPLE t3 range c c 5 NULL 1 Using where
# select_type=SIMPLE, type=ref
select * from t1,t3 where t1.c=t3.c and t3.c=-1;
a b c a b c
@@ -136,11 +136,11 @@ id select_type table type possible_keys key key_len ref rows Extra
# SELECT * FROM tbl_name WHERE <vcol expr>
select * from t3 where c >= -2;
a b c
-1 -1 -1
2 -2 -2
+1 -1 -1
explain select * from t3 where c >= -2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Rowid-ordered scan
+1 SIMPLE t3 range c c 5 NULL 1 Using where
# SELECT * FROM tbl_name WHERE <non-vcol expr>
select * from t3 where a between 1 and 2;
a b c
@@ -160,11 +160,11 @@ id select_type table type possible_keys key key_len ref rows Extra
# SELECT * FROM tbl_name WHERE <indexed vcol expr>
select * from t3 where c between -2 and -1;
a b c
-1 -1 -1
2 -2 -2
+1 -1 -1
explain select * from t3 where c between -2 and -1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Rowid-ordered scan
+1 SIMPLE t3 range c c 5 NULL 1 Using where
# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol>
select * from t3 where a between 1 and 2 order by b;
a b c
@@ -204,7 +204,7 @@ a b c
1 -1 -1
explain select * from t3 where c between -2 and -1 order by b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Rowid-ordered scan; Using filesort
+1 SIMPLE t3 range c c 5 NULL 1 Using where; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
select * from t3 where b between -2 and -1 order by c;
a b c
@@ -220,7 +220,7 @@ a b c
1 -1 -1
explain select * from t3 where c between -2 and -1 order by c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition
+1 SIMPLE t3 range c c 5 NULL 1 Using where
# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
select sum(b) from t1 group by b;
sum(b)
diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result
index bec56f841dc..cc327a818b4 100644
--- a/mysql-test/suite/vcol/r/vcol_select_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result
@@ -44,7 +44,7 @@ a b c
1 -1 -1
explain select * from t3 where c>=-1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Rowid-ordered scan
+1 SIMPLE t3 range c c 5 NULL 2 Using where
# select_type=SIMPLE, type=ref
select * from t1,t3 where t1.c=t3.c and t3.c=-1;
a b c a b c
@@ -140,15 +140,15 @@ a b c
1 -1 -1
explain select * from t3 where c >= -2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Rowid-ordered scan
+1 SIMPLE t3 range c c 5 NULL 2 Using where
# SELECT * FROM tbl_name WHERE <non-vcol expr>
select * from t3 where a between 1 and 2;
a b c
-2 -2 -2
1 -1 -1
+2 -2 -2
explain select * from t3 where a between 1 and 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
+1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using where
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr>
select * from t3 where b between -2 and -1;
a b c
@@ -164,7 +164,7 @@ a b c
1 -1 -1
explain select * from t3 where c between -2 and -1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Rowid-ordered scan
+1 SIMPLE t3 range c c 5 NULL 1 Using where
# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol>
select * from t3 where a between 1 and 2 order by c;
a b c
@@ -172,7 +172,7 @@ a b c
1 -1 -1
explain select * from t3 where a between 1 and 2 order by c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan; Using filesort
+1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol>
select * from t3 where b between -2 and -1 order by a;
a b c
@@ -188,7 +188,7 @@ a b c
2 -2 -2
explain select * from t3 where c between -2 and -1 order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Rowid-ordered scan; Using filesort
+1 SIMPLE t3 range c c 5 NULL 1 Using where; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol>
select * from t3 where b between -2 and -1 order by b;
a b c
@@ -204,7 +204,7 @@ a b c
1 -1 -1
explain select * from t3 where c between -2 and -1 order by b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Rowid-ordered scan; Using filesort
+1 SIMPLE t3 range c c 5 NULL 1 Using where; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
select * from t3 where b between -2 and -1 order by c;
a b c
@@ -220,7 +220,7 @@ a b c
1 -1 -1
explain select * from t3 where c between -2 and -1 order by c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range c c 5 NULL 1 Using index condition
+1 SIMPLE t3 range c c 5 NULL 1 Using where
# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
select sum(b) from t1 group by b;
sum(b)
diff --git a/mysql-test/t/mrr_icp_extra.test b/mysql-test/t/mrr_icp_extra.test
index 2f137845cfc..2d0fd527dcf 100644
--- a/mysql-test/t/mrr_icp_extra.test
+++ b/mysql-test/t/mrr_icp_extra.test
@@ -189,24 +189,17 @@ use world;
--enable_result_log
--enable_query_log
-explain
-SELECT * FROM City
-WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
-(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
-
-SELECT * FROM City
-WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
-(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
-
SELECT * FROM City
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
AND (Population > 101000 AND Population < 102000);
+--replace_column 9 #
explain
SELECT * FROM City
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
AND (Population > 101000 AND Population < 102000);
+--replace_column 9 #
explain
SELECT * FROM City
WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
@@ -219,16 +212,19 @@ WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
+--replace_column 9 #
explain
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
+--replace_column 6 # 7 # 9 #
explain
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
+--replace_column 6 # 7 # 9 #
explain
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
View Lorry Controller Status