Debian Codegraph @

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

raw
1 create schema xtest default charset 'utf8mb4';
2
3
4 ## I. Check validation schema setting when creating a collection. Check
5 # result of insert operation which violates the check constraints.
6 #
7 # 1. Check collection creation with valid schema encoded as protobuf
8 # string.
9 # 2. Check collection creation with valid schema encoded as
10 # Mysqlx.Datatypes.Object.
11 # 3. Check that when validation is not enforced then it is possible to
12 # insert noncopmlying data.
13 # 4. Check collection creation using schema encoded as
14 # Mysqlx>datatypes.Object containing special characters in key/values.
15
16 ## II. Check validation level setting.
17 #
18 # 1. Check validation level set to 'off'.
19 # a. Check validation level for validation encoded as protobuf string.
20 # b. Check validation level for validation encoded as
21 # Mysqlx.Datatypes.Object.
22 # 2. Check validation level set to 'strict'.
23 # a. Check validation level for validation encoded as protobuf string.
24 # b. Check validation level for validation encoded as
25 # Mysqlx.Datatypes.Object.
26 # 3. Assert that validation field is optional and check the default
27 # setting for validation level.
28 # a. Check validation level for validation encoded as protobuf string.
29 # b. Check validation level for validation encoded as
30 # Mysqlx.Datatypes.Object.
31 # 4. Check invalid value for validation level field.
32 # a. Check validation level for validation encoded as protobuf string.
33 # b. Check validation level for validation encoded as
34 # Mysqlx.Datatypes.Object.
35 # 5. Check invalid collection option.
36
37 ## III. Create collection without the optional validation field.
38
39 ## IV. Create collection with empty validation schema field.
40 #
41 # 1. Check empty validation schema for validation encoded as protobuf
42 # string.
43 # 2. Check empty validation schema for validation encoded as
44 # Mysqlx.Datatypes.Object.
45
46 ## V. Create collection with invalid validation options.
47 #
48 # 1. Validation contains only unknown fields.
49 # 2. Validation contains valid fields along with unknown fields.
50 # 3. Options is different type than "object".
51
52 ## IV. Create collection with empty validation level.
53 #
54 # 1. Check empty validation level for validation encoded as protobuf
55 # string.
56 # 2. Check empty validation level for validation encoded as
57 # Mysqlx.Datatypes.Object.
58
59 #
60 # I.1
61
62 command ok
63 Table Create Table
64 test_coll CREATE TABLE `test_coll` (
65 `doc` json DEFAULT NULL,
66 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
67 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"id":"http://json-schema.org/geo","$schema":"http://json-schema.org/draft-06/schema#","description":"A geographical coordinate","type":"object","properties":{"latitude":{"type":"number","minimum":-90,"maximum":90},"longitude":{"type":"number","minimum":-180,"maximum":180}},"required":["latitude","longitude"]}') VIRTUAL,
68 PRIMARY KEY (`_id`),
69 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
70 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
71 0 rows affected
72 Got expected error:
73 Mysqlx.Error {
74 severity: ERROR
75 code: 5180
76 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
77 sql_state: "HY000"
78 }
79
80
81 1 rows affected
82 Got expected error:
83 Mysqlx.Error {
84 severity: ERROR
85 code: 5180
86 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
87 sql_state: "HY000"
88 }
89
90
91 1 rows affected
92 Rows matched: 1 Changed: 1 Warnings: 0
93 doc _id _json_schema
94 {"_id": "one", "latitude": 72, "longitude": 54} one {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
95 0 rows affected
96
97 0 rows affected
98
99 #
100 # I.2
101
102 command ok
103 Table Create Table
104 test_coll CREATE TABLE `test_coll` (
105 `doc` json DEFAULT NULL,
106 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
107 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"id":"http://json-schema.org/geo","$schema":"http://json-schema.org/draft-06/schema#","description":"A geographical coordinate","type":"object","properties":{"latitude":{"type":"number","minimum":-90,"maximum":90},"longitude":{"type":"number","minimum":-180,"maximum":180}},"required":["latitude","longitude"]}') VIRTUAL,
108 PRIMARY KEY (`_id`),
109 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
111 0 rows affected
112 Got expected error:
113 Mysqlx.Error {
114 severity: ERROR
115 code: 5180
116 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
117 sql_state: "HY000"
118 }
119
120
121 1 rows affected
122 Got expected error:
123 Mysqlx.Error {
124 severity: ERROR
125 code: 5180
126 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
127 sql_state: "HY000"
128 }
129
130
131 1 rows affected
132 Rows matched: 1 Changed: 1 Warnings: 0
133 doc _id _json_schema
134 {"_id": "one", "latitude": 72, "longitude": 54} one {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
135 0 rows affected
136
137 0 rows affected
138
139 #
140 # I.3
141
142 command ok
143
144 1 rows affected
145
146 1 rows affected
147 doc _id _json_schema
148 {"_id": "one", "latitude": 3312} one {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
149 {"_id": "two", "latitude": 45, "longitude": 54} two {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
150 0 rows affected
151
152 0 rows affected
153
154 #
155 # I.4
156
157 command ok
158 Table Create Table
159 test_coll CREATE TABLE `test_coll` (
160 `doc` json DEFAULT NULL,
161 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
162 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"description":"t`y\\p"e o`b`\\\\c""t"}') VIRTUAL,
163 PRIMARY KEY (`_id`),
164 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
166 0 rows affected
167
168 0 rows affected
169
170 #
171 # II.1.a
172
173 command ok
174
175 0 rows affected
176
177 command ok
178
179 0 rows affected
180
181 #
182 # II.1.b
183
184 command ok
185
186 0 rows affected
187
188 command ok
189
190 0 rows affected
191
192 #
193 # II.2.a
194
195 command ok
196
197 0 rows affected
198
199 command ok
200
201 0 rows affected
202
203 #
204 # II.2.b
205
206 command ok
207
208 0 rows affected
209
210 command ok
211
212 0 rows affected
213
214 #
215 # II.3.a
216
217 command ok
218
219 0 rows affected
220
221 #
222 # II.3.b
223
224 command ok
225
226 0 rows affected
227
228 #
229 # II.4.a
230 Got expected error:
231 Mysqlx.Error {
232 severity: ERROR
233 code: 5017
234 msg: "Invalid \"validation.level\" argument. Allowed values are \'OFF\' and \'STRICT\'"
235 sql_state: "HY000"
236 }
237
238
239 #
240 # II.4.b
241 Got expected error:
242 Mysqlx.Error {
243 severity: ERROR
244 code: 5017
245 msg: "Invalid \"validation.level\" argument. Allowed values are \'OFF\' and \'STRICT\'"
246 sql_state: "HY000"
247 }
248
249
250 #
251 # II.5
252 Got expected error:
253 Mysqlx.Error {
254 severity: ERROR
255 code: 5021
256 msg: "\'foo\' is not a valid field for create_collection command"
257 sql_state: "HY000"
258 }
259
260
261 #
262 # III
263
264 command ok
265 Table Create Table
266 test_coll CREATE TABLE `test_coll` (
267 `doc` json DEFAULT NULL,
268 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
269 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
270 PRIMARY KEY (`_id`),
271 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
273 0 rows affected
274
275 0 rows affected
276
277 #
278 # IV.1
279
280 command ok
281 Table Create Table
282 test_coll CREATE TABLE `test_coll` (
283 `doc` json DEFAULT NULL,
284 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
285 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{}') VIRTUAL,
286 PRIMARY KEY (`_id`),
287 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
288 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
289 0 rows affected
290
291 0 rows affected
292
293 #
294 # IV.2
295
296 command ok
297 Table Create Table
298 test_coll CREATE TABLE `test_coll` (
299 `doc` json DEFAULT NULL,
300 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
301 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{}') VIRTUAL,
302 PRIMARY KEY (`_id`),
303 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
305 0 rows affected
306
307 0 rows affected
308
309 #
310 # V.1
311 Got expected error:
312 Mysqlx.Error {
313 severity: ERROR
314 code: 5021
315 msg: "\'foo\' is not a valid field for create_collection command"
316 sql_state: "HY000"
317 }
318
319
320 #
321 # V.2
322 Got expected error:
323 Mysqlx.Error {
324 severity: ERROR
325 code: 5021
326 msg: "\'foo\' is not a valid field for create_collection command"
327 sql_state: "HY000"
328 }
329
330
331 #
332 # V.3
333 Got expected error:
334 Mysqlx.Error {
335 severity: ERROR
336 code: 5016
337 msg: "Invalid data type for argument \'options\', expected \'object\' type"
338 sql_state: "HY000"
339 }
340
341
342 #
343 # VI.1
344
345 command ok
346
347 1 rows affected
348 Got expected error: Document is not valid according to the schema assigned to collection. The JSON document location '#' failed requirement 'required' at JSON Schema location '#'. (code 5180)
349
350 0 rows affected
351
352 #
353 # VI.2
354
355 command ok
356
357 1 rows affected
358 Got expected error: Document is not valid according to the schema assigned to collection. The JSON document location '#' failed requirement 'required' at JSON Schema location '#'. (code 5180)
359
360 0 rows affected
361 Mysqlx.Ok {
362 msg: "bye!"
363 }
364 ok
365
366
367 ## I. Check validation schema setting when creating a collection. Check
368 # result of insert operation which violates the check constraints.
369 #
370 # 1. Check collection creation with valid schema encoded as protobuf
371 # string.
372 # 2. Check collection creation with valid schema encoded as
373 # Mysqlx.Datatypes.Object.
374 # 3. Check that when validation is not enforced then it is possible to
375 # insert noncopmlying data.
376 # 4. Check collection creation using schema encoded as
377 # Mysqlx>datatypes.Object containing special characters in key/values.
378
379 ## II. Check validation level setting.
380 #
381 # 1. Check validation level set to 'off'.
382 # a. Check validation level for validation encoded as protobuf string.
383 # b. Check validation level for validation encoded as
384 # Mysqlx.Datatypes.Object.
385 # 2. Check validation level set to 'strict'.
386 # a. Check validation level for validation encoded as protobuf string.
387 # b. Check validation level for validation encoded as
388 # Mysqlx.Datatypes.Object.
389 # 3. Assert that validation field is optional and check the default
390 # setting for validation level.
391 # a. Check validation level for validation encoded as protobuf string.
392 # b. Check validation level for validation encoded as
393 # Mysqlx.Datatypes.Object.
394 # 4. Check invalid value for validation level field.
395 # a. Check validation level for validation encoded as protobuf string.
396 # b. Check validation level for validation encoded as
397 # Mysqlx.Datatypes.Object.
398 # 5. Check invalid collection option.
399
400 ## III. Create collection without the optional validation field.
401
402 ## IV. Create collection with empty validation schema field.
403 #
404 # 1. Check empty validation schema for validation encoded as protobuf
405 # string.
406 # 2. Check empty validation schema for validation encoded as
407 # Mysqlx.Datatypes.Object.
408
409 ## V. Create collection with invalid validation options.
410 #
411 # 1. Validation contains only unknown fields.
412 # 2. Validation contains valid fields along with unknown fields.
413 # 3. Options is different type than "object".
414
415 ## IV. Create collection with empty validation level.
416 #
417 # 1. Check empty validation level for validation encoded as protobuf
418 # string.
419 # 2. Check empty validation level for validation encoded as
420 # Mysqlx.Datatypes.Object.
421
422 #
423 # I.1
424
425 command ok
426 Table Create Table
427 test_coll CREATE TABLE `test_coll` (
428 `doc` json DEFAULT NULL,
429 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
430 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"id":"http://json-schema.org/geo","$schema":"http://json-schema.org/draft-06/schema#","description":"A geographical coordinate","type":"object","properties":{"latitude":{"type":"number","minimum":-90,"maximum":90},"longitude":{"type":"number","minimum":-180,"maximum":180}},"required":["latitude","longitude"]}') VIRTUAL,
431 PRIMARY KEY (`_id`),
432 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
434 0 rows affected
435 Got expected error:
436 Mysqlx.Error {
437 severity: ERROR
438 code: 5180
439 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
440 sql_state: "HY000"
441 }
442
443
444 1 rows affected
445 Got expected error:
446 Mysqlx.Error {
447 severity: ERROR
448 code: 5180
449 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
450 sql_state: "HY000"
451 }
452
453
454 1 rows affected
455 Rows matched: 1 Changed: 1 Warnings: 0
456 doc _id _json_schema
457 {"_id": "one", "latitude": 72, "longitude": 54} one {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
458 0 rows affected
459
460 0 rows affected
461
462 #
463 # I.2
464
465 command ok
466 Table Create Table
467 test_coll CREATE TABLE `test_coll` (
468 `doc` json DEFAULT NULL,
469 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
470 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"id":"http://json-schema.org/geo","$schema":"http://json-schema.org/draft-06/schema#","description":"A geographical coordinate","type":"object","properties":{"latitude":{"type":"number","minimum":-90,"maximum":90},"longitude":{"type":"number","minimum":-180,"maximum":180}},"required":["latitude","longitude"]}') VIRTUAL,
471 PRIMARY KEY (`_id`),
472 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
474 0 rows affected
475 Got expected error:
476 Mysqlx.Error {
477 severity: ERROR
478 code: 5180
479 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
480 sql_state: "HY000"
481 }
482
483
484 1 rows affected
485 Got expected error:
486 Mysqlx.Error {
487 severity: ERROR
488 code: 5180
489 msg: "Document is not valid according to the schema assigned to collection. The JSON document location \'#/latitude\' failed requirement \'maximum\' at JSON Schema location \'#/properties/latitude\'."
490 sql_state: "HY000"
491 }
492
493
494 1 rows affected
495 Rows matched: 1 Changed: 1 Warnings: 0
496 doc _id _json_schema
497 {"_id": "one", "latitude": 72, "longitude": 54} one {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
498 0 rows affected
499
500 0 rows affected
501
502 #
503 # I.3
504
505 command ok
506
507 1 rows affected
508
509 1 rows affected
510 doc _id _json_schema
511 {"_id": "one", "latitude": 3312} one {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
512 {"_id": "two", "latitude": 45, "longitude": 54} two {"id": "http://json-schema.org/geo", "type": "object", "$schema": "http://json-schema.org/draft-06/schema#", "required": ["latitude", "longitude"], "properties": {"latitude": {"type": "number", "maximum": 90, "minimum": -90}, "longitude": {"type": "number", "maximum": 180, "minimum": -180}}, "description": "A geographical coordinate"}
513 0 rows affected
514
515 0 rows affected
516
517 #
518 # I.4
519
520 command ok
521 Table Create Table
522 test_coll CREATE TABLE `test_coll` (
523 `doc` json DEFAULT NULL,
524 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
525 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"description":"t`y\\p"e o`b`\\\\c""t"}') VIRTUAL,
526 PRIMARY KEY (`_id`),
527 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
529 0 rows affected
530
531 0 rows affected
532
533 #
534 # II.1.a
535
536 command ok
537
538 0 rows affected
539
540 command ok
541
542 0 rows affected
543
544 #
545 # II.1.b
546
547 command ok
548
549 0 rows affected
550
551 command ok
552
553 0 rows affected
554
555 #
556 # II.2.a
557
558 command ok
559
560 0 rows affected
561
562 command ok
563
564 0 rows affected
565
566 #
567 # II.2.b
568
569 command ok
570
571 0 rows affected
572
573 command ok
574
575 0 rows affected
576
577 #
578 # II.3.a
579
580 command ok
581
582 0 rows affected
583
584 #
585 # II.3.b
586
587 command ok
588
589 0 rows affected
590
591 #
592 # II.4.a
593 Got expected error:
594 Mysqlx.Error {
595 severity: ERROR
596 code: 5017
597 msg: "Invalid \"validation.level\" argument. Allowed values are \'OFF\' and \'STRICT\'"
598 sql_state: "HY000"
599 }
600
601
602 #
603 # II.4.b
604 Got expected error:
605 Mysqlx.Error {
606 severity: ERROR
607 code: 5017
608 msg: "Invalid \"validation.level\" argument. Allowed values are \'OFF\' and \'STRICT\'"
609 sql_state: "HY000"
610 }
611
612
613 #
614 # II.5
615 Got expected error:
616 Mysqlx.Error {
617 severity: ERROR
618 code: 5021
619 msg: "\'foo\' is not a valid field for create_collection command"
620 sql_state: "HY000"
621 }
622
623
624 #
625 # III
626
627 command ok
628 Table Create Table
629 test_coll CREATE TABLE `test_coll` (
630 `doc` json DEFAULT NULL,
631 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
632 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
633 PRIMARY KEY (`_id`),
634 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
636 0 rows affected
637
638 0 rows affected
639
640 #
641 # IV.1
642
643 command ok
644 Table Create Table
645 test_coll CREATE TABLE `test_coll` (
646 `doc` json DEFAULT NULL,
647 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
648 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{}') VIRTUAL,
649 PRIMARY KEY (`_id`),
650 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
652 0 rows affected
653
654 0 rows affected
655
656 #
657 # IV.2
658
659 command ok
660 Table Create Table
661 test_coll CREATE TABLE `test_coll` (
662 `doc` json DEFAULT NULL,
663 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
664 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{}') VIRTUAL,
665 PRIMARY KEY (`_id`),
666 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
667 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
668 0 rows affected
669
670 0 rows affected
671
672 #
673 # V.1
674 Got expected error:
675 Mysqlx.Error {
676 severity: ERROR
677 code: 5021
678 msg: "\'foo\' is not a valid field for ensure_collection command"
679 sql_state: "HY000"
680 }
681
682
683 #
684 # V.2
685 Got expected error:
686 Mysqlx.Error {
687 severity: ERROR
688 code: 5021
689 msg: "\'foo\' is not a valid field for ensure_collection command"
690 sql_state: "HY000"
691 }
692
693
694 #
695 # V.3
696 Got expected error:
697 Mysqlx.Error {
698 severity: ERROR
699 code: 5016
700 msg: "Invalid data type for argument \'options\', expected \'object\' type"
701 sql_state: "HY000"
702 }
703
704
705 #
706 # VI.1
707
708 command ok
709
710 1 rows affected
711 Got expected error: Document is not valid according to the schema assigned to collection. The JSON document location '#' failed requirement 'required' at JSON Schema location '#'. (code 5180)
712
713 0 rows affected
714
715 #
716 # VI.2
717
718 command ok
719
720 1 rows affected
721 Got expected error: Document is not valid according to the schema assigned to collection. The JSON document location '#' failed requirement 'required' at JSON Schema location '#'. (code 5180)
722
723 0 rows affected
724 Mysqlx.Ok {
725 msg: "bye!"
726 }
727 ok
728
729
730 ## I. Check that validation schema do not affect how ensure_collection
731 ## establishes whether to create a collection.
732 #
733 # 1. Try to create two collections with same name and identical
734 # validation schema.
735 # 2. Try to create two collections with same name and different
736 # validation schema.
737 # 3. Try to create two collections with same name, one containing
738 # custom validation schema and the next without a validation schema.
739 # 4. Try to create two collections with different name and identical
740 # validation schema.
741 # 5. Try to create two collections with different name and different
742 # validation schema.
743
744 #
745 # I.1
746
747 command ok
748
749 command ok
750 RUN show create table xtest.test_coll
751 Table Create Table
752 test_coll CREATE TABLE `test_coll` (
753 `doc` json DEFAULT NULL,
754 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
755 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"description":"bar"}') VIRTUAL,
756 PRIMARY KEY (`_id`),
757 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
758 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
759 0 rows affected
760
761 0 rows affected
762
763 #
764 # I.2
765
766 command ok
767
768 command ok
769 RUN show create table xtest.test_coll
770 Table Create Table
771 test_coll CREATE TABLE `test_coll` (
772 `doc` json DEFAULT NULL,
773 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
774 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"description":"foo"}') VIRTUAL,
775 PRIMARY KEY (`_id`),
776 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
778 0 rows affected
779
780 0 rows affected
781
782 #
783 # I.3
784
785 command ok
786 Table Create Table
787 test_coll CREATE TABLE `test_coll` (
788 `doc` json DEFAULT NULL,
789 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
790 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"description":"custom schema"}') VIRTUAL,
791 PRIMARY KEY (`_id`),
792 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`))
793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
794 0 rows affected
795
796 command ok
797 Table Create Table
798 test_coll CREATE TABLE `test_coll` (
799 `doc` json DEFAULT NULL,
800 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
801 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
802 PRIMARY KEY (`_id`),
803 CONSTRAINT `$val_strict_889D1D09A01D7029B101675FBE738D1897A37F32` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
804 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
805 0 rows affected
806
807 0 rows affected
808
809 #
810 # I.4
811
812 command ok
813
814 command ok
815
816 0 rows affected
817
818 0 rows affected
819
820 #
821 # I.5
822
823 command ok
824
825 command ok
826
827 0 rows affected
828
829 0 rows affected
830 Mysqlx.Ok {
831 msg: "bye!"
832 }
833 ok