Debian Codegraph @

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

raw
1 ## Varius tests related to manipulation on json members
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6
7 ## Test data
8 CREATE SCHEMA xtest;
9 USE xtest;
10 CREATE TABLE xtable (doc JSON);
11 INSERT INTO xtable (doc) VALUES ('{"a":10, "b":20, "c":"30", "d":"40", "e":"foo", "f":"bar", "g":"2015.10.05"}');
12
13 ## Test starts here
14 --write_file $MYSQL_TMP_DIR/crud_sundries.xpl
15 -->quiet
16 ##-- function concat
17 Mysqlx.Crud.Find {
18 collection { name: "xtable" schema: "xtest" }
19 data_model: TABLE
20 projection {
21 alias: "string"
22 source {
23 type: FUNC_CALL function_call {
24 name { name: "concat" }
25 param {
26 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
27 }
28 param {
29 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
30 }
31 }
32 }
33 }
34 projection {
35 alias: "number"
36 source {
37 type: FUNC_CALL function_call {
38 name { name: "concat" }
39 param {
40 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
41 }
42 param {
43 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" } }
44 }
45 }
46 }
47 }
48 }
49 -->recvresult
50
51 ##-- function concat-in-concat
52 Mysqlx.Crud.Find {
53 collection { name: "xtable" schema: "xtest" }
54 data_model: TABLE
55 projection {
56 alias: "string"
57 source {
58 type: FUNC_CALL function_call {
59 name { name: "concat" }
60 param {
61 type: FUNC_CALL function_call {
62 name { name: "concat" }
63 param {
64 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
65 }
66 param {
67 type: LITERAL literal { type: V_OCTETS v_octets {value:"-in-"} }
68 }
69 }
70 }
71 param {
72 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
73 }
74 }
75 }
76 }
77 projection {
78 alias: "number"
79 source {
80 type: FUNC_CALL function_call {
81 name { name: "concat" }
82 param {
83 type: FUNC_CALL function_call {
84 name { name: "concat" }
85 param {
86 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
87 }
88 param {
89 type: LITERAL literal { type: V_OCTETS v_octets {value:"-in-"} }
90 }
91 }
92 }
93 param {
94 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" } }
95 }
96 }
97 }
98 }
99 }
100 -->recvresult
101
102 ##-- function sqrt
103 Mysqlx.Crud.Find {
104 collection { name: "xtable" schema: "xtest" }
105 data_model: TABLE
106 projection {
107 alias: "string"
108 source {
109 type: FUNC_CALL function_call {
110 name { name: "sqrt" }
111 param {
112 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "c" } }
113 }
114 }
115 }
116 }
117 projection {
118 alias: "number"
119 source {
120 type: FUNC_CALL function_call {
121 name { name: "sqrt" }
122 param {
123 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
124 }
125 }
126 }
127 }
128 }
129 -->recvresult
130
131 ##-- function adddate, operator date_add
132 Mysqlx.Crud.Find {
133 collection { name: "xtable" schema: "xtest" }
134 data_model: TABLE
135 projection {
136 alias: "fun"
137 source {
138 type: FUNC_CALL function_call {
139 name { name: "adddate" }
140 param {
141 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "g" } }
142 }
143 param {
144 type: LITERAL literal {type: V_SINT v_signed_int: 3}
145 }
146 }
147 }
148 }
149 projection {
150 alias: "oper"
151 source {
152 type: OPERATOR operator {
153 name: "date_add"
154 param {
155 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "g" } }
156 }
157 param {
158 type: LITERAL literal {type: V_SINT v_signed_int: 3}
159 }
160 param {
161 type: LITERAL literal { type: V_OCTETS v_octets {value:"DAY"} }
162 }
163 }
164 }
165 }
166 }
167 -->recvresult
168
169 ##-- function subdate, operator date_sub
170 Mysqlx.Crud.Find {
171 collection { name: "xtable" schema: "xtest" }
172 data_model: TABLE
173 projection {
174 alias: "fun"
175 source {
176 type: FUNC_CALL function_call {
177 name { name: "subdate" }
178 param {
179 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "g" } }
180 }
181 param {
182 type: LITERAL literal {type: V_SINT v_signed_int: 3}
183 }
184 }
185 }
186 }
187 projection {
188 alias: "oper"
189 source {
190 type: OPERATOR operator {
191 name: "date_sub"
192 param {
193 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "g" } }
194 }
195 param {
196 type: LITERAL literal {type: V_SINT v_signed_int: 3}
197 }
198 param {
199 type: LITERAL literal { type: V_OCTETS v_octets {value:"DAY"} }
200 }
201 }
202 }
203 }
204 }
205 -->recvresult
206
207 ##-- operator cast to number
208 Mysqlx.Crud.Find {
209 collection { name: "xtable" schema: "xtest" }
210 data_model: TABLE
211 projection {
212 alias: "string"
213 source {
214 type: OPERATOR operator {
215 name: "cast"
216 param {
217 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "d" } }
218 }
219 param {
220 type: LITERAL literal { type: V_OCTETS v_octets {value:"DECIMAL"} }
221 }
222 }
223 }
224 }
225 projection {
226 alias: "number"
227 source {
228 type: OPERATOR operator {
229 name: "cast"
230 param {
231 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
232 }
233 param {
234 type: LITERAL literal { type: V_OCTETS v_octets {value:"DECIMAL"} }
235 }
236 }
237 }
238 }
239 }
240 -->recvresult
241
242 ##-- operator cast to string
243 Mysqlx.Crud.Find {
244 collection { name: "xtable" schema: "xtest" }
245 data_model: TABLE
246 projection {
247 alias: "string"
248 source {
249 type: OPERATOR operator {
250 name: "cast"
251 param {
252 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "d" } }
253 }
254 param {
255 type: LITERAL literal { type: V_OCTETS v_octets {value:"CHAR"} }
256 }
257 }
258 }
259 }
260 projection {
261 alias: "number"
262 source {
263 type: OPERATOR operator {
264 name: "cast"
265 param {
266 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
267 }
268 param {
269 type: LITERAL literal { type: V_OCTETS v_octets {value:"CHAR"} }
270 }
271 }
272 }
273 }
274 }
275 -->recvresult
276
277 ##-- operator in (check member)
278 Mysqlx.Crud.Find {
279 collection { name: "xtable" schema: "xtest" }
280 data_model: TABLE
281 projection {
282 alias: "string"
283 source {
284 type: OPERATOR operator {
285 name: "in"
286 param {
287 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
288 }
289 param {
290 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
291 }
292 param {
293 type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }
294 }
295 }
296 }
297 }
298 projection {
299 alias: "number"
300 source {
301 type: OPERATOR operator {
302 name: "in"
303 param {
304 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
305 }
306 param {
307 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" } }
308 }
309 param {
310 type: LITERAL literal { type: V_SINT v_signed_int: 10}
311 }
312 }
313 }
314 }
315 }
316 -->recvresult
317
318 ##-- operator in (check literal)
319 Mysqlx.Crud.Find {
320 collection { name: "xtable" schema: "xtest" }
321 data_model: TABLE
322 projection {
323 alias: "string"
324 source {
325 type: OPERATOR operator {
326 name: "in"
327 param {
328 type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }
329 }
330 param {
331 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
332 }
333 param {
334 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
335 }
336 }
337 }
338 }
339 projection {
340 alias: "number"
341 source {
342 type: OPERATOR operator {
343 name: "in"
344 param {
345 type: LITERAL literal { type: V_SINT v_signed_int: 10}
346 }
347 param {
348 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
349 }
350 param {
351 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" } }
352 }
353 }
354 }
355 }
356 }
357 -->recvresult
358
359 ##-- operator between (check member)
360 Mysqlx.Crud.Find {
361 collection { name: "xtable" schema: "xtest" }
362 data_model: TABLE
363 projection {
364 alias: "string"
365 source {
366 type: OPERATOR operator {
367 name: "between"
368 param {
369 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
370 }
371 param {
372 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
373 }
374 param {
375 type: LITERAL literal { type: V_OCTETS v_octets {value:"zoo"} }
376 }
377 }
378 }
379 }
380 projection {
381 alias: "number"
382 source {
383 type: OPERATOR operator {
384 name: "between"
385 param {
386 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" } }
387 }
388 param {
389 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
390 }
391 param {
392 type: LITERAL literal { type: V_SINT v_signed_int: 100}
393 }
394 }
395 }
396 }
397 }
398 -->recvresult
399
400 ##-- operator between (check literal)
401 Mysqlx.Crud.Find {
402 collection { name: "xtable" schema: "xtest" }
403 data_model: TABLE
404 projection {
405 alias: "string"
406 source {
407 type: OPERATOR operator {
408 name: "between"
409 param {
410 type: LITERAL literal { type: V_OCTETS v_octets {value:"cent"} }
411 }
412 param {
413 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
414 }
415 param {
416 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
417 }
418 }
419 }
420 }
421 projection {
422 alias: "number"
423 source {
424 type: OPERATOR operator {
425 name: "between"
426 param {
427 type: LITERAL literal { type: V_SINT v_signed_int: 15}
428 }
429 param {
430 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
431 }
432 param {
433 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" } }
434 }
435 }
436 }
437 }
438 }
439 -->recvresult
440
441 ##-- operator like, regex (check literal)
442 Mysqlx.Crud.Find {
443 collection { name: "xtable" schema: "xtest" }
444 data_model: TABLE
445 projection {
446 alias: "like"
447 source {
448 type: OPERATOR operator {
449 name: "like"
450 param {
451 type: LITERAL literal { type: V_OCTETS v_octets {value:"bar"} }
452 }
453 param {
454 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
455 }
456 }
457 }
458 }
459 projection {
460 alias: "regex"
461 source {
462 type: OPERATOR operator {
463 name: "regexp"
464 param {
465 type: LITERAL literal { type: V_OCTETS v_octets {value:"2015A10B05"} }
466 }
467 param {
468 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "g" } }
469 }
470 }
471 }
472 }
473 }
474 -->recvresult
475
476 ##-- operator like, regex (check member)
477 Mysqlx.Crud.Find {
478 collection { name: "xtable" schema: "xtest" }
479 data_model: TABLE
480 projection {
481 alias: "like"
482 source {
483 type: OPERATOR operator {
484 name: "like"
485 param {
486 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" } }
487 }
488 param {
489 type: LITERAL literal { type: V_OCTETS v_octets {value:"BAR"} }
490 }
491 }
492 }
493 }
494 projection {
495 alias: "regex"
496 source {
497 type: OPERATOR operator {
498 name: "regexp"
499 param {
500 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
501 }
502 param {
503 type: LITERAL literal { type: V_OCTETS v_octets {value:"^f.*"} }
504 }
505 }
506 }
507 }
508 }
509 -->recvresult
510
511 ##-- updating with function result
512 Mysqlx.Crud.Update {
513 collection { name: "xtable" schema: "xtest"}
514 data_model: TABLE
515 operation {
516 source { name: 'doc' document_path {type: MEMBER value: 'extra'}
517 }
518 operation: ITEM_SET
519 value: {
520 type: FUNC_CALL function_call {
521 name { name: "upper" }
522 param {
523 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
524 }
525 }
526 }
527 }
528 operation {
529 source { name: 'doc' document_path {type: MEMBER value: 'sqrt'}
530 }
531 operation: ITEM_SET
532 value: {
533 type: FUNC_CALL function_call {
534 name { name: "sqrt" }
535 param {
536 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
537 }
538 }
539 }
540 }
541 }
542 -->recvresult
543 -->sql
544 SELECT * FROM xtest.xtable;
545 -->endsql
546
547
548 ##-- find number in array of literals
549 Mysqlx.Crud.Find {
550 collection { name: "xtable" schema: "xtest" }
551 data_model: TABLE
552 projection {
553 alias: "result-in"
554 source {
555 type: OPERATOR operator {
556 name: "in"
557 param {
558 type: LITERAL literal { type: V_SINT v_signed_int: 2 }
559 }
560 param {
561 type: ARRAY array {
562 value {type: LITERAL literal { type: V_SINT v_signed_int: 1 } }
563 value {type: LITERAL literal { type: V_SINT v_signed_int: 2 } }
564 value {type: LITERAL literal { type: V_SINT v_signed_int: 3 } }
565 }
566 }
567 }
568 }
569 }
570 projection {
571 alias: "result-not_in"
572 source {
573 type: OPERATOR operator {
574 name: "not_in"
575 param {
576 type: LITERAL literal { type: V_SINT v_signed_int: 0 }
577 }
578 param {
579 type: ARRAY array {
580 value {type: LITERAL literal { type: V_SINT v_signed_int: 1 } }
581 value {type: LITERAL literal { type: V_SINT v_signed_int: 2 } }
582 value {type: LITERAL literal { type: V_SINT v_signed_int: 3 } }
583 }
584 }
585 }
586 }
587 }
588 }
589 -->recvresult
590
591
592 ##-- find string in array of literals
593 Mysqlx.Crud.Find {
594 collection { name: "xtable" schema: "xtest" }
595 data_model: TABLE
596 projection {
597 alias: "result-in"
598 source {
599 type: OPERATOR operator {
600 name: "in"
601 param {
602 type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }
603 }
604 param {
605 type: ARRAY array {
606 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }}
607 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"bar"} }}
608 }
609 }
610 }
611 }
612 }
613 projection {
614 alias: "result-not_in"
615 source {
616 type: OPERATOR operator {
617 name: "not_in"
618 param {
619 type: LITERAL literal { type: V_OCTETS v_octets {value:"far"} }
620 }
621 param {
622 type: ARRAY array {
623 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }}
624 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"bar"} }}
625 }
626 }
627 }
628 }
629 }
630 }
631 -->recvresult
632
633
634 ##-- find member (number) in array of literals
635 Mysqlx.Crud.Find {
636 collection { name: "xtable" schema: "xtest" }
637 data_model: TABLE
638 projection {
639 alias: "result-in"
640 source {
641 type: OPERATOR operator {
642 name: "in"
643 param {
644 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" } }
645 }
646 param {
647 type: ARRAY array {
648 value {type: LITERAL literal { type: V_SINT v_signed_int: 10 } }
649 value {type: LITERAL literal { type: V_SINT v_signed_int: 20 } }
650 value {type: LITERAL literal { type: V_SINT v_signed_int: 30 } }
651 }
652 }
653 }
654 }
655 }
656 projection {
657 alias: "result-not_in"
658 source {
659 type: OPERATOR operator {
660 name: "not_in"
661 param {
662 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "d" } }
663 }
664 param {
665 type: ARRAY array {
666 value {type: LITERAL literal { type: V_SINT v_signed_int: 10 } }
667 value {type: LITERAL literal { type: V_SINT v_signed_int: 20 } }
668 value {type: LITERAL literal { type: V_SINT v_signed_int: 30 } }
669 }
670 }
671 }
672 }
673 }
674 }
675 -->recvresult
676
677
678 ##-- find member (string) in array of literals
679 Mysqlx.Crud.Find {
680 collection { name: "xtable" schema: "xtest" }
681 data_model: TABLE
682 projection {
683 alias: "result-in"
684 source {
685 type: OPERATOR operator {
686 name: "in"
687 param {
688 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
689 }
690 param {
691 type: ARRAY array {
692 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }}
693 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"bar"} }}
694 }
695 }
696 }
697 }
698 }
699 projection {
700 alias: "result-not_in"
701 source {
702 type: OPERATOR operator {
703 name: "not_in"
704 param {
705 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "d" } }
706 }
707 param {
708 type: ARRAY array {
709 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }}
710 value {type: LITERAL literal { type: V_OCTETS v_octets {value:"bar"} }}
711 }
712 }
713 }
714 }
715 }
716 }
717 -->recvresult
718
719
720 ##-- find number in array of members
721 Mysqlx.Crud.Find {
722 collection { name: "xtable" schema: "xtest" }
723 data_model: TABLE
724 projection {
725 alias: "result-in"
726 source {
727 type: OPERATOR operator {
728 name: "in"
729 param {
730 type: LITERAL literal { type: V_SINT v_signed_int: 20 }
731 }
732 param {
733 type: ARRAY array {
734 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" }}}
735 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" }}}
736 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "c" }}}
737 }
738 }
739 }
740 }
741 }
742 projection {
743 alias: "result-not_in"
744 source {
745 type: OPERATOR operator {
746 name: "not_in"
747 param {
748 type: LITERAL literal { type: V_SINT v_signed_int: 40 }
749 }
750 param {
751 type: ARRAY array {
752 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" }}}
753 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "b" }}}
754 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "c" }}}
755 }
756 }
757 }
758 }
759 }
760 }
761 -->recvresult
762
763
764 ##-- find string in array of members
765 Mysqlx.Crud.Find {
766 collection { name: "xtable" schema: "xtest" }
767 data_model: TABLE
768 projection {
769 alias: "result-in"
770 source {
771 type: OPERATOR operator {
772 name: "in"
773 param {
774 type: LITERAL literal { type: V_OCTETS v_octets {value:"foo"} }
775 }
776 param {
777 type: ARRAY array {
778 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" }}}
779 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" }}}
780 }
781 }
782 }
783 }
784 }
785 projection {
786 alias: "result-not_in"
787 source {
788 type: OPERATOR operator {
789 name: "not_in"
790 param {
791 type: LITERAL literal { type: V_OCTETS v_octets {value:"boo"} }
792 }
793 param {
794 type: ARRAY array {
795 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" }}}
796 value {type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "f" }}}
797 }
798 }
799 }
800 }
801 }
802 }
803 -->recvresult
804
805 ##-- function json_type
806 Mysqlx.Crud.Find {
807 collection { name: "xtable" schema: "xtest" }
808 data_model: TABLE
809 projection {
810 alias: "string"
811 source {
812 type: FUNC_CALL function_call {
813 name { name: "json_type" }
814 param {
815 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "e" } }
816 }
817 }
818 }
819 }
820 projection {
821 alias: "number"
822 source {
823 type: FUNC_CALL function_call {
824 name { name: "json_type" }
825 param {
826 type: IDENT identifier { name: "doc" document_path { type: MEMBER value: "a" } }
827 }
828 }
829 }
830 }
831 projection {
832 alias: "root"
833 source {
834 type: FUNC_CALL function_call {
835 name { name: "json_type" }
836 param {
837 type: IDENT identifier { name: "doc" document_path { type: MEMBER } }
838 }
839 }
840 }
841 }
842 }
843 -->recvresult
844
845 ##-- function json_keys
846 Mysqlx.Crud.Find {
847 collection { name: "xtable" schema: "xtest" }
848 data_model: TABLE
849 projection {
850 alias: "keys"
851 source {
852 type: FUNC_CALL function_call {
853 name { name: "json_keys" }
854 param {
855 type: IDENT identifier { name: "doc" document_path { type: MEMBER } }
856 }
857 }
858 }
859 }
860 }
861 -->recvresult
862
863 ##-- Empty "identifier" equivalent of doc->$ (document data model)
864 Mysqlx.Crud.Find {
865 collection { name: "xtable" schema: "xtest" }
866 data_model: DOCUMENT
867 projection {
868 alias: "empty"
869 source {
870 type: IDENT identifier { }
871 }
872 }
873 }
874 -->recvresult
875
876 ##-- Empty "identifier" (table data model)
877 Mysqlx.Crud.Find {
878 collection { name: "xtable" schema: "xtest" }
879 data_model: TABLE
880 projection {
881 alias: "empty"
882 source {
883 type: IDENT identifier { }
884 }
885 }
886 }
887 -->expecterror ER_X_EXPR_MISSING_ARG
888 -->recvresult
889
890 EOF
891
892 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_sundries.xpl 2>&1
893
894 ## Cleanup
895 --source ../include/xplugin_cleanup.inc