1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
# See that NULL-handling is correct
SELECT JSON_SCHEMA_VALIDATION_REPORT(NULL, NULL);
JSON_SCHEMA_VALIDATION_REPORT(NULL, NULL)
NULL
SELECT JSON_SCHEMA_VALIDATION_REPORT(JSON_OBJECT(), NULL);
JSON_SCHEMA_VALIDATION_REPORT(JSON_OBJECT(), NULL)
NULL
SELECT JSON_SCHEMA_VALIDATION_REPORT(NULL, JSON_OBJECT());
JSON_SCHEMA_VALIDATION_REPORT(NULL, JSON_OBJECT())
NULL
# A few "happy" cases
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type": "object"}', '{}');
JSON_SCHEMA_VALIDATION_REPORT('{"type": "object"}', '{}')
{"valid": true}
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type": "array"}', '[]');
JSON_SCHEMA_VALIDATION_REPORT('{"type": "array"}', '[]')
{"valid": true}
# Cases where the JSON document is invalid
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type": "object"}', '[]');
JSON_SCHEMA_VALIDATION_REPORT('{"type": "object"}', '[]')
{"valid": false, "reason": "The JSON document location '#' failed requirement 'type' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "type"}
SELECT JSON_SCHEMA_VALIDATION_REPORT('{
"type": "object",
"properties": {
"geometry": {
"type": "object",
"properties": {
"latitude": {
"minimum": -90
}
}
}
}
}',
'{
"geometry": {
"longitude": -90,
"latitude": -180
}
}');
JSON_SCHEMA_VALIDATION_REPORT('{
"type": "object",
"properties": {
"geometry": {
"type": "object",
"properties": {
"latitude": {
"minimum": -90
}
}
}
}
}',
'{
"geometry": {
"longitude": -90,
{"valid": false, "reason": "The JSON document location '#/geometry/latitude' failed requirement 'minimum' at JSON Schema location '#/properties/geometry/properties/latitude'", "schema-location": "#/properties/geometry/properties/latitude", "document-location": "#/geometry/latitude", "schema-failed-keyword": "minimum"}
# A case where the JSON Schema is invalid
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type": "object-object"}', '{}');
JSON_SCHEMA_VALIDATION_REPORT('{"type": "object-object"}', '{}')
{"valid": false, "reason": "The JSON document location '#' failed requirement 'type' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "type"}
# Non-json JSON Schema input
SELECT JSON_SCHEMA_VALIDATION_REPORT(POINT(1, 1), '{}');
ERROR 22032: Invalid data type for JSON data in argument 1 to function json_schema_validation_report; a JSON string or JSON type is required.
SELECT JSON_SCHEMA_VALIDATION_REPORT('{ bar', '{}');
ERROR 22032: Invalid JSON text in argument 1 to function json_schema_validation_report: "Missing a name for object member." at position 2.
# Non-object JSON Schema input
SELECT JSON_SCHEMA_VALIDATION_REPORT('[]', '{}');
ERROR 22032: Invalid JSON type in argument 1 to function json_schema_validation_report; an object is required.
# Non-json JSON document input
SELECT JSON_SCHEMA_VALIDATION_REPORT('{}', POINT(1, 1));
ERROR 22032: Invalid data type for JSON data in argument 2 to function json_schema_validation_report; a JSON string or JSON type is required.
SELECT JSON_SCHEMA_VALIDATION_REPORT('{}', '{ bar');
ERROR 22032: Invalid JSON text in argument 2 to function json_schema_validation_report: "Missing a name for object member." at position 2.
# Wrong argument count
SELECT JSON_SCHEMA_VALIDATION_REPORT();
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_SCHEMA_VALIDATION_REPORT'
SELECT JSON_SCHEMA_VALIDATION_REPORT(NULL);
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_SCHEMA_VALIDATION_REPORT'
SELECT JSON_SCHEMA_VALIDATION_REPORT(NULL, NULL, NULL);
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_SCHEMA_VALIDATION_REPORT'
# Ensure that our item tree transformation doesn't get stuck forever when
# using prepared statements.
PREPARE stmt FROM 'SELECT JSON_SCHEMA_VALIDATION_REPORT(?, ''{}'') FROM DUAL';
SET @json_schema = '{"type":"object"}';
SET @null = NULL;
EXECUTE stmt USING @json_schema;
JSON_SCHEMA_VALIDATION_REPORT(?, '{}')
{"valid": true}
EXECUTE stmt USING @null;
JSON_SCHEMA_VALIDATION_REPORT(?, '{}')
NULL
EXECUTE stmt USING @json_schema;
JSON_SCHEMA_VALIDATION_REPORT(?, '{}')
{"valid": true}
#
# Bug#29529220: WL#13005: ASSERTION FAILURE: `!ARGS[0]->CONST_ITEM()'
# Bug#29528888: WL#11999: SIG6 IN ITEM_FUNC_JSON_SCHEMA_VALID::VAL_BOOL()
# AT ITEM_JSON_FUNC.CC
#
CREATE TABLE t1 (pk INT PRIMARY KEY, j JSON);
INSERT INTO t1 VALUES (1, '{"key": "foobar"}' );
SELECT JSON_SCHEMA_VALIDATION_REPORT(j, j) FROM t1 WHERE pk = 1;
JSON_SCHEMA_VALIDATION_REPORT(j, j)
{"valid": true}
SELECT JSON_SCHEMA_VALIDATION_REPORT(t2.j, t2.j)
FROM t1, (SELECT * FROM t1 WHERE pk = 1) t2;
JSON_SCHEMA_VALIDATION_REPORT(t2.j, t2.j)
{"valid": true}
DROP TABLE t1;
#
# Bug#30622327: JSON SCHEMA FUNCTIONS SHOULD CHECK ARGUMENT TYPES
# AT RESOLVE TIME
#
CREATE TABLE t(j JSON, g GEOMETRY);
PREPARE ps FROM 'SELECT * FROM t WHERE JSON_SCHEMA_VALIDATION_REPORT(j, g)';
ERROR 22032: Invalid data type for JSON data in argument 2 to function json_schema_validation_report; a JSON string or JSON type is required.
PREPARE ps FROM 'SELECT * FROM t WHERE JSON_SCHEMA_VALIDATION_REPORT(g, j)';
ERROR 22032: Invalid data type for JSON data in argument 1 to function json_schema_validation_report; a JSON string or JSON type is required.
DROP TABLE t;
#
# Bug#32047630: MISSING ERROR PROPAGATION IN JSON FUNCTIONS
#
SELECT JSON_ARRAY(JSON_SCHEMA_VALIDATION_REPORT('{}', '{'), 1);
ERROR 22032: Invalid JSON text in argument 2 to function json_schema_validation_report: "Missing a name for object member." at position 1.
SELECT JSON_ARRAY(JSON_SCHEMA_VALIDATION_REPORT('{}', CAST('{' AS JSON)), 1);
ERROR 22032: Invalid JSON text in argument 1 to function cast_as_json: "Missing a name for object member." at position 1.
SELECT JSON_ARRAY(JSON_SCHEMA_VALIDATION_REPORT(
CAST(CONCAT('{', RAND()) AS JSON), '{}'), 1);
ERROR 22032: Invalid JSON text in argument 1 to function cast_as_json: "Missing a name for object member." at position 1.
|
