Debian Codegraph @

mysql-test/suite/json/t/json_schema.test

raw
1 --echo # See that SQL NULL is handled properly
2 SELECT JSON_SCHEMA_VALID(NULL, NULL);
3 SELECT JSON_SCHEMA_VALID('{}', NULL);
4 SELECT JSON_SCHEMA_VALID(NULL, '{}');
5
6 --echo # Check some basic scenarios to verify that everything is working as
7 --echo # expected. Note that the rapidjson library contains a bunch of tests to
8 --echo # verify the correctness of the JSON Schema validation, so we don't do
9 --echo # any extensive testing of the validation process itself here.
10
11 SELECT JSON_SCHEMA_VALID('{
12 "type": "object",
13 "properties": {
14 "latitude": {
15 "type": "number",
16 "minimum": 63
17 },
18 "longitude": {
19 "type": "number"
20 }
21 },
22 "required": ["latitude", "longitude"]
23 }','{
24 "latitude": 63.444697,
25 "longitude": 10.445118
26 }') AS should_be_valid;
27
28 SELECT JSON_SCHEMA_VALID('{
29 "type": "object",
30 "properties": {
31 "latitude": {
32 "type": "number",
33 "minimum": 63
34 },
35 "longitude": {
36 "type": "number"
37 }
38 },
39 "required": ["latitude", "longitude"]
40 }','{
41 "longitude": 10.445118
42 }') AS should_be_invalid;
43
44 SELECT JSON_SCHEMA_VALID('{
45 "type": "object",
46 "properties": {
47 "latitude": {
48 "type": "number",
49 "minimum": 63
50 },
51 "longitude": {
52 "type": "number"
53 }
54 },
55 "required": ["latitude", "longitude"]
56 }','{
57 "latitude": 62,
58 "longitude": 10.445118
59 }') AS should_be_invalid;
60
61 SELECT JSON_SCHEMA_VALID('{
62 "type": "object",
63 "properties": {
64 "a_string": {
65 "type": "string",
66 "pattern": "[5-9]"
67 }
68 }
69 }','{
70 "a_string": "8"
71 }') AS should_be_valid;
72
73 SELECT JSON_SCHEMA_VALID('{
74 "type": "object",
75 "properties": {
76 "a_string": {
77 "type": "string",
78 "pattern": "[5-9]"
79 }
80 }
81 }','{
82 "a_string": "4"
83 }') AS should_be_invalid;
84
85 --error ER_NOT_SUPPORTED_YET
86 SELECT JSON_SCHEMA_VALID('{
87 "type": "object",
88 "properties": { "$ref": "http://example.com" }
89 }','
90 {
91 "latitude": 63.444697
92 }') AS invalid;
93
94 --error ER_JSON_DOCUMENT_TOO_DEEP
95 SELECT JSON_SCHEMA_VALID(repeat('[', 100000), json_object());
96
97 --error ER_JSON_DOCUMENT_TOO_DEEP
98 SELECT JSON_SCHEMA_VALID(json_object(), repeat('[', 100000));
99
100 --echo # Check that we can use JSON_SCHEMA_VALID as a CHECK constraint.
101 CREATE TABLE t1 (
102 geometry JSON,
103 CHECK(JSON_SCHEMA_VALID('{
104 "type": "object",
105 "properties": {
106 "latitude": {
107 "type": "number",
108 "minimum": -90,
109 "maximum": 90
110 },
111 "longitude": {
112 "type": "number",
113 "minimum": -180,
114 "maximum": 180
115 }
116 },
117 "required": ["latitude", "longitude"]
118 }', geometry)
119 )
120 );
121
122 INSERT INTO t1 VALUES ('{"latitude": 0, "longitude": 0}');
123 INSERT INTO t1 VALUES ('{"latitude": -90, "longitude": -180}');
124 INSERT INTO t1 VALUES ('{"latitude": 90, "longitude": 180}');
125
126 SELECT geometry FROM t1;
127
128 --error ER_CHECK_CONSTRAINT_VIOLATED
129 INSERT INTO t1 VALUES ('{"latitude": 0}'); # missing required property "longitude"
130 --error ER_CHECK_CONSTRAINT_VIOLATED
131 INSERT INTO t1 VALUES ('{"latitude": 181, "longitude": 0}'); # latitude out of range
132
133 DROP TABLE t1;
134
135 --echo # Negative test for wrong number of arguments
136 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
137 SELECT JSON_SCHEMA_VALID();
138
139 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
140 SELECT JSON_SCHEMA_VALID(NULL);
141
142 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
143 SELECT JSON_SCHEMA_VALID(NULL, NULL, NULL);
144
145 --echo # Invalid JSON document in either argument
146 SET @invalid_json = '{"foo": "bar"'; # Missing closing '}'
147 SET @valid_json = '{}';
148
149 SELECT JSON_SCHEMA_VALID(@valid_json, @valid_json) AS should_be_true;
150
151 --error ER_INVALID_JSON_TEXT_IN_PARAM
152 SELECT JSON_SCHEMA_VALID(@invalid_json, @valid_json);
153
154 --error ER_INVALID_JSON_TEXT_IN_PARAM
155 SELECT JSON_SCHEMA_VALID(@valid_json, @invalid_json);
156
157 --error ER_INVALID_JSON_TEXT_IN_PARAM
158 SELECT JSON_SCHEMA_VALID(@invalid_json, @invalid_json);
159
160 --echo # Invalid regex patterns. rapidjson ignores invalid regex patterns, so
161 --echo # they are removed from the validation process.
162 SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"') AS should_be_true;
163
164 SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"[asdf@123"}', '"abc"') AS should_be_true;
165
166 --echo # Positive and negative tests for anchored regex patterns
167 SELECT JSON_SCHEMA_VALID('{
168 "type": "object",
169 "properties": {
170 "a_string": {
171 "type": "string",
172 "pattern": "[5-9]"
173 }
174 }
175 }','{
176 "a_string": "a8"
177 }') AS should_be_valid;
178
179 SELECT JSON_SCHEMA_VALID('{
180 "type": "object",
181 "properties": {
182 "a_string": {
183 "type": "string",
184 "pattern": "^[5-9]"
185 }
186 }
187 }','{
188 "a_string": "a8"
189 }') AS should_be_invalid;
190
191 SELECT JSON_SCHEMA_VALID('{
192 "type": "object",
193 "properties": {
194 "a_string": {
195 "type": "string",
196 "pattern": "[5-9]"
197 }
198 }
199 }','{
200 "a_string": "8a"
201 }') AS should_be_valid;
202
203 SELECT JSON_SCHEMA_VALID('{
204 "type": "object",
205 "properties": {
206 "a_string": {
207 "type": "string",
208 "pattern": "[5-9]$"
209 }
210 }
211 }','{
212 "a_string": "8a"
213 }') AS should_be_invalid;
214
215 SELECT JSON_SCHEMA_VALID('{
216 "type": "object",
217 "properties": {
218 "a_string": {
219 "type": "string",
220 "pattern": "^[5-9]$"
221 }
222 }
223 }','{
224 "a_string": "8"
225 }') AS should_be_valid;
226
227 SELECT JSON_SCHEMA_VALID('{
228 "type": "object",
229 "properties": {
230 "a_string": {
231 "type": "string",
232 "pattern": "^[5-9]$"
233 }
234 }
235 }','{
236 "a_string": "a8"
237 }') AS should_be_invalid;
238
239 SELECT JSON_SCHEMA_VALID('{
240 "type": "object",
241 "properties": {
242 "a_string": {
243 "type": "string",
244 "pattern": "^[5-9]$"
245 }
246 }
247 }','{
248 "a_string": "8a"
249 }') AS should_be_invalid;
250
251
252 --echo # The JSON Schema must be an object, so see that we don't accept any
253 --echo # other type as the first argument.
254 --error ER_INVALID_JSON_TYPE
255 SELECT JSON_SCHEMA_VALID('[]', '{}');
256
257 --error ER_INVALID_JSON_TEXT_IN_PARAM
258 SELECT JSON_SCHEMA_VALID(CAST('test' AS JSON), '{}');
259 --error ER_INVALID_JSON_TEXT_IN_PARAM
260 SELECT JSON_SCHEMA_VALID('{"type":"object"}', '{');
261
262 --echo # Test some various scenarios with cached JSON schema (first argument is const)
263 CREATE TABLE t1 (col1 JSON);
264 INSERT INTO t1 VALUES ('{"latitude": 0, "longitude": 0}');
265 INSERT INTO t1 VALUES ('{"latitude": -90, "longitude": -180}');
266 INSERT INTO t1 VALUES (NULL);
267 INSERT INTO t1 VALUES ('[]');
268 INSERT INTO t1 VALUES ('{"latitude": 90, "longitude": 180}');
269
270 --echo # All rows should return SQL NULL
271 SELECT JSON_SCHEMA_VALID(NULL, col1) FROM t1;
272
273 --echo # We should get true, true, null, false, true
274 SELECT JSON_SCHEMA_VALID('{"type":"object"}', col1) AS valid FROM t1;
275
276 --echo # Both arguments should be JSON
277 --error ER_INVALID_TYPE_FOR_JSON
278 SELECT JSON_SCHEMA_VALID(JSON_DEPTH(col1), col1) FROM t1;
279 --error ER_INVALID_TYPE_FOR_JSON
280 SELECT JSON_SCHEMA_VALID('{"type":"object"}', JSON_DEPTH(col1)) FROM t1;
281 --error ER_INVALID_TYPE_FOR_JSON
282 SELECT JSON_SCHEMA_VALID(123, col1) FROM t1;
283
284 DROP TABLE t1;
285
286 --echo # Test some scenarios with non-cached JSON schema (first argument is not const)
287 CREATE TABLE t1 (col1 JSON);
288 INSERT INTO t1 VALUES ('{"type":"object"}');
289 INSERT INTO t1 VALUES ('{"type":"array"}');
290 INSERT INTO t1 VALUES (NULL);
291 INSERT INTO t1 VALUES ('{"type":"string"}');
292
293 --echo # All rows should return SQL NULL
294 SELECT JSON_SCHEMA_VALID(col1, NULL) FROM t1;
295
296 --echo # We should get false, true, null, false
297 SELECT JSON_SCHEMA_VALID(col1, '[]') AS valid FROM t1;
298
299 DROP TABLE t1;
300
301 CREATE TABLE t1(s VARCHAR(100), d VARCHAR(100));
302 --echo # Invalid JSON document, non-const schema
303 INSERT INTO t1 VALUES('{"type":"object"}', '{');
304 --error ER_INVALID_JSON_TEXT_IN_PARAM
305 SELECT JSON_SCHEMA_VALID(s, d) FROM t1;
306 --echo # Invalid, non-const schema
307 UPDATE t1 SET s = '{', d = '{}';
308 --error ER_INVALID_JSON_TEXT_IN_PARAM
309 SELECT JSON_SCHEMA_VALID(s, d) FROM t1;
310 DROP TABLE t1;
311
312 --echo # Ensure that our item tree transformation doesn't get stuck forever when
313 --echo # using prepared statements.
314 PREPARE stmt FROM 'SELECT JSON_SCHEMA_VALID(?, ''{}'') FROM DUAL';
315 SET @json_schema = '{"type":"object"}';
316 SET @null = NULL;
317 EXECUTE stmt USING @json_schema;
318 EXECUTE stmt USING @null;
319 EXECUTE stmt USING @json_schema;
320
321 --echo #
322 --echo # Bug#29366780 WL#11999: SIG6 IN SETUP_FIELDS() AT SQL/SQL_BASE.CC
323 --echo #
324 --error ER_INVALID_JSON_TEXT_IN_PARAM
325 SELECT JSON_SCHEMA_VALID(CAST('NULL' AS JSON), CAST('NULL' AS JSON));
326
327 --echo # See that we don't accept non-JSON types like geometry, bool, ints etc.
328 --error ER_INVALID_TYPE_FOR_JSON
329 SELECT JSON_SCHEMA_VALID(JSON_OBJECT(), 123);
330 --error ER_INVALID_TYPE_FOR_JSON
331 SELECT JSON_SCHEMA_VALID(JSON_OBJECT(), POINT(1, 1));
332 --error ER_INVALID_TYPE_FOR_JSON
333 SELECT JSON_SCHEMA_VALID(JSON_OBJECT(), true);
334
335 --error ER_INVALID_TYPE_FOR_JSON
336 SELECT JSON_SCHEMA_VALID(123, JSON_OBJECT());
337 --error ER_INVALID_TYPE_FOR_JSON
338 SELECT JSON_SCHEMA_VALID(POINT(1, 1), JSON_OBJECT());
339 --error ER_INVALID_TYPE_FOR_JSON
340 SELECT JSON_SCHEMA_VALID(true, JSON_OBJECT());
341
342 --echo #
343 --echo # Bug#29524331: WL#11999: ASSERTION FAILURE: `!ARGS[0]->CONST_ITEM()'
344 --echo # Bug#29528888: WL#11999: SIG6 IN ITEM_FUNC_JSON_SCHEMA_VALID::VAL_BOOL()
345 --echo # AT ITEM_JSON_FUNC.CC
346 --echo #
347 CREATE TABLE t1 (pk INT PRIMARY KEY, j JSON);
348 INSERT INTO t1 VALUES (1, '{"key": "foobar"}' );
349 SELECT JSON_SCHEMA_VALID(j, j) FROM t1 WHERE pk = 1;
350 SELECT JSON_SCHEMA_VALID(t2.j, t2.j)
351 FROM t1, (SELECT * FROM t1 WHERE pk = 1) t2;
352 DROP TABLE t1;
353
354 --echo #
355 --echo # WL#13195 - TABLE WITH JSON SCHEMA VALIDATION CONSTRAINT SHOULD
356 --echo # RETURN ERROR FOR CONCRETE ROW
357 --echo #
358 CREATE TABLE t1 (id INT,
359 geometry JSON,
360 CHECK(JSON_SCHEMA_VALID('{
361 "type": "object",
362 "properties": {
363 "latitude": {
364 "type": "number",
365 "minimum": -90,
366 "maximum": 90
367 },
368 "longitude": {
369 "type": "number",
370 "minimum": -180,
371 "maximum": 180
372 }
373 },
374 "required": ["latitude", "longitude"]
375 }', geometry)
376 )
377 );
378
379 --echo # With WL changes, on CHECK constraint violation detailed information
380 --echo # about the failed JSON schema validation is pushed to diagnostics area.
381 --echo # SHOW WARNINGS and GET DIAGNOSTICS lists detailed information about the
382 --echo # failed schema validation.
383
384 --echo # CHECK constraint evaluation fails as required property "longitude" is
385 --echo # missing.
386 --error ER_CHECK_CONSTRAINT_VIOLATED
387 INSERT INTO t1 VALUES (1, '{"latitude": 0}');
388 SHOW WARNINGS;
389 GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
390 SELECT @p1, @p2;
391
392 --echo # CHECK constraint evaluation fails as latitude is out of range.
393 --error ER_CHECK_CONSTRAINT_VIOLATED
394 INSERT INTO t1 VALUES (1, '{"latitude": 181, "longitude": 0}');
395 SHOW WARNINGS;
396 GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
397 SELECT @p1, @p2;
398
399 --echo # CHECK constraint evaluation fails for 3rd row as latitude is out of
400 --echo # range.
401 --error ER_CHECK_CONSTRAINT_VIOLATED
402 INSERT INTO t1 VALUES (1, '{"latitude": 0, "longitude": 0}'),
403 (2, '{"latitude": -90, "longitude": -180}'),
404 (3, '{"latitude": 181, "longitude": 0}'),
405 (4, '{"latitude": 90, "longitude": 180}');
406 SHOW WARNINGS;
407 GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
408 SELECT @p1, @p2;
409
410 INSERT INTO t1 VALUES (1, '{"latitude": 0, "longitude": 0}'),
411 (2, '{"latitude": -90, "longitude": -180}'),
412 (3, '{"latitude": 90, "longitude": 180}');
413 --echo # CHECK constraint evaluation fails as required property "longitude"
414 --echo # is missing.
415 --error ER_CHECK_CONSTRAINT_VIOLATED
416 UPDATE t1 SET geometry = '{"latitude": 90}';
417 SHOW WARNINGS;
418 GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
419 SELECT @p1, @p2;
420
421 --echo # CHECK constraint evaluation fails as latitude is out of range.
422 --error ER_CHECK_CONSTRAINT_VIOLATED
423 UPDATE t1 SET geometry = '{"latitude": 181, "longitude": 0}';
424 SHOW WARNINGS;
425 GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
426 SELECT @p1, @p2;
427
428 --echo # CHECK constraint evaluation fails for 3rd row as latitude is out of
429 --echo # range.
430 --error ER_CHECK_CONSTRAINT_VIOLATED
431 UPDATE t1 SET geometry = IF (id = 3, '{"latitude": 181, "longitude": 0}',
432 '{"latitude": 90, "longitude": 0}');
433 SHOW WARNINGS;
434 GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
435 SELECT @p1, @p2;
436 DROP TABLE t1;
437
438 --echo # With ALTER TABLE and INSERT statements verify constraint evaluation
439 --echo # with NULL value.
440 CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);
441 INSERT INTO t1 VALUES (1), (2);
442 ALTER TABLE t1 ADD COLUMN jcol1 JSON,
443 ADD COLUMN jcol2 JSON,
444 ADD CONSTRAINT CHECK (json_schema_valid('{
445 "type": "object",
446 "properties": {
447 "id": {
448 "type": "integer"
449 },
450 "name": {
451 "type": "string"
452 },
453 "price": {
454 "type": "number",
455 "minimum": 0,
456 "exclusiveMinimum": true
457 }
458 },
459 "required": ["id","name","price"]}',`jcol1`)),
460 ADD CONSTRAINT CHECK (json_schema_valid('{
461 "type": "object",
462 "properties": {
463 "latitude": {
464 "type": "number",
465 "minimum": -90,
466 "maximum": 90
467 },
468 "longitude": {
469 "type": "number",
470 "minimum": -180,
471 "maximum": 180
472 }
473 },
474 "required": ["latitude", "longitude"]}',`jcol2`));
475 INSERT INTO t1 ( jcol1 ) VALUES ('{"id": 2, "name": "shiva", "price": 1 }');
476
477 --echo # With ALTER TABLE and INSERT statements verify constraint evaluation
478 --echo # with reference in JSON schema.
479 ALTER TABLE t1 ADD COLUMN jcol3 JSON,
480 ADD CONSTRAINT CHECK(json_schema_valid('{
481 "type": "object",
482 "properties": {
483 "id": {
484 "type": "integer"
485 },
486 "$ref": "http://example.com"
487 },
488 "required": ["id","$ref"]}',`jcol3`));
489 --error ER_CHECK_CONSTRAINT_VIOLATED
490 INSERT INTO t1 ( jcol3 ) VALUES ('{"id": 3, "$ref": "http://example.com"}');
491 SHOW WARNINGS;
492 DROP TABLE t1;
493
494 --echo #
495 --echo # Bug#30622327: JSON SCHEMA FUNCTIONS SHOULD CHECK ARGUMENT TYPES
496 --echo # AT RESOLVE TIME
497 --echo #
498 CREATE TABLE t(j JSON, g GEOMETRY);
499 --error ER_INVALID_TYPE_FOR_JSON
500 PREPARE ps FROM 'SELECT * FROM t WHERE JSON_SCHEMA_VALID(j, g)';
501 --error ER_INVALID_TYPE_FOR_JSON
502 PREPARE ps FROM 'SELECT * FROM t WHERE JSON_SCHEMA_VALID(g, j)';
503 DROP TABLE t;