~drizzle-trunk/drizzle/development

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