| blob | 2bdac08e7fd19e3494a6000be56e51a3b67aeefe |
1 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
2 CREATE TABLE t0 (a int, b int, c int);
3 CREATE TABLE t1 (a int, b int, c int);
4 CREATE TABLE t2 (a int, b int, c int);
5 CREATE TABLE t3 (a int, b int, c int);
6 CREATE TABLE t4 (a int, b int, c int);
7 CREATE TABLE t5 (a int, b int, c int);
8 CREATE TABLE t6 (a int, b int, c int);
9 CREATE TABLE t7 (a int, b int, c int);
10 CREATE TABLE t8 (a int, b int, c int);
11 CREATE TABLE t9 (a int, b int, c int);
12 INSERT INTO t0 VALUES (1,1,0), (1,2,0), (2,2,0);
13 INSERT INTO t1 VALUES (1,3,0), (2,2,0), (3,2,0);
14 INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
15 INSERT INTO t3 VALUES (1,2,0), (2,2,0);
16 INSERT INTO t4 VALUES (3,2,0), (4,2,0);
17 INSERT INTO t5 VALUES (3,1,0), (2,2,0), (3,3,0);
18 INSERT INTO t6 VALUES (3,2,0), (6,2,0), (6,1,0);
19 INSERT INTO t7 VALUES (1,1,0), (2,2,0);
20 INSERT INTO t8 VALUES (0,2,0), (1,2,0);
21 INSERT INTO t9 VALUES (1,1,0), (1,2,0), (3,3,0);
22 SELECT t2.a,t2.b
23 FROM t2;
24 a b
25 3 3
26 4 2
27 5 3
28 SELECT t3.a,t3.b
29 FROM t3;
30 a b
31 1 2
32 2 2
33 SELECT t4.a,t4.b
34 FROM t4;
35 a b
36 3 2
37 4 2
38 SELECT t3.a,t3.b,t4.a,t4.b
39 FROM t3,t4;
40 a b a b
41 1 2 3 2
42 2 2 3 2
43 1 2 4 2
44 2 2 4 2
45 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
46 FROM t2
47 LEFT JOIN
48 (t3, t4)
49 ON t2.b=t4.b;
50 a b a b a b
51 3 3 NULL NULL NULL NULL
52 4 2 1 2 3 2
53 4 2 1 2 4 2
54 4 2 2 2 3 2
55 4 2 2 2 4 2
56 5 3 NULL NULL NULL NULL
57 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
58 FROM t2
59 LEFT JOIN
60 (t3, t4)
61 ON t3.a=1 AND t2.b=t4.b;
62 a b a b a b
63 3 3 NULL NULL NULL NULL
64 4 2 1 2 3 2
65 4 2 1 2 4 2
66 5 3 NULL NULL NULL NULL
67 EXPLAIN EXTENDED
68 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
69 FROM t2
70 LEFT JOIN
71 (t3, t4)
72 ON t2.b=t4.b
73 WHERE t3.a=1 OR t3.c IS NULL;
74 id select_type table type possible_keys key key_len ref rows filtered Extra
75 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00
76 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
77 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
78 Warnings:
79 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`))
80 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
81 FROM t2
82 LEFT JOIN
83 (t3, t4)
84 ON t2.b=t4.b
85 WHERE t3.a=1 OR t3.c IS NULL;
86 a b a b a b
87 3 3 NULL NULL NULL NULL
88 4 2 1 2 3 2
89 4 2 1 2 4 2
90 5 3 NULL NULL NULL NULL
91 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
92 FROM t2
93 LEFT JOIN
94 (t3, t4)
95 ON t2.b=t4.b
96 WHERE t3.a>1 OR t3.c IS NULL;
97 a b a b a b
98 3 3 NULL NULL NULL NULL
99 4 2 2 2 3 2
100 4 2 2 2 4 2
101 5 3 NULL NULL NULL NULL
102 SELECT t5.a,t5.b
103 FROM t5;
104 a b
105 3 1
106 2 2
107 3 3
108 SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
109 FROM t3,t4,t5;
110 a b a b a b
111 1 2 3 2 3 1
112 2 2 3 2 3 1
113 1 2 4 2 3 1
114 2 2 4 2 3 1
115 1 2 3 2 2 2
116 2 2 3 2 2 2
117 1 2 4 2 2 2
118 2 2 4 2 2 2
119 1 2 3 2 3 3
120 2 2 3 2 3 3
121 1 2 4 2 3 3
122 2 2 4 2 3 3
123 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
124 FROM t2
125 LEFT JOIN
126 (t3, t4, t5)
127 ON t2.b=t4.b;
128 a b a b a b a b
129 3 3 NULL NULL NULL NULL NULL NULL
130 4 2 1 2 3 2 3 1
131 4 2 1 2 3 2 2 2
132 4 2 1 2 3 2 3 3
133 4 2 1 2 4 2 3 1
134 4 2 1 2 4 2 2 2
135 4 2 1 2 4 2 3 3
136 4 2 2 2 3 2 3 1
137 4 2 2 2 3 2 2 2
138 4 2 2 2 3 2 3 3
139 4 2 2 2 4 2 3 1
140 4 2 2 2 4 2 2 2
141 4 2 2 2 4 2 3 3
142 5 3 NULL NULL NULL NULL NULL NULL
143 EXPLAIN EXTENDED
144 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
145 FROM t2
146 LEFT JOIN
147 (t3, t4, t5)
148 ON t2.b=t4.b
149 WHERE t3.a>1 OR t3.c IS NULL;
150 id select_type table type possible_keys key key_len ref rows filtered Extra
151 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00
152 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
153 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
154 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00
155 Warnings:
156 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`))
157 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
158 FROM t2
159 LEFT JOIN
160 (t3, t4, t5)
161 ON t2.b=t4.b
162 WHERE t3.a>1 OR t3.c IS NULL;
163 a b a b a b a b
164 3 3 NULL NULL NULL NULL NULL NULL
165 4 2 2 2 3 2 3 1
166 4 2 2 2 3 2 2 2
167 4 2 2 2 3 2 3 3
168 4 2 2 2 4 2 3 1
169 4 2 2 2 4 2 2 2
170 4 2 2 2 4 2 3 3
171 5 3 NULL NULL NULL NULL NULL NULL
172 EXPLAIN EXTENDED
173 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
174 FROM t2
175 LEFT JOIN
176 (t3, t4, t5)
177 ON t2.b=t4.b
178 WHERE (t3.a>1 OR t3.c IS NULL) AND
179 (t5.a<3 OR t5.c IS NULL);
180 id select_type table type possible_keys key key_len ref rows filtered Extra
181 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00
182 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
183 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
184 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where
185 Warnings:
186 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`)))
187 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
188 FROM t2
189 LEFT JOIN
190 (t3, t4, t5)
191 ON t2.b=t4.b
192 WHERE (t3.a>1 OR t3.c IS NULL) AND
193 (t5.a<3 OR t5.c IS NULL);
194 a b a b a b a b
195 3 3 NULL NULL NULL NULL NULL NULL
196 4 2 2 2 3 2 2 2
197 4 2 2 2 4 2 2 2
198 5 3 NULL NULL NULL NULL NULL NULL
199 SELECT t6.a,t6.b
200 FROM t6;
201 a b
202 3 2
203 6 2
204 6 1
205 SELECT t7.a,t7.b
206 FROM t7;
207 a b
208 1 1
209 2 2
210 SELECT t6.a,t6.b,t7.a,t7.b
211 FROM t6,t7;
212 a b a b
213 3 2 1 1
214 3 2 2 2
215 6 2 1 1
216 6 2 2 2
217 6 1 1 1
218 6 1 2 2
219 SELECT t8.a,t8.b
220 FROM t8;
221 a b
222 0 2
223 1 2
224 EXPLAIN EXTENDED
225 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
226 FROM (t6, t7)
227 LEFT JOIN
228 t8
229 ON t7.b=t8.b AND t6.b < 10;
230 id select_type table type possible_keys key key_len ref rows filtered Extra
231 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00
232 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer
233 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00
234 Warnings:
235 Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1
236 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
237 FROM (t6, t7)
238 LEFT JOIN
239 t8
240 ON t7.b=t8.b AND t6.b < 10;
241 a b a b a b
242 3 2 1 1 NULL NULL
243 3 2 2 2 0 2
244 3 2 2 2 1 2
245 6 2 1 1 NULL NULL
246 6 2 2 2 0 2
247 6 2 2 2 1 2
248 6 1 1 1 NULL NULL
249 6 1 2 2 0 2
250 6 1 2 2 1 2
251 SELECT t5.a,t5.b
252 FROM t5;
253 a b
254 3 1
255 2 2
256 3 3
257 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
258 FROM t5
259 LEFT JOIN
260 (
261 (t6, t7)
262 LEFT JOIN
263 t8
264 ON t7.b=t8.b AND t6.b < 10
265 )
266 ON t6.b >= 2 AND t5.b=t7.b;
267 a b a b a b a b
268 3 1 3 2 1 1 NULL NULL
269 3 1 6 2 1 1 NULL NULL
270 2 2 3 2 2 2 0 2
271 2 2 3 2 2 2 1 2
272 2 2 6 2 2 2 0 2
273 2 2 6 2 2 2 1 2
274 3 3 NULL NULL NULL NULL NULL NULL
275 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
276 FROM t5
277 LEFT JOIN
278 (
279 (t6, t7)
280 LEFT JOIN
281 t8
282 ON t7.b=t8.b AND t6.b < 10
283 )
284 ON t6.b >= 2 AND t5.b=t7.b AND
285 (t8.a < 1 OR t8.c IS NULL);
286 a b a b a b a b
287 3 1 3 2 1 1 NULL NULL
288 3 1 6 2 1 1 NULL NULL
289 2 2 3 2 2 2 0 2
290 2 2 6 2 2 2 0 2
291 3 3 NULL NULL NULL NULL NULL NULL
292 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
293 FROM t2
294 LEFT JOIN
295 (t3, t4)
296 ON t3.a=1 AND t2.b=t4.b;
297 a b a b a b
298 3 3 NULL NULL NULL NULL
299 4 2 1 2 3 2
300 4 2 1 2 4 2
301 5 3 NULL NULL NULL NULL
302 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
303 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
304 FROM t2
305 LEFT JOIN
306 (t3, t4)
307 ON t3.a=1 AND t2.b=t4.b,
308 t5
309 LEFT JOIN
310 (
311 (t6, t7)
312 LEFT JOIN
313 t8
314 ON t7.b=t8.b AND t6.b < 10
315 )
316 ON t6.b >= 2 AND t5.b=t7.b;
317 a b a b a b a b a b a b a b
318 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
319 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
320 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
321 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
322 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
323 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
324 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
325 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
326 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
327 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
328 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
329 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
330 4 2 1 2 3 2 2 2 3 2 2 2 0 2
331 4 2 1 2 3 2 2 2 3 2 2 2 1 2
332 4 2 1 2 3 2 2 2 6 2 2 2 0 2
333 4 2 1 2 3 2 2 2 6 2 2 2 1 2
334 4 2 1 2 4 2 2 2 3 2 2 2 0 2
335 4 2 1 2 4 2 2 2 3 2 2 2 1 2
336 4 2 1 2 4 2 2 2 6 2 2 2 0 2
337 4 2 1 2 4 2 2 2 6 2 2 2 1 2
338 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
339 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
340 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
341 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
342 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
343 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
344 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
345 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
346 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
347 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
348 FROM t2
349 LEFT JOIN
350 (t3, t4)
351 ON t3.a=1 AND t2.b=t4.b,
352 t5
353 LEFT JOIN
354 (
355 (t6, t7)
356 LEFT JOIN
357 t8
358 ON t7.b=t8.b AND t6.b < 10
359 )
360 ON t6.b >= 2 AND t5.b=t7.b
361 WHERE t2.a > 3 AND
362 (t6.a < 6 OR t6.c IS NULL);
363 a b a b a b a b a b a b a b
364 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
365 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
366 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
367 4 2 1 2 3 2 2 2 3 2 2 2 0 2
368 4 2 1 2 3 2 2 2 3 2 2 2 1 2
369 4 2 1 2 4 2 2 2 3 2 2 2 0 2
370 4 2 1 2 4 2 2 2 3 2 2 2 1 2
371 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
372 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
373 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
374 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
375 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
376 SELECT t1.a,t1.b
377 FROM t1;
378 a b
379 1 3
380 2 2
381 3 2
382 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
383 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
384 FROM t1
385 LEFT JOIN
386 (
387 t2
388 LEFT JOIN
389 (t3, t4)
390 ON t3.a=1 AND t2.b=t4.b,
391 t5
392 LEFT JOIN
393 (
394 (t6, t7)
395 LEFT JOIN
396 t8
397 ON t7.b=t8.b AND t6.b < 10
398 )
399 ON t6.b >= 2 AND t5.b=t7.b
400 )
401 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
402 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
403 (t1.a != 2);
404 a b a b a b a b a b a b a b a b
405 1 3 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
406 1 3 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
407 1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
408 1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
409 1 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
410 1 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
411 1 3 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
412 1 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
413 1 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
414 1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
415 1 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
416 1 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
417 1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
418 1 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
419 1 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
420 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
421 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
422 1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
423 1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
424 1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
425 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
426 3 2 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
427 3 2 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
428 3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
429 3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
430 3 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
431 3 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
432 3 2 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
433 3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
434 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
435 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2
436 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2
437 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2
438 3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2
439 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
440 3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
441 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
442 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2
443 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2
444 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2
445 3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2
446 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
447 3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
448 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
449 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
450 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
451 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
452 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
453 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
454 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
455 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
456 FROM t1
457 LEFT JOIN
458 (
459 t2
460 LEFT JOIN
461 (t3, t4)
462 ON t3.a=1 AND t2.b=t4.b,
463 t5
464 LEFT JOIN
465 (
466 (t6, t7)
467 LEFT JOIN
468 t8
469 ON t7.b=t8.b AND t6.b < 10
470 )
471 ON t6.b >= 2 AND t5.b=t7.b
472 )
473 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
474 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
475 (t1.a != 2)
476 WHERE (t2.a >= 4 OR t2.c IS NULL);
477 a b a b a b a b a b a b a b a b
478 1 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
479 1 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
480 1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
481 1 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
482 1 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
483 1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
484 1 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
485 1 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
486 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
487 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
488 1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
489 1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
490 1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
491 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
492 3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
493 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
494 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2
495 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2
496 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2
497 3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2
498 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
499 3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
500 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
501 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2
502 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2
503 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2
504 3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2
505 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
506 3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
507 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
508 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
509 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
510 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
511 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
512 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
513 SELECT t0.a,t0.b
514 FROM t0;
515 a b
516 1 1
517 1 2
518 2 2
519 EXPLAIN EXTENDED
520 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
521 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
522 FROM t0,t1
523 LEFT JOIN
524 (
525 t2
526 LEFT JOIN
527 (t3, t4)
528 ON t3.a=1 AND t2.b=t4.b,
529 t5
530 LEFT JOIN
531 (
532 (t6, t7)
533 LEFT JOIN
534 t8
535 ON t7.b=t8.b AND t6.b < 10
536 )
537 ON t6.b >= 2 AND t5.b=t7.b
538 )
539 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
540 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
541 (t1.a != 2)
542 WHERE t0.a=1 AND
543 t0.b=t1.b AND
544 (t2.a >= 4 OR t2.c IS NULL);
545 id select_type table type possible_keys key key_len ref rows filtered Extra
546 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
547 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
548 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
549 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
550 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
551 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00
552 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00
553 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00
554 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00
555 Warnings:
556 Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)))
557 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
558 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
559 FROM t0,t1
560 LEFT JOIN
561 (
562 t2
563 LEFT JOIN
564 (t3, t4)
565 ON t3.a=1 AND t2.b=t4.b,
566 t5
567 LEFT JOIN
568 (
569 (t6, t7)
570 LEFT JOIN
571 t8
572 ON t7.b=t8.b AND t6.b < 10
573 )
574 ON t6.b >= 2 AND t5.b=t7.b
575 )
576 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
577 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
578 (t1.a != 2)
579 WHERE t0.a=1 AND
580 t0.b=t1.b AND
581 (t2.a >= 4 OR t2.c IS NULL);
582 a b a b a b a b a b a b a b a b a b
583 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
584 1 2 3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
585 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
586 1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2
587 1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2
588 1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2
589 1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2
590 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
591 1 2 3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
592 1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
593 1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2
594 1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2
595 1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2
596 1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2
597 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
598 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
599 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
600 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
601 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
602 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
603 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
604 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
605 EXPLAIN EXTENDED
606 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
607 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
608 FROM t0,t1
609 LEFT JOIN
610 (
611 t2
612 LEFT JOIN
613 (t3, t4)
614 ON t3.a=1 AND t2.b=t4.b,
615 t5
616 LEFT JOIN
617 (
618 (t6, t7)
619 LEFT JOIN
620 t8
621 ON t7.b=t8.b AND t6.b < 10
622 )
623 ON t6.b >= 2 AND t5.b=t7.b
624 )
625 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
626 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
627 (t1.a != 2),
628 t9
629 WHERE t0.a=1 AND
630 t0.b=t1.b AND
631 (t2.a >= 4 OR t2.c IS NULL) AND
632 (t3.a < 5 OR t3.c IS NULL) AND
633 (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
634 (t5.a >=2 OR t5.c IS NULL) AND
635 (t6.a >=4 OR t6.c IS NULL) AND
636 (t7.a <= 2 OR t7.c IS NULL) AND
637 (t8.a < 1 OR t8.c IS NULL) AND
638 (t8.b=t9.b OR t8.c IS NULL) AND
639 (t9.a=1);
640 id select_type table type possible_keys key key_len ref rows filtered Extra
641 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
642 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
643 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
644 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
645 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where
646 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where
647 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
648 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
649 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where
650 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
651 Warnings:
652 Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
653 SELECT t9.a,t9.b
654 FROM t9;
655 a b
656 1 1
657 1 2
658 3 3
659 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
660 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
661 FROM t0,t1
662 LEFT JOIN
663 (
664 t2
665 LEFT JOIN
666 (t3, t4)
667 ON t3.a=1 AND t2.b=t4.b,
668 t5
669 LEFT JOIN
670 (
671 (t6, t7)
672 LEFT JOIN
673 t8
674 ON t7.b=t8.b AND t6.b < 10
675 )
676 ON t6.b >= 2 AND t5.b=t7.b
677 )
678 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
679 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
680 (t1.a != 2),
681 t9
682 WHERE t0.a=1 AND
683 t0.b=t1.b AND
684 (t2.a >= 4 OR t2.c IS NULL) AND
685 (t3.a < 5 OR t3.c IS NULL) AND
686 (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
687 (t5.a >=2 OR t5.c IS NULL) AND
688 (t6.a >=4 OR t6.c IS NULL) AND
689 (t7.a <= 2 OR t7.c IS NULL) AND
690 (t8.a < 1 OR t8.c IS NULL) AND
691 (t8.b=t9.b OR t8.c IS NULL) AND
692 (t9.a=1);
693 a b a b a b a b a b a b a b a b a b a b
694 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1
695 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1
696 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
697 1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1
698 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
699 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1
700 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1
701 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2
702 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2
703 1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2
704 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
705 1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2
706 1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2
707 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
708 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2
709 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2
710 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2
711 SELECT t1.a,t1.b
712 FROM t1;
713 a b
714 1 3
715 2 2
716 3 2
717 SELECT t2.a,t2.b
718 FROM t2;
719 a b
720 3 3
721 4 2
722 5 3
723 SELECT t3.a,t3.b
724 FROM t3;
725 a b
726 1 2
727 2 2
728 SELECT t2.a,t2.b,t3.a,t3.b
729 FROM t2
730 LEFT JOIN
731 t3
732 ON t2.b=t3.b;
733 a b a b
734 3 3 NULL NULL
735 4 2 1 2
736 4 2 2 2
737 5 3 NULL NULL
738 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
739 FROM t1, t2
740 LEFT JOIN
741 t3
742 ON t2.b=t3.b
743 WHERE t1.a <= 2;
744 a b a b a b
745 1 3 3 3 NULL NULL
746 2 2 3 3 NULL NULL
747 1 3 4 2 1 2
748 1 3 4 2 2 2
749 2 2 4 2 1 2
750 2 2 4 2 2 2
751 1 3 5 3 NULL NULL
752 2 2 5 3 NULL NULL
753 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
754 FROM t1, t3
755 RIGHT JOIN
756 t2
757 ON t2.b=t3.b
758 WHERE t1.a <= 2;
759 a b a b a b
760 1 3 3 3 NULL NULL
761 2 2 3 3 NULL NULL
762 1 3 4 2 1 2
763 1 3 4 2 2 2
764 2 2 4 2 1 2
765 2 2 4 2 2 2
766 1 3 5 3 NULL NULL
767 2 2 5 3 NULL NULL
768 SELECT t3.a,t3.b,t4.a,t4.b
769 FROM t3,t4;
770 a b a b
771 1 2 3 2
772 2 2 3 2
773 1 2 4 2
774 2 2 4 2
775 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
776 FROM t2
777 LEFT JOIN
778 (t3, t4)
779 ON t3.a=1 AND t2.b=t4.b;
780 a b a b a b
781 3 3 NULL NULL NULL NULL
782 4 2 1 2 3 2
783 4 2 1 2 4 2
784 5 3 NULL NULL NULL NULL
785 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
786 FROM t1, t2
787 LEFT JOIN
788 (t3, t4)
789 ON t3.a=1 AND t2.b=t4.b
790 WHERE t1.a <= 2;
791 a b a b a b a b
792 1 3 3 3 NULL NULL NULL NULL
793 2 2 3 3 NULL NULL NULL NULL
794 1 3 4 2 1 2 3 2
795 1 3 4 2 1 2 4 2
796 2 2 4 2 1 2 3 2
797 2 2 4 2 1 2 4 2
798 1 3 5 3 NULL NULL NULL NULL
799 2 2 5 3 NULL NULL NULL NULL
800 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
801 FROM t1, (t3, t4)
802 RIGHT JOIN
803 t2
804 ON t3.a=1 AND t2.b=t4.b
805 WHERE t1.a <= 2;
806 a b a b a b a b
807 1 3 3 3 NULL NULL NULL NULL
808 2 2 3 3 NULL NULL NULL NULL
809 1 3 4 2 1 2 3 2
810 1 3 4 2 1 2 4 2
811 2 2 4 2 1 2 3 2
812 2 2 4 2 1 2 4 2
813 1 3 5 3 NULL NULL NULL NULL
814 2 2 5 3 NULL NULL NULL NULL
815 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
816 FROM t1, (t3, t4)
817 RIGHT JOIN
818 t2
819 ON t3.a=1 AND t2.b=t4.b
820 WHERE t1.a <= 2;
821 a b a b a b a b
822 1 3 3 3 NULL NULL NULL NULL
823 2 2 3 3 NULL NULL NULL NULL
824 1 3 4 2 1 2 3 2
825 1 3 4 2 1 2 4 2
826 2 2 4 2 1 2 3 2
827 2 2 4 2 1 2 4 2
828 1 3 5 3 NULL NULL NULL NULL
829 2 2 5 3 NULL NULL NULL NULL
830 EXPLAIN EXTENDED
831 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
832 FROM t1, (t3, t4)
833 RIGHT JOIN
834 t2
835 ON t3.a=1 AND t2.b=t4.b
836 WHERE t1.a <= 2;
837 id select_type table type possible_keys key key_len ref rows filtered Extra
838 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
839 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer
840 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
841 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
842 Warnings:
843 Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.`a` <= 2)
844 CREATE INDEX idx_b ON t2(b);
845 EXPLAIN EXTENDED
846 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
847 FROM (t3,t4)
848 LEFT JOIN
849 (t1,t2)
850 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
851 id select_type table type possible_keys key key_len ref rows filtered Extra
852 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
853 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
854 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00
855 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
856 Warnings:
857 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1
858 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
859 FROM (t3,t4)
860 LEFT JOIN
861 (t1,t2)
862 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
863 a b a b a b
864 4 2 1 2 3 2
865 4 2 1 2 3 2
866 4 2 1 2 3 2
867 NULL NULL 2 2 3 2
868 4 2 1 2 4 2
869 4 2 1 2 4 2
870 4 2 1 2 4 2
871 NULL NULL 2 2 4 2
872 EXPLAIN EXTENDED
873 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
874 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
875 FROM t0,t1
876 LEFT JOIN
877 (
878 t2
879 LEFT JOIN
880 (t3, t4)
881 ON t3.a=1 AND t2.b=t4.b,
882 t5
883 LEFT JOIN
884 (
885 (t6, t7)
886 LEFT JOIN
887 t8
888 ON t7.b=t8.b AND t6.b < 10
889 )
890 ON t6.b >= 2 AND t5.b=t7.b
891 )
892 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
893 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
894 (t1.a != 2),
895 t9
896 WHERE t0.a=1 AND
897 t0.b=t1.b AND
898 (t2.a >= 4 OR t2.c IS NULL) AND
899 (t3.a < 5 OR t3.c IS NULL) AND
900 (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
901 (t5.a >=2 OR t5.c IS NULL) AND
902 (t6.a >=4 OR t6.c IS NULL) AND
903 (t7.a <= 2 OR t7.c IS NULL) AND
904 (t8.a < 1 OR t8.c IS NULL) AND
905 (t8.b=t9.b OR t8.c IS NULL) AND
906 (t9.a=1);
907 id select_type table type possible_keys key key_len ref rows filtered Extra
908 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
909 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
910 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
911 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
912 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where
913 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where
914 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
915 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
916 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where
917 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
918 Warnings:
919 Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
920 CREATE INDEX idx_b ON t4(b);
921 CREATE INDEX idx_b ON t5(b);
922 EXPLAIN EXTENDED
923 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
924 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
925 FROM t0,t1
926 LEFT JOIN
927 (
928 t2
929 LEFT JOIN
930 (t3, t4)
931 ON t3.a=1 AND t2.b=t4.b,
932 t5
933 LEFT JOIN
934 (
935 (t6, t7)
936 LEFT JOIN
937 t8
938 ON t7.b=t8.b AND t6.b < 10
939 )
940 ON t6.b >= 2 AND t5.b=t7.b
941 )
942 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
943 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
944 (t1.a != 2),
945 t9
946 WHERE t0.a=1 AND
947 t0.b=t1.b AND
948 (t2.a >= 4 OR t2.c IS NULL) AND
949 (t3.a < 5 OR t3.c IS NULL) AND
950 (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
951 (t5.a >=2 OR t5.c IS NULL) AND
952 (t6.a >=4 OR t6.c IS NULL) AND
953 (t7.a <= 2 OR t7.c IS NULL) AND
954 (t8.a < 1 OR t8.c IS NULL) AND
955 (t8.b=t9.b OR t8.c IS NULL) AND
956 (t9.a=1);
957 id select_type table type possible_keys key key_len ref rows filtered Extra
958 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
959 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
960 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
961 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
962 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where
963 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where
964 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
965 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
966 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where
967 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
968 Warnings:
969 Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
970 CREATE INDEX idx_b ON t8(b);
971 EXPLAIN EXTENDED
972 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
973 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
974 FROM t0,t1
975 LEFT JOIN
976 (
977 t2
978 LEFT JOIN
979 (t3, t4)
980 ON t3.a=1 AND t2.b=t4.b,
981 t5
982 LEFT JOIN
983 (
984 (t6, t7)
985 LEFT JOIN
986 t8
987 ON t7.b=t8.b AND t6.b < 10
988 )
989 ON t6.b >= 2 AND t5.b=t7.b
990 )
991 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
992 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
993 (t1.a != 2),
994 t9
995 WHERE t0.a=1 AND
996 t0.b=t1.b AND
997 (t2.a >= 4 OR t2.c IS NULL) AND
998 (t3.a < 5 OR t3.c IS NULL) AND
999 (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
1000 (t5.a >=2 OR t5.c IS NULL) AND
1001 (t6.a >=4 OR t6.c IS NULL) AND
1002 (t7.a <= 2 OR t7.c IS NULL) AND
1003 (t8.a < 1 OR t8.c IS NULL) AND
1004 (t8.b=t9.b OR t8.c IS NULL) AND
1005 (t9.a=1);
1006 id select_type table type possible_keys key key_len ref rows filtered Extra
1007 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
1008 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
1009 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
1010 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1011 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where
1012 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where
1013 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
1014 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
1015 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where
1016 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
1017 Warnings:
1018 Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
1019 CREATE INDEX idx_b ON t1(b);
1020 CREATE INDEX idx_a ON t0(a);
1021 EXPLAIN EXTENDED
1022 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
1023 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
1024 FROM t0,t1
1025 LEFT JOIN
1026 (
1027 t2
1028 LEFT JOIN
1029 (t3, t4)
1030 ON t3.a=1 AND t2.b=t4.b,
1031 t5
1032 LEFT JOIN
1033 (
1034 (t6, t7)
1035 LEFT JOIN
1036 t8
1037 ON t7.b=t8.b AND t6.b < 10
1038 )
1039 ON t6.b >= 2 AND t5.b=t7.b
1040 )
1041 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
1042 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
1043 (t1.a != 2),
1044 t9
1045 WHERE t0.a=1 AND
1046 t0.b=t1.b AND
1047 (t2.a >= 4 OR t2.c IS NULL) AND
1048 (t3.a < 5 OR t3.c IS NULL) AND
1049 (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
1050 (t5.a >=2 OR t5.c IS NULL) AND
1051 (t6.a >=4 OR t6.c IS NULL) AND
1052 (t7.a <= 2 OR t7.c IS NULL) AND
1053 (t8.a < 1 OR t8.c IS NULL) AND
1054 (t8.b=t9.b OR t8.c IS NULL) AND
1055 (t9.a=1);
1056 id select_type table type possible_keys key key_len ref rows filtered Extra
1057 1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where
1058 1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using where
1059 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
1060 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1061 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where
1062 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where
1063 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
1064 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
1065 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where
1066 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
1067 Warnings:
1068 Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
1069 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
1070 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
1071 FROM t0,t1
1072 LEFT JOIN
1073 (
1074 t2
1075 LEFT JOIN
1076 (t3, t4)
1077 ON t3.a=1 AND t2.b=t4.b,
1078 t5
1079 LEFT JOIN
1080 (
1081 (t6, t7)
1082 LEFT JOIN
1083 t8
1084 ON t7.b=t8.b AND t6.b < 10
1085 )
1086 ON t6.b >= 2 AND t5.b=t7.b
1087 )
1088 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
1089 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
1090 (t1.a != 2),
1091 t9
1092 WHERE t0.a=1 AND
1093 t0.b=t1.b AND
1094 (t2.a >= 4 OR t2.c IS NULL) AND
1095 (t3.a < 5 OR t3.c IS NULL) AND
1096 (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
1097 (t5.a >=2 OR t5.c IS NULL) AND
1098 (t6.a >=4 OR t6.c IS NULL) AND
1099 (t7.a <= 2 OR t7.c IS NULL) AND
1100 (t8.a < 1 OR t8.c IS NULL) AND
1101 (t8.b=t9.b OR t8.c IS NULL) AND
1102 (t9.a=1);
1103 a b a b a b a b a b a b a b a b a b a b
1104 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1
1105 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1
1106 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
1107 1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1
1108 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
1109 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1
1110 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1
1111 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2
1112 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2
1113 1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2
1114 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
1115 1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2
1116 1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2
1117 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
1118 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2
1119 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2
1120 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2
1121 SELECT t2.a,t2.b
1122 FROM t2;
1123 a b
1124 3 3
1125 4 2
1126 5 3
1127 SELECT t3.a,t3.b
1128 FROM t3;
1129 a b
1130 1 2
1131 2 2
1132 SELECT t2.a,t2.b,t3.a,t3.b
1133 FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1134 WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);
1135 a b a b
1136 4 2 1 2
1137 4 2 2 2
1138 5 3 NULL NULL
1139 SELECT t2.a,t2.b,t3.a,t3.b
1140 FROM t2 LEFT JOIN (t3) ON t2.b=t3.b
1141 WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);
1142 a b a b
1143 4 2 1 2
1144 4 2 2 2
1145 5 3 NULL NULL
1146 ALTER TABLE t3
1147 CHANGE COLUMN a a1 int,
1148 CHANGE COLUMN c c1 int;
1149 SELECT t2.a,t2.b,t3.a1,t3.b
1150 FROM t2 LEFT JOIN t3 ON t2.b=t3.b
1151 WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
1152 a b a1 b
1153 4 2 1 2
1154 4 2 2 2
1155 5 3 NULL NULL
1156 SELECT t2.a,t2.b,t3.a1,t3.b
1157 FROM t2 NATURAL LEFT JOIN t3
1158 WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
1159 a b a1 b
1160 4 2 1 2
1161 4 2 2 2
1162 5 3 NULL NULL
1163 DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
1164 CREATE TABLE t1 (a int);
1165 CREATE TABLE t2 (a int);
1166 CREATE TABLE t3 (a int);
1167 INSERT INTO t1 VALUES (1);
1168 INSERT INTO t2 VALUES (2);
1169 INSERT INTO t3 VALUES (2);
1170 INSERT INTO t1 VALUES (2);
1171 SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;
1172 a a a
1173 1 NULL NULL
1174 2 2 2
1175 SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
1176 a a a
1177 1 NULL NULL
1178 2 2 2
1179 DELETE FROM t1 WHERE a=2;
1180 SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
1181 a a a
1182 1 NULL NULL
1183 DELETE FROM t2;
1184 SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
1185 a a a
1186 1 NULL NULL
1187 DROP TABLE t1,t2,t3;
1188 CREATE TABLE t1(a int, key (a));
1189 CREATE TABLE t2(b int, key (b));
1190 CREATE TABLE t3(c int, key (c));
1191 INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
1192 (10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
1193 INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
1194 (10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
1195 INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);
1196 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
1197 id select_type table type possible_keys key key_len ref rows Extra
1198 1 SIMPLE t1 index NULL a 5 NULL 21 Using index
1199 1 SIMPLE t3 index c c 5 NULL 6 Using index
1200 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index
1201 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
1202 id select_type table type possible_keys key key_len ref rows Extra
1203 1 SIMPLE t1 index NULL a 5 NULL 21 Using index
1204 1 SIMPLE t3 index c c 5 NULL 6 Using index
1205 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index
1206 SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
1207 a b c
1208 NULL 0 0
1209 NULL 1 1
1210 NULL 2 2
1211 0 0 0
1212 0 1 1
1213 0 2 2
1214 1 0 0
1215 1 1 1
1216 1 2 2
1217 2 0 0
1218 2 1 1
1219 2 2 2
1220 3 0 0
1221 3 1 1
1222 3 2 2
1223 4 0 0
1224 4 1 1
1225 4 2 2
1226 5 0 0
1227 5 1 1
1228 5 2 2
1229 6 0 0
1230 6 1 1
1231 6 2 2
1232 7 0 0
1233 7 1 1
1234 7 2 2
1235 8 0 0
1236 8 1 1
1237 8 2 2
1238 9 0 0
1239 9 1 1
1240 9 2 2
1241 10 0 0
1242 10 1 1
1243 10 2 2
1244 11 0 0
1245 11 1 1
1246 11 2 2
1247 12 0 0
1248 12 1 1
1249 12 2 2
1250 13 0 0
1251 13 1 1
1252 13 2 2
1253 14 0 0
1254 14 1 1
1255 14 2 2
1256 15 0 0
1257 15 1 1
1258 15 2 2
1259 16 0 0
1260 16 1 1
1261 16 2 2
1262 17 0 0
1263 17 1 1
1264 17 2 2
1265 18 0 0
1266 18 1 1
1267 18 2 2
1268 19 0 0
1269 19 1 1
1270 19 2 2
1271 DELETE FROM t3;
1272 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
1273 id select_type table type possible_keys key key_len ref rows Extra
1274 1 SIMPLE t1 index NULL a 5 NULL 21 Using index
1275 1 SIMPLE t3 index c c 5 NULL 0 Using index
1276 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index
1277 SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
1278 a b c
1279 NULL NULL NULL
1280 0 NULL NULL
1281 1 NULL NULL
1282 2 NULL NULL
1283 3 NULL NULL
1284 4 NULL NULL
1285 5 NULL NULL
1286 6 NULL NULL
1287 7 NULL NULL
1288 8 NULL NULL
1289 9 NULL NULL
1290 10 NULL NULL
1291 11 NULL NULL
1292 12 NULL NULL
1293 13 NULL NULL
1294 14 NULL NULL
1295 15 NULL NULL
1296 16 NULL NULL
1297 17 NULL NULL
1298 18 NULL NULL
1299 19 NULL NULL
1300 DROP TABLE t1,t2,t3;
1301 CREATE TABLE t1 (c11 int);
1302 CREATE TABLE t2 (c21 int);
1303 CREATE TABLE t3 (c31 int);
1304 INSERT INTO t1 VALUES (4), (5);
1305 SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
1306 c11 c21
1307 4 NULL
1308 5 NULL
1309 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
1310 id select_type table type possible_keys key key_len ref rows Extra
1311 1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found
1312 1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1313 SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
1314 c11 c21 c31
1315 4 NULL NULL
1316 5 NULL NULL
1317 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
1318 id select_type table type possible_keys key key_len ref rows Extra
1319 1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1320 1 SIMPLE t2 ALL NULL NULL NULL NULL 0
1321 1 SIMPLE t3 ALL NULL NULL NULL NULL 0
1322 DROP TABLE t1,t2,t3;
1323 CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
1324 INSERT INTO t1 VALUES (23, 2340), (26, 9900);
1325 CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
1326 INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
1327 create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
1328 INSERT INTO t3 VALUES (3,23), (6,26);
1329 CREATE TABLE t4 (groupid int(12));
1330 INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
1331 SELECT * FROM
1332 (SELECT DISTINCT gl.groupid, gp.price
1333 FROM t4 gl
1334 LEFT JOIN
1335 (t3 g INNER JOIN t2 p ON g.goodsid = p.goods
1336 INNER JOIN t1 gp ON p.goods = gp.goods)
1337 ON gl.groupid = g.groupid and p.shop = 'fr') t;
1338 groupid price
1339 1 NULL
1340 2 NULL
1341 3 2340
1342 4 NULL
1343 5 NULL
1344 6 9900
1345 CREATE VIEW v1 AS
1346 SELECT g.groupid groupid, p.goods goods,
1347 p.name name, p.shop shop,
1348 gp.price price
1349 FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
1350 INNER JOIN t1 gp on p.goods = gp.goods;
1351 CREATE VIEW v2 AS
1352 SELECT DISTINCT g.groupid, fr.price
1353 FROM t4 g
1354 LEFT JOIN
1355 v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
1356 SELECT * FROM v2;
1357 groupid price
1358 1 NULL
1359 2 NULL
1360 3 2340
1361 4 NULL
1362 5 NULL
1363 6 9900
1364 SELECT * FROM
1365 (SELECT DISTINCT g.groupid, fr.price
1366 FROM t4 g
1367 LEFT JOIN
1368 v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
1369 groupid price
1370 1 NULL
1371 2 NULL
1372 3 2340
1373 4 NULL
1374 5 NULL
1375 6 9900
1376 DROP VIEW v1,v2;
1377 DROP TABLE t1,t2,t3,t4;
1378 CREATE TABLE t1(a int);
1379 CREATE TABLE t2(b int);
1380 CREATE TABLE t3(c int, d int);
1381 CREATE TABLE t4(d int);
1382 CREATE TABLE t5(e int, f int);
1383 CREATE TABLE t6(f int);
1384 CREATE VIEW v1 AS
1385 SELECT e FROM t5 JOIN t6 ON t5.e=t6.f;
1386 CREATE VIEW v2 AS
1387 SELECT e FROM t5 NATURAL JOIN t6;
1388 SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);
1389 a
1390 SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);
1391 ERROR 42S22: Unknown column 't1.x' in 'field list'
1392 SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;
1393 a
1394 SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;
1395 ERROR 42S22: Unknown column 't1.x' in 'field list'
1396 SELECT v1.e FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
1397 e
1398 SELECT v1.x FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
1399 ERROR 42S22: Unknown column 'v1.x' in 'field list'
1400 SELECT v2.e FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
1401 e
1402 SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
1403 ERROR 42S22: Unknown column 'v2.x' in 'field list'
1404 DROP VIEW v1, v2;
1405 DROP TABLE t1, t2, t3, t4, t5, t6;
1406 create table t1 (id1 int(11) not null);
1407 insert into t1 values (1),(2);
1408 create table t2 (id2 int(11) not null);
1409 insert into t2 values (1),(2),(3),(4);
1410 create table t3 (id3 char(16) not null);
1411 insert into t3 values ('100');
1412 create table t4 (id2 int(11) not null, id3 char(16));
1413 create table t5 (id1 int(11) not null, key (id1));
1414 insert into t5 values (1),(2),(1);
1415 create view v1 as
1416 select t4.id3 from t4 join t2 on t4.id2 = t2.id2;
1417 select t1.id1 from t1 inner join (t3 left join v1 on t3.id3 = v1.id3);
1418 id1
1419 1
1420 2
1421 drop view v1;
1422 drop table t1, t2, t3, t4, t5;
1423 create table t0 (a int);
1424 insert into t0 values (0),(1),(2),(3);
1425 create table t1(a int);
1426 insert into t1 select A.a + 10*(B.a) from t0 A, t0 B;
1427 create table t2 (a int, b int);
1428 insert into t2 values (1,1), (2,2), (3,3);
1429 create table t3(a int, b int, filler char(200), key(a));
1430 insert into t3 select a,a,'filler' from t1;
1431 insert into t3 select a,a,'filler' from t1;
1432 create table t4 like t3;
1433 insert into t4 select * from t3;
1434 insert into t4 select * from t3;
1435 create table t5 like t4;
1436 insert into t5 select * from t4;
1437 insert into t5 select * from t4;
1438 create table t6 like t5;
1439 insert into t6 select * from t5;
1440 insert into t6 select * from t5;
1441 create table t7 like t6;
1442 insert into t7 select * from t6;
1443 insert into t7 select * from t6;
1444 explain select * from t4 join
1445 t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b;
1446 id select_type table type possible_keys key key_len ref rows Extra
1447 1 SIMPLE t2 ALL NULL NULL NULL NULL X
1448 1 SIMPLE t3 ref a a 5 test.t2.b X
1449 1 SIMPLE t5 ref a a 5 test.t3.b X
1450 1 SIMPLE t4 ref a a 5 test.t3.b X Using where
1451 explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b
1452 join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b;
1453 id select_type table type possible_keys key key_len ref rows Extra
1454 1 SIMPLE t2 ALL NULL NULL NULL NULL X
1455 1 SIMPLE t3 ref a a 5 test.t2.b X Using where
1456 1 SIMPLE t4 ref a a 5 test.t3.b X
1457 1 SIMPLE t6 ref a a 5 test.t4.b X
1458 1 SIMPLE t5 ref a a 5 test.t2.b X
1459 1 SIMPLE t7 ref a a 5 test.t5.b X
1460 explain select * from t2 left join
1461 (t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b
1462 join t5 on t5.a=t3.b) on t3.a=t2.b;
1463 id select_type table type possible_keys key key_len ref rows Extra
1464 1 SIMPLE t2 ALL NULL NULL NULL NULL X
1465 1 SIMPLE t3 ref a a 5 test.t2.b X
1466 1 SIMPLE t4 ref a a 5 test.t3.b X
1467 1 SIMPLE t6 ref a a 5 test.t4.b X
1468 1 SIMPLE t5 ref a a 5 test.t3.b X
1469 drop table t0, t1, t2, t3, t4, t5, t6, t7;
1470 create table t1 (a int);
1471 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1472 create table t2 (a int, filler char(100), key(a));
1473 insert into t2 select A.a + 10*B.a, '' from t1 A, t1 B;
1474 create table t3 like t2;
1475 insert into t3 select * from t2;
1476 explain select * from t1 left join
1477 (t2 left join t3 on (t2.a = t3.a))
1478 on (t1.a = t2.a);
1479 id select_type table type possible_keys key key_len ref rows Extra
1480 1 SIMPLE t1 ALL NULL NULL NULL NULL 10
1481 1 SIMPLE t2 ref a a 5 test.t1.a 1
1482 1 SIMPLE t3 ref a a 5 test.t2.a 1
1483 drop table t1, t2, t3;
1484 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
1485 CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
1486 CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,
1487 id int NOT NULL,
1488 pid int NOT NULL);
1489 INSERT INTO t1 VALUES (1, 'A'), (3, 'C');
1490 INSERT INTO t2 VALUES (1, 'A'), (3, 'C');
1491 INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3);
1492 SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)
1493 ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)
1494 LEFT JOIN t2 ON (t3.pid=t2.pid)
1495 WHERE p.id=1;
1496 id type cid id pid id type pid type
1497 1 A NULL NULL NULL NULL NULL NULL NULL
1498 CREATE VIEW v1 AS
1499 SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';
1500 SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
1501 LEFT JOIN t2 ON v1.pid=t2.pid
1502 WHERE p.id=1;
1503 id type cid id pid pid type
1504 1 A NULL NULL NULL NULL NULL
1505 DROP VIEW v1;
1506 DROP TABLE t1,t2,t3;
1507 CREATE TABLE t1 (id1 int PRIMARY KEY, id2 int);
1508 CREATE TABLE t2 (id1 int PRIMARY KEY, id2 int);
1509 CREATE TABLE t3 (id1 int PRIMARY KEY, id2 int);
1510 CREATE TABLE t4 (id1 int PRIMARY KEY, id2 int);
1511 CREATE TABLE t5 (id1 int PRIMARY KEY, id2 int);
1512 SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
1513 FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
1514 LEFT OUTER JOIN
1515 (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
1516 ON t3.id2 IS NOT NULL
1517 WHERE t1.id1=2;
1518 id ngroupbynsa
1519 PREPARE stmt FROM
1520 "SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
1521 FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
1522 LEFT OUTER JOIN
1523 (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
1524 ON t3.id2 IS NOT NULL
1525 WHERE t1.id1=2";
1526 EXECUTE stmt;
1527 id ngroupbynsa
1528 EXECUTE stmt;
1529 id ngroupbynsa
1530 EXECUTE stmt;
1531 id ngroupbynsa
1532 EXECUTE stmt;
1533 id ngroupbynsa
1534 INSERT INTO t1 VALUES (1,1), (2,1), (3,2);
1535 INSERT INTO t2 VALUES (2,1), (3,2), (4,3);
1536 INSERT INTO t3 VALUES (1,1), (3,2), (2,NULL);
1537 INSERT INTO t4 VALUES (1,1), (2,1), (3,3);
1538 INSERT INTO t5 VALUES (1,1), (2,2), (3,3), (4,3);
1539 EXECUTE stmt;
1540 id ngroupbynsa
1541 2 1
1542 2 1
1543 EXECUTE stmt;
1544 id ngroupbynsa
1545 2 1
1546 2 1
1547 EXECUTE stmt;
1548 id ngroupbynsa
1549 2 1
1550 2 1
1551 EXECUTE stmt;
1552 id ngroupbynsa
1553 2 1
1554 2 1
1555 SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
1556 FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
1557 LEFT OUTER JOIN
1558 (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
1559 ON t3.id2 IS NOT NULL
1560 WHERE t1.id1=2;
1561 id ngroupbynsa
1562 2 1
1563 2 1
1564 DROP TABLE t1,t2,t3,t4,t5;
1565 CREATE TABLE t1 (
1566 id int NOT NULL PRIMARY KEY,
1567 ct int DEFAULT NULL,
1568 pc int DEFAULT NULL,
1569 INDEX idx_ct (ct),
1570 INDEX idx_pc (pc)
1571 );
1572 INSERT INTO t1 VALUES
1573 (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL);
1574 CREATE TABLE t2 (
1575 id int NOT NULL PRIMARY KEY,
1576 sr int NOT NULL,
1577 nm varchar(255) NOT NULL,
1578 INDEX idx_sr (sr)
1579 );
1580 INSERT INTO t2 VALUES
1581 (2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand');
1582 CREATE TABLE t3 (
1583 id int NOT NULL PRIMARY KEY,
1584 ct int NOT NULL,
1585 ln int NOT NULL,
1586 INDEX idx_ct (ct),
1587 INDEX idx_ln (ln)
1588 );
1589 CREATE TABLE t4 (
1590 id int NOT NULL PRIMARY KEY,
1591 nm varchar(255) NOT NULL
1592 );
1593 INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique');
1594 SELECT t1.*
1595 FROM t1 LEFT JOIN
1596 (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
1597 WHERE t1.id='5';
1598 id ct pc
1599 5 NULL NULL
1600 SELECT t1.*, t4.nm
1601 FROM t1 LEFT JOIN
1602 (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
1603 LEFT JOIN t4 ON t2.sr=t4.id
1604 WHERE t1.id='5';
1605 id ct pc nm
1606 5 NULL NULL NULL
1607 DROP TABLE t1,t2,t3,t4;
1608 CREATE TABLE t1 (a INT, b INT);
1609 CREATE TABLE t2 (a INT);
1610 CREATE TABLE t3 (a INT, c INT);
1611 CREATE TABLE t4 (a INT, c INT);
1612 CREATE TABLE t5 (a INT, c INT);
1613 SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
1614 LEFT JOIN t5 USING (a)) USING (a);
1615 b
1616 SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
1617 LEFT JOIN t5 USING (a)) USING (a);
1618 ERROR 23000: Column 'c' in field list is ambiguous
1619 SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
1620 JOIN t5 USING (a)) USING (a);
1621 b
1622 SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
1623 JOIN t5 USING (a)) USING (a);
1624 ERROR 23000: Column 'c' in field list is ambiguous
1625 DROP TABLE t1,t2,t3,t4,t5;
1626 CREATE TABLE t1 (a INT, b INT);
1627 CREATE TABLE t2 (a INT, b INT);
1628 CREATE TABLE t3 (a INT, b INT);
1629 INSERT INTO t1 VALUES (1,1);
1630 INSERT INTO t2 VALUES (1,1);
1631 INSERT INTO t3 VALUES (1,1);
1632 SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a);
1633 ERROR 23000: Column 'a' in from clause is ambiguous
1634 DROP TABLE t1,t2,t3;
1635 CREATE TABLE t1 (
1636 carrier char(2) default NULL,
1637 id int NOT NULL auto_increment PRIMARY KEY
1638 );
1639 INSERT INTO t1 VALUES
1640 ('CO',235371754),('CO',235376554),('CO',235376884),('CO',235377874),
1641 ('CO',231060394),('CO',231059224),('CO',231059314),('CO',231060484),
1642 ('CO',231060274),('CO',231060124),('CO',231060244),('CO',231058594),
1643 ('CO',231058924),('CO',231058504),('CO',231059344),('CO',231060424),
1644 ('CO',231059554),('CO',231060304),('CO',231059644),('CO',231059464),
1645 ('CO',231059764),('CO',231058294),('CO',231058624),('CO',231058864),
1646 ('CO',231059374),('CO',231059584),('CO',231059734),('CO',231059014),
1647 ('CO',231059854),('CO',231059494),('CO',231059794),('CO',231058534),
1648 ('CO',231058324),('CO',231058684),('CO',231059524),('CO',231059974);
1649 CREATE TABLE t2 (
1650 scan_date date default NULL,
1651 package_id int default NULL,
1652 INDEX scan_date(scan_date),
1653 INDEX package_id(package_id)
1654 );
1655 INSERT INTO t2 VALUES
1656 ('2008-12-29',231062944),('2008-12-29',231065764),('2008-12-29',231066124),
1657 ('2008-12-29',231060094),('2008-12-29',231061054),('2008-12-29',231065644),
1658 ('2008-12-29',231064384),('2008-12-29',231064444),('2008-12-29',231073774),
1659 ('2008-12-29',231058594),('2008-12-29',231059374),('2008-12-29',231066004),
1660 ('2008-12-29',231068494),('2008-12-29',231070174),('2008-12-29',231071884),
1661 ('2008-12-29',231063274),('2008-12-29',231063754),('2008-12-29',231064144),
1662 ('2008-12-29',231069424),('2008-12-29',231073714),('2008-12-29',231058414),
1663 ('2008-12-29',231060994),('2008-12-29',231069154),('2008-12-29',231068614),
1664 ('2008-12-29',231071464),('2008-12-29',231074014),('2008-12-29',231059614),
1665 ('2008-12-29',231059074),('2008-12-29',231059464),('2008-12-29',231069094),
1666 ('2008-12-29',231067294),('2008-12-29',231070144),('2008-12-29',231073804),
1667 ('2008-12-29',231072634),('2008-12-29',231058294),('2008-12-29',231065344),
1668 ('2008-12-29',231066094),('2008-12-29',231069034),('2008-12-29',231058594),
1669 ('2008-12-29',231059854),('2008-12-29',231059884),('2008-12-29',231059914),
1670 ('2008-12-29',231063664),('2008-12-29',231063814),('2008-12-29',231063904);
1671 CREATE TABLE t3 (
1672 package_id int default NULL,
1673 INDEX package_id(package_id)
1674 );
1675 INSERT INTO t3 VALUES
1676 (231058294),(231058324),(231058354),(231058384),(231058414),(231058444),
1677 (231058474),(231058504),(231058534),(231058564),(231058594),(231058624),
1678 (231058684),(231058744),(231058804),(231058864),(231058924),(231058954),
1679 (231059014),(231059074),(231059104),(231059134),(231059164),(231059194),
1680 (231059224),(231059254),(231059284),(231059314),(231059344),(231059374),
1681 (231059404),(231059434),(231059464),(231059494),(231059524),(231059554),
1682 (231059584),(231059614),(231059644),(231059674),(231059704),(231059734),
1683 (231059764),(231059794),(231059824),(231059854),(231059884),(231059914),
1684 (231059944),(231059974),(231060004),(231060034),(231060064),(231060094),
1685 (231060124),(231060154),(231060184),(231060214),(231060244),(231060274),
1686 (231060304),(231060334),(231060364),(231060394),(231060424),(231060454),
1687 (231060484),(231060514),(231060544),(231060574),(231060604),(231060634),
1688 (231060664),(231060694),(231060724),(231060754),(231060784),(231060814),
1689 (231060844),(231060874),(231060904),(231060934),(231060964),(231060994),
1690 (231061024),(231061054),(231061084),(231061144),(231061174),(231061204),
1691 (231061234),(231061294),(231061354),(231061384),(231061414),(231061474),
1692 (231061564),(231061594),(231061624),(231061684),(231061714),(231061774),
1693 (231061804),(231061894),(231061984),(231062074),(231062134),(231062224),
1694 (231062254),(231062314),(231062374),(231062434),(231062494),(231062554),
1695 (231062584),(231062614),(231062644),(231062704),(231062734),(231062794),
1696 (231062854),(231062884),(231062944),(231063004),(231063034),(231063064),
1697 (231063124),(231063154),(231063184),(231063214),(231063274),(231063334),
1698 (231063394),(231063424),(231063454),(231063514),(231063574),(231063664);
1699 CREATE TABLE t4 (
1700 carrier char(2) NOT NULL default '' PRIMARY KEY,
1701 id int(11) default NULL,
1702 INDEX id(id)
1703 );
1704 INSERT INTO t4 VALUES
1705 ('99',6),('SK',456),('UA',486),('AI',1081),('OS',1111),('VS',1510);
1706 CREATE TABLE t5 (
1707 carrier_id int default NULL,
1708 INDEX carrier_id(carrier_id)
1709 );
1710 INSERT INTO t5 VALUES
1711 (6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),
1712 (6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),
1713 (6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),
1714 (6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),
1715 (6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),
1716 (6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(456),(456),(456),
1717 (456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),
1718 (456),(486),(1081),(1111),(1111),(1111),(1111),(1510);
1719 SELECT COUNT(*)
1720 FROM((t2 JOIN t1 ON t2.package_id = t1.id)
1721 JOIN t3 ON t3.package_id = t1.id);
1722 COUNT(*)
1723 6
1724 EXPLAIN
1725 SELECT COUNT(*)
1726 FROM ((t2 JOIN t1 ON t2.package_id = t1.id)
1727 JOIN t3 ON t3.package_id = t1.id)
1728 LEFT JOIN
1729 (t5 JOIN t4 ON t5.carrier_id = t4.id)
1730 ON t4.carrier = t1.carrier;
1731 id select_type table type possible_keys key key_len ref rows Extra
1732 1 SIMPLE t2 index package_id package_id 5 NULL 45 Using index
1733 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1
1734 1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1
1735 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index
1736 1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index
1737 SELECT COUNT(*)
1738 FROM ((t2 JOIN t1 ON t2.package_id = t1.id)
1739 JOIN t3 ON t3.package_id = t1.id)
1740 LEFT JOIN
1741 (t5 JOIN t4 ON t5.carrier_id = t4.id)
1742 ON t4.carrier = t1.carrier;
1743 COUNT(*)
1744 6
1745 DROP TABLE t1,t2,t3,t4,t5;
1746 End of 5.0 tests