1============================================= 2CRUD TABLE CRITERIA ARGS SCENARIOS 3============================================= 4 5================================================================================ 6PREAMBLE 7================================================================================ 8================================================================================ 9TEST START 10================================================================================ 11RUN drop schema if exists xtest 12 130 rows affected 14RUN create schema xtest 15 161 rows affected 17RUN use xtest 18 190 rows affected 20RUN create table mytable (id int primary key, name varchar(40), price decimal(5,2), info json) 21 220 rows affected 23RUN insert into mytable values (1, 'banana', 1.20, '{"color": "yellow"}') 24 251 rows affected 26RUN insert into mytable values (2, 'apple', 0.25, '{"color":"red"}') 27 281 rows affected 29RUN insert into mytable values (3, 'tomato', 1.80, '{"color":"red"}') 30 311 rows affected 32RUN insert into mytable values (4, 'mango', 3.14, '{"color":"green"}') 33 341 rows affected 35RUN insert into mytable values (5, 'orange', 0.90, '{"color":"orange"}') 36 371 rows affected 38RUN insert into mytable values (6, 'berry', null, '{"color":"orange"}') 39 401 rows affected 41RUN SELECT * FROM xtest.mytable ORDER BY price 42id name price info 436 berry null {"color": "orange"} 442 apple 0.25 {"color": "red"} 455 orange 0.90 {"color": "orange"} 461 banana 1.20 {"color": "yellow"} 473 tomato 1.80 {"color": "red"} 484 mango 3.14 {"color": "green"} 490 rows affected 50Find with == Operator and placeholder 51send Mysqlx.Crud.Find { 52 collection { 53 name: "mytable" 54 schema: "xtest" 55 } 56 data_model: TABLE 57 criteria { 58 type: OPERATOR 59 operator { 60 name: "==" 61 param { 62 type: IDENT 63 identifier { 64 name: "name" 65 } 66 } 67 param { 68 type: PLACEHOLDER 69 position: 0 70 } 71 } 72 } 73 args { 74 type: V_STRING 75 v_string { 76 value: "tomato" 77 } 78 } 79} 80 81id name price info 823 tomato 1.80 {"color": "red"} 83command ok 84Find with != Operator. placeholder and projection 85send Mysqlx.Crud.Find { 86 collection { 87 name: "mytable" 88 schema: "xtest" 89 } 90 data_model: TABLE 91 projection { 92 source { 93 type: IDENT 94 identifier { 95 name: "name" 96 } 97 } 98 } 99 criteria { 100 type: OPERATOR 101 operator { 102 name: "==" 103 param { 104 type: IDENT 105 identifier { 106 name: "name" 107 } 108 } 109 param { 110 type: PLACEHOLDER 111 position: 0 112 } 113 } 114 } 115 args { 116 type: V_STRING 117 v_string { 118 value: "tomato" 119 } 120 } 121} 122 123name 124tomato 125command ok 126Find with != Operator and placeholder 127send Mysqlx.Crud.Find { 128 collection { 129 name: "mytable" 130 schema: "xtest" 131 } 132 data_model: TABLE 133 criteria { 134 type: OPERATOR 135 operator { 136 name: "!=" 137 param { 138 type: IDENT 139 identifier { 140 name: "name" 141 } 142 } 143 param { 144 type: PLACEHOLDER 145 position: 0 146 } 147 } 148 } 149 args { 150 type: V_STRING 151 v_string { 152 value: "tomato" 153 } 154 } 155} 156 157id name price info 1581 banana 1.20 {"color": "yellow"} 1592 apple 0.25 {"color": "red"} 1604 mango 3.14 {"color": "green"} 1615 orange 0.90 {"color": "orange"} 1626 berry null {"color": "orange"} 163command ok 164Find with > Operator and placeholder 165send Mysqlx.Crud.Find { 166 collection { 167 name: "mytable" 168 schema: "xtest" 169 } 170 data_model: TABLE 171 criteria { 172 type: OPERATOR 173 operator { 174 name: ">" 175 param { 176 type: IDENT 177 identifier { 178 name: "price" 179 } 180 } 181 param { 182 type: PLACEHOLDER 183 position: 0 184 } 185 } 186 } 187 args { 188 type: V_DOUBLE 189 v_double: 1.8 190 } 191} 192 193id name price info 1944 mango 3.14 {"color": "green"} 195command ok 196Find with != Operator, placeholder and Order desc 197send Mysqlx.Crud.Find { 198 collection { 199 name: "mytable" 200 schema: "xtest" 201 } 202 data_model: TABLE 203 criteria { 204 type: OPERATOR 205 operator { 206 name: "!=" 207 param { 208 type: IDENT 209 identifier { 210 name: "price" 211 } 212 } 213 param { 214 type: PLACEHOLDER 215 position: 0 216 } 217 } 218 } 219 order { 220 expr { 221 type: IDENT 222 identifier { 223 name: "name" 224 } 225 } 226 direction: DESC 227 } 228 args { 229 type: V_DOUBLE 230 v_double: 0 231 } 232} 233 234id name price info 2353 tomato 1.80 {"color": "red"} 2365 orange 0.90 {"color": "orange"} 2374 mango 3.14 {"color": "green"} 2381 banana 1.20 {"color": "yellow"} 2392 apple 0.25 {"color": "red"} 240command ok 241Find with != Operator, placeholder and Order asc 242send Mysqlx.Crud.Find { 243 collection { 244 name: "mytable" 245 schema: "xtest" 246 } 247 data_model: TABLE 248 criteria { 249 type: OPERATOR 250 operator { 251 name: "!=" 252 param { 253 type: IDENT 254 identifier { 255 name: "price" 256 } 257 } 258 param { 259 type: PLACEHOLDER 260 position: 0 261 } 262 } 263 } 264 order { 265 expr { 266 type: IDENT 267 identifier { 268 name: "name" 269 } 270 } 271 direction: ASC 272 } 273 args { 274 type: V_DOUBLE 275 v_double: 0 276 } 277} 278 279id name price info 2802 apple 0.25 {"color": "red"} 2811 banana 1.20 {"color": "yellow"} 2824 mango 3.14 {"color": "green"} 2835 orange 0.90 {"color": "orange"} 2843 tomato 1.80 {"color": "red"} 285command ok 286Find with < Operator and placeholder 287send Mysqlx.Crud.Find { 288 collection { 289 name: "mytable" 290 schema: "xtest" 291 } 292 data_model: TABLE 293 criteria { 294 type: OPERATOR 295 operator { 296 name: "<" 297 param { 298 type: IDENT 299 identifier { 300 name: "price" 301 } 302 } 303 param { 304 type: PLACEHOLDER 305 position: 0 306 } 307 } 308 } 309 args { 310 type: V_DOUBLE 311 v_double: 0.25 312 } 313} 314 315id name price info 316command ok 317Find with >= Operator and placeholder 318send Mysqlx.Crud.Find { 319 collection { 320 name: "mytable" 321 schema: "xtest" 322 } 323 data_model: TABLE 324 criteria { 325 type: OPERATOR 326 operator { 327 name: ">=" 328 param { 329 type: IDENT 330 identifier { 331 name: "price" 332 } 333 } 334 param { 335 type: PLACEHOLDER 336 position: 0 337 } 338 } 339 } 340 args { 341 type: V_DOUBLE 342 v_double: 1.8 343 } 344} 345 346id name price info 3473 tomato 1.80 {"color": "red"} 3484 mango 3.14 {"color": "green"} 349command ok 350Find with <= Operator and placeholder 351send Mysqlx.Crud.Find { 352 collection { 353 name: "mytable" 354 schema: "xtest" 355 } 356 data_model: TABLE 357 criteria { 358 type: OPERATOR 359 operator { 360 name: "<=" 361 param { 362 type: IDENT 363 identifier { 364 name: "price" 365 } 366 } 367 param { 368 type: PLACEHOLDER 369 position: 0 370 } 371 } 372 } 373 args { 374 type: V_DOUBLE 375 v_double: 0.25 376 } 377} 378 379id name price info 3802 apple 0.25 {"color": "red"} 381command ok 382Find with in Operator and placeholder 383send Mysqlx.Crud.Find { 384 collection { 385 name: "mytable" 386 schema: "xtest" 387 } 388 data_model: TABLE 389 criteria { 390 type: OPERATOR 391 operator { 392 name: "in" 393 param { 394 type: IDENT 395 identifier { 396 name: "price" 397 } 398 } 399 param { 400 type: PLACEHOLDER 401 position: 0 402 } 403 } 404 } 405 args { 406 type: V_DOUBLE 407 v_double: 1.8 408 } 409} 410 411id name price info 4123 tomato 1.80 {"color": "red"} 413command ok 414Find with not in Operator and placeholder 415send Mysqlx.Crud.Find { 416 collection { 417 name: "mytable" 418 schema: "xtest" 419 } 420 data_model: TABLE 421 criteria { 422 type: OPERATOR 423 operator { 424 name: "not_in" 425 param { 426 type: IDENT 427 identifier { 428 name: "price" 429 } 430 } 431 param { 432 type: PLACEHOLDER 433 position: 0 434 } 435 } 436 } 437 args { 438 type: V_DOUBLE 439 v_double: 1.8 440 } 441} 442 443id name price info 4441 banana 1.20 {"color": "yellow"} 4452 apple 0.25 {"color": "red"} 4464 mango 3.14 {"color": "green"} 4475 orange 0.90 {"color": "orange"} 448command ok 449Find with == Operator, V_NULL and placeholder 450send Mysqlx.Crud.Find { 451 collection { 452 name: "mytable" 453 schema: "xtest" 454 } 455 data_model: TABLE 456 criteria { 457 type: OPERATOR 458 operator { 459 name: "is" 460 param { 461 type: IDENT 462 identifier { 463 name: "price" 464 } 465 } 466 param { 467 type: PLACEHOLDER 468 position: 0 469 } 470 } 471 } 472 args { 473 type: V_NULL 474 } 475} 476 477id name price info 4786 berry null {"color": "orange"} 479command ok 480Update with == operator and placeholder 481send Mysqlx.Crud.Update { 482 collection { 483 name: "mytable" 484 schema: "xtest" 485 } 486 data_model: TABLE 487 criteria { 488 type: OPERATOR 489 operator { 490 name: "==" 491 param { 492 type: IDENT 493 identifier { 494 name: "price" 495 } 496 } 497 param { 498 type: PLACEHOLDER 499 position: 0 500 } 501 } 502 } 503 operation { 504 source { 505 name: "price" 506 } 507 operation: SET 508 value { 509 type: LITERAL 510 literal { 511 type: V_DOUBLE 512 v_double: 18 513 } 514 } 515 } 516 args { 517 type: V_DOUBLE 518 v_double: 1.8 519 } 520} 521 522 5231 rows affected 524Rows matched: 1 Changed: 1 Warnings: 0 525RUN SELECT * FROM xtest.mytable ORDER BY price 526id name price info 5276 berry null {"color": "orange"} 5282 apple 0.25 {"color": "red"} 5295 orange 0.90 {"color": "orange"} 5301 banana 1.20 {"color": "yellow"} 5314 mango 3.14 {"color": "green"} 5323 tomato 18.00 {"color": "red"} 5330 rows affected 534Update with > operator and placeholder 535send Mysqlx.Crud.Update { 536 collection { 537 name: "mytable" 538 schema: "xtest" 539 } 540 data_model: TABLE 541 criteria { 542 type: OPERATOR 543 operator { 544 name: ">" 545 param { 546 type: IDENT 547 identifier { 548 name: "price" 549 } 550 } 551 param { 552 type: PLACEHOLDER 553 position: 0 554 } 555 } 556 } 557 operation { 558 source { 559 name: "price" 560 } 561 operation: SET 562 value { 563 type: LITERAL 564 literal { 565 type: V_DOUBLE 566 v_double: 10 567 } 568 } 569 } 570 args { 571 type: V_DOUBLE 572 v_double: 1 573 } 574} 575 576 5773 rows affected 578Rows matched: 3 Changed: 3 Warnings: 0 579RUN SELECT * FROM xtest.mytable ORDER BY price 580id name price info 5816 berry null {"color": "orange"} 5822 apple 0.25 {"color": "red"} 5835 orange 0.90 {"color": "orange"} 5841 banana 10.00 {"color": "yellow"} 5853 tomato 10.00 {"color": "red"} 5864 mango 10.00 {"color": "green"} 5870 rows affected 588Update with >= operator and placeholder 589send Mysqlx.Crud.Update { 590 collection { 591 name: "mytable" 592 schema: "xtest" 593 } 594 data_model: TABLE 595 criteria { 596 type: OPERATOR 597 operator { 598 name: ">=" 599 param { 600 type: IDENT 601 identifier { 602 name: "price" 603 } 604 } 605 param { 606 type: PLACEHOLDER 607 position: 0 608 } 609 } 610 } 611 operation { 612 source { 613 name: "price" 614 } 615 operation: SET 616 value { 617 type: LITERAL 618 literal { 619 type: V_DOUBLE 620 v_double: 895.63 621 } 622 } 623 } 624 args { 625 type: V_DOUBLE 626 v_double: 1.8 627 } 628} 629 630 6313 rows affected 632Rows matched: 3 Changed: 3 Warnings: 0 633RUN SELECT * FROM xtest.mytable ORDER BY price 634id name price info 6356 berry null {"color": "orange"} 6362 apple 0.25 {"color": "red"} 6375 orange 0.90 {"color": "orange"} 6381 banana 895.63 {"color": "yellow"} 6393 tomato 895.63 {"color": "red"} 6404 mango 895.63 {"color": "green"} 6410 rows affected 642Update with <= operator and placeholder 643send Mysqlx.Crud.Update { 644 collection { 645 name: "mytable" 646 schema: "xtest" 647 } 648 data_model: TABLE 649 criteria { 650 type: OPERATOR 651 operator { 652 name: "<=" 653 param { 654 type: IDENT 655 identifier { 656 name: "price" 657 } 658 } 659 param { 660 type: PLACEHOLDER 661 position: 0 662 } 663 } 664 } 665 operation { 666 source { 667 name: "price" 668 } 669 operation: SET 670 value { 671 type: LITERAL 672 literal { 673 type: V_DOUBLE 674 v_double: 456.54 675 } 676 } 677 } 678 args { 679 type: V_DOUBLE 680 v_double: 0.9 681 } 682} 683 684 6852 rows affected 686Rows matched: 2 Changed: 2 Warnings: 0 687RUN SELECT * FROM xtest.mytable ORDER BY price 688id name price info 6896 berry null {"color": "orange"} 6902 apple 456.54 {"color": "red"} 6915 orange 456.54 {"color": "orange"} 6921 banana 895.63 {"color": "yellow"} 6933 tomato 895.63 {"color": "red"} 6944 mango 895.63 {"color": "green"} 6950 rows affected 696Update with Float value and placeholder 697send Mysqlx.Crud.Update { 698 collection { 699 name: "mytable" 700 schema: "xtest" 701 } 702 data_model: TABLE 703 criteria { 704 type: OPERATOR 705 operator { 706 name: "==" 707 param { 708 type: IDENT 709 identifier { 710 name: "name" 711 } 712 } 713 param { 714 type: PLACEHOLDER 715 position: 0 716 } 717 } 718 } 719 operation { 720 source { 721 name: "price" 722 } 723 operation: SET 724 value { 725 type: LITERAL 726 literal { 727 type: V_FLOAT 728 v_float: 256.53 729 } 730 } 731 } 732 args { 733 type: V_STRING 734 v_string { 735 value: "berry" 736 } 737 } 738} 739 740 7411 rows affected 742Rows matched: 1 Changed: 1 Warnings: 0 743RUN SELECT * FROM xtest.mytable ORDER BY price 744id name price info 7456 berry 256.53 {"color": "orange"} 7462 apple 456.54 {"color": "red"} 7475 orange 456.54 {"color": "orange"} 7481 banana 895.63 {"color": "yellow"} 7493 tomato 895.63 {"color": "red"} 7504 mango 895.63 {"color": "green"} 7510 rows affected 752Update with String value and placeholder 753send Mysqlx.Crud.Update { 754 collection { 755 name: "mytable" 756 schema: "xtest" 757 } 758 data_model: TABLE 759 criteria { 760 type: OPERATOR 761 operator { 762 name: "==" 763 param { 764 type: IDENT 765 identifier { 766 name: "price" 767 } 768 } 769 param { 770 type: PLACEHOLDER 771 position: 0 772 } 773 } 774 } 775 operation { 776 source { 777 name: "name" 778 } 779 operation: SET 780 value { 781 type: LITERAL 782 literal { 783 type: V_STRING 784 v_string { 785 value: "watermelon" 786 } 787 } 788 } 789 } 790 args { 791 type: V_DOUBLE 792 v_double: 256.53 793 } 794} 795 796 7971 rows affected 798Rows matched: 1 Changed: 1 Warnings: 0 799RUN SELECT * FROM xtest.mytable ORDER BY price 800id name price info 8016 watermelon 256.53 {"color": "orange"} 8022 apple 456.54 {"color": "red"} 8035 orange 456.54 {"color": "orange"} 8041 banana 895.63 {"color": "yellow"} 8053 tomato 895.63 {"color": "red"} 8064 mango 895.63 {"color": "green"} 8070 rows affected 808Update with Null value and placeholder 809send 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: "price" 834 } 835 operation: SET 836 value { 837 type: LITERAL 838 literal { 839 type: V_NULL 840 } 841 } 842 } 843 args { 844 type: V_STRING 845 v_string { 846 value: "banana" 847 } 848 } 849} 850 851 8521 rows affected 853Rows matched: 1 Changed: 1 Warnings: 0 854RUN SELECT * FROM xtest.mytable ORDER BY price 855id name price info 8561 banana null {"color": "yellow"} 8576 watermelon 256.53 {"color": "orange"} 8582 apple 456.54 {"color": "red"} 8595 orange 456.54 {"color": "orange"} 8603 tomato 895.63 {"color": "red"} 8614 mango 895.63 {"color": "green"} 8620 rows affected 863Update with ITEM_MERGE value and placeholder 864send Mysqlx.Crud.Update { 865 collection { 866 name: "mytable" 867 schema: "xtest" 868 } 869 data_model: TABLE 870 criteria { 871 type: OPERATOR 872 operator { 873 name: "==" 874 param { 875 type: IDENT 876 identifier { 877 name: "name" 878 } 879 } 880 param { 881 type: PLACEHOLDER 882 position: 0 883 } 884 } 885 } 886 operation { 887 source { 888 name: "info" 889 } 890 operation: ITEM_MERGE 891 value { 892 type: LITERAL 893 literal { 894 type: V_OCTETS 895 v_octets { 896 value: "{\"third\":3.0, \"fourth\": \"four\"}" 897 } 898 } 899 } 900 } 901 args { 902 type: V_STRING 903 v_string { 904 value: "banana" 905 } 906 } 907} 908 909 9101 rows affected 911Rows matched: 1 Changed: 1 Warnings: 0 912RUN SELECT * FROM xtest.mytable ORDER BY price 913id name price info 9141 banana null {"color": "yellow", "third": 3.0, "fourth": "four"} 9156 watermelon 256.53 {"color": "orange"} 9162 apple 456.54 {"color": "red"} 9175 orange 456.54 {"color": "orange"} 9183 tomato 895.63 {"color": "red"} 9194 mango 895.63 {"color": "green"} 9200 rows affected 921RUN insert into xtest.mytable values (7, 'Grapes',null, '{"third": ["two"]}') 922 9231 rows affected 924Update with ARRAY_INSERT value and placeholder 925send Mysqlx.Crud.Update { 926 collection { 927 name: "mytable" 928 schema: "xtest" 929 } 930 data_model: TABLE 931 criteria { 932 type: OPERATOR 933 operator { 934 name: "==" 935 param { 936 type: IDENT 937 identifier { 938 name: "name" 939 } 940 } 941 param { 942 type: PLACEHOLDER 943 position: 0 944 } 945 } 946 } 947 operation { 948 source { 949 document_path { 950 type: MEMBER 951 value: "third" 952 } 953 document_path { 954 type: ARRAY_INDEX 955 index: 0 956 } 957 name: "info" 958 } 959 operation: ARRAY_INSERT 960 value { 961 type: LITERAL 962 literal { 963 type: V_OCTETS 964 v_octets { 965 value: "two.1" 966 } 967 } 968 } 969 } 970 args { 971 type: V_STRING 972 v_string { 973 value: "Grapes" 974 } 975 } 976} 977 978 9791 rows affected 980Rows matched: 1 Changed: 1 Warnings: 0 981RUN SELECT * FROM xtest.mytable ORDER BY price 982id name price info 9831 banana null {"color": "yellow", "third": 3.0, "fourth": "four"} 9847 Grapes null {"third": ["two.1", "two"]} 9856 watermelon 256.53 {"color": "orange"} 9862 apple 456.54 {"color": "red"} 9875 orange 456.54 {"color": "orange"} 9883 tomato 895.63 {"color": "red"} 9894 mango 895.63 {"color": "green"} 9900 rows affected 991Delete with == Operator and placeholder 992send Mysqlx.Crud.Delete { 993 collection { 994 name: "mytable" 995 schema: "xtest" 996 } 997 data_model: TABLE 998 criteria { 999 type: OPERATOR 1000 operator { 1001 name: "==" 1002 param { 1003 type: IDENT 1004 identifier { 1005 name: "price" 1006 } 1007 } 1008 param { 1009 type: PLACEHOLDER 1010 position: 0 1011 } 1012 } 1013 } 1014 args { 1015 type: V_DOUBLE 1016 v_double: 256.53 1017 } 1018} 1019 1020 10211 rows affected 1022RUN SELECT * FROM xtest.mytable 1023id name price info 10241 banana null {"color": "yellow", "third": 3.0, "fourth": "four"} 10252 apple 456.54 {"color": "red"} 10263 tomato 895.63 {"color": "red"} 10274 mango 895.63 {"color": "green"} 10285 orange 456.54 {"color": "orange"} 10297 Grapes null {"third": ["two.1", "two"]} 10300 rows affected 1031Delete with != Operator and placeholder 1032send Mysqlx.Crud.Delete { 1033 collection { 1034 name: "mytable" 1035 schema: "xtest" 1036 } 1037 data_model: TABLE 1038 criteria { 1039 type: OPERATOR 1040 operator { 1041 name: "!=" 1042 param { 1043 type: IDENT 1044 identifier { 1045 name: "price" 1046 } 1047 } 1048 param { 1049 type: PLACEHOLDER 1050 position: 0 1051 } 1052 } 1053 } 1054 args { 1055 type: V_DOUBLE 1056 v_double: 256.53 1057 } 1058} 1059 1060 10614 rows affected 1062RUN SELECT * FROM xtest.mytable 1063id name price info 10641 banana null {"color": "yellow", "third": 3.0, "fourth": "four"} 10657 Grapes null {"third": ["two.1", "two"]} 10660 rows affected 1067Find Wrong placeholder 1068send Mysqlx.Crud.Find { 1069 collection { 1070 name: "mytable" 1071 schema: "xtest" 1072 } 1073 data_model: TABLE 1074 criteria { 1075 type: OPERATOR 1076 operator { 1077 name: ">" 1078 param { 1079 type: IDENT 1080 identifier { 1081 name: "price" 1082 } 1083 } 1084 param { 1085 type: PLACEHOLDER 1086 position: 1 1087 } 1088 } 1089 } 1090 args { 1091 type: V_DOUBLE 1092 v_double: 0 1093 } 1094} 1095 1096Got expected error: Invalid value of placeholder (code 5154) 1097Find Missing placeholder 1098send Mysqlx.Crud.Find { 1099 collection { 1100 name: "mytable" 1101 schema: "xtest" 1102 } 1103 data_model: TABLE 1104 criteria { 1105 type: OPERATOR 1106 operator { 1107 name: ">" 1108 param { 1109 type: IDENT 1110 identifier { 1111 name: "price" 1112 } 1113 } 1114 param { 1115 type: PLACEHOLDER 1116 position: 0 1117 } 1118 } 1119 } 1120} 1121 1122Got expected error: Invalid value of placeholder (code 5154) 1123================================================================================ 1124CLEAN UP 1125================================================================================ 1126RUN drop schema if exists xtest 1127 11281 rows affected 1129Mysqlx.Ok { 1130 msg: "bye!" 1131} 1132ok 1133