← Back to branch summary

~percona-toolkit-dev/percona-toolkit/mysql-5.6-test-fixes

« back to all changes in this revision

Viewing changes to t/pt-visual-explain/explain-to-tree.t

  • Committer: Daniel Nichter
  • Date: 2011-06-24 22:02:05 UTC
  • Revision ID: daniel@percona.com-20110624220205-e779cao9hcwyly1w
Add forked Maatkit tools in bin/ and their tests in t/.

Show diffs side-by-side

added added

removed removed

Lines of Context:
t/pt-visual-explain/explain-to-tree.t
 
1
#!/usr/bin/env perl
 
2
 
 
3
BEGIN {
 
4
   die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n"
 
5
      unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH};
 
6
   unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib";
 
7
};
 
8
 
 
9
use strict;
 
10
use warnings FATAL => 'all';
 
11
use English qw(-no_match_vars);
 
12
use Test::More tests => 60;
 
13
 
 
14
use MaatkitTest;
 
15
require "$trunk/bin/pt-visual-explain";
 
16
 
 
17
my $e = new ExplainTree;
 
18
my $t;
 
19
 
 
20
$t = $e->parse('');
 
21
is_deeply( $t, undef, 'No valid input' );
 
22
 
 
23
$t = $e->parse( load_file("t/pt-visual-explain/samples/fulltext.sql") );
 
24
## Please see file perltidy.ERR
 
25
is_deeply(
 
26
   $t,
 
27
   {  type     => 'Filter with WHERE',
 
28
      id       => 1,
 
29
      rowid    => 0,
 
30
      children => [
 
31
         {  type     => 'Bookmark lookup',
 
32
            children => [
 
33
               {  type          => 'Fulltext scan',
 
34
                  key_len       => undef,
 
35
                  possible_keys => 'a',
 
36
                  ref           => undef,
 
37
                  rows          => '1',
 
38
                  partitions    => undef,
 
39
                  key           => 'foo->a'
 
40
               },
 
41
               {  type          => 'Table',
 
42
                  table         => 'foo',
 
43
                  partitions    => undef,
 
44
                  possible_keys => 'a',
 
45
               },
 
46
            ],
 
47
         },
 
48
      ],
 
49
   },
 
50
   'Fulltext query',
 
51
);
 
52
 
 
53
$t = $e->parse( load_file("t/pt-visual-explain/samples/impossible_where.sql") );
 
54
is_deeply(
 
55
   $t,
 
56
   {  type  => 'IMPOSSIBLE',
 
57
      id    => 1,
 
58
      rowid => 0,
 
59
      warning => 'Impossible WHERE noticed after reading const tables',
 
60
   },
 
61
   'Impossible WHERE',
 
62
);
 
63
 
 
64
$t = $e->parse( load_file("t/pt-visual-explain/samples/impossible_having.sql") );
 
65
is_deeply(
 
66
   $t,
 
67
   {  type  => 'IMPOSSIBLE',
 
68
      id    => 1,
 
69
      rowid => 0,
 
70
      warning => 'Impossible HAVING',
 
71
   },
 
72
   'Impossible HAVING',
 
73
);
 
74
 
 
75
$t = $e->parse( load_file("t/pt-visual-explain/samples/const_row_not_found.sql") );
 
76
is_deeply(
 
77
   $t,
 
78
   {  type     => 'UNION RESULT',
 
79
      children => [
 
80
         {  type     => 'Constant table access',
 
81
            id       => 1,
 
82
            rowid    => 0,
 
83
            rows     => undef,
 
84
            warning  => 'const row not found',
 
85
            children => [
 
86
               {  type          => 'Table',
 
87
                  partitions    => undef,
 
88
                  possible_keys => undef,
 
89
                  table         => 't1',
 
90
               },
 
91
            ],
 
92
         },
 
93
         {  type     => 'SUBQUERY',
 
94
            id       => 2,
 
95
            rowid    => 1,
 
96
            children => [
 
97
               {  type     => 'Table scan',
 
98
                  id       => 2,
 
99
                  rowid    => 4,
 
100
                  rows     => undef,
 
101
                  children => [
 
102
                     {  type          => 'UNION',
 
103
                        table         => 'union(<none>,t12)',
 
104
                        possible_keys => undef,
 
105
                        partitions    => undef,
 
106
                        children      => [
 
107
                           {  type     => 'SUBQUERY',
 
108
                              children => [
 
109
                                 {  type  => 'SUBQUERY',
 
110
                                    id    => 2,
 
111
                                    rowid => 1,
 
112
                                 },
 
113
                                 {  type    => 'IMPOSSIBLE',
 
114
                                    id      => 3,
 
115
                                    rowid   => 2,
 
116
                                 },
 
117
                              ],
 
118
                           },
 
119
                           {  type     => 'Constant table access',
 
120
                              warning  => 'const row not found',
 
121
                              id       => 4,
 
122
                              rowid    => 3,
 
123
                              rows     => undef,
 
124
                              children => [
 
125
                                 {  type          => 'Table',
 
126
                                    table         => 't12',
 
127
                                    partitions    => undef,
 
128
                                    possible_keys => undef,
 
129
                                 },
 
130
                              ],
 
131
                           },
 
132
                        ],
 
133
                     },
 
134
                  ],
 
135
               },
 
136
            ],
 
137
         },
 
138
      ],
 
139
   },
 
140
   'Const row not found',
 
141
);
 
142
 
 
143
$t = $e->parse( load_file("t/pt-visual-explain/samples/dual_union_in_subquery.sql") );
 
144
is_deeply(
 
145
   $t,
 
146
   {  type     => 'UNION RESULT',
 
147
      children => [
 
148
         {  id    => 1,
 
149
            type  => 'DUAL',
 
150
            rowid => 0,
 
151
         },
 
152
         {  id       => 2,
 
153
            type     => 'DEPENDENT SUBQUERY',
 
154
            rowid    => 1,
 
155
            children => [
 
156
               {  type     => 'Table scan',
 
157
                  rows     => undef,
 
158
                  rowid    => 3,
 
159
                  id       => 2,
 
160
                  children => [
 
161
                     {  type          => 'UNION',
 
162
                        partitions    => undef,
 
163
                        possible_keys => undef,
 
164
                        table         => 'union(<none>,<none>)',
 
165
                        children      => [
 
166
                           {  id    => 2,
 
167
                              type  => 'DEPENDENT SUBQUERY',
 
168
                              rowid => 1
 
169
                           },
 
170
                           {  id    => 3,
 
171
                              type  => 'DEPENDENT UNION',
 
172
                              rowid => 2
 
173
                           }
 
174
                        ],
 
175
                     }
 
176
                  ],
 
177
               }
 
178
            ],
 
179
         }
 
180
      ],
 
181
   },
 
182
   'UNION of DUAL in a subquery',
 
183
);
 
184
 
 
185
$t = $e->parse( load_file("t/pt-visual-explain/samples/no_const_row.sql") );
 
186
is_deeply(
 
187
   $t,
 
188
   {  type     => 'Constant table access',
 
189
      id       => 1,
 
190
      rowid    => 0,
 
191
      rows     => undef,
 
192
      warning  => 'const row not found',
 
193
      children => [
 
194
         {  type          => 'Table',
 
195
            table         => 't1',
 
196
            possible_keys => undef,
 
197
            partitions    => undef,
 
198
         },
 
199
      ],
 
200
   },
 
201
   'No constant row found',
 
202
);
 
203
 
 
204
$t = $e->parse( load_file("t/pt-visual-explain/samples/unique_row_not_found.sql") );
 
205
is_deeply(
 
206
   $t,
 
207
   {  type     => 'JOIN',
 
208
      children => [
 
209
         {  type     => 'JOIN',
 
210
            children => [
 
211
               {  type     => 'Bookmark lookup',
 
212
                  rowid    => 0,
 
213
                  id       => 1,
 
214
                  children => [
 
215
                     {  key_len       => '4',
 
216
                        possible_keys => 'PRIMARY',
 
217
                        ref           => 'const',
 
218
                        type          => 'Constant index lookup',
 
219
                        rows          => '1',
 
220
                        partitions    => undef,
 
221
                        key           => 'user->PRIMARY'
 
222
                     },
 
223
                     {  possible_keys => 'PRIMARY',
 
224
                        table         => 'user',
 
225
                        type          => 'Table',
 
226
                        partitions    => undef
 
227
                     }
 
228
                  ],
 
229
               },
 
230
               {  type     => 'Bookmark lookup',
 
231
                  rowid    => 1,
 
232
                  warning  => 'unique row not found',
 
233
                  id       => 1,
 
234
                  children => [
 
235
                     {  key_len       => '2',
 
236
                        possible_keys => 'PRIMARY',
 
237
                        ref           => 'const',
 
238
                        type          => 'Constant index lookup',
 
239
                        rows          => undef,
 
240
                        partitions    => undef,
 
241
                        key           => 'avatar->PRIMARY'
 
242
                     },
 
243
                     {  possible_keys => 'PRIMARY',
 
244
                        table         => 'avatar',
 
245
                        type          => 'Table',
 
246
                        partitions    => undef
 
247
                     }
 
248
                  ],
 
249
               }
 
250
            ],
 
251
         },
 
252
         {  type     => 'Bookmark lookup',
 
253
            rowid    => 2,
 
254
            warning  => 'unique row not found',
 
255
            id       => 1,
 
256
            children => [
 
257
               {  key_len       => '4',
 
258
                  possible_keys => 'PRIMARY',
 
259
                  ref           => 'const',
 
260
                  type          => 'Constant index lookup',
 
261
                  rows          => undef,
 
262
                  partitions    => undef,
 
263
                  key           => 'customavatar->PRIMARY'
 
264
               },
 
265
               {  possible_keys => 'PRIMARY',
 
266
                  table         => 'customavatar',
 
267
                  type          => 'Table',
 
268
                  partitions    => undef
 
269
               }
 
270
            ],
 
271
         }
 
272
      ],
 
273
   },
 
274
   'Unique row not found',
 
275
);
 
276
 
 
277
$t = $e->parse( load_file("t/pt-visual-explain/samples/no_min_max_row.sql") );
 
278
is_deeply(
 
279
   $t,
 
280
   {  type    => 'IMPOSSIBLE',
 
281
      warning => 'No matching min/max row',
 
282
      id      => 1,
 
283
      rowid   => 0,
 
284
   },
 
285
   'No min/max row',
 
286
);
 
287
 
 
288
$t = $e->parse( load_file("t/pt-visual-explain/samples/simple_partition.sql") );
 
289
is_deeply(
 
290
   $t,
 
291
   {  type     => 'Filesort',
 
292
      id       => 1,
 
293
      rowid    => 0,
 
294
      children => [
 
295
         {  type     => 'Table scan',
 
296
            rows     => 10,
 
297
            children => [
 
298
               {  type          => 'Table',
 
299
                  table         => 'trb1',
 
300
                  possible_keys => undef,
 
301
                  partitions    => 'p0,p1,p2,p3',
 
302
               },
 
303
            ],
 
304
         },
 
305
      ],
 
306
   },
 
307
   'Partition',
 
308
);
 
309
 
 
310
$t = $e->parse( load_file("t/pt-visual-explain/samples/full_scan_sakila_film.sql") );
 
