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

10 files changed, 87 insertions, 15 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
diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result
index 6e3411dc066..05d9fa3a244 100644
--- a/mysql-test/suite/gcol/r/gcol_select_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result
@@ -1102,7 +1102,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where
-1	SIMPLE	t1	ref	b	b	5	test.t2.b	1	
+1	SIMPLE	t1	eq_ref	b	b	5	test.t2.b	1	
 EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	b	5	NULL	2	Using index
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d6c4a49526c..de66f8bc59e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10066,6 +10066,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
   uchar *key_buff=j->ref.key_buff, *null_ref_key= 0;
   uint null_ref_part= NO_REF_PART;
   bool keyuse_uses_no_tables= TRUE;
+  uint not_null_keyparts= 0;
   if (ftkey)
   {
     j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
@@ -10095,6 +10096,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
       j->ref.items[i]=keyuse->val;		// Save for cond removal
       j->ref.cond_guards[i]= keyuse->cond_guard;
 
+      if (!keyuse->val->maybe_null || keyuse->null_rejecting)
+        not_null_keyparts++;
       /*
         Set ref.null_rejecting to true only if we are going to inject a
         "keyuse->val IS NOT NULL" predicate.
@@ -10154,12 +10157,18 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
   ulong key_flags= j->table->actual_key_flags(keyinfo);
   if (j->type == JT_CONST)
     j->table->const_table= 1;
-  else if (!((keyparts == keyinfo->user_defined_key_parts && 
-              ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)) ||
-	     (keyparts > keyinfo->user_defined_key_parts &&   // true only for extended keys 
-              MY_TEST(key_flags & HA_EXT_NOSAME) &&
-              keyparts == keyinfo->ext_key_parts)) ||
-	    null_ref_key)
+  else if (!((keyparts == keyinfo->user_defined_key_parts &&
+              (
+                (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
+                /* Unique key and all keyparts are NULL rejecting */
+                ((key_flags & HA_NOSAME) && keyparts == not_null_keyparts)
+              )) ||
+              /* true only for extended keys */
+              (keyparts > keyinfo->user_defined_key_parts &&
+               MY_TEST(key_flags & HA_EXT_NOSAME) &&
+               keyparts == keyinfo->ext_key_parts)
+            ) ||
+            null_ref_key)
   {
     /* Must read with repeat */
     j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;