1 files changed, 182 insertions, 0 deletions
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index bd2299bac5f..014916a4d7f 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2053,3 +2053,185 @@ drop table t1;
 --echo #
 --echo # End of 10.2 tests
 --echo #
+
+--echo #
+--echo # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer
+--echo #
+
+set @save_optimizer_switch= @@optimizer_switch;
+set @@optimizer_switch='not_null_range_scan=on';
+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 (
+  id int NOT NULL,
+  subset_id int DEFAULT NULL,
+  PRIMARY KEY (id),
+  KEY t1_subset_id (subset_id));
+
+create table t2 (
+  id int,
+  col int NOT NULL,
+  key (id)
+);
+
+insert into t1 select a,a from one_k limit 5;
+insert into t1 select a+5,NULL from one_k limit 995;
+insert into t2 select a,a from one_k;
+
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2);
+--echo # with a subquery
+--echo # expected the same plan as above
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1
+  WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col);
+--echo # non-mergable subquery
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+create view v1 as SELECT t2.id FROM t2;
+create view v2 as SELECT t2.id FROM t2 group by t2.col;
+
+let $q=
+SELECT * FROM t1, v1 where t1.subset_id=v1.id;
+--echo # with mergeable view
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+let $q= SELECT * FROM t1, v2 where t1.subset_id=v2.id;
+--echo # with non-mergeable view
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4);
+--echo # expected for t2 and for t1: range access
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10;
+--echo # no range access expected for t1
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id;
+--echo # expected for t1: range access
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id
+LIMIT 10;
+--echo # no range access expected for t1
+eval explain $q;
+eval $q;
+
+drop index id on t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+--echo # expected for t1: range access
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL;
+--echo # expected impossible where after reading const tables
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL;
+--echo # expected impossible where after reading const tables
+eval explain $q;
+eval $q;
+
+drop index t1_subset_id on t1;
+alter table t1 add column m int not null default 0;
+alter table t1 add index idx(m,subset_id);
+alter table t2 add index (id);
+update t1 set m = id mod 2;
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ;
+--echo # expected for t1: range access by idx (keylen=9)
+eval explain $q;
+eval $q;
+
+
+drop view v1,v2;
+drop table t1,t2;
+
+create table t1 (
+  id int NOT NULL,
+  subset_id int DEFAULT NULL,
+  KEY key1(id, subset_id),
+  KEY t1_subset_id (subset_id)
+);
+
+create table t2 (
+  id int NOT NULL,
+  col int NOT NULL,
+  key (id)
+);
+
+insert into t1 select 1,a from one_k limit 5;
+insert into t1 select 1,NULL from one_k limit 495;
+insert into t2 select a,a from one_k;
+
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id;
+--echo # expected for t1 :range access by index key1
+--echo # rows 4 instead of 500
+eval explain $q;
+eval $q;
+
+drop table t1,t2;
+
+create table t1 (id int unsigned,col int, KEY key1(id));
+create table t2 (id int unsigned,col int DEFAULT NULL,key (id));
+insert into t1 select a,2 from one_k limit 50;
+insert into t1 select NULL,2 from one_k limit 450;
+insert into t2 select a,a from one_k;
+insert into t2 select a,a from one_k;
+
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10;
+--echo # using key1 for range access on t1 and also using index for sorting,
+--echo # no filesort, rows should be 75 not 500
+eval explain $q;
+eval $q;
+
+drop table t1,t2;
+
+drop table ten,one_k;
+set @@optimizer_switch= @save_optimizer_switch;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #