diff options
Diffstat (limited to 'mysql-test/main')
| -rw-r--r-- | mysql-test/main/invisible_field.result | 2 | ||||
| -rw-r--r-- | mysql-test/main/join.result | 4 | ||||
| -rw-r--r-- | mysql-test/main/join.test | 2 | ||||
| -rw-r--r-- | mysql-test/main/key.result | 42 | ||||
| -rw-r--r-- | mysql-test/main/key.test | 21 | ||||
| -rw-r--r-- | mysql-test/main/order_by.result | 2 | ||||
| -rw-r--r-- | mysql-test/main/subselect_sj_nonmerged.result | 4 | ||||
| -rw-r--r-- | mysql-test/main/table_elim.result | 2 |
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 |