Debian Codegraph @

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

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