~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
drop table if exists t1,t2,t3,t4;
2
set @save_storage_engine= @@storage_engine;
3
set storage_engine=InnoDB;
4
create table t1(a int);
5
show create table t1;
6
Table	Create Table
7
t1	CREATE TABLE "t1" (
8
  "a" int(11)
9
) ENGINE=InnoDB DEFAULT CHARSET=latin1
10
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
11
create table t2(a int);
12
insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
13
create table t3 (
14
a char(8) not null, b char(8) not null, filler char(200),
15
key(a)
16
);
17
insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A;
18
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), 
19
'filler-1' from t2 A;
20
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), 
21
'filler-2' from t2 A;
22
select a,filler from t3 where a >= 'c-9011=w';
23
a	filler
24
select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w';
25
a	filler
26
c-1011=w	filler
27
c-1012=w	filler
28
c-1013=w	filler
29
c-1014=w	filler
30
c-1015=w	filler
31
c-1011=w	filler-1
32
c-1012=w	filler-1
33
c-1013=w	filler-1
34
c-1014=w	filler-1
35
c-1015=w	filler-1
36
c-1011=w	filler-2
37
c-1012=w	filler-2
38
c-1013=w	filler-2
39
c-1014=w	filler-2
40
c-1015=w	filler-2
41
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
42
(a>='c-1014=w' and a <= 'c-1015=w');
43
a	filler
44
c-1011=w	filler
45
c-1012=w	filler
46
c-1013=w	filler
47
c-1014=w	filler
48
c-1015=w	filler
49
c-1011=w	filler-1
50
c-1012=w	filler-1
51
c-1013=w	filler-1
52
c-1014=w	filler-1
53
c-1015=w	filler-1
54
c-1011=w	filler-2
55
c-1012=w	filler-2
56
c-1013=w	filler-2
57
c-1014=w	filler-2
58
c-1015=w	filler-2
59
insert into t3 values ('c-1013=z', 'c-1013=z', 'err');
60
insert into t3 values ('a-1014=w', 'a-1014=w', 'err');
61
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
62
(a>='c-1014=w' and a <= 'c-1015=w');
63
a	filler
64
c-1011=w	filler
65
c-1012=w	filler
66
c-1013=w	filler
67
c-1014=w	filler
68
c-1015=w	filler
69
c-1011=w	filler-1
70
c-1012=w	filler-1
71
c-1013=w	filler-1
72
c-1014=w	filler-1
73
c-1015=w	filler-1
74
c-1011=w	filler-2
75
c-1012=w	filler-2
76
c-1013=w	filler-2
77
c-1014=w	filler-2
78
c-1015=w	filler-2
79
delete from t3 where b in ('c-1013=z', 'a-1014=w');
80
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
81
a='c-1014=w' or a='c-1015=w';
82
a	filler
83
c-1011=w	filler
84
c-1012=w	filler
85
c-1013=w	filler
86
c-1014=w	filler
87
c-1015=w	filler
88
c-1011=w	filler-1
89
c-1012=w	filler-1
90
c-1013=w	filler-1
91
c-1014=w	filler-1
92
c-1015=w	filler-1
93
c-1011=w	filler-2
94
c-1012=w	filler-2
95
c-1013=w	filler-2
96
c-1014=w	filler-2
97
c-1015=w	filler-2
98
insert into t3 values ('c-1013=w', 'del-me', 'inserted');
99
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
100
a='c-1014=w' or a='c-1015=w';
101
a	filler
102
c-1011=w	filler
103
c-1012=w	filler
104
c-1013=w	filler
105
c-1014=w	filler
106
c-1015=w	filler
107
c-1011=w	filler-1
108
c-1012=w	filler-1
109
c-1013=w	filler-1
110
c-1014=w	filler-1
111
c-1015=w	filler-1
112
c-1011=w	filler-2
113
c-1012=w	filler-2
114
c-1013=w	filler-2
115
c-1014=w	filler-2
116
c-1015=w	filler-2
117
c-1013=w	inserted
118
delete from t3 where b='del-me';
119
alter table t3 add primary key(b);
120
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 
121
b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', 
122
'c-1022=w', 'c-1023=w', 'c-1024=w');
123
b	filler
124
c-1011=w	filler
125
c-1012=w	filler
126
c-1013=w	filler
127
c-1014=w	filler
128
c-1015=w	filler
129
c-1016=w	filler
130
c-1017=w	filler
131
c-1018=w	filler
132
c-1019=w	filler
133
c-1020=w	filler
134
c-1021=w	filler
135
c-1022=w	filler
136
c-1023=w	filler
137
c-1024=w	filler
138
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or 
139
b IN ('c-1021=w', 'c-1022=w', 'c-1023=w');
140
b	filler
141
c-1011=w	filler
142
c-1012=w	filler
143
c-1013=w	filler
144
c-1014=w	filler
145
c-1015=w	filler
146
c-1016=w	filler
147
c-1017=w	filler
148
c-1018=w	filler
149
c-1019=w	filler
150
c-1020=w	filler
151
c-1021=w	filler
152
c-1022=w	filler
153
c-1023=w	filler
154
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 
155
b IN ('c-1019=w', 'c-1020=w') or 
156
(b>='c-1021=w' and b<= 'c-1023=w');
157
b	filler
158
c-1011=w	filler
159
c-1012=w	filler
160
c-1013=w	filler
161
c-1014=w	filler
162
c-1015=w	filler
163
c-1016=w	filler
164
c-1017=w	filler
165
c-1018=w	filler
166
c-1019=w	filler
167
c-1020=w	filler
168
c-1021=w	filler
169
c-1022=w	filler
170
c-1023=w	filler
171
create table t4 (a varchar(10), b int, c char(10), filler char(200),
172
key idx1 (a, b, c));
173
insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
174
insert into t4 (a,b,c,filler) 
175
select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15;
176
insert into t4 (a,b,c,filler) 
177
select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15;
178
insert into t4 (a,b,c,filler) 
179
select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15;
180
insert into t4 (a,b,c,filler) 
181
select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
182
explain 
183
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
184
                                                      or c='no-such-row2');