311
is_deeply(
 
312
   $t,
 
313
   {  type     => 'Table scan',
 
314
      id       => 1,
 
315
      rowid    => 0,
 
316
      rows     => 935,
 
317
      children => [
 
318
         {  type          => 'Table',
 
319
            table         => 'film',
 
320
            possible_keys => undef,
 
321
            partitions    => undef,
 
322
         }
 
323
      ]
 
324
   },
 
325
   'Simple scan',
 
326
);
 
327
 
 
328
$t = $e->parse( load_file("t/pt-visual-explain/samples/actor_join_film_ref.sql") );
 
329
is_deeply(
 
330
   $t,
 
331
   {  type     => 'JOIN',
 
332
      children => [
 
333
         {  type     => 'Table scan',
 
334
            rows     => 952,
 
335
            id       => 1,
 
336
            rowid    => 0,
 
337
            children => [
 
338
               {  type          => 'Table',
 
339
                  table         => 'film',
 
340
                  possible_keys => 'PRIMARY',
 
341
                  partitions    => undef,
 
342
               }
 
343
            ],
 
344
         },
 
345
         {  type     => 'Bookmark lookup',
 
346
            id       => 1,
 
347
            rowid    => 1,
 
348
            children => [
 
349
               {  type          => 'Index lookup',
 
350
                  key           => 'film_actor->idx_fk_film_id',
 
351
                  key_len       => 2,
 
352
                  'ref'         => 'sakila.film.film_id',
 
353
                  rows          => 2,
 
354
                  possible_keys => 'idx_fk_film_id',
 
355
                  partitions    => undef,
 
356
               },
 
357
               {  type          => 'Table',
 
358
                  table         => 'film_actor',
 
359
                  possible_keys => 'idx_fk_film_id',
 
360
                  partitions    => undef,
 
361
               },
 
362
            ],
 
363
         },
 
364
      ],
 
365
   },
 
366
   'Simple join',
 
367
);
 
368
 
 
369
$t = $e->parse( load_file("t/pt-visual-explain/samples/join_buffer.sql") );
 
370
is_deeply(
 
371
   $t,
 
372
   {  type     => 'JOIN',
 
373
      children => [
 
374
         {  type     => 'JOIN',
 
375
            children => [
 
376
               {  type     => 'Table scan',
 
377
                  rows     => 10,
 
378
                  id       => 1,
 
379
                  rowid    => 0,
 
380
                  children => [
 
381
                     {  type          => 'Table',
 
382
                        table         => 't1',
 
383
                        possible_keys => undef,
 
384
                        partitions    => undef,
 
385
                     }
 
386
                  ],
 
387
               },
 
388
               {  type     => 'Filter with WHERE',
 
389
                  id       => 1,
 
390
                  rowid    => 1,
 
391
                  children => [
 
392
                     {  type     => 'Bookmark lookup',
 
393
                        children => [
 
394
                           {  type          => 'Index lookup',
 
395
                              key           => 't2->key1',
 
396
                              key_len       => 5,
 
397
                              'ref'         => 'test.t1.col1',
 
398
                              rows          => 2,
 
399
                              possible_keys => 'key1',
 
400
                              partitions    => undef,
 
401
                           },
 
402
                           {  type          => 'Table',
 
403
                              table         => 't2',
 
404
                              possible_keys => 'key1',
 
405
                              partitions    => undef,
 
406
                           },
 
407
                        ],
 
408
                     },
 
409
                  ],
 
410
               },
 
411
            ],
 
412
         },
 
413
         {  type     => 'Join buffer',
 
414
            id       => 1,
 
415
            rowid    => 2,
 
416
            children => [
 
417
               {  type     => 'Filter with WHERE',
 
418
                  children => [
 
419
                     {  type     => 'Bookmark lookup',
 
420
                        children => [
 
421
                           {  type          => 'Index range scan',
 
422
                              key           => 't3->key1',
 
423
                              key_len       => 5,
 
424
                              'ref'         => undef,
 
425
                              rows          => 40,
 
426
                              possible_keys => 'key1',
 
427
                              partitions    => undef,
 
428
                           },
 
429
                           {  type          => 'Table',
 
430
                              table         => 't3',
 
431
                              possible_keys => 'key1',
 
432
                              partitions    => undef,
 
433
                           },
 
434
                        ],
 
435
                     },
 
436
                  ],
 
437
               },
 
438
            ],
 
439
         },
 
440
      ],
 
441
   },
 
442
   'Three-way join with buffer',
 
443
);
 
444
 
 
445
$t = $e->parse( load_file("t/pt-visual-explain/samples/range_check.sql") );
 
446
is_deeply(
 
447
   $t,
 
448
   {  type     => 'JOIN',
 
449
      children => [
 
450
         {  type     => 'Filter with WHERE',
 
451
            id       => 1,
 
452
            rowid    => 0,
 
453
            children => [
 
454
               {  type     => 'Bookmark lookup',
 
455
                  children => [
 
456
                     {  type          => 'Index lookup',
 
457
                        rows          => 5,
 
458
                        key           => 'v->OXROOTID',
 
459
                        key_len       => 32,
 
460
                        'ref'         => 'const',
 
461
                        possible_keys => 'OXLEFT,OXRIGHT,OXROOTID',
 
462
                        partitions    => undef,
 
463
                     },
 
464
                     {  type          => 'Table',
 
465
                        table         => 'v',
 
466
                        possible_keys => 'OXLEFT,OXRIGHT,OXROOTID',
 
467
                        partitions    => undef,
 
468
                     },
 
469
                  ],
 
470
               },
 
471
            ],
 
472
         },
 
473
         {
 
474
            type => 'Re-evaluate indexes each row',
 
475
            id            => 1,
 
476
            rowid         => 1,
 
477
            possible_keys => '3',
 
478
            children      => [
 
479
               {  type     => 'Table scan',
 
480
                  rows     => 5,
 
481
                  children => [
 
482
                     {  type          => 'Table',
 
483
                        table         => 's',
 
484
                        possible_keys => 'OXLEFT',
 
485
                        partitions    => undef,
 
486
                     },
 
487
                  ],
 
488
               },
 
489
            ],
 
490
         },
 
491
      ],
 
492
   },
 
493
   'Join that uses a range check',
 
494
);
 
495
 
 
496
is_deeply(
 
497
   $e->parse( load_file("t/pt-visual-explain/samples/range_check_3.sql") ),
 
498
   $t,
 
499
   'Key map same when decimal as when hex',
 
500
);
 
501
 
 
502
is_deeply(
 
503
   $e->parse( load_file("t/pt-visual-explain/samples/range_check_2.sql") ),
 
504
   $t,
 
505
   'Key map same as index map',
 
506
);
 
507
 
 
508
$t = $e->parse( load_file("t/pt-visual-explain/samples/not_exists.sql") );
 
509
is_deeply(
 
510
   $t,
 
511
   {  type     => 'JOIN',
 
512
      children => [
 
513
         {  type          => 'Index scan',
 
514
            rows          => 951,
 
515
            id            => 1,
 
516
            rowid         => 0,
 
517
            key           => 'film->idx_fk_language_id',
 
518
            key_len       => 1,
 
519
            'ref'         => undef,
 
520
            possible_keys => undef,
 
521
            partitions    => undef,
 
522
         },
 
523
         {  type     => 'Distinct/Not-Exists',
 
524
            id       => 1,
 
525
            rowid    => 1,
 
526
            children => [
 
527
               {  type     => 'Filter with WHERE',
 
528
                  children => [
 
529
                     {  type          => 'Index lookup',
 
530
                        key           => 'film_actor->idx_fk_film_id',
 
531
                        key_len       => 2,
 
532
                        'ref'         => 'sakila.film.film_id',
 
533
                        rows          => 2,
 
534
                        possible_keys => 'idx_fk_film_id',
 
535
                        partitions    => undef,
 
536
                     },
 
537
                  ],
 
538
               },
 
539
            ],
 
540
         },
 
541
      ],
 
542
   },
 
543
   'Join that uses Not exists',
 
544
);
 
545
 
 
546
$t = $e->parse( load_file("t/pt-visual-explain/samples/join_temporary_with_where_distinct.sql") );
 
547
is_deeply(
 
548
   $t,
 
549
   {  type     => 'JOIN',
 
550
      children => [
 
551
         {  type     => 'Table scan',
 
552
            rows     => undef,
 
553
            id       => 1,
 
554
            rowid    => 0,
 
555
            children => [
 
556
               {  type          => 'TEMPORARY',
 
557
                  table         => 'temporary(film)',
 
558
                  possible_keys => undef,
 
559
                  partitions    => undef,
 
560
                  children      => [
 
561
                     {  type     => 'Filter with WHERE',
 
562
                        children => [
 
563
                           {  type     => 'Table scan',
 
564
                              rows     => 951,
 
565
                              children => [
 
566
                                 {  type          => 'Table',
 
567
                                    table         => 'film',
 
568
                                    possible_keys => 'PRIMARY',
 
569
                                    partitions    => undef,
 
570
                                 },
 
571
                              ],
 
572
                           },
 
573
                        ],
 
574
                     },
 
575
                  ],
 
576
               },
 
577
            ],
 
578
         },
 
579
         {  type     => 'Distinct/Not-Exists',
 
580
            id       => 1,
 
581
            rowid    => 1,
 
582
            children => [
 
583
               {  type          => 'Index lookup',
 
584
                  key           => 'film_actor->idx_fk_film_id',
 
585
                  possible_keys => 'idx_fk_film_id',
 
586
                  partitions    => undef,
 
587
                  key_len       => 2,
 
588
                  'ref'         => 'sakila.film.film_id',
 
589
                  rows          => 2,
 
590
               },
 
591
            ],
 
592
         },
 
593
      ],
 
594
   },
 
595
   'Join that uses a temp table, WHERE, and Distinct',
 
596
);
 
597
 
 
598
$t = $e->parse( load_file("t/pt-visual-explain/samples/simple_join_three_tables.sql") );
 
599
is_deeply(
 
600
   $t,
 
601
   {  type     => 'JOIN',
 
602
      children => [
 
603
         {  type     => 'JOIN',
 
604
            children => [
 
605
               {  type          => 'Index scan',
 
606
                  key           => 'actor_1->PRIMARY',
 
607
                  possible_keys => 'PRIMARY',
 
608
                  partitions    => undef,
 
609
                  key_len       => 2,
 
610
                  'ref'         => undef,
 
611
                  rows          => 200,
 
612
                  id            => 1,
 
613
                  rowid         => 0,
 
614
               },
 
615
               {  type          => 'Unique index lookup',
 
616
                  key           => 'actor_2->PRIMARY',
 
617
                  possible_keys => 'PRIMARY',
 
618
                  partitions    => undef,
 
619
                  key_len       => 2,
 
620
                  'ref'         => 'sakila.actor_1.actor_id',
 
621
                  rows          => 1,
 
622
                  id            => 1,
 
623
                  rowid         => 1,
 
624
               },
 
625
            ],
 
626
         },
 
627
         {  type          => 'Unique index lookup',
 
628
            key           => 'actor_3->PRIMARY',
 
629
            possible_keys => 'PRIMARY',
 
630
            partitions    => undef,
 
631
            key_len       => 2,
 
632
            'ref'         => 'sakila.actor_1.actor_id',
 
633
            rows          => 1,
 
634
            id            => 1,
 
635
            rowid         => 2,
 
636
         },
 
637
      ],
 
638
   },
 
639
   'Simple join over three tables',
 
640
);
 
641
 
 
642
$t = $e->parse( load_file("t/pt-visual-explain/samples/film_join_actor_eq_ref.sql") );
 
