MDEV-6480: Remove conditions for which range optimizer returned SEL_ARG::IMPOSSIBLE

Let range optimizer remove parts of OR-clauses for which range analysis
produced SEL_TREE(IMPOSSIBLE).
There is no need to remove parts of AND-clauses: either they are inside
of OR (and the whole AND-clause will be removed), or the AND-clause is
at the top level, in which case the whole WHERE (or ON) is always FALSE
and this is a degenerate case which receives special treatment.

The removal process takes care not to produce 1-way ORs (in that case
we substitute the OR for its remaining member).

6 files changed, 127 insertions, 5 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/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/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;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 7b68f42c4cb..6249d2b5e4f 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1745,3 +1745,35 @@ explain select * from t3, t2 where t2.a < t3.b  and t3.a=1;
 --echo # The second table should use 'range':
 explain select * from t3, t2 where t3.b > t2.a   and t3.a=1;
 drop table t1,t2,t3;
+
+--echo #
+--echo # MDEV-6480: Remove conditions for which range optimizer returned SEL_ARG::IMPOSSIBLE.
+--echo #
+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;
+
+--echo # 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;
+
+--echo # 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);
+
+--echo # Here, conditions b will not be removed, because "c<44" is not sargable
+--echo #   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);
+
+--echo # 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;
+
+--echo # 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);
+
+drop table t1,t2;