Debian Codegraph @

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

raw
1 create schema xtest default charset 'utf8mb4';
2
3
4 ## I. Create collection with respect to old style collections.
5 #
6 # 1. Create collection when there is an old collection with same name.
7 # 2. Create collection when there is an old collection with different
8 # name.
9
10 ## II. Create collection using ensure_collection with respect to old
11 ## style collections.
12 #
13 # 1. Create collection when there is an old collection with same name.
14
15 ## III. List collection objects created with and without validation
16 ## schema
17 #
18 # 1. List just old style collection.
19 # 2. List old and new collection types.
20 # 3. List old and new collection types with regular table.
21 # 4. Drop old collection and list new collection type and a regular
22 # table.
23
24 ## IV. Call modify_collection_options on a schema created without
25 ## a schema validation
26 #
27 # 1. Modify old collection by trying to change only the validation
28 # level.
29 # 2. Modify old collection by trying to change only the validation
30 # schema.
31 # 3. Modify old collection by trying to change both validation schema
32 # and level.
33 # 4. Modify old collection containing data that is not valid according
34 # to the new schema,
35 # modification must fail and old table layout should not be changed.
36 # Update the
37 # faulty value, modification must pass and layout should be updated.
38
39 ## V. Call get_collection_options on a schema created withoud a schema
40 ## validation.
41 #
42 # 1. Fetch valid option.
43 # 1. Fetch invalid option.
44 RUN USE xtest;
45
46 0 rows affected
47
48 #
49 # I.1
50 RUN CREATE TABLE xtest.foobar (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
51 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
52 CHARSET utf8mb4 ENGINE=InnoDB
53
54 0 rows affected
55 Got expected error:
56 Mysqlx.Error {
57 severity: ERROR
58 code: 1050
59 msg: "Table \'foobar\' already exists"
60 sql_state: "42S01"
61 }
62
63 Got expected error:
64 Mysqlx.Error {
65 severity: ERROR
66 code: 1050
67 msg: "Table \'foobar\' already exists"
68 sql_state: "42S01"
69 }
70
71 RUN SHOW CREATE TABLE xtest.foobar
72 Table Create Table
73 foobar CREATE TABLE `foobar` (
74 `doc` json DEFAULT NULL,
75 `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
76 PRIMARY KEY (`_id`)
77 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
78 0 rows affected
79 RUN DROP TABLE IF EXISTS xtest.foobar
80
81 0 rows affected
82
83 #
84 # I.2
85 RUN CREATE TABLE xtest.foo (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
86 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
87 CHARSET utf8mb4 ENGINE=InnoDB
88
89 0 rows affected
90
91 command ok
92 RUN SHOW CREATE TABLE xtest.foo
93 Table Create Table
94 foo CREATE TABLE `foo` (
95 `doc` json DEFAULT NULL,
96 `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
97 PRIMARY KEY (`_id`)
98 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
99 0 rows affected
100 RUN SHOW CREATE TABLE xtest.bar
101 Table Create Table
102 bar CREATE TABLE `bar` (
103 `doc` json DEFAULT NULL,
104 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
105 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
106 PRIMARY KEY (`_id`),
107 CONSTRAINT `$val_strict_E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB` CHECK (json_schema_valid(`_json_schema`,`doc`))
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
109 0 rows affected
110 RUN DROP TABLE IF EXISTS xtest.foo
111
112 0 rows affected
113 RUN DROP TABLE IF EXISTS xtest.bar
114
115 0 rows affected
116
117 #
118 # II.1
119 RUN CREATE TABLE xtest.foobar (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
120 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
121 CHARSET utf8mb4 ENGINE=InnoDB
122
123 0 rows affected
124
125 command ok
126
127 command ok
128
129 command ok
130 RUN SHOW CREATE TABLE xtest.foobar
131 Table Create Table
132 foobar CREATE TABLE `foobar` (
133 `doc` json DEFAULT NULL,
134 `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
135 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
136 PRIMARY KEY (`_id`),
137 CONSTRAINT `$val_strict_9B500343BC52E2911172EB52AE5CF4847604C6E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
139 0 rows affected
140 RUN DROP TABLE IF EXISTS xtest.foobar
141
142 0 rows affected
143
144 #
145 # III.1
146 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
147 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
148 CHARSET utf8mb4 ENGINE=InnoDB
149
150 0 rows affected
151 name type
152 old_coll COLLECTION
153 command ok
154
155 #
156 # III.2
157
158 command ok
159 name type
160 new_coll2 COLLECTION
161 old_coll COLLECTION
162 command ok
163
164 command ok
165 name type
166 new_coll2 COLLECTION
167 new_coll3 COLLECTION
168 old_coll COLLECTION
169 command ok
170
171 #
172 # III.3
173 RUN CREATE TABLE xtest.regular_tab (doc JSON);
174
175 0 rows affected
176 name type
177 new_coll2 COLLECTION
178 new_coll3 COLLECTION
179 old_coll COLLECTION
180 regular_tab TABLE
181 command ok
182
183 #
184 # III.4
185 RUN DROP TABLE IF EXISTS xtest.old_coll
186
187 0 rows affected
188 name type
189 new_coll2 COLLECTION
190 new_coll3 COLLECTION
191 regular_tab TABLE
192 command ok
193
194 0 rows affected
195
196 #
197 # IV.1
198 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
199 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
200 CHARSET utf8mb4 ENGINE=InnoDB
201
202 0 rows affected
203 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
204 COLUMN_NAME
205 _id
206 doc
207 0 rows affected
208 send Mysqlx.Crud.Insert {
209 collection {
210 name: "old_coll"
211 schema: "xtest"
212 }
213 data_model: DOCUMENT
214 row {
215 field {
216 type: LITERAL
217 literal {
218 type: V_STRING
219 v_string {
220 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
221 }
222 }
223 }
224 }
225 }
226
227
228 1 rows affected
229
230 command ok
231 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
232 COLUMN_NAME
233 _id
234 _json_schema
235 doc
236 0 rows affected
237 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
238 RUN DROP TABLE IF EXISTS xtest.old_coll
239
240 0 rows affected
241
242 #
243 # IV.2
244 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
245 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
246 CHARSET utf8mb4 ENGINE=InnoDB
247
248 0 rows affected
249 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
250 COLUMN_NAME
251 _id
252 doc
253 0 rows affected
254 send Mysqlx.Crud.Insert {
255 collection {
256 name: "old_coll"
257 schema: "xtest"
258 }
259 data_model: DOCUMENT
260 row {
261 field {
262 type: LITERAL
263 literal {
264 type: V_STRING
265 v_string {
266 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
267 }
268 }
269 }
270 }
271 }
272
273
274 1 rows affected
275
276 command ok
277 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
278 COLUMN_NAME
279 _id
280 _json_schema
281 doc
282 0 rows affected
283 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
284 RUN DROP TABLE IF EXISTS xtest.old_coll
285
286 0 rows affected
287
288 #
289 # IV.3
290 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
291 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
292 CHARSET utf8mb4 ENGINE=InnoDB
293
294 0 rows affected
295 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
296 COLUMN_NAME
297 _id
298 doc
299 0 rows affected
300 send Mysqlx.Crud.Insert {
301 collection {
302 name: "old_coll"
303 schema: "xtest"
304 }
305 data_model: DOCUMENT
306 row {
307 field {
308 type: LITERAL
309 literal {
310 type: V_STRING
311 v_string {
312 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
313 }
314 }
315 }
316 }
317 }
318
319
320 1 rows affected
321
322 command ok
323 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
324 COLUMN_NAME
325 _id
326 _json_schema
327 doc
328 0 rows affected
329 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
330 RUN DROP TABLE IF EXISTS xtest.old_coll
331
332 0 rows affected
333
334 #
335 # IV.4
336 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
337 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
338 CHARSET utf8mb4 ENGINE=InnoDB
339
340 0 rows affected
341 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
342 COLUMN_NAME
343 _id
344 doc
345 0 rows affected
346 send Mysqlx.Crud.Insert {
347 collection {
348 name: "old_coll"
349 schema: "xtest"
350 }
351 data_model: DOCUMENT
352 row {
353 field {
354 type: LITERAL
355 literal {
356 type: V_STRING
357 v_string {
358 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
359 }
360 }
361 }
362 }
363 }
364
365
366 1 rows affected
367 send Mysqlx.Crud.Insert {
368 collection {
369 name: "old_coll"
370 schema: "xtest"
371 }
372 data_model: DOCUMENT
373 row {
374 field {
375 type: LITERAL
376 literal {
377 type: V_STRING
378 v_string {
379 value: "{\"_id\": \"2\", \"name\": \"Bob\", \"amount\": 11.19}"
380 }
381 }
382 }
383 }
384 }
385
386
387 1 rows affected
388 send Mysqlx.Crud.Insert {
389 collection {
390 name: "old_coll"
391 schema: "xtest"
392 }
393 data_model: DOCUMENT
394 row {
395 field {
396 type: LITERAL
397 literal {
398 type: V_STRING
399 v_string {
400 value: "{\"_id\": \"3\", \"name\": \"John\", \"amount\": 223.17}"
401 }
402 }
403 }
404 }
405 }
406
407
408 1 rows affected
409
410 command ok
411 Got expected error:
412 Mysqlx.Error {
413 severity: ERROR
414 code: 5180
415 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/amount\' failed requirement \'minimum\' at JSON Schema location \'#/properties/amount\'."
416 sql_state: "HY000"
417 }
418
419 send Mysqlx.Crud.Update {
420 collection {
421 name: "old_coll"
422 schema: "xtest"
423 }
424 data_model: DOCUMENT
425 criteria {
426 type: OPERATOR
427 operator {
428 name: "=="
429 param {
430 type: IDENT
431 identifier {
432 document_path {
433 type: MEMBER
434 value: "amount"
435 }
436 }
437 }
438 param {
439 type: LITERAL
440 literal {
441 type: V_DOUBLE
442 v_double: 11.19
443 }
444 }
445 }
446 }
447 operation {
448 source {
449 document_path {
450 type: MEMBER
451 value: "amount"
452 }
453 }
454 operation: ITEM_SET
455 value {
456 type: LITERAL
457 literal {
458 type: V_DOUBLE
459 v_double: 200
460 }
461 }
462 }
463 }
464
465
466 1 rows affected
467 Rows matched: 1 Changed: 1 Warnings: 0
468
469 command ok
470 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
471 COLUMN_NAME
472 _id
473 _json_schema
474 doc
475 0 rows affected
476 RUN DROP TABLE IF EXISTS xtest.old_coll
477
478 0 rows affected
479
480 #
481 # V.1
482 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
483 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
484 CHARSET utf8mb4 ENGINE=InnoDB
485
486 0 rows affected
487 Result
488 { "validation": { "level": "off", "schema": {"type": "object"} } }
489 command ok
490 RUN DROP TABLE IF EXISTS xtest.old_coll
491
492 0 rows affected
493
494 #
495 # V.2
496 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS
497 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY)
498 CHARSET utf8mb4 ENGINE=InnoDB
499
500 0 rows affected
501 Got expected error:
502 Mysqlx.Error {
503 severity: ERROR
504 code: 5181
505 msg: "Requested collection option \'foo\' doesn\'t exist."
506 sql_state: "HY000"
507 }
508
509 RUN DROP TABLE IF EXISTS xtest.old_coll
510
511 0 rows affected
512 Mysqlx.Ok {
513 msg: "bye!"
514 }
515 ok
516
517
518 ## I. Create collection with respect to old style collections.
519 #
520 # 1. Create collection when there is an old collection with same name.
521 # 2. Create collection when there is an old collection with different
522 # name.
523
524 ## II. Create collection using ensure_collection with respect to old
525 ## style collections.
526 #
527 # 1. Create collection when there is an old collection with same name.
528
529 ## III. List collection objects created with and without validation
530 ## schema
531 #
532 # 1. List just old style collection.
533 # 2. List old and new collection types.
534 # 3. List old and new collection types with regular table.
535 # 4. Drop old collection and list new collection type and a regular
536 # table.
537
538 ## IV. Call modify_collection_options on a schema created without
539 ## a schema validation
540 #
541 # 1. Modify old collection by trying to change only the validation
542 # level.
543 # 2. Modify old collection by trying to change only the validation
544 # schema.
545 # 3. Modify old collection by trying to change both validation schema
546 # and level.
547 # 4. Modify old collection containing data that is not valid according
548 # to the new schema,
549 # modification must fail and old table layout should not be changed.
550 # Update the
551 # faulty value, modification must pass and layout should be updated.
552
553 ## V. Call get_collection_options on a schema created withoud a schema
554 ## validation.
555 #
556 # 1. Fetch valid option.
557 # 1. Fetch invalid option.
558 RUN USE xtest;
559
560 0 rows affected
561
562 #
563 # I.1
564 RUN CREATE TABLE xtest.foobar (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
565 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
566 ENGINE=InnoDB
567
568 0 rows affected
569 Got expected error:
570 Mysqlx.Error {
571 severity: ERROR
572 code: 1050
573 msg: "Table \'foobar\' already exists"
574 sql_state: "42S01"
575 }
576
577 Got expected error:
578 Mysqlx.Error {
579 severity: ERROR
580 code: 1050
581 msg: "Table \'foobar\' already exists"
582 sql_state: "42S01"
583 }
584
585 RUN SHOW CREATE TABLE xtest.foobar
586 Table Create Table
587 foobar CREATE TABLE `foobar` (
588 `doc` json DEFAULT NULL,
589 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
590 PRIMARY KEY (`_id`)
591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
592 0 rows affected
593 RUN DROP TABLE IF EXISTS xtest.foobar
594
595 0 rows affected
596
597 #
598 # I.2
599 RUN CREATE TABLE xtest.foo (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
600 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
601 ENGINE=InnoDB
602
603 0 rows affected
604
605 command ok
606 RUN SHOW CREATE TABLE xtest.foo
607 Table Create Table
608 foo CREATE TABLE `foo` (
609 `doc` json DEFAULT NULL,
610 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
611 PRIMARY KEY (`_id`)
612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
613 0 rows affected
614 RUN SHOW CREATE TABLE xtest.bar
615 Table Create Table
616 bar CREATE TABLE `bar` (
617 `doc` json DEFAULT NULL,
618 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
619 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
620 PRIMARY KEY (`_id`),
621 CONSTRAINT `$val_strict_E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB` CHECK (json_schema_valid(`_json_schema`,`doc`))
622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
623 0 rows affected
624 RUN DROP TABLE IF EXISTS xtest.foo
625
626 0 rows affected
627 RUN DROP TABLE IF EXISTS xtest.bar
628
629 0 rows affected
630
631 #
632 # II.1
633 RUN CREATE TABLE xtest.foobar (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
634 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
635 ENGINE=InnoDB
636
637 0 rows affected
638
639 command ok
640
641 command ok
642
643 command ok
644 RUN SHOW CREATE TABLE xtest.foobar
645 Table Create Table
646 foobar CREATE TABLE `foobar` (
647 `doc` json DEFAULT NULL,
648 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
649 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
650 PRIMARY KEY (`_id`),
651 CONSTRAINT `$val_strict_9B500343BC52E2911172EB52AE5CF4847604C6E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
653 0 rows affected
654 RUN DROP TABLE IF EXISTS xtest.foobar
655
656 0 rows affected
657
658 #
659 # III.1
660 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
661 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
662 ENGINE=InnoDB
663
664 0 rows affected
665 name type
666 old_coll COLLECTION
667 command ok
668
669 #
670 # III.2
671
672 command ok
673 name type
674 new_coll2 COLLECTION
675 old_coll COLLECTION
676 command ok
677
678 command ok
679 name type
680 new_coll2 COLLECTION
681 new_coll3 COLLECTION
682 old_coll COLLECTION
683 command ok
684
685 #
686 # III.3
687 RUN CREATE TABLE xtest.regular_tab (doc JSON);
688
689 0 rows affected
690 name type
691 new_coll2 COLLECTION
692 new_coll3 COLLECTION
693 old_coll COLLECTION
694 regular_tab TABLE
695 command ok
696
697 #
698 # III.4
699 RUN DROP TABLE IF EXISTS xtest.old_coll
700
701 0 rows affected
702 name type
703 new_coll2 COLLECTION
704 new_coll3 COLLECTION
705 regular_tab TABLE
706 command ok
707
708 0 rows affected
709
710 #
711 # IV.1
712 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
713 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
714 ENGINE=InnoDB
715
716 0 rows affected
717 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
718 COLUMN_NAME
719 _id
720 doc
721 0 rows affected
722 send Mysqlx.Crud.Insert {
723 collection {
724 name: "old_coll"
725 schema: "xtest"
726 }
727 data_model: DOCUMENT
728 row {
729 field {
730 type: LITERAL
731 literal {
732 type: V_STRING
733 v_string {
734 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
735 }
736 }
737 }
738 }
739 }
740
741
742 1 rows affected
743
744 command ok
745 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
746 COLUMN_NAME
747 _id
748 _json_schema
749 doc
750 0 rows affected
751 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
752 RUN DROP TABLE IF EXISTS xtest.old_coll
753
754 0 rows affected
755
756 #
757 # IV.2
758 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
759 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
760 ENGINE=InnoDB
761
762 0 rows affected
763 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
764 COLUMN_NAME
765 _id
766 doc
767 0 rows affected
768 send Mysqlx.Crud.Insert {
769 collection {
770 name: "old_coll"
771 schema: "xtest"
772 }
773 data_model: DOCUMENT
774 row {
775 field {
776 type: LITERAL
777 literal {
778 type: V_STRING
779 v_string {
780 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
781 }
782 }
783 }
784 }
785 }
786
787
788 1 rows affected
789
790 command ok
791 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
792 COLUMN_NAME
793 _id
794 _json_schema
795 doc
796 0 rows affected
797 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
798 RUN DROP TABLE IF EXISTS xtest.old_coll
799
800 0 rows affected
801
802 #
803 # IV.3
804 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
805 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
806 ENGINE=InnoDB
807
808 0 rows affected
809 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
810 COLUMN_NAME
811 _id
812 doc
813 0 rows affected
814 send Mysqlx.Crud.Insert {
815 collection {
816 name: "old_coll"
817 schema: "xtest"
818 }
819 data_model: DOCUMENT
820 row {
821 field {
822 type: LITERAL
823 literal {
824 type: V_STRING
825 v_string {
826 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
827 }
828 }
829 }
830 }
831 }
832
833
834 1 rows affected
835
836 command ok
837 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
838 COLUMN_NAME
839 _id
840 _json_schema
841 doc
842 0 rows affected
843 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
844 RUN DROP TABLE IF EXISTS xtest.old_coll
845
846 0 rows affected
847
848 #
849 # IV.4
850 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
851 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
852 ENGINE=InnoDB
853
854 0 rows affected
855 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
856 COLUMN_NAME
857 _id
858 doc
859 0 rows affected
860 send Mysqlx.Crud.Insert {
861 collection {
862 name: "old_coll"
863 schema: "xtest"
864 }
865 data_model: DOCUMENT
866 row {
867 field {
868 type: LITERAL
869 literal {
870 type: V_STRING
871 v_string {
872 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
873 }
874 }
875 }
876 }
877 }
878
879
880 1 rows affected
881 send Mysqlx.Crud.Insert {
882 collection {
883 name: "old_coll"
884 schema: "xtest"
885 }
886 data_model: DOCUMENT
887 row {
888 field {
889 type: LITERAL
890 literal {
891 type: V_STRING
892 v_string {
893 value: "{\"_id\": \"2\", \"name\": \"Bob\", \"amount\": 11.19}"
894 }
895 }
896 }
897 }
898 }
899
900
901 1 rows affected
902 send Mysqlx.Crud.Insert {
903 collection {
904 name: "old_coll"
905 schema: "xtest"
906 }
907 data_model: DOCUMENT
908 row {
909 field {
910 type: LITERAL
911 literal {
912 type: V_STRING
913 v_string {
914 value: "{\"_id\": \"3\", \"name\": \"John\", \"amount\": 223.17}"
915 }
916 }
917 }
918 }
919 }
920
921
922 1 rows affected
923
924 command ok
925 Got expected error:
926 Mysqlx.Error {
927 severity: ERROR
928 code: 5180
929 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/amount\' failed requirement \'minimum\' at JSON Schema location \'#/properties/amount\'."
930 sql_state: "HY000"
931 }
932
933 send Mysqlx.Crud.Update {
934 collection {
935 name: "old_coll"
936 schema: "xtest"
937 }
938 data_model: DOCUMENT
939 criteria {
940 type: OPERATOR
941 operator {
942 name: "=="
943 param {
944 type: IDENT
945 identifier {
946 document_path {
947 type: MEMBER
948 value: "amount"
949 }
950 }
951 }
952 param {
953 type: LITERAL
954 literal {
955 type: V_DOUBLE
956 v_double: 11.19
957 }
958 }
959 }
960 }
961 operation {
962 source {
963 document_path {
964 type: MEMBER
965 value: "amount"
966 }
967 }
968 operation: ITEM_SET
969 value {
970 type: LITERAL
971 literal {
972 type: V_DOUBLE
973 v_double: 200
974 }
975 }
976 }
977 }
978
979
980 1 rows affected
981 Rows matched: 1 Changed: 1 Warnings: 0
982
983 command ok
984 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
985 COLUMN_NAME
986 _id
987 _json_schema
988 doc
989 0 rows affected
990 RUN DROP TABLE IF EXISTS xtest.old_coll
991
992 0 rows affected
993
994 #
995 # V.1
996 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
997 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
998 ENGINE=InnoDB
999
1000 0 rows affected
1001 Result
1002 { "validation": { "level": "off", "schema": {"type": "object"} } }
1003 command ok
1004 RUN DROP TABLE IF EXISTS xtest.old_coll
1005
1006 0 rows affected
1007
1008 #
1009 # V.2
1010 RUN CREATE TABLE xtest.old_coll (doc JSON, _id VARBINARY(32) GENERATED ALWAYS AS
1011 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4
1012 ENGINE=InnoDB
1013
1014 0 rows affected
1015 Got expected error:
1016 Mysqlx.Error {
1017 severity: ERROR
1018 code: 5181
1019 msg: "Requested collection option \'foo\' doesn\'t exist."
1020 sql_state: "HY000"
1021 }
1022
1023 RUN DROP TABLE IF EXISTS xtest.old_coll
1024
1025 0 rows affected
1026 Mysqlx.Ok {
1027 msg: "bye!"
1028 }
1029 ok
1030
1031
1032 ## I. Create collection with respect to old style collections.
1033 #
1034 # 1. Create collection when there is an old collection with same name.
1035 # 2. Create collection when there is an old collection with different
1036 # name.
1037
1038 ## II. Create collection using ensure_collection with respect to old
1039 ## style collections.
1040 #
1041 # 1. Create collection when there is an old collection with same name.
1042
1043 ## III. List collection objects created with and without validation
1044 ## schema
1045 #
1046 # 1. List just old style collection.
1047 # 2. List old and new collection types.
1048 # 3. List old and new collection types with regular table.
1049 # 4. Drop old collection and list new collection type and a regular
1050 # table.
1051
1052 ## IV. Call modify_collection_options on a schema created without
1053 ## a schema validation
1054 #
1055 # 1. Modify old collection by trying to change only the validation
1056 # level.
1057 # 2. Modify old collection by trying to change only the validation
1058 # schema.
1059 # 3. Modify old collection by trying to change both validation schema
1060 # and level.
1061 # 4. Modify old collection containing data that is not valid according
1062 # to the new schema,
1063 # modification must fail and old table layout should not be changed.
1064 # Update the
1065 # faulty value, modification must pass and layout should be updated.
1066
1067 ## V. Call get_collection_options on a schema created withoud a schema
1068 ## validation.
1069 #
1070 # 1. Fetch valid option.
1071 # 1. Fetch invalid option.
1072 RUN USE xtest;
1073
1074 0 rows affected
1075
1076 #
1077 # I.1
1078 RUN CREATE TABLE xtest.foobar (`doc` json DEFAULT NULL, `_id` varbinary(32)
1079 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1080 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1081 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1082 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1083 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1084 ENGINE=InnoDB
1085
1086 0 rows affected
1087 Got expected error:
1088 Mysqlx.Error {
1089 severity: ERROR
1090 code: 1050
1091 msg: "Table \'foobar\' already exists"
1092 sql_state: "42S01"
1093 }
1094
1095 Got expected error:
1096 Mysqlx.Error {
1097 severity: ERROR
1098 code: 1050
1099 msg: "Table \'foobar\' already exists"
1100 sql_state: "42S01"
1101 }
1102
1103 RUN SHOW CREATE TABLE xtest.foobar
1104 Table Create Table
1105 foobar CREATE TABLE `foobar` (
1106 `doc` json DEFAULT NULL,
1107 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1108 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1109 PRIMARY KEY (`_id`),
1110 CONSTRAINT `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK (json_schema_valid(`_json_schema`,`doc`))
1111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1112 0 rows affected
1113 RUN DROP TABLE IF EXISTS xtest.foobar
1114
1115 0 rows affected
1116
1117 #
1118 # I.2
1119 RUN CREATE TABLE xtest.foo (`doc` json DEFAULT NULL, `_id` varbinary(32)
1120 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1121 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1122 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1123 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1124 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1125 ENGINE=InnoDB
1126
1127 0 rows affected
1128
1129 command ok
1130 RUN SHOW CREATE TABLE xtest.foo
1131 Table Create Table
1132 foo CREATE TABLE `foo` (
1133 `doc` json DEFAULT NULL,
1134 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1135 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1136 PRIMARY KEY (`_id`),
1137 CONSTRAINT `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK (json_schema_valid(`_json_schema`,`doc`))
1138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1139 0 rows affected
1140 RUN SHOW CREATE TABLE xtest.bar
1141 Table Create Table
1142 bar CREATE TABLE `bar` (
1143 `doc` json DEFAULT NULL,
1144 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1145 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1146 PRIMARY KEY (`_id`),
1147 CONSTRAINT `$val_strict_E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB` CHECK (json_schema_valid(`_json_schema`,`doc`))
1148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1149 0 rows affected
1150 RUN DROP TABLE IF EXISTS xtest.foo
1151
1152 0 rows affected
1153 RUN DROP TABLE IF EXISTS xtest.bar
1154
1155 0 rows affected
1156
1157 #
1158 # II.1
1159 RUN CREATE TABLE xtest.foobar (`doc` json DEFAULT NULL, `_id` varbinary(32)
1160 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1161 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1162 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1163 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1164 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1165 ENGINE=InnoDB
1166
1167 0 rows affected
1168
1169 command ok
1170
1171 command ok
1172
1173 command ok
1174 RUN SHOW CREATE TABLE xtest.foobar
1175 Table Create Table
1176 foobar CREATE TABLE `foobar` (
1177 `doc` json DEFAULT NULL,
1178 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1179 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1180 PRIMARY KEY (`_id`),
1181 CONSTRAINT `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK (json_schema_valid(`_json_schema`,`doc`))
1182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1183 0 rows affected
1184 RUN DROP TABLE IF EXISTS xtest.foobar
1185
1186 0 rows affected
1187
1188 #
1189 # III.1
1190 RUN CREATE TABLE xtest.old_coll (`doc` json DEFAULT NULL, `_id` varbinary(32)
1191 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1192 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1193 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1194 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1195 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1196 ENGINE=InnoDB
1197
1198 0 rows affected
1199 name type
1200 old_coll COLLECTION
1201 command ok
1202
1203 #
1204 # III.2
1205
1206 command ok
1207 name type
1208 new_coll2 COLLECTION
1209 old_coll COLLECTION
1210 command ok
1211
1212 command ok
1213 name type
1214 new_coll2 COLLECTION
1215 new_coll3 COLLECTION
1216 old_coll COLLECTION
1217 command ok
1218
1219 #
1220 # III.3
1221 RUN CREATE TABLE xtest.regular_tab (doc JSON);
1222
1223 0 rows affected
1224 name type
1225 new_coll2 COLLECTION
1226 new_coll3 COLLECTION
1227 old_coll COLLECTION
1228 regular_tab TABLE
1229 command ok
1230
1231 #
1232 # III.4
1233 RUN DROP TABLE IF EXISTS xtest.old_coll
1234
1235 0 rows affected
1236 name type
1237 new_coll2 COLLECTION
1238 new_coll3 COLLECTION
1239 regular_tab TABLE
1240 command ok
1241
1242 0 rows affected
1243
1244 #
1245 # IV.1
1246 RUN CREATE TABLE xtest.old_coll (`doc` json DEFAULT NULL, `_id` varbinary(32)
1247 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1248 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1249 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1250 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1251 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1252 ENGINE=InnoDB
1253
1254 0 rows affected
1255 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1256 COLUMN_NAME
1257 _id
1258 _json_schema
1259 doc
1260 0 rows affected
1261 send Mysqlx.Crud.Insert {
1262 collection {
1263 name: "old_coll"
1264 schema: "xtest"
1265 }
1266 data_model: DOCUMENT
1267 row {
1268 field {
1269 type: LITERAL
1270 literal {
1271 type: V_STRING
1272 v_string {
1273 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
1274 }
1275 }
1276 }
1277 }
1278 }
1279
1280
1281 1 rows affected
1282
1283 command ok
1284 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1285 COLUMN_NAME
1286 _id
1287 _json_schema
1288 doc
1289 0 rows affected
1290 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
1291 RUN DROP TABLE IF EXISTS xtest.old_coll
1292
1293 0 rows affected
1294
1295 #
1296 # IV.2
1297 RUN CREATE TABLE xtest.old_coll (`doc` json DEFAULT NULL, `_id` varbinary(32)
1298 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1299 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1300 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1301 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1302 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1303 ENGINE=InnoDB
1304
1305 0 rows affected
1306 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1307 COLUMN_NAME
1308 _id
1309 _json_schema
1310 doc
1311 0 rows affected
1312 send Mysqlx.Crud.Insert {
1313 collection {
1314 name: "old_coll"
1315 schema: "xtest"
1316 }
1317 data_model: DOCUMENT
1318 row {
1319 field {
1320 type: LITERAL
1321 literal {
1322 type: V_STRING
1323 v_string {
1324 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
1325 }
1326 }
1327 }
1328 }
1329 }
1330
1331
1332 1 rows affected
1333
1334 command ok
1335 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1336 COLUMN_NAME
1337 _id
1338 _json_schema
1339 doc
1340 0 rows affected
1341 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
1342 RUN DROP TABLE IF EXISTS xtest.old_coll
1343
1344 0 rows affected
1345
1346 #
1347 # IV.3
1348 RUN CREATE TABLE xtest.old_coll (`doc` json DEFAULT NULL, `_id` varbinary(32)
1349 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1350 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1351 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1352 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1353 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1354 ENGINE=InnoDB
1355
1356 0 rows affected
1357 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1358 COLUMN_NAME
1359 _id
1360 _json_schema
1361 doc
1362 0 rows affected
1363 send Mysqlx.Crud.Insert {
1364 collection {
1365 name: "old_coll"
1366 schema: "xtest"
1367 }
1368 data_model: DOCUMENT
1369 row {
1370 field {
1371 type: LITERAL
1372 literal {
1373 type: V_STRING
1374 v_string {
1375 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
1376 }
1377 }
1378 }
1379 }
1380 }
1381
1382
1383 1 rows affected
1384
1385 command ok
1386 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1387 COLUMN_NAME
1388 _id
1389 _json_schema
1390 doc
1391 0 rows affected
1392 RUN SELECT enforced FROM information_schema.table_constraints WHERE constraint_schema = "xtest" AND table_name ='old_coll' AND constraint_name LIKE '$val_strict_%'
1393 RUN DROP TABLE IF EXISTS xtest.old_coll
1394
1395 0 rows affected
1396
1397 #
1398 # IV.4
1399 RUN CREATE TABLE xtest.old_coll (`doc` json DEFAULT NULL, `_id` varbinary(32)
1400 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1401 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1402 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1403 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1404 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1405 ENGINE=InnoDB
1406
1407 0 rows affected
1408 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1409 COLUMN_NAME
1410 _id
1411 _json_schema
1412 doc
1413 0 rows affected
1414 send Mysqlx.Crud.Insert {
1415 collection {
1416 name: "old_coll"
1417 schema: "xtest"
1418 }
1419 data_model: DOCUMENT
1420 row {
1421 field {
1422 type: LITERAL
1423 literal {
1424 type: V_STRING
1425 v_string {
1426 value: "{\"_id\": \"1\", \"name\": \"Robb\", \"amount\": 125.21}"
1427 }
1428 }
1429 }
1430 }
1431 }
1432
1433
1434 1 rows affected
1435 send Mysqlx.Crud.Insert {
1436 collection {
1437 name: "old_coll"
1438 schema: "xtest"
1439 }
1440 data_model: DOCUMENT
1441 row {
1442 field {
1443 type: LITERAL
1444 literal {
1445 type: V_STRING
1446 v_string {
1447 value: "{\"_id\": \"2\", \"name\": \"Bob\", \"amount\": 11.19}"
1448 }
1449 }
1450 }
1451 }
1452 }
1453
1454
1455 1 rows affected
1456 send Mysqlx.Crud.Insert {
1457 collection {
1458 name: "old_coll"
1459 schema: "xtest"
1460 }
1461 data_model: DOCUMENT
1462 row {
1463 field {
1464 type: LITERAL
1465 literal {
1466 type: V_STRING
1467 v_string {
1468 value: "{\"_id\": \"3\", \"name\": \"John\", \"amount\": 223.17}"
1469 }
1470 }
1471 }
1472 }
1473 }
1474
1475
1476 1 rows affected
1477
1478 command ok
1479 Got expected error:
1480 Mysqlx.Error {
1481 severity: ERROR
1482 code: 5180
1483 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/amount\' failed requirement \'minimum\' at JSON Schema location \'#/properties/amount\'."
1484 sql_state: "HY000"
1485 }
1486
1487 send Mysqlx.Crud.Update {
1488 collection {
1489 name: "old_coll"
1490 schema: "xtest"
1491 }
1492 data_model: DOCUMENT
1493 criteria {
1494 type: OPERATOR
1495 operator {
1496 name: "=="
1497 param {
1498 type: IDENT
1499 identifier {
1500 document_path {
1501 type: MEMBER
1502 value: "amount"
1503 }
1504 }
1505 }
1506 param {
1507 type: LITERAL
1508 literal {
1509 type: V_DOUBLE
1510 v_double: 11.19
1511 }
1512 }
1513 }
1514 }
1515 operation {
1516 source {
1517 document_path {
1518 type: MEMBER
1519 value: "amount"
1520 }
1521 }
1522 operation: ITEM_SET
1523 value {
1524 type: LITERAL
1525 literal {
1526 type: V_DOUBLE
1527 v_double: 200
1528 }
1529 }
1530 }
1531 }
1532
1533
1534 1 rows affected
1535 Rows matched: 1 Changed: 1 Warnings: 0
1536
1537 command ok
1538 RUN SELECT column_name FROM information_schema.columns WHERE table_name = 'old_coll' ORDER BY column_name ASC
1539 COLUMN_NAME
1540 _id
1541 _json_schema
1542 doc
1543 0 rows affected
1544 RUN DROP TABLE IF EXISTS xtest.old_coll
1545
1546 0 rows affected
1547
1548 #
1549 # V.1
1550 RUN CREATE TABLE xtest.old_coll (`doc` json DEFAULT NULL, `_id` varbinary(32)
1551 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1552 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1553 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1554 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1555 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1556 ENGINE=InnoDB
1557
1558 0 rows affected
1559 Result
1560 { "validation": { "level": "strict", "schema": {"type":"object"} } }
1561 command ok
1562 RUN DROP TABLE IF EXISTS xtest.old_coll
1563
1564 0 rows affected
1565
1566 #
1567 # V.2
1568 RUN CREATE TABLE xtest.old_coll (`doc` json DEFAULT NULL, `_id` varbinary(32)
1569 GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED
1570 NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}')
1571 VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT
1572 `$val_strict_F2DE1896B6B5A7BAD6369FB94E3B214EBC2F27EA` CHECK
1573 (JSON_SCHEMA_VALID(`_json_schema`,`doc`)) ENFORCED) CHARSET utf8mb4
1574 ENGINE=InnoDB
1575
1576 0 rows affected
1577 Got expected error:
1578 Mysqlx.Error {
1579 severity: ERROR
1580 code: 5181
1581 msg: "Requested collection option \'foo\' doesn\'t exist."
1582 sql_state: "HY000"
1583 }
1584
1585 RUN DROP TABLE IF EXISTS xtest.old_coll
1586
1587 0 rows affected
1588 Mysqlx.Ok {
1589 msg: "bye!"
1590 }
1591 ok