Debian Codegraph @

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

raw
1 ==============================================
2 CRUD UPDATE SCENARIOS
3 ==============================================
4
5 ================================================================================
6 PREAMBLE
7 ================================================================================
8 create user updatecruduser@localhost identified by 'updatecruduser';
9 grant all on *.* to updatecruduser@localhost;
10 ================================================================================
11 TEST START
12 ================================================================================
13 RUN DROP SCHEMA if EXISTS mysqlxplugin
14
15 0 rows affected
16 RUN CREATE SCHEMA mysqlxplugin DEFAULT CHARSET='utf8'
17
18 1 rows affected
19 RUN USE mysqlxplugin
20
21 0 rows affected
22 RUN CREATE TABLE categories (
23 CategoryID int NOT NULL AUTO_INCREMENT,
24 CategoryName varchar(100),
25 CategoryDescription varchar(200),
26 CategoryIMEI tinyint,
27 CategoryDecimal decimal(5,2),
28 CategoryBool bit(1),
29 PRIMARY key (CategoryID)
30 )
31
32 0 rows affected
33 RUN INSERT INTO categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal,CategoryBool)
34 VALUES
35 (1,'Sports','Sports related category',1,235.15,true),
36 (2,'Entertaiment','Entertaiment related category',2,235.15,false),
37 (3, 'Home','Home related category',3,235.15,true),
38 (4, 'Kitchen','Kitchen related category',4,235.15,false),
39 (5, 'Garden','Garden related category',5,535.15,true),
40 (6, 'Toys','Toys related category',6,635.15,false)
41
42 6 rows affected
43 last insert id: 6
44 Records: 6 Duplicates: 0 Warnings: 0
45 RUN SET SQL_SAFE_UPDATES = 1
46
47 0 rows affected
48 RUN SHOW STATUS like 'Mysqlx_crud_update'
49 Variable_name Value
50 Mysqlx_crud_update 0
51 0 rows affected
52 Update varchar with where based in key column
53 send Mysqlx.Crud.Update {
54 collection {
55 name: "categories"
56 schema: "mysqlxplugin"
57 }
58 data_model: TABLE
59 criteria {
60 type: OPERATOR
61 operator {
62 name: "=="
63 param {
64 type: IDENT
65 identifier {
66 name: "CategoryID"
67 }
68 }
69 param {
70 type: LITERAL
71 literal {
72 type: V_SINT
73 v_signed_int: 1
74 }
75 }
76 }
77 }
78 operation {
79 source {
80 name: "CategoryName"
81 }
82 operation: SET
83 value {
84 type: LITERAL
85 literal {
86 type: V_STRING
87 v_string {
88 value: "SportsUpdate"
89 }
90 }
91 }
92 }
93 }
94
95
96 1 rows affected
97 Rows matched: 1 Changed: 1 Warnings: 0
98 RUN select * from mysqlxplugin.categories where CategoryID=1
99 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
100 1 SportsUpdate Sports related category 1 235.15 1
101 0 rows affected
102 RUN SHOW STATUS like 'Mysqlx_crud_update'
103 Variable_name Value
104 Mysqlx_crud_update 1
105 0 rows affected
106 Update Tiny Int with where based in key column
107 send Mysqlx.Crud.Update {
108 collection {
109 name: "categories"
110 schema: "mysqlxplugin"
111 }
112 data_model: TABLE
113 criteria {
114 type: OPERATOR
115 operator {
116 name: "=="
117 param {
118 type: IDENT
119 identifier {
120 name: "CategoryID"
121 }
122 }
123 param {
124 type: LITERAL
125 literal {
126 type: V_SINT
127 v_signed_int: 1
128 }
129 }
130 }
131 }
132 operation {
133 source {
134 name: "CategoryIMEI"
135 }
136 operation: SET
137 value {
138 type: LITERAL
139 literal {
140 type: V_SINT
141 v_signed_int: 110
142 }
143 }
144 }
145 }
146
147
148 1 rows affected
149 Rows matched: 1 Changed: 1 Warnings: 0
150 RUN select * from mysqlxplugin.categories where CategoryID=1
151 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
152 1 SportsUpdate Sports related category 110 235.15 1
153 0 rows affected
154 RUN SHOW STATUS like 'Mysqlx_crud_update'
155 Variable_name Value
156 Mysqlx_crud_update 2
157 0 rows affected
158 Update Decimal with where based in key column
159 send Mysqlx.Crud.Update {
160 collection {
161 name: "categories"
162 schema: "mysqlxplugin"
163 }
164 data_model: TABLE
165 criteria {
166 type: OPERATOR
167 operator {
168 name: "=="
169 param {
170 type: IDENT
171 identifier {
172 name: "CategoryID"
173 }
174 }
175 param {
176 type: LITERAL
177 literal {
178 type: V_SINT
179 v_signed_int: 1
180 }
181 }
182 }
183 }
184 operation {
185 source {
186 name: "CategoryDecimal"
187 }
188 operation: SET
189 value {
190 type: LITERAL
191 literal {
192 type: V_DOUBLE
193 v_double: 613.57
194 }
195 }
196 }
197 }
198
199
200 1 rows affected
201 Rows matched: 1 Changed: 1 Warnings: 0
202 RUN select * from mysqlxplugin.categories where CategoryID in (1,2)
203 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
204 1 SportsUpdate Sports related category 110 613.57 1
205 2 Entertaiment Entertaiment related category 2 235.15 0
206 0 rows affected
207 RUN SHOW STATUS like 'Mysqlx_crud_update'
208 Variable_name Value
209 Mysqlx_crud_update 3
210 0 rows affected
211 Update with Quotes value
212 send Mysqlx.Crud.Update {
213 collection {
214 name: "categories"
215 schema: "mysqlxplugin"
216 }
217 data_model: TABLE
218 criteria {
219 type: OPERATOR
220 operator {
221 name: "=="
222 param {
223 type: IDENT
224 identifier {
225 name: "CategoryID"
226 }
227 }
228 param {
229 type: LITERAL
230 literal {
231 type: V_SINT
232 v_signed_int: 2
233 }
234 }
235 }
236 }
237 operation {
238 source {
239 name: "CategoryDescription"
240 }
241 operation: SET
242 value {
243 type: LITERAL
244 literal {
245 type: V_STRING
246 v_string {
247 value: "\"Quotes\""
248 }
249 }
250 }
251 }
252 }
253
254
255 1 rows affected
256 Rows matched: 1 Changed: 1 Warnings: 0
257 RUN select * from mysqlxplugin.categories where CategoryID=2
258 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
259 2 Entertaiment "Quotes" 2 235.15 0
260 0 rows affected
261 RUN SHOW STATUS like 'Mysqlx_crud_update'
262 Variable_name Value
263 Mysqlx_crud_update 4
264 0 rows affected
265 Update with NULL value
266 send Mysqlx.Crud.Update {
267 collection {
268 name: "categories"
269 schema: "mysqlxplugin"
270 }
271 data_model: TABLE
272 criteria {
273 type: OPERATOR
274 operator {
275 name: "=="
276 param {
277 type: IDENT
278 identifier {
279 name: "CategoryID"
280 }
281 }
282 param {
283 type: LITERAL
284 literal {
285 type: V_SINT
286 v_signed_int: 2
287 }
288 }
289 }
290 }
291 operation {
292 source {
293 name: "CategoryName"
294 }
295 operation: SET
296 value {
297 type: LITERAL
298 literal {
299 type: V_NULL
300 }
301 }
302 }
303 }
304
305
306 1 rows affected
307 Rows matched: 1 Changed: 1 Warnings: 0
308 RUN select * from mysqlxplugin.categories where CategoryID in (2,3)
309 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
310 2 null "Quotes" 2 235.15 0
311 3 Home Home related category 3 235.15 1
312 0 rows affected
313 RUN SHOW STATUS like 'Mysqlx_crud_update'
314 Variable_name Value
315 Mysqlx_crud_update 5
316 0 rows affected
317 Update with Corner decimal value and != operator
318 send Mysqlx.Crud.Update {
319 collection {
320 name: "categories"
321 schema: "mysqlxplugin"
322 }
323 data_model: TABLE
324 criteria {
325 type: OPERATOR
326 operator {
327 name: "!="
328 param {
329 type: IDENT
330 identifier {
331 name: "CategoryID"
332 }
333 }
334 param {
335 type: LITERAL
336 literal {
337 type: V_SINT
338 v_signed_int: 2
339 }
340 }
341 }
342 }
343 operation {
344 source {
345 name: "CategoryDecimal"
346 }
347 operation: SET
348 value {
349 type: LITERAL
350 literal {
351 type: V_DOUBLE
352 v_double: 999.99
353 }
354 }
355 }
356 }
357
358
359 5 rows affected
360 Rows matched: 5 Changed: 5 Warnings: 0
361 RUN select * from mysqlxplugin.categories where CategoryID != 2
362 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
363 1 SportsUpdate Sports related category 110 999.99 1
364 3 Home Home related category 3 999.99 1
365 4 Kitchen Kitchen related category 4 999.99 0
366 5 Garden Garden related category 5 999.99 1
367 6 Toys Toys related category 6 999.99 0
368 0 rows affected
369 RUN SHOW STATUS like 'Mysqlx_crud_update'
370 Variable_name Value
371 Mysqlx_crud_update 6
372 0 rows affected
373 Update with Corner decimal value and "in" operator
374 send Mysqlx.Crud.Update {
375 collection {
376 name: "categories"
377 schema: "mysqlxplugin"
378 }
379 data_model: TABLE
380 criteria {
381 type: OPERATOR
382 operator {
383 name: "in"
384 param {
385 type: IDENT
386 identifier {
387 name: "CategoryID"
388 }
389 }
390 param {
391 type: LITERAL
392 literal {
393 type: V_SINT
394 v_signed_int: 3
395 }
396 }
397 param {
398 type: LITERAL
399 literal {
400 type: V_SINT
401 v_signed_int: 4
402 }
403 }
404 }
405 }
406 operation {
407 source {
408 name: "CategoryDecimal"
409 }
410 operation: SET
411 value {
412 type: LITERAL
413 literal {
414 type: V_DOUBLE
415 v_double: -999.99
416 }
417 }
418 }
419 }
420
421
422 2 rows affected
423 Rows matched: 2 Changed: 2 Warnings: 0
424 RUN select * from mysqlxplugin.categories where CategoryID in (3,4)
425 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
426 3 Home Home related category 3 -999.99 1
427 4 Kitchen Kitchen related category 4 -999.99 0
428 0 rows affected
429 RUN SHOW STATUS like 'Mysqlx_crud_update'
430 Variable_name Value
431 Mysqlx_crud_update 7
432 0 rows affected
433 Update with Corner decimal value and != operator
434 send Mysqlx.Crud.Update {
435 collection {
436 name: "categories"
437 schema: "mysqlxplugin"
438 }
439 data_model: TABLE
440 criteria {
441 type: OPERATOR
442 operator {
443 name: "!="
444 param {
445 type: IDENT
446 identifier {
447 name: "CategoryID"
448 }
449 }
450 param {
451 type: LITERAL
452 literal {
453 type: V_SINT
454 v_signed_int: 2
455 }
456 }
457 }
458 }
459 operation {
460 source {
461 name: "CategoryIMEI"
462 }
463 operation: SET
464 value {
465 type: LITERAL
466 literal {
467 type: V_DOUBLE
468 v_double: 127
469 }
470 }
471 }
472 }
473
474
475 5 rows affected
476 Rows matched: 5 Changed: 5 Warnings: 0
477 RUN select * from mysqlxplugin.categories where CategoryID != 2
478 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
479 1 SportsUpdate Sports related category 127 999.99 1
480 3 Home Home related category 127 -999.99 1
481 4 Kitchen Kitchen related category 127 -999.99 0
482 5 Garden Garden related category 127 999.99 1
483 6 Toys Toys related category 127 999.99 0
484 0 rows affected
485 RUN SHOW STATUS like 'Mysqlx_crud_update'
486 Variable_name Value
487 Mysqlx_crud_update 8
488 0 rows affected
489 Update with Corner decimal value and "in" operator
490 send Mysqlx.Crud.Update {
491 collection {
492 name: "categories"
493 schema: "mysqlxplugin"
494 }
495 data_model: TABLE
496 criteria {
497 type: OPERATOR
498 operator {
499 name: "in"
500 param {
501 type: IDENT
502 identifier {
503 name: "CategoryID"
504 }
505 }
506 param {
507 type: LITERAL
508 literal {
509 type: V_SINT
510 v_signed_int: 4
511 }
512 }
513 param {
514 type: LITERAL
515 literal {
516 type: V_SINT
517 v_signed_int: 5
518 }
519 }
520 }
521 }
522 operation {
523 source {
524 name: "CategoryIMEI"
525 }
526 operation: SET
527 value {
528 type: LITERAL
529 literal {
530 type: V_DOUBLE
531 v_double: -128
532 }
533 }
534 }
535 }
536
537
538 2 rows affected
539 Rows matched: 2 Changed: 2 Warnings: 0
540 RUN select * from mysqlxplugin.categories where CategoryID in (4,5)
541 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
542 4 Kitchen Kitchen related category -128 -999.99 0
543 5 Garden Garden related category -128 999.99 1
544 0 rows affected
545 RUN SHOW STATUS like 'Mysqlx_crud_update'
546 Variable_name Value
547 Mysqlx_crud_update 9
548 0 rows affected
549 Mysqlx.Ok {
550 msg: "bye!"
551 }
552 ok
553 .
554 #---------- TEST WITH SAFE MODE OFF ----------#
555 .
556 RUN USE mysqlxplugin
557
558 0 rows affected
559 RUN SET SQL_SAFE_UPDATES = 0
560
561 0 rows affected
562 Update with wrong schema name
563 send Mysqlx.Crud.Update {
564 collection {
565 name: "categories"
566 schema: "noschemalikethis"
567 }
568 data_model: TABLE
569 criteria {
570 type: OPERATOR
571 operator {
572 name: "in"
573 param {
574 type: IDENT
575 identifier {
576 name: "CategoryID"
577 }
578 }
579 param {
580 type: LITERAL
581 literal {
582 type: V_SINT
583 v_signed_int: 4
584 }
585 }
586 param {
587 type: LITERAL
588 literal {
589 type: V_SINT
590 v_signed_int: 5
591 }
592 }
593 }
594 }
595 operation {
596 source {
597 name: "CategoryIMEI"
598 }
599 operation: SET
600 value {
601 type: LITERAL
602 literal {
603 type: V_DOUBLE
604 v_double: 101
605 }
606 }
607 }
608 }
609
610 Mysqlx.Error {
611 severity: ERROR
612 code: 1049
613 msg: "Unknown database \'noschemalikethis\'"
614 sql_state: "42000"
615 }
616
617 Update with wrong table name
618 send Mysqlx.Crud.Update {
619 collection {
620 name: "notablelikethis"
621 schema: "mysqlxplugin"
622 }
623 data_model: TABLE
624 criteria {
625 type: OPERATOR
626 operator {
627 name: "in"
628 param {
629 type: IDENT
630 identifier {
631 name: "CategoryID"
632 }
633 }
634 param {
635 type: LITERAL
636 literal {
637 type: V_SINT
638 v_signed_int: 4
639 }
640 }
641 param {
642 type: LITERAL
643 literal {
644 type: V_SINT
645 v_signed_int: 5
646 }
647 }
648 }
649 }
650 operation {
651 source {
652 name: "CategoryIMEI"
653 }
654 operation: SET
655 value {
656 type: LITERAL
657 literal {
658 type: V_DOUBLE
659 v_double: 101.02
660 }
661 }
662 }
663 }
664
665 Mysqlx.Error {
666 severity: ERROR
667 code: 1146
668 msg: "Table \'mysqlxplugin.notablelikethis\' doesn\'t exist"
669 sql_state: "42S02"
670 }
671
672 Update with wrong column name
673 send Mysqlx.Crud.Update {
674 collection {
675 name: "categories"
676 schema: "mysqlxplugin"
677 }
678 data_model: TABLE
679 criteria {
680 type: OPERATOR
681 operator {
682 name: "in"
683 param {
684 type: IDENT
685 identifier {
686 name: "nocolumnname"
687 }
688 }
689 param {
690 type: LITERAL
691 literal {
692 type: V_SINT
693 v_signed_int: 4
694 }
695 }
696 param {
697 type: LITERAL
698 literal {
699 type: V_SINT
700 v_signed_int: 5
701 }
702 }
703 }
704 }
705 operation {
706 source {
707 name: "CategoryIMEI"
708 }
709 operation: SET
710 value {
711 type: LITERAL
712 literal {
713 type: V_DOUBLE
714 v_double: 101
715 }
716 }
717 }
718 }
719
720 Mysqlx.Error {
721 severity: ERROR
722 code: 1054
723 msg: "Unknown column \'nocolumnname\' in \'where clause\'"
724 sql_state: "42S22"
725 }
726
727 Update decimal value with a corner value plus 1
728 send Mysqlx.Crud.Update {
729 collection {
730 name: "categories"
731 schema: "mysqlxplugin"
732 }
733 data_model: TABLE
734 criteria {
735 type: OPERATOR
736 operator {
737 name: "=="
738 param {
739 type: IDENT
740 identifier {
741 name: "CategoryID"
742 }
743 }
744 param {
745 type: LITERAL
746 literal {
747 type: V_SINT
748 v_signed_int: 4
749 }
750 }
751 }
752 }
753 operation {
754 source {
755 name: "CategoryDecimal"
756 }
757 operation: SET
758 value {
759 type: LITERAL
760 literal {
761 type: V_DOUBLE
762 v_double: 1000
763 }
764 }
765 }
766 }
767
768 Got expected error: Out of range value for column 'CategoryDecimal' at row 1 (code 1264)
769 Update tiny int value with a corner value plus 1
770 send Mysqlx.Crud.Update {
771 collection {
772 name: "categories"
773 schema: "mysqlxplugin"
774 }
775 data_model: TABLE
776 criteria {
777 type: OPERATOR
778 operator {
779 name: "=="
780 param {
781 type: IDENT
782 identifier {
783 name: "CategoryID"
784 }
785 }
786 param {
787 type: LITERAL
788 literal {
789 type: V_SINT
790 v_signed_int: 4
791 }
792 }
793 }
794 }
795 operation {
796 source {
797 name: "CategoryIMEI"
798 }
799 operation: SET
800 value {
801 type: LITERAL
802 literal {
803 type: V_DOUBLE
804 v_double: -129
805 }
806 }
807 }
808 }
809
810 Got expected error: Out of range value for column 'CategoryIMEI' at row 1 (code 1264)
811 Update multiple column
812 RUN select * from mysqlxplugin.categories where CategoryID=4
813 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
814 4 Kitchen Kitchen related category -128 -999.99 0
815 0 rows affected
816 send Mysqlx.Crud.Update {
817 collection {
818 name: "categories"
819 schema: "mysqlxplugin"
820 }
821 data_model: TABLE
822 criteria {
823 type: OPERATOR
824 operator {
825 name: "=="
826 param {
827 type: IDENT
828 identifier {
829 name: "CategoryID"
830 }
831 }
832 param {
833 type: LITERAL
834 literal {
835 type: V_SINT
836 v_signed_int: 4
837 }
838 }
839 }
840 }
841 operation {
842 source {
843 name: "CategoryIMEI"
844 }
845 operation: SET
846 value {
847 type: LITERAL
848 literal {
849 type: V_DOUBLE
850 v_double: -99
851 }
852 }
853 }
854 operation {
855 source {
856 name: "CategoryDecimal"
857 }
858 operation: SET
859 value {
860 type: LITERAL
861 literal {
862 type: V_DOUBLE
863 v_double: 501.11
864 }
865 }
866 }
867 }
868
869
870 1 rows affected
871 Rows matched: 1 Changed: 1 Warnings: 0
872 RUN select * from mysqlxplugin.categories where CategoryID=4
873 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal CategoryBool
874 4 Kitchen Kitchen related category -99 501.11 0
875 0 rows affected
876 Mysqlx.Ok {
877 msg: "bye!"
878 }
879 ok
880 CREATE SCHEMA mysqlxcoll;
881
882 command ok
883 RUN SET SQL_SAFE_UPDATES = 0
884
885 0 rows affected
886 RUN USE mysqlxcoll
887
888 0 rows affected
889 RUN INSERT INTO maincoll (doc) values ('{"_id": "1", "name": "Victor", "last_name": "Otero","amount": 4.99}')
890
891 1 rows affected
892 RUN INSERT INTO maincoll (doc) values ('{"_id": "2", "name": "Gonzalo", "last_name": "Chrystens","amount": 120.57}')
893
894 1 rows affected
895 RUN INSERT INTO maincoll (doc) values ('{"_id": "3", "name": "Abraham", "last_name": "Vega","amount": 74.56}')
896
897 1 rows affected
898 RUN INSERT INTO maincoll (doc) values ('{"_id": "4", "name": "Jennifer", "last_name": "Leon","amount": 387.14}')
899
900 1 rows affected
901 RUN INSERT INTO maincoll (doc) values ('{"_id": "5", "name": "Jhonny", "last_name": "Test","amount": 125.45}')
902
903 1 rows affected
904 Update a valid collection with string value and > operator for multiple docs
905 send Mysqlx.Crud.Update {
906 collection {
907 name: "maincoll"
908 schema: "mysqlxcoll"
909 }
910 data_model: DOCUMENT
911 criteria {
912 type: OPERATOR
913 operator {
914 name: ">"
915 param {
916 type: IDENT
917 identifier {
918 document_path {
919 type: MEMBER
920 value: "_id"
921 }
922 }
923 }
924 param {
925 type: LITERAL
926 literal {
927 type: V_OCTETS
928 v_octets {
929 value: "3"
930 }
931 }
932 }
933 }
934 }
935 operation {
936 source {
937 document_path {
938 type: MEMBER
939 value: "name"
940 }
941 }
942 operation: ITEM_SET
943 value {
944 type: LITERAL
945 literal {
946 type: V_STRING
947 v_string {
948 value: "\"xtest_>\""
949 }
950 }
951 }
952 }
953 }
954
955
956 2 rows affected
957 Rows matched: 2 Changed: 2 Warnings: 0
958 RUN select doc from maincoll
959 doc
960 {"_id": "1", "name": "Victor", "amount": 4.99, "last_name": "Otero"}
961 {"_id": "2", "name": "Gonzalo", "amount": 120.57, "last_name": "Chrystens"}
962 {"_id": "3", "name": "Abraham", "amount": 74.56, "last_name": "Vega"}
963 {"_id": "4", "name": "\"xtest_>\"", "amount": 387.14, "last_name": "Leon"}
964 {"_id": "5", "name": "\"xtest_>\"", "amount": 125.45, "last_name": "Test"}
965 0 rows affected
966 Update a valid collection with string value and > operator for multiple docs
967 send Mysqlx.Crud.Update {
968 collection {
969 name: "maincoll"
970 schema: "mysqlxcoll"
971 }
972 data_model: DOCUMENT
973 criteria {
974 type: OPERATOR
975 operator {
976 name: "<"
977 param {
978 type: IDENT
979 identifier {
980 document_path {
981 type: MEMBER
982 value: "_id"
983 }
984 }
985 }
986 param {
987 type: LITERAL
988 literal {
989 type: V_OCTETS
990 v_octets {
991 value: "3"
992 }
993 }
994 }
995 }
996 }
997 operation {
998 source {
999 document_path {
1000 type: MEMBER
1001 value: "name"
1002 }
1003 }
1004 operation: ITEM_SET
1005 value {
1006 type: LITERAL
1007 literal {
1008 type: V_STRING
1009 v_string {
1010 value: "\"xtest_<\""
1011 }
1012 }
1013 }
1014 }
1015 }
1016
1017
1018 2 rows affected
1019 Rows matched: 2 Changed: 2 Warnings: 0
1020 RUN select doc from maincoll
1021 doc
1022 {"_id": "1", "name": "\"xtest_<\"", "amount": 4.99, "last_name": "Otero"}
1023 {"_id": "2", "name": "\"xtest_<\"", "amount": 120.57, "last_name": "Chrystens"}
1024 {"_id": "3", "name": "Abraham", "amount": 74.56, "last_name": "Vega"}
1025 {"_id": "4", "name": "\"xtest_>\"", "amount": 387.14, "last_name": "Leon"}
1026 {"_id": "5", "name": "\"xtest_>\"", "amount": 125.45, "last_name": "Test"}
1027 0 rows affected
1028 Update a valid collection with string value and <= operator for multiple docs
1029 send Mysqlx.Crud.Update {
1030 collection {
1031 name: "maincoll"
1032 schema: "mysqlxcoll"
1033 }
1034 data_model: DOCUMENT
1035 criteria {
1036 type: OPERATOR
1037 operator {
1038 name: "<="
1039 param {
1040 type: IDENT
1041 identifier {
1042 document_path {
1043 type: MEMBER
1044 value: "_id"
1045 }
1046 }
1047 }
1048 param {
1049 type: LITERAL
1050 literal {
1051 type: V_OCTETS
1052 v_octets {
1053 value: "3"
1054 }
1055 }
1056 }
1057 }
1058 }
1059 operation {
1060 source {
1061 document_path {
1062 type: MEMBER
1063 value: "name"
1064 }
1065 }
1066 operation: ITEM_SET
1067 value {
1068 type: LITERAL
1069 literal {
1070 type: V_STRING
1071 v_string {
1072 value: "\"xtest_<=\""
1073 }
1074 }
1075 }
1076 }
1077 }
1078
1079
1080 3 rows affected
1081 Rows matched: 3 Changed: 3 Warnings: 0
1082 RUN select doc from maincoll
1083 doc
1084 {"_id": "1", "name": "\"xtest_<=\"", "amount": 4.99, "last_name": "Otero"}
1085 {"_id": "2", "name": "\"xtest_<=\"", "amount": 120.57, "last_name": "Chrystens"}
1086 {"_id": "3", "name": "\"xtest_<=\"", "amount": 74.56, "last_name": "Vega"}
1087 {"_id": "4", "name": "\"xtest_>\"", "amount": 387.14, "last_name": "Leon"}
1088 {"_id": "5", "name": "\"xtest_>\"", "amount": 125.45, "last_name": "Test"}
1089 0 rows affected
1090 Update a valid collection with string value and >= operator for multiple docs
1091 send Mysqlx.Crud.Update {
1092 collection {
1093 name: "maincoll"
1094 schema: "mysqlxcoll"
1095 }
1096 data_model: DOCUMENT
1097 criteria {
1098 type: OPERATOR
1099 operator {
1100 name: ">="
1101 param {
1102 type: IDENT
1103 identifier {
1104 document_path {
1105 type: MEMBER
1106 value: "_id"
1107 }
1108 }
1109 }
1110 param {
1111 type: LITERAL
1112 literal {
1113 type: V_OCTETS
1114 v_octets {
1115 value: "4"
1116 }
1117 }
1118 }
1119 }
1120 }
1121 operation {
1122 source {
1123 document_path {
1124 type: MEMBER
1125 value: "name"
1126 }
1127 }
1128 operation: ITEM_SET
1129 value {
1130 type: LITERAL
1131 literal {
1132 type: V_STRING
1133 v_string {
1134 value: "\"xtest_>=\""
1135 }
1136 }
1137 }
1138 }
1139 }
1140
1141
1142 2 rows affected
1143 Rows matched: 2 Changed: 2 Warnings: 0
1144 RUN select doc from maincoll
1145 doc
1146 {"_id": "1", "name": "\"xtest_<=\"", "amount": 4.99, "last_name": "Otero"}
1147 {"_id": "2", "name": "\"xtest_<=\"", "amount": 120.57, "last_name": "Chrystens"}
1148 {"_id": "3", "name": "\"xtest_<=\"", "amount": 74.56, "last_name": "Vega"}
1149 {"_id": "4", "name": "\"xtest_>=\"", "amount": 387.14, "last_name": "Leon"}
1150 {"_id": "5", "name": "\"xtest_>=\"", "amount": 125.45, "last_name": "Test"}
1151 0 rows affected
1152 Update a valid collection with string value and != operator for multiple docs
1153 send Mysqlx.Crud.Update {
1154 collection {
1155 name: "maincoll"
1156 schema: "mysqlxcoll"
1157 }
1158 data_model: DOCUMENT
1159 criteria {
1160 type: OPERATOR
1161 operator {
1162 name: "!="
1163 param {
1164 type: IDENT
1165 identifier {
1166 document_path {
1167 type: MEMBER
1168 value: "_id"
1169 }
1170 }
1171 }
1172 param {
1173 type: LITERAL
1174 literal {
1175 type: V_OCTETS
1176 v_octets {
1177 value: "2"
1178 }
1179 }
1180 }
1181 }
1182 }
1183 operation {
1184 source {
1185 document_path {
1186 type: MEMBER
1187 value: "last_name"
1188 }
1189 }
1190 operation: ITEM_SET
1191 value {
1192 type: LITERAL
1193 literal {
1194 type: V_STRING
1195 v_string {
1196 value: "\"xtest_!=\""
1197 }
1198 }
1199 }
1200 }
1201 }
1202
1203
1204 4 rows affected
1205 Rows matched: 4 Changed: 4 Warnings: 0
1206 RUN select doc from maincoll
1207 doc
1208 {"_id": "1", "name": "\"xtest_<=\"", "amount": 4.99, "last_name": "\"xtest_!=\""}
1209 {"_id": "2", "name": "\"xtest_<=\"", "amount": 120.57, "last_name": "Chrystens"}
1210 {"_id": "3", "name": "\"xtest_<=\"", "amount": 74.56, "last_name": "\"xtest_!=\""}
1211 {"_id": "4", "name": "\"xtest_>=\"", "amount": 387.14, "last_name": "\"xtest_!=\""}
1212 {"_id": "5", "name": "\"xtest_>=\"", "amount": 125.45, "last_name": "\"xtest_!=\""}
1213 0 rows affected
1214 Update order clause update last _id in desc order (_id = 5)
1215 send Mysqlx.Crud.Update {
1216 collection {
1217 name: "maincoll"
1218 schema: "mysqlxcoll"
1219 }
1220 data_model: DOCUMENT
1221 criteria {
1222 type: OPERATOR
1223 operator {
1224 name: "!="
1225 param {
1226 type: IDENT
1227 identifier {
1228 document_path {
1229 type: MEMBER
1230 value: "_id"
1231 }
1232 }
1233 }
1234 param {
1235 type: LITERAL
1236 literal {
1237 type: V_OCTETS
1238 v_octets {
1239 value: "0"
1240 }
1241 }
1242 }
1243 }
1244 }
1245 limit {
1246 row_count: 1
1247 }
1248 order {
1249 expr {
1250 type: IDENT
1251 identifier {
1252 name: "_id"
1253 }
1254 }
1255 direction: DESC
1256 }
1257 operation {
1258 source {
1259 document_path {
1260 type: MEMBER
1261 value: "last_name"
1262 }
1263 }
1264 operation: ITEM_SET
1265 value {
1266 type: LITERAL
1267 literal {
1268 type: V_STRING
1269 v_string {
1270 value: "\"Limit1OrderDesc\""
1271 }
1272 }
1273 }
1274 }
1275 }
1276
1277
1278 1 rows affected
1279 Rows matched: 1 Changed: 1 Warnings: 0
1280 RUN select doc from maincoll
1281 doc
1282 {"_id": "1", "name": "\"xtest_<=\"", "amount": 4.99, "last_name": "\"xtest_!=\""}
1283 {"_id": "2", "name": "\"xtest_<=\"", "amount": 120.57, "last_name": "Chrystens"}
1284 {"_id": "3", "name": "\"xtest_<=\"", "amount": 74.56, "last_name": "\"xtest_!=\""}
1285 {"_id": "4", "name": "\"xtest_>=\"", "amount": 387.14, "last_name": "\"xtest_!=\""}
1286 {"_id": "5", "name": "\"xtest_>=\"", "amount": 125.45, "last_name": "\"Limit1OrderDesc\""}
1287 0 rows affected
1288 Update with order clause update first two _id in asc order (_id = 1,2)
1289 send Mysqlx.Crud.Update {
1290 collection {
1291 name: "maincoll"
1292 schema: "mysqlxcoll"
1293 }
1294 data_model: DOCUMENT
1295 criteria {
1296 type: OPERATOR
1297 operator {
1298 name: "!="
1299 param {
1300 type: IDENT
1301 identifier {
1302 document_path {
1303 type: MEMBER
1304 value: "_id"
1305 }
1306 }
1307 }
1308 param {
1309 type: LITERAL
1310 literal {
1311 type: V_OCTETS
1312 v_octets {
1313 value: "0"
1314 }
1315 }
1316 }
1317 }
1318 }
1319 limit {
1320 row_count: 2
1321 }
1322 order {
1323 expr {
1324 type: IDENT
1325 identifier {
1326 name: "_id"
1327 }
1328 }
1329 direction: ASC
1330 }
1331 operation {
1332 source {
1333 document_path {
1334 type: MEMBER
1335 value: "last_name"
1336 }
1337 }
1338 operation: ITEM_SET
1339 value {
1340 type: LITERAL
1341 literal {
1342 type: V_STRING
1343 v_string {
1344 value: "\"Limit2OrderAsc\""
1345 }
1346 }
1347 }
1348 }
1349 }
1350
1351
1352 2 rows affected
1353 Rows matched: 2 Changed: 2 Warnings: 0
1354 RUN select doc from maincoll
1355 doc
1356 {"_id": "1", "name": "\"xtest_<=\"", "amount": 4.99, "last_name": "\"Limit2OrderAsc\""}
1357 {"_id": "2", "name": "\"xtest_<=\"", "amount": 120.57, "last_name": "\"Limit2OrderAsc\""}
1358 {"_id": "3", "name": "\"xtest_<=\"", "amount": 74.56, "last_name": "\"xtest_!=\""}
1359 {"_id": "4", "name": "\"xtest_>=\"", "amount": 387.14, "last_name": "\"xtest_!=\""}
1360 {"_id": "5", "name": "\"xtest_>=\"", "amount": 125.45, "last_name": "\"Limit1OrderDesc\""}
1361 0 rows affected
1362 Invalid type of update for Document
1363 send Mysqlx.Crud.Update {
1364 collection {
1365 name: "maincoll"
1366 schema: "mysqlxcoll"
1367 }
1368 data_model: DOCUMENT
1369 criteria {
1370 type: OPERATOR
1371 operator {
1372 name: ">"
1373 param {
1374 type: IDENT
1375 identifier {
1376 document_path {
1377 type: MEMBER
1378 value: "_id"
1379 }
1380 }
1381 }
1382 param {
1383 type: LITERAL
1384 literal {
1385 type: V_OCTETS
1386 v_octets {
1387 value: "3"
1388 }
1389 }
1390 }
1391 }
1392 }
1393 operation {
1394 source {
1395 document_path {
1396 type: MEMBER
1397 value: "name"
1398 }
1399 }
1400 operation: SET
1401 value {
1402 type: LITERAL
1403 literal {
1404 type: V_STRING
1405 v_string {
1406 value: "xtest"
1407 }
1408 }
1409 }
1410 }
1411 }
1412
1413 Got expected error: Invalid type of update operation for document (code 5051)
1414 Update multiple column + transaction
1415 RUN START TRANSACTION
1416
1417 0 rows affected
1418 send Mysqlx.Crud.Update {
1419 collection {
1420 name: "maincoll"
1421 schema: "mysqlxcoll"
1422 }
1423 data_model: DOCUMENT
1424 criteria {
1425 type: OPERATOR
1426 operator {
1427 name: ">"
1428 param {
1429 type: IDENT
1430 identifier {
1431 document_path {
1432 type: MEMBER
1433 value: "_id"
1434 }
1435 }
1436 }
1437 param {
1438 type: LITERAL
1439 literal {
1440 type: V_OCTETS
1441 v_octets {
1442 value: "3"
1443 }
1444 }
1445 }
1446 }
1447 }
1448 operation {
1449 source {
1450 document_path {
1451 type: MEMBER
1452 value: "name"
1453 }
1454 }
1455 operation: ITEM_SET
1456 value {
1457 type: LITERAL
1458 literal {
1459 type: V_STRING
1460 v_string {
1461 value: "name_mul_update_save_a"
1462 }
1463 }
1464 }
1465 }
1466 operation {
1467 source {
1468 document_path {
1469 type: MEMBER
1470 value: "last_name"
1471 }
1472 }
1473 operation: ITEM_SET
1474 value {
1475 type: LITERAL
1476 literal {
1477 type: V_STRING
1478 v_string {
1479 value: "last_name_mul_update_save_a"
1480 }
1481 }
1482 }
1483 }
1484 }
1485
1486
1487 2 rows affected
1488 Rows matched: 2 Changed: 2 Warnings: 0
1489 RUN select doc FROM mysqlxcoll.maincoll where _id > 3
1490 doc
1491 {"_id": "4", "name": "name_mul_update_save_a", "amount": 387.14, "last_name": "last_name_mul_update_save_a"}
1492 {"_id": "5", "name": "name_mul_update_save_a", "amount": 125.45, "last_name": "last_name_mul_update_save_a"}
1493 0 rows affected
1494 RUN SAVEPOINT A
1495
1496 0 rows affected
1497 send Mysqlx.Crud.Update {
1498 collection {
1499 name: "maincoll"
1500 schema: "mysqlxcoll"
1501 }
1502 data_model: DOCUMENT
1503 criteria {
1504 type: OPERATOR
1505 operator {
1506 name: ">"
1507 param {
1508 type: IDENT
1509 identifier {
1510 document_path {
1511 type: MEMBER
1512 value: "_id"
1513 }
1514 }
1515 }
1516 param {
1517 type: LITERAL
1518 literal {
1519 type: V_OCTETS
1520 v_octets {
1521 value: "3"
1522 }
1523 }
1524 }
1525 }
1526 }
1527 operation {
1528 source {
1529 document_path {
1530 type: MEMBER
1531 value: "name"
1532 }
1533 }
1534 operation: ITEM_SET
1535 value {
1536 type: LITERAL
1537 literal {
1538 type: V_STRING
1539 v_string {
1540 value: "name_mul_update_save_b"
1541 }
1542 }
1543 }
1544 }
1545 operation {
1546 source {
1547 document_path {
1548 type: MEMBER
1549 value: "last_name"
1550 }
1551 }
1552 operation: ITEM_SET
1553 value {
1554 type: LITERAL
1555 literal {
1556 type: V_STRING
1557 v_string {
1558 value: "last_name_mul_update_save_b"
1559 }
1560 }
1561 }
1562 }
1563 }
1564
1565
1566 2 rows affected
1567 Rows matched: 2 Changed: 2 Warnings: 0
1568 RUN select doc FROM mysqlxcoll.maincoll where _id > 3
1569 doc
1570 {"_id": "4", "name": "name_mul_update_save_b", "amount": 387.14, "last_name": "last_name_mul_update_save_b"}
1571 {"_id": "5", "name": "name_mul_update_save_b", "amount": 125.45, "last_name": "last_name_mul_update_save_b"}
1572 0 rows affected
1573 RUN ROLLBACK TO SAVEPOINT A
1574
1575 0 rows affected
1576 RUN COMMIT
1577
1578 0 rows affected
1579 RUN select doc FROM mysqlxcoll.maincoll where _id > 3
1580 doc
1581 {"_id": "4", "name": "name_mul_update_save_a", "amount": 387.14, "last_name": "last_name_mul_update_save_a"}
1582 {"_id": "5", "name": "name_mul_update_save_a", "amount": 125.45, "last_name": "last_name_mul_update_save_a"}
1583 0 rows affected
1584 document member _id is immutable
1585 send Mysqlx.Crud.Update {
1586 collection {
1587 name: "maincoll"
1588 schema: "mysqlxcoll"
1589 }
1590 data_model: DOCUMENT
1591 criteria {
1592 type: OPERATOR
1593 operator {
1594 name: "=="
1595 param {
1596 type: IDENT
1597 identifier {
1598 document_path {
1599 type: MEMBER
1600 value: "last_name"
1601 }
1602 }
1603 }
1604 param {
1605 type: LITERAL
1606 literal {
1607 type: V_STRING
1608 v_string {
1609 value: "Chrystens"
1610 }
1611 }
1612 }
1613 }
1614 }
1615 operation {
1616 source {
1617 document_path {
1618 type: MEMBER
1619 value: "_id"
1620 }
1621 }
1622 operation: ITEM_SET
1623 value {
1624 type: LITERAL
1625 literal {
1626 type: V_OCTETS
1627 v_octets {
1628 value: "11"
1629 }
1630 }
1631 }
1632 }
1633 }
1634
1635 Got expected error: Forbidden update operation on '$._id' member (code 5053)
1636 RUN select doc FROM mysqlxcoll.maincoll
1637 doc
1638 {"_id": "1", "name": "\"xtest_<=\"", "amount": 4.99, "last_name": "\"Limit2OrderAsc\""}
1639 {"_id": "2", "name": "\"xtest_<=\"", "amount": 120.57, "last_name": "\"Limit2OrderAsc\""}
1640 {"_id": "3", "name": "\"xtest_<=\"", "amount": 74.56, "last_name": "\"xtest_!=\""}
1641 {"_id": "4", "name": "name_mul_update_save_a", "amount": 387.14, "last_name": "last_name_mul_update_save_a"}
1642 {"_id": "5", "name": "name_mul_update_save_a", "amount": 125.45, "last_name": "last_name_mul_update_save_a"}
1643 0 rows affected
1644 document member _id is immutable but _id column can't be updated also
1645 send Mysqlx.Crud.Update {
1646 collection {
1647 name: "maincoll"
1648 schema: "mysqlxcoll"
1649 }
1650 data_model: TABLE
1651 criteria {
1652 type: OPERATOR
1653 operator {
1654 name: "=="
1655 param {
1656 type: IDENT
1657 identifier {
1658 document_path {
1659 type: MEMBER
1660 value: "last_name"
1661 }
1662 }
1663 }
1664 param {
1665 type: LITERAL
1666 literal {
1667 type: V_STRING
1668 v_string {
1669 value: "Chrystens"
1670 }
1671 }
1672 }
1673 }
1674 }
1675 operation {
1676 source {
1677 name: "_id"
1678 }
1679 operation: SET
1680 value {
1681 type: LITERAL
1682 literal {
1683 type: V_OCTETS
1684 v_octets {
1685 value: "11"
1686 }
1687 }
1688 }
1689 }
1690 }
1691
1692 Got expected error: The value specified for generated column '_id' in table 'maincoll' is not allowed. (code 3105)
1693 Update using multiple condition
1694 send Mysqlx.Crud.Update {
1695 collection {
1696 name: "maincoll"
1697 schema: "mysqlxcoll"
1698 }
1699 data_model: DOCUMENT
1700 criteria {
1701 type: OPERATOR
1702 operator {
1703 name: "&&"
1704 param {
1705 type: OPERATOR
1706 operator {
1707 name: ">="
1708 param {
1709 type: IDENT
1710 identifier {
1711 document_path {
1712 type: MEMBER
1713 value: "_id"
1714 }
1715 }
1716 }
1717 param {
1718 type: LITERAL
1719 literal {
1720 type: V_SINT
1721 v_signed_int: 4
1722 }
1723 }
1724 }
1725 }
1726 param {
1727 type: OPERATOR
1728 operator {
1729 name: "=="
1730 param {
1731 type: IDENT
1732 identifier {
1733 name: "_id"
1734 }
1735 }
1736 param {
1737 type: LITERAL
1738 literal {
1739 type: V_SINT
1740 v_signed_int: 4
1741 }
1742 }
1743 }
1744 }
1745 }
1746 }
1747 operation {
1748 source {
1749 document_path {
1750 type: MEMBER
1751 value: "amount"
1752 }
1753 }
1754 operation: ITEM_SET
1755 value {
1756 type: LITERAL
1757 literal {
1758 type: V_OCTETS
1759 v_octets {
1760 value: "444"
1761 }
1762 }
1763 }
1764 }
1765 }
1766
1767
1768 1 rows affected
1769 Rows matched: 1 Changed: 1 Warnings: 0
1770 RUN SELECT doc FROM mysqlxcoll.maincoll
1771 doc
1772 {"_id": "1", "name": "\"xtest_<=\"", "amount": 4.99, "last_name": "\"Limit2OrderAsc\""}
1773 {"_id": "2", "name": "\"xtest_<=\"", "amount": 120.57, "last_name": "\"Limit2OrderAsc\""}
1774 {"_id": "3", "name": "\"xtest_<=\"", "amount": 74.56, "last_name": "\"xtest_!=\""}
1775 {"_id": "4", "name": "name_mul_update_save_a", "amount": "444", "last_name": "last_name_mul_update_save_a"}
1776 {"_id": "5", "name": "name_mul_update_save_a", "amount": 125.45, "last_name": "last_name_mul_update_save_a"}
1777 0 rows affected
1778 ================================================================================
1779 CLEAN UP
1780 ================================================================================
1781 RUN DROP USER updatecruduser@localhost
1782
1783 0 rows affected
1784 RUN SET SQL_SAFE_UPDATES = 1
1785
1786 0 rows affected
1787 Mysqlx.Ok {
1788 msg: "bye!"
1789 }
1790 ok
1791 DROP SCHEMA if EXISTS mysqlxplugin;
1792 DROP SCHEMA if EXISTS mysqlxcoll;