Debian Codegraph @

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

raw
1 ########### ../t/update_crud_remove_o.test #############
2 ### #
3 ### This test runs aims to run UPDATE REMOVE operation statement #
4 ### variant with mysqlxtest client. #
5 ### Test covers #
6 ### - update with different operators #
7 ### - update on multiple rows #
8 ### - update (ITEM_REMOVE) with data model as document #
9 ### - update with transaction (rollback/commit) #
10 ### #
11 ########################################################################
12 #
13
14 --echo =============================================
15 --echo CRUD UPDATE ITEM_REMOVE SCENARIOS
16 --echo =============================================
17 --echo
18
19 ## Preamble
20 --echo ================================================================================
21 --echo PREAMBLE
22 --echo ================================================================================
23 --source include/xplugin_preamble.inc
24 create user updateitemremove@localhost identified by 'updateitemremove';
25 grant all on *.* to updateitemremove@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_itemremove.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 -->endsql
46
47 -->echo Update a valid collection with == operator
48 Mysqlx.Crud.Update {
49 collection {
50 name: "maincoll"
51 schema: "mysqlxcoll"
52 }
53 data_model: DOCUMENT
54 criteria {
55 type: OPERATOR
56 operator {
57 name: "=="
58 param {
59 type: IDENT
60 identifier {
61 document_path {
62 type: MEMBER
63 value: "_id"
64 }
65 }
66 }
67 param {
68 type: LITERAL
69 literal {
70 type: V_OCTETS
71 v_octets {value:"5"}
72 }
73 }
74 }
75 }
76 operation {
77 source {
78 document_path {
79 type: MEMBER
80 value: "name"
81 }
82 }
83 operation: ITEM_REMOVE
84 }
85 }
86 #-- Mysqlx.Sql.StmtExecuteOk
87 -->recvresult
88
89 -->sql
90 select doc from maincoll;
91 -->endsql
92
93 -->echo Update a valid collection with string value and > operator for multiple docs
94 Mysqlx.Crud.Update {
95 collection {
96 name: "maincoll"
97 schema: "mysqlxcoll"
98 }
99 data_model: DOCUMENT
100 criteria {
101 type: OPERATOR
102 operator {
103 name: ">"
104 param {
105 type: IDENT
106 identifier {
107 document_path {
108 type: MEMBER
109 value: "_id"
110 }
111 }
112 }
113 param {
114 type: LITERAL
115 literal {
116 type: V_OCTETS
117 v_octets {value:"3"}
118 }
119 }
120 }
121 }
122 operation {
123 source {
124 document_path {
125 type: MEMBER
126 value: "name"
127 }
128 }
129 operation: ITEM_REMOVE
130 }
131 }
132 #-- Mysqlx.Sql.StmtExecuteOk
133 -->recvresult
134
135 -->sql
136 select doc from maincoll;
137 -->endsql
138
139 -->echo Update a valid collection with string value and < operator for multiple docs
140 Mysqlx.Crud.Update {
141 collection {
142 name: "maincoll"
143 schema: "mysqlxcoll"
144 }
145 data_model: DOCUMENT
146 criteria {
147 type: OPERATOR
148 operator {
149 name: "<"
150 param {
151 type: IDENT
152 identifier {
153 document_path {
154 type: MEMBER
155 value: "_id"
156 }
157 }
158 }
159 param {
160 type: LITERAL
161 literal {
162 type: V_OCTETS
163 v_octets {value:"3"}
164 }
165 }
166 }
167 }
168 operation {
169 source {
170 document_path {
171 type: MEMBER
172 value: "name"
173 }
174 }
175 operation: ITEM_REMOVE
176 }
177 }
178 #-- Mysqlx.Sql.StmtExecuteOk
179 -->recvresult
180
181 -->sql
182 select doc from maincoll;
183 -->endsql
184
185 -->echo Update a valid collection with string value and <= operator for multiple docs
186 Mysqlx.Crud.Update {
187 collection {
188 name: "maincoll"
189 schema: "mysqlxcoll"
190 }
191 data_model: DOCUMENT
192 criteria {
193 type: OPERATOR
194 operator {
195 name: "<="
196 param {
197 type: IDENT
198 identifier {
199 document_path {
200 type: MEMBER
201 value: "_id"
202 }
203 }
204 }
205 param {
206 type: LITERAL
207 literal {
208 type: V_OCTETS
209 v_octets {value:"3"}
210 }
211 }
212 }
213 }
214 operation {
215 source {
216 document_path {
217 type: MEMBER
218 value: "last_name"
219 }
220 }
221 operation: ITEM_REMOVE
222 }
223 }
224 #-- Mysqlx.Sql.StmtExecuteOk
225 -->recvresult
226
227 -->sql
228 select doc from maincoll;
229 -->endsql
230
231 -->echo Update a valid collection with string value and >= operator for multiple docs
232 Mysqlx.Crud.Update {
233 collection {
234 name: "maincoll"
235 schema: "mysqlxcoll"
236 }
237 data_model: DOCUMENT
238 criteria {
239 type: OPERATOR
240 operator {
241 name: ">="
242 param {
243 type: IDENT
244 identifier {
245 document_path {
246 type: MEMBER
247 value: "_id"
248 }
249 }
250 }
251 param {
252 type: LITERAL
253 literal {
254 type: V_OCTETS
255 v_octets {value:"4"}
256 }
257 }
258 }
259 }
260 operation {
261 source {
262 document_path {
263 type: MEMBER
264 value: "last_name"
265 }
266 }
267 operation: ITEM_REMOVE
268 }
269 }
270 #-- Mysqlx.Sql.StmtExecuteOk
271 -->recvresult
272
273 -->sql
274 select doc from maincoll;
275 -->endsql
276
277 -->echo Update with order clause update last _id in desc order (_id = 5)
278 Mysqlx.Crud.Update {
279 collection {
280 name: "maincoll"
281 schema: "mysqlxcoll"
282 }
283 data_model: DOCUMENT
284 criteria {
285 type: OPERATOR
286 operator {
287 name: "!="
288 param {
289 type: IDENT
290 identifier {
291 document_path {
292 type: MEMBER
293 value: "_id"
294 }
295 }
296 }
297 param {
298 type: LITERAL
299 literal {
300 type: V_OCTETS
301 v_octets {value:"0"}
302 }
303 }
304 }
305 }
306 limit {
307 row_count: 1
308 }
309 order {
310 expr {
311 type: IDENT
312 identifier {
313 name: "_id"
314 }
315 }
316 direction: DESC
317 }
318 operation {
319 source {
320 document_path {
321 type: MEMBER
322 value: "amount"
323 }
324 }
325 operation: ITEM_REMOVE
326 }
327 }
328 #-- Mysqlx.Sql.StmtExecuteOk
329 -->recvresult
330
331 -->sql
332 select doc from maincoll;
333 -->endsql
334
335 -->echo Update with order clause update first two _id in asc order (_id = 1,2)
336 Mysqlx.Crud.Update {
337 collection {
338 name: "maincoll"
339 schema: "mysqlxcoll"
340 }
341 data_model: DOCUMENT
342 criteria {
343 type: OPERATOR
344 operator {
345 name: "!="
346 param {
347 type: IDENT
348 identifier {
349 document_path {
350 type: MEMBER
351 value: "_id"
352 }
353 }
354 }
355 param {
356 type: LITERAL
357 literal {
358 type: V_OCTETS
359 v_octets {value:"0"}
360 }
361 }
362 }
363 }
364 limit {
365 row_count: 2
366 }
367 order {
368 expr {
369 type: IDENT
370 identifier {
371 name: "_id"
372 }
373 }
374 direction: ASC
375 }
376 operation {
377 source {
378 document_path {
379 type: MEMBER
380 value: "amount"
381 }
382 }
383 operation: ITEM_REMOVE
384 }
385 }
386 #-- Mysqlx.Sql.StmtExecuteOk
387 -->recvresult
388
389 -->sql
390 select doc from maincoll;
391 -->endsql
392
393 -->echo Update a valid collection with string value and != operator for multiple docs
394 Mysqlx.Crud.Update {
395 collection {
396 name: "maincoll"
397 schema: "mysqlxcoll"
398 }
399 data_model: DOCUMENT
400 criteria {
401 type: OPERATOR
402 operator {
403 name: "!="
404 param {
405 type: IDENT
406 identifier {
407 document_path {
408 type: MEMBER
409 value: "_id"
410 }
411 }
412 }
413 param {
414 type: LITERAL
415 literal {
416 type: V_OCTETS
417 v_octets {value:"2"}
418 }
419 }
420 }
421 }
422 operation {
423 source {
424 document_path {
425 type: MEMBER
426 value: "amount"
427 }
428 }
429 operation: ITEM_REMOVE
430 }
431 }
432 #-- Mysqlx.Sql.StmtExecuteOk
433 -->recvresult
434
435 -->sql
436 select doc from maincoll;
437 START TRANSACTION;
438 -->endsql
439
440 -->echo Update transaction rollback
441 Mysqlx.Crud.Update {
442 collection {
443 name: "maincoll"
444 schema: "mysqlxcoll"
445 }
446 data_model: DOCUMENT
447 criteria {
448 type: OPERATOR
449 operator {
450 name: "=="
451 param {
452 type: IDENT
453 identifier {
454 document_path {
455 type: MEMBER
456 value: "_id"
457 }
458 }
459 }
460 param {
461 type: LITERAL
462 literal {
463 type: V_OCTETS
464 v_octets {value:"3"}
465 }
466 }
467 }
468 }
469 operation {
470 source {
471 document_path {
472 type: MEMBER
473 value: "name"
474 }
475 }
476 operation: ITEM_REMOVE
477 }
478 }
479 #-- Mysqlx.Sql.StmtExecuteOk
480 -->recvresult
481
482 -->sql
483 select doc from maincoll;
484 ROLLBACK;
485 select doc from maincoll;
486 -->endsql
487
488 -->sql
489 START TRANSACTION;
490 -->endsql
491
492 -->echo Update transaction commit
493 Mysqlx.Crud.Update {
494 collection {
495 name: "maincoll"
496 schema: "mysqlxcoll"
497 }
498 data_model: DOCUMENT
499 criteria {
500 type: OPERATOR
501 operator {
502 name: "=="
503 param {
504 type: IDENT
505 identifier {
506 document_path {
507 type: MEMBER
508 value: "_id"
509 }
510 }
511 }
512 param {
513 type: LITERAL
514 literal {
515 type: V_OCTETS
516 v_octets {value:"3"}
517 }
518 }
519 }
520 }
521 operation {
522 source {
523 document_path {
524 type: MEMBER
525 value: "name"
526 }
527 }
528 operation: ITEM_REMOVE
529 }
530 }
531 #-- Mysqlx.Sql.StmtExecuteOk
532 -->recvresult
533
534 -->sql
535 COMMIT;
536 select doc from maincoll;
537 -->endsql
538
539 -->echo Update with non existing doc member
540 Mysqlx.Crud.Update {
541 collection {
542 name: "maincoll"
543 schema: "mysqlxcoll"
544 }
545 data_model: DOCUMENT
546 criteria {
547 type: OPERATOR
548 operator {
549 name: "!="
550 param {
551 type: IDENT
552 identifier {
553 document_path {
554 type: MEMBER
555 value: "_id"
556 }
557 }
558 }
559 param {
560 type: LITERAL
561 literal {
562 type: V_OCTETS
563 v_octets {value:"2"}
564 }
565 }
566 }
567 }
568 operation {
569 source {
570 document_path {
571 type: MEMBER
572 value: "notamember"
573 }
574 }
575 operation: ITEM_REMOVE
576 }
577 }
578 #-- Mysqlx.Sql.StmtExecuteOk
579 -->recvresult
580
581 -->sql
582 select doc from maincoll;
583 -->endsql
584
585 -->echo Invalid type of update for Document
586 Mysqlx.Crud.Update {
587 collection {
588 name: "maincoll"
589 schema: "mysqlxcoll"
590 }
591 data_model: DOCUMENT
592 criteria {
593 type: OPERATOR
594 operator {
595 name: ">"
596 param {
597 type: IDENT
598 identifier {
599 document_path {
600 type: MEMBER
601 value: "_id"
602 }
603 }
604 }
605 param {
606 type: LITERAL
607 literal {
608 type: V_OCTETS
609 v_octets {value:"3"}
610 }
611 }
612 }
613 }
614 operation {
615 source {
616 document_path {
617 type: MEMBER
618 value: "name"
619 }
620 }
621 operation: SET
622 value {
623 type: LITERAL
624 literal {
625 type: V_STRING
626 v_string {
627 value: "xtest"
628 }
629 }
630 }
631 }
632 }
633 -->expecterror 5051
634 -->recvresult
635
636 -->echo Invalid operator for Criteria
637 Mysqlx.Crud.Update {
638 collection {
639 name: "maincoll"
640 schema: "mysqlxcoll"
641 }
642 data_model: DOCUMENT
643 criteria {
644 type: OPERATOR
645 operator {
646 name: "not an operator"
647 param {
648 type: IDENT
649 identifier {
650 document_path {
651 type: MEMBER
652 value: "_id"
653 }
654 }
655 }
656 param {
657 type: LITERAL
658 literal {
659 type: V_OCTETS
660 v_octets {value:"3"}
661 }
662 }
663 }
664 }
665 operation {
666 source {
667 document_path {
668 type: MEMBER
669 value: "name"
670 }
671 }
672 operation: ITEM_REMOVE
673 }
674 }
675 -->expecterror 5150
676 -->recvresult
677
678 -->echo # Remove already removed item
679 -->sql
680 select doc from mysqlxcoll.maincoll;
681 -->endsql
682 Mysqlx.Crud.Update {
683 collection {
684 name: "maincoll"
685 schema: "mysqlxcoll"
686 }
687 data_model: DOCUMENT
688 operation {
689 source {
690 document_path {
691 type: MEMBER
692 value: "amount"
693 }
694 }
695 operation: ITEM_REMOVE
696 }
697 }
698 -->recvresult
699 -->sql
700 select doc from mysqlxcoll.maincoll;
701 -->endsql
702 Mysqlx.Crud.Update {
703 collection {
704 name: "maincoll"
705 schema: "mysqlxcoll"
706 }
707 data_model: DOCUMENT
708 operation {
709 source {
710 document_path {
711 type: MEMBER
712 value: "amount"
713 }
714 }
715 operation: ITEM_REMOVE
716 }
717 }
718 -->recvresult
719 -->sql
720 select doc from mysqlxcoll.maincoll;
721 -->endsql
722 -->echo # Can not remove immutable document member _id
723 Mysqlx.Crud.Update {
724 collection {
725 name: "maincoll"
726 schema: "mysqlxcoll"
727 }
728 data_model: DOCUMENT
729 operation {
730 source {
731 document_path {
732 type: MEMBER
733 value: "_id"
734 }
735 }
736 operation: ITEM_REMOVE
737 }
738 }
739 -->recverror 5053
740 -->sql
741 select doc from mysqlxcoll.maincoll;
742 -->endsql
743
744 ## Cleanup
745 -->echo ================================================================================
746 -->echo CLEAN UP
747 -->echo ================================================================================
748 -->sql
749 DROP USER updateitemremove@localhost;
750 SET SQL_SAFE_UPDATES = 1;
751 -->endsql
752 EOF
753
754 --exec $MYSQLXTEST -u updateitemremove --password='updateitemremove' --file=$MYSQL_TMP_DIR/mysqlx-update_itemremove.xpl 2>&1
755
756 ## Cleanup
757 DROP SCHEMA if EXISTS mysqlxcoll;
758 --source ../include/xplugin_cleanup.inc