diff options
Diffstat (limited to 'mysql-test/main/index_merge_myisam.result')
| -rw-r--r-- | mysql-test/main/index_merge_myisam.result | 72 |
1 files changed, 38 insertions, 34 deletions
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 3fb2af69e61..e9b327e037f 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1693,41 +1693,8 @@ id select_type table type possible_keys key key_len ref rows Extra DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; # -# MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +# MDEV-21932: ROR union with index_merge_sort_union=off # -create table t0 -( -key1 int not null, -INDEX i1(key1) -); -insert into t0 select * from seq_1_to_1024; -alter table t0 add key2 int not null, add index i2(key2); -alter table t0 add key3 int not null, add index i3(key3); -alter table t0 add key8 int not null, add index i8(key8); -update t0 set key2=key1,key3=key1,key8=1024-key1; -analyze table t0; -Table Op Msg_type Msg_text -test.t0 analyze status Engine-independent statistics collected -test.t0 analyze status OK -set @optimizer_switch_save=@@optimizer_switch; -set optimizer_switch='derived_merge=off,derived_with_keys=off'; -explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where -2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where -select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; -key1 key2 key3 key8 -3 3 3 1021 -set optimizer_use_condition_selectivity=2; -explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where -2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where -select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; -key1 key2 key3 key8 -3 3 3 1021 -set @@optimizer_switch= @optimizer_switch_save; -drop table t0; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); insert into t0 select a+10 from t0; @@ -1779,3 +1746,40 @@ f1 f2 f3 f4 9 0 2 6 drop table t0,t1; set optimizer_switch= @optimizer_switch_save; +# +# MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +# +create table t0 +( +key1 int not null, +INDEX i1(key1) +); +insert into t0 select * from seq_1_to_1024; +alter table t0 add key2 int not null, add index i2(key2); +alter table t0 add key3 int not null, add index i3(key3); +alter table t0 add key8 int not null, add index i8(key8); +update t0 set key2=key1,key3=key1,key8=1024-key1; +analyze table t0; +Table Op Msg_type Msg_text +test.t0 analyze status Engine-independent statistics collected +test.t0 analyze status OK +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set optimizer_use_condition_selectivity=2; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set @@optimizer_switch= @optimizer_switch_save; +drop table t0; +# End of 10.1 tests |