Debian Codegraph @

mysql-test/suite/x/t/update_crud_o.test

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