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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
# t/index_merge_innodb.test
#
# Index merge tests
#
# Last update:
# 2006-08-07 ML test refactored (MySQL 5.1)
# Main code of several index_merge tests
# -> include/index_merge*.inc
# wrapper t/index_merge_innodb.test sources now several
# include/index_merge*.inc files
#
# Slow test, don't run during staging part
--source include/not_staging.inc
--source include/have_innodb.inc
let $engine_type= InnoDB;
# InnoDB does not support Merge tables (affects include/index_merge1.inc)
let $merge_table_support= 0;
set @optimizer_switch_save= @@optimizer_switch;
set optimizer_switch='index_merge_sort_intersection=off';
# The first two tests are disabled because of non deterministic explain output.
# If include/index_merge1.inc can be enabled for InnoDB and all other
# storage engines, please remove the subtest for Bug#21277 from
# include/index_merge2.inc.
# This test exists already in include/index_merge1.inc.
# --source include/index_merge1.inc
# --source include/index_merge_ror.inc
#the next one is disabled in MySQL too: Bug#45727
--source include/index_merge2.inc
--source include/index_merge_2sweeps.inc
--source include/index_merge_ror_cpk.inc
--echo #
--echo # BUG#56862/640419: Wrong result with sort_union index merge when one
--echo # of the merged index scans is the primary key scan
--echo #
CREATE TABLE t1 (
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
a int,
b int,
INDEX idx(a))
ENGINE=INNODB;
INSERT INTO t1(a,b) VALUES
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1 VALUES (1000000, 0, 0);
SET SESSION sort_buffer_size = 1024*36;
# We have to use FORCE INDEX here as Innodb gives inconsistent estimates
# which causes different query plans.
EXPLAIN
SELECT COUNT(*) FROM
(SELECT * FROM t1 FORCE INDEX(primary,idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
SELECT COUNT(*) FROM
(SELECT * FROM t1 FORCE INDEX(primary,idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
--replace_column 9 #
EXPLAIN
SELECT COUNT(*) FROM
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
SELECT COUNT(*) FROM
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
DROP TABLE t1;
--echo #
--echo # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly
--echo # with Innodb tables
--echo #
CREATE TABLE t1 (
i int(11) DEFAULT NULL,
v1 varchar(1) DEFAULT NULL,
v2 varchar(20) DEFAULT NULL,
KEY i (i),
KEY v (v1,i)
) ENGINE=innodb;
INSERT INTO t1 VALUES (1,'f','no');
INSERT INTO t1 VALUES (2,'u','yes-u');
INSERT INTO t1 VALUES (2,'h','yes-h');
INSERT INTO t1 VALUES (3,'d','no');
--echo
SELECT v2
FROM t1
WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
--echo
--echo # Should not use index_merge
EXPLAIN
SELECT v2
FROM t1
WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
DROP TABLE t1;
set optimizer_switch= @optimizer_switch_save;
|
