Debian Codegraph @

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

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