Debian Codegraph @

mysql-test/suite/x/r/update_crud_itemmerge_o.result

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