Debian Codegraph @

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

raw
1 ########### ../t/update_crud_replace_o.test ############
2 ### #
3 ### This test runs aims to run UPDATE REPLACE operation statement #
4 ### variant with mysqlxtest client. #
5 ### Test covers #
6 ### - update with different operators #
7 ### - update on multiple rows #
8 ### - update (ITEM_REPLACE) with data model as document #
9 ### - update with transaction (rollback/commit) #
10 ### #
11 ########################################################################
12 #
13
14 --echo =============================================
15 --echo CRUD UPDATE ITEM_REPLACE SCENARIOS
16 --echo =============================================
17 --echo
18
19 --echo ================================================================================
20 --echo PREAMBLE
21 --echo ================================================================================
22 ## Preamble
23 --source include/xplugin_preamble.inc
24 create user upditemreplace@localhost identified by 'upditemreplace';
25 grant all on *.* to upditemreplace@localhost;
26 CREATE SCHEMA mysqlxcoll;
27
28 ## TEST STARTS HERE
29 --echo ================================================================================
30 --echo TEST START
31 --echo ================================================================================
32 --write_file $MYSQL_TMP_DIR/mysqlx-update_itemreplace.xpl
33
34 -->stmtadmin create_collection {"schema":"mysqlxcoll","name":"maincoll"}
35 -->recvresult
36
37 -->sql
38 SET SQL_SAFE_UPDATES = 0;
39 USE mysqlxcoll;
40 INSERT INTO maincoll (doc) values ('{"_id": "1", "name": "Victor", "last_name": "Otero","amount": 4.99}');
41 INSERT INTO maincoll (doc) values ('{"_id": "2", "name": "Gonzalo", "last_name": "Chrystens","amount": 120.57}');
42 INSERT INTO maincoll (doc) values ('{"_id": "3", "name": "Abraham", "last_name": "Vega","amount": 74.56}');
43 INSERT INTO maincoll (doc) values ('{"_id": "4", "name": "Jennifer", "last_name": "Leon","amount": 387.14}');
44 INSERT INTO maincoll (doc) values ('{"_id": "5", "name": "Jhonny", "last_name": "Test","amount": 125.45}');
45 INSERT INTO maincoll (doc) values ('{"_id": "0"}');
46 -->endsql
47
48 -->echo Update a valid collection with == operator
49 Mysqlx.Crud.Update {
50 collection {
51 name: "maincoll"
52 schema: "mysqlxcoll"
53 }
54 data_model: DOCUMENT
55 criteria {
56 type: OPERATOR
57 operator {
58 name: "=="
59 param {
60 type: IDENT
61 identifier {
62 name: "_id"
63 }
64 }
65 param {
66 type: LITERAL
67 literal {
68 type: V_OCTETS
69 v_octets {value:"1"}
70 }
71 }
72 }
73 }
74 operation {
75 source {
76 document_path {
77 type: MEMBER
78 value: "name"
79 }
80 }
81 operation: ITEM_REPLACE
82 value {
83 type: LITERAL
84 literal {
85 type: V_STRING
86 v_string {
87 value: "\"VictorReplaced\""
88 }
89 }
90 }
91 }
92 }
93
94 #-- Mysqlx.Sql.StmtExecuteOk
95 -->recvresult
96
97 -->sql
98 select doc from maincoll;
99 -->endsql
100
101
102 -->echo Update a valid collection with string value and > operator for multiple docs
103 Mysqlx.Crud.Update {
104 collection {
105 name: "maincoll"
106 schema: "mysqlxcoll"
107 }
108 data_model: DOCUMENT
109 criteria {
110 type: OPERATOR
111 operator {
112 name: ">"
113 param {
114 type: IDENT
115 identifier {
116 document_path {
117 type: MEMBER
118 value: "_id"
119 }
120 }
121 }
122 param {
123 type: LITERAL
124 literal {
125 type: V_OCTETS
126 v_octets {value:"3"}
127 }
128 }
129 }
130 }
131 operation {
132 source {
133 document_path {
134 type: MEMBER
135 value: "name"
136 }
137 }
138 operation: ITEM_REPLACE
139 value {
140 type: LITERAL
141 literal {
142 type: V_STRING
143 v_string {
144 value: "\"xtest_>\""
145 }
146 }
147 }
148 }
149 }
150 #-- Mysqlx.Sql.StmtExecuteOk
151 -->recvresult
152
153 -->sql
154 select doc from maincoll;
155 -->endsql
156
157 -->echo Update a valid collection with string value and < operator for multiple docs
158 Mysqlx.Crud.Update {
159 collection {
160 name: "maincoll"
161 schema: "mysqlxcoll"
162 }
163 data_model: DOCUMENT
164 criteria {
165 type: OPERATOR
166 operator {
167 name: "<"
168 param {
169 type: IDENT
170 identifier {
171 document_path {
172 type: MEMBER
173 value: "_id"
174 }
175 }
176 }
177 param {
178 type: LITERAL
179 literal {
180 type: V_OCTETS
181 v_octets {value:"3"}
182 }
183 }
184 }
185 }
186 operation {
187 source {
188 document_path {
189 type: MEMBER
190 value: "name"
191 }
192 }
193 operation: ITEM_REPLACE
194 value {
195 type: LITERAL
196 literal {
197 type: V_STRING
198 v_string {
199 value: "\"xtest_<\""
200 }
201 }
202 }
203 }
204 }
205 #-- Mysqlx.Sql.StmtExecuteOk
206 -->recvresult
207
208 -->sql
209 select doc from maincoll;
210 -->endsql
211
212 -->echo Update a valid collection with string value and <= operator for multiple docs
213 Mysqlx.Crud.Update {
214 collection {
215 name: "maincoll"
216 schema: "mysqlxcoll"
217 }
218 data_model: DOCUMENT
219 criteria {
220 type: OPERATOR
221 operator {
222 name: "<="
223 param {
224 type: IDENT
225 identifier {
226 document_path {
227 type: MEMBER
228 value: "_id"
229 }
230 }
231 }
232 param {
233 type: LITERAL
234 literal {
235 type: V_OCTETS
236 v_octets {value:"3"}
237 }
238 }
239 }
240 }
241 operation {
242 source {
243 document_path {
244 type: MEMBER
245 value: "last_name"
246 }
247 }
248 operation: ITEM_REPLACE
249 value {
250 type: LITERAL
251 literal {
252 type: V_STRING
253 v_string {
254 value: "\"xtest_<=\""
255 }
256 }
257 }
258 }
259 }
260 #-- Mysqlx.Sql.StmtExecuteOk
261 -->recvresult
262
263 -->sql
264 select doc from maincoll;
265 -->endsql
266
267 -->echo Update a valid collection with string value and >= operator for multiple docs
268 Mysqlx.Crud.Update {
269 collection {
270 name: "maincoll"
271 schema: "mysqlxcoll"
272 }
273 data_model: DOCUMENT
274 criteria {
275 type: OPERATOR
276 operator {
277 name: ">="
278 param {
279 type: IDENT
280 identifier {
281 document_path {
282 type: MEMBER
283 value: "_id"
284 }
285 }
286 }
287 param {
288 type: LITERAL
289 literal {
290 type: V_OCTETS
291 v_octets {value:"4"}
292 }
293 }
294 }
295 }
296 operation {
297 source {
298 document_path {
299 type: MEMBER
300 value: "last_name"
301 }
302 }
303 operation: ITEM_REPLACE
304 value {
305 type: LITERAL
306 literal {
307 type: V_STRING
308 v_string {
309 value: "\"xtest_>=\""
310 }
311 }
312 }
313 }
314 }
315 #-- Mysqlx.Sql.StmtExecuteOk
316 -->recvresult
317
318 -->sql
319 select doc from maincoll;
320 -->endsql
321
322 -->echo Update a valid collection with string value and != operator for multiple docs
323 Mysqlx.Crud.Update {
324 collection {
325 name: "maincoll"
326 schema: "mysqlxcoll"
327 }
328 data_model: DOCUMENT
329 criteria {
330 type: OPERATOR
331 operator {
332 name: "!="
333 param {
334 type: IDENT
335 identifier {
336 document_path {
337 type: MEMBER
338 value: "_id"
339 }
340 }
341 }
342 param {
343 type: LITERAL
344 literal {
345 type: V_OCTETS
346 v_octets {value:"2"}
347 }
348 }
349 }
350 }
351 operation {
352 source {
353 document_path {
354 type: MEMBER
355 value: "amount"
356 }
357 }
358 operation: ITEM_REPLACE
359 value {
360 type: LITERAL
361 literal {
362 type: V_STRING
363 v_string {
364 value: "\"xtest_!=\""
365 }
366 }
367 }
368 }
369 }
370 #-- Mysqlx.Sql.StmtExecuteOk
371 -->recvresult
372
373 -->sql
374 select doc from maincoll;
375 -->endsql
376
377 -->echo Update with non existing doc member
378 Mysqlx.Crud.Update {
379 collection {
380 name: "maincoll"
381 schema: "mysqlxcoll"
382 }
383 data_model: DOCUMENT
384 criteria {
385 type: OPERATOR
386 operator {
387 name: "!="
388 param {
389 type: IDENT
390 identifier {
391 document_path {
392 type: MEMBER
393 value: "_id"
394 }
395 }
396 }
397 param {
398 type: LITERAL
399 literal {
400 type: V_OCTETS
401 v_octets {value:"2"}
402 }
403 }
404 }
405 }
406 operation {
407 source {
408 document_path {
409 type: MEMBER
410 value: "notamember"
411 }
412 }
413 operation: ITEM_REPLACE
414 value {
415 type: LITERAL
416 literal {
417 type: V_STRING
418 v_string {
419 value: "\"xtest_notmemberdoc\""
420 }
421 }
422 }
423 }
424 }
425 #-- Mysqlx.Sql.StmtExecuteOk
426 -->recvresult
427
428 -->sql
429 select doc from maincoll;
430 -->endsql
431
432 -->echo unsigned int data type update for Document
433 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 {value:"3"}
457 }
458 }
459 }
460 }
461 operation {
462 source {
463 document_path {
464 type: MEMBER
465 value: "amount"
466 }
467 }
468 operation: ITEM_REPLACE
469 value {
470 type: LITERAL
471 literal {
472 type: V_UINT
473 v_unsigned_int: 23
474 }
475 }
476 }
477 }
478 #-- Mysqlx.Sql.StmtExecuteOk
479 -->recvresult
480
481 -->sql
482 select doc from maincoll;
483 -->endsql
484
485 -->echo signed int data type update for Document
486 Mysqlx.Crud.Update {
487 collection {
488 name: "maincoll"
489 schema: "mysqlxcoll"
490 }
491 data_model: DOCUMENT
492 criteria {
493 type: OPERATOR
494 operator {
495 name: ">"
496 param {
497 type: IDENT
498 identifier {
499 document_path {
500 type: MEMBER
501 value: "_id"
502 }
503 }
504 }
505 param {
506 type: LITERAL
507 literal {
508 type: V_OCTETS
509 v_octets {value:"3"}
510 }
511 }
512 }
513 }
514 operation {
515 source {
516 document_path {
517 type: MEMBER
518 value: "amount"
519 }
520 }
521 operation: ITEM_REPLACE
522 value {
523 type: LITERAL
524 literal {
525 type: V_SINT
526 v_signed_int: 25
527 }
528 }
529 }
530 }
531 #-- Mysqlx.Sql.StmtExecuteOk
532 -->recvresult
533
534 -->sql
535 select doc from maincoll;
536 -->endsql
537
538 -->echo double data type update for Document
539 Mysqlx.Crud.Update {
540 collection {
541 name: "maincoll"
542 schema: "mysqlxcoll"
543 }
544 data_model: DOCUMENT
545 criteria {
546 type: OPERATOR
547 operator {
548 name: ">"
549 param {
550 type: IDENT
551 identifier {
552 document_path {
553 type: MEMBER
554 value: "_id"
555 }
556 }
557 }
558 param {
559 type: LITERAL
560 literal {
561 type: V_OCTETS
562 v_octets {value:"3"}
563 }
564 }
565 }
566 }
567 operation {
568 source {
569 document_path {
570 type: MEMBER
571 value: "amount"
572 }
573 }
574 operation: ITEM_REPLACE
575 value {
576 type: LITERAL
577 literal {
578 type: V_DOUBLE
579 v_double: 26.53
580 }
581 }
582 }
583 }
584 #-- Mysqlx.Sql.StmtExecuteOk
585 -->recvresult
586
587 -->sql
588 select doc from maincoll;
589 -->endsql
590
591 -->echo float data type update for Document
592 Mysqlx.Crud.Update {
593 collection {
594 name: "maincoll"
595 schema: "mysqlxcoll"
596 }
597 data_model: DOCUMENT
598 criteria {
599 type: OPERATOR
600 operator {
601 name: ">"
602 param {
603 type: IDENT
604 identifier {
605 document_path {
606 type: MEMBER
607 value: "_id"
608 }
609 }
610 }
611 param {
612 type: LITERAL
613 literal {
614 type: V_OCTETS
615 v_octets {value:"3"}
616 }
617 }
618 }
619 }
620 operation {
621 source {
622 document_path {
623 type: MEMBER
624 value: "amount"
625 }
626 }
627 operation: ITEM_REPLACE
628 value {
629 type: LITERAL
630 literal {
631 type: V_FLOAT
632 v_float: 27.75
633 }
634 }
635 }
636 }
637 #-- Mysqlx.Sql.StmtExecuteOk
638 -->recvresult
639
640 -->sql
641 select doc from maincoll;
642 -->endsql
643
644 -->echo Update with order clause update last _id in desc order (_id = 5)
645 Mysqlx.Crud.Update {
646 collection {
647 name: "maincoll"
648 schema: "mysqlxcoll"
649 }
650 data_model: DOCUMENT
651 criteria {
652 type: OPERATOR
653 operator {
654 name: "!="
655 param {
656 type: IDENT
657 identifier {
658 document_path {
659 type: MEMBER
660 value: "_id"
661 }
662 }
663 }
664 param {
665 type: LITERAL
666 literal {
667 type: V_OCTETS
668 v_octets {value:"0"}
669 }
670 }
671 }
672 }
673 limit {
674 row_count: 1
675 }
676 order {
677 expr {
678 type: IDENT
679 identifier {
680 name: "_id"
681 }
682 }
683 direction: DESC
684 }
685 operation {
686 source {
687 document_path {
688 type: MEMBER
689 value: "amount"
690 }
691 }
692 operation: ITEM_REPLACE
693 value {
694 type: LITERAL
695 literal {
696 type: V_DOUBLE
697 v_double: 1234.567
698 }
699 }
700 }
701 }
702 #-- Mysqlx.Sql.StmtExecuteOk
703 -->recvresult
704
705 -->sql
706 select doc from maincoll;
707 -->endsql
708
709 -->echo Update with order clause update first two _id in asc order (_id = 1,2)
710 Mysqlx.Crud.Update {
711 collection {
712 name: "maincoll"
713 schema: "mysqlxcoll"
714 }
715 data_model: DOCUMENT
716 criteria {
717 type: OPERATOR
718 operator {
719 name: "!="
720 param {
721 type: IDENT
722 identifier {
723 document_path {
724 type: MEMBER
725 value: "_id"
726 }
727 }
728 }
729 param {
730 type: LITERAL
731 literal {
732 type: V_OCTETS
733 v_octets {value:"0"}
734 }
735 }
736 }
737 }
738 limit {
739 row_count: 2
740 }
741 order {
742 expr {
743 type: IDENT
744 identifier {
745 name: "_id"
746 }
747 }
748 direction: ASC
749 }
750 operation {
751 source {
752 document_path {
753 type: MEMBER
754 value: "amount"
755 }
756 }
757 operation: ITEM_REPLACE
758 value {
759 type: LITERAL
760 literal {
761 type: V_DOUBLE
762 v_double: 9876.543
763 }
764 }
765 }
766 }
767 #-- Mysqlx.Sql.StmtExecuteOk
768 -->recvresult
769
770 -->sql
771 select doc from maincoll;
772 -->endsql
773
774 -->echo Null value for Document
775 Mysqlx.Crud.Update {
776 collection {
777 name: "maincoll"
778 schema: "mysqlxcoll"
779 }
780 data_model: DOCUMENT
781 criteria {
782 type: OPERATOR
783 operator {
784 name: ">"
785 param {
786 type: IDENT
787 identifier {
788 document_path {
789 type: MEMBER
790 value: "_id"
791 }
792 }
793 }
794 param {
795 type: LITERAL
796 literal {
797 type: V_OCTETS
798 v_octets {value:"3"}
799 }
800 }
801 }
802 }
803 operation {
804 source {
805 document_path {
806 type: MEMBER
807 value: "amount"
808 }
809 }
810 operation: ITEM_REPLACE
811 value {
812 type: LITERAL
813 literal {
814 type: V_NULL
815 }
816 }
817 }
818 }
819 #-- Mysqlx.Sql.StmtExecuteOk
820 -->recvresult
821
822 -->sql
823 select doc from maincoll;
824 START TRANSACTION;
825 -->endsql
826
827 -->echo Update Transaction Rollback
828 Mysqlx.Crud.Update {
829 collection {
830 name: "maincoll"
831 schema: "mysqlxcoll"
832 }
833 data_model: DOCUMENT
834 criteria {
835 type: OPERATOR
836 operator {
837 name: "=="
838 param {
839 type: IDENT
840 identifier {
841 document_path {
842 type: MEMBER
843 value: "_id"
844 }
845 }
846 }
847 param {
848 type: LITERAL
849 literal {
850 type: V_OCTETS
851 v_octets {value:"5"}
852 }
853 }
854 }
855 }
856 operation {
857 source {
858 document_path {
859 type: MEMBER
860 value: "last_name"
861 }
862 }
863 operation: ITEM_REPLACE
864 value {
865 type: LITERAL
866 literal {
867 type: V_STRING
868 v_string
869 {
870 value: "\"Iam_rollback\""
871 }
872 }
873 }
874 }
875 }
876 #-- Mysqlx.Sql.StmtExecuteOk
877 -->recvresult
878
879 -->sql
880 select doc from maincoll;
881 ROLLBACK;
882 select doc from maincoll;
883 -->endsql
884
885 -->sql
886 START TRANSACTION;
887 -->endsql
888
889 -->echo Update Transaction and Commit
890 Mysqlx.Crud.Update {
891 collection {
892 name: "maincoll"
893 schema: "mysqlxcoll"
894 }
895 data_model: DOCUMENT
896 criteria {
897 type: OPERATOR
898 operator {
899 name: "=="
900 param {
901 type: IDENT
902 identifier {
903 document_path {
904 type: MEMBER
905 value: "_id"
906 }
907 }
908 }
909 param {
910 type: LITERAL
911 literal {
912 type: V_OCTETS
913 v_octets {value:"5"}
914 }
915 }
916 }
917 }
918 operation {
919 source {
920 document_path {
921 type: MEMBER
922 value: "last_name"
923 }
924 }
925 operation: ITEM_REPLACE
926 value {
927 type: LITERAL
928 literal {
929 type: V_STRING
930 v_string
931 {
932 value: "\"Iam_commit\""
933 }
934 }
935 }
936 }
937 }
938 #-- Mysqlx.Sql.StmtExecuteOk
939 -->recvresult
940
941 -->sql
942 COMMIT;
943 select doc from maincoll;
944 -->endsql
945
946 -->echo Invalid type of update for Document
947 Mysqlx.Crud.Update {
948 collection {
949 name: "maincoll"
950 schema: "mysqlxcoll"
951 }
952 data_model: DOCUMENT
953 criteria {
954 type: OPERATOR
955 operator {
956 name: ">"
957 param {
958 type: IDENT
959 identifier {
960 document_path {
961 type: MEMBER
962 value: "_id"
963 }
964 }
965 }
966 param {
967 type: LITERAL
968 literal {
969 type: V_OCTETS
970 v_octets {value:"3"}
971 }
972 }
973 }
974 }
975 operation {
976 source {
977 document_path {
978 type: MEMBER
979 value: "name"
980 }
981 }
982 operation: SET
983 value {
984 type: LITERAL
985 literal {
986 type: V_STRING
987 v_string {
988 value: "xtest"
989 }
990 }
991 }
992 }
993 }
994 #-- Mysqlx.Error
995 -->recverror 5051
996
997 ## Cleanup
998 -->echo ================================================================================
999 -->echo CLEAN UP
1000 -->echo ================================================================================
1001 -->sql
1002 DROP SCHEMA if EXISTS mysqlxcoll;
1003 DROP USER upditemreplace@localhost;
1004 SET SQL_SAFE_UPDATES = 1;
1005 -->endsql
1006 EOF
1007
1008 --exec $MYSQLXTEST -u upditemreplace --password='upditemreplace' --file=$MYSQL_TMP_DIR/mysqlx-update_itemreplace.xpl 2>&1
1009
1010 ## Cleanup
1011 --source ../include/xplugin_cleanup.inc
1012