643
is_deeply(
 
644
   $t,
 
645
   {  type     => 'JOIN',
 
646
      children => [
 
647
         {  type     => 'Table scan',
 
648
            rows     => 5143,
 
649
            id       => 1,
 
650
            rowid    => 0,
 
651
            children => [
 
652
               {  type          => 'Table',
 
653
                  table         => 'film_actor',
 
654
                  possible_keys => 'idx_fk_film_id',
 
655
                  partitions    => undef,
 
656
               },
 
657
            ]
 
658
         },
 
659
         {  type     => 'Bookmark lookup',
 
660
            id       => 1,
 
661
            rowid    => 1,
 
662
            children => [
 
663
               {  type          => 'Unique index lookup',
 
664
                  key           => 'film->PRIMARY',
 
665
                  key_len       => 2,
 
666
                  'ref'         => 'sakila.film_actor.film_id',
 
667
                  rows          => 1,
 
668
                  possible_keys => 'PRIMARY',
 
669
                  partitions    => undef,
 
670
               },
 
671
               {  type          => 'Table',
 
672
                  table         => 'film',
 
673
                  possible_keys => 'PRIMARY',
 
674
                  partitions    => undef,
 
675
               },
 
676
            ],
 
677
         },
 
678
      ],
 
679
   },
 
680
   'Straight join',
 
681
);
 
682
 
 
683
$t = $e->parse(
 
684
   load_file("t/pt-visual-explain/samples/film_join_actor_eq_ref.sql"),
 
685
   { clustered => 1 },
 
686
);
 
687
is_deeply(
 
688
   $t,
 
689
   {  type     => 'JOIN',
 
690
      children => [
 
691
         {  type     => 'Table scan',
 
692
            rows     => 5143,
 
693
            id       => 1,
 
694
            rowid    => 0,
 
695
            children => [
 
696
               {  type          => 'Table',
 
697
                  table         => 'film_actor',
 
698
                  possible_keys => 'idx_fk_film_id',
 
699
                  partitions    => undef,
 
700
               },
 
701
            ]
 
702
         },
 
703
         {  type          => 'Unique index lookup',
 
704
            id            => 1,
 
705
            rowid         => 1,
 
706
            key           => 'film->PRIMARY',
 
707
            possible_keys => 'PRIMARY',
 
708
            partitions    => undef,
 
709
            key_len       => 2,
 
710
            'ref'         => 'sakila.film_actor.film_id',
 
711
            rows          => 1,
 
712
         },
 
713
      ],
 
714
   },
 
715
   'Straight join assuming clustered PK',
 
716
);
 
717
 
 
718
$t = $e->parse( load_file("t/pt-visual-explain/samples/full_row_pk_lookup_sakila_film.sql") );
 
719
is_deeply(
 
720
   $t,
 
721
   {  type     => 'Bookmark lookup',
 
722
      id       => 1,
 
723
      rowid    => 0,
 
724
      children => [
 
725
         {  type          => 'Constant index lookup',
 
726
            key           => 'film->PRIMARY',
 
727
            key_len       => 2,
 
728
            'ref'         => 'const',
 
729
            rows          => 1,
 
730
            possible_keys => 'PRIMARY',
 
731
            partitions    => undef,
 
732
         },
 
733
         {  type          => 'Table',
 
734
            table         => 'film',
 
735
            possible_keys => 'PRIMARY',
 
736
            partitions    => undef,
 
737
         },
 
738
      ],
 
739
   },
 
740
   'Constant lookup',
 
741
);
 
742
 
 
743
$t = $e->parse( load_file("t/pt-visual-explain/samples/index_scan_sakila_film.sql") );
 
744
is_deeply(
 
745
   $t,
 
746
   {  type     => 'Bookmark lookup',
 
747
      id       => 1,
 
748
      rowid    => 0,
 
749
      children => [
 
750
         {  type          => 'Index scan',
 
751
            key           => 'film->idx_title',
 
752
            key_len       => 767,
 
753
            'ref'         => undef,
 
754
            rows          => 952,
 
755
            possible_keys => undef,
 
756
            partitions    => undef,
 
757
         },
 
758
         {  type          => 'Table',
 
759
            table         => 'film',
 
760
            possible_keys => undef,
 
761
            partitions    => undef,
 
762
         },
 
763
      ],
 
764
   },
 
765
   'Index scan',
 
766
);
 
767
 
 
768
$t = $e->parse( load_file("t/pt-visual-explain/samples/index_scan_sakila_film_using_where.sql") );
 
769
is_deeply(
 
770
   $t,
 
771
   {  type     => 'Filter with WHERE',
 
772
      id       => 1,
 
773
      rowid    => 0,
 
774
      children => [
 
775
         {  type     => 'Bookmark lookup',
 
776
            children => [
 
777
               {  type          => 'Index scan',
 
778
                  key           => 'film->idx_title',
 
779
                  key_len       => 767,
 
780
                  'ref'         => undef,
 
781
                  rows          => 952,
 
782
                  possible_keys => undef,
 
783
                  partitions    => undef,
 
784
               },
 
785
               {  type          => 'Table',
 
786
                  table         => 'film',
 
787
                  possible_keys => undef,
 
788
                  partitions    => undef,
 
789
               },
 
790
            ],
 
791
         },
 
792
      ],
 
793
   },
 
794
   'Index scan with WHERE clause',
 
795
);
 
796
 
 
797
$t = $e->parse( load_file("t/pt-visual-explain/samples/pk_lookup_sakila_film.sql") );
 
798
is_deeply(
 
799
   $t,
 
800
   {  type          => 'Constant index lookup',
 
801
      key           => 'film->PRIMARY',
 
802
      possible_keys => 'PRIMARY',
 
803
      partitions    => undef,
 
804
      key_len       => 2,
 
805
      'ref'         => 'const',
 
806
      rows          => 1,
 
807
      id            => 1,
 
808
      rowid         => 0,
 
809
   },
 
810
   'PK lookup with covering index',
 
811
);
 
812
 
 
813
$t = $e->parse( load_file("t/pt-visual-explain/samples/film_join_actor_const.sql") );
 
814
is_deeply(
 
815
   $t,
 
816
   {  type     => 'JOIN',
 
817
      children => [
 
818
         {  type     => 'Bookmark lookup',
 
819
            id       => 1,
 
820
            rowid    => 0,
 
821
            children => [
 
822
               {  type          => 'Constant index lookup',
 
823
                  key           => 'film->PRIMARY',
 
824
                  key_len       => 2,
 
825
                  'ref'         => 'const',
 
826
                  rows          => 1,
 
827
                  possible_keys => 'PRIMARY',
 
828
                  partitions    => undef,
 
829
               },
 
830
               {  type          => 'Table',
 
831
                  table         => 'film',
 
832
                  possible_keys => 'PRIMARY',
 
833
                  partitions    => undef,
 
834
               },
 
835
            ],
 
836
         },
 
837
         {  type     => 'Bookmark lookup',
 
838
            id       => 1,
 
839
            rowid    => 1,
 
840
            children => [
 
841
               {  type          => 'Index lookup',
 
842
                  key           => 'film_actor->idx_fk_film_id',
 
843
                  key_len       => 2,
 
844
                  'ref'         => 'const',
 
845
                  rows          => 10,
 
846
                  possible_keys => 'idx_fk_film_id',
 
847
                  partitions    => undef,
 
848
               },
 
849
               {  type          => 'Table',
 
850
                  table         => 'film_actor',
 
851
                  possible_keys => 'idx_fk_film_id',
 
852
                  partitions    => undef,
 
853
               },
 
854
            ],
 
855
         },
 
856
      ],
 
857
   },
 
858
   'Join from constant lookup in film to const ref in film_actor',
 
859
);
 
860
 
 
861
$t = $e->parse( load_file("t/pt-visual-explain/samples/film_join_actor_const_using_index.sql") );
 
862
is_deeply(
 
863
   $t,
 
864
   {  type     => 'JOIN',
 
865
      children => [
 
866
         {  type          => 'Constant index lookup',
 
867
            key           => 'film->PRIMARY',
 
868
            possible_keys => 'PRIMARY',
 
869
            partitions    => undef,
 
870
            key_len       => 2,
 
871
            'ref'         => 'const',
 
872
            rows          => 1,
 
873
            id            => 1,
 
874
            rowid         => 0,
 
875
         },
 
876
         {  type          => 'Index lookup',
 
877
            key           => 'film_actor->idx_fk_film_id',
 
878
            possible_keys => 'idx_fk_film_id',
 
879
            partitions    => undef,
 
880
            key_len       => 2,
 
881
            'ref'         => 'const',
 
882
            rows          => 10,
 
883
            id            => 1,
 
884
            rowid         => 1,
 
885
         },
 
886
      ],
 
887
   },
 
888
   'Join from const film to const ref film_actor with covering index',
 
889
);
 
890
 
 
891
$t = $e->parse( load_file("t/pt-visual-explain/samples/film_range_on_pk.sql") );
 
892
is_deeply(
 
893
   $t,
 
894
   {  type     => 'Filter with WHERE',
 
895
      id       => 1,
 
896
      rowid    => 0,
 
897
      children => [
 
898
         {  type     => 'Bookmark lookup',
 
899
            children => [
 
900
               {  type          => 'Index range scan',
 
901
                  key           => 'film->PRIMARY',
 
902
                  key_len       => 2,
 
903
                  'ref'         => undef,
 
904
                  rows          => 20,
 
905
                  possible_keys => 'PRIMARY',
 
906
                  partitions    => undef,
 
907
               },
 
908
               {  type          => 'Table',
 
909
                  table         => 'film',
 
910
                  possible_keys => 'PRIMARY',
 
911
                  partitions    => undef,
 
912
               },
 
913
            ],
 
914
         },
 
915
      ],
 
916
   },
 
917
   'Index range scan with WHERE clause',
 
918
);
 
919
 
 
920
$t = $e->parse( load_file("t/pt-visual-explain/samples/loose_index_scan.sql") );
 
921
is_deeply(
 
922
   $t,
 
923
   {  type          => 'Loose index scan',
 
924
      key           => 'film->idx_fk_language_id',
 
925
      key_len       => 1,
 
926
      'ref'         => undef,
 
927
      rows          => 2,
 
928
      id            => 1,
 
929
      rowid         => 0,
 
930
      possible_keys => undef,
 
931
      partitions    => undef,
 
932
   },
 
933
   'Loose index scan',
 
934
);
 
935
 
 
936
$t = $e->parse(
 
937
   load_file("t/pt-visual-explain/samples/film_ref_or_null_on_original_language_id.sql") );
 
938
is_deeply(
 
939
   $t,
 
940
   {  type     => 'Filter with WHERE',
 
941
      id       => 1,
 
942
      rowid    => 0,
 
943
      children => [
 
944
         {  type     => 'Bookmark lookup',
 
945
            children => [
 
946
               {  type          => 'Index lookup with extra null lookup',
 
947
                  key           => 'film->idx_fk_original_language_id',
 
948
                  key_len       => 2,
 
949
                  'ref'         => 'const',
 
950
                  rows          => 512,
 
951
                  possible_keys => 'idx_fk_original_language_id',
 
952
                  partitions    => undef,
 
953
               },
 
954
               {  type          => 'Table',
 
955
                  table         => 'film',
 
956
                  possible_keys => 'idx_fk_original_language_id',
 
957
                  partitions    => undef,
 
958
               },
 
959
            ],
 
960
         },
 
961
      ],
 
962
   },
 
963
   'Index ref_or_null scan',
 
964
);
 
