diff options
Diffstat (limited to 'mysql-test/main/index_merge_myisam.test')
| -rw-r--r-- | mysql-test/main/index_merge_myisam.test | 245 |
1 files changed, 245 insertions, 0 deletions
diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test new file mode 100644 index 00000000000..d265007431e --- /dev/null +++ b/mysql-test/main/index_merge_myisam.test @@ -0,0 +1,245 @@ +# t/index_merge_myisam.test +# +# Index merge tests +# +# Last update: +# 2006-08-07 ML test refactored (MySQL 5.1) +# Main code of several index_merge tests +# -> include/index_merge*.inc +# wrapper t/index_merge_innodb.test sources now several +# include/index_merge*.inc files +# + +let $engine_type= MyISAM; +# MyISAM supports Merge tables +let $merge_table_support= 1; + +set @optimizer_switch_save= @@optimizer_switch; + +set optimizer_switch='index_merge_sort_intersection=off'; + +--source include/index_merge1.inc +--source include/index_merge_ror.inc +--source include/index_merge2.inc +--source include/index_merge_2sweeps.inc +--source include/index_merge_ror_cpk.inc + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, filler char(100), + key(a), key(b), key(c)); +insert into t1 select + A.a * B.a*10 + C.a*100, + A.a * B.a*10 + C.a*100, + A.a, + 'filler' +from t0 A, t0 B, t0 C; + +--echo This should use union: +explain select * from t1 where a=1 or b=1; + +--echo This should use ALL: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a=1 or b=1; + +--echo This should use sort-union: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=1 or b=1; + +--echo This will use sort-union: +set optimizer_switch=default; +explain select * from t1 where a<1 or b <1; + +--echo This should use ALL: +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where a<1 or b <1; + + +--echo This should use ALL: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a<1 or b <1; + +--echo This will use sort-union: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a<1 or b <1; + +alter table t1 add d int, add key(d); +update t1 set d=a; + +--echo This will use sort_union: +set optimizer_switch=default; +explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); + +--echo And if we disable sort_union, union: +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); + +drop table t1; + +# Now test that intersection can be disabled +create table t1 ( + a int, b int, c int, + filler1 char(200), filler2 char(200), + key(a),key(b),key(c) +); + +insert into t1 +select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' +from t0 A, t0 B, t0 C, t0 D where D.a<5; + +--echo This should be intersect: +set optimizer_switch=default; +explain select * from t1 where a=10 and b=10; + +--echo No intersect when index_merge is disabled: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a=10 and b=10; + +--echo No intersect if it is disabled: +set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off'; +explain select * from t1 where a=10 and b=10; + +--echo Do intersect when union was disabled +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=10 and b=10; + +--echo Do intersect when sort_union was disabled +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where a=10 and b=10; + +# Now take union-of-intersection and see how we can disable parts of it +--echo This will use intersection inside a union: +set optimizer_switch=default; +explain select * from t1 where a=10 and b=10 or c=10; + +--echo Should be only union left: +set optimizer_switch='default,index_merge_intersection=off'; +explain select * from t1 where a=10 and b=10 or c=10; + +--echo This will switch to sort-union (intersection will be gone, too, +--echo that's a known limitation: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=10 and b=10 or c=10; + +set optimizer_switch=default; + +drop table t0, t1; + + +--echo # +--echo # BUG#834514 Assertion `!table || (!table->read_set || bitmap_is_set(...' with aggregates +--echo # +CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ; +INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0); + +CREATE TABLE t2 ( b int, c int, KEY (c,b)) ; +INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0); + +CREATE TABLE t3 ( a int ) ; + +SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c) +FROM t1, t2 +WHERE (t2.c = t1.c) +AND ( + t1.b IN ( 4 ) + OR t1.a = 137 + AND EXISTS ( SELECT a FROM t3 ) +) +GROUP BY 2; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-4556 Server crashes in SEL_ARG::rb_insert with index_merge+index_merge_sort_union, FORCE INDEX +--echo # +CREATE TABLE t1 ( + pk int, + code char(2), + population_rate int, + area_rate int, + primary key (pk), + index (code), + key (population_rate), + key (area_rate) +); + +INSERT INTO t1 VALUES (1,'WI',20, 23), (2, 'WA', 13, 18); + +EXPLAIN +SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) +WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL ) +AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX'); + +SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) +WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL ) +AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX'); + +DROP TABLE t1; + +--echo # +--echo # MDEV-5069: Server crashes in SEL_ARG::increment_use_count with index_merge+index_merge_sort_union, FORCE INDEX +--echo # + +CREATE TABLE t1 ( + c1 varchar(64), + i int, + pk integer auto_increment, + c2 varchar(64), + index (c1), + index (i), + primary key (pk), + key (c2)) ENGINE=myisam; + +ALTER TABLE t1 DISABLE KEYS; + +INSERT INTO t1 VALUES +('West Virginia', 6121, NULL, 'California'),('Georgia', 60177, NULL, 'Arkansas'), +('Delaware', 90, NULL, 'Oregon'),('Wyoming', 7, NULL, 'Missouri'),( +'Delaware', 2, NULL, 'Utah'),('Wisconsin', 0, NULL, 'Iowa'), +('Kansas', 0, NULL, 'Florida'),('Ohio', 34358, NULL, 'Colorado'), +('Maine', 118, NULL, 'Texas'),('Mississippi', 0, NULL, 'Georgia'), +('Tennessee', 4, NULL, 'N/A'),('Georgia', 0, NULL, 'New Hampshire'), +('Wyoming', 2, NULL, 'N/A'),('Florida', 0, NULL, 'Arizona'), +('Rhode Island', -24002, NULL, 'Michigan'),('Alabama', 142, NULL, 'Indiana'), +('Colorado', 0, NULL, 'Louisiana'),('Michigan', 21194, NULL, 'Louisiana'), +('Oklahoma', 31475, NULL, 'Alabama'),('Pennsylvania', 0, NULL, 'Oklahoma'), +('Texas', 0, NULL, 'Texas'),('West Virginia', 5, NULL, 'Utah'), +('Florida', 49653, NULL, 'Kentucky'),('Tennessee', 19075, NULL, 'Oregon'), +('Maine', 3, NULL, 'Kansas, Kentucky, Iowa'),('Iowa', 1, NULL, 'South Dakota'), +('Kansas', -4037, NULL, 'Virginia'),('Delaware', 22550, NULL, 'Utah'), +('Illinois', 14634, NULL, 'South Carolina, Colorado'), +('Kansas', 6, NULL, 'South Dakota'),('Delaware', 9, NULL, ''), +('', 0, NULL, 'Utah, Delaware, Florida, Georgia, Nevada'), +('Colorado', 8, NULL, 'Montana'),('Maryland', 2689, NULL, 'Hawaii'), +('Florida', -12306, NULL, 'Delaware'), +('Indiana', 38567, NULL, 'Iowa, Minnesota, Maine'), +('Oklahoma', 9, NULL, 'Delaware, Kansas, Oregon, Nebraska, Maryland, Minnesota'), +('Tennessee', 12460, NULL, NULL),('Kentucky', 0, NULL, 'Ohio'), +('Nevada', 7, NULL, 'Vermont, Oregon, Oklahoma, Montana'), +('Nebraska', 61966, NULL, 'Nevada'),('Alaska', 131, NULL, 'Louisiana, Maine'), +('Wisconsin', 4, NULL, 'Nevada'),('South Carolina', 0, NULL, 'Washington'), +('West Virginia', 51314, NULL, 'Ohio'),('Louisiana', 0, NULL, ''), +('Pennsylvania', 0, NULL, 'Iowa, Idaho'),('Arkansas', 14010, NULL, 'Indiana'), +('Wyoming', -15514, NULL, 'Maine'),('Georgia', 0, NULL, 'N/A'), +('Kentucky', 1, NULL, 'Idaho'),('Wyoming', 60249, NULL, 'Indiana, Iowa'), +('Pennsylvania', 69, NULL, 'W'), +('New Mexico', 11480, NULL, 'Florida, Georgia, Hawaii'), +('South Carolina', 9, NULL, 'Iowa'),('Virginia', 0, NULL, 'Connecticut'), +('Mississippi', 19749, NULL, 'Rhode Island'),('Illinois', 5, NULL, 'Virginia'), +('Texas', -1749, NULL, 'Tennessee'),('Arizona', 28, NULL, 'California'), +('Florida', 62151, NULL, 'Kansas'),('California', 172, NULL, 'SC'), +('New Jersey', 0, NULL, 'North Carolina'),('Wyoming', 4, NULL, 'I'), +('Kansas', 10683, NULL, 'California'),('Arkansas', -14275, NULL, 'K'), +('Arizona', 5, NULL, 'California, Delaware, Rhode Island, Maryland'), +('Florida', 0, NULL, 'T'),('Alaska', 241, NULL, 'Virginia'); + +ALTER TABLE t1 ENABLE KEYS; + +EXPLAIN +SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) +WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR pk != 1 ; + +DROP TABLE t1; + +set optimizer_switch= @optimizer_switch_save; + |