MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columnsbb-10.3-mdev19600

Modify best_access_path() to produce rows=1 estimate for null-rejecting
lookups on unique NULL keys.

1 files changed, 57 insertions, 1 deletions

diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index 014c44d1181..0026924b605 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -2569,9 +2569,22 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
 INSERT INTO t1 VALUES
 (1,2),(2,1),(3,3),(4,2),(5,5),
 (6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
 INSERT INTO t2 VALUES
 (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+1	PRIMARY	t1	index	b	b	5	NULL	10	Using where; Using index; LooseScan
+1	PRIMARY	t2	ref	c	c	5	test.t1.b	1	Using where; FirstMatch(t1)
+1	PRIMARY	t1	ref	b	b	5	test.t1.b	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT a, b, d FROM t1, t2
 WHERE ( b, d ) IN
 ( SELECT b, d FROM t1, t2 WHERE b = c );
@@ -2596,6 +2609,49 @@ a	b	d
 10	2	1
 10	2	1
 DROP TABLE t1, t2;
+# Another testcase for the above that still uses LooseScan:
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t1 (
+pk int primary key auto_increment,
+kp1 int,
+kp2 int,
+filler char(100),
+key (kp1, kp2)
+);
+insert into t1 (kp1, kp2, filler) 
+select 
+A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+create table t2 (a int, filler char(100), key(a));
+create table t3 (a int);
+insert into t3 values (1),(2);
+insert into t2 
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+analyze table t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Table is already up to date
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	OK
+delete from t1 where kp2 in (1,3);
+# Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	10	Using where; Using index; LooseScan
+1	PRIMARY	t2	ref	a	a	5	test.t1.kp2	20	Using index
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+sum(t2.a)
+1640
+drop table t0,t10;
+drop table t1,t2,t3;
 #
 # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
 #