Debian Codegraph @

mysql-test/suite/json/r/json_value.result

raw
1 #
2 # WL#12228: Add JSON_VALUE function
3 #
4 SELECT JSON_VALUE('{"data": "asdf"}', '$.data') v;
5 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
6 def v 253 2048 4 Y 128 31 255
7 v
8 asdf
9 SELECT JSON_VALUE('{"data": 123.45}', '$.data') v;
10 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
11 def v 253 2048 6 Y 128 31 255
12 v
13 123.45
14 SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING CHAR) v;
15 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
16 def v 251 4294967295 4 Y 128 31 255
17 v
18 asdf
19 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR) v;
20 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
21 def v 251 4294967295 6 Y 128 31 255
22 v
23 123.45
24 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DECIMAL) v;
25 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
26 def v 246 11 3 Y 32896 0 63
27 v
28 123
29 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DECIMAL(6,4)) v;
30 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
31 def v 246 8 0 Y 32896 4 63
32 v
33 NULL
34 SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING BINARY(4)) v;
35 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
36 def v 253 4 4 Y 128 31 63
37 v
38 asdf
39 SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING BINARY) v;
40 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
41 def v 251 4294967295 4 Y 128 31 63
42 v
43 asdf
44 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING FLOAT) v;
45 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
46 def v 4 23 6 Y 32896 31 63
47 v
48 123.45
49 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DOUBLE) v;
50 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
51 def v 5 23 6 Y 32896 31 63
52 v
53 123.45
54 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING UNSIGNED) v;
55 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
56 def v 8 21 3 Y 32928 0 63
57 v
58 123
59 SELECT JSON_VALUE('{"data": -123.45}','$.data' RETURNING UNSIGNED) v;
60 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
61 def v 8 21 0 Y 32928 0 63
62 v
63 NULL
64 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING SIGNED) v;
65 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
66 def v 8 21 3 Y 32896 0 63
67 v
68 123
69 SELECT JSON_VALUE('{"data": -123.45}', '$.data' RETURNING SIGNED) v;
70 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
71 def v 8 21 4 Y 32896 0 63
72 v
73 -123
74 SELECT JSON_VALUE('{"data": 1999}', '$.data' RETURNING YEAR) v;
75 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
76 def v 13 5 4 Y 32896 0 63
77 v
78 1999
79 SELECT JSON_VALUE('{"data": -1999}', '$.data' RETURNING YEAR) v;
80 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
81 def v 13 5 0 Y 32896 0 63
82 v
83 NULL
84 SELECT JSON_VALUE(JSON_OBJECT('data', 1.0e0/3), '$.data' RETURNING FLOAT) v;
85 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
86 def v 4 23 8 Y 32896 31 63
87 v
88 0.333333
89 SELECT JSON_VALUE(JSON_OBJECT('data', 1.0e0/3), '$.data' RETURNING DOUBLE) v;
90 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
91 def v 5 23 18 Y 32896 31 63
92 v
93 0.3333333333333333
94 SELECT COLLATION(JSON_VALUE('{"x": "y"}', '$.x')) AS col1,
95 COLLATION(JSON_VALUE('{"x": "y"}', '$.x' RETURNING CHAR(10))) AS col2,
96 COLLATION(JSON_VALUE('{"x": "y"}',
97 '$.x' RETURNING CHAR(10) CHARSET utf8mb4)) AS col3;
98 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
99 def col1 253 256 16 Y 0 31 255
100 def col2 253 256 16 Y 0 31 255
101 def col3 253 256 18 Y 0 31 255
102 col1 col2 col3
103 utf8mb4_0900_bin utf8mb4_0900_bin utf8mb4_0900_ai_ci
104 SELECT JSON_VALUE('{"data": "2019-01-01"}', '$.data' RETURNING DATE);
105 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
106 def JSON_VALUE('{"data": "2019-01-01"}', '$.data' RETURNING DATE) 10 40 10 Y 0 0 255
107 JSON_VALUE('{"data": "2019-01-01"}', '$.data' RETURNING DATE)
108 2019-01-01
109 SELECT JSON_VALUE(JSON_OBJECT('data', CAST( DATE'2019-01-01' AS JSON)),
110 '$.data' RETURNING DATE);
111 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
112 def JSON_VALUE(JSON_OBJECT('data', CAST( DATE'2019-01-01' AS JSON)),
113 '$.data' RETURNING DATE) 10 40 10 Y 0 0 255
114 JSON_VALUE(JSON_OBJECT('data', CAST( DATE'2019-01-01' AS JSON)),
115 '$.data' RETURNING DATE)
116 2019-01-01
117 SELECT JSON_VALUE('{"data": "11:11:11"}', '$.data' RETURNING TIME);
118 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
119 def JSON_VALUE('{"data": "11:11:11"}', '$.data' RETURNING TIME) 11 40 8 Y 0 0 255
120 JSON_VALUE('{"data": "11:11:11"}', '$.data' RETURNING TIME)
121 11:11:11
122 SELECT JSON_VALUE(JSON_OBJECT('data', CAST( TIME'11:11:11' AS JSON)),
123 '$.data' RETURNING TIME);
124 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
125 def JSON_VALUE(JSON_OBJECT('data', CAST( TIME'11:11:11' AS JSON)),
126 '$.data' RETURNING TIME) 11 40 8 Y 0 0 255
127 JSON_VALUE(JSON_OBJECT('data', CAST( TIME'11:11:11' AS JSON)),
128 '$.data' RETURNING TIME)
129 11:11:11
130 SELECT JSON_VALUE('{"data": "2019-01-01 11:11:11"}',
131 '$.data' RETURNING DATETIME);
132 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
133 def JSON_VALUE('{"data": "2019-01-01 11:11:11"}',
134 '$.data' RETURNING DATETIME) 12 76 19 Y 0 0 255
135 JSON_VALUE('{"data": "2019-01-01 11:11:11"}',
136 '$.data' RETURNING DATETIME)
137 2019-01-01 11:11:11
138 SELECT JSON_VALUE(
139 JSON_OBJECT('data', CAST(TIMESTAMP'2019-01-01 11:11:11' AS JSON)),
140 '$.data' RETURNING DATETIME);
141 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
142 def JSON_VALUE(
143 JSON_OBJECT('data', CAST(TIMESTAMP'2019-01-01 11:11:11' AS JSON)),
144 '$.data' RETURNING DATETIME) 12 76 19 Y 0 0 255
145 JSON_VALUE(
146 JSON_OBJECT('data', CAST(TIMESTAMP'2019-01-01 11:11:11' AS JSON)),
147 '$.data' RETURNING DATETIME)
148 2019-01-01 11:11:11
149 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR(10));
150 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
151 def JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR(10)) 253 40 6 Y 128 31 255
152 JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR(10))
153 123.45
154 SELECT JSON_VALUE('{"data": "1234567890"}', '$.data' RETURNING CHAR(10));
155 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
156 def JSON_VALUE('{"data": "1234567890"}', '$.data' RETURNING CHAR(10)) 253 40 10 Y 128 31 255
157 JSON_VALUE('{"data": "1234567890"}', '$.data' RETURNING CHAR(10))
158 1234567890
159 SELECT JSON_VALUE('{"data": "12345678901"}', '$.data' RETURNING CHAR(10));
160 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
161 def JSON_VALUE('{"data": "12345678901"}', '$.data' RETURNING CHAR(10)) 253 40 0 Y 128 31 255
162 JSON_VALUE('{"data": "12345678901"}', '$.data' RETURNING CHAR(10))
163 NULL
164 SELECT JSON_VALUE('{"data": "1234567890"}',
165 '$.data' RETURNING CHAR(10) CHARSET utf8mb4);
166 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
167 def JSON_VALUE('{"data": "1234567890"}',
168 '$.data' RETURNING CHAR(10) CHARSET utf8mb4) 253 40 10 Y 0 31 255
169 JSON_VALUE('{"data": "1234567890"}',
170 '$.data' RETURNING CHAR(10) CHARSET utf8mb4)
171 1234567890
172 SELECT JSON_VALUE('{"data": "123"}', '$.data' RETURNING JSON);
173 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
174 def JSON_VALUE('{"data": "123"}', '$.data' RETURNING JSON) 245 4294967292 5 Y 128 31 255
175 JSON_VALUE('{"data": "123"}', '$.data' RETURNING JSON)
176 "123"
177 SELECT JSON_VALUE('{}', NULL);
178 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL)' at line 1
179 SELECT JSON_VALUE('{}', '$.x' DEFAULT NULL ON EMPTY);
180 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON EMPTY)' at line 1
181 SELECT JSON_VALUE('{}', '$.x' DEFAULT NULL ON ERROR);
182 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON ERROR)' at line 1
183 SELECT JSON_VALUE(1, '$');
184 ERROR 22032: Invalid data type for JSON data in argument 1 to function json_value; a JSON string or JSON type is required.
185 SELECT JSON_VALUE(CAST('[]' AS BINARY), '$');
186 ERROR 22032: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
187 SELECT JSON_VALUE('{"data": [123]}', '$.data' RETURNING JSON) v;
188 v
189 [123]
190 SELECT JSON_VALUE('{"data": {"val":123}}', '$.data' RETURNING JSON) v;
191 v
192 {"val": 123}
193 SELECT JSON_VALUE('[1,2,3]', '$') v;
194 v
195 [1, 2, 3]
196 SELECT JSON_VALUE('{"a":"b"}', '$') v;
197 v
198 {"a": "b"}
199 SELECT JSON_VALUE('1', '$' RETURNING TIME(1000));
200 ERROR 42000: Too-big precision 1000 specified for 'CAST'. Maximum is 6.
201 SELECT JSON_VALUE('{"data": [123]}', '$.data' RETURNING SIGNED) v;
202 v
203 NULL
204 SELECT JSON_VALUE('{"data": {"x":"y"}}', '$.data' RETURNING SIGNED) v;
205 v
206 NULL
207 SELECT JSON_VALUE('{"data": [123]}',
208 '$.data' RETURNING SIGNED DEFAULT 456 ON ERROR) v;
209 v
210 456
211 # Wildcards are OK if they match a single value.
212 SELECT JSON_VALUE('["abc"]', '$[*]') v1,
213 JSON_VALUE('[1]', '$[*]' RETURNING SIGNED) v2,
214 JSON_VALUE('[1]', '$[*]' RETURNING UNSIGNED) v3,
215 JSON_VALUE('["2020-01-01"]', '$[*]' RETURNING DATE) v4,
216 JSON_VALUE('["12:00:00"]', '$[*]' RETURNING TIME) v5,
217 JSON_VALUE('["2020-01-01 12:00:00"]', '$[*]' RETURNING DATETIME) v6,
218 JSON_VALUE('[1]', '$[*]' RETURNING DECIMAL) v7,
219 JSON_VALUE('[{}]', '$[*]' RETURNING JSON) v8,
220 JSON_VALUE('[1]', '$[*]' RETURNING FLOAT) v9,
221 JSON_VALUE('[1]', '$[*]' RETURNING DOUBLE) v10;
222 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10
223 abc 1 1 2020-01-01 12:00:00 2020-01-01 12:00:00 1 {} 1 1
224 # Paths matching multiple values should invoke ON ERROR.
225 SELECT JSON_VALUE('["abc", "def"]', '$[*]') v1,
226 JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED) v2,
227 JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED) v3,
228 JSON_VALUE('["2020-01-01", "2020-01-02"]', '$[*]' RETURNING DATE) v4,
229 JSON_VALUE('["12:00:00", "13:00:00"]', '$[*]' RETURNING TIME) v5,
230 JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
231 '$[*]' RETURNING DATETIME) v6,
232 JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL) v7,
233 JSON_VALUE('[{}, []]', '$[*]' RETURNING JSON) v8,
234 JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT) v9,
235 JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE) v10;
236 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10
237 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
238 SELECT JSON_VALUE('["abc", "def"]', '$[*]' DEFAULT 'error' ON ERROR) v1,
239 JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED DEFAULT 9 ON ERROR) v2,
240 JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED DEFAULT 9 ON ERROR) v3,
241 JSON_VALUE('["2020-01-01", "2020-01-02"]',
242 '$[*]' RETURNING DATE DEFAULT DATE'1999-12-31' ON ERROR) v4,
243 JSON_VALUE('["12:00:00", "13:00:00"]',
244 '$[*]' RETURNING TIME DEFAULT TIME'10:20:30' ON ERROR) v5,
245 JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
246 '$[*]' RETURNING DATETIME
247 DEFAULT '1999-12-31 10:20:30' ON ERROR) v6,
248 JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL DEFAULT 9 ON ERROR) v7,
249 JSON_VALUE('[{}, []]',
250 '$[*]' RETURNING JSON DEFAULT '"error"' ON ERROR) v8,
251 JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT DEFAULT 9 ON ERROR) v9,
252 JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE DEFAULT 9 ON ERROR) v10;
253 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10
254 error 9 9 1999-12-31 10:20:30 1999-12-31 10:20:30 9 "error" 9 9
255 SELECT JSON_VALUE('["abc", "def"]', '$[*]' ERROR ON ERROR) v;
256 ERROR 22034: More than one value was found by 'json_value' on the specified path.
257 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED ERROR ON ERROR) v;
258 ERROR 22034: More than one value was found by 'json_value' on the specified path.
259 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED ERROR ON ERROR) v;
260 ERROR 22034: More than one value was found by 'json_value' on the specified path.
261 SELECT JSON_VALUE('["2020-01-01", "2020-01-02"]',
262 '$[*]' RETURNING DATE ERROR ON ERROR) v;
263 ERROR 22034: More than one value was found by 'json_value' on the specified path.
264 SELECT JSON_VALUE('["12:00:00", "13:00:00"]',
265 '$[*]' RETURNING TIME ERROR ON ERROR) v;
266 ERROR 22034: More than one value was found by 'json_value' on the specified path.
267 SELECT JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
268 '$[*]' RETURNING DATETIME ERROR ON ERROR) v;
269 ERROR 22034: More than one value was found by 'json_value' on the specified path.
270 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL ERROR ON ERROR) v;
271 ERROR 22034: More than one value was found by 'json_value' on the specified path.
272 SELECT JSON_VALUE('[{}, []]', '$[*]' RETURNING JSON ERROR ON ERROR) v;
273 ERROR 22034: More than one value was found by 'json_value' on the specified path.
274 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT ERROR ON ERROR) v;
275 ERROR 22034: More than one value was found by 'json_value' on the specified path.
276 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE ERROR ON ERROR) v;
277 ERROR 22034: More than one value was found by 'json_value' on the specified path.
278 # Max length check should use char length, not byte length.
279 SELECT JSON_VALUE('"æøå"', '$' RETURNING CHAR(3)) v;
280 v
281 æøå
282 SELECT JSON_VALUE('"æøåa"', '$' RETURNING CHAR(3) DEFAULT 'ÆØÅ' ON ERROR) v;
283 v
284 ÆØÅ
285 # Charset conversion errors should activate the ON ERROR clause.
286 SELECT JSON_VALUE('"æøå"', '$' RETURNING CHAR(10) CHARSET ascii
287 DEFAULT 'error' ON ERROR) v;
288 v
289 error
290 SELECT JSON_VALUE(NULL, '$.x') v1,
291 JSON_VALUE(NULL, '$.x' RETURNING SIGNED) v2,
292 JSON_VALUE(NULL, '$.x' RETURNING UNSIGNED) v3,
293 JSON_VALUE(NULL, '$.x' RETURNING DATE) v4,
294 JSON_VALUE(NULL, '$.x' RETURNING TIME) v5,
295 JSON_VALUE(NULL, '$.x' RETURNING DATETIME) v6,
296 JSON_VALUE(NULL, '$.x' RETURNING DECIMAL) v7,
297 JSON_VALUE(NULL, '$.x' RETURNING JSON) v8,
298 JSON_VALUE(NULL, '$.x' RETURNING FLOAT) v9,
299 JSON_VALUE(NULL, '$.x' RETURNING DOUBLE) v10,
300 JSON_VALUE(NULL, '$.x' RETURNING YEAR) v11;
301 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11
302 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
303 SELECT JSON_VALUE('not json', '$.x') v1,
304 JSON_VALUE('not json', '$.x' RETURNING SIGNED) v2,
305 JSON_VALUE('not json', '$.x' RETURNING UNSIGNED) v3,
306 JSON_VALUE('not json', '$.x' RETURNING DATE) v4,
307 JSON_VALUE('not json', '$.x' RETURNING TIME) v5,
308 JSON_VALUE('not json', '$.x' RETURNING DATETIME) v6,
309 JSON_VALUE('not json', '$.x' RETURNING DECIMAL) v7,
310 JSON_VALUE('not json', '$.x' RETURNING JSON) v8,
311 JSON_VALUE('not json', '$.x' RETURNING FLOAT) v9,
312 JSON_VALUE('not json', '$.x' RETURNING DOUBLE) v10,
313 JSON_VALUE('not json', '$.x' RETURNING YEAR) v11;
314 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11
315 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
316 Warnings:
317 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
318 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
319 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
320 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
321 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
322 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
323 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
324 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
325 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
326 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
327 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
328 SELECT JSON_VALUE('not json', '$.x') v1,
329 JSON_VALUE('not json', '$.x' RETURNING SIGNED NULL ON ERROR) v2,
330 JSON_VALUE('not json', '$.x' RETURNING UNSIGNED NULL ON ERROR) v3,
331 JSON_VALUE('not json', '$.x' RETURNING DATE NULL ON ERROR) v4,
332 JSON_VALUE('not json', '$.x' RETURNING TIME NULL ON ERROR) v5,
333 JSON_VALUE('not json', '$.x' RETURNING DATETIME NULL ON ERROR) v6,
334 JSON_VALUE('not json', '$.x' RETURNING DECIMAL NULL ON ERROR) v7,
335 JSON_VALUE('not json', '$.x' RETURNING JSON NULL ON ERROR) v8,
336 JSON_VALUE('not json', '$.x' RETURNING FLOAT NULL ON ERROR) v9,
337 JSON_VALUE('not json', '$.x' RETURNING DOUBLE NULL ON ERROR) v10,
338 JSON_VALUE('not json', '$.x' RETURNING YEAR NULL ON ERROR) v11;
339 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11
340 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
341 Warnings:
342 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
343 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
344 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
345 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
346 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
347 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
348 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
349 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
350 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
351 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
352 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
353 SELECT JSON_VALUE('not json', '$.x' DEFAULT 'error' ON ERROR) v1,
354 JSON_VALUE('not json', '$.x' RETURNING SIGNED DEFAULT 123 ON ERROR) v2,
355 JSON_VALUE('not json', '$.x' RETURNING UNSIGNED DEFAULT 123 ON ERROR) v3,
356 JSON_VALUE('not json', '$.x' RETURNING DATE
357 DEFAULT '2020-01-01' ON ERROR) v4,
358 JSON_VALUE('not json', '$.x' RETURNING TIME
359 DEFAULT '10:11:12' ON ERROR) v5,
360 JSON_VALUE('not json', '$.x' RETURNING DATETIME
361 DEFAULT '2020-01-01 12:13:14' ON ERROR) v6,
362 JSON_VALUE('not json', '$.x' RETURNING DECIMAL DEFAULT 123 ON ERROR) v7,
363 JSON_VALUE('not json', '$.x' RETURNING JSON
364 DEFAULT '[1,2,3]' ON ERROR) v8,
365 JSON_VALUE('not json', '$.x' RETURNING FLOAT DEFAULT 123 ON ERROR) v9,
366 JSON_VALUE('not json', '$.x' RETURNING DOUBLE DEFAULT 123 ON ERROR) v10,
367 JSON_VALUE('not json', '$.x' RETURNING YEAR DEFAULT 1988 ON ERROR) v11;
368 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11
369 error 123 123 2020-01-01 10:11:12 2020-01-01 12:13:14 123 [1, 2, 3] 123 123 1988
370 Warnings:
371 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
372 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
373 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
374 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
375 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
376 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
377 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
378 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
379 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
380 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
381 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
382 SELECT JSON_VALUE('not json', '$.x' ERROR ON EMPTY ERROR ON ERROR) v;
383 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
384 SELECT JSON_VALUE('not json', '$.x' RETURNING SIGNED
385 ERROR ON EMPTY ERROR ON ERROR) v;
386 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
387 SELECT JSON_VALUE('not json', '$.x' RETURNING UNSIGNED
388 ERROR ON EMPTY ERROR ON ERROR) v;
389 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
390 SELECT JSON_VALUE('not json', '$.x' RETURNING DATE
391 ERROR ON EMPTY ERROR ON ERROR) v;
392 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
393 SELECT JSON_VALUE('not json', '$.x' RETURNING TIME
394 ERROR ON EMPTY ERROR ON ERROR) v;
395 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
396 SELECT JSON_VALUE('not json', '$.x' RETURNING DATETIME
397 ERROR ON EMPTY ERROR ON ERROR) v;
398 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
399 SELECT JSON_VALUE('not json', '$.x' RETURNING DECIMAL
400 ERROR ON EMPTY ERROR ON ERROR) v;
401 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
402 SELECT JSON_VALUE('not json', '$.x' RETURNING JSON
403 ERROR ON EMPTY ERROR ON ERROR) v;
404 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
405 SELECT JSON_VALUE('not json', '$.x' RETURNING FLOAT
406 ERROR ON EMPTY ERROR ON ERROR) v;
407 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
408 SELECT JSON_VALUE('not json', '$.x' RETURNING DOUBLE
409 ERROR ON EMPTY ERROR ON ERROR) v;
410 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
411 SELECT JSON_VALUE('not json', '$.x' RETURNING YEAR
412 ERROR ON EMPTY ERROR ON ERROR) v;
413 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
414 SELECT JSON_VALUE(CAST(JSON_EXTRACT('not json', '$') AS CHAR), '$') v;
415 ERROR 22032: Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 1.
416 SELECT JSON_VALUE(REPEAT('[', 200), '$') v;
417 ERROR 22032: The JSON document exceeds the maximum depth.
418 CREATE VIEW v AS
419 SELECT JSON_VALUE('{"don''t":"panic"}', '$."don''t"') AS x,
420 JSON_VALUE('""', '$' RETURNING CHAR(10)
421 DEFAULT 'it''s empty' ON EMPTY
422 DEFAULT 'it''s wrong' ON ERROR) AS y,
423 JSON_VALUE('""', '$' NULL ON EMPTY ERROR ON ERROR) AS z,
424 JSON_VALUE('""', '$' ERROR ON EMPTY NULL ON ERROR) AS w;
425 SHOW CREATE VIEW v;
426 View Create View character_set_client collation_connection
427 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select json_value('{"don\'t":"panic"}', '$."don\'t"' returning char(512)) AS `x`,json_value('""', '$' returning char(10) default 'it\'s empty' on empty default 'it\'s wrong' on error) AS `y`,json_value('""', '$' returning char(512) null on empty error on error) AS `z`,json_value('""', '$' returning char(512) error on empty null on error) AS `w` utf8mb4 utf8mb4_0900_ai_ci
428 SELECT * FROM v;
429 x y z w
430 panic
431 DROP VIEW v;
432 CREATE TABLE json_value(json_value JSON);
433 SELECT JSON_VALUE(json_value, '$.a') AS json_value FROM json_value;
434 json_value
435 DROP TABLE json_value;
436 CREATE TABLE returning(returning JSON);
437 SELECT JSON_VALUE(returning, '$.a' RETURNING CHAR) AS returning FROM returning;
438 returning
439 DROP TABLE returning;
440 # Test ON EMPTY clause
441 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED) v;
442 v
443 NULL
444 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED NULL ON EMPTY) v;
445 v
446 NULL
447 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED ERROR ON EMPTY);
448 ERROR 22035: No value was found by 'json_value' on the specified path.
449 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
450 DEFAULT 456 ON EMPTY);
451 JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
452 DEFAULT 456 ON EMPTY)
453 456
454 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
455 DEFAULT '[456]' ON EMPTY);
456 ERROR 22007: Truncated incorrect INTEGER SIGNED value: '[456]'
457 SELECT JSON_VALUE('{"data": 123}',
458 '$.num' RETURNING SIGNED NULL ON EMPTY NULL ON EMPTY);
459 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EMPTY)' at line 2
460 SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET utf16
461 DEFAULT 'abc' ON EMPTY) v;
462 v
463 abc
464 SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET ascii
465 DEFAULT 'abc' ON EMPTY) v;
466 v
467 abc
468 SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET ascii
469 DEFAULT 'æøå' ON EMPTY) v;
470 ERROR 22003: CHAR DEFAULT value is out of range in 'json_value'
471 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
472 DEFAULT 'asdf' ON EMPTY);
473 ERROR 22007: Truncated incorrect INTEGER SIGNED value: 'asdf'
474 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING UNSIGNED
475 DEFAULT 'asdf' ON EMPTY);
476 ERROR 22007: Truncated incorrect INTEGER UNSIGNED value: 'asdf'
477 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING CHAR(2)
478 DEFAULT 'asdf' ON EMPTY);
479 ERROR 22003: CHAR DEFAULT value is out of range in 'json_value'
480 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING FLOAT
481 DEFAULT 'asdf' ON EMPTY);
482 ERROR 22007: Truncated incorrect DOUBLE value: 'asdf'
483 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DOUBLE
484 DEFAULT 'asdf' ON EMPTY);
485 ERROR 22007: Truncated incorrect DOUBLE value: 'asdf'
486 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DATE
487 DEFAULT 'asdf' ON EMPTY);
488 ERROR 22007: Incorrect datetime value: 'asdf'
489 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING TIME
490 DEFAULT 'asdf' ON EMPTY);
491 ERROR 22007: Truncated incorrect time value: 'asdf'
492 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DATETIME
493 DEFAULT 'asdf' ON EMPTY);
494 ERROR 22007: Incorrect datetime value: 'asdf'
495 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DECIMAL
496 DEFAULT 'asdf' ON EMPTY);
497 ERROR 22007: Truncated incorrect DECIMAL value: 'asdf'
498 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING YEAR
499 DEFAULT 'asdf' ON EMPTY);
500 ERROR 22007: Truncated incorrect YEAR value: 'asdf'
501 SELECT JSON_VALUE('""', '$' RETURNING JSON DEFAULT 'not JSON' ON EMPTY);
502 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
503 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING SIGNED
504 DEFAULT 'asdf' ON ERROR);
505 ERROR 22007: Truncated incorrect INTEGER SIGNED value: 'asdf'
506 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING UNSIGNED
507 DEFAULT 'asdf' ON ERROR);
508 ERROR 22007: Truncated incorrect INTEGER UNSIGNED value: 'asdf'
509 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING CHAR(2)
510 DEFAULT 'asdf' ON ERROR);
511 ERROR 22003: CHAR DEFAULT value is out of range in 'json_value'
512 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING FLOAT
513 DEFAULT 'asdf' ON ERROR);
514 ERROR 22007: Truncated incorrect DOUBLE value: 'asdf'
515 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DOUBLE
516 DEFAULT 'asdf' ON ERROR);
517 ERROR 22007: Truncated incorrect DOUBLE value: 'asdf'
518 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DATE
519 DEFAULT 'asdf' ON ERROR);
520 ERROR 22007: Incorrect datetime value: 'asdf'
521 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING TIME
522 DEFAULT 'asdf' ON ERROR);
523 ERROR 22007: Truncated incorrect time value: 'asdf'
524 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DATETIME
525 DEFAULT 'asdf' ON ERROR);
526 ERROR 22007: Incorrect datetime value: 'asdf'
527 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DECIMAL
528 DEFAULT 'asdf' ON ERROR);
529 ERROR 22007: Truncated incorrect DECIMAL value: 'asdf'
530 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING YEAR
531 DEFAULT 'asdf' ON ERROR);
532 ERROR 22007: Truncated incorrect YEAR value: 'asdf'
533 SELECT JSON_VALUE('""', '$' RETURNING JSON DEFAULT 'not JSON' ON ERROR);
534 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
535 # Test ON ERROR clause
536 SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED);
537 JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED)
538 NULL
539 SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED NULL ON ERROR);
540 JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED NULL ON ERROR)
541 NULL
542 SELECT JSON_VALUE('{"data": "asd"}',
543 '$.data' RETURNING SIGNED NULL ON ERROR NULL ON ERROR);
544 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON ERROR)' at line 2
545 SELECT JSON_VALUE('{"data": 123', '$.data' RETURNING SIGNED ERROR ON ERROR);
546 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Missing a comma or '}' after an object member." at position 12.
547 SELECT JSON_VALUE('{"data": 123}', '.data' RETURNING SIGNED ERROR ON ERROR);
548 ERROR 42000: Invalid JSON path expression. The error is around character position 1.
549 SELECT JSON_VALUE('{"data": "asd"}', '$.data'
550 RETURNING SIGNED DEFAULT '{"aaa":321}' ON ERROR);
551 ERROR 22007: Truncated incorrect INTEGER SIGNED value: '{"aaa":321}'
552 SELECT JSON_VALUE('{"data": "asd"}', '$.data'
553 RETURNING SIGNED DEFAULT 321 ON ERROR);
554 JSON_VALUE('{"data": "asd"}', '$.data'
555 RETURNING SIGNED DEFAULT 321 ON ERROR)
556 321
557 SELECT JSON_VALUE('{"data": "123.45"}', '$.data' RETURNING SIGNED);
558 JSON_VALUE('{"data": "123.45"}', '$.data' RETURNING SIGNED)
559 NULL
560 SELECT JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
561 RETURNING SIGNED);
562 JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
563 RETURNING SIGNED)
564 NULL
565 SELECT JSON_VALUE('{"data": "-99999"}', '$.data' RETURNING UNSIGNED);
566 JSON_VALUE('{"data": "-99999"}', '$.data' RETURNING UNSIGNED)
567 NULL
568 SELECT JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
569 RETURNING CHAR(10));
570 JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
571 RETURNING CHAR(10))
572 NULL
573 SELECT JSON_VALUE('"abc"', '$' RETURNING DATE DEFAULT '2000-01-02' ON ERROR);
574 JSON_VALUE('"abc"', '$' RETURNING DATE DEFAULT '2000-01-02' ON ERROR)
575 2000-01-02
576 SELECT JSON_VALUE('"abc"', '$' RETURNING DATE
577 DEFAULT DATE'2000-01-02' ON ERROR);
578 JSON_VALUE('"abc"', '$' RETURNING DATE
579 DEFAULT DATE'2000-01-02' ON ERROR)
580 2000-01-02
581 SELECT JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT '12:13:14' ON ERROR);
582 JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT '12:13:14' ON ERROR)
583 12:13:14
584 SELECT JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT TIME'12:13:14' ON ERROR);
585 JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT TIME'12:13:14' ON ERROR)
586 12:13:14
587 SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME
588 DEFAULT '2000-01-02 12:13:14' ON ERROR);
589 JSON_VALUE('"abc"', '$' RETURNING DATETIME
590 DEFAULT '2000-01-02 12:13:14' ON ERROR)
591 2000-01-02 12:13:14
592 SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME
593 DEFAULT TIMESTAMP'2000-01-02 12:13:14' ON ERROR);
594 JSON_VALUE('"abc"', '$' RETURNING DATETIME
595 DEFAULT TIMESTAMP'2000-01-02 12:13:14' ON ERROR)
596 2000-01-02 12:13:14
597 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) DEFAULT 'x' ON ERROR);
598 JSON_VALUE('"abc"', '$' RETURNING CHAR(1) DEFAULT 'x' ON ERROR)
599 x
600 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET utf16
601 DEFAULT 'x' ON ERROR) v;
602 v
603 x
604 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET ascii
605 DEFAULT 'a' ON ERROR) v;
606 v
607 a
608 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET ascii
609 DEFAULT 'æ' ON ERROR) v;
610 ERROR 22003: CHAR DEFAULT value is out of range in 'json_value'
611 SELECT JSON_VALUE('"abc"', '$' RETURNING DECIMAL DEFAULT 123 ON ERROR);
612 JSON_VALUE('"abc"', '$' RETURNING DECIMAL DEFAULT 123 ON ERROR)
613 123
614 SELECT JSON_VALUE('"abc"', '$' RETURNING FLOAT DEFAULT 123 ON ERROR);
615 JSON_VALUE('"abc"', '$' RETURNING FLOAT DEFAULT 123 ON ERROR)
616 123
617 SELECT JSON_VALUE('"abc"', '$' RETURNING DOUBLE DEFAULT 123 ON ERROR);
618 JSON_VALUE('"abc"', '$' RETURNING DOUBLE DEFAULT 123 ON ERROR)
619 123
620 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 1988 ON ERROR);
621 JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 1988 ON ERROR)
622 1988
623 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 1 ON ERROR);
624 ERROR 22003: YEAR value is out of range in 'json_value'
625 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 2270 ON ERROR);
626 ERROR 22003: YEAR value is out of range in 'json_value'
627 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT -1 ON ERROR);
628 ERROR 22003: YEAR value is out of range in 'json_value'
629 SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED ERROR ON ERROR);
630 ERROR 22003: SIGNED value is out of range in 'json_value'
631 SELECT JSON_VALUE('-1', '$' RETURNING UNSIGNED ERROR ON ERROR);
632 ERROR 22003: UNSIGNED value is out of range in 'json_value'
633 SELECT JSON_VALUE('"abc"', '$' RETURNING DATE ERROR ON ERROR);
634 ERROR 22003: DATE value is out of range in 'json_value'
635 SELECT JSON_VALUE('"abc"', '$' RETURNING TIME ERROR ON ERROR);
636 ERROR 22003: TIME value is out of range in 'json_value'
637 SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME ERROR ON ERROR);
638 ERROR 22003: DATETIME value is out of range in 'json_value'
639 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) ERROR ON ERROR);
640 ERROR 22003: STRING value is out of range in 'json_value'
641 SELECT JSON_VALUE('"abc"', '$' RETURNING DECIMAL ERROR ON ERROR);
642 ERROR 22003: DECIMAL value is out of range in 'json_value'
643 SELECT JSON_VALUE('"abc"', '$' RETURNING FLOAT ERROR ON ERROR);
644 ERROR 22003: FLOAT value is out of range in 'json_value'
645 SELECT JSON_VALUE('"abc"', '$' RETURNING DOUBLE ERROR ON ERROR);
646 ERROR 22003: DOUBLE value is out of range in 'json_value'
647 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR ERROR ON ERROR);
648 ERROR 22003: YEAR value is out of range in 'json_value'
649 SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED NULL ON ERROR) v1,
650 JSON_VALUE('-1', '$' RETURNING UNSIGNED NULL ON ERROR) v2,
651 JSON_VALUE('"abc"', '$' RETURNING DATE NULL ON ERROR) v3,
652 JSON_VALUE('"abc"', '$' RETURNING TIME NULL ON ERROR) v4,
653 JSON_VALUE('"abc"', '$' RETURNING DATETIME NULL ON ERROR) v5,
654 JSON_VALUE('"abc"', '$' RETURNING CHAR(1) NULL ON ERROR) v6,
655 JSON_VALUE('"abc"', '$' RETURNING DECIMAL NULL ON ERROR) v7,
656 JSON_VALUE('"abc"', '$' RETURNING FLOAT NULL ON ERROR) v8,
657 JSON_VALUE('"abc"', '$' RETURNING DOUBLE NULL ON ERROR) v9,
658 JSON_VALUE('"abc"', '$' RETURNING YEAR NULL ON ERROR) v10;
659 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10
660 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
661 SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED) v1,
662 JSON_VALUE('-1', '$' RETURNING UNSIGNED) v2,
663 JSON_VALUE('"abc"', '$' RETURNING DATE) v3,
664 JSON_VALUE('"abc"', '$' RETURNING TIME) v4,
665 JSON_VALUE('"abc"', '$' RETURNING DATETIME) v5,
666 JSON_VALUE('"abc"', '$' RETURNING CHAR(1)) v6,
667 JSON_VALUE('"abc"', '$' RETURNING DECIMAL) v7,
668 JSON_VALUE('"abc"', '$' RETURNING FLOAT) v8,
669 JSON_VALUE('"abc"', '$' RETURNING DOUBLE) v9,
670 JSON_VALUE('"abc"', '$' RETURNING YEAR) v10;
671 v1 v2 v3 v4 v5 v6 v7 v8 v9 v10
672 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
673 # Test ON EMPTY in combination with ON ERROR.
674 SELECT JSON_VALUE('{"data": "asd"}',
675 '$.data' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR);
676 JSON_VALUE('{"data": "asd"}',
677 '$.data' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR)
678 NULL
679 SELECT JSON_VALUE('{"data": "asd"}',
680 '$.data' RETURNING SIGNED NULL ON ERROR NULL ON EMPTY);
681 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ON EMPTY)' at line 2
682 SELECT j, JSON_VALUE(j, '$.data' RETURNING SIGNED DEFAULT 111 ON EMPTY
683 DEFAULT 222 ON ERROR) AS jv
684 FROM (SELECT '{"data": "asd"}' AS j UNION ALL SELECT '{"num": 123}') t1;
685 j jv
686 {"data": "asd"} 222
687 {"num": 123} 111
688 SELECT JSON_VALUE('not json', '$' RETURNING JSON ERROR ON EMPTY ERROR ON ERROR);
689 ERROR 22032: Invalid JSON text in argument 1 to function json_value: "Invalid value." at position 1.
690 # Returning array types is not supported.
691 SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED ARRAY);
692 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ARRAY)' at line 1
693 # Test defaults within range.
694 SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT -123 ON EMPTY) a,
695 JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT '-123' ON EMPTY) b,
696 JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT 123 ON EMPTY) c,
697 JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT '123' ON EMPTY) d,
698 JSON_VALUE('{}', '$.a' RETURNING DATE
699 DEFAULT DATE'2020-04-05' ON EMPTY) e,
700 JSON_VALUE('{}', '$.a' RETURNING DATE
701 DEFAULT '2020-04-05' ON EMPTY) f,
702 JSON_VALUE('{}', '$.a' RETURNING TIME(6)
703 DEFAULT TIME'09:10:11.22' ON EMPTY) g,
704 JSON_VALUE('{}', '$.a' RETURNING TIME(6)
705 DEFAULT '09:10:11.22' ON EMPTY) h,
706 JSON_VALUE('{}', '$.a' RETURNING DATETIME(6)
707 DEFAULT '2020-04-05 09:10:11.22' ON EMPTY) i,
708 JSON_VALUE('{}', '$.a'
709 RETURNING DATETIME(6)
710 DEFAULT TIMESTAMP'2020-04-05 09:10:11.22' ON EMPTY) j,
711 JSON_VALUE('{}', '$.a' DEFAULT 'empty' ON EMPTY) k,
712 JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 3) DEFAULT 1.23 ON EMPTY) l,
713 JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 3)
714 DEFAULT '1.23' ON EMPTY) m,
715 JSON_VALUE('{}', '$.a' RETURNING JSON DEFAULT '"empty"' ON EMPTY) n,
716 JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT -1234.5 ON EMPTY) o,
717 JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT '-1234.5' ON EMPTY) p,
718 JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT -1e308 ON EMPTY) q,
719 JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT '-1e308' ON EMPTY) r,
720 JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT 1901 ON EMPTY) s,
721 JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT '2155' ON EMPTY) t;
722 a b c d e f g h i j k l m n o p q r s t
723 -123 -123 123 123 2020-04-05 2020-04-05 09:10:11.220000 09:10:11.220000 2020-04-05 09:10:11.220000 2020-04-05 09:10:11.220000 empty 1.230 1.230 "empty" -1234.5 -1234.5 -1e308 -1e308 1901 2155
724 # Test defaults out of range.
725 SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT 9223372036854775808 ON EMPTY) v;
726 ERROR 22003: SIGNED DEFAULT value is out of range in 'json_value'
727 SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT '9223372036854775808' ON EMPTY) v;
728 ERROR 22003: SIGNED DEFAULT value is out of range in 'json_value'
729 SELECT JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT -1 ON EMPTY) v;
730 ERROR 22003: UNSIGNED DEFAULT value is out of range in 'json_value'
731 SELECT JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT '-1' ON EMPTY) v;
732 ERROR 22003: UNSIGNED DEFAULT value is out of range in 'json_value'
733 SELECT JSON_VALUE('{}', '$.a' RETURNING DATE DEFAULT 'abc' ON EMPTY) v;
734 ERROR 22007: Incorrect datetime value: 'abc'
735 SELECT JSON_VALUE('{}', '$.a' RETURNING TIME DEFAULT 'abc' ON EMPTY) v;
736 ERROR 22007: Truncated incorrect time value: 'abc'
737 SELECT JSON_VALUE('{}', '$.a' RETURNING TIME(2)
738 DEFAULT TIME'12:13:14.123' ON EMPTY) v;
739 ERROR 22003: TIME DEFAULT value is out of range in 'json_value'
740 SELECT JSON_VALUE('{}', '$.a' RETURNING TIME(2)
741 DEFAULT '12:13:14.123' ON EMPTY) v;
742 ERROR 22003: TIME DEFAULT value is out of range in 'json_value'
743 SELECT JSON_VALUE('{}', '$.a' RETURNING DATETIME DEFAULT 'abc' ON EMPTY) v;
744 ERROR 22007: Incorrect datetime value: 'abc'
745 SELECT JSON_VALUE('{}', '$.a'
746 RETURNING DATETIME(2) DEFAULT TIMESTAMP'2000-01-02 12:13:14.123' ON EMPTY) v;
747 ERROR 22003: TIME DEFAULT value is out of range in 'json_value'
748 SELECT JSON_VALUE('{}', '$.a' RETURNING DATETIME(2)
749 DEFAULT '2000-01-02 12:13:14.123' ON EMPTY) v;
750 ERROR 22003: TIME DEFAULT value is out of range in 'json_value'
751 SELECT JSON_VALUE('{}', '$.a' RETURNING CHAR(1) DEFAULT 'empty' ON EMPTY) v;
752 ERROR 22003: CHAR DEFAULT value is out of range in 'json_value'
753 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(1, 0) DEFAULT 12 ON EMPTY) v;
754 ERROR 22003: DECIMAL DEFAULT value is out of range in 'json_value'
755 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(1, 0) DEFAULT '12' ON EMPTY) v;
756 ERROR 22003: DECIMAL DEFAULT value is out of range in 'json_value'
757 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 1) DEFAULT 1.23 ON EMPTY) v;
758 ERROR 22003: DECIMAL DEFAULT value is out of range in 'json_value'
759 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 1) DEFAULT '1.23' ON EMPTY) v;
760 ERROR 22003: DECIMAL DEFAULT value is out of range in 'json_value'
761 SELECT JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT 1e308 ON EMPTY) v;
762 ERROR 22003: FLOAT DEFAULT value is out of range in 'json_value'
763 SELECT JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT '1e308' ON EMPTY) v;
764 ERROR 22003: FLOAT DEFAULT value is out of range in 'json_value'
765 SELECT JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT 'xyz' ON EMPTY) v;
766 ERROR 22007: Truncated incorrect DOUBLE value: 'xyz'
767 SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT '-1990' ON EMPTY) v;
768 ERROR 22003: YEAR value is out of range in 'json_value'
769 SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT -1990 ON EMPTY) v;
770 ERROR 22003: YEAR value is out of range in 'json_value'
771 SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT 'xyz' ON EMPTY) v;
772 ERROR 22007: Truncated incorrect YEAR value: 'xyz'
773 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON EMPTY);
774 ERROR 22007: Incorrect datetime value: '0000-00-00'
775 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON ERROR);
776 ERROR 22007: Incorrect datetime value: '0000-00-00'
777 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-01-00' ON EMPTY);
778 ERROR 22007: Incorrect datetime value: '2000-01-00'
779 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-00-01' ON ERROR);
780 ERROR 22007: Incorrect datetime value: '2000-00-01'
781 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON EMPTY);
782 ERROR 22007: Incorrect datetime value: '2004-02-31'
783 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON ERROR);
784 ERROR 22007: Incorrect datetime value: '2004-02-31'
785 SET @saved_sql_mode = @@sql_mode;
786 SET sql_mode = 'ALLOW_INVALID_DATES';
787 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON EMPTY);
788 ERROR 22007: Incorrect datetime value: '0000-00-00'
789 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON ERROR);
790 ERROR 22007: Incorrect datetime value: '0000-00-00'
791 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-01-00' ON EMPTY);
792 ERROR 22007: Incorrect datetime value: '2000-01-00'
793 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-00-01' ON ERROR);
794 ERROR 22007: Incorrect datetime value: '2000-00-01'
795 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON EMPTY);
796 ERROR 22007: Incorrect datetime value: '2004-02-31'
797 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON ERROR);
798 ERROR 22007: Incorrect datetime value: '2004-02-31'
799 SET sql_mode = @saved_sql_mode;
800 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING SIGNED) AS c1,
801 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING SIGNED), 10) AS c2,
802 JSON_VALUE('{"a":"b"}', '$.a' RETURNING UNSIGNED) AS c3,
803 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING UNSIGNED), 10) AS c4;
804 c1 c2 c3 c4
805 NULL NULL NULL NULL
806 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING DECIMAL(5, 2)) AS c1,
807 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DECIMAL(5, 2)), 10) AS c2;
808 c1 c2
809 NULL NULL
810 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING FLOAT) AS c1,
811 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING FLOAT), 10) AS c2,
812 JSON_VALUE('{"a":"b"}', '$.a' RETURNING DOUBLE) AS c3,
813 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DOUBLE), 10) AS c4;
814 c1 c2 c3 c4
815 NULL NULL NULL NULL
816 SELECT JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1)) AS c1,
817 JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1)) + 1 AS c2,
818 REPEAT('x', JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1))) AS c3;
819 c1 c2 c3
820 NULL NULL NULL
821 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATE) AS c1,
822 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATE), 10) AS c2,
823 JSON_VALUE('{"a":"b"}', '$.a' RETURNING TIME) AS c3,
824 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING TIME), 10) AS c4,
825 JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATETIME) AS c5,
826 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATETIME), 10) AS c6;
827 c1 c2 c3 c4 c5 c6
828 NULL NULL NULL NULL NULL NULL
829 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING YEAR) AS c1,
830 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING YEAR), 10) AS c2;
831 c1 c2
832 NULL NULL
833 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING JSON) AS c1,
834 JSON_VALUE('{"a":"b"}', '$.a' RETURNING JSON) + 1 AS c2;
835 c1 c2
836 "b" 1
837 Warnings:
838 Warning 3156 Invalid JSON value for CAST to DOUBLE from column json_value at row 1
839 SELECT JSON_VALUE('1', '$' RETURNING SIGNED) + 1.0e0;
840 JSON_VALUE('1', '$' RETURNING SIGNED) + 1.0e0
841 2
842 SELECT JSON_VALUE('1', '$' RETURNING UNSIGNED) + 1.0e0;
843 JSON_VALUE('1', '$' RETURNING UNSIGNED) + 1.0e0
844 2
845 SELECT JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) + 1.0e0;
846 JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) + 1.0e0
847 20000103
848 SELECT JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) + 1.0e0;
849 JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) + 1.0e0
850 121315
851 SELECT JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) + 1.0e0;
852 JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) + 1.0e0
853 20000102121315
854 SELECT JSON_VALUE('"1"', '$') + 1.0e0;
855 JSON_VALUE('"1"', '$') + 1.0e0
856 2
857 SELECT JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)) + 1.0e0;
858 JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)) + 1.0e0
859 2.2
860 SELECT JSON_VALUE('1', '$' RETURNING JSON) + 1.0e0;
861 JSON_VALUE('1', '$' RETURNING JSON) + 1.0e0
862 2
863 SELECT JSON_VALUE('1.2', '$' RETURNING FLOAT) + 1.0e0;
864 JSON_VALUE('1.2', '$' RETURNING FLOAT) + 1.0e0
865 2.200000047683716
866 SELECT JSON_VALUE('1988', '$' RETURNING YEAR) + 1.0e0;
867 JSON_VALUE('1988', '$' RETURNING YEAR) + 1.0e0
868 1989
869 SELECT JSON_VALUE('123', '$' RETURNING SIGNED) DIV 2;
870 JSON_VALUE('123', '$' RETURNING SIGNED) DIV 2
871 61
872 SELECT JSON_VALUE('123', '$' RETURNING UNSIGNED) DIV 2;
873 JSON_VALUE('123', '$' RETURNING UNSIGNED) DIV 2
874 61
875 SELECT JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) DIV 2;
876 JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) DIV 2
877 10000051
878 SELECT JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) DIV 2;
879 JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) DIV 2
880 60657
881 SELECT JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) DIV 2;
882 JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) DIV 2
883 10000051060657
884 SELECT JSON_VALUE('"123"', '$') DIV 2;
885 JSON_VALUE('"123"', '$') DIV 2
886 61
887 SELECT JSON_VALUE('123', '$' RETURNING JSON) DIV 2;
888 JSON_VALUE('123', '$' RETURNING JSON) DIV 2
889 61
890 SELECT JSON_VALUE('12.3', '$' RETURNING FLOAT) DIV 2;
891 JSON_VALUE('12.3', '$' RETURNING FLOAT) DIV 2
892 6
893 SELECT JSON_VALUE('12.3', '$' RETURNING DOUBLE) DIV 2;
894 JSON_VALUE('12.3', '$' RETURNING DOUBLE) DIV 2
895 6
896 SELECT CAST(JSON_VALUE('123', '$' RETURNING SIGNED) AS DECIMAL);
897 CAST(JSON_VALUE('123', '$' RETURNING SIGNED) AS DECIMAL)
898 123
899 SELECT CAST(JSON_VALUE('123', '$' RETURNING UNSIGNED) AS DECIMAL);
900 CAST(JSON_VALUE('123', '$' RETURNING UNSIGNED) AS DECIMAL)
901 123
902 SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS DECIMAL);
903 CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS DECIMAL)
904 1988
905 SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING SIGNED));
906 CONCAT('x', JSON_VALUE('1', '$' RETURNING SIGNED))
907 x1
908 SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING UNSIGNED));
909 CONCAT('x', JSON_VALUE('1', '$' RETURNING UNSIGNED))
910 x1
911 SELECT CONCAT('x', JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
912 CONCAT('x', JSON_VALUE('"2000-01-02"', '$' RETURNING DATE))
913 x2000-01-02
914 SELECT CONCAT('x', JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME));
915 CONCAT('x', JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME))
916 x12:13:14
917 SELECT CONCAT('x', JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME));
918 CONCAT('x', JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME))
919 x2000-01-02 12:13:14
920 SELECT CONCAT('x', JSON_VALUE('"1"', '$'));
921 CONCAT('x', JSON_VALUE('"1"', '$'))
922 x1
923 SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)));
924 CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)))
925 x1.2
926 SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING JSON));
927 CONCAT('x', JSON_VALUE('1', '$' RETURNING JSON))
928 x1
929 SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING FLOAT));
930 CONCAT('x', JSON_VALUE('1.2', '$' RETURNING FLOAT))
931 x1.2
932 SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DOUBLE));
933 CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DOUBLE))
934 x1.2
935 SELECT CONCAT('x', JSON_VALUE('1988', '$' RETURNING YEAR));
936 CONCAT('x', JSON_VALUE('1988', '$' RETURNING YEAR))
937 x1988
938 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING SIGNED), INTERVAL 1 DAY);
939 DATE_ADD(JSON_VALUE('20000102', '$' RETURNING SIGNED), INTERVAL 1 DAY)
940 2000-01-03
941 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING UNSIGNED), INTERVAL 1 DAY);
942 DATE_ADD(JSON_VALUE('20000102', '$' RETURNING UNSIGNED), INTERVAL 1 DAY)
943 2000-01-03
944 SET TIMESTAMP=UNIX_TIMESTAMP('2019-01-01 12:00:00');
945 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATE);
946 CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATE)
947 2019-01-01
948 SET TIMESTAMP=DEFAULT;
949 SELECT DATE_ADD(JSON_VALUE('"2000-01-02"', '$'), INTERVAL 1 DAY);
950 DATE_ADD(JSON_VALUE('"2000-01-02"', '$'), INTERVAL 1 DAY)
951 2000-01-03
952 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DECIMAL), INTERVAL 1 DAY);
953 DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DECIMAL), INTERVAL 1 DAY)
954 2000-01-03
955 SELECT DATE_ADD(JSON_VALUE('"2000-01-02"', '$' RETURNING JSON), INTERVAL 1 DAY);
956 DATE_ADD(JSON_VALUE('"2000-01-02"', '$' RETURNING JSON), INTERVAL 1 DAY)
957 2000-01-03
958 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING FLOAT), INTERVAL 1 DAY);
959 DATE_ADD(JSON_VALUE('20000102', '$' RETURNING FLOAT), INTERVAL 1 DAY)
960 2000-01-03
961 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DOUBLE), INTERVAL 1 DAY);
962 DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DOUBLE), INTERVAL 1 DAY)
963 2000-01-03
964 SELECT DATE_ADD(JSON_VALUE('1988', '$' RETURNING YEAR), INTERVAL 1 DAY);
965 DATE_ADD(JSON_VALUE('1988', '$' RETURNING YEAR), INTERVAL 1 DAY)
966 NULL
967 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING SIGNED));
968 TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING SIGNED))
969 43994
970 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING UNSIGNED));
971 TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING UNSIGNED))
972 43994
973 SELECT TIME_TO_SEC(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
974 TIME_TO_SEC(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE))
975 0
976 SELECT TIME_TO_SEC(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME));
977 TIME_TO_SEC(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME))
978 43994
979 SELECT TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$'));
980 TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$'))
981 43994
982 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DECIMAL));
983 TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DECIMAL))
984 43994
985 SELECT TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$' RETURNING JSON));
986 TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$' RETURNING JSON))
987 43994
988 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING FLOAT));
989 TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING FLOAT))
990 43994
991 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DOUBLE));
992 TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DOUBLE))
993 43994
994 SELECT TIME_TO_SEC(JSON_VALUE('2030', '$' RETURNING YEAR));
995 TIME_TO_SEC(JSON_VALUE('2030', '$' RETURNING YEAR))
996 1230
997 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
998 '$' RETURNING SIGNED));
999 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1000 '$' RETURNING SIGNED))
1001 2000-01-02 12:14:14
1002 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1003 '$' RETURNING UNSIGNED));
1004 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1005 '$' RETURNING UNSIGNED))
1006 2000-01-02 12:14:14
1007 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
1008 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02"', '$' RETURNING DATE))
1009 2000-01-02 00:01:00
1010 SET TIMESTAMP=UNIX_TIMESTAMP('2019-01-01 12:00:00');
1011 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATETIME);
1012 CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATETIME)
1013 2019-01-01 12:13:14
1014 SET TIMESTAMP=DEFAULT;
1015 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02 12:13:14"', '$'));
1016 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02 12:13:14"', '$'))
1017 2000-01-02 12:14:14
1018 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1019 '$' RETURNING DECIMAL(14, 0)));
1020 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1021 '$' RETURNING DECIMAL(14, 0)))
1022 2000-01-02 12:14:14
1023 # Warns because 20000102121314 gets truncated to fit in a FLOAT,
1024 # and is not a valid timestamp after truncation.
1025 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1026 '$' RETURNING FLOAT));
1027 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1028 '$' RETURNING FLOAT))
1029 NULL
1030 Warnings:
1031 Warning 1292 Incorrect datetime value: '20000102416384'
1032 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1033 '$' RETURNING DOUBLE));
1034 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
1035 '$' RETURNING DOUBLE))
1036 2000-01-02 12:14:14
1037 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('1988', '$' RETURNING YEAR));
1038 TIMESTAMPADD(MINUTE, 1, JSON_VALUE('1988', '$' RETURNING YEAR))
1039 NULL
1040 SELECT CAST(JSON_VALUE('1234', '$' RETURNING UNSIGNED) AS SIGNED);
1041 CAST(JSON_VALUE('1234', '$' RETURNING UNSIGNED) AS SIGNED)
1042 1234
1043 SELECT CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS SIGNED);
1044 CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS SIGNED)
1045 20000102
1046 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS SIGNED);
1047 CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS SIGNED)
1048 121314
1049 SELECT CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
1050 AS SIGNED);
1051 CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
1052 AS SIGNED)
1053 20000102121314
1054 SELECT CAST(JSON_VALUE('"1234"', '$') AS SIGNED);
1055 CAST(JSON_VALUE('"1234"', '$') AS SIGNED)
1056 1234
1057 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS SIGNED);
1058 CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS SIGNED)
1059 1234
1060 SELECT CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS SIGNED);
1061 CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS SIGNED)
1062 1234
1063 SELECT CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS SIGNED);
1064 CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS SIGNED)
1065 1234
1066 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS SIGNED);
1067 CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS SIGNED)
1068 1234
1069 SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS SIGNED);
1070 CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS SIGNED)
1071 1988
1072 SELECT CAST(JSON_VALUE('43', '$' RETURNING YEAR) AS SIGNED);
1073 CAST(JSON_VALUE('43', '$' RETURNING YEAR) AS SIGNED)
1074 NULL
1075 SELECT CAST(JSON_VALUE('88', '$' RETURNING YEAR) AS SIGNED);
1076 CAST(JSON_VALUE('88', '$' RETURNING YEAR) AS SIGNED)
1077 NULL
1078 SELECT REPEAT('x', JSON_VALUE('10', '$' RETURNING JSON));
1079 REPEAT('x', JSON_VALUE('10', '$' RETURNING JSON))
1080 xxxxxxxxxx
1081 SELECT CAST(JSON_VALUE('1234', '$' RETURNING SIGNED) AS UNSIGNED);
1082 CAST(JSON_VALUE('1234', '$' RETURNING SIGNED) AS UNSIGNED)
1083 1234
1084 SELECT CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS UNSIGNED);
1085 CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS UNSIGNED)
1086 20000102
1087 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS UNSIGNED);
1088 CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS UNSIGNED)
1089 121314
1090 SELECT CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
1091 AS UNSIGNED);
1092 CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
1093 AS UNSIGNED)
1094 20000102121314
1095 SELECT CAST(JSON_VALUE('"1234"', '$') AS UNSIGNED);
1096 CAST(JSON_VALUE('"1234"', '$') AS UNSIGNED)
1097 1234
1098 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS UNSIGNED);
1099 CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS UNSIGNED)
1100 1234
1101 SELECT CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS UNSIGNED);
1102 CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS UNSIGNED)
1103 1234
1104 SELECT CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS UNSIGNED);
1105 CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS UNSIGNED)
1106 1234
1107 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS UNSIGNED);
1108 CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS UNSIGNED)
1109 1234
1110 SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS UNSIGNED);
1111 CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS UNSIGNED)
1112 1988
1113 # SIGNED ranges.
1114 SELECT JSON_VALUE('9223372036854775807', '$' RETURNING SIGNED) AS max_val,
1115 JSON_VALUE('9223372036854775808', '$' RETURNING SIGNED) AS plus_one,
1116 JSON_VALUE('-9223372036854775808', '$' RETURNING SIGNED) AS min_val,
1117 JSON_VALUE('-9223372036854775809', '$' RETURNING SIGNED) AS minus_one;
1118 max_val plus_one min_val minus_one
1119 9223372036854775807 NULL -9223372036854775808 NULL
1120 SELECT JSON_VALUE('"9223372036854775807"', '$' RETURNING SIGNED) AS max_val,
1121 JSON_VALUE('"9223372036854775808"', '$' RETURNING SIGNED) AS plus_one,
1122 JSON_VALUE('"-9223372036854775808"', '$' RETURNING SIGNED) AS min_val,
1123 JSON_VALUE('"-9223372036854775809"', '$' RETURNING SIGNED) AS minus_one;
1124 max_val plus_one min_val minus_one
1125 9223372036854775807 NULL -9223372036854775808 NULL
1126 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
1127 DEFAULT '9223372036854775807' ON EMPTY) AS a,
1128 JSON_VALUE('{}', '$.x' RETURNING SIGNED
1129 DEFAULT 9223372036854775807 ON EMPTY) AS b,
1130 JSON_VALUE('{}', '$.x' RETURNING SIGNED
1131 DEFAULT '-9223372036854775808' ON EMPTY) AS c,
1132 JSON_VALUE('{}', '$.x' RETURNING SIGNED
1133 DEFAULT -9223372036854775808 ON EMPTY) AS d;
1134 a b c d
1135 9223372036854775807 9223372036854775807 -9223372036854775808 -9223372036854775808
1136 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
1137 DEFAULT '9223372036854775808' ON EMPTY);
1138 ERROR 22003: SIGNED DEFAULT value is out of range in 'json_value'
1139 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
1140 DEFAULT 9223372036854775808 ON EMPTY);
1141 ERROR 22003: SIGNED DEFAULT value is out of range in 'json_value'
1142 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
1143 DEFAULT '-9223372036854775809' ON EMPTY);
1144 ERROR 22003: SIGNED DEFAULT value is out of range in 'json_value'
1145 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
1146 DEFAULT -9223372036854775809 ON EMPTY);
1147 ERROR 22003: SIGNED DEFAULT value is out of range in 'json_value'
1148 # UNSIGNED ranges.
1149 SELECT JSON_VALUE('18446744073709551615', '$' RETURNING UNSIGNED) AS max_val,
1150 JSON_VALUE('18446744073709551616', '$' RETURNING UNSIGNED) AS plus_one,
1151 JSON_VALUE('0', '$' RETURNING UNSIGNED) AS min_val,
1152 JSON_VALUE('-1', '$' RETURNING UNSIGNED) AS minus_one;
1153 max_val plus_one min_val minus_one
1154 18446744073709551615 NULL 0 NULL
1155 SELECT JSON_VALUE('"18446744073709551615"', '$' RETURNING UNSIGNED) AS max_val,
1156 JSON_VALUE('"18446744073709551616"', '$' RETURNING UNSIGNED) AS plus_one,
1157 JSON_VALUE('"0"', '$' RETURNING UNSIGNED) AS min_val,
1158 JSON_VALUE('"-1"', '$' RETURNING UNSIGNED) AS minus_one;
1159 max_val plus_one min_val minus_one
1160 18446744073709551615 NULL 0 NULL
1161 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1162 DEFAULT '18446744073709551615' ON EMPTY) AS a,
1163 JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1164 DEFAULT 18446744073709551615 ON EMPTY) AS b,
1165 JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1166 DEFAULT '0' ON EMPTY) AS c,
1167 JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1168 DEFAULT 0 ON EMPTY) AS d;
1169 a b c d
1170 18446744073709551615 18446744073709551615 0 0
1171 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1172 DEFAULT '18446744073709551616' ON EMPTY);
1173 ERROR 22003: UNSIGNED DEFAULT value is out of range in 'json_value'
1174 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1175 DEFAULT 18446744073709551616 ON EMPTY);
1176 ERROR 22003: UNSIGNED DEFAULT value is out of range in 'json_value'
1177 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1178 DEFAULT '-1' ON EMPTY);
1179 ERROR 22003: UNSIGNED DEFAULT value is out of range in 'json_value'
1180 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
1181 DEFAULT -1 ON EMPTY);
1182 ERROR 22003: UNSIGNED DEFAULT value is out of range in 'json_value'
1183 # Check that the nullability of JSON_VALUE is correct.
1184 # Should be nullable because of implicit NULL ON EMPTY/NULL ON ERROR.
1185 CREATE VIEW v AS SELECT JSON_VALUE('{}', '$.x') AS c1,
1186 JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY) AS c2,
1187 JSON_VALUE('{}', '$.x' DEFAULT '2' ON ERROR) AS c3;
1188 DESCRIBE v;
1189 Field Type Null Key Default Extra
1190 c1 varchar(512) YES NULL
1191 c2 varchar(512) YES NULL
1192 c3 varchar(512) YES NULL
1193 DROP VIEW v;
1194 # Should be nullable because of explicit NULL ON EMPTY/NULL ON ERROR.
1195 CREATE VIEW v AS
1196 SELECT JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY NULL ON ERROR) AS c1,
1197 JSON_VALUE('{}', '$.x' NULL ON EMPTY DEFAULT '2' ON ERROR) AS c2,
1198 JSON_VALUE('{}', '$.x' ERROR ON EMPTY NULL ON ERROR) AS c3,
1199 JSON_VALUE('{}', '$.x' NULL ON EMPTY ERROR ON ERROR) AS c4;
1200 DESCRIBE v;
1201 Field Type Null Key Default Extra
1202 c1 varchar(512) YES NULL
1203 c2 varchar(512) YES NULL
1204 c3 varchar(512) YES NULL
1205 c4 varchar(512) YES NULL
1206 DROP VIEW v;
1207 # Should be nullable because the first argument is nullable.
1208 CREATE VIEW v AS
1209 SELECT JSON_VALUE(NULL, '$.x' ERROR ON EMPTY ERROR ON ERROR) AS c1;
1210 DESCRIBE v;
1211 Field Type Null Key Default Extra
1212 c1 varchar(512) YES NULL
1213 DROP VIEW v;
1214 # Should not be nullable.
1215 CREATE VIEW v AS
1216 SELECT JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY DEFAULT '2' ON ERROR) c1,
1217 JSON_VALUE('{}', '$.x' ERROR ON EMPTY ERROR ON ERROR) c2;
1218 DESCRIBE v;
1219 Field Type Null Key Default Extra
1220 c1 varchar(512) YES NULL
1221 c2 varchar(512) YES NULL
1222 DROP VIEW v;
1223 # Test index creation/usage
1224 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING SIGNED))));
1225 SHOW CREATE TABLE t1;
1226 Table Create Table
1227 t1 CREATE TABLE `t1` (
1228 `j` json DEFAULT NULL,
1229 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning signed)))
1230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1231 INSERT INTO t1 VALUES('1'),('2'),('-3'),('-4');
1232 ANALYZE TABLE t1;
1233 Table Op Msg_type Msg_text
1234 test.t1 analyze status OK
1235 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING SIGNED) = -3;
1236 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1237 1 SIMPLE t1 NULL ref i1 i1 9 const 1 100.00 Using where
1238 Warnings:
1239 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning signed) = -3)
1240 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING SIGNED) = -3;
1241 j
1242 -3
1243 EXPLAIN SELECT * FROM t1
1244 WHERE JSON_VALUE(j, '$' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR) = -3;
1245 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1246 1 SIMPLE t1 NULL ref i1 i1 9 const 1 100.00 Using where
1247 Warnings:
1248 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning signed) = -3)
1249 SELECT * FROM t1
1250 WHERE JSON_VALUE(j, '$' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR) = -3;
1251 j
1252 -3
1253 # The index is not used if the JSON_VALUE expressions differ.
1254 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = -3;
1255 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1256 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1257 Warnings:
1258 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning char(512)) = <cache>(-(3)))
1259 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING UNSIGNED) = -3;
1260 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1261 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1262 Warnings:
1263 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning unsigned) = <cache>(-(3)))
1264 EXPLAIN SELECT * FROM t1
1265 WHERE JSON_VALUE(j, '$' RETURNING SIGNED DEFAULT '4' ON EMPTY) = -3;
1266 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1267 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1268 Warnings:
1269 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning signed default '4' on empty) = <cache>(-(3)))
1270 EXPLAIN SELECT * FROM t1
1271 WHERE JSON_VALUE(j, '$' RETURNING SIGNED ERROR ON ERROR) = -3;
1272 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1273 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1274 Warnings:
1275 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning signed error on error) = <cache>(-(3)))
1276 DROP TABLE t1;
1277 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING UNSIGNED))));
1278 SHOW CREATE TABLE t1;
1279 Table Create Table
1280 t1 CREATE TABLE `t1` (
1281 `j` json DEFAULT NULL,
1282 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning unsigned)))
1283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1284 INSERT INTO t1 VALUES('1'),('2'),('3'),('4');
1285 ANALYZE TABLE t1;
1286 Table Op Msg_type Msg_text
1287 test.t1 analyze status OK
1288 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING UNSIGNED) = 3;
1289 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1290 1 SIMPLE t1 NULL ref i1 i1 9 const 1 100.00 NULL
1291 Warnings:
1292 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning unsigned) = 3)
1293 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING UNSIGNED) = 3;
1294 j
1295 3
1296 INSERT INTO t1 VALUES('-1'),('-2');
1297 DROP TABLE t1;
1298 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DECIMAL(3, 2)))));
1299 SHOW CREATE TABLE t1;
1300 Table Create Table
1301 t1 CREATE TABLE `t1` (
1302 `j` json DEFAULT NULL,
1303 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning decimal(3, 2))))
1304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1305 INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
1306 ANALYZE TABLE t1;
1307 Table Op Msg_type Msg_text
1308 test.t1 analyze status OK
1309 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(3, 2)) = 3.3;
1310 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1311 1 SIMPLE t1 NULL ref i1 i1 3 const 1 100.00 NULL
1312 Warnings:
1313 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning decimal(3, 2)) = 3.3)
1314 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(3, 2)) = 3.3;
1315 j
1316 3.3
1317 # The index is not used if the JSON_VALUE expressions differ.
1318 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(3, 0)) = 3.3;
1319 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1320 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1321 Warnings:
1322 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning decimal(3, 0)) = 3.3)
1323 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(2, 2)) = 3.3;
1324 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1325 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1326 Warnings:
1327 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning decimal(2, 2)) = 3.3)
1328 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(4, 0)) = 3.3;
1329 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1330 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1331 Warnings:
1332 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning decimal(4, 0)) = 3.3)
1333 DROP TABLE t1;
1334 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING FLOAT))));
1335 SHOW CREATE TABLE t1;
1336 Table Create Table
1337 t1 CREATE TABLE `t1` (
1338 `j` json DEFAULT NULL,
1339 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning float)))
1340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1341 INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
1342 ANALYZE TABLE t1;
1343 Table Op Msg_type Msg_text
1344 test.t1 analyze status OK
1345 EXPLAIN SELECT * FROM t1
1346 WHERE JSON_VALUE(j, '$' RETURNING FLOAT) = CAST(3.3 AS FLOAT);
1347 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1348 1 SIMPLE t1 NULL ref i1 i1 5 const 1 100.00 Using where
1349 Warnings:
1350 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning float) = <cache>(cast(3.3 as float)))
1351 SELECT * FROM t1
1352 WHERE JSON_VALUE(j, '$' RETURNING FLOAT) = CAST(3.3 AS FLOAT);
1353 j
1354 3.3
1355 # The index is not used if the JSON_VALUE expressions differ.
1356 EXPLAIN SELECT * FROM t1
1357 WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS FLOAT);
1358 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1359 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1360 Warnings:
1361 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning double) = <cache>(cast(3.3 as float)))
1362 DROP TABLE t1;
1363 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DOUBLE))));
1364 SHOW CREATE TABLE t1;
1365 Table Create Table
1366 t1 CREATE TABLE `t1` (
1367 `j` json DEFAULT NULL,
1368 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning double)))
1369 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1370 INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
1371 ANALYZE TABLE t1;
1372 Table Op Msg_type Msg_text
1373 test.t1 analyze status OK
1374 EXPLAIN SELECT * FROM t1
1375 WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS DOUBLE);
1376 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1377 1 SIMPLE t1 NULL ref i1 i1 9 const 1 100.00 NULL
1378 Warnings:
1379 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning double) = cast(3.3 as double))
1380 SELECT * FROM t1
1381 WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS DOUBLE);
1382 j
1383 3.3
1384 EXPLAIN SELECT * FROM t1
1385 WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS DOUBLE);
1386 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1387 1 SIMPLE t1 NULL ref i1 i1 9 const 1 100.00 NULL
1388 Warnings:
1389 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning double) = cast(3.3 as double))
1390 # The index is not used if the JSON_VALUE expressions differ.
1391 EXPLAIN SELECT * FROM t1
1392 WHERE JSON_VALUE(j, '$' RETURNING FLOAT) = CAST(3.3 AS DOUBLE);
1393 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1394 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1395 Warnings:
1396 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning float) = <cache>(cast(3.3 as double)))
1397 DROP TABLE t1;
1398 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING CHAR(10)))));
1399 SHOW CREATE TABLE t1;
1400 Table Create Table
1401 t1 CREATE TABLE `t1` (
1402 `j` json DEFAULT NULL,
1403 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning char(10))))
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1405 INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"FghJ"'),('"fghj"'),('"hjkl"');
1406 ANALYZE TABLE t1;
1407 Table Op Msg_type Msg_text
1408 test.t1 analyze status OK
1409 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10)) = 'fghj';
1410 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1411 1 SIMPLE t1 NULL ref i1 i1 43 const 1 100.00 NULL
1412 Warnings:
1413 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning char(10)) = 'fghj')
1414 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10)) = 'fghj';
1415 j
1416 "fghj"
1417 # The index is not used if the JSON_VALUE expressions differ.
1418 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING CHAR(11)) = 'fghj';
1419 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1420 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where
1421 Warnings:
1422 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning char(11)) = 'fghj')
1423 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = 'fghj';
1424 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1425 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where
1426 Warnings:
1427 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning char(512)) = 'fghj')
1428 DROP TABLE t1;
1429 CREATE TABLE t1(j JSON,
1430 KEY i1((JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf8mb4))));
1431 SHOW CREATE TABLE t1;
1432 Table Create Table
1433 t1 CREATE TABLE `t1` (
1434 `j` json DEFAULT NULL,
1435 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning char(10) character set utf8mb4)))
1436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1437 INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"FghJ"'),('"fghj"'),('"hjkl"');
1438 ANALYZE TABLE t1;
1439 Table Op Msg_type Msg_text
1440 test.t1 analyze status OK
1441 EXPLAIN SELECT * FROM t1
1442 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf8mb4) = 'fghj';
1443 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1444 1 SIMPLE t1 NULL ref i1 i1 43 const 2 100.00 NULL
1445 Warnings:
1446 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning char(10) character set utf8mb4) = 'fghj')
1447 SELECT * FROM t1
1448 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf8mb4) = 'fghj';
1449 j
1450 "FghJ"
1451 "fghj"
1452 # The index is not used if the JSON_VALUE expressions differ.
1453 EXPLAIN SELECT * FROM t1
1454 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET latin1) = 'fghj';
1455 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1456 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where
1457 Warnings:
1458 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning char(10) character set latin1) = 'fghj')
1459 DROP TABLE t1;
1460 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$'))));
1461 SHOW CREATE TABLE t1;
1462 Table Create Table
1463 t1 CREATE TABLE `t1` (
1464 `j` json DEFAULT NULL,
1465 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning char(512))))
1466 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1467 INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"fghj"'),('"hjkl"');
1468 ANALYZE TABLE t1;
1469 Table Op Msg_type Msg_text
1470 test.t1 analyze status OK
1471 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = 'fghj';
1472 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1473 1 SIMPLE t1 NULL ref i1 i1 2051 const 1 100.00 NULL
1474 Warnings:
1475 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning char(512)) = 'fghj')
1476 SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = 'fghj';
1477 j
1478 "fghj"
1479 DROP TABLE t1;
1480 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DATETIME))));
1481 SHOW CREATE TABLE t1;
1482 Table Create Table
1483 t1 CREATE TABLE `t1` (
1484 `j` json DEFAULT NULL,
1485 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning datetime)))
1486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1487 INSERT INTO t1 VALUES('"2019-01-01 11:11:11"'),('"2019-01-01 11:11:12"'),
1488 ('"2019-01-01 11:11:13"'),('"2019-01-01 11:11:14"');
1489 ANALYZE TABLE t1;
1490 Table Op Msg_type Msg_text
1491 test.t1 analyze status OK
1492 EXPLAIN SELECT * FROM t1 WHERE
1493 JSON_VALUE(j, '$' RETURNING DATETIME) = TIMESTAMP'2019-01-01 11:11:12';
1494 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1495 1 SIMPLE t1 NULL ref i1 i1 6 const 1 100.00 NULL
1496 Warnings:
1497 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning datetime) = TIMESTAMP'2019-01-01 11:11:12')
1498 SELECT * FROM t1 WHERE
1499 JSON_VALUE(j, '$' RETURNING DATETIME) = TIMESTAMP'2019-01-01 11:11:12';
1500 j
1501 "2019-01-01 11:11:12"
1502 # The index is not used if the JSON_VALUE expressions differ.
1503 EXPLAIN SELECT * FROM t1
1504 WHERE JSON_VALUE(j, '$' RETURNING DATE) = TIMESTAMP'2019-01-01 11:11:12';
1505 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1506 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1507 Warnings:
1508 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`test`.`t1`.`j`, '$' returning date) = TIMESTAMP'2019-01-01 11:11:12')
1509 DROP TABLE t1;
1510 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING TIME))));
1511 SHOW CREATE TABLE t1;
1512 Table Create Table
1513 t1 CREATE TABLE `t1` (
1514 `j` json DEFAULT NULL,
1515 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning time)))
1516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1517 INSERT INTO t1 VALUES('"11:11:11"'),('"11:11:12"'),
1518 ('"11:11:13"'),('"11:11:14"');
1519 ANALYZE TABLE t1;
1520 Table Op Msg_type Msg_text
1521 test.t1 analyze status OK
1522 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING TIME) = TIME'11:11:12';
1523 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1524 1 SIMPLE t1 NULL ref i1 i1 4 const 1 100.00 NULL
1525 Warnings:
1526 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning time) = TIME'11:11:12')
1527 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING TIME) = TIME'11:11:12';
1528 j
1529 "11:11:12"
1530 DROP TABLE t1;
1531 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DATE))));
1532 SHOW CREATE TABLE t1;
1533 Table Create Table
1534 t1 CREATE TABLE `t1` (
1535 `j` json DEFAULT NULL,
1536 KEY `i1` ((json_value(`j`, _utf8mb4'$' returning date)))
1537 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1538 INSERT INTO t1 VALUES('"2019-01-01"'),('"2019-01-02"'),
1539 ('"2019-01-03"'),('"2019-01-04"');
1540 ANALYZE TABLE t1;
1541 Table Op Msg_type Msg_text
1542 test.t1 analyze status OK
1543 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DATE) = DATE'2019-01-02';
1544 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1545 1 SIMPLE t1 NULL ref i1 i1 4 const 1 100.00 NULL
1546 Warnings:
1547 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where (json_value(`j`, _utf8mb4'$' returning date) = DATE'2019-01-02')
1548 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DATE) = DATE'2019-01-02';
1549 j
1550 "2019-01-02"
1551 DROP TABLE t1;
1552 CREATE TABLE t(j JSON);
1553 INSERT INTO t VALUES ('[1]'), (NULL), ('[2]'), (NULL), ('[3]');
1554 SELECT JSON_VALUE(j, '$[0]' RETURNING SIGNED) AS v FROM t;
1555 v
1556 1
1557 2
1558 3
1559 NULL
1560 NULL
1561 DROP TABLE t;
1562 CREATE TABLE t1(id INT PRIMARY KEY, vc VARCHAR(100));
1563 INSERT INTO t1 VALUES
1564 (1, '{"a":"a very long string"}'),
1565 (2, '{invalid json}'),
1566 (3, '{"a":"good"}'),
1567 (4, '{"b":"xyz"}');
1568 CREATE TABLE t2(id INT PRIMARY KEY, vc VARCHAR(100));
1569 INSERT INTO t2
1570 SELECT id, JSON_VALUE(vc, '$.a' RETURNING CHAR(10)
1571 DEFAULT 'empty' ON EMPTY
1572 DEFAULT 'error' ON ERROR)
1573 FROM t1;
1574 Warnings:
1575 Warning 3141 Invalid JSON text in argument 1 to function json_value: "Missing a name for object member." at position 1.
1576 SELECT * FROM t2 ORDER BY id;
1577 id vc
1578 1 error
1579 2 error
1580 3 good
1581 4 empty
1582 DROP TABLE t1, t2;
1583 #
1584 # Bug#30141215: SIG6 IN ROW_UPD_SEC_INDEX_ENTRY_LOW() AT ROW/ROW0UPD.CC
1585 #
1586 CREATE TABLE t (j JSON, KEY ((JSON_VALUE(j, '$[0]' RETURNING CHAR(1)))));
1587 SELECT * FROM t;
1588 j
1589 INSERT INTO t VALUES ('["abcde"]');
1590 DELETE FROM t;
1591 DROP TABLE t;
1592 #
1593 # Bug#30084642: ERROR FROM MEMBER OF AFTER ADDING FUNCTIONAL INDEX
1594 #
1595 CREATE TABLE t(j JSON, KEY ((JSON_VALUE(j, '$' RETURNING SIGNED))));
1596 INSERT INTO t VALUES ('1'), ('2');
1597 SELECT * FROM t WHERE 1 MEMBER OF (j->'$');
1598 j
1599 1
1600 DROP TABLE t;
1601 #
1602 # Bug#30330513: SIG6 IN ROW_UPD_SEC_INDEX_ENTRY_LOW()
1603 #
1604 CREATE TABLE t
1605 (j JSON, KEY ((JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf32))));
1606 INSERT INTO t VALUES
1607 ('"foobar"'),
1608 ('"Peh4UsNdzmNQbUhbXSK7L0uY0SP1U5F1PvIDDqbaUpR2Gu8NTnXnUmsDy"');
1609 DELETE FROM t;
1610 DROP TABLE t;
1611 #
1612 # Bug#30315526: SIG6 IN ITEM::EVALUATE() AT SQL/ITEM.CC
1613 #
1614 SELECT JSON_VALUE('"1990-12-29"', '$' RETURNING DATE) IS NULL AS c1,
1615 JSON_VALUE('"12:00:00"', '$' RETURNING TIME) IS NULL AS c2,
1616 JSON_VALUE('"1990-12-29 12:00:00"', '$' RETURNING TIME) IS NULL AS c3;
1617 c1 c2 c3
1618 0 0 0
1619 #
1620 # Bug#30320927: SIG6 IN MY_PACKED_TIME_MAKE() AT MYSYS/MY_TIME.CC
1621 #
1622 SELECT JSON_VALUE('[]', '$[0]' RETURNING DATE)
1623 BETWEEN DATE'2019-01-01' AND DATE'2020-01-01' AS c1,
1624 JSON_VALUE('"abc"', '$' RETURNING DATE)
1625 BETWEEN DATE'2019-01-01' AND DATE'2020-01-01' AS c2,
1626 JSON_VALUE('[]', '$[0]' RETURNING TIME)
1627 BETWEEN TIME'11:00:00' AND TIME'12:00:00' AS c3,
1628 JSON_VALUE('"abc"', '$' RETURNING TIME)
1629 BETWEEN TIME'11:00:00' AND TIME'12:00:00' AS c4;
1630 c1 c2 c3 c4
1631 NULL NULL NULL NULL
1632 #
1633 # Bug#30349041: SIG6 PRINT_CAST_TYPE() AT SQL/ITEM_JSON_FUNC.CC
1634 #
1635 EXPLAIN SELECT JSON_VALUE('true', '$' RETURNING JSON) v;
1636 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1637 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1638 Warnings:
1639 Note 1003 /* select#1 */ select json_value('true', '$' returning json) AS `v`
1640 #
1641 # Bug#30631787: SIG6 IN SQL_CMD_DML::PREPARE() AT SQL/SQL_SELECT.CC
1642 #
1643 Warnings:
1644 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1645 SELECT JSON_VALUE('{}', '$' RETURNING TIME DEFAULT '184' ON EMPTY);
1646 ERROR 22007: Truncated incorrect time value: '184'
1647 #
1648 # Bug#30633294: SIG11 IN STD::DEFAULT_DELETE() AT BITS/UNIQUE_PTR.H
1649 #
1650 CREATE TABLE t(
1651 j JSON,
1652 gc VARCHAR(512) AS (JSON_VALUE(j, '$[*]' DEFAULT 'abc' ON EMPTY
1653 DEFAULT 'def' ON ERROR)));
1654 SELECT * FROM t;
1655 j gc
1656 SELECT * FROM t;
1657 j gc
1658 SELECT * FROM t;
1659 j gc
1660 DROP TABLE t;
1661 #
1662 # Bug#30720910: JSON_VALUE() DOES NOT CHECK PRECISION FOR DECIMAL TYPE
1663 #
1664 SELECT JSON_VALUE('[12345.67]', '$[0]' RETURNING DECIMAL(5, 2)) v;
1665 v
1666 NULL
1667 SELECT JSON_VALUE('[12345.67]', '$[0]' RETURNING DECIMAL(5, 2) ERROR ON ERROR);
1668 ERROR 22003: DECIMAL value is out of range in 'json_value'
1669 SELECT JSON_VALUE('{}', '$.x' RETURNING DECIMAL(5, 2) DEFAULT 12345.67 ON EMPTY);
1670 ERROR 22003: DECIMAL DEFAULT value is out of range in 'json_value'
1671 #
1672 # Bug#31168181 FILTERING USING JSON FIELD, IS TRUE DOES NOT WORK
1673 #
1674 CREATE TABLE test (id INT, j JSON);
1675 INSERT INTO test VALUES (1, '{"some_key": true}');
1676 INSERT INTO test VALUES (2, '{"some_key": false}');
1677 INSERT INTO test VALUES (3, '{"some_key": null}');
1678 INSERT INTO test VALUES (4, '{"some_key": "true"}');
1679 SELECT j, (j->'$.some_key' IS TRUE) AS ist,
1680 (j->'$.some_key' IS FALSE) AS isf,
1681 (j->'$.some_key' IS NULL) AS isn
1682 FROM test;
1683 j ist isf isn
1684 {"some_key": true} 1 0 0
1685 {"some_key": false} 1 0 0
1686 {"some_key": null} 1 0 0
1687 {"some_key": "true"} 1 0 0
1688 Warnings:
1689 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1690 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1691 SELECT j FROM test
1692 WHERE j->'$.some_key';
1693 j
1694 {"some_key": true}
1695 {"some_key": false}
1696 {"some_key": null}
1697 {"some_key": "true"}
1698 Warnings:
1699 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1700 SELECT j FROM test
1701 WHERE NOT j->'$.some_key';
1702 j
1703 Warnings:
1704 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1705 SELECT j, (json_value(j, '$.some_key' RETURNING SIGNED) IS TRUE) AS ist,
1706 (json_value(j, '$.some_key' RETURNING SIGNED) IS FALSE) AS isf,
1707 (json_value(j, '$.some_key' RETURNING SIGNED) IS NULL) AS isn
1708 FROM test;
1709 j ist isf isn
1710 {"some_key": true} 1 0 0
1711 {"some_key": false} 0 1 0
1712 {"some_key": null} 0 0 1
1713 {"some_key": "true"} 0 0 1
1714 SELECT j FROM test
1715 WHERE json_value(j, '$.some_key' RETURNING SIGNED);
1716 j
1717 {"some_key": true}
1718 SELECT j FROM test
1719 WHERE NOT json_value(j, '$.some_key' RETURNING SIGNED);
1720 j
1721 {"some_key": false}
1722 SELECT json_extract(json_object( "a",FALSE),'$.a') IS TRUE;
1723 json_extract(json_object( "a",FALSE),'$.a') IS TRUE
1724 1
1725 Warnings:
1726 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1727 SELECT CAST(TRUE AS JSON) IS TRUE AS ist,
1728 CAST(TRUE AS JSON) IS FALSE AS isf;
1729 ist isf
1730 1 0
1731 Warnings:
1732 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1733 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1734 SELECT CAST(FALSE AS JSON) IS TRUE AS ist,
1735 CAST(FALSE AS JSON) IS FALSE AS isf;
1736 ist isf
1737 1 0
1738 Warnings:
1739 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1740 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1741 SELECT CAST(NULL AS JSON) IS TRUE AS ist,
1742 CAST(NULL AS JSON) IS FALSE AS isf;
1743 ist isf
1744 0 0
1745 Warnings:
1746 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1747 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1748 SELECT CAST(123 AS JSON) IS TRUE AS ist,
1749 CAST(123 AS JSON) IS FALSE AS isf;
1750 ist isf
1751 1 0
1752 Warnings:
1753 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1754 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1755 SELECT CAST('"ab"' AS JSON) IS TRUE AS ist,
1756 CAST('"ab"' AS JSON) IS FALSE AS isf;
1757 ist isf
1758 1 0
1759 Warnings:
1760 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1761 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
1762 DROP TABLE test;
1763 #
1764 # Bug#31336625 JSON_VALUE(EXPRESSION PATH RETURNING TYPE) SHOULD RETURN SQL NULL FOR JSON NULL
1765 #
1766 SELECT JSON_VALUE('null', '$' RETURNING DATE ERROR ON ERROR),
1767 JSON_VALUE('null', '$' RETURNING DATE),
1768 JSON_VALUE('null', '$' RETURNING DATE) IS NULL;
1769 JSON_VALUE('null', '$' RETURNING DATE ERROR ON ERROR) JSON_VALUE('null', '$' RETURNING DATE) JSON_VALUE('null', '$' RETURNING DATE) IS NULL
1770 NULL NULL 1
1771 SELECT JSON_VALUE('null', '$' RETURNING SIGNED ERROR ON ERROR),
1772 JSON_VALUE('null', '$' RETURNING SIGNED),
1773 JSON_VALUE('null', '$' RETURNING SIGNED) IS NULL;
1774 JSON_VALUE('null', '$' RETURNING SIGNED ERROR ON ERROR) JSON_VALUE('null', '$' RETURNING SIGNED) JSON_VALUE('null', '$' RETURNING SIGNED) IS NULL
1775 NULL NULL 1
1776 SELECT JSON_VALUE('null', '$' RETURNING CHAR ERROR ON ERROR),
1777 JSON_VALUE('null', '$' RETURNING CHAR),
1778 JSON_VALUE('null', '$' RETURNING CHAR) IS NULL;
1779 JSON_VALUE('null', '$' RETURNING CHAR ERROR ON ERROR) JSON_VALUE('null', '$' RETURNING CHAR) JSON_VALUE('null', '$' RETURNING CHAR) IS NULL
1780 NULL NULL 1
1781 SELECT JSON_VALUE(CAST('null' AS JSON), '$' RETURNING CHAR ERROR ON ERROR);
1782 JSON_VALUE(CAST('null' AS JSON), '$' RETURNING CHAR ERROR ON ERROR)
1783 NULL
1784 SELECT JSON_VALUE(CAST('null' AS JSON), '$' RETURNING SIGNED ERROR ON ERROR);
1785 JSON_VALUE(CAST('null' AS JSON), '$' RETURNING SIGNED ERROR ON ERROR)
1786 NULL
1787 CREATE TABLE t(a JSON);
1788 INSERT INTO t VALUES('[1,2,3,null]');
1789 SELECT JSON_VALUE(a, '$[2]' RETURNING SIGNED),
1790 JSON_VALUE(a, '$[3]' RETURNING SIGNED ERROR ON ERROR),
1791 JSON_VALUE(a, '$[3]' RETURNING SIGNED) FROM t;
1792 JSON_VALUE(a, '$[2]' RETURNING SIGNED) JSON_VALUE(a, '$[3]' RETURNING SIGNED ERROR ON ERROR) JSON_VALUE(a, '$[3]' RETURNING SIGNED)
1793 3 NULL NULL
1794 DELETE FROM t;
1795 INSERT INTO t VALUES('{"k1":"xy", "k2":null}');
1796 SELECT JSON_VALUE(a, '$.k1' RETURNING CHAR),
1797 JSON_VALUE(a, '$.k2' RETURNING CHAR ERROR ON ERROR),
1798 JSON_VALUE(a, '$.k2' RETURNING CHAR) FROM t;
1799 JSON_VALUE(a, '$.k1' RETURNING CHAR) JSON_VALUE(a, '$.k2' RETURNING CHAR ERROR ON ERROR) JSON_VALUE(a, '$.k2' RETURNING CHAR)
1800 xy NULL NULL
1801 DELETE FROM t;
1802 INSERT INTO t VALUES('{"k1":"2020-05-12", "k2":null}');
1803 SELECT JSON_VALUE(a, '$.k1' RETURNING DATE),
1804 JSON_VALUE(a, '$.k2' RETURNING DATE ERROR ON ERROR),
1805 JSON_VALUE(a, '$.k2' RETURNING DATE) FROM t;
1806 JSON_VALUE(a, '$.k1' RETURNING DATE) JSON_VALUE(a, '$.k2' RETURNING DATE ERROR ON ERROR) JSON_VALUE(a, '$.k2' RETURNING DATE)
1807 2020-05-12 NULL NULL
1808 DROP TABLE t;
1809 SELECT JSON_VALUE(JSON_OBJECT('a', FALSE), '$.a' RETURNING SIGNED),
1810 JSON_VALUE(JSON_OBJECT('a', TRUE), '$.a' RETURNING SIGNED),
1811 JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED ERROR ON ERROR),
1812 JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED);
1813 JSON_VALUE(JSON_OBJECT('a', FALSE), '$.a' RETURNING SIGNED) JSON_VALUE(JSON_OBJECT('a', TRUE), '$.a' RETURNING SIGNED) JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED ERROR ON ERROR) JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED)
1814 0 1 NULL NULL
1815 SELECT JSON_VALUE(JSON_OBJECT('a', 'value'), '$.a'),
1816 JSON_VALUE(JSON_OBJECT('a', NULL), '$.a');
1817 JSON_VALUE(JSON_OBJECT('a', 'value'), '$.a') JSON_VALUE(JSON_OBJECT('a', NULL), '$.a')
1818 value NULL
1819 #
1820 # Bug#34554755: DISTINCT on FLOAT values gives duplicates
1821 # with SQL_BIG_RESULT
1822 #
1823 SELECT JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) =
1824 JSON_VALUE('1111111111111110', '$' RETURNING FLOAT) AS eq;
1825 eq
1826 1
1827 SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) AS CHAR) AS v;
1828 v
1829 1.11111e15
1830 SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) AS SIGNED) AS v;
1831 v
1832 1111111126482944
1833 SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT)
1834 AS DECIMAL(20,2)) AS res;
1835 res
1836 1111111126482944.00
1837 SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
1838 DEFAULT 1111111111111111 ON EMPTY) AS CHAR) AS v;
1839 v
1840 1.11111e15
1841 SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
1842 DEFAULT 1111111111111111 ON EMPTY) AS SIGNED) AS v;
1843 v
1844 1111111126482944
1845 SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
1846 DEFAULT 1111111111111111 ON EMPTY)
1847 AS DECIMAL(20,2)) AS v;
1848 v
1849 1111111126482944.00
1850 #
1851 # Bug#35206138: Assigning json_value output to user defined
1852 # variable will return NULL or empty string if
1853 # input is empty string
1854 #
1855 SET @x = JSON_VALUE('{"x": ""}', '$.x');
1856 SELECT @x;
1857 @x
1858