| blob | 58c587fe5887f5480bb1b8e10675701d12dcf308 |
1 drop table if exists t1,t2;
2 create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam;
3 insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6);
4 explain select * from t1 where a is null;
5 id select_type table type possible_keys key key_len ref rows Extra
6 1 SIMPLE t1 ref a a 5 const 3 Using where; Using index
7 explain select * from t1 where a is null and b = 2;
8 id select_type table type possible_keys key key_len ref rows Extra
9 1 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index
10 explain select * from t1 where a is null and b = 7;
11 id select_type table type possible_keys key key_len ref rows Extra
12 1 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index
13 explain select * from t1 where a=2 and b = 2;
14 id select_type table type possible_keys key key_len ref rows Extra
15 1 SIMPLE t1 const a,b a 9 const,const 1 Using index
16 explain select * from t1 where a<=>b limit 2;
17 id select_type table type possible_keys key key_len ref rows Extra
18 1 SIMPLE t1 index NULL a 9 NULL 12 Using where; Using index
19 explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
20 id select_type table type possible_keys key key_len ref rows Extra
21 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index
22 explain select * from t1 where (a is null or a = 7) and b=7;
23 id select_type table type possible_keys key key_len ref rows Extra
24 1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index
25 explain select * from t1 where (a is null or a = 7) and b=7 order by a;
26 id select_type table type possible_keys key key_len ref rows Extra
27 1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort
28 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
29 id select_type table type possible_keys key key_len ref rows Extra
30 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index
31 explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
32 id select_type table type possible_keys key key_len ref rows Extra
33 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index
34 explain select * from t1 where a > 1 and a < 3 limit 1;
35 id select_type table type possible_keys key key_len ref rows Extra
36 1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index
37 explain select * from t1 where a > 8 and a < 9;
38 id select_type table type possible_keys key key_len ref rows Extra
39 1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index
40 select * from t1 where a is null;
41 a b
42 NULL 7
43 NULL 9
44 NULL 9
45 select * from t1 where a is null and b = 7;
46 a b
47 NULL 7
48 select * from t1 where a<=>b limit 2;
49 a b
50 1 1
51 2 2
52 select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
53 a b
54 1 1
55 2 2
56 select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
57 a b
58 NULL 9
59 NULL 9
60 select * from t1 where (a is null or a = 7) and b=7;
61 a b
62 7 7
63 NULL 7
64 select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
65 a b
66 NULL 7
67 NULL 9
68 NULL 9
69 select * from t1 where a > 1 and a < 3 limit 1;
70 a b
71 2 2
72 select * from t1 where a > 8 and a < 9;
73 a b
74 create table t2 like t1;
75 insert into t2 select * from t1;
76 alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
77 explain select * from t1 where a is null and b = 2;
78 id select_type table type possible_keys key key_len ref rows Extra
79 1 SIMPLE t1 ref a,b a 5 const 3 Using where
80 explain select * from t1 where a is null and b = 2 and c=0;
81 id select_type table type possible_keys key key_len ref rows Extra
82 1 SIMPLE t1 ref a,b a 5 const 3 Using where
83 explain select * from t1 where a is null and b = 7 and c=0;
84 id select_type table type possible_keys key key_len ref rows Extra
85 1 SIMPLE t1 ref a,b a 5 const 3 Using where
86 explain select * from t1 where a=2 and b = 2;
87 id select_type table type possible_keys key key_len ref rows Extra
88 1 SIMPLE t1 ref a,b a 5 const 1 Using where
89 explain select * from t1 where a<=>b limit 2;
90 id select_type table type possible_keys key key_len ref rows Extra
91 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where
92 explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
93 id select_type table type possible_keys key key_len ref rows Extra
94 1 SIMPLE t1 range a,b a 5 NULL 5 Using where
95 explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
96 id select_type table type possible_keys key key_len ref rows Extra
97 1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where
98 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
99 id select_type table type possible_keys key key_len ref rows Extra
100 1 SIMPLE t1 ref a,b a 5 const 3 Using where
101 explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
102 id select_type table type possible_keys key key_len ref rows Extra
103 1 SIMPLE t1 ref a,b a 5 const 3 Using where
104 explain select * from t1 where a > 1 and a < 3 limit 1;
105 id select_type table type possible_keys key key_len ref rows Extra
106 1 SIMPLE t1 range a a 5 NULL 1 Using where
107 explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
108 id select_type table type possible_keys key key_len ref rows Extra
109 1 SIMPLE t1 range a,b a 5 NULL 4 Using where
110 explain select * from t1 where a > 8 and a < 9;
111 id select_type table type possible_keys key key_len ref rows Extra
112 1 SIMPLE t1 range a a 5 NULL 1 Using where
113 explain select * from t1 where b like "6%";
114 id select_type table type possible_keys key key_len ref rows Extra
115 1 SIMPLE t1 range b b 12 NULL 1 Using where
116 select * from t1 where a is null;
117 a b c
118 NULL 7 0
119 NULL 9 0
120 NULL 9 0
121 select * from t1 where a is null and b = 7 and c=0;
122 a b c
123 NULL 7 0
124 select * from t1 where a<=>b limit 2;
125 a b c
126 1 1 0
127 2 2 0
128 select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
129 a b c
130 1 1 0
131 2 2 0
132 select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
133 a b c
134 NULL 9 0
135 NULL 9 0
136 select * from t1 where (a is null or a = 7) and b=7 and c=0;
137 a b c
138 7 7 0
139 NULL 7 0
140 select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
141 a b c
142 NULL 7 0
143 NULL 9 0
144 NULL 9 0
145 select * from t1 where b like "6%";
146 a b c
147 6 6 0
148 drop table t1;
149 rename table t2 to t1;
150 alter table t1 modify b int null;
151 insert into t1 values (7,null), (8,null), (8,7);
152 explain select * from t1 where a = 7 and (b=7 or b is null);
153 id select_type table type possible_keys key key_len ref rows Extra
154 1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index
155 select * from t1 where a = 7 and (b=7 or b is null);
156 a b
157 7 7
158 7 NULL
159 explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
160 id select_type table type possible_keys key key_len ref rows Extra
161 1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where; Using index
162 select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
163 a b
164 7 NULL
165 7 7
166 NULL 7
167 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
168 id select_type table type possible_keys key key_len ref rows Extra
169 1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index
170 select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
171 a b
172 7 NULL
173 7 7
174 NULL 7
175 NULL 9
176 NULL 9
177 create table t2 (a int);
178 insert into t2 values (7),(8);
179 explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
180 id select_type table type possible_keys key key_len ref rows Extra
181 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
182 1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index
183 drop index b on t1;
184 explain select * from t2,t1 where t1.a=t2.a and b is null;
185 id select_type table type possible_keys key key_len ref rows Extra
186 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
187 1 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index
188 select * from t2,t1 where t1.a=t2.a and b is null;
189 a a b
190 7 7 NULL
191 8 8 NULL
192 explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
193 id select_type table type possible_keys key key_len ref rows Extra
194 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
195 1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
196 select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
197 a a b
198 7 7 7
199 7 7 NULL
200 8 8 7
201 8 8 NULL
202 explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
203 id select_type table type possible_keys key key_len ref rows Extra
204 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
205 1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
206 select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
207 a a b
208 7 7 7
209 7 NULL 7
210 8 8 7
211 8 NULL 7
212 explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
213 id select_type table type possible_keys key key_len ref rows Extra
214 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
215 1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
216 select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
217 a a b
218 7 7 NULL
219 7 7 7
220 7 NULL 7
221 8 8 NULL
222 8 8 7
223 8 NULL 7
224 insert into t2 values (null),(6);
225 delete from t1 where a=8;
226 explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
227 id select_type table type possible_keys key key_len ref rows Extra
228 1 SIMPLE t2 ALL NULL NULL NULL NULL 4
229 1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
230 explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
231 id select_type table type possible_keys key key_len ref rows Extra
232 1 SIMPLE t2 ALL NULL NULL NULL NULL 4
233 1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
234 select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
235 a a b
236 7 7 NULL
237 7 7 7
238 7 NULL 7
239 8 NULL 7
240 NULL NULL 7
241 NULL NULL 9
242 NULL NULL 9
243 6 6 6
244 6 NULL 7
245 drop table t1,t2;
246 CREATE TABLE t1 (
247 id int(10) unsigned NOT NULL auto_increment,
248 uniq_id int(10) unsigned default NULL,
249 PRIMARY KEY (id),
250 UNIQUE KEY idx1 (uniq_id)
251 ) ENGINE=MyISAM;
252 CREATE TABLE t2 (
253 id int(10) unsigned NOT NULL auto_increment,
254 uniq_id int(10) unsigned default NULL,
255 PRIMARY KEY (id)
256 ) ENGINE=MyISAM;
257 INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
258 INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
259 explain select id from t1 where uniq_id is null;
260 id select_type table type possible_keys key key_len ref rows Extra
261 1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where
262 explain select id from t1 where uniq_id =1;
263 id select_type table type possible_keys key key_len ref rows Extra
264 1 SIMPLE t1 const idx1 idx1 5 const 1
265 UPDATE t1 SET id=id+100 where uniq_id is null;
266 UPDATE t2 SET id=id+100 where uniq_id is null;
267 select id from t1 where uniq_id is null;
268 id
269 101
270 102
271 105
272 106
273 109
274 110
275 select id from t2 where uniq_id is null;
276 id
277 101
278 102
279 105
280 106
281 109
282 110
283 DELETE FROM t1 WHERE uniq_id IS NULL;
284 DELETE FROM t2 WHERE uniq_id IS NULL;
285 SELECT * FROM t1 ORDER BY uniq_id, id;
286 id uniq_id
287 3 1
288 4 2
289 7 3
290 8 4
291 SELECT * FROM t2 ORDER BY uniq_id, id;
292 id uniq_id
293 3 1
294 4 2
295 7 3
296 8 4
297 DROP table t1,t2;
298 CREATE TABLE `t1` (
299 `order_id` char(32) NOT NULL default '',
300 `product_id` char(32) NOT NULL default '',
301 `product_type` int(11) NOT NULL default '0',
302 PRIMARY KEY (`order_id`,`product_id`,`product_type`)
303 ) ENGINE=MyISAM;
304 CREATE TABLE `t2` (
305 `order_id` char(32) NOT NULL default '',
306 `product_id` char(32) NOT NULL default '',
307 `product_type` int(11) NOT NULL default '0',
308 PRIMARY KEY (`order_id`,`product_id`,`product_type`)
309 ) ENGINE=MyISAM;
310 INSERT INTO t1 (order_id, product_id, product_type) VALUES
311 ('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3),
312 ('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3),
313 ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
314 INSERT INTO t2 (order_id, product_id, product_type) VALUES
315 ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
316 select t1.* from t1
317 left join t2 using(order_id, product_id, product_type)
318 where t2.order_id=NULL;
319 order_id product_id product_type
320 select t1.* from t1
321 left join t2 using(order_id, product_id, product_type)
322 where t2.order_id is NULL;
323 order_id product_id product_type
324 3d7ce39b5d4b3e3d22aaafe9b633de51 1206029 3
325 3d7ce39b5d4b3e3d22aaafe9b633de51 5880836 3
326 drop table t1,t2;
327 create table t1 (id int);
328 insert into t1 values (null), (0);
329 create table t2 (id int);
330 insert into t2 values (null);
331 select * from t1, t2 where t1.id = t2.id;
332 id id
333 alter table t1 add key id (id);
334 select * from t1, t2 where t1.id = t2.id;
335 id id
336 drop table t1,t2;
337 create table t1 (
338 id integer,
339 id2 integer not null,
340 index (id),
341 index (id2)
342 );
343 insert into t1 values(null,null),(1,1);
344 Warnings:
345 Warning 1048 Column 'id2' cannot be null
346 select * from t1;
347 id id2
348 NULL 0
349 1 1
350 select * from t1 where id <=> null;
351 id id2
352 NULL 0
353 select * from t1 where id <=> null or id > 0;
354 id id2
355 NULL 0
356 1 1
357 select * from t1 where id is null or id > 0;
358 id id2
359 NULL 0
360 1 1
361 select * from t1 where id2 <=> null or id2 > 0;
362 id id2
363 1 1
364 select * from t1 where id2 is null or id2 > 0;
365 id id2
366 1 1
367 delete from t1 where id <=> NULL;
368 select * from t1;
369 id id2
370 1 1
371 drop table t1;
372 CREATE TABLE t1 (a int);
373 CREATE TABLE t2 (a int, b int, INDEX idx(a));
374 CREATE TABLE t3 (b int, INDEX idx(b));
375 CREATE TABLE t4 (b int, INDEX idx(b));
376 INSERT INTO t1 VALUES (1), (2), (3), (4);
377 INSERT INTO t2 VALUES (1, 1), (3, 1);
378 INSERT INTO t3 VALUES
379 (NULL), (NULL), (NULL), (NULL), (NULL),
380 (NULL), (NULL), (NULL), (NULL), (NULL);
381 INSERT INTO t4 SELECT * FROM t3;
382 INSERT INTO t3 SELECT * FROM t4;
383 INSERT INTO t4 SELECT * FROM t3;
384 INSERT INTO t3 SELECT * FROM t4;
385 INSERT INTO t4 SELECT * FROM t3;
386 INSERT INTO t3 SELECT * FROM t4;
387 INSERT INTO t4 SELECT * FROM t3;
388 INSERT INTO t3 SELECT * FROM t4;
389 INSERT INTO t4 SELECT * FROM t3;
390 INSERT INTO t3 SELECT * FROM t4;
391 INSERT INTO t4 SELECT * FROM t3;
392 INSERT INTO t3 SELECT * FROM t4;
393 INSERT INTO t4 SELECT * FROM t3;
394 INSERT INTO t3 SELECT * FROM t4;
395 INSERT INTO t4 SELECT * FROM t3;
396 INSERT INTO t3 SELECT * FROM t4;
397 INSERT INTO t3 VALUES (2), (3);
398 ANALYZE table t1, t2, t3;
399 Table Op Msg_type Msg_text
400 test.t1 analyze status OK
401 test.t2 analyze status OK
402 test.t3 analyze status OK
403 SELECT COUNT(*) FROM t3;
404 COUNT(*)
405 15972
406 EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
407 LEFT JOIN t3 ON t2.b=t3.b;
408 id select_type table type possible_keys key key_len ref rows Extra
409 1 SIMPLE t1 ALL NULL NULL NULL NULL 4
410 1 SIMPLE t2 ref idx idx 5 test.t1.a 1
411 1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index
412 FLUSH STATUS ;
413 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
414 LEFT JOIN t3 ON t2.b=t3.b;
415 a a b b
416 1 1 1 NULL
417 2 NULL NULL NULL
418 3 3 1 NULL
419 4 NULL NULL NULL
420 SELECT FOUND_ROWS();
421 FOUND_ROWS()
422 4
423 SHOW STATUS LIKE "handler_read%";
424 Variable_name Value
425 Handler_read_first 0
426 Handler_read_key 6
427 Handler_read_next 2
428 Handler_read_prev 0
429 Handler_read_rnd 0
430 Handler_read_rnd_next 5
431 DROP TABLE t1,t2,t3,t4;
432 CREATE TABLE t1 (
433 a int(11) default NULL,
434 b int(11) default NULL,
435 KEY a (a,b)
436 );
437 INSERT INTO t1 VALUES (0,10),(0,11),(0,12);
438 CREATE TABLE t2 (
439 a int(11) default NULL,
440 b int(11) default NULL,
441 KEY a (a)
442 );
443 INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12);
444 SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b;
445 a b a b
446 3 11 0 11
447 3 12 0 12
448 drop table t1, t2;
449 End of 5.0 tests