path: root/mysql-test/main/index_merge_myisam.test

# 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='rowid_filter=off';
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,rowid_filter=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,rowid_filter=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,rowid_filter=off';
explain select * from t1 where a<1 or b <1;


--echo This should use ALL:
set optimizer_switch='default,index_merge=off,rowid_filter=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,rowid_filter=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,rowid_filter=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,rowid_filter=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,rowid_filter=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,rowid_filter=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,rowid_filter=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,rowid_filter=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,rowid_filter=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;

--echo #
--echo # MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union
--echo #

create table t0
(
  key1 int not null,
  INDEX i1(key1)
);

insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
let $1=7;
set @d=8;
while ($1)
{
  eval insert into t0 select key1+ @d from t0;
  eval set @d=@d*2;
  dec $1;
}
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;

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;
select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
set optimizer_use_condition_selectivity=2;
explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
set @@optimizer_switch= @optimizer_switch_save;
drop table t0;