MDEV-15777 Use inferred IS NOT NULL predicates in the range optimizer10.3-mdev15777

This patch introduces the optimization that allows range optimizer to
consider index range scans that are built employing NOT NULL predicates
inferred from WHERE conditions and ON expressions.
The patch adds a new optimizer switch not_null_range_scan.

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 #