summaryrefslogtreecommitdiff
path: root/mysql-test/t/myisam_icp.test
blob: 7cc35a25e916caebb41abc2d2c543cdd624d0eda (plain)
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
#
# ICP/MyISAM tests (Index Condition Pushdown)
#

--source include/icp_tests.inc

set @myisam_icp_tmp=@@optimizer_switch;
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';

--disable_warnings
drop table if exists t0, t1, t1i, t1m;
--enable_warnings

#
# BUG#711565 Index Condition Pushdown can make a thread hold MyISAM locks as well as be unKILLable for long time
#   This is not a ready mysqltest testcase but rather a set of queries that
#   allow to check the bug[fix] manually. Making this testcase automatic is
#   difficult because
#    - big tables are involved
#    - it's difficult to have automatic checks of whether the query could be
#       KILLed that would reliably work on fast/slow buildslaves, etc.

--disable_parsing
 
  create table t0 (a int);
  insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

  create table t1 (
    kp1 int, kp2 int, 
    filler char(100),
    col int,
    key(kp1, kp2)
  );

  set myisam_sort_buffer_size=32*1000*1000;
  insert into t1 
  select 
    1000 + A.a + 10*B.a + 100*C.a + 1000*D.a + 10000 * F.a,
    1,
    'filler-data filler-data filler-data filler-data filler-data',
    1
  from 
    t0 A, t0 B, t0 C, t0 D, t0 E, t0 F, t0 G
  ;

  insert into t1 values (990, 100, 'a record to test index_next checks',100); 

  update t1 set kp2=10 where kp1 between 20000+100 and 28000;

  update t1 set kp1=20000 where kp1 between 20000 and 28000;

  insert into t1 values (20000, 100, 'last-20K-record',1);

  create table t1i like t1;
  alter table t1i engine=innodb;
  insert into t1i select * from t1;

  create table t1m like t1; 
  alter table t1m engine=maria;
  insert into t1m select * from t1;

#
# XtraDB has one check for all kinds of ranges.
#
  explain 
  select * from t1i
  where 
    kp1 < 8000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 10;


#
# MyISAM, check range access + mi_rnext():
#   (will return HA_ERR_END_OF_FILE)
  explain 
  select * from t1 
  where 
    kp1 < 10000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 10;


#
# MyISAM, check range access + mi_rkey():
#   (will return HA_ERR_END_OF_FILE)
  explain 
  select * from t1 
  where 
    kp1 >= 999 and kp1 < 10000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 10;



#
# MyISAM, check mi_rnext_same:
#

  explain 
  select * from t1 
  where 
    kp1 = 20000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 < 10;


# 
# MyISAM, check mi_rprev:
#

  explain 
  select * from t1 
  where 
    kp1 = 20000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 20
  order by kp1, kp2 desc;



#
# Maria, check range access + maria_rkey():
#
  explain 
  select * from t1m 
  where 
    kp1 >= 999 and kp1 < 10000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 10;



#
# Maria, check range access + maria_rnext():
#
  explain 
  select * from t1m
  where 
    kp1 < 10000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 10;


#
# Maria, check maria_rnext_same:
#

  explain 
  select * from t1m
  where 
    kp1 = 20000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 100;

# 
# Maria, check maria_rprev:
#

  explain 
  select * from t1m
  where 
    kp1 = 20000 and 
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
    kp2 +1 > 20
  order by kp1, kp2 desc;

drop table t0, t1, t1i, t1m;

--enable_parsing
set optimizer_switch=@myisam_icp_tmp;