1860 lines
104 KiB
Plaintext
1860 lines
104 KiB
Plaintext
set global xengine_force_flush_memtable_now=1;
|
|
#---------------- Index merge test 1 -------------------------------------------
|
|
# (Start of test file: index_merge1.inc)
|
|
SET SESSION DEFAULT_STORAGE_ENGINE = XEngine;
|
|
drop table if exists t0, t1, t2, t3, t4;
|
|
# Create and fill a table with simple keys
|
|
create table t0
|
|
(
|
|
key1 int not null,
|
|
key2 int not null,
|
|
key3 int not null,
|
|
key4 int not null,
|
|
key5 int not null,
|
|
key6 int not null,
|
|
key7 int not null,
|
|
key8 int not null,
|
|
INDEX i1(key1),
|
|
INDEX i2(key2),
|
|
INDEX i3(key3),
|
|
INDEX i4(key4),
|
|
INDEX i5(key5),
|
|
INDEX i6(key6),
|
|
INDEX i7(key7),
|
|
INDEX i8(key8)
|
|
);
|
|
analyze table t0;
|
|
Table Op Msg_type Msg_text
|
|
test.t0 analyze status OK
|
|
# 1. One index
|
|
explain select * from t0 where key1 < 3 or key1 > 1020;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL range i1 i1 4 NULL 2 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key1` > 1020))
|
|
# 2. Simple cases
|
|
explain
|
|
select * from t0 where key1 < 3 or key2 > 1020;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL 2 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` > 1020))
|
|
select * from t0 where key1 < 3 or key2 > 1020;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 1023
|
|
2 2 2 2 2 2 2 1022
|
|
1021 1021 1021 1021 1021 1021 1021 3
|
|
1022 1022 1022 1022 1022 1022 1022 2
|
|
1023 1023 1023 1023 1023 1023 1023 1
|
|
1024 1024 1024 1024 1024 1024 1024 0
|
|
explain select * from t0 where key1 < 2 or key2 <3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 3))
|
|
explain
|
|
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` > 30) and (`test`.`t0`.`key1` < 35)) or ((`test`.`t0`.`key2` > 32) and (`test`.`t0`.`key2` < 40)))
|
|
# Bug#21277: InnoDB, wrong result set, index_merge strategy,
|
|
# second index not evaluated
|
|
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
31 31 31 31 31 31 31 993
|
|
32 32 32 32 32 32 32 992
|
|
33 33 33 33 33 33 33 991
|
|
34 34 34 34 34 34 34 990
|
|
35 35 35 35 35 35 35 989
|
|
36 36 36 36 36 36 36 988
|
|
37 37 37 37 37 37 37 987
|
|
38 38 38 38 38 38 38 986
|
|
39 39 39 39 39 39 39 985
|
|
# 3. Check that index_merge doesn't break "ignore/force/use index"
|
|
explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ALL i1 NULL NULL NULL # 55.55 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` IGNORE INDEX (`i2`) where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4))
|
|
explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ref i1,i2,i3 i3 4 const # 55.55 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key3` = 50) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4)))
|
|
explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL # 2.50 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` USE INDEX (`i2`) USE INDEX (`i1`) where ((`test`.`t0`.`key3` = 50) and ((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 3)))
|
|
explain select * from t0 where (key1 > 1 or key2 > 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ALL i1,i2 NULL NULL NULL # 55.55 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` > 1) or (`test`.`t0`.`key2` > 2))
|
|
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` FORCE INDEX (`i2`) FORCE INDEX (`i1`) where ((`test`.`t0`.`key1` > 1) or (`test`.`t0`.`key2` > 2))
|
|
# 4. Check if conjuncts are grouped by keyuse
|
|
explain
|
|
select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or
|
|
(key1>10 and key1<12) or (key2>100 and key2<102);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 3) or ((`test`.`t0`.`key1` > 5) and (`test`.`t0`.`key1` < 7)) or ((`test`.`t0`.`key1` > 10) and (`test`.`t0`.`key1` < 12)) or ((`test`.`t0`.`key2` > 100) and (`test`.`t0`.`key2` < 102)))
|
|
# 5. Check index_merge with conjuncts that are always true/false
|
|
# verify fallback to "range" if there is only one non-confluent condition
|
|
explain select * from t0 where key2 = 45 or key1 <=> null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL range i1,i2 i2 4 NULL # 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 45) or (`test`.`t0`.`key1` <=> NULL))
|
|
explain select * from t0 where key2 = 45 or key1 is not null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ALL i1,i2 NULL NULL NULL # 90.01 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 45) or (`test`.`t0`.`key1` is not null))
|
|
explain select * from t0 where key2 = 45 or key1 is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ref i2 i2 4 const # 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (`test`.`t0`.`key2` = 45)
|
|
# the last conj. is always false and will be discarded
|
|
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i2,i3,i4 i2,i3 4,4 NULL # 100.00 Using union(i2,i3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 10) or (`test`.`t0`.`key3` = 3) or (`test`.`t0`.`key4` <=> NULL))
|
|
# the last conj. is always true and will cause 'all' scan
|
|
explain select * from t0 where key2=10 or key3=3 or key4 is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i2,i3 i2,i3 4,4 NULL # 100.00 Using union(i2,i3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 10) or (`test`.`t0`.`key3` = 3))
|
|
# some more complicated cases
|
|
explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or
|
|
(key3=10) or (key4 <=> null);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i4 i2,i3 4,4 NULL # 100.00 Using sort_union(i2,i3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1` from `test`.`t0` where ((`test`.`t0`.`key1` <=> NULL) or (`test`.`t0`.`key2` < 2) or (`test`.`t0`.`key3` = 10) or (`test`.`t0`.`key4` <=> NULL))
|
|
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
|
|
(key3=10) or (key4 <=> null);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i3,i4 i1,i3 4,4 NULL # 100.00 Using sort_union(i1,i3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1` from `test`.`t0` where ((`test`.`t0`.`key1` <=> NULL) or (`test`.`t0`.`key1` < 5) or (`test`.`t0`.`key3` = 10) or (`test`.`t0`.`key4` <=> NULL))
|
|
# 6.Several ways to do index_merge, (ignored) index_merge vs. range
|
|
explain select * from t0 where
|
|
(key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL # 30.86 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 2)) and ((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key4` < 3)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 5)))
|
|
explain
|
|
select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 4)) and ((`test`.`t0`.`key1` < 5) or (`test`.`t0`.`key3` < 3)))
|
|
select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3);
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 1023
|
|
2 2 2 2 2 2 2 1022
|
|
3 3 3 3 3 3 3 1021
|
|
explain select * from t0 where
|
|
(key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL # 30.86 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 2)) and ((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key4` < 3)) and ((`test`.`t0`.`key5` < 2) or (`test`.`t0`.`key6` < 2)))
|
|
explain select * from t0 where
|
|
(key1 < 3 or key2 < 3) and (key3 < 70);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL range i1,i2,i3 i3 4 NULL # 55.55 Using index condition; Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3)) and (`test`.`t0`.`key3` < 70))
|
|
explain select * from t0 where
|
|
(key1 < 3 or key2 < 3) and (key3 < 1000);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3 i1,i2 4,4 NULL # 98.93 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3)) and (`test`.`t0`.`key3` < 1000))
|
|
# 7. Complex cases
|
|
# tree_or(List<SEL_IMERGE>, range SEL_TREE).
|
|
explain select * from t0 where
|
|
((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3))
|
|
or
|
|
key2 > 4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ALL i1,i2,i3 NULL NULL NULL # 53.90 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3)) and ((`test`.`t0`.`key2` < 4) or (`test`.`t0`.`key3` < 3))) or (`test`.`t0`.`key2` > 4))
|
|
explain select * from t0 where
|
|
((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3))
|
|
or
|
|
key1 < 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key1` < 4) or (`test`.`t0`.`key2` < 4)) and ((`test`.`t0`.`key2` < 4) or (`test`.`t0`.`key3` < 3))) or (`test`.`t0`.`key1` < 5))
|
|
select * from t0 where
|
|
((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3))
|
|
or
|
|
key1 < 5;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 1023
|
|
2 2 2 2 2 2 2 1022
|
|
3 3 3 3 3 3 3 1021
|
|
4 4 4 4 4 4 4 1020
|
|
# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
|
|
explain select * from t0 where
|
|
((key1 < 2 or key2 < 2) and (key3 <4 or key5 < 3))
|
|
or
|
|
((key5 < 3 or key6 < 3) and (key7 <3 or key8 < 3));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL # 100.00 Using sort_union(i1,i2,i5,i6); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 2)) and ((`test`.`t0`.`key3` < 4) or (`test`.`t0`.`key5` < 3))) or (((`test`.`t0`.`key5` < 3) or (`test`.`t0`.`key6` < 3)) and ((`test`.`t0`.`key7` < 3) or (`test`.`t0`.`key8` < 3))))
|
|
explain select * from t0 where
|
|
((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3))
|
|
or
|
|
((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL # 100.00 Using sort_union(i3,i5,i7,i8); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3))) or (((`test`.`t0`.`key7` < 5) or (`test`.`t0`.`key8` < 3)) and ((`test`.`t0`.`key5` < 4) or (`test`.`t0`.`key6` < 4))))
|
|
explain select * from t0 where
|
|
((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4))
|
|
or
|
|
((key3 <4 or key5 < 2) and (key5 < 5 or key6 < 3));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # 100.00 Using sort_union(i3,i5); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4))) or (((`test`.`t0`.`key3` < 4) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 3))))
|
|
explain select * from t0 where
|
|
((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3))
|
|
or
|
|
(((key3 <5 and key7 < 5) or key5 < 2) and (key5 < 4 or key6 < 4));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL # 100.00 Using sort_union(i3,i5); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 4) or (`test`.`t0`.`key5` < 3)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3))) or ((((`test`.`t0`.`key3` < 5) and (`test`.`t0`.`key7` < 5)) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 4) or (`test`.`t0`.`key6` < 4))))
|
|
explain select * from t0 where
|
|
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
|
|
or
|
|
((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ALL i1,i2,i3,i5,i6 NULL NULL NULL # 52.20 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 5) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 4) or (`test`.`t0`.`key2` < 4))) or (((`test`.`t0`.`key3` > 5) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 6))))
|
|
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
|
|
((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3))
|
|
or
|
|
((key3 >4 or key5 < 2) and (key5 < 5 or key6 < 4));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # 100.00 Using sort_union(i3,i5); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` FORCE INDEX (`i6`) FORCE INDEX (`i5`) FORCE INDEX (`i4`) FORCE INDEX (`i3`) FORCE INDEX (`i2`) FORCE INDEX (`i1`) where ((((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3))) or (((`test`.`t0`.`key3` > 4) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 4))))
|
|
# Can't merge any indexes here (predicate on key3 is always true)
|
|
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
|
|
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
|
|
or
|
|
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ALL i1,i2,i3,i5,i6 NULL NULL NULL # 52.20 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` FORCE INDEX (`i6`) FORCE INDEX (`i5`) FORCE INDEX (`i4`) FORCE INDEX (`i3`) FORCE INDEX (`i2`) FORCE INDEX (`i1`) where ((((`test`.`t0`.`key3` < 5) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 4) or (`test`.`t0`.`key2` < 4))) or (((`test`.`t0`.`key3` >= 5) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 6))))
|
|
# 8. Verify that "order by" after index merge uses filesort
|
|
select * from t0 where key1 < 3 or key8 < 2 order by key1;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 1023
|
|
2 2 2 2 2 2 2 1022
|
|
1023 1023 1023 1023 1023 1023 1023 1
|
|
1024 1024 1024 1024 1024 1024 1024 0
|
|
explain
|
|
select * from t0 where key1 < 3 or key8 < 2 order by key1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i8 i1,i8 4,4 NULL # 100.00 Using sort_union(i1,i8); Using where; Using filesort
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key8` < 2)) order by `test`.`t0`.`key1`
|
|
# 9. Check that index_merge cost is compared to 'index' where possible
|
|
create table t2 like t0;
|
|
insert into t2 select * from t0;
|
|
alter table t2 add index i1_3(key1, key3);
|
|
alter table t2 add index i2_3(key2, key3);
|
|
alter table t2 drop index i1;
|
|
alter table t2 drop index i2;
|
|
alter table t2 add index i321(key3, key2, key1);
|
|
# index_merge vs 'index', index_merge is better.
|
|
explain select key3 from t2 where key1 = 100 or key2 = 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # 100.00 Using sort_union(i1_3,i2_3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1` = 100) or (`test`.`t2`.`key2` = 100))
|
|
# index_merge vs 'index', 'index' is better.
|
|
explain select key3 from t2 where key1 <100 or key2 < 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # 100.00 Using sort_union(i1_3,i2_3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1` < 100) or (`test`.`t2`.`key2` < 100))
|
|
# index_merge vs 'all', index_merge is better.
|
|
explain select key7 from t2 where key1 <100 or key2 < 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # 100.00 Using sort_union(i1_3,i2_3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key7` AS `key7` from `test`.`t2` where ((`test`.`t2`.`key1` < 100) or (`test`.`t2`.`key2` < 100))
|
|
# 10. Multipart keys.
|
|
create table t4 (
|
|
key1a int not null,
|
|
key1b int not null,
|
|
key2 int not null,
|
|
key2_1 int not null,
|
|
key2_2 int not null,
|
|
key3 int not null,
|
|
index i1a (key1a, key1b),
|
|
index i1b (key1b, key1a),
|
|
index i2_1(key2, key2_1),
|
|
index i2_2(key2, key2_1)
|
|
);
|
|
Warnings:
|
|
Warning 1831 Duplicate index 'i2_2' defined on the table 'test.t4'. This is deprecated and will be disallowed in a future release.
|
|
insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
|
|
# the following will be handled by index_merge:
|
|
select * from t4 where key1a = 3 or key1b = 4;
|
|
key1a key1b key2 key2_1 key2_2 key3
|
|
3 3 0 3 3 3
|
|
4 4 0 4 4 4
|
|
explain select * from t4 where key1a = 3 or key1b = 4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t4 NULL index_merge i1a,i1b i1a,i1b 4,4 NULL 2 100.00 Using sort_union(i1a,i1b); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key1a` = 3) or (`test`.`t4`.`key1b` = 4))
|
|
# and the following will not
|
|
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t4 NULL ref i2_1,i2_2 i2_1 4 const 1 19.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key2` = 1) and ((`test`.`t4`.`key2_1` = 1) or (`test`.`t4`.`key3` = 5)))
|
|
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t4 NULL ref i2_1,i2_2 i2_1 4 const 1 19.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key2` = 1) and ((`test`.`t4`.`key2_1` = 1) or (`test`.`t4`.`key2_2` = 5)))
|
|
explain select * from t4 where key2_1 = 1 or key2_2 = 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL # 19.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key2_1` = 1) or (`test`.`t4`.`key2_2` = 5))
|
|
# 11. Multitable selects
|
|
create table t1 like t0;
|
|
insert into t1 select * from t0;
|
|
# index_merge on first table in join
|
|
explain select * from t0 left join t1 on (t0.key1=t1.key1)
|
|
where t0.key1=3 or t0.key2=4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL 2 100.00 Using union(i1,i2); Using where
|
|
1 SIMPLE t1 NULL ref i1 i1 4 test.t0.key1 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` left join `test`.`t1` on((`test`.`t1`.`key1` = `test`.`t0`.`key1`)) where ((`test`.`t0`.`key1` = 3) or (`test`.`t0`.`key2` = 4))
|
|
select * from t0 left join t1 on (t0.key1=t1.key1)
|
|
where t0.key1=3 or t0.key2=4;
|
|
key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8
|
|
3 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021
|
|
4 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020
|
|
explain
|
|
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL 2 100.00 Using union(i1,i2); Using where
|
|
1 SIMPLE t1 NULL ref i1 i1 4 test.t0.key1 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t1`.`key1` = `test`.`t0`.`key1`) and ((`test`.`t0`.`key1` = 3) or (`test`.`t0`.`key2` = 4)))
|
|
# index_merge vs. ref
|
|
# index_merge vs. ref
|
|
explain
|
|
select * from t0,t1 where (t0.key1=t1.key1) and
|
|
(t0.key1=3 or t0.key2<4) and t1.key1=2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ref i1,i2 i1 4 const 1 5.00 Using where
|
|
1 SIMPLE t1 NULL ref i1 i1 4 const 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t0`.`key1` = 2) and (`test`.`t1`.`key1` = 2) and (`test`.`t0`.`key2` < 4))
|
|
# index_merge on second table in join
|
|
explain select * from t0,t1 where t0.key1 = 5 and
|
|
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL ref i1 i1 4 const 1 100.00 NULL
|
|
1 SIMPLE t1 NULL index_merge i1,i8 i1,i8 4,4 NULL 2 100.00 Using union(i1,i8); Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t0`.`key1` = 5) and ((`test`.`t1`.`key1` = 5) or (`test`.`t1`.`key8` = 5)))
|
|
# Fix for bug#1974
|
|
explain select * from t0,t1 where t0.key1 < 3 and
|
|
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL range i1 i1 4 NULL # 100.00 Using index condition
|
|
1 SIMPLE t1 NULL ALL i1,i8 NULL NULL NULL # 0.20 Range checked for each record (index map: 0x81)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t0`.`key1` < 3) and ((`test`.`t1`.`key1` = `test`.`t0`.`key1`) or (`test`.`t1`.`key8` = `test`.`t0`.`key1`)))
|
|
# index_merge inside union
|
|
explain select * from t1 where key1=3 or key2=4
|
|
union select * from t1 where key1<4 or key3=5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL index_merge i1,i2 i1,i2 4,4 NULL 2 100.00 Using union(i1,i2); Using where
|
|
2 UNION t1 NULL index_merge i1,i3 i1,i3 4,4 NULL 2 100.00 Using sort_union(i1,i3); Using where
|
|
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` = 3) or (`test`.`t1`.`key2` = 4)) union /* select#2 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` < 4) or (`test`.`t1`.`key3` = 5))
|
|
# index merge in subselect
|
|
explain select * from (select * from t1 where key1 = 3 or key2 =3) as z where key8 >5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge i1,i2,i8 i1,i2 4,4 NULL 2 98.93 Using union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key8` > 5) and ((`test`.`t1`.`key1` = 3) or (`test`.`t1`.`key2` = 3)))
|
|
# 12. check for long index_merges.
|
|
create table t3 like t0;
|
|
insert into t3 select * from t0;
|
|
alter table t3 add key9 int not null, add index i9(key9);
|
|
alter table t3 add keyA int not null, add index iA(keyA);
|
|
alter table t3 add keyB int not null, add index iB(keyB);
|
|
alter table t3 add keyC int not null, add index iC(keyC);
|
|
update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
|
|
explain select * from t3 where
|
|
key1=1 or key2=2 or key3=3 or key4=4 or
|
|
key5=5 or key6=6 or key7=7 or key8=8 or
|
|
key9=9 or keyA=10 or keyB=11 or keyC=12;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 NULL index_merge i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC 4,4,4,4,4,4,4,4,4,4,4,4 NULL 12 100.00 Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t3`.`key1` AS `key1`,`test`.`t3`.`key2` AS `key2`,`test`.`t3`.`key3` AS `key3`,`test`.`t3`.`key4` AS `key4`,`test`.`t3`.`key5` AS `key5`,`test`.`t3`.`key6` AS `key6`,`test`.`t3`.`key7` AS `key7`,`test`.`t3`.`key8` AS `key8`,`test`.`t3`.`key9` AS `key9`,`test`.`t3`.`keyA` AS `keyA`,`test`.`t3`.`keyB` AS `keyB`,`test`.`t3`.`keyC` AS `keyC` from `test`.`t3` where ((`test`.`t3`.`key1` = 1) or (`test`.`t3`.`key2` = 2) or (`test`.`t3`.`key3` = 3) or (`test`.`t3`.`key4` = 4) or (`test`.`t3`.`key5` = 5) or (`test`.`t3`.`key6` = 6) or (`test`.`t3`.`key7` = 7) or (`test`.`t3`.`key8` = 8) or (`test`.`t3`.`key9` = 9) or (`test`.`t3`.`keyA` = 10) or (`test`.`t3`.`keyB` = 11) or (`test`.`t3`.`keyC` = 12))
|
|
select * from t3 where
|
|
key1=1 or key2=2 or key3=3 or key4=4 or
|
|
key5=5 or key6=6 or key7=7 or key8=8 or
|
|
key9=9 or keyA=10 or keyB=11 or keyC=12;
|
|
key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC
|
|
1 1 1 1 1 1 1 1023 1 1 1 1
|
|
2 2 2 2 2 2 2 1022 2 2 2 2
|
|
3 3 3 3 3 3 3 1021 3 3 3 3
|
|
4 4 4 4 4 4 4 1020 4 4 4 4
|
|
5 5 5 5 5 5 5 1019 5 5 5 5
|
|
6 6 6 6 6 6 6 1018 6 6 6 6
|
|
7 7 7 7 7 7 7 1017 7 7 7 7
|
|
9 9 9 9 9 9 9 1015 9 9 9 9
|
|
10 10 10 10 10 10 10 1014 10 10 10 10
|
|
11 11 11 11 11 11 11 1013 11 11 11 11
|
|
12 12 12 12 12 12 12 1012 12 12 12 12
|
|
1016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016
|
|
# Test for Bug#3183
|
|
explain select * from t0 where key1 < 3 or key2 < 4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t0 NULL index_merge i1,i2 i1,i2 4,4 NULL 2 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4))
|
|
# Bug#21277: InnoDB, wrong result set, index_merge strategy,
|
|
# second index not evaluated
|
|
select * from t0 where key1 < 3 or key2 < 4;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 1023
|
|
2 2 2 2 2 2 2 1022
|
|
3 3 3 3 3 3 3 1021
|
|
update t0 set key8=123 where key1 < 3 or key2 < 4;
|
|
# Bug#21277: InnoDB, wrong result set, index_merge strategy,
|
|
# second index not evaluated
|
|
select * from t0 where key1 < 3 or key2 < 4;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 123
|
|
2 2 2 2 2 2 2 123
|
|
3 3 3 3 3 3 3 123
|
|
delete from t0 where key1 < 3 or key2 < 4;
|
|
select * from t0 where key1 < 3 or key2 < 4;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
select count(*) from t0;
|
|
count(*)
|
|
1021
|
|
# Test for BUG#4177
|
|
drop table t4;
|
|
create table t4 (a int);
|
|
insert into t4 values (1),(4),(3);
|
|
set @save_join_buffer_size=@@join_buffer_size;
|
|
set join_buffer_size= 4096;
|
|
explain select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
|
|
where (a.key1 < 500000 or a.key2 < 3)
|
|
and (b.key1 < 500000 or b.key2 < 3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE a NULL index_merge i1,i2 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where
|
|
1 SIMPLE b NULL index_merge i1,i2 i1,i2 4,4 NULL # 100.00 Using sort_union(i1,i2); Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select max((((((((((`test`.`a`.`key1` + `test`.`b`.`key1`) + `test`.`a`.`key2`) + `test`.`b`.`key2`) + `test`.`a`.`key3`) + `test`.`b`.`key3`) + `test`.`a`.`key4`) + `test`.`b`.`key4`) + `test`.`a`.`key5`) + `test`.`b`.`key5`)) AS `max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)` from `test`.`t0` `a` FORCE INDEX (`i2`) FORCE INDEX (`i1`) join `test`.`t0` `b` FORCE INDEX (`i2`) FORCE INDEX (`i1`) where (((`test`.`a`.`key1` < 500000) or (`test`.`a`.`key2` < 3)) and ((`test`.`b`.`key1` < 500000) or (`test`.`b`.`key2` < 3)))
|
|
select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
|
|
where (a.key1 < 500000 or a.key2 < 3)
|
|
and (b.key1 < 500000 or b.key2 < 3);
|
|
max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
10240
|
|
update t0 set key1=1;
|
|
explain select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
|
|
where (a.key1 = 1 or a.key2 = 1)
|
|
and (b.key1 = 1 or b.key2 = 1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE a NULL index_merge i1,i2 i1,i2 4,4 NULL # # Using union(i1,i2); Using where
|
|
1 SIMPLE b NULL index_merge i1,i2 i1,i2 4,4 NULL # # Using union(i1,i2); Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select max((((((((((`test`.`a`.`key1` + `test`.`b`.`key1`) + `test`.`a`.`key2`) + `test`.`b`.`key2`) + `test`.`a`.`key3`) + `test`.`b`.`key3`) + `test`.`a`.`key4`) + `test`.`b`.`key4`) + `test`.`a`.`key5`) + `test`.`b`.`key5`)) AS `max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)` from `test`.`t0` `a` FORCE INDEX (`i2`) FORCE INDEX (`i1`) join `test`.`t0` `b` FORCE INDEX (`i2`) FORCE INDEX (`i1`) where (((`test`.`a`.`key1` = 1) or (`test`.`a`.`key2` = 1)) and ((`test`.`b`.`key1` = 1) or (`test`.`b`.`key2` = 1)))
|
|
select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
|
|
where (a.key1 = 1 or a.key2 = 1)
|
|
and (b.key1 = 1 or b.key2 = 1);
|
|
max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
8194
|
|
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
|
|
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
|
|
# The next query will not use index i7 in intersection if the OS doesn't
|
|
# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index
|
|
# scan cost estimates depend on ha_myisam::ref_length)
|
|
select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
from t0 as a, t0 as b
|
|
where (a.key1 = 1 and a.key2 = 1 and a.key3 = 1 and a.key4=1 and a.key5=1 and a.key6=1 and a.key7 = 1 or a.key8=1)
|
|
and (b.key1 = 1 and b.key2 = 1 and b.key3 = 1 and b.key4=1 and b.key5=1 and b.key6=1 and b.key7 = 1 or b.key8=1);
|
|
max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
|
|
8186
|
|
set join_buffer_size= @save_join_buffer_size;
|
|
# Test for BUG#4177 ends
|
|
drop table t0, t1, t2, t3, t4;
|
|
# BUG#16166
|
|
CREATE TABLE t1 (
|
|
cola char(3) not null, colb char(3) not null, filler char(200),
|
|
key(cola), key(colb)
|
|
);
|
|
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize status OK
|
|
select count(*) from t1;
|
|
count(*)
|
|
8704
|
|
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge cola,colb cola,colb 3,3 NULL # # Using intersect(cola,colb); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))
|
|
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge cola,colb cola,colb 3,3 NULL # # Using intersect(cola,colb); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` FORCE INDEX (`colb`) FORCE INDEX (`cola`) where ((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))
|
|
drop table t1;
|
|
#
|
|
# BUG#20256 - LOCK WRITE - MyISAM
|
|
#
|
|
CREATE TABLE t1(a INT);
|
|
INSERT INTO t1 VALUES(1);
|
|
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
|
|
INSERT INTO t2(a,b) VALUES
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(1,2);
|
|
LOCK TABLES t1 WRITE, t2 WRITE;
|
|
INSERT INTO t2(a,b) VALUES(1,2);
|
|
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
|
|
a
|
|
1
|
|
1
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# BUG#29740: HA_KEY_SCAN_NOT_ROR wasn't set for HEAP engine
|
|
#
|
|
CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`filler` char(200) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
KEY `a` (`a`),
|
|
KEY `b` (`b`)
|
|
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
|
|
insert into t1 values
|
|
(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3),
|
|
(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7),
|
|
(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1),
|
|
(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5),
|
|
(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9),
|
|
(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
|
|
(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17),
|
|
(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0),
|
|
(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4);
|
|
create table t2(
|
|
`a` int(11) DEFAULT NULL,
|
|
`filler` char(200) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
KEY USING BTREE (`a`),
|
|
KEY USING BTREE (`b`)
|
|
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
|
|
insert into t2 select * from t1;
|
|
must use sort-union rather than union:
|
|
explain select * from t1 where a=4 or b=4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge a,b a,b 5,5 NULL # 100.00 Using sort_union(a,b); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`filler` AS `filler`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 4) or (`test`.`t1`.`b` = 4))
|
|
select * from t1 where a=4 or b=4;
|
|
a filler b
|
|
4 4 0
|
|
4 5 0
|
|
4 filler 4
|
|
4 filler 4
|
|
4 qq 5
|
|
4 zz 4
|
|
5 qq 4
|
|
select * from t1 ignore index(a,b) where a=4 or b=4;
|
|
a filler b
|
|
4 4 0
|
|
4 5 0
|
|
4 filler 4
|
|
4 filler 4
|
|
4 qq 5
|
|
4 zz 4
|
|
5 qq 4
|
|
must use union, not sort-union:
|
|
explain select * from t2 where a=4 or b=4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL index_merge a,b a,b 5,5 NULL # 100.00 Using union(a,b); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`filler` AS `filler`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`a` = 4) or (`test`.`t2`.`b` = 4))
|
|
select * from t2 where a=4 or b=4;
|
|
a filler b
|
|
4 4 0
|
|
4 5 0
|
|
4 filler 4
|
|
4 filler 4
|
|
4 qq 5
|
|
4 zz 4
|
|
5 qq 4
|
|
drop table t1, t2;
|
|
#
|
|
# Bug #37943: Reproducible mysqld crash/sigsegv in sel_trees_can_be_ored
|
|
#
|
|
CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'),
|
|
KEY b(b), KEY a(a));
|
|
INSERT INTO t1 VALUES ('y',''), ('z','');
|
|
SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR
|
|
(a='pure-S') OR (a='DE80337a') OR (a='DE80799');
|
|
b a
|
|
y
|
|
z
|
|
DROP TABLE t1;
|
|
#
|
|
# BUG#40974: Incorrect query results when using clause
|
|
# evaluated using range check
|
|
#
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (a int);
|
|
insert into t1 values (1),(2);
|
|
create table t2(a int, b int);
|
|
insert into t2 values (1,1), (2, 1000);
|
|
create table t3 (a int, b int, filler char(100), key(a), key(b));
|
|
insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
|
|
insert into t3 values (1,1,'data');
|
|
insert into t3 values (1,1,'data');
|
|
The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
|
|
explain select * from t1
|
|
where exists (select 1 from t2, t3
|
|
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL # # Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL # # Using where
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL a,b NULL NULL NULL # # Range checked for each record (index map: 0x3)
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(/* select#2 */ select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and ((`test`.`t3`.`a` = `test`.`t2`.`b`) or (`test`.`t3`.`b` = `test`.`t2`.`b`) or (`test`.`t3`.`b` = (`test`.`t2`.`b` + 1)))))
|
|
select * from t1
|
|
where exists (select 1 from t2, t3
|
|
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
|
|
a
|
|
1
|
|
2
|
|
drop table t0, t1, t2, t3;
|
|
#
|
|
# BUG#44810: index merge and order by with low sort_buffer_size
|
|
# crashes server!
|
|
#
|
|
CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B));
|
|
INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128));
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SET SESSION sort_buffer_size=1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect sort_buffer_size value: '1'
|
|
EXPLAIN
|
|
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
|
|
ORDER BY a,b;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge a,b a,b 131,131 NULL # 100.00 Using sort_union(a,b); Using where; Using filesort
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`b`) FORCE INDEX (`a`) where ((`test`.`t1`.`a` like 'a%') or (`test`.`t1`.`b` like 'b%')) order by `test`.`t1`.`a`,`test`.`t1`.`b`
|
|
# we don't actually care about the result : we're checking if it crashes
|
|
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
|
|
ORDER BY a,b;
|
|
SET SESSION sort_buffer_size=DEFAULT;
|
|
DROP TABLE t1;
|
|
End of 5.0 tests
|
|
set global xengine_force_flush_memtable_now=1;
|
|
#---------------- ROR-index_merge tests -----------------------
|
|
# (Start of test file: index_merge_ror.inc)
|
|
SET SESSION DEFAULT_STORAGE_ENGINE = XEngine;
|
|
drop table if exists t0,t1,t2;
|
|
create table t1
|
|
(
|
|
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
|
|
st_a int not null default 0,
|
|
swt1a int not null default 0,
|
|
swt2a int not null default 0,
|
|
st_b int not null default 0,
|
|
swt1b int not null default 0,
|
|
swt2b int not null default 0,
|
|
/* fields/keys for row retrieval tests */
|
|
key1 int,
|
|
key2 int,
|
|
key3 int,
|
|
key4 int,
|
|
/* make rows much bigger then keys */
|
|
filler1 char (200),
|
|
filler2 char (200),
|
|
filler3 char (200),
|
|
filler4 char (200),
|
|
filler5 char (200),
|
|
filler6 char (200),
|
|
/* order of keys is important */
|
|
key sta_swt12a(st_a,swt1a,swt2a),
|
|
key sta_swt1a(st_a,swt1a),
|
|
key sta_swt2a(st_a,swt2a),
|
|
key sta_swt21a(st_a,swt2a,swt1a),
|
|
key st_a(st_a),
|
|
key stb_swt1a_2b(st_b,swt1b,swt2a),
|
|
key stb_swt1b(st_b,swt1b),
|
|
key st_b(st_b),
|
|
key(key1),
|
|
key(key2),
|
|
key(key3),
|
|
key(key4)
|
|
) ;
|
|
create table t0 as select * from t1;
|
|
# Printing of many insert into t0 values (....) disabled.
|
|
alter table t1 disable keys;
|
|
Warnings:
|
|
Note 1031 Table storage engine for 't1' doesn't have this option
|
|
# Printing of many insert into t1 select .... from t0 disabled.
|
|
# Printing of many insert into t1 (...) values (....) disabled.
|
|
alter table t1 enable keys;
|
|
Warnings:
|
|
Note 1031 Table storage engine for 't1' doesn't have this option
|
|
select count(*) from t1;
|
|
count(*)
|
|
64801
|
|
# One row results tests for cases where a single row matches all conditions
|
|
explain select key1,key2 from t1 where key1=100 and key2=100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge key1,key2 key2,key1 5,5 NULL 2 100.00 Using intersect(key2,key1); Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100))
|
|
select key1,key2 from t1 where key1=100 and key2=100;
|
|
key1 key2
|
|
100 100
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 100 100 key1-key2-key3-key4
|
|
# Several-rows results
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
|
|
# ROR-intersection, not covering
|
|
select key1,key2,filler1 from t1 where key1=100 and key2=100;
|
|
key1 key2 filler1
|
|
100 100 key1-key2-key3-key4
|
|
100 100 key1-key2
|
|
# ROR-intersection, covering
|
|
select key1,key2 from t1 where key1=100 and key2=100;
|
|
key1 key2
|
|
100 100
|
|
100 100
|
|
# ROR-union of ROR-intersections
|
|
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4
|
|
100 100 100 100
|
|
100 100 -1 -1
|
|
-1 -1 100 100
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 100 100 key1-key2-key3-key4
|
|
100 100 -1 -1 key1-key2
|
|
-1 -1 100 100 key4-key3
|
|
# 3-way ROR-intersection
|
|
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge key1,key2,key3 key3,key2 5,5 NULL 1 5.00 Using intersect(key3,key2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3` from `test`.`t1` where ((`test`.`t1`.`key3` = 100) and (`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100))
|
|
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
|
|
key1 key2 key3
|
|
100 100 100
|
|
# ROR-union(ROR-intersection, ROR-range)
|
|
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 100 100 key1-key2-key3-key4
|
|
100 100 -1 -1 key1-key2
|
|
101 101 101 101 key1234-101
|
|
# Run some ROR updates/deletes
|
|
select key1,key2, filler1 from t1 where key1=100 and key2=100;
|
|
key1 key2 filler1
|
|
100 100 key1-key2-key3-key4
|
|
100 100 key1-key2
|
|
update t1 set filler1='to be deleted' where key1=100 and key2=100;
|
|
update t1 set key1=200,key2=200 where key1=100 and key2=100;
|
|
delete from t1 where key1=200 and key2=200;
|
|
select key1,key2,filler1 from t1 where key2=100 and key2=200;
|
|
key1 key2 filler1
|
|
# ROR-union(ROR-intersection) with one of ROR-intersection giving empty
|
|
# results
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
-1 -1 100 100 key4-key3
|
|
delete from t1 where key3=100 and key4=100;
|
|
# ROR-union with all ROR-intersections giving empty results
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
# ROR-intersection with empty result
|
|
select key1,key2 from t1 where key1=100 and key2=100;
|
|
key1 key2
|
|
# ROR-union tests with various cases.
|
|
# All scans returning duplicate rows:
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
|
|
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 200 200 key1-key2-key3-key4-1
|
|
100 100 200 200 key1-key2-key3-key4-2
|
|
100 100 200 200 key1-key2-key3-key4-3
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
|
|
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 200 200 key1-key2-key3-key4-1
|
|
100 100 200 200 key1-key2-key3-key4-2
|
|
100 100 200 200 key1-key2-key3-key4-3
|
|
-1 -1 -1 200 key4
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
|
|
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 200 200 key1-key2-key3-key4-1
|
|
100 100 200 200 key1-key2-key3-key4-2
|
|
100 100 200 200 key1-key2-key3-key4-3
|
|
-1 -1 -1 200 key4
|
|
-1 -1 200 -1 key3
|
|
##
|
|
## Optimizer tests
|
|
##
|
|
# Check that the shortest key is used for ROR-intersection, covering and non-covering.
|
|
# Do many tests
|
|
# Check that keys that don't improve selectivity are skipped.
|
|
drop table t0,t1;
|
|
# 'Partially' covered fields test
|
|
create table t2 (
|
|
a char(10),
|
|
b char(10),
|
|
filler1 char(255),
|
|
filler2 char(255),
|
|
key(a(5)),
|
|
key(b(5))
|
|
);
|
|
select count(a) from t2 where a='BBBBBBBB';
|
|
count(a)
|
|
4
|
|
select count(a) from t2 where b='BBBBBBBB';
|
|
count(a)
|
|
4
|
|
# BUG#1:
|
|
expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
|
|
id select_type ta_or_ba_or_ble pa_or_brtitions type possia_or_ble_keys key key_len ref rows filtered Extra_or_b
|
|
1 SIMPLE t2 NULL ref a_or_b,a_or_b a_or_b 6 const 1 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(`test`.`t2`.`a_or_b`) AS `count(a_or_b)` from `test`.`t2` where ((`test`.`t2`.`a_or_b` = 'AAAAAAAA') a_or_bnd (`test`.`t2`.`a_or_b` = 'AAAAAAAA'))
|
|
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
|
|
count(a)
|
|
4
|
|
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
|
|
count(a)
|
|
4
|
|
insert into t2 values ('ab', 'ab', 'uh', 'oh');
|
|
explain select a from t2 where a='ab';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL ref a a 6 const 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a` = 'ab')
|
|
drop table t2;
|
|
#
|
|
# BUG#25048 - ERROR 126 : Incorrect key file for table '.XXXX.MYI';
|
|
# try to repair it
|
|
#
|
|
CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
|
|
KEY(c1), KEY(c2), KEY(c3));
|
|
INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
|
|
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
|
|
INSERT INTO t1 VALUES(0,0,0);
|
|
CREATE TABLE t2(c1 int);
|
|
INSERT INTO t2 VALUES(1);
|
|
DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
|
|
SELECT * FROM t1;
|
|
c1 c2 c3
|
|
DROP TABLE t1,t2;
|
|
set global xengine_force_flush_memtable_now=1;
|
|
#---------------- Index merge test 2 -------------------------------------------
|
|
# (Start of test file: index_merge2.inc)
|
|
SET SESSION DEFAULT_STORAGE_ENGINE = XEngine;
|
|
drop table if exists t1,t2;
|
|
create table t1
|
|
(
|
|
key1 int not null,
|
|
key2 int not null,
|
|
INDEX i1(key1),
|
|
INDEX i2(key2)
|
|
);
|
|
# No primary key
|
|
explain select * from t1 where key1 < 5 or key2 > 197;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge i1,i2 i1,i2 4,4 NULL 2 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key1` < 5) or (`test`.`t1`.`key2` > 197))
|
|
select * from t1 where key1 < 5 or key2 > 197;
|
|
key1 key2
|
|
0 200
|
|
1 199
|
|
2 198
|
|
3 197
|
|
4 196
|
|
explain select * from t1 where key1 < 3 or key2 > 195;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge i1,i2 i1,i2 4,4 NULL 2 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 195))
|
|
select * from t1 where key1 < 3 or key2 > 195;
|
|
key1 key2
|
|
0 200
|
|
1 199
|
|
2 198
|
|
3 197
|
|
4 196
|
|
# Primary key as case-sensitive string with \0s.
|
|
# also make primary key be longer then max. index length of MyISAM.
|
|
alter table t1 add str1 char (255) not null,
|
|
add zeroval int not null default 0,
|
|
add str2 char (255) not null,
|
|
add str3 char (255) not null;
|
|
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
|
|
alter table t1 add primary key (str1, zeroval, str2, str3);
|
|
explain select * from t1 where key1 < 5 or key2 > 197;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL i1,i2 NULL NULL NULL 200 55.55 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`str1` AS `str1`,`test`.`t1`.`zeroval` AS `zeroval`,`test`.`t1`.`str2` AS `str2`,`test`.`t1`.`str3` AS `str3` from `test`.`t1` where ((`test`.`t1`.`key1` < 5) or (`test`.`t1`.`key2` > 197))
|
|
select * from t1 where key1 < 5 or key2 > 197;
|
|
key1 key2 str1 zeroval str2 str3
|
|
4 196 aaa 0 bbb 196-2_a
|
|
3 197 aaa 0 bbb 197-1_A
|
|
2 198 aaa 0 bbb 198-1_a
|
|
1 199 aaa 0 bbb 199-0_A
|
|
0 200 aaa 0 bbb 200-0_a
|
|
explain select * from t1 where key1 < 3 or key2 > 195;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL i1,i2 NULL NULL NULL 200 55.55 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`str1` AS `str1`,`test`.`t1`.`zeroval` AS `zeroval`,`test`.`t1`.`str2` AS `str2`,`test`.`t1`.`str3` AS `str3` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 195))
|
|
select * from t1 where key1 < 3 or key2 > 195;
|
|
key1 key2 str1 zeroval str2 str3
|
|
4 196 aaa 0 bbb 196-2_a
|
|
3 197 aaa 0 bbb 197-1_A
|
|
2 198 aaa 0 bbb 198-1_a
|
|
1 199 aaa 0 bbb 199-0_A
|
|
0 200 aaa 0 bbb 200-0_a
|
|
# Test for BUG#5401
|
|
drop table t1;
|
|
create table t1 (
|
|
pk integer not null auto_increment primary key,
|
|
key1 integer,
|
|
key2 integer not null,
|
|
filler char (200),
|
|
index (key1),
|
|
index (key2)
|
|
);
|
|
show warnings;
|
|
Level Code Message
|
|
explain select pk from t1 where key1 = 1 and key2 = 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,key2 key1 5 const 1 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where ((`test`.`t1`.`key2` = 1) and (`test`.`t1`.`key1` = 1))
|
|
select pk from t1 where key2 = 1 and key1 = 1;
|
|
pk
|
|
26
|
|
27
|
|
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
|
|
pk
|
|
26
|
|
27
|
|
# More tests for BUG#5401.
|
|
drop table t1;
|
|
create table t1 (
|
|
pk int primary key auto_increment,
|
|
key1a int,
|
|
key2a int,
|
|
key1b int,
|
|
key2b int,
|
|
dummy1 int,
|
|
dummy2 int,
|
|
dummy3 int,
|
|
dummy4 int,
|
|
key3a int,
|
|
key3b int,
|
|
filler1 char (200),
|
|
index i1(key1a, key1b),
|
|
index i2(key2a, key2b),
|
|
index i3(key3a, key3b)
|
|
);
|
|
create table t2 (a int);
|
|
insert into t2 values (0),(1),(2),(3),(4),(NULL);
|
|
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
|
|
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
|
|
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
|
|
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
|
|
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
|
|
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
select count(*) from t1;
|
|
count(*)
|
|
5184
|
|
select count(*) from t1 where
|
|
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
|
|
count(*)
|
|
4
|
|
select count(*) from t1 where
|
|
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
|
|
count(*)
|
|
4
|
|
drop table t1,t2;
|
|
# Test for BUG#8441
|
|
create table t1 (
|
|
id1 int,
|
|
id2 date ,
|
|
index idx2 (id1,id2),
|
|
index idx1 (id2)
|
|
);
|
|
insert into t1 values(1,'20040101'), (2,'20040102');
|
|
select * from t1 where id1 = 1 and id2= '20040101';
|
|
id1 id2
|
|
1 2004-01-01
|
|
drop table t1;
|
|
# Test for BUG#12720
|
|
drop view if exists v1;
|
|
CREATE TABLE t1 (
|
|
`oid` int(11) unsigned NOT NULL auto_increment,
|
|
`fk_bbk_niederlassung` int(11) unsigned NOT NULL,
|
|
`fk_wochentag` int(11) unsigned NOT NULL,
|
|
`uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
|
|
`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
|
|
`geloescht` tinyint(4) NOT NULL,
|
|
`version` int(5) NOT NULL,
|
|
PRIMARY KEY (`oid`),
|
|
KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
|
|
KEY `fk_wochentag` (`fk_wochentag`),
|
|
KEY `ix_version` (`version`)
|
|
) DEFAULT CHARSET=latin1;
|
|
insert into t1 values
|
|
(1, 38, 1, '08:00:00', '13:00:00', 0, 1),
|
|
(2, 38, 2, '08:00:00', '13:00:00', 0, 1),
|
|
(3, 38, 3, '08:00:00', '13:00:00', 0, 1),
|
|
(4, 38, 4, '08:00:00', '13:00:00', 0, 1),
|
|
(5, 38, 5, '08:00:00', '13:00:00', 0, 1),
|
|
(6, 38, 5, '08:00:00', '13:00:00', 1, 2),
|
|
(7, 38, 3, '08:00:00', '13:00:00', 1, 2),
|
|
(8, 38, 1, '08:00:00', '13:00:00', 1, 2),
|
|
(9, 38, 2, '08:00:00', '13:00:00', 1, 2),
|
|
(10, 38, 4, '08:00:00', '13:00:00', 1, 2),
|
|
(11, 38, 1, '08:00:00', '13:00:00', 0, 3),
|
|
(12, 38, 2, '08:00:00', '13:00:00', 0, 3),
|
|
(13, 38, 3, '08:00:00', '13:00:00', 0, 3),
|
|
(14, 38, 4, '08:00:00', '13:00:00', 0, 3),
|
|
(15, 38, 5, '08:00:00', '13:00:00', 0, 3),
|
|
(16, 38, 4, '08:00:00', '13:00:00', 0, 4),
|
|
(17, 38, 5, '08:00:00', '13:00:00', 0, 4),
|
|
(18, 38, 1, '08:00:00', '13:00:00', 0, 4),
|
|
(19, 38, 2, '08:00:00', '13:00:00', 0, 4),
|
|
(20, 38, 3, '08:00:00', '13:00:00', 0, 4),
|
|
(21, 7, 1, '08:00:00', '13:00:00', 0, 1),
|
|
(22, 7, 2, '08:00:00', '13:00:00', 0, 1),
|
|
(23, 7, 3, '08:00:00', '13:00:00', 0, 1),
|
|
(24, 7, 4, '08:00:00', '13:00:00', 0, 1),
|
|
(25, 7, 5, '08:00:00', '13:00:00', 0, 1);
|
|
create view v1 as
|
|
select
|
|
zeit1.oid AS oid,
|
|
zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
|
|
zeit1.fk_wochentag AS fk_wochentag,
|
|
zeit1.uhrzeit_von AS uhrzeit_von,
|
|
zeit1.uhrzeit_bis AS uhrzeit_bis,
|
|
zeit1.geloescht AS geloescht,
|
|
zeit1.version AS version
|
|
from
|
|
t1 zeit1
|
|
where
|
|
(zeit1.version =
|
|
(select max(zeit2.version) AS `max(version)`
|
|
from t1 zeit2
|
|
where
|
|
((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
|
|
(zeit1.fk_wochentag = zeit2.fk_wochentag) and
|
|
(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
|
|
(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
|
|
)
|
|
)
|
|
)
|
|
and (zeit1.geloescht = 0);
|
|
select * from v1 where oid = 21;
|
|
oid fk_bbk_niederlassung fk_wochentag uhrzeit_von uhrzeit_bis geloescht version
|
|
21 7 1 08:00:00 13:00:00 0 1
|
|
drop view v1;
|
|
drop table t1;
|
|
CREATE TABLE t1(
|
|
t_cpac varchar(2) NOT NULL,
|
|
t_vers varchar(4) NOT NULL,
|
|
t_rele varchar(2) NOT NULL,
|
|
t_cust varchar(4) NOT NULL,
|
|
filler1 char(250) default NULL,
|
|
filler2 char(250) default NULL,
|
|
PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
|
|
UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
|
|
KEY IX_5 (t_vers,t_rele,t_cust)
|
|
);
|
|
insert into t1 values
|
|
('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''),
|
|
('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''),
|
|
('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''),
|
|
('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''),
|
|
('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''),
|
|
('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''),
|
|
('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''),
|
|
('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''),
|
|
('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''),
|
|
('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''),
|
|
('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''),
|
|
('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''),
|
|
('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''),
|
|
('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
|
|
('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
|
|
('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
|
|
('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
|
|
('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
|
|
('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
|
|
('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
|
|
('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
|
|
('wh','B61U','a ','stnd','','');
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`t_cpac` varchar(2) NOT NULL,
|
|
`t_vers` varchar(4) NOT NULL,
|
|
`t_rele` varchar(2) NOT NULL,
|
|
`t_cust` varchar(4) NOT NULL,
|
|
`filler1` char(250) DEFAULT NULL,
|
|
`filler2` char(250) DEFAULT NULL,
|
|
PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`),
|
|
UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),
|
|
KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=latin1
|
|
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
|
|
t_vers t_rele t_cust filler1
|
|
7.6 a
|
|
7.6 a
|
|
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
|
|
and t_rele='a' and t_cust = ' ';
|
|
t_vers t_rele t_cust filler1
|
|
7.6 a
|
|
7.6 a
|
|
drop table t1;
|
|
# BUG#19021: Crash in index_merge/ROR-intersection optimizer under
|
|
# specific circumstances.
|
|
create table t1 (
|
|
pk int(11) not null auto_increment,
|
|
a int(11) not null default '0',
|
|
b int(11) not null default '0',
|
|
c int(11) not null default '0',
|
|
filler1 datetime, filler2 varchar(15),
|
|
filler3 longtext,
|
|
kp1 varchar(4), kp2 varchar(7),
|
|
kp3 varchar(2), kp4 varchar(4),
|
|
kp5 varchar(7),
|
|
filler4 char(1),
|
|
primary key (pk),
|
|
key idx1(a,b,c),
|
|
key idx2(c),
|
|
key idx3(kp1,kp2,kp3,kp4,kp5)
|
|
) default charset=latin1;
|
|
set @fill=NULL;
|
|
SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
|
|
kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R ';
|
|
COUNT(*)
|
|
1
|
|
drop table t1;
|
|
# BUG#21277: Index Merge/sort_union: wrong query results
|
|
create table t1
|
|
(
|
|
key1 int not null,
|
|
key2 int not null default 0,
|
|
key3 int not null default 0
|
|
);
|
|
insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
|
|
set @d=8;
|
|
insert into t1 (key1) select key1+@d from t1;
|
|
set @d=@d*2;
|
|
insert into t1 (key1) select key1+@d from t1;
|
|
set @d=@d*2;
|
|
insert into t1 (key1) select key1+@d from t1;
|
|
set @d=@d*2;
|
|
insert into t1 (key1) select key1+@d from t1;
|
|
set @d=@d*2;
|
|
insert into t1 (key1) select key1+@d from t1;
|
|
set @d=@d*2;
|
|
insert into t1 (key1) select key1+@d from t1;
|
|
set @d=@d*2;
|
|
insert into t1 (key1) select key1+@d from t1;
|
|
set @d=@d*2;
|
|
alter table t1 add index i2(key2);
|
|
alter table t1 add index i3(key3);
|
|
update t1 set key2=key1,key3=key1;
|
|
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
|
|
key1 key2 key3
|
|
31 31 31
|
|
32 32 32
|
|
33 33 33
|
|
34 34 34
|
|
35 35 35
|
|
36 36 36
|
|
37 37 37
|
|
38 38 38
|
|
39 39 39
|
|
drop table t1;
|
|
#
|
|
# Bug#56423: Different count with SELECT and CREATE SELECT queries
|
|
#
|
|
CREATE TABLE t1 (
|
|
a INT,
|
|
b INT,
|
|
c INT,
|
|
d INT,
|
|
PRIMARY KEY (a),
|
|
KEY (c),
|
|
KEY bd (b,d)
|
|
);
|
|
INSERT INTO t1 VALUES
|
|
(1, 0, 1, 0),
|
|
(2, 1, 1, 1),
|
|
(3, 1, 1, 1),
|
|
(4, 0, 1, 1);
|
|
EXPLAIN
|
|
SELECT a
|
|
FROM t1
|
|
WHERE c = 1 AND b = 1 AND d = 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref c,bd c 5 const 1 75.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`d` = 1) and (`test`.`t1`.`b` = 1) and (`test`.`t1`.`c` = 1))
|
|
CREATE TABLE t2 ( a INT )
|
|
SELECT a
|
|
FROM t1
|
|
WHERE c = 1 AND b = 1 AND d = 1;
|
|
SELECT * FROM t2;
|
|
a
|
|
2
|
|
3
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
|
|
INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
|
|
SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
|
|
a b
|
|
1 2
|
|
1 2
|
|
1 2
|
|
1 2
|
|
DROP TABLE t1;
|
|
# Code coverage of fix.
|
|
CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
|
|
INSERT INTO t1 (b) VALUES (1);
|
|
UPDATE t1 SET b = 2 WHERE a = 1;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 2
|
|
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
|
|
INSERT INTO t2 (b) VALUES ('a');
|
|
UPDATE t2 SET b = 'b' WHERE a = 1;
|
|
SELECT * FROM t2;
|
|
a b
|
|
1 b
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN
|
|
# HANDLER::MULTI_RANGE_READ_INFO_CONST
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INT NOT NULL,
|
|
col_int_key INT NOT NULL,
|
|
col_varchar_key VARCHAR(1) NOT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY col_int_key (col_int_key),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b');
|
|
EXPLAIN
|
|
SELECT col_int_key
|
|
FROM t1
|
|
WHERE col_varchar_key >= 'l' OR
|
|
(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l')
|
|
AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index PRIMARY,col_int_key,col_varchar_key col_varchar_key 7 NULL 2 78.12 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`col_int_key` AS `col_int_key` from `test`.`t1` where ((`test`.`t1`.`col_varchar_key` >= 'l') or (((`test`.`t1`.`pk` between 141 and 141) or (`test`.`t1`.`col_varchar_key` <> 'l')) and ((`test`.`t1`.`pk` between 141 and 141) or (`test`.`t1`.`col_int_key` > 141))))
|
|
SELECT col_int_key
|
|
FROM t1
|
|
WHERE col_varchar_key >= 'l' OR
|
|
(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l')
|
|
AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
|
|
col_int_key
|
|
DROP TABLE t1;
|
|
set global xengine_force_flush_memtable_now=1;
|
|
#---------------- 2-sweeps read Index merge test 2 -------------------------------
|
|
# (Start of test file: index_merge_2sweeps.inc)
|
|
SET SESSION DEFAULT_STORAGE_ENGINE = XEngine;
|
|
drop table if exists t1;
|
|
create table t1 (
|
|
pk int primary key,
|
|
key1 int,
|
|
key2 int,
|
|
filler char(200),
|
|
filler2 char(200),
|
|
index(key1),
|
|
index(key2)
|
|
);
|
|
select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
|
|
pk key1 key2 filler filler2
|
|
10 10 10 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
5 5 5 filler-data filler-data-2
|
|
6 6 6 filler-data filler-data-2
|
|
7 7 7 filler-data filler-data-2
|
|
8 8 8 filler-data filler-data-2
|
|
9 9 9 filler-data filler-data-2
|
|
set @maxv=1000;
|
|
select * from t1 where
|
|
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
|
|
or key1=18 or key1=60;
|
|
pk key1 key2 filler filler2
|
|
1 1 1 filler-data filler-data-2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
18 18 18 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
60 60 60 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
select * from t1 where
|
|
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
|
|
or key1 < 3 or key1 > @maxv-11;
|
|
pk key1 key2 filler filler2
|
|
1 1 1 filler-data filler-data-2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
990 990 990 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
select * from t1 where
|
|
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
|
|
or
|
|
(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
|
|
pk key1 key2 filler filler2
|
|
1 1 1 filler-data filler-data-2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
select * from t1 where
|
|
(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
|
|
or
|
|
(key1 < 5) or (key1 > @maxv-10);
|
|
pk key1 key2 filler filler2
|
|
1 1 1 filler-data filler-data-2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
drop table t1;
|
|
set global xengine_force_flush_memtable_now=1;
|
|
#---------------- Clustered PK ROR-index_merge tests -----------------------------
|
|
# (Start of test file: index_merge_ror_cpk.inc)
|
|
SET SESSION DEFAULT_STORAGE_ENGINE = XEngine;
|
|
drop table if exists t1;
|
|
create table t1
|
|
(
|
|
pk1 int not null,
|
|
pk2 int not null,
|
|
key1 int not null,
|
|
key2 int not null,
|
|
pktail1ok int not null,
|
|
pktail2ok int not null,
|
|
pktail3bad int not null,
|
|
pktail4bad int not null,
|
|
pktail5bad int not null,
|
|
pk2copy int not null,
|
|
badkey int not null,
|
|
filler1 char (200),
|
|
filler2 char (200),
|
|
key (key1),
|
|
key (key2),
|
|
/* keys with tails from CPK members */
|
|
key (pktail1ok, pk1),
|
|
key (pktail2ok, pk1, pk2),
|
|
key (pktail3bad, pk2, pk1),
|
|
key (pktail4bad, pk1, pk2copy),
|
|
key (pktail5bad, pk1, pk2, pk2copy),
|
|
primary key (pk1, pk2)
|
|
);
|
|
# Verify that range scan on CPK is ROR
|
|
# (use index_intersection because it is impossible to check that for index union)
|
|
# Column 9, rows, can change depending on innodb-page-size.
|
|
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref PRIMARY,key1 key1 8 const,const ROWS # Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 0) and (`test`.`t1`.`pk1` = 1) and (`test`.`t1`.`pk2` < 80))
|
|
# CPK scan + 1 ROR range scan is a special case
|
|
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
|
|
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
|
|
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
|
|
1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
|
|
1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
|
|
1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
|
|
1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
|
|
1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
|
|
1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
|
|
1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
|
|
1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
|
|
1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
|
|
# Verify that CPK fields are considered to be covered by index scans
|
|
explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,key2 key1 4 const 1 5.00 Using index condition; Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) and (`test`.`t1`.`key1` = 10) and (((2 * `test`.`t1`.`pk1`) + 1) < <cache>(((2 * 96) + 1))))
|
|
select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
|
|
pk1 pk2
|
|
95 50
|
|
95 51
|
|
95 52
|
|
95 53
|
|
95 54
|
|
95 55
|
|
95 56
|
|
95 57
|
|
95 58
|
|
95 59
|
|
# Verify that CPK is always used for index intersection scans
|
|
# (this is because it is used as a filter, not for retrieval)
|
|
# The expected number of rows can vary depending on page size
|
|
explain select * from t1 where badkey=1 and key1=10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1 key1 4 const ROWS 10.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`badkey` = 1))
|
|
# The expected number of rows can vary depending on page size
|
|
explain select * from t1 where pk1 < 7500 and key1 = 10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge PRIMARY,key1 key1,PRIMARY 8,4 NULL ROWS 100.00 Using intersect(key1,PRIMARY); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pk1` < 7500))
|
|
# Verify that keys with 'tails' of PK members are ok.
|
|
explain select * from t1 where pktail1ok=1 and key1=10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,pktail1ok key1 4 const 1 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail1ok` = 1))
|
|
explain select * from t1 where pktail2ok=1 and key1=10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,pktail2ok key1 4 const 1 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail2ok` = 1))
|
|
# Note: The following is actually a deficiency, it uses sort_union currently.
|
|
# This comment refers to InnoDB and is probably not valid for other engines.
|
|
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL ROWS 100.00 Using sort_union(pktail2ok,key1); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where (((`test`.`t1`.`pktail2ok` = 1) and (`test`.`t1`.`pk1` < 50000)) or (`test`.`t1`.`key1` = 10))
|
|
explain select * from t1 where pktail3bad=1 and key1=10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,pktail3bad EITHER_KEY 4 const ROWS 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail3bad` = 1))
|
|
explain select * from t1 where pktail4bad=1 and key1=10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,pktail4bad key1 4 const ROWS 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail4bad` = 1))
|
|
explain select * from t1 where pktail5bad=1 and key1=10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,pktail5bad key1 4 const ROWS 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail5bad` = 1))
|
|
# Test for problem with innodb key values prefetch buffer:
|
|
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref key1,key2 key1 4 const 1 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) and (`test`.`t1`.`key1` = 10)) limit 10
|
|
select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
|
|
pk1 pk2 key1 key2
|
|
95 50 10 10
|
|
95 51 10 10
|
|
95 52 10 10
|
|
95 53 10 10
|
|
95 54 10 10
|
|
95 55 10 10
|
|
95 56 10 10
|
|
95 57 10 10
|
|
95 58 10 10
|
|
95 59 10 10
|
|
drop table t1;
|
|
# Testcase for BUG#4984
|
|
create table t1
|
|
(
|
|
RUNID varchar(22),
|
|
SUBMITNR varchar(5),
|
|
ORDERNR char(1),
|
|
PROGRAMM varchar(8),
|
|
TESTID varchar(4),
|
|
UCCHECK char(1),
|
|
ETEXT varchar(80),
|
|
ETEXT_TYPE char(1),
|
|
INFO char(1),
|
|
SEVERITY tinyint(3),
|
|
TADIRFLAG char(1),
|
|
PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
|
|
KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
|
|
) DEFAULT CHARSET=latin1;
|
|
update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
|
|
WHERE
|
|
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
|
|
`TESTID`='' AND `UCCHECK`='';
|
|
drop table t1;
|
|
#
|
|
# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
|
|
#
|
|
CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
|
|
INSERT INTO t1 VALUES (2);
|
|
CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
|
|
PRIMARY KEY (f1), KEY (f2), KEY (f3) );
|
|
INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
|
|
SELECT t1.f1 FROM t1
|
|
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
|
|
f1
|
|
2
|
|
EXPLAIN SELECT t1.f1 FROM t1
|
|
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ref f2,f3 f2 5 const 1 75.00 Using where
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select '2' AS `f1` from `test`.`t1` where (((/* select#2 */ select count(0) from `test`.`t2` where ((`test`.`t2`.`f2` = '2') and (`test`.`t2`.`f3` = 'h'))) = 0))
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# Bug#19055268 ASSERT `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET,...) FAILED
|
|
#
|
|
SET sql_mode = '';
|
|
CREATE TABLE t1 (
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
col_int_nokey int(11) NOT NULL,
|
|
col_int_key int(11) NOT NULL,
|
|
col_date_key date NOT NULL,
|
|
col_date_nokey date NOT NULL,
|
|
col_time_key time NOT NULL,
|
|
col_time_nokey time NOT NULL,
|
|
col_datetime_key datetime NOT NULL,
|
|
col_datetime_nokey datetime NOT NULL,
|
|
col_varchar_key varchar(1) NOT NULL,
|
|
col_varchar_nokey varchar(1) NOT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY col_int_key (col_int_key),
|
|
KEY col_date_key (col_date_key),
|
|
KEY col_time_key (col_time_key),
|
|
KEY col_datetime_key (col_datetime_key),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
INSERT INTO t1 VALUES
|
|
(1,2,1,'0000-00-00','0000-00-00','00:00:07','00:00:25','0000-00-00 00:00:00','0000-00-00 00:00:00','2','2'),
|
|
(109,25,1,'0000-00-00','0000-00-00','00:00:07','00:00:25','0000-00-00 00:00:00','0000-00-00 00:00:00','1','7');
|
|
CREATE TABLE t2 (
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
col_int_nokey int(11) NOT NULL,
|
|
col_int_key int(11) NOT NULL,
|
|
col_date_key date NOT NULL,
|
|
col_date_nokey date NOT NULL,
|
|
col_time_key time NOT NULL,
|
|
col_time_nokey time NOT NULL,
|
|
col_datetime_key datetime NOT NULL,
|
|
col_datetime_nokey datetime NOT NULL,
|
|
col_varchar_key varchar(1) NOT NULL,
|
|
col_varchar_nokey varchar(1) NOT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY col_int_key (col_int_key),
|
|
KEY col_date_key (col_date_key),
|
|
KEY col_time_key (col_time_key),
|
|
KEY col_datetime_key (col_datetime_key),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
INSERT INTO t2 VALUES
|
|
(10,2,4,'2000-04-16','2000-04-16','17:02:34','17:02:34','2009-08-04 02:49:10','2009-08-04 02:49:10','u','u'),
|
|
(11,6,5,'1900-01-01','1900-01-01','12:31:32','12:31:32','2004-05-24 22:22:06','2004-05-24 22:22:06','b','b'),
|
|
(12,6,0,'2005-05-14','2005-05-14','16:49:31','16:49:31','1900-01-01 00:00:00','1900-01-01 00:00:00','c','c'),
|
|
(13,94,140,'2001-05-23','2001-05-23','00:00:00','00:00:00','2003-07-28 07:31:49','2003-07-28 07:31:49','f','f'),
|
|
(14,10,230,'2000-06-04','2000-06-04','20:23:27','20:23:27','2000-02-04 21:03:36','2000-02-04 21:03:36','g','g'),
|
|
(15,8,2,'1900-01-01','1900-01-01','10:31:09','10:31:09','2005-05-22 11:48:12','2005-05-22 11:48:12','q','q'),
|
|
(16,43,110,'2008-03-18','2008-03-18','09:58:35','09:58:35','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n'),
|
|
(17,1,8,'2007-02-25','2007-02-25','05:39:28','05:39:28','2005-05-06 00:53:37','2005-05-06 00:53:37','c','c'),
|
|
(18,6,5,'2001-11-22','2001-11-22','00:00:00','00:00:00','2002-08-08 23:43:46','2002-08-08 23:43:46','x','x'),
|
|
(19,5,8,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-10-26 04:07:22','2009-10-26 04:07:22','n','n'),
|
|
(20,1,5,'2001-08-07','2001-08-07','14:06:09','14:06:09','2005-03-12 22:55:23','2005-03-12 22:55:23','r','r'),
|
|
(21,195,70,'2000-08-03','2000-08-03','00:00:00','00:00:00','2008-05-03 22:34:19','2008-05-03 22:34:19','j','j'),
|
|
(22,1,0,'2007-04-21','2007-04-21','17:43:27','17:43:27','2001-10-12 13:30:14','2001-10-12 13:30:14','l','l'),
|
|
(23,0,9,'2000-06-13','2000-06-13','12:23:15','12:23:15','2007-10-07 03:53:06','2007-10-07 03:53:06','h','h'),
|
|
(24,7,3,'0000-00-00','0000-00-00','19:35:06','19:35:06','2005-12-27 20:58:56','2005-12-27 20:58:56','z','z'),
|
|
(25,1,9,'2004-07-08','2004-07-08','10:16:08','10:16:08','2009-05-16 18:44:21','2009-05-16 18:44:21','z','z'),
|
|
(26,4,3,'2003-06-01','2003-06-01','00:00:00','00:00:00','2008-10-02 00:00:00','2008-10-02 00:00:00','d','d'),
|
|
(27,76,22,'0000-00-00','0000-00-00','16:51:15','16:51:15','2001-03-20 00:00:00','2001-03-20 00:00:00','l','l'),
|
|
(28,6,1,'1900-01-01','1900-01-01','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','s','s'),
|
|
(29,7,3,'2007-01-22','2007-01-22','00:00:00','00:00:00','2000-01-10 23:19:37','2000-01-10 23:19:37','k','k'),
|
|
(30,6,3,'2006-10-19','2006-10-19','21:46:20','21:46:20','2008-06-21 16:56:37','2008-06-21 16:56:37','l','l'),
|
|
(31,6,8,'2005-02-27','2005-02-27','00:00:00','00:00:00','2002-03-01 00:34:37','2002-03-01 00:34:37','t','t'),
|
|
(32,1,4,'2001-09-10','2001-09-10','03:34:16','03:34:16','2003-02-05 18:41:00','2003-02-05 18:41:00','n','n'),
|
|
(33,5,1,'2002-09-06','2002-09-06','11:25:31','11:25:31','2004-01-18 13:28:24','2004-01-18 13:28:24','z','z'),
|
|
(34,2,3,'2005-01-15','2005-01-15','17:00:53','17:00:53','2002-12-03 01:06:27','2002-12-03 01:06:27','f','f'),
|
|
(35,1,3,'1900-01-01','1900-01-01','23:32:47','23:32:47','2006-03-03 02:58:59','2006-03-03 02:58:59','q','q'),
|
|
(36,185,63,'2006-12-20','2006-12-20','00:00:00','00:00:00','2002-07-01 10:13:39','2002-07-01 10:13:39','l','l'),
|
|
(37,3,4,'2002-04-17','2002-04-17','12:15:05','12:15:05','2009-01-13 20:47:01','2009-01-13 20:47:01','a','a'),
|
|
(38,7,8,'2004-01-10','2004-01-10','22:26:38','22:26:38','2008-07-18 13:35:29','2008-07-18 13:35:29','c','c'),
|
|
(39,8,6,'2008-03-24','2008-03-24','00:00:00','00:00:00','2001-03-15 02:44:57','2001-03-15 02:44:57','c','c'),
|
|
(40,1,4,'2007-08-18','2007-08-18','00:00:00','00:00:00','2000-06-25 14:03:55','2000-06-25 14:03:55','i','i'),
|
|
(41,3,9,'2005-07-22','2005-07-22','00:00:00','00:00:00','2005-12-13 21:32:03','2005-12-13 21:32:03','r','r'),
|
|
(42,8,2,'2008-07-12','2008-07-12','08:59:37','08:59:37','2009-03-18 19:27:29','2009-03-18 19:27:29','g','g'),
|
|
(43,159,118,'2003-08-18','2003-08-18','07:19:50','07:19:50','2004-04-10 02:50:59','2004-04-10 02:50:59','l','l'),
|
|
(44,112,10,'2002-02-26','2002-02-26','03:51:46','03:51:46','2008-09-27 06:49:19','2008-09-27 06:49:19','w','w'),
|
|
(45,0,9,'2007-07-26','2007-07-26','15:57:32','15:57:32','2006-02-04 15:10:41','2006-02-04 15:10:41','b','b'),
|
|
(46,2,9,'1900-01-01','1900-01-01','02:57:08','02:57:08','2004-01-04 18:45:14','2004-01-04 18:45:14','i','i'),
|
|
(47,1,1,'2000-07-26','2000-07-26','00:21:15','00:21:15','2009-04-04 02:52:09','2009-04-04 02:52:09','o','o'),
|
|
(48,7,6,'2005-03-06','2005-03-06','23:35:02','23:35:02','2001-03-20 12:50:17','2001-03-20 12:50:17','f','f'),
|
|
(49,5,2,'2005-04-16','2005-04-16','09:21:51','09:21:51','2001-11-15 10:51:46','2001-11-15 10:51:46','a','a'),
|
|
(50,2,2,'2004-04-09','2004-04-09','19:21:02','19:21:02','2007-08-13 00:00:00','2007-08-13 00:00:00','j','j'),
|
|
(51,5,3,'2003-05-13','2003-05-13','03:16:22','03:16:22','1900-01-01 00:00:00','1900-01-01 00:00:00','k','k'),
|
|
(52,5,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2008-01-24 08:37:15','2008-01-24 08:37:15','x','x'),
|
|
(53,2,8,'2000-05-27','2000-05-27','18:19:51','18:19:51','2003-03-02 10:18:02','2003-03-02 10:18:02','r','r'),
|
|
(54,2,5,'2004-07-04','2004-07-04','01:42:17','01:42:17','2003-04-25 07:35:13','2003-04-25 07:35:13','t','t'),
|
|
(55,9,0,'2006-03-07','2006-03-07','00:00:00','00:00:00','2006-07-11 23:31:13','2006-07-11 23:31:13','p','p'),
|
|
(56,3,1,'2002-08-17','2002-08-17','20:32:51','20:32:51','2009-08-08 16:55:59','2009-08-08 16:55:59','g','g'),
|
|
(57,2,9,'2003-06-08','2003-06-08','10:04:19','10:04:19','1900-01-01 00:00:00','1900-01-01 00:00:00','q','q'),
|
|
(58,7,4,'1900-01-01','1900-01-01','06:39:26','06:39:26','2000-12-22 10:27:41','2000-12-22 10:27:41','m','m'),
|
|
(59,4,7,'2003-10-09','2003-10-09','15:48:26','15:48:26','2002-07-20 00:00:00','2002-07-20 00:00:00','l','l'),
|
|
(60,5,6,'2007-08-12','2007-08-12','02:17:23','02:17:23','2007-09-24 00:12:11','2007-09-24 00:12:11','d','d'),
|
|
(61,3,3,'2008-12-13','2008-12-13','07:48:47','07:48:47','2009-01-05 06:13:21','2009-01-05 06:13:21','n','n'),
|
|
(62,0,8,'0000-00-00','0000-00-00','09:09:14','09:09:14','2008-11-23 18:13:35','2008-11-23 18:13:35','l','l'),
|
|
(63,1,2,'2003-07-27','2003-07-27','22:46:23','22:46:23','2002-12-19 00:00:00','2002-12-19 00:00:00','c','c'),
|
|
(64,5,5,'2003-04-13','2003-04-13','19:42:42','19:42:42','2003-04-03 16:16:35','2003-04-03 16:16:35','v','v'),
|
|
(65,0,3,'2007-04-25','2007-04-25','15:15:32','15:15:32','2009-04-13 13:44:41','2009-04-13 13:44:41','z','z'),
|
|
(66,4,4,'2000-03-04','2000-03-04','02:50:26','02:50:26','2007-04-19 20:49:24','2007-04-19 20:49:24','h','h'),
|
|
(67,0,9,'2001-08-03','2001-08-03','14:44:31','14:44:31','2002-11-15 23:36:04','2002-11-15 23:36:04','y','y'),
|
|
(68,2,0,'2006-04-13','2006-04-13','00:00:00','00:00:00','2000-05-16 00:00:00','2000-05-16 00:00:00','o','o'),
|
|
(69,2,1,'2006-04-22','2006-04-22','07:31:27','07:31:27','2002-06-08 06:51:27','2002-06-08 06:51:27','h','h'),
|
|
(70,8,5,'2004-08-26','2004-08-26','10:48:57','10:48:57','2006-12-07 01:49:26','2006-12-07 01:49:26','h','h'),
|
|
(71,8,6,'2002-11-26','2002-11-26','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','c','c'),
|
|
(72,5,2,'2002-01-07','2002-01-07','23:06:00','23:06:00','2000-09-27 16:26:23','2000-09-27 16:26:23','m','m'),
|
|
(73,1,0,'2005-05-07','2005-05-07','05:56:02','05:56:02','1900-01-01 00:00:00','1900-01-01 00:00:00','s','s'),
|
|
(74,0,0,'2001-04-21','2001-04-21','10:44:22','10:44:22','2008-02-13 04:28:14','2008-02-13 04:28:14','t','t'),
|
|
(75,7,9,'2000-09-01','2000-09-01','00:00:00','00:00:00','2000-03-10 06:17:52','2000-03-10 06:17:52','r','r'),
|
|
(76,6,8,'2004-07-06','2004-07-06','01:44:26','01:44:26','2003-05-23 00:00:00','2003-05-23 00:00:00','u','u'),
|
|
(77,0,6,'0000-00-00','0000-00-00','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),
|
|
(78,6,0,'2001-02-21','2001-02-21','17:01:49','17:01:49','2003-10-27 12:45:52','2003-10-27 12:45:52','p','p'),
|
|
(79,9,0,'2004-01-28','2004-01-28','16:27:14','16:27:14','2004-08-06 00:00:00','2004-08-06 00:00:00','z','z'),
|
|
(80,5,3,'2008-10-07','2008-10-07','00:00:00','00:00:00','2001-10-05 05:13:39','2001-10-05 05:13:39','h','h'),
|
|
(81,5,5,'0000-00-00','0000-00-00','10:34:22','10:34:22','2001-03-25 04:24:51','2001-03-25 04:24:51','q','q'),
|
|
(82,6,0,'2008-03-04','2008-03-04','10:48:51','10:48:51','2006-08-20 11:54:04','2006-08-20 11:54:04','l','l'),
|
|
(83,1,0,'2009-05-08','2009-05-08','16:55:06','16:55:06','2003-05-16 18:14:52','2003-05-16 18:14:52','p','p'),
|
|
(84,1,1,'2005-03-19','2005-03-19','14:46:32','14:46:32','2009-10-24 13:39:35','2009-10-24 13:39:35','t','t'),
|
|
(85,9,8,'2007-09-14','2007-09-14','16:26:50','16:26:50','2001-08-10 00:00:00','2001-08-10 00:00:00','o','o'),
|
|
(86,249,190,'2007-02-10','2007-02-10','06:35:32','06:35:32','2001-09-06 10:15:08','2001-09-06 10:15:08','o','o'),
|
|
(87,3,0,'2000-06-12','2000-06-12','02:28:38','02:28:38','2006-09-27 00:00:00','2006-09-27 00:00:00','p','p'),
|
|
(88,6,4,'2002-07-06','2002-07-06','00:00:00','00:00:00','2002-02-27 20:12:29','2002-02-27 20:12:29','e','e'),
|
|
(89,7,2,'2001-02-25','2001-02-25','04:52:29','04:52:29','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),
|
|
(90,5,8,'2000-04-27','2000-04-27','00:00:00','00:00:00','2005-08-18 00:00:00','2005-08-18 00:00:00','z','z'),
|
|
(91,4,0,'2005-08-23','2005-08-23','09:11:04','09:11:04','2000-10-15 15:52:54','2000-10-15 15:52:54','j','j'),
|
|
(92,24,137,'2006-01-07','2006-01-07','22:38:18','22:38:18','2007-11-22 13:52:22','2007-11-22 13:52:22','e','e'),
|
|
(93,3,7,'2007-07-08','2007-07-08','13:23:13','13:23:13','2005-09-20 09:09:48','2005-09-20 09:09:48','k','k'),
|
|
(94,179,153,'2000-01-06','2000-01-06','15:33:10','15:33:10','2008-01-11 17:35:34','2008-01-11 17:35:34','d','d'),
|
|
(95,5,7,'0000-00-00','0000-00-00','09:20:10','09:20:10','2002-11-01 00:00:00','2002-11-01 00:00:00','q','q'),
|
|
(96,7,9,'2002-02-22','2002-02-22','11:19:10','11:19:10','2009-06-27 00:00:00','2009-06-27 00:00:00','j','j'),
|
|
(97,7,2,'2001-04-17','2001-04-17','08:08:41','08:08:41','2000-09-25 14:11:58','2000-09-25 14:11:58','j','j'),
|
|
(98,1,0,'2005-06-26','2005-06-26','10:17:36','10:17:36','2004-01-05 04:49:39','2004-01-05 04:49:39','e','e'),
|
|
(99,5,6,'2008-09-14','2008-09-14','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','f','f'),
|
|
(100,7,6,'0000-00-00','0000-00-00','01:05:02','01:05:02','2001-01-17 23:16:24','2001-01-17 23:16:24','a','a'),
|
|
(101,9,2,'2001-12-20','2001-12-20','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','o','o'),
|
|
(102,5,9,'2001-11-05','2001-11-05','00:00:00','00:00:00','2008-07-15 00:00:00','2008-07-15 00:00:00','y','y'),
|
|
(103,4,4,'1900-01-01','1900-01-01','15:32:04','15:32:04','2001-03-06 10:00:37','2001-03-06 10:00:37','d','d'),
|
|
(104,8,7,'2009-02-13','2009-02-13','20:25:36','20:25:36','2004-08-15 00:00:00','2004-08-15 00:00:00','z','z'),
|
|
(105,8,6,'2008-03-25','2008-03-25','21:33:53','21:33:53','2006-10-12 06:13:06','2006-10-12 06:13:06','s','s');
|
|
analyze table t1;
|
|
analyze table t2;
|
|
EXPLAIN UPDATE t1 SET col_time_nokey = 25
|
|
WHERE (col_varchar_key, col_varchar_nokey) IN (
|
|
SELECT col_int_key, col_int_key
|
|
FROM t2
|
|
WHERE (col_time_key, col_int_key, col_int_key) IN (
|
|
('s','a', 'm'), (0, 'n', 7)
|
|
)
|
|
);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL index NULL PRIMARY 4 NULL # # Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL index_subquery col_int_key,col_time_key col_int_key 4 func # # Using where
|
|
UPDATE t1 SET col_time_nokey = 25
|
|
WHERE (col_varchar_key, col_varchar_nokey) IN (
|
|
SELECT col_int_key, col_int_key
|
|
FROM t2
|
|
WHERE (col_time_key, col_int_key, col_int_key) IN (
|
|
('s','a', 'm'), (0, 'n', 7)
|
|
)
|
|
);
|
|
DROP TABLE t1, t2;
|
|
SET sql_mode = default;
|
|
set global xengine_force_flush_memtable_now=1;
|
|
#
|
|
# Bug#11747423 32254: INDEX MERGE USED UNNECESSARILY
|
|
#
|
|
CREATE TABLE t1 (
|
|
id INT NOT NULL PRIMARY KEY,
|
|
id2 INT NOT NULL,
|
|
id3 INT NOT NULL,
|
|
KEY (id2),
|
|
KEY (id3),
|
|
KEY covering_index (id2,id3)
|
|
) ENGINE=XEngine;
|
|
INSERT INTO t1 VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7);
|
|
INSERT INTO t1 SELECT id + 8, id2 + 8, id3 +8 FROM t1;
|
|
INSERT INTO t1 SELECT id + 16, 7, 0 FROM t1;
|
|
EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id2=7 AND id3=0;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref id2,id3,covering_index id2 4 const 1 5.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select sql_no_cache count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`id3` = 0) and (`test`.`t1`.`id2` = 7))
|
|
DROP TABLE t1;
|