Debian Codegraph @

mysql-test/suite/ndb_opt/spj_rqg.result

raw
1 Table Op Msg_type Msg_text
2 test.a analyze status OK
3 test.b analyze status OK
4 test.c analyze status OK
5 test.d analyze status OK
6 test.e analyze status OK
7 test.f analyze status OK
8 test.g analyze status OK
9 test.h analyze status OK
10 test.i analyze status OK
11 test.j analyze status OK
12 test.k analyze status OK
13 test.l analyze status OK
14 test.m analyze status OK
15 test.n analyze status OK
16 test.o analyze status OK
17 test.p analyze status OK
18 test.q analyze status OK
19 test.r analyze status OK
20 test.s analyze status OK
21 test.t analyze status OK
22 test.u analyze status OK
23 test.v analyze status OK
24 test.w analyze status OK
25 test.x analyze status OK
26 test.y analyze status OK
27 test.z analyze status OK
28 explain format=tree SELECT STRAIGHT_JOIN
29 table1.pk, table2.pk, table3.pk
30 FROM n AS table1
31 LEFT JOIN w AS table2
32 LEFT JOIN w AS table3 ON table2.col_varchar_256_unique = table3.col_char_16_unique
33 ON table1.col_int = table2.col_int_key AND table3.col_int IS NULL;
34 EXPLAIN
35 -> Nested loop left join
36 -> Table scan on table1
37 -> Filter: (table3.col_int is null)
38 -> Nested loop left join
39 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int)
40 -> Filter: (table2.col_varchar_256_unique = table3.col_char_16_unique)
41 -> Single-row index lookup on table3 using col_char_16_unique (col_char_16_unique=table2.col_varchar_256_unique)
42
43 Warnings:
44 Note 1003 Can't push outer joined table 'table2' as child of 'table1', join-nest containing the table has FILTER conditions
45 Note 1003 Can't push table 'table3' as child, column 'col_char_16_unique' does not have same datatype as ref'ed column 'table2.col_varchar_256_unique'
46 SELECT STRAIGHT_JOIN
47 table1.pk, table2.pk, table3.pk
48 FROM n AS table1
49 LEFT JOIN w AS table2
50 LEFT JOIN w AS table3 ON table2.col_varchar_256_unique = table3.col_char_16_unique
51 ON table1.col_int = table2.col_int_key AND table3.col_int IS NULL;
52 pk pk pk
53 1 1 NULL
54 1 12 NULL
55 1 14 NULL
56 1 15 NULL
57 1 16 NULL
58 1 18 NULL
59 1 22 NULL
60 1 24 NULL
61 1 29 NULL
62 1 9 NULL
63 10 1 NULL
64 10 12 NULL
65 10 14 NULL
66 10 15 NULL
67 10 16 NULL
68 10 18 NULL
69 10 22 NULL
70 10 24 NULL
71 10 29 NULL
72 10 9 NULL
73 11 11 NULL
74 11 13 NULL
75 11 2 NULL
76 11 27 NULL
77 11 30 NULL
78 11 6 NULL
79 12 NULL NULL
80 13 10 NULL
81 13 23 NULL
82 13 25 NULL
83 13 7 NULL
84 14 17 NULL
85 14 20 NULL
86 14 21 NULL
87 14 26 NULL
88 14 28 NULL
89 14 3 NULL
90 14 5 NULL
91 15 10 NULL
92 15 23 NULL
93 15 25 NULL
94 15 7 NULL
95 16 17 NULL
96 16 20 NULL
97 16 21 NULL
98 16 26 NULL
99 16 28 NULL
100 16 3 NULL
101 16 5 NULL
102 17 10 NULL
103 17 23 NULL
104 17 25 NULL
105 17 7 NULL
106 18 17 NULL
107 18 20 NULL
108 18 21 NULL
109 18 26 NULL
110 18 28 NULL
111 18 3 NULL
112 18 5 NULL
113 19 10 NULL
114 19 23 NULL
115 19 25 NULL
116 19 7 NULL
117 2 11 NULL
118 2 13 NULL
119 2 2 NULL
120 2 27 NULL
121 2 30 NULL
122 2 6 NULL
123 20 1 NULL
124 20 12 NULL
125 20 14 NULL
126 20 15 NULL
127 20 16 NULL
128 20 18 NULL
129 20 22 NULL
130 20 24 NULL
131 20 29 NULL
132 20 9 NULL
133 21 10 NULL
134 21 23 NULL
135 21 25 NULL
136 21 7 NULL
137 22 NULL NULL
138 3 1 NULL
139 3 12 NULL
140 3 14 NULL
141 3 15 NULL
142 3 16 NULL
143 3 18 NULL
144 3 22 NULL
145 3 24 NULL
146 3 29 NULL
147 3 9 NULL
148 4 17 NULL
149 4 20 NULL
150 4 21 NULL
151 4 26 NULL
152 4 28 NULL
153 4 3 NULL
154 4 5 NULL
155 5 17 NULL
156 5 20 NULL
157 5 21 NULL
158 5 26 NULL
159 5 28 NULL
160 5 3 NULL
161 5 5 NULL
162 6 17 NULL
163 6 20 NULL
164 6 21 NULL
165 6 26 NULL
166 6 28 NULL
167 6 3 NULL
168 6 5 NULL
169 7 NULL NULL
170 8 1 NULL
171 8 12 NULL
172 8 14 NULL
173 8 15 NULL
174 8 16 NULL
175 8 18 NULL
176 8 22 NULL
177 8 24 NULL
178 8 29 NULL
179 8 9 NULL
180 9 1 NULL
181 9 12 NULL
182 9 14 NULL
183 9 15 NULL
184 9 16 NULL
185 9 18 NULL
186 9 22 NULL
187 9 24 NULL
188 9 29 NULL
189 9 9 NULL
190 explain format=tree SELECT straight_join
191 table2.pk, table3.pk, table1.pk, table4.pk, table5.pk, table6.pk
192 FROM r AS table2
193 LEFT JOIN c AS table3 ON table2.col_int_unique = table3.col_int_unique
194 INNER JOIN m AS table1 ON table1.col_int_key = table2.pk
195 LEFT JOIN r AS table4 ON table2.pk = table4.col_int_key
196 LEFT JOIN e AS table5 ON table3.col_int_unique = table5.col_int_unique
197 LEFT JOIN s AS table6 ON table1.col_int = table6.col_int
198 AND table5.col_int_unique = table6.col_int_unique
199 where table2.pk between 2 and 3;
200 EXPLAIN
201 -> Nested loop left join
202 -> Nested loop left join
203 -> Nested loop left join
204 -> Nested loop inner join
205 -> Nested loop left join
206 -> Table scan on table2, activating pushed join of 5 tables, with pushed condition: (table2.pk between 2 and 3)
207 -> Index lookup on table3 using ix1 (col_int_unique=table2.col_int_unique), child of table2 in pushed join
208 -> Index lookup on table1 using col_int_key (col_int_key=table2.pk), child of table2 in pushed join
209 -> Index lookup on table4 using col_int_key (col_int_key=table2.pk), child of table2 in pushed join
210 -> Single-row index lookup on table5 using ix1 (col_int_unique=table3.col_int_unique), child of table3 in pushed join
211 -> Single-row index lookup on table6 using ix1 (col_int=table1.col_int, col_int_unique=table5.col_int_unique)
212
213 Warnings:
214 Note 1003 Can't push table 'table6' as child of 'table2', as it would make the parent table 'table5' depend on table(s) outside of its join-nest
215 SELECT straight_join
216 table2.pk, table3.pk, table1.pk, table4.pk, table5.pk, table6.pk
217 FROM r AS table2
218 LEFT JOIN c AS table3 ON table2.col_int_unique = table3.col_int_unique
219 INNER JOIN m AS table1 ON table1.col_int_key = table2.pk
220 LEFT JOIN r AS table4 ON table2.pk = table4.col_int_key
221 LEFT JOIN e AS table5 ON table3.col_int_unique = table5.col_int_unique
222 LEFT JOIN s AS table6 ON table1.col_int = table6.col_int
223 AND table5.col_int_unique = table6.col_int_unique
224 where table2.pk between 2 and 3;
225 pk pk pk pk pk pk
226 2 NULL 10 10 NULL NULL
227 2 NULL 10 11 NULL NULL
228 2 NULL 10 13 NULL NULL
229 2 NULL 10 14 NULL NULL
230 2 NULL 10 18 NULL NULL
231 2 NULL 10 2 NULL NULL
232 2 NULL 10 22 NULL NULL
233 2 NULL 10 5 NULL NULL
234 2 NULL 10 6 NULL NULL
235 2 NULL 11 10 NULL NULL
236 2 NULL 11 11 NULL NULL
237 2 NULL 11 13 NULL NULL
238 2 NULL 11 14 NULL NULL
239 2 NULL 11 18 NULL NULL
240 2 NULL 11 2 NULL NULL
241 2 NULL 11 22 NULL NULL
242 2 NULL 11 5 NULL NULL
243 2 NULL 11 6 NULL NULL
244 2 NULL 13 10 NULL NULL
245 2 NULL 13 11 NULL NULL
246 2 NULL 13 13 NULL NULL
247 2 NULL 13 14 NULL NULL
248 2 NULL 13 18 NULL NULL
249 2 NULL 13 2 NULL NULL
250 2 NULL 13 22 NULL NULL
251 2 NULL 13 5 NULL NULL
252 2 NULL 13 6 NULL NULL
253 2 NULL 15 10 NULL NULL
254 2 NULL 15 11 NULL NULL
255 2 NULL 15 13 NULL NULL
256 2 NULL 15 14 NULL NULL
257 2 NULL 15 18 NULL NULL
258 2 NULL 15 2 NULL NULL
259 2 NULL 15 22 NULL NULL
260 2 NULL 15 5 NULL NULL
261 2 NULL 15 6 NULL NULL
262 2 NULL 18 10 NULL NULL
263 2 NULL 18 11 NULL NULL
264 2 NULL 18 13 NULL NULL
265 2 NULL 18 14 NULL NULL
266 2 NULL 18 18 NULL NULL
267 2 NULL 18 2 NULL NULL
268 2 NULL 18 22 NULL NULL
269 2 NULL 18 5 NULL NULL
270 2 NULL 18 6 NULL NULL
271 2 NULL 2 10 NULL NULL
272 2 NULL 2 11 NULL NULL
273 2 NULL 2 13 NULL NULL
274 2 NULL 2 14 NULL NULL
275 2 NULL 2 18 NULL NULL
276 2 NULL 2 2 NULL NULL
277 2 NULL 2 22 NULL NULL
278 2 NULL 2 5 NULL NULL
279 2 NULL 2 6 NULL NULL
280 2 NULL 3 10 NULL NULL
281 2 NULL 3 11 NULL NULL
282 2 NULL 3 13 NULL NULL
283 2 NULL 3 14 NULL NULL
284 2 NULL 3 18 NULL NULL
285 2 NULL 3 2 NULL NULL
286 2 NULL 3 22 NULL NULL
287 2 NULL 3 5 NULL NULL
288 2 NULL 3 6 NULL NULL
289 2 NULL 4 10 NULL NULL
290 2 NULL 4 11 NULL NULL
291 2 NULL 4 13 NULL NULL
292 2 NULL 4 14 NULL NULL
293 2 NULL 4 18 NULL NULL
294 2 NULL 4 2 NULL NULL
295 2 NULL 4 22 NULL NULL
296 2 NULL 4 5 NULL NULL
297 2 NULL 4 6 NULL NULL
298 3 NULL 1 16 NULL NULL
299 3 NULL 1 19 NULL NULL
300 3 NULL 1 3 NULL NULL
301 3 NULL 12 16 NULL NULL
302 3 NULL 12 19 NULL NULL
303 3 NULL 12 3 NULL NULL
304 3 NULL 20 16 NULL NULL
305 3 NULL 20 19 NULL NULL
306 3 NULL 20 3 NULL NULL
307 3 NULL 5 16 NULL NULL
308 3 NULL 5 19 NULL NULL
309 3 NULL 5 3 NULL NULL
310 3 NULL 6 16 NULL NULL
311 3 NULL 6 19 NULL NULL
312 3 NULL 6 3 NULL NULL
313 explain format=tree SELECT
314 table1.pk, table3.pk, table4.pk
315 FROM e AS table1
316 JOIN w AS table3
317 JOIN w AS table4 ON table3.col_int = table4.col_int
318 AND table3.col_int_key = table4.col_int_key
319 ON table1.col_int_unique = table3.col_int_key
320 WHERE table4.col_int IN (
321 SELECT table2s.col_int AS field3
322 FROM o AS table1s
323 LEFT JOIN x AS table2s ON table1s.col_varchar_256_key = table2s.col_varchar_256_unique
324 ) and table1.pk between 3 and 3;
325 EXPLAIN
326 -> Nested loop semijoin
327 -> Nested loop inner join
328 -> Nested loop inner join
329 -> Table scan on table1, activating pushed join of 5 tables, with pushed condition: ((table1.pk = 3) and (table1.col_int_unique is not null))
330 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int_unique), child of table1 in pushed join
331 -> Index lookup on table4 using col_int_key (col_int_key=table1.col_int_unique), child of table3 in pushed join, with pushed condition: (table4.col_int = table3.col_int)
332 -> Nested loop inner join
333 -> Index lookup on table2s using PRIMARY (col_int=table3.col_int), child of table3 in pushed join, with pushed condition: (table2s.col_varchar_256_unique is not null)
334 -> Index lookup on table1s using col_varchar_256_key (col_varchar_256_key=table2s.col_varchar_256_unique), child of table2s in pushed join
335
336 SELECT
337 table1.pk, table3.pk, table4.pk
338 FROM e AS table1
339 JOIN w AS table3
340 JOIN w AS table4 ON table3.col_int = table4.col_int
341 AND table3.col_int_key = table4.col_int_key
342 ON table1.col_int_unique = table3.col_int_key
343 WHERE table4.col_int IN (
344 SELECT table2s.col_int AS field3
345 FROM o AS table1s
346 LEFT JOIN x AS table2s ON table1s.col_varchar_256_key = table2s.col_varchar_256_unique
347 ) and table1.pk between 3 and 3;
348 pk pk pk
349 3 20 20
350 3 20 5
351 3 26 26
352 3 26 28
353 3 26 3
354 3 28 26
355 3 28 28
356 3 28 3
357 3 3 26
358 3 3 28
359 3 3 3
360 3 5 20
361 3 5 5
362 explain format=tree SELECT
363 table1.pk, table2.pk
364 FROM f AS table1
365 LEFT JOIN y AS table2 ON table1.col_int NOT IN (1, 9)
366 WHERE EXISTS (
367 SELECT table2s.pk, table1s.pk, table3s.pk
368 FROM r AS table1s
369 JOIN c AS table2s ON table1s.col_int = table2s.col_int_key
370 JOIN g AS table3s ON table2s.col_int = table3s.col_int AND # <<- unpushed
371 table1s.col_int_unique = table3s.col_int_unique
372 )
373 and table1.pk < 3;
374 EXPLAIN
375 -> Left hash join (no condition), extra conditions: (table1.col_int not in (1,9))
376 -> Inner hash join (no condition)
377 -> Table scan on table1, with pushed condition: (table1.pk < 3)
378 -> Hash
379 -> Limit: 1 row(s)
380 -> Nested loop inner join
381 -> Nested loop inner join
382 -> Table scan on table2s, activating pushed join of 3 tables, with pushed condition: (table2s.col_int_key is not null)
383 -> Index lookup on table1s using ix1 (col_int=table2s.col_int_key), child of table2s in pushed join, with pushed condition: (table1s.col_int_unique is not null)
384 -> Single-row index lookup on table3s using ix1 (col_int_unique=table1s.col_int_unique), child of table1s in pushed join, with pushed condition: (table3s.col_int = table2s.col_int)
385 -> Hash
386 -> Table scan on table2
387
388 Warnings:
389 Note 1003 Can't push table 'table1' as child, 'type' must be a 'ref' access
390 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
391 SELECT
392 table1.pk, table2.pk
393 FROM f AS table1
394 LEFT JOIN y AS table2 ON table1.col_int NOT IN (1, 9)
395 WHERE EXISTS (
396 SELECT table2s.pk, table1s.pk, table3s.pk
397 FROM r AS table1s
398 JOIN c AS table2s ON table1s.col_int = table2s.col_int_key
399 JOIN g AS table3s ON table2s.col_int = table3s.col_int AND # <<- unpushed
400 table1s.col_int_unique = table3s.col_int_unique
401 )
402 and table1.pk < 3;
403 pk pk
404 1 NULL
405 2 1
406 2 10
407 2 11
408 2 12
409 2 13
410 2 14
411 2 15
412 2 16
413 2 17
414 2 18
415 2 19
416 2 2
417 2 20
418 2 21
419 2 22
420 2 23
421 2 24
422 2 25
423 2 26
424 2 27
425 2 28
426 2 29
427 2 3
428 2 30
429 2 31
430 2 32
431 2 33
432 2 4
433 2 5
434 2 6
435 2 7
436 2 8
437 2 9
438 explain format=tree SELECT straight_join
439 table1.pk, table2.pk, table3.pk, table4.pk
440 FROM h AS table1
441 JOIN y AS table2
442 LEFT JOIN q AS table3
443 LEFT JOIN k AS table4 ON table3.col_int = table4.pk
444 ON table2.col_int = table3.col_int AND table2.col_int_unique = table3.col_int_unique
445 ON table1.col_int_unique = table2.col_int;
446 EXPLAIN
447 -> Nested loop left join
448 -> Nested loop inner join
449 -> Table scan on table1, activating pushed join of 4 tables, with pushed condition: (table1.col_int_unique is not null)
450 -> Index lookup on table2 using PRIMARY (col_int=table1.col_int_unique), child of table1 in pushed join
451 -> Nested loop left join
452 -> Single-row index lookup on table3 using ix1 (col_int=table1.col_int_unique, col_int_unique=table2.col_int_unique), child of table2 in pushed join
453 -> Index lookup on table4 using PRIMARY (pk=table1.col_int_unique), child of table2 in pushed join
454
455 SELECT straight_join
456 table1.pk, table2.pk, table3.pk, table4.pk
457 FROM h AS table1
458 JOIN y AS table2
459 LEFT JOIN q AS table3
460 LEFT JOIN k AS table4 ON table3.col_int = table4.pk
461 ON table2.col_int = table3.col_int AND table2.col_int_unique = table3.col_int_unique
462 ON table1.col_int_unique = table2.col_int;
463 pk pk pk pk
464 14 19 NULL NULL
465 14 20 24 1
466 14 23 2 1
467 14 25 NULL NULL
468 14 26 NULL NULL
469 14 27 NULL NULL
470 14 4 NULL NULL
471 explain format=tree SELECT straight_join
472 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk, table6.pk
473 FROM e AS table1
474 JOIN d as table2 ON table2.col_int_unique = table1.pk
475 LEFT JOIN m AS table3
476 LEFT JOIN q AS table4 ON table3.col_int_key = table4.col_int
477 ON table1.col_int_key = table3.col_int_key
478 LEFT JOIN s AS table5 ON table3.col_int_unique = table5.col_int
479 LEFT JOIN t AS table6 ON table4.col_int = table6.col_int
480 AND table2.col_int_key = table6.col_int_key
481 AND table5.col_int_unique = table6.col_int_unique
482 where table1.pk between 1 and 3;
483 EXPLAIN
484 -> Nested loop left join
485 -> Nested loop left join
486 -> Nested loop left join
487 -> Nested loop inner join
488 -> Table scan on table1, activating pushed join of 6 tables, with pushed condition: (table1.pk between 1 and 3)
489 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.pk), child of table1 in pushed join
490 -> Nested loop left join
491 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int_key), child of table2 in pushed join
492 -> Index lookup on table4 using ix1 (col_int=table1.col_int_key), child of table2 in pushed join
493 -> Index lookup on table5 using ix1 (col_int=table3.col_int_unique), child of table3 in pushed join
494 -> Filter: (table6.col_int = table4.col_int)
495 -> Single-row index lookup on table6 using ix2 (col_int_key=table2.col_int_key, col_int_unique=table5.col_int_unique), child of table5 in pushed join
496
497 SELECT straight_join
498 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk, table6.pk
499 FROM e AS table1
500 JOIN d as table2 ON table2.col_int_unique = table1.pk
501 LEFT JOIN m AS table3
502 LEFT JOIN q AS table4 ON table3.col_int_key = table4.col_int
503 ON table1.col_int_key = table3.col_int_key
504 LEFT JOIN s AS table5 ON table3.col_int_unique = table5.col_int
505 LEFT JOIN t AS table6 ON table4.col_int = table6.col_int
506 AND table2.col_int_key = table6.col_int_key
507 AND table5.col_int_unique = table6.col_int_unique
508 where table1.pk between 1 and 3;
509 pk pk pk pk pk pk
510 2 5 14 10 NULL NULL
511 2 5 14 11 NULL NULL
512 2 5 14 14 NULL NULL
513 2 5 14 17 NULL NULL
514 2 5 14 18 NULL NULL
515 2 5 14 19 NULL NULL
516 2 5 14 3 NULL NULL
517 2 5 14 4 NULL NULL
518 2 5 22 10 NULL NULL
519 2 5 22 11 NULL NULL
520 2 5 22 14 NULL NULL
521 2 5 22 17 NULL NULL
522 2 5 22 18 NULL NULL
523 2 5 22 19 NULL NULL
524 2 5 22 3 NULL NULL
525 2 5 22 4 NULL NULL
526 2 5 7 10 NULL NULL
527 2 5 7 11 NULL NULL
528 2 5 7 14 NULL NULL
529 2 5 7 17 NULL NULL
530 2 5 7 18 NULL NULL
531 2 5 7 19 NULL NULL
532 2 5 7 3 NULL NULL
533 2 5 7 4 NULL NULL
534 explain format=tree SELECT
535 table1.pk, table2.pk, table3.pk, table4.pk
536 FROM c AS table1
537 INNER JOIN f AS table2 ON table1.col_int_unique = table2.col_int
538 LEFT JOIN p AS table3
539 LEFT JOIN u AS table4 ON table3.col_int_key = table4.col_int
540 AND table3.col_int_unique = table4.pk
541 ON table1.col_int = table3.col_int
542 AND table2.col_int_key = table3.col_int_key
543 AND table1.col_int_unique = table3.col_int_unique;
544 EXPLAIN
545 -> Nested loop left join
546 -> Nested loop inner join
547 -> Table scan on table1, activating pushed join of 4 tables, with pushed condition: (table1.col_int_unique is not null)
548 -> Index lookup on table2 using PRIMARY (col_int=table1.col_int_unique), child of table1 in pushed join
549 -> Nested loop left join
550 -> Single-row index lookup on table3 using ix1 (col_int=table1.col_int, col_int_unique=table1.col_int_unique), child of table2 in pushed join, with pushed condition: (table3.col_int_key = table2.col_int_key)
551 -> Single-row index lookup on table4 using PRIMARY (col_int=table2.col_int_key, pk=table1.col_int_unique), child of table2 in pushed join
552
553 SELECT
554 table1.pk, table2.pk, table3.pk, table4.pk
555 FROM c AS table1
556 INNER JOIN f AS table2 ON table1.col_int_unique = table2.col_int
557 LEFT JOIN p AS table3
558 LEFT JOIN u AS table4 ON table3.col_int_key = table4.col_int
559 AND table3.col_int_unique = table4.pk
560 ON table1.col_int = table3.col_int
561 AND table2.col_int_key = table3.col_int_key
562 AND table1.col_int_unique = table3.col_int_unique;
563 pk pk pk pk
564 explain format=tree SELECT
565 # NOTE: NO_BNL is a synonym for NO_HASH_JOIN
566 /*+ JOIN_ORDER(table3,table2,table1,table4,table5) NO_BNL() */
567 table3.pk, table2.pk, table1.pk, table4.pk, table5.pk
568 FROM u AS table1
569 JOIN q AS table2 USE INDEX FOR JOIN(ix1)
570 JOIN h AS table3 ON table2.col_int = table3.col_int
571 ON table1.col_int_key = table2.col_int_key
572 LEFT JOIN n AS table4
573 LEFT JOIN q AS table5 ON table4.col_int = table5.col_int
574 ON table2.col_int = table4.col_int AND
575 table1.col_int_key = table4.col_int_key AND
576 table2.col_int_unique = table4.col_int_unique
577 # Limits output somewhat:
578 where table3.pk between 0 and 1
579 and table2.pk between 4 and 5;
580 EXPLAIN
581 -> Nested loop left join
582 -> Nested loop inner join
583 -> Nested loop inner join
584 -> Index range scan on table3 using PRIMARY over (0 <= pk <= 1), activating pushed join of 5 tables, with pushed condition: ((table3.pk between 0 and 1) and (table3.col_int is not null))
585 -> Index lookup on table2 using ix1 (col_int=table3.col_int), child of table3 in pushed join, with pushed condition: ((table2.pk between 4 and 5) and (table2.col_int_key is not null))
586 -> Index lookup on table1 using col_int_key (col_int_key=table2.col_int_key), child of table2 in pushed join
587 -> Nested loop left join
588 -> Single-row index lookup on table4 using ix3 (col_int=table3.col_int, col_int_key=table2.col_int_key, col_int_unique=table2.col_int_unique), child of table2 in pushed join
589 -> Index lookup on table5 using ix1 (col_int=table3.col_int), child of table2 in pushed join
590
591 SELECT
592 # NOTE: NO_BNL is a synonym for NO_HASH_JOIN
593 /*+ JOIN_ORDER(table3,table2,table1,table4,table5) NO_BNL() */
594 table3.pk, table2.pk, table1.pk, table4.pk, table5.pk
595 FROM u AS table1
596 JOIN q AS table2 USE INDEX FOR JOIN(ix1)
597 JOIN h AS table3 ON table2.col_int = table3.col_int
598 ON table1.col_int_key = table2.col_int_key
599 LEFT JOIN n AS table4
600 LEFT JOIN q AS table5 ON table4.col_int = table5.col_int
601 ON table2.col_int = table4.col_int AND
602 table1.col_int_key = table4.col_int_key AND
603 table2.col_int_unique = table4.col_int_unique
604 # Limits output somewhat:
605 where table3.pk between 0 and 1
606 and table2.pk between 4 and 5;
607 pk pk pk pk pk
608 1 4 1 NULL NULL
609 1 4 12 NULL NULL
610 1 4 18 NULL NULL
611 1 4 2 NULL NULL
612 1 4 24 NULL NULL
613 1 4 27 NULL NULL
614 explain format=tree SELECT
615 table1.pk, table2.pk
616 FROM x AS table1
617 JOIN y AS table2 ON table1.col_int = table2.col_int
618 AND table1.col_int_unique = table2.col_int_unique
619 WHERE table1.pk IN (
620 SELECT
621 table1s.pk AS field3
622 FROM d AS table1s
623 JOIN m AS table2s ON table1s.pk = table2s.col_int_key
624 );
625 EXPLAIN
626 -> Nested loop semijoin
627 -> Nested loop inner join
628 -> Nested loop inner join
629 -> Table scan on table1, activating pushed join of 4 tables
630 -> Index lookup on table2 using PRIMARY (col_int=table1.col_int), child of table1 in pushed join, with pushed condition: (table2.col_int_unique = table1.col_int_unique)
631 -> Single-row index lookup on table1s using PRIMARY (pk=table1.pk), child of table1 in pushed join
632 -> Index lookup on table2s using col_int_key (col_int_key=table1.pk), child of table1 in pushed join
633
634 SELECT
635 table1.pk, table2.pk
636 FROM x AS table1
637 JOIN y AS table2 ON table1.col_int = table2.col_int
638 AND table1.col_int_unique = table2.col_int_unique
639 WHERE table1.pk IN (
640 SELECT
641 table1s.pk AS field3
642 FROM d AS table1s
643 JOIN m AS table2s ON table1s.pk = table2s.col_int_key
644 );
645 pk pk
646 3 16
647 explain format=tree SELECT straight_join
648 table1.pk, table3.pk, table2.pk, table4.pk
649 FROM t AS table1
650 LEFT JOIN (q AS table3
651 INNER JOIN m AS table2 ON table2.col_int = table3.col_int_key)
652 LEFT JOIN r AS table4 ON table2.col_int_key = table4.col_int
653 ON table1.col_int_unique = table3.col_int_key
654 where table1.pk between 14 and 14;
655 EXPLAIN
656 -> Nested loop left join
657 -> Table scan on table1, activating pushed join of 4 tables, with pushed condition: (table1.pk = 14)
658 -> Nested loop left join
659 -> Nested loop inner join
660 -> Index lookup on table3 using ix2 (col_int_key=table1.col_int_unique), child of table1 in pushed join
661 -> Index lookup on table2 using ix3 (col_int=table1.col_int_unique), child of table1 in pushed join
662 -> Index lookup on table4 using ix1 (col_int=table2.col_int_key), child of table2 in pushed join
663
664 SELECT straight_join
665 table1.pk, table3.pk, table2.pk, table4.pk
666 FROM t AS table1
667 LEFT JOIN (q AS table3
668 INNER JOIN m AS table2 ON table2.col_int = table3.col_int_key)
669 LEFT JOIN r AS table4 ON table2.col_int_key = table4.col_int
670 ON table1.col_int_unique = table3.col_int_key
671 where table1.pk between 14 and 14;
672 pk pk pk pk
673 14 10 11 16
674 14 10 11 23
675 14 10 16 19
676 14 10 16 20
677 14 10 16 24
678 14 10 16 4
679 14 10 16 6
680 14 10 16 9
681 14 10 17 19
682 14 10 17 20
683 14 10 17 24
684 14 10 17 4
685 14 10 17 6
686 14 10 17 9
687 14 10 2 16
688 14 10 2 23
689 14 10 20 11
690 14 10 20 13
691 14 10 20 14
692 14 10 20 17
693 14 10 20 25
694 14 10 21 19
695 14 10 21 20
696 14 10 21 24
697 14 10 21 4
698 14 10 21 6
699 14 10 21 9
700 14 10 8 19
701 14 10 8 20
702 14 10 8 24
703 14 10 8 4
704 14 10 8 6
705 14 10 8 9
706 14 14 11 16
707 14 14 11 23
708 14 14 16 19
709 14 14 16 20
710 14 14 16 24
711 14 14 16 4
712 14 14 16 6
713 14 14 16 9
714 14 14 17 19
715 14 14 17 20
716 14 14 17 24
717 14 14 17 4
718 14 14 17 6
719 14 14 17 9
720 14 14 2 16
721 14 14 2 23
722 14 14 20 11
723 14 14 20 13
724 14 14 20 14
725 14 14 20 17
726 14 14 20 25
727 14 14 21 19
728 14 14 21 20
729 14 14 21 24
730 14 14 21 4
731 14 14 21 6
732 14 14 21 9
733 14 14 8 19
734 14 14 8 20
735 14 14 8 24
736 14 14 8 4
737 14 14 8 6
738 14 14 8 9
739 14 21 11 16
740 14 21 11 23
741 14 21 16 19
742 14 21 16 20
743 14 21 16 24
744 14 21 16 4
745 14 21 16 6
746 14 21 16 9
747 14 21 17 19
748 14 21 17 20
749 14 21 17 24
750 14 21 17 4
751 14 21 17 6
752 14 21 17 9
753 14 21 2 16
754 14 21 2 23
755 14 21 20 11
756 14 21 20 13
757 14 21 20 14
758 14 21 20 17
759 14 21 20 25
760 14 21 21 19
761 14 21 21 20
762 14 21 21 24
763 14 21 21 4
764 14 21 21 6
765 14 21 21 9
766 14 21 8 19
767 14 21 8 20
768 14 21 8 24
769 14 21 8 4
770 14 21 8 6
771 14 21 8 9
772 14 24 11 16
773 14 24 11 23
774 14 24 16 19
775 14 24 16 20
776 14 24 16 24
777 14 24 16 4
778 14 24 16 6
779 14 24 16 9
780 14 24 17 19
781 14 24 17 20
782 14 24 17 24
783 14 24 17 4
784 14 24 17 6
785 14 24 17 9
786 14 24 2 16
787 14 24 2 23
788 14 24 20 11
789 14 24 20 13
790 14 24 20 14
791 14 24 20 17
792 14 24 20 25
793 14 24 21 19
794 14 24 21 20
795 14 24 21 24
796 14 24 21 4
797 14 24 21 6
798 14 24 21 9
799 14 24 8 19
800 14 24 8 20
801 14 24 8 24
802 14 24 8 4
803 14 24 8 6
804 14 24 8 9
805 14 25 11 16
806 14 25 11 23
807 14 25 16 19
808 14 25 16 20
809 14 25 16 24
810 14 25 16 4
811 14 25 16 6
812 14 25 16 9
813 14 25 17 19
814 14 25 17 20
815 14 25 17 24
816 14 25 17 4
817 14 25 17 6
818 14 25 17 9
819 14 25 2 16
820 14 25 2 23
821 14 25 20 11
822 14 25 20 13
823 14 25 20 14
824 14 25 20 17
825 14 25 20 25
826 14 25 21 19
827 14 25 21 20
828 14 25 21 24
829 14 25 21 4
830 14 25 21 6
831 14 25 21 9
832 14 25 8 19
833 14 25 8 20
834 14 25 8 24
835 14 25 8 4
836 14 25 8 6
837 14 25 8 9
838 14 8 11 16
839 14 8 11 23
840 14 8 16 19
841 14 8 16 20
842 14 8 16 24
843 14 8 16 4
844 14 8 16 6
845 14 8 16 9
846 14 8 17 19
847 14 8 17 20
848 14 8 17 24
849 14 8 17 4
850 14 8 17 6
851 14 8 17 9
852 14 8 2 16
853 14 8 2 23
854 14 8 20 11
855 14 8 20 13
856 14 8 20 14
857 14 8 20 17
858 14 8 20 25
859 14 8 21 19
860 14 8 21 20
861 14 8 21 24
862 14 8 21 4
863 14 8 21 6
864 14 8 21 9
865 14 8 8 19
866 14 8 8 20
867 14 8 8 24
868 14 8 8 4
869 14 8 8 6
870 14 8 8 9
871 explain format=tree SELECT STRAIGHT_JOIN
872 table1.pk, table2.pk, table3.pk
873 FROM r AS table1
874 LEFT JOIN z AS table2
875 LEFT JOIN f AS table3 ON table2.col_int_key = table3.col_int_unique
876 ON table1.col_int_unique = table3.col_int_unique;
877 EXPLAIN
878 -> Nested loop left join
879 -> Table scan on table1, activating pushed join of 3 tables
880 -> Nested loop inner join
881 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_unique), child of table1 in pushed join
882 -> Index lookup on table3 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join
883
884 SELECT STRAIGHT_JOIN
885 table1.pk, table2.pk, table3.pk
886 FROM r AS table1
887 LEFT JOIN z AS table2
888 LEFT JOIN f AS table3 ON table2.col_int_key = table3.col_int_unique
889 ON table1.col_int_unique = table3.col_int_unique;
890 pk pk pk
891 1 NULL NULL
892 10 NULL NULL
893 11 NULL NULL
894 12 NULL NULL
895 13 NULL NULL
896 14 NULL NULL
897 15 NULL NULL
898 16 NULL NULL
899 17 NULL NULL
900 18 NULL NULL
901 19 NULL NULL
902 2 NULL NULL
903 20 NULL NULL
904 21 NULL NULL
905 22 NULL NULL
906 23 NULL NULL
907 24 NULL NULL
908 25 NULL NULL
909 3 NULL NULL
910 4 NULL NULL
911 5 NULL NULL
912 6 NULL NULL
913 7 11 15
914 7 13 15
915 7 16 15
916 7 24 15
917 7 28 15
918 7 29 15
919 7 32 15
920 7 52 15
921 7 65 15
922 7 7 15
923 7 70 15
924 7 72 15
925 7 9 15
926 8 NULL NULL
927 9 NULL NULL
928 explain format=tree SELECT
929 table2.pk AS field1, table2.col_int_unique AS field2
930 FROM v AS table1
931 LEFT JOIN g AS table2
932 LEFT JOIN u AS table3 ON table2.col_int_unique = table3.col_int_key
933 ON table1.col_int_key = table2.col_int_unique;
934 EXPLAIN
935 -> Nested loop left join
936 -> Table scan on table1, activating pushed join of 3 tables
937 -> Nested loop left join
938 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int_key), child of table1 in pushed join
939 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
940
941 SELECT
942 table2.pk AS field1, table2.col_int_unique AS field2
943 FROM v AS table1
944 LEFT JOIN g AS table2
945 LEFT JOIN u AS table3 ON table2.col_int_unique = table3.col_int_key
946 ON table1.col_int_key = table2.col_int_unique;
947 field1 field2
948 5 0
949 5 0
950 5 0
951 5 0
952 5 0
953 5 0
954 5 0
955 5 0
956 5 0
957 5 0
958 5 0
959 5 0
960 5 0
961 5 0
962 5 0
963 5 0
964 5 0
965 5 0
966 5 0
967 5 0
968 5 0
969 5 0
970 5 0
971 5 0
972 5 0
973 5 0
974 5 0
975 5 0
976 5 0
977 5 0
978 5 0
979 5 0
980 5 0
981 5 0
982 5 0
983 5 0
984 5 0
985 5 0
986 5 0
987 5 0
988 NULL NULL
989 NULL NULL
990 NULL NULL
991 NULL NULL
992 NULL NULL
993 NULL NULL
994 NULL NULL
995 NULL NULL
996 NULL NULL
997 NULL NULL
998 NULL NULL
999 NULL NULL
1000 NULL NULL
1001 NULL NULL
1002 NULL NULL
1003 NULL NULL
1004 NULL NULL
1005 NULL NULL
1006 NULL NULL
1007 NULL NULL
1008 NULL NULL
1009 NULL NULL
1010 NULL NULL
1011 NULL NULL
1012 explain format=tree SELECT
1013 table1.pk, table2.pk, table4.pk, table3.pk
1014 FROM t AS table1
1015 LEFT JOIN f AS table2
1016 INNER JOIN
1017 (r AS table3 inner JOIN i AS table4 ON table3.col_int_key = table4.col_int_key)
1018 ON table2.col_int_unique = table4.col_int
1019 ON table1.col_int = table2.col_int_unique
1020 where table1.pk > 8 and table1.pk < 11;
1021 EXPLAIN
1022 -> Nested loop left join
1023 -> Table scan on table1, activating pushed join of 4 tables, with pushed condition: ((table1.pk > 8) and (table1.pk < 11))
1024 -> Nested loop inner join
1025 -> Nested loop inner join
1026 -> Index lookup on table2 using ix1 (col_int_unique=table1.col_int), child of table1 in pushed join
1027 -> Index lookup on table4 using ix3 (col_int=table1.col_int), child of table1 in pushed join
1028 -> Index lookup on table3 using col_int_key (col_int_key=table4.col_int_key), child of table4 in pushed join
1029
1030 SELECT
1031 table1.pk, table2.pk, table4.pk, table3.pk
1032 FROM t AS table1
1033 LEFT JOIN f AS table2
1034 INNER JOIN
1035 (r AS table3 inner JOIN i AS table4 ON table3.col_int_key = table4.col_int_key)
1036 ON table2.col_int_unique = table4.col_int
1037 ON table1.col_int = table2.col_int_unique
1038 where table1.pk > 8 and table1.pk < 11;
1039 pk pk pk pk
1040 10 NULL NULL NULL
1041 9 15 6 1
1042 9 15 6 12
1043 9 15 6 15
1044 9 15 6 17
1045 9 15 6 4
1046 9 15 6 8
1047 9 15 6 9
1048 9 15 9 16
1049 9 15 9 19
1050 9 15 9 3
1051 explain format=tree SELECT
1052 table1.pk, table2.pk, table4.pk, table3.pk
1053 FROM i AS table1
1054 LEFT JOIN j AS table2
1055 LEFT JOIN d AS table3
1056 JOIN e AS table4 ON table3.col_int_key = table4.col_int_unique
1057 ON table2.col_int_key = table4.col_int_unique
1058 ON table1.col_int_key = table2.col_int_key
1059 where table1.pk > 2 and table1.pk < 5;
1060 EXPLAIN
1061 -> Nested loop left join
1062 -> Table scan on table1, activating pushed join of 4 tables, with pushed condition: ((table1.pk > 2) and (table1.pk < 5))
1063 -> Nested loop left join
1064 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
1065 -> Nested loop inner join
1066 -> Single-row index lookup on table4 using ix1 (col_int_unique=table1.col_int_key), child of table1 in pushed join
1067 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
1068
1069 SELECT
1070 table1.pk, table2.pk, table4.pk, table3.pk
1071 FROM i AS table1
1072 LEFT JOIN j AS table2
1073 LEFT JOIN d AS table3
1074 JOIN e AS table4 ON table3.col_int_key = table4.col_int_unique
1075 ON table2.col_int_key = table4.col_int_unique
1076 ON table1.col_int_key = table2.col_int_key
1077 where table1.pk > 2 and table1.pk < 5;
1078 pk pk pk pk
1079 3 1 3 1
1080 3 1 3 5
1081 3 1 3 8
1082 3 1 3 9
1083 3 18 3 1
1084 3 18 3 5
1085 3 18 3 8
1086 3 18 3 9
1087 3 19 3 1
1088 3 19 3 5
1089 3 19 3 8
1090 3 19 3 9
1091 3 4 3 1
1092 3 4 3 5
1093 3 4 3 8
1094 3 4 3 9
1095 3 7 3 1
1096 3 7 3 5
1097 3 7 3 8
1098 3 7 3 9
1099 4 1 3 1
1100 4 1 3 5
1101 4 1 3 8
1102 4 1 3 9
1103 4 18 3 1
1104 4 18 3 5
1105 4 18 3 8
1106 4 18 3 9
1107 4 19 3 1
1108 4 19 3 5
1109 4 19 3 8
1110 4 19 3 9
1111 4 4 3 1
1112 4 4 3 5
1113 4 4 3 8
1114 4 4 3 9
1115 4 7 3 1
1116 4 7 3 5
1117 4 7 3 8
1118 4 7 3 9
1119 explain format=tree SELECT straight_join
1120 table1.pk, table2.pk, table3.pk, table4.pk
1121 FROM s AS table1
1122 LEFT JOIN t AS table2
1123 LEFT JOIN q AS table3 ON table2.col_int_key = table3.col_int_unique AND table2.col_int_unique = table3.col_int_unique
1124 LEFT JOIN o AS table4 ON table2.pk = table4.col_int
1125 ON table4.col_int IS NULL AND table1.col_int_unique = table2.col_int_key;
1126 EXPLAIN
1127 -> Nested loop left join
1128 -> Table scan on table1
1129 -> Filter: (table4.col_int is null)
1130 -> Nested loop left join
1131 -> Nested loop left join
1132 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_unique), activating pushed join of 2 tables
1133 -> Table scan on table3, with pushed condition: ((table2.col_int_unique = table1.col_int_unique) and (table3.col_int_unique = table1.col_int_unique))
1134 -> Index lookup on table4 using ix3 (col_int=table2.pk), child of table2 in pushed join
1135
1136 Warnings:
1137 Note 1003 Can't push outer joined table 'table2' as child of 'table1', join-nest containing the table has FILTER conditions
1138 Note 1003 Can't push table 'table3' as child, 'type' must be a 'ref' access
1139 Note 1003 Can't push table 'table4' as child of 'table1', column 'table2.pk' refers a table which was not pushed
1140 SELECT straight_join
1141 table1.pk, table2.pk, table3.pk, table4.pk
1142 FROM s AS table1
1143 LEFT JOIN t AS table2
1144 LEFT JOIN q AS table3 ON table2.col_int_key = table3.col_int_unique AND table2.col_int_unique = table3.col_int_unique
1145 LEFT JOIN o AS table4 ON table2.pk = table4.col_int
1146 ON table4.col_int IS NULL AND table1.col_int_unique = table2.col_int_key;
1147 pk pk pk pk
1148 1 NULL NULL NULL
1149 10 NULL NULL NULL
1150 11 NULL NULL NULL
1151 12 22 NULL NULL
1152 12 25 NULL NULL
1153 12 6 NULL NULL
1154 12 7 NULL NULL
1155 13 NULL NULL NULL
1156 14 NULL NULL NULL
1157 15 NULL NULL NULL
1158 16 NULL NULL NULL
1159 17 NULL NULL NULL
1160 18 NULL NULL NULL
1161 19 NULL NULL NULL
1162 2 NULL NULL NULL
1163 20 NULL NULL NULL
1164 21 13 NULL NULL
1165 21 14 7 NULL
1166 21 15 NULL NULL
1167 21 17 NULL NULL
1168 21 20 NULL NULL
1169 21 4 NULL NULL
1170 21 5 NULL NULL
1171 22 NULL NULL NULL
1172 23 NULL NULL NULL
1173 3 NULL NULL NULL
1174 4 NULL NULL NULL
1175 5 NULL NULL NULL
1176 6 NULL NULL NULL
1177 7 NULL NULL NULL
1178 8 NULL NULL NULL
1179 9 NULL NULL NULL
1180 explain format=tree SELECT straight_join
1181 table1.pk, table2.pk, table3.pk
1182 FROM c AS table1
1183 INNER JOIN x AS table2 ON table1.col_int_unique = table2.col_int_unique
1184 LEFT JOIN j AS table3 ON table2.col_int_unique = table3.col_int_key;
1185 EXPLAIN
1186 -> Nested loop left join
1187 -> Inner hash join (table2.col_int_unique = table1.col_int_unique)
1188 -> Table scan on table2, activating pushed join of 2 tables
1189 -> Hash
1190 -> Table scan on table1
1191 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int_unique), child of table2 in pushed join
1192
1193 Warnings:
1194 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
1195 Note 1003 Can't push table 'table3' as child of 'table1', it is in a hash-bucket-branch which can't be referred.
1196 SELECT straight_join
1197 table1.pk, table2.pk, table3.pk
1198 FROM c AS table1
1199 INNER JOIN x AS table2 ON table1.col_int_unique = table2.col_int_unique
1200 LEFT JOIN j AS table3 ON table2.col_int_unique = table3.col_int_key;
1201 pk pk pk
1202 1 18 NULL
1203 3 20 NULL
1204 explain format=tree SELECT straight_join
1205 table1.pk, table2.pk, table3.pk
1206 FROM s AS table1
1207 LEFT OUTER JOIN c AS table2 ON table1.col_int = table2.pk
1208 LEFT OUTER JOIN w AS table3 ON table2.col_int = table3.col_int_key;
1209 EXPLAIN
1210 -> Nested loop left join
1211 -> Left hash join (table2.pk = table1.col_int)
1212 -> Table scan on table1
1213 -> Hash
1214 -> Table scan on table2
1215 -> Index lookup on table3 using col_int_key (col_int_key=table2.col_int)
1216
1217 Warnings:
1218 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
1219 Note 1003 Can't push table 'table3' as child of 'table1', it is in a hash-probe-branch which can't be referred.
1220 Note 1003 Can't push table 'table3' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
1221 SELECT straight_join
1222 table1.pk, table2.pk, table3.pk
1223 FROM s AS table1
1224 LEFT OUTER JOIN c AS table2 ON table1.col_int = table2.pk
1225 LEFT OUTER JOIN w AS table3 ON table2.col_int = table3.col_int_key;
1226 pk pk pk
1227 1 NULL NULL
1228 10 1 10
1229 10 1 23
1230 10 1 25
1231 10 1 7
1232 10 1 8
1233 11 2 11
1234 11 2 13
1235 11 2 2
1236 11 2 27
1237 11 2 30
1238 11 2 6
1239 12 NULL NULL
1240 13 1 10
1241 13 1 23
1242 13 1 25
1243 13 1 7
1244 13 1 8
1245 14 NULL NULL
1246 15 2 11
1247 15 2 13
1248 15 2 2
1249 15 2 27
1250 15 2 30
1251 15 2 6
1252 16 NULL NULL
1253 17 2 11
1254 17 2 13
1255 17 2 2
1256 17 2 27
1257 17 2 30
1258 17 2 6
1259 18 2 11
1260 18 2 13
1261 18 2 2
1262 18 2 27
1263 18 2 30
1264 18 2 6
1265 19 2 11
1266 19 2 13
1267 19 2 2
1268 19 2 27
1269 19 2 30
1270 19 2 6
1271 2 1 10
1272 2 1 23
1273 2 1 25
1274 2 1 7
1275 2 1 8
1276 20 1 10
1277 20 1 23
1278 20 1 25
1279 20 1 7
1280 20 1 8
1281 21 NULL NULL
1282 22 3 10
1283 22 3 23
1284 22 3 25
1285 22 3 7
1286 22 3 8
1287 23 NULL NULL
1288 3 NULL NULL
1289 4 2 11
1290 4 2 13
1291 4 2 2
1292 4 2 27
1293 4 2 30
1294 4 2 6
1295 5 3 10
1296 5 3 23
1297 5 3 25
1298 5 3 7
1299 5 3 8
1300 6 1 10
1301 6 1 23
1302 6 1 25
1303 6 1 7
1304 6 1 8
1305 7 NULL NULL
1306 8 3 10
1307 8 3 23
1308 8 3 25
1309 8 3 7
1310 8 3 8
1311 9 3 10
1312 9 3 23
1313 9 3 25
1314 9 3 7
1315 9 3 8
1316 explain format=tree SELECT straight_join
1317 table1.pk, table2.pk, table3.pk
1318 FROM r AS table1
1319 LEFT JOIN g AS table2
1320 LEFT JOIN j AS table3 ON table2.col_int_unique = table3.col_int_key
1321 ON table1.pk = table2.col_int_unique
1322 WHERE table1.pk < 3;
1323 EXPLAIN
1324 -> Nested loop left join
1325 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.pk < 3)
1326 -> Nested loop left join
1327 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.pk), child of table1 in pushed join
1328 -> Index lookup on table3 using col_int_key (col_int_key=table1.pk), child of table1 in pushed join
1329
1330 SELECT straight_join
1331 table1.pk, table2.pk, table3.pk
1332 FROM r AS table1
1333 LEFT JOIN g AS table2
1334 LEFT JOIN j AS table3 ON table2.col_int_unique = table3.col_int_key
1335 ON table1.pk = table2.col_int_unique
1336 WHERE table1.pk < 3;
1337 pk pk pk
1338 1 NULL NULL
1339 2 NULL NULL
1340 explain format=tree SELECT straight_join
1341 table1.pk, table2.pk, table3.pk
1342 FROM c AS table1
1343 INNER JOIN p AS table2 ON table1.pk = table2.pk
1344 LEFT OUTER JOIN i AS table3 ON table2.col_int_unique = table3.col_int;
1345 EXPLAIN
1346 -> Nested loop left join
1347 -> Nested loop inner join
1348 -> Table scan on table1, activating pushed join of 3 tables
1349 -> Single-row index lookup on table2 using PRIMARY (pk=table1.pk), child of table1 in pushed join
1350 -> Index lookup on table3 using ix3 (col_int=table2.col_int_unique), child of table2 in pushed join
1351
1352 SELECT straight_join
1353 table1.pk, table2.pk, table3.pk
1354 FROM c AS table1
1355 INNER JOIN p AS table2 ON table1.pk = table2.pk
1356 LEFT OUTER JOIN i AS table3 ON table2.col_int_unique = table3.col_int;
1357 pk pk pk
1358 1 1 NULL
1359 2 2 6
1360 2 2 9
1361 3 3 NULL
1362 4 4 NULL
1363 5 5 NULL
1364 explain format=tree SELECT
1365 table2.col_int_key AS field2
1366 FROM z AS table2
1367 WHERE NOT table2.col_int_unique IN (
1368 SELECT table1s.col_int_key AS field3
1369 FROM f AS table1s
1370 left JOIN u AS table2s ON table2s.col_int IN (6, 6, 7)
1371 WHERE table2.pk = 9 OR table2s.col_int = 1
1372 ) IS TRUE ;
1373 EXPLAIN
1374 -> Filter: <if>(found_match(table2s), true, true)
1375 -> Nested loop antijoin
1376 -> Table scan on table2
1377 -> Filter: ((table2.pk = 9) or (table2s.col_int = 1))
1378 -> Nested loop left join
1379 -> Index lookup on table1s using col_int_key (col_int_key=table2.col_int_unique)
1380 -> Index range scan on table2s using PRIMARY over (col_int = 6) OR (col_int = 7), with pushed condition: (table2s.col_int in (6,6,7))
1381
1382 Warnings:
1383 Note 1276 Field or reference 'test.table2.pk' of SELECT #2 was resolved in SELECT #1
1384 Note 1003 Can't push anti joined table 'table1s' as child of 'table2', join-nest containing the table has FILTER conditions
1385 Note 1003 Can't push table 'table2s' as child, access type 'Range-scan' not implemented
1386 SELECT
1387 table2.col_int_key AS field2
1388 FROM z AS table2
1389 WHERE NOT table2.col_int_unique IN (
1390 SELECT table1s.col_int_key AS field3
1391 FROM f AS table1s
1392 left JOIN u AS table2s ON table2s.col_int IN (6, 6, 7)
1393 WHERE table2.pk = 9 OR table2s.col_int = 1
1394 ) IS TRUE ;
1395 field2
1396 0
1397 0
1398 0
1399 0
1400 0
1401 0
1402 0
1403 0
1404 0
1405 0
1406 0
1407 0
1408 1
1409 1
1410 1
1411 1
1412 1
1413 1
1414 1
1415 1
1416 1
1417 1
1418 1
1419 1
1420 1
1421 1
1422 1
1423 2
1424 2
1425 2
1426 2
1427 2
1428 2
1429 2
1430 2
1431 2
1432 2
1433 2
1434 2
1435 2
1436 3
1437 3
1438 3
1439 3
1440 3
1441 3
1442 3
1443 3
1444 3
1445 3
1446 3
1447 3
1448 3
1449 NULL
1450 NULL
1451 explain format=tree SELECT
1452 table1.col_int_key AS field1
1453 FROM t AS table1
1454 WHERE table1.col_int NOT IN (
1455 SELECT table1s.pk AS field2
1456 FROM z AS table1s
1457 JOIN v AS table2s ON table1s.col_int = table2s.col_int AND
1458 table1s.col_int_unique = table2s.col_int_unique
1459 );
1460 EXPLAIN
1461 -> Nested loop antijoin
1462 -> Table scan on table1, activating pushed join of 2 tables
1463 -> Limit: 1 row(s)
1464 -> Nested loop inner join
1465 -> Single-row index lookup on table1s using PRIMARY (pk=table1.col_int), child of table1 in pushed join
1466 -> Table scan on table2s, with pushed condition: ((table2s.col_int_unique = table1s.col_int_unique) and (table2s.col_int = table1s.col_int))
1467
1468 Warnings:
1469 Note 1003 Can't push table 'table2s' as child, 'type' must be a 'ref' access
1470 SELECT
1471 table1.col_int_key AS field1
1472 FROM t AS table1
1473 WHERE table1.col_int NOT IN (
1474 SELECT table1s.pk AS field2
1475 FROM z AS table1s
1476 JOIN v AS table2s ON table1s.col_int = table2s.col_int AND
1477 table1s.col_int_unique = table2s.col_int_unique
1478 );
1479 field1
1480 0
1481 0
1482 0
1483 0
1484 0
1485 0
1486 0
1487 1
1488 1
1489 1
1490 1
1491 1
1492 1
1493 1
1494 1
1495 1
1496 2
1497 2
1498 2
1499 2
1500 3
1501 3
1502 3
1503 3
1504 3
1505 explain format=tree SELECT STRAIGHT_JOIN
1506 table1.pk, table2.pk, table3.pk
1507 FROM b AS table1
1508 LEFT JOIN z AS table2
1509 JOIN z AS table3 ON table2.pk = table3.col_int_key
1510 LEFT JOIN q AS table4
1511 LEFT JOIN b AS table5 ON table4.col_int = table5.col_int_key
1512 ON table2.col_int_key = table5.col_int_unique AND
1513 table2.col_int_unique = table4.col_int
1514 ON table1.col_int_key = table2.col_int_key;
1515 EXPLAIN
1516 -> Nested loop left join
1517 -> Table scan on table1, activating pushed join of 5 tables
1518 -> Nested loop left join
1519 -> Nested loop inner join
1520 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
1521 -> Index lookup on table3 using col_int_key (col_int_key=table2.pk), child of table2 in pushed join
1522 -> Nested loop inner join
1523 -> Index lookup on table4 using ix1 (col_int=table2.col_int_unique), child of table2 in pushed join
1524 -> Single-row index lookup on table5 using ix1 (col_int_unique=table1.col_int_key), child of table2 in pushed join, with pushed condition: (table5.col_int_key = table2.col_int_unique)
1525
1526 SELECT STRAIGHT_JOIN
1527 table1.pk, table2.pk, table3.pk
1528 FROM b AS table1
1529 LEFT JOIN z AS table2
1530 JOIN z AS table3 ON table2.pk = table3.col_int_key
1531 LEFT JOIN q AS table4
1532 LEFT JOIN b AS table5 ON table4.col_int = table5.col_int_key
1533 ON table2.col_int_key = table5.col_int_unique AND
1534 table2.col_int_unique = table4.col_int
1535 ON table1.col_int_key = table2.col_int_key;
1536 pk pk pk
1537 1 1 1
1538 1 1 14
1539 1 1 17
1540 1 1 19
1541 1 1 21
1542 1 1 26
1543 1 1 3
1544 1 1 4
1545 1 1 5
1546 1 1 54
1547 1 1 55
1548 1 1 59
1549 1 1 62
1550 1 1 64
1551 1 1 67
1552 1 3 10
1553 1 3 18
1554 1 3 2
1555 1 3 20
1556 1 3 33
1557 1 3 56
1558 1 3 57
1559 1 3 58
1560 1 3 6
1561 1 3 60
1562 1 3 63
1563 1 3 71
1564 1 3 8
1565 explain format=tree SELECT
1566 /*+ JOIN_ORDER(table2,table1,table2s,table1s) */
1567 table2.pk, table1.pk
1568 FROM p AS table2
1569 JOIN o AS table1 ON table1.col_int = table2.col_int
1570 WHERE table1.col_int_key IN (
1571 SELECT table2s.col_int_unique AS field3
1572 FROM l AS table1s USE INDEX FOR JOIN(PRIMARY)
1573 JOIN h AS table2s ON table1s.col_int = table2s.col_int
1574 );
1575 EXPLAIN
1576 -> Nested loop semijoin
1577 -> Nested loop inner join
1578 -> Nested loop inner join
1579 -> Table scan on table2, activating pushed join of 4 tables, with pushed condition: (table2.col_int is not null)
1580 -> Index lookup on table1 using ix3 (col_int=table2.col_int), child of table2 in pushed join, with pushed condition: (table1.col_int_key is not null)
1581 -> Single-row index lookup on table2s using ix1 (col_int_unique=table1.col_int_key), child of table1 in pushed join, with pushed condition: (table2s.col_int is not null)
1582 -> Index lookup on table1s using PRIMARY (col_int=table2s.col_int), child of table2s in pushed join
1583
1584 SELECT
1585 /*+ JOIN_ORDER(table2,table1,table2s,table1s) */
1586 table2.pk, table1.pk
1587 FROM p AS table2
1588 JOIN o AS table1 ON table1.col_int = table2.col_int
1589 WHERE table1.col_int_key IN (
1590 SELECT table2s.col_int_unique AS field3
1591 FROM l AS table1s USE INDEX FOR JOIN(PRIMARY)
1592 JOIN h AS table2s ON table1s.col_int = table2s.col_int
1593 );
1594 pk pk
1595 10 11
1596 10 21
1597 10 4
1598 10 7
1599 12 10
1600 12 5
1601 15 10
1602 15 5
1603 17 1
1604 17 8
1605 19 11
1606 19 21
1607 19 4
1608 19 7
1609 2 1
1610 2 8
1611 20 1
1612 20 8
1613 21 11
1614 21 21
1615 21 4
1616 21 7
1617 3 10
1618 3 5
1619 4 10
1620 4 5
1621 5 1
1622 5 8
1623 9 1
1624 9 8
1625 explain format=tree SELECT
1626 table1.pk
1627 FROM i AS table1
1628 WHERE table1.col_int IN (
1629 SELECT table1s.col_int
1630 FROM y AS table1s
1631 JOIN y AS table2s ON table1s.col_int_key = table2s.col_int_key
1632 );
1633 EXPLAIN
1634 -> Nested loop semijoin
1635 -> Table scan on table1, activating pushed join of 3 tables
1636 -> Nested loop inner join
1637 -> Index lookup on table1s using PRIMARY (col_int=table1.col_int), child of table1 in pushed join, with pushed condition: (table1s.col_int_key is not null)
1638 -> Index lookup on table2s using col_int_key (col_int_key=table1s.col_int_key), child of table1s in pushed join
1639
1640 SELECT
1641 table1.pk
1642 FROM i AS table1
1643 WHERE table1.col_int IN (
1644 SELECT table1s.col_int
1645 FROM y AS table1s
1646 JOIN y AS table2s ON table1s.col_int_key = table2s.col_int_key
1647 );
1648 pk
1649 1
1650 10
1651 11
1652 12
1653 13
1654 14
1655 2
1656 3
1657 4
1658 5
1659 6
1660 7
1661 8
1662 9
1663 explain format=tree SELECT
1664 table1.pk, table2.pk
1665 FROM j AS table1
1666 JOIN z AS table2 ON table1.col_int_key = table2.col_int_key
1667 WHERE table1.col_int IN (
1668 SELECT table1s.col_int_key AS field3 FROM u AS table1s
1669 )
1670 and table1.pk in (1,2);
1671 EXPLAIN
1672 -> Nested loop inner join
1673 -> Nested loop semijoin
1674 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: ((table1.pk in (1,2)) and (table1.col_int_key is not null))
1675 -> Index lookup on table1s using col_int_key (col_int_key=table1.col_int), child of table1 in pushed join
1676 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
1677
1678 SELECT
1679 table1.pk, table2.pk
1680 FROM j AS table1
1681 JOIN z AS table2 ON table1.col_int_key = table2.col_int_key
1682 WHERE table1.col_int IN (
1683 SELECT table1s.col_int_key AS field3 FROM u AS table1s
1684 )
1685 and table1.pk in (1,2);
1686 pk pk
1687 1 10
1688 1 18
1689 1 2
1690 1 20
1691 1 33
1692 1 56
1693 1 57
1694 1 58
1695 1 6
1696 1 60
1697 1 63
1698 1 71
1699 1 8
1700 2 11
1701 2 13
1702 2 16
1703 2 24
1704 2 28
1705 2 29
1706 2 32
1707 2 52
1708 2 65
1709 2 7
1710 2 70
1711 2 72
1712 2 9
1713 explain format=tree SELECT
1714 table1.pk, table2.pk
1715 FROM j AS table1
1716 JOIN z AS table2 ON table1.col_int_key = table2.col_int_key
1717 WHERE table1.col_int IN (
1718 SELECT table1s.col_int_key AS field3 FROM u AS table1s
1719 )
1720 and table1.pk in (4,5);
1721 EXPLAIN
1722 -> Nested loop inner join
1723 -> Nested loop semijoin
1724 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: ((table1.pk in (4,5)) and (table1.col_int_key is not null))
1725 -> Index lookup on table1s using col_int_key (col_int_key=table1.col_int), child of table1 in pushed join
1726 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
1727
1728 SELECT
1729 table1.pk, table2.pk
1730 FROM j AS table1
1731 JOIN z AS table2 ON table1.col_int_key = table2.col_int_key
1732 WHERE table1.col_int IN (
1733 SELECT table1s.col_int_key AS field3 FROM u AS table1s
1734 )
1735 and table1.pk in (4,5);
1736 pk pk
1737 4 10
1738 4 18
1739 4 2
1740 4 20
1741 4 33
1742 4 56
1743 4 57
1744 4 58
1745 4 6
1746 4 60
1747 4 63
1748 4 71
1749 4 8
1750 5 11
1751 5 13
1752 5 16
1753 5 24
1754 5 28
1755 5 29
1756 5 32
1757 5 52
1758 5 65
1759 5 7
1760 5 70
1761 5 72
1762 5 9
1763 explain format=tree SELECT
1764 table1.pk, table2.pk
1765 FROM f AS table1
1766 JOIN r AS table2 ON table1.col_int_unique = table2.col_int_key
1767 WHERE table1.col_int IN (SELECT table1s.col_int_key AS field3 FROM w AS table1s);
1768 EXPLAIN
1769 -> Nested loop inner join
1770 -> Nested loop semijoin
1771 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.col_int_unique is not null)
1772 -> Index lookup on table1s using col_int_key (col_int_key=table1.col_int), child of table1 in pushed join
1773 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_unique), child of table1 in pushed join
1774
1775 SELECT
1776 table1.pk, table2.pk
1777 FROM f AS table1
1778 JOIN r AS table2 ON table1.col_int_unique = table2.col_int_key
1779 WHERE table1.col_int IN (SELECT table1s.col_int_key AS field3 FROM w AS table1s);
1780 pk pk
1781 1 16
1782 1 19
1783 1 3
1784 15 10
1785 15 11
1786 15 13
1787 15 14
1788 15 18
1789 15 2
1790 15 22
1791 15 5
1792 15 6
1793 explain format=tree SELECT straight_join
1794 table1.pk, table2.pk, table3.pk, table4.pk
1795 FROM j AS table1
1796 LEFT JOIN a AS table2 ON table1.col_int_unique = table2.pk
1797 LEFT JOIN m AS table3
1798 LEFT JOIN o AS table4 ON table3.col_int_key = table4.col_int_key
1799 ON table1.col_int = table3.col_int AND
1800 table1.col_int_key = table3.col_int_key AND
1801 table2.col_int_unique = table3.col_int_unique;
1802 EXPLAIN
1803 -> Nested loop left join
1804 -> Nested loop left join
1805 -> Table scan on table1, activating pushed join of 4 tables
1806 -> Single-row index lookup on table2 using PRIMARY (pk=table1.col_int_unique), child of table1 in pushed join
1807 -> Nested loop left join
1808 -> Single-row index lookup on table3 using ix3 (col_int=table1.col_int, col_int_key=table1.col_int_key, col_int_unique=table2.col_int_unique), child of table2 in pushed join
1809 -> Index lookup on table4 using col_int_key (col_int_key=table1.col_int_key), child of table2 in pushed join
1810
1811 SELECT straight_join
1812 table1.pk, table2.pk, table3.pk, table4.pk
1813 FROM j AS table1
1814 LEFT JOIN a AS table2 ON table1.col_int_unique = table2.pk
1815 LEFT JOIN m AS table3
1816 LEFT JOIN o AS table4 ON table3.col_int_key = table4.col_int_key
1817 ON table1.col_int = table3.col_int AND
1818 table1.col_int_key = table3.col_int_key AND
1819 table2.col_int_unique = table3.col_int_unique;
1820 pk pk pk pk
1821 1 NULL NULL NULL
1822 10 NULL NULL NULL
1823 11 NULL NULL NULL
1824 12 NULL NULL NULL
1825 13 NULL NULL NULL
1826 14 NULL NULL NULL
1827 15 NULL NULL NULL
1828 16 NULL NULL NULL
1829 17 NULL NULL NULL
1830 18 NULL NULL NULL
1831 19 NULL NULL NULL
1832 2 NULL NULL NULL
1833 20 NULL NULL NULL
1834 21 NULL NULL NULL
1835 3 NULL NULL NULL
1836 4 NULL NULL NULL
1837 5 NULL NULL NULL
1838 6 NULL NULL NULL
1839 7 NULL NULL NULL
1840 8 NULL NULL NULL
1841 9 NULL NULL NULL
1842 explain format=tree SELECT
1843 table1.pk, table2.pk
1844 FROM v AS table1
1845 LEFT JOIN o table2 ON table1.pk = table2.pk
1846 WHERE NOT EXISTS (
1847 SELECT *
1848 FROM m AS table2s
1849 WHERE table2s.pk = table2.pk
1850 )
1851 and table1.pk = 11;
1852 EXPLAIN
1853 -> Nested loop antijoin
1854 -> Left hash join (no condition)
1855 -> Table scan on table1, with pushed condition: (table1.pk = 11)
1856 -> Hash
1857 -> Table scan on table2, with pushed condition: (table2.pk = 11)
1858 -> Single-row index lookup on table2s using PRIMARY (pk=table2.pk)
1859
1860 Warnings:
1861 Note 1276 Field or reference 'test.table2.pk' of SELECT #2 was resolved in SELECT #1
1862 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
1863 Note 1003 Can't push table 'table2s' as child of 'table1', it is in a hash-probe-branch which can't be referred.
1864 Note 1003 Can't push table 'table2s' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
1865 SELECT
1866 table1.pk, table2.pk
1867 FROM v AS table1
1868 LEFT JOIN o table2 ON table1.pk = table2.pk
1869 WHERE NOT EXISTS (
1870 SELECT *
1871 FROM m AS table2s
1872 WHERE table2s.pk = table2.pk
1873 )
1874 and table1.pk = 11;
1875 pk pk
1876 explain format=tree SELECT
1877 table1.pk, table2.pk
1878 FROM v AS table1
1879 left JOIN o AS table2 ON table1.pk = table2.pk
1880 WHERE NOT EXISTS (
1881 SELECT table1.col_int AS field4, table1s.col_char_16_key AS field5
1882 FROM d AS table1s
1883 JOIN m AS table2s ON table1s.col_char_16_key = table2s.col_char_16_key
1884 WHERE table2s.pk = table2.pk
1885 )
1886 and table1.pk = 11;
1887 EXPLAIN
1888 -> Nested loop antijoin
1889 -> Left hash join (no condition)
1890 -> Table scan on table1, with pushed condition: (table1.pk = 11)
1891 -> Hash
1892 -> Table scan on table2, with pushed condition: (table2.pk = 11)
1893 -> Nested loop inner join
1894 -> Single-row index lookup on table2s using PRIMARY (pk=table2.pk)
1895 -> Index lookup on table1s using col_char_16_key (col_char_16_key=table2s.col_char_16_key)
1896
1897 Warnings:
1898 Note 1276 Field or reference 'test.table1.col_int' of SELECT #2 was resolved in SELECT #1
1899 Note 1276 Field or reference 'test.table2.pk' of SELECT #2 was resolved in SELECT #1
1900 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
1901 Note 1003 Can't push table 'table2s' as child of 'table1', it is in a hash-probe-branch which can't be referred.
1902 Note 1003 Can't push table 'table1s' as child of 'table1', it is in a hash-probe-branch which can't be referred.
1903 Note 1003 Can't push table 'table2s' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
1904 Note 1003 Can't push table 'table1s' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
1905 Note 1003 Push of table 'table1s' as scan-child with lookup-root 'table2s' not implemented
1906 SELECT
1907 table1.pk, table2.pk
1908 FROM v AS table1
1909 left JOIN o AS table2 ON table1.pk = table2.pk
1910 WHERE NOT EXISTS (
1911 SELECT table1.col_int AS field4, table1s.col_char_16_key AS field5
1912 FROM d AS table1s
1913 JOIN m AS table2s ON table1s.col_char_16_key = table2s.col_char_16_key
1914 WHERE table2s.pk = table2.pk
1915 )
1916 and table1.pk = 11;
1917 pk pk
1918 explain format=tree SELECT
1919 table1.pk, table2.pk
1920 FROM t AS table1
1921 JOIN x AS table2 ON table1.col_int_key = table2.col_int
1922 WHERE NOT EXISTS (
1923 SELECT *
1924 FROM b AS table1s WHERE table1.col_int_key = table1s.col_int_key
1925 ) and table1.pk between 2 and 2;
1926 EXPLAIN
1927 -> Nested loop inner join
1928 -> Nested loop antijoin
1929 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: ((table1.pk = 2) and (table1.col_int_key is not null))
1930 -> Index lookup on table1s using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
1931 -> Index lookup on table2 using PRIMARY (col_int=table1.col_int_key), child of table1 in pushed join
1932
1933 Warnings:
1934 Note 1276 Field or reference 'test.table1.col_int_key' of SELECT #2 was resolved in SELECT #1
1935 SELECT
1936 table1.pk, table2.pk
1937 FROM t AS table1
1938 JOIN x AS table2 ON table1.col_int_key = table2.col_int
1939 WHERE NOT EXISTS (
1940 SELECT *
1941 FROM b AS table1s WHERE table1.col_int_key = table1s.col_int_key
1942 ) and table1.pk between 2 and 2;
1943 pk pk
1944 2 13
1945 2 15
1946 2 2
1947 2 22
1948 2 25
1949 2 6
1950 2 7
1951 explain format=tree SELECT
1952 table1.pk, table2.pk
1953 FROM v AS table1
1954 LEFT JOIN o table2 ON table1.pk = table2.pk
1955 WHERE NOT EXISTS (
1956 SELECT *
1957 FROM m AS table2s
1958 WHERE table2s.pk = table2.pk
1959 )
1960 and table1.pk = 11;
1961 EXPLAIN
1962 -> Nested loop antijoin
1963 -> Left hash join (no condition)
1964 -> Table scan on table1, with pushed condition: (table1.pk = 11)
1965 -> Hash
1966 -> Table scan on table2, with pushed condition: (table2.pk = 11)
1967 -> Single-row index lookup on table2s using PRIMARY (pk=table2.pk)
1968
1969 Warnings:
1970 Note 1276 Field or reference 'test.table2.pk' of SELECT #2 was resolved in SELECT #1
1971 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
1972 Note 1003 Can't push table 'table2s' as child of 'table1', it is in a hash-probe-branch which can't be referred.
1973 Note 1003 Can't push table 'table2s' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
1974 SELECT
1975 table1.pk, table2.pk
1976 FROM v AS table1
1977 LEFT JOIN o table2 ON table1.pk = table2.pk
1978 WHERE NOT EXISTS (
1979 SELECT *
1980 FROM m AS table2s
1981 WHERE table2s.pk = table2.pk
1982 )
1983 and table1.pk = 11;
1984 pk pk
1985 explain format=tree SELECT
1986 table1.pk, table2.pk
1987 FROM v AS table1
1988 LEFT JOIN o table2 ON table1.pk = table2.pk
1989 WHERE NOT EXISTS (
1990 SELECT *
1991 FROM m AS table2s
1992 WHERE table2s.col_int = table2.pk
1993 )
1994 and table1.pk = 11;
1995 EXPLAIN
1996 -> Nested loop antijoin
1997 -> Left hash join (no condition)
1998 -> Table scan on table1, with pushed condition: (table1.pk = 11)
1999 -> Hash
2000 -> Table scan on table2, with pushed condition: (table2.pk = 11)
2001 -> Index lookup on table2s using ix3 (col_int=table2.pk)
2002
2003 Warnings:
2004 Note 1276 Field or reference 'test.table2.pk' of SELECT #2 was resolved in SELECT #1
2005 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
2006 Note 1003 Can't push table 'table2s' as child of 'table1', it is in a hash-probe-branch which can't be referred.
2007 Note 1003 Can't push table 'table2s' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
2008 SELECT
2009 table1.pk, table2.pk
2010 FROM v AS table1
2011 LEFT JOIN o table2 ON table1.pk = table2.pk
2012 WHERE NOT EXISTS (
2013 SELECT *
2014 FROM m AS table2s
2015 WHERE table2s.col_int = table2.pk
2016 )
2017 and table1.pk = 11;
2018 pk pk
2019 11 11
2020 explain format=tree SELECT
2021 table1.pk, table2.pk
2022 FROM n AS table1
2023 JOIN t AS table2 ON table1.col_int_key = table2.col_int_key
2024 WHERE table1.pk IN (
2025 SELECT table1s.pk AS field3 FROM b AS table1s
2026 );
2027 EXPLAIN
2028 -> Nested loop inner join
2029 -> Nested loop inner join
2030 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.col_int_key is not null)
2031 -> Single-row index lookup on table1s using PRIMARY (pk=table1.pk), child of table1 in pushed join
2032 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
2033
2034 SELECT
2035 table1.pk, table2.pk
2036 FROM n AS table1
2037 JOIN t AS table2 ON table1.col_int_key = table2.col_int_key
2038 WHERE table1.pk IN (
2039 SELECT table1s.pk AS field3 FROM b AS table1s
2040 );
2041 pk pk
2042 1 12
2043 1 18
2044 1 2
2045 1 8
2046 1 9
2047 explain format=tree SELECT
2048 table1.pk, table2.pk, table3.pk
2049 FROM w AS table1
2050 STRAIGHT_JOIN (
2051 v AS table2 LEFT JOIN i AS table3 ON table2.pk = table3.col_int_key
2052 AND table2.pk = table3.col_int
2053 ) ON table1.col_int_key = table2.pk
2054 AND table1.col_int_unique = table2.pk
2055 WHERE
2056 NOT EXISTS (
2057 SELECT *
2058 FROM u AS table1s
2059 WHERE table3.col_char_16 = table1s.col_char_16
2060 )
2061 and table1.pk between 4 and 6;
2062 EXPLAIN
2063 -> Nested loop antijoin
2064 -> Nested loop left join
2065 -> Inner hash join (table2.pk = table1.col_int_key)
2066 -> Table scan on table2, activating pushed join of 3 tables
2067 -> Hash
2068 -> Index range scan on table1 using PRIMARY over (4 <= pk <= 6), with pushed condition: ((table1.col_int_unique = table1.col_int_key) and (table1.pk between 4 and 6))
2069 -> Index lookup on table3 using ix3 (col_int=table1.col_int_key, col_int_key=table1.col_int_key), child of table2 in pushed join
2070 -> Index lookup on table1s using ix1 (col_char_16=table3.col_char_16), child of table3 in pushed join
2071
2072 Warnings:
2073 Note 1276 Field or reference 'test.table3.col_char_16' of SELECT #2 was resolved in SELECT #1
2074 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
2075 Note 1003 Can't push table 'table3' as child of 'table1', it is in a hash-bucket-branch which can't be referred.
2076 Note 1003 Can't push table 'table1s' as child of 'table1', it is in a hash-bucket-branch which can't be referred.
2077 SELECT
2078 table1.pk, table2.pk, table3.pk
2079 FROM w AS table1
2080 STRAIGHT_JOIN (
2081 v AS table2 LEFT JOIN i AS table3 ON table2.pk = table3.col_int_key
2082 AND table2.pk = table3.col_int
2083 ) ON table1.col_int_key = table2.pk
2084 AND table1.col_int_unique = table2.pk
2085 WHERE
2086 NOT EXISTS (
2087 SELECT *
2088 FROM u AS table1s
2089 WHERE table3.col_char_16 = table1s.col_char_16
2090 )
2091 and table1.pk between 4 and 6;
2092 pk pk pk
2093 5 3 NULL
2094 explain format=tree SELECT
2095 table6.col_varchar_256 AS field1, table3.col_varchar_256 AS field2
2096 FROM
2097 f AS table1
2098 LEFT JOIN a AS table2 ON table1.col_int = table2.col_int_unique
2099 LEFT JOIN r AS table3
2100 JOIN g AS table4 ON table3.col_int = table4.col_int AND
2101 table3.col_int_unique = table4.col_int_unique
2102 ON table3.col_int IN (9)
2103 LEFT JOIN f AS table5
2104 JOIN z AS table6 ON table5.col_int = table6.col_int
2105 JOIN g AS table7 ON table6.col_int_key = table7.pk
2106 LEFT JOIN b AS table8
2107 LEFT JOIN m AS table9 ON (table8.col_int = table9.col_int AND
2108 table8.col_int_key = table9.col_int_key AND
2109 table8.col_int_unique = table9.col_int_unique) IS TRUE
2110 LEFT JOIN h AS table10 ON table8.col_int_key = table10.col_int_key AND
2111 table9.col_int_unique = table10.col_int_unique
2112 ON table6.col_int = table8.col_int AND
2113 table6.col_int_unique = table8.col_int_unique
2114 ON table2.col_int = table6.col_int AND
2115 table2.col_int_unique = table6.col_int_unique;
2116 EXPLAIN
2117 -> Nested loop left join
2118 -> Nested loop left join
2119 -> Nested loop left join
2120 -> Table scan on table1, activating pushed join of 3 tables
2121 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int), child of table1 in pushed join
2122 -> Nested loop inner join
2123 -> Index lookup on table3 using PRIMARY (col_int=9), activating pushed join of 2 tables
2124 -> Single-row index lookup on table4 using ix1 (col_int_unique=table3.col_int_unique), child of table3 in pushed join, with pushed condition: (table4.col_int = 9)
2125 -> Nested loop inner join
2126 -> Nested loop left join
2127 -> Nested loop inner join
2128 -> Table scan on table7, activating pushed join of 2 tables
2129 -> Index lookup on table6 using col_int_key (col_int_key=table7.pk), child of table7 in pushed join, with pushed condition: ((table6.col_int_unique = table2.col_int_unique) and (table6.col_int = table2.col_int))
2130 -> Nested loop left join
2131 -> Nested loop left join
2132 -> Single-row index lookup on table8 using ix1 (col_int_unique=table2.col_int_unique), child of table2 in pushed join, with pushed condition: (table8.col_int = table2.col_int)
2133 -> Filter: (((table2.col_int = table9.col_int) and (table8.col_int_key = table9.col_int_key) and (table2.col_int_unique = table9.col_int_unique)) is true)
2134 -> Table scan on table9, activating pushed join of 2 tables
2135 -> Single-row index lookup on table10 using ix1 (col_int_unique=table9.col_int_unique), child of table9 in pushed join, with pushed condition: (table10.col_int_key = table8.col_int_key)
2136 -> Index lookup on table5 using PRIMARY (col_int=table2.col_int)
2137
2138 Warnings:
2139 Note 1003 Can't push table 'table3' as child of 'table1', no parent-child dependency exists between these tables
2140 Note 1003 Can't push table 'table4' as child of 'table1', column 'table3.col_int_unique' refers a table which was not pushed
2141 Note 1003 Can't push table 'table7' as child, 'type' must be a 'ref' access
2142 Note 1003 Can't push table 'table6' as child of 'table1', column 'table7.pk' refers a table which was not pushed
2143 Note 1003 Can't push table 'table9' as child, 'type' must be a 'ref' access
2144 Note 1003 Can't push table 'table10' as child of 'table1', column 'table9.col_int_unique' refers a table which was not pushed
2145 Note 1003 Can't push outer joined table 'table5' as child of 'table1', table 'table6' in its dependant join-nest(s) is not part of the pushed join
2146 Note 1003 Can't push table 'table6' as child of 'table3', column 'table7.pk' refers a table which was not pushed
2147 Note 1003 Can't push table 'table10' as child of 'table3', column 'table9.col_int_unique' refers a table which was not pushed
2148 Note 1003 Can't push table 'table5' as child of 'table3', no parent-child dependency exists between these tables
2149 Note 1003 Can't push table 'table10' as child of 'table7', column 'table9.col_int_unique' refers a table which was not pushed
2150 Note 1003 Can't push table 'table5' as child of 'table7', no parent-child dependency exists between these tables
2151 Note 1003 Can't push table 'table5' as child of 'table9', no parent-child dependency exists between these tables
2152 SELECT
2153 table6.col_varchar_256 AS field1, table3.col_varchar_256 AS field2
2154 FROM
2155 f AS table1
2156 LEFT JOIN a AS table2 ON table1.col_int = table2.col_int_unique
2157 LEFT JOIN r AS table3
2158 JOIN g AS table4 ON table3.col_int = table4.col_int AND
2159 table3.col_int_unique = table4.col_int_unique
2160 ON table3.col_int IN (9)
2161 LEFT JOIN f AS table5
2162 JOIN z AS table6 ON table5.col_int = table6.col_int
2163 JOIN g AS table7 ON table6.col_int_key = table7.pk
2164 LEFT JOIN b AS table8
2165 LEFT JOIN m AS table9 ON (table8.col_int = table9.col_int AND
2166 table8.col_int_key = table9.col_int_key AND
2167 table8.col_int_unique = table9.col_int_unique) IS TRUE
2168 LEFT JOIN h AS table10 ON table8.col_int_key = table10.col_int_key AND
2169 table9.col_int_unique = table10.col_int_unique
2170 ON table6.col_int = table8.col_int AND
2171 table6.col_int_unique = table8.col_int_unique
2172 ON table2.col_int = table6.col_int AND
2173 table2.col_int_unique = table6.col_int_unique;
2174 field1 field2
2175 NULL NULL
2176 NULL NULL
2177 NULL NULL
2178 NULL NULL
2179 NULL NULL
2180 NULL NULL
2181 NULL NULL
2182 NULL NULL
2183 NULL NULL
2184 NULL NULL
2185 NULL NULL
2186 NULL NULL
2187 NULL NULL
2188 NULL NULL
2189 NULL NULL
2190 NULL NULL
2191 NULL NULL
2192 explain format=tree SELECT straight_join
2193 table1.pk, table2.pk, table3.pk,
2194 table1.col_int_unique AS field1, table1.col_varchar_256_unique AS field2
2195 FROM p AS table1
2196 JOIN m AS table2 ON table1.col_int_key = table2.pk
2197 LEFT JOIN h AS table3 ON table1.col_int = table3.col_int AND table2.col_int_unique = table3.col_int_unique
2198 ORDER BY field1,field2;
2199 EXPLAIN
2200 -> Nested loop left join
2201 -> Nested loop inner join
2202 -> Sort: table1.col_int_unique, table1.col_varchar_256_unique
2203 -> Table scan on table1, with pushed condition: (table1.col_int_key is not null)
2204 -> Single-row index lookup on table2 using PRIMARY (pk=table1.col_int_key), activating pushed join of 2 tables
2205 -> Filter: (table3.col_int = table1.col_int)
2206 -> Single-row index lookup on table3 using ix1 (col_int_unique=table2.col_int_unique), child of table2 in pushed join
2207
2208 Warnings:
2209 Note 1003 Can't push table 'table2' as child of 'table1', it is in a sorted-branch which can't be referred.
2210 Note 1003 Can't push table 'table3' as child of 'table1', it is in a sorted-branch which can't be referred.
2211 SELECT straight_join
2212 table1.pk, table2.pk, table3.pk,
2213 table1.col_int_unique AS field1, table1.col_varchar_256_unique AS field2
2214 FROM p AS table1
2215 JOIN m AS table2 ON table1.col_int_key = table2.pk
2216 LEFT JOIN h AS table3 ON table1.col_int = table3.col_int AND table2.col_int_unique = table3.col_int_unique
2217 ORDER BY field1,field2;
2218 pk pk pk field1 field2
2219 1 2 NULL 13 gsxtwkvtbqscgcbnjz
2220 10 1 NULL 14 a
2221 11 3 NULL 84 can
2222 13 2 NULL 87 xzkjxgrcyqi
2223 15 2 NULL 71 me
2224 16 3 NULL 7 you're
2225 18 1 NULL 8 mkcharcpqd
2226 2 3 NULL 2 we
2227 20 2 NULL 38 see
2228 21 3 NULL 20 gyusxngucicqcjhvtmfnumoxifzchbfu
2229 3 2 NULL 17 could
2230 4 3 NULL 99 you
2231 6 3 NULL 10 emywwmshhimsbq
2232 9 2 NULL 50 qljcfstzgruojzffwbhxogpkk
2233 explain format=tree SELECT
2234 table1.pk, table2.pk, table3.pk
2235 FROM k AS table1
2236 LEFT JOIN m AS table2 ON table1.col_int_unique = table2.col_int_unique
2237 JOIN q AS table3 ON table2.col_int = table3.col_int AND table1.col_int_unique = table3.col_int_unique;
2238 EXPLAIN
2239 -> Nested loop inner join
2240 -> Inner hash join (table2.col_int_unique = table1.col_int_unique)
2241 -> Index range scan on table2 using ix3 over (NULL < col_int), activating pushed join of 2 tables, with pushed condition: (table2.col_int is not null)
2242 -> Hash
2243 -> Table scan on table1, with pushed condition: (table1.col_int_unique is not null)
2244 -> Single-row index lookup on table3 using ix1 (col_int=table2.col_int, col_int_unique=table1.col_int_unique), child of table2 in pushed join
2245
2246 Warnings:
2247 Note 1003 Can't push table 'table2' as child, access type 'Range-scan' not implemented
2248 Note 1003 Can't push table 'table3' as child of 'table1', it is in a hash-bucket-branch which can't be referred.
2249 SELECT
2250 table1.pk, table2.pk, table3.pk
2251 FROM k AS table1
2252 LEFT JOIN m AS table2 ON table1.col_int_unique = table2.col_int_unique
2253 JOIN q AS table3 ON table2.col_int = table3.col_int AND table1.col_int_unique = table3.col_int_unique;
2254 pk pk pk
2255 explain format=tree SELECT
2256 DISTINCT table2.col_varchar_256_unique AS field1, table1.col_varchar_256_unique AS field2
2257 FROM d AS table1
2258 JOIN d AS table2
2259 JOIN h AS table3
2260 INNER JOIN e AS table4 ON table3.col_int_key = table4.col_int_key OR table3.col_int_unique = table4.col_int_unique
2261 ON table2.col_int_unique = table4.col_int_unique
2262 ON table1.col_int_unique = table2.col_int_unique;
2263 EXPLAIN
2264 -> Table scan on <temporary>
2265 -> Temporary table with deduplication
2266 -> Nested loop inner join
2267 -> Nested loop inner join
2268 -> Inner hash join (no condition)
2269 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.col_int_unique is not null)
2270 -> Hash
2271 -> Table scan on table3
2272 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join
2273 -> Limit: 1 row(s)
2274 -> Filter: ((table4.col_int_key = table3.col_int_key) or (table1.col_int_unique = table3.col_int_unique))
2275 -> Single-row index lookup on table4 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join
2276
2277 Warnings:
2278 Note 1003 Can't push table 'table1' as child, 'type' must be a 'ref' access
2279 Note 1003 Can't push table 'table2' as child of 'table3', it is in a hash-bucket-branch which can't be referred.
2280 Note 1003 Can't push table 'table4' as child of 'table3', it is in a hash-bucket-branch which can't be referred.
2281 SELECT
2282 DISTINCT table2.col_varchar_256_unique AS field1, table1.col_varchar_256_unique AS field2
2283 FROM d AS table1
2284 JOIN d AS table2
2285 JOIN h AS table3
2286 INNER JOIN e AS table4 ON table3.col_int_key = table4.col_int_key OR table3.col_int_unique = table4.col_int_unique
2287 ON table2.col_int_unique = table4.col_int_unique
2288 ON table1.col_int_unique = table2.col_int_unique;
2289 field1 field2
2290 my my
2291 explain format=tree SELECT
2292 table1.pk, table2.pk
2293 FROM j AS table1
2294 JOIN r AS table2 ON table1.col_int = table2.col_int AND table1.col_int_unique = table2.col_int_unique
2295 WHERE table1.col_int_key IN (SELECT table1s.col_int_key AS field6 FROM z AS table1s);
2296 EXPLAIN
2297 -> Nested loop inner join
2298 -> Nested loop semijoin
2299 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: ((table1.col_int_key is not null) and (table1.col_int_unique is not null))
2300 -> Index lookup on table1s using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
2301 -> Index lookup on table2 using ix1 (col_int=table1.col_int, col_int_unique=table1.col_int_unique), child of table1 in pushed join
2302
2303 SELECT
2304 table1.pk, table2.pk
2305 FROM j AS table1
2306 JOIN r AS table2 ON table1.col_int = table2.col_int AND table1.col_int_unique = table2.col_int_unique
2307 WHERE table1.col_int_key IN (SELECT table1s.col_int_key AS field6 FROM z AS table1s);
2308 pk pk
2309 explain format=tree SELECT
2310 table2.col_int_unique AS field1, table1.col_int AS field
2311 FROM m AS table1
2312 JOIN x AS table2 ON table1.col_int_key = table2.col_int_key AND table1.col_int_unique = table2.col_int_unique
2313 WHERE table2.col_int_key IN (
2314 SELECT table2s.col_int_key AS field3
2315 FROM z AS table1s
2316 LEFT JOIN t AS table2s
2317 JOIN l AS table3s ON table2s.col_int = table3s.col_int_key
2318 ON table1s.pk = table2s.col_int_unique
2319 WHERE table1.col_int_unique = 4 IS UNKNOWN)
2320 AND table2.col_varchar_10_key = table1.col_varchar_256;
2321 EXPLAIN
2322 -> Nested loop semijoin
2323 -> Nested loop inner join
2324 -> Filter: ((table1.col_int_unique = 4) is null)
2325 -> Table scan on table1, activating pushed join of 4 tables, with pushed condition: (table1.col_varchar_256 is not null)
2326 -> Filter: (table2.col_varchar_10_key = table1.col_varchar_256)
2327 -> Index lookup on table2 using col_varchar_10_key (col_varchar_10_key=table1.col_varchar_256), with pushed condition: ((table2.col_int_unique = table1.col_int_unique) and (table2.col_int_key = table1.col_int_key))
2328 -> Nested loop inner join
2329 -> Nested loop inner join
2330 -> Index lookup on table2s using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join, with pushed condition: (table2s.col_int_unique is not null)
2331 -> Single-row index lookup on table1s using PRIMARY (pk=table2s.col_int_unique), child of table2s in pushed join
2332 -> Index lookup on table3s using col_int_key (col_int_key=table2s.col_int), child of table2s in pushed join
2333
2334 Warnings:
2335 Note 1276 Field or reference 'test.table1.col_int_unique' of SELECT #2 was resolved in SELECT #1
2336 Note 1003 Can't push table 'table2' as child, column 'col_varchar_10_key' does not have same datatype as ref'ed column 'table1.col_varchar_256'
2337 SELECT
2338 table2.col_int_unique AS field1, table1.col_int AS field
2339 FROM m AS table1
2340 JOIN x AS table2 ON table1.col_int_key = table2.col_int_key AND table1.col_int_unique = table2.col_int_unique
2341 WHERE table2.col_int_key IN (
2342 SELECT table2s.col_int_key AS field3
2343 FROM z AS table1s
2344 LEFT JOIN t AS table2s
2345 JOIN l AS table3s ON table2s.col_int = table3s.col_int_key
2346 ON table1s.pk = table2s.col_int_unique
2347 WHERE table1.col_int_unique = 4 IS UNKNOWN)
2348 AND table2.col_varchar_10_key = table1.col_varchar_256;
2349 field1 field
2350 explain format=tree SELECT
2351 table5.pk, table1.pk, table2.pk, table3.pk, table4.pk
2352 FROM q AS table1
2353 STRAIGHT_JOIN h AS table2 ON table1.col_int_key = table2.col_int_key AND
2354 table1.col_int_unique = table2.col_int_unique
2355 LEFT JOIN u AS table3
2356 STRAIGHT_JOIN x AS table4 ON table3.col_int_key = table4.col_int_key
2357 ON table2.col_int_key = table3.col_int_key AND
2358 table2.col_int_unique = table4.col_int_unique
2359 JOIN f AS table5 ON table2.col_int_key = table5.pk;
2360 EXPLAIN
2361 -> Nested loop left join
2362 -> Nested loop inner join
2363 -> Nested loop inner join
2364 -> Table scan on table5, activating pushed join of 5 tables
2365 -> Index lookup on table1 using ix2 (col_int_key=table5.pk), child of table5 in pushed join, with pushed condition: (table1.col_int_unique is not null)
2366 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join, with pushed condition: (table2.col_int_key = table5.pk)
2367 -> Nested loop inner join
2368 -> Index lookup on table3 using col_int_key (col_int_key=table5.pk), child of table1 in pushed join
2369 -> Index lookup on table4 using col_int_key (col_int_key=table5.pk), child of table1 in pushed join, with pushed condition: (table4.col_int_unique = table1.col_int_unique)
2370
2371 SELECT
2372 table5.pk, table1.pk, table2.pk, table3.pk, table4.pk
2373 FROM q AS table1
2374 STRAIGHT_JOIN h AS table2 ON table1.col_int_key = table2.col_int_key AND
2375 table1.col_int_unique = table2.col_int_unique
2376 LEFT JOIN u AS table3
2377 STRAIGHT_JOIN x AS table4 ON table3.col_int_key = table4.col_int_key
2378 ON table2.col_int_key = table3.col_int_key AND
2379 table2.col_int_unique = table4.col_int_unique
2380 JOIN f AS table5 ON table2.col_int_key = table5.pk;
2381 pk pk pk pk pk
2382 1 19 10 NULL NULL
2383 explain format=tree SELECT
2384 table1.pk, table2.pk, table4.pk, table3.pk
2385 FROM n AS table1
2386 JOIN a AS table2 ON table1.col_int = table2.col_int AND table1.col_int_unique = table2.col_int_unique
2387 LEFT OUTER JOIN x AS table3
2388 INNER JOIN m AS table4 ON table3.col_int = table4.pk
2389 ON table2.col_int_key = table3.col_int_unique AND table2.col_int_unique = table3.col_int;
2390 EXPLAIN
2391 -> Nested loop left join
2392 -> Nested loop inner join
2393 -> Table scan on table1, activating pushed join of 4 tables, with pushed condition: (table1.col_int_unique is not null)
2394 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join, with pushed condition: (table2.col_int = table1.col_int)
2395 -> Nested loop inner join
2396 -> Single-row index lookup on table4 using PRIMARY (pk=table1.col_int_unique), child of table2 in pushed join
2397 -> Index lookup on table3 using PRIMARY (col_int=table1.col_int_unique), child of table2 in pushed join, with pushed condition: (table3.col_int_unique = table2.col_int_key)
2398
2399 SELECT
2400 table1.pk, table2.pk, table4.pk, table3.pk
2401 FROM n AS table1
2402 JOIN a AS table2 ON table1.col_int = table2.col_int AND table1.col_int_unique = table2.col_int_unique
2403 LEFT OUTER JOIN x AS table3
2404 INNER JOIN m AS table4 ON table3.col_int = table4.pk
2405 ON table2.col_int_key = table3.col_int_unique AND table2.col_int_unique = table3.col_int;
2406 pk pk pk pk
2407 explain format=tree SELECT
2408 table2.col_varchar_10_key AS field1
2409 FROM p AS table1
2410 INNER JOIN a AS table2
2411 JOIN v AS table3 ON table2.col_int = table3.col_int AND
2412 table2.col_int_unique = table3.col_int_unique
2413 ON table1.col_int = table3.col_int AND
2414 table1.col_int_key = table2.col_int_key AND
2415 table1.col_int_unique = table3.col_int_unique
2416 WHERE
2417 table3.col_int_unique = 7 AND
2418 table3.col_int = 4 OR
2419 NOT table3.col_int_unique = 9 OR
2420 table1.col_varchar_10 = table1.col_varchar_256_unique;
2421 EXPLAIN
2422 -> Nested loop inner join
2423 -> Nested loop inner join
2424 -> Table scan on table3, activating pushed join of 3 tables, with pushed condition: ((table3.col_int_unique is not null) and (table3.col_int is not null))
2425 -> Single-row index lookup on table2 using ix1 (col_int_unique=table3.col_int_unique), child of table3 in pushed join, with pushed condition: (table2.col_int = table3.col_int)
2426 -> Filter: (((table3.col_int = 4) and (table3.col_int_unique = 7)) or (table3.col_int_unique <> 9) or (table1.col_varchar_10 = table1.col_varchar_256_unique))
2427 -> Single-row index lookup on table1 using ix1 (col_int=table3.col_int, col_int_unique=table3.col_int_unique), child of table2 in pushed join, with pushed condition: (table1.col_int_key = table2.col_int_key)
2428
2429 SELECT
2430 table2.col_varchar_10_key AS field1
2431 FROM p AS table1
2432 INNER JOIN a AS table2
2433 JOIN v AS table3 ON table2.col_int = table3.col_int AND
2434 table2.col_int_unique = table3.col_int_unique
2435 ON table1.col_int = table3.col_int AND
2436 table1.col_int_key = table2.col_int_key AND
2437 table1.col_int_unique = table3.col_int_unique
2438 WHERE
2439 table3.col_int_unique = 7 AND
2440 table3.col_int = 4 OR
2441 NOT table3.col_int_unique = 9 OR
2442 table1.col_varchar_10 = table1.col_varchar_256_unique;
2443 field1
2444 explain format=tree SELECT
2445 table1.pk, table2.pk, table3.pk
2446 FROM i AS table1
2447 LEFT JOIN z AS table2 ON table1.col_int_key = table2.col_int_key AND
2448 table1.col_int_unique = table2.col_int_unique
2449 LEFT JOIN m AS table3 ON table2.col_int_key = table3.col_int_key;
2450 EXPLAIN
2451 -> Nested loop left join
2452 -> Nested loop left join
2453 -> Table scan on table1, activating pushed join of 3 tables
2454 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join, with pushed condition: (table2.col_int_unique = table1.col_int_unique)
2455 -> Index lookup on table3 using col_int_key (col_int_key=table2.col_int_key), child of table2 in pushed join
2456
2457 SELECT
2458 table1.pk, table2.pk, table3.pk
2459 FROM i AS table1
2460 LEFT JOIN z AS table2 ON table1.col_int_key = table2.col_int_key AND
2461 table1.col_int_unique = table2.col_int_unique
2462 LEFT JOIN m AS table3 ON table2.col_int_key = table3.col_int_key;
2463 pk pk pk
2464 1 NULL NULL
2465 10 72 10
2466 10 72 11
2467 10 72 13
2468 10 72 15
2469 10 72 18
2470 10 72 2
2471 10 72 3
2472 10 72 4
2473 11 NULL NULL
2474 12 NULL NULL
2475 13 NULL NULL
2476 14 NULL NULL
2477 2 NULL NULL
2478 3 NULL NULL
2479 4 60 1
2480 4 60 12
2481 4 60 20
2482 4 60 5
2483 4 60 6
2484 5 NULL NULL
2485 6 NULL NULL
2486 7 NULL NULL
2487 8 NULL NULL
2488 9 NULL NULL
2489 explain format=tree SELECT
2490 table1.pk, table3.pk, table2.pk, table5.pk, table4.pk, table6.pk
2491 FROM f AS table1
2492 LEFT JOIN ( j AS table3
2493 straight_JOIN x AS table2 ON table2.col_int_key = table3.col_int_key AND
2494 table2.col_int_unique = table3.col_int_unique)
2495 LEFT JOIN ((d AS table4
2496 straight_JOIN b AS table5 ON table4.pk = table5.col_int_unique)
2497 LEFT JOIN x AS table6 ON table5.col_int_unique = table6.col_int)
2498 ON table2.col_int_key = table5.col_int_unique AND
2499 table2.col_int_unique = table5.col_int_key
2500 ON table1.pk = table3.col_int_unique;
2501 EXPLAIN
2502 -> Nested loop left join
2503 -> Table scan on table1
2504 -> Nested loop left join
2505 -> Nested loop inner join
2506 -> Table scan on table3, activating pushed join of 5 tables, with pushed condition: (table3.col_int_unique = table1.pk)
2507 -> Index lookup on table2 using col_int_key (col_int_key=table3.col_int_key), child of table3 in pushed join, with pushed condition: (table2.col_int_unique = table1.pk)
2508 -> Nested loop left join
2509 -> Nested loop inner join
2510 -> Single-row index lookup on table4 using PRIMARY (pk=table2.col_int_key), child of table2 in pushed join, with pushed condition: (table4.pk = table3.col_int_key)
2511 -> Single-row index lookup on table5 using ix1 (col_int_unique=table2.col_int_key), child of table2 in pushed join, with pushed condition: ((table5.col_int_key = table1.pk) and (table5.col_int_unique = table3.col_int_key))
2512 -> Index lookup on table6 using PRIMARY (col_int=table2.col_int_key), child of table2 in pushed join, with pushed condition: (table6.col_int = table3.col_int_key)
2513
2514 Warnings:
2515 Note 1003 Can't push table 'table3' as child, 'type' must be a 'ref' access
2516 Note 1003 Can't push table 'table2' as child of 'table1', column 'table3.col_int_key' refers a table which was not pushed
2517 Note 1003 Can't push table 'table4' as child of 'table1', column 'table2.col_int_key' refers a table which was not pushed
2518 Note 1003 Can't push table 'table5' as child of 'table1', column 'table2.col_int_key' refers a table which was not pushed
2519 Note 1003 Can't push table 'table6' as child of 'table1', column 'table2.col_int_key' refers a table which was not pushed
2520 SELECT
2521 table1.pk, table3.pk, table2.pk, table5.pk, table4.pk, table6.pk
2522 FROM f AS table1
2523 LEFT JOIN ( j AS table3
2524 straight_JOIN x AS table2 ON table2.col_int_key = table3.col_int_key AND
2525 table2.col_int_unique = table3.col_int_unique)
2526 LEFT JOIN ((d AS table4
2527 straight_JOIN b AS table5 ON table4.pk = table5.col_int_unique)
2528 LEFT JOIN x AS table6 ON table5.col_int_unique = table6.col_int)
2529 ON table2.col_int_key = table5.col_int_unique AND
2530 table2.col_int_unique = table5.col_int_key
2531 ON table1.pk = table3.col_int_unique;
2532 pk pk pk pk pk pk
2533 1 NULL NULL NULL NULL NULL
2534 10 NULL NULL NULL NULL NULL
2535 11 NULL NULL NULL NULL NULL
2536 12 NULL NULL NULL NULL NULL
2537 13 NULL NULL NULL NULL NULL
2538 14 NULL NULL NULL NULL NULL
2539 15 NULL NULL NULL NULL NULL
2540 16 NULL NULL NULL NULL NULL
2541 17 NULL NULL NULL NULL NULL
2542 2 NULL NULL NULL NULL NULL
2543 3 NULL NULL NULL NULL NULL
2544 4 NULL NULL NULL NULL NULL
2545 5 NULL NULL NULL NULL NULL
2546 6 18 6 NULL NULL NULL
2547 7 NULL NULL NULL NULL NULL
2548 8 NULL NULL NULL NULL NULL
2549 9 NULL NULL NULL NULL NULL
2550 explain format=tree SELECT
2551 table1.pk, table3.pk, table2.pk, table5.pk, table4.pk, table6.pk
2552 FROM f AS table1
2553 LEFT JOIN ( j AS table3
2554 straight_JOIN x AS table2 ON table2.col_int_key = table3.col_int_key AND
2555 table2.col_int_unique = table3.col_int_unique)
2556 LEFT JOIN (b AS table5
2557 JOIN d AS table4 ON table4.pk = table5.col_int_unique)
2558 LEFT JOIN x AS table6 ON table5.col_int_unique = table6.col_int
2559 ON table2.col_int_key = table5.col_int_unique AND
2560 table2.col_int_unique = table5.col_int_key
2561 ON table1.pk = table3.col_int_unique;
2562 EXPLAIN
2563 -> Nested loop left join
2564 -> Table scan on table1
2565 -> Nested loop left join
2566 -> Nested loop inner join
2567 -> Table scan on table3, activating pushed join of 5 tables, with pushed condition: (table3.col_int_unique = table1.pk)
2568 -> Index lookup on table2 using col_int_key (col_int_key=table3.col_int_key), child of table3 in pushed join, with pushed condition: (table2.col_int_unique = table1.pk)
2569 -> Nested loop left join
2570 -> Nested loop inner join
2571 -> Single-row index lookup on table5 using ix1 (col_int_unique=table2.col_int_key), child of table2 in pushed join, with pushed condition: ((table5.col_int_key = table1.pk) and (table5.col_int_unique = table3.col_int_key))
2572 -> Single-row index lookup on table4 using PRIMARY (pk=table2.col_int_key), child of table2 in pushed join, with pushed condition: (table4.pk = table3.col_int_key)
2573 -> Index lookup on table6 using PRIMARY (col_int=table2.col_int_key), child of table2 in pushed join, with pushed condition: (table6.col_int = table3.col_int_key)
2574
2575 Warnings:
2576 Note 1003 Can't push table 'table3' as child, 'type' must be a 'ref' access
2577 Note 1003 Can't push table 'table2' as child of 'table1', column 'table3.col_int_key' refers a table which was not pushed
2578 Note 1003 Can't push table 'table5' as child of 'table1', column 'table2.col_int_key' refers a table which was not pushed
2579 Note 1003 Can't push table 'table4' as child of 'table1', column 'table2.col_int_key' refers a table which was not pushed
2580 Note 1003 Can't push table 'table6' as child of 'table1', column 'table2.col_int_key' refers a table which was not pushed
2581 SELECT
2582 table1.pk, table3.pk, table2.pk, table5.pk, table4.pk, table6.pk
2583 FROM f AS table1
2584 LEFT JOIN ( j AS table3
2585 straight_JOIN x AS table2 ON table2.col_int_key = table3.col_int_key AND
2586 table2.col_int_unique = table3.col_int_unique)
2587 LEFT JOIN (b AS table5
2588 JOIN d AS table4 ON table4.pk = table5.col_int_unique)
2589 LEFT JOIN x AS table6 ON table5.col_int_unique = table6.col_int
2590 ON table2.col_int_key = table5.col_int_unique AND
2591 table2.col_int_unique = table5.col_int_key
2592 ON table1.pk = table3.col_int_unique;
2593 pk pk pk pk pk pk
2594 1 NULL NULL NULL NULL NULL
2595 10 NULL NULL NULL NULL NULL
2596 11 NULL NULL NULL NULL NULL
2597 12 NULL NULL NULL NULL NULL
2598 13 NULL NULL NULL NULL NULL
2599 14 NULL NULL NULL NULL NULL
2600 15 NULL NULL NULL NULL NULL
2601 16 NULL NULL NULL NULL NULL
2602 17 NULL NULL NULL NULL NULL
2603 2 NULL NULL NULL NULL NULL
2604 3 NULL NULL NULL NULL NULL
2605 4 NULL NULL NULL NULL NULL
2606 5 NULL NULL NULL NULL NULL
2607 6 18 6 NULL NULL NULL
2608 7 NULL NULL NULL NULL NULL
2609 8 NULL NULL NULL NULL NULL
2610 9 NULL NULL NULL NULL NULL
2611 explain format=tree SELECT
2612 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk
2613 FROM
2614 (b AS table1
2615 JOIN b AS table2 ON table1.col_int_key = table2.col_int_unique AND
2616 table1.col_int_unique = table2.col_int_key)
2617 LEFT JOIN
2618 ((c AS table3 JOIN q AS table4 ON table3.col_int_key = table4.pk)
2619 JOIN i AS table5 ON table3.col_int_unique = table5.col_int_key
2620 )
2621 ON table2.col_int = table4.col_int AND
2622 table2.col_int_key = table5.col_int_key AND
2623 table1.col_int_unique = table3.col_int_unique;
2624 EXPLAIN
2625 -> Nested loop left join
2626 -> Nested loop inner join
2627 -> Table scan on table1, activating pushed join of 5 tables, with pushed condition: (table1.col_int_key is not null)
2628 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int_key), child of table1 in pushed join, with pushed condition: (table2.col_int_key = table1.col_int_unique)
2629 -> Nested loop inner join
2630 -> Nested loop inner join
2631 -> Index lookup on table3 using ix1 (col_int_unique=table1.col_int_unique), child of table2 in pushed join
2632 -> Single-row index lookup on table4 using PRIMARY (pk=table3.col_int_key), child of table3 in pushed join, with pushed condition: (table4.col_int = table2.col_int)
2633 -> Index lookup on table5 using col_int_key (col_int_key=table1.col_int_unique), child of table2 in pushed join
2634
2635 SELECT
2636 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk
2637 FROM
2638 (b AS table1
2639 JOIN b AS table2 ON table1.col_int_key = table2.col_int_unique AND
2640 table1.col_int_unique = table2.col_int_key)
2641 LEFT JOIN
2642 ((c AS table3 JOIN q AS table4 ON table3.col_int_key = table4.pk)
2643 JOIN i AS table5 ON table3.col_int_unique = table5.col_int_key
2644 )
2645 ON table2.col_int = table4.col_int AND
2646 table2.col_int_key = table5.col_int_key AND
2647 table1.col_int_unique = table3.col_int_unique;
2648 pk pk pk pk pk
2649 explain format=tree SELECT table1.pk, table2.pk
2650 FROM t AS table1
2651 JOIN b AS table2 on table2.pk = table1.pk
2652 WHERE table1.col_int IN (8,7) AND
2653 table1.col_int_unique = 4;
2654 EXPLAIN
2655 -> Nested loop inner join
2656 -> Index range scan on table1 using ix1 over (col_int = 7 AND col_int_unique = 4) OR (col_int = 8 AND col_int_unique = 4), activating pushed join of 2 tables, with pushed condition: ((table1.col_int_unique = 4) and (table1.col_int in (8,7)))
2657 -> Single-row index lookup on table2 using PRIMARY (pk=table1.pk), child of table1 in pushed join
2658
2659 SELECT table1.pk, table2.pk
2660 FROM t AS table1
2661 JOIN b AS table2 on table2.pk = table1.pk
2662 WHERE table1.col_int IN (8,7) AND
2663 table1.col_int_unique = 4;
2664 pk pk
2665 explain format=tree SELECT
2666 table1.pk, table2.pk, table3.pk
2667 FROM c AS table1
2668 JOIN g AS table2
2669 LEFT JOIN e AS table3 ON table2.col_int_key = table3.pk AND table2.col_int_unique = table3.col_int_unique
2670 ON table3.col_int IS NULL;
2671 EXPLAIN
2672 -> Filter: (table3.col_int is null)
2673 -> Nested loop antijoin
2674 -> Inner hash join (no condition)
2675 -> Table scan on table2, activating pushed join of 2 tables
2676 -> Hash
2677 -> Table scan on table1
2678 -> Single-row index lookup on table3 using ix1 (col_int_unique=table2.col_int_unique), child of table2 in pushed join, with pushed condition: (table3.pk = table2.col_int_key)
2679
2680 Warnings:
2681 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
2682 Note 1003 Can't push table 'table3' as child of 'table1', it is in a hash-bucket-branch which can't be referred.
2683 SELECT
2684 table1.pk, table2.pk, table3.pk
2685 FROM c AS table1
2686 JOIN g AS table2
2687 LEFT JOIN e AS table3 ON table2.col_int_key = table3.pk AND table2.col_int_unique = table3.col_int_unique
2688 ON table3.col_int IS NULL;
2689 pk pk pk
2690 1 1 NULL
2691 1 10 NULL
2692 1 11 NULL
2693 1 12 NULL
2694 1 13 NULL
2695 1 14 NULL
2696 1 15 NULL
2697 1 16 NULL
2698 1 17 NULL
2699 1 18 NULL
2700 1 19 NULL
2701 1 2 NULL
2702 1 3 NULL
2703 1 4 NULL
2704 1 5 NULL
2705 1 6 NULL
2706 1 7 NULL
2707 1 8 NULL
2708 1 9 NULL
2709 2 1 NULL
2710 2 10 NULL
2711 2 11 NULL
2712 2 12 NULL
2713 2 13 NULL
2714 2 14 NULL
2715 2 15 NULL
2716 2 16 NULL
2717 2 17 NULL
2718 2 18 NULL
2719 2 19 NULL
2720 2 2 NULL
2721 2 3 NULL
2722 2 4 NULL
2723 2 5 NULL
2724 2 6 NULL
2725 2 7 NULL
2726 2 8 NULL
2727 2 9 NULL
2728 3 1 NULL
2729 3 10 NULL
2730 3 11 NULL
2731 3 12 NULL
2732 3 13 NULL
2733 3 14 NULL
2734 3 15 NULL
2735 3 16 NULL
2736 3 17 NULL
2737 3 18 NULL
2738 3 19 NULL
2739 3 2 NULL
2740 3 3 NULL
2741 3 4 NULL
2742 3 5 NULL
2743 3 6 NULL
2744 3 7 NULL
2745 3 8 NULL
2746 3 9 NULL
2747 4 1 NULL
2748 4 10 NULL
2749 4 11 NULL
2750 4 12 NULL
2751 4 13 NULL
2752 4 14 NULL
2753 4 15 NULL
2754 4 16 NULL
2755 4 17 NULL
2756 4 18 NULL
2757 4 19 NULL
2758 4 2 NULL
2759 4 3 NULL
2760 4 4 NULL
2761 4 5 NULL
2762 4 6 NULL
2763 4 7 NULL
2764 4 8 NULL
2765 4 9 NULL
2766 5 1 NULL
2767 5 10 NULL
2768 5 11 NULL
2769 5 12 NULL
2770 5 13 NULL
2771 5 14 NULL
2772 5 15 NULL
2773 5 16 NULL
2774 5 17 NULL
2775 5 18 NULL
2776 5 19 NULL
2777 5 2 NULL
2778 5 3 NULL
2779 5 4 NULL
2780 5 5 NULL
2781 5 6 NULL
2782 5 7 NULL
2783 5 8 NULL
2784 5 9 NULL
2785 explain format=tree SELECT DISTINCT
2786 table1.pk, table2.pk, table3.pk, table4.pk
2787 FROM a AS table1
2788 LEFT JOIN d AS table2
2789 LEFT JOIN r AS table3 ON table2.col_int IS NULL
2790 ON table1.col_int_key = table2.col_int AND
2791 table1.col_int_unique = table2.col_int
2792 LEFT JOIN q AS table4 ON table3.col_int = table4.col_int AND
2793 table2.col_int_key = table4.col_int_key AND
2794 table2.col_int_unique = table4.col_int_unique;
2795 EXPLAIN
2796 -> Table scan on <temporary>
2797 -> Temporary table with deduplication
2798 -> Nested loop left join
2799 -> Nested loop left join
2800 -> Table scan on table1
2801 -> Filter: ((table1.col_int_unique = table1.col_int_key) and (table2.col_int = table1.col_int_key))
2802 -> Left hash join (no condition)
2803 -> Table scan on table2
2804 -> Hash
2805 -> Table scan on table3, with pushed condition: (table1.col_int_key is null)
2806 -> Filter: (table4.col_int = table3.col_int)
2807 -> Single-row index lookup on table4 using ix2 (col_int_key=table2.col_int_key, col_int_unique=table2.col_int_unique)
2808
2809 Warnings:
2810 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
2811 Note 1003 Can't push table 'table3' as child, 'type' must be a 'ref' access
2812 Note 1003 Can't push table 'table4' as child of 'table1', column 'table2.col_int_key' is in a hash-probe-branch which can't be referred
2813 Note 1003 Can't push table 'table4' as child of 'table2', it is in a hash-probe-branch which can't be referred.
2814 Note 1003 Can't push table 'table4' as child of 'table3', it is in a hash-bucket-branch which can't be referred.
2815 SELECT DISTINCT
2816 table1.pk, table2.pk, table3.pk, table4.pk
2817 FROM a AS table1
2818 LEFT JOIN d AS table2
2819 LEFT JOIN r AS table3 ON table2.col_int IS NULL
2820 ON table1.col_int_key = table2.col_int AND
2821 table1.col_int_unique = table2.col_int
2822 LEFT JOIN q AS table4 ON table3.col_int = table4.col_int AND
2823 table2.col_int_key = table4.col_int_key AND
2824 table2.col_int_unique = table4.col_int_unique;
2825 pk pk pk pk
2826 explain format=tree SELECT table1.pk, table2.pk
2827 FROM p AS table1
2828 JOIN d AS table2 ON table1.col_int_unique = table2.pk
2829 WHERE NOT EXISTS (
2830 SELECT table1.col_varchar_10_unique
2831 FROM k AS table1s JOIN c AS table2s ON table1s.pk = table2s.pk
2832 );
2833 EXPLAIN
2834 -> Nested loop antijoin
2835 -> Nested loop inner join
2836 -> Table scan on table1, activating pushed join of 2 tables, with pushed condition: (table1.col_int_unique is not null)
2837 -> Single-row index lookup on table2 using PRIMARY (pk=table1.col_int_unique), child of table1 in pushed join
2838 -> Constant row from <subquery2>
2839 -> Materialize with deduplication
2840 -> Nested loop inner join
2841 -> Table scan on table2s, activating pushed join of 2 tables
2842 -> Index lookup on table1s using PRIMARY (pk=table2s.pk), child of table2s in pushed join
2843
2844 Warnings:
2845 Note 1276 Field or reference 'test.table1.col_varchar_10_unique' of SELECT #2 was resolved in SELECT #1
2846 Note 1003 Can't push table 'table2s' as child, 'type' must be a 'ref' access
2847 Note 1003 Can't push table 'table1s' as child of 'table1', it is in a query-branch which can't be referred.
2848 SELECT table1.pk, table2.pk
2849 FROM p AS table1
2850 JOIN d AS table2 ON table1.col_int_unique = table2.pk
2851 WHERE NOT EXISTS (
2852 SELECT table1.col_varchar_10_unique
2853 FROM k AS table1s JOIN c AS table2s ON table1s.pk = table2s.pk
2854 );
2855 pk pk
2856 explain format=tree SELECT DISTINCT
2857 table1.pk AS field1
2858 FROM y AS table1
2859 JOIN d AS table2 ON table1.col_int_key = table2.col_int_unique
2860 JOIN y AS table3 ON table1.col_int_key = table3.col_int_unique
2861 WHERE table2.pk < table2.col_int_key AND NOT table2.col_int = 5
2862 HAVING field1 <= 2;
2863 EXPLAIN
2864 -> Table scan on <temporary>
2865 -> Temporary table with deduplication
2866 -> Filter: (field1 <= 2)
2867 -> Nested loop inner join
2868 -> Nested loop inner join
2869 -> Table scan on table3, activating pushed join of 3 tables, with pushed condition: (table3.col_int_unique is not null)
2870 -> Single-row index lookup on table2 using ix1 (col_int_unique=table3.col_int_unique), child of table3 in pushed join, with pushed condition: ((table2.pk < table2.col_int_key) and (table2.col_int <> 5))
2871 -> Index lookup on table1 using col_int_key (col_int_key=table3.col_int_unique), child of table3 in pushed join
2872
2873 SELECT DISTINCT
2874 table1.pk AS field1
2875 FROM y AS table1
2876 JOIN d AS table2 ON table1.col_int_key = table2.col_int_unique
2877 JOIN y AS table3 ON table1.col_int_key = table3.col_int_unique
2878 WHERE table2.pk < table2.col_int_key AND NOT table2.col_int = 5
2879 HAVING field1 <= 2;
2880 field1
2881 explain format=tree SELECT DISTINCT
2882 table2.col_int_key AS field1, table2.col_int AS field2, table2.col_int_key AS field3
2883 FROM t AS table1
2884 LEFT JOIN z AS table2 ON table1.pk = table2.col_int
2885 JOIN c AS table3 ON NOT (table1.col_int_key = table3.col_int_key AND table1.col_int_unique = table3.col_int_unique)
2886 WHERE table2.pk = table3.pk OR table3.col_int BETWEEN 3 AND 3+3
2887 HAVING field1 != 6;
2888 EXPLAIN
2889 -> Table scan on <temporary>
2890 -> Temporary table with deduplication
2891 -> Filter: (field1 <> 6)
2892 -> Filter: ((table2.pk = table3.pk) or (table3.col_int between 3 and <cache>((3 + 3))))
2893 -> Left hash join (table2.col_int = table1.pk)
2894 -> Filter: ((table1.col_int_key <> table3.col_int_key) or (table1.col_int_unique <> table3.col_int_unique))
2895 -> Inner hash join (no condition)
2896 -> Table scan on table3
2897 -> Hash
2898 -> Table scan on table1
2899 -> Hash
2900 -> Table scan on table2
2901
2902 Warnings:
2903 Note 1003 Can't push table 'table3' as child, 'type' must be a 'ref' access
2904 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
2905 SELECT DISTINCT
2906 table2.col_int_key AS field1, table2.col_int AS field2, table2.col_int_key AS field3
2907 FROM t AS table1
2908 LEFT JOIN z AS table2 ON table1.pk = table2.col_int
2909 JOIN c AS table3 ON NOT (table1.col_int_key = table3.col_int_key AND table1.col_int_unique = table3.col_int_unique)
2910 WHERE table2.pk = table3.pk OR table3.col_int BETWEEN 3 AND 3+3
2911 HAVING field1 != 6;
2912 field1 field2 field3
2913 1 1 1
2914 1 3 1
2915 3 1 3
2916 explain format=tree SELECT
2917 /*+ JOIN_ORDER(table2s@subq1, table1s@subq1, table2, table1, table1s@subq2, table2s@subq2)
2918 SEMIJOIN(@subq1 MATERIALIZATION)
2919 */
2920 table1.col_varchar_10_unique AS field1, table1.col_char_16 AS field2
2921 FROM e AS table1
2922 LEFT OUTER JOIN c AS table2 ON table1.col_int_key = table2.col_int_key AND
2923 table1.col_int_unique = table2.col_int_unique
2924 WHERE table1.col_varchar_256 IN (
2925 SELECT /*+ QB_NAME(subq1)*/ table2s.col_varchar_10 AS field3
2926 FROM (j AS table1s
2927 JOIN d AS table2s ON table1s.col_int_key = table2s.col_int_key)
2928 )
2929 AND table2.col_varchar_10_unique = ANY (
2930 SELECT /*+ QB_NAME(subq2)*/ DISTINCT table1s.col_varchar_10 AS field4
2931 FROM e AS table1s
2932 JOIN u AS table2s ON table1s.col_int = table2s.col_int AND
2933 table1s.col_int_unique = table2s.col_int_unique
2934 )
2935 AND table1.col_int_unique <> 2;
2936 EXPLAIN
2937 -> Nested loop semijoin
2938 -> Nested loop inner join
2939 -> Inner hash join (no condition)
2940 -> Index range scan on table2 using ix1 over (NULL < col_int_unique < 2) OR (2 < col_int_unique), activating pushed join of 2 tables, with pushed condition: ((table2.col_int_unique <> 2) and (table2.col_int_unique is not null))
2941 -> Hash
2942 -> Table scan on <subquery2>
2943 -> Materialize with deduplication
2944 -> Filter: (table2s.col_varchar_10 is not null)
2945 -> Nested loop inner join
2946 -> Index range scan on table2s using col_int_key over (NULL < col_int_key), activating pushed join of 2 tables, with pushed condition: (table2s.col_int_key is not null)
2947 -> Index lookup on table1s using col_int_key (col_int_key=table2s.col_int_key), child of table2s in pushed join
2948 -> Filter: (table1.col_varchar_256 = `<subquery2>`.field3)
2949 -> Single-row index lookup on table1 using ix1 (col_int_unique=table2.col_int_unique), child of table2 in pushed join, with pushed condition: (table1.col_int_key = table2.col_int_key)
2950 -> Nested loop inner join
2951 -> Table scan on table1s, activating pushed join of 2 tables, with pushed condition: (table1s.col_varchar_10 = table2.col_varchar_10_unique)
2952 -> Index lookup on table2s using PRIMARY (col_int=table1s.col_int), child of table1s in pushed join, with pushed condition: (table2s.col_int_unique = table1s.col_int_unique)
2953
2954 Warnings:
2955 Note 1003 Can't push table 'table2' as child, access type 'Range-scan' not implemented
2956 Note 1003 Can't push table 'table1' as child of 'table2s', it is in a materialized-branch which can't be referred.
2957 Note 1003 Can't push table 'table1s' as child, 'type' must be a 'ref' access
2958 Note 1003 Can't push table 'table2s' as child of 'table2s', it is in a materialized-branch which can't be referred.
2959 Note 1003 Can't push table 'table2s' as child of 'table2', column 'table1s.col_int' refers a table which was not pushed
2960 SELECT
2961 /*+ JOIN_ORDER(table2s@subq1, table1s@subq1, table2, table1, table1s@subq2, table2s@subq2)
2962 SEMIJOIN(@subq1 MATERIALIZATION)
2963 */
2964 table1.col_varchar_10_unique AS field1, table1.col_char_16 AS field2
2965 FROM e AS table1
2966 LEFT OUTER JOIN c AS table2 ON table1.col_int_key = table2.col_int_key AND
2967 table1.col_int_unique = table2.col_int_unique
2968 WHERE table1.col_varchar_256 IN (
2969 SELECT /*+ QB_NAME(subq1)*/ table2s.col_varchar_10 AS field3
2970 FROM (j AS table1s
2971 JOIN d AS table2s ON table1s.col_int_key = table2s.col_int_key)
2972 )
2973 AND table2.col_varchar_10_unique = ANY (
2974 SELECT /*+ QB_NAME(subq2)*/ DISTINCT table1s.col_varchar_10 AS field4
2975 FROM e AS table1s
2976 JOIN u AS table2s ON table1s.col_int = table2s.col_int AND
2977 table1s.col_int_unique = table2s.col_int_unique
2978 )
2979 AND table1.col_int_unique <> 2;
2980 field1 field2
2981 explain format=tree SELECT
2982 table2.col_int AS field1
2983 FROM j AS table1
2984 INNER JOIN v AS table2 ON table1.pk = table2.col_int_key
2985 ORDER BY field1 LIMIT 1;
2986 EXPLAIN
2987 -> Limit: 1 row(s)
2988 -> Sort: table2.col_int, limit input to 1 row(s) per chunk
2989 -> Stream results
2990 -> Nested loop inner join
2991 -> Table scan on table1, activating pushed join of 2 tables
2992 -> Index lookup on table2 using col_int_key (col_int_key=table1.pk), child of table1 in pushed join
2993
2994 SELECT
2995 table2.col_int AS field1
2996 FROM j AS table1
2997 INNER JOIN v AS table2 ON table1.pk = table2.col_int_key
2998 ORDER BY field1 LIMIT 1;
2999 field1
3000 NULL
3001 explain format=tree SELECT
3002 table6.col_int AS field1, table6.col_varchar_10_key AS field2
3003 FROM f AS table1
3004 JOIN q AS table2 ON table1.col_int_key = table2.col_int_key AND table1.col_int_unique = table2.col_int_unique
3005 JOIN u AS table3 ON table1.col_int_key = table3.pk AND table2.col_int_unique = table3.pk
3006 JOIN w AS table4 ON table3.col_int_unique = table4.col_int_key
3007 LEFT JOIN b AS table5 ON table3.col_int IN (5)
3008 JOIN d AS table6 ON table4.col_int_key = table6.col_int_key;
3009 EXPLAIN
3010 -> Left hash join (no condition), extra conditions: (table3.col_int = 5)
3011 -> Nested loop inner join
3012 -> Nested loop inner join
3013 -> Nested loop inner join
3014 -> Nested loop inner join
3015 -> Table scan on table3, activating pushed join of 5 tables, with pushed condition: (table3.col_int_unique is not null)
3016 -> Index lookup on table1 using ix1 (col_int_unique=table3.pk), child of table3 in pushed join, with pushed condition: (table1.col_int_key = table3.pk)
3017 -> Single-row index lookup on table2 using ix2 (col_int_key=table3.pk, col_int_unique=table3.pk), child of table3 in pushed join
3018 -> Index lookup on table6 using col_int_key (col_int_key=table3.col_int_unique), child of table3 in pushed join
3019 -> Index lookup on table4 using col_int_key (col_int_key=table3.col_int_unique), child of table3 in pushed join
3020 -> Hash
3021 -> Table scan on table5
3022
3023 Warnings:
3024 Note 1003 Can't push table 'table5' as child, 'type' must be a 'ref' access
3025 SELECT
3026 table6.col_int AS field1, table6.col_varchar_10_key AS field2
3027 FROM f AS table1
3028 JOIN q AS table2 ON table1.col_int_key = table2.col_int_key AND table1.col_int_unique = table2.col_int_unique
3029 JOIN u AS table3 ON table1.col_int_key = table3.pk AND table2.col_int_unique = table3.pk
3030 JOIN w AS table4 ON table3.col_int_unique = table4.col_int_key
3031 LEFT JOIN b AS table5 ON table3.col_int IN (5)
3032 JOIN d AS table6 ON table4.col_int_key = table6.col_int_key;
3033 field1 field2
3034 explain format=tree SELECT
3035 table2.col_int AS field1
3036 FROM q AS table1
3037 JOIN i AS table2 ON table1.pk = table2.col_int_key AND table1.col_int IN (5)
3038 WHERE 'vp' = ANY (
3039 SELECT DISTINCT table2.col_varchar_10 AS field2
3040 FROM b AS table1s
3041 LEFT JOIN w AS table2s ON table1s.col_int = table2s.col_int
3042 )
3043 HAVING field1 <= 3
3044 ORDER BY field1 LIMIT 1;
3045 EXPLAIN
3046 -> Limit: 1 row(s)
3047 -> Nested loop semijoin
3048 -> Nested loop inner join
3049 -> Index scan on table2 using PRIMARY, activating pushed join of 2 tables, with pushed condition: ((table2.col_varchar_10 = 'vp') and (table2.col_int_key is not null) and (field1 <= 3))
3050 -> Single-row index lookup on table1 using PRIMARY (pk=table2.col_int_key), child of table2 in pushed join, with pushed condition: (table1.col_int = 5)
3051 -> Nested loop left join
3052 -> Table scan on table1s
3053 -> Table scan on table2s, with pushed condition: (table1s.col_int = table2s.col_int)
3054
3055 Warnings:
3056 Note 1276 Field or reference 'test.table2.col_varchar_10' of SELECT #2 was resolved in SELECT #1
3057 Note 1003 Can't push table 'table1s' as child, 'type' must be a 'ref' access
3058 Note 1003 Can't push table 'table2s' as child, 'type' must be a 'ref' access
3059 SELECT
3060 table2.col_int AS field1
3061 FROM q AS table1
3062 JOIN i AS table2 ON table1.pk = table2.col_int_key AND table1.col_int IN (5)
3063 WHERE 'vp' = ANY (
3064 SELECT DISTINCT table2.col_varchar_10 AS field2
3065 FROM b AS table1s
3066 LEFT JOIN w AS table2s ON table1s.col_int = table2s.col_int
3067 )
3068 HAVING field1 <= 3
3069 ORDER BY field1 LIMIT 1;
3070 field1
3071 explain format=tree SELECT DISTINCT
3072 table5.col_varchar_256 AS field1, table2.col_varchar_10_key AS field2, table5.col_int_key AS field3
3073 FROM x AS table1
3074 JOIN p AS table2 ON table2.col_int IN (5,9)
3075 JOIN j AS table3 ON table2.pk = table3.col_int_unique
3076 LEFT JOIN n AS table4
3077 LEFT JOIN s AS table5 ON table4.col_int = table5.col_int
3078 AND table4.col_int_unique = table5.col_int_unique
3079 ON table1.col_int_unique = table4.col_int_unique
3080 HAVING field1 != 6 OR field3 < 6;
3081 EXPLAIN
3082 -> Table scan on <temporary>
3083 -> Temporary table with deduplication
3084 -> Filter: ((field1 <> 6) or (field3 < 6))
3085 -> Nested loop left join
3086 -> Inner hash join (no condition)
3087 -> Table scan on table1
3088 -> Hash
3089 -> Nested loop inner join
3090 -> Table scan on table3, activating pushed join of 2 tables, with pushed condition: (table3.col_int_unique is not null)
3091 -> Single-row index lookup on table2 using PRIMARY (pk=table3.col_int_unique), child of table3 in pushed join, with pushed condition: (table2.col_int in (5,9))
3092 -> Nested loop left join
3093 -> Table scan on table4, activating pushed join of 2 tables, with pushed condition: (table4.col_int_unique = table1.col_int_unique)
3094 -> Single-row index lookup on table5 using ix1 (col_int=table4.col_int, col_int_unique=table1.col_int_unique), child of table4 in pushed join
3095
3096 Warnings:
3097 Note 1003 Can't push table 'table1' as child, 'type' must be a 'ref' access
3098 Note 1003 Can't push table 'table4' as child, 'type' must be a 'ref' access
3099 Note 1003 Can't push table 'table5' as child of 'table3', it is in a hash-bucket-branch which can't be referred.
3100 Note 1003 Can't push table 'table5' as child of 'table1', column 'table4.col_int' refers a table which was not pushed
3101 SELECT DISTINCT
3102 table5.col_varchar_256 AS field1, table2.col_varchar_10_key AS field2, table5.col_int_key AS field3
3103 FROM x AS table1
3104 JOIN p AS table2 ON table2.col_int IN (5,9)
3105 JOIN j AS table3 ON table2.pk = table3.col_int_unique
3106 LEFT JOIN n AS table4
3107 LEFT JOIN s AS table5 ON table4.col_int = table5.col_int
3108 AND table4.col_int_unique = table5.col_int_unique
3109 ON table1.col_int_unique = table4.col_int_unique
3110 HAVING field1 != 6 OR field3 < 6;
3111 field1 field2 field3
3112 explain format=tree SELECT
3113 /*+ JOIN_ORDER(table6,table5,table3,table4,table7,table2,table1,table8) */
3114 table3.col_varchar_10 AS field1, 5 + (CHAR_LENGTH(table7.col_varchar_256_key)) AS field2
3115 FROM k AS table1
3116 JOIN x AS table2 ON table1.col_int_unique = table2.col_int_unique
3117 LEFT JOIN y AS table3
3118 LEFT JOIN m AS table4 ON table3.col_int_key = table4.col_int_key
3119 AND table3.col_int_unique = table4.col_int_unique
3120 LEFT JOIN h AS table5
3121 JOIN c AS table6 ON table5.pk = table6.col_int_key
3122 ON table3.col_int = table5.col_int AND table4.col_int_unique = table6.col_int_unique
3123 LEFT JOIN c AS table7 USE INDEX FOR JOIN(ix1)
3124 ON table3.col_int_key = table7.col_int_key
3125 AND table5.col_int_unique = table7.col_int_unique
3126 ON table2.col_int_key = table5.col_int_unique
3127 JOIN z AS table8 ON table3.pk = table8.col_int_key;
3128 EXPLAIN
3129 -> Nested loop inner join
3130 -> Inner hash join (table1.col_int_unique = table2.col_int_unique)
3131 -> Table scan on table1
3132 -> Hash
3133 -> Nested loop inner join
3134 -> Nested loop left join
3135 -> Nested loop inner join
3136 -> Nested loop inner join
3137 -> Nested loop inner join
3138 -> Table scan on table6, activating pushed join of 5 tables
3139 -> Single-row index lookup on table5 using PRIMARY (pk=table6.col_int_key), child of table6 in pushed join, with pushed condition: (table5.col_int_unique is not null)
3140 -> Index lookup on table3 using PRIMARY (col_int=table5.col_int), child of table5 in pushed join, with pushed condition: ((table3.col_int_unique = table6.col_int_unique) and (table3.pk is not null))
3141 -> Index lookup on table4 using col_int_key (col_int_key=table3.col_int_key), child of table3 in pushed join, with pushed condition: (table4.col_int_unique = table6.col_int_unique)
3142 -> Index lookup on table7 using ix1 (col_int_unique=table5.col_int_unique), with pushed condition: (table7.col_int_key = table3.col_int_key)
3143 -> Index lookup on table2 using col_int_key (col_int_key=table5.col_int_unique), child of table5 in pushed join
3144 -> Index lookup on table8 using col_int_key (col_int_key=table3.pk)
3145
3146 Warnings:
3147 Note 1003 Can't push outer joined table 'table7' as child of 'table6', table condition can not be fully evaluated by pushed join
3148 Note 1003 Can't push table 'table1' as child, 'type' must be a 'ref' access
3149 Note 1003 Can't push table 'table8' as child of 'table6', it is in a hash-bucket-branch which can't be referred.
3150 Note 1003 Can't push table 'table8' as child of 'table7', it is in a hash-bucket-branch which can't be referred.
3151 Note 1003 Can't push table 'table8' as child of 'table1', column 'table3.pk' is in a hash-bucket-branch which can't be referred
3152 SELECT
3153 /*+ JOIN_ORDER(table6,table5,table3,table4,table7,table2,table1,table8) */
3154 table3.col_varchar_10 AS field1, 5 + (CHAR_LENGTH(table7.col_varchar_256_key)) AS field2
3155 FROM k AS table1
3156 JOIN x AS table2 ON table1.col_int_unique = table2.col_int_unique
3157 LEFT JOIN y AS table3
3158 LEFT JOIN m AS table4 ON table3.col_int_key = table4.col_int_key
3159 AND table3.col_int_unique = table4.col_int_unique
3160 LEFT JOIN h AS table5
3161 JOIN c AS table6 ON table5.pk = table6.col_int_key
3162 ON table3.col_int = table5.col_int AND table4.col_int_unique = table6.col_int_unique
3163 LEFT JOIN c AS table7 USE INDEX FOR JOIN(ix1)
3164 ON table3.col_int_key = table7.col_int_key
3165 AND table5.col_int_unique = table7.col_int_unique
3166 ON table2.col_int_key = table5.col_int_unique
3167 JOIN z AS table8 ON table3.pk = table8.col_int_key;
3168 field1 field2
3169 explain format=tree SELECT STRAIGHT_JOIN
3170 table1.col_int AS field1, table1.col_int_key AS field2
3171 FROM a AS table1 WHERE EXISTS (
3172 SELECT table1s.col_varchar_256_unique AS field3, table2s.col_char_16_key AS field4
3173 FROM f AS table1s
3174 LEFT JOIN g AS table2s ON table1s.col_int_unique = table2s.col_int_key
3175 GROUP BY field3, field4)
3176 HAVING field1 = 9
3177 ORDER BY field2,field1;
3178 EXPLAIN
3179 -> Sort: table1.col_int_key, table1.col_int
3180 -> Table scan on table1, with pushed condition: (field1 = 9)
3181
3182 SELECT STRAIGHT_JOIN
3183 table1.col_int AS field1, table1.col_int_key AS field2
3184 FROM a AS table1 WHERE EXISTS (
3185 SELECT table1s.col_varchar_256_unique AS field3, table2s.col_char_16_key AS field4
3186 FROM f AS table1s
3187 LEFT JOIN g AS table2s ON table1s.col_int_unique = table2s.col_int_key
3188 GROUP BY field3, field4)
3189 HAVING field1 = 9
3190 ORDER BY field2,field1;
3191 field1 field2
3192 explain format=tree SELECT DISTINCT
3193 table2.col_char_16_unique AS field1
3194 FROM y AS table1
3195 JOIN m AS table2 ON table1.col_int_unique = table2.col_int_key
3196 LEFT JOIN h AS table3 ON table1.col_int = table3.col_int
3197 AND table1.col_int_unique = table3.col_int_unique
3198 WHERE 'sgxf' != ANY (
3199 SELECT 'ls' AS field2
3200 FROM r AS table1s
3201 JOIN c AS table2s ON table1s.col_int = table2s.col_int
3202 LEFT JOIN x AS table3s ON table2s.col_varchar_10_key = table3s.col_varchar_256_unique
3203 WHERE table3.col_int_unique <> 6)
3204 HAVING field1 >= 7 AND field1 = 4;
3205 EXPLAIN
3206 -> Table scan on <temporary>
3207 -> Temporary table with deduplication
3208 -> Filter: ((field1 >= 7) and (field1 = 4))
3209 -> Nested loop inner join
3210 -> Filter: <nop>(<in_optimizer>('sgxf',<exists>(select #2)))
3211 -> Nested loop left join
3212 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.col_int_unique is not null)
3213 -> Single-row index lookup on table3 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join, with pushed condition: (table3.col_int = table1.col_int)
3214 -> Select #2 (subquery in condition; dependent)
3215 -> Limit: 1 row(s)
3216 -> Nested loop inner join
3217 -> Nested loop left join
3218 -> Table scan on table2s, activating pushed join of 2 tables, with pushed condition: (table3.col_int_unique <> 6)
3219 -> Filter: (table2s.col_varchar_10_key = table3s.col_varchar_256_unique)
3220 -> Single-row index lookup on table3s using col_varchar_256_unique (col_varchar_256_unique=table2s.col_varchar_10_key)
3221 -> Index lookup on table1s using ix1 (col_int=table2s.col_int), child of table2s in pushed join
3222 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_unique), child of table1 in pushed join
3223
3224 Warnings:
3225 Note 1276 Field or reference 'test.table3.col_int_unique' of SELECT #2 was resolved in SELECT #1
3226 Note 1003 Can't push table 'table3s' as child, column 'col_varchar_256_unique' does not have same datatype as ref'ed column 'table2s.col_varchar_10_key'
3227 SELECT DISTINCT
3228 table2.col_char_16_unique AS field1
3229 FROM y AS table1
3230 JOIN m AS table2 ON table1.col_int_unique = table2.col_int_key
3231 LEFT JOIN h AS table3 ON table1.col_int = table3.col_int
3232 AND table1.col_int_unique = table3.col_int_unique
3233 WHERE 'sgxf' != ANY (
3234 SELECT 'ls' AS field2
3235 FROM r AS table1s
3236 JOIN c AS table2s ON table1s.col_int = table2s.col_int
3237 LEFT JOIN x AS table3s ON table2s.col_varchar_10_key = table3s.col_varchar_256_unique
3238 WHERE table3.col_int_unique <> 6)
3239 HAVING field1 >= 7 AND field1 = 4;
3240 field1
3241 Warning 1292 Truncated incorrect DOUBLE value: 'cjxyuoyxkuhx'
3242 Warning 1292 Truncated incorrect DOUBLE value: 'didn't'
3243 Warning 1292 Truncated incorrect DOUBLE value: 'hjfcw'
3244 Warning 1292 Truncated incorrect DOUBLE value: 'kgcla'
3245 Warning 1292 Truncated incorrect DOUBLE value: 'kivs'
3246 Warning 1292 Truncated incorrect DOUBLE value: 'oyxkuhxesmg'
3247 Warning 1292 Truncated incorrect DOUBLE value: 'qqmfokivsitbedsj'
3248 Warnings:
3249 explain format=tree SELECT
3250 'gscm' AS field1, table2.col_char_16 AS field2
3251 FROM c AS table1
3252 LEFT JOIN x AS table2 ON table1.col_int_unique = table2.col_int_key
3253 LEFT JOIN m AS table3 ON table2.col_int = table3.pk
3254 WHERE NOT table1.pk = 5
3255 AND 144 < ALL (
3256 SELECT 3 AS field3
3257 FROM g AS table1s
3258 JOIN n AS table2s
3259 LEFT JOIN c AS table3s ON table2s.col_int = table3s.col_int_key
3260 ON table1s.col_int_key = table2s.col_int
3261 WHERE NOT table3.col_int_key BETWEEN 2 AND 2+3)
3262 ORDER BY field1, field1;
3263 EXPLAIN
3264 -> Filter: <not>(<in_optimizer>(144,<exists>(select #2)))
3265 -> Nested loop left join
3266 -> Nested loop left join
3267 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.pk <> 5)
3268 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_unique), child of table1 in pushed join
3269 -> Single-row index lookup on table3 using PRIMARY (pk=table2.col_int), child of table2 in pushed join
3270 -> Select #2 (subquery in condition; dependent)
3271 -> Limit: 1 row(s)
3272 -> Nested loop inner join
3273 -> Nested loop left join
3274 -> Table scan on table2s, activating pushed join of 3 tables, with pushed condition: ((table3.col_int_key not between 2 and <cache>((2 + 3))) and (table2s.col_int is not null))
3275 -> Index lookup on table3s using col_int_key (col_int_key=table2s.col_int), child of table2s in pushed join
3276 -> Index lookup on table1s using col_int_key (col_int_key=table2s.col_int), child of table2s in pushed join
3277
3278 Warnings:
3279 Note 1276 Field or reference 'test.table3.col_int_key' of SELECT #2 was resolved in SELECT #1
3280 SELECT
3281 'gscm' AS field1, table2.col_char_16 AS field2
3282 FROM c AS table1
3283 LEFT JOIN x AS table2 ON table1.col_int_unique = table2.col_int_key
3284 LEFT JOIN m AS table3 ON table2.col_int = table3.pk
3285 WHERE NOT table1.pk = 5
3286 AND 144 < ALL (
3287 SELECT 3 AS field3
3288 FROM g AS table1s
3289 JOIN n AS table2s
3290 LEFT JOIN c AS table3s ON table2s.col_int = table3s.col_int_key
3291 ON table1s.col_int_key = table2s.col_int
3292 WHERE NOT table3.col_int_key BETWEEN 2 AND 2+3)
3293 ORDER BY field1, field1;
3294 field1 field2
3295 gscm NULL
3296 gscm NULL
3297 gscm NULL
3298 gscm NULL
3299 explain format=tree SELECT
3300 table2.col_char_16_unique AS field1, table3.col_int_key AS field2
3301 FROM t AS table1
3302 LEFT JOIN n AS table2
3303 JOIN l AS table3 ON table2.col_int_key = table3.col_int_unique
3304 ON table1.col_int_unique = table3.col_int_unique
3305 WHERE table2.col_int = table2.col_int ;
3306 EXPLAIN
3307 -> Inner hash join (table1.col_int_unique = table3.col_int_unique)
3308 -> Table scan on table1
3309 -> Hash
3310 -> Nested loop inner join
3311 -> Table scan on table3, activating pushed join of 2 tables
3312 -> Index lookup on table2 using col_int_key (col_int_key=table3.col_int_unique), child of table3 in pushed join, with pushed condition: (table2.col_int = table2.col_int)
3313
3314 Warnings:
3315 Note 1003 Can't push table 'table1' as child, 'type' must be a 'ref' access
3316 SELECT
3317 table2.col_char_16_unique AS field1, table3.col_int_key AS field2
3318 FROM t AS table1
3319 LEFT JOIN n AS table2
3320 JOIN l AS table3 ON table2.col_int_key = table3.col_int_unique
3321 ON table1.col_int_unique = table3.col_int_unique
3322 WHERE table2.col_int = table2.col_int ;
3323 field1 field2
3324 explain format=tree SELECT STRAIGHT_JOIN
3325 table2.col_int_unique AS field1, table1.col_varchar_10 AS field2, table2.col_varchar_256_unique AS field3
3326 FROM (s AS table2
3327 JOIN w AS table3 ON table2.col_int_key = table3.pk)
3328 JOIN z AS table1 ON table1.pk = table3.col_int_unique
3329 WHERE EXISTS (
3330 SELECT STRAIGHT_JOIN *
3331 FROM k AS table1s
3332 JOIN l AS table2s ON table1s.col_int = table2s.col_int
3333 AND table1s.col_int_unique = table2s.col_int_unique
3334 LEFT JOIN h AS table3s ON table1s.col_int_key = table3s.col_int_key
3335 AND table1s.col_int_unique = table3s.col_int_unique
3336 );
3337 EXPLAIN
3338 -> Zero rows (Impossible WHERE)
3339
3340 SELECT STRAIGHT_JOIN
3341 table2.col_int_unique AS field1, table1.col_varchar_10 AS field2, table2.col_varchar_256_unique AS field3
3342 FROM (s AS table2
3343 JOIN w AS table3 ON table2.col_int_key = table3.pk)
3344 JOIN z AS table1 ON table1.pk = table3.col_int_unique
3345 WHERE EXISTS (
3346 SELECT STRAIGHT_JOIN *
3347 FROM k AS table1s
3348 JOIN l AS table2s ON table1s.col_int = table2s.col_int
3349 AND table1s.col_int_unique = table2s.col_int_unique
3350 LEFT JOIN h AS table3s ON table1s.col_int_key = table3s.col_int_key
3351 AND table1s.col_int_unique = table3s.col_int_unique
3352 );
3353 field1 field2 field3
3354 explain format=tree SELECT STRAIGHT_JOIN
3355 table1.pk
3356 FROM s AS table1
3357 WHERE 'xyz' IN (
3358 SELECT table1.col_varchar_10 AS field4
3359 FROM i AS table1s
3360 JOIN c AS table2s ON table1s.col_int_key = table2s.col_int_unique
3361 WHERE EXISTS (
3362 SELECT *
3363 FROM s AS table1ss
3364 WHERE table1s.col_int_key = 5
3365 )
3366 );
3367 EXPLAIN
3368 -> Filter: <in_optimizer>('xyz',<exists>(select #2))
3369 -> Table scan on table1
3370 -> Select #2 (subquery in condition; dependent)
3371 -> Limit: 1 row(s)
3372 -> Nested loop inner join
3373 -> Hash semijoin (no condition)
3374 -> Index lookup on table1s using col_int_key (col_int_key=5), with pushed condition: ('xyz' = table1.col_varchar_10)
3375 -> Hash
3376 -> Limit: 1 row(s)
3377 -> Table scan on table1ss
3378 -> Index lookup on table2s using ix1 (col_int_unique=5)
3379
3380 Warnings:
3381 Note 1276 Field or reference 'test.table1.col_varchar_10' of SELECT #2 was resolved in SELECT #1
3382 Note 1276 Field or reference 'test.table1s.col_int_key' of SELECT #3 was resolved in SELECT #2
3383 Note 1003 Can't push table 'table1ss' as child, 'type' must be a 'ref' access
3384 Note 1003 Can't push table 'table2s' as child of 'table1s', it is in a hash-probe-branch which can't be referred.
3385 Note 1003 Can't push table 'table2s' as child of 'table1ss', it is in a hash-bucket-branch which can't be referred.
3386 SELECT STRAIGHT_JOIN
3387 table1.pk
3388 FROM s AS table1
3389 WHERE 'xyz' IN (
3390 SELECT table1.col_varchar_10 AS field4
3391 FROM i AS table1s
3392 JOIN c AS table2s ON table1s.col_int_key = table2s.col_int_unique
3393 WHERE EXISTS (
3394 SELECT *
3395 FROM s AS table1ss
3396 WHERE table1s.col_int_key = 5
3397 )
3398 );
3399 pk
3400 explain format=tree SELECT
3401 table2.col_int_key
3402 FROM h AS table1
3403 JOIN s AS table2 ON table1.col_int_unique = table2.col_int_key
3404 WHERE table1.pk IN (
3405 SELECT /*+ SEMIJOIN(LOOSESCAN)*/ table2s.col_int_key
3406 FROM p AS table1s
3407 LEFT JOIN l AS table2s ON table1s.pk = table2s.col_int
3408 WHERE table2s.col_int = 8
3409 );
3410 EXPLAIN
3411 -> Nested loop inner join
3412 -> Nested loop inner join
3413 -> Nested loop inner join
3414 -> Single-row index lookup on table1s using PRIMARY (pk=8)
3415 -> Remove duplicates from input sorted on ix3
3416 -> Index lookup on table2s using ix3 (col_int=8), activating pushed join of 2 tables, with pushed condition: (table2s.col_int_key is not null)
3417 -> Single-row index lookup on table1 using PRIMARY (pk=table2s.col_int_key), child of table2s in pushed join, with pushed condition: (table1.col_int_unique is not null)
3418 -> Index lookup on table2 using ix2 (col_int_key=table1.col_int_unique)
3419
3420 Warnings:
3421 Note 1003 Push of table 'table2s' as scan-child with lookup-root 'table1s' not implemented
3422 Note 1003 Can't push table 'table1' as child of 'table1s', column 'table2s.col_int_key' refers a table which was not pushed
3423 Note 1003 Push of table 'table2' as scan-child with lookup-root 'table1s' not implemented
3424 Note 1003 Can't push table 'table2' as scan-child of 'table2s', depends on tables being 'skip-read'
3425 SELECT
3426 table2.col_int_key
3427 FROM h AS table1
3428 JOIN s AS table2 ON table1.col_int_unique = table2.col_int_key
3429 WHERE table1.pk IN (
3430 SELECT /*+ SEMIJOIN(LOOSESCAN)*/ table2s.col_int_key
3431 FROM p AS table1s
3432 LEFT JOIN l AS table2s ON table1s.pk = table2s.col_int
3433 WHERE table2s.col_int = 8
3434 );
3435 col_int_key
3436 explain format=tree SELECT
3437 table2.col_int_key
3438 FROM h AS table1
3439 JOIN s AS table2 ON table1.col_int_unique = table2.col_int_key
3440 WHERE table1.col_int_unique IN (
3441 SELECT /*+ SEMIJOIN(LOOSESCAN)*/ table2s.col_int_key
3442 FROM p AS table1s
3443 LEFT JOIN l AS table2s ON table1s.pk = table2s.col_int
3444 WHERE table2s.col_int = 1
3445 );
3446 EXPLAIN
3447 -> Nested loop inner join
3448 -> Nested loop inner join
3449 -> Nested loop inner join
3450 -> Single-row index lookup on table1s using PRIMARY (pk=1)
3451 -> Remove duplicates from input sorted on ix3
3452 -> Index lookup on table2s using ix3 (col_int=1), activating pushed join of 2 tables, with pushed condition: (table2s.col_int_key is not null)
3453 -> Single-row index lookup on table1 using ix1 (col_int_unique=table2s.col_int_key), child of table2s in pushed join
3454 -> Index lookup on table2 using ix2 (col_int_key=table2s.col_int_key)
3455
3456 Warnings:
3457 Note 1003 Push of table 'table2s' as scan-child with lookup-root 'table1s' not implemented
3458 Note 1003 Can't push table 'table1' as child of 'table1s', column 'table2s.col_int_key' refers a table which was not pushed
3459 Note 1003 Push of table 'table2' as scan-child with lookup-root 'table1s' not implemented
3460 Note 1003 Can't push table 'table2' as scan-child of 'table2s', depends on tables being 'skip-read'
3461 SELECT
3462 table2.col_int_key
3463 FROM h AS table1
3464 JOIN s AS table2 ON table1.col_int_unique = table2.col_int_key
3465 WHERE table1.col_int_unique IN (
3466 SELECT /*+ SEMIJOIN(LOOSESCAN)*/ table2s.col_int_key
3467 FROM p AS table1s
3468 LEFT JOIN l AS table2s ON table1s.pk = table2s.col_int
3469 WHERE table2s.col_int = 1
3470 );
3471 col_int_key
3472 1
3473 1
3474 1
3475 1
3476 1
3477 1
3478 1
3479 explain format=tree SELECT DISTINCT
3480 table1.col_int AS field1, table2.col_char_16_key AS field2
3481 FROM a AS table1
3482 LEFT JOIN n AS table2 ON table1.col_int = table2.col_int
3483 WHERE EXISTS (SELECT * FROM i AS table1s)
3484 AND table1.col_int_key = 9;
3485 EXPLAIN
3486 -> Table scan on <temporary>
3487 -> Temporary table with deduplication
3488 -> Left hash join (table2.col_int = table1.col_int)
3489 -> Nested loop inner join
3490 -> Limit: 1 row(s)
3491 -> Table scan on table1s
3492 -> Index lookup on table1 using col_int_key (col_int_key=9)
3493 -> Hash
3494 -> Table scan on table2
3495
3496 Warnings:
3497 Note 1003 Can't push table 'table1' as child of 'table1s', no parent-child dependency exists between these tables
3498 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
3499 SELECT DISTINCT
3500 table1.col_int AS field1, table2.col_char_16_key AS field2
3501 FROM a AS table1
3502 LEFT JOIN n AS table2 ON table1.col_int = table2.col_int
3503 WHERE EXISTS (SELECT * FROM i AS table1s)
3504 AND table1.col_int_key = 9;
3505 field1 field2
3506 explain format=tree SELECT DISTINCT
3507 4 AS field1
3508 FROM f AS table1
3509 LEFT JOIN p AS table2 ON table1.col_int IS NULL;
3510 EXPLAIN
3511 -> Limit: 1 row(s)
3512 -> Table scan on <temporary>
3513 -> Temporary table
3514 -> Table scan on table1
3515
3516 SELECT DISTINCT
3517 4 AS field1
3518 FROM f AS table1
3519 LEFT JOIN p AS table2 ON table1.col_int IS NULL;
3520 field1
3521 4
3522 explain format=tree SELECT table3.col_int_key AS field1
3523 FROM k AS table1
3524 LEFT JOIN w AS table2
3525 LEFT JOIN v AS table3 ON table2.col_int_unique = table3.col_int_key
3526 ON table1.col_int_unique = table3.col_int_unique
3527 WHERE table2.col_int = ALL (
3528 SELECT DISTINCT table1s.pk
3529 FROM w AS table1s JOIN i AS table2s ON table1s.col_int_key = table2s.pk
3530 HAVING table1s.pk = 6
3531 );
3532 EXPLAIN
3533 -> Filter: <not>(<in_optimizer>(table2.col_int,<exists>(select #2)))
3534 -> Nested loop left join
3535 -> Table scan on table1
3536 -> Nested loop inner join
3537 -> Table scan on table2, activating pushed join of 2 tables
3538 -> Index lookup on table3 using col_int_key (col_int_key=table2.col_int_unique), child of table2 in pushed join, with pushed condition: (table3.col_int_unique = table1.col_int_unique)
3539 -> Select #2 (subquery in condition; dependent)
3540 -> Limit: 1 row(s)
3541 -> Filter: ((table1s.pk = 6) and <if>(outer_field_is_not_null, (<cache>(table2.col_int) <> <ref_null_helper>(table1s.pk)), true))
3542 -> Nested loop inner join
3543 -> Table scan on table2s, activating pushed join of 2 tables
3544 -> Index lookup on table1s using col_int_key (col_int_key=table2s.pk), child of table2s in pushed join
3545
3546 Warnings:
3547 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
3548 Note 1003 Can't push table 'table3' as child of 'table1', column 'table2.col_int_unique' refers a table which was not pushed
3549 SELECT table3.col_int_key AS field1
3550 FROM k AS table1
3551 LEFT JOIN w AS table2
3552 LEFT JOIN v AS table3 ON table2.col_int_unique = table3.col_int_key
3553 ON table1.col_int_unique = table3.col_int_unique
3554 WHERE table2.col_int = ALL (
3555 SELECT DISTINCT table1s.pk
3556 FROM w AS table1s JOIN i AS table2s ON table1s.col_int_key = table2s.pk
3557 HAVING table1s.pk = 6
3558 );
3559 field1
3560 SET optimizer_switch='batched_key_access=on';
3561 explain format=tree SELECT STRAIGHT_JOIN
3562 table1.pk, table2.pk, table3.pk
3563 FROM h AS table1
3564 LEFT JOIN v AS table2
3565 JOIN t AS table3 ON table2.col_int_key = table3.pk
3566 AND table2.col_int_unique = table3.col_int
3567 ON table1.col_int = table3.col_int
3568 AND table1.col_int_key = table2.col_int_key;
3569 EXPLAIN
3570 -> Nested loop left join
3571 -> Table scan on table1
3572 -> Batched key access inner join
3573 -> Batch input rows
3574 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), with pushed condition: (table2.col_int_unique = table1.col_int)
3575 -> Multi-range index lookup on table3 using PRIMARY (col_int=table1.col_int, pk=table1.col_int_key)
3576
3577 Warnings:
3578 Note 1003 Can't push table 'table3' as child, access type 'Multi-range' not implemented
3579 Note 1003 Can't push outer joined table 'table2' as child of 'table1', some tables in embedding join-nest(s) are not part of pushed join
3580 SELECT STRAIGHT_JOIN
3581 table1.pk, table2.pk, table3.pk
3582 FROM h AS table1
3583 LEFT JOIN v AS table2
3584 JOIN t AS table3 ON table2.col_int_key = table3.pk
3585 AND table2.col_int_unique = table3.col_int
3586 ON table1.col_int = table3.col_int
3587 AND table1.col_int_key = table2.col_int_key;
3588 pk pk pk
3589 1 NULL NULL
3590 10 NULL NULL
3591 11 NULL NULL
3592 12 NULL NULL
3593 13 NULL NULL
3594 14 NULL NULL
3595 15 NULL NULL
3596 16 NULL NULL
3597 17 NULL NULL
3598 18 NULL NULL
3599 19 NULL NULL
3600 2 NULL NULL
3601 20 NULL NULL
3602 21 NULL NULL
3603 3 NULL NULL
3604 4 NULL NULL
3605 5 NULL NULL
3606 6 NULL NULL
3607 7 NULL NULL
3608 8 NULL NULL
3609 9 NULL NULL
3610 SET optimizer_switch='batched_key_access=default';
3611 explain format=tree SELECT
3612 table1.pk
3613 FROM u AS table1 WHERE NOT EXISTS (
3614 SELECT /*+ SEMIJOIN(MATERIALIZATION)*/ *
3615 FROM p AS table1s WHERE table1.pk = 9
3616 );
3617 EXPLAIN
3618 -> Nested loop antijoin
3619 -> Table scan on table1
3620 -> Filter: (table1.pk = 9)
3621 -> Single-row index lookup on <subquery2> using <auto_distinct_key> (9=table1.pk)
3622 -> Materialize with deduplication
3623 -> Table scan on table1s
3624
3625 Warnings:
3626 Note 1276 Field or reference 'test.table1.pk' of SELECT #2 was resolved in SELECT #1
3627 Note 1003 Can't push table 'table1s' as child, 'type' must be a 'ref' access
3628 SELECT
3629 table1.pk
3630 FROM u AS table1 WHERE NOT EXISTS (
3631 SELECT /*+ SEMIJOIN(MATERIALIZATION)*/ *
3632 FROM p AS table1s WHERE table1.pk = 9
3633 );
3634 pk
3635 1
3636 10
3637 11
3638 12
3639 13
3640 14
3641 15
3642 16
3643 17
3644 18
3645 19
3646 2
3647 20
3648 21
3649 22
3650 23
3651 24
3652 25
3653 26
3654 27
3655 3
3656 4
3657 5
3658 6
3659 7
3660 8
3661 explain format=tree SELECT
3662 table1.pk, table2.pk
3663 FROM x AS table1
3664 LEFT JOIN b AS table2 ON table1.pk = table2.col_int_unique
3665 WHERE EXISTS (
3666 SELECT /*+ SEMIJOIN(MATERIALIZATION)*/ *
3667 FROM r AS table1s
3668 JOIN e AS table2s ON table1s.pk = table2s.col_int_key
3669 WHERE table2s.col_int_key = table2.col_int
3670 );
3671 EXPLAIN
3672 -> Nested loop inner join
3673 -> Nested loop inner join
3674 -> Table scan on table1, activating pushed join of 2 tables
3675 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.pk), child of table1 in pushed join, with pushed condition: (table2.col_int is not null)
3676 -> Single-row index lookup on <subquery2> using <auto_distinct_key> (col_int_key=table2.col_int)
3677 -> Materialize with deduplication
3678 -> Filter: (table2s.col_int_key is not null)
3679 -> Nested loop inner join
3680 -> Table scan on table1s, activating pushed join of 2 tables
3681 -> Index lookup on table2s using col_int_key (col_int_key=table1s.pk), child of table1s in pushed join
3682
3683 Warnings:
3684 Note 1276 Field or reference 'test.table2.col_int' of SELECT #2 was resolved in SELECT #1
3685 Note 1003 Can't push table 'table1s' as child, 'type' must be a 'ref' access
3686 Note 1003 Can't push table 'table2s' as child of 'table1', it is in a query-branch which can't be referred.
3687 SELECT
3688 table1.pk, table2.pk
3689 FROM x AS table1
3690 LEFT JOIN b AS table2 ON table1.pk = table2.col_int_unique
3691 WHERE EXISTS (
3692 SELECT /*+ SEMIJOIN(MATERIALIZATION)*/ *
3693 FROM r AS table1s
3694 JOIN e AS table2s ON table1s.pk = table2s.col_int_key
3695 WHERE table2s.col_int_key = table2.col_int
3696 );
3697 pk pk
3698 13 1
3699 explain format=tree SELECT DISTINCT
3700 table1.pk
3701 FROM y AS table1
3702 WHERE table1.col_int_unique IN (
3703 SELECT /*+ SEMIJOIN(MATERIALIZATION)*/ table2s.pk AS field3
3704 FROM o AS table1s
3705 JOIN i AS table2s ON table1s.col_int_unique = table2s.col_int
3706 WHERE table1s.col_int_unique BETWEEN 2 AND 10)
3707 ;
3708 EXPLAIN
3709 -> Table scan on <temporary>
3710 -> Temporary table with deduplication
3711 -> Nested loop inner join
3712 -> Table scan on table1, with pushed condition: (table1.col_int_unique is not null)
3713 -> Single-row index lookup on <subquery2> using <auto_distinct_key> (field3=table1.col_int_unique)
3714 -> Materialize with deduplication
3715 -> Inner hash join (table1s.col_int_unique = table2s.col_int)
3716 -> Table scan on table1s
3717 -> Hash
3718 -> Index range scan on table2s using PRIMARY over (2 <= col_int <= 10), with pushed condition: (table2s.col_int between 2 and 10)
3719
3720 Warnings:
3721 Note 1003 Can't push table 'table2s' as child, access type 'Range-scan' not implemented
3722 Note 1003 Can't push table 'table1s' as child, 'type' must be a 'ref' access
3723 SELECT DISTINCT
3724 table1.pk
3725 FROM y AS table1
3726 WHERE table1.col_int_unique IN (
3727 SELECT /*+ SEMIJOIN(MATERIALIZATION)*/ table2s.pk AS field3
3728 FROM o AS table1s
3729 JOIN i AS table2s ON table1s.col_int_unique = table2s.col_int
3730 WHERE table1s.col_int_unique BETWEEN 2 AND 10)
3731 ;
3732 pk
3733 28
3734 33
3735 7
3736 explain format=tree SELECT
3737 table1.pk, table2.pk, table3.pk, table4.pk
3738 #table3.col_int AS field1, table3.col_int_key AS field2
3739 FROM r AS table1
3740 JOIN s AS table2 ON table1.pk = table2.col_int_unique
3741 LEFT JOIN k AS table3
3742 LEFT JOIN e AS table4 ON table3.col_int_key = table4.col_int_unique
3743 ON table1.col_int_key = table4.col_int_unique AND
3744 table1.col_int_unique = table4.col_int_unique
3745 WHERE table2.col_int_key <> 5;
3746 EXPLAIN
3747 -> Nested loop left join
3748 -> Inner hash join (table1.pk = table2.col_int_unique)
3749 -> Table scan on table1, activating pushed join of 2 tables
3750 -> Hash
3751 -> Index range scan on table2 using ix2 over (NULL < col_int_key < 5) OR (5 < col_int_key), with pushed condition: (table2.col_int_key <> 5)
3752 -> Nested loop inner join
3753 -> Filter: (table1.col_int_unique = table1.col_int_key)
3754 -> Single-row index lookup on table4 using ix1 (col_int_unique=table1.col_int_key), child of table1 in pushed join
3755 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int_key)
3756
3757 Warnings:
3758 Note 1003 Can't push table 'table1' as child, 'type' must be a 'ref' access
3759 Note 1003 Can't push table 'table4' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
3760 Note 1003 Can't push table 'table3' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
3761 Note 1003 Can't push outer joined table 'table3' as child of 'table1', condition on its dependant table 'table4' is not pushed down
3762 SELECT
3763 table1.pk, table2.pk, table3.pk, table4.pk
3764 #table3.col_int AS field1, table3.col_int_key AS field2
3765 FROM r AS table1
3766 JOIN s AS table2 ON table1.pk = table2.col_int_unique
3767 LEFT JOIN k AS table3
3768 LEFT JOIN e AS table4 ON table3.col_int_key = table4.col_int_unique
3769 ON table1.col_int_key = table4.col_int_unique AND
3770 table1.col_int_unique = table4.col_int_unique
3771 WHERE table2.col_int_key <> 5;
3772 pk pk pk pk
3773 10 2 NULL NULL
3774 11 20 NULL NULL
3775 15 8 NULL NULL
3776 16 23 NULL NULL
3777 19 6 NULL NULL
3778 2 12 NULL NULL
3779 21 16 NULL NULL
3780 22 22 NULL NULL
3781 4 3 NULL NULL
3782 5 19 NULL NULL
3783 6 1 NULL NULL
3784 7 17 NULL NULL
3785 9 13 NULL NULL
3786 explain format=tree SELECT DISTINCT
3787 table3.col_int_key AS field1, table1.col_int AS field2, table1.col_int AS field3
3788 FROM t AS table1
3789 JOIN u AS table2 ON table1.col_int_key = table2.col_int_key
3790 LEFT JOIN i AS table3 ON table1.col_int = table3.col_int_key;
3791 EXPLAIN
3792 -> Table scan on <temporary>
3793 -> Temporary table with deduplication
3794 -> Nested loop inner join
3795 -> Nested loop left join
3796 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.col_int_key is not null)
3797 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int), child of table1 in pushed join
3798 -> Limit: 1 row(s)
3799 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
3800
3801 SELECT DISTINCT
3802 table3.col_int_key AS field1, table1.col_int AS field2, table1.col_int AS field3
3803 FROM t AS table1
3804 JOIN u AS table2 ON table1.col_int_key = table2.col_int_key
3805 LEFT JOIN i AS table3 ON table1.col_int = table3.col_int_key;
3806 field1 field2 field3
3807 0 0 0
3808 1 1 1
3809 2 2 2
3810 3 3 3
3811 explain format=tree SELECT
3812 table1.pk, table2.pk, table1.col_varchar_10_unique AS field3
3813 FROM l AS table1
3814 JOIN j AS table2 ON table1.pk = table2.col_int_key
3815 WHERE table1.pk IN (
3816 SELECT table1s.col_int
3817 FROM i AS table1s
3818 JOIN z AS table2s ON table1s.col_int = table2s.col_int_key
3819 ) ORDER BY field3;
3820 EXPLAIN
3821 -> Nested loop inner join
3822 -> Nested loop semijoin
3823 -> Sort: table1.col_varchar_10_unique
3824 -> Table scan on table1
3825 -> Nested loop inner join
3826 -> Index lookup on table1s using ix3 (col_int=table1.pk), activating pushed join of 2 tables
3827 -> Index lookup on table2s using col_int_key (col_int_key=table1.pk), child of table1s in pushed join
3828 -> Index lookup on table2 using col_int_key (col_int_key=table1.pk)
3829
3830 Warnings:
3831 Note 1003 Can't push table 'table1s' as child of 'table1', it is in a sorted-branch which can't be referred.
3832 Note 1003 Can't push table 'table2s' as child of 'table1', it is in a sorted-branch which can't be referred.
3833 Note 1003 Can't push table 'table2' as child of 'table1', it is in a sorted-branch which can't be referred.
3834 Note 1003 Can't push table 'table2' as child of 'table1s', column 'table1.pk' is in a sorted-branch which can't be referred
3835 SELECT
3836 table1.pk, table2.pk, table1.col_varchar_10_unique AS field3
3837 FROM l AS table1
3838 JOIN j AS table2 ON table1.pk = table2.col_int_key
3839 WHERE table1.pk IN (
3840 SELECT table1s.col_int
3841 FROM i AS table1s
3842 JOIN z AS table2s ON table1s.col_int = table2s.col_int_key
3843 ) ORDER BY field3;
3844 pk pk field3
3845 1 13 they
3846 1 15 they
3847 1 20 they
3848 1 6 they
3849 1 8 they
3850 1 9 they
3851 2 10 anvsodxuro
3852 2 12 anvsodxuro
3853 2 2 anvsodxuro
3854 2 3 anvsodxuro
3855 2 5 anvsodxuro
3856 3 1 hey
3857 3 18 hey
3858 3 19 hey
3859 3 4 hey
3860 3 7 hey
3861 explain format=tree SELECT
3862 DISTINCT table4.col_int_unique
3863 FROM u AS table1
3864 JOIN v AS table2 ON table1.col_int_key = table2.col_int_unique
3865 LEFT JOIN w AS table3 ON table2.col_varchar_256_unique = table3.col_varchar_256_unique
3866 JOIN c AS table4 ON table2.col_int_unique = table4.col_int;
3867 EXPLAIN
3868 -> Table scan on <temporary>
3869 -> Temporary table with deduplication
3870 -> Nested loop inner join
3871 -> Nested loop inner join
3872 -> Nested loop left join
3873 -> Table scan on table2, activating pushed join of 4 tables, with pushed condition: (table2.col_int_unique is not null)
3874 -> Single-row index lookup on table3 using col_varchar_256_unique (col_varchar_256_unique=table2.col_varchar_256_unique), child of table2 in pushed join
3875 -> Index lookup on table4 using PRIMARY (col_int=table2.col_int_unique), child of table2 in pushed join
3876 -> Limit: 1 row(s)
3877 -> Index lookup on table1 using col_int_key (col_int_key=table2.col_int_unique), child of table2 in pushed join
3878
3879 SELECT
3880 DISTINCT table4.col_int_unique
3881 FROM u AS table1
3882 JOIN v AS table2 ON table1.col_int_key = table2.col_int_unique
3883 LEFT JOIN w AS table3 ON table2.col_varchar_256_unique = table3.col_varchar_256_unique
3884 JOIN c AS table4 ON table2.col_int_unique = table4.col_int;
3885 col_int_unique
3886 23
3887 NULL
3888 explain format=tree SELECT DISTINCT
3889 table3.col_char_16_unique AS field1, table2.col_varchar_256_unique AS field2
3890 FROM w AS table1
3891 LEFT JOIN c AS table2
3892 JOIN d AS table3 ON table2.pk = table3.pk
3893 ON table1.col_int_key = table2.col_int AND table1.col_int_unique = table2.col_int
3894 JOIN r AS table4 ON table3.col_int IS NULL
3895 WHERE table4.col_varchar_10_key IN (
3896 SELECT
3897 table2.col_varchar_256 AS field3
3898 FROM t AS table1s
3899 LEFT JOIN i AS table2s
3900 LEFT JOIN b AS table3s ON table2s.col_int = table3s.col_int AND table2s.col_int_unique = table3s.col_int_unique
3901 ON table1s.col_varchar_10_key = table2s.col_varchar_256_unique
3902 WHERE table3s.pk = table3.col_int_key);
3903 EXPLAIN
3904 -> Table scan on <temporary>
3905 -> Temporary table with deduplication
3906 -> Nested loop semijoin
3907 -> Nested loop inner join
3908 -> Nested loop inner join
3909 -> Nested loop inner join
3910 -> Nested loop inner join
3911 -> Table scan on table2, activating pushed join of 4 tables, with pushed condition: ((table2.col_int is not null) and (table2.col_varchar_256 is not null))
3912 -> Single-row index lookup on table3 using PRIMARY (pk=table2.pk), child of table2 in pushed join, with pushed condition: (table3.col_int is null)
3913 -> Index lookup on table1 using col_int_key (col_int_key=table2.col_int), child of table2 in pushed join, with pushed condition: (table1.col_int_unique = table2.col_int)
3914 -> Single-row index lookup on table3s using PRIMARY (pk=table3.col_int_key), child of table3 in pushed join
3915 -> Filter: (table4.col_varchar_10_key = table2.col_varchar_256)
3916 -> Index lookup on table4 using col_varchar_10_key (col_varchar_10_key=table2.col_varchar_256)
3917 -> Nested loop inner join
3918 -> Index lookup on table2s using ix3 (col_int=table3s.col_int), with pushed condition: ((table2s.col_int_unique = table3s.col_int_unique) and (table2s.col_varchar_256_unique is not null))
3919 -> Limit: 1 row(s)
3920 -> Filter: (table1s.col_varchar_10_key = table2s.col_varchar_256_unique)
3921 -> Index lookup on table1s using col_varchar_10_key (col_varchar_10_key=table2s.col_varchar_256_unique)
3922
3923 Warnings:
3924 Note 1276 Field or reference 'test.table2.col_varchar_256' of SELECT #2 was resolved in SELECT #1
3925 Note 1276 Field or reference 'test.table3.col_int_key' of SELECT #2 was resolved in SELECT #1
3926 Note 1003 Can't push table 'table4' as child, column 'col_varchar_10_key' does not have same datatype as ref'ed column 'table2.col_varchar_256'
3927 Note 1003 Can't push table 'table1s' as child, column 'col_varchar_10_key' does not have same datatype as ref'ed column 'table2s.col_varchar_256_unique'
3928 Note 1003 Can't push semi joined table 'table2s' as child of 'table2', some tables in embedding join-nest(s) are not part of pushed join
3929 Note 1003 Can't push table 'table2s' as child of 'table4', no parent-child dependency exists between these tables
3930 SELECT DISTINCT
3931 table3.col_char_16_unique AS field1, table2.col_varchar_256_unique AS field2
3932 FROM w AS table1
3933 LEFT JOIN c AS table2
3934 JOIN d AS table3 ON table2.pk = table3.pk
3935 ON table1.col_int_key = table2.col_int AND table1.col_int_unique = table2.col_int
3936 JOIN r AS table4 ON table3.col_int IS NULL
3937 WHERE table4.col_varchar_10_key IN (
3938 SELECT
3939 table2.col_varchar_256 AS field3
3940 FROM t AS table1s
3941 LEFT JOIN i AS table2s
3942 LEFT JOIN b AS table3s ON table2s.col_int = table3s.col_int AND table2s.col_int_unique = table3s.col_int_unique
3943 ON table1s.col_varchar_10_key = table2s.col_varchar_256_unique
3944 WHERE table3s.pk = table3.col_int_key);
3945 field1 field2
3946 explain format=tree SELECT table1.col_varchar_256_unique AS field1
3947 FROM o AS table1
3948 JOIN x AS table2 ON table1.col_int_key = table2.pk
3949 JOIN n AS table3 ON table1.pk = table3.pk
3950 GROUP BY field1;
3951 EXPLAIN
3952 -> Table scan on <temporary>
3953 -> Temporary table with deduplication
3954 -> Nested loop inner join
3955 -> Inner hash join (no condition)
3956 -> Table scan on table2, activating pushed join of 2 tables
3957 -> Hash
3958 -> Table scan on table3
3959 -> Filter: (table1.pk = table3.pk)
3960 -> Index lookup on table1 using col_int_key (col_int_key=table2.pk), child of table2 in pushed join
3961
3962 Warnings:
3963 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
3964 Note 1003 Can't push table 'table1' as child of 'table3', it is in a hash-bucket-branch which can't be referred.
3965 SELECT table1.col_varchar_256_unique AS field1
3966 FROM o AS table1
3967 JOIN x AS table2 ON table1.col_int_key = table2.pk
3968 JOIN n AS table3 ON table1.pk = table3.pk
3969 GROUP BY field1;
3970 field1
3971 I'm
3972 about
3973 because
3974 did
3975 evslluevgxtytgzicaravwfckhsczypx
3976 fakeimranclptwxgaispoifyx
3977 from
3978 gzicaravwfckhsczyp
3979 hcwvfusmtftlevhia
3980 herobmchxfakeimranclptwxgaisp
3981 no
3982 oypznfiherobmchxfakeimrancl
3983 something
3984 up
3985 was
3986 yes
3987 explain format=tree SELECT table1.pk, table2.pk
3988 FROM x AS table1
3989 JOIN b AS table2 ON table1.col_int_key = table2.pk
3990 WHERE #table2.col_int_unique != 7 AND
3991 table1.col_int_key IN (
3992 SELECT
3993 table1s.col_int AS field2
3994 FROM f AS table1s
3995 JOIN g AS table2s ON table1s.col_int_unique = table2s.col_int_unique
3996 );
3997 EXPLAIN
3998 -> Nested loop inner join
3999 -> Nested loop inner join
4000 -> Table scan on table2, activating pushed join of 4 tables
4001 -> Nested loop semijoin with duplicate removal on PRIMARY
4002 -> Index lookup on table1s using PRIMARY (col_int=table2.pk), child of table2 in pushed join, with pushed condition: (table1s.col_int_unique is not null)
4003 -> Single-row index lookup on table2s using ix1 (col_int_unique=table1s.col_int_unique), child of table1s in pushed join
4004 -> Index lookup on table1 using col_int_key (col_int_key=table2.pk), child of table2 in pushed join
4005
4006 SELECT table1.pk, table2.pk
4007 FROM x AS table1
4008 JOIN b AS table2 ON table1.col_int_key = table2.pk
4009 WHERE #table2.col_int_unique != 7 AND
4010 table1.col_int_key IN (
4011 SELECT
4012 table1s.col_int AS field2
4013 FROM f AS table1s
4014 JOIN g AS table2s ON table1s.col_int_unique = table2s.col_int_unique
4015 );
4016 pk pk
4017 10 1
4018 17 1
4019 21 1
4020 22 1
4021 3 1
4022 explain format=tree SELECT table1.pk, table2.pk
4023 FROM x AS table1
4024 JOIN b AS table2 ON table1.col_int_key = table2.pk
4025 WHERE #table2.col_int_unique != 7 AND
4026 table1.col_int_key IN (
4027 SELECT
4028 table1s.col_int AS field2
4029 FROM f AS table1s
4030 JOIN g AS table2s ON table1s.col_int_unique+0 = table2s.col_int_unique
4031 );
4032 EXPLAIN
4033 -> Nested loop inner join
4034 -> Nested loop inner join
4035 -> Table scan on table2, activating pushed join of 2 tables
4036 -> Nested loop semijoin with duplicate removal on PRIMARY
4037 -> Index lookup on table1s using PRIMARY (col_int=table2.pk)
4038 -> Filter: ((table1s.col_int_unique + 0) = table2s.col_int_unique)
4039 -> Single-row index lookup on table2s using ix1 (col_int_unique=(table1s.col_int_unique + 0))
4040 -> Index lookup on table1 using col_int_key (col_int_key=table2.pk), child of table2 in pushed join
4041
4042 Warnings:
4043 Note 1003 Can't push table 'table2s' as child, column 'col_int_unique' does neither 'ref' a column nor a constant
4044 Note 1003 Can't push semi joined table 'table1s' as child of 'table2', some tables in embedding join-nest(s) are not part of pushed join
4045 SELECT table1.pk, table2.pk
4046 FROM x AS table1
4047 JOIN b AS table2 ON table1.col_int_key = table2.pk
4048 WHERE #table2.col_int_unique != 7 AND
4049 table1.col_int_key IN (
4050 SELECT
4051 table1s.col_int AS field2
4052 FROM f AS table1s
4053 JOIN g AS table2s ON table1s.col_int_unique+0 = table2s.col_int_unique
4054 );
4055 pk pk
4056 10 1
4057 17 1
4058 21 1
4059 22 1
4060 3 1
4061 explain format=tree SELECT
4062 /*+ JOIN_ORDER(table2,table2s@subq,table1) SEMIJOIN(@subq DUPSWEEDOUT) */
4063 table1.pk, table2.pk
4064 FROM z AS table1
4065 JOIN b AS table2 ON table1.col_int_key = table2.pk
4066 WHERE table1.col_int_key IN (
4067 SELECT /*+ QB_NAME(subq) */
4068 table2s.col_int_key
4069 FROM q AS table2s
4070 );
4071 EXPLAIN
4072 -> Nested loop inner join
4073 -> Table scan on table2
4074 -> Remove duplicate table1 rows using temporary table (weedout)
4075 -> Nested loop inner join
4076 -> Index lookup on table2s using ix2 (col_int_key=table2.pk), activating pushed join of 2 tables
4077 -> Index lookup on table1 using col_int_key (col_int_key=table2.pk), child of table2s in pushed join
4078
4079 Warnings:
4080 Note 1003 Can't push table 'table2s' as child of 'table2', it is in a query-branch which can't be referred.
4081 Note 1003 Can't push table 'table1' as child of 'table2', it is in a query-branch which can't be referred.
4082 SELECT
4083 /*+ JOIN_ORDER(table2,table2s@subq,table1) SEMIJOIN(@subq DUPSWEEDOUT) */
4084 table1.pk, table2.pk
4085 FROM z AS table1
4086 JOIN b AS table2 ON table1.col_int_key = table2.pk
4087 WHERE table1.col_int_key IN (
4088 SELECT /*+ QB_NAME(subq) */
4089 table2s.col_int_key
4090 FROM q AS table2s
4091 );
4092 pk pk
4093 1 1
4094 14 1
4095 17 1
4096 19 1
4097 21 1
4098 26 1
4099 3 1
4100 4 1
4101 5 1
4102 54 1
4103 55 1
4104 59 1
4105 62 1
4106 64 1
4107 67 1
4108 set optimizer_switch='batched_key_access=on';
4109 explain format=tree SELECT straight_join
4110 table1.pk, table2.pk, table3.pk
4111 FROM i AS table2
4112 JOIN i AS table3 ON table2.col_int = table3.col_int AND
4113 table2.col_int_key = table3.col_int_key AND
4114 table2.col_int_unique = table3.col_int_unique
4115 JOIN t as table1
4116 ON table1.col_int = table2.col_int AND
4117 table1.col_int_unique = table3.col_int_unique;
4118 EXPLAIN
4119 -> Nested loop inner join
4120 -> Batched key access inner join
4121 -> Batch input rows
4122 -> Table scan on table2, with pushed condition: ((table2.col_int_key is not null) and (table2.col_int_unique is not null))
4123 -> Multi-range index lookup on table3 using ix3 (col_int=table2.col_int, col_int_key=table2.col_int_key, col_int_unique=table2.col_int_unique), activating pushed join of 2 tables
4124 -> Single-row index lookup on table1 using ix1 (col_int=table2.col_int, col_int_unique=table2.col_int_unique), child of table3 in pushed join
4125
4126 Warnings:
4127 Note 1003 Can't push table 'table3' as child, access type 'Multi-range' not implemented
4128 Note 1003 Can't push table 'table1' as child of 'table2', it is in a batched-keys-branch which can't be referred.
4129 SELECT straight_join
4130 table1.pk, table2.pk, table3.pk
4131 FROM i AS table2
4132 JOIN i AS table3 ON table2.col_int = table3.col_int AND
4133 table2.col_int_key = table3.col_int_key AND
4134 table2.col_int_unique = table3.col_int_unique
4135 JOIN t as table1
4136 ON table1.col_int = table2.col_int AND
4137 table1.col_int_unique = table3.col_int_unique;
4138 pk pk pk
4139 1 14 14
4140 20 3 3
4141 set optimizer_switch='batched_key_access=default';
4142 ##################
4143 #
4144 # Bug#33670002: Pushed outer join fails:
4145 # Error 4829 'FirstInner/Upper has to be an ancestor or a sibling'
4146 #
4147 ##################
4148 explain format=tree SELECT
4149 table2.pk, table1.pk, table3.pk, table5.pk, table4.pk
4150 FROM (v AS table2
4151 STRAIGHT_JOIN u AS table1 ON table1.col_int_key = table2.col_int)
4152 LEFT JOIN m AS table3
4153 LEFT JOIN (u AS table5
4154 STRAIGHT_JOIN n AS table4 ON table4.col_int_key = table5.col_int_key)
4155 ON table3.col_int_unique = table5.col_int_key
4156 ON table1.col_int = table3.col_int AND
4157 table2.col_int_unique = table3.col_int_unique;
4158 EXPLAIN
4159 -> Nested loop left join
4160 -> Nested loop inner join
4161 -> Table scan on table2, activating pushed join of 5 tables, with pushed condition: (table2.col_int is not null)
4162 -> Index lookup on table1 using col_int_key (col_int_key=table2.col_int), child of table2 in pushed join
4163 -> Nested loop left join
4164 -> Index lookup on table3 using ix3 (col_int=table1.col_int), child of table1 in pushed join, with pushed condition: (table3.col_int_unique = table2.col_int_unique)
4165 -> Nested loop inner join
4166 -> Index lookup on table5 using col_int_key (col_int_key=table2.col_int_unique), child of table1 in pushed join
4167 -> Index lookup on table4 using col_int_key (col_int_key=table2.col_int_unique), child of table1 in pushed join
4168
4169 SELECT
4170 table2.pk, table1.pk, table3.pk, table5.pk, table4.pk
4171 FROM (v AS table2
4172 STRAIGHT_JOIN u AS table1 ON table1.col_int_key = table2.col_int)
4173 LEFT JOIN m AS table3
4174 LEFT JOIN (u AS table5
4175 STRAIGHT_JOIN n AS table4 ON table4.col_int_key = table5.col_int_key)
4176 ON table3.col_int_unique = table5.col_int_key
4177 ON table1.col_int = table3.col_int AND
4178 table2.col_int_unique = table3.col_int_unique;
4179 pk pk pk pk pk
4180 1 10 NULL NULL NULL
4181 1 20 NULL NULL NULL
4182 1 25 NULL NULL NULL
4183 1 26 NULL NULL NULL
4184 1 4 NULL NULL NULL
4185 1 6 NULL NULL NULL
4186 1 7 15 13 1
4187 1 7 15 13 11
4188 1 7 15 13 12
4189 1 7 15 13 15
4190 1 7 15 13 17
4191 1 7 15 13 18
4192 1 7 15 16 1
4193 1 7 15 16 11
4194 1 7 15 16 12
4195 1 7 15 16 15
4196 1 7 15 16 17
4197 1 7 15 16 18
4198 1 7 15 17 1
4199 1 7 15 17 11
4200 1 7 15 17 12
4201 1 7 15 17 15
4202 1 7 15 17 17
4203 1 7 15 17 18
4204 1 7 15 22 1
4205 1 7 15 22 11
4206 1 7 15 22 12
4207 1 7 15 22 15
4208 1 7 15 22 17
4209 1 7 15 22 18
4210 1 8 NULL NULL NULL
4211 10 13 NULL NULL NULL
4212 10 16 NULL NULL NULL
4213 10 17 NULL NULL NULL
4214 10 22 NULL NULL NULL
4215 12 1 NULL NULL NULL
4216 12 12 NULL NULL NULL
4217 12 18 NULL NULL NULL
4218 12 2 NULL NULL NULL
4219 12 24 NULL NULL NULL
4220 12 27 NULL NULL NULL
4221 13 1 NULL NULL NULL
4222 13 12 16 NULL NULL
4223 13 18 NULL NULL NULL
4224 13 2 16 NULL NULL
4225 13 24 16 NULL NULL
4226 13 27 NULL NULL NULL
4227 14 11 NULL NULL NULL
4228 14 14 NULL NULL NULL
4229 14 15 NULL NULL NULL
4230 14 19 NULL NULL NULL
4231 14 21 NULL NULL NULL
4232 14 23 NULL NULL NULL
4233 14 3 NULL NULL NULL
4234 14 5 NULL NULL NULL
4235 15 1 NULL NULL NULL
4236 15 12 NULL NULL NULL
4237 15 18 NULL NULL NULL
4238 15 2 NULL NULL NULL
4239 15 24 NULL NULL NULL
4240 15 27 NULL NULL NULL
4241 16 11 NULL NULL NULL
4242 16 14 NULL NULL NULL
4243 16 15 NULL NULL NULL
4244 16 19 NULL NULL NULL
4245 16 21 NULL NULL NULL
4246 16 23 NULL NULL NULL
4247 16 3 NULL NULL NULL
4248 16 5 NULL NULL NULL
4249 18 1 NULL NULL NULL
4250 18 12 NULL NULL NULL
4251 18 18 NULL NULL NULL
4252 18 2 NULL NULL NULL
4253 18 24 NULL NULL NULL
4254 18 27 NULL NULL NULL
4255 19 1 NULL NULL NULL
4256 19 12 NULL NULL NULL
4257 19 18 NULL NULL NULL
4258 19 2 NULL NULL NULL
4259 19 24 NULL NULL NULL
4260 19 27 NULL NULL NULL
4261 2 13 NULL NULL NULL
4262 2 16 NULL NULL NULL
4263 2 17 NULL NULL NULL
4264 2 22 NULL NULL NULL
4265 20 1 NULL NULL NULL
4266 20 12 NULL NULL NULL
4267 20 18 NULL NULL NULL
4268 20 2 NULL NULL NULL
4269 20 24 NULL NULL NULL
4270 20 27 NULL NULL NULL
4271 21 11 NULL NULL NULL
4272 21 14 NULL NULL NULL
4273 21 15 NULL NULL NULL
4274 21 19 NULL NULL NULL
4275 21 21 NULL NULL NULL
4276 21 23 NULL NULL NULL
4277 21 3 NULL NULL NULL
4278 21 5 NULL NULL NULL
4279 22 1 NULL NULL NULL
4280 22 12 NULL NULL NULL
4281 22 18 NULL NULL NULL
4282 22 2 NULL NULL NULL
4283 22 24 NULL NULL NULL
4284 22 27 NULL NULL NULL
4285 23 1 1 11 10
4286 23 1 1 11 13
4287 23 1 1 11 14
4288 23 1 1 11 21
4289 23 1 1 11 22
4290 23 1 1 11 3
4291 23 1 1 11 4
4292 23 1 1 11 5
4293 23 1 1 11 8
4294 23 1 1 14 10
4295 23 1 1 14 13
4296 23 1 1 14 14
4297 23 1 1 14 21
4298 23 1 1 14 22
4299 23 1 1 14 3
4300 23 1 1 14 4
4301 23 1 1 14 5
4302 23 1 1 14 8
4303 23 1 1 15 10
4304 23 1 1 15 13
4305 23 1 1 15 14
4306 23 1 1 15 21
4307 23 1 1 15 22
4308 23 1 1 15 3
4309 23 1 1 15 4
4310 23 1 1 15 5
4311 23 1 1 15 8
4312 23 1 1 19 10
4313 23 1 1 19 13
4314 23 1 1 19 14
4315 23 1 1 19 21
4316 23 1 1 19 22
4317 23 1 1 19 3
4318 23 1 1 19 4
4319 23 1 1 19 5
4320 23 1 1 19 8
4321 23 1 1 21 10
4322 23 1 1 21 13
4323 23 1 1 21 14
4324 23 1 1 21 21
4325 23 1 1 21 22
4326 23 1 1 21 3
4327 23 1 1 21 4
4328 23 1 1 21 5
4329 23 1 1 21 8
4330 23 1 1 23 10
4331 23 1 1 23 13
4332 23 1 1 23 14
4333 23 1 1 23 21
4334 23 1 1 23 22
4335 23 1 1 23 3
4336 23 1 1 23 4
4337 23 1 1 23 5
4338 23 1 1 23 8
4339 23 1 1 3 10
4340 23 1 1 3 13
4341 23 1 1 3 14
4342 23 1 1 3 21
4343 23 1 1 3 22
4344 23 1 1 3 3
4345 23 1 1 3 4
4346 23 1 1 3 5
4347 23 1 1 3 8
4348 23 1 1 5 10
4349 23 1 1 5 13
4350 23 1 1 5 14
4351 23 1 1 5 21
4352 23 1 1 5 22
4353 23 1 1 5 3
4354 23 1 1 5 4
4355 23 1 1 5 5
4356 23 1 1 5 8
4357 23 12 NULL NULL NULL
4358 23 18 NULL NULL NULL
4359 23 2 NULL NULL NULL
4360 23 24 NULL NULL NULL
4361 23 27 NULL NULL NULL
4362 24 13 NULL NULL NULL
4363 24 16 NULL NULL NULL
4364 24 17 NULL NULL NULL
4365 24 22 NULL NULL NULL
4366 25 10 NULL NULL NULL
4367 25 20 NULL NULL NULL
4368 25 25 NULL NULL NULL
4369 25 26 NULL NULL NULL
4370 25 4 NULL NULL NULL
4371 25 6 NULL NULL NULL
4372 25 7 NULL NULL NULL
4373 25 8 NULL NULL NULL
4374 26 1 NULL NULL NULL
4375 26 12 NULL NULL NULL
4376 26 18 NULL NULL NULL
4377 26 2 NULL NULL NULL
4378 26 24 NULL NULL NULL
4379 26 27 NULL NULL NULL
4380 27 1 NULL NULL NULL
4381 27 12 NULL NULL NULL
4382 27 18 NULL NULL NULL
4383 27 2 NULL NULL NULL
4384 27 24 NULL NULL NULL
4385 27 27 NULL NULL NULL
4386 28 13 NULL NULL NULL
4387 28 16 NULL NULL NULL
4388 28 17 NULL NULL NULL
4389 28 22 NULL NULL NULL
4390 29 1 NULL NULL NULL
4391 29 12 NULL NULL NULL
4392 29 18 NULL NULL NULL
4393 29 2 NULL NULL NULL
4394 29 24 NULL NULL NULL
4395 29 27 NULL NULL NULL
4396 3 11 NULL NULL NULL
4397 3 14 NULL NULL NULL
4398 3 15 NULL NULL NULL
4399 3 19 NULL NULL NULL
4400 3 21 NULL NULL NULL
4401 3 23 NULL NULL NULL
4402 3 3 13 NULL NULL
4403 3 5 13 NULL NULL
4404 4 1 NULL NULL NULL
4405 4 12 NULL NULL NULL
4406 4 18 NULL NULL NULL
4407 4 2 NULL NULL NULL
4408 4 24 NULL NULL NULL
4409 4 27 NULL NULL NULL
4410 5 13 NULL NULL NULL
4411 5 16 NULL NULL NULL
4412 5 17 NULL NULL NULL
4413 5 22 NULL NULL NULL
4414 6 1 NULL NULL NULL
4415 6 12 NULL NULL NULL
4416 6 18 NULL NULL NULL
4417 6 2 NULL NULL NULL
4418 6 24 NULL NULL NULL
4419 6 27 NULL NULL NULL
4420 7 13 NULL NULL NULL
4421 7 16 NULL NULL NULL
4422 7 17 NULL NULL NULL
4423 7 22 NULL NULL NULL
4424 8 13 NULL NULL NULL
4425 8 16 NULL NULL NULL
4426 8 17 NULL NULL NULL
4427 8 22 NULL NULL NULL
4428 9 10 NULL NULL NULL
4429 9 20 NULL NULL NULL
4430 9 25 NULL NULL NULL
4431 9 26 NULL NULL NULL
4432 9 4 NULL NULL NULL
4433 9 6 NULL NULL NULL
4434 9 7 NULL NULL NULL
4435 9 8 NULL NULL NULL
4436 ##################
4437 #
4438 # Bug#34379950 Pushed join execution fails with:
4439 # error 4800 'Required argument is NULL'
4440 #
4441 ##################
4442 explain format=tree SELECT
4443 table1.pk
4444 FROM v AS table1
4445 WHERE table1.col_int_unique IN (
4446 SELECT table3s.col_int_key
4447 FROM q AS table1s
4448 RIGHT JOIN d AS table2s
4449 RIGHT JOIN w AS table3s ON table2s.pk = table3s.col_int_key
4450 ON table1s.col_varchar_256_unique = table3s.col_char_16_key
4451 );
4452 EXPLAIN
4453 -> Nested loop semijoin
4454 -> Table scan on table1, with pushed condition: (table1.col_int_unique is not null)
4455 -> Nested loop left join
4456 -> Nested loop left join
4457 -> Index lookup on table3s using col_int_key (col_int_key=table1.col_int_unique), activating pushed join of 2 tables
4458 -> Single-row index lookup on table2s using PRIMARY (pk=table1.col_int_unique), child of table3s in pushed join, with pushed condition: (table2s.pk = table3s.col_int_key)
4459 -> Filter: (table1s.col_varchar_256_unique = table3s.col_char_16_key)
4460 -> Single-row index lookup on table1s using col_varchar_256_unique (col_varchar_256_unique=table3s.col_char_16_key)
4461
4462 Warnings:
4463 Note 1003 Can't push table 'table1s' as child, column 'col_varchar_256_unique' does not have same datatype as ref'ed column 'table3s.col_char_16_key'
4464 Note 1003 Can't push semi joined table 'table3s' as child of 'table1', some tables in embedding join-nest(s) are not part of pushed join
4465 SELECT
4466 table1.pk
4467 FROM v AS table1
4468 WHERE table1.col_int_unique IN (
4469 SELECT table3s.col_int_key
4470 FROM q AS table1s
4471 RIGHT JOIN d AS table2s
4472 RIGHT JOIN w AS table3s ON table2s.pk = table3s.col_int_key
4473 ON table1s.col_varchar_256_unique = table3s.col_char_16_key
4474 );
4475 pk
4476 1
4477 23
4478 26
4479 ##################
4480 # Bug#34486874 Error 4829:
4481 # 'FirstInner/Upper has to be an ancestor or a sibling'
4482 ##################
4483 explain format=tree SELECT
4484 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk, table6.pk
4485 FROM (j AS table1
4486 JOIN w AS table2 ON table1.col_int = table2.col_int AND
4487 table1.col_int_key = table2.col_int_key AND
4488 table1.col_int_unique = table2.col_int_unique
4489 JOIN n AS table3 ON table2.pk = table3.col_int_key
4490 RIGHT JOIN c AS table4
4491 JOIN g AS table5 ON table4.col_int_key = table5.col_int_key
4492 ON table2.pk = table5.col_int_key)
4493 LEFT JOIN s AS table6 ON table1.col_int_key = table6.col_int_key AND
4494 table5.col_int_unique = table6.col_int_unique;
4495 EXPLAIN
4496 -> Nested loop left join
4497 -> Nested loop left join
4498 -> Nested loop inner join
4499 -> Table scan on table4, activating pushed join of 6 tables, with pushed condition: (table4.col_int_key is not null)
4500 -> Index lookup on table5 using col_int_key (col_int_key=table4.col_int_key), child of table4 in pushed join
4501 -> Nested loop inner join
4502 -> Nested loop inner join
4503 -> Single-row index lookup on table2 using PRIMARY (pk=table4.col_int_key), child of table5 in pushed join
4504 -> Single-row index lookup on table1 using ix3 (col_int=table2.col_int, col_int_key=table2.col_int_key, col_int_unique=table2.col_int_unique), child of table2 in pushed join
4505 -> Index lookup on table3 using col_int_key (col_int_key=table4.col_int_key), child of table5 in pushed join
4506 -> Single-row index lookup on table6 using ix2 (col_int_key=table1.col_int_key, col_int_unique=table5.col_int_unique), child of table1 in pushed join
4507
4508 SELECT
4509 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk, table6.pk
4510 FROM (j AS table1
4511 JOIN w AS table2 ON table1.col_int = table2.col_int AND
4512 table1.col_int_key = table2.col_int_key AND
4513 table1.col_int_unique = table2.col_int_unique
4514 JOIN n AS table3 ON table2.pk = table3.col_int_key
4515 RIGHT JOIN c AS table4
4516 JOIN g AS table5 ON table4.col_int_key = table5.col_int_key
4517 ON table2.pk = table5.col_int_key)
4518 LEFT JOIN s AS table6 ON table1.col_int_key = table6.col_int_key AND
4519 table5.col_int_unique = table6.col_int_unique;
4520 pk pk pk pk pk pk
4521 NULL NULL NULL 1 16 NULL
4522 NULL NULL NULL 1 17 NULL
4523 NULL NULL NULL 1 19 NULL
4524 NULL NULL NULL 1 3 NULL
4525 NULL NULL NULL 1 4 NULL
4526 NULL NULL NULL 1 6 NULL
4527 NULL NULL NULL 1 8 NULL
4528 NULL NULL NULL 2 16 NULL
4529 NULL NULL NULL 2 17 NULL
4530 NULL NULL NULL 2 19 NULL
4531 NULL NULL NULL 2 3 NULL
4532 NULL NULL NULL 2 4 NULL
4533 NULL NULL NULL 2 6 NULL
4534 NULL NULL NULL 2 8 NULL
4535 NULL NULL NULL 3 11 NULL
4536 NULL NULL NULL 3 7 NULL
4537 NULL NULL NULL 4 13 NULL
4538 NULL NULL NULL 4 14 NULL
4539 NULL NULL NULL 4 18 NULL
4540 NULL NULL NULL 4 5 NULL
4541 NULL NULL NULL 4 9 NULL
4542 NULL NULL NULL 5 16 NULL
4543 NULL NULL NULL 5 17 NULL
4544 NULL NULL NULL 5 19 NULL
4545 NULL NULL NULL 5 3 NULL
4546 NULL NULL NULL 5 4 NULL
4547 NULL NULL NULL 5 6 NULL
4548 NULL NULL NULL 5 8 NULL
4549 explain format=tree SELECT
4550 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk
4551 FROM d AS table1
4552 LEFT JOIN v AS table2
4553 INNER JOIN q AS table3 ON table2.col_int_key = table3.col_int_key AND
4554 table2.col_int_unique = table3.col_int_unique
4555 ON table1.col_int_key = table2.col_int_key AND
4556 table1.col_int_unique = table2.col_int_unique
4557 RIGHT JOIN p AS table4
4558 RIGHT JOIN q AS table5 ON table4.col_int_key = table5.col_int_key AND
4559 table4.col_int_unique = table5.col_int_unique OR
4560 table4.pk = table5.col_int_key
4561 ON table1.col_int_key = table5.col_int_key AND
4562 table1.col_int_unique = table5.col_int_unique;
4563 EXPLAIN
4564 -> Nested loop left join
4565 -> Nested loop left join
4566 -> Table scan on table5, activating pushed join of 4 tables
4567 -> Index range scan on table4 (re-planned for each iteration), with pushed condition: (((table4.col_int_unique = table5.col_int_unique) and (table4.col_int_key = table5.col_int_key)) or (table4.pk = table5.col_int_key))
4568 -> Nested loop left join
4569 -> Single-row index lookup on table1 using ix1 (col_int_unique=table5.col_int_unique), child of table5 in pushed join, with pushed condition: (table1.col_int_key = table5.col_int_key)
4570 -> Nested loop inner join
4571 -> Single-row index lookup on table3 using ix2 (col_int_key=table1.col_int_key, col_int_unique=table1.col_int_unique), child of table1 in pushed join, with pushed condition: ((table3.col_int_unique = table5.col_int_unique) and (table3.col_int_key = table5.col_int_key))
4572 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join, with pushed condition: ((table2.col_int_unique = table5.col_int_unique) and (table2.col_int_key = table5.col_int_key))
4573
4574 Warnings:
4575 Note 1003 Table 'table4' is not pushable: Access type was not chosen at 'prepare' time
4576 SELECT
4577 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk
4578 FROM d AS table1
4579 LEFT JOIN v AS table2
4580 INNER JOIN q AS table3 ON table2.col_int_key = table3.col_int_key AND
4581 table2.col_int_unique = table3.col_int_unique
4582 ON table1.col_int_key = table2.col_int_key AND
4583 table1.col_int_unique = table2.col_int_unique
4584 RIGHT JOIN p AS table4
4585 RIGHT JOIN q AS table5 ON table4.col_int_key = table5.col_int_key AND
4586 table4.col_int_unique = table5.col_int_unique OR
4587 table4.pk = table5.col_int_key
4588 ON table1.col_int_key = table5.col_int_key AND
4589 table1.col_int_unique = table5.col_int_unique;
4590 pk pk pk pk pk
4591 6 NULL NULL 2 9
4592 NULL NULL NULL 1 11
4593 NULL NULL NULL 1 19
4594 NULL NULL NULL 1 2
4595 NULL NULL NULL 1 3
4596 NULL NULL NULL 1 4
4597 NULL NULL NULL 2 15
4598 NULL NULL NULL 2 18
4599 NULL NULL NULL 2 23
4600 NULL NULL NULL 2 5
4601 NULL NULL NULL 2 7
4602 NULL NULL NULL 3 1
4603 NULL NULL NULL 3 12
4604 NULL NULL NULL 3 13
4605 NULL NULL NULL 3 16
4606 NULL NULL NULL 3 17
4607 NULL NULL NULL 3 20
4608 NULL NULL NULL 3 22
4609 NULL NULL NULL 6 17
4610 NULL NULL NULL 8 25
4611 NULL NULL NULL NULL 10
4612 NULL NULL NULL NULL 14
4613 NULL NULL NULL NULL 21
4614 NULL NULL NULL NULL 24
4615 NULL NULL NULL NULL 6
4616 NULL NULL NULL NULL 8
4617 explain format=tree SELECT
4618 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk
4619 FROM j AS table1
4620 JOIN j AS table2 ON table1.col_int_key = table2.col_int_key
4621 LEFT JOIN w AS table3 ON table2.pk = table3.col_int_key
4622 LEFT JOIN w AS table4
4623 JOIN x AS table5 ON table4.col_int_unique = table5.pk
4624 ON table1.col_int = table5.col_int AND
4625 table3.col_int_key = table4.col_int_key AND
4626 table1.col_int_unique = table5.col_int_unique;
4627 EXPLAIN
4628 -> Nested loop left join
4629 -> Nested loop left join
4630 -> Nested loop inner join
4631 -> Table scan on table1, activating pushed join of 5 tables, with pushed condition: (table1.col_int_key is not null)
4632 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
4633 -> Index lookup on table3 using col_int_key (col_int_key=table2.pk), child of table2 in pushed join
4634 -> Nested loop inner join
4635 -> Index lookup on table4 using col_int_key (col_int_key=table3.col_int_key), child of table3 in pushed join
4636 -> Single-row index lookup on table5 using PRIMARY (col_int=table1.col_int, pk=table4.col_int_unique), child of table4 in pushed join, with pushed condition: (table5.col_int_unique = table1.col_int_unique)
4637
4638 SELECT
4639 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk
4640 FROM j AS table1
4641 JOIN j AS table2 ON table1.col_int_key = table2.col_int_key
4642 LEFT JOIN w AS table3 ON table2.pk = table3.col_int_key
4643 LEFT JOIN w AS table4
4644 JOIN x AS table5 ON table4.col_int_unique = table5.pk
4645 ON table1.col_int = table5.col_int AND
4646 table3.col_int_key = table4.col_int_key AND
4647 table1.col_int_unique = table5.col_int_unique;
4648 pk pk pk pk pk
4649 1 1 1 NULL NULL
4650 1 1 12 NULL NULL
4651 1 1 14 NULL NULL
4652 1 1 15 NULL NULL
4653 1 1 16 NULL NULL
4654 1 1 18 NULL NULL
4655 1 1 22 NULL NULL
4656 1 1 24 NULL NULL
4657 1 1 29 NULL NULL
4658 1 1 9 NULL NULL
4659 1 18 NULL NULL NULL
4660 1 19 NULL NULL NULL
4661 1 4 NULL NULL NULL
4662 1 7 NULL NULL NULL
4663 10 10 NULL NULL NULL
4664 10 12 NULL NULL NULL
4665 10 2 11 NULL NULL
4666 10 2 13 NULL NULL
4667 10 2 2 NULL NULL
4668 10 2 27 NULL NULL
4669 10 2 30 NULL NULL
4670 10 2 6 NULL NULL
4671 10 3 17 NULL NULL
4672 10 3 20 NULL NULL
4673 10 3 21 NULL NULL
4674 10 3 26 NULL NULL
4675 10 3 28 NULL NULL
4676 10 3 3 NULL NULL
4677 10 3 5 NULL NULL
4678 10 5 NULL NULL NULL
4679 11 11 NULL NULL NULL
4680 11 14 NULL NULL NULL
4681 11 17 NULL NULL NULL
4682 11 21 NULL NULL NULL
4683 12 10 NULL NULL NULL
4684 12 12 NULL NULL NULL
4685 12 2 11 NULL NULL
4686 12 2 13 NULL NULL
4687 12 2 2 NULL NULL
4688 12 2 27 NULL NULL
4689 12 2 30 NULL NULL
4690 12 2 6 NULL NULL
4691 12 3 17 NULL NULL
4692 12 3 20 NULL NULL
4693 12 3 21 NULL NULL
4694 12 3 26 NULL NULL
4695 12 3 28 NULL NULL
4696 12 3 3 NULL NULL
4697 12 3 5 NULL NULL
4698 12 5 NULL NULL NULL
4699 13 13 NULL NULL NULL
4700 13 15 NULL NULL NULL
4701 13 20 NULL NULL NULL
4702 13 6 NULL NULL NULL
4703 13 8 NULL NULL NULL
4704 13 9 NULL NULL NULL
4705 14 11 NULL NULL NULL
4706 14 14 NULL NULL NULL
4707 14 17 NULL NULL NULL
4708 14 21 NULL NULL NULL
4709 15 13 NULL NULL NULL
4710 15 15 NULL NULL NULL
4711 15 20 NULL NULL NULL
4712 15 6 NULL NULL NULL
4713 15 8 NULL NULL NULL
4714 15 9 NULL NULL NULL
4715 17 11 NULL NULL NULL
4716 17 14 NULL NULL NULL
4717 17 17 NULL NULL NULL
4718 17 21 NULL NULL NULL
4719 18 1 1 NULL NULL
4720 18 1 12 NULL NULL
4721 18 1 14 NULL NULL
4722 18 1 15 NULL NULL
4723 18 1 16 NULL NULL
4724 18 1 18 NULL NULL
4725 18 1 22 NULL NULL
4726 18 1 24 NULL NULL
4727 18 1 29 NULL NULL
4728 18 1 9 NULL NULL
4729 18 18 NULL NULL NULL
4730 18 19 NULL NULL NULL
4731 18 4 NULL NULL NULL
4732 18 7 NULL NULL NULL
4733 19 1 1 NULL NULL
4734 19 1 12 NULL NULL
4735 19 1 14 NULL NULL
4736 19 1 15 NULL NULL
4737 19 1 16 NULL NULL
4738 19 1 18 NULL NULL
4739 19 1 22 NULL NULL
4740 19 1 24 NULL NULL
4741 19 1 29 NULL NULL
4742 19 1 9 NULL NULL
4743 19 18 NULL NULL NULL
4744 19 19 NULL NULL NULL
4745 19 4 NULL NULL NULL
4746 19 7 NULL NULL NULL
4747 2 10 NULL NULL NULL
4748 2 12 NULL NULL NULL
4749 2 2 11 NULL NULL
4750 2 2 13 NULL NULL
4751 2 2 2 NULL NULL
4752 2 2 27 NULL NULL
4753 2 2 30 NULL NULL
4754 2 2 6 NULL NULL
4755 2 3 17 NULL NULL
4756 2 3 20 NULL NULL
4757 2 3 21 NULL NULL
4758 2 3 26 NULL NULL
4759 2 3 28 NULL NULL
4760 2 3 3 NULL NULL
4761 2 3 5 NULL NULL
4762 2 5 NULL NULL NULL
4763 20 13 NULL NULL NULL
4764 20 15 NULL NULL NULL
4765 20 20 NULL NULL NULL
4766 20 6 NULL NULL NULL
4767 20 8 NULL NULL NULL
4768 20 9 NULL NULL NULL
4769 21 11 NULL NULL NULL
4770 21 14 NULL NULL NULL
4771 21 17 NULL NULL NULL
4772 21 21 NULL NULL NULL
4773 3 10 NULL NULL NULL
4774 3 12 NULL NULL NULL
4775 3 2 11 NULL NULL
4776 3 2 13 NULL NULL
4777 3 2 2 NULL NULL
4778 3 2 27 NULL NULL
4779 3 2 30 NULL NULL
4780 3 2 6 NULL NULL
4781 3 3 17 NULL NULL
4782 3 3 20 NULL NULL
4783 3 3 21 NULL NULL
4784 3 3 26 NULL NULL
4785 3 3 28 NULL NULL
4786 3 3 3 NULL NULL
4787 3 3 5 NULL NULL
4788 3 5 NULL NULL NULL
4789 4 1 1 NULL NULL
4790 4 1 12 NULL NULL
4791 4 1 14 NULL NULL
4792 4 1 15 NULL NULL
4793 4 1 16 NULL NULL
4794 4 1 18 NULL NULL
4795 4 1 22 NULL NULL
4796 4 1 24 NULL NULL
4797 4 1 29 NULL NULL
4798 4 1 9 NULL NULL
4799 4 18 NULL NULL NULL
4800 4 19 NULL NULL NULL
4801 4 4 NULL NULL NULL
4802 4 7 NULL NULL NULL
4803 5 10 NULL NULL NULL
4804 5 12 NULL NULL NULL
4805 5 2 11 NULL NULL
4806 5 2 13 NULL NULL
4807 5 2 2 NULL NULL
4808 5 2 27 NULL NULL
4809 5 2 30 NULL NULL
4810 5 2 6 NULL NULL
4811 5 3 17 NULL NULL
4812 5 3 20 NULL NULL
4813 5 3 21 NULL NULL
4814 5 3 26 NULL NULL
4815 5 3 28 NULL NULL
4816 5 3 3 NULL NULL
4817 5 3 5 NULL NULL
4818 5 5 NULL NULL NULL
4819 6 13 NULL NULL NULL
4820 6 15 NULL NULL NULL
4821 6 20 NULL NULL NULL
4822 6 6 NULL NULL NULL
4823 6 8 NULL NULL NULL
4824 6 9 NULL NULL NULL
4825 7 1 1 NULL NULL
4826 7 1 12 NULL NULL
4827 7 1 14 NULL NULL
4828 7 1 15 NULL NULL
4829 7 1 16 NULL NULL
4830 7 1 18 NULL NULL
4831 7 1 22 NULL NULL
4832 7 1 24 NULL NULL
4833 7 1 29 NULL NULL
4834 7 1 9 NULL NULL
4835 7 18 NULL NULL NULL
4836 7 19 NULL NULL NULL
4837 7 4 NULL NULL NULL
4838 7 7 NULL NULL NULL
4839 8 13 NULL NULL NULL
4840 8 15 NULL NULL NULL
4841 8 20 NULL NULL NULL
4842 8 6 NULL NULL NULL
4843 8 8 NULL NULL NULL
4844 8 9 NULL NULL NULL
4845 9 13 NULL NULL NULL
4846 9 15 NULL NULL NULL
4847 9 20 NULL NULL NULL
4848 9 6 NULL NULL NULL
4849 9 8 NULL NULL NULL
4850 9 9 NULL NULL NULL
4851 explain format=tree SELECT
4852 table1.pk, table2.pk, table3.pk, table4.pk
4853 FROM y AS table1
4854 RIGHT JOIN n AS table2
4855 JOIN w AS table3 ON table2.col_int = table3.col_int AND
4856 table2.col_int_key = table3.col_int_key AND
4857 table2.col_int_unique = table3.col_int_unique
4858 ON table1.col_int = table3.col_int_unique
4859 INNER JOIN l AS table4 ON table4.col_int IS NULL OR
4860 table1.col_int_unique = table4.col_int_key;
4861 EXPLAIN
4862 -> Nested loop inner join
4863 -> Filter: (table1.col_int_unique is not null)
4864 -> Nested loop left join
4865 -> Nested loop inner join
4866 -> Table scan on table2, activating pushed join of 4 tables, with pushed condition: (table2.col_int_key is not null)
4867 -> Index lookup on table3 using col_int_key (col_int_key=table2.col_int_key), child of table2 in pushed join, with pushed condition: ((table3.col_int_unique = table2.col_int_unique) and (table3.col_int = table2.col_int))
4868 -> Index lookup on table1 using PRIMARY (col_int=table2.col_int_unique), child of table2 in pushed join
4869 -> Index lookup on table4 using col_int_key (col_int_key=table1.col_int_unique), child of table1 in pushed join
4870
4871 SELECT
4872 table1.pk, table2.pk, table3.pk, table4.pk
4873 FROM y AS table1
4874 RIGHT JOIN n AS table2
4875 JOIN w AS table3 ON table2.col_int = table3.col_int AND
4876 table2.col_int_key = table3.col_int_key AND
4877 table2.col_int_unique = table3.col_int_unique
4878 ON table1.col_int = table3.col_int_unique
4879 INNER JOIN l AS table4 ON table4.col_int IS NULL OR
4880 table1.col_int_unique = table4.col_int_key;
4881 pk pk pk pk
4882 ##################
4883 #
4884 # Bug#34508948 Allow more conditions to be pushed down
4885 #
4886 ##################
4887 explain format=tree SELECT STRAIGHT_JOIN
4888 table1.pk, table2.pk, table3.pk
4889 FROM p AS table1
4890 JOIN g AS table2 ON table2.col_int_unique = table1.col_int_unique
4891 JOIN n AS table3 ON table3.col_int_key = table1.col_int
4892 WHERE table3.pk = 8 OR table3.col_varchar_256_key = table2.col_varchar_256;
4893 EXPLAIN
4894 -> Nested loop inner join
4895 -> Nested loop inner join
4896 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: ((table1.col_int_unique is not null) and (table1.col_int is not null))
4897 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join
4898 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int), child of table2 in pushed join, with pushed condition: ((table3.pk = 8) or (table3.col_varchar_256_key = table2.col_varchar_256))
4899
4900 SELECT STRAIGHT_JOIN
4901 table1.pk, table2.pk, table3.pk
4902 FROM p AS table1
4903 JOIN g AS table2 ON table2.col_int_unique = table1.col_int_unique
4904 JOIN n AS table3 ON table3.col_int_key = table1.col_int
4905 WHERE table3.pk = 8 OR table3.col_varchar_256_key = table2.col_varchar_256;
4906 pk pk pk
4907 16 1 8
4908 explain format=tree SELECT STRAIGHT_JOIN
4909 table1.pk, table2.pk, table3.pk
4910 FROM p AS table1
4911 JOIN g AS table2 ON table2.col_int_key = table1.col_int_key
4912 JOIN n AS table3 ON table3.col_int_key = table1.col_int
4913 WHERE table3.pk = 8 OR table3.col_varchar_256_key = table2.col_varchar_256;
4914 EXPLAIN
4915 -> Nested loop inner join
4916 -> Nested loop inner join
4917 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: ((table1.col_int_key is not null) and (table1.col_int is not null))
4918 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key), child of table1 in pushed join
4919 -> Filter: ((table3.pk = 8) or (table3.col_varchar_256_key = table2.col_varchar_256))
4920 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int), child of table1 in pushed join
4921
4922 SELECT STRAIGHT_JOIN
4923 table1.pk, table2.pk, table3.pk
4924 FROM p AS table1
4925 JOIN g AS table2 ON table2.col_int_key = table1.col_int_key
4926 JOIN n AS table3 ON table3.col_int_key = table1.col_int
4927 WHERE table3.pk = 8 OR table3.col_varchar_256_key = table2.col_varchar_256;
4928 pk pk pk
4929 1 16 8
4930 1 17 8
4931 1 19 8
4932 1 3 8
4933 1 4 8
4934 1 6 8
4935 1 8 8
4936 11 13 8
4937 11 14 8
4938 11 18 8
4939 11 5 8
4940 11 9 8
4941 13 16 8
4942 13 17 8
4943 13 19 8
4944 13 3 8
4945 13 4 8
4946 13 6 8
4947 13 8 8
4948 14 11 8
4949 14 7 8
4950 16 13 8
4951 16 14 8
4952 16 18 8
4953 16 5 8
4954 16 9 8
4955 6 13 8
4956 6 14 8
4957 6 18 8
4958 6 5 8
4959 6 9 8
4960 7 11 8
4961 7 7 8
4962 8 11 8
4963 8 7 8
4964 explain format=tree SELECT STRAIGHT_JOIN
4965 table1.pk, table2.pk, table3.pk
4966 FROM p AS table1
4967 LEFT JOIN g AS table2 ON table2.col_int_unique = table1.col_int_unique
4968 JOIN n AS table3 ON table3.col_int_key = table1.col_int
4969 WHERE table3.pk = 8 OR table3.col_varchar_256_key = table2.col_varchar_256;
4970 EXPLAIN
4971 -> Nested loop inner join
4972 -> Nested loop left join
4973 -> Table scan on table1, activating pushed join of 3 tables, with pushed condition: (table1.col_int is not null)
4974 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.col_int_unique), child of table1 in pushed join
4975 -> Filter: ((table3.pk = 8) or (table3.col_varchar_256_key = table2.col_varchar_256))
4976 -> Index lookup on table3 using col_int_key (col_int_key=table1.col_int), child of table1 in pushed join
4977
4978 SELECT STRAIGHT_JOIN
4979 table1.pk, table2.pk, table3.pk
4980 FROM p AS table1
4981 LEFT JOIN g AS table2 ON table2.col_int_unique = table1.col_int_unique
4982 JOIN n AS table3 ON table3.col_int_key = table1.col_int
4983 WHERE table3.pk = 8 OR table3.col_varchar_256_key = table2.col_varchar_256;
4984 pk pk pk
4985 1 NULL 8
4986 11 NULL 8
4987 13 NULL 8
4988 14 NULL 8
4989 16 1 8
4990 6 NULL 8
4991 7 NULL 8
4992 8 NULL 8
4993 explain format=tree SELECT
4994 table1.pk, table2.pk, table3.pk, table4.pk
4995 FROM c AS table1
4996 RIGHT JOIN a AS table2
4997 RIGHT JOIN d AS table3 ON table2.pk = table3.col_int
4998 RIGHT JOIN d AS table4 ON table3.col_int_key = table4.col_int_key AND
4999 table2.col_int_unique = table4.col_int_unique
5000 ON table1.pk = table2.col_int_unique
5001 WHERE table1.col_int_unique > 9;
5002 EXPLAIN
5003 -> Nested loop inner join
5004 -> Nested loop inner join
5005 -> Nested loop inner join
5006 -> Index range scan on table1 using ix1 over (9 < col_int_unique), activating pushed join of 4 tables, with pushed condition: ((table1.col_int_unique > 9) and (table1.pk is not null))
5007 -> Single-row index lookup on table4 using ix1 (col_int_unique=table1.pk), child of table1 in pushed join
5008 -> Single-row index lookup on table2 using ix1 (col_int_unique=table1.pk), child of table4 in pushed join
5009 -> Index lookup on table3 using col_int_key (col_int_key=table4.col_int_key), child of table2 in pushed join, with pushed condition: (table3.col_int = table2.pk)
5010
5011 SELECT
5012 table1.pk, table2.pk, table3.pk, table4.pk
5013 FROM c AS table1
5014 RIGHT JOIN a AS table2
5015 RIGHT JOIN d AS table3 ON table2.pk = table3.col_int
5016 RIGHT JOIN d AS table4 ON table3.col_int_key = table4.col_int_key AND
5017 table2.col_int_unique = table4.col_int_unique
5018 ON table1.pk = table2.col_int_unique
5019 WHERE table1.col_int_unique > 9;
5020 pk pk pk pk
5021 explain format=tree SELECT table3.col_char_16_unique AS field1
5022 FROM d AS table1
5023 JOIN m AS table2 ON table1.col_int_unique = table2.pk
5024 JOIN m AS table3 ON table1.pk = table3.col_int_unique
5025 RIGHT JOIN j AS table4 ON table3.col_int_key = table4.col_int AND
5026 table2.col_int_unique = table4.col_int_key
5027 WHERE table2.col_int = 76;
5028 EXPLAIN
5029 -> Nested loop inner join
5030 -> Nested loop inner join
5031 -> Nested loop inner join
5032 -> Index lookup on table2 using ix3 (col_int=76), activating pushed join of 4 tables, with pushed condition: (table2.col_int_unique is not null)
5033 -> Single-row index lookup on table1 using ix1 (col_int_unique=table2.pk), child of table2 in pushed join
5034 -> Index lookup on table4 using col_int_key (col_int_key=table2.col_int_unique), child of table1 in pushed join
5035 -> Index lookup on table3 using col_int_key (col_int_key=table4.col_int), child of table4 in pushed join, with pushed condition: (table3.col_int_unique = table1.pk)
5036
5037 SELECT table3.col_char_16_unique AS field1
5038 FROM d AS table1
5039 JOIN m AS table2 ON table1.col_int_unique = table2.pk
5040 JOIN m AS table3 ON table1.pk = table3.col_int_unique
5041 RIGHT JOIN j AS table4 ON table3.col_int_key = table4.col_int AND
5042 table2.col_int_unique = table4.col_int_key
5043 WHERE table2.col_int = 76;
5044 field1
5045 explain format=tree SELECT
5046 table2.col_int_key AS field1
5047 FROM d AS table1
5048 JOIN f AS table2
5049 LEFT JOIN m AS table3
5050 RIGHT JOIN e AS table4 ON table3.col_int_key = table4.col_int_unique
5051 ON table2.pk = table3.col_int
5052 ON table1.col_int_unique = table2.col_int_key
5053 JOIN h AS table5 ON table4.col_int_key = table5.col_int_key AND
5054 table2.col_int_unique = table5.col_int_unique;
5055 EXPLAIN
5056 -> Nested loop inner join
5057 -> Nested loop inner join
5058 -> Nested loop inner join
5059 -> Nested loop inner join
5060 -> Table scan on table1, activating pushed join of 5 tables, with pushed condition: (table1.col_int_unique is not null)
5061 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_unique), child of table1 in pushed join, with pushed condition: (table2.col_int_unique is not null)
5062 -> Single-row index lookup on table5 using ix1 (col_int_unique=table2.col_int_unique), child of table2 in pushed join
5063 -> Index lookup on table3 using ix3 (col_int=table2.pk), child of table5 in pushed join
5064 -> Single-row index lookup on table4 using ix1 (col_int_unique=table3.col_int_key), child of table3 in pushed join, with pushed condition: (table4.col_int_key = table5.col_int_key)
5065
5066 SELECT
5067 table2.col_int_key AS field1
5068 FROM d AS table1
5069 JOIN f AS table2
5070 LEFT JOIN m AS table3
5071 RIGHT JOIN e AS table4 ON table3.col_int_key = table4.col_int_unique
5072 ON table2.pk = table3.col_int
5073 ON table1.col_int_unique = table2.col_int_key
5074 JOIN h AS table5 ON table4.col_int_key = table5.col_int_key AND
5075 table2.col_int_unique = table5.col_int_unique;
5076 field1
5077 explain format=tree SELECT
5078 #table2.col_char_16_unique AS field1
5079 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk, table6.pk
5080 FROM a AS table1
5081 JOIN k AS table2 ON table1.col_int_unique = table2.col_int
5082 RIGHT JOIN r AS table3 ON table1.col_varchar_10_key = table3.col_varchar_10_key
5083 RIGHT JOIN w AS table4
5084 JOIN l AS table5
5085 RIGHT JOIN v AS table6 ON table5.col_varchar_10_unique = table6.col_varchar_10_unique
5086 ON table4.pk = table6.col_int_unique
5087 ON table2.col_int_key = table5.col_int_key AND
5088 table3.col_int_unique = table4.col_int_unique
5089 WHERE table4.pk >= 3;
5090 EXPLAIN
5091 -> Nested loop left join
5092 -> Nested loop inner join
5093 -> Nested loop left join
5094 -> Table scan on table6, activating pushed join of 3 tables, with pushed condition: ((table6.col_int_unique >= 3) and (table6.col_int_unique is not null))
5095 -> Single-row index lookup on table5 using col_varchar_10_unique (col_varchar_10_unique=table6.col_varchar_10_unique), child of table6 in pushed join
5096 -> Single-row index lookup on table4 using PRIMARY (pk=table6.col_int_unique), child of table6 in pushed join
5097 -> Nested loop inner join
5098 -> Nested loop inner join
5099 -> Index lookup on table2 using col_int_key (col_int_key=table5.col_int_key), activating pushed join of 3 tables
5100 -> Single-row index lookup on table1 using ix1 (col_int_unique=table2.col_int), child of table2 in pushed join
5101 -> Index lookup on table3 using col_varchar_10_key (col_varchar_10_key=table1.col_varchar_10_key), child of table1 in pushed join, with pushed condition: (table3.col_int_unique = table4.col_int_unique)
5102
5103 Warnings:
5104 Note 1003 Can't push outer joined table 'table3' as child of 'table6', table condition can not be fully evaluated by pushed join
5105 Note 1003 Can't push outer joined table 'table2' as child of 'table6', some tables in embedding join-nest(s) are not part of pushed join
5106 SELECT
5107 #table2.col_char_16_unique AS field1
5108 table1.pk, table2.pk, table3.pk, table4.pk, table5.pk, table6.pk
5109 FROM a AS table1
5110 JOIN k AS table2 ON table1.col_int_unique = table2.col_int
5111 RIGHT JOIN r AS table3 ON table1.col_varchar_10_key = table3.col_varchar_10_key
5112 RIGHT JOIN w AS table4
5113 JOIN l AS table5
5114 RIGHT JOIN v AS table6 ON table5.col_varchar_10_unique = table6.col_varchar_10_unique
5115 ON table4.pk = table6.col_int_unique
5116 ON table2.col_int_key = table5.col_int_key AND
5117 table3.col_int_unique = table4.col_int_unique
5118 WHERE table4.pk >= 3;
5119 pk pk pk pk pk pk
5120 NULL NULL NULL 19 NULL 8
5121 NULL NULL NULL 25 NULL 5
5122 NULL NULL NULL 26 NULL 2
5123 NULL NULL NULL 3 NULL 1
5124 NULL NULL NULL 30 NULL 24
5125 NULL NULL NULL 4 NULL 22
5126 NULL NULL NULL 8 NULL 20
5127 NULL NULL NULL 9 NULL 21
5128 ##################
5129 #
5130 # Bug#34231798 Incorrect query result from pushed join with IN-subquery
5131 #
5132 ##################
5133 explain format=tree SELECT DISTINCT
5134 table2.col_varchar_256_unique
5135 FROM r AS table1
5136 JOIN q AS table2 ON table2.col_int IS NOT NULL
5137 WHERE table2.col_int IN (SELECT /*+ SEMIJOIN(LOOSESCAN)*/ col_int_key FROM u);
5138 EXPLAIN
5139 -> Table scan on <temporary>
5140 -> Temporary table with deduplication
5141 -> Inner hash join (no condition)
5142 -> Table scan on table1
5143 -> Hash
5144 -> Nested loop inner join
5145 -> Remove duplicates from input sorted on col_int_key
5146 -> Index range scan on u using col_int_key over (NULL < col_int_key), with pushed condition: ((u.col_int_key is not null) and (u.col_int_key is not null))
5147 -> Index lookup on table2 using ix1 (col_int=u.col_int_key)
5148
5149 Warnings:
5150 Note 1003 Can't push table 'table2' as scan-child of 'u', depends on tables being 'skip-read'
5151 Note 1003 Can't push table 'table1' as child, 'type' must be a 'ref' access
5152 SELECT DISTINCT
5153 table2.col_varchar_256_unique
5154 FROM r AS table1
5155 JOIN q AS table2 ON table2.col_int IS NOT NULL
5156 WHERE table2.col_int IN (SELECT /*+ SEMIJOIN(LOOSESCAN)*/ col_int_key FROM u);
5157 col_varchar_256_unique
5158 I
5159 back
5160 ckpwhjduuegjyzyykqjnzab
5161 didn't
5162 fuaiiyufnngmlsudti
5163 hey
5164 him
5165 hvtm
5166 icqcjhvtmfnumoxifzchbfuaiiy
5167 ifzchbfuaiiyufnngm
5168 nduhvuctfgfcwjacjpett
5169 now
5170 one
5171 or
5172 qjnzabvesmothzh
5173 see
5174 that
5175 then
5176 there
5177 ucicqcjhvtm
5178 we
5179 whj
5180 would
5181 wtnjnpizfjtvnxtispzc
5182 explain format=tree SELECT
5183 table2.col_int_key AS field1,
5184 table1.col_int_unique AS field2,
5185 table2.col_int AS field3
5186 FROM (q AS table1
5187 JOIN h AS table2 ON table1.col_int = table2.col_int_key )
5188 WHERE ( table2.col_int_unique = ANY (
5189 SELECT /*+ SEMIJOIN(LOOSESCAN)*/
5190 table2s.col_int_key
5191 FROM o AS table1s
5192 INNER JOIN l AS table2s ON table1s.col_int = table2s.col_int)
5193 AND table2.col_int_unique <= 3);
5194 EXPLAIN
5195 -> Nested loop inner join
5196 -> Nested loop inner join
5197 -> Nested loop semijoin with duplicate removal on col_int_key
5198 -> Index range scan on table2s using col_int_key over (NULL < col_int_key <= 3), activating pushed join of 3 tables, with pushed condition: ((table2s.col_int_key <= 3) and (table2s.col_int_key is not null))
5199 -> Index lookup on table1s using ix3 (col_int=table2s.col_int), child of table2s in pushed join
5200 -> Single-row index lookup on table2 using ix1 (col_int_unique=table2s.col_int_key), child of table2s in pushed join, with pushed condition: (table2.col_int_key is not null)
5201 -> Index lookup on table1 using ix1 (col_int=table2.col_int_key)
5202
5203 Warnings:
5204 Note 1003 Can't push table 'table1' as scan-child of 'table2s', depends on tables being 'skip-read'
5205 SELECT
5206 table2.col_int_key AS field1,
5207 table1.col_int_unique AS field2,
5208 table2.col_int AS field3
5209 FROM (q AS table1
5210 JOIN h AS table2 ON table1.col_int = table2.col_int_key )
5211 WHERE ( table2.col_int_unique = ANY (
5212 SELECT /*+ SEMIJOIN(LOOSESCAN)*/
5213 table2s.col_int_key
5214 FROM o AS table1s
5215 INNER JOIN l AS table2s ON table1s.col_int = table2s.col_int)
5216 AND table2.col_int_unique <= 3);
5217 field1 field2 field3
5218 0 10 0
5219 0 12 0
5220 0 22 0
5221 0 33 0
5222 0 4 0
5223 0 41 0
5224 0 5 0
5225 0 NULL 0
5226 ##################
5227 #
5228 # Bug#34782276:
5229 # Improve SPJ-internal execution plan, evaluate inner-joins first
5230 #
5231 ##################
5232 explain format=tree SELECT
5233 table1.pk, table2.pk, table3.pk, table4.pk
5234 FROM x AS table1
5235 JOIN q AS table2
5236 RIGHT OUTER JOIN q AS table3
5237 LEFT OUTER JOIN x AS table4 ON table3.pk = table4.col_int_key
5238 ON table2.col_int = table3.col_int AND
5239 table2.col_int_unique = table3.col_int_unique
5240 ON table1.col_int = table4.col_int_unique;
5241 EXPLAIN
5242 -> Nested loop inner join
5243 -> Nested loop left join
5244 -> Nested loop inner join
5245 -> Table scan on table3, activating pushed join of 4 tables
5246 -> Index lookup on table4 using col_int_key (col_int_key=table3.pk), child of table3 in pushed join, with pushed condition: (table4.col_int_unique is not null)
5247 -> Single-row index lookup on table2 using ix1 (col_int=table3.col_int, col_int_unique=table3.col_int_unique), child of table3 in pushed join
5248 -> Index lookup on table1 using PRIMARY (col_int=table4.col_int_unique), child of table4 in pushed join
5249
5250 SELECT
5251 table1.pk, table2.pk, table3.pk, table4.pk
5252 FROM x AS table1
5253 JOIN q AS table2
5254 RIGHT OUTER JOIN q AS table3
5255 LEFT OUTER JOIN x AS table4 ON table3.pk = table4.col_int_key
5256 ON table2.col_int = table3.col_int AND
5257 table2.col_int_unique = table3.col_int_unique
5258 ON table1.col_int = table4.col_int_unique;
5259 pk pk pk pk
5260 10 3 3 26
5261 13 1 1 17
5262 15 1 1 17
5263 16 3 3 26
5264 17 1 1 10
5265 18 1 1 10
5266 19 3 3 26
5267 2 1 1 17
5268 20 3 3 26
5269 22 1 1 17
5270 23 1 1 10
5271 24 3 3 26
5272 25 1 1 17
5273 26 3 3 26
5274 27 1 1 10
5275 4 1 1 10
5276 5 1 1 10
5277 6 1 1 17
5278 7 1 1 17
5279 8 3 3 26
5280 9 3 3 26
5281 DROP TABLE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z;
5282 ##################
5283 #
5284 # Bug #33181964 Incorrect result for pushed join
5285 # w/ outer join inside an exists-subqry
5286 #
5287 ##################
5288 create table t (
5289 pk int NOT NULL,
5290 col_int int NOT NULL,
5291 primary key(col_int,pk)
5292 ) engine=ndbcluster;
5293 insert into t values
5294 (1, 0), (3, 0), (2, 0), (4, 0), (5, 0), (7, 0);
5295 create table x (
5296 col_int int
5297 ) engine=ndbcluster ;
5298 insert into x select pk from t;
5299 insert into x select pk from t;
5300 ######################################
5301 # Expect 'firstMatch' algorithm to be
5302 # used for pushed join over [t1..t3]
5303 ######################################
5304 explain format=traditional select * from x
5305 where exists (
5306 select *
5307 from t as t1
5308 left join (
5309 t as t2 join t as t3 on
5310 t2.pk = 999 # 999 -> no t2 matches
5311 )
5312 on t1.col_int = t3.col_int and
5313 t1.col_int = t2.col_int
5314 );
5315 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
5316 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 6 100.00 Parent of 3 pushed join@1
5317 1 SIMPLE t2 p0,p1 eq_ref PRIMARY PRIMARY 8 test.t1.col_int,const 1 100.00 Child of 't1' in pushed join@1
5318 1 SIMPLE t3 p0,p1 ref PRIMARY PRIMARY 4 test.t1.col_int 1 100.00 Child of 't1' in pushed join@1; FirstMatch
5319 1 SIMPLE x p0,p1 ALL NULL NULL NULL NULL 12 100.00 Using join buffer (hash join)
5320 Warnings:
5321 Note 1003 Can't push table 'x' as child, 'type' must be a 'ref' access
5322 Note 1003 /* select#1 */ select `test`.`x`.`col_int` AS `col_int` from `test`.`x` semi join (`test`.`t` `t1` left join (`test`.`t` `t2` join `test`.`t` `t3`) on(((`test`.`t1`.`col_int` = `test`.`t3`.`col_int`) and (`test`.`t1`.`col_int` = `test`.`t2`.`col_int`) and (`test`.`t2`.`pk` = 999)))) where true
5323 select * from x
5324 where exists (
5325 select *
5326 from t as t1
5327 left join (
5328 t as t2 join t as t3 on
5329 t2.pk = 999 # 999 -> no t2 matches
5330 )
5331 on t1.col_int = t3.col_int and
5332 t1.col_int = t2.col_int
5333 );
5334 col_int
5335 1
5336 1
5337 2
5338 2
5339 3
5340 3
5341 4
5342 4
5343 5
5344 5
5345 7
5346 7
5347 drop table t,x;