Debian Codegraph @

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

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