MDEV-21480: Unique key using ref access though eq_ref access can be used

For a unique key if all the keyparts are NOT NULL or the predicates involving
the keyparts is NULL rejecting, then we can use EQ_REF access instead of ref
access with the unique key

8 files changed, 71 insertions, 8 deletions

diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
index 9cdc54f2e43..053a9fd8e50 100644
--- a/mysql-test/main/invisible_field.result
+++ b/mysql-test/main/invisible_field.result
@@ -405,7 +405,7 @@ c	int(11)	YES		NULL
 explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
-1	SIMPLE	t1	ref	b,c	b	5	test.t2.c	1	Using where
+1	SIMPLE	t1	eq_ref	b,c	b	5	test.t2.c	1	Using where
 select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
 a	a	b	c
 1	1	1	1
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index fe6d18f7807..e667eab7eb9 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -3333,11 +3333,11 @@ show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
 t1	0	PRIMARY	1	pk	A	1010	NULL	NULL		BTREE		
 t1	0	a	1	a	A	10	NULL	NULL	YES	BTREE		
-# t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+# t1 must use eq_ref(t1.a=t0.a) and rows must be 1 (and not 45):
 explain select * from t0,t1 where t0.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
-1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	
+1	SIMPLE	t1	eq_ref	a	a	5	test.t0.a	1	
 drop table t0,t1;
 # 
 # MDEV-21383: Possible range plan is not used under certain conditions
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index c72ff0e1a8c..07c72827d25 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1744,7 +1744,7 @@ analyze table t1;
 set myisam_stats_method=@tmp1;
 show keys from t1;
 
---echo # t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+--echo # t1 must use eq_ref(t1.a=t0.a) and rows must be 1 (and not 45):
 explain select * from t0,t1 where t0.a=t1.a;
 
 drop table t0,t1;
diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result
index ba1124aaa14..6edb0404524 100644
--- a/mysql-test/main/key.result
+++ b/mysql-test/main/key.result
@@ -639,3 +639,45 @@ SHOW STATUS LIKE 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	14.199000
 DROP TABLE t1;
+#
+# MDEV-21480: Unique key using ref access though eq_ref access can be used
+#
+create table t1(a int, b int,c int,  primary key(a), unique key(b,c));
+insert into t1 select seq, seq, seq from seq_1_to_10;
+create table t2(a int, b int,c int);
+insert into t2 select seq, seq, seq+1 from seq_1_to_100;
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	eq_ref	b	b	10	test.t2.a,test.t2.b	1	Using index
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+c	c
+1	2
+2	3
+3	4
+4	5
+5	6
+6	7
+7	8
+8	9
+9	10
+10	11
+alter table t1 drop PRIMARY KEY;
+alter table t1 add PRIMARY KEY(b,c);
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY,b	PRIMARY	8	test.t2.a,test.t2.b	1	Using index
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+c	c
+1	2
+2	3
+3	4
+4	5
+5	6
+6	7
+7	8
+8	9
+9	10
+10	11
+drop table t1,t2;
diff --git a/mysql-test/main/key.test b/mysql-test/main/key.test
index 0695dc2eecb..b386f1298fd 100644
--- a/mysql-test/main/key.test
+++ b/mysql-test/main/key.test
@@ -1,6 +1,7 @@
 --disable_warnings
 drop table if exists t1,t2,t3;
 --enable_warnings
+--source include/have_sequence.inc
 SET SQL_WARNINGS=1;
 
 #
@@ -581,3 +582,23 @@ EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a;
 SHOW STATUS LIKE 'Last_query_cost';
 
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-21480: Unique key using ref access though eq_ref access can be used
+--echo #
+
+create table t1(a int, b int,c int,  primary key(a), unique key(b,c));
+insert into t1 select seq, seq, seq from seq_1_to_10;
+
+create table t2(a int, b int,c int);
+insert into t2 select seq, seq, seq+1 from seq_1_to_100;
+
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+
+alter table t1 drop PRIMARY KEY;
+alter table t1 add PRIMARY KEY(b,c);
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+
+drop table t1,t2;
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index ec03e8c5f45..1bb597095db 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1515,7 +1515,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	a,b,c	c	5	NULL	420	Using where
-1	SIMPLE	t1	ref	a	a	39	test.t2.a,const	1	Using where; Using index
+1	SIMPLE	t1	eq_ref	a	a	39	test.t2.a,const	1	Using where; Using index
 SELECT d FROM t3 AS t1, t2 AS t2 
 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result
index 4d9a70e6bba..7b73ae0fc57 100644
--- a/mysql-test/main/subselect_sj_nonmerged.result
+++ b/mysql-test/main/subselect_sj_nonmerged.result
@@ -69,7 +69,7 @@ t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	t4	ref	a	a	10	<subquery2>.max(t2.a),test.t0.a	1	
+1	PRIMARY	t4	eq_ref	a	a	10	<subquery2>.max(t2.a),test.t0.a	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B;
@@ -79,7 +79,7 @@ t4.b in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
-1	PRIMARY	t4	ref	a	a	10	<subquery2>.max(t2.a),<subquery3>.max(t2.a)	1	
+1	PRIMARY	t4	eq_ref	a	a	10	<subquery2>.max(t2.a),<subquery3>.max(t2.a)	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
index 7780e165451..d12b6470ecf 100644
--- a/mysql-test/main/table_elim.result
+++ b/mysql-test/main/table_elim.result
@@ -279,7 +279,7 @@ insert into t2 values
 explain select t1.* from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	10	NULL	2	Using index
-1	SIMPLE	t2	ref	a	a	3	test.t1.a	1	Using where
+1	SIMPLE	t2	eq_ref	a	a	3	test.t1.a	1	Using where
 drop table t1, t2;
 #
 # check UPDATE/DELETE that look like they could be eliminated