summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
authorVarun Gupta <varun.gupta@mariadb.com>2019-05-11 20:44:18 +0530
committerVarun Gupta <varun.gupta@mariadb.com>2019-05-11 20:50:12 +0530
commit6a365e0bf212cd11001a0dad1e51fd6020905301 (patch)
treef9ec17b312c21e8e838fe8f51c8bb2fdc647170a /mysql-test/main
parent9965966a49b5b17da4f631664a35da1b46f03cb2 (diff)
downloadmariadb-git-6a365e0bf212cd11001a0dad1e51fd6020905301.tar.gz
MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
So to push index condition for each join tab we have calculate the index condition that can be pushed and then remove this index condition from the original condition. This is done through the function make_cond_remainder. The problem is the function make_cond_remainder does not remove index condition when there is an OR operator. Fixed this by making the function make_cond_remainder to keep in mind of the OR operator. Also updated results for multiple test files which were incorrectly updated by the commit e0c1b3f24246d22e6785315f9a8448bd9a590422 code which was supposed to remove the condition present in the index condition was not getting executed when the condition had OR operator, with AND the pushed index condition was getting removed from where. This problem affects all versions starting from 5.5 but this is a performance improvement, so fixing it in 10.4
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/index_merge_myisam.result2
-rw-r--r--mysql-test/main/innodb_icp.result2
-rw-r--r--mysql-test/main/mrr_icp_extra.result6
-rw-r--r--mysql-test/main/myisam.result2
-rw-r--r--mysql-test/main/myisam_icp.result23
-rw-r--r--mysql-test/main/myisam_icp.test15
-rw-r--r--mysql-test/main/myisam_mrr,64bit.rdiff22
-rw-r--r--mysql-test/main/order_by.result4
-rw-r--r--mysql-test/main/range.result8
-rw-r--r--mysql-test/main/range_mrr_icp.result8
-rw-r--r--mysql-test/main/range_vs_index_merge.result14
-rw-r--r--mysql-test/main/range_vs_index_merge_innodb.result8
-rw-r--r--mysql-test/main/select.result2
-rw-r--r--mysql-test/main/select_jcl6.result2
-rw-r--r--mysql-test/main/select_pkeycache.result2
-rw-r--r--mysql-test/main/subselect_mat_cost.result2
-rw-r--r--mysql-test/main/xtradb_mrr.result4
17 files changed, 89 insertions, 37 deletions
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index 3d31f8d3dfa..484ee626b98 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -23,7 +23,7 @@ test.t0 analyze status Engine-independent statistics collected
test.t0 analyze status OK
explain select * from t0 where key1 < 3 or key1 > 920 and key1 < 924;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1 i1 4 NULL 5 Using index condition; Using where
+1 SIMPLE t0 range i1 i1 4 NULL 5 Using index condition
explain
select * from t0 where key1 < 3 or key2 > 920 and key2 < 924;
id select_type table type possible_keys key key_len ref rows Extra
diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result
index 07d317925cd..d65acd5a48d 100644
--- a/mysql-test/main/innodb_icp.result
+++ b/mysql-test/main/innodb_icp.result
@@ -670,7 +670,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
diff --git a/mysql-test/main/mrr_icp_extra.result b/mysql-test/main/mrr_icp_extra.result
index 49acd7bde20..176df5cf9d5 100644
--- a/mysql-test/main/mrr_icp_extra.result
+++ b/mysql-test/main/mrr_icp_extra.result
@@ -74,7 +74,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12);
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 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -83,7 +83,7 @@ a b
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 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
@@ -125,7 +125,7 @@ Table Op Msg_type Msg_text
test.t1 optimize status OK
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 5 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 4 NULL 5 Using index condition; Rowid-ordered scan
select * from t1 force index (a) where a=0 or a=2;
a b c
0 NULL 0
diff --git a/mysql-test/main/myisam.result b/mysql-test/main/myisam.result
index 8c464f2f312..f13ffc57901 100644
--- a/mysql-test/main/myisam.result
+++ b/mysql-test/main/myisam.result
@@ -395,7 +395,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL a NULL NULL NULL 5 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 5 Using index condition; Using where
+1 SIMPLE t1 range a a 4 NULL 5 Using index condition
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
diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result
index 577a0df12b0..0fdc3f11627 100644
--- a/mysql-test/main/myisam_icp.result
+++ b/mysql-test/main/myisam_icp.result
@@ -505,7 +505,7 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
@@ -663,7 +663,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
@@ -995,4 +995,23 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 10 NULL 10 Using where
drop table t0, t1;
+#
+# MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+#
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (key1 int not null, filler char(100));
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range key1 key1 4 NULL 22 Using index condition; Rowid-ordered scan
+select * from t1 where key1 < 3 or key1 > 99999;
+key1 filler
+0 filler-data
+1 filler-data
+2 filler-data
+drop table ten,one_k,t1;
set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/main/myisam_icp.test b/mysql-test/main/myisam_icp.test
index 508c282b1dc..b6d35968b1c 100644
--- a/mysql-test/main/myisam_icp.test
+++ b/mysql-test/main/myisam_icp.test
@@ -282,5 +282,20 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
drop table t0, t1;
+--echo #
+--echo # MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (key1 int not null, filler char(100));
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+select * from t1 where key1 < 3 or key1 > 99999;
+drop table ten,one_k,t1;
+
set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/main/myisam_mrr,64bit.rdiff b/mysql-test/main/myisam_mrr,64bit.rdiff
index 82f6dfabb65..feb97548ad8 100644
--- a/mysql-test/main/myisam_mrr,64bit.rdiff
+++ b/mysql-test/main/myisam_mrr,64bit.rdiff
@@ -1,5 +1,23 @@
---- main/myisam_mrr.result 2019-02-04 13:47:00.000000000 +0530
-+++ main/myisam_mrr,64bit.reject 2019-02-04 13:50:01.000000000 +0530
+--- main/myisam_mrr.result 2019-04-27 21:46:07.000000000 +0530
++++ main/myisam_mrr,64bit.reject 2019-05-11 20:40:32.000000000 +0530
+@@ -188,7 +188,7 @@
+ select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
+ or c='no-such-row2');
+ id select_type table type possible_keys key key_len ref rows Extra
+-1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Using where; Rowid-ordered scan
++1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Rowid-ordered scan
+ select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
+ or c='no-such-row2');
+ a b c filler
+@@ -210,7 +210,7 @@
+ explain
+ select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
+ id select_type table type possible_keys key key_len ref rows Extra
+-1 SIMPLE t4 range idx1 idx1 29 NULL 20 Using index condition; Using where; Rowid-ordered scan
++1 SIMPLE t4 range idx1 idx1 29 NULL 20 Using index condition; Rowid-ordered scan
+ select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
+ a b c filler
+ b-1 NULL c-1 NULL-15
@@ -617,8 +617,8 @@
show status like 'handler_mrr%';
Variable_name Value
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 4e8f8bfb17d..a053b6060c4 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -736,7 +736,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12);
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 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -745,7 +745,7 @@ a b
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 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 9a2d99e2f82..fe0e3ef024d 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -953,7 +953,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 17 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 17 Using index condition; Using where
+1 SIMPLE t1 range status status 23 NULL 17 Using index condition
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1073,13 +1073,13 @@ id select_type table type possible_keys key key_len ref rows Extra
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 index condition; Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition
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 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 index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
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
@@ -2008,7 +2008,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 3 Using index condition; Using where
+1 SIMPLE t100 range I I 10 NULL 3 Using index condition
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index 5cda4111b6d..fbcee9247a2 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -956,7 +956,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 17 Using index condition; Rowid-ordered scan
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 17 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range status status 23 NULL 17 Using index condition; Rowid-ordered scan
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1076,13 +1076,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
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 index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
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 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 index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
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
@@ -2011,7 +2011,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 3 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t100 range I I 10 NULL 3 Using index condition; Rowid-ordered scan
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result
index 65ac003b427..5c3e5441b8b 100644
--- a/mysql-test/main/range_vs_index_merge.result
+++ b/mysql-test/main/range_vs_index_merge.result
@@ -327,11 +327,11 @@ ID Name Country Population
EXPLAIN
SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500);
id select_type table type possible_keys key key_len ref rows Extra
@@ -343,11 +343,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 223 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 223 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 72 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1079,7 +1079,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1166,7 +1166,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1938,6 +1938,6 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR
Country='NOR' AND Name IN ('Oslo', 'Bergen') OR
Country='ITA' AND Name IN ('Napoli', 'Venezia');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where
+1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition
DROP DATABASE world;
set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result
index 061fcab15b4..e2fd8020049 100644
--- a/mysql-test/main/range_vs_index_merge_innodb.result
+++ b/mysql-test/main/range_vs_index_merge_innodb.result
@@ -349,11 +349,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 395 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 395 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 133 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -957,7 +957,7 @@ WHERE ((Population > 101000 AND Population < 11000) OR
ID BETWEEN 3500 AND 3800) AND Country='USA'
AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using index condition; Using where
+1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((Population > 101000 AND Population < 11000) OR
@@ -1944,7 +1944,7 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR
Country='NOR' AND Name IN ('Oslo', 'Bergen') OR
Country='ITA' AND Name IN ('Napoli', 'Venezia');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where
+1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition
DROP DATABASE world;
set session optimizer_switch='index_merge_sort_intersection=default';
set global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index a527459657a..804830c48df 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3422,7 +3422,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 index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index c1e9e9d3ad5..31856279ed5 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3433,7 +3433,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 index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index a527459657a..804830c48df 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3422,7 +3422,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 index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/main/subselect_mat_cost.result b/mysql-test/main/subselect_mat_cost.result
index 9b0578b62b1..21ab292e7e5 100644
--- a/mysql-test/main/subselect_mat_cost.result
+++ b/mysql-test/main/subselect_mat_cost.result
@@ -288,7 +288,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English
AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
AND Code = Country;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 44 Using index condition; Using where; Rowid-ordered scan
+1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 44 Using index condition; Rowid-ordered scan
1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
3 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 48 Using index condition
set statement optimizer_switch='rowid_filter=off' for
diff --git a/mysql-test/main/xtradb_mrr.result b/mysql-test/main/xtradb_mrr.result
index f49207c0e41..383d04207af 100644
--- a/mysql-test/main/xtradb_mrr.result
+++ b/mysql-test/main/xtradb_mrr.result
@@ -186,7 +186,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -208,7 +208,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15