Debian Codegraph @

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

raw
1 # Use at least 16k page size for this test. With smaller page sizes,
2 # the maximum key length is too small for strings returned by
3 # JSON_VALUE with an implicit RETURNING clause (which returns
4 # CHAR(512) with utf8mb4 encoding).
5 --source include/have_innodb_min_16k.inc
6
7 --echo #
8 --echo # WL#12228: Add JSON_VALUE function
9 --echo #
10
11 # Enable display of metadata to verify that the RETURNING clause is respected.
12 --enable_metadata
13
14 SELECT JSON_VALUE('{"data": "asdf"}', '$.data') v;
15 SELECT JSON_VALUE('{"data": 123.45}', '$.data') v;
16 SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING CHAR) v;
17 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR) v;
18 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DECIMAL) v;
19 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DECIMAL(6,4)) v;
20 SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING BINARY(4)) v;
21 SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING BINARY) v;
22
23 # PS protocol gives different metadata for the "Max length" column for
24 # integer and floating-point types.
25 --disable_ps_protocol
26 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING FLOAT) v;
27 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DOUBLE) v;
28 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING UNSIGNED) v;
29 SELECT JSON_VALUE('{"data": -123.45}','$.data' RETURNING UNSIGNED) v;
30 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING SIGNED) v;
31 SELECT JSON_VALUE('{"data": -123.45}', '$.data' RETURNING SIGNED) v;
32 SELECT JSON_VALUE('{"data": 1999}', '$.data' RETURNING YEAR) v;
33 SELECT JSON_VALUE('{"data": -1999}', '$.data' RETURNING YEAR) v;
34 SELECT JSON_VALUE(JSON_OBJECT('data', 1.0e0/3), '$.data' RETURNING FLOAT) v;
35 SELECT JSON_VALUE(JSON_OBJECT('data', 1.0e0/3), '$.data' RETURNING DOUBLE) v;
36 --enable_ps_protocol
37
38 # For character types, if no character set or collation is specified,
39 # it should use the default character set and collation of the JSON
40 # data type.
41 SELECT COLLATION(JSON_VALUE('{"x": "y"}', '$.x')) AS col1,
42 COLLATION(JSON_VALUE('{"x": "y"}', '$.x' RETURNING CHAR(10))) AS col2,
43 COLLATION(JSON_VALUE('{"x": "y"}',
44 '$.x' RETURNING CHAR(10) CHARSET utf8mb4)) AS col3;
45
46 SELECT JSON_VALUE('{"data": "2019-01-01"}', '$.data' RETURNING DATE);
47 SELECT JSON_VALUE(JSON_OBJECT('data', CAST( DATE'2019-01-01' AS JSON)),
48 '$.data' RETURNING DATE);
49
50 # PS protocol gives different metadata for the "Max length" column for
51 # TIME and DATETIME.
52 --disable_ps_protocol
53 SELECT JSON_VALUE('{"data": "11:11:11"}', '$.data' RETURNING TIME);
54 SELECT JSON_VALUE(JSON_OBJECT('data', CAST( TIME'11:11:11' AS JSON)),
55 '$.data' RETURNING TIME);
56
57 SELECT JSON_VALUE('{"data": "2019-01-01 11:11:11"}',
58 '$.data' RETURNING DATETIME);
59 SELECT JSON_VALUE(
60 JSON_OBJECT('data', CAST(TIMESTAMP'2019-01-01 11:11:11' AS JSON)),
61 '$.data' RETURNING DATETIME);
62 --enable_ps_protocol
63
64 SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR(10));
65 SELECT JSON_VALUE('{"data": "1234567890"}', '$.data' RETURNING CHAR(10));
66 SELECT JSON_VALUE('{"data": "12345678901"}', '$.data' RETURNING CHAR(10));
67 SELECT JSON_VALUE('{"data": "1234567890"}',
68 '$.data' RETURNING CHAR(10) CHARSET utf8mb4);
69 SELECT JSON_VALUE('{"data": "123"}', '$.data' RETURNING JSON);
70 --disable_metadata
71
72 --error ER_PARSE_ERROR
73 SELECT JSON_VALUE('{}', NULL);
74 --error ER_PARSE_ERROR
75 SELECT JSON_VALUE('{}', '$.x' DEFAULT NULL ON EMPTY);
76 --error ER_PARSE_ERROR
77 SELECT JSON_VALUE('{}', '$.x' DEFAULT NULL ON ERROR);
78 --error ER_INVALID_TYPE_FOR_JSON
79 SELECT JSON_VALUE(1, '$');
80 --error ER_INVALID_JSON_CHARSET
81 SELECT JSON_VALUE(CAST('[]' AS BINARY), '$');
82
83 SELECT JSON_VALUE('{"data": [123]}', '$.data' RETURNING JSON) v;
84 SELECT JSON_VALUE('{"data": {"val":123}}', '$.data' RETURNING JSON) v;
85 SELECT JSON_VALUE('[1,2,3]', '$') v;
86 SELECT JSON_VALUE('{"a":"b"}', '$') v;
87 --error ER_TOO_BIG_PRECISION
88 SELECT JSON_VALUE('1', '$' RETURNING TIME(1000));
89
90 SELECT JSON_VALUE('{"data": [123]}', '$.data' RETURNING SIGNED) v;
91 SELECT JSON_VALUE('{"data": {"x":"y"}}', '$.data' RETURNING SIGNED) v;
92 SELECT JSON_VALUE('{"data": [123]}',
93 '$.data' RETURNING SIGNED DEFAULT 456 ON ERROR) v;
94
95 --echo # Wildcards are OK if they match a single value.
96 SELECT JSON_VALUE('["abc"]', '$[*]') v1,
97 JSON_VALUE('[1]', '$[*]' RETURNING SIGNED) v2,
98 JSON_VALUE('[1]', '$[*]' RETURNING UNSIGNED) v3,
99 JSON_VALUE('["2020-01-01"]', '$[*]' RETURNING DATE) v4,
100 JSON_VALUE('["12:00:00"]', '$[*]' RETURNING TIME) v5,
101 JSON_VALUE('["2020-01-01 12:00:00"]', '$[*]' RETURNING DATETIME) v6,
102 JSON_VALUE('[1]', '$[*]' RETURNING DECIMAL) v7,
103 JSON_VALUE('[{}]', '$[*]' RETURNING JSON) v8,
104 JSON_VALUE('[1]', '$[*]' RETURNING FLOAT) v9,
105 JSON_VALUE('[1]', '$[*]' RETURNING DOUBLE) v10;
106
107 --echo # Paths matching multiple values should invoke ON ERROR.
108 SELECT JSON_VALUE('["abc", "def"]', '$[*]') v1,
109 JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED) v2,
110 JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED) v3,
111 JSON_VALUE('["2020-01-01", "2020-01-02"]', '$[*]' RETURNING DATE) v4,
112 JSON_VALUE('["12:00:00", "13:00:00"]', '$[*]' RETURNING TIME) v5,
113 JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
114 '$[*]' RETURNING DATETIME) v6,
115 JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL) v7,
116 JSON_VALUE('[{}, []]', '$[*]' RETURNING JSON) v8,
117 JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT) v9,
118 JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE) v10;
119 SELECT JSON_VALUE('["abc", "def"]', '$[*]' DEFAULT 'error' ON ERROR) v1,
120 JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED DEFAULT 9 ON ERROR) v2,
121 JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED DEFAULT 9 ON ERROR) v3,
122 JSON_VALUE('["2020-01-01", "2020-01-02"]',
123 '$[*]' RETURNING DATE DEFAULT DATE'1999-12-31' ON ERROR) v4,
124 JSON_VALUE('["12:00:00", "13:00:00"]',
125 '$[*]' RETURNING TIME DEFAULT TIME'10:20:30' ON ERROR) v5,
126 JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
127 '$[*]' RETURNING DATETIME
128 DEFAULT '1999-12-31 10:20:30' ON ERROR) v6,
129 JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL DEFAULT 9 ON ERROR) v7,
130 JSON_VALUE('[{}, []]',
131 '$[*]' RETURNING JSON DEFAULT '"error"' ON ERROR) v8,
132 JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT DEFAULT 9 ON ERROR) v9,
133 JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE DEFAULT 9 ON ERROR) v10;
134 --error ER_MULTIPLE_JSON_VALUES
135 SELECT JSON_VALUE('["abc", "def"]', '$[*]' ERROR ON ERROR) v;
136 --error ER_MULTIPLE_JSON_VALUES
137 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED ERROR ON ERROR) v;
138 --error ER_MULTIPLE_JSON_VALUES
139 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED ERROR ON ERROR) v;
140 --error ER_MULTIPLE_JSON_VALUES
141 SELECT JSON_VALUE('["2020-01-01", "2020-01-02"]',
142 '$[*]' RETURNING DATE ERROR ON ERROR) v;
143 --error ER_MULTIPLE_JSON_VALUES
144 SELECT JSON_VALUE('["12:00:00", "13:00:00"]',
145 '$[*]' RETURNING TIME ERROR ON ERROR) v;
146 --error ER_MULTIPLE_JSON_VALUES
147 SELECT JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
148 '$[*]' RETURNING DATETIME ERROR ON ERROR) v;
149 --error ER_MULTIPLE_JSON_VALUES
150 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL ERROR ON ERROR) v;
151 --error ER_MULTIPLE_JSON_VALUES
152 SELECT JSON_VALUE('[{}, []]', '$[*]' RETURNING JSON ERROR ON ERROR) v;
153 --error ER_MULTIPLE_JSON_VALUES
154 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT ERROR ON ERROR) v;
155 --error ER_MULTIPLE_JSON_VALUES
156 SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE ERROR ON ERROR) v;
157
158 --echo # Max length check should use char length, not byte length.
159 SELECT JSON_VALUE('"æøå"', '$' RETURNING CHAR(3)) v;
160 SELECT JSON_VALUE('"æøåa"', '$' RETURNING CHAR(3) DEFAULT 'ÆØÅ' ON ERROR) v;
161
162 --echo # Charset conversion errors should activate the ON ERROR clause.
163 SELECT JSON_VALUE('"æøå"', '$' RETURNING CHAR(10) CHARSET ascii
164 DEFAULT 'error' ON ERROR) v;
165
166 # Allow NULL for all target types.
167 SELECT JSON_VALUE(NULL, '$.x') v1,
168 JSON_VALUE(NULL, '$.x' RETURNING SIGNED) v2,
169 JSON_VALUE(NULL, '$.x' RETURNING UNSIGNED) v3,
170 JSON_VALUE(NULL, '$.x' RETURNING DATE) v4,
171 JSON_VALUE(NULL, '$.x' RETURNING TIME) v5,
172 JSON_VALUE(NULL, '$.x' RETURNING DATETIME) v6,
173 JSON_VALUE(NULL, '$.x' RETURNING DECIMAL) v7,
174 JSON_VALUE(NULL, '$.x' RETURNING JSON) v8,
175 JSON_VALUE(NULL, '$.x' RETURNING FLOAT) v9,
176 JSON_VALUE(NULL, '$.x' RETURNING DOUBLE) v10,
177 JSON_VALUE(NULL, '$.x' RETURNING YEAR) v11;
178
179 # Check JSON parse errors for all target types.
180 SELECT JSON_VALUE('not json', '$.x') v1,
181 JSON_VALUE('not json', '$.x' RETURNING SIGNED) v2,
182 JSON_VALUE('not json', '$.x' RETURNING UNSIGNED) v3,
183 JSON_VALUE('not json', '$.x' RETURNING DATE) v4,
184 JSON_VALUE('not json', '$.x' RETURNING TIME) v5,
185 JSON_VALUE('not json', '$.x' RETURNING DATETIME) v6,
186 JSON_VALUE('not json', '$.x' RETURNING DECIMAL) v7,
187 JSON_VALUE('not json', '$.x' RETURNING JSON) v8,
188 JSON_VALUE('not json', '$.x' RETURNING FLOAT) v9,
189 JSON_VALUE('not json', '$.x' RETURNING DOUBLE) v10,
190 JSON_VALUE('not json', '$.x' RETURNING YEAR) v11;
191 SELECT JSON_VALUE('not json', '$.x') v1,
192 JSON_VALUE('not json', '$.x' RETURNING SIGNED NULL ON ERROR) v2,
193 JSON_VALUE('not json', '$.x' RETURNING UNSIGNED NULL ON ERROR) v3,
194 JSON_VALUE('not json', '$.x' RETURNING DATE NULL ON ERROR) v4,
195 JSON_VALUE('not json', '$.x' RETURNING TIME NULL ON ERROR) v5,
196 JSON_VALUE('not json', '$.x' RETURNING DATETIME NULL ON ERROR) v6,
197 JSON_VALUE('not json', '$.x' RETURNING DECIMAL NULL ON ERROR) v7,
198 JSON_VALUE('not json', '$.x' RETURNING JSON NULL ON ERROR) v8,
199 JSON_VALUE('not json', '$.x' RETURNING FLOAT NULL ON ERROR) v9,
200 JSON_VALUE('not json', '$.x' RETURNING DOUBLE NULL ON ERROR) v10,
201 JSON_VALUE('not json', '$.x' RETURNING YEAR NULL ON ERROR) v11;
202 SELECT JSON_VALUE('not json', '$.x' DEFAULT 'error' ON ERROR) v1,
203 JSON_VALUE('not json', '$.x' RETURNING SIGNED DEFAULT 123 ON ERROR) v2,
204 JSON_VALUE('not json', '$.x' RETURNING UNSIGNED DEFAULT 123 ON ERROR) v3,
205 JSON_VALUE('not json', '$.x' RETURNING DATE
206 DEFAULT '2020-01-01' ON ERROR) v4,
207 JSON_VALUE('not json', '$.x' RETURNING TIME
208 DEFAULT '10:11:12' ON ERROR) v5,
209 JSON_VALUE('not json', '$.x' RETURNING DATETIME
210 DEFAULT '2020-01-01 12:13:14' ON ERROR) v6,
211 JSON_VALUE('not json', '$.x' RETURNING DECIMAL DEFAULT 123 ON ERROR) v7,
212 JSON_VALUE('not json', '$.x' RETURNING JSON
213 DEFAULT '[1,2,3]' ON ERROR) v8,
214 JSON_VALUE('not json', '$.x' RETURNING FLOAT DEFAULT 123 ON ERROR) v9,
215 JSON_VALUE('not json', '$.x' RETURNING DOUBLE DEFAULT 123 ON ERROR) v10,
216 JSON_VALUE('not json', '$.x' RETURNING YEAR DEFAULT 1988 ON ERROR) v11;
217 --error ER_INVALID_JSON_TEXT_IN_PARAM
218 SELECT JSON_VALUE('not json', '$.x' ERROR ON EMPTY ERROR ON ERROR) v;
219 --error ER_INVALID_JSON_TEXT_IN_PARAM
220 SELECT JSON_VALUE('not json', '$.x' RETURNING SIGNED
221 ERROR ON EMPTY ERROR ON ERROR) v;
222 --error ER_INVALID_JSON_TEXT_IN_PARAM
223 SELECT JSON_VALUE('not json', '$.x' RETURNING UNSIGNED
224 ERROR ON EMPTY ERROR ON ERROR) v;
225 --error ER_INVALID_JSON_TEXT_IN_PARAM
226 SELECT JSON_VALUE('not json', '$.x' RETURNING DATE
227 ERROR ON EMPTY ERROR ON ERROR) v;
228 --error ER_INVALID_JSON_TEXT_IN_PARAM
229 SELECT JSON_VALUE('not json', '$.x' RETURNING TIME
230 ERROR ON EMPTY ERROR ON ERROR) v;
231 --error ER_INVALID_JSON_TEXT_IN_PARAM
232 SELECT JSON_VALUE('not json', '$.x' RETURNING DATETIME
233 ERROR ON EMPTY ERROR ON ERROR) v;
234 --error ER_INVALID_JSON_TEXT_IN_PARAM
235 SELECT JSON_VALUE('not json', '$.x' RETURNING DECIMAL
236 ERROR ON EMPTY ERROR ON ERROR) v;
237 --error ER_INVALID_JSON_TEXT_IN_PARAM
238 SELECT JSON_VALUE('not json', '$.x' RETURNING JSON
239 ERROR ON EMPTY ERROR ON ERROR) v;
240 --error ER_INVALID_JSON_TEXT_IN_PARAM
241 SELECT JSON_VALUE('not json', '$.x' RETURNING FLOAT
242 ERROR ON EMPTY ERROR ON ERROR) v;
243 --error ER_INVALID_JSON_TEXT_IN_PARAM
244 SELECT JSON_VALUE('not json', '$.x' RETURNING DOUBLE
245 ERROR ON EMPTY ERROR ON ERROR) v;
246 --error ER_INVALID_JSON_TEXT_IN_PARAM
247 SELECT JSON_VALUE('not json', '$.x' RETURNING YEAR
248 ERROR ON EMPTY ERROR ON ERROR) v;
249
250 # If the syntax error is raised in a nested function call, the ON
251 # ERROR clause is not invoked. It is only invoked when it is
252 # JSON_VALUE itself that cannot parse the string.
253 --error ER_INVALID_JSON_TEXT_IN_PARAM
254 SELECT JSON_VALUE(CAST(JSON_EXTRACT('not json', '$') AS CHAR), '$') v;
255
256 # If the document is too deep for the parser, raise an error instead
257 # of invoking the ON ERROR clause.
258 --error ER_JSON_DOCUMENT_TOO_DEEP
259 SELECT JSON_VALUE(REPEAT('[', 200), '$') v;
260
261 # Verify that JSON_VALUE expressions are correctly represented in VIEWs.
262 CREATE VIEW v AS
263 SELECT JSON_VALUE('{"don''t":"panic"}', '$."don''t"') AS x,
264 JSON_VALUE('""', '$' RETURNING CHAR(10)
265 DEFAULT 'it''s empty' ON EMPTY
266 DEFAULT 'it''s wrong' ON ERROR) AS y,
267 JSON_VALUE('""', '$' NULL ON EMPTY ERROR ON ERROR) AS z,
268 JSON_VALUE('""', '$' ERROR ON EMPTY NULL ON ERROR) AS w;
269 SHOW CREATE VIEW v;
270 SELECT * FROM v;
271 DROP VIEW v;
272
273 # JSON_VALUE is a reserved word in the standard. It is non-reserved in
274 # MySQL for now.
275 CREATE TABLE json_value(json_value JSON);
276 SELECT JSON_VALUE(json_value, '$.a') AS json_value FROM json_value;
277 DROP TABLE json_value;
278
279 # RETURNING is a non-reserved word both in the standard and in MySQL.
280 CREATE TABLE returning(returning JSON);
281 SELECT JSON_VALUE(returning, '$.a' RETURNING CHAR) AS returning FROM returning;
282 DROP TABLE returning;
283
284 --echo # Test ON EMPTY clause
285 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED) v;
286 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED NULL ON EMPTY) v;
287 --error ER_MISSING_JSON_VALUE
288 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED ERROR ON EMPTY);
289 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
290 DEFAULT 456 ON EMPTY);
291 --error ER_TRUNCATED_WRONG_VALUE
292 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
293 DEFAULT '[456]' ON EMPTY);
294 --error ER_PARSE_ERROR
295 SELECT JSON_VALUE('{"data": 123}',
296 '$.num' RETURNING SIGNED NULL ON EMPTY NULL ON EMPTY);
297 SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET utf16
298 DEFAULT 'abc' ON EMPTY) v;
299 SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET ascii
300 DEFAULT 'abc' ON EMPTY) v;
301 --error ER_DATA_OUT_OF_RANGE
302 SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET ascii
303 DEFAULT 'æøå' ON EMPTY) v;
304
305 --error ER_TRUNCATED_WRONG_VALUE
306 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
307 DEFAULT 'asdf' ON EMPTY);
308 --error ER_TRUNCATED_WRONG_VALUE
309 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING UNSIGNED
310 DEFAULT 'asdf' ON EMPTY);
311 --error ER_DATA_OUT_OF_RANGE
312 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING CHAR(2)
313 DEFAULT 'asdf' ON EMPTY);
314 --error ER_TRUNCATED_WRONG_VALUE
315 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING FLOAT
316 DEFAULT 'asdf' ON EMPTY);
317 --error ER_TRUNCATED_WRONG_VALUE
318 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DOUBLE
319 DEFAULT 'asdf' ON EMPTY);
320 --error ER_TRUNCATED_WRONG_VALUE
321 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DATE
322 DEFAULT 'asdf' ON EMPTY);
323 --error ER_TRUNCATED_WRONG_VALUE
324 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING TIME
325 DEFAULT 'asdf' ON EMPTY);
326 --error ER_TRUNCATED_WRONG_VALUE
327 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DATETIME
328 DEFAULT 'asdf' ON EMPTY);
329 --error ER_TRUNCATED_WRONG_VALUE
330 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DECIMAL
331 DEFAULT 'asdf' ON EMPTY);
332 --error ER_TRUNCATED_WRONG_VALUE
333 SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING YEAR
334 DEFAULT 'asdf' ON EMPTY);
335 --error ER_INVALID_JSON_TEXT_IN_PARAM
336 SELECT JSON_VALUE('""', '$' RETURNING JSON DEFAULT 'not JSON' ON EMPTY);
337
338 --error ER_TRUNCATED_WRONG_VALUE
339 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING SIGNED
340 DEFAULT 'asdf' ON ERROR);
341 --error ER_TRUNCATED_WRONG_VALUE
342 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING UNSIGNED
343 DEFAULT 'asdf' ON ERROR);
344 --error ER_DATA_OUT_OF_RANGE
345 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING CHAR(2)
346 DEFAULT 'asdf' ON ERROR);
347 --error ER_TRUNCATED_WRONG_VALUE
348 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING FLOAT
349 DEFAULT 'asdf' ON ERROR);
350 --error ER_TRUNCATED_WRONG_VALUE
351 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DOUBLE
352 DEFAULT 'asdf' ON ERROR);
353 --error ER_TRUNCATED_WRONG_VALUE
354 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DATE
355 DEFAULT 'asdf' ON ERROR);
356 --error ER_TRUNCATED_WRONG_VALUE
357 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING TIME
358 DEFAULT 'asdf' ON ERROR);
359 --error ER_TRUNCATED_WRONG_VALUE
360 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DATETIME
361 DEFAULT 'asdf' ON ERROR);
362 --error ER_TRUNCATED_WRONG_VALUE
363 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DECIMAL
364 DEFAULT 'asdf' ON ERROR);
365 --error ER_TRUNCATED_WRONG_VALUE
366 SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING YEAR
367 DEFAULT 'asdf' ON ERROR);
368 --error ER_INVALID_JSON_TEXT_IN_PARAM
369 SELECT JSON_VALUE('""', '$' RETURNING JSON DEFAULT 'not JSON' ON ERROR);
370
371 --echo # Test ON ERROR clause
372 SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED);
373 SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED NULL ON ERROR);
374 --error ER_PARSE_ERROR
375 SELECT JSON_VALUE('{"data": "asd"}',
376 '$.data' RETURNING SIGNED NULL ON ERROR NULL ON ERROR);
377 --error ER_INVALID_JSON_TEXT_IN_PARAM
378 SELECT JSON_VALUE('{"data": 123', '$.data' RETURNING SIGNED ERROR ON ERROR);
379 --error ER_INVALID_JSON_PATH
380 SELECT JSON_VALUE('{"data": 123}', '.data' RETURNING SIGNED ERROR ON ERROR);
381 --error ER_TRUNCATED_WRONG_VALUE
382 SELECT JSON_VALUE('{"data": "asd"}', '$.data'
383 RETURNING SIGNED DEFAULT '{"aaa":321}' ON ERROR);
384
385 SELECT JSON_VALUE('{"data": "asd"}', '$.data'
386 RETURNING SIGNED DEFAULT 321 ON ERROR);
387 SELECT JSON_VALUE('{"data": "123.45"}', '$.data' RETURNING SIGNED);
388 SELECT JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
389 RETURNING SIGNED);
390 SELECT JSON_VALUE('{"data": "-99999"}', '$.data' RETURNING UNSIGNED);
391 SELECT JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
392 RETURNING CHAR(10));
393 SELECT JSON_VALUE('"abc"', '$' RETURNING DATE DEFAULT '2000-01-02' ON ERROR);
394 SELECT JSON_VALUE('"abc"', '$' RETURNING DATE
395 DEFAULT DATE'2000-01-02' ON ERROR);
396 SELECT JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT '12:13:14' ON ERROR);
397 SELECT JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT TIME'12:13:14' ON ERROR);
398 SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME
399 DEFAULT '2000-01-02 12:13:14' ON ERROR);
400 SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME
401 DEFAULT TIMESTAMP'2000-01-02 12:13:14' ON ERROR);
402 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) DEFAULT 'x' ON ERROR);
403 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET utf16
404 DEFAULT 'x' ON ERROR) v;
405 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET ascii
406 DEFAULT 'a' ON ERROR) v;
407 --error ER_DATA_OUT_OF_RANGE
408 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET ascii
409 DEFAULT 'æ' ON ERROR) v;
410 SELECT JSON_VALUE('"abc"', '$' RETURNING DECIMAL DEFAULT 123 ON ERROR);
411 SELECT JSON_VALUE('"abc"', '$' RETURNING FLOAT DEFAULT 123 ON ERROR);
412 SELECT JSON_VALUE('"abc"', '$' RETURNING DOUBLE DEFAULT 123 ON ERROR);
413 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 1988 ON ERROR);
414 --error ER_DATA_OUT_OF_RANGE
415 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 1 ON ERROR);
416 --error ER_DATA_OUT_OF_RANGE
417 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 2270 ON ERROR);
418 --error ER_DATA_OUT_OF_RANGE
419 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT -1 ON ERROR);
420
421 --error ER_DATA_OUT_OF_RANGE
422 SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED ERROR ON ERROR);
423 --error ER_DATA_OUT_OF_RANGE
424 SELECT JSON_VALUE('-1', '$' RETURNING UNSIGNED ERROR ON ERROR);
425 --error ER_DATA_OUT_OF_RANGE
426 SELECT JSON_VALUE('"abc"', '$' RETURNING DATE ERROR ON ERROR);
427 --error ER_DATA_OUT_OF_RANGE
428 SELECT JSON_VALUE('"abc"', '$' RETURNING TIME ERROR ON ERROR);
429 --error ER_DATA_OUT_OF_RANGE
430 SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME ERROR ON ERROR);
431 --error ER_DATA_OUT_OF_RANGE
432 SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) ERROR ON ERROR);
433 --error ER_DATA_OUT_OF_RANGE
434 SELECT JSON_VALUE('"abc"', '$' RETURNING DECIMAL ERROR ON ERROR);
435 --error ER_DATA_OUT_OF_RANGE
436 SELECT JSON_VALUE('"abc"', '$' RETURNING FLOAT ERROR ON ERROR);
437 --error ER_DATA_OUT_OF_RANGE
438 SELECT JSON_VALUE('"abc"', '$' RETURNING DOUBLE ERROR ON ERROR);
439 --error ER_DATA_OUT_OF_RANGE
440 SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR ERROR ON ERROR);
441
442 SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED NULL ON ERROR) v1,
443 JSON_VALUE('-1', '$' RETURNING UNSIGNED NULL ON ERROR) v2,
444 JSON_VALUE('"abc"', '$' RETURNING DATE NULL ON ERROR) v3,
445 JSON_VALUE('"abc"', '$' RETURNING TIME NULL ON ERROR) v4,
446 JSON_VALUE('"abc"', '$' RETURNING DATETIME NULL ON ERROR) v5,
447 JSON_VALUE('"abc"', '$' RETURNING CHAR(1) NULL ON ERROR) v6,
448 JSON_VALUE('"abc"', '$' RETURNING DECIMAL NULL ON ERROR) v7,
449 JSON_VALUE('"abc"', '$' RETURNING FLOAT NULL ON ERROR) v8,
450 JSON_VALUE('"abc"', '$' RETURNING DOUBLE NULL ON ERROR) v9,
451 JSON_VALUE('"abc"', '$' RETURNING YEAR NULL ON ERROR) v10;
452
453 SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED) v1,
454 JSON_VALUE('-1', '$' RETURNING UNSIGNED) v2,
455 JSON_VALUE('"abc"', '$' RETURNING DATE) v3,
456 JSON_VALUE('"abc"', '$' RETURNING TIME) v4,
457 JSON_VALUE('"abc"', '$' RETURNING DATETIME) v5,
458 JSON_VALUE('"abc"', '$' RETURNING CHAR(1)) v6,
459 JSON_VALUE('"abc"', '$' RETURNING DECIMAL) v7,
460 JSON_VALUE('"abc"', '$' RETURNING FLOAT) v8,
461 JSON_VALUE('"abc"', '$' RETURNING DOUBLE) v9,
462 JSON_VALUE('"abc"', '$' RETURNING YEAR) v10;
463
464 --echo # Test ON EMPTY in combination with ON ERROR.
465 SELECT JSON_VALUE('{"data": "asd"}',
466 '$.data' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR);
467 --error ER_PARSE_ERROR
468 SELECT JSON_VALUE('{"data": "asd"}',
469 '$.data' RETURNING SIGNED NULL ON ERROR NULL ON EMPTY);
470 --sorted_result
471 SELECT j, JSON_VALUE(j, '$.data' RETURNING SIGNED DEFAULT 111 ON EMPTY
472 DEFAULT 222 ON ERROR) AS jv
473 FROM (SELECT '{"data": "asd"}' AS j UNION ALL SELECT '{"num": 123}') t1;
474 --error ER_INVALID_JSON_TEXT_IN_PARAM
475 SELECT JSON_VALUE('not json', '$' RETURNING JSON ERROR ON EMPTY ERROR ON ERROR);
476
477 --echo # Returning array types is not supported.
478 --error ER_PARSE_ERROR
479 SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED ARRAY);
480
481 --echo # Test defaults within range.
482 SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT -123 ON EMPTY) a,
483 JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT '-123' ON EMPTY) b,
484 JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT 123 ON EMPTY) c,
485 JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT '123' ON EMPTY) d,
486 JSON_VALUE('{}', '$.a' RETURNING DATE
487 DEFAULT DATE'2020-04-05' ON EMPTY) e,
488 JSON_VALUE('{}', '$.a' RETURNING DATE
489 DEFAULT '2020-04-05' ON EMPTY) f,
490 JSON_VALUE('{}', '$.a' RETURNING TIME(6)
491 DEFAULT TIME'09:10:11.22' ON EMPTY) g,
492 JSON_VALUE('{}', '$.a' RETURNING TIME(6)
493 DEFAULT '09:10:11.22' ON EMPTY) h,
494 JSON_VALUE('{}', '$.a' RETURNING DATETIME(6)
495 DEFAULT '2020-04-05 09:10:11.22' ON EMPTY) i,
496 JSON_VALUE('{}', '$.a'
497 RETURNING DATETIME(6)
498 DEFAULT TIMESTAMP'2020-04-05 09:10:11.22' ON EMPTY) j,
499 JSON_VALUE('{}', '$.a' DEFAULT 'empty' ON EMPTY) k,
500 JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 3) DEFAULT 1.23 ON EMPTY) l,
501 JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 3)
502 DEFAULT '1.23' ON EMPTY) m,
503 JSON_VALUE('{}', '$.a' RETURNING JSON DEFAULT '"empty"' ON EMPTY) n,
504 JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT -1234.5 ON EMPTY) o,
505 JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT '-1234.5' ON EMPTY) p,
506 JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT -1e308 ON EMPTY) q,
507 JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT '-1e308' ON EMPTY) r,
508 JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT 1901 ON EMPTY) s,
509 JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT '2155' ON EMPTY) t;
510
511 --echo # Test defaults out of range.
512 --error ER_DATA_OUT_OF_RANGE
513 SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT 9223372036854775808 ON EMPTY) v;
514 --error ER_DATA_OUT_OF_RANGE
515 SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT '9223372036854775808' ON EMPTY) v;
516 --error ER_DATA_OUT_OF_RANGE
517 SELECT JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT -1 ON EMPTY) v;
518 --error ER_DATA_OUT_OF_RANGE
519 SELECT JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT '-1' ON EMPTY) v;
520 --error ER_TRUNCATED_WRONG_VALUE
521 SELECT JSON_VALUE('{}', '$.a' RETURNING DATE DEFAULT 'abc' ON EMPTY) v;
522 --error ER_TRUNCATED_WRONG_VALUE
523 SELECT JSON_VALUE('{}', '$.a' RETURNING TIME DEFAULT 'abc' ON EMPTY) v;
524 --error ER_DATA_OUT_OF_RANGE
525 SELECT JSON_VALUE('{}', '$.a' RETURNING TIME(2)
526 DEFAULT TIME'12:13:14.123' ON EMPTY) v;
527 --error ER_DATA_OUT_OF_RANGE
528 SELECT JSON_VALUE('{}', '$.a' RETURNING TIME(2)
529 DEFAULT '12:13:14.123' ON EMPTY) v;
530 --error ER_TRUNCATED_WRONG_VALUE
531 SELECT JSON_VALUE('{}', '$.a' RETURNING DATETIME DEFAULT 'abc' ON EMPTY) v;
532 --error ER_DATA_OUT_OF_RANGE
533 SELECT JSON_VALUE('{}', '$.a'
534 RETURNING DATETIME(2) DEFAULT TIMESTAMP'2000-01-02 12:13:14.123' ON EMPTY) v;
535 --error ER_DATA_OUT_OF_RANGE
536 SELECT JSON_VALUE('{}', '$.a' RETURNING DATETIME(2)
537 DEFAULT '2000-01-02 12:13:14.123' ON EMPTY) v;
538 --error ER_DATA_OUT_OF_RANGE
539 SELECT JSON_VALUE('{}', '$.a' RETURNING CHAR(1) DEFAULT 'empty' ON EMPTY) v;
540 --error ER_DATA_OUT_OF_RANGE
541 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(1, 0) DEFAULT 12 ON EMPTY) v;
542 --error ER_DATA_OUT_OF_RANGE
543 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(1, 0) DEFAULT '12' ON EMPTY) v;
544 --error ER_DATA_OUT_OF_RANGE
545 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 1) DEFAULT 1.23 ON EMPTY) v;
546 --error ER_DATA_OUT_OF_RANGE
547 SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 1) DEFAULT '1.23' ON EMPTY) v;
548 --error ER_DATA_OUT_OF_RANGE
549 SELECT JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT 1e308 ON EMPTY) v;
550 --error ER_DATA_OUT_OF_RANGE
551 SELECT JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT '1e308' ON EMPTY) v;
552 --error ER_TRUNCATED_WRONG_VALUE
553 SELECT JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT 'xyz' ON EMPTY) v;
554 --error ER_DATA_OUT_OF_RANGE
555 SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT '-1990' ON EMPTY) v;
556 --error ER_DATA_OUT_OF_RANGE
557 SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT -1990 ON EMPTY) v;
558 --error ER_TRUNCATED_WRONG_VALUE
559 SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT 'xyz' ON EMPTY) v;
560
561 # Zero dates and invalid dates should not be accepted in the DEFAULT
562 # values, even if strict mode, NO_ZERO_DATE and NO_ZERO_IN_DATE are
563 # disabled, and ALLOW_INVALID_DATES is enabled.
564 --error ER_TRUNCATED_WRONG_VALUE
565 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON EMPTY);
566 --error ER_TRUNCATED_WRONG_VALUE
567 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON ERROR);
568 --error ER_TRUNCATED_WRONG_VALUE
569 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-01-00' ON EMPTY);
570 --error ER_TRUNCATED_WRONG_VALUE
571 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-00-01' ON ERROR);
572 --error ER_TRUNCATED_WRONG_VALUE
573 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON EMPTY);
574 --error ER_TRUNCATED_WRONG_VALUE
575 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON ERROR);
576 SET @saved_sql_mode = @@sql_mode;
577 SET sql_mode = 'ALLOW_INVALID_DATES';
578 --error ER_TRUNCATED_WRONG_VALUE
579 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON EMPTY);
580 --error ER_TRUNCATED_WRONG_VALUE
581 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON ERROR);
582 --error ER_TRUNCATED_WRONG_VALUE
583 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-01-00' ON EMPTY);
584 --error ER_TRUNCATED_WRONG_VALUE
585 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-00-01' ON ERROR);
586 --error ER_TRUNCATED_WRONG_VALUE
587 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON EMPTY);
588 --error ER_TRUNCATED_WRONG_VALUE
589 SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON ERROR);
590 SET sql_mode = @saved_sql_mode;
591
592 # Verify that the ON ERROR clause (by default NULL ON ERROR) kicks in
593 # also when accessing the result of JSON_VALUE as another type than
594 # the one in the RETURNING clause.
595
596 # Use integers as strings.
597 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING SIGNED) AS c1,
598 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING SIGNED), 10) AS c2,
599 JSON_VALUE('{"a":"b"}', '$.a' RETURNING UNSIGNED) AS c3,
600 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING UNSIGNED), 10) AS c4;
601
602 # Use a DECIMAL as a string.
603 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING DECIMAL(5, 2)) AS c1,
604 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DECIMAL(5, 2)), 10) AS c2;
605
606 # Use floting-point values as strings.
607 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING FLOAT) AS c1,
608 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING FLOAT), 10) AS c2,
609 JSON_VALUE('{"a":"b"}', '$.a' RETURNING DOUBLE) AS c3,
610 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DOUBLE), 10) AS c4;
611
612 # Use a CHAR(1) as a number.
613 SELECT JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1)) AS c1,
614 JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1)) + 1 AS c2,
615 REPEAT('x', JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1))) AS c3;
616
617 # Use temporal values as strings.
618 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATE) AS c1,
619 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATE), 10) AS c2,
620 JSON_VALUE('{"a":"b"}', '$.a' RETURNING TIME) AS c3,
621 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING TIME), 10) AS c4,
622 JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATETIME) AS c5,
623 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATETIME), 10) AS c6;
624
625 #Use year as strings.
626 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING YEAR) AS c1,
627 REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING YEAR), 10) AS c2;
628
629 # Use a JSON value as a number.
630 SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING JSON) AS c1,
631 JSON_VALUE('{"a":"b"}', '$.a' RETURNING JSON) + 1 AS c2;
632
633 # Test conversions to DOUBLE.
634 SELECT JSON_VALUE('1', '$' RETURNING SIGNED) + 1.0e0;
635 SELECT JSON_VALUE('1', '$' RETURNING UNSIGNED) + 1.0e0;
636 SELECT JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) + 1.0e0;
637 SELECT JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) + 1.0e0;
638 SELECT JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) + 1.0e0;
639 SELECT JSON_VALUE('"1"', '$') + 1.0e0;
640 SELECT JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)) + 1.0e0;
641 SELECT JSON_VALUE('1', '$' RETURNING JSON) + 1.0e0;
642 SELECT JSON_VALUE('1.2', '$' RETURNING FLOAT) + 1.0e0;
643 SELECT JSON_VALUE('1988', '$' RETURNING YEAR) + 1.0e0;
644
645 # Test conversions to DECIMAL.
646 SELECT JSON_VALUE('123', '$' RETURNING SIGNED) DIV 2;
647 SELECT JSON_VALUE('123', '$' RETURNING UNSIGNED) DIV 2;
648 SELECT JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) DIV 2;
649 SELECT JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) DIV 2;
650 SELECT JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) DIV 2;
651 SELECT JSON_VALUE('"123"', '$') DIV 2;
652 SELECT JSON_VALUE('123', '$' RETURNING JSON) DIV 2;
653 SELECT JSON_VALUE('12.3', '$' RETURNING FLOAT) DIV 2;
654 SELECT JSON_VALUE('12.3', '$' RETURNING DOUBLE) DIV 2;
655 SELECT CAST(JSON_VALUE('123', '$' RETURNING SIGNED) AS DECIMAL);
656 SELECT CAST(JSON_VALUE('123', '$' RETURNING UNSIGNED) AS DECIMAL);
657 SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS DECIMAL);
658
659 # Test conversions to string.
660 SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING SIGNED));
661 SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING UNSIGNED));
662 SELECT CONCAT('x', JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
663 SELECT CONCAT('x', JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME));
664 SELECT CONCAT('x', JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME));
665 SELECT CONCAT('x', JSON_VALUE('"1"', '$'));
666 SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)));
667 SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING JSON));
668 SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING FLOAT));
669 SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DOUBLE));
670 SELECT CONCAT('x', JSON_VALUE('1988', '$' RETURNING YEAR));
671
672 # Test conversions to DATE.
673 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING SIGNED), INTERVAL 1 DAY);
674 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING UNSIGNED), INTERVAL 1 DAY);
675 SET TIMESTAMP=UNIX_TIMESTAMP('2019-01-01 12:00:00');
676 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATE);
677 SET TIMESTAMP=DEFAULT;
678 SELECT DATE_ADD(JSON_VALUE('"2000-01-02"', '$'), INTERVAL 1 DAY);
679 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DECIMAL), INTERVAL 1 DAY);
680 SELECT DATE_ADD(JSON_VALUE('"2000-01-02"', '$' RETURNING JSON), INTERVAL 1 DAY);
681 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING FLOAT), INTERVAL 1 DAY);
682 SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DOUBLE), INTERVAL 1 DAY);
683 SELECT DATE_ADD(JSON_VALUE('1988', '$' RETURNING YEAR), INTERVAL 1 DAY);
684
685 # Test conversions to TIME.
686 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING SIGNED));
687 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING UNSIGNED));
688 SELECT TIME_TO_SEC(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
689 SELECT TIME_TO_SEC(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME));
690 SELECT TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$'));
691 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DECIMAL));
692 SELECT TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$' RETURNING JSON));
693 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING FLOAT));
694 SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DOUBLE));
695 SELECT TIME_TO_SEC(JSON_VALUE('2030', '$' RETURNING YEAR));
696
697 # Test conversions to DATETIME.
698 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
699 '$' RETURNING SIGNED));
700 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
701 '$' RETURNING UNSIGNED));
702 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
703 SET TIMESTAMP=UNIX_TIMESTAMP('2019-01-01 12:00:00');
704 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATETIME);
705 SET TIMESTAMP=DEFAULT;
706 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02 12:13:14"', '$'));
707 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
708 '$' RETURNING DECIMAL(14, 0)));
709 --echo # Warns because 20000102121314 gets truncated to fit in a FLOAT,
710 --echo # and is not a valid timestamp after truncation.
711 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
712 '$' RETURNING FLOAT));
713 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
714 '$' RETURNING DOUBLE));
715 SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('1988', '$' RETURNING YEAR));
716
717 # Test conversions to SIGNED.
718 SELECT CAST(JSON_VALUE('1234', '$' RETURNING UNSIGNED) AS SIGNED);
719 SELECT CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS SIGNED);
720 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS SIGNED);
721 SELECT CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
722 AS SIGNED);
723 SELECT CAST(JSON_VALUE('"1234"', '$') AS SIGNED);
724 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS SIGNED);
725 SELECT CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS SIGNED);
726 SELECT CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS SIGNED);
727 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS SIGNED);
728 SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS SIGNED);
729 SELECT CAST(JSON_VALUE('43', '$' RETURNING YEAR) AS SIGNED);
730 SELECT CAST(JSON_VALUE('88', '$' RETURNING YEAR) AS SIGNED);
731 SELECT REPEAT('x', JSON_VALUE('10', '$' RETURNING JSON));
732
733 # Test conversions to UNSIGNED.
734 SELECT CAST(JSON_VALUE('1234', '$' RETURNING SIGNED) AS UNSIGNED);
735 SELECT CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS UNSIGNED);
736 SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS UNSIGNED);
737 SELECT CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
738 AS UNSIGNED);
739 SELECT CAST(JSON_VALUE('"1234"', '$') AS UNSIGNED);
740 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS UNSIGNED);
741 SELECT CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS UNSIGNED);
742 SELECT CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS UNSIGNED);
743 SELECT CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS UNSIGNED);
744 SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS UNSIGNED);
745
746 --echo # SIGNED ranges.
747 SELECT JSON_VALUE('9223372036854775807', '$' RETURNING SIGNED) AS max_val,
748 JSON_VALUE('9223372036854775808', '$' RETURNING SIGNED) AS plus_one,
749 JSON_VALUE('-9223372036854775808', '$' RETURNING SIGNED) AS min_val,
750 JSON_VALUE('-9223372036854775809', '$' RETURNING SIGNED) AS minus_one;
751 SELECT JSON_VALUE('"9223372036854775807"', '$' RETURNING SIGNED) AS max_val,
752 JSON_VALUE('"9223372036854775808"', '$' RETURNING SIGNED) AS plus_one,
753 JSON_VALUE('"-9223372036854775808"', '$' RETURNING SIGNED) AS min_val,
754 JSON_VALUE('"-9223372036854775809"', '$' RETURNING SIGNED) AS minus_one;
755 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
756 DEFAULT '9223372036854775807' ON EMPTY) AS a,
757 JSON_VALUE('{}', '$.x' RETURNING SIGNED
758 DEFAULT 9223372036854775807 ON EMPTY) AS b,
759 JSON_VALUE('{}', '$.x' RETURNING SIGNED
760 DEFAULT '-9223372036854775808' ON EMPTY) AS c,
761 JSON_VALUE('{}', '$.x' RETURNING SIGNED
762 DEFAULT -9223372036854775808 ON EMPTY) AS d;
763 --error ER_DATA_OUT_OF_RANGE
764 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
765 DEFAULT '9223372036854775808' ON EMPTY);
766 --error ER_DATA_OUT_OF_RANGE
767 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
768 DEFAULT 9223372036854775808 ON EMPTY);
769 --error ER_DATA_OUT_OF_RANGE
770 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
771 DEFAULT '-9223372036854775809' ON EMPTY);
772 --error ER_DATA_OUT_OF_RANGE
773 SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
774 DEFAULT -9223372036854775809 ON EMPTY);
775
776 --echo # UNSIGNED ranges.
777 SELECT JSON_VALUE('18446744073709551615', '$' RETURNING UNSIGNED) AS max_val,
778 JSON_VALUE('18446744073709551616', '$' RETURNING UNSIGNED) AS plus_one,
779 JSON_VALUE('0', '$' RETURNING UNSIGNED) AS min_val,
780 JSON_VALUE('-1', '$' RETURNING UNSIGNED) AS minus_one;
781 SELECT JSON_VALUE('"18446744073709551615"', '$' RETURNING UNSIGNED) AS max_val,
782 JSON_VALUE('"18446744073709551616"', '$' RETURNING UNSIGNED) AS plus_one,
783 JSON_VALUE('"0"', '$' RETURNING UNSIGNED) AS min_val,
784 JSON_VALUE('"-1"', '$' RETURNING UNSIGNED) AS minus_one;
785 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
786 DEFAULT '18446744073709551615' ON EMPTY) AS a,
787 JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
788 DEFAULT 18446744073709551615 ON EMPTY) AS b,
789 JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
790 DEFAULT '0' ON EMPTY) AS c,
791 JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
792 DEFAULT 0 ON EMPTY) AS d;
793 --error ER_DATA_OUT_OF_RANGE
794 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
795 DEFAULT '18446744073709551616' ON EMPTY);
796 --error ER_DATA_OUT_OF_RANGE
797 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
798 DEFAULT 18446744073709551616 ON EMPTY);
799 --error ER_DATA_OUT_OF_RANGE
800 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
801 DEFAULT '-1' ON EMPTY);
802 --error ER_DATA_OUT_OF_RANGE
803 SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
804 DEFAULT -1 ON EMPTY);
805
806 --echo # Check that the nullability of JSON_VALUE is correct.
807
808 --echo # Should be nullable because of implicit NULL ON EMPTY/NULL ON ERROR.
809 CREATE VIEW v AS SELECT JSON_VALUE('{}', '$.x') AS c1,
810 JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY) AS c2,
811 JSON_VALUE('{}', '$.x' DEFAULT '2' ON ERROR) AS c3;
812 DESCRIBE v;
813 DROP VIEW v;
814
815 --echo # Should be nullable because of explicit NULL ON EMPTY/NULL ON ERROR.
816 CREATE VIEW v AS
817 SELECT JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY NULL ON ERROR) AS c1,
818 JSON_VALUE('{}', '$.x' NULL ON EMPTY DEFAULT '2' ON ERROR) AS c2,
819 JSON_VALUE('{}', '$.x' ERROR ON EMPTY NULL ON ERROR) AS c3,
820 JSON_VALUE('{}', '$.x' NULL ON EMPTY ERROR ON ERROR) AS c4;
821 DESCRIBE v;
822 DROP VIEW v;
823
824 --echo # Should be nullable because the first argument is nullable.
825 CREATE VIEW v AS
826 SELECT JSON_VALUE(NULL, '$.x' ERROR ON EMPTY ERROR ON ERROR) AS c1;
827 DESCRIBE v;
828 DROP VIEW v;
829
830 --echo # Should not be nullable.
831 CREATE VIEW v AS
832 SELECT JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY DEFAULT '2' ON ERROR) c1,
833 JSON_VALUE('{}', '$.x' ERROR ON EMPTY ERROR ON ERROR) c2;
834 DESCRIBE v;
835 DROP VIEW v;
836
837 --echo # Test index creation/usage
838 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING SIGNED))));
839 SHOW CREATE TABLE t1;
840 INSERT INTO t1 VALUES('1'),('2'),('-3'),('-4');
841 ANALYZE TABLE t1;
842 --let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING SIGNED) = -3
843 --eval EXPLAIN $query
844 --eval $query
845 let $query = SELECT * FROM t1
846 WHERE JSON_VALUE(j, '$' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR) = -3;
847 --eval EXPLAIN $query
848 --eval $query
849 --echo # The index is not used if the JSON_VALUE expressions differ.
850 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = -3;
851 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING UNSIGNED) = -3;
852 EXPLAIN SELECT * FROM t1
853 WHERE JSON_VALUE(j, '$' RETURNING SIGNED DEFAULT '4' ON EMPTY) = -3;
854 EXPLAIN SELECT * FROM t1
855 WHERE JSON_VALUE(j, '$' RETURNING SIGNED ERROR ON ERROR) = -3;
856 DROP TABLE t1;
857
858 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING UNSIGNED))));
859 SHOW CREATE TABLE t1;
860 INSERT INTO t1 VALUES('1'),('2'),('3'),('4');
861 ANALYZE TABLE t1;
862 --let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING UNSIGNED) = 3
863 --eval EXPLAIN $query
864 --eval $query
865 INSERT INTO t1 VALUES('-1'),('-2');
866 DROP TABLE t1;
867
868 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DECIMAL(3, 2)))));
869 SHOW CREATE TABLE t1;
870 INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
871 ANALYZE TABLE t1;
872 let $query =
873 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(3, 2)) = 3.3;
874 --eval EXPLAIN $query
875 --eval $query
876 --echo # The index is not used if the JSON_VALUE expressions differ.
877 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(3, 0)) = 3.3;
878 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(2, 2)) = 3.3;
879 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(4, 0)) = 3.3;
880 DROP TABLE t1;
881
882 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING FLOAT))));
883 SHOW CREATE TABLE t1;
884 INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
885 ANALYZE TABLE t1;
886 let $query =
887 SELECT * FROM t1
888 WHERE JSON_VALUE(j, '$' RETURNING FLOAT) = CAST(3.3 AS FLOAT);
889 --eval EXPLAIN $query
890 --eval $query
891 --echo # The index is not used if the JSON_VALUE expressions differ.
892 EXPLAIN SELECT * FROM t1
893 WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS FLOAT);
894 DROP TABLE t1;
895
896 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DOUBLE))));
897 SHOW CREATE TABLE t1;
898 INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
899 ANALYZE TABLE t1;
900 let $query =
901 SELECT * FROM t1
902 WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS DOUBLE);
903 --eval EXPLAIN $query
904 --eval $query
905 EXPLAIN SELECT * FROM t1
906 WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS DOUBLE);
907 --echo # The index is not used if the JSON_VALUE expressions differ.
908 EXPLAIN SELECT * FROM t1
909 WHERE JSON_VALUE(j, '$' RETURNING FLOAT) = CAST(3.3 AS DOUBLE);
910 DROP TABLE t1;
911
912 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING CHAR(10)))));
913 SHOW CREATE TABLE t1;
914 INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"FghJ"'),('"fghj"'),('"hjkl"');
915 ANALYZE TABLE t1;
916 --let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10)) = 'fghj'
917 --eval EXPLAIN $query
918 --eval $query
919 --echo # The index is not used if the JSON_VALUE expressions differ.
920 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING CHAR(11)) = 'fghj';
921 EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = 'fghj';
922 DROP TABLE t1;
923
924 CREATE TABLE t1(j JSON,
925 KEY i1((JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf8mb4))));
926 SHOW CREATE TABLE t1;
927 INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"FghJ"'),('"fghj"'),('"hjkl"');
928 ANALYZE TABLE t1;
929 let $query =
930 SELECT * FROM t1
931 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf8mb4) = 'fghj';
932 --eval EXPLAIN $query
933 --eval $query
934 --echo # The index is not used if the JSON_VALUE expressions differ.
935 EXPLAIN SELECT * FROM t1
936 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET latin1) = 'fghj';
937 DROP TABLE t1;
938
939 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$'))));
940 SHOW CREATE TABLE t1;
941 INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"fghj"'),('"hjkl"');
942 ANALYZE TABLE t1;
943 --let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = 'fghj'
944 --eval EXPLAIN $query
945 --eval $query
946 DROP TABLE t1;
947
948 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DATETIME))));
949 SHOW CREATE TABLE t1;
950 INSERT INTO t1 VALUES('"2019-01-01 11:11:11"'),('"2019-01-01 11:11:12"'),
951 ('"2019-01-01 11:11:13"'),('"2019-01-01 11:11:14"');
952 ANALYZE TABLE t1;
953 let $query =
954 SELECT * FROM t1 WHERE
955 JSON_VALUE(j, '$' RETURNING DATETIME) = TIMESTAMP'2019-01-01 11:11:12';
956 --eval EXPLAIN $query
957 --eval $query
958 --echo # The index is not used if the JSON_VALUE expressions differ.
959 EXPLAIN SELECT * FROM t1
960 WHERE JSON_VALUE(j, '$' RETURNING DATE) = TIMESTAMP'2019-01-01 11:11:12';
961 DROP TABLE t1;
962
963 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING TIME))));
964 SHOW CREATE TABLE t1;
965 INSERT INTO t1 VALUES('"11:11:11"'),('"11:11:12"'),
966 ('"11:11:13"'),('"11:11:14"');
967 ANALYZE TABLE t1;
968 let $query =
969 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING TIME) = TIME'11:11:12';
970 --eval EXPLAIN $query
971 --eval $query
972 DROP TABLE t1;
973
974 CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DATE))));
975 SHOW CREATE TABLE t1;
976 INSERT INTO t1 VALUES('"2019-01-01"'),('"2019-01-02"'),
977 ('"2019-01-03"'),('"2019-01-04"');
978 ANALYZE TABLE t1;
979 let $query =
980 SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DATE) = DATE'2019-01-02';
981 --eval EXPLAIN $query
982 --eval $query
983 DROP TABLE t1;
984
985 # Verify that the null_value flag is reset between rows.
986 CREATE TABLE t(j JSON);
987 INSERT INTO t VALUES ('[1]'), (NULL), ('[2]'), (NULL), ('[3]');
988 --sorted_result
989 SELECT JSON_VALUE(j, '$[0]' RETURNING SIGNED) AS v FROM t;
990 DROP TABLE t;
991
992 # Verify that JSON_VALUE can be used in INSERT statements.
993 CREATE TABLE t1(id INT PRIMARY KEY, vc VARCHAR(100));
994 INSERT INTO t1 VALUES
995 (1, '{"a":"a very long string"}'),
996 (2, '{invalid json}'),
997 (3, '{"a":"good"}'),
998 (4, '{"b":"xyz"}');
999 CREATE TABLE t2(id INT PRIMARY KEY, vc VARCHAR(100));
1000 INSERT INTO t2
1001 SELECT id, JSON_VALUE(vc, '$.a' RETURNING CHAR(10)
1002 DEFAULT 'empty' ON EMPTY
1003 DEFAULT 'error' ON ERROR)
1004 FROM t1;
1005 SELECT * FROM t2 ORDER BY id;
1006 DROP TABLE t1, t2;
1007
1008 --echo #
1009 --echo # Bug#30141215: SIG6 IN ROW_UPD_SEC_INDEX_ENTRY_LOW() AT ROW/ROW0UPD.CC
1010 --echo #
1011 CREATE TABLE t (j JSON, KEY ((JSON_VALUE(j, '$[0]' RETURNING CHAR(1)))));
1012 SELECT * FROM t;
1013 INSERT INTO t VALUES ('["abcde"]');
1014 # Used to hit an assertion.
1015 DELETE FROM t;
1016 DROP TABLE t;
1017
1018 --echo #
1019 --echo # Bug#30084642: ERROR FROM MEMBER OF AFTER ADDING FUNCTIONAL INDEX
1020 --echo #
1021 CREATE TABLE t(j JSON, KEY ((JSON_VALUE(j, '$' RETURNING SIGNED))));
1022 INSERT INTO t VALUES ('1'), ('2');
1023 SELECT * FROM t WHERE 1 MEMBER OF (j->'$');
1024 DROP TABLE t;
1025
1026 --echo #
1027 --echo # Bug#30330513: SIG6 IN ROW_UPD_SEC_INDEX_ENTRY_LOW()
1028 --echo #
1029 CREATE TABLE t
1030 (j JSON, KEY ((JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf32))));
1031 INSERT INTO t VALUES
1032 ('"foobar"'),
1033 ('"Peh4UsNdzmNQbUhbXSK7L0uY0SP1U5F1PvIDDqbaUpR2Gu8NTnXnUmsDy"');
1034 DELETE FROM t;
1035 DROP TABLE t;
1036
1037 --echo #
1038 --echo # Bug#30315526: SIG6 IN ITEM::EVALUATE() AT SQL/ITEM.CC
1039 --echo #
1040 SELECT JSON_VALUE('"1990-12-29"', '$' RETURNING DATE) IS NULL AS c1,
1041 JSON_VALUE('"12:00:00"', '$' RETURNING TIME) IS NULL AS c2,
1042 JSON_VALUE('"1990-12-29 12:00:00"', '$' RETURNING TIME) IS NULL AS c3;
1043
1044 --echo #
1045 --echo # Bug#30320927: SIG6 IN MY_PACKED_TIME_MAKE() AT MYSYS/MY_TIME.CC
1046 --echo #
1047 SELECT JSON_VALUE('[]', '$[0]' RETURNING DATE)
1048 BETWEEN DATE'2019-01-01' AND DATE'2020-01-01' AS c1,
1049 JSON_VALUE('"abc"', '$' RETURNING DATE)
1050 BETWEEN DATE'2019-01-01' AND DATE'2020-01-01' AS c2,
1051 JSON_VALUE('[]', '$[0]' RETURNING TIME)
1052 BETWEEN TIME'11:00:00' AND TIME'12:00:00' AS c3,
1053 JSON_VALUE('"abc"', '$' RETURNING TIME)
1054 BETWEEN TIME'11:00:00' AND TIME'12:00:00' AS c4;
1055
1056 --echo #
1057 --echo # Bug#30349041: SIG6 PRINT_CAST_TYPE() AT SQL/ITEM_JSON_FUNC.CC
1058 --echo #
1059 EXPLAIN SELECT JSON_VALUE('true', '$' RETURNING JSON) v;
1060
1061 --echo #
1062 --echo # Bug#30631787: SIG6 IN SQL_CMD_DML::PREPARE() AT SQL/SQL_SELECT.CC
1063 --echo #
1064 --source include/turn_off_strict_mode.inc
1065 --error ER_TRUNCATED_WRONG_VALUE
1066 SELECT JSON_VALUE('{}', '$' RETURNING TIME DEFAULT '184' ON EMPTY);
1067 --source include/restore_strict_mode.inc
1068
1069 --echo #
1070 --echo # Bug#30633294: SIG11 IN STD::DEFAULT_DELETE() AT BITS/UNIQUE_PTR.H
1071 --echo #
1072 CREATE TABLE t(
1073 j JSON,
1074 gc VARCHAR(512) AS (JSON_VALUE(j, '$[*]' DEFAULT 'abc' ON EMPTY
1075 DEFAULT 'def' ON ERROR)));
1076 SELECT * FROM t;
1077 SELECT * FROM t;
1078 SELECT * FROM t;
1079 DROP TABLE t;
1080
1081 --echo #
1082 --echo # Bug#30720910: JSON_VALUE() DOES NOT CHECK PRECISION FOR DECIMAL TYPE
1083 --echo #
1084 SELECT JSON_VALUE('[12345.67]', '$[0]' RETURNING DECIMAL(5, 2)) v;
1085 --error ER_DATA_OUT_OF_RANGE
1086 SELECT JSON_VALUE('[12345.67]', '$[0]' RETURNING DECIMAL(5, 2) ERROR ON ERROR);
1087 --error ER_DATA_OUT_OF_RANGE
1088 SELECT JSON_VALUE('{}', '$.x' RETURNING DECIMAL(5, 2) DEFAULT 12345.67 ON EMPTY);
1089
1090 --echo #
1091 --echo # Bug#31168181 FILTERING USING JSON FIELD, IS TRUE DOES NOT WORK
1092 --echo #
1093
1094 CREATE TABLE test (id INT, j JSON);
1095 INSERT INTO test VALUES (1, '{"some_key": true}');
1096 INSERT INTO test VALUES (2, '{"some_key": false}');
1097 INSERT INTO test VALUES (3, '{"some_key": null}');
1098 INSERT INTO test VALUES (4, '{"some_key": "true"}'); # a string, NOT a literal!
1099
1100 # The results are not always intuitive. We want to test that a warning
1101 # is emitted when this happens. That should happen only for comparison against
1102 # TRUE or FALSE. Not for IS NULL as there is risk of false positives.
1103
1104 SELECT j, (j->'$.some_key' IS TRUE) AS ist,
1105 (j->'$.some_key' IS FALSE) AS isf,
1106 (j->'$.some_key' IS NULL) AS isn
1107 FROM test;
1108
1109 SELECT j FROM test
1110 WHERE j->'$.some_key';
1111
1112 SELECT j FROM test
1113 WHERE NOT j->'$.some_key';
1114
1115 # Test the workaround suggested by the warnings above; now results
1116 # should be good:
1117
1118 SELECT j, (json_value(j, '$.some_key' RETURNING SIGNED) IS TRUE) AS ist,
1119 (json_value(j, '$.some_key' RETURNING SIGNED) IS FALSE) AS isf,
1120 (json_value(j, '$.some_key' RETURNING SIGNED) IS NULL) AS isn
1121 FROM test;
1122
1123 SELECT j FROM test
1124 WHERE json_value(j, '$.some_key' RETURNING SIGNED);
1125
1126 SELECT j FROM test
1127 WHERE NOT json_value(j, '$.some_key' RETURNING SIGNED);
1128
1129 # Other cases of warning:
1130
1131 SELECT json_extract(json_object( "a",FALSE),'$.a') IS TRUE;
1132
1133 SELECT CAST(TRUE AS JSON) IS TRUE AS ist,
1134 CAST(TRUE AS JSON) IS FALSE AS isf;
1135
1136 SELECT CAST(FALSE AS JSON) IS TRUE AS ist,
1137 CAST(FALSE AS JSON) IS FALSE AS isf;
1138
1139 SELECT CAST(NULL AS JSON) IS TRUE AS ist,
1140 CAST(NULL AS JSON) IS FALSE AS isf;
1141
1142 SELECT CAST(123 AS JSON) IS TRUE AS ist,
1143 CAST(123 AS JSON) IS FALSE AS isf;
1144
1145 SELECT CAST('"ab"' AS JSON) IS TRUE AS ist,
1146 CAST('"ab"' AS JSON) IS FALSE AS isf;
1147
1148 DROP TABLE test;
1149
1150 --echo #
1151 --echo # Bug#31336625 JSON_VALUE(EXPRESSION PATH RETURNING TYPE) SHOULD RETURN SQL NULL FOR JSON NULL
1152 --echo #
1153
1154 # When we use ERROR ON ERROR below, it is to be sure that the expected NULL is
1155 # not due to an error.
1156
1157 SELECT JSON_VALUE('null', '$' RETURNING DATE ERROR ON ERROR),
1158 JSON_VALUE('null', '$' RETURNING DATE),
1159 JSON_VALUE('null', '$' RETURNING DATE) IS NULL;
1160
1161 SELECT JSON_VALUE('null', '$' RETURNING SIGNED ERROR ON ERROR),
1162 JSON_VALUE('null', '$' RETURNING SIGNED),
1163 JSON_VALUE('null', '$' RETURNING SIGNED) IS NULL;
1164
1165 SELECT JSON_VALUE('null', '$' RETURNING CHAR ERROR ON ERROR),
1166 JSON_VALUE('null', '$' RETURNING CHAR),
1167 JSON_VALUE('null', '$' RETURNING CHAR) IS NULL;
1168
1169 SELECT JSON_VALUE(CAST('null' AS JSON), '$' RETURNING CHAR ERROR ON ERROR);
1170 SELECT JSON_VALUE(CAST('null' AS JSON), '$' RETURNING SIGNED ERROR ON ERROR);
1171
1172 CREATE TABLE t(a JSON);
1173 INSERT INTO t VALUES('[1,2,3,null]');
1174 SELECT JSON_VALUE(a, '$[2]' RETURNING SIGNED),
1175 JSON_VALUE(a, '$[3]' RETURNING SIGNED ERROR ON ERROR),
1176 JSON_VALUE(a, '$[3]' RETURNING SIGNED) FROM t;
1177 DELETE FROM t;
1178 INSERT INTO t VALUES('{"k1":"xy", "k2":null}');
1179 SELECT JSON_VALUE(a, '$.k1' RETURNING CHAR),
1180 JSON_VALUE(a, '$.k2' RETURNING CHAR ERROR ON ERROR),
1181 JSON_VALUE(a, '$.k2' RETURNING CHAR) FROM t;
1182 DELETE FROM t;
1183 INSERT INTO t VALUES('{"k1":"2020-05-12", "k2":null}');
1184 SELECT JSON_VALUE(a, '$.k1' RETURNING DATE),
1185 JSON_VALUE(a, '$.k2' RETURNING DATE ERROR ON ERROR),
1186 JSON_VALUE(a, '$.k2' RETURNING DATE) FROM t;
1187 DROP TABLE t;
1188
1189 # It's possible to store and retrieve 3-value boolean logic values:
1190 SELECT JSON_VALUE(JSON_OBJECT('a', FALSE), '$.a' RETURNING SIGNED),
1191 JSON_VALUE(JSON_OBJECT('a', TRUE), '$.a' RETURNING SIGNED),
1192 JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED ERROR ON ERROR),
1193 JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED);
1194
1195 # As CHAR:
1196 SELECT JSON_VALUE(JSON_OBJECT('a', 'value'), '$.a'),
1197 JSON_VALUE(JSON_OBJECT('a', NULL), '$.a');
1198
1199 --echo #
1200 --echo # Bug#34554755: DISTINCT on FLOAT values gives duplicates
1201 --echo # with SQL_BIG_RESULT
1202 --echo #
1203
1204 # These two integers map to the same FLOAT value, so the comparison
1205 # should return TRUE.
1206 SELECT JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) =
1207 JSON_VALUE('1111111111111110', '$' RETURNING FLOAT) AS eq;
1208
1209 # Converting the FLOAT to other types should not keep the full DOUBLE precision.
1210 SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) AS CHAR) AS v;
1211 SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) AS SIGNED) AS v;
1212 SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT)
1213 AS DECIMAL(20,2)) AS res;
1214 SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
1215 DEFAULT 1111111111111111 ON EMPTY) AS CHAR) AS v;
1216 SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
1217 DEFAULT 1111111111111111 ON EMPTY) AS SIGNED) AS v;
1218 SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
1219 DEFAULT 1111111111111111 ON EMPTY)
1220 AS DECIMAL(20,2)) AS v;
1221
1222 --echo #
1223 --echo # Bug#35206138: Assigning json_value output to user defined
1224 --echo # variable will return NULL or empty string if
1225 --echo # input is empty string
1226 --echo #
1227
1228 SET @x = JSON_VALUE('{"x": ""}', '$.x');
1229 # Used to return NULL. Should return an empty string.
1230 SELECT @x;