965
 
 
966
$t = $e->parse( load_file("t/pt-visual-explain/samples/rental_index_merge_intersect.sql") );
 
967
is_deeply(
 
968
   $t,
 
969
   {  type     => 'Filter with WHERE',
 
970
      id       => 1,
 
971
      rowid    => 0,
 
972
      children => [
 
973
         {  type     => 'Bookmark lookup',
 
974
            children => [
 
975
               {  type     => 'Index merge',
 
976
                  method   => 'intersect',
 
977
                  rows     => 1,
 
978
                  children => [
 
979
                     {  type => 'Index range scan',
 
980
                        key  => 'rental->idx_fk_inventory_id',
 
981
                        possible_keys =>
 
982
                           'idx_fk_inventory_id,idx_fk_customer_id',
 
983
                        partitions    => undef,
 
984
                        key_len => 3,
 
985
                        'ref'   => undef,
 
986
                        rows    => 1,
 
987
                     },
 
988
                     {  type => 'Index range scan',
 
989
                        key  => 'rental->idx_fk_customer_id',
 
990
                        possible_keys =>
 
991
                           'idx_fk_inventory_id,idx_fk_customer_id',
 
992
                        partitions    => undef,
 
993
                        key_len => 2,
 
994
                        'ref'   => undef,
 
995
                        rows    => 1,
 
996
                     },
 
997
                  ],
 
998
               },
 
999
               {  type          => 'Table',
 
1000
                  table         => 'rental',
 
1001
                  possible_keys => 'idx_fk_inventory_id,idx_fk_customer_id',
 
1002
                  partitions    => undef,
 
1003
               },
 
1004
            ],
 
1005
         },
 
1006
      ],
 
1007
   },
 
1008
   'Index intersection merge',
 
1009
);
 
1010
 
 
1011
$t = $e->parse( load_file("t/pt-visual-explain/samples/index_merge_three_keys.sql") );
 
1012
is_deeply(
 
1013
   $t,
 
1014
   {  type     => 'Filter with WHERE',
 
1015
      id       => 1,
 
1016
      rowid    => 0,
 
1017
      children => [
 
1018
         {  type     => 'Index merge',
 
1019
            method   => 'intersect',
 
1020
            rows     => 2,
 
1021
            children => [
 
1022
               {  type          => 'Index range scan',
 
1023
                  key           => 't1->key1',
 
1024
                  possible_keys => 'key1,key2,key3',
 
1025
                  partitions    => undef,
 
1026
                  key_len       => 5,
 
1027
                  'ref'         => undef,
 
1028
                  rows          => 2,
 
1029
               },
 
1030
               {  type          => 'Index range scan',
 
1031
                  key           => 't1->key2',
 
1032
                  possible_keys => 'key1,key2,key3',
 
1033
                  partitions    => undef,
 
1034
                  key_len       => 5,
 
1035
                  'ref'         => undef,
 
1036
                  rows          => 2,
 
1037
               },
 
1038
               {  type          => 'Index range scan',
 
1039
                  key           => 't1->key3',
 
1040
                  possible_keys => 'key1,key2,key3',
 
1041
                  partitions    => undef,
 
1042
                  key_len       => 5,
 
1043
                  'ref'         => undef,
 
1044
                  rows          => 2,
 
1045
               },
 
1046
            ],
 
1047
         },
 
1048
      ],
 
1049
   },
 
1050
   'Index intersection merge with three keys and covering index',
 
1051
);
 
1052
 
 
1053
$t = $e->parse( load_file("t/pt-visual-explain/samples/index_merge_union_intersect.sql") );
 
1054
is_deeply(
 
1055
   $t,
 
1056
   {  type     => 'Filter with WHERE',
 
1057
      id       => 1,
 
1058
      rowid    => 0,
 
1059
      children => [
 
1060
         {  type     => 'Bookmark lookup',
 
1061
            children => [
 
1062
               {  type     => 'Index merge',
 
1063
                  method   => 'union',
 
1064
                  rows     => 154,
 
1065
                  children => [
 
1066
                     {  type     => 'Index merge',
 
1067
                        method   => 'intersect',
 
1068
                        rows     => 154,
 
1069
                        children => [
 
1070
                           {  type          => 'Index range scan',
 
1071
                              key           => 't1->key1',
 
1072
                              possible_keys => 'key1,key2,key3,key4',
 
1073
                              partitions    => undef,
 
1074
                              key_len       => 5,
 
1075
                              'ref'         => undef,
 
1076
                              rows          => 154,
 
1077
                           },
 
1078
                           {  type          => 'Index range scan',
 
1079
                              key           => 't1->key2',
 
1080
                              possible_keys => 'key1,key2,key3,key4',
 
1081
                              partitions    => undef,
 
1082
                              key_len       => 5,
 
1083
                              'ref'         => undef,
 
1084
                              rows          => 154,
 
1085
                           },
 
1086
                        ],
 
1087
                     },
 
1088
                     {  type     => 'Index merge',
 
1089
                        method   => 'intersect',
 
1090
                        rows     => 154,
 
1091
                        children => [
 
1092
                           {  type          => 'Index range scan',
 
1093
                              key           => 't1->key3',
 
1094
                              possible_keys => 'key1,key2,key3,key4',
 
1095
                              partitions    => undef,
 
1096
                              key_len       => 5,
 
1097
                              'ref'         => undef,
 
1098
                              rows          => 154,
 
1099
                           },
 
1100
                           {  type          => 'Index range scan',
 
1101
                              key           => 't1->key4',
 
1102
                              possible_keys => 'key1,key2,key3,key4',
 
1103
                              partitions    => undef,
 
1104
                              key_len       => 5,
 
1105
                              'ref'         => undef,
 
1106
                              rows          => 154,
 
1107
                           },
 
1108
                        ],
 
1109
                     },
 
1110
                  ],
 
1111
               },
 
1112
               {  type          => 'Table',
 
1113
                  table         => 't1',
 
1114
                  possible_keys => 'key1,key2,key3,key4',
 
1115
                  partitions    => undef,
 
1116
               },
 
1117
            ],
 
1118
         },
 
1119
      ],
 
1120
   },
 
1121
   'Index merge union-intersection',
 
1122
);
 
1123
 
 
1124
$t = $e->parse( load_file("t/pt-visual-explain/samples/index_merge_sort_union.sql") );
 
1125
is_deeply(
 
1126
   $t,
 
1127
   {  type     => 'Filter with WHERE',
 
1128
      id       => 1,
 
1129
      rowid    => 0,
 
1130
      children => [
 
1131
         {  type     => 'Bookmark lookup',
 
1132
            children => [
 
1133
               {  type     => 'Index merge',
 
1134
                  method   => 'sort_union',
 
1135
                  rows     => 45,
 
1136
                  children => [
 
1137
                     {  type          => 'Index range scan',
 
1138
                        key           => 't0->i1',
 
1139
                        possible_keys => 'i1,i2',
 
1140
                        partitions    => undef,
 
1141
                        key_len       => 4,
 
1142
                        'ref'         => undef,
 
1143
                        rows          => 45,
 
1144
                     },
 
1145
                     {  type          => 'Index range scan',
 
1146
                        key           => 't0->i2',
 
1147
                        possible_keys => 'i1,i2',
 
1148
                        partitions    => undef,
 
1149
                        key_len       => 4,
 
1150
                        'ref'         => undef,
 
1151
                        rows          => 45,
 
1152
                     },
 
1153
                  ],
 
1154
               },
 
1155
               {  type          => 'Table',
 
1156
                  table         => 't0',
 
1157
                  possible_keys => 'i1,i2',
 
1158
                  partitions    => undef,
 
1159
               },
 
1160
            ],
 
1161
         },
 
1162
      ],
 
1163
   },
 
1164
   'Index merge sort_union',
 
1165
);
 
1166
 
 
1167
$t = $e->parse( load_file("t/pt-visual-explain/samples/optimized_away.sql") );
 
1168
is_deeply(
 
1169
   $t,
 
1170
   {  type  => 'CONSTANT',
 
1171
      id    => 1,
 
1172
      rowid => 0,
 
1173
   },
 
1174
   'No tables used - constant',
 
1175
);
 
1176
 
 
1177
$t = $e->parse( load_file("t/pt-visual-explain/samples/no_from.sql") );
 
1178
is_deeply(
 
1179
   $t,
 
1180
   {  type  => 'DUAL',
 
1181
      id    => 1,
 
1182
      rowid => 0,
 
1183
   },
 
1184
   'No tables used - no FROM',
 
1185
);
 
1186
 
 
1187
$t = $e->parse( load_file("t/pt-visual-explain/samples/filesort.sql") );
 
1188
is_deeply(
 
1189
   $t,
 
1190
   {  type     => 'Filesort',
 
1191
      id       => 1,
 
1192
      rowid    => 0,
 
1193
      children => [
 
1194
         {  type     => 'Table scan',
 
1195
            rows     => 951,
 
1196
            children => [
 
1197
               {  type          => 'Table',
 
1198
                  table         => 'film',
 
1199
                  possible_keys => undef,
 
1200
                  partitions    => undef,
 
1201
               },
 
1202
            ],
 
1203
         },
 
1204
      ],
 
1205
   },
 
1206
   'Filesort',
 
1207
);
 
1208
 
 
1209
$t = $e->parse( load_file("t/pt-visual-explain/samples/temporary_filesort.sql") );
 
1210
is_deeply(
 
1211
   $t,
 
1212
   {  type     => 'Filesort',
 
1213
      children => [
 
1214
         {  type          => 'TEMPORARY',
 
1215
            table         => 'temporary(film)',
 
1216
            possible_keys => undef,
 
1217
            partitions    => undef,
 
1218
            children      => [
 
1219
               {  type          => 'Index scan',
 
1220
                  key           => 'film->PRIMARY',
 
1221
                  possible_keys => undef,
 
1222
                  partitions    => undef,
 
1223
                  key_len       => 2,
 
1224
                  'ref'         => undef,
 
1225
                  rows          => 951,
 
1226
                  rowid         => 0,
 
1227
                  id            => 1,
 
1228
               },
 
1229
            ],
 
1230
         },
 
1231
      ],
 
1232
   },
 
1233
   'Filesort with temporary',
 
1234
);
 
1235
 
 
1236
$t = $e->parse( load_file("t/pt-visual-explain/samples/filesort_on_subsequent_tbl.sql") );
 
1237
is_deeply(
 
1238
   $t,
 
1239
   {  type     => 'JOIN',
 
1240
      children => [
 
1241
         {  type     => 'JOIN',
 
1242
            children => [
 
1243
               {  type     => 'Constant table access',
 
1244
                  rows     => '1',
 
1245
                  rowid    => 0,
 
1246
                  id       => 1,
 
1247
                  children => [
 
1248
                     {  type          => 'Table',
 
1249
                        table         => 'const_tbl',
 
1250
                        possible_keys => undef,
 
1251
                        partitions    => undef,
 
1252
                     },
 
1253
                  ],
 
1254
               },
 
1255
               {  type     => 'Filesort',
 
1256
                  rowid    => 1,
 
1257
                  id       => 1,
 
1258
                  children => [
 
1259
                     {  type     => 'Filter with WHERE',
 
1260
                        children => [
 
1261
                           {  type     => 'Table scan',
 
1262
                              rows     => '10',
 
1263
                              children => [
 
1264
                                 {  type          => 'Table',
 
1265
                                    table         => 't1',
 
1266
                                    partitions    => undef,
 
1267
                                    possible_keys => undef,
 
1268
                                 },
 
1269
                              ],
 
1270
                           },
 
1271
                        ],
 
1272
                     },
 
1273
                  ],
 
1274
               },
 
1275
            ],
 
1276
         },
 
1277
         {  type     => 'Filter with WHERE',
 
1278
            rowid    => 2,
 
1279
            id       => 1,
 
1280
            children => [
 
1281
               {  type     => 'Bookmark lookup',
 
1282
                  children => [
 
1283
                     {  type          => 'Index lookup',
 
1284
                        key           => 't2->a',
 
1285
                        key_len       => '5',
 
1286
                        possible_keys => 'a',
 
1287
                        ref           => 'test4.t1.a',
 
1288
                        rows          => '11',
 
1289
                        partitions    => undef,
 
1290
                     },
 
1291
                     {  type          => 'Table',
 
1292
                        table         => 't2',
 
1293
                        possible_keys => 'a',
 
1294
                        partitions    => undef,
 
1295
                     },
 
1296
                  ],
 
1297
               },
 
1298
            ],
 
1299
         },
 
1300
      ],
 
1301
   },
 
1302
   'Filesort on first non-constant table',
 
1303
);
 
1304
 
 
1305
$t = $e->parse( load_file("t/pt-visual-explain/samples/three_table_join_with_temp_filesort.sql") );
 
1306
is_deeply(
 
1307
   $t,
 
1308
   {  type     => 'Filesort',
 
1309
      children => [
 
1310
         {  type          => 'TEMPORARY',
 
1311
            partitions    => undef,
 
1312
            possible_keys => undef,
 
1313
            table         => 'temporary(actor,film_actor,film)',
 
1314
            children      => [
 
1315
               {  type     => 'JOIN',
 
1316
                  children => [
 
1317
                     {  type     => 'JOIN',
 
1318
                        children => [
 
1319
                           {  type          => 'Index scan',
 
1320
                              key           => 'actor->PRIMARY',
 
1321
                              possible_keys => 'PRIMARY',
 
1322
                              key_len       => '2',
 
1323
                              ref           => undef,
 
1324
                              rows          => '200',
 
1325
                              partitions    => undef,
 
1326
                              rowid         => 0,
 
1327
                              id            => 1,
 
1328
                           },
 
1329
                           {  type          => 'Index lookup',
 
1330
                              key           => 'film_actor->PRIMARY',
 
1331
                              key_len       => '2',
 
1332
                              ref           => 'sakila.actor.actor_id',
 
1333
                              rows          => '13',
 
1334
                              partitions    => undef,
 
1335
                              possible_keys => 'PRIMARY,idx_fk_film_id',
 
1336
                              rowid         => 1,
 
1337
                              id            => 1
 
1338
                           }
 
1339
                        ],
 
1340
                     },
 
1341
                     {  type          => 'Unique index lookup',
 
1342
                        key           => 'film->PRIMARY',
 
1343
                        possible_keys => 'PRIMARY',
 
1344
                        key_len       => '2',
 
1345
                        ref           => 'sakila.film_actor.film_id',
 
1346
                        rows          => '1',
 
1347
                        partitions    => undef,
 
1348
                        rowid         => 2,
 
1349
                        id            => 1,
 
1350
                     }
 
1351
                  ],
 
1352
               }
 
1353
            ],
 
1354
         }
 
1355
      ],
 
1356
   },
 
1357
   'Filesort with temporary',
 
1358
);
 
1359
 
 
1360
eval {
 
1361
   $t = $e->parse( load_file("t/pt-visual-explain/samples/too_many_unions.sql") );
 
1362
};
 
1363
like($EVAL_ERROR, qr/UNION has too many tables/, 'Too many unions');
 
1364
 
 
1365
 
 
1366
$t = $e->parse( load_file("t/pt-visual-explain/samples/simple_union.sql") );
 
1367
is_deeply(
 
1368
   $t,
 
1369
   {  type     => 'Table scan',
 
1370
      rows     => undef,
 
1371
      id       => 1,
 
1372
      rowid    => 2,
 
1373
      children => [
 
1374
         {  type          => 'UNION',
 
1375
            possible_keys => undef,
 
1376
            partitions    => undef,
 
1377
            table         => 'union(actor_1,actor_2)',
 
1378
            children      => [
 
1379
               {  type          => 'Index scan',
 
1380
                  key           => 'actor_1->PRIMARY',
 
1381
                  possible_keys => undef,
 
1382
                  partitions    => undef,
 
1383
                  key_len       => 2,
 
1384
                  'ref'         => undef,
 
1385
                  rows          => 200,
 
1386
                  id            => 1,
 
1387
                  rowid         => 0,
 
1388
               },
 
1389
               {  type          => 'Index scan',
 
1390
                  key           => 'actor_2->PRIMARY',
 
1391
                  possible_keys => undef,
 
1392
                  partitions    => undef,
 
1393
                  key_len       => 2,
 
1394
                  'ref'         => undef,
 
1395
                  rows          => 200,
 
1396
                  id            => 2,
 
1397
                  rowid         => 1,
 
1398
               },
 
1399
            ],
 
1400
         },
 
1401
      ],
 
1402
   },
 
1403
   'Simple union',
 
1404
);
 
1405
 
 
1406
$t = $e->parse( load_file("t/pt-visual-explain/samples/derived_over_bookmark_lookup.sql") );
 
1407
is_deeply(
 
1408
   $t,
 
1409
   {  type     => 'Table scan',
 
1410
      rows     => 10,
 
1411
      id       => 1,
 
1412
      rowid    => 0,
 
1413
      children => [
 
1414
         {  type          => 'DERIVED',
 
1415
            table         => 'derived(film_actor)',
 
1416
            possible_keys => undef,
 
1417
            partitions    => undef,
 
1418
            children      => [
 
1419
               {  type          => 'Bookmark lookup',
 
1420
                  id            => 2,
 
1421
                  rowid         => 1,
 
1422
                  children      => [
 
1423
                     {  type          => 'Index lookup',
 
1424
                        key           => 'film_actor->idx_fk_film_id',
 
1425
                        possible_keys => 'idx_fk_film_id',
 
1426
                        partitions    => undef,
 
1427
                        key_len       => 2,
 
1428
                        'ref'         => undef,
 
1429
                        rows          => 10,
 
1430
                     },
 
1431
                     {  type          => 'Table',
 
1432
                        table         => 'film_actor',
 
1433
                        possible_keys => 'idx_fk_film_id',
 
1434
                        partitions    => undef,
 
1435
                     },
 
1436
                  ],
 
1437
               },
 
1438
            ],
 
1439
         },
 
1440
      ],
 
1441
   },
 
1442
   'Derived table over a bookmark lookup',
 
1443
);
 
1444
 
 
1445
$t = $e->parse( load_file("t/pt-visual-explain/samples/simple_derived.sql") );
 
1446
is_deeply(
 
1447
   $t,
 
1448
   {  type     => 'Table scan',
 
1449
      rows     => 200,
 
1450
      id       => 1,
 
1451
      rowid    => 0,
 
1452
      children => [
 
1453
         {  type          => 'DERIVED',
 
1454
            table         => 'derived(actor)',
 
1455
            possible_keys => undef,
 
1456
            partitions    => undef,
 
1457
            children      => [
 
1458
               {  type          => 'Index scan',
 
1459
                  key           => 'actor->PRIMARY',
 
1460
                  possible_keys => undef,
 
1461
                  partitions    => undef,
 
1462
                  key_len       => 2,
 
1463
                  'ref'         => undef,
 
1464
                  rows          => 200,
 
1465
                  id            => 2,
 
1466
                  rowid         => 1,
 
1467
               },
 
1468
            ],
 
1469
         },
 
1470
      ],
 
1471
   },
 
1472
   'Simple derived table',
 
1473
);
 
1474
 
 
1475
$t = $e->parse( load_file("t/pt-visual-explain/samples/derived_over_join.sql") );
 
1476
is_deeply(
 
1477
   $t,
 
1478
   {  type     => 'Table scan',
 
1479
      rows     => 40000,
 
1480
      id       => 1,
 
1481
      rowid    => 0,
 
1482
      children => [
 
1483
         {  type          => 'DERIVED',
 
1484
            table         => 'derived(actor_1,actor_2)',
 
1485
            possible_keys => undef,
 
1486
            partitions    => undef,
 
1487
            children      => [
 
1488
               {  type     => 'JOIN',
 
1489
                  children => [
 
1490
                     {  type          => 'Index scan',
 
1491
                        key           => 'actor_1->PRIMARY',
 
1492
                        possible_keys => undef,
 
1493
                        partitions    => undef,
 
1494
                        key_len       => 2,
 
1495
                        'ref'         => undef,
 
1496
                        rows          => 200,
 
1497
                        id            => 2,
 
1498
                        rowid         => 1,
 
1499
                     },
 
1500
                     {  type          => 'Index scan',
 
1501
                        key           => 'actor_2->PRIMARY',
 
1502
                        possible_keys => undef,
 
1503
                        partitions    => undef,
 
1504
                        key_len       => 2,
 
1505
                        'ref'         => undef,
 
1506
                        rows          => 200,
 
1507
                        id            => 2,
 
1508
                        rowid         => 2,
 
1509
                     },
 
1510
                  ],
 
1511
               },
 
1512
            ],
 
1513
         },
 
1514
      ],
 
1515
   },
 
1516
   'Simple derived table over a simple join',
 
1517
);
 
1518
 
 
1519
$t = $e->parse( load_file("t/pt-visual-explain/samples/join_two_derived_tables_of_joins.sql") );
 
1520
is_deeply(
 
1521
   $t,
 
1522
   {  type     => 'JOIN',
 
1523
      children => [
 
1524
         {  type     => 'Table scan',
 
1525
            rows     => 40000,
 
1526
            id       => 1,
 
1527
            rowid    => 0,
 
1528
            children => [
 
1529
               {  type          => 'DERIVED',
 
1530
                  table         => 'derived(actor_1,actor_2)',
 
1531
                  possible_keys => undef,
 
1532
                  partitions    => undef,
 
1533
                  children      => [
 
1534
                     {  type     => 'JOIN',
 
1535
                        children => [
 
1536
                           {  type          => 'Index scan',
 
1537
                              key           => 'actor_1->PRIMARY',
 
1538
                              possible_keys => undef,
 
1539
                              partitions    => undef,
 
1540
                              key_len       => 2,
 
1541
                              'ref'         => undef,
 
1542
                              rows          => 200,
 
1543
                              id            => 2,
 
1544
                              rowid         => 4,
 
1545
                           },
 
1546
                           {  type          => 'Index scan',
 
1547
                              key           => 'actor_2->PRIMARY',
 
1548
                              possible_keys => undef,
 
1549
                              partitions    => undef,
 
1550
                              key_len       => 2,
 
1551
                              'ref'         => undef,
 
1552
                              rows          => 200,
 
1553
                              id            => 2,
 
1554
                              rowid         => 5,
 
1555
                           },
 
1556
                        ],
 
1557
                     },
 
1558
                  ],
 
1559
               },
 
1560
            ],
 
1561
         },
 
1562
         {  type     => 'Filter with WHERE',
 
1563
            id       => 1,
 
1564
            rowid    => 1,
 
1565
            children => [
 
1566
               {  type     => 'Table scan',
 
1567
                  rows     => 40000,
 
1568
                  children => [
 
1569
                     {  type          => 'DERIVED',
 
1570
                        table         => 'derived(actor_3,actor_4)',
 
1571
                        possible_keys => undef,
 
1572
                        partitions    => undef,
 
1573
                        children      => [
 
1574
                           {  type     => 'JOIN',
 
1575
                              children => [
 
1576
                                 {  type          => 'Index scan',
 
1577
                                    key           => 'actor_3->PRIMARY',
 
1578
                                    possible_keys => undef,
 
1579
                                    partitions    => undef,
 
1580
                                    key_len       => 2,
 
1581
                                    'ref'         => undef,
 
1582
                                    rows          => 200,
 
1583
                                    id            => 3,
 
1584
                                    rowid         => 2,
 
1585
                                 },
 
1586
                                 {  type          => 'Index scan',
 
1587
                                    key           => 'actor_4->PRIMARY',
 
1588
                                    possible_keys => undef,
 
1589
                                    partitions    => undef,
 
1590
                                    key_len       => 2,
 
1591
                                    'ref'         => undef,
 
1592
                                    rows          => 200,
 
1593
                                    id            => 3,
 
1594
                                    rowid         => 3,
 
1595
                                 },
 
1596
                              ],
 
1597
                           },
 
1598
                        ],
 
1599
                     },
 
1600
                  ],
 
1601
               },
 
1602
            ],
 
1603
         },
 
1604
      ],
 
1605
   },
 
1606
   'Join two derived tables which each contain a join',
 
1607
);
 
