Debian Codegraph @

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

raw
1 create user readuser@localhost identified by 'readuser';
2 grant select on *.* to readuser@localhost;
3 RUN DROP SCHEMA if EXISTS xplugintest
4
5 0 rows affected
6 RUN CREATE SCHEMA xplugintest DEFAULT CHARSET='utf8'
7
8 1 rows affected
9 RUN USE xplugintest
10
11 0 rows affected
12 RUN CREATE TABLE categories (
13 categoryid int NOT NULL AUTO_INCREMENT,
14 categoryname varchar(100),
15 categorydescription varchar(200),
16 categoryimei tinyint,
17 categorydecimal decimal(5,2),
18 categorybool bit(1),
19 PRIMARY key (categoryid)
20 )
21
22 0 rows affected
23 Insert multiple rows
24 send Mysqlx.Crud.Insert {
25 collection {
26 name: "categories"
27 schema: "xplugintest"
28 }
29 data_model: TABLE
30 projection {
31 name: "categoryid"
32 }
33 projection {
34 name: "categoryname"
35 }
36 projection {
37 name: "categorydescription"
38 }
39 projection {
40 name: "categoryimei"
41 }
42 projection {
43 name: "categorydecimal"
44 }
45 projection {
46 name: "categorybool"
47 }
48 row {
49 field {
50 type: LITERAL
51 literal {
52 type: V_SINT
53 v_signed_int: 1
54 }
55 }
56 field {
57 type: LITERAL
58 literal {
59 type: V_STRING
60 v_string {
61 value: "Sports"
62 }
63 }
64 }
65 field {
66 type: LITERAL
67 literal {
68 type: V_STRING
69 v_string {
70 value: "Sports related category"
71 }
72 }
73 }
74 field {
75 type: LITERAL
76 literal {
77 type: V_SINT
78 v_signed_int: 1
79 }
80 }
81 field {
82 type: LITERAL
83 literal {
84 type: V_DOUBLE
85 v_double: 235.15
86 }
87 }
88 field {
89 type: LITERAL
90 literal {
91 type: V_BOOL
92 v_bool: false
93 }
94 }
95 }
96 row {
97 field {
98 type: LITERAL
99 literal {
100 type: V_SINT
101 v_signed_int: 2
102 }
103 }
104 field {
105 type: LITERAL
106 literal {
107 type: V_STRING
108 v_string {
109 value: "Entertaiment"
110 }
111 }
112 }
113 field {
114 type: LITERAL
115 literal {
116 type: V_STRING
117 v_string {
118 value: "Entertaiment related category"
119 }
120 }
121 }
122 field {
123 type: LITERAL
124 literal {
125 type: V_SINT
126 v_signed_int: 2
127 }
128 }
129 field {
130 type: LITERAL
131 literal {
132 type: V_DOUBLE
133 v_double: 156.17
134 }
135 }
136 field {
137 type: LITERAL
138 literal {
139 type: V_BOOL
140 v_bool: true
141 }
142 }
143 }
144 row {
145 field {
146 type: LITERAL
147 literal {
148 type: V_SINT
149 v_signed_int: 18
150 }
151 }
152 field {
153 type: LITERAL
154 literal {
155 type: V_STRING
156 v_string {
157 value: "Cooking"
158 }
159 }
160 }
161 field {
162 type: LITERAL
163 literal {
164 type: V_STRING
165 v_string {
166 value: "Cooking related category"
167 }
168 }
169 }
170 field {
171 type: LITERAL
172 literal {
173 type: V_SINT
174 v_signed_int: 18
175 }
176 }
177 field {
178 type: LITERAL
179 literal {
180 type: V_DOUBLE
181 v_double: 116.17
182 }
183 }
184 field {
185 type: LITERAL
186 literal {
187 type: V_BOOL
188 v_bool: true
189 }
190 }
191 }
192 }
193
194
195 3 rows affected
196 last insert id: 18
197 Records: 3 Duplicates: 0 Warnings: 0
198 RUN SELECT * FROM categories
199 categoryid categoryname categorydescription categoryimei categorydecimal categorybool
200 1 Sports Sports related category 1 235.15 0
201 2 Entertaiment Entertaiment related category 2 156.17 1
202 18 Cooking Cooking related category 18 116.17 1
203 0 rows affected
204 "No auto increment column mentioned in projection"
205 send Mysqlx.Crud.Insert {
206 collection {
207 name: "categories"
208 schema: "xplugintest"
209 }
210 data_model: TABLE
211 projection {
212 name: "categoryname"
213 }
214 row {
215 field {
216 type: LITERAL
217 literal {
218 type: V_STRING
219 v_string {
220 value: "GROUND"
221 }
222 }
223 }
224 }
225 }
226
227
228 1 rows affected
229 last insert id: 19
230 RUN SELECT * FROM categories
231 categoryid categoryname categorydescription categoryimei categorydecimal categorybool
232 1 Sports Sports related category 1 235.15 0
233 2 Entertaiment Entertaiment related category 2 156.17 1
234 18 Cooking Cooking related category 18 116.17 1
235 19 GROUND null null null null
236 0 rows affected
237 "Enter NULL values"
238 send Mysqlx.Crud.Insert {
239 collection {
240 name: "categories"
241 schema: "xplugintest"
242 }
243 data_model: TABLE
244 projection {
245 name: "categoryname"
246 }
247 row {
248 field {
249 type: LITERAL
250 literal {
251 type: V_NULL
252 }
253 }
254 }
255 }
256
257
258 1 rows affected
259 last insert id: 20
260 RUN SELECT * FROM categories
261 categoryid categoryname categorydescription categoryimei categorydecimal categorybool
262 1 Sports Sports related category 1 235.15 0
263 2 Entertaiment Entertaiment related category 2 156.17 1
264 18 Cooking Cooking related category 18 116.17 1
265 19 GROUND null null null null
266 20 null null null null null
267 0 rows affected
268 RUN CREATE TABLE categories_child( col1 INT , col2 JSON ,
269 FOREIGN KEY (col1) REFERENCES categories(categoryid))
270
271 0 rows affected
272 send Mysqlx.Crud.Insert {
273 collection {
274 name: "categories_child"
275 schema: "xplugintest"
276 }
277 data_model: TABLE
278 projection {
279 name: "col1"
280 }
281 row {
282 field {
283 type: LITERAL
284 literal {
285 type: V_SINT
286 v_signed_int: 18
287 }
288 }
289 }
290 }
291
292
293 1 rows affected
294 send Mysqlx.Crud.Insert {
295 collection {
296 name: "categories_child"
297 schema: "xplugintest"
298 }
299 data_model: TABLE
300 projection {
301 name: "col1"
302 }
303 row {
304 field {
305 type: LITERAL
306 literal {
307 type: V_SINT
308 v_signed_int: 100
309 }
310 }
311 }
312 }
313
314 "Insert fails as referencing value not present in parent"
315 Got expected error: Cannot add or update a child row: a foreign key constraint fails (`xplugintest`.`categories_child`, CONSTRAINT `categories_child_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `categories` (`categoryid`)) (code 1452)
316 RUN SELECT * FROM categories_child
317 col1 col2
318 18 null
319 0 rows affected
320 "Column mismatch"
321 send Mysqlx.Crud.Insert {
322 collection {
323 name: "categories_child"
324 schema: "xplugintest"
325 }
326 data_model: TABLE
327 projection {
328 name: "col1"
329 }
330 projection {
331 name: "col2"
332 }
333 row {
334 field {
335 type: LITERAL
336 literal {
337 type: V_SINT
338 v_signed_int: 18
339 }
340 }
341 }
342 }
343
344 Got expected error: Wrong number of fields in row being inserted (code 5014)
345 RUN SELECT * FROM categories_child
346 col1 col2
347 18 null
348 0 rows affected
349 RUN DROP TABLE categories_child
350
351 0 rows affected
352
353 command ok
354 Insert multiple rows into a Document column
355 send Mysqlx.Crud.Insert {
356 collection {
357 name: "maincollection"
358 schema: "xplugintest"
359 }
360 data_model: DOCUMENT
361 row {
362 field {
363 type: LITERAL
364 literal {
365 type: V_STRING
366 v_string {
367 value: "\n{\n \"_id\": \"1\",\n \"name\": \"Omar Bras\", \"id\": \"1\"\n}"
368 }
369 }
370 }
371 }
372 row {
373 field {
374 type: LITERAL
375 literal {
376 type: V_STRING
377 v_string {
378 value: "\n{\n \"_id\": \"2\",\n \"name\": \"Omar Mex\", \"id\": \"2\"\n}"
379 }
380 }
381 }
382 }
383 }
384
385
386 2 rows affected
387 Records: 2 Duplicates: 0 Warnings: 0
388 RUN SELECT doc FROM maincollection
389 doc
390 {"id": "1", "_id": "1", "name": "Omar Bras"}
391 {"id": "2", "_id": "2", "name": "Omar Mex"}
392 0 rows affected
393 "Non unique value for _id member of `doc` "
394 send Mysqlx.Crud.Insert {
395 collection {
396 name: "maincollection"
397 schema: "xplugintest"
398 }
399 data_model: DOCUMENT
400 row {
401 field {
402 type: LITERAL
403 literal {
404 type: V_STRING
405 v_string {
406 value: "\n{\n \"_id\": \"3\",\n \"name\": \"Omar Bras\", \"id\": \"3\"\n}"
407 }
408 }
409 }
410 }
411 row {
412 field {
413 type: LITERAL
414 literal {
415 type: V_STRING
416 v_string {
417 value: "\n{\n \"_id\": \"2\",\n \"name\": \"Omar Mex\", \"id\": \"4\"\n}"
418 }
419 }
420 }
421 }
422 }
423
424 Got expected error: Document contains a field value that is not unique but required to be (code 5116)
425 "Error expected as non unique value for _id provided for member of JSON doc "
426 RUN SELECT doc FROM maincollection
427 doc
428 {"id": "1", "_id": "1", "name": "Omar Bras"}
429 {"id": "2", "_id": "2", "name": "Omar Mex"}
430 0 rows affected
431 "No _id member for JSON doc"
432 send Mysqlx.Crud.Insert {
433 collection {
434 name: "maincollection"
435 schema: "xplugintest"
436 }
437 data_model: DOCUMENT
438 row {
439 field {
440 type: LITERAL
441 literal {
442 type: V_STRING
443 v_string {
444 value: "\n{\n \"name\": \"Omar Bras\", \"id\": \"3\"\n}"
445 }
446 }
447 }
448 }
449 }
450
451
452 1 rows affected
453 auto-generated id(s): 0000XXXXXXXX0000000000000001
454 "Error expected as _id member is not provided for JSON doc "
455 RUN SELECT doc FROM maincollection
456 doc
457 {"id": "3", "_id": "0000XXXXXXXX0000000000000001", "name": "Omar Bras"}
458 {"id": "1", "_id": "1", "name": "Omar Bras"}
459 {"id": "2", "_id": "2", "name": "Omar Mex"}
460 0 rows affected
461 "projection is not allowed for DOCUMENT table"
462 send Mysqlx.Crud.Insert {
463 collection {
464 name: "maincollection"
465 schema: "xplugintest"
466 }
467 data_model: DOCUMENT
468 projection {
469 name: "doc"
470 }
471 row {
472 field {
473 type: LITERAL
474 literal {
475 type: V_STRING
476 v_string {
477 value: "\n{\n \"name\": \"Omar Bras\", \"id\": \"3\"\n}"
478 }
479 }
480 }
481 }
482 }
483
484 Got expected error: Invalid projection for document operation (code 5114)
485 "Error expected as projection given for DOCUMENT table "
486 RUN SELECT doc FROM maincollection
487 doc
488 {"id": "3", "_id": "0000XXXXXXXX0000000000000001", "name": "Omar Bras"}
489 {"id": "1", "_id": "1", "name": "Omar Bras"}
490 {"id": "2", "_id": "2", "name": "Omar Mex"}
491 0 rows affected
492 "Error when inserting doc as null"
493 send Mysqlx.Crud.Insert {
494 collection {
495 name: "maincollection"
496 schema: "xplugintest"
497 }
498 data_model: DOCUMENT
499 row {
500 field {
501 type: LITERAL
502 literal {
503 type: V_NULL
504 }
505 }
506 }
507 }
508
509 Got expected error (one of: 5114 5115 )
510 RUN SELECT doc FROM maincollection
511 doc
512 {"id": "3", "_id": "0000XXXXXXXX0000000000000001", "name": "Omar Bras"}
513 {"id": "1", "_id": "1", "name": "Omar Bras"}
514 {"id": "2", "_id": "2", "name": "Omar Mex"}
515 0 rows affected
516 "enter null value in doc member"
517 send Mysqlx.Crud.Insert {
518 collection {
519 name: "maincollection"
520 schema: "xplugintest"
521 }
522 data_model: DOCUMENT
523 row {
524 field {
525 type: LITERAL
526 literal {
527 type: V_STRING
528 v_string {
529 value: "\n{\n \"_id\": \"4\",\n \"name\": \"Jackson\", \"id\": null\n}"
530 }
531 }
532 }
533 }
534 }
535
536
537 1 rows affected
538 RUN SELECT doc FROM maincollection
539 doc
540 {"id": "3", "_id": "0000XXXXXXXX0000000000000001", "name": "Omar Bras"}
541 {"id": "1", "_id": "1", "name": "Omar Bras"}
542 {"id": "2", "_id": "2", "name": "Omar Mex"}
543 {"id": null, "_id": "4", "name": "Jackson"}
544 0 rows affected
545 "Insert using transaction"
546 RUN START TRANSACTION
547
548 0 rows affected
549 send Mysqlx.Crud.Insert {
550 collection {
551 name: "maincollection"
552 schema: "xplugintest"
553 }
554 data_model: DOCUMENT
555 row {
556 field {
557 type: LITERAL
558 literal {
559 type: V_STRING
560 v_string {
561 value: "\n{\n \"_id\": \"5\",\n \"name\": \"Jimmy \", \"id\": null\n}"
562 }
563 }
564 }
565 }
566 }
567
568
569 1 rows affected
570 RUN SAVEPOINT A
571
572 0 rows affected
573 send Mysqlx.Crud.Insert {
574 collection {
575 name: "maincollection"
576 schema: "xplugintest"
577 }
578 data_model: DOCUMENT
579 row {
580 field {
581 type: LITERAL
582 literal {
583 type: V_STRING
584 v_string {
585 value: "\n{\n \"_id\": \"6\",\n \"name\": \"Joe\", \"id\": null\n}"
586 }
587 }
588 }
589 }
590 }
591
592
593 1 rows affected
594 RUN SAVEPOINT B
595
596 0 rows affected
597 send Mysqlx.Crud.Insert {
598 collection {
599 name: "maincollection"
600 schema: "xplugintest"
601 }
602 data_model: DOCUMENT
603 row {
604 field {
605 type: LITERAL
606 literal {
607 type: V_STRING
608 v_string {
609 value: "\n{\n \"_id\": \"7\",\n \"name\": \"Jacky\", \"id\": null\n}"
610 }
611 }
612 }
613 }
614 }
615
616
617 1 rows affected
618 RUN ROLLBACK TO SAVEPOINT A
619
620 0 rows affected
621 RUN COMMIT
622
623 0 rows affected
624 RUN SELECT doc FROM maincollection
625 doc
626 {"id": "3", "_id": "0000XXXXXXXX0000000000000001", "name": "Omar Bras"}
627 {"id": "1", "_id": "1", "name": "Omar Bras"}
628 {"id": "2", "_id": "2", "name": "Omar Mex"}
629 {"id": null, "_id": "4", "name": "Jackson"}
630 {"id": null, "_id": "5", "name": "Jimmy "}
631 0 rows affected
632 RUN LOCK TABLES maincollection READ
633
634 0 rows affected
635 send Mysqlx.Crud.Insert {
636 collection {
637 name: "maincollection"
638 schema: "xplugintest"
639 }
640 data_model: DOCUMENT
641 row {
642 field {
643 type: LITERAL
644 literal {
645 type: V_STRING
646 v_string {
647 value: "\n{\n \"_id\": \"7\",\n \"name\": \"Jacky\", \"id\": null\n}"
648 }
649 }
650 }
651 }
652 }
653
654 Got expected error: Table 'maincollection' was locked with a READ lock and can't be updated (code 1099)
655 "Error as table is locked with read lock"
656 RUN SELECT doc FROM maincollection
657 doc
658 {"id": "3", "_id": "0000XXXXXXXX0000000000000001", "name": "Omar Bras"}
659 {"id": "1", "_id": "1", "name": "Omar Bras"}
660 {"id": "2", "_id": "2", "name": "Omar Mex"}
661 {"id": null, "_id": "4", "name": "Jackson"}
662 {"id": null, "_id": "5", "name": "Jimmy "}
663 0 rows affected
664 RUN UNLOCK TABLES
665
666 0 rows affected
667 RUN LOCK TABLES maincollection WRITE
668
669 0 rows affected
670 send Mysqlx.Crud.Insert {
671 collection {
672 name: "maincollection"
673 schema: "xplugintest"
674 }
675 data_model: DOCUMENT
676 row {
677 field {
678 type: LITERAL
679 literal {
680 type: V_STRING
681 v_string {
682 value: "\n{\n \"_id\": \"7\",\n \"name\": \"Jacky\", \"id\": null\n}"
683 }
684 }
685 }
686 }
687 }
688
689
690 1 rows affected
691 RUN SELECT doc FROM maincollection
692 doc
693 {"id": "3", "_id": "0000XXXXXXXX0000000000000001", "name": "Omar Bras"}
694 {"id": "1", "_id": "1", "name": "Omar Bras"}
695 {"id": "2", "_id": "2", "name": "Omar Mex"}
696 {"id": null, "_id": "4", "name": "Jackson"}
697 {"id": null, "_id": "5", "name": "Jimmy "}
698 {"id": null, "_id": "7", "name": "Jacky"}
699 0 rows affected
700 RUN UNLOCK TABLES
701
702 0 rows affected
703 Mysqlx.Ok {
704 msg: "bye!"
705 }
706 ok
707 RUN SET SESSION lock_wait_timeout= 3
708
709 0 rows affected
710 send Mysqlx.Crud.Insert {
711 collection {
712 name: "categories"
713 schema: "xplugintest"
714 }
715 data_model: TABLE
716 projection {
717 name: "categoryname"
718 }
719 row {
720 field {
721 type: LITERAL
722 literal {
723 type: V_STRING
724 v_string {
725 value: "Mountain"
726 }
727 }
728 }
729 }
730 }
731
732 Got expected error (one of: 1142 1205 )
733 RUN SELECT * FROM xplugintest.categories
734 categoryid categoryname categorydescription categoryimei categorydecimal categorybool
735 1 Sports Sports related category 1 235.15 0
736 2 Entertaiment Entertaiment related category 2 156.17 1
737 18 Cooking Cooking related category 18 116.17 1
738 19 GROUND null null null null
739 20 null null null null null
740 0 rows affected
741 Got expected error (one of: 0 1205 )
742 Mysqlx.Ok {
743 msg: "bye!"
744 }
745 ok
746 LOCK TABLES xplugintest.categories WRITE;
747 RUN SET SESSION lock_wait_timeout= 3
748
749 0 rows affected
750 send Mysqlx.Crud.Insert {
751 collection {
752 name: "categories"
753 schema: "xplugintest"
754 }
755 data_model: TABLE
756 projection {
757 name: "categoryname"
758 }
759 row {
760 field {
761 type: LITERAL
762 literal {
763 type: V_STRING
764 v_string {
765 value: "Mountain"
766 }
767 }
768 }
769 }
770 }
771
772 Got expected error (one of: 1142 1205 )
773 RUN SELECT * FROM xplugintest.categories
774 While executing SELECT * FROM xplugintest.categories:
775 Got expected error (one of: 0 1205 )
776 Mysqlx.Ok {
777 msg: "bye!"
778 }
779 ok
780 UNLOCK TABLES;
781 DELETE FROM xplugintest.maincollection;
782 SELECT COUNT(*) FROM xplugintest.maincollection;
783 COUNT(*)
784 30
785 DROP SCHEMA if EXISTS xplugintest;
786 DROP USER readuser@localhost;