Debian Codegraph @

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

raw
1 ########### ../t/insert_crud_o.test ###########
2 ### #
3 ### This test runs aims to run INSERT operation statements #
4 ### variant with mysqlxtest client for Table and Document data models. #
5 ### Test covers #
6 ### - insert with different operators #
7 ### - insert multiple rows #
8 ### - insert with rollback transaction #
9 ### - status variable Mysqlx_crud_insert for number of Insert #
10 ### requests received #
11 ### #
12 ########################################################################
13 #
14
15 --echo ==============================================
16 --echo CRUD INSERT SCENARIOS
17 --echo ==============================================
18 --echo
19
20 ## Preamble
21 --echo ================================================================================
22 --echo PREAMBLE
23 --echo ================================================================================
24 --source include/xplugin_preamble.inc
25 create user insertcruduser@localhost identified by 'insertcruduser';
26 grant all on *.* to insertcruduser@localhost;
27
28 ## TEST STARTS HERE
29 --echo ================================================================================
30 --echo TEST START
31 --echo ================================================================================
32
33 --write_file $MYSQL_TMP_DIR/mysqlx-insert_CRUD.xpl
34 -->sql
35 DROP SCHEMA if EXISTS mysqlxplugin;
36 CREATE SCHEMA mysqlxplugin DEFAULT CHARSET='utf8';
37 USE mysqlxplugin;
38 CREATE TABLE categories (
39 CategoryID int NOT NULL AUTO_INCREMENT,
40 CategoryName varchar(100),
41 CategoryDescription varchar(200),
42 CategoryIMEI tinyint,
43 CategoryDecimal decimal(5,2),
44 CategoryBool bit(1),
45 PRIMARY key (CategoryID)
46 );
47
48 SHOW STATUS like 'Mysqlx_crud_insert';
49 -->endsql
50
51 -->echo Insert multiple rows
52 Mysqlx.Crud.Insert {
53 collection {
54 name: "categories"
55 schema: "mysqlxplugin"
56 }
57 data_model: TABLE
58 projection {
59 name: "CategoryID"
60 }
61 projection {
62 name: "CategoryName"
63 }
64 projection {
65 name: "CategoryDescription"
66 }
67 projection {
68 name: "CategoryIMEI"
69 }
70 projection {
71 name: "CategoryDecimal"
72 }
73 projection {
74 name: "CategoryBool"
75 }
76 row {
77 field {
78 type: LITERAL
79 literal {
80 type: V_SINT
81 v_signed_int: 1
82 }
83 }
84 field {
85 type: LITERAL
86 literal {
87 type: V_STRING
88 v_string {
89 value: "Sports"
90 }
91 }
92 }
93 field {
94 type: LITERAL
95 literal {
96 type: V_STRING
97 v_string {
98 value: "Sports related category"
99 }
100 }
101 }
102 field {
103 type: LITERAL
104 literal {
105 type: V_SINT
106 v_signed_int: 1
107 }
108 }
109 field {
110 type: LITERAL
111 literal {
112 type: V_DOUBLE
113 v_double: 235.15
114 }
115 }
116 field {
117 type: LITERAL
118 literal {
119 type: V_BOOL
120 v_bool: false
121 }
122 }
123 }
124 row {
125 field {
126 type: LITERAL
127 literal {
128 type: V_SINT
129 v_signed_int: 2
130 }
131 }
132 field {
133 type: LITERAL
134 literal {
135 type: V_STRING
136 v_string {
137 value: "Entertaiment"
138 }
139 }
140 }
141 field {
142 type: LITERAL
143 literal {
144 type: V_STRING
145 v_string {
146 value: "Entertaiment related category"
147 }
148 }
149 }
150 field {
151 type: LITERAL
152 literal {
153 type: V_SINT
154 v_signed_int: 2
155 }
156 }
157 field {
158 type: LITERAL
159 literal {
160 type: V_DOUBLE
161 v_double: 156.17
162 }
163 }
164 field {
165 type: LITERAL
166 literal {
167 type: V_BOOL
168 v_bool: true
169 }
170 }
171 }
172 row {
173 field {
174 type: LITERAL
175 literal {
176 type: V_SINT
177 v_signed_int: 18
178 }
179 }
180 field {
181 type: LITERAL
182 literal {
183 type: V_STRING
184 v_string {
185 value: "Cooking"
186 }
187 }
188 }
189 field {
190 type: LITERAL
191 literal {
192 type: V_STRING
193 v_string {
194 value: "Cooking related category"
195 }
196 }
197 }
198 field {
199 type: LITERAL
200 literal {
201 type: V_SINT
202 v_signed_int: 18
203 }
204 }
205 field {
206 type: LITERAL
207 literal {
208 type: V_DOUBLE
209 v_double: 116.17
210 }
211 }
212 field {
213 type: LITERAL
214 literal {
215 type: V_BOOL
216 v_bool: true
217 }
218 }
219 }
220 }
221 #-- Mysqlx.Sql.StmtExecuteOk
222 -->recvresult
223
224 -->sql
225 select * from categories;
226 SHOW STATUS like 'Mysqlx_crud_insert';
227 -->endsql
228
229 ## Corner Values
230 -->echo Insert Quotes with scape character
231 Mysqlx.Crud.Insert {
232 collection {
233 name: "categories"
234 schema: "mysqlxplugin"
235 }
236 data_model: TABLE
237 projection {
238 name: "CategoryID"
239 }
240 projection {
241 name: "CategoryName"
242 }
243 projection {
244 name: "CategoryDescription"
245 }
246 projection {
247 name: "CategoryIMEI"
248 }
249 projection {
250 name: "CategoryDecimal"
251 }
252 projection {
253 name: "CategoryBool"
254 }
255 row {
256 field {
257 type: LITERAL
258 literal {
259 type: V_SINT
260 v_signed_int: 3
261 }
262 }
263 field {
264 type: LITERAL
265 literal {
266 type: V_STRING
267 v_string {
268 value: "\"Quotes\""
269 }
270 }
271 }
272 field {
273 type: LITERAL
274 literal {
275 type: V_STRING
276 v_string {
277 value: "Quote related category"
278 }
279 }
280 }
281 field {
282 type: LITERAL
283 literal {
284 type: V_SINT
285 v_signed_int: 3
286 }
287 }
288 field {
289 type: LITERAL
290 literal {
291 type: V_DOUBLE
292 v_double: 184.25
293 }
294 }
295 field {
296 type: LITERAL
297 literal {
298 type: V_BOOL
299 v_bool: false
300 }
301 }
302 }
303 }
304 #-- Mysqlx.Sql.StmtExecuteOk
305 -->recvresult
306
307 -->sql
308 select * from categories;
309 SHOW STATUS like 'Mysqlx_crud_insert';
310 -->endsql
311
312 -->echo Insert NULL values
313 Mysqlx.Crud.Insert {
314 collection {
315 name: "categories"
316 schema: "mysqlxplugin"
317 }
318 data_model: TABLE
319 projection {
320 name: "CategoryID"
321 }
322 projection {
323 name: "CategoryName"
324 }
325 projection {
326 name: "CategoryDescription"
327 }
328 projection {
329 name: "CategoryIMEI"
330 }
331 projection {
332 name: "CategoryDecimal"
333 }
334 projection {
335 name: "CategoryBool"
336 }
337 row {
338 field {
339 type: LITERAL
340 literal {
341 type: V_NULL
342 }
343 }
344 field {
345 type: LITERAL
346 literal {
347 type: V_NULL
348 }
349 }
350 field {
351 type: LITERAL
352 literal {
353 type: V_NULL
354 }
355 }
356 field {
357 type: LITERAL
358 literal {
359 type: V_NULL
360 }
361 }
362 field {
363 type: LITERAL
364 literal {
365 type: V_NULL
366 }
367 }
368 field {
369 type: LITERAL
370 literal {
371 type: V_NULL
372 }
373 }
374 }
375 }
376 #-- Mysqlx.Sql.StmtExecuteOk
377 -->recvresult
378
379 -->sql
380 select * from categories;
381 SHOW STATUS like 'Mysqlx_crud_insert';
382 -->endsql
383
384 -->echo Insert Corner decimal value
385 Mysqlx.Crud.Insert {
386 collection {
387 name: "categories"
388 schema: "mysqlxplugin"
389 }
390 data_model: TABLE
391 projection {
392 name: "CategoryID"
393 }
394 projection {
395 name: "CategoryName"
396 }
397 projection {
398 name: "CategoryDescription"
399 }
400 projection {
401 name: "CategoryIMEI"
402 }
403 projection {
404 name: "CategoryDecimal"
405 }
406 projection {
407 name: "CategoryBool"
408 }
409 row {
410 field {
411 type: LITERAL
412 literal {
413 type: V_SINT
414 v_signed_int: 6
415 }
416 }
417 field {
418 type: LITERAL
419 literal {
420 type: V_STRING
421 v_string {
422 value: "Kitchen2"
423 }
424 }
425 }
426 field {
427 type: LITERAL
428 literal {
429 type: V_STRING
430 v_string {
431 value: "Kitchen2 related category"
432 }
433 }
434 }
435 field {
436 type: LITERAL
437 literal {
438 type: V_SINT
439 v_signed_int: 6
440 }
441 }
442 field {
443 type: LITERAL
444 literal {
445 type: V_DOUBLE
446 v_double: 999.99
447 }
448 }
449 field {
450 type: LITERAL
451 literal {
452 type: V_BOOL
453 v_bool: true
454 }
455 }
456 }
457 row {
458 field {
459 type: LITERAL
460 literal {
461 type: V_SINT
462 v_signed_int: 12
463 }
464 }
465 field {
466 type: LITERAL
467 literal {
468 type: V_STRING
469 v_string {
470 value: "Kitchen"
471 }
472 }
473 }
474 field {
475 type: LITERAL
476 literal {
477 type: V_STRING
478 v_string {
479 value: "Kitchen related category"
480 }
481 }
482 }
483 field {
484 type: LITERAL
485 literal {
486 type: V_SINT
487 v_signed_int: 12
488 }
489 }
490 field {
491 type: LITERAL
492 literal {
493 type: V_DOUBLE
494 v_double: -999.99
495 }
496 }
497 field {
498 type: LITERAL
499 literal {
500 type: V_BOOL
501 v_bool: false
502 }
503 }
504 }
505 }
506 #-- Mysqlx.Sql.StmtExecuteOk
507 -->recvresult
508
509 -->sql
510 select * from categories;
511 SHOW STATUS like 'Mysqlx_crud_insert';
512 -->endsql
513
514 -->echo Insert Corner tiny int value
515 Mysqlx.Crud.Insert {
516 collection {
517 name: "categories"
518 schema: "mysqlxplugin"
519 }
520 data_model: TABLE
521 projection {
522 name: "CategoryID"
523 }
524 projection {
525 name: "CategoryName"
526 }
527 projection {
528 name: "CategoryDescription"
529 }
530 projection {
531 name: "CategoryIMEI"
532 }
533 projection {
534 name: "CategoryDecimal"
535 }
536 projection {
537 name: "CategoryBool"
538 }
539 row {
540 field {
541 type: LITERAL
542 literal {
543 type: V_SINT
544 v_signed_int: 13
545 }
546 }
547 field {
548 type: LITERAL
549 literal {
550 type: V_STRING
551 v_string {
552 value: "Kitchen"
553 }
554 }
555 }
556 field {
557 type: LITERAL
558 literal {
559 type: V_STRING
560 v_string {
561 value: "Kitchen related category"
562 }
563 }
564 }
565 field {
566 type: LITERAL
567 literal {
568 type: V_SINT
569 v_signed_int: 127
570 }
571 }
572 field {
573 type: LITERAL
574 literal {
575 type: V_DOUBLE
576 v_double: 156.99
577 }
578 }
579 field {
580 type: LITERAL
581 literal {
582 type: V_BOOL
583 v_bool: false
584 }
585 }
586 }
587 row {
588 field {
589 type: LITERAL
590 literal {
591 type: V_SINT
592 v_signed_int: 14
593 }
594 }
595 field {
596 type: LITERAL
597 literal {
598 type: V_STRING
599 v_string {
600 value: "Kitchen"
601 }
602 }
603 }
604 field {
605 type: LITERAL
606 literal {
607 type: V_STRING
608 v_string {
609 value: "Kitchen related category"
610 }
611 }
612 }
613 field {
614 type: LITERAL
615 literal {
616 type: V_SINT
617 v_signed_int: -128
618 }
619 }
620 field {
621 type: LITERAL
622 literal {
623 type: V_DOUBLE
624 v_double: 156.99
625 }
626 }
627 field {
628 type: LITERAL
629 literal {
630 type: V_BOOL
631 v_bool: false
632 }
633 }
634 }
635 }
636 #-- Mysqlx.Sql.StmtExecuteOk
637 -->recvresult
638
639 -->sql
640 select * from categories;
641 SHOW STATUS like 'Mysqlx_crud_insert';
642 -->endsql
643 EOF
644
645 --exec $MYSQLXTEST -u insertcruduser --password='insertcruduser' --file=$MYSQL_TMP_DIR/mysqlx-insert_CRUD.xpl 2>&1
646 --remove_file $MYSQL_TMP_DIR/mysqlx-insert_CRUD.xpl
647
648 --echo .
649 --echo #---------- START NEGATIVE TABLE TESTING----------#
650 --echo .
651
652 --write_file $MYSQL_TMP_DIR/mysqlx-insert_Negative_CRUD.xpl
653 -->sql
654 USE mysqlxplugin;
655 -->endsql
656
657 ## ******************************************
658 ## 2. Negative Table Test starts here |
659 ## |
660 ## ******************************************
661
662 -->echo Repeat key column ID
663 ##Error Code: 1062
664 Mysqlx.Crud.Insert {
665 collection {
666 name: "categories"
667 schema: "mysqlxplugin"
668 }
669 data_model: TABLE
670 projection {
671 name: "CategoryID"
672 }
673 projection {
674 name: "CategoryName"
675 }
676 projection {
677 name: "CategoryDescription"
678 }
679 projection {
680 name: "CategoryIMEI"
681 }
682 projection {
683 name: "CategoryDecimal"
684 }
685 projection {
686 name: "CategoryBool"
687 }
688 row {
689 field {
690 type: LITERAL
691 literal {
692 type: V_SINT
693 v_signed_int: 1
694 }
695 }
696 field {
697 type: LITERAL
698 literal {
699 type: V_STRING
700 v_string {
701 value: "Home"
702 }
703 }
704 }
705 field {
706 type: LITERAL
707 literal {
708 type: V_STRING
709 v_string {
710 value: "Home related category"
711 }
712 }
713 }
714 field {
715 type: LITERAL
716 literal {
717 type: V_SINT
718 v_signed_int: 1
719 }
720 }
721 field {
722 type: LITERAL
723 literal {
724 type: V_FLOAT
725 v_float: 235.15
726 }
727 }
728 field {
729 type: LITERAL
730 literal {
731 type: V_BOOL
732 v_bool: false
733 }
734 }
735 }
736 }
737 -->expecterror 1062
738 -->recvresult
739
740 -->echo Data types corner value plus 1
741 ## Error Code: 1264
742 Mysqlx.Crud.Insert {
743 collection {
744 name: "categories"
745 schema: "mysqlxplugin"
746 }
747 data_model: TABLE
748 projection {
749 name: "CategoryID"
750 }
751 projection {
752 name: "CategoryName"
753 }
754 projection {
755 name: "CategoryDescription"
756 }
757 projection {
758 name: "CategoryIMEI"
759 }
760 projection {
761 name: "CategoryDecimal"
762 }
763 projection {
764 name: "CategoryBool"
765 }
766 row {
767 field {
768 type: LITERAL
769 literal {
770 type: V_SINT
771 v_signed_int: 5
772 }
773 }
774 field {
775 type: LITERAL
776 literal {
777 type: V_STRING
778 v_string {
779 value: "Kitchen"
780 }
781 }
782 }
783 field {
784 type: LITERAL
785 literal {
786 type: V_STRING
787 v_string {
788 value: "Kitchen related category"
789 }
790 }
791 }
792 field {
793 type: LITERAL
794 literal {
795 type: V_SINT
796 v_signed_int: 4
797 }
798 }
799 field {
800 type: LITERAL
801 literal {
802 type: V_DOUBLE
803 v_double: 1000.00
804 }
805 }
806 field {
807 type: LITERAL
808 literal {
809 type: V_BOOL
810 v_bool: false
811 }
812 }
813 }
814 row {
815 field {
816 type: LITERAL
817 literal {
818 type: V_SINT
819 v_signed_int: 5
820 }
821 }
822 field {
823 type: LITERAL
824 literal {
825 type: V_STRING
826 v_string {
827 value: "Kitchen"
828 }
829 }
830 }
831 field {
832 type: LITERAL
833 literal {
834 type: V_STRING
835 v_string {
836 value: "Kitchen related category"
837 }
838 }
839 }
840 field {
841 type: LITERAL
842 literal {
843 type: V_SINT
844 v_signed_int: 128
845 }
846 }
847 field {
848 type: LITERAL
849 literal {
850 type: V_DOUBLE
851 v_double: 125.35
852 }
853 }
854 field {
855 type: LITERAL
856 literal {
857 type: V_BOOL
858 v_bool: false
859 }
860 }
861 }
862 }
863 -->expecterror 1264
864 -->recvresult
865
866 ## Correspondence between data and field types
867 -->echo String value in tiny int data type column
868 ## Error Code: 1366
869 Mysqlx.Crud.Insert {
870 collection {
871 name: "categories"
872 schema: "mysqlxplugin"
873 }
874 data_model: TABLE
875 projection {
876 name: "CategoryID"
877 }
878 projection {
879 name: "CategoryName"
880 }
881 projection {
882 name: "CategoryDescription"
883 }
884 projection {
885 name: "CategoryIMEI"
886 }
887 projection {
888 name: "CategoryDecimal"
889 }
890 projection {
891 name: "CategoryBool"
892 }
893 row {
894 field {
895 type: LITERAL
896 literal {
897 type: V_SINT
898 v_signed_int: 5
899 }
900 }
901 field {
902 type: LITERAL
903 literal {
904 type: V_STRING
905 v_string {
906 value: "Kitchen"
907 }
908 }
909 }
910 field {
911 type: LITERAL
912 literal {
913 type: V_STRING
914 v_string {
915 value: "Kitchen related category"
916 }
917 }
918 }
919 field {
920 type: LITERAL
921 literal {
922 type: V_STRING
923 v_string {
924 value: "TinyInt"
925 }
926 }
927 }
928 field {
929 type: LITERAL
930 literal {
931 type: V_DOUBLE
932 v_double: 235.15
933 }
934 }
935 field {
936 type: LITERAL
937 literal {
938 type: V_BOOL
939 v_bool: false
940 }
941 }
942 }
943 }
944 -->expecterror 1366
945 -->recvresult
946
947 -->echo String value in decimal data type column
948 Mysqlx.Crud.Insert {
949 collection {
950 name: "categories"
951 schema: "mysqlxplugin"
952 }
953 data_model: TABLE
954 projection {
955 name: "CategoryID"
956 }
957 projection {
958 name: "CategoryName"
959 }
960 projection {
961 name: "CategoryDescription"
962 }
963 projection {
964 name: "CategoryIMEI"
965 }
966 projection {
967 name: "CategoryDecimal"
968 }
969 projection {
970 name: "CategoryBool"
971 }
972 row {
973 field {
974 type: LITERAL
975 literal {
976 type: V_SINT
977 v_signed_int: 5
978 }
979 }
980 field {
981 type: LITERAL
982 literal {
983 type: V_STRING
984 v_string {
985 value: "Kitchen"
986 }
987 }
988 }
989 field {
990 type: LITERAL
991 literal {
992 type: V_STRING
993 v_string {
994 value: "Kitchen related category"
995 }
996 }
997 }
998 field {
999 type: LITERAL
1000 literal {
1001 type: V_SINT
1002 v_signed_int: 4
1003 }
1004 }
1005 field {
1006 type: LITERAL
1007 literal {
1008 type: V_STRING
1009 v_string {
1010 value: "Decimal"
1011 }
1012 }
1013 }
1014 field {
1015 type: LITERAL
1016 literal {
1017 type: V_BOOL
1018 v_bool: false
1019 }
1020 }
1021 }
1022 }
1023 -->expecterror 1366
1024 -->recvresult
1025
1026 -->echo Invalid Schema name
1027 Mysqlx.Crud.Insert {
1028 collection {
1029 name: "categories"
1030 schema: "schema_no_exist"
1031 }
1032 data_model: TABLE
1033 projection {
1034 name: "CategoryID"
1035 }
1036 projection {
1037 name: "CategoryName"
1038 }
1039 projection {
1040 name: "CategoryDescription"
1041 }
1042 projection {
1043 name: "CategoryIMEI"
1044 }
1045 projection {
1046 name: "CategoryDecimal"
1047 }
1048 projection {
1049 name: "CategoryBool"
1050 }
1051 row {
1052 field {
1053 type: LITERAL
1054 literal {
1055 type: V_SINT
1056 v_signed_int: 5
1057 }
1058 }
1059 field {
1060 type: LITERAL
1061 literal {
1062 type: V_STRING
1063 v_string {
1064 value: "Kitchen"
1065 }
1066 }
1067 }
1068 field {
1069 type: LITERAL
1070 literal {
1071 type: V_STRING
1072 v_string {
1073 value: "Kitchen related category"
1074 }
1075 }
1076 }
1077 field {
1078 type: LITERAL
1079 literal {
1080 type: V_SINT
1081 v_signed_int: 4
1082 }
1083 }
1084 field {
1085 type: LITERAL
1086 literal {
1087 type: V_DOUBLE
1088 v_double: 256.54
1089 }
1090 }
1091 field {
1092 type: LITERAL
1093 literal {
1094 type: V_BOOL
1095 v_bool: false
1096 }
1097 }
1098 }
1099 }
1100 -->expecterror 1049
1101 -->recvresult
1102
1103 -->echo Invalid Table name
1104 Mysqlx.Crud.Insert {
1105 collection {
1106 name: "table_no_exist"
1107 schema: "mysqlxplugin"
1108 }
1109 data_model: TABLE
1110 projection {
1111 name: "CategoryID"
1112 }
1113 projection {
1114 name: "CategoryName"
1115 }
1116 projection {
1117 name: "CategoryDescription"
1118 }
1119 projection {
1120 name: "CategoryIMEI"
1121 }
1122 projection {
1123 name: "CategoryDecimal"
1124 }
1125 projection {
1126 name: "CategoryBool"
1127 }
1128 row {
1129 field {
1130 type: LITERAL
1131 literal {
1132 type: V_STRING
1133 v_string {
1134 value: "NoIntValue"
1135 }
1136 }
1137 }
1138 field {
1139 type: LITERAL
1140 literal {
1141 type: V_STRING
1142 v_string {
1143 value: "Kitchen"
1144 }
1145 }
1146 }
1147 field {
1148 type: LITERAL
1149 literal {
1150 type: V_STRING
1151 v_string {
1152 value: "Kitchen related category"
1153 }
1154 }
1155 }
1156 field {
1157 type: LITERAL
1158 literal {
1159 type: V_SINT
1160 v_signed_int: 4
1161 }
1162 }
1163 field {
1164 type: LITERAL
1165 literal {
1166 type: V_DOUBLE
1167 v_double: 256.54
1168 }
1169 }
1170 field {
1171 type: LITERAL
1172 literal {
1173 type: V_BOOL
1174 v_bool: false
1175 }
1176 }
1177 }
1178 }
1179 -->expecterror 1146
1180 -->recvresult
1181
1182 -->echo Invalid Column name
1183 Mysqlx.Crud.Insert {
1184 collection {
1185 name: "categories_no_exist"
1186 schema: "mysqlxplugin"
1187 }
1188 data_model: TABLE
1189 projection {
1190 name: "CategoryID"
1191 }
1192 projection {
1193 name: "ColumnNoExist"
1194 }
1195 projection {
1196 name: "CategoryDescription"
1197 }
1198 projection {
1199 name: "CategoryIMEI"
1200 }
1201 projection {
1202 name: "CategoryDecimal"
1203 }
1204 projection {
1205 name: "CategoryBool"
1206 }
1207 row {
1208 field {
1209 type: LITERAL
1210 literal {
1211 type: V_SINT
1212 v_signed_int: 5
1213 }
1214 }
1215 field {
1216 type: LITERAL
1217 literal {
1218 type: V_STRING
1219 v_string {
1220 value: "Kitchen"
1221 }
1222 }
1223 }
1224 field {
1225 type: LITERAL
1226 literal {
1227 type: V_STRING
1228 v_string {
1229 value: "Kitchen related category"
1230 }
1231 }
1232 }
1233 field {
1234 type: LITERAL
1235 literal {
1236 type: V_SINT
1237 v_signed_int: 4
1238 }
1239 }
1240 field {
1241 type: LITERAL
1242 literal {
1243 type: V_STRING
1244 v_string {
1245 value: "NotADouble"
1246 }
1247 }
1248 }
1249 field {
1250 type: LITERAL
1251 literal {
1252 type: V_BOOL
1253 v_bool: false
1254 }
1255 }
1256 }
1257 }
1258 -->expecterror 1146
1259 -->recvresult
1260
1261 ## string value different from true or false in V_BOOL data type
1262 Mysqlx.Crud.Insert {
1263 collection {
1264 name: "categories"
1265 schema: "mysqlxplugin"
1266 }
1267 data_model: TABLE
1268 projection {
1269 name: "CategoryID"
1270 }
1271 projection {
1272 name: "CategoryName"
1273 }
1274 projection {
1275 name: "CategoryDescription"
1276 }
1277 projection {
1278 name: "CategoryIMEI"
1279 }
1280 projection {
1281 name: "CategoryDecimal"
1282 }
1283 projection {
1284 name: "CategoryBool"
1285 }
1286 row {
1287 field {
1288 type: LITERAL
1289 literal {
1290 type: V_SINT
1291 v_signed_int: 16
1292 }
1293 }
1294 field {
1295 type: LITERAL
1296 literal {
1297 type: V_STRING
1298 v_string {
1299 value: "Kitchen"
1300 }
1301 }
1302 }
1303 field {
1304 type: LITERAL
1305 literal {
1306 type: V_STRING
1307 v_string {
1308 value: "Kitchen related category"
1309 }
1310 }
1311 }
1312 field {
1313 type: LITERAL
1314 literal {
1315 type: V_SINT
1316 v_signed_int: 4
1317 }
1318 }
1319 field {
1320 type: LITERAL
1321 literal {
1322 type: V_DOUBLE
1323 v_double: 235.65
1324 }
1325 }
1326 field {
1327 type: LITERAL
1328 literal {
1329 type: V_STRING
1330 v_string {
1331 value: "NotAboolValue"
1332 }
1333 }
1334 }
1335 }
1336 }
1337 -->expecterror 1406
1338 -->recvresult
1339
1340 -->sql
1341 select * from categories order by CategoryID asc;
1342 -->endsql
1343 EOF
1344
1345 --exec $MYSQLXTEST -u insertcruduser --password='insertcruduser' --file=$MYSQL_TMP_DIR/mysqlx-insert_Negative_CRUD.xpl 2>&1
1346 --remove_file $MYSQL_TMP_DIR/mysqlx-insert_Negative_CRUD.xpl
1347
1348 ## ******************************************
1349 ## 3. Collection test based starts here |
1350 ## |
1351 ## ******************************************
1352
1353 --echo .
1354 --echo #---------- START COLLECTION TESTING----------#
1355 --echo .
1356
1357 --write_file $MYSQL_TMP_DIR/mysqlx-insert_collection_CRUD.xpl
1358 -->sql
1359 CREATE SCHEMA mysqlxplugin_coll;
1360 USE mysqlxplugin_coll;
1361 -->endsql
1362
1363 -->stmtadmin create_collection {"schema":"mysqlxplugin_coll","name":"main_collection"}
1364 -->recvresult
1365
1366 -->echo Insert a row into a Document column
1367 Mysqlx.Crud.Insert {
1368 collection {
1369 name: "main_collection"
1370 schema: "mysqlxplugin_coll"
1371 }
1372 data_model: DOCUMENT
1373 row {
1374 field {
1375 type: LITERAL
1376 literal {
1377 type: V_STRING
1378 v_string {
1379 value: "\n{\n \"_id\": \"1\",\n \"name\": \"Omar Port\", \"id\": \"1\"\n}"
1380 }
1381 }
1382 }
1383 }
1384 }
1385 #-- Mysqlx.Sql.StmtExecuteOk
1386 -->recvresult
1387
1388 -->sql
1389 select doc from main_collection;
1390 -->endsql
1391
1392 -->echo Insert multiple rows into a Document column
1393 Mysqlx.Crud.Insert {
1394 collection {
1395 name: "main_collection"
1396 schema: "mysqlxplugin_coll"
1397 }
1398 data_model: DOCUMENT
1399 row {
1400 field {
1401 type: LITERAL
1402 literal {
1403 type: V_STRING
1404 v_string {
1405 value: "\n{\n \"_id\": \"2\",\n \"name\": \"Omar Bras\", \"id\": \"2\"\n}"
1406 }
1407 }
1408 }
1409 }
1410 row {
1411 field {
1412 type: LITERAL
1413 literal {
1414 type: V_STRING
1415 v_string {
1416 value: "\n{\n \"_id\": \"3\",\n \"name\": \"Omar Mex\", \"id\": \"3\"\n}"
1417 }
1418 }
1419 }
1420 }
1421 }
1422 #-- Mysqlx.Sql.StmtExecuteOk
1423 -->recvresult
1424
1425 -->sql
1426 select doc from main_collection;
1427 -->endsql
1428
1429 -->echo Insert malformed JSON into Document column
1430 Mysqlx.Crud.Insert {
1431 collection {
1432 name: "main_collection"
1433 schema: "mysqlxplugin_coll"
1434 }
1435 data_model: DOCUMENT
1436 row {
1437 field {
1438 type: LITERAL
1439 literal {
1440 type: V_STRING
1441 v_string {
1442 value: "\n{\n \"_id\": \"1\",\n \"from\": {\n \"name\": \"Omar Alf\", \"id\": \"10\"\n }\n {\n \"name\": \"Omar Mend\", \"id\": \"11\"\n }\n}"
1443 }
1444 }
1445 }
1446 }
1447 }
1448 -->expecterror 3140
1449 -->recvresult
1450
1451 ## Cleanup
1452 -->echo ================================================================================
1453 -->echo CLEAN UP
1454 -->echo ================================================================================
1455 -->sql
1456 SELECT doc FROM main_collection;
1457 DROP SCHEMA if EXISTS mysqlxplugin;
1458 DROP SCHEMA if EXISTS mysqlxplugin_coll;
1459 DROP USER insertcruduser@localhost;
1460 -->endsql
1461 EOF
1462
1463 --exec $MYSQLXTEST -u insertcruduser --password='insertcruduser' --file=$MYSQL_TMP_DIR/mysqlx-insert_collection_CRUD.xpl 2>&1
1464
1465 ## Cleanup
1466 --source ../include/xplugin_cleanup.inc