|
psergey, I tried to include the following change in my 10.1 cleanup, but it would cause result differences:
diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc
|
index 9ebca3cd22f..1ceeb159cc0 100644
|
--- a/mysql-test/include/index_merge_ror_cpk.inc
|
+++ b/mysql-test/include/index_merge_ror_cpk.inc
|
@@ -47,16 +47,10 @@ create table t1
|
primary key (pk1, pk2)
|
);
|
|
---disable_query_log
|
-begin;
|
-let $1=10000;
|
-while ($1)
|
-{
|
- eval insert into t1 values ($1 div 10,$1 mod 100, $1/100,$1/100, $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2');
|
- dec $1;
|
-}
|
-commit;
|
---enable_query_log
|
+insert into t1
|
+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,
|
+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'
|
+from seq_10000_to_1;
|
|
# Verify that range scan on CPK is ROR
|
# (use index_intersection because it is impossible to check that for index union)
|
For InnoDB, the only result difference was the addition of the statement. For MyISAM, we surprisingly get different EXPLAIN results:
--- mysql-test/r/index_merge_myisam.result 2019-10-11 12:21:24.254070301 +0300
|
+++ mysql-test/r/index_merge_myisam.reject 2019-10-11 12:38:18.194391736 +0300
|
@@ -1357,6 +1357,10 @@
|
key (pktail5bad, pk1, pk2, pk2copy),
|
primary key (pk1, pk2)
|
);
|
+insert into t1
|
+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,
|
+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'
|
+from seq_10000_to_1;
|
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where
|
@@ -1389,7 +1393,7 @@
|
95 50
|
explain select * from t1 where badkey=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref key1 key1 4 const 91 Using where
|
+1 SIMPLE t1 ref key1 key1 4 const 73 Using where
|
set @tmp_index_merge_ror_cpk=@@optimizer_switch;
|
set optimizer_switch='extended_keys=off';
|
explain select * from t1 where pk1 < 7500 and key1 = 10;
|
@@ -1398,22 +1402,22 @@
|
set optimizer_switch=@tmp_index_merge_ror_cpk;
|
explain select * from t1 where pktail1ok=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where
|
+1 SIMPLE t1 ref key1,pktail1ok key1 4 const 73 Using where
|
explain select * from t1 where pktail2ok=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where
|
+1 SIMPLE t1 ref key1,pktail2ok key1 4 const 73 Using where
|
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where
|
+1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 154 Using sort_union(pktail2ok,key1); Using where
|
explain select * from t1 where pktail3bad=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where
|
+1 SIMPLE t1 ref key1,pktail3bad key1 4 const 73 Using where
|
explain select * from t1 where pktail4bad=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where
|
+1 SIMPLE t1 ref key1,pktail4bad key1 4 const 73 Using where
|
explain select * from t1 where pktail5bad=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 70 Using where
|
+1 SIMPLE t1 ref key1,pktail5bad key1 4 const 73 Using where
|
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
|
On a related note, div returns an integer result, while / is floating-point. If we change the first div to /, then there will also be result differences. And I guess it would be better to do something like
insert … select …, @h:=seq/100, @h, @h, …
|
to save some redundant typing and evaluation.
|