Debian Codegraph @

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

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