Merge 10.1 into bb-10.1-mdev6657

7 files changed, 132 insertions, 6 deletions

diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index c907997573a..9820170aa59 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -74,7 +74,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	17	Using sort_union(i1,i2); Using where
 explain select * from t0 where key2 = 45 or key1 <=> null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	range	i1,i2	i2	4	NULL	1	Using where
+1	SIMPLE	t0	range	i1,i2	i2	4	NULL	1	Using index condition
 explain select * from t0 where key2 = 45 or key1 is not null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	i1,i2	NULL	NULL	NULL	1024	Using where
diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result
index 9140f306f77..cae402a9f12 100644
--- a/mysql-test/r/innodb_ext_key.result
+++ b/mysql-test/r/innodb_ext_key.result
@@ -1002,7 +1002,7 @@ insert into t2 (b) values (null), (null), (null);
 set optimizer_switch='extended_keys=on';
 explain select a from t1 where b is null order by a desc limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	b	b	9	const	2	Using where; Using filesort
+1	SIMPLE	t1	index	b	PRIMARY	8	NULL	3	Using where
 select a from t1 where b is null order by a desc limit 2;
 a
 3
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index c4770182598..36c2f1898f8 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2090,10 +2090,10 @@ SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
 WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
 ORDER BY t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where
 1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
 Warnings:
-Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
 SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
 WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
 ORDER BY t1.b;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 862fc194a7a..6f3da3efdd7 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2101,10 +2101,10 @@ SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
 WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
 ORDER BY t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where
 1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
 Warnings:
-Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
 SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
 WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
 ORDER BY t1.b;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index aba8d6c201c..d5b25534de0 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -2949,3 +2949,39 @@ explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 li
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	key1	key1	5	NULL	2	Using where; Using buffer
 drop table t1,t2;
+#
+# MDEV-465: Optimizer : wrong index choice, leading to strong performances issues
+#
+CREATE TABLE t1 (
+id1 int(10) unsigned NOT NULL auto_increment,
+id2 tinyint(3) unsigned NOT NULL default '0',
+id3 tinyint(3) unsigned NOT NULL default '0',
+id4 int(10) unsigned NOT NULL default '0',
+date timestamp NOT NULL default CURRENT_TIMESTAMP,
+PRIMARY KEY  (id1),
+KEY id_234_date (id2,id3,id4,date),
+KEY id_23_date (id2,id3,date)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+# t1 has "bad" index declaration order..
+CREATE TABLE t2 (
+id1 int(10) unsigned NOT NULL auto_increment,
+id2 tinyint(3) unsigned NOT NULL default '0',
+id3 tinyint(3) unsigned NOT NULL default '0',
+id4 int(10) unsigned NOT NULL default '0',
+date timestamp NOT NULL default CURRENT_TIMESTAMP,
+PRIMARY KEY  (id1),
+KEY id_23_date (id2,id3,date),
+KEY id_234_date (id2,id3,id4,date)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+# t2 has a "good" index declaration order
+INSERT INTO t1 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);
+INSERT INTO t2 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);
+# The following two must both use id_23_date and no "using filesort":
+EXPLAIN SELECT id1 FROM t1 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	id_234_date,id_23_date	id_23_date	2	NULL	3	Using where
+# See above query
+EXPLAIN SELECT id1 FROM t2 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	id_23_date,id_234_date	id_23_date	2	const,const	3	Using where
+drop table t1,t2;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index f2ad42ebc8d..fe528e1b2e9 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2196,3 +2196,48 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	a	a	5	NULL	1	Using where; Using index
 drop table t1,t2,t3;
+#
+# MDEV-6480: Remove conditions for which range optimizer returned SEL_ARG::IMPOSSIBLE.
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, c int, key(a), key(b));
+insert into t2
+select 
+A.a + B.a* 10 + C.a * 100,
+A.a + B.a* 10 + C.a * 100,
+12345
+from 
+t1 A, t1 B, t1 C;
+# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
+explain extended select * from t2 where (b > 25 and b < 15) or a<44;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	a,b	a	5	NULL	43	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`a` < 44)
+# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
+explain extended select * from t2 where a < 44 or (b > 25 and b < 15);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	a,b	a	5	NULL	43	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`a` < 44)
+# Here, conditions b will not be removed, because "c<44" is not sargable
+#   and hence (b.. and .. b) part is not analyzed at all:
+explain extended select * from t2 where c < 44 or (b > 25 and b < 15);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	1000	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` < 44) or ((`test`.`t2`.`b` > 25) and (`test`.`t2`.`b` < 15)))
+# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
+explain extended select * from t2 where (b > 25 and b < 15) or c < 44;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	1000	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` < 44))
+# Try a case where both OR parts produce SEL_ARG::IMPOSSIBLE:
+explain extended select * from t2 where (b > 25 and b < 15) or (a>55 and a<44);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 0
+drop table t1,t2;
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 16b35448c50..62bea71173c 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -2198,4 +2198,49 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	a	a	5	NULL	1	Using where; Using index
 drop table t1,t2,t3;
+#
+# MDEV-6480: Remove conditions for which range optimizer returned SEL_ARG::IMPOSSIBLE.
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, c int, key(a), key(b));
+insert into t2
+select 
+A.a + B.a* 10 + C.a * 100,
+A.a + B.a* 10 + C.a * 100,
+12345
+from 
+t1 A, t1 B, t1 C;
+# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
+explain extended select * from t2 where (b > 25 and b < 15) or a<44;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	a,b	a	5	NULL	43	100.00	Using index condition; Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`a` < 44)
+# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
+explain extended select * from t2 where a < 44 or (b > 25 and b < 15);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	a,b	a	5	NULL	43	100.00	Using index condition; Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`a` < 44)
+# Here, conditions b will not be removed, because "c<44" is not sargable
+#   and hence (b.. and .. b) part is not analyzed at all:
+explain extended select * from t2 where c < 44 or (b > 25 and b < 15);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	1000	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` < 44) or ((`test`.`t2`.`b` > 25) and (`test`.`t2`.`b` < 15)))
+# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
+explain extended select * from t2 where (b > 25 and b < 15) or c < 44;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	1000	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` < 44))
+# Try a case where both OR parts produce SEL_ARG::IMPOSSIBLE:
+explain extended select * from t2 where (b > 25 and b < 15) or (a>55 and a<44);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 0
+drop table t1,t2;
 set optimizer_switch=@mrr_icp_extra_tmp;