mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
332 lines
11 KiB
Text
332 lines
11 KiB
Text
# 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
|
|
#
|
|
--source include/no_valgrind_without_big.inc
|
|
|
|
--source include/test_db_charset_latin1.inc
|
|
|
|
|
|
SET DEFAULT_STORAGE_ENGINE = 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;
|
|
|
|
# note: (pk is not null and (pk <1 or pk>1)) below is a sargable form of pk!=1
|
|
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 is not null and (pk <1 or pk>1)) ;
|
|
|
|
DROP TABLE t1;
|
|
|
|
set optimizer_switch= @optimizer_switch_save;
|
|
|
|
--echo #
|
|
--echo # MDEV-21932: ROR union with index_merge_sort_union=off
|
|
--echo #
|
|
|
|
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;
|
|
insert into t0 select a+20 from t0;
|
|
insert into t0 select a+40 from t0;
|
|
insert into t0 select a+80 from t0;
|
|
insert into t0 select a+160 from t0;
|
|
delete from t0 where a > 300;
|
|
|
|
create table t1 (
|
|
f1 int, f2 int, f3 int, f4 int,
|
|
primary key (f1), key (f3), key(f4)
|
|
) engine=myisam;
|
|
insert into t1 select a+100, a+100, a+100, a+100 from t0;
|
|
insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL);
|
|
analyze table t1;
|
|
|
|
set optimizer_switch='index_merge_sort_union=off';
|
|
set optimizer_switch='index_merge_union=on';
|
|
|
|
let $q1=
|
|
select * from t1
|
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
|
eval explain $q1;
|
|
eval $q1;
|
|
|
|
insert into t1 values (52,0,1,0),(53,0,1,0);
|
|
insert into t1 values (50,0,1,0),(51,0,1,0);
|
|
insert into t1 values (48,0,1,0),(49,0,1,0);
|
|
insert into t1 values (46,0,1,0),(47,0,1,0);
|
|
insert into t1 values (44,0,1,0),(45,0,1,0);
|
|
analyze table t1;
|
|
|
|
let $q2=
|
|
select * from t1
|
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
|
eval explain $q2;
|
|
eval $q2;
|
|
|
|
drop table t0,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 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;
|
|
|
|
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;
|
|
|
|
--echo # End of 10.1 tests
|
|
|
|
--source include/test_db_charset_restore.inc
|