Debian Codegraph @

mysql-test/suite/x/r/find_crud_groupby_o.result

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