Debian Codegraph @

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

raw
1 =============================================
2 CRUD UPDATE ARRAY_INSERT SCENARIOS
3 =============================================
4
5 ================================================================================
6 PREAMBLE
7 ================================================================================
8 create user updatearrayinsert@localhost identified by 'updatearrayinsert';
9 grant all on *.* to updatearrayinsert@localhost;
10 CREATE SCHEMA mysqlxcoll;
11 ================================================================================
12 TEST START
13 ================================================================================
14
15 command ok
16 RUN SET SQL_SAFE_UPDATES = 0
17
18 0 rows affected
19 RUN USE mysqlxcoll
20
21 0 rows affected
22 RUN INSERT INTO maincoll (doc) values ('{"_id": "1", "name": ["Victor"], "last_name": ["Otero"],"amount": [4.99]}')
23
24 1 rows affected
25 RUN INSERT INTO maincoll (doc) values ('{"_id": "2", "name": ["Gonzalo"], "last_name": ["Chrystens"],"amount": [120.57]}')
26
27 1 rows affected
28 RUN INSERT INTO maincoll (doc) values ('{"_id": "3", "name": ["Abraham"], "last_name": ["Vega"],"amount": [74.56]}')
29
30 1 rows affected
31 RUN INSERT INTO maincoll (doc) values ('{"_id": "4", "name": ["Jennifer"], "last_name": ["Leon"],"amount": [387.14]}')
32
33 1 rows affected
34 RUN INSERT INTO maincoll (doc) values ('{"_id": "5", "name": ["Jhonny"], "last_name": ["Test"],"amount": [125.45]}')
35
36 1 rows affected
37 Update insert String value into Array using == Operator for criteria
38 send Mysqlx.Crud.Update {
39 collection {
40 name: "maincoll"
41 schema: "mysqlxcoll"
42 }
43 data_model: TABLE
44 criteria {
45 type: OPERATOR
46 operator {
47 name: "=="
48 param {
49 type: IDENT
50 identifier {
51 name: "_id"
52 }
53 }
54 param {
55 type: LITERAL
56 literal {
57 type: V_OCTETS
58 v_octets {
59 value: "1"
60 }
61 }
62 }
63 }
64 }
65 operation {
66 source {
67 document_path {
68 type: MEMBER
69 value: "name"
70 }
71 document_path {
72 type: ARRAY_INDEX
73 index: 0
74 }
75 name: "doc"
76 }
77 operation: ARRAY_INSERT
78 value {
79 type: LITERAL
80 literal {
81 type: V_STRING
82 v_string {
83 value: "Victor_Extra_Name"
84 }
85 }
86 }
87 }
88 }
89
90
91 1 rows affected
92 Rows matched: 1 Changed: 1 Warnings: 0
93 RUN select doc from maincoll
94 doc
95 {"_id": "1", "name": ["Victor_Extra_Name", "Victor"], "amount": [4.99], "last_name": ["Otero"]}
96 {"_id": "2", "name": ["Gonzalo"], "amount": [120.57], "last_name": ["Chrystens"]}
97 {"_id": "3", "name": ["Abraham"], "amount": [74.56], "last_name": ["Vega"]}
98 {"_id": "4", "name": ["Jennifer"], "amount": [387.14], "last_name": ["Leon"]}
99 {"_id": "5", "name": ["Jhonny"], "amount": [125.45], "last_name": ["Test"]}
100 0 rows affected
101 Update a valid collection with == operator and for Quotes
102 send Mysqlx.Crud.Update {
103 collection {
104 name: "maincoll"
105 schema: "mysqlxcoll"
106 }
107 data_model: TABLE
108 criteria {
109 type: OPERATOR
110 operator {
111 name: "=="
112 param {
113 type: IDENT
114 identifier {
115 name: "_id"
116 }
117 }
118 param {
119 type: LITERAL
120 literal {
121 type: V_OCTETS
122 v_octets {
123 value: "1"
124 }
125 }
126 }
127 }
128 }
129 operation {
130 source {
131 document_path {
132 type: MEMBER
133 value: "name"
134 }
135 document_path {
136 type: ARRAY_INDEX
137 index: 0
138 }
139 name: "doc"
140 }
141 operation: ARRAY_INSERT
142 value {
143 type: LITERAL
144 literal {
145 type: V_STRING
146 v_string {
147 value: "\"VictorArrayInsert\""
148 }
149 }
150 }
151 }
152 }
153
154
155 1 rows affected
156 Rows matched: 1 Changed: 1 Warnings: 0
157 RUN select doc from maincoll
158 doc
159 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [4.99], "last_name": ["Otero"]}
160 {"_id": "2", "name": ["Gonzalo"], "amount": [120.57], "last_name": ["Chrystens"]}
161 {"_id": "3", "name": ["Abraham"], "amount": [74.56], "last_name": ["Vega"]}
162 {"_id": "4", "name": ["Jennifer"], "amount": [387.14], "last_name": ["Leon"]}
163 {"_id": "5", "name": ["Jhonny"], "amount": [125.45], "last_name": ["Test"]}
164 0 rows affected
165 Update a valid collection with string value and > operator for multiple docs
166 send Mysqlx.Crud.Update {
167 collection {
168 name: "maincoll"
169 schema: "mysqlxcoll"
170 }
171 data_model: TABLE
172 criteria {
173 type: OPERATOR
174 operator {
175 name: ">"
176 param {
177 type: IDENT
178 identifier {
179 name: "_id"
180 }
181 }
182 param {
183 type: LITERAL
184 literal {
185 type: V_OCTETS
186 v_octets {
187 value: "3"
188 }
189 }
190 }
191 }
192 }
193 operation {
194 source {
195 document_path {
196 type: MEMBER
197 value: "last_name"
198 }
199 document_path {
200 type: ARRAY_INDEX
201 index: 0
202 }
203 name: "doc"
204 }
205 operation: ARRAY_INSERT
206 value {
207 type: LITERAL
208 literal {
209 type: V_STRING
210 v_string {
211 value: "extra_last_name"
212 }
213 }
214 }
215 }
216 }
217
218
219 2 rows affected
220 Rows matched: 2 Changed: 2 Warnings: 0
221 RUN select doc from maincoll
222 doc
223 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [4.99], "last_name": ["Otero"]}
224 {"_id": "2", "name": ["Gonzalo"], "amount": [120.57], "last_name": ["Chrystens"]}
225 {"_id": "3", "name": ["Abraham"], "amount": [74.56], "last_name": ["Vega"]}
226 {"_id": "4", "name": ["Jennifer"], "amount": [387.14], "last_name": ["extra_last_name", "Leon"]}
227 {"_id": "5", "name": ["Jhonny"], "amount": [125.45], "last_name": ["extra_last_name", "Test"]}
228 0 rows affected
229 Update a valid collection with string value and < operator for multiple docs
230 send Mysqlx.Crud.Update {
231 collection {
232 name: "maincoll"
233 schema: "mysqlxcoll"
234 }
235 data_model: TABLE
236 criteria {
237 type: OPERATOR
238 operator {
239 name: "<"
240 param {
241 type: IDENT
242 identifier {
243 name: "_id"
244 }
245 }
246 param {
247 type: LITERAL
248 literal {
249 type: V_OCTETS
250 v_octets {
251 value: "2"
252 }
253 }
254 }
255 }
256 }
257 operation {
258 source {
259 document_path {
260 type: MEMBER
261 value: "last_name"
262 }
263 document_path {
264 type: ARRAY_INDEX
265 index: 0
266 }
267 name: "doc"
268 }
269 operation: ARRAY_INSERT
270 value {
271 type: LITERAL
272 literal {
273 type: V_STRING
274 v_string {
275 value: "extra_last_name_2"
276 }
277 }
278 }
279 }
280 }
281
282
283 1 rows affected
284 Rows matched: 1 Changed: 1 Warnings: 0
285 RUN select doc from maincoll
286 doc
287 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [4.99], "last_name": ["extra_last_name_2", "Otero"]}
288 {"_id": "2", "name": ["Gonzalo"], "amount": [120.57], "last_name": ["Chrystens"]}
289 {"_id": "3", "name": ["Abraham"], "amount": [74.56], "last_name": ["Vega"]}
290 {"_id": "4", "name": ["Jennifer"], "amount": [387.14], "last_name": ["extra_last_name", "Leon"]}
291 {"_id": "5", "name": ["Jhonny"], "amount": [125.45], "last_name": ["extra_last_name", "Test"]}
292 0 rows affected
293 Update a valid collection with string value and <= operator for multiple docs
294 send Mysqlx.Crud.Update {
295 collection {
296 name: "maincoll"
297 schema: "mysqlxcoll"
298 }
299 data_model: TABLE
300 criteria {
301 type: OPERATOR
302 operator {
303 name: "<="
304 param {
305 type: IDENT
306 identifier {
307 name: "_id"
308 }
309 }
310 param {
311 type: LITERAL
312 literal {
313 type: V_OCTETS
314 v_octets {
315 value: "2"
316 }
317 }
318 }
319 }
320 }
321 operation {
322 source {
323 document_path {
324 type: MEMBER
325 value: "last_name"
326 }
327 document_path {
328 type: ARRAY_INDEX
329 index: 0
330 }
331 name: "doc"
332 }
333 operation: ARRAY_INSERT
334 value {
335 type: LITERAL
336 literal {
337 type: V_STRING
338 v_string {
339 value: "extra_last_name_3"
340 }
341 }
342 }
343 }
344 }
345
346
347 2 rows affected
348 Rows matched: 2 Changed: 2 Warnings: 0
349 RUN select doc from maincoll
350 doc
351 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
352 {"_id": "2", "name": ["Gonzalo"], "amount": [120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
353 {"_id": "3", "name": ["Abraham"], "amount": [74.56], "last_name": ["Vega"]}
354 {"_id": "4", "name": ["Jennifer"], "amount": [387.14], "last_name": ["extra_last_name", "Leon"]}
355 {"_id": "5", "name": ["Jhonny"], "amount": [125.45], "last_name": ["extra_last_name", "Test"]}
356 0 rows affected
357 Update a valid collection with string value and >= operator for multiple docs
358 send Mysqlx.Crud.Update {
359 collection {
360 name: "maincoll"
361 schema: "mysqlxcoll"
362 }
363 data_model: TABLE
364 criteria {
365 type: OPERATOR
366 operator {
367 name: ">="
368 param {
369 type: IDENT
370 identifier {
371 name: "_id"
372 }
373 }
374 param {
375 type: LITERAL
376 literal {
377 type: V_OCTETS
378 v_octets {
379 value: "3"
380 }
381 }
382 }
383 }
384 }
385 operation {
386 source {
387 document_path {
388 type: MEMBER
389 value: "amount"
390 }
391 document_path {
392 type: ARRAY_INDEX
393 index: 0
394 }
395 name: "doc"
396 }
397 operation: ARRAY_INSERT
398 value {
399 type: LITERAL
400 literal {
401 type: V_DOUBLE
402 v_double: 257.52
403 }
404 }
405 }
406 }
407
408
409 3 rows affected
410 Rows matched: 3 Changed: 3 Warnings: 0
411 RUN select doc from maincoll
412 doc
413 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
414 {"_id": "2", "name": ["Gonzalo"], "amount": [120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
415 {"_id": "3", "name": ["Abraham"], "amount": [257.52, 74.56], "last_name": ["Vega"]}
416 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
417 {"_id": "5", "name": ["Jhonny"], "amount": [257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
418 0 rows affected
419 Update a valid collection with string value and != operator for multiple docs
420 send Mysqlx.Crud.Update {
421 collection {
422 name: "maincoll"
423 schema: "mysqlxcoll"
424 }
425 data_model: TABLE
426 criteria {
427 type: OPERATOR
428 operator {
429 name: "!="
430 param {
431 type: IDENT
432 identifier {
433 name: "_id"
434 }
435 }
436 param {
437 type: LITERAL
438 literal {
439 type: V_OCTETS
440 v_octets {
441 value: "4"
442 }
443 }
444 }
445 }
446 }
447 operation {
448 source {
449 document_path {
450 type: MEMBER
451 value: "amount"
452 }
453 document_path {
454 type: ARRAY_INDEX
455 index: 0
456 }
457 name: "doc"
458 }
459 operation: ARRAY_INSERT
460 value {
461 type: LITERAL
462 literal {
463 type: V_DOUBLE
464 v_double: 546.12
465 }
466 }
467 }
468 }
469
470
471 4 rows affected
472 Rows matched: 4 Changed: 4 Warnings: 0
473 RUN select doc from maincoll
474 doc
475 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
476 {"_id": "2", "name": ["Gonzalo"], "amount": [546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
477 {"_id": "3", "name": ["Abraham"], "amount": [546.12, 257.52, 74.56], "last_name": ["Vega"]}
478 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
479 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
480 0 rows affected
481 Update array insert a Double
482 send Mysqlx.Crud.Update {
483 collection {
484 name: "maincoll"
485 schema: "mysqlxcoll"
486 }
487 data_model: TABLE
488 criteria {
489 type: OPERATOR
490 operator {
491 name: "=="
492 param {
493 type: IDENT
494 identifier {
495 name: "_id"
496 }
497 }
498 param {
499 type: LITERAL
500 literal {
501 type: V_OCTETS
502 v_octets {
503 value: "3"
504 }
505 }
506 }
507 }
508 }
509 operation {
510 source {
511 document_path {
512 type: MEMBER
513 value: "amount"
514 }
515 document_path {
516 type: ARRAY_INDEX
517 index: 1
518 }
519 name: "doc"
520 }
521 operation: ARRAY_INSERT
522 value {
523 type: LITERAL
524 literal {
525 type: V_DOUBLE
526 v_double: 6.2564
527 }
528 }
529 }
530 }
531
532
533 1 rows affected
534 Rows matched: 1 Changed: 1 Warnings: 0
535 RUN select doc from maincoll
536 doc
537 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
538 {"_id": "2", "name": ["Gonzalo"], "amount": [546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
539 {"_id": "3", "name": ["Abraham"], "amount": [546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
540 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
541 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
542 0 rows affected
543 Update array insert a Float
544 send Mysqlx.Crud.Update {
545 collection {
546 name: "maincoll"
547 schema: "mysqlxcoll"
548 }
549 data_model: TABLE
550 criteria {
551 type: OPERATOR
552 operator {
553 name: "=="
554 param {
555 type: IDENT
556 identifier {
557 name: "_id"
558 }
559 }
560 param {
561 type: LITERAL
562 literal {
563 type: V_OCTETS
564 v_octets {
565 value: "3"
566 }
567 }
568 }
569 }
570 }
571 operation {
572 source {
573 document_path {
574 type: MEMBER
575 value: "amount"
576 }
577 document_path {
578 type: ARRAY_INDEX
579 index: 0
580 }
581 name: "doc"
582 }
583 operation: ARRAY_INSERT
584 value {
585 type: LITERAL
586 literal {
587 type: V_FLOAT
588 v_float: 356.859
589 }
590 }
591 }
592 }
593
594
595 1 rows affected
596 Rows matched: 1 Changed: 1 Warnings: 0
597 RUN select doc from maincoll
598 doc
599 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
600 {"_id": "2", "name": ["Gonzalo"], "amount": [546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
601 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
602 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
603 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
604 0 rows affected
605 Update array insert a NULL
606 send Mysqlx.Crud.Update {
607 collection {
608 name: "maincoll"
609 schema: "mysqlxcoll"
610 }
611 data_model: TABLE
612 criteria {
613 type: OPERATOR
614 operator {
615 name: "=="
616 param {
617 type: IDENT
618 identifier {
619 name: "_id"
620 }
621 }
622 param {
623 type: LITERAL
624 literal {
625 type: V_OCTETS
626 v_octets {
627 value: "2"
628 }
629 }
630 }
631 }
632 }
633 operation {
634 source {
635 document_path {
636 type: MEMBER
637 value: "amount"
638 }
639 document_path {
640 type: ARRAY_INDEX
641 index: 0
642 }
643 name: "doc"
644 }
645 operation: ARRAY_INSERT
646 value {
647 type: LITERAL
648 literal {
649 type: V_NULL
650 }
651 }
652 }
653 }
654
655
656 1 rows affected
657 Rows matched: 1 Changed: 1 Warnings: 0
658 RUN select doc from maincoll
659 doc
660 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
661 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
662 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
663 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
664 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
665 0 rows affected
666 Update array insert in different index values
667 array index equal to 1
668 send Mysqlx.Crud.Update {
669 collection {
670 name: "maincoll"
671 schema: "mysqlxcoll"
672 }
673 data_model: TABLE
674 criteria {
675 type: OPERATOR
676 operator {
677 name: "=="
678 param {
679 type: IDENT
680 identifier {
681 name: "_id"
682 }
683 }
684 param {
685 type: LITERAL
686 literal {
687 type: V_OCTETS
688 v_octets {
689 value: "2"
690 }
691 }
692 }
693 }
694 }
695 operation {
696 source {
697 document_path {
698 type: MEMBER
699 value: "amount"
700 }
701 document_path {
702 type: ARRAY_INDEX
703 index: 1
704 }
705 name: "doc"
706 }
707 operation: ARRAY_INSERT
708 value {
709 type: LITERAL
710 literal {
711 type: V_DOUBLE
712 v_double: 1
713 }
714 }
715 }
716 }
717
718
719 1 rows affected
720 Rows matched: 1 Changed: 1 Warnings: 0
721 RUN select doc from maincoll
722 doc
723 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
724 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 1, 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
725 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
726 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
727 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
728 0 rows affected
729 array index equal to 2
730 send Mysqlx.Crud.Update {
731 collection {
732 name: "maincoll"
733 schema: "mysqlxcoll"
734 }
735 data_model: TABLE
736 criteria {
737 type: OPERATOR
738 operator {
739 name: "=="
740 param {
741 type: IDENT
742 identifier {
743 name: "_id"
744 }
745 }
746 param {
747 type: LITERAL
748 literal {
749 type: V_OCTETS
750 v_octets {
751 value: "2"
752 }
753 }
754 }
755 }
756 }
757 operation {
758 source {
759 document_path {
760 type: MEMBER
761 value: "amount"
762 }
763 document_path {
764 type: ARRAY_INDEX
765 index: 2
766 }
767 name: "doc"
768 }
769 operation: ARRAY_INSERT
770 value {
771 type: LITERAL
772 literal {
773 type: V_DOUBLE
774 v_double: 2
775 }
776 }
777 }
778 }
779
780
781 1 rows affected
782 Rows matched: 1 Changed: 1 Warnings: 0
783 RUN select doc from maincoll
784 doc
785 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
786 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 1, 2, 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
787 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
788 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
789 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
790 0 rows affected
791 array index equal to 3
792 send Mysqlx.Crud.Update {
793 collection {
794 name: "maincoll"
795 schema: "mysqlxcoll"
796 }
797 data_model: TABLE
798 criteria {
799 type: OPERATOR
800 operator {
801 name: "=="
802 param {
803 type: IDENT
804 identifier {
805 name: "_id"
806 }
807 }
808 param {
809 type: LITERAL
810 literal {
811 type: V_OCTETS
812 v_octets {
813 value: "2"
814 }
815 }
816 }
817 }
818 }
819 operation {
820 source {
821 document_path {
822 type: MEMBER
823 value: "amount"
824 }
825 document_path {
826 type: ARRAY_INDEX
827 index: 3
828 }
829 name: "doc"
830 }
831 operation: ARRAY_INSERT
832 value {
833 type: LITERAL
834 literal {
835 type: V_DOUBLE
836 v_double: 3
837 }
838 }
839 }
840 }
841
842
843 1 rows affected
844 Rows matched: 1 Changed: 1 Warnings: 0
845 RUN select doc from maincoll
846 doc
847 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
848 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 1, 2, 3, 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
849 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
850 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
851 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
852 0 rows affected
853 array index equal to 4
854 send Mysqlx.Crud.Update {
855 collection {
856 name: "maincoll"
857 schema: "mysqlxcoll"
858 }
859 data_model: TABLE
860 criteria {
861 type: OPERATOR
862 operator {
863 name: "=="
864 param {
865 type: IDENT
866 identifier {
867 name: "_id"
868 }
869 }
870 param {
871 type: LITERAL
872 literal {
873 type: V_OCTETS
874 v_octets {
875 value: "2"
876 }
877 }
878 }
879 }
880 }
881 operation {
882 source {
883 document_path {
884 type: MEMBER
885 value: "amount"
886 }
887 document_path {
888 type: ARRAY_INDEX
889 index: 4
890 }
891 name: "doc"
892 }
893 operation: ARRAY_INSERT
894 value {
895 type: LITERAL
896 literal {
897 type: V_DOUBLE
898 v_double: 4
899 }
900 }
901 }
902 }
903
904
905 1 rows affected
906 Rows matched: 1 Changed: 1 Warnings: 0
907 RUN select doc from maincoll
908 doc
909 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
910 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 1, 2, 3, 4, 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
911 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
912 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
913 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
914 0 rows affected
915 Update array insert a String into a only numbers member
916 send Mysqlx.Crud.Update {
917 collection {
918 name: "maincoll"
919 schema: "mysqlxcoll"
920 }
921 data_model: TABLE
922 criteria {
923 type: OPERATOR
924 operator {
925 name: "=="
926 param {
927 type: IDENT
928 identifier {
929 name: "_id"
930 }
931 }
932 param {
933 type: LITERAL
934 literal {
935 type: V_OCTETS
936 v_octets {
937 value: "2"
938 }
939 }
940 }
941 }
942 }
943 operation {
944 source {
945 document_path {
946 type: MEMBER
947 value: "amount"
948 }
949 document_path {
950 type: ARRAY_INDEX
951 index: 5
952 }
953 name: "doc"
954 }
955 operation: ARRAY_INSERT
956 value {
957 type: LITERAL
958 literal {
959 type: V_STRING
960 v_string {
961 value: "Index_5"
962 }
963 }
964 }
965 }
966 }
967
968
969 1 rows affected
970 Rows matched: 1 Changed: 1 Warnings: 0
971 RUN select doc from maincoll
972 doc
973 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor"], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
974 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
975 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
976 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
977 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
978 0 rows affected
979 Update array insert a number into a only String member
980 send Mysqlx.Crud.Update {
981 collection {
982 name: "maincoll"
983 schema: "mysqlxcoll"
984 }
985 data_model: TABLE
986 criteria {
987 type: OPERATOR
988 operator {
989 name: "=="
990 param {
991 type: IDENT
992 identifier {
993 name: "_id"
994 }
995 }
996 param {
997 type: LITERAL
998 literal {
999 type: V_OCTETS
1000 v_octets {
1001 value: "1"
1002 }
1003 }
1004 }
1005 }
1006 }
1007 operation {
1008 source {
1009 document_path {
1010 type: MEMBER
1011 value: "name"
1012 }
1013 document_path {
1014 type: ARRAY_INDEX
1015 index: 3
1016 }
1017 name: "doc"
1018 }
1019 operation: ARRAY_INSERT
1020 value {
1021 type: LITERAL
1022 literal {
1023 type: V_UINT
1024 v_unsigned_int: 253
1025 }
1026 }
1027 }
1028 }
1029
1030
1031 1 rows affected
1032 Rows matched: 1 Changed: 1 Warnings: 0
1033 RUN select doc from maincoll
1034 doc
1035 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", 253], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1036 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
1037 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1038 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1039 {"_id": "5", "name": ["Jhonny"], "amount": [546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1040 0 rows affected
1041 Update with order clause update last _id in desc order (_id = 5)
1042 send Mysqlx.Crud.Update {
1043 collection {
1044 name: "maincoll"
1045 schema: "mysqlxcoll"
1046 }
1047 data_model: TABLE
1048 criteria {
1049 type: OPERATOR
1050 operator {
1051 name: "!="
1052 param {
1053 type: IDENT
1054 identifier {
1055 name: "_id"
1056 }
1057 }
1058 param {
1059 type: LITERAL
1060 literal {
1061 type: V_OCTETS
1062 v_octets {
1063 value: "0"
1064 }
1065 }
1066 }
1067 }
1068 }
1069 limit {
1070 row_count: 1
1071 }
1072 order {
1073 expr {
1074 type: IDENT
1075 identifier {
1076 name: "_id"
1077 }
1078 }
1079 direction: DESC
1080 }
1081 operation {
1082 source {
1083 document_path {
1084 type: MEMBER
1085 value: "amount"
1086 }
1087 document_path {
1088 type: ARRAY_INDEX
1089 index: 0
1090 }
1091 name: "doc"
1092 }
1093 operation: ARRAY_INSERT
1094 value {
1095 type: LITERAL
1096 literal {
1097 type: V_DOUBLE
1098 v_double: 1234.567
1099 }
1100 }
1101 }
1102 }
1103
1104
1105 1 rows affected
1106 Rows matched: 1 Changed: 1 Warnings: 0
1107 RUN select doc from maincoll
1108 doc
1109 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", 253], "amount": [546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1110 {"_id": "2", "name": ["Gonzalo"], "amount": [null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
1111 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1112 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1113 {"_id": "5", "name": ["Jhonny"], "amount": [1234.567, 546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1114 0 rows affected
1115 Update with order clause update first two _id in asc order (_id = 1,2)
1116 send Mysqlx.Crud.Update {
1117 collection {
1118 name: "maincoll"
1119 schema: "mysqlxcoll"
1120 }
1121 data_model: TABLE
1122 criteria {
1123 type: OPERATOR
1124 operator {
1125 name: "!="
1126 param {
1127 type: IDENT
1128 identifier {
1129 name: "_id"
1130 }
1131 }
1132 param {
1133 type: LITERAL
1134 literal {
1135 type: V_OCTETS
1136 v_octets {
1137 value: "0"
1138 }
1139 }
1140 }
1141 }
1142 }
1143 limit {
1144 row_count: 2
1145 }
1146 order {
1147 expr {
1148 type: IDENT
1149 identifier {
1150 name: "_id"
1151 }
1152 }
1153 direction: ASC
1154 }
1155 operation {
1156 source {
1157 document_path {
1158 type: MEMBER
1159 value: "amount"
1160 }
1161 document_path {
1162 type: ARRAY_INDEX
1163 index: 0
1164 }
1165 name: "doc"
1166 }
1167 operation: ARRAY_INSERT
1168 value {
1169 type: LITERAL
1170 literal {
1171 type: V_DOUBLE
1172 v_double: 9876.543
1173 }
1174 }
1175 }
1176 }
1177
1178
1179 2 rows affected
1180 Rows matched: 2 Changed: 2 Warnings: 0
1181 RUN select doc from maincoll
1182 doc
1183 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", 253], "amount": [9876.543, 546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1184 {"_id": "2", "name": ["Gonzalo"], "amount": [9876.543, null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
1185 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1186 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1187 {"_id": "5", "name": ["Jhonny"], "amount": [1234.567, 546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1188 0 rows affected
1189 RUN START TRANSACTION
1190
1191 0 rows affected
1192 Update transaction rollback
1193 send Mysqlx.Crud.Update {
1194 collection {
1195 name: "maincoll"
1196 schema: "mysqlxcoll"
1197 }
1198 data_model: TABLE
1199 criteria {
1200 type: OPERATOR
1201 operator {
1202 name: "=="
1203 param {
1204 type: IDENT
1205 identifier {
1206 name: "_id"
1207 }
1208 }
1209 param {
1210 type: LITERAL
1211 literal {
1212 type: V_OCTETS
1213 v_octets {
1214 value: "1"
1215 }
1216 }
1217 }
1218 }
1219 }
1220 operation {
1221 source {
1222 document_path {
1223 type: MEMBER
1224 value: "name"
1225 }
1226 document_path {
1227 type: ARRAY_INDEX
1228 index: 3
1229 }
1230 name: "doc"
1231 }
1232 operation: ARRAY_INSERT
1233 value {
1234 type: LITERAL
1235 literal {
1236 type: V_STRING
1237 v_string {
1238 value: "i_must_be_rollback"
1239 }
1240 }
1241 }
1242 }
1243 }
1244
1245
1246 1 rows affected
1247 Rows matched: 1 Changed: 1 Warnings: 0
1248 RUN select doc from maincoll
1249 doc
1250 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", "i_must_be_rollback", 253], "amount": [9876.543, 546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1251 {"_id": "2", "name": ["Gonzalo"], "amount": [9876.543, null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
1252 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1253 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1254 {"_id": "5", "name": ["Jhonny"], "amount": [1234.567, 546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1255 0 rows affected
1256 RUN ROLLBACK
1257
1258 0 rows affected
1259 RUN select doc from maincoll
1260 doc
1261 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", 253], "amount": [9876.543, 546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1262 {"_id": "2", "name": ["Gonzalo"], "amount": [9876.543, null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
1263 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1264 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1265 {"_id": "5", "name": ["Jhonny"], "amount": [1234.567, 546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1266 0 rows affected
1267 Update transaction commit
1268 send Mysqlx.Crud.Update {
1269 collection {
1270 name: "maincoll"
1271 schema: "mysqlxcoll"
1272 }
1273 data_model: TABLE
1274 criteria {
1275 type: OPERATOR
1276 operator {
1277 name: "=="
1278 param {
1279 type: IDENT
1280 identifier {
1281 name: "_id"
1282 }
1283 }
1284 param {
1285 type: LITERAL
1286 literal {
1287 type: V_OCTETS
1288 v_octets {
1289 value: "1"
1290 }
1291 }
1292 }
1293 }
1294 }
1295 operation {
1296 source {
1297 document_path {
1298 type: MEMBER
1299 value: "name"
1300 }
1301 document_path {
1302 type: ARRAY_INDEX
1303 index: 3
1304 }
1305 name: "doc"
1306 }
1307 operation: ARRAY_INSERT
1308 value {
1309 type: LITERAL
1310 literal {
1311 type: V_STRING
1312 v_string {
1313 value: "i_must_be_commited"
1314 }
1315 }
1316 }
1317 }
1318 }
1319
1320
1321 1 rows affected
1322 Rows matched: 1 Changed: 1 Warnings: 0
1323 RUN COMMIT
1324
1325 0 rows affected
1326 RUN select doc from maincoll
1327 doc
1328 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", "i_must_be_commited", 253], "amount": [9876.543, 546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1329 {"_id": "2", "name": ["Gonzalo"], "amount": [9876.543, null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
1330 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1331 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1332 {"_id": "5", "name": ["Jhonny"], "amount": [1234.567, 546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1333 0 rows affected
1334 Update with non existing doc member
1335 send Mysqlx.Crud.Update {
1336 collection {
1337 name: "maincoll"
1338 schema: "mysqlxcoll"
1339 }
1340 data_model: TABLE
1341 criteria {
1342 type: OPERATOR
1343 operator {
1344 name: "=="
1345 param {
1346 type: IDENT
1347 identifier {
1348 name: "_id"
1349 }
1350 }
1351 param {
1352 type: LITERAL
1353 literal {
1354 type: V_OCTETS
1355 v_octets {
1356 value: "1"
1357 }
1358 }
1359 }
1360 }
1361 }
1362 operation {
1363 source {
1364 document_path {
1365 type: MEMBER
1366 value: "i_donot_exists"
1367 }
1368 document_path {
1369 type: ARRAY_INDEX
1370 index: 3
1371 }
1372 name: "iamnotadoc"
1373 }
1374 operation: ARRAY_INSERT
1375 value {
1376 type: LITERAL
1377 literal {
1378 type: V_STRING
1379 v_string {
1380 value: "Test"
1381 }
1382 }
1383 }
1384 }
1385 }
1386
1387 Got expected error: Unknown column 'iamnotadoc' in 'field list' (code 1054)
1388 RUN select doc from maincoll
1389 doc
1390 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", "i_must_be_commited", 253], "amount": [9876.543, 546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1391 {"_id": "2", "name": ["Gonzalo"], "amount": [9876.543, null, 1, 2, 3, 4, "Index_5", 546.12, 120.57], "last_name": ["extra_last_name_3", "Chrystens"]}
1392 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1393 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1394 {"_id": "5", "name": ["Jhonny"], "amount": [1234.567, 546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1395 0 rows affected
1396 Invalid type of update for Document
1397 send Mysqlx.Crud.Update {
1398 collection {
1399 name: "maincoll"
1400 schema: "mysqlxcoll"
1401 }
1402 data_model: TABLE
1403 criteria {
1404 type: OPERATOR
1405 operator {
1406 name: "=="
1407 param {
1408 type: IDENT
1409 identifier {
1410 name: "_id"
1411 }
1412 }
1413 param {
1414 type: LITERAL
1415 literal {
1416 type: V_OCTETS
1417 v_octets {
1418 value: "1"
1419 }
1420 }
1421 }
1422 }
1423 }
1424 operation {
1425 source {
1426 document_path {
1427 type: MEMBER
1428 value: "name"
1429 }
1430 document_path {
1431 type: ARRAY_INDEX
1432 index: 3
1433 }
1434 name: "doc"
1435 }
1436 operation: SET
1437 value {
1438 type: LITERAL
1439 literal {
1440 type: V_STRING
1441 v_string {
1442 value: "Test"
1443 }
1444 }
1445 }
1446 }
1447 }
1448
1449 Got expected error: Invalid column name to update (code 5052)
1450 Update array insert with an index value out of range
1451 send Mysqlx.Crud.Update {
1452 collection {
1453 name: "maincoll"
1454 schema: "mysqlxcoll"
1455 }
1456 data_model: TABLE
1457 criteria {
1458 type: OPERATOR
1459 operator {
1460 name: "=="
1461 param {
1462 type: IDENT
1463 identifier {
1464 name: "_id"
1465 }
1466 }
1467 param {
1468 type: LITERAL
1469 literal {
1470 type: V_OCTETS
1471 v_octets {
1472 value: "2"
1473 }
1474 }
1475 }
1476 }
1477 }
1478 operation {
1479 source {
1480 document_path {
1481 type: MEMBER
1482 value: "amount"
1483 }
1484 document_path {
1485 type: ARRAY_INDEX
1486 index: 9999999
1487 }
1488 name: "doc"
1489 }
1490 operation: ARRAY_INSERT
1491 value {
1492 type: LITERAL
1493 literal {
1494 type: V_DOUBLE
1495 v_double: 20
1496 }
1497 }
1498 }
1499 }
1500
1501
1502 1 rows affected
1503 Rows matched: 1 Changed: 1 Warnings: 0
1504 RUN select doc from maincoll
1505 doc
1506 {"_id": "1", "name": ["\"VictorArrayInsert\"", "Victor_Extra_Name", "Victor", "i_must_be_commited", 253], "amount": [9876.543, 546.12, 4.99], "last_name": ["extra_last_name_3", "extra_last_name_2", "Otero"]}
1507 {"_id": "2", "name": ["Gonzalo"], "amount": [9876.543, null, 1, 2, 3, 4, "Index_5", 546.12, 120.57, 20], "last_name": ["extra_last_name_3", "Chrystens"]}
1508 {"_id": "3", "name": ["Abraham"], "amount": [356.859, 546.12, 6.2564, 257.52, 74.56], "last_name": ["Vega"]}
1509 {"_id": "4", "name": ["Jennifer"], "amount": [257.52, 387.14], "last_name": ["extra_last_name", "Leon"]}
1510 {"_id": "5", "name": ["Jhonny"], "amount": [1234.567, 546.12, 257.52, 125.45], "last_name": ["extra_last_name", "Test"]}
1511 0 rows affected
1512 ================================================================================
1513 CLEAN UP
1514 ================================================================================
1515 RUN DROP USER updatearrayinsert@localhost
1516
1517 0 rows affected
1518 RUN SET SQL_SAFE_UPDATES = 1
1519
1520 0 rows affected
1521 Mysqlx.Ok {
1522 msg: "bye!"
1523 }
1524 ok
1525 DROP SCHEMA if EXISTS mysqlxcoll;