Debian Codegraph @

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

raw
1 =============================================
2 CRUD UPDATE ITEM_REMOVE SCENARIOS
3 =============================================
4
5 ================================================================================
6 PREAMBLE
7 ================================================================================
8 create user updateitemremove@localhost identified by 'updateitemremove';
9 grant all on *.* to updateitemremove@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 a valid collection with == operator
38 send Mysqlx.Crud.Update {
39 collection {
40 name: "maincoll"
41 schema: "mysqlxcoll"
42 }
43 data_model: DOCUMENT
44 criteria {
45 type: OPERATOR
46 operator {
47 name: "=="
48 param {
49 type: IDENT
50 identifier {
51 document_path {
52 type: MEMBER
53 value: "_id"
54 }
55 }
56 }
57 param {
58 type: LITERAL
59 literal {
60 type: V_OCTETS
61 v_octets {
62 value: "5"
63 }
64 }
65 }
66 }
67 }
68 operation {
69 source {
70 document_path {
71 type: MEMBER
72 value: "name"
73 }
74 }
75 operation: ITEM_REMOVE
76 }
77 }
78
79
80 1 rows affected
81 Rows matched: 1 Changed: 1 Warnings: 0
82 RUN select doc from maincoll
83 doc
84 {"_id": "1", "name": "Victor", "amount": 4.99, "last_name": "Otero"}
85 {"_id": "2", "name": "Gonzalo", "amount": 120.57, "last_name": "Chrystens"}
86 {"_id": "3", "name": "Abraham", "amount": 74.56, "last_name": "Vega"}
87 {"_id": "4", "name": "Jennifer", "amount": 387.14, "last_name": "Leon"}
88 {"_id": "5", "amount": 125.45, "last_name": "Test"}
89 0 rows affected
90 Update a valid collection with string value and > operator for multiple docs
91 send Mysqlx.Crud.Update {
92 collection {
93 name: "maincoll"
94 schema: "mysqlxcoll"
95 }
96 data_model: DOCUMENT
97 criteria {
98 type: OPERATOR
99 operator {
100 name: ">"
101 param {
102 type: IDENT
103 identifier {
104 document_path {
105 type: MEMBER
106 value: "_id"
107 }
108 }
109 }
110 param {
111 type: LITERAL
112 literal {
113 type: V_OCTETS
114 v_octets {
115 value: "3"
116 }
117 }
118 }
119 }
120 }
121 operation {
122 source {
123 document_path {
124 type: MEMBER
125 value: "name"
126 }
127 }
128 operation: ITEM_REMOVE
129 }
130 }
131
132
133 1 rows affected
134 Rows matched: 2 Changed: 1 Warnings: 0
135 RUN select doc from maincoll
136 doc
137 {"_id": "1", "name": "Victor", "amount": 4.99, "last_name": "Otero"}
138 {"_id": "2", "name": "Gonzalo", "amount": 120.57, "last_name": "Chrystens"}
139 {"_id": "3", "name": "Abraham", "amount": 74.56, "last_name": "Vega"}
140 {"_id": "4", "amount": 387.14, "last_name": "Leon"}
141 {"_id": "5", "amount": 125.45, "last_name": "Test"}
142 0 rows affected
143 Update a valid collection with string value and < operator for multiple docs
144 send Mysqlx.Crud.Update {
145 collection {
146 name: "maincoll"
147 schema: "mysqlxcoll"
148 }
149 data_model: DOCUMENT
150 criteria {
151 type: OPERATOR
152 operator {
153 name: "<"
154 param {
155 type: IDENT
156 identifier {
157 document_path {
158 type: MEMBER
159 value: "_id"
160 }
161 }
162 }
163 param {
164 type: LITERAL
165 literal {
166 type: V_OCTETS
167 v_octets {
168 value: "3"
169 }
170 }
171 }
172 }
173 }
174 operation {
175 source {
176 document_path {
177 type: MEMBER
178 value: "name"
179 }
180 }
181 operation: ITEM_REMOVE
182 }
183 }
184
185
186 2 rows affected
187 Rows matched: 2 Changed: 2 Warnings: 0
188 RUN select doc from maincoll
189 doc
190 {"_id": "1", "amount": 4.99, "last_name": "Otero"}
191 {"_id": "2", "amount": 120.57, "last_name": "Chrystens"}
192 {"_id": "3", "name": "Abraham", "amount": 74.56, "last_name": "Vega"}
193 {"_id": "4", "amount": 387.14, "last_name": "Leon"}
194 {"_id": "5", "amount": 125.45, "last_name": "Test"}
195 0 rows affected
196 Update a valid collection with string value and <= operator for multiple docs
197 send Mysqlx.Crud.Update {
198 collection {
199 name: "maincoll"
200 schema: "mysqlxcoll"
201 }
202 data_model: DOCUMENT
203 criteria {
204 type: OPERATOR
205 operator {
206 name: "<="
207 param {
208 type: IDENT
209 identifier {
210 document_path {
211 type: MEMBER
212 value: "_id"
213 }
214 }
215 }
216 param {
217 type: LITERAL
218 literal {
219 type: V_OCTETS
220 v_octets {
221 value: "3"
222 }
223 }
224 }
225 }
226 }
227 operation {
228 source {
229 document_path {
230 type: MEMBER
231 value: "last_name"
232 }
233 }
234 operation: ITEM_REMOVE
235 }
236 }
237
238
239 3 rows affected
240 Rows matched: 3 Changed: 3 Warnings: 0
241 RUN select doc from maincoll
242 doc
243 {"_id": "1", "amount": 4.99}
244 {"_id": "2", "amount": 120.57}
245 {"_id": "3", "name": "Abraham", "amount": 74.56}
246 {"_id": "4", "amount": 387.14, "last_name": "Leon"}
247 {"_id": "5", "amount": 125.45, "last_name": "Test"}
248 0 rows affected
249 Update a valid collection with string value and >= operator for multiple docs
250 send Mysqlx.Crud.Update {
251 collection {
252 name: "maincoll"
253 schema: "mysqlxcoll"
254 }
255 data_model: DOCUMENT
256 criteria {
257 type: OPERATOR
258 operator {
259 name: ">="
260 param {
261 type: IDENT
262 identifier {
263 document_path {
264 type: MEMBER
265 value: "_id"
266 }
267 }
268 }
269 param {
270 type: LITERAL
271 literal {
272 type: V_OCTETS
273 v_octets {
274 value: "4"
275 }
276 }
277 }
278 }
279 }
280 operation {
281 source {
282 document_path {
283 type: MEMBER
284 value: "last_name"
285 }
286 }
287 operation: ITEM_REMOVE
288 }
289 }
290
291
292 2 rows affected
293 Rows matched: 2 Changed: 2 Warnings: 0
294 RUN select doc from maincoll
295 doc
296 {"_id": "1", "amount": 4.99}
297 {"_id": "2", "amount": 120.57}
298 {"_id": "3", "name": "Abraham", "amount": 74.56}
299 {"_id": "4", "amount": 387.14}
300 {"_id": "5", "amount": 125.45}
301 0 rows affected
302 Update with order clause update last _id in desc order (_id = 5)
303 send Mysqlx.Crud.Update {
304 collection {
305 name: "maincoll"
306 schema: "mysqlxcoll"
307 }
308 data_model: DOCUMENT
309 criteria {
310 type: OPERATOR
311 operator {
312 name: "!="
313 param {
314 type: IDENT
315 identifier {
316 document_path {
317 type: MEMBER
318 value: "_id"
319 }
320 }
321 }
322 param {
323 type: LITERAL
324 literal {
325 type: V_OCTETS
326 v_octets {
327 value: "0"
328 }
329 }
330 }
331 }
332 }
333 limit {
334 row_count: 1
335 }
336 order {
337 expr {
338 type: IDENT
339 identifier {
340 name: "_id"
341 }
342 }
343 direction: DESC
344 }
345 operation {
346 source {
347 document_path {
348 type: MEMBER
349 value: "amount"
350 }
351 }
352 operation: ITEM_REMOVE
353 }
354 }
355
356
357 1 rows affected
358 Rows matched: 1 Changed: 1 Warnings: 0
359 RUN select doc from maincoll
360 doc
361 {"_id": "1", "amount": 4.99}
362 {"_id": "2", "amount": 120.57}
363 {"_id": "3", "name": "Abraham", "amount": 74.56}
364 {"_id": "4", "amount": 387.14}
365 {"_id": "5"}
366 0 rows affected
367 Update with order clause update first two _id in asc order (_id = 1,2)
368 send Mysqlx.Crud.Update {
369 collection {
370 name: "maincoll"
371 schema: "mysqlxcoll"
372 }
373 data_model: DOCUMENT
374 criteria {
375 type: OPERATOR
376 operator {
377 name: "!="
378 param {
379 type: IDENT
380 identifier {
381 document_path {
382 type: MEMBER
383 value: "_id"
384 }
385 }
386 }
387 param {
388 type: LITERAL
389 literal {
390 type: V_OCTETS
391 v_octets {
392 value: "0"
393 }
394 }
395 }
396 }
397 }
398 limit {
399 row_count: 2
400 }
401 order {
402 expr {
403 type: IDENT
404 identifier {
405 name: "_id"
406 }
407 }
408 direction: ASC
409 }
410 operation {
411 source {
412 document_path {
413 type: MEMBER
414 value: "amount"
415 }
416 }
417 operation: ITEM_REMOVE
418 }
419 }
420
421
422 2 rows affected
423 Rows matched: 2 Changed: 2 Warnings: 0
424 RUN select doc from maincoll
425 doc
426 {"_id": "1"}
427 {"_id": "2"}
428 {"_id": "3", "name": "Abraham", "amount": 74.56}
429 {"_id": "4", "amount": 387.14}
430 {"_id": "5"}
431 0 rows affected
432 Update a valid collection with string value and != operator for multiple docs
433 send Mysqlx.Crud.Update {
434 collection {
435 name: "maincoll"
436 schema: "mysqlxcoll"
437 }
438 data_model: DOCUMENT
439 criteria {
440 type: OPERATOR
441 operator {
442 name: "!="
443 param {
444 type: IDENT
445 identifier {
446 document_path {
447 type: MEMBER
448 value: "_id"
449 }
450 }
451 }
452 param {
453 type: LITERAL
454 literal {
455 type: V_OCTETS
456 v_octets {
457 value: "2"
458 }
459 }
460 }
461 }
462 }
463 operation {
464 source {
465 document_path {
466 type: MEMBER
467 value: "amount"
468 }
469 }
470 operation: ITEM_REMOVE
471 }
472 }
473
474
475 2 rows affected
476 Rows matched: 4 Changed: 2 Warnings: 0
477 RUN select doc from maincoll
478 doc
479 {"_id": "1"}
480 {"_id": "2"}
481 {"_id": "3", "name": "Abraham"}
482 {"_id": "4"}
483 {"_id": "5"}
484 0 rows affected
485 RUN START TRANSACTION
486
487 0 rows affected
488 Update transaction rollback
489 send Mysqlx.Crud.Update {
490 collection {
491 name: "maincoll"
492 schema: "mysqlxcoll"
493 }
494 data_model: DOCUMENT
495 criteria {
496 type: OPERATOR
497 operator {
498 name: "=="
499 param {
500 type: IDENT
501 identifier {
502 document_path {
503 type: MEMBER
504 value: "_id"
505 }
506 }
507 }
508 param {
509 type: LITERAL
510 literal {
511 type: V_OCTETS
512 v_octets {
513 value: "3"
514 }
515 }
516 }
517 }
518 }
519 operation {
520 source {
521 document_path {
522 type: MEMBER
523 value: "name"
524 }
525 }
526 operation: ITEM_REMOVE
527 }
528 }
529
530
531 1 rows affected
532 Rows matched: 1 Changed: 1 Warnings: 0
533 RUN select doc from maincoll
534 doc
535 {"_id": "1"}
536 {"_id": "2"}
537 {"_id": "3"}
538 {"_id": "4"}
539 {"_id": "5"}
540 0 rows affected
541 RUN ROLLBACK
542
543 0 rows affected
544 RUN select doc from maincoll
545 doc
546 {"_id": "1"}
547 {"_id": "2"}
548 {"_id": "3", "name": "Abraham"}
549 {"_id": "4"}
550 {"_id": "5"}
551 0 rows affected
552 RUN START TRANSACTION
553
554 0 rows affected
555 Update transaction commit
556 send Mysqlx.Crud.Update {
557 collection {
558 name: "maincoll"
559 schema: "mysqlxcoll"
560 }
561 data_model: DOCUMENT
562 criteria {
563 type: OPERATOR
564 operator {
565 name: "=="
566 param {
567 type: IDENT
568 identifier {
569 document_path {
570 type: MEMBER
571 value: "_id"
572 }
573 }
574 }
575 param {
576 type: LITERAL
577 literal {
578 type: V_OCTETS
579 v_octets {
580 value: "3"
581 }
582 }
583 }
584 }
585 }
586 operation {
587 source {
588 document_path {
589 type: MEMBER
590 value: "name"
591 }
592 }
593 operation: ITEM_REMOVE
594 }
595 }
596
597
598 1 rows affected
599 Rows matched: 1 Changed: 1 Warnings: 0
600 RUN COMMIT
601
602 0 rows affected
603 RUN select doc from maincoll
604 doc
605 {"_id": "1"}
606 {"_id": "2"}
607 {"_id": "3"}
608 {"_id": "4"}
609 {"_id": "5"}
610 0 rows affected
611 Update with non existing doc member
612 send Mysqlx.Crud.Update {
613 collection {
614 name: "maincoll"
615 schema: "mysqlxcoll"
616 }
617 data_model: DOCUMENT
618 criteria {
619 type: OPERATOR
620 operator {
621 name: "!="
622 param {
623 type: IDENT
624 identifier {
625 document_path {
626 type: MEMBER
627 value: "_id"
628 }
629 }
630 }
631 param {
632 type: LITERAL
633 literal {
634 type: V_OCTETS
635 v_octets {
636 value: "2"
637 }
638 }
639 }
640 }
641 }
642 operation {
643 source {
644 document_path {
645 type: MEMBER
646 value: "notamember"
647 }
648 }
649 operation: ITEM_REMOVE
650 }
651 }
652
653
654 0 rows affected
655 Rows matched: 4 Changed: 0 Warnings: 0
656 RUN select doc from maincoll
657 doc
658 {"_id": "1"}
659 {"_id": "2"}
660 {"_id": "3"}
661 {"_id": "4"}
662 {"_id": "5"}
663 0 rows affected
664 Invalid type of update for Document
665 send Mysqlx.Crud.Update {
666 collection {
667 name: "maincoll"
668 schema: "mysqlxcoll"
669 }
670 data_model: DOCUMENT
671 criteria {
672 type: OPERATOR
673 operator {
674 name: ">"
675 param {
676 type: IDENT
677 identifier {
678 document_path {
679 type: MEMBER
680 value: "_id"
681 }
682 }
683 }
684 param {
685 type: LITERAL
686 literal {
687 type: V_OCTETS
688 v_octets {
689 value: "3"
690 }
691 }
692 }
693 }
694 }
695 operation {
696 source {
697 document_path {
698 type: MEMBER
699 value: "name"
700 }
701 }
702 operation: SET
703 value {
704 type: LITERAL
705 literal {
706 type: V_STRING
707 v_string {
708 value: "xtest"
709 }
710 }
711 }
712 }
713 }
714
715 Got expected error: Invalid type of update operation for document (code 5051)
716 Invalid operator for Criteria
717 send Mysqlx.Crud.Update {
718 collection {
719 name: "maincoll"
720 schema: "mysqlxcoll"
721 }
722 data_model: DOCUMENT
723 criteria {
724 type: OPERATOR
725 operator {
726 name: "not an operator"
727 param {
728 type: IDENT
729 identifier {
730 document_path {
731 type: MEMBER
732 value: "_id"
733 }
734 }
735 }
736 param {
737 type: LITERAL
738 literal {
739 type: V_OCTETS
740 v_octets {
741 value: "3"
742 }
743 }
744 }
745 }
746 }
747 operation {
748 source {
749 document_path {
750 type: MEMBER
751 value: "name"
752 }
753 }
754 operation: ITEM_REMOVE
755 }
756 }
757
758 Got expected error: Invalid operator not an operator (code 5150)
759 # Remove already removed item
760 RUN select doc from mysqlxcoll.maincoll
761 doc
762 {"_id": "1"}
763 {"_id": "2"}
764 {"_id": "3"}
765 {"_id": "4"}
766 {"_id": "5"}
767 0 rows affected
768 send Mysqlx.Crud.Update {
769 collection {
770 name: "maincoll"
771 schema: "mysqlxcoll"
772 }
773 data_model: DOCUMENT
774 operation {
775 source {
776 document_path {
777 type: MEMBER
778 value: "amount"
779 }
780 }
781 operation: ITEM_REMOVE
782 }
783 }
784
785
786 0 rows affected
787 Rows matched: 5 Changed: 0 Warnings: 0
788 RUN select doc from mysqlxcoll.maincoll
789 doc
790 {"_id": "1"}
791 {"_id": "2"}
792 {"_id": "3"}
793 {"_id": "4"}
794 {"_id": "5"}
795 0 rows affected
796 send Mysqlx.Crud.Update {
797 collection {
798 name: "maincoll"
799 schema: "mysqlxcoll"
800 }
801 data_model: DOCUMENT
802 operation {
803 source {
804 document_path {
805 type: MEMBER
806 value: "amount"
807 }
808 }
809 operation: ITEM_REMOVE
810 }
811 }
812
813
814 0 rows affected
815 Rows matched: 5 Changed: 0 Warnings: 0
816 RUN select doc from mysqlxcoll.maincoll
817 doc
818 {"_id": "1"}
819 {"_id": "2"}
820 {"_id": "3"}
821 {"_id": "4"}
822 {"_id": "5"}
823 0 rows affected
824 # Can not remove immutable document member _id
825 send Mysqlx.Crud.Update {
826 collection {
827 name: "maincoll"
828 schema: "mysqlxcoll"
829 }
830 data_model: DOCUMENT
831 operation {
832 source {
833 document_path {
834 type: MEMBER
835 value: "_id"
836 }
837 }
838 operation: ITEM_REMOVE
839 }
840 }
841
842 Got expected error:
843 Mysqlx.Error {
844 severity: ERROR
845 code: 5053
846 msg: "Forbidden update operation on \'$._id\' member"
847 sql_state: "HY000"
848 }
849
850 RUN select doc from mysqlxcoll.maincoll
851 doc
852 {"_id": "1"}
853 {"_id": "2"}
854 {"_id": "3"}
855 {"_id": "4"}
856 {"_id": "5"}
857 0 rows affected
858 ================================================================================
859 CLEAN UP
860 ================================================================================
861 RUN DROP USER updateitemremove@localhost
862
863 0 rows affected
864 RUN SET SQL_SAFE_UPDATES = 1
865
866 0 rows affected
867 Mysqlx.Ok {
868 msg: "bye!"
869 }
870 ok
871 DROP SCHEMA if EXISTS mysqlxcoll;