Debian Codegraph @

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

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