1608
 
 
1609
$t = $e->parse( load_file("t/pt-visual-explain/samples/union_of_derived_tables.sql") );
 
1610
is_deeply(
 
1611
   $t,
 
1612
   {  type     => 'Table scan',
 
1613
      rows     => undef,
 
1614
      id       => 1,
 
1615
      rowid    => 4,
 
1616
      children => [
 
1617
         {  type          => 'UNION',
 
1618
            table         => 'union(derived(actor),derived(film))',
 
1619
            possible_keys => undef,
 
1620
            partitions    => undef,
 
1621
            children      => [
 
1622
               {  type     => 'Table scan',
 
1623
                  id       => 1,
 
1624
                  rowid    => 0,
 
1625
                  rows     => 200,
 
1626
                  children => [
 
1627
                     {  type          => 'DERIVED',
 
1628
                        table         => 'derived(actor)',
 
1629
                        possible_keys => undef,
 
1630
                        partitions    => undef,
 
1631
                        children      => [
 
1632
                           {  type          => 'Index scan',
 
1633
                              key           => 'actor->PRIMARY',
 
1634
                              possible_keys => undef,
 
1635
                              partitions    => undef,
 
1636
                              key_len       => 2,
 
1637
                              'ref'         => undef,
 
1638
                              rows          => 200,
 
1639
                              id            => 2,
 
1640
                              rowid         => 1,
 
1641
                           },
 
1642
                        ],
 
1643
                     },
 
1644
                  ],
 
1645
               },
 
1646
               {  type     => 'Table scan',
 
1647
                  id       => 3,
 
1648
                  rowid    => 2,
 
1649
                  rows     => 1000,
 
1650
                  children => [
 
1651
                     {  type          => 'DERIVED',
 
1652
                        table         => 'derived(film)',
 
1653
                        possible_keys => undef,
 
1654
                        partitions    => undef,
 
1655
                        children      => [
 
1656
                           {  type          => 'Index scan',
 
1657
                              key           => 'film->idx_fk_language_id',
 
1658
                              possible_keys => undef,
 
1659
                              partitions    => undef,
 
1660
                              key_len       => 1,
 
1661
                              'ref'         => undef,
 
1662
                              rows          => 951,
 
1663
                              id            => 4,
 
1664
                              rowid         => 3,
 
1665
                           },
 
1666
                        ],
 
1667
                     },
 
1668
                  ],
 
1669
               },
 
1670
            ],
 
1671
         },
 
1672
      ],
 
1673
   },
 
1674
   'Union over two derived tables',
 
1675
);
 
1676
 
 
1677
$t = $e->parse( load_file("t/pt-visual-explain/samples/join_two_derived_tables_of_unions.sql") );
 
1678
is_deeply(
 
1679
   $t,
 
1680
   {  type     => 'JOIN',
 
1681
      children => [
 
1682
         {  type     => 'Constant table access',
 
1683
            id       => 1,
 
1684
            rowid    => 0,
 
1685
            rows     => 1,
 
1686
            children => [
 
1687
               {  type          => 'DERIVED',
 
1688
                  table         => 'derived(union(actor_1,actor_2))',
 
1689
                  possible_keys => undef,
 
1690
                  partitions    => undef,
 
1691
                  children      => [
 
1692
                     {  type     => 'Table scan',
 
1693
                        id       => 2,
 
1694
                        rowid    => 7,
 
1695
                        rows     => undef,
 
1696
                        children => [
 
1697
                           {  type          => 'UNION',
 
1698
                              possible_keys => undef,
 
1699
                              partitions    => undef,
 
1700
                              table         => 'union(actor_1,actor_2)',
 
1701
                              children      => [
 
1702
                                 {  type          => 'Index scan',
 
1703
                                    key           => 'actor_1->PRIMARY',
 
1704
                                    possible_keys => undef,
 
1705
                                    partitions    => undef,
 
1706
                                    key_len       => 2,
 
1707
                                    'ref'         => undef,
 
1708
                                    rows          => 200,
 
1709
                                    id            => 2,
 
1710
                                    rowid         => 5,
 
1711
                                 },
 
1712
                                 {  type          => 'Index scan',
 
1713
                                    key           => 'actor_2->PRIMARY',
 
1714
                                    possible_keys => undef,
 
1715
                                    partitions    => undef,
 
1716
                                    key_len       => 2,
 
1717
                                    'ref'         => undef,
 
1718
                                    rows          => 200,
 
1719
                                    id            => 3,
 
1720
                                    rowid         => 6,
 
1721
                                 },
 
1722
                              ],
 
1723
                           },
 
1724
                        ],
 
1725
                     }
 
1726
                  ],
 
1727
               },
 
1728
            ],
 
1729
         },
 
1730
         {  type     => 'Constant table access',
 
1731
            id       => 1,
 
1732
            rowid    => 1,
 
1733
            rows     => 1,
 
1734
            children => [
 
1735
               {  type          => 'DERIVED',
 
1736
                  table         => 'derived(union(actor_3,actor_4))',
 
1737
                  possible_keys => undef,
 
1738
                  partitions    => undef,
 
1739
                  children      => [
 
1740
                     {  type     => 'Table scan',
 
1741
                        id       => 4,
 
1742
                        rowid    => 4,
 
1743
                        rows     => undef,
 
1744
                        children => [
 
1745
                           {  type          => 'UNION',
 
1746
                              possible_keys => undef,
 
1747
                              partitions    => undef,
 
1748
                              table         => 'union(actor_3,actor_4)',
 
1749
                              children      => [
 
1750
                                 {  type          => 'Index scan',
 
1751
                                    key           => 'actor_3->PRIMARY',
 
1752
                                    possible_keys => undef,
 
1753
                                    partitions    => undef,
 
1754
                                    key_len       => 2,
 
1755
                                    'ref'         => undef,
 
1756
                                    rows          => 200,
 
1757
                                    id            => 4,
 
1758
                                    rowid         => 2,
 
1759
                                 },
 
1760
                                 {  type          => 'Index scan',
 
1761
                                    key           => 'actor_4->PRIMARY',
 
1762
                                    possible_keys => undef,
 
1763
                                    partitions    => undef,
 
1764
                                    key_len       => 2,
 
1765
                                    'ref'         => undef,
 
1766
                                    rows          => 200,
 
1767
                                    id            => 5,
 
1768
                                    rowid         => 3,
 
1769
                                 },
 
1770
                              ],
 
1771
                           },
 
1772
                        ],
 
1773
                     }
 
1774
                  ],
 
1775
               },
 
1776
            ],
 
1777
         },
 
1778
      ],
 
1779
   },
 
1780
   'Join over two derived tables of unions',
 
1781
);
 
1782
 
 
1783
$t = $e->parse( load_file("t/pt-visual-explain/samples/union_of_derived_unions.sql") );
 
1784
is_deeply(
 
1785
   $t,
 
1786
   {  type     => 'Table scan',
 
1787
      rows     => undef,
 
1788
      id       => 1,
 
1789
      rowid    => 8,
 
1790
      children => [
 
1791
         {  type => 'UNION',
 
1792
            table =>
 
1793
               'union(derived(union(actor_1,actor_2)),derived(union(actor_3,actor_4)))',
 
1794
            possible_keys => undef,
 
1795
            partitions    => undef,
 
1796
            children      => [
 
1797
               {  type     => 'Constant table access',
 
1798
                  id       => 1,
 
1799
                  rowid    => 0,
 
1800
                  rows     => 1,
 
1801
                  children => [
 
1802
                     {  type          => 'DERIVED',
 
1803
                        table         => 'derived(union(actor_1,actor_2))',
 
1804
                        possible_keys => undef,
 
1805
                        partitions    => undef,
 
1806
                        children      => [
 
1807
                           {  type     => 'Table scan',
 
1808
                              id       => 2,
 
1809
                              rowid    => 3,
 
1810
                              rows     => undef,
 
1811
                              children => [
 
1812
                                 {  type          => 'UNION',
 
1813
                                    possible_keys => undef,
 
1814
                                    partitions    => undef,
 
1815
                                    table         => 'union(actor_1,actor_2)',
 
1816
                                    children      => [
 
1817
                                       {  type          => 'Index scan',
 
1818
                                          key           => 'actor_1->PRIMARY',
 
1819
                                          possible_keys => undef,
 
1820
                                          partitions    => undef,
 
1821
                                          key_len       => 2,
 
1822
                                          'ref'         => undef,
 
1823
                                          rows          => 200,
 
1824
                                          id            => 2,
 
1825
                                          rowid         => 1,
 
1826
                                       },
 
1827
                                       {  type          => 'Index scan',
 
1828
                                          key           => 'actor_2->PRIMARY',
 
1829
                                          possible_keys => undef,
 
1830
                                          partitions    => undef,
 
1831
                                          key_len       => 2,
 
1832
                                          'ref'         => undef,
 
1833
                                          rows          => 200,
 
1834
                                          id            => 3,
 
1835
                                          rowid         => 2,
 
1836
                                       },
 
1837
                                    ],
 
1838
                                 },
 
1839
                              ],
 
1840
                           }
 
1841
                        ],
 
1842
                     },
 
1843
                  ],
 
1844
               },
 
1845
               {  type     => 'Table scan',
 
1846
                  id       => 4,
 
1847
                  rowid    => 4,
 
1848
                  rows     => 400,
 
1849
                  children => [
 
1850
                     {  type          => 'DERIVED',
 
1851
                        table         => 'derived(union(actor_3,actor_4))',
 
1852
                        possible_keys => undef,
 
1853
                        partitions    => undef,
 
1854
                        children      => [
 
1855
                           {  type     => 'Table scan',
 
1856
                              rows     => undef,
 
1857
                              id       => 5,
 
1858
                              rowid    => 7,
 
1859
                              children => [
 
1860
                                 {  type          => 'UNION',
 
1861
                                    possible_keys => undef,
 
1862
                                    partitions    => undef,
 
1863
                                    table         => 'union(actor_3,actor_4)',
 
1864
                                    children      => [
 
1865
                                       {  type          => 'Index scan',
 
1866
                                          key           => 'actor_3->PRIMARY',
 
1867
                                          possible_keys => undef,
 
1868
                                          partitions    => undef,
 
1869
                                          key_len       => 2,
 
1870
                                          'ref'         => undef,
 
1871
                                          rows          => 200,
 
1872
                                          id            => 5,
 
1873
                                          rowid         => 5,
 
1874
                                       },
 
1875
                                       {  type          => 'Index scan',
 
1876
                                          key           => 'actor_4->PRIMARY',
 
1877
                                          possible_keys => undef,
 
1878
                                          partitions    => undef,
 
1879
                                          key_len       => 2,
 
1880
                                          'ref'         => undef,
 
1881
                                          rows          => 200,
 
1882
                                          id            => 6,
 
1883
                                          rowid         => 6,
 
1884
                                       },
 
1885
                                    ],
 
1886
                                 },
 
1887
                              ]
 
1888
                           },
 
1889
                        ],
 
1890
                     },
 
1891
                  ],
 
1892
               }
 
1893
            ],
 
1894
         },
 
1895
      ],
 
1896
   },
 
1897
   'Union over two derived tables of unions',
 
1898
);
 
1899
 
 
1900
$t = $e->parse( load_file("t/pt-visual-explain/samples/simple_subquery.sql") );
 
1901
is_deeply(
 
1902
   $t,
 
1903
   {  type     => 'SUBQUERY',
 
1904
      children => [
 
1905
         {  type          => 'Index scan',
 
1906
            rows          => 200,
 
1907
            id            => 1,
 
1908
            rowid         => 0,
 
1909
            key_len       => 2,
 
1910
            key           => 'actor->PRIMARY',
 
1911
            possible_keys => undef,
 
1912
            partitions    => undef,
 
1913
            'ref'         => undef,
 
1914
         },
 
1915
         {  type          => 'Index scan',
 
1916
            key           => 'film->idx_fk_language_id',
 
1917
            possible_keys => undef,
 
1918
            partitions    => undef,
 
1919
            key_len       => 1,
 
1920
            'ref'         => undef,
 
1921
            rows          => 951,
 
1922
            id            => 2,
 
1923
            rowid         => 1,
 
1924
         },
 
1925
      ]
 
1926
   },
 
1927
   'Simple subquery',
 
1928
);
 
1929
 
 
1930
$t = $e->parse( load_file("t/pt-visual-explain/samples/dependent_subquery.sql") );
 
1931
is_deeply(
 
1932
   $t,
 
1933
   {  type     => 'DEPENDENT SUBQUERY',
 
1934
      children => [
 
1935
         {  type          => 'Index scan',
 
1936
            rows          => 200,
 
1937
            id            => 1,
 
1938
            rowid         => 0,
 
1939
            key_len       => 2,
 
1940
            key           => 'actor->PRIMARY',
 
1941
            possible_keys => undef,
 
1942
            partitions    => undef,
 
1943
            'ref'         => undef,
 
1944
         },
 
1945
         {  type     => 'Filter with WHERE',
 
1946
            id       => 2,
 
1947
            rowid    => 1,
 
1948
            children => [
 
1949
               {  type          => 'Index lookup',
 
1950
                  key           => 'film_actor->PRIMARY',
 
1951
                  possible_keys => 'PRIMARY',
 
1952
                  partitions    => undef,
 
1953
                  key_len       => 2,
 
1954
                  'ref'         => 'actor.actor_id',
 
1955
                  rows          => 13,
 
1956
               },
 
1957
            ],
 
1958
         },
 
1959
      ],
 
1960
   },
 
1961
   'Dependent subquery',
 
1962
);
 
1963
 
 
1964
$t = $e->parse( load_file("t/pt-visual-explain/samples/uncacheable_subquery.sql") );
 
1965
is_deeply(
 
1966
   $t,
 
1967
   {  type     => 'UNCACHEABLE SUBQUERY',
 
1968
      children => [
 
1969
         {  type          => 'Index scan',
 
1970
            rows          => 200,
 
1971
            id            => 1,
 
1972
            rowid         => 0,
 
1973
            key_len       => 2,
 
1974
            key           => 'actor->PRIMARY',
 
1975
            possible_keys => undef,
 
1976
            partitions    => undef,
 
1977
            'ref'         => undef,
 
1978
         },
 
1979
         {  type          => 'Index scan',
 
1980
            key           => 'actor->PRIMARY',
 
1981
            possible_keys => undef,
 
1982
            partitions    => undef,
 
1983
            key_len       => 2,
 
1984
            'ref'         => undef,
 
1985
            rows          => 200,
 
1986
            id            => 2,
 
1987
            rowid         => 1,
 
1988
         },
 
1989
      ],
 
1990
   },
 
1991
   'Dependent uncacheable subquery',
 
1992
);
 
1993
 
 
1994
$t = $e->parse( load_file("t/pt-visual-explain/samples/join_in_subquery.sql") );
 
1995
is_deeply(
 
1996
   $t,
 
1997
   {  type     => 'SUBQUERY',
 
1998
      children => [
 
1999
         {  type          => 'Index scan',
 
2000
            rows          => 200,
 
2001
            id            => 1,
 
2002
            rowid         => 0,
 
2003
            key_len       => 2,
 
2004
            key           => 'actor->PRIMARY',
 
2005
            possible_keys => undef,
 
2006
            partitions    => undef,
 
2007
            'ref'         => undef,
 
2008
         },
 
2009
         {  type     => 'JOIN',
 
2010
            children => [
 
2011
               {  type          => 'Index scan',
 
2012
                  key           => 'film->idx_fk_language_id',
 
2013
                  key_len       => 1,
 
2014
                  possible_keys => 'PRIMARY',
 
2015
                  partitions    => undef,
 
2016
                  'ref'         => undef,
 
2017
                  rows          => 951,
 
2018
                  id            => 2,
 
2019
                  rowid         => 1,
 
2020
               },
 
2021
               {  type          => 'Index lookup',
 
2022
                  key           => 'film_actor->idx_fk_film_id',
 
2023
                  possible_keys => 'idx_fk_film_id',
 
2024
                  partitions    => undef,
 
2025
                  key_len       => 2,
 
2026
                  'ref'         => 'sakila.film.film_id',
 
2027
                  rows          => 2,
 
2028
                  id            => 2,
 
2029
                  rowid         => 2,
 
2030
               },
 
2031
            ],
 
2032
         },
 
2033
      ],
 
2034
   },
 
2035
   'Join inside a subquery',
 
2036
);
 
2037
 
 
2038
$t = $e->parse( load_file("t/pt-visual-explain/samples/unique_subquery_in_where_clause.sql") );
 
2039
is_deeply(
 
2040
   $t,
 
2041
   {  type     => 'DEPENDENT SUBQUERY',
 
2042
      children => [
 
2043
         {  type     => 'Filter with WHERE',
 
2044
            id       => 1,
 
2045
            rowid    => 0,
 
2046
            children => [
 
2047
               {  type          => 'Index scan',
 
2048
                  rows          => 5143,
 
2049
                  key_len       => 2,
 
2050
                  key           => 'film_actor->idx_fk_film_id',
 
2051
                  possible_keys => undef,
 
2052
                  partitions    => undef,
 
2053
                  'ref'         => undef,
 
2054
               },
 
2055
            ],
 
2056
         },
 
2057
         {  type          => 'Unique subquery',
 
2058
            rows          => 1,
 
2059
            id            => 2,
 
2060
            rowid         => 1,
 
2061
            key_len       => 2,
 
2062
            key           => 'actor->PRIMARY',
 
2063
            possible_keys => 'PRIMARY',
 
2064
            partitions    => undef,
 
2065
            'ref'         => 'func',
 
2066
         },
 
2067
      ],
 
2068
   },
 
2069
   'Unique subquery',
 
2070
);
 
2071
 
 
2072
$t = $e->parse( load_file("t/pt-visual-explain/samples/index_subquery_in_where_clause.sql") );
 
2073
is_deeply(
 
2074
   $t,
 
2075
   {  type     => 'DEPENDENT SUBQUERY',
 
2076
      children => [
 
2077
         {  type     => 'Filter with WHERE',
 
2078
            id       => 1,
 
2079
            rowid    => 0,
 
2080
            children => [
 
2081
               {  type          => 'Index scan',
 
2082
                  rows          => 200,
 
2083
                  key_len       => 2,
 
2084
                  key           => 'actor->PRIMARY',
 
2085
                  possible_keys => undef,
 
2086
                  partitions    => undef,
 
2087
                  'ref'         => undef,
 
2088
               },
 
2089
            ],
 
2090
         },
 
2091
         {  type          => 'Index subquery',
 
2092
            rows          => 13,
 
2093
            id            => 2,
 
2094
            rowid         => 1,
 
2095
            key_len       => 2,
 
2096
            key           => 'film_actor->PRIMARY',
 
2097
            possible_keys => 'PRIMARY',
 
2098
            partitions    => undef,
 
2099
            'ref'         => 'func',
 
2100
         },
 
2101
      ],
 
2102
   },
 
2103
   'Index subquery',
 
2104
);
 
2105
 
 
2106
$t = $e->parse( load_file("t/pt-visual-explain/samples/full_scan_on_null_key.sql") );
 
2107
is_deeply(
 
2108
   $t,
 
2109
   {  type     => 'DEPENDENT SUBQUERY',
 
2110
      children => [
 
2111
         {  type     => 'Filter with WHERE',
 
2112
            id       => 1,
 
2113
            rowid    => 0,
 
2114
            children => [
 
2115
               {  type     => 'Table scan',
 
2116
                  rows     => 4,
 
2117
                  children => [
 
2118
                     {  type          => 'Table',
 
2119
                        table         => 't1',
 
2120
                        possible_keys => undef,
 
2121
                        partitions    => undef,
 
2122
                     },
 
2123
                  ],
 
2124
               },
 
2125
            ],
 
2126
         },
 
2127
         {  type     => 'JOIN',
 
2128
            children => [
 
2129
               {  type     => 'Filter with WHERE',
 
2130
                  id       => 2,
 
2131
                  rowid    => 1,
 
2132
                  children => [
 
2133
                     {  type          => 'Unique index lookup',
 
2134
                        rows          => 1,
 
2135
                        key_len       => 4,
 
2136
                        key           => 't2->PRIMARY',
 
2137
                        possible_keys => 'PRIMARY',
 
2138
                        partitions    => undef,
 
2139
                        'ref'         => 'func',
 
2140
                        warning       => 'Full scan on NULL key',
 
2141
                     },
 
2142
                  ],
 
2143
               },
 
2144
               {  type     => 'Filter with WHERE',
 
2145
                  id       => 2,
 
2146
                  rowid    => 2,
 
2147
                  children => [
 
2148
                     {  type     => 'Bookmark lookup',
 
2149
                        children => [
 
2150
                           {  type          => 'Unique index lookup',
 
2151
                              rows          => 1,
 
2152
                              key_len       => 4,
 
2153
                              key           => 't3->PRIMARY',
 
2154
                              'ref'         => 'func',
 
2155
                              warning       => 'Full scan on NULL key',
 
2156
                              possible_keys => 'PRIMARY',
 
2157
                              partitions    => undef,
 
2158
                           },
 
2159
                           {  type          => 'Table',
 
2160
                              table         => 't3',
 
2161
                              possible_keys => 'PRIMARY',
 
2162
                              partitions    => undef,
 
2163
                           },
 
2164
                        ],
 
2165
                     },
 
2166
                  ],
 
2167
               },
 
2168
            ],
 
2169
         },
 
2170
      ],
 
2171
   },
 
2172
   'Subqueries that do a full scan on a NULL key',
 
2173
);
 
2174
 
 
2175
$t = $e->parse( load_file("t/pt-visual-explain/samples/nested_derived_tables.sql") );
 
2176
is_deeply(
 
2177
   $t,
 
2178
   {  type     => 'DEPENDENT SUBQUERY',
 
2179
      children => [
 
2180
         {  type     => 'Table scan',
 
2181
            rows     => 1000,
 
2182
            id       => 1,
 
2183
            rowid    => 0,
 
2184
            children => [
 
2185
               {  type     => 'DERIVED',
 
2186
                  table         => 'derived(derived(inner_der,inner_sub),mid_sub)',
 
2187
                  possible_keys => undef,
 
2188
                  partitions    => undef,
 
2189
                  children => [
 
2190
                     {  type     => 'DEPENDENT SUBQUERY',
 
2191
                        children => [
 
2192
                           {  type     => 'Table scan',
 
2193
                              rows     => 1000,
 
2194
                              id       => 3,
 
2195
                              rowid    => 1,
 
2196
                              children => [
 
2197
                                 {  type     => 'DERIVED',
 
2198
                                    table         => 'derived(inner_der,inner_sub)',
 
2199
                                    possible_keys => undef,
 
2200
                                    partitions    => undef,
 
2201
                                    children => [
 
2202
                                       {  type     => 'DEPENDENT SUBQUERY',
 
2203
                                          children => [
 
2204
                                             {  type     => 'Table scan',
 
2205
                                                rows     => 951,
 
2206
                                                id       => 5,
 
2207
                                                rowid    => 2,
 
2208
                                                children => [
 
2209
                                                   {  type          => 'Table',
 
2210
                                                      table         => 'inner_der',
 
2211
                                                      possible_keys => undef,
 
2212
                                                      partitions    => undef,
 
2213
                                                   },
 
2214
                                                ],
 
2215
                                             },
 
2216
                                             {  type     => 'Filter with WHERE',
 
2217
                                                id       => 6,
 
2218
                                                rowid    => 3,
 
2219
                                                children => [
 
2220
                                                   {  type          => 'Unique index lookup',
 
2221
                                                      rows          => 1,
 
2222
                                                      key_len       => 2,
 
2223
                                                      key           => 'inner_sub->PRIMARY',
 
2224
                                                      possible_keys => 'PRIMARY',
 
2225
                                                      partitions    => undef,
 
2226
                                                      'ref'         => 'inner_der.film_id',
 
2227
                                                   },
 
2228
                                                ],
 
2229
                                             },
 
2230
                                          ],
 
2231
                                       },
 
2232
                                    ],
 
2233
                                 },
 
2234
                              ],
 
2235
                           },
 
2236
                           {  type     => 'Filter with WHERE',
 
2237
                              id       => 4,
 
2238
                              rowid    => 4,
 
2239
                              children => [
 
2240
                                 {  type          => 'Unique index lookup',
 
2241
                                    rows          => 1,
 
2242
                                    key_len       => 2,
 
2243
                                    key           => 'mid_sub->PRIMARY',
 
2244
                                    possible_keys => 'PRIMARY',
 
2245
                                    partitions    => undef,
 
2246
                                    'ref'         => 'mid_der.film_id',
 
2247
                                 },
 
2248
                              ],
 
2249
                           },
 
2250
                        ],
 
2251
                     },
 
2252
                  ],
 
2253
               },
 
2254
            ],
 
2255
         },
 
2256
         {  type     => 'Filter with WHERE',
 
2257
            id       => 2,
 
2258
            rowid    => 5,
 
2259
            children => [
 
2260
               {  type          => 'Unique index lookup',
 
2261
                  rows          => 1,
 
2262
                  key_len       => 2,
 
2263
                  key           => 'outer_sub->PRIMARY',
 
2264
                  possible_keys => 'PRIMARY',
 
2265
                  partitions    => undef,
 
2266
                  'ref'         => 'outer_der.film_id',
 
2267
               },
 
2268
            ],
 
2269
         },
 
2270
      ],
 
2271
   },
 
2272
   'Nested derived tables and subqueries',
 
2273
);
 
2274
 
 
2275
$t = $e->parse( load_file("t/pt-visual-explain/samples/adjacent_subqueries.sql") );
 
2276
is_deeply(
 
2277
   $t,
 
2278
   {  type     => 'DEPENDENT SUBQUERY',
 
2279
      children => [
 
2280
         {  type     => 'SUBQUERY',
 
2281
            children => [
 
2282
               {  type    => 'Index scan',
 
2283
                  key     => 'actor->PRIMARY',
 
2284
                  key_len => 2,
 
2285
                  rows    => 200,
 
2286
                  'ref'   => undef,
 
2287
                  partitions => undef,
 
2288
                  possible_keys => undef,
 
2289
                  id       => 1,
 
2290
                  rowid    => 0,
 
2291
               },
 
2292
               {  type    => 'Index scan',
 
2293
                  key     => 'f->idx_fk_language_id',
 
2294
                  key_len => 1,
 
2295
                  rows    => 951,
 
2296
                  'ref'   => undef,
 
2297
                  partitions => undef,
 
2298
                  possible_keys => undef,
 
2299
                  id       => 3,
 
2300
                  rowid    => 1,
 
2301
               },
 
2302
            ],
 
2303
         },
 
2304
         {  type     => 'Filter with WHERE',
 
2305
            id       => 2,
 
2306
            rowid    => 2,
 
2307
            children => [
 
2308
               {  type          => 'Index lookup',
 
2309
                  key           => 'film_actor->PRIMARY',
 
2310
                  possible_keys => 'PRIMARY',
 
2311
                  partitions => undef,
 
2312
                  key_len       => 2,
 
2313
                  'ref'         => 'actor.actor_id',
 
2314
                  rows          => 13,
 
2315
               },
 
2316
            ],
 
2317
         },
 
2318
      ],
 
2319
   },
 
2320
   'Adjacent subqueries',
 
2321
);
 
2322
 
 
2323
$t = $e->parse( load_file("t/pt-visual-explain/samples/complex_select_types.sql") );
 
2324
is_deeply(
 
2325
   $t,
 
2326
   {  id       => '1',
 
2327
      type     => 'Table scan',
 
2328
      rows     => undef,
 
2329
      rowid    => 7,
 
2330
      children => [
 
2331
         {  possible_keys => undef,
 
2332
            table      => 'union(derived(actor),film_actor,derived(film,store),rental)',
 
2333
            type       => 'UNION',
 
2334
            partitions => undef,
 
2335
            children   => [
 
2336
               {  type     => 'DEPENDENT SUBQUERY',
 
2337
                  children => [
 
2338
                     {  id       => 1,
 
2339
                        type     => 'Table scan',
 
2340
                        rows     => '5',
 
2341
                        rowid    => 0,
 
2342
                        children => [
 
2343
                           {  possible_keys => undef,
 
2344
                              table         => 'derived(actor)',
 
2345
                              type          => 'DERIVED',
 
2346
                              partitions    => undef,
 
2347
                              children      => [
 
2348
                                 {  key_len       => '2',
 
2349
                                    ref           => undef,
 
2350
                                    rows          => '200',
 
2351
                                    partitions    => undef,
 
2352
                                    rowid         => 1,
 
2353
                                    key           => 'actor->PRIMARY',
 
2354
                                    possible_keys => undef,
 
2355
                                    type          => 'Index scan',
 
2356
                                    id            => 3
 
2357
                                 }
 
2358
                              ],
 
2359
                           }
 
2360
                        ],
 
2361
                     },
 
2362
                     {  key_len       => '2',
 
2363
                        ref           => 'der_1.actor_id',
 
2364
                        rows          => '13',
 
2365
                        partitions    => undef,
 
2366
                        rowid         => 2,
 
2367
                        key           => 'film_actor->PRIMARY',
 
2368
                        possible_keys => 'PRIMARY',
 
2369
                        type          => 'Index lookup',
 
2370
                        id            => 2
 
2371
                     }
 
2372
                  ],
 
2373
               },
 
2374
               {  type     => 'UNCACHEABLE SUBQUERY',
 
2375
                  children => [
 
2376
                     {  id       => 4,
 
2377
                        type     => 'Table scan',
 
2378
                        rows     => '5',
 
2379
                        rowid    => 3,
 
2380
                        children => [
 
2381
                           {  possible_keys => undef,
 
2382
                              table         => 'derived(film,store)',
 
2383
                              type          => 'DERIVED',
 
2384
                              partitions    => undef,
 
2385
                              children      => [
 
2386
                                 {  type     => 'SUBQUERY',
 
2387
                                    children => [
 
2388
                                       {  key_len       => '1',
 
2389
                                          ref           => undef,
 
2390
                                          rows          => '1022',
 
2391
                                          partitions    => undef,
 
2392
                                          rowid         => 4,
 
2393
                                          key           => 'film->idx_fk_language_id',
 
2394
                                          possible_keys => undef,
 
2395
                                          type          => 'Index scan',
 
2396
                                          id            => 6
 
2397
                                       },
 
2398
                                       {  key_len       => '1',
 
2399
                                          ref           => undef,
 
2400
                                          rows          => '2',
 
2401
                                          partitions    => undef,
 
2402
                                          rowid         => 5,
 
2403
                                          key           => 'store->PRIMARY',
 
2404
                                          possible_keys => undef,
 
2405
                                          type          => 'Index scan',
 
2406
                                          id            => 7
 
2407
                                       }
 
2408
                                    ],
 
2409
                                 }
 
2410
                              ],
 
2411
                           }
 
2412
                        ],
 
2413
                     },
 
2414
                     {  key_len       => '1',
 
2415
                        ref           => undef,
 
2416
                        rows          => '16305',
 
2417
                        partitions    => undef,
 
2418
                        rowid         => 6,
 
2419
                        key           => 'rental->idx_fk_staff_id',
 
2420
                        possible_keys => undef,
 
2421
                        type          => 'Index scan',
 
2422
                        id            => 5
 
2423
                     }
 
2424
                  ],
 
2425
               }
 
2426
            ],
 
2427
         }
 
2428
      ],
 
2429
   },
 
2430
   'Complex SELECT types combined',
 
2431
);
 
2432
 
 
2433
$t = $e->parse( load_file("t/pt-visual-explain/samples/derived_without_table.sql") );
 
2434
is_deeply(
 
2435
   $t,
 
2436
   {  id       => 1,
 
2437
      type     => 'Constant table access',
 
2438
      rows     => '1',
 
2439
      rowid    => 0,
 
2440
      children => [
 
2441
         {  possible_keys => undef,
 
2442
            table         => 'derived(<none>)',
 
2443
            type          => 'DERIVED',
 
2444
            partitions    => undef,
 
2445
            children      => [
 
2446
               {  id    => 2,
 
2447
                  type  => 'DERIVED',
 
2448
                  rowid => 1
 
2449
               }
 
2450
            ],
 
2451
         }
 
2452
      ],
 
2453
   },
 
2454
   'Recursive table name with anonymous derived table',
 
2455
);
 
2456
 
 
2457
# #############################################################################
 
2458
# Done.
 
2459
# #############################################################################
 
2460
exit;