185
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
186
1	SIMPLE	t4	range	idx1	idx1	29	NULL	16	Using index condition; Using MRR
187
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
188
                                                    or c='no-such-row2');
189
a	b	c	filler
190
NULL	NULL	NULL	NULL-15
191
NULL	NULL	NULL	NULL-14
192
NULL	NULL	NULL	NULL-13
193
NULL	NULL	NULL	NULL-12
194
NULL	NULL	NULL	NULL-11
195
NULL	NULL	NULL	NULL-10
196
NULL	NULL	NULL	NULL-9
197
NULL	NULL	NULL	NULL-8
198
NULL	NULL	NULL	NULL-7
199
NULL	NULL	NULL	NULL-6
200
NULL	NULL	NULL	NULL-5
201
NULL	NULL	NULL	NULL-4
202
NULL	NULL	NULL	NULL-3
203
NULL	NULL	NULL	NULL-2
204
NULL	NULL	NULL	NULL-1
205
explain 
206
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
207
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
208
1	SIMPLE	t4	range	idx1	idx1	29	NULL	32	Using index condition; Using MRR
209
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
210
a	b	c	filler
211
b-1	NULL	c-1	NULL-15
212
b-1	NULL	c-1	NULL-14
213
b-1	NULL	c-1	NULL-13
214
b-1	NULL	c-1	NULL-12
215
b-1	NULL	c-1	NULL-11
216
b-1	NULL	c-1	NULL-10
217
b-1	NULL	c-1	NULL-9
218
b-1	NULL	c-1	NULL-8
219
b-1	NULL	c-1	NULL-7
220
b-1	NULL	c-1	NULL-6
221
b-1	NULL	c-1	NULL-5
222
b-1	NULL	c-1	NULL-4
223
b-1	NULL	c-1	NULL-3
224
b-1	NULL	c-1	NULL-2
225
b-1	NULL	c-1	NULL-1
226
bb-1	NULL	cc-2	NULL-15
227
bb-1	NULL	cc-2	NULL-14
228
bb-1	NULL	cc-2	NULL-13
229
bb-1	NULL	cc-2	NULL-12
230
bb-1	NULL	cc-2	NULL-11
231
bb-1	NULL	cc-2	NULL-10
232
bb-1	NULL	cc-2	NULL-9
233
bb-1	NULL	cc-2	NULL-8
234
bb-1	NULL	cc-2	NULL-7
235
bb-1	NULL	cc-2	NULL-6
236
bb-1	NULL	cc-2	NULL-5
237
bb-1	NULL	cc-2	NULL-4
238
bb-1	NULL	cc-2	NULL-3
239
bb-1	NULL	cc-2	NULL-2
240
bb-1	NULL	cc-2	NULL-1
241
select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
242
a	b	c	filler
243
b-1	NULL	c-1	NULL-15
244
b-1	NULL	c-1	NULL-14
245
b-1	NULL	c-1	NULL-13
246
b-1	NULL	c-1	NULL-12
247
b-1	NULL	c-1	NULL-11
248
b-1	NULL	c-1	NULL-10
249
b-1	NULL	c-1	NULL-9
250
b-1	NULL	c-1	NULL-8
251
b-1	NULL	c-1	NULL-7
252
b-1	NULL	c-1	NULL-6
253
b-1	NULL	c-1	NULL-5
254
b-1	NULL	c-1	NULL-4
255
b-1	NULL	c-1	NULL-3
256
b-1	NULL	c-1	NULL-2
257
b-1	NULL	c-1	NULL-1
258
bb-1	NULL	cc-2	NULL-15
259
bb-1	NULL	cc-2	NULL-14
260
bb-1	NULL	cc-2	NULL-13
261
bb-1	NULL	cc-2	NULL-12
262
bb-1	NULL	cc-2	NULL-11
263
bb-1	NULL	cc-2	NULL-10
264
bb-1	NULL	cc-2	NULL-9
265
bb-1	NULL	cc-2	NULL-8
266
bb-1	NULL	cc-2	NULL-7
267
bb-1	NULL	cc-2	NULL-6
268
bb-1	NULL	cc-2	NULL-5
269
bb-1	NULL	cc-2	NULL-4
270
bb-1	NULL	cc-2	NULL-3
271
bb-1	NULL	cc-2	NULL-2
272
bb-1	NULL	cc-2	NULL-1
273
set storage_engine= @save_storage_engine;
274
drop table t1, t2, t3, t4;
275
set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
276
set read_rnd_buffer_size=64;
277
create table t1(a int);
278
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
279
create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) ) engine=InnoDB;
280
insert into t2 select 
281
concat('a-', 1000 + A.a, '-a'),
282
concat('b-', 1000 + B.a, '-b'),
283
concat('c-', 1000 + C.a, '-c'),
284
'filler'
285
from t1 A, t1 B, t1 C;
286
explain
287
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
288
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
289
1	SIMPLE	t2	range	a	a	9	NULL	99	Using index condition; Using MRR
290
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
291
count(length(a) + length(filler))
292
100
293
drop table t2;
294
create table t2 (a char(100), b char(100), c char(100), d int, 
295
filler char(10), key(d), primary key (a,b,c)) engine= innodb;
296
insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B;
297
explain select * from t2 force index (d) where d < 10;
298
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
299
1	SIMPLE	t2	range	d	d	5	NULL	47	Using index condition; Using MRR
300
drop table t2;
301
drop table t1;
302
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
303
create table t1 (f1 int not null, f2 int not null,f3 int not null, f4 char(1), primary key (f1,f2), key ix(f3))Engine=InnoDB;
304
select * from t1 where (f3>=5 and f3<=10) or (f3>=1 and f3<=4);
305
f1	f2	f3	f4
306
1	1	1	A
307
2	2	2	A
308
3	3	3	A
309
4	4	4	A
310
5	5	5	A
311
6	6	6	A
312
7	7	7	A
313
8	8	8	A
314
9	9	9	A
315
10	10	10	A
316
drop table t1;