summaryrefslogtreecommitdiffstats
path: root/mysql-test/include/index_merge_ror.inc
blob: 8a55ef1a67d65c333ffa4ff27581309f764d0b3f (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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
# include/index_merge_ror.inc
#
#  ROR-index_merge tests.
#
# The variable
#     $engine_type       -- storage engine to be tested
# has to be set before sourcing this script.
#
# Note: The comments/expectations refer to MyISAM.
#       They might be not valid for other storage engines.
#
# Last update:
# 2006-08-02 ML test refactored
#               old name was t/index_merge_ror.test
#               main code went into include/index_merge_ror.inc
#

--echo #---------------- ROR-index_merge tests -----------------------

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)
) ;
# Fill table
create table t0 as select * from t1;
--disable_query_log
--echo # Printing of many insert into t0 values (....) disabled.
begin;
let $cnt=1000;
while ($cnt)
{
  eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6');
  dec $cnt;
}
commit;
--enable_query_log

alter table t1 disable keys;
--disable_query_log
--echo # Printing of many insert into t1 select .... from t0 disabled.
let $1=4;
begin;
while ($1)
{
  let $2=4;
  while ($2)
  {
    let $3=4;
    while ($3)
    {
      eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0;
      dec $3;
    }
    dec $2;
  }
 dec $1;
}
commit;

--echo # Printing of many insert into t1 (...) values (....) disabled.
# Row retrieval tests
# -1 is used for values 'out of any range we are using'
# insert enough rows for index intersection to be used for (key1,key2)
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4');
let $cnt=400;
begin;
while ($cnt)
{
  eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3');
  dec $cnt;
}
let $cnt=400;
while ($cnt)
{
  eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4');
  dec $cnt;
}
commit;
--enable_query_log
alter table t1 enable keys;
select count(*) from t1;

# 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;
select key1,key2 from t1 where key1=100 and key2=100;
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

# 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
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
select key1,key2,filler1 from t1 where key1=100 and key2=100;

#  ROR-intersection, covering
explain select key1,key2 from t1 where key1=100 and key2=100;
select key1,key2 from t1 where key1=100 and key2=100;

#  ROR-union of ROR-intersections
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

#  3-way ROR-intersection
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;

#  ROR-union(ROR-intersection, ROR-range)
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;

# Run some ROR updates/deletes
select key1,key2, filler1 from t1 where key1=100 and key2=100;
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;

# ROR-union(ROR-intersection) with one of ROR-intersection giving empty
# results
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

delete from t1 where key3=100 and key4=100;

# ROR-union with all ROR-intersections giving empty results
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

# ROR-intersection with empty result
explain select key1,key2 from t1 where key1=100 and key2=100;
select key1,key2 from t1 where key1=100 and key2=100;

# 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');

explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;

insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');

explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;

insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');

explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;

##
## Optimizer tests
##

# Check that the shortest key is used for ROR-intersection, covering and non-covering.
explain select * from t1 where st_a=1 and st_b=1;
explain select st_a,st_b from t1 where st_a=1 and st_b=1;

# Check if "ingore index" syntax works
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;

# Do many tests
# Check that keys that don't improve selectivity are skipped.
#

# Different value on 32 and 64 bit
--replace_result sta_swt12a sta_swt21a sta_swt12a, sta_swt12a,
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;

explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;

explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

explain select * from t1
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;

explain select * from t1
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;

explain select st_a from t1
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;

explain select st_a from t1
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;

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))
);

--disable_query_log
let $1=8;
begin;
while ($1)
{
  eval insert into t2 values (repeat(char($1+64), 8),repeat(char($1+64), 8),'filler1', 'filler2');
  dec $1;
}
insert into t2 select * from t2;
insert into t2 select * from t2;
commit;
--enable_query_log

# The table row buffer is reused. Fill it with rows that don't match.
select count(a) from t2 where a='BBBBBBBB';
select count(a) from t2 where b='BBBBBBBB';

# BUG#1:
--replace_result a a_or_b b a_or_b
explain select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';

insert into t2 values ('ab', 'ab', 'uh', 'oh');
explain select a from t2 where 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;
DROP TABLE t1,t2;