1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
#
# Bug #31582383: QUICK_RANGE_SELECT::INIT_ROR_MERGED_SCAN ALWAYS
# USES F_RDLCK
#
CREATE TABLE t1 (
id int auto_increment NOT NULL,
c1 int NOT NULL ,
c2 int NOT NULL,
c3 int NOT NULL,
PRIMARY KEY(id),
KEY c1 (c1),
KEY c2 (c2)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1(c1, c2, c3) VALUES (1, 1, 1);
INSERT INTO t1 (c1, c2, c3) SELECT c1+1, c2+1, c3+1 FROM t1;
INSERT INTO t1 (c1, c2, c3) SELECT c1+8, c2+8, c3+8 FROM t1;
INSERT INTO t1 (c1, c2, c3) VALUES (1, 2, 888);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2 FOR UPDATE;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL index_merge c1,c2 c1,c2 4,4 NULL 1 100.00 Using intersect(c1,c2); Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3` from `test`.`t1` where ((`test`.`t1`.`c2` = 2) and (`test`.`t1`.`c1` = 1))
SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2 FOR UPDATE;
id c1 c2 c3
6 1 2 888
DROP TABLE t1;
#
# Bug#33499071: 'keyread'(only) broken for index-merge access method
#
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, INDEX i1(f1), INDEX i2(f2));
INSERT INTO t1 VALUES (1,1),(2,2);
INSERT INTO t1 SELECT f1+2, f2+2 FROM t1;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE f1 < 3 or f2 > 1020;
EXPLAIN
-> Filter: ((t1.f1 < 3) or (t1.f2 > 1020)) (cost=2.15 rows=3)
-> Sort-deduplicate by row ID (cost=2.15 rows=3)
-> Index range scan on t1 using i1 over (NULL < f1 < 3) (cost=0.46 rows=2)
-> Index range scan on t1 using i2 over (1020 < f2) (cost=0.36 rows=1)
SELECT * FROM t1 WHERE f1 < 3 or f2 > 1020;
f1 f2
1 1
2 2
DROP TABLE t1;
#
# Bug#34826692: Index merge should favour union over sort_union
#
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, key(f1), key(f2, f3), key(f2));
INSERT INTO t1 VALUES (0,1,2);
INSERT INTO t1 VALUES (1,2,3);
INSERT INTO t1 VALUES (2,3,4);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN SELECT * FROM t1 WHERE f1 = 0 OR f2 = 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL index_merge f1,f2,f2_2 f1,f2_2 5,5 NULL 2 100.00 Using union(f1,f2_2); Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where ((`test`.`t1`.`f1` = 0) or (`test`.`t1`.`f2` = 2))
DROP TABLE t1;
#
# Bug#35302794: Segfault in get_best_disjunct_quick on JOIN
# and range predicates
#
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, key(f1), key(f2, f3), key(f2));
INSERT INTO t1 VALUES (0,1,2);
INSERT INTO t1 VALUES (1,2,3);
INSERT INTO t1 VALUES (2,3,4);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='index_merge_sort_union=off') */ *
FROM t1 WHERE f1 = 0 OR f2 = 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL index_merge f1,f2,f2_2 f1,f2_2 5,5 NULL 2 100.00 Using union(f1,f2_2); Using where
Warnings:
Note 1003 /* select#1 */ select /*+ SET_VAR(optimizer_switch='index_merge_sort_union=off') */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where ((`test`.`t1`.`f1` = 0) or (`test`.`t1`.`f2` = 2))
DROP TABLE t1;
|
