Debian Codegraph @

mysql-test/suite/compat/oracle/r/table_value_constr.result

raw
1 SET sql_mode=ORACLE;
2 create table t1 (a int, b int);
3 insert into t1 values (1,2),(4,6),(9,7),
4 (1,1),(2,5),(7,8);
5 # just VALUES
6 values (1,2);
7 1 2
8 1 2
9 values (1,2), (3,4), (5.6,0);
10 1 2
11 1.0 2
12 3.0 4
13 5.6 0
14 values ('abc', 'def');
15 abc def
16 abc def
17 # UNION that uses VALUES structure(s)
18 select 1,2
19 union
20 values (1,2);
21 1 2
22 1 2
23 values (1,2)
24 union
25 select 1,2;
26 1 2
27 1 2
28 select 1,2
29 union
30 values (1,2),(3,4),(5,6),(7,8);
31 1 2
32 1 2
33 3 4
34 5 6
35 7 8
36 select 3,7
37 union
38 values (1,2),(3,4),(5,6);
39 3 7
40 3 7
41 1 2
42 3 4
43 5 6
44 select 3,7,4
45 union
46 values (1,2,5),(4,5,6);
47 3 7 4
48 3 7 4
49 1 2 5
50 4 5 6
51 select 1,2
52 union
53 values (1,7),(3,6.5);
54 1 2
55 1 2.0
56 1 7.0
57 3 6.5
58 select 1,2
59 union
60 values (1,2.0),(3,6);
61 1 2
62 1 2.0
63 3 6.0
64 select 1.8,2
65 union
66 values (1,2),(3,6);
67 1.8 2
68 1.8 2
69 1.0 2
70 3.0 6
71 values (1,2.4),(3,6)
72 union
73 select 2.8,9;
74 1 2.4
75 1.0 2.4
76 3.0 6.0
77 2.8 9.0
78 values (1,2),(3,4),(5,6),(7,8)
79 union
80 select 5,6;
81 1 2
82 1 2
83 3 4
84 5 6
85 7 8
86 select 'ab','cdf'
87 union
88 values ('al','zl'),('we','q');
89 ab cdf
90 ab cdf
91 al zl
92 we q
93 values ('ab', 'cdf')
94 union
95 select 'ab','cdf';
96 ab cdf
97 ab cdf
98 values (1,2)
99 union
100 values (1,2),(5,6);
101 1 2
102 1 2
103 5 6
104 values (1,2)
105 union
106 values (3,4),(5,6);
107 1 2
108 1 2
109 3 4
110 5 6
111 values (1,2)
112 union
113 values (1,2)
114 union values (4,5);
115 1 2
116 1 2
117 4 5
118 # UNION ALL that uses VALUES structure
119 values (1,2),(3,4)
120 union all
121 select 5,6;
122 1 2
123 1 2
124 3 4
125 5 6
126 values (1,2),(3,4)
127 union all
128 select 1,2;
129 1 2
130 1 2
131 3 4
132 1 2
133 select 5,6
134 union all
135 values (1,2),(3,4);
136 5 6
137 5 6
138 1 2
139 3 4
140 select 1,2
141 union all
142 values (1,2),(3,4);
143 1 2
144 1 2
145 1 2
146 3 4
147 values (1,2)
148 union all
149 values (1,2),(5,6);
150 1 2
151 1 2
152 1 2
153 5 6
154 values (1,2)
155 union all
156 values (3,4),(5,6);
157 1 2
158 1 2
159 3 4
160 5 6
161 values (1,2)
162 union all
163 values (1,2)
164 union all
165 values (4,5);
166 1 2
167 1 2
168 1 2
169 4 5
170 values (1,2)
171 union all
172 values (1,2)
173 union values (1,2);
174 1 2
175 1 2
176 values (1,2)
177 union
178 values (1,2)
179 union all
180 values (1,2);
181 1 2
182 1 2
183 1 2
184 # EXCEPT that uses VALUES structure(s)
185 select 1,2
186 except
187 values (3,4),(5,6);
188 1 2
189 1 2
190 select 1,2
191 except
192 values (1,2),(3,4);
193 1 2
194 values (1,2),(3,4)
195 except
196 select 5,6;
197 1 2
198 1 2
199 3 4
200 values (1,2),(3,4)
201 except
202 select 1,2;
203 1 2
204 3 4
205 values (1,2),(3,4)
206 except
207 values (5,6);
208 1 2
209 1 2
210 3 4
211 values (1,2),(3,4)
212 except
213 values (1,2);
214 1 2
215 3 4
216 # INTERSECT that uses VALUES structure(s)
217 select 1,2
218 intersect
219 values (3,4),(5,6);
220 1 2
221 select 1,2
222 intersect
223 values (1,2),(3,4);
224 1 2
225 1 2
226 values (1,2),(3,4)
227 intersect
228 select 5,6;
229 1 2
230 values (1,2),(3,4)
231 intersect
232 select 1,2;
233 1 2
234 1 2
235 values (1,2),(3,4)
236 intersect
237 values (5,6);
238 1 2
239 values (1,2),(3,4)
240 intersect
241 values (1,2);
242 1 2
243 1 2
244 # combination of different structures that uses VALUES structures : UNION + EXCEPT
245 values (1,2),(3,4)
246 except
247 select 1,2
248 union values (1,2);
249 1 2
250 3 4
251 1 2
252 values (1,2),(3,4)
253 except
254 values (1,2)
255 union
256 values (1,2);
257 1 2
258 3 4
259 1 2
260 values (1,2),(3,4)
261 except
262 values (1,2)
263 union
264 values (3,4);
265 1 2
266 3 4
267 values (1,2),(3,4)
268 union
269 values (1,2)
270 except
271 values (1,2);
272 1 2
273 3 4
274 # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT
275 values (1,2),(3,4)
276 except
277 select 1,2
278 union all
279 values (1,2);
280 1 2
281 3 4
282 1 2
283 values (1,2),(3,4)
284 except
285 values (1,2)
286 union all
287 values (1,2);
288 1 2
289 3 4
290 1 2
291 values (1,2),(3,4)
292 except
293 values (1,2)
294 union all
295 values (3,4);
296 1 2
297 3 4
298 3 4
299 values (1,2),(3,4)
300 union all
301 values (1,2)
302 except
303 values (1,2);
304 1 2
305 3 4
306 # combination of different structures that uses VALUES structures : UNION + INTERSECT
307 values (1,2),(3,4)
308 intersect
309 select 1,2
310 union
311 values (1,2);
312 1 2
313 1 2
314 values (1,2),(3,4)
315 intersect
316 values (1,2)
317 union
318 values (1,2);
319 1 2
320 1 2
321 values (1,2),(3,4)
322 intersect
323 values (1,2)
324 union
325 values (3,4);
326 1 2
327 1 2
328 3 4
329 values (1,2),(3,4)
330 union
331 values (1,2)
332 intersect
333 values (1,2);
334 1 2
335 1 2
336 # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT
337 values (1,2),(3,4)
338 intersect
339 select 1,2
340 union all
341 values (1,2);
342 1 2
343 1 2
344 1 2
345 values (1,2),(3,4)
346 intersect
347 values (1,2)
348 union all
349 values (1,2);
350 1 2
351 1 2
352 1 2
353 values (1,2),(3,4)
354 intersect
355 values (1,2)
356 union all
357 values (3,4);
358 1 2
359 1 2
360 3 4
361 values (1,2),(3,4)
362 union all
363 values (1,2)
364 intersect
365 values (1,2);
366 1 2
367 1 2
368 # combination of different structures that uses VALUES structures : UNION + UNION ALL
369 values (1,2),(3,4)
370 union all
371 select 1,2
372 union
373 values (1,2);
374 1 2
375 1 2
376 3 4
377 values (1,2),(3,4)
378 union all
379 values (1,2)
380 union
381 values (1,2);
382 1 2
383 1 2
384 3 4
385 values (1,2),(3,4)
386 union all
387 values (1,2)
388 union
389 values (3,4);
390 1 2
391 1 2
392 3 4
393 values (1,2),(3,4)
394 union
395 values (1,2)
396 union all
397 values (1,2);
398 1 2
399 1 2
400 3 4
401 1 2
402 values (1,2)
403 union
404 values (1,2)
405 union all
406 values (1,2);
407 1 2
408 1 2
409 1 2
410 # CTE that uses VALUES structure(s) : non-recursive CTE
411 with t2 as
412 (
413 values (1,2),(3,4)
414 )
415 select * from t2;
416 1 2
417 1 2
418 3 4
419 with t2 as
420 (
421 select 1,2
422 union
423 values (1,2)
424 )
425 select * from t2;
426 1 2
427 1 2
428 with t2 as
429 (
430 select 1,2
431 union
432 values (1,2),(3,4)
433 )
434 select * from t2;
435 1 2
436 1 2
437 3 4
438 with t2 as
439 (
440 values (1,2)
441 union
442 select 1,2
443 )
444 select * from t2;
445 1 2
446 1 2
447 with t2 as
448 (
449 values (1,2),(3,4)
450 union
451 select 1,2
452 )
453 select * from t2;
454 1 2
455 1 2
456 3 4
457 with t2 as
458 (
459 values (5,6)
460 union
461 values (1,2),(3,4)
462 )
463 select * from t2;
464 5 6
465 5 6
466 1 2
467 3 4
468 with t2 as
469 (
470 values (1,2)
471 union
472 values (1,2),(3,4)
473 )
474 select * from t2;
475 1 2
476 1 2
477 3 4
478 with t2 as
479 (
480 select 1,2
481 union all
482 values (1,2),(3,4)
483 )
484 select * from t2;
485 1 2
486 1 2
487 1 2
488 3 4
489 with t2 as
490 (
491 values (1,2),(3,4)
492 union all
493 select 1,2
494 )
495 select * from t2;
496 1 2
497 1 2
498 3 4
499 1 2
500 with t2 as
501 (
502 values (1,2)
503 union all
504 values (1,2),(3,4)
505 )
506 select * from t2;
507 1 2
508 1 2
509 1 2
510 3 4
511 # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor
512 with recursive t2(a,b) as
513 (
514 values(1,1)
515 union
516 select t1.a, t1.b
517 from t1,t2
518 where t1.a=t2.a
519 )
520 select * from t2;
521 a b
522 1 1
523 1 2
524 with recursive t2(a,b) as
525 (
526 values(1,1)
527 union
528 select t1.a+1, t1.b
529 from t1,t2
530 where t1.a=t2.a
531 )
532 select * from t2;
533 a b
534 1 1
535 2 2
536 2 1
537 3 5
538 # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors
539 with recursive t2(a,b) as
540 (
541 values(1,1)
542 union
543 values (3,4)
544 union
545 select t2.a+1, t1.b
546 from t1,t2
547 where t1.a=t2.a
548 )
549 select * from t2;
550 a b
551 1 1
552 3 4
553 2 2
554 2 1
555 3 5
556 # recursive CTE that uses VALUES structure(s) : that uses UNION ALL
557 with recursive t2(a,b,st) as
558 (
559 values(1,1,1)
560 union all
561 select t2.a, t1.b, t2.st+1
562 from t1,t2
563 where t1.a=t2.a and st<3
564 )
565 select * from t2;
566 a b st
567 1 1 1
568 1 1 2
569 1 1 3
570 1 1 3
571 1 2 2
572 1 2 3
573 1 2 3
574 # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements)
575 with recursive fact(n,f) as
576 (
577 values(1,1)
578 union
579 select n+1,f*n from fact where n < 10
580 )
581 select * from fact;
582 n f
583 1 1
584 2 1
585 3 2
586 4 6
587 5 24
588 6 120
589 7 720
590 8 5040
591 9 40320
592 10 362880
593 # Derived table that uses VALUES structure(s) : singe VALUES structure
594 select * from (values (1,2),(3,4)) as t2;
595 1 2
596 1 2
597 3 4
598 # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
599 select * from (select 1,2 union values (1,2)) as t2;
600 1 2
601 1 2
602 select * from (select 1,2 union values (1,2),(3,4)) as t2;
603 1 2
604 1 2
605 3 4
606 select * from (values (1,2) union select 1,2) as t2;
607 1 2
608 1 2
609 select * from (values (1,2),(3,4) union select 1,2) as t2;
610 1 2
611 1 2
612 3 4
613 select * from (values (5,6) union values (1,2),(3,4)) as t2;
614 5 6
615 5 6
616 1 2
617 3 4
618 select * from (values (1,2) union values (1,2),(3,4)) as t2;
619 1 2
620 1 2
621 3 4
622 # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
623 select * from (select 1,2 union all values (1,2),(3,4)) as t2;
624 1 2
625 1 2
626 1 2
627 3 4
628 select * from (values (1,2),(3,4) union all select 1,2) as t2;
629 1 2
630 1 2
631 3 4
632 1 2
633 select * from (values (1,2) union all values (1,2),(3,4)) as t2;
634 1 2
635 1 2
636 1 2
637 3 4
638 # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure
639 create view v1 as values (1,2),(3,4);
640 select * from v1;
641 1 2
642 1 2
643 3 4
644 drop view v1;
645 # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s)
646 create view v1 as
647 select 1,2
648 union
649 values (1,2);
650 select * from v1;
651 1 2
652 1 2
653 drop view v1;
654 create view v1 as
655 select 1,2
656 union
657 values (1,2),(3,4);
658 select * from v1;
659 1 2
660 1 2
661 3 4
662 drop view v1;
663 create view v1 as
664 values (1,2)
665 union
666 select 1,2;
667 select * from v1;
668 1 2
669 1 2
670 drop view v1;
671 create view v1 as
672 values (1,2),(3,4)
673 union
674 select 1,2;
675 select * from v1;
676 1 2
677 1 2
678 3 4
679 drop view v1;
680 create view v1 as
681 values (5,6)
682 union
683 values (1,2),(3,4);
684 select * from v1;
685 5 6
686 5 6
687 1 2
688 3 4
689 drop view v1;
690 # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
691 create view v1 as
692 values (1,2)
693 union
694 values (1,2),(3,4);
695 select * from v1;
696 1 2
697 1 2
698 3 4
699 drop view v1;
700 create view v1 as
701 select 1,2
702 union all
703 values (1,2),(3,4);
704 select * from v1;
705 1 2
706 1 2
707 1 2
708 3 4
709 drop view v1;
710 create view v1 as
711 values (1,2),(3,4)
712 union all
713 select 1,2;
714 select * from v1;
715 1 2
716 1 2
717 3 4
718 1 2
719 drop view v1;
720 create view v1 as
721 values (1,2)
722 union all
723 values (1,2),(3,4);
724 select * from v1;
725 1 2
726 1 2
727 1 2
728 3 4
729 drop view v1;
730 # IN-subquery with VALUES structure(s) : simple case
731 select * from t1
732 where a in (values (1));
733 a b
734 1 2
735 1 1
736 select * from t1
737 where a in (select * from (values (1)) as tvc_0);
738 a b
739 1 2
740 1 1
741 explain extended select * from t1
742 where a in (values (1));
743 id select_type table type possible_keys key key_len ref rows filtered Extra
744 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
745 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
746 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
747 Warnings:
748 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
749 explain extended select * from t1
750 where a in (select * from (values (1)) as tvc_0);
751 id select_type table type possible_keys key key_len ref rows filtered Extra
752 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
753 1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
754 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
755 Warnings:
756 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
757 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
758 select * from t1
759 where a in (values (1) union select 2);
760 a b
761 1 2
762 1 1
763 2 5
764 select * from t1
765 where a in (select * from (values (1)) as tvc_0 union
766 select 2);
767 a b
768 1 2
769 1 1
770 2 5
771 explain extended select * from t1
772 where a in (values (1) union select 2);
773 id select_type table type possible_keys key key_len ref rows filtered Extra
774 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
775 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
776 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
777 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
778 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
779 Warnings:
780 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1) union /* select#3 */ select 2 AS "2") "__4" join "test"."t1" where "__4"."1" = "test"."t1"."a"
781 explain extended select * from t1
782 where a in (select * from (values (1)) as tvc_0 union
783 select 2);
784 id select_type table type possible_keys key key_len ref rows filtered Extra
785 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
786 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
787 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2 100.00
788 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
789 4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
790 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
791 Warnings:
792 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select "tvc_0"."1" AS "1" from (values (1)) "tvc_0" union /* select#4 */ select 2 AS "2") "__5" join "test"."t1" where "__5"."1" = "test"."t1"."a"
793 # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place
794 select * from t1
795 where a in (select 2 union values (1));
796 a b
797 1 2
798 1 1
799 2 5
800 select * from t1
801 where a in (select 2 union
802 select * from (values (1)) tvc_0);
803 a b
804 1 2
805 1 1
806 2 5
807 explain extended select * from t1
808 where a in (select 2 union values (1));
809 id select_type table type possible_keys key key_len ref rows filtered Extra
810 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
811 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
812 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
813 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
814 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
815 Warnings:
816 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select 2 AS "2" union values (1)) "__4" join "test"."t1" where "__4"."2" = "test"."t1"."a"
817 explain extended select * from t1
818 where a in (select 2 union
819 select * from (values (1)) tvc_0);
820 id select_type table type possible_keys key key_len ref rows filtered Extra
821 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
822 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
823 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
824 3 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00
825 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
826 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
827 Warnings:
828 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select 2 AS "2" union /* select#3 */ select "tvc_0"."1" AS "1" from (values (1)) "tvc_0") "__5" join "test"."t1" where "__5"."2" = "test"."t1"."a"
829 # IN-subquery with VALUES structure(s) : UNION ALL
830 select * from t1
831 where a in (values (1) union all select b from t1);
832 a b
833 1 2
834 1 1
835 2 5
836 7 8
837 select * from t1
838 where a in (select * from (values (1)) as tvc_0 union all
839 select b from t1);
840 a b
841 1 2
842 1 1
843 2 5
844 7 8
845 explain extended select * from t1
846 where a in (values (1) union all select b from t1);
847 id select_type table type possible_keys key key_len ref rows filtered Extra
848 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
849 1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 FirstMatch(t1)
850 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
851 3 UNION t1 ALL NULL NULL NULL NULL 6 100.00
852 Warnings:
853 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1) union all /* select#3 */ select "test"."t1"."b" AS "b" from "test"."t1") "__4") where "__4"."1" = "test"."t1"."a"
854 explain extended select * from t1
855 where a in (select * from (values (1)) as tvc_0 union all
856 select b from t1);
857 id select_type table type possible_keys key key_len ref rows filtered Extra
858 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
859 1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 FirstMatch(t1)
860 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2 100.00
861 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
862 4 UNION t1 ALL NULL NULL NULL NULL 6 100.00
863 Warnings:
864 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((/* select#2 */ select "tvc_0"."1" AS "1" from (values (1)) "tvc_0" union all /* select#4 */ select "test"."t1"."b" AS "b" from "test"."t1") "__5") where "__5"."1" = "test"."t1"."a"
865 # NOT IN subquery with VALUES structure(s) : simple case
866 select * from t1
867 where a not in (values (1),(2));
868 a b
869 4 6
870 9 7
871 7 8
872 select * from t1
873 where a not in (select * from (values (1),(2)) as tvc_0);
874 a b
875 4 6
876 9 7
877 7 8
878 explain extended select * from t1
879 where a not in (values (1),(2));
880 id select_type table type possible_keys key key_len ref rows filtered Extra
881 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
882 3 DEPENDENT SUBQUERY <derived2> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
883 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
884 Warnings:
885 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1")))))
886 explain extended select * from t1
887 where a not in (select * from (values (1),(2)) as tvc_0);
888 id select_type table type possible_keys key key_len ref rows filtered Extra
889 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
890 2 DEPENDENT SUBQUERY <derived3> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
891 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
892 Warnings:
893 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1")))))
894 # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place
895 select * from t1
896 where a not in (values (1) union select 2);
897 a b
898 4 6
899 9 7
900 7 8
901 select * from t1
902 where a not in (select * from (values (1)) as tvc_0 union
903 select 2);
904 a b
905 4 6
906 9 7
907 7 8
908 explain extended select * from t1
909 where a not in (values (1) union select 2);
910 id select_type table type possible_keys key key_len ref rows filtered Extra
911 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
912 4 DEPENDENT SUBQUERY <derived2> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
913 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
914 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
915 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
916 Warnings:
917 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "__4"."1")))))
918 explain extended select * from t1
919 where a not in (select * from (values (1)) as tvc_0 union
920 select 2);
921 id select_type table type possible_keys key key_len ref rows filtered Extra
922 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
923 5 DEPENDENT SUBQUERY <derived2> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
924 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2 100.00
925 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
926 4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
927 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
928 Warnings:
929 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "__5"."1")))))
930 # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place
931 select * from t1
932 where a not in (select 2 union values (1));
933 a b
934 4 6
935 9 7
936 7 8
937 select * from t1
938 where a not in (select 2 union
939 select * from (values (1)) as tvc_0);
940 a b
941 4 6
942 9 7
943 7 8
944 explain extended select * from t1
945 where a not in (select 2 union values (1));
946 id select_type table type possible_keys key key_len ref rows filtered Extra
947 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
948 4 DEPENDENT SUBQUERY <derived2> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
949 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
950 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
951 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
952 Warnings:
953 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "__4"."2")))))
954 explain extended select * from t1
955 where a not in (select 2 union
956 select * from (values (1)) as tvc_0);
957 id select_type table type possible_keys key key_len ref rows filtered Extra
958 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
959 5 DEPENDENT SUBQUERY <derived2> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
960 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
961 3 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00
962 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
963 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
964 Warnings:
965 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "__5"."2")))))
966 # ANY-subquery with VALUES structure(s) : simple case
967 select * from t1
968 where a = any (values (1),(2));
969 a b
970 1 2
971 1 1
972 2 5
973 select * from t1
974 where a = any (select * from (values (1),(2)) as tvc_0);
975 a b
976 1 2
977 1 1
978 2 5
979 explain extended select * from t1
980 where a = any (values (1),(2));
981 id select_type table type possible_keys key key_len ref rows filtered Extra
982 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
983 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
984 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
985 Warnings:
986 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1),(2)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
987 explain extended select * from t1
988 where a = any (select * from (values (1),(2)) as tvc_0);
989 id select_type table type possible_keys key key_len ref rows filtered Extra
990 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
991 1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
992 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
993 Warnings:
994 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1),(2)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
995 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
996 select * from t1
997 where a = any (values (1) union select 2);
998 a b
999 1 2
1000 1 1
1001 2 5
1002 select * from t1
1003 where a = any (select * from (values (1)) as tvc_0 union
1004 select 2);
1005 a b
1006 1 2
1007 1 1
1008 2 5
1009 explain extended select * from t1
1010 where a = any (values (1) union select 2);
1011 id select_type table type possible_keys key key_len ref rows filtered Extra
1012 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1013 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
1014 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1015 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1016 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1017 Warnings:
1018 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1) union /* select#3 */ select 2 AS "2") "__4" join "test"."t1" where "__4"."1" = "test"."t1"."a"
1019 explain extended select * from t1
1020 where a = any (select * from (values (1)) as tvc_0 union
1021 select 2);
1022 id select_type table type possible_keys key key_len ref rows filtered Extra
1023 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1024 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
1025 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2 100.00
1026 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1027 4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1028 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
1029 Warnings:
1030 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select "tvc_0"."1" AS "1" from (values (1)) "tvc_0" union /* select#4 */ select 2 AS "2") "__5" join "test"."t1" where "__5"."1" = "test"."t1"."a"
1031 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place
1032 select * from t1
1033 where a = any (select 2 union values (1));
1034 a b
1035 1 2
1036 1 1
1037 2 5
1038 select * from t1
1039 where a = any (select 2 union
1040 select * from (values (1)) as tvc_0);
1041 a b
1042 1 2
1043 1 1
1044 2 5
1045 explain extended select * from t1
1046 where a = any (select 2 union values (1));
1047 id select_type table type possible_keys key key_len ref rows filtered Extra
1048 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1049 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
1050 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1051 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1052 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1053 Warnings:
1054 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select 2 AS "2" union values (1)) "__4" join "test"."t1" where "__4"."2" = "test"."t1"."a"
1055 explain extended select * from t1
1056 where a = any (select 2 union
1057 select * from (values (1)) as tvc_0);
1058 id select_type table type possible_keys key key_len ref rows filtered Extra
1059 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1060 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
1061 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1062 3 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00
1063 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1064 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1065 Warnings:
1066 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select 2 AS "2" union /* select#3 */ select "tvc_0"."1" AS "1" from (values (1)) "tvc_0") "__5" join "test"."t1" where "__5"."2" = "test"."t1"."a"
1067 # ALL-subquery with VALUES structure(s) : simple case
1068 select * from t1
1069 where a = all (values (1));
1070 a b
1071 1 2
1072 1 1
1073 select * from t1
1074 where a = all (select * from (values (1)) as tvc_0);
1075 a b
1076 1 2
1077 1 1
1078 explain extended select * from t1
1079 where a = all (values (1));
1080 id select_type table type possible_keys key key_len ref rows filtered Extra
1081 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1082 3 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
1083 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1084 Warnings:
1085 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") <> "tvc_0"."1")))))
1086 explain extended select * from t1
1087 where a = all (select * from (values (1)) as tvc_0);
1088 id select_type table type possible_keys key key_len ref rows filtered Extra
1089 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1090 2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where
1091 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1092 Warnings:
1093 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") <> "tvc_0"."1")))))
1094 # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place
1095 select * from t1
1096 where a = all (values (1) union select 1);
1097 a b
1098 1 2
1099 1 1
1100 select * from t1
1101 where a = all (select * from (values (1)) as tvc_0 union
1102 select 1);
1103 a b
1104 1 2
1105 1 1
1106 explain extended select * from t1
1107 where a = all (values (1) union select 1);
1108 id select_type table type possible_keys key key_len ref rows filtered Extra
1109 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1110 4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
1111 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1112 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1113 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1114 Warnings:
1115 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#4 */ select "__4"."1" from (values (1) union /* select#3 */ select 1 AS "1") "__4" where trigcond(<cache>("test"."t1"."a") <> "__4"."1")))))
1116 explain extended select * from t1
1117 where a = all (select * from (values (1)) as tvc_0 union
1118 select 1);
1119 id select_type table type possible_keys key key_len ref rows filtered Extra
1120 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1121 5 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
1122 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2 100.00
1123 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1124 4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1125 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
1126 Warnings:
1127 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#5 */ select "__5"."1" from (/* select#2 */ select "tvc_0"."1" AS "1" from (values (1)) "tvc_0" union /* select#4 */ select 1 AS "1") "__5" where trigcond(<cache>("test"."t1"."a") <> "__5"."1")))))
1128 # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place
1129 select * from t1
1130 where a = any (select 1 union values (1));
1131 a b
1132 1 2
1133 1 1
1134 select * from t1
1135 where a = any (select 1 union
1136 select * from (values (1)) as tvc_0);
1137 a b
1138 1 2
1139 1 1
1140 explain extended select * from t1
1141 where a = any (select 1 union values (1));
1142 id select_type table type possible_keys key key_len ref rows filtered Extra
1143 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1144 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
1145 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1146 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1147 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1148 Warnings:
1149 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select 1 AS "1" union values (1)) "__4" join "test"."t1" where "__4"."1" = "test"."t1"."a"
1150 explain extended select * from t1
1151 where a = any (select 1 union
1152 select * from (values (1)) as tvc_0);
1153 id select_type table type possible_keys key key_len ref rows filtered Extra
1154 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
1155 1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
1156 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1157 3 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00
1158 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1159 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1160 Warnings:
1161 Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (/* select#2 */ select 1 AS "1" union /* select#3 */ select "tvc_0"."1" AS "1" from (values (1)) "tvc_0") "__5" join "test"."t1" where "__5"."1" = "test"."t1"."a"
1162 # prepare statement that uses VALUES structure(s): single VALUES structure
1163 prepare stmt1 from '
1164 values (1,2);
1165 ';
1166 execute stmt1;
1167 1 2
1168 1 2
1169 execute stmt1;
1170 1 2
1171 1 2
1172 deallocate prepare stmt1;
1173 # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s)
1174 prepare stmt1 from '
1175 select 1,2
1176 union
1177 values (1,2),(3,4);
1178 ';
1179 execute stmt1;
1180 1 2
1181 1 2
1182 3 4
1183 execute stmt1;
1184 1 2
1185 1 2
1186 3 4
1187 deallocate prepare stmt1;
1188 prepare stmt1 from '
1189 values (1,2),(3,4)
1190 union
1191 select 1,2;
1192 ';
1193 execute stmt1;
1194 1 2
1195 1 2
1196 3 4
1197 execute stmt1;
1198 1 2
1199 1 2
1200 3 4
1201 deallocate prepare stmt1;
1202 prepare stmt1 from '
1203 select 1,2
1204 union
1205 values (3,4)
1206 union
1207 values (1,2);
1208 ';
1209 execute stmt1;
1210 1 2
1211 1 2
1212 3 4
1213 execute stmt1;
1214 1 2
1215 1 2
1216 3 4
1217 deallocate prepare stmt1;
1218 prepare stmt1 from '
1219 values (5,6)
1220 union
1221 values (1,2),(3,4);
1222 ';
1223 execute stmt1;
1224 5 6
1225 5 6
1226 1 2
1227 3 4
1228 execute stmt1;
1229 5 6
1230 5 6
1231 1 2
1232 3 4
1233 deallocate prepare stmt1;
1234 # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s)
1235 prepare stmt1 from '
1236 select 1,2
1237 union
1238 values (1,2),(3,4);
1239 ';
1240 execute stmt1;
1241 1 2
1242 1 2
1243 3 4
1244 execute stmt1;
1245 1 2
1246 1 2
1247 3 4
1248 deallocate prepare stmt1;
1249 prepare stmt1 from '
1250 values (1,2),(3,4)
1251 union all
1252 select 1,2;
1253 ';
1254 execute stmt1;
1255 1 2
1256 1 2
1257 3 4
1258 1 2
1259 execute stmt1;
1260 1 2
1261 1 2
1262 3 4
1263 1 2
1264 deallocate prepare stmt1;
1265 prepare stmt1 from '
1266 select 1,2
1267 union all
1268 values (3,4)
1269 union all
1270 values (1,2);
1271 ';
1272 execute stmt1;
1273 1 2
1274 1 2
1275 3 4
1276 1 2
1277 execute stmt1;
1278 1 2
1279 1 2
1280 3 4
1281 1 2
1282 deallocate prepare stmt1;
1283 prepare stmt1 from '
1284 values (1,2)
1285 union all
1286 values (1,2),(3,4);
1287 ';
1288 execute stmt1;
1289 1 2
1290 1 2
1291 1 2
1292 3 4
1293 execute stmt1;
1294 1 2
1295 1 2
1296 1 2
1297 3 4
1298 deallocate prepare stmt1;
1299 # explain query that uses VALUES structure(s): single VALUES structure
1300 explain
1301 values (1,2);
1302 id select_type table type possible_keys key key_len ref rows Extra
1303 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
1304 explain format=json
1305 values (1,2);
1306 EXPLAIN
1307 {
1308 "query_block": {
1309 "union_result": {
1310 "query_specifications": [
1311 {
1312 "query_block": {
1313 "select_id": 1,
1314 "table": {
1315 "message": "No tables used"
1316 }
1317 }
1318 }
1319 ]
1320 }
1321 }
1322 }
1323 # explain query that uses VALUES structure(s): UNION with VALUES structure(s)
1324 explain
1325 select 1,2
1326 union
1327 values (1,2),(3,4);
1328 id select_type table type possible_keys key key_len ref rows Extra
1329 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
1330 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1331 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1332 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
1333 explain
1334 values (1,2),(3,4)
1335 union
1336 select 1,2;
1337 id select_type table type possible_keys key key_len ref rows Extra
1338 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
1339 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1340 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1341 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
1342 explain
1343 values (5,6)
1344 union
1345 values (1,2),(3,4);
1346 id select_type table type possible_keys key key_len ref rows Extra
1347 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
1348 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1349 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1350 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
1351 explain format=json
1352 select 1,2
1353 union
1354 values (1,2),(3,4);
1355 EXPLAIN
1356 {
1357 "query_block": {
1358 "select_id": 1,
1359 "cost": 0.010089369,
1360 "nested_loop": [
1361 {
1362 "table": {
1363 "table_name": "<derived2>",
1364 "access_type": "ALL",
1365 "loops": 1,
1366 "rows": 2,
1367 "cost": 0.010089369,
1368 "filtered": 100,
1369 "materialized": {
1370 "query_block": {
1371 "union_result": {
1372 "table_name": "<union2,3>",
1373 "access_type": "ALL",
1374 "query_specifications": [
1375 {
1376 "query_block": {
1377 "select_id": 2,
1378 "table": {
1379 "message": "No tables used"
1380 }
1381 }
1382 },
1383 {
1384 "query_block": {
1385 "select_id": 3,
1386 "operation": "UNION",
1387 "table": {
1388 "message": "No tables used"
1389 }
1390 }
1391 }
1392 ]
1393 }
1394 }
1395 }
1396 }
1397 }
1398 ]
1399 }
1400 }
1401 explain format=json
1402 values (1,2),(3,4)
1403 union
1404 select 1,2;
1405 EXPLAIN
1406 {
1407 "query_block": {
1408 "select_id": 1,
1409 "cost": 0.010089369,
1410 "nested_loop": [
1411 {
1412 "table": {
1413 "table_name": "<derived2>",
1414 "access_type": "ALL",
1415 "loops": 1,
1416 "rows": 2,
1417 "cost": 0.010089369,
1418 "filtered": 100,
1419 "materialized": {
1420 "query_block": {
1421 "union_result": {
1422 "table_name": "<union2,3>",
1423 "access_type": "ALL",
1424 "query_specifications": [
1425 {
1426 "query_block": {
1427 "select_id": 2,
1428 "table": {
1429 "message": "No tables used"
1430 }
1431 }
1432 },
1433 {
1434 "query_block": {
1435 "select_id": 3,
1436 "operation": "UNION",
1437 "table": {
1438 "message": "No tables used"
1439 }
1440 }
1441 }
1442 ]
1443 }
1444 }
1445 }
1446 }
1447 }
1448 ]
1449 }
1450 }
1451 explain format=json
1452 values (5,6)
1453 union
1454 values (1,2),(3,4);
1455 EXPLAIN
1456 {
1457 "query_block": {
1458 "select_id": 1,
1459 "cost": 0.010134054,
1460 "nested_loop": [
1461 {
1462 "table": {
1463 "table_name": "<derived2>",
1464 "access_type": "ALL",
1465 "loops": 1,
1466 "rows": 3,
1467 "cost": 0.010134054,
1468 "filtered": 100,
1469 "materialized": {
1470 "query_block": {
1471 "union_result": {
1472 "table_name": "<union2,3>",
1473 "access_type": "ALL",
1474 "query_specifications": [
1475 {
1476 "query_block": {
1477 "select_id": 2,
1478 "table": {
1479 "message": "No tables used"
1480 }
1481 }
1482 },
1483 {
1484 "query_block": {
1485 "select_id": 3,
1486 "operation": "UNION",
1487 "table": {
1488 "message": "No tables used"
1489 }
1490 }
1491 }
1492 ]
1493 }
1494 }
1495 }
1496 }
1497 }
1498 ]
1499 }
1500 }
1501 explain
1502 select 1,2
1503 union
1504 values (3,4)
1505 union
1506 values (1,2);
1507 id select_type table type possible_keys key key_len ref rows Extra
1508 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 3
1509 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2
1510 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1511 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1512 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
1513 5 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1514 NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL
1515 explain format=json
1516 select 1,2
1517 union
1518 values (3,4)
1519 union
1520 values (1,2);
1521 EXPLAIN
1522 {
1523 "query_block": {
1524 "select_id": 1,
1525 "cost": 0.010134054,
1526 "nested_loop": [
1527 {
1528 "table": {
1529 "table_name": "<derived4>",
1530 "access_type": "ALL",
1531 "loops": 1,
1532 "rows": 3,
1533 "cost": 0.010134054,
1534 "filtered": 100,
1535 "materialized": {
1536 "query_block": {
1537 "union_result": {
1538 "table_name": "<union4,5>",
1539 "access_type": "ALL",
1540 "query_specifications": [
1541 {
1542 "query_block": {
1543 "select_id": 4,
1544 "cost": 0.010089369,
1545 "nested_loop": [
1546 {
1547 "table": {
1548 "table_name": "<derived2>",
1549 "access_type": "ALL",
1550 "loops": 1,
1551 "rows": 2,
1552 "cost": 0.010089369,
1553 "filtered": 100,
1554 "materialized": {
1555 "query_block": {
1556 "union_result": {
1557 "table_name": "<union2,3>",
1558 "access_type": "ALL",
1559 "query_specifications": [
1560 {
1561 "query_block": {
1562 "select_id": 2,
1563 "table": {
1564 "message": "No tables used"
1565 }
1566 }
1567 },
1568 {
1569 "query_block": {
1570 "select_id": 3,
1571 "operation": "UNION",
1572 "table": {
1573 "message": "No tables used"
1574 }
1575 }
1576 }
1577 ]
1578 }
1579 }
1580 }
1581 }
1582 }
1583 ]
1584 }
1585 },
1586 {
1587 "query_block": {
1588 "select_id": 5,
1589 "operation": "UNION",
1590 "table": {
1591 "message": "No tables used"
1592 }
1593 }
1594 }
1595 ]
1596 }
1597 }
1598 }
1599 }
1600 }
1601 ]
1602 }
1603 }
1604 # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
1605 explain
1606 select 1,2
1607 union
1608 values (1,2),(3,4);
1609 id select_type table type possible_keys key key_len ref rows Extra
1610 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
1611 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1612 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1613 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
1614 explain
1615 values (1,2),(3,4)
1616 union all
1617 select 1,2;
1618 id select_type table type possible_keys key key_len ref rows Extra
1619 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
1620 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1621 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1622 explain
1623 values (1,2)
1624 union all
1625 values (1,2),(3,4);
1626 id select_type table type possible_keys key key_len ref rows Extra
1627 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
1628 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1629 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1630 explain format=json
1631 values (1,2),(3,4)
1632 union all
1633 select 1,2;
1634 EXPLAIN
1635 {
1636 "query_block": {
1637 "select_id": 1,
1638 "cost": 0.010089369,
1639 "nested_loop": [
1640 {
1641 "table": {
1642 "table_name": "<derived2>",
1643 "access_type": "ALL",
1644 "loops": 1,
1645 "rows": 2,
1646 "cost": 0.010089369,
1647 "filtered": 100,
1648 "materialized": {
1649 "query_block": {
1650 "union_result": {
1651 "query_specifications": [
1652 {
1653 "query_block": {
1654 "select_id": 2,
1655 "table": {
1656 "message": "No tables used"
1657 }
1658 }
1659 },
1660 {
1661 "query_block": {
1662 "select_id": 3,
1663 "operation": "UNION",
1664 "table": {
1665 "message": "No tables used"
1666 }
1667 }
1668 }
1669 ]
1670 }
1671 }
1672 }
1673 }
1674 }
1675 ]
1676 }
1677 }
1678 explain format=json
1679 select 1,2
1680 union
1681 values (1,2),(3,4);
1682 EXPLAIN
1683 {
1684 "query_block": {
1685 "select_id": 1,
1686 "cost": 0.010089369,
1687 "nested_loop": [
1688 {
1689 "table": {
1690 "table_name": "<derived2>",
1691 "access_type": "ALL",
1692 "loops": 1,
1693 "rows": 2,
1694 "cost": 0.010089369,
1695 "filtered": 100,
1696 "materialized": {
1697 "query_block": {
1698 "union_result": {
1699 "table_name": "<union2,3>",
1700 "access_type": "ALL",
1701 "query_specifications": [
1702 {
1703 "query_block": {
1704 "select_id": 2,
1705 "table": {
1706 "message": "No tables used"
1707 }
1708 }
1709 },
1710 {
1711 "query_block": {
1712 "select_id": 3,
1713 "operation": "UNION",
1714 "table": {
1715 "message": "No tables used"
1716 }
1717 }
1718 }
1719 ]
1720 }
1721 }
1722 }
1723 }
1724 }
1725 ]
1726 }
1727 }
1728 explain format=json
1729 values (1,2)
1730 union all
1731 values (1,2),(3,4);
1732 EXPLAIN
1733 {
1734 "query_block": {
1735 "select_id": 1,
1736 "cost": 0.010134054,
1737 "nested_loop": [
1738 {
1739 "table": {
1740 "table_name": "<derived2>",
1741 "access_type": "ALL",
1742 "loops": 1,
1743 "rows": 3,
1744 "cost": 0.010134054,
1745 "filtered": 100,
1746 "materialized": {
1747 "query_block": {
1748 "union_result": {
1749 "query_specifications": [
1750 {
1751 "query_block": {
1752 "select_id": 2,
1753 "table": {
1754 "message": "No tables used"
1755 }
1756 }
1757 },
1758 {
1759 "query_block": {
1760 "select_id": 3,
1761 "operation": "UNION",
1762 "table": {
1763 "message": "No tables used"
1764 }
1765 }
1766 }
1767 ]
1768 }
1769 }
1770 }
1771 }
1772 }
1773 ]
1774 }
1775 }
1776 explain
1777 select 1,2
1778 union all
1779 values (3,4)
1780 union all
1781 values (1,2);
1782 id select_type table type possible_keys key key_len ref rows Extra
1783 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 3
1784 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2
1785 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
1786 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1787 5 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
1788 explain format=json
1789 select 1,2
1790 union all
1791 values (3,4)
1792 union all
1793 values (1,2);
1794 EXPLAIN
1795 {
1796 "query_block": {
1797 "select_id": 1,
1798 "cost": 0.010134054,
1799 "nested_loop": [
1800 {
1801 "table": {
1802 "table_name": "<derived4>",
1803 "access_type": "ALL",
1804 "loops": 1,
1805 "rows": 3,
1806 "cost": 0.010134054,
1807 "filtered": 100,
1808 "materialized": {
1809 "query_block": {
1810 "union_result": {
1811 "query_specifications": [
1812 {
1813 "query_block": {
1814 "select_id": 4,
1815 "cost": 0.010089369,
1816 "nested_loop": [
1817 {
1818 "table": {
1819 "table_name": "<derived2>",
1820 "access_type": "ALL",
1821 "loops": 1,
1822 "rows": 2,
1823 "cost": 0.010089369,
1824 "filtered": 100,
1825 "materialized": {
1826 "query_block": {
1827 "union_result": {
1828 "query_specifications": [
1829 {
1830 "query_block": {
1831 "select_id": 2,
1832 "table": {
1833 "message": "No tables used"
1834 }
1835 }
1836 },
1837 {
1838 "query_block": {
1839 "select_id": 3,
1840 "operation": "UNION",
1841 "table": {
1842 "message": "No tables used"
1843 }
1844 }
1845 }
1846 ]
1847 }
1848 }
1849 }
1850 }
1851 }
1852 ]
1853 }
1854 },
1855 {
1856 "query_block": {
1857 "select_id": 5,
1858 "operation": "UNION",
1859 "table": {
1860 "message": "No tables used"
1861 }
1862 }
1863 }
1864 ]
1865 }
1866 }
1867 }
1868 }
1869 }
1870 ]
1871 }
1872 }
1873 # analyze query that uses VALUES structure(s): single VALUES structure
1874 analyze
1875 values (1,2);
1876 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1877 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1878 analyze format=json
1879 values (1,2);
1880 ANALYZE
1881 {
1882 "query_optimization": {
1883 "r_total_time_ms": "REPLACED"
1884 },
1885 "query_block": {
1886 "union_result": {
1887 "query_specifications": [
1888 {
1889 "query_block": {
1890 "select_id": 1,
1891 "table": {
1892 "message": "No tables used"
1893 }
1894 }
1895 }
1896 ]
1897 }
1898 }
1899 }
1900 # analyze query that uses VALUES structure(s): UNION with VALUES structure(s)
1901 analyze
1902 select 1,2
1903 union
1904 values (1,2),(3,4);
1905 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1906 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 2.00 100.00 100.00
1907 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1908 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1909 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL
1910 analyze
1911 values (1,2),(3,4)
1912 union
1913 select 1,2;
1914 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1915 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 2.00 100.00 100.00
1916 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1917 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1918 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL
1919 analyze
1920 values (5,6)
1921 union
1922 values (1,2),(3,4);
1923 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1924 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 3.00 100.00 100.00
1925 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1926 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1927 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 3.00 NULL NULL
1928 analyze format=json
1929 select 1,2
1930 union
1931 values (1,2),(3,4);
1932 ANALYZE
1933 {
1934 "query_optimization": {
1935 "r_total_time_ms": "REPLACED"
1936 },
1937 "query_block": {
1938 "select_id": 1,
1939 "cost": "REPLACED",
1940 "r_loops": 1,
1941 "r_total_time_ms": "REPLACED",
1942 "nested_loop": [
1943 {
1944 "table": {
1945 "table_name": "<derived2>",
1946 "access_type": "ALL",
1947 "loops": 1,
1948 "r_loops": 1,
1949 "rows": 3,
1950 "r_rows": 2,
1951 "cost": "REPLACED",
1952 "r_table_time_ms": "REPLACED",
1953 "r_other_time_ms": "REPLACED",
1954 "filtered": 100,
1955 "r_total_filtered": 100,
1956 "r_filtered": 100,
1957 "materialized": {
1958 "query_block": {
1959 "union_result": {
1960 "table_name": "<union2,3>",
1961 "access_type": "ALL",
1962 "r_loops": 1,
1963 "r_rows": 2,
1964 "query_specifications": [
1965 {
1966 "query_block": {
1967 "select_id": 2,
1968 "table": {
1969 "message": "No tables used"
1970 }
1971 }
1972 },
1973 {
1974 "query_block": {
1975 "select_id": 3,
1976 "operation": "UNION",
1977 "table": {
1978 "message": "No tables used"
1979 }
1980 }
1981 }
1982 ]
1983 }
1984 }
1985 }
1986 }
1987 }
1988 ]
1989 }
1990 }
1991 analyze format=json
1992 values (1,2),(3,4)
1993 union
1994 select 1,2;
1995 ANALYZE
1996 {
1997 "query_optimization": {
1998 "r_total_time_ms": "REPLACED"
1999 },
2000 "query_block": {
2001 "select_id": 1,
2002 "cost": "REPLACED",
2003 "r_loops": 1,
2004 "r_total_time_ms": "REPLACED",
2005 "nested_loop": [
2006 {
2007 "table": {
2008 "table_name": "<derived2>",
2009 "access_type": "ALL",
2010 "loops": 1,
2011 "r_loops": 1,
2012 "rows": 3,
2013 "r_rows": 2,
2014 "cost": "REPLACED",
2015 "r_table_time_ms": "REPLACED",
2016 "r_other_time_ms": "REPLACED",
2017 "filtered": 100,
2018 "r_total_filtered": 100,
2019 "r_filtered": 100,
2020 "materialized": {
2021 "query_block": {
2022 "union_result": {
2023 "table_name": "<union2,3>",
2024 "access_type": "ALL",
2025 "r_loops": 1,
2026 "r_rows": 2,
2027 "query_specifications": [
2028 {
2029 "query_block": {
2030 "select_id": 2,
2031 "table": {
2032 "message": "No tables used"
2033 }
2034 }
2035 },
2036 {
2037 "query_block": {
2038 "select_id": 3,
2039 "operation": "UNION",
2040 "table": {
2041 "message": "No tables used"
2042 }
2043 }
2044 }
2045 ]
2046 }
2047 }
2048 }
2049 }
2050 }
2051 ]
2052 }
2053 }
2054 analyze format=json
2055 values (5,6)
2056 union
2057 values (1,2),(3,4);
2058 ANALYZE
2059 {
2060 "query_optimization": {
2061 "r_total_time_ms": "REPLACED"
2062 },
2063 "query_block": {
2064 "select_id": 1,
2065 "cost": "REPLACED",
2066 "r_loops": 1,
2067 "r_total_time_ms": "REPLACED",
2068 "nested_loop": [
2069 {
2070 "table": {
2071 "table_name": "<derived2>",
2072 "access_type": "ALL",
2073 "loops": 1,
2074 "r_loops": 1,
2075 "rows": 3,
2076 "r_rows": 3,
2077 "cost": "REPLACED",
2078 "r_table_time_ms": "REPLACED",
2079 "r_other_time_ms": "REPLACED",
2080 "filtered": 100,
2081 "r_total_filtered": 100,
2082 "r_filtered": 100,
2083 "materialized": {
2084 "query_block": {
2085 "union_result": {
2086 "table_name": "<union2,3>",
2087 "access_type": "ALL",
2088 "r_loops": 1,
2089 "r_rows": 3,
2090 "query_specifications": [
2091 {
2092 "query_block": {
2093 "select_id": 2,
2094 "table": {
2095 "message": "No tables used"
2096 }
2097 }
2098 },
2099 {
2100 "query_block": {
2101 "select_id": 3,
2102 "operation": "UNION",
2103 "table": {
2104 "message": "No tables used"
2105 }
2106 }
2107 }
2108 ]
2109 }
2110 }
2111 }
2112 }
2113 }
2114 ]
2115 }
2116 }
2117 analyze
2118 select 1,2
2119 union
2120 values (3,4)
2121 union
2122 values (1,2);
2123 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
2124 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 3 2.00 100.00 100.00
2125 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 2.00 100.00 100.00
2126 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2127 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2128 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL
2129 5 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2130 NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL
2131 analyze format=json
2132 select 1,2
2133 union
2134 values (3,4)
2135 union
2136 values (1,2);
2137 ANALYZE
2138 {
2139 "query_optimization": {
2140 "r_total_time_ms": "REPLACED"
2141 },
2142 "query_block": {
2143 "select_id": 1,
2144 "cost": "REPLACED",
2145 "r_loops": 1,
2146 "r_total_time_ms": "REPLACED",
2147 "nested_loop": [
2148 {
2149 "table": {
2150 "table_name": "<derived4>",
2151 "access_type": "ALL",
2152 "loops": 1,
2153 "r_loops": 1,
2154 "rows": 3,
2155 "r_rows": 2,
2156 "cost": "REPLACED",
2157 "r_table_time_ms": "REPLACED",
2158 "r_other_time_ms": "REPLACED",
2159 "filtered": 100,
2160 "r_total_filtered": 100,
2161 "r_filtered": 100,
2162 "materialized": {
2163 "query_block": {
2164 "union_result": {
2165 "table_name": "<union4,5>",
2166 "access_type": "ALL",
2167 "r_loops": 1,
2168 "r_rows": 2,
2169 "query_specifications": [
2170 {
2171 "query_block": {
2172 "select_id": 4,
2173 "cost": "REPLACED",
2174 "r_loops": 1,
2175 "r_total_time_ms": "REPLACED",
2176 "nested_loop": [
2177 {
2178 "table": {
2179 "table_name": "<derived2>",
2180 "access_type": "ALL",
2181 "loops": 1,
2182 "r_loops": 1,
2183 "rows": 2,
2184 "r_rows": 2,
2185 "cost": "REPLACED",
2186 "r_table_time_ms": "REPLACED",
2187 "r_other_time_ms": "REPLACED",
2188 "filtered": 100,
2189 "r_total_filtered": 100,
2190 "r_filtered": 100,
2191 "materialized": {
2192 "query_block": {
2193 "union_result": {
2194 "table_name": "<union2,3>",
2195 "access_type": "ALL",
2196 "r_loops": 1,
2197 "r_rows": 2,
2198 "query_specifications": [
2199 {
2200 "query_block": {
2201 "select_id": 2,
2202 "table": {
2203 "message": "No tables used"
2204 }
2205 }
2206 },
2207 {
2208 "query_block": {
2209 "select_id": 3,
2210 "operation": "UNION",
2211 "table": {
2212 "message": "No tables used"
2213 }
2214 }
2215 }
2216 ]
2217 }
2218 }
2219 }
2220 }
2221 }
2222 ]
2223 }
2224 },
2225 {
2226 "query_block": {
2227 "select_id": 5,
2228 "operation": "UNION",
2229 "table": {
2230 "message": "No tables used"
2231 }
2232 }
2233 }
2234 ]
2235 }
2236 }
2237 }
2238 }
2239 }
2240 ]
2241 }
2242 }
2243 # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
2244 analyze
2245 select 1,2
2246 union
2247 values (1,2),(3,4);
2248 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
2249 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 2.00 100.00 100.00
2250 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2251 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2252 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL
2253 analyze
2254 values (1,2),(3,4)
2255 union all
2256 select 1,2;
2257 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
2258 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 3.00 100.00 100.00
2259 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2260 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2261 analyze
2262 values (1,2)
2263 union all
2264 values (1,2),(3,4);
2265 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
2266 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 3.00 100.00 100.00
2267 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2268 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2269 analyze format=json
2270 values (1,2),(3,4)
2271 union all
2272 select 1,2;
2273 ANALYZE
2274 {
2275 "query_optimization": {
2276 "r_total_time_ms": "REPLACED"
2277 },
2278 "query_block": {
2279 "select_id": 1,
2280 "cost": "REPLACED",
2281 "r_loops": 1,
2282 "r_total_time_ms": "REPLACED",
2283 "nested_loop": [
2284 {
2285 "table": {
2286 "table_name": "<derived2>",
2287 "access_type": "ALL",
2288 "loops": 1,
2289 "r_loops": 1,
2290 "rows": 3,
2291 "r_rows": 3,
2292 "cost": "REPLACED",
2293 "r_table_time_ms": "REPLACED",
2294 "r_other_time_ms": "REPLACED",
2295 "filtered": 100,
2296 "r_total_filtered": 100,
2297 "r_filtered": 100,
2298 "materialized": {
2299 "query_block": {
2300 "union_result": {
2301 "query_specifications": [
2302 {
2303 "query_block": {
2304 "select_id": 2,
2305 "table": {
2306 "message": "No tables used"
2307 }
2308 }
2309 },
2310 {
2311 "query_block": {
2312 "select_id": 3,
2313 "operation": "UNION",
2314 "table": {
2315 "message": "No tables used"
2316 }
2317 }
2318 }
2319 ]
2320 }
2321 }
2322 }
2323 }
2324 }
2325 ]
2326 }
2327 }
2328 analyze format=json
2329 select 1,2
2330 union
2331 values (1,2),(3,4);
2332 ANALYZE
2333 {
2334 "query_optimization": {
2335 "r_total_time_ms": "REPLACED"
2336 },
2337 "query_block": {
2338 "select_id": 1,
2339 "cost": "REPLACED",
2340 "r_loops": 1,
2341 "r_total_time_ms": "REPLACED",
2342 "nested_loop": [
2343 {
2344 "table": {
2345 "table_name": "<derived2>",
2346 "access_type": "ALL",
2347 "loops": 1,
2348 "r_loops": 1,
2349 "rows": 3,
2350 "r_rows": 2,
2351 "cost": "REPLACED",
2352 "r_table_time_ms": "REPLACED",
2353 "r_other_time_ms": "REPLACED",
2354 "filtered": 100,
2355 "r_total_filtered": 100,
2356 "r_filtered": 100,
2357 "materialized": {
2358 "query_block": {
2359 "union_result": {
2360 "table_name": "<union2,3>",
2361 "access_type": "ALL",
2362 "r_loops": 1,
2363 "r_rows": 2,
2364 "query_specifications": [
2365 {
2366 "query_block": {
2367 "select_id": 2,
2368 "table": {
2369 "message": "No tables used"
2370 }
2371 }
2372 },
2373 {
2374 "query_block": {
2375 "select_id": 3,
2376 "operation": "UNION",
2377 "table": {
2378 "message": "No tables used"
2379 }
2380 }
2381 }
2382 ]
2383 }
2384 }
2385 }
2386 }
2387 }
2388 ]
2389 }
2390 }
2391 analyze format=json
2392 values (1,2)
2393 union all
2394 values (1,2),(3,4);
2395 ANALYZE
2396 {
2397 "query_optimization": {
2398 "r_total_time_ms": "REPLACED"
2399 },
2400 "query_block": {
2401 "select_id": 1,
2402 "cost": "REPLACED",
2403 "r_loops": 1,
2404 "r_total_time_ms": "REPLACED",
2405 "nested_loop": [
2406 {
2407 "table": {
2408 "table_name": "<derived2>",
2409 "access_type": "ALL",
2410 "loops": 1,
2411 "r_loops": 1,
2412 "rows": 3,
2413 "r_rows": 3,
2414 "cost": "REPLACED",
2415 "r_table_time_ms": "REPLACED",
2416 "r_other_time_ms": "REPLACED",
2417 "filtered": 100,
2418 "r_total_filtered": 100,
2419 "r_filtered": 100,
2420 "materialized": {
2421 "query_block": {
2422 "union_result": {
2423 "query_specifications": [
2424 {
2425 "query_block": {
2426 "select_id": 2,
2427 "table": {
2428 "message": "No tables used"
2429 }
2430 }
2431 },
2432 {
2433 "query_block": {
2434 "select_id": 3,
2435 "operation": "UNION",
2436 "table": {
2437 "message": "No tables used"
2438 }
2439 }
2440 }
2441 ]
2442 }
2443 }
2444 }
2445 }
2446 }
2447 ]
2448 }
2449 }
2450 analyze
2451 select 1,2
2452 union all
2453 values (3,4)
2454 union all
2455 values (1,2);
2456 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
2457 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 3 3.00 100.00 100.00
2458 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 2.00 100.00 100.00
2459 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2460 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2461 5 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2462 analyze format=json
2463 select 1,2
2464 union all
2465 values (3,4)
2466 union all
2467 values (1,2);
2468 ANALYZE
2469 {
2470 "query_optimization": {
2471 "r_total_time_ms": "REPLACED"
2472 },
2473 "query_block": {
2474 "select_id": 1,
2475 "cost": "REPLACED",
2476 "r_loops": 1,
2477 "r_total_time_ms": "REPLACED",
2478 "nested_loop": [
2479 {
2480 "table": {
2481 "table_name": "<derived4>",
2482 "access_type": "ALL",
2483 "loops": 1,
2484 "r_loops": 1,
2485 "rows": 3,
2486 "r_rows": 3,
2487 "cost": "REPLACED",
2488 "r_table_time_ms": "REPLACED",
2489 "r_other_time_ms": "REPLACED",
2490 "filtered": 100,
2491 "r_total_filtered": 100,
2492 "r_filtered": 100,
2493 "materialized": {
2494 "query_block": {
2495 "union_result": {
2496 "query_specifications": [
2497 {
2498 "query_block": {
2499 "select_id": 4,
2500 "cost": "REPLACED",
2501 "r_loops": 1,
2502 "r_total_time_ms": "REPLACED",
2503 "nested_loop": [
2504 {
2505 "table": {
2506 "table_name": "<derived2>",
2507 "access_type": "ALL",
2508 "loops": 1,
2509 "r_loops": 1,
2510 "rows": 2,
2511 "r_rows": 2,
2512 "cost": "REPLACED",
2513 "r_table_time_ms": "REPLACED",
2514 "r_other_time_ms": "REPLACED",
2515 "filtered": 100,
2516 "r_total_filtered": 100,
2517 "r_filtered": 100,
2518 "materialized": {
2519 "query_block": {
2520 "union_result": {
2521 "query_specifications": [
2522 {
2523 "query_block": {
2524 "select_id": 2,
2525 "table": {
2526 "message": "No tables used"
2527 }
2528 }
2529 },
2530 {
2531 "query_block": {
2532 "select_id": 3,
2533 "operation": "UNION",
2534 "table": {
2535 "message": "No tables used"
2536 }
2537 }
2538 }
2539 ]
2540 }
2541 }
2542 }
2543 }
2544 }
2545 ]
2546 }
2547 },
2548 {
2549 "query_block": {
2550 "select_id": 5,
2551 "operation": "UNION",
2552 "table": {
2553 "message": "No tables used"
2554 }
2555 }
2556 }
2557 ]
2558 }
2559 }
2560 }
2561 }
2562 }
2563 ]
2564 }
2565 }
2566 # different number of values in TVC
2567 values (1,2),(3,4,5);
2568 ERROR HY000: The used table value constructor has a different number of values
2569 # illegal parameter data types in TVC
2570 values (1,point(1,1)),(1,1);
2571 ERROR HY000: Illegal parameter data types point and int for operation 'TABLE VALUE CONSTRUCTOR'
2572 values (1,point(1,1)+1);
2573 ERROR HY000: Illegal parameter data types point and int for operation '+'
2574 # field reference in TVC
2575 select * from (values (1), (b), (2)) as new_tvc;
2576 ERROR HY000: Field reference 'b' can't be used in table value constructor
2577 select * from (values (1), (t1.b), (2)) as new_tvc;
2578 ERROR HY000: Field reference 't1.b' can't be used in table value constructor
2579 drop table t1;
2580 #
2581 # MDEV-15940: cursor over TVC
2582 #
2583 DECLARE
2584 v INT;
2585 CURSOR cur IS VALUES(7);
2586 BEGIN
2587 OPEN cur;
2588 FETCH cur INTO v;
2589 SELECT v;
2590 END;
2591 |
2592 v
2593 7
2594 DECLARE
2595 v INT DEFAULT 0;
2596 BEGIN
2597 FOR a IN (VALUES (7)) LOOP
2598 SET v = v + 1;
2599 END LOOP;
2600 SELECT v;
2601 END;
2602 |
2603 v
2604 1
2605 #
2606 # MDEV-16038: empty row in TVC
2607 #
2608 with t as (values (),()) select 1 from t;
2609 ERROR HY000: Row with no elements is not allowed in table value constructor in this context
2610 #
2611 # MDEV-17017: TVC in derived table
2612 #
2613 create table t1 (a int);
2614 insert into t1 values (9), (3), (2);
2615 select * from (values (7), (5), (8), (1), (3), (8), (1)) t;
2616 7
2617 7
2618 5
2619 8
2620 1
2621 3
2622 8
2623 1
2624 explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t;
2625 id select_type table type possible_keys key key_len ref rows Extra
2626 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7
2627 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
2628 select * from (values (1,11), (7,77), (3,31), (4,42)) t;
2629 1 11
2630 1 11
2631 7 77
2632 3 31
2633 4 42
2634 explain select * from (values (1,11), (7,77), (3,31), (4,42)) t;
2635 id select_type table type possible_keys key key_len ref rows Extra
2636 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
2637 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
2638 select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;
2639 7
2640 7
2641 5
2642 8
2643 1
2644 3
2645 explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;
2646 id select_type table type possible_keys key key_len ref rows Extra
2647 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7
2648 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
2649 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
2650 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
2651 select * from (values (7), (5), (8), (1) union select * from t1) t;
2652 7
2653 7
2654 5
2655 8
2656 1
2657 9
2658 3
2659 2
2660 explain select * from (values (7), (5), (8), (1) union select * from t1) t;
2661 id select_type table type possible_keys key key_len ref rows Extra
2662 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7
2663 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
2664 3 UNION t1 ALL NULL NULL NULL NULL 3
2665 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
2666 drop table t1;
2667 #
2668 # MDEV-16930: expression in the first row of TVC specifying derived table
2669 #
2670 SELECT 1 + 1, 2, 'abc';
2671 1 + 1 2 abc
2672 2 2 abc
2673 SELECT * FROM (SELECT 1 + 1, 2, 'abc') t;
2674 1 + 1 2 abc
2675 2 2 abc
2676 WITH cte AS (SELECT 1 + 1, 2, 'abc') SELECT * FROM cte;
2677 1 + 1 2 abc
2678 2 2 abc
2679 SELECT 1 + 1, 2, 'abc' UNION SELECT 3+4, 3, 'abc';
2680 1 + 1 2 abc
2681 2 2 abc
2682 7 3 abc
2683 CREATE VIEW v1 AS SELECT 1 + 1, 2, 'abc';
2684 SELECT * FROM v1;
2685 1 + 1 2 abc
2686 2 2 abc
2687 DROP VIEW v1;
2688 VALUES(1 + 1,2,'abc');
2689 1 + 1 2 abc
2690 2 2 abc
2691 SELECT * FROM (VALUES(1 + 1,2,'abc')) t;
2692 1 + 1 2 abc
2693 2 2 abc
2694 #
2695 # MDEV-17894: tvc with ORDER BY ... LIMIT
2696 #
2697 values (5), (7), (1), (3), (4) limit 2;
2698 5
2699 5
2700 7
2701 explain extended values (5), (7), (1), (3), (4) limit 2;
2702 id select_type table type possible_keys key key_len ref rows filtered Extra
2703 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2704 Warnings:
2705 Note 1003 values (5),(7),(1),(3),(4) limit 2
2706 values (5), (7), (1), (3), (4) limit 2 offset 1;
2707 5
2708 7
2709 1
2710 explain extended values (5), (7), (1), (3), (4) limit 2 offset 1;
2711 id select_type table type possible_keys key key_len ref rows filtered Extra
2712 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2713 Warnings:
2714 Note 1003 values (5),(7),(1),(3),(4) limit 1,2
2715 values (5), (7), (1), (3), (4) order by 1 limit 2;
2716 5
2717 1
2718 3
2719 explain extended values (5), (7), (1), (3), (4) order by 1 limit 2;
2720 id select_type table type possible_keys key key_len ref rows filtered Extra
2721 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2722 NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort
2723 Warnings:
2724 Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 2
2725 values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
2726 5
2727 3
2728 4
2729 explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
2730 id select_type table type possible_keys key key_len ref rows filtered Extra
2731 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2732 NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort
2733 Warnings:
2734 Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 1,2
2735 values (5), (7), (1), (3), (4) order by 1;
2736 5
2737 1
2738 3
2739 4
2740 5
2741 7
2742 explain extended values (5), (7), (1), (3), (4) order by 1;
2743 id select_type table type possible_keys key key_len ref rows filtered Extra
2744 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2745 NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort
2746 Warnings:
2747 Note 1003 values (5),(7),(1),(3),(4) order by 1
2748 values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2;
2749 5 90
2750 4 10
2751 7 20
2752 3 50
2753 1 70
2754 5 90
2755 explain extended values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2;
2756 id select_type table type possible_keys key key_len ref rows filtered Extra
2757 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2758 NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort
2759 Warnings:
2760 Note 1003 values (5,90),(7,20),(1,70),(3,50),(4,10) order by 2
2761 select 2 union (values (5), (7), (1), (3), (4) limit 2);
2762 2
2763 2
2764 5
2765 7
2766 explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2);
2767 id select_type table type possible_keys key key_len ref rows filtered Extra
2768 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2769 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2770 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2771 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
2772 Warnings:
2773 Note 1003 /* select#1 */ select "__3"."2" AS "2" from (/* select#2 */ select 2 AS "2" union (values (5),(7),(1),(3),(4) limit 2)) "__3"
2774 select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
2775 2
2776 2
2777 7
2778 1
2779 explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
2780 id select_type table type possible_keys key key_len ref rows filtered Extra
2781 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2782 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2783 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2784 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
2785 Warnings:
2786 Note 1003 /* select#1 */ select "__3"."2" AS "2" from (/* select#2 */ select 2 AS "2" union (values (5),(7),(1),(3),(4) limit 1,2)) "__3"
2787 select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
2788 2
2789 2
2790 1
2791 3
2792 explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
2793 id select_type table type possible_keys key key_len ref rows filtered Extra
2794 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
2795 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2796 4 UNION <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2797 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2798 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
2799 Warnings:
2800 Note 1003 /* select#1 */ select "__3"."2" AS "2" from (/* select#2 */ select 2 AS "2" union (/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)) "__3"
2801 select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
2802 2
2803 2
2804 3
2805 4
2806 explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
2807 id select_type table type possible_keys key key_len ref rows filtered Extra
2808 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00
2809 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2810 4 UNION <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2811 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2812 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
2813 Warnings:
2814 Note 1003 /* select#1 */ select "__3"."2" AS "2" from (/* select#2 */ select 2 AS "2" union (/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,2)) "__3"
2815 (values (5), (7), (1), (3), (4) limit 2) union select 2;
2816 5
2817 5
2818 7
2819 2
2820 explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2;
2821 id select_type table type possible_keys key key_len ref rows filtered Extra
2822 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2823 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2824 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2825 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
2826 Warnings:
2827 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((values (5),(7),(1),(3),(4) limit 2) union /* select#3 */ select 2 AS "2") "__3"
2828 (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
2829 5
2830 7
2831 1
2832 2
2833 explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
2834 id select_type table type possible_keys key key_len ref rows filtered Extra
2835 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2836 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2837 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2838 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
2839 Warnings:
2840 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((values (5),(7),(1),(3),(4) limit 1,2) union /* select#3 */ select 2 AS "2") "__3"
2841 (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
2842 5
2843 1
2844 3
2845 2
2846 explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
2847 id select_type table type possible_keys key key_len ref rows filtered Extra
2848 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
2849 4 DERIVED <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2850 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2851 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2852 NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
2853 Warnings:
2854 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) union /* select#3 */ select 2 AS "2") "__3"
2855 (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
2856 5
2857 3
2858 4
2859 2
2860 explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
2861 id select_type table type possible_keys key key_len ref rows filtered Extra
2862 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00
2863 4 DERIVED <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2864 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2865 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2866 NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
2867 Warnings:
2868 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,2) union /* select#3 */ select 2 AS "2") "__3"
2869 select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
2870 3
2871 3
2872 3
2873 4
2874 explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
2875 id select_type table type possible_keys key key_len ref rows filtered Extra
2876 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2877 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2878 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2879 Warnings:
2880 Note 1003 /* select#1 */ select "__3"."3" AS "3" from (/* select#2 */ select 3 AS "3" union all (values (5),(7),(1),(3),(4) limit 3,2)) "__3"
2881 (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
2882 5
2883 3
2884 4
2885 3
2886 explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
2887 id select_type table type possible_keys key key_len ref rows filtered Extra
2888 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2889 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2890 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2891 Warnings:
2892 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((values (5),(7),(1),(3),(4) limit 3,2) union all /* select#3 */ select 3 AS "3") "__3"
2893 select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
2894 3
2895 3
2896 1
2897 explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
2898 id select_type table type possible_keys key key_len ref rows filtered Extra
2899 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
2900 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2901 4 UNION <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2902 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2903 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
2904 Warnings:
2905 Note 1003 /* select#1 */ select "__3"."3" AS "3" from (/* select#2 */ select 3 AS "3" union all (/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)) "__3"
2906 (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
2907 5
2908 1
2909 3
2910 explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
2911 id select_type table type possible_keys key key_len ref rows filtered Extra
2912 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
2913 4 DERIVED <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2914 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2915 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2916 NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
2917 Warnings:
2918 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) union all /* select#3 */ select 3 AS "3") "__3"
2919 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
2920 union
2921 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
2922 5
2923 7
2924 1
2925 3
2926 explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
2927 union
2928 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
2929 id select_type table type possible_keys key key_len ref rows filtered Extra
2930 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 100.00
2931 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2932 4 UNION <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2933 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2934 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
2935 Warnings:
2936 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((values (5),(7),(1),(3),(4) limit 1,2) union (/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)) "__3"
2937 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
2938 union all
2939 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
2940 5
2941 7
2942 1
2943 3
2944 explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
2945 union all
2946 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
2947 id select_type table type possible_keys key key_len ref rows filtered Extra
2948 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 100.00
2949 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2950 4 UNION <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2951 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2952 NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL
2953 Warnings:
2954 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)) "__3"
2955 (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
2956 5
2957 3
2958 3
2959 4
2960 explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
2961 id select_type table type possible_keys key key_len ref rows filtered Extra
2962 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2963 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2964 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2965 Warnings:
2966 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((values (5),(7),(1),(3),(4) limit 3,2) union all /* select#3 */ select 3 AS "3") "__3" order by 1
2967 (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
2968 5
2969 3
2970 4
2971 5
2972 explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
2973 id select_type table type possible_keys key key_len ref rows filtered Extra
2974 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 Using filesort
2975 4 DERIVED <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2976 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2977 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2978 NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
2979 Warnings:
2980 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,3) union all /* select#3 */ select 3 AS "3") "__3" order by 1
2981 (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
2982 order by 1 limit 2 offset 1;
2983 5
2984 4
2985 5
2986 explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
2987 order by 1 limit 2 offset 1;
2988 id select_type table type possible_keys key key_len ref rows filtered Extra
2989 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 Using filesort
2990 4 DERIVED <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort
2991 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2992 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2993 NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL
2994 Warnings:
2995 Note 1003 /* select#1 */ select "__3"."5" AS "5" from ((/* select#4 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,3) union all /* select#3 */ select 3 AS "3") "__3" order by 1 limit 1,2
2996 values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
2997 ERROR 42S22: Unknown column '3' in 'ORDER BY'
2998 create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2;
2999 show create view v1;
3000 View Create View character_set_client collation_connection
3001 v1 CREATE VIEW "v1" AS values (5),(7),(1),(3),(4) order by 1 limit 2 latin1 latin1_swedish_ci
3002 select * from v1;
3003 5
3004 1
3005 3
3006 drop view v1;
3007 create view v1 as
3008 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
3009 union
3010 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
3011 show create view v1;
3012 View Create View character_set_client collation_connection
3013 v1 CREATE VIEW "v1" AS select "__4"."5" AS "5" from (select "__5"."5" AS "5" from ((values (5),(7),(1),(3),(4) limit 1,2) union (values (5),(7),(1),(3),(4) order by 1 limit 2)) "__5") "__4" latin1 latin1_swedish_ci
3014 select * from v1;
3015 5
3016 7
3017 1
3018 3
3019 drop view v1;
3020 create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
3021 ERROR 42S22: Unknown column '3' in 'ORDER BY'
3022 create view v1 as
3023 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
3024 union
3025 ( values (5), (7), (1), (3), (4) order by 2 limit 2 );
3026 ERROR 42S22: Unknown column '2' in 'ORDER BY'