Debian Codegraph @

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

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