summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2020-05-05 21:16:22 +0300
committerMarko Mäkelä <marko.makela@mariadb.com>2020-05-05 21:16:22 +0300
commit7bcaa541aa1f298abf8e863566a19b3e9ec2f659 (patch)
tree066d58ad89536edf0e9a3de898506735c933381f /mysql-test/main
parent36b8ac2c0d763a3f96b254cb6e2cbdbc40dde22b (diff)
parent2c3c851d2cba73825f81cd06220138b15c17ae4d (diff)
downloadmariadb-git-7bcaa541aa1f298abf8e863566a19b3e9ec2f659.tar.gz
Merge 10.4 into 10.5
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/invisible_field.result2
-rw-r--r--mysql-test/main/join.result4
-rw-r--r--mysql-test/main/join.test2
-rw-r--r--mysql-test/main/key.result42
-rw-r--r--mysql-test/main/key.test21
-rw-r--r--mysql-test/main/order_by.result2
-rw-r--r--mysql-test/main/subselect_sj_nonmerged.result4
-rw-r--r--mysql-test/main/table_elim.result2
8 files changed, 71 insertions, 8 deletions
diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
index 0b3ec037613..a57298dd5c5 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 e5fa88512b9..d8eb9795b4e 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -3340,11 +3340,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 1010 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 bab1ce42a25..08930b622e2 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1745,7 +1745,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 996341e1557..78a2f42c477 100644
--- a/mysql-test/main/key.result
+++ b/mysql-test/main/key.result
@@ -643,3 +643,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 ccaef163d08..4e3e02c8add 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;
#
@@ -582,3 +583,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 b4e508f1bda..120a318a683 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1589,7 +1589,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 cd32c815de8..422af02c31a 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 7b0806c63bf..2088e94c65a 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