Debian Codegraph @

mysql-test/suite/x/t/find_crud_groupby_o.test

raw
1 ########### ../t/find_crud_groupby_o.test ##################
2 ### #
3 ### This test runs aims to run FIND operation statements #
4 ### variant with mysqlxtest client for Table and Document data models. #
5 ### Test covers #
6 ### - find with group by (grouping) #
7 ### - find with group by (grouping) using Alias and Functions #
8 ### - find with different operators in having (grouping_criteria) #
9 ### #
10 ########################################################################
11 #
12
13 --echo ==============================================
14 --echo CRUD FIND GROUP BY SCENARIOS
15 --echo ==============================================
16 --echo
17
18 --echo ================================================================================
19 --echo PREAMBLE
20 --echo ================================================================================
21 --source include/xplugin_preamble.inc
22 create user crudfindgroupuser@localhost identified by 'crudfindgroupuser';
23 grant all on *.* to crudfindgroupuser@localhost;
24
25 ## TEST STARTS HERE
26 --echo ================================================================================
27 --echo TEST START
28 --echo ================================================================================
29 --write_file $MYSQL_TMP_DIR/find_crud_table_groupby.xpl
30 -->sql
31 DROP SCHEMA if EXISTS minisakila;
32 CREATE SCHEMA minisakila;
33 USE minisakila;
34
35 CREATE TABLE actor (
36 actor_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
37 first_name varchar(45) NOT NULL,
38 age int,
39 last_update timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
40 PRIMARY KEY (actor_id)
41 );
42
43 INSERT INTO actor VALUES
44 (1,'actor1',20,'2006-02-15 04:34:33'),
45 (2,'actor2',25,'2006-02-14 04:34:33'),
46 (3,'actor3',28,'2006-02-14 04:34:33'),
47 (4,'actor4',15,'2006-02-15 04:34:33'),
48 (5,'actor5',32,'2006-02-16 04:34:33'),
49 (6,'actor6',37,'2006-02-17 04:34:33'),
50 (7,'actor7',null,'2006-02-18 04:34:33');
51 -->endsql
52
53 -->echo Table based scenarios
54
55 -->echo Find specific row criteria and group by (grouping)
56 Mysqlx.Crud.Find {
57 collection {
58 name: "actor"
59 schema: "minisakila"
60 }
61 data_model: TABLE
62 projection {
63 source {
64 type: IDENT
65 identifier {
66 name: "last_update"
67 }
68 }
69 }
70 criteria {
71 type: OPERATOR
72 operator {
73 name: "!="
74 param {
75 type: IDENT identifier {
76 name: "first_name"
77 }
78 }
79 param {
80 type: LITERAL
81 literal {
82 type: V_STRING
83 v_string { value: "actor7" }
84 }
85 }
86 }
87 }
88 grouping {
89 type: IDENT
90 identifier {
91 name: "last_update"
92 }
93 }
94 }
95 -->recvresult
96
97 -->echo Find specific row with criteria and group by (grouping)
98 Mysqlx.Crud.Find {
99 collection {
100 name: "actor"
101 schema: "minisakila"
102 }
103 data_model: TABLE
104 projection {
105 source {
106 type: IDENT
107 identifier {
108 name: "last_update"
109 }
110 }
111 }
112 grouping {
113 type: IDENT
114 identifier {
115 name: "last_update"
116 }
117 }
118 }
119 -->recvresult
120
121 -->echo Find specific row criteria and group by (grouping) and having (grouping_criteria)
122 Mysqlx.Crud.Find {
123 collection {
124 name: "actor"
125 schema: "minisakila"
126 }
127 data_model: TABLE
128 projection {
129 source {
130 type: FUNC_CALL
131 function_call {
132 name {
133 name: "AVG"
134 }
135 param {
136 type: IDENT
137 identifier {
138 name: "age"
139 }
140 }
141 }
142 }
143 alias: "Average Age"
144 }
145 projection {
146 source {
147 type: IDENT
148 identifier {
149 name: "last_update"
150 }
151 }
152 }
153 criteria {
154 type: OPERATOR
155 operator {
156 name: "!="
157 param {
158 type: IDENT identifier {
159 name: "first_name"
160 }
161 }
162 param {
163 type: LITERAL
164 literal {
165 type: V_STRING
166 v_string { value: "actor7" }
167 }
168 }
169 }
170 }
171 grouping {
172 type: IDENT
173 identifier {
174 name: "last_update"
175 }
176 }
177 grouping_criteria {
178 type: OPERATOR
179 operator {
180 name: "<"
181 param {
182 type: IDENT
183 identifier {
184 name: "Average Age"
185 }
186 }
187 param {
188 type: LITERAL
189 literal {
190 type: V_DOUBLE
191 v_double: 32
192 }
193 }
194 }
195 }
196 }
197 -->recvresult
198
199 -->echo Find specific row with COUNT, MAX and MIN functions with group by (grouping)
200 Mysqlx.Crud.Find {
201 collection {
202 name: "actor"
203 schema: "minisakila"
204 }
205 data_model: TABLE
206 projection {
207 source {
208 type: FUNC_CALL
209 function_call {
210 name {
211 name: "COUNT"
212 }
213 param {
214 type: IDENT
215 identifier {
216 name: "age"
217 }
218 }
219 }
220 }
221 alias: "Age count"
222 }
223 projection {
224 source {
225 type: FUNC_CALL
226 function_call {
227 name {
228 name: "MAX"
229 }
230 param {
231 type: IDENT
232 identifier {
233 name: "age"
234 }
235 }
236 }
237 }
238 alias: "Maximum_Age"
239 }
240 projection {
241 source {
242 type: FUNC_CALL
243 function_call {
244 name {
245 name: "MIN"
246 }
247 param {
248 type: IDENT
249 identifier {
250 name: "age"
251 }
252 }
253 }
254 }
255 alias: "Minimum_Age"
256 }
257 projection {
258 source {
259 type: IDENT
260 identifier {
261 name: "last_update"
262 }
263 }
264 }
265 grouping {
266 type: IDENT
267 identifier {
268 name: "last_update"
269 }
270 }
271 }
272 -->recvresult
273
274 -->echo Find specific row with COUNT, MAX and MIN functions with group by (grouping) and Having (grouping_criteria)
275 Mysqlx.Crud.Find {
276 collection {
277 name: "actor"
278 schema: "minisakila"
279 }
280 data_model: TABLE
281 projection {
282 source {
283 type: FUNC_CALL
284 function_call {
285 name {
286 name: "COUNT"
287 }
288 param {
289 type: IDENT
290 identifier {
291 name: "age"
292 }
293 }
294 }
295 }
296 alias: "Age count"
297 }
298 projection {
299 source {
300 type: FUNC_CALL
301 function_call {
302 name {
303 name: "MAX"
304 }
305 param {
306 type: IDENT
307 identifier {
308 name: "age"
309 }
310 }
311 }
312 }
313 alias: "Maximum_Age"
314 }
315 projection {
316 source {
317 type: FUNC_CALL
318 function_call {
319 name {
320 name: "MIN"
321 }
322 param {
323 type: IDENT
324 identifier {
325 name: "age"
326 }
327 }
328 }
329 }
330 alias: "Minimum_Age"
331 }
332 projection {
333 source {
334 type: IDENT
335 identifier {
336 name: "last_update"
337 }
338 }
339 }
340 grouping {
341 type: IDENT
342 identifier {
343 name: "last_update"
344 }
345 }
346 grouping_criteria {
347 type: OPERATOR
348 operator {
349 name: "=="
350 param {
351 type: IDENT
352 identifier {
353 name: "Maximum_Age"
354 }
355 }
356 param {
357 type: LITERAL
358 literal {
359 type: V_UINT
360 v_unsigned_int: 32
361 }
362 }
363 }
364 }
365 }
366 -->recvresult
367
368 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with != Operator
369 Mysqlx.Crud.Find {
370 collection {
371 name: "actor"
372 schema: "minisakila"
373 }
374 data_model: TABLE
375 projection {
376 source {
377 type: FUNC_CALL
378 function_call {
379 name {
380 name: "MIN"
381 }
382 param {
383 type: IDENT
384 identifier {
385 name: "age"
386 }
387 }
388 }
389 }
390 alias: "Minimum_Age"
391 }
392 projection {
393 source {
394 type: IDENT
395 identifier {
396 name: "last_update"
397 }
398 }
399 }
400 grouping {
401 type: IDENT
402 identifier {
403 name: "last_update"
404 }
405 }
406 grouping_criteria {
407 type: OPERATOR
408 operator {
409 name: "!="
410 param {
411 type: IDENT
412 identifier {
413 name: "Minimum_Age"
414 }
415 }
416 param {
417 type: LITERAL
418 literal {
419 type: V_SINT
420 v_signed_int: 15
421 }
422 }
423 }
424 }
425 }
426 -->recvresult
427
428 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with >= Operator
429 Mysqlx.Crud.Find {
430 collection {
431 name: "actor"
432 schema: "minisakila"
433 }
434 data_model: TABLE
435 projection {
436 source {
437 type: FUNC_CALL
438 function_call {
439 name {
440 name: "MIN"
441 }
442 param {
443 type: IDENT
444 identifier {
445 name: "age"
446 }
447 }
448 }
449 }
450 alias: "Minimum_Age"
451 }
452 projection {
453 source {
454 type: IDENT
455 identifier {
456 name: "last_update"
457 }
458 }
459 }
460 grouping {
461 type: IDENT
462 identifier {
463 name: "last_update"
464 }
465 }
466 grouping_criteria {
467 type: OPERATOR
468 operator {
469 name: ">="
470 param {
471 type: IDENT
472 identifier {
473 name: "Minimum_Age"
474 }
475 }
476 param {
477 type: LITERAL
478 literal {
479 type: V_SINT
480 v_signed_int: 32
481 }
482 }
483 }
484 }
485 }
486 -->recvresult
487
488 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with > Operator
489 Mysqlx.Crud.Find {
490 collection {
491 name: "actor"
492 schema: "minisakila"
493 }
494 data_model: TABLE
495 projection {
496 source {
497 type: FUNC_CALL
498 function_call {
499 name {
500 name: "MIN"
501 }
502 param {
503 type: IDENT
504 identifier {
505 name: "age"
506 }
507 }
508 }
509 }
510 alias: "Minimum_Age"
511 }
512 projection {
513 source {
514 type: IDENT
515 identifier {
516 name: "last_update"
517 }
518 }
519 }
520 grouping {
521 type: IDENT
522 identifier {
523 name: "last_update"
524 }
525 }
526 grouping_criteria {
527 type: OPERATOR
528 operator {
529 name: ">"
530 param {
531 type: IDENT
532 identifier {
533 name: "Minimum_Age"
534 }
535 }
536 param {
537 type: LITERAL
538 literal {
539 type: V_SINT
540 v_signed_int: 32
541 }
542 }
543 }
544 }
545 }
546 -->recvresult
547
548 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with <= Operator
549 Mysqlx.Crud.Find {
550 collection {
551 name: "actor"
552 schema: "minisakila"
553 }
554 data_model: TABLE
555 projection {
556 source {
557 type: FUNC_CALL
558 function_call {
559 name {
560 name: "MIN"
561 }
562 param {
563 type: IDENT
564 identifier {
565 name: "age"
566 }
567 }
568 }
569 }
570 alias: "Minimum_Age"
571 }
572 projection {
573 source {
574 type: IDENT
575 identifier {
576 name: "last_update"
577 }
578 }
579 }
580 grouping {
581 type: IDENT
582 identifier {
583 name: "last_update"
584 }
585 }
586 grouping_criteria {
587 type: OPERATOR
588 operator {
589 name: "<="
590 param {
591 type: IDENT
592 identifier {
593 name: "Minimum_Age"
594 }
595 }
596 param {
597 type: LITERAL
598 literal {
599 type: V_SINT
600 v_signed_int: 25
601 }
602 }
603 }
604 }
605 }
606 -->recvresult
607
608 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with < Operator
609 Mysqlx.Crud.Find {
610 collection {
611 name: "actor"
612 schema: "minisakila"
613 }
614 data_model: TABLE
615 projection {
616 source {
617 type: FUNC_CALL
618 function_call {
619 name {
620 name: "MIN"
621 }
622 param {
623 type: IDENT
624 identifier {
625 name: "age"
626 }
627 }
628 }
629 }
630 alias: "Minimum_Age"
631 }
632 projection {
633 source {
634 type: IDENT
635 identifier {
636 name: "last_update"
637 }
638 }
639 }
640 grouping {
641 type: IDENT
642 identifier {
643 name: "last_update"
644 }
645 }
646 grouping_criteria {
647 type: OPERATOR
648 operator {
649 name: "<"
650 param {
651 type: IDENT
652 identifier {
653 name: "Minimum_Age"
654 }
655 }
656 param {
657 type: LITERAL
658 literal {
659 type: V_SINT
660 v_signed_int: 25
661 }
662 }
663 }
664 }
665 }
666 -->recvresult
667
668 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with null DataType
669 Mysqlx.Crud.Find {
670 collection {
671 name: "actor"
672 schema: "minisakila"
673 }
674 data_model: TABLE
675 projection {
676 source {
677 type: FUNC_CALL
678 function_call {
679 name {
680 name: "MIN"
681 }
682 param {
683 type: IDENT
684 identifier {
685 name: "age"
686 }
687 }
688 }
689 }
690 alias: "Minimum_Age"
691 }
692 projection {
693 source {
694 type: IDENT
695 identifier {
696 name: "last_update"
697 }
698 }
699 }
700 grouping {
701 type: IDENT
702 identifier {
703 name: "last_update"
704 }
705 }
706 grouping_criteria {
707 type: OPERATOR
708 operator {
709 name: "is"
710 param {
711 type: IDENT
712 identifier {
713 name: "Minimum_Age"
714 }
715 }
716 param {
717 type: LITERAL
718 literal {
719 type: V_NULL
720 }
721 }
722 }
723 }
724 }
725 -->recvresult
726
727 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with not null DataType
728 Mysqlx.Crud.Find {
729 collection {
730 name: "actor"
731 schema: "minisakila"
732 }
733 data_model: TABLE
734 projection {
735 source {
736 type: FUNC_CALL
737 function_call {
738 name {
739 name: "MIN"
740 }
741 param {
742 type: IDENT
743 identifier {
744 name: "age"
745 }
746 }
747 }
748 }
749 alias: "Minimum_Age"
750 }
751 projection {
752 source {
753 type: IDENT
754 identifier {
755 name: "last_update"
756 }
757 }
758 }
759 grouping {
760 type: IDENT
761 identifier {
762 name: "last_update"
763 }
764 }
765 grouping_criteria {
766 type: OPERATOR
767 operator {
768 name: "is_not"
769 param {
770 type: IDENT
771 identifier {
772 name: "Minimum_Age"
773 }
774 }
775 param {
776 type: LITERAL
777 literal {
778 type: V_NULL
779 }
780 }
781 }
782 }
783 }
784 -->recvresult
785
786 -->echo Find specific row with group by (grouping) and Having (grouping_criteria) with double DataType
787 Mysqlx.Crud.Find {
788 collection {
789 name: "actor"
790 schema: "minisakila"
791 }
792 data_model: TABLE
793 projection {
794 source {
795 type: FUNC_CALL
796 function_call {
797 name {
798 name: "AVG"
799 }
800 param {
801 type: IDENT
802 identifier {
803 name: "age"
804 }
805 }
806 }
807 }
808 alias: "Average_Age"
809 }
810 projection {
811 source {
812 type: IDENT
813 identifier {
814 name: "last_update"
815 }
816 }
817 }
818 grouping {
819 type: IDENT
820 identifier {
821 name: "last_update"
822 }
823 }
824 grouping_criteria {
825 type: OPERATOR
826 operator {
827 name: "=="
828 param {
829 type: IDENT
830 identifier {
831 name: "Average_Age"
832 }
833 }
834 param {
835 type: LITERAL
836 literal {
837 type: V_DOUBLE
838 v_double: 17.500000
839 }
840 }
841 }
842 }
843 }
844 -->recvresult
845
846 ## Negative test scenarios for Table
847 -->echo Error because expression not in group list
848 Mysqlx.Crud.Find {
849 collection {
850 name: "actor"
851 schema: "minisakila"
852 }
853 data_model: TABLE
854 criteria {
855 type: OPERATOR
856 operator {
857 name: "!="
858 param {
859 type: IDENT
860 identifier {
861 name: "first_name"
862 }
863 }
864 param {
865 type: LITERAL
866 literal {
867 type: V_STRING
868 v_string {
869 value: "Omar"
870 }
871 }
872 }
873 }
874 }
875 grouping {
876 type: IDENT
877 identifier {
878 name: "last_update"
879 }
880 }
881 }
882 -->recverror ER_WRONG_FIELD_WITH_GROUP
883
884 ## Cleanup
885 -->echo ================================================================================
886 -->echo CLEAN UP
887 -->echo ================================================================================
888 -->sql
889 DROP SCHEMA IF EXISTS minisakila;
890 DROP SCHEMA IF EXISTS mysqlxcoll;
891 DROP USER crudfindgroupuser@localhost;
892 -->endsql
893 EOF
894
895 --exec $MYSQLXTEST -u crudfindgroupuser --password='crudfindgroupuser' --file=$MYSQL_TMP_DIR/find_crud_table_groupby.xpl 2>&1
896
897 ## Cleanup
898 --source ../include/xplugin_cleanup.inc
899