Debian Codegraph @

mysql-test/suite/compat/oracle/t/table_value_constr.test

raw
1 SET sql_mode=ORACLE;
2
3 create table t1 (a int, b int);
4
5 insert into t1 values (1,2),(4,6),(9,7),
6 (1,1),(2,5),(7,8);
7
8 --echo # just VALUES
9
10 values (1,2);
11
12 values (1,2), (3,4), (5.6,0);
13
14 values ('abc', 'def');
15
16 --echo # UNION that uses VALUES structure(s)
17
18 select 1,2
19 union
20 values (1,2);
21
22 values (1,2)
23 union
24 select 1,2;
25
26 select 1,2
27 union
28 values (1,2),(3,4),(5,6),(7,8);
29
30 select 3,7
31 union
32 values (1,2),(3,4),(5,6);
33
34 select 3,7,4
35 union
36 values (1,2,5),(4,5,6);
37
38 select 1,2
39 union
40 values (1,7),(3,6.5);
41
42 select 1,2
43 union
44 values (1,2.0),(3,6);
45
46 select 1.8,2
47 union
48 values (1,2),(3,6);
49
50 values (1,2.4),(3,6)
51 union
52 select 2.8,9;
53
54 values (1,2),(3,4),(5,6),(7,8)
55 union
56 select 5,6;
57
58 select 'ab','cdf'
59 union
60 values ('al','zl'),('we','q');
61
62 values ('ab', 'cdf')
63 union
64 select 'ab','cdf';
65
66 values (1,2)
67 union
68 values (1,2),(5,6);
69
70 values (1,2)
71 union
72 values (3,4),(5,6);
73
74 values (1,2)
75 union
76 values (1,2)
77 union values (4,5);
78
79 --echo # UNION ALL that uses VALUES structure
80
81 values (1,2),(3,4)
82 union all
83 select 5,6;
84
85 values (1,2),(3,4)
86 union all
87 select 1,2;
88
89 select 5,6
90 union all
91 values (1,2),(3,4);
92
93 select 1,2
94 union all
95 values (1,2),(3,4);
96
97 values (1,2)
98 union all
99 values (1,2),(5,6);
100
101 values (1,2)
102 union all
103 values (3,4),(5,6);
104
105 values (1,2)
106 union all
107 values (1,2)
108 union all
109 values (4,5);
110
111 values (1,2)
112 union all
113 values (1,2)
114 union values (1,2);
115
116 values (1,2)
117 union
118 values (1,2)
119 union all
120 values (1,2);
121
122 --echo # EXCEPT that uses VALUES structure(s)
123
124 select 1,2
125 except
126 values (3,4),(5,6);
127
128 select 1,2
129 except
130 values (1,2),(3,4);
131
132 values (1,2),(3,4)
133 except
134 select 5,6;
135
136 values (1,2),(3,4)
137 except
138 select 1,2;
139
140 values (1,2),(3,4)
141 except
142 values (5,6);
143
144 values (1,2),(3,4)
145 except
146 values (1,2);
147
148 --echo # INTERSECT that uses VALUES structure(s)
149
150 select 1,2
151 intersect
152 values (3,4),(5,6);
153
154 select 1,2
155 intersect
156 values (1,2),(3,4);
157
158 values (1,2),(3,4)
159 intersect
160 select 5,6;
161
162 values (1,2),(3,4)
163 intersect
164 select 1,2;
165
166 values (1,2),(3,4)
167 intersect
168 values (5,6);
169
170 values (1,2),(3,4)
171 intersect
172 values (1,2);
173
174 --echo # combination of different structures that uses VALUES structures : UNION + EXCEPT
175
176 values (1,2),(3,4)
177 except
178 select 1,2
179 union values (1,2);
180
181 values (1,2),(3,4)
182 except
183 values (1,2)
184 union
185 values (1,2);
186
187 values (1,2),(3,4)
188 except
189 values (1,2)
190 union
191 values (3,4);
192
193 values (1,2),(3,4)
194 union
195 values (1,2)
196 except
197 values (1,2);
198
199 --echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT
200
201 values (1,2),(3,4)
202 except
203 select 1,2
204 union all
205 values (1,2);
206
207 values (1,2),(3,4)
208 except
209 values (1,2)
210 union all
211 values (1,2);
212
213 values (1,2),(3,4)
214 except
215 values (1,2)
216 union all
217 values (3,4);
218
219 values (1,2),(3,4)
220 union all
221 values (1,2)
222 except
223 values (1,2);
224
225 --echo # combination of different structures that uses VALUES structures : UNION + INTERSECT
226
227 values (1,2),(3,4)
228 intersect
229 select 1,2
230 union
231 values (1,2);
232
233 values (1,2),(3,4)
234 intersect
235 values (1,2)
236 union
237 values (1,2);
238
239 values (1,2),(3,4)
240 intersect
241 values (1,2)
242 union
243 values (3,4);
244
245 values (1,2),(3,4)
246 union
247 values (1,2)
248 intersect
249 values (1,2);
250
251 --echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT
252
253 values (1,2),(3,4)
254 intersect
255 select 1,2
256 union all
257 values (1,2);
258
259 values (1,2),(3,4)
260 intersect
261 values (1,2)
262 union all
263 values (1,2);
264
265 values (1,2),(3,4)
266 intersect
267 values (1,2)
268 union all
269 values (3,4);
270
271 values (1,2),(3,4)
272 union all
273 values (1,2)
274 intersect
275 values (1,2);
276
277 --echo # combination of different structures that uses VALUES structures : UNION + UNION ALL
278
279 values (1,2),(3,4)
280 union all
281 select 1,2
282 union
283 values (1,2);
284
285 values (1,2),(3,4)
286 union all
287 values (1,2)
288 union
289 values (1,2);
290
291 values (1,2),(3,4)
292 union all
293 values (1,2)
294 union
295 values (3,4);
296
297 values (1,2),(3,4)
298 union
299 values (1,2)
300 union all
301 values (1,2);
302
303 values (1,2)
304 union
305 values (1,2)
306 union all
307 values (1,2);
308
309 --echo # CTE that uses VALUES structure(s) : non-recursive CTE
310
311 with t2 as
312 (
313 values (1,2),(3,4)
314 )
315 select * from t2;
316
317 with t2 as
318 (
319 select 1,2
320 union
321 values (1,2)
322 )
323 select * from t2;
324
325 with t2 as
326 (
327 select 1,2
328 union
329 values (1,2),(3,4)
330 )
331 select * from t2;
332
333 with t2 as
334 (
335 values (1,2)
336 union
337 select 1,2
338 )
339 select * from t2;
340
341 with t2 as
342 (
343 values (1,2),(3,4)
344 union
345 select 1,2
346 )
347 select * from t2;
348
349 with t2 as
350 (
351 values (5,6)
352 union
353 values (1,2),(3,4)
354 )
355 select * from t2;
356
357 with t2 as
358 (
359 values (1,2)
360 union
361 values (1,2),(3,4)
362 )
363 select * from t2;
364
365 with t2 as
366 (
367 select 1,2
368 union all
369 values (1,2),(3,4)
370 )
371 select * from t2;
372
373 with t2 as
374 (
375 values (1,2),(3,4)
376 union all
377 select 1,2
378 )
379 select * from t2;
380
381 with t2 as
382 (
383 values (1,2)
384 union all
385 values (1,2),(3,4)
386 )
387 select * from t2;
388
389 --echo # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor
390
391 with recursive t2(a,b) as
392 (
393 values(1,1)
394 union
395 select t1.a, t1.b
396 from t1,t2
397 where t1.a=t2.a
398 )
399 select * from t2;
400
401 with recursive t2(a,b) as
402 (
403 values(1,1)
404 union
405 select t1.a+1, t1.b
406 from t1,t2
407 where t1.a=t2.a
408 )
409 select * from t2;
410
411 --echo # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors
412
413 with recursive t2(a,b) as
414 (
415 values(1,1)
416 union
417 values (3,4)
418 union
419 select t2.a+1, t1.b
420 from t1,t2
421 where t1.a=t2.a
422 )
423 select * from t2;
424
425 --echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL
426
427 --sorted_result
428 with recursive t2(a,b,st) as
429 (
430 values(1,1,1)
431 union all
432 select t2.a, t1.b, t2.st+1
433 from t1,t2
434 where t1.a=t2.a and st<3
435 )
436 select * from t2;
437
438 --echo # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements)
439
440 with recursive fact(n,f) as
441 (
442 values(1,1)
443 union
444 select n+1,f*n from fact where n < 10
445 )
446 select * from fact;
447
448 --echo # Derived table that uses VALUES structure(s) : singe VALUES structure
449
450 select * from (values (1,2),(3,4)) as t2;
451
452 --echo # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
453
454 select * from (select 1,2 union values (1,2)) as t2;
455
456 select * from (select 1,2 union values (1,2),(3,4)) as t2;
457
458 select * from (values (1,2) union select 1,2) as t2;
459
460 select * from (values (1,2),(3,4) union select 1,2) as t2;
461
462 select * from (values (5,6) union values (1,2),(3,4)) as t2;
463
464 select * from (values (1,2) union values (1,2),(3,4)) as t2;
465
466 --echo # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
467
468 select * from (select 1,2 union all values (1,2),(3,4)) as t2;
469
470 select * from (values (1,2),(3,4) union all select 1,2) as t2;
471
472 select * from (values (1,2) union all values (1,2),(3,4)) as t2;
473
474 --echo # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure
475
476 let $drop_view= drop view v1;
477 let $select_view= select * from v1;
478
479 create view v1 as values (1,2),(3,4);
480
481 eval $select_view;
482 eval $drop_view;
483
484 --echo # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s)
485
486 create view v1 as
487 select 1,2
488 union
489 values (1,2);
490
491 eval $select_view;
492 eval $drop_view;
493
494 create view v1 as
495 select 1,2
496 union
497 values (1,2),(3,4);
498
499 eval $select_view;
500 eval $drop_view;
501
502 create view v1 as
503 values (1,2)
504 union
505 select 1,2;
506
507 eval $select_view;
508 eval $drop_view;
509
510 create view v1 as
511 values (1,2),(3,4)
512 union
513 select 1,2;
514
515 eval $select_view;
516 eval $drop_view;
517
518 create view v1 as
519 values (5,6)
520 union
521 values (1,2),(3,4);
522
523 eval $select_view;
524 eval $drop_view;
525
526 --echo # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
527
528 create view v1 as
529 values (1,2)
530 union
531 values (1,2),(3,4);
532
533 eval $select_view;
534 eval $drop_view;
535
536 create view v1 as
537 select 1,2
538 union all
539 values (1,2),(3,4);
540
541 eval $select_view;
542 eval $drop_view;
543
544 create view v1 as
545 values (1,2),(3,4)
546 union all
547 select 1,2;
548
549 eval $select_view;
550 eval $drop_view;
551
552 create view v1 as
553 values (1,2)
554 union all
555 values (1,2),(3,4);
556
557 eval $select_view;
558 eval $drop_view;
559
560 --echo # IN-subquery with VALUES structure(s) : simple case
561 let $query=
562 select * from t1
563 where a in (values (1));
564 let $subst_query=
565 select * from t1
566 where a in (select * from (values (1)) as tvc_0);
567 eval $query;
568 eval $subst_query;
569 eval explain extended $query;
570 eval explain extended $subst_query;
571
572 --echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
573 let $query=
574 select * from t1
575 where a in (values (1) union select 2);
576 let $subst_query=
577 select * from t1
578 where a in (select * from (values (1)) as tvc_0 union
579 select 2);
580 eval $query;
581 eval $subst_query;
582 eval explain extended $query;
583 eval explain extended $subst_query;
584
585 --echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place
586 let $query=
587 select * from t1
588 where a in (select 2 union values (1));
589 let $subst_query=
590 select * from t1
591 where a in (select 2 union
592 select * from (values (1)) tvc_0);
593 eval $query;
594 eval $subst_query;
595 eval explain extended $query;
596 eval explain extended $subst_query;
597
598 --echo # IN-subquery with VALUES structure(s) : UNION ALL
599 let $query=
600 select * from t1
601 where a in (values (1) union all select b from t1);
602 let $subst_query=
603 select * from t1
604 where a in (select * from (values (1)) as tvc_0 union all
605 select b from t1);
606
607 eval $query;
608 eval $subst_query;
609 eval explain extended $query;
610 eval explain extended $subst_query;
611
612 --echo # NOT IN subquery with VALUES structure(s) : simple case
613 let $query=
614 select * from t1
615 where a not in (values (1),(2));
616 let $subst_query=
617 select * from t1
618 where a not in (select * from (values (1),(2)) as tvc_0);
619
620 eval $query;
621 eval $subst_query;
622 eval explain extended $query;
623 eval explain extended $subst_query;
624
625 --echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place
626 let $query=
627 select * from t1
628 where a not in (values (1) union select 2);
629 let $subst_query=
630 select * from t1
631 where a not in (select * from (values (1)) as tvc_0 union
632 select 2);
633
634 eval $query;
635 eval $subst_query;
636 eval explain extended $query;
637 eval explain extended $subst_query;
638
639 --echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place
640 let $query=
641 select * from t1
642 where a not in (select 2 union values (1));
643 let $subst_query=
644 select * from t1
645 where a not in (select 2 union
646 select * from (values (1)) as tvc_0);
647
648 eval $query;
649 eval $subst_query;
650 eval explain extended $query;
651 eval explain extended $subst_query;
652
653 --echo # ANY-subquery with VALUES structure(s) : simple case
654 let $query=
655 select * from t1
656 where a = any (values (1),(2));
657 let $subst_query=
658 select * from t1
659 where a = any (select * from (values (1),(2)) as tvc_0);
660
661 eval $query;
662 eval $subst_query;
663 eval explain extended $query;
664 eval explain extended $subst_query;
665
666 --echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
667 let $query=
668 select * from t1
669 where a = any (values (1) union select 2);
670 let $subst_query=
671 select * from t1
672 where a = any (select * from (values (1)) as tvc_0 union
673 select 2);
674
675 eval $query;
676 eval $subst_query;
677 eval explain extended $query;
678 eval explain extended $subst_query;
679
680 --echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place
681 let $query=
682 select * from t1
683 where a = any (select 2 union values (1));
684 let $subst_query=
685 select * from t1
686 where a = any (select 2 union
687 select * from (values (1)) as tvc_0);
688
689 eval $query;
690 eval $subst_query;
691 eval explain extended $query;
692 eval explain extended $subst_query;
693
694 --echo # ALL-subquery with VALUES structure(s) : simple case
695 let $query=
696 select * from t1
697 where a = all (values (1));
698 let $subst_query=
699 select * from t1
700 where a = all (select * from (values (1)) as tvc_0);
701
702 eval $query;
703 eval $subst_query;
704 eval explain extended $query;
705 eval explain extended $subst_query;
706
707 --echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place
708 let $query=
709 select * from t1
710 where a = all (values (1) union select 1);
711 let $subst_query=
712 select * from t1
713 where a = all (select * from (values (1)) as tvc_0 union
714 select 1);
715
716 eval $query;
717 eval $subst_query;
718 eval explain extended $query;
719 eval explain extended $subst_query;
720
721 --echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place
722 let $query=
723 select * from t1
724 where a = any (select 1 union values (1));
725 let $subst_query=
726 select * from t1
727 where a = any (select 1 union
728 select * from (values (1)) as tvc_0);
729
730 eval $query;
731 eval $subst_query;
732 eval explain extended $query;
733 eval explain extended $subst_query;
734
735 --echo # prepare statement that uses VALUES structure(s): single VALUES structure
736
737 prepare stmt1 from '
738 values (1,2);
739 ';
740
741 execute stmt1;
742 execute stmt1;
743 deallocate prepare stmt1;
744
745 --echo # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s)
746
747 prepare stmt1 from '
748 select 1,2
749 union
750 values (1,2),(3,4);
751 ';
752
753 execute stmt1;
754 execute stmt1;
755 deallocate prepare stmt1;
756
757 prepare stmt1 from '
758 values (1,2),(3,4)
759 union
760 select 1,2;
761 ';
762
763 execute stmt1;
764 execute stmt1;
765 deallocate prepare stmt1;
766
767 prepare stmt1 from '
768 select 1,2
769 union
770 values (3,4)
771 union
772 values (1,2);
773 ';
774
775 execute stmt1;
776 execute stmt1;
777 deallocate prepare stmt1;
778
779 prepare stmt1 from '
780 values (5,6)
781 union
782 values (1,2),(3,4);
783 ';
784
785 execute stmt1;
786 execute stmt1;
787 deallocate prepare stmt1;
788
789 --echo # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s)
790
791 prepare stmt1 from '
792 select 1,2
793 union
794 values (1,2),(3,4);
795 ';
796
797 execute stmt1;
798 execute stmt1;
799 deallocate prepare stmt1;
800
801 prepare stmt1 from '
802 values (1,2),(3,4)
803 union all
804 select 1,2;
805 ';
806
807 execute stmt1;
808 execute stmt1;
809 deallocate prepare stmt1;
810
811 prepare stmt1 from '
812 select 1,2
813 union all
814 values (3,4)
815 union all
816 values (1,2);
817 ';
818
819 execute stmt1;
820 execute stmt1;
821 deallocate prepare stmt1;
822
823 prepare stmt1 from '
824 values (1,2)
825 union all
826 values (1,2),(3,4);
827 ';
828
829 execute stmt1;
830 execute stmt1;
831 deallocate prepare stmt1;
832
833 --echo # explain query that uses VALUES structure(s): single VALUES structure
834
835 explain
836 values (1,2);
837
838 explain format=json
839 values (1,2);
840
841 --echo # explain query that uses VALUES structure(s): UNION with VALUES structure(s)
842
843 explain
844 select 1,2
845 union
846 values (1,2),(3,4);
847
848 explain
849 values (1,2),(3,4)
850 union
851 select 1,2;
852
853 explain
854 values (5,6)
855 union
856 values (1,2),(3,4);
857
858 explain format=json
859 select 1,2
860 union
861 values (1,2),(3,4);
862
863 explain format=json
864 values (1,2),(3,4)
865 union
866 select 1,2;
867
868 explain format=json
869 values (5,6)
870 union
871 values (1,2),(3,4);
872
873 explain
874 select 1,2
875 union
876 values (3,4)
877 union
878 values (1,2);
879
880 explain format=json
881 select 1,2
882 union
883 values (3,4)
884 union
885 values (1,2);
886
887 --echo # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
888
889 explain
890 select 1,2
891 union
892 values (1,2),(3,4);
893
894 explain
895 values (1,2),(3,4)
896 union all
897 select 1,2;
898
899 explain
900 values (1,2)
901 union all
902 values (1,2),(3,4);
903
904 explain format=json
905 values (1,2),(3,4)
906 union all
907 select 1,2;
908
909 explain format=json
910 select 1,2
911 union
912 values (1,2),(3,4);
913
914 explain format=json
915 values (1,2)
916 union all
917 values (1,2),(3,4);
918
919 explain
920 select 1,2
921 union all
922 values (3,4)
923 union all
924 values (1,2);
925
926 explain format=json
927 select 1,2
928 union all
929 values (3,4)
930 union all
931 values (1,2);
932
933 --echo # analyze query that uses VALUES structure(s): single VALUES structure
934
935 analyze
936 values (1,2);
937
938 --source include/analyze-format.inc
939 analyze format=json
940 values (1,2);
941
942 --echo # analyze query that uses VALUES structure(s): UNION with VALUES structure(s)
943
944 analyze
945 select 1,2
946 union
947 values (1,2),(3,4);
948
949 analyze
950 values (1,2),(3,4)
951 union
952 select 1,2;
953
954 analyze
955 values (5,6)
956 union
957 values (1,2),(3,4);
958
959 --source include/analyze-format.inc
960 analyze format=json
961 select 1,2
962 union
963 values (1,2),(3,4);
964
965 --source include/analyze-format.inc
966 analyze format=json
967 values (1,2),(3,4)
968 union
969 select 1,2;
970
971 --source include/analyze-format.inc
972 analyze format=json
973 values (5,6)
974 union
975 values (1,2),(3,4);
976
977 analyze
978 select 1,2
979 union
980 values (3,4)
981 union
982 values (1,2);
983
984 --source include/analyze-format.inc
985 analyze format=json
986 select 1,2
987 union
988 values (3,4)
989 union
990 values (1,2);
991
992 --echo # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
993
994 analyze
995 select 1,2
996 union
997 values (1,2),(3,4);
998
999 analyze
1000 values (1,2),(3,4)
1001 union all
1002 select 1,2;
1003
1004 analyze
1005 values (1,2)
1006 union all
1007 values (1,2),(3,4);
1008
1009 --source include/analyze-format.inc
1010 analyze format=json
1011 values (1,2),(3,4)
1012 union all
1013 select 1,2;
1014
1015 --source include/analyze-format.inc
1016 analyze format=json
1017 select 1,2
1018 union
1019 values (1,2),(3,4);
1020
1021 --source include/analyze-format.inc
1022 analyze format=json
1023 values (1,2)
1024 union all
1025 values (1,2),(3,4);
1026
1027 analyze
1028 select 1,2
1029 union all
1030 values (3,4)
1031 union all
1032 values (1,2);
1033
1034 --source include/analyze-format.inc
1035 analyze format=json
1036 select 1,2
1037 union all
1038 values (3,4)
1039 union all
1040 values (1,2);
1041
1042 --echo # different number of values in TVC
1043 --error ER_WRONG_NUMBER_OF_VALUES_IN_TVC
1044 values (1,2),(3,4,5);
1045
1046 --echo # illegal parameter data types in TVC
1047 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
1048 values (1,point(1,1)),(1,1);
1049 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
1050 values (1,point(1,1)+1);
1051
1052 --echo # field reference in TVC
1053 --error ER_FIELD_REFERENCE_IN_TVC
1054 select * from (values (1), (b), (2)) as new_tvc;
1055 --error ER_FIELD_REFERENCE_IN_TVC
1056 select * from (values (1), (t1.b), (2)) as new_tvc;
1057
1058 drop table t1;
1059
1060 --echo #
1061 --echo # MDEV-15940: cursor over TVC
1062 --echo #
1063
1064 DELIMITER |;
1065
1066 DECLARE
1067 v INT;
1068 CURSOR cur IS VALUES(7);
1069 BEGIN
1070 OPEN cur;
1071 FETCH cur INTO v;
1072 SELECT v;
1073 END;
1074 |
1075
1076 DECLARE
1077 v INT DEFAULT 0;
1078 BEGIN
1079 FOR a IN (VALUES (7)) LOOP
1080 SET v = v + 1;
1081 END LOOP;
1082 SELECT v;
1083 END;
1084 |
1085
1086 DELIMITER ;|
1087
1088 --echo #
1089 --echo # MDEV-16038: empty row in TVC
1090 --echo #
1091
1092 --error ER_EMPTY_ROW_IN_TVC
1093 with t as (values (),()) select 1 from t;
1094
1095 --echo #
1096 --echo # MDEV-17017: TVC in derived table
1097 --echo #
1098
1099 create table t1 (a int);
1100 insert into t1 values (9), (3), (2);
1101
1102 let $q1=
1103 select * from (values (7), (5), (8), (1), (3), (8), (1)) t;
1104 eval $q1;
1105 eval explain $q1;
1106
1107 let $q2=
1108 select * from (values (1,11), (7,77), (3,31), (4,42)) t;
1109 eval $q2;
1110 eval explain $q2;
1111
1112 let $q3=
1113 select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;
1114 eval $q3;
1115 eval explain $q3;
1116
1117 let $q4=
1118 select * from (values (7), (5), (8), (1) union select * from t1) t;
1119 eval $q4;
1120 eval explain $q4;
1121
1122 drop table t1;
1123
1124 --echo #
1125 --echo # MDEV-16930: expression in the first row of TVC specifying derived table
1126 --echo #
1127
1128 SELECT 1 + 1, 2, 'abc';
1129 SELECT * FROM (SELECT 1 + 1, 2, 'abc') t;
1130 WITH cte AS (SELECT 1 + 1, 2, 'abc') SELECT * FROM cte;
1131 SELECT 1 + 1, 2, 'abc' UNION SELECT 3+4, 3, 'abc';
1132 CREATE VIEW v1 AS SELECT 1 + 1, 2, 'abc';
1133 SELECT * FROM v1;
1134 DROP VIEW v1;
1135
1136 VALUES(1 + 1,2,'abc');
1137 SELECT * FROM (VALUES(1 + 1,2,'abc')) t;
1138
1139 --echo #
1140 --echo # MDEV-17894: tvc with ORDER BY ... LIMIT
1141 --echo #
1142
1143 let $q=
1144 values (5), (7), (1), (3), (4) limit 2;
1145 eval $q;
1146 eval explain extended $q;
1147
1148 let $q=
1149 values (5), (7), (1), (3), (4) limit 2 offset 1;
1150 eval $q;
1151 eval explain extended $q;
1152
1153 let $q=
1154 values (5), (7), (1), (3), (4) order by 1 limit 2;
1155 eval $q;
1156 eval explain extended $q;
1157
1158 let $q=
1159 values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
1160 eval $q;
1161 eval explain extended $q;
1162
1163 let $q=
1164 values (5), (7), (1), (3), (4) order by 1;
1165 eval $q;
1166 eval explain extended $q;
1167
1168 let $q=
1169 values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2;
1170 eval $q;
1171 eval explain extended $q;
1172
1173 let $q=
1174 select 2 union (values (5), (7), (1), (3), (4) limit 2);
1175 --replace_result * 2
1176 eval $q;
1177 eval explain extended $q;
1178
1179 let $q=
1180 select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
1181 --replace_result * 2
1182 eval $q;
1183 eval explain extended $q;
1184
1185 let $q=
1186 select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
1187 --replace_result * 2
1188 eval $q;
1189 eval explain extended $q;
1190
1191 let $q=
1192 select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
1193 --replace_result * 2
1194 eval $q;
1195 eval explain extended $q;
1196
1197
1198 let $q=
1199 (values (5), (7), (1), (3), (4) limit 2) union select 2;
1200 eval $q;
1201 --replace_result derived4 derived2
1202 eval explain extended $q;
1203
1204 let $q=
1205 (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
1206 eval $q;
1207 eval explain extended $q;
1208
1209 let $q=
1210 (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
1211 eval $q;
1212 --replace_result derived4 derived2
1213 eval explain extended $q;
1214
1215 let $q=
1216 (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
1217 eval $q;
1218 --replace_result derived4 derived2
1219 eval explain extended $q;
1220
1221
1222 let $q=
1223 select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
1224 --replace_result * 3
1225 eval $q;
1226 eval explain extended $q;
1227
1228 let $q=
1229 (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
1230 eval $q;
1231 eval explain extended $q;
1232
1233 let $q=
1234 select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
1235 --replace_result * 3
1236 eval $q;
1237 eval explain extended $q;
1238
1239 let $q=
1240 (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
1241 eval $q;
1242 --replace_result derived4 derived2
1243 eval explain extended $q;
1244
1245 let $q=
1246 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
1247 union
1248 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
1249 eval $q;
1250 eval explain extended $q;
1251
1252 let $q=
1253 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
1254 union all
1255 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
1256 eval $q;
1257 eval explain extended $q;
1258
1259 let $q=
1260 (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
1261 eval $q;
1262 eval explain extended $q;
1263
1264 let $q=
1265 (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
1266 eval $q;
1267 --replace_result derived4 derived2
1268 eval explain extended $q;
1269
1270 let $q=
1271 (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
1272 order by 1 limit 2 offset 1;
1273 eval $q;
1274 --replace_result derived4 derived2
1275 eval explain extended $q;
1276
1277 --error ER_BAD_FIELD_ERROR
1278 values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
1279
1280 create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2;
1281 show create view v1;
1282 select * from v1;
1283 drop view v1;
1284
1285 create view v1 as
1286 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
1287 union
1288 ( values (5), (7), (1), (3), (4) order by 1 limit 2 );
1289 show create view v1;
1290 select * from v1;
1291 drop view v1;
1292
1293 --error ER_BAD_FIELD_ERROR
1294 create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
1295
1296 --error ER_BAD_FIELD_ERROR
1297 create view v1 as
1298 ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
1299 union
1300 ( values (5), (7), (1), (3), (4) order by 2 limit 2 );