Debian Codegraph @

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

raw
1 ########### suite/json/t/json_notable.test #
2 # Tests json columns functionality that does not need any tables #
3 # to be defined (no storage engine functionality) #
4 # #
5 # This test copies some tests originally in json.test #
6 ######################################################################
7
8 # Some extra checks for comparisons between positive and negative zero.
9 # All should be equal.
10 SELECT CAST(0.0e0 AS JSON) = -0.0e0;
11 SELECT CAST(CAST(0 AS DECIMAL) AS JSON) = CAST(-0.0e0 AS DECIMAL);
12 SELECT CAST(0.0e0 AS JSON) = CAST(-0.0e0 AS DECIMAL);
13 SELECT CAST(CAST(0 AS DECIMAL) AS JSON) = -0.0e0;
14 SELECT CAST(CAST(0 AS SIGNED) AS JSON) = -0.0e0;
15 SELECT CAST(CAST(0 AS SIGNED) AS JSON) = CAST(-0.0e0 AS DECIMAL);
16 SELECT CAST(CAST(0 AS UNSIGNED) AS JSON) = -0.0e0;
17 SELECT CAST(CAST(0 AS UNSIGNED) AS JSON) = CAST(-0.0e0 AS DECIMAL);
18
19 # Test that CAST string argument isn't treated as ANY_JSON_ATOM
20 # in that a MySQL string needs to be parsed to JSON here; it is not
21 # auto-converted to a JSON string as in ANY_JSON_ATOM contexts.
22 select cast('"abc"' as json);
23 --error ER_INVALID_JSON_TEXT_IN_PARAM
24 select cast('abc' as json);
25
26 --echo
27 --echo # String literal - valid JSON
28 select JSON_VALID('123'); # uint
29 select JSON_VALID('-123'); # int
30 select JSON_VALID('5000000000'); # uint64
31 select JSON_VALID('-5000000000'); # int64
32 select JSON_VALID('1.23'); # double
33 select JSON_VALID('"123"');
34 select JSON_VALID('true');
35 select JSON_VALID('false');
36 select JSON_VALID('null');
37 select JSON_VALID('{"address": "Trondheim"}');
38
39 --echo
40 --echo # String literal - not valid JSON
41 select JSON_VALID('12 3');
42
43 --echo
44 --echo # String literal not in UTF-8
45 set names 'ascii';
46 # auto-convert to utf-8
47 select JSON_VALID('123');
48
49 set names 'utf8';
50
51 --echo
52 --echo # Json expression
53 select JSON_VALID(cast('[123]' as JSON ));
54
55 --echo
56 --echo # Json expression NULL
57 select JSON_VALID(cast(NULL as JSON ));
58
59 --echo
60 --echo # Bare NULL
61 select JSON_VALID( NULL );
62
63 --echo
64 --echo # Function result - string
65 select JSON_VALID( UPPER('"abc"') );
66
67 set names 'latin1';
68 --echo
69 --echo # Function result - string
70 # auto-convert to utf-8
71 select JSON_VALID( UPPER('"abc"') );
72
73 set names 'utf8';
74
75 --echo
76 --echo # Function result - date, not valid as JSON without CAST
77 select JSON_VALID( CAST('2015-01-15' AS DATE) );
78
79 --echo
80 --echo # The date string doesn't parse as JSON text, so wrong:
81 select JSON_VALID( CAST(CAST('2015-01-15' AS DATE) as CHAR CHARACTER SET 'utf8') );
82 --echo # OK, though:
83 select JSON_VALID( CAST(CURDATE() as JSON) );
84
85 --echo
86 --echo # Function result - NULL
87 select JSON_VALID( UPPER(NULL) );
88 select JSON_VALID( UPPER(CAST(NULL as CHAR)) );
89
90 # examples from wl7909 spec
91 # returns 1
92 SELECT JSON_VALID( '{ "firstName" : "Fred", "lastName" : "Flintstone" }' );
93
94 # returns 1
95 SELECT JSON_VALID( '3' );
96
97 # returns NULL as IS JSON would
98 SELECT JSON_VALID( null );
99
100
101 --echo # ----------------------------------------------------------------------
102 --echo # Test of JSON_CONTAINS_PATH function.
103 --echo # ----------------------------------------------------------------------
104
105 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
106 select json_contains_path();
107 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
108 select json_contains_path('{ "a": true }' );
109 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
110 select json_contains_path('{ "a": true }', 'all' );
111 --error ER_INVALID_JSON_TEXT_IN_PARAM
112 select json_contains_path('{ "a": tru }', 'all', '$' );
113 --error ER_INVALID_JSON_PATH
114 select json_contains_path('{ "a": true }', 'all', '$[' );
115 --error ER_INVALID_JSON_PATH
116 select json_contains_path('{ "a": true }', 'all', '$a.***[3]' );
117 --error ER_JSON_BAD_ONE_OR_ALL_ARG
118 select json_contains_path('{ "a": true }', 'foo', '$.a' );
119 --error ER_INVALID_JSON_CHARSET
120 select json_contains_path('{}', 'all', cast('$' as binary));
121
122 select json_contains_path(null, 'all', '$.a' );
123 select json_contains_path('{ "a": true }', null, '$.a' );
124 select json_contains_path('{ "a": true }', 'all', null );
125
126 # degenerate path
127 select json_contains_path('{ "a": true }', 'all', '$' );
128
129 # positive, one path
130 select json_contains_path('{ "a": true }', 'all', '$.a' );
131 select json_contains_path('{ "a": true }', 'one', '$.a' );
132
133 # negative, one path
134 select json_contains_path('{ "a": true }', 'all', '$.b' );
135 select json_contains_path('{ "a": true }', 'one', '$.b' );
136
137 # all
138 select json_contains_path('{ "a": true }', 'all', '$.a', '$.b' );
139 select json_contains_path('{ "a": true }', 'all', '$.b', '$.a' );
140 select json_contains_path('{ "a": true }', 'ALL', '$.a', '$.b' );
141 select json_contains_path('{ "a": true }', 'aLl', '$.a', '$.b' );
142
143 # some
144 select json_contains_path('{ "a": true }', 'one', '$.a', '$.b' );
145 select json_contains_path('{ "a": true }', 'one', '$.b', '$.a' );
146 select json_contains_path('{ "a": true }', 'ONE', '$.a', '$.b' );
147 select json_contains_path('{ "a": true }', 'oNe', '$.a', '$.b' );
148
149 # some wildcards
150 select json_contains_path('{ "a": true, "b": [ 1, 2, { "c": [ 4, 5, { "d": [ 6, 7, 8, 9, 10 ]} ] } ] }', 'all', '$**[4]' );
151 select json_contains_path('{ "a": true, "b": [ 1, 2, { "c": [ 4, 5, { "d": [ 6, 7, 8, 9, 10 ]} ] } ] }', 'all', '$**[4]', '$**[5]' );
152 select json_contains_path('{ "a": true, "b": [ 1, 2, { "c": [ 4, 5, { "d": [ 6, 7, 8, 9, 10 ]} ] } ] }', 'all', '$**.c[2]' );
153 select json_contains_path('{ "a": true, "b": [ 1, 2, { "c": [ 4, 5, { "d": [ 6, 7, 8, 9, 10 ]} ] } ] }', 'all', '$**.c[3]' );
154 select json_contains_path('{"a":1, "b":2}', 'one', '$.*');
155 select json_contains_path('[1,2,3]', 'one', '$.*');
156 select json_contains_path('{}', 'one', '$[*]');
157
158 # combine ellipsis and wildcard
159 SELECT JSON_CONTAINS_PATH('[1, [[{"x": [{"a":{"b":{"c":42}}}]}]]]',
160 'one', '$**.a.*');
161 SELECT JSON_CONTAINS_PATH('[1, [[{"x": [{"a":{"b":{"c":42}}}]}]]]',
162 'all', '$**.a.*');
163 SELECT JSON_CONTAINS_PATH('[1,2,3]', 'one', '$**[*]');
164 SELECT JSON_CONTAINS_PATH('[1,2,3]', 'all', '$**[*]');
165
166 # 3 paths
167 select json_contains_path('{ "a": true, "b": [ 1, 2 ] }', 'all', '$**[1]', '$.b[0]', '$.c' );
168 select json_contains_path('{ "a": true, "b": [ 1, 2 ] }', 'all', '$.c', '$**[1]', '$.b[0]' );
169 select json_contains_path('{ "a": true, "b": [ 1, 2 ] }', 'all', '$.b[0]', '$.c', '$**[1]' );
170 select json_contains_path('{ "a": true, "b": [ 1, 2 ] }', 'one', '$**[1]', '$.b[0]', '$.c' );
171 select json_contains_path('{ "a": true, "b": [ 1, 2 ] }', 'one', '$.c', '$**[1]', '$.b[0]' );
172 select json_contains_path('{ "a": true, "b": [ 1, 2 ] }', 'one', '$.b[0]', '$.c', '$**[1]' );
173
174 # examples from the wl7909 spec
175 # returns 0 because there is no element at $.a.c
176 SELECT JSON_CONTAINS_PATH
177 (
178 '{ "a" : 123, "b" : [ 123, 456 ] }',
179 'all',
180 '$.a.c',
181 '$.b[1]'
182 );
183
184 # returns 1 because there is an element at $.b[1]
185 SELECT JSON_CONTAINS_PATH
186 (
187 '{ "a" : 123, "b" : [ 123, 456 ] }',
188 'one',
189 '$.a.c',
190 '$.b[1]'
191 );
192
193 # returns 0 because there is no element at the given path
194 SELECT JSON_CONTAINS_PATH
195 (
196 '{ "a" : 123, "b" : [ 123, 456 ] }',
197 'all',
198 '$.c'
199 );
200
201 # returns 1 because there is an element at $.b[1].c.d
202 SELECT JSON_CONTAINS_PATH
203 (
204 '{ "a" : 123, "b" : [ 123, { "c" : { "d" : true } } ] }',
205 'all',
206 '$.b[1].c.d'
207 );
208
209 select json_length( null );
210 select json_length( '1' );
211 --echo
212 --echo # invalid json text
213 --error ER_INVALID_JSON_TEXT_IN_PARAM
214 select json_length( 'abc' );
215 select json_length( '"abc"' );
216 select json_length( 'true' );
217 select json_length( 'false' );
218 select json_length( 'null' );
219
220 select json_length( '{}' );
221 select json_length( '{ "a" : 100, "b" : 200 }' );
222 select json_length( '{ "a" : 100, "b" : [ 300, 400, 500 ] }' );
223 select json_length( '[]' );
224 select json_length( '[ null, "foo", true, 1.1 ]' );
225 select json_length( '[ null, "foo", true, { "a" : "b", "c" : "d" } ]' );
226 select json_length( '"foo"' );
227 select json_length( '1.2' );
228
229
230 # bad path expressions
231 --echo
232 --echo # invalid json path
233 --error ER_INVALID_JSON_PATH
234 select json_length( 'true', 'c$' );
235 --echo
236 --echo # invalid json path
237 --error ER_INVALID_JSON_PATH
238 select json_length( '{ "foo" : [ true, false ] }', '$.foo[bar]' );
239
240 # json_length() with non-vacuous path expressions
241
242 # 1
243 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[0]' );
244 # 3
245 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[1]' );
246 # 1
247 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' );
248 # auto-wrapping: 1
249 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ); # auto-wrap scalar
250 select json_length( '[ 1, [ 2, 3, 4 ], {"a": 1} ]', '$[2][0]' ); # ditto object
251
252 # wildcards
253 select json_length('{"a":1,"b":2}', '$.*');
254 select json_length('{"foo":[{"bar":1},{"bar":2}]}', '$.foo**.bar');
255
256 # non-existent path: null
257 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][1]' );
258 # 3
259 select json_length( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]' );
260
261 # examples from the wl7909 spec
262 # returns 0
263 SELECT JSON_LENGTH
264 (
265 '{}'
266 );
267
268 # returns 1
269 SELECT JSON_LENGTH
270 (
271 '3'
272 );
273
274 # returns 2
275 SELECT JSON_LENGTH
276 (
277 '{ "a" : 123, "b" : [ 123, 456, 789 ] }'
278 );
279
280 # returns 3
281 SELECT JSON_LENGTH
282 (
283 '{ "a" : 123, "b" : [ 123, 456, 789 ] }',
284 '$.b'
285 );
286
287 # returns null because the path does not exist
288 SELECT JSON_LENGTH
289 (
290 '{ "a" : 123, "b" : [ 123, 456, 789 ] }',
291 '$.c'
292 );
293
294 --echo # ----------------------------------------------------------------------
295 --echo # Test of JSON_DEPTH function.
296 --echo # ----------------------------------------------------------------------
297 select json_depth(null);
298 select json_depth(cast(null as json));
299 #select i, json_depth(j) from t1;
300 select json_depth(cast('[]' as json)),
301 json_depth(cast('{}' as json)),
302 json_depth(cast('null' as json)),
303 json_depth(json_quote('foo'));
304 select json_depth(cast('[[2], 3, [[[4]]]]' as json));
305 select json_depth(cast('{"a": {"a1": [3]}, "b": {"b1": {"c": {"d": [5]}}}}' as json));
306
307 # examples from the wl7909 spec
308 # returns 1
309 SELECT JSON_DEPTH
310 (
311 '{}'
312 );
313
314 # returns 1
315 SELECT JSON_DEPTH
316 (
317 '[]'
318 );
319
320 # returns 1
321 SELECT JSON_DEPTH( '"abc"' );
322
323 # returns 1
324 SELECT JSON_DEPTH( CAST( '"abc"' AS JSON ) );
325
326 --error ER_INVALID_TYPE_FOR_JSON
327 SELECT JSON_DEPTH( 1 );
328
329 --error ER_INVALID_JSON_TEXT_IN_PARAM
330 SELECT JSON_DEPTH( 'abc' );
331
332 # returns 1
333 SELECT JSON_DEPTH( CAST( 1 AS JSON ) );
334
335 # returns 2
336 SELECT JSON_DEPTH
337 (
338 '{ "a" : true, "b" : false, "c" : null }'
339 );
340
341 # returns 2
342 SELECT JSON_DEPTH
343 (
344 '[ "a", true, "b" , false, "c" , null ]'
345 );
346
347 # returns 2
348 SELECT JSON_DEPTH
349 (
350 '{ "a" : true, "b" : {}, "c" : null }'
351 );
352
353 # returns 2
354 SELECT JSON_DEPTH
355 (
356 '[ "a", true, "b" , {}, "c" , null ]'
357 );
358
359 # returns 3
360 SELECT JSON_DEPTH
361 (
362 '{ "a" : true, "b" : { "e" : false }, "c" : null }'
363 );
364
365 # returns 3
366 SELECT JSON_DEPTH
367 (
368 '[ "a", true, "b" , { "e" : false }, "c" , null ]'
369 );
370
371 --error ER_INVALID_JSON_TEXT_IN_PARAM
372 SELECT JSON_DEPTH
373 (
374 '[ "a", true, "b" , { "e" : false }, "c" , null'
375 );
376
377 --echo # ----------------------------------------------------------------------
378 --echo # Test of JSON_REMOVE function.
379 --echo # ----------------------------------------------------------------------
380
381 # null args
382 select json_remove( null, '$[1]' );
383 select json_remove( null, '$[1]' ) is null;
384 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', null );
385 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', null ) is null;
386 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]', null );
387 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]', null ) is null;
388
389 # too few args
390
391 --echo
392 --echo # not enough args
393 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
394 select json_remove();
395 --echo
396 --echo # not enough args
397 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
398 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]' );
399 --echo
400 --echo # not enough args
401 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
402 select json_remove( '$[1]' );
403
404 # malformed args
405 --echo
406 --echo # invalid json text
407 --error ER_INVALID_JSON_TEXT_IN_PARAM
408 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ', '$[1]', '$[2]' );
409 --echo
410 --echo # invalid json path
411 --error ER_INVALID_JSON_PATH
412 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1', '$[2]' );
413 --echo
414 --echo # invalid json path
415 --error ER_INVALID_JSON_PATH
416 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]', '$[2' );
417 --echo
418 --echo # Vacuous path expression
419 --error ER_JSON_VACUOUS_PATH
420 select json_remove( '[ 1, 2, 3 ]', '$' );
421 --echo
422 --echo # Vacuous path expression
423 --error ER_JSON_VACUOUS_PATH
424 select json_remove( '[ 1, 2, 3 ]', '$', '$[2]' );
425 --echo
426 --echo # Vacuous path expression
427 --error ER_JSON_VACUOUS_PATH
428 select json_remove( '[ 1, 2, 3 ]', '$[1]', '$' );
429
430 # wildcard/ellipsis not allowed in paths
431 --error ER_INVALID_JSON_PATH_WILDCARD
432 select json_remove( '[ 1, 2, 3 ]', '$[*]' );
433 --error ER_INVALID_JSON_PATH_WILDCARD
434 select json_remove( '[ 1, 2, 3 ]', '$**[2]' );
435 --error ER_INVALID_JSON_PATH_WILDCARD
436 select json_remove( '[ 1, 2, 3 ]', '$[2]', '$[*]' );
437 --error ER_INVALID_JSON_PATH_WILDCARD
438 select json_remove( '[ 1, 2, 3 ]', '$[2]', '$**[2]' );
439
440 # simple matches
441
442 select json_remove( '[ 1, 2, 3 ]', '$[0]' );
443 select json_remove( '[ 1, 2, 3 ]', '$[1]' );
444 select json_remove( '[ 1, 2, 3 ]', '$[2]' );
445 select json_remove( '[ 1, 2, 3 ]', '$[3]' );
446 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]' );
447
448 # one match nested inside another
449 --error ER_INVALID_JSON_PATH_WILDCARD
450 select json_remove( '[ { "a": { "a": true } } ]', '$**.a' );
451
452 # multiple paths
453 select json_remove( '[ { "a": true }, { "b": false }, { "c": null }, { "a": null } ]', '$[0].a', '$[2].c' );
454
455 # ellipsis with matches at different levels
456 --error ER_INVALID_JSON_PATH_WILDCARD
457 select json_remove( '[ { "a": true }, { "b": [ { "c": { "a": true } } ] }, { "c": null }, { "a": null } ]', '$**.a' );
458
459 # nonsense path
460 select json_remove( '{"id": 123, "name": "systemQA", "array": [1, 2, 3]}', '$[0]' );
461
462 # examples from wl7909 spec
463 # returns the document {"a": "foo", "b": [true]}
464 SELECT JSON_REMOVE
465 (
466 '{"a" : "foo", "b" : [true, {"c" : 123}]}',
467 '$.b[ 1 ]'
468 );
469
470 # returns {"a": "foo", "b": [true, {}]} due to normalization
471 SELECT JSON_REMOVE
472 (
473 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
474 '$.b[ 1 ].c'
475 );
476
477 # returns {"a": "foo", "b": [true, {}]}
478 SELECT JSON_REMOVE
479 (
480 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
481 '$.b[ 1 ].c'
482 );
483
484 # returns the original document because the path doesn't identify an element
485 SELECT JSON_REMOVE
486 (
487 '{ "a" : "foo", "b" : [ true, { "c" : 123, "d" : 456 } ] }',
488 '$.b[ 1 ].e'
489 );
490
491 SELECT JSON_REMOVE('123', '$.a');
492
493 --echo # ----------------------------------------------------------------------
494 --echo # Test of JSON_MERGE_PRESERVE function.
495 --echo # ----------------------------------------------------------------------
496
497 --echo
498 --echo # not enough args
499 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
500 SELECT JSON_MERGE_PRESERVE();
501 --echo
502 --echo # not enough args
503 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
504 SELECT JSON_MERGE_PRESERVE('[ 1, 2, 3 ]');
505
506 # null args result in NULL value
507 SELECT JSON_MERGE_PRESERVE(NULL, NULL);
508 SELECT JSON_MERGE_PRESERVE(NULL, '[ 1, 2, 3 ]');
509 SELECT JSON_MERGE_PRESERVE('[ 1, 2, 3 ]', NULL);
510 SELECT JSON_MERGE_PRESERVE(NULL, '[ 1, 2, 3 ]', '[ 4, 5, 6 ]');
511 SELECT JSON_MERGE_PRESERVE('[ 1, 2, 3 ]', NULL, '[ 4, 5, 6 ]');
512 SELECT JSON_MERGE_PRESERVE('[ 1, 2, 3 ]', '[ 4, 5, 6 ]', NULL);
513
514 --error ER_INVALID_JSON_TEXT_IN_PARAM
515 SELECT JSON_MERGE_PRESERVE('[1, 2]', '[3, 4');
516 --error ER_INVALID_JSON_TEXT_IN_PARAM
517 SELECT JSON_MERGE_PRESERVE('[1, 2', '[3, 4]');
518
519 # good json_merge_preserve() expressions
520 SELECT JSON_MERGE_PRESERVE('1', '2');
521 SELECT JSON_MERGE_PRESERVE('1', '[2, 3]');
522 SELECT JSON_MERGE_PRESERVE('[1, 2]', '3');
523 SELECT JSON_MERGE_PRESERVE('1', '{ "a": 2 }');
524 SELECT JSON_MERGE_PRESERVE('{ "a": 2 }', '1');
525 SELECT JSON_MERGE_PRESERVE('[1, 2]', '[3, 4]');
526 SELECT JSON_MERGE_PRESERVE('{ "a": 2 }', '{ "b": 3}');
527 SELECT JSON_MERGE_PRESERVE('[1, 2]', '{ "a": 2 }');
528 SELECT JSON_MERGE_PRESERVE('{ "a": 2 }', '[1, 2]');
529
530 SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2 }',
531 '{"b": 3, "d": 4 }');
532 SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2 }',
533 '{"b": [3, 4], "d": 4 }');
534 SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": [2, 3] }', '{"b": 4, "d": 4 }');
535 SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2 }',
536 '{"b": {"e": 7, "f": 8}, "d": 4 }');
537 SELECT JSON_MERGE_PRESERVE('{"b": {"e": 7, "f": 8}, "d": 4 }',
538 '{"a": 1, "b": 2 }');
539 SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": [2, 9] }',
540 '{"b": [10, 11], "d": 4 }');
541 SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": [2, 9] }',
542 '{"b": {"e": 7, "f": 8}, "d": 4 }');
543 SELECT JSON_MERGE_PRESERVE('{"b": {"e": 7, "f": 8}, "d": 4 }',
544 '{"a": 1, "b": [2, 9] }');
545 SELECT JSON_MERGE_PRESERVE('{"b": {"e": 7, "f": 8}, "d": 4 }',
546 '{ "a": 1, "b": {"e": 20, "g": 21 } }');
547
548 SELECT JSON_MERGE_PRESERVE('1', '2', '3');
549 SELECT JSON_MERGE_PRESERVE('[1, 2 ]', '3', '[4, 5]');
550 SELECT JSON_MERGE_PRESERVE
551 (
552 '{ "a": true, "b": { "c": 3, "d": 4 }, "e": [ 1, 2 ] }',
553 '{ "d": false, "b": { "g": 3, "d": 5 }, "f": [ 1, 2 ] }',
554 '{ "m": true, "b": { "h": 8, "d": 4 }, "e": [ 3, 4 ] }'
555 );
556
557 # examples from the wl7909 spec
558 # returns [{"a": "foo", "b": [true, {"c": 123}]}, 5, 6]
559 SELECT JSON_MERGE_PRESERVE
560 (
561 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
562 '[ 5, 6]'
563 );
564
565 # returns {"a": "foo", "b": [true, {"c": 123}, false, 34]}
566 SELECT JSON_MERGE_PRESERVE
567 (
568 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
569 '{ "b": [ false, 34 ] }'
570 );
571
572 # returns {"a": "foo", "b": [true, {"c": 123}, "bar"]}
573 SELECT JSON_MERGE_PRESERVE
574 (
575 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
576 '{ "b": "bar" }'
577 );
578
579 # returns {"a": {"b": 1, "c": 1}}
580 SELECT JSON_MERGE_PRESERVE
581 (
582 '{ "a" : { "b" : 1 } }',
583 '{ "a" : { "c" : 1 } }'
584 );
585
586 --echo # ----------------------------------------------------------------------
587 --echo # Test of JSON_TYPE function.
588 --echo # ----------------------------------------------------------------------
589
590 # negative test
591 --error ER_INVALID_JSON_TEXT_IN_PARAM
592 select json_type('abc');
593
594 #select i, json_type(j) from t1;
595 select json_type('{"a": 2}');
596 select json_type('[1,2]');
597 select json_type('"scalar string"');
598 select json_type('true');
599 select json_type('false');
600 select json_type('null');
601 select json_type('1');
602 select json_type('-0');
603 select json_type('-0.0');
604 --error ER_INVALID_TYPE_FOR_JSON
605 select json_type(-1);
606 --error ER_INVALID_TYPE_FOR_JSON
607 select json_type(CAST(1 AS UNSIGNED));
608 select json_type('32767');
609 --error ER_INVALID_TYPE_FOR_JSON
610 select json_type(PI());
611 select json_type('3.14');
612
613 --error ER_INVALID_JSON_TEXT_IN_PARAM
614 select json_type(CAST(CAST('2015-01-15' AS DATE) as CHAR CHARACTER SET 'utf8'));
615
616 --echo # ----------------------------------------------------------------------
617 --echo # Test of CAST(literal AS JSON)
618 --echo # ----------------------------------------------------------------------
619 select json_type(cast(cast('2014-11-25 18:00' as datetime) as json));
620 select json_type(cast(cast('2014-11-25' as date) as json));
621 select json_type(cast(cast('18:00:59' as time) as json));
622 # select json_type(cast(cast('2014-11-25 18:00' as timestamp) as json)); -- cast target type not supported
623
624 # select json_type(cast(cast('1999' as year) as json)); -- cast target type not supported
625 select json_type(cast(127 as json));
626 select json_type(cast(255 as json));
627 select json_type(cast(32767 as json));
628 select json_type(cast(65535 as json));
629 select json_type(cast(8388607 as json));
630 select json_type(cast(16777215 as json));
631 select json_type(cast(2147483647 as json));
632 select json_type(cast(4294967295 as json));
633 select json_type(cast(9223372036854775807 as json));
634 select json_type(cast(18446744073709551615 as json));
635 select json_type(cast(true as json));
636 select json_type(cast(b'10101' as json));
637
638 select json_type(cast(cast(3.14 as decimal(5,2)) as json));
639 select json_type(cast(3.14 as json));
640 select json_type(cast(3.14E30 as json));
641 # select json_type(cast(cast(3.14 as numeral(5,2)) as json)); -- cast target type not supported
642
643 # select json_type(cast(cast(3.14 as double) as json)); -- cast target type not supported
644 # select json_type(cast(cast(3.14 as float) as json)); -- cast target type not supported
645
646 # select json_type(cast(cast(b'10101' as bit(10)) as json)); -- cast target type not supported
647 # select json_type(cast(cast('10101abcde' as blob) as json)); -- cast target type not supported
648 select json_type(cast(cast('10101abcde' as binary) as json));
649
650 # select json_type(cast(cast('a' as enum('a','b','c')) as json)); -- cast target type not supported
651 # select json_type(cast(cast('a,c' as set('a','b','c')) as json)); -- cast target type not supported
652
653 select json_type(cast(ST_GeomFromText('POINT(1 1)') as json));
654 select json_type(cast(ST_GeomFromText('LINESTRING(0 0,1 1,2 2)') as json));
655 select json_type(cast(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),
656 (5 5,7 5,7 7,5 7, 5 5))') as json));
657 select json_type(cast(null as json));
658 select json_type(cast(null as json)) is null; # check that it is an SQL NULL
659 select json_type(null) is null; # is an SQL NULL
660
661 #
662 # same, but now show the printable value:
663 #
664 select cast(cast('2014-11-25 18:00' as datetime) as json);
665 select cast(cast('2014-11-25' as date) as json);
666 select cast(cast('18:00:59' as time) as json);
667 # select cast(cast('2014-11-25 18:00' as timestamp) as json); -- cast target type not supported
668
669 # select cast(cast('1999' as year) as json); -- cast target type not supported
670 select cast(127 as json);
671 select cast(255 as json);
672 select cast(32767 as json);
673 select cast(65535 as json);
674 select cast(8388607 as json);
675 select cast(16777215 as json);
676 select cast(2147483647 as json);
677 select cast(4294967295 as json);
678 select cast(9223372036854775807 as json);
679 select cast(18446744073709551615 as json);
680 select cast(true as json);
681 select cast(b'10101' as json);
682
683 select cast(cast(3.14 as decimal(5,2)) as json);
684 select cast(3.14 as json);
685 select cast(3.14e0 as json);
686 # select cast(cast(3.14 as numeral(5,2)) as json); -- cast target type not supported
687
688 # select cast(cast(3.14 as double) as json); -- cast target type not supported
689 # select cast(cast(3.14 as float) as json); -- cast target type not supported
690
691 # select cast(cast(b'10101' as bit(10) as json); -- cast target type not supported
692 # select cast(cast('10101abcde' as blob) as json); -- cast target type not supported
693 select cast(cast('10101abcde' as binary) as json);
694
695 # select cast(cast('a' as enum('a','b','c') as json); -- cast target type not supported
696 # select cast(cast('a,c' as set('a','b','c') as json); -- cast target type not supported
697
698 select cast(ST_GeomFromText('POINT(1 1)') as json);
699 select cast(ST_GeomFromText('LINESTRING(0 0,1 1,2 2)') as json);
700 select cast(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),
701 (5 5,7 5,7 7,5 7, 5 5))') as json);
702 select cast(null as json);
703 select cast(null as json) is null; # check that it is an SQL NULL
704
705 --echo # ----------------------------------------------------------------------
706 --echo # Test of JSON_KEYS function.
707 --echo # ----------------------------------------------------------------------
708 # should all give NULL:
709 select json_keys(NULL);
710 select json_keys(NULL, '$.b');
711 select json_keys(NULL, NULL);
712 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a');
713 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', NULL);
714
715 # non NULL results
716 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}');
717 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.b');
718
719 --error ER_INVALID_JSON_PATH_WILDCARD
720 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.*.b');
721
722 # Examples from the specification
723 --echo # returns [ "a", "b" ]
724 SELECT JSON_KEYS('{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }');
725
726 --echo # returns []
727 SELECT JSON_KEYS('{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
728 '$.b[1].c');
729
730 --echo # returns NULL
731 SELECT JSON_KEYS('{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
732 '$.a.b[2]');
733
734 --error ER_INVALID_JSON_PATH
735 SELECT JSON_KEYS('{"a":1}', '1010');
736 --error ER_INVALID_JSON_PATH
737 SELECT JSON_KEYS('{"a":1}', '1010') IS NULL;
738
739 # examples from the wl7909 spec
740 # returns [ "a", "b" ]
741 SELECT JSON_KEYS
742 (
743 '{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }'
744 );
745
746 # returns []
747 SELECT JSON_KEYS
748 (
749 '{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
750 '$.b[1].c'
751 );
752
753 # returns NULL
754 SELECT JSON_KEYS
755 (
756 '{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
757 '$.a.b[2]'
758 );
759
760 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
761 SELECT JSON_KEYS();
762 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
763 SELECT JSON_KEYS('{}', '$', '$');
764
765 --echo # ----------------------------------------------------------------------
766 --echo # CAST(<json> AS CHAR). See also 'json_conversions.test' for other
767 --echo # conversion tests.
768 --echo # ----------------------------------------------------------------------
769 select cast(json_keys('{"a": 1}') as char);
770 select cast(cast(1 as json) as char);
771 select cast(json_keys(NULL) as char);
772 #select cast(j as char) from keys1;
773
774 --echo # ----------------------------------------------------------------------
775 --echo # Path matching with double-quotes
776 --echo # ----------------------------------------------------------------------
777
778 # matches
779 select json_extract( '{ "one potato" : 1 }', '$."one potato"' );
780 # matches
781 select json_extract( '{ "a.b" : 1 }', '$."a.b"' );
782
783 # doesn't match
784 select json_extract( '{ "\\"a\\"": 1}', '$."a"' );
785 # matches
786 select json_extract( '{ "\\"a\\"": 1}', '$."\\"a\\""' );
787 # matches
788 select json_extract( '{ "a": 1}', '$."a"' );
789 # matches
790 select json_extract( '{ "a": 1}', '$.a' );
791
792 # examples from functional spec section on Path Syntax
793
794 # [3, 2]
795 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[0]' );
796 # 2
797 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[0][1]' );
798 # [ { "c": "d" }, 1 ]
799 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[1]' );
800 # { "c": "d" }
801 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[1][0]' );
802 # "d"
803 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[1][0].c' );
804 # 7
805 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$."one potato"' );
806 # 6
807 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.b.c' );
808 # 8
809 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$."b.c"' );
810
811
812 --echo # ----------------------------------------------------------------------
813 --echo # Test of JSON_EXTRACT function.
814 --echo # ----------------------------------------------------------------------
815
816 # errors
817 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
818 select json_extract(NULL);
819 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
820 select json_extract('$.b');
821 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
822 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}');
823
824 # Confused argument order
825 --error ER_INVALID_JSON_TEXT_IN_PARAM
826 select json_extract('$.a', '{"a": 1, "b": {"e": "foo", "b": 3}}');
827
828 # NULLs
829 select json_extract(NULL, '$.b');
830 select json_extract(NULL, NULL);
831
832 # non-NULLs
833 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a');
834 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.*');
835 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a', '$.b.e');
836 select json_extract('{"a": 1, "b": [1,2,3]}', '$.b[2]');
837 # one path is NULL
838 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a', NULL);
839
840 # Examples from the specification
841 --echo # returns a JSON value containing just the string "123"
842 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }',
843 '$.b[ 1 ].c');
844
845 --echo # returns a JSON value containing just the number 123
846 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
847 '$.b[ 1 ].c');
848
849 --echo # raises an error because the document is not valid
850 --error ER_INVALID_JSON_TEXT_IN_PARAM
851 SELECT JSON_EXTRACT('{ "a" : [ }',
852 '$.b[ 1 ].c');
853
854 --echo # raises an error because the path is invalid
855 --error ER_INVALID_JSON_PATH
856 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
857 '$.b[ 1 ].');
858
859 --echo # returns a JSON value containing the number 123 (because of
860 --echo # auto-wrapping the scalar)
861 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
862 '$.b[ 1 ].c[ 0 ]');
863 --echo # returns a JSON value containing the object because of auto-wrapping
864 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : {"not array": 4} } ] }',
865 '$.b[ 1 ].c[ 0 ]');
866
867 --echo # returns null because the path, although valid, does not identify a value
868 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
869 '$.b[ 1 ].c[ 1 ]');
870
871 --echo # returns a JSON value containing the number 456 (due to normalization)
872 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
873 '$.b[ 1 ].c');
874
875 --echo # returns a JSON array [ "foo", true ]
876 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
877 '$.a', '$.b[0]');
878
879 --echo # returns a JSON array [ true ]
880 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
881 '$.d', '$.b[0]');
882
883 --echo # Some examples verifying ellipsis behavior.
884
885 --echo # Should have the same result, apart from wrapping.
886 select json_extract( '[1]', '$[0][0]' );
887 select json_extract( '[1]', '$**[0]' );
888 select json_extract( '[1]', '$[last][last]' );
889 select json_extract( '[1]', '$**[last]' );
890
891 --echo # Should have the same result.
892 select json_extract( '{ "a": 1 }', '$**[0]' );
893 select json_extract( '{ "a": 1 }', '$[0]', '$.a[0]' );
894 select json_extract( '{ "a": 1 }', '$[0]', '$[0].a[0]' );
895 select json_extract( '{ "a": 1 }', '$**[last]' );
896 select json_extract( '{ "a": 1 }', '$[last]', '$.a[last]' );
897
898 --echo # Should have the same result, apart from wrapping.
899 select json_extract( '{ "a": 1 }', '$**.a' );
900 select json_extract( '{ "a": 1 }', '$[0].a' );
901 select json_extract( '{ "a": 1 }', '$[last].a' );
902
903 --echo # Should have the same result, apart from wrapping.
904 select json_extract( '{ "a": 1 }', '$**.a' );
905 select json_extract( '{ "a": 1 }', '$[0].a' );
906 select json_extract( '{ "a": 1 }', '$[0][0].a' );
907 select json_extract( '{ "a": 1 }', '$[0][0][0].a' );
908 select json_extract( '{ "a": 1 }', '$[last].a' );
909 select json_extract( '{ "a": 1 }', '$[last][last].a' );
910 select json_extract( '{ "a": 1 }', '$[last][last][last].a' );
911
912 --echo # Should have the same result.
913 SELECT JSON_EXTRACT('[1, [[{"x": [{"a":{"b":{"c":42}}}]}]]]', '$**.a.*');
914 SELECT JSON_EXTRACT('[1, [[{"x": [{"a":{"b":{"c":42}}}]}]]]',
915 '$[1][0][0].x[0].a.*');
916
917 SELECT JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[0]');
918 SELECT JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[1]');
919 SELECT JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[2]');
920 SELECT JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[3]');
921 SELECT JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[*]');
922 SELECT JSON_EXTRACT('[1,2,[3,4,[5,6,{"a":7}]]]', '$**[*]**[0]');
923
924 # examples from the wl7909 spec
925 # returns a JSON value containing just the string "123"
926 SELECT JSON_EXTRACT
927 (
928 '{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }',
929 '$.b[ 1 ].c'
930 );
931
932 # returns a JSON value containing just the number 123
933 SELECT JSON_EXTRACT
934 (
935 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
936 '$.b[ 1 ].c'
937 );
938
939 --error ER_INVALID_JSON_TEXT_IN_PARAM
940 SELECT JSON_EXTRACT
941 (
942 '{ "a" : [ }',
943 '$.b[ 1 ].c'
944 );
945
946 --error ER_INVALID_JSON_PATH
947 SELECT JSON_EXTRACT
948 (
949 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
950 '$.b[ 1 ].'
951 );
952
953 # returns a JSON value containing the number 123 (because of auto-wrapping)
954 SELECT JSON_EXTRACT
955 (
956 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
957 '$.b[ 1 ].c[ 0 ]'
958 );
959
960 # returns null because the path, although valid, does not identify a value
961 SELECT JSON_EXTRACT
962 (
963 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
964 '$.b[ 1 ].c[ 1 ]'
965 );
966
967 # returns a JSON value containing the number 456 (due to normalization)
968 SELECT JSON_EXTRACT
969 (
970 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
971 '$.b[ 1 ].c'
972 );
973
974 # returns a JSON array ["foo", true]
975 SELECT JSON_EXTRACT
976 (
977 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
978 '$.a', '$.b[0]'
979 );
980
981 # returns a the 'true' literal
982 SELECT JSON_EXTRACT
983 (
984 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
985 '$.d', '$.b[0]'
986 );
987
988 # should return NULL
989 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[*].b' ) jdoc;
990
991 # should return NULL
992 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[0].b' ) jdoc;
993
994 # should return 1
995 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[0].a' ) jdoc;
996
997 # should return [1, 2]
998 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[*].a' ) jdoc;
999
1000 # should return [1]
1001 select json_extract( '[ { "a": 1 }, { "b": 2 } ]', '$[*].a' ) jdoc;
1002
1003 # should return [3, 4]
1004 select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[0].a' ) jdoc;
1005
1006 # should return [[3, 4]]
1007 select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[*].a' ) jdoc;
1008
1009 # should return [[3, 4]]
1010 select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[0].a', '$[1].a' ) jdoc;
1011
1012 --echo # ----------------------------------------------------------------------
1013 --echo # Test of JSON_ARRAY_APPEND function.
1014 --echo # ----------------------------------------------------------------------
1015
1016 # NULLs
1017 select json_array_append(NULL, '$.b', cast(1 as json));
1018 select json_array_append('[1,2,3]', NULL, cast(1 as json));
1019 select json_array_append('[1,2,3]', '$', NULL);
1020
1021 # wrong # args
1022 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1023 select json_array_append(NULL);
1024 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1025 select json_array_append(NULL, NULL);
1026 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1027 select json_array_append(NULL, NULL, NULL, NULL);
1028
1029 # auto-wrap
1030 SELECT JSON_ARRAY_APPEND(cast('1' as json), '$', 3);
1031 SELECT JSON_ARRAY_APPEND(cast('{"a": 3}' as json), '$', 3);
1032 SELECT JSON_ARRAY_APPEND('1', '$[0]', 3);
1033
1034 --error ER_INVALID_JSON_PATH_WILDCARD
1035 select json_array_append(cast('{"a": {"b": [3]}}' as json), '$**[0]', 6);
1036 --error ER_INVALID_JSON_PATH_WILDCARD
1037 SELECT JSON_ARRAY_APPEND('1', '$[0 to 1]', 3);
1038
1039 # Examples from the specification
1040
1041 --echo # Auto-wrapping, since because the paths identify scalars.
1042 --echo # should return {"a": "foo", "b": ["bar", 4], "c": ["wibble", "grape"]}
1043 SELECT JSON_ARRAY_APPEND('{"a": "foo", "b": "bar", "c": "wibble"}',
1044 '$.b', cast(4 as json),
1045 '$.c', cast('"grape"' as json));
1046
1047 --echo # should return {"a": "foo", "b": [1, 2, 3, 4],
1048 --echo # "c": ["apple", "pear", "grape"]}
1049 SELECT JSON_ARRAY_APPEND('{"a" : "foo","b": [1, 2, 3], "c": ["apple", "pear"]}',
1050 '$.b', cast(4 as json),
1051 '$.c', cast('"grape"' as json));
1052
1053 # without CAST: cf. not required for ANY_JSON_ATOM arguments in specification
1054 SELECT JSON_ARRAY_APPEND('{"a" : "foo","b": [1, 2, 3], "c": ["apple", "pear"]}',
1055 '$.b', 4,
1056 '$.c', 'grape');
1057
1058 # wild cards, multiple pairs
1059 --error ER_INVALID_JSON_PATH_WILDCARD
1060 select json_array_append( '[[], [], []]', '$[*]', 3, '$[*]', 4);
1061 --error ER_INVALID_JSON_PATH_WILDCARD
1062 select json_array_append( '[[], "not array", []]', '$[*]', 3, '$[*]', 4);
1063
1064 # examples from wl7909 spec
1065 # should return {"a": "foo", "b": ["bar", 4], "c": ["wibble", "grape"]} due to autowrapping
1066 SELECT JSON_ARRAY_APPEND
1067 (
1068 '{ "a" : "foo", "b" : "bar", "c" : "wibble" }',
1069 '$.b', 4,
1070 '$.c', "grape"
1071 );
1072
1073 # should return {"a": "foo", "b": [1, 2, 3, 4], "c": ["apple", "pear", "grape"]}
1074 SELECT JSON_ARRAY_APPEND
1075 (
1076 '{ "a" : "foo", "b" : [ 1, 2, 3 ], "c" : [ "apple", "pear" ] }',
1077 '$.b', 4,
1078 '$.c', "grape"
1079 );
1080
1081 --echo # ----------------------------------------------------------------------
1082 --echo # Bug#21373874 ASSERTION `PARENT' FAILED
1083 --echo # ----------------------------------------------------------------------
1084
1085 select json_array_append('{"a":1}', '$[0]', 100);
1086 select json_array_append('3', '$[0]', 100);
1087 select json_array_append('3', '$[0][0][0][0]', 100);
1088
1089 --echo # ----------------------------------------------------------------------
1090 --echo # Test of JSON_INSERT function.
1091 --echo # ----------------------------------------------------------------------
1092
1093 # NULLs
1094 select json_insert(NULL, '$.b', cast(1 as json));
1095 select json_insert('[1,2,3]', NULL, cast(1 as json));
1096 select json_insert('[1,2,3]', '$[3]', NULL);
1097
1098 # wrong # args
1099 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1100 select json_insert(NULL);
1101 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1102 select json_insert(NULL, NULL);
1103 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1104 select json_insert(NULL, NULL, NULL, NULL);
1105
1106 # positive test cases
1107
1108 select json_insert('[1,2,3]', '$[0]', 4);
1109 select json_insert('[1,2,3]', '$[1]', 4);
1110 select json_insert('[1,2,3]', '$[2]', 4);
1111 select json_insert('[1,2,3]', '$[3]', 4);
1112 select json_insert('[1,2,3]', '$[10]', 4);
1113 select json_insert('[1,2,3]', '$[last]', 4);
1114 select json_insert('[1,2,3]', '$[last-1]', 4);
1115 select json_insert('[1,2,3]', '$[last-2]', 4);
1116 select json_insert('[1,2,3]', '$[last-3]', 4);
1117 select json_insert('[1,2,3]', '$[last-10]', 4);
1118
1119 select json_insert('[]', '$[0]', 1);
1120 select json_insert('[]', '$[1]', 1);
1121 select json_insert('[]', '$[last]', 1);
1122 select json_insert('[]', '$[last-1]', 1);
1123
1124 select json_insert('{"c":4}', '$.c', 4);
1125 select json_insert('{"c":4}', '$.a', 4);
1126
1127 select json_insert('1', '$', 4);
1128 select json_insert('1', '$[0]', 4);
1129 select json_insert('1', '$[1]', 4);
1130 select json_insert('1', '$[10]', '4', '$[11]', 5);
1131 select json_insert('1', '$[last]', 4);
1132 select json_insert('1', '$[last-1]', 4);
1133 select json_insert('1', '$[last-10]', '4', '$[last-11]', 5);
1134
1135 select json_insert('[1,2,3]', '$[2][0]', 4);
1136 select json_insert('[1,2,3]', '$[2][1]', 4);
1137 select json_insert('[1,2,3]', '$[2][2]', 4);
1138 select json_insert('[1,2,3]', '$[2][last]', 4);
1139 select json_insert('[1,2,3]', '$[2][last-1]', 4);
1140 select json_insert('[1,2,3]', '$[2][last-2]', 4);
1141
1142 select json_insert('{"a": 3}', '$.a[0]', 4);
1143 select json_insert('{"a": 3}', '$.a[1]', 4, '$.a[2]', '5');
1144 select json_insert('{"a": 3}', '$.a[last]', 4);
1145 select json_insert('{"a": 3}', '$.a[last-1]', 4, '$.a[last-2]', '5');
1146
1147 # wild card & auto-wrap (scalars)
1148 --error ER_INVALID_JSON_PATH_WILDCARD
1149 select json_insert(cast('{"a": [1], "b": 2}' as json), '$.*[1]', 6);
1150 --error ER_INVALID_JSON_PATH_WILDCARD
1151 select json_insert(cast('{"a": 1, "b": 2}' as json), '$.*[1]', 6);
1152
1153 --error ER_INVALID_JSON_PATH_WILDCARD
1154 select json_insert(cast('{"a": {"b": 3}}' as json), '$.a.*[1]', 6);
1155 --error ER_INVALID_JSON_PATH_WILDCARD
1156 select json_insert(cast('{"a": {"b": [3]}}' as json), '$.a.*[1]', 6);
1157 --error ER_INVALID_JSON_PATH_WILDCARD
1158 select json_insert(cast('{"a": {"b": 3}}' as json), '$**[1]', 6);
1159 --error ER_INVALID_JSON_PATH_WILDCARD
1160 select json_insert(cast('{"a": {"b": [3]}}' as json), '$**[1]', 6);
1161
1162 --error ER_INVALID_JSON_PATH_WILDCARD
1163 select json_insert(cast('[1]' as json), '$[*][1]', 6);
1164 --error ER_INVALID_JSON_PATH_WILDCARD
1165 select json_insert(cast('[1]' as json), '$**[1]', 6);
1166 --error ER_INVALID_JSON_PATH_WILDCARD
1167 select json_insert(cast('[1, [2], 3]' as json), '$[*][1]', 6);
1168 --error ER_INVALID_JSON_PATH_WILDCARD
1169 select json_insert(cast('[1, [2], 3]' as json), '$**[1]', 6);
1170
1171 --error ER_INVALID_JSON_PATH_WILDCARD
1172 select json_insert(cast('[[1]]' as json), '$[*][1]', 6);
1173 --error ER_INVALID_JSON_PATH_WILDCARD
1174 select json_insert(cast('[[1]]' as json), '$**[1]', 6);
1175
1176 --error ER_INVALID_JSON_PATH_WILDCARD
1177 select json_insert(cast('[1, [2], 3]' as json), '$[1 to 2]', 6);
1178
1179 # auto-wrap object
1180 select json_insert(cast('{"a": 3}' as json), '$[1]', 6);
1181
1182
1183 # Examples from the specification
1184
1185 # returns the original document because the path does exist
1186 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.a', true);
1187
1188 # inserts a number, returns '{ "a" : "foo", "b" : [ 1, 2, 3 ], "c" : 123 }
1189 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.c', 123);
1190
1191 # inserts a string, returns '{ "a" : "foo", "b" : [ 1, 2, 3 ], "c" : "123" }
1192 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.c', '123');
1193
1194 # returns '{ "a" : [ "foo", true ], "b" : [ 1, 2, 3 ] }'
1195 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.a[1]', true);
1196
1197 # should return { "a" : "foo", "b": true }
1198 SELECT JSON_INSERT('{ "a" : "foo"}', '$.b', true, '$.b', false);
1199
1200 # examples from the wl7909 spec
1201 # returns the original document because the path does exist
1202 SELECT JSON_INSERT
1203 (
1204 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1205 '$.a',
1206 true
1207 );
1208
1209 # inserts a number, returns '{"a": "foo", "b": [1, 2, 3], "c": 123}
1210 SELECT JSON_INSERT
1211 (
1212 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1213 '$.c',
1214 123
1215 );
1216
1217 # inserts a string, returns '{"a": "foo", "b": [1, 2, 3], "c": "123"}
1218 SELECT JSON_INSERT
1219 (
1220 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1221 '$.c',
1222 '123'
1223 );
1224
1225 # returns '{"a": ["foo", true], "b": [1, 2, 3]}'
1226 SELECT JSON_INSERT
1227 (
1228 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1229 '$.a[1]',
1230 true
1231 );
1232
1233 # returns {"a": "foo", "b": true}
1234 SELECT JSON_INSERT
1235 (
1236 '{ "a" : "foo"}',
1237 '$.b', true,
1238 '$.b', false
1239 );
1240
1241 --echo # ----------------------------------------------------------------------
1242 --echo # Test of JSON_ARRAY_INSERT function.
1243 --echo # ----------------------------------------------------------------------
1244
1245 # NULLs
1246 select json_array_insert(NULL, '$.b[1]', 1);
1247 select json_array_insert('[1,2,3]', NULL, 1);
1248 select json_array_insert('[1,2,3]', '$[3]', NULL);
1249
1250 # wrong # args
1251 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1252 select json_array_insert(NULL);
1253 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1254 select json_array_insert(NULL, NULL);
1255 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1256 select json_array_insert(NULL, NULL, NULL, NULL);
1257
1258 # path does not indicate a cell position
1259 --error ER_INVALID_JSON_PATH_ARRAY_CELL
1260 select json_array_insert('true', '$', 1);
1261 --error ER_INVALID_JSON_PATH_ARRAY_CELL
1262 select json_array_insert('true', '$.a', 1);
1263 --error ER_INVALID_JSON_PATH_ARRAY_CELL
1264 select json_array_insert('true', '$.a[1].b', 1);
1265
1266 # nop if there is no array at the path's parent
1267 select json_array_insert( 'true', '$[0]', false );
1268 select json_array_insert( 'true', '$[1]', false );
1269 select json_array_insert( '{ "a": true }', '$.a[0]', false );
1270 select json_array_insert( '{ "a": true }', '$.a[1]', false );
1271
1272 # positive tests
1273 select json_array_insert( '[]', '$[0]', false );
1274 select json_array_insert( '[]', '$[1]', false );
1275 select json_array_insert( '[]', '$[last]', false );
1276 select json_array_insert( '[]', '$[last-1]', false );
1277 select json_array_insert( '[true]', '$[0]', false );
1278 select json_array_insert( '[true]', '$[1]', false );
1279 select json_array_insert( '[true]', '$[2]', false );
1280 select json_array_insert( '{ "a": [] }', '$.a[0]', false );
1281 select json_array_insert( '{ "a": [] }', '$.a[1]', false );
1282 select json_array_insert( '{ "a": [true] }', '$.a[0]', false );
1283 select json_array_insert( '{ "a": [true] }', '$.a[1]', false );
1284 select json_array_insert( '{ "a": [true] }', '$.a[2]', false );
1285 select json_array_insert( '{ "a": [true] }', '$.a[last]', false );
1286 select json_array_insert( '{ "a": [true] }', '$.a[last-1]', false );
1287
1288 # insert into the middle of an array
1289 select json_array_insert( '[1, 2, 3, 4]', '$[0]', false );
1290 select json_array_insert( '[1, 2, 3, 4]', '$[1]', false );
1291 select json_array_insert( '[1, 2, 3, 4]', '$[2]', false );
1292 select json_array_insert( '[1, 2, 3, 4]', '$[3]', false );
1293 select json_array_insert( '[1, 2, 3, 4]', '$[4]', false );
1294 select json_array_insert( '[1, 2, 3, 4]', '$[5]', false );
1295 select json_array_insert( '[1, 2, 3, 4]', '$[last]', false );
1296 select json_array_insert( '[1, 2, 3, 4]', '$[last-1]', false );
1297 select json_array_insert( '[1, 2, 3, 4]', '$[last-2]', false );
1298 select json_array_insert( '[1, 2, 3, 4]', '$[last-3]', false );
1299 select json_array_insert( '[1, 2, 3, 4]', '$[last-4]', false );
1300
1301 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[0]', false );
1302 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[1]', false );
1303 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[2]', false );
1304 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[3]', false );
1305 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[4]', false );
1306 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[5]', false );
1307 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[last]', false );
1308 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[last-1]', false );
1309 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[last-4]', false );
1310
1311 # nop
1312 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.b[0]', false );
1313 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.b[1]', false );
1314
1315 # no auto-wrapping
1316 select json_array_insert( '"a"', '$[0]', true );
1317 select json_array_insert( '[ "a" ]', '$[0][0]', true );
1318 select json_array_insert( '"a"', '$[1]', true );
1319
1320 --error ER_INVALID_JSON_PATH_WILDCARD
1321 select json_insert('[]', '$.a.*[1]', 6);
1322 --error ER_INVALID_JSON_PATH_WILDCARD
1323 select json_insert('[]', '$**[1]', 6);
1324 --error ER_INVALID_JSON_PATH_WILDCARD
1325 select json_insert('[]', '$[*][1]', 6);
1326 --error ER_INVALID_JSON_PATH_WILDCARD
1327 select json_insert('[]', '$[1 to 2]', 6);
1328
1329 # multiple paths,
1330 select json_array_insert( '[ 1, 2, 3 ]', '$[1]', true, '$[1]', false );
1331 select json_array_insert( '[ 1, 2, 3 ]', '$[1]',
1332 cast( '[ "a", "b", "c", "d" ]' as json ), '$[1][2]', false );
1333
1334 # test an error while evaluating the document expression
1335 --error ER_INVALID_JSON_TEXT_IN_PARAM
1336 SELECT JSON_ARRAY_INSERT(JSON_EXTRACT('[1', '$'), '$[0]', 1);
1337
1338 # error in reading new value
1339 --error ER_INVALID_JSON_TEXT_IN_PARAM
1340 select json_array_insert( '[ 1, 2, 3 ]', '$[1]', json_extract( '[', '$' ) );
1341
1342 --echo # ----------------------------------------------------------------------
1343 --echo # Test of JSON_SET function.
1344 --echo # ----------------------------------------------------------------------
1345
1346 # NULLs
1347 select json_set(NULL, '$.b', cast(1 as json));
1348 select json_set('[1,2,3]', NULL, cast(1 as json));
1349 select json_set('[1,2,3]', '$[3]', NULL);
1350
1351 # wrong # args
1352 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1353 select json_set(NULL);
1354 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1355 select json_set(NULL, NULL);
1356 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1357 select json_set(NULL, NULL, NULL, NULL);
1358 --error ER_INVALID_JSON_TEXT_IN_PARAM
1359 SELECT JSON_SET('[', '$[0]', 0);
1360
1361 # Detect errors in nested function calls.
1362 --error ER_INVALID_JSON_TEXT_IN_PARAM
1363 SELECT JSON_SET('{}', '$.name', JSON_EXTRACT('', '$'));
1364
1365 # positive test cases
1366
1367 select json_set('[1,2,3]', '$[2]', 4);
1368 select json_set('[1,2,3]', '$[3]', 4);
1369 select json_set('[1,2,3]', '$[10]', 4);
1370
1371 select json_set('[]', '$[0]', 1);
1372 select json_set('[]', '$[1]', 1);
1373 select json_set('[]', '$[last]', 1);
1374 select json_set('[]', '$[last-1]', 1);
1375
1376 select json_set('{"c":4}', '$.c', 5);
1377 select json_set('{"c":4}', '$.a', 5);
1378
1379 select json_set('1', '$', 4);
1380 select json_set('1', '$[0]', 4);
1381 select json_set('1', '$[1]', 4);
1382 select json_set('1', '$[10]', '4', '$[11]', 5);
1383 select json_set('1', '$[last]', 4);
1384 select json_set('1', '$[last-1]', 4);
1385 select json_set('1', '$[last-10]', '4', '$[last-11]', 5);
1386
1387 select json_set('[1,2,3]', '$[2][0]', 4);
1388 select json_set('[1,2,3]', '$[2][2]', 4);
1389 select json_set('[1,2,3]', '$[2][last]', 4);
1390 select json_set('[1,2,3]', '$[2][last-2]', 4);
1391
1392 select json_set('{"a": 3}', '$.a[0]', 4);
1393 select json_set('{"a": 3}', '$.a[1]', 4, '$.a[2]', '5');
1394 select json_set('{"a": 3}', '$.a[last]', 4);
1395 select json_set('{"a": 3}', '$.a[last-1]', 4, '$.a[last-2]', '5');
1396
1397 # auto-wrap plus ellipsis with nested hits should give: {"a": [{"b": [3, 6]}, 6]}
1398 --error ER_INVALID_JSON_PATH_WILDCARD
1399 select json_set(cast('{"a": {"b": [3]}}' as json), '$**[1]', 6);
1400 --error ER_INVALID_JSON_PATH_WILDCARD
1401 SELECT JSON_SET('[1,2,3]', '$[*]', 4);
1402 --error ER_INVALID_JSON_PATH_WILDCARD
1403 SELECT JSON_SET('[1,2,3]', '$[1 to 2]', 4);
1404
1405 # Examples from the specification: Include when missing functions are
1406 # available.
1407
1408 # returns { "a" : {}, "b" : [ 1, 2, 3 ] }
1409 SELECT JSON_SET('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1410 '$.a',
1411 JSON_OBJECT());
1412
1413 # # returns { "a" : "foo", "b" : [ 1, 2, 3 ], "c" : [ true, false ] }
1414 # SELECT JSON_SET('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1415 # '$.c',
1416 # JSON_ARRAY( true, false ));
1417
1418 # # returns { "a" : "foo", "b" : [ 1, 2, 3 ], "c" : [ true, false ] }
1419 # SELECT JSON_SET('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1420 # '$.c',
1421 # JSON_ARRAY( CAST( 'true' AS JSON ), CAST( 'false' AS JSON ) ));
1422
1423 # # returns [ 1, null, null, 2 ]
1424 # SELECT JSON_SET('1', '$[3]', 2);
1425
1426 # should return { "a": { "b": false, "c": true } }
1427 SELECT JSON_SET('{ "a" : "foo"}', '$.a',
1428 JSON_OBJECT( 'b', false ), '$.a.c', true);
1429
1430 # returns { "a" : {}, "b" : [ 1, 2, 3 ] }
1431 select json_set('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1432 '$.a',
1433 cast('{}' as json));
1434
1435 # returns { "a" : "foo", "b" : [ 1, 2, 3 ], "c" : [ true, false ] }
1436 select json_set('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1437 '$.c',
1438 cast('[true, false]' as json));
1439
1440 # returns [ 1, null, null, 2 ]
1441 select json_set('1', '$[3]', 2);
1442
1443 # should return { "a": { "b": false, "c": true } }
1444 select json_set('{ "a" : "foo"}', '$.a',
1445 cast('{"b": false}' as json), '$.a.c', true);
1446
1447 # examples from wl7909 spec
1448 # returns {"a": {}, "b": [1, 2, 3]}
1449 SELECT JSON_SET
1450 (
1451 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1452 '$.a',
1453 JSON_OBJECT()
1454 );
1455
1456 # returns {"a": "foo", "b": [1, 2, 3], "c": [true, false]}
1457 SELECT JSON_SET
1458 (
1459 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1460 '$.c',
1461 JSON_ARRAY( true, false )
1462 );
1463
1464 # returns {"a": "foo", "b": [1, 2, 3], "c": [true, false]}
1465 SELECT JSON_SET
1466 (
1467 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1468 '$.c',
1469 JSON_ARRAY( CAST( 'true' AS JSON ), CAST( 'false' AS JSON ) )
1470 );
1471
1472 # returns [1, 2]
1473 SELECT JSON_SET
1474 (
1475 '1',
1476 '$[3]',
1477 2
1478 );
1479
1480 # returns {"a": {"b": false, "c": true}}
1481 SELECT JSON_SET
1482 (
1483 '{ "a" : "foo"}',
1484 '$.a', JSON_OBJECT( 'b', false ),
1485 '$.a.c', true
1486 );
1487
1488 --echo # ----------------------------------------------------------------------
1489 --echo # Test of JSON_REPLACE function.
1490 --echo # ----------------------------------------------------------------------
1491
1492 # NULLs
1493 select json_replace(NULL, '$.b', cast(1 as json));
1494 select json_replace('[1,2,3]', NULL, cast(1 as json));
1495 select json_replace('[1,2,3]', '$[2]', NULL);
1496
1497 # wrong # args
1498 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1499 select json_replace(NULL);
1500 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1501 select json_replace(NULL, NULL);
1502 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1503 select json_replace(NULL, NULL, NULL, NULL);
1504
1505 # positive test cases
1506
1507 select json_replace('[1,2,3]', '$[2]', 4);
1508 select json_replace('[1,2,3]', '$[3]', 4);
1509 select json_replace('[1,2,3]', '$[10]', 4);
1510 select json_replace('[1,2,3]', '$[last]', 4);
1511 select json_replace('[1,2,3]', '$[last-1]', 4);
1512 select json_replace('[1,2,3]', '$[last-10]', 4);
1513
1514 select json_replace('{"c":4}', '$.c', 5);
1515 select json_replace('{"c":4}', '$.a', 5);
1516
1517 select json_replace('1', '$', 4);
1518 select json_replace('1', '$[0]', 4);
1519 select json_replace('1', '$[1]', 4);
1520 select json_replace('1', '$[10]', '4', '$[11]', 5);
1521
1522 select json_replace('[1,2,3]', '$[2][0]', 4);
1523 select json_replace('[1,2,3]', '$[2][2]', 4);
1524
1525 select json_replace('{"a": 3}', '$.a[0]', 4);
1526 select json_replace('{"a": 3}', '$.a[1]', 4, '$.a[2]', '5');
1527
1528 # wildcards not allowed
1529 --error ER_INVALID_JSON_PATH_WILDCARD
1530 SELECT JSON_REPLACE('[1,2,3]', '$**.a', 4);
1531 --error ER_INVALID_JSON_PATH_WILDCARD
1532 SELECT JSON_REPLACE('[1,2,3]', '$[*]', 4);
1533 --error ER_INVALID_JSON_PATH_WILDCARD
1534 SELECT JSON_REPLACE('[1,2,3]', '$[1 to 2]', 4);
1535
1536 # Examples from the specification
1537
1538 # returns the original document because the path doesn't exist
1539 SELECT JSON_REPLACE('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1540 '$.c',
1541 true);
1542
1543 # returns '{ "a" : true, "b" : [ 1, 2, 3 ] }'
1544 SELECT JSON_REPLACE('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1545 '$.a[0]',
1546 true);
1547
1548 # returns the original document because the path doesn't exist
1549 SELECT JSON_REPLACE('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1550 '$.b[5]',
1551 true);
1552
1553 --echo # ----------------------------------------------------------------------
1554 --echo # Test of JSON_ARRAY function.
1555 --echo # ----------------------------------------------------------------------
1556
1557 # NULLs
1558 select json_array(NULL, '$.b', cast(1 as json));
1559 select json_array('[1,2,3]', NULL, cast(1 as json));
1560 select json_array('[1,2,3]', '$[3]', NULL);
1561
1562
1563 # positive test cases
1564 select json_array();
1565 select json_array(3.14);
1566 select json_array('[1,2,3]');
1567 select json_array(cast('[1,2,3]' as json));
1568 select json_array(1,2,3);
1569 select json_array(b'0', b'1', b'10');
1570
1571 # returns the empty array: []
1572 SELECT JSON_ARRAY();
1573
1574 --echo # ----------------------------------------------------------------------
1575 --echo # Test of JSON_OBJECT function.
1576 --echo # ----------------------------------------------------------------------
1577
1578 # odd number of args
1579 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1580 select json_object( 'a' );
1581 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1582 select json_object( 'a', 1, 'b' );
1583
1584 # null arg
1585 --error ER_JSON_DOCUMENT_NULL_KEY
1586 select json_object( null, 1 );
1587
1588 # positive tests
1589 select json_object();
1590 select json_object( 'a', null );
1591 select json_object( 'a', 1 );
1592 select json_object( 'a', 1, 'b', 'foo' );
1593 select json_object( 'a', 1, 'b', 'foo', 'c', cast( '{ "d": "wibble" }' as json ) );
1594 select json_object( 'a', true, 'b', false, 'c', cast( 'null' as json) );
1595 select json_valid( json_object( '"a"', 1 ) );
1596
1597 # long key
1598 select json_object( REPEAT('a', 64 * 1024), 1 );
1599
1600 # non-string keyNames are cast to CHAR
1601 select json_object(json_array(), json_array());
1602 select json_object( cast(json_array() as char), json_array());
1603
1604 select json_object( 1, json_array());
1605 select json_object( cast(1 as char), json_array());
1606
1607 # duplicate keys
1608 SELECT JSON_OBJECT('a', 1, 'b', 2, 'a', 3);
1609
1610 # returns the empty object: {}
1611 SELECT JSON_OBJECT();
1612
1613 --echo # ----------------------------------------------------------------------
1614 --echo # Test of JSON_SEARCH function.
1615 --echo # ----------------------------------------------------------------------
1616
1617 # wrong number of args
1618 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1619 select json_search();
1620 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1621 select json_search( '{ "a": true }' );
1622 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1623 select json_search( '{ "a": true }', 'one' );
1624
1625 # null args
1626 select json_search( null, 'one', 'foo' );
1627 select json_search( '{ "a": "foo" }', null, 'foo' );
1628 # FIXME. what should happen here?
1629 #select json_search( '{ "a": "foo" }', 'one', null );
1630 select json_search( '{ "a": "foo" }', 'one', 'foo', null, null );
1631 select json_search( '{ "a": "foo" }', 'one', 'foo', null, '$.a', null );
1632
1633 # bad values for the oneOrAll arg
1634 --error ER_JSON_BAD_ONE_OR_ALL_ARG
1635 select json_search( '{ "a": "foo" }', 'twof', 'foo' );
1636 --error ER_JSON_BAD_ONE_OR_ALL_ARG
1637 select json_search( '{ "a": "foo" }', 'two', 'foo' );
1638
1639 # bad escape arg
1640 --error ER_WRONG_ARGUMENTS
1641 select json_search( '{ "a": "foo" }', 'one', 'foo', 'ab' );
1642
1643 # bad path args
1644 --error ER_INVALID_JSON_PATH
1645 select json_search( '{ "a": "foo" }', 'one', 'foo', null, '$a' );
1646 --error ER_INVALID_JSON_PATH
1647 select json_search( '{ "a": "foo" }', 'all', 'foo', null, '$.a', '$b' );
1648
1649 --error ER_BAD_FIELD_ERROR
1650 select json_search(a, b, c);
1651
1652 # simple tests for search without path arguments
1653 select json_search( '{ "a": "foobar" }', 'one', 'foo%' );
1654 select json_search( '{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }', 'one', 'foo%' );
1655 select json_search( '{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }', 'all', 'foo%' );
1656 select json_search( '{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }', 'all', 'f__us' );
1657
1658 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'all', 'foo%', null, '$.a' );
1659 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'all', 'foo%', null, '$.a', '$.b' );
1660 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'one', 'foo%', null, '$.a', '$.b' );
1661
1662 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'ALL', 'foo%', null, '$.a' );
1663 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'aLl', 'foo%', null, '$.a', '$.b' );
1664 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'ONE', 'foo%', null, '$.a', '$.b' );
1665
1666 # wildcards in the path expression
1667 select json_search
1668 (
1669 '[ { "a": { "b": { "c": "fool" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "food"} } ]',
1670 'all',
1671 'foo%',
1672 null,
1673 '$**.c'
1674 );
1675
1676 select json_search
1677 (
1678 '[ { "a": { "b": { "c": "showtime" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "shoe"} } ]',
1679 'all',
1680 'sho%',
1681 null,
1682 '$**.c'
1683 );
1684
1685 select json_search
1686 (
1687 '[ { "a": { "b": { "c": "showtime" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "shoe"} } ]',
1688 'all',
1689 'sho%e',
1690 null,
1691 '$**.c'
1692 );
1693
1694 select json_search
1695 (
1696 '[ { "a": { "b": { "c": "showtime" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "shoe"} } ]',
1697 'all',
1698 'sho%',
1699 null,
1700 '$[*].c'
1701 );
1702
1703 select json_search
1704 (
1705 '[ { "a": { "b": { "c": "showtime" } } }, [ { "b": { "c": "shout" } }, { "c": { "c": "shoe"} } ] ]',
1706 'all',
1707 'sho%',
1708 null,
1709 '$[1]**.c'
1710 );
1711
1712 # escape character
1713 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo%bar' );
1714 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar' );
1715 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|' );
1716 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]' );
1717 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]', '$[1]' );
1718 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]', '$[1]', '$[2]' );
1719
1720 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar', null );
1721 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar', null, '$[0]' );
1722 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar', null, '$[1]' );
1723 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]' );
1724 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[1]' );
1725
1726 # search is case-sensitive
1727 select json_search( '[ "abc", "ABC" ]', 'all', 'aBc' );
1728 select json_search( '[ "abc", "ABC" ]', 'all', 'abc' );
1729 select json_search( '[ "abc", "ABC" ]', 'all', 'ABC' );
1730
1731 # only matches strings, not numerics
1732 select json_search( '[ 10, "10", 1.0, "1.0" ]', 'all', '1%' );
1733
1734 # examples from the wl7909 spec
1735 # returns null because numeric values don't match string values
1736 SELECT JSON_SEARCH
1737 (
1738 '{ "a" : 123, "b" : [ 123, 456 ] }',
1739 'one',
1740 '123'
1741 );
1742
1743 # returns "$.b[2]"
1744 SELECT JSON_SEARCH
1745 (
1746 '{ "a" : "123", "b" : [ 123, "789", "123", "456", "123" ] }',
1747 'one',
1748 '123',
1749 null,
1750 '$.b'
1751 );
1752
1753 # could return either "$.a" or "$.b.key"
1754 SELECT JSON_SEARCH
1755 (
1756 '{ "a" : "123", "b" : { "key" : "123" } }',
1757 'one',
1758 '123'
1759 );
1760
1761 # returns "$.b.key"
1762 SELECT JSON_SEARCH
1763 (
1764 '{ "a" : "1243", "b" : { "key" : "1234" } }',
1765 'one',
1766 '123%'
1767 );
1768
1769 # returns "$.b.c"
1770 SELECT JSON_SEARCH
1771 (
1772 '{ "a" : "1243", "b" : { "key" : "1234", "c": "directorysub%directoryabc" } }',
1773 'one',
1774 'dir%torysub@%dir%',
1775 '@'
1776 );
1777
1778 # returns null because the path doesn't exist
1779 SELECT JSON_SEARCH
1780 (
1781 '{ "a" : "1243", "b" : { "key" : "1234" } }',
1782 'one',
1783 '123%',
1784 null,
1785 '$.c'
1786 );
1787
1788 # returns $."one potato"
1789 SELECT JSON_UNQUOTE
1790 (
1791 JSON_SEARCH
1792 (
1793 '{ "onepotato": "foot", "one potato": "food" , "one \\"potato": "fool" }',
1794 'all',
1795 'food'
1796 )
1797 );
1798
1799 select json_type(case (null is null) when 1 then
1800 cast('null' as json) else
1801 cast('[1,2,3]' as json) end);
1802
1803 select json_type(case (null is not null) when 1 then
1804 cast('null' as json) else
1805 cast('[1,2,3]' as json) end);
1806
1807 select json_type( if(null is null,
1808 cast('null' as json),
1809 cast('[1,2,3]' as json)) );
1810
1811 select json_type( if(null is not null,
1812 cast('null' as json),
1813 cast('[1,2,3]' as json)));
1814
1815 select cast(json_extract(cast(concat('[', cast('["A",2]' as json), ']') as json),
1816 '$[0][1]') as char) = 2;
1817
1818 --echo # ----------------------------------------------------------------------
1819 --echo # Test of aggregate function MAX, MIN.
1820 --echo # ----------------------------------------------------------------------
1821
1822 select max(cast('[1,2,3]' as json));
1823
1824 --echo # ----------------------------------------------------------------------
1825 --echo # Test of JSON_QUOTE, JSON_UNQUOTE
1826 --echo # ----------------------------------------------------------------------
1827
1828 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1829 select json_quote();
1830 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1831 select json_quote('abc', 'def');
1832 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1833 select json_quote(NULL, 'def');
1834 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1835 select json_quote('abc', NULL);
1836
1837 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1838 select json_unquote();
1839 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1840 select json_unquote('"abc"', '"def"');
1841 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1842 select json_unquote(NULL, 'def');
1843 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1844 select json_unquote('"abc"', NULL);
1845
1846 select json_quote(NULL);
1847 select json_unquote(NULL);
1848
1849 select json_quote('abc');
1850 select json_quote(convert('abc' using ascii));
1851 select json_quote(convert('abc' using latin1));
1852 select json_quote(convert('abc' using utf8));
1853 select json_quote(convert('abc' using utf8mb4));
1854
1855 select json_unquote('abc'); # should do nothing
1856
1857 select json_unquote('"abc"');
1858 select json_unquote(convert('"abc"' using ascii));
1859 select json_unquote(convert('"abc"' using latin1));
1860 select json_unquote(convert('"abc"' using utf8));
1861 select json_unquote(convert('"abc"' using utf8mb4));
1862
1863 select json_quote('"');
1864 select json_unquote('"'); # should do nothing
1865
1866 --error ER_INCORRECT_TYPE
1867 select json_quote(123); # integer not allowed
1868 --error ER_INCORRECT_TYPE
1869 select json_unquote(123); # integer not allowed
1870
1871 select json_unquote('""'); # empty string
1872 select char_length(json_unquote('""')); # verify empty string
1873 select json_unquote('"" '); # unchanged: no final "
1874 select json_unquote(cast(json_quote('abc') as json)); # round trip
1875
1876 # No change in this JSON string: it is an object
1877 select cast('{"abc": "foo"}' as json);
1878 select json_unquote(cast('{"abc": "foo"}' as json));
1879
1880 # This is a JSON string, so it is actually unquoted
1881 select json_extract(cast('{"abc": "foo"}' as json), '$.abc');
1882 select json_unquote(json_extract(cast('{"abc": "foo"}' as json), '$.abc'));
1883
1884 # Bug fix: thse should be the same
1885 select json_unquote('["a", "b", "c"]');
1886 select json_unquote(cast('["a", "b", "c"]' as json));
1887
1888 select charset(json_unquote('"abc"'));
1889
1890 select json_quote(convert(X'e68891' using utf8)); # chinese "I" (wo3)
1891 select json_quote(convert(X'e68891' using utf8mb4)); # chinese "I" (wo3)
1892
1893 select cast(json_quote(convert(X'e68891' using utf8)) as json);
1894
1895 select json_unquote(convert(X'e68891' using utf8)); # chinese "I" (wo3)
1896
1897 select json_quote(json_quote(json_quote('abc'))); # deep quote
1898 select json_unquote(json_unquote(json_unquote( # long round trip of it
1899 json_quote(json_quote(json_quote('abc'))))));
1900
1901 # DATE/TIME will lose their quotes, too:
1902 select cast(cast('2015-01-15 23:24:25' as datetime) as json);
1903 select json_unquote(cast(cast('2015-01-15 23:24:25' as datetime) as json));
1904
1905 # as well as opaque values:
1906 select cast(st_geomfromtext('point(1 1)') as json);
1907 select json_unquote(cast(st_geomfromtext('point(1 1)') as json));
1908
1909 # examples from the wl7909 spec
1910 # returns the SQL string literal abc
1911 SELECT JSON_UNQUOTE( '"abc"' );
1912
1913 # returns the SQL string literal "abc
1914 SELECT JSON_UNQUOTE( '"abc' );
1915
1916 --error ER_INCORRECT_TYPE
1917 SELECT JSON_UNQUOTE( 123 );
1918
1919 # returns the SQL string literal abc
1920 SELECT JSON_UNQUOTE
1921 ( CAST( CAST( '"abc"' AS JSON ) AS CHAR ) );
1922
1923 # returns 1
1924 SELECT JSON_UNQUOTE
1925 (
1926 CAST(
1927 JSON_EXTRACT( '{ "userName" : "fred" }', '$.userName' )
1928 AS CHAR
1929 )
1930 ) = 'fred';
1931
1932 # returns 0
1933 SELECT
1934 CAST(
1935 JSON_EXTRACT( '{ "userName" : "fred" }', '$.userName' )
1936 AS CHAR
1937 ) = 'fred';
1938
1939 # returns "abc"
1940 SELECT JSON_QUOTE( 'abc' );
1941
1942 --error ER_INCORRECT_TYPE
1943 SELECT JSON_QUOTE( 123 );
1944
1945 # returns the JSON document consisting of the string scalar "123"
1946 SELECT CAST( JSON_QUOTE( '123' ) AS JSON );
1947
1948 --echo # ----------------------------------------------------------------------
1949 --echo # Test of JSON_CONTAINS
1950 --echo # ----------------------------------------------------------------------
1951
1952 --echo # should give NULL
1953 select json_contains(NULL, NULL);
1954 select json_contains(cast('{"a": 1, "b": 2}' as json), NULL);
1955 select json_contains(NULL, cast('null' as json));
1956 select json_contains(cast('[1]' as json), cast('[1]' as json), NULL);
1957
1958 --echo # should give 0:
1959 select json_contains(cast(3.14 as json), cast(3 as json));
1960
1961 --echo # should give 0: not at top level
1962 select json_contains(cast('{"a": {"b": 7}}' as json), cast('{"b": 7}' as json));
1963
1964 --echo # but path argument will fix it:
1965 select json_contains(cast('{"a": {"b": 7}}' as json), cast('{"b": 7}' as json), '$.a');
1966
1967 --echo # but arrays "introspect"
1968 select json_contains(cast('[1,[2.0, 3.0]]' as json), cast('[2.0]' as json));
1969 select json_contains(cast('[1, 2, [3, [4, 5]], 6, 7]' as json), cast('5' as json));
1970
1971 --echo # should give 0: just a key
1972 select json_contains(cast('{"a": 1, "b": 2}' as json), cast('"a"' as json));
1973
1974 --echo # should give 0: one candidate element doesn't match
1975 select json_contains(cast('[1]' as json), cast('[1,2]' as json));
1976
1977 --echo # should all give 1
1978 select json_contains(cast('null' as json), cast('null' as json));
1979 --echo # simple object subset
1980 select json_contains(cast('{"a": 1, "b": 2}' as json), cast( '{"a": 1}' as json));
1981 --echo # simple vector subset
1982 select json_contains(cast('[1, 2, 3]' as json), cast('[1, 3]' as json));
1983 --echo # auto-wrap, should give 1
1984 select json_contains(cast('[1, 2, 3]' as json), cast(3 as json));
1985 --echo # ok even with nested cast off elements
1986 select json_contains(cast('{"person": {"id": 1, "country": "norway"}}' as json),
1987 cast('{"person": {"country": "norway"}}' as json));
1988 --echo # vector reordering and duplicates is ok
1989 select json_contains(cast('[1,3,5]' as json), cast('[5,3,1,5]' as json));
1990 --echo # ok even with more elts in candidate than in doc
1991 select json_contains(cast('[{"b": 4, "a":7}]' as json), cast('[{"a":7},{"b":4}]' as json));
1992 select json_contains(cast('[{"b": 4, "a":7}, 5]' as json), cast('[5, {"a":7, "b":4}]' as json));
1993 --echo # ok even with mixed number types that compare equal
1994 select json_contains(cast('[{"b": 4, "a":7}, 5.0]' as json), cast('[5, {"a":7.0E0, "b":4}]' as json));
1995
1996 # Bug discovered by Rick: used to give 1 (true).
1997 select json_contains( '{"customer": "cust3"}', '{"customer": "cust1"}' );
1998
1999 SELECT JSON_CONTAINS('[null,1,[2,3],true,false]', '[null,1,[3],false]');
2000 SELECT JSON_CONTAINS('[null,1,[2,3],true,false]', '[null,1,[4],false]');
2001 SELECT JSON_CONTAINS('[true,false]', '[[true]]');
2002 SELECT JSON_CONTAINS('[1,2]', '[[1]]');
2003 SELECT JSON_CONTAINS('[1,2]', '1', '$.abc');
2004 SELECT JSON_CONTAINS('{}', '{}');
2005 SELECT JSON_CONTAINS('{}', '{"a":1}');
2006 SELECT JSON_CONTAINS('{"a":1}', '{"a":1,"b":2}');
2007
2008 # examples from the wl7909 spec
2009 # returns 1
2010 SELECT JSON_CONTAINS
2011 (
2012 CAST('[1, 4, 6]' AS JSON),
2013 CAST('[1, 6]' AS JSON)
2014 );
2015
2016 # returns 1; even with nested cast off elements
2017 SELECT JSON_CONTAINS
2018 (
2019 CAST('{"person": {"id": 1, "country": "norway"}}' AS JSON),
2020 CAST('{"person": {"country": "norway"}}' AS JSON)
2021 );
2022
2023 # returns 1; reordering and duplicates are ok
2024 SELECT JSON_CONTAINS
2025 (
2026 CAST('[1,3,5]' AS JSON),
2027 CAST('[5,3,1,5]' AS JSON)
2028 );
2029
2030 # return 0; no type conversion is performed
2031 SELECT JSON_CONTAINS
2032 (
2033 CAST('[3.14]' AS JSON),
2034 CAST('[3]' AS JSON)
2035 );
2036
2037 # returns 1, due to auto-wrapping
2038 SELECT JSON_CONTAINS
2039 (
2040 CAST('[1, 2, 3]' AS JSON),
2041 CAST(3 AS JSON)
2042 );
2043
2044 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
2045 SELECT JSON_CONTAINS();
2046 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
2047 SELECT JSON_CONTAINS('[1]');
2048 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
2049 SELECT JSON_CONTAINS('[1]', '[1]', '$', '$[0]');
2050
2051 --echo # ----------------------------------------------------------------------
2052 --echo # Wrong collation from JSON_QUOTE caused problems: Set it in
2053 --echo # Item_func_json_quote::fix_length_and_dec. Bug found by Knut.
2054 --echo # Similar issue for JSON_UNQUOTE and JSON_TYPE.
2055 --echo # ----------------------------------------------------------------------
2056 select json_object("a", ifnull(json_quote('test'), cast('null' as json)));
2057 select cast(concat('[', json_quote('ab'), ']') as json);
2058 select cast(concat('[', json_unquote('"12"'), ']') as json);
2059 select cast(concat('["', json_type(cast(1 as json)), '"]') as json);
2060
2061 --echo #
2062 --echo # Bug#20912438: ITEM_TYPE_HOLDER::DISPLAY_LENGTH(ITEM*): ASSERTION `0' FAILED
2063 --echo #
2064 (SELECT JSON_KEYS('{ "key80": "2015-04-20 11:53:55"}')) UNION ALL
2065 (SELECT JSON_KEYS('{ "key80": "2015-04-20 11:53:55" }') LIMIT 0);
2066 SELECT CAST(1 AS JSON) UNION ALL SELECT CAST(1 AS JSON);
2067
2068 # Exercise NULL handling and error handling in Item_copy_json::copy().
2069 SELECT COUNT(*), CAST(NULL AS JSON);
2070 --error ER_INVALID_JSON_TEXT_IN_PARAM
2071 SELECT COUNT(*), JSON_EXTRACT('not valid json!', '$');
2072
2073 --echo # ----------------------------------------------------------------------
2074 --echo # Bug report from John E.
2075 --echo # Crash in Item_copy_json::~Item_copy_json
2076 --echo # ----------------------------------------------------------------------
2077 EXPLAIN SELECT COUNT(*), JSON_KEYS('{}');
2078
2079 select json_search( '{ "a": "foo" }', 'one', 'foo', 'a' );
2080 select json_search( '{ "a": "foo" }', 'one', 'foo', null );
2081 select json_search( '{ "a": "foo" }', 'one', 'foo', convert(x'f8' using latin1) );
2082
2083 # bad escape arg
2084 --error ER_WRONG_ARGUMENTS
2085 select json_search( '{ "a": "foo" }', 'one', 'foo', 'ab' );
2086
2087 --echo # ----------------------------------------------------------------------
2088 --echo # Wrong results when Json_path_cache primed is accessed
2089 --echo # during the prepare-phase.
2090 --echo #----------------------------------------------------------------------
2091
2092 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', null ) is null;
2093
2094 prepare stmt1 from 'select json_remove( ''[ 1, { "a": true, "b": false, "c": null }, 5 ]'', null ) is null';
2095 execute stmt1;
2096
2097 --error ER_INVALID_JSON_PATH
2098 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$.' ) is null;
2099
2100 --error ER_INVALID_JSON_PATH
2101 prepare stmt1 from 'select json_remove( ''[ 1, { "a": true, "b": false, "c": null }, 5 ]'', ''$.'' ) is null';
2102
2103 --echo # ----------------------------------------------------------------------
2104 --echo # Bug#20987329 VALUE OF PREPARED STATEMENT PLACEHOLDER FOR PARAMETER
2105 --echo # IN JSON_EXTRACT IS STICKY
2106 --echo #----------------------------------------------------------------------
2107
2108 # should get different results with different parameter values
2109
2110 # json_extract()
2111
2112 # json_contains()
2113
2114 prepare json_stmt1 FROM 'select json_contains( ''{ "keyA": [1, 2, 3], "keyB": [4, 5, 6] }'', ''[2]'', ? )';
2115 set @mypath = '$.keyA';
2116 execute json_stmt1 USING @mypath;
2117 set @mypath = '$.keyB';
2118 execute json_stmt1 USING @mypath;
2119
2120 # json_contains_path()
2121
2122 prepare json_stmt2 FROM 'select json_contains_path( ''{ "keyA": [1, 2, 3] }'', ''all'', ? )';
2123 set @mypath = '$.keyA';
2124 execute json_stmt2 USING @mypath;
2125 set @mypath = '$.keyB';
2126 execute json_stmt2 USING @mypath;
2127
2128 # json_length()
2129
2130 prepare json_stmt3 FROM 'select json_length( ''{ "keyA": [1, 2, 3], "keyB": [1, 2, 3, 4] }'', ? )';
2131 set @mypath = '$.keyA';
2132 execute json_stmt3 USING @mypath;
2133 set @mypath = '$.keyB';
2134 execute json_stmt3 USING @mypath;
2135
2136 # json_keys()
2137
2138 prepare json_stmt4 FROM 'select json_keys( ''[ { "keyA": true }, { "keyB": false } ]'', ? )';
2139 set @mypath = '$[0]';
2140 execute json_stmt4 USING @mypath;
2141 set @mypath = '$[1]';
2142 execute json_stmt4 USING @mypath;
2143
2144 # json_array_append()
2145
2146 prepare json_stmt5 FROM 'select json_array_append( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2147 set @mypath = '$.keyA';
2148 execute json_stmt5 USING @mypath;
2149 set @mypath = '$.keyB';
2150 execute json_stmt5 USING @mypath;
2151
2152 # json_insert()
2153
2154 prepare json_stmt6 FROM 'select json_insert( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2155 set @mypath = '$.keyA[2]';
2156 execute json_stmt6 USING @mypath;
2157 set @mypath = '$.keyB[2]';
2158 execute json_stmt6 USING @mypath;
2159
2160 # json_set()
2161
2162 prepare json_stmt7 FROM 'select json_set( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2163 set @mypath = '$.keyA[2]';
2164 execute json_stmt7 USING @mypath;
2165 set @mypath = '$.keyB[2]';
2166 execute json_stmt7 USING @mypath;
2167
2168 # json_replace()
2169
2170 prepare json_stmt8 FROM 'select json_replace( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2171 set @mypath = '$.keyA[1]';
2172 execute json_stmt8 USING @mypath;
2173 set @mypath = '$.keyB[1]';
2174 execute json_stmt8 USING @mypath;
2175
2176 # json_search()
2177
2178 prepare json_stmt9 FROM 'select json_search( ''{ "keyA": [ "foot" ], "keyB": [ "food" ] }'', ''all'', ''foo%'', null, ? )';
2179 set @mypath = '$.keyA';
2180 execute json_stmt9 USING @mypath;
2181 set @mypath = '$.keyB';
2182 execute json_stmt9 USING @mypath;
2183
2184 # json_remove()
2185
2186 prepare json_stmt10 FROM 'select json_remove( ''{ "keyA": [ "foot" ], "keyB": [ "food" ] }'', ? )';
2187 set @mypath = '$.keyA';
2188 execute json_stmt10 USING @mypath;
2189 set @mypath = '$.keyB';
2190 execute json_stmt10 USING @mypath;
2191
2192 # similar caching problem for the oneOrAll args
2193
2194 prepare json_stmt11 FROM 'select json_contains_path( ''{ "keyA": true }'', ?, ''$.keyA'', ''$.keyB'' )';
2195 set @mypath = 'one';
2196 execute json_stmt11 USING @mypath;
2197 set @mypath = 'all';
2198 execute json_stmt11 USING @mypath;
2199
2200 prepare json_stmt12 FROM 'select json_search( ''{ "keyA": [ "foot" ], "keyB": [ "food" ] }'', ?, ''foo%'' )';
2201 set @mypath = 'one';
2202 execute json_stmt12 USING @mypath;
2203 set @mypath = 'all';
2204 execute json_stmt12 USING @mypath;
2205
2206 --echo #
2207 --echo # Bug#21128632 JSON_QUOTE(JSON_TYPE(...)) GIVES ERROR 3139 ER_INVALID_JSON_CHARSET
2208 --echo #
2209
2210 select json_quote( json_type( json_object() ) );
2211 select json_quote( json_type( cast('{}' as json) ) );
2212
2213 --echo #
2214 --echo # Bug#21148020 OUTPUT FROM JSON_TYPE() IS TRUNCATED
2215 --echo # WHEN EXECUTED IN A VIEW OR JOIN
2216 --echo #
2217
2218 SELECT JSON_TYPE(JSON_OBJECT());
2219 CREATE VIEW v1 AS SELECT JSON_TYPE(JSON_OBJECT());
2220 SELECT * FROM v1;
2221
2222 SELECT JSON_TYPE(CAST(CAST('2015-05-25 11:23:55' AS DATETIME) AS JSON));
2223 CREATE VIEW v2 AS SELECT JSON_TYPE(CAST(CAST('2015-05-25 11:23:55' AS
2224 DATETIME) AS JSON));
2225 SELECT * FROM v2;
2226
2227 drop view v1;
2228 drop view v2;
2229
2230 --echo #
2231 --echo # Bug#21198333 SIG 6 IN ITEM_CACHE_JSON::CACHE_VALUE AT SQL/ITEM.CC:9470
2232 --echo #
2233 --error ER_INVALID_JSON_TEXT_IN_PARAM
2234 SELECT MIN(JSON_EXTRACT('not json', '$'));
2235
2236 --echo #
2237 --echo # Bug#21200657 DATA FROM DERIVED TABLE BASED
2238 --echo # ON JSN_QUOTE()/JSN_UNQUOTE() CALL IS TRUNCATED
2239 --echo #
2240 SELECT JSON_QUOTE('This is a string that should not be truncated') AS field1;
2241 SELECT JSON_UNQUOTE(JSON_QUOTE('This is a string that should not be truncated')) AS field1;
2242
2243 SELECT * FROM (SELECT JSON_QUOTE('This is a string that should not be truncated') AS field1) AS DERIVED_TABLE;
2244 SELECT * FROM (SELECT JSON_UNQUOTE("This is a string that should not be truncated") AS field1) AS DERIVED_TABLE;
2245 SELECT * FROM (SELECT JSON_UNQUOTE(JSON_QUOTE('This is a string that should not be truncated')) AS field1) AS DERIVED_TABLE;
2246
2247 --echo #
2248 --echo # Bug#21296173 JSON_OBJECT('KEY', BOOLEAN_LITERAL) USES VALUES 0, 1
2249 --echo # FOR BOOL WHEN USED VIA VIEW
2250 --echo #
2251
2252 SELECT JSON_OBJECT('key1', false, 'key2', true);
2253 SELECT JSON_ARRAY('key1', false, 'key2', true);
2254 CREATE VIEW v1 AS SELECT JSON_OBJECT('key1', false, 'key2', true);
2255 SELECT * FROM v1;
2256 CREATE VIEW v2 AS SELECT JSON_ARRAY('key1', false, 'key2', true);
2257 SELECT * FROM v2;
2258
2259 drop view v1;
2260 drop view v2;
2261
2262 --echo #
2263 --echo # Bug#21293089 JSON_CONTAINS() RETURNS WRONG RESULT WITH EMPTY JSON ARRAY
2264 --echo #
2265 SELECT JSON_CONTAINS('[]', '{"a" : 1}');
2266 SELECT JSON_CONTAINS('[]', '[1, 2, 3, 4, 5]');
2267 SELECT JSON_CONTAINS('[]', '[1, 2, 3, 4, {"a" : 1}]');
2268 SELECT JSON_CONTAINS('[]', '{"a" : [1, 2, 3, 4, 5]}');
2269 SELECT JSON_CONTAINS('[]', '[]');
2270
2271 --echo #
2272 --echo # Bug#21377136 STACK OVERFLOW IN RAPIDJSON::GENERICREADER
2273 --echo #
2274 --error ER_JSON_DOCUMENT_TOO_DEEP
2275 SELECT JSON_VALID(REPEAT('[', 100000));
2276 --error ER_JSON_DOCUMENT_TOO_DEEP
2277 SELECT JSON_VALID(REPEAT('{"a":', 100000));
2278 --error ER_JSON_DOCUMENT_TOO_DEEP
2279 SELECT JSON_VALID(REPEAT('{"a":[', 100000));
2280 --error ER_JSON_DOCUMENT_TOO_DEEP
2281 SELECT JSON_VALID(REPEAT('[{"a":', 100000));
2282
2283 --echo #
2284 --echo # Bug#21381806 JSON: ASSERTION FAILED: ARG->NULL_VALUE
2285 --echo #
2286 SELECT JSON_SET(CASE WHEN 1 THEN NULL ELSE NULL END, '{}', '{}');
2287 SELECT JSON_VALID(CASE WHEN 1 THEN NULL ELSE NULL END);
2288 SELECT JSON_ARRAY(CASE WHEN 1 THEN NULL ELSE NULL END);
2289
2290 --echo #
2291 --echo # Bug#21384048 ASSERTION FAILED: N >= 0 && N <= 308
2292 --echo # IN RAPIDJSON::INTERNAL::FASTPATH
2293 --echo #
2294 SELECT JSON_EXTRACT('-1E-36181012216111515851075235238', '$');
2295 SELECT JSON_EXTRACT('1E-36181012216111515851075235238', '$');
2296 SELECT JSON_EXTRACT('1E-325', '$');
2297 SELECT JSON_EXTRACT('1E-324', '$');
2298 SELECT JSON_EXTRACT('1E-323', '$');
2299 SELECT JSON_EXTRACT('1E+308', '$');
2300 --error ER_INVALID_JSON_TEXT_IN_PARAM
2301 SELECT JSON_EXTRACT('1E+309', '$');
2302 --error ER_INVALID_JSON_TEXT_IN_PARAM
2303 SELECT JSON_EXTRACT('1E+36181012216111515851075235238', '$');
2304 --error ER_INVALID_JSON_TEXT_IN_PARAM
2305 SELECT JSON_EXTRACT('-1E+36181012216111515851075235238', '$');
2306
2307 --echo #
2308 --echo # Bug#21383284: ASSERTION IN SELECT_LEX::SETUP_CONDS
2309 --echo #
2310 --error ER_WRONG_ARGUMENTS
2311 SELECT 1 FROM dual WHERE JSON_SEARCH('{}', 'one', 'foo', 'too-long-escape');
2312 --error ER_INVALID_JSON_TEXT_IN_PARAM
2313 SELECT 1 FROM dual WHERE JSON_SEARCH('{}', 'one', 'foo', JSON_EXTRACT('', '$'));
2314
2315 --echo #
2316 --echo # Bug#21442624 INCORRECT RESULT FROM JSON_SET WITH AUTO-WRAPPING
2317 --echo #
2318
2319 SELECT JSON_SET('1', '$', 100);
2320 SELECT JSON_SET('1', '$[0]', 100);
2321 SELECT JSON_SET('1', '$[0][0]', 100);
2322 SELECT JSON_SET('1', '$[0][0][0]', 100);
2323
2324 SELECT JSON_SET('[]', '$', 100);
2325 SELECT JSON_SET('[]', '$[0]', 100);
2326 SELECT JSON_SET('[]', '$[0][0]', 100);
2327 SELECT JSON_SET('[]', '$[0][0][0]', 100);
2328
2329 SELECT JSON_SET('[1]', '$', 100);
2330 SELECT JSON_SET('[1]', '$[0]', 100);
2331 SELECT JSON_SET('[1]', '$[0][0]', 100);
2332 SELECT JSON_SET('[1]', '$[0][0][0]', 100);
2333
2334 SELECT JSON_SET('[[1]]', '$', 100);
2335 SELECT JSON_SET('[[1]]', '$[0]', 100);
2336 SELECT JSON_SET('[[1]]', '$[0][0]', 100);
2337 SELECT JSON_SET('[[1]]', '$[0][0][0]', 100);
2338
2339 SELECT JSON_SET('[[[1]]]', '$', 100);
2340 SELECT JSON_SET('[[[1]]]', '$[0]', 100);
2341 SELECT JSON_SET('[[[1]]]', '$[0][0]', 100);
2342 SELECT JSON_SET('[[[1]]]', '$[0][0][0]', 100);
2343
2344 SELECT JSON_REPLACE('1', '$', 100);
2345 SELECT JSON_REPLACE('1', '$[0]', 100);
2346 SELECT JSON_REPLACE('1', '$[0][0]', 100);
2347 SELECT JSON_REPLACE('1', '$[0][0][0]', 100);
2348
2349 SELECT JSON_REPLACE('[]', '$', 100);
2350 SELECT JSON_REPLACE('[]', '$[0]', 100);
2351 SELECT JSON_REPLACE('[]', '$[0][0]', 100);
2352 SELECT JSON_REPLACE('[]', '$[0][0][0]', 100);
2353
2354 SELECT JSON_REPLACE('[1]', '$', 100);
2355 SELECT JSON_REPLACE('[1]', '$[0]', 100);
2356 SELECT JSON_REPLACE('[1]', '$[0][0]', 100);
2357 SELECT JSON_REPLACE('[1]', '$[0][0][0]', 100);
2358
2359 SELECT JSON_REPLACE('[[1]]', '$', 100);
2360 SELECT JSON_REPLACE('[[1]]', '$[0]', 100);
2361 SELECT JSON_REPLACE('[[1]]', '$[0][0]', 100);
2362 SELECT JSON_REPLACE('[[1]]', '$[0][0][0]', 100);
2363
2364 SELECT JSON_REPLACE('[[[1]]]', '$', 100);
2365 SELECT JSON_REPLACE('[[[1]]]', '$[0]', 100);
2366 SELECT JSON_REPLACE('[[[1]]]', '$[0][0]', 100);
2367 SELECT JSON_REPLACE('[[[1]]]', '$[0][0][0]', 100);
2368
2369
2370 --echo #
2371 --echo # Bug#21828321: JSON FUNCS CALL DBUG_ABORT OR EXIT() ON WINDOWS!
2372 --echo #
2373 # LEAST and GREATEST treat JSON arguments as strings for now. They used to hit
2374 # an assertion if used in a JSON context and all arguments were JSON values, or
2375 # a mix of NULLs and JSON values.
2376 SELECT JSON_ARRAY(LEAST(NULL, NULL), GREATEST(NULL, NULL), LEAST(j1, NULL),
2377 GREATEST(NULL, j2), LEAST(j1, j2), GREATEST(j1, j2)) AS j
2378 FROM (SELECT CAST('1' AS JSON) AS j1, CAST('2' AS JSON) AS j2) t;
2379
2380 --echo #
2381 --echo # Bug#22961128: WRONG RESULT WHEN MANIPULATING RESULT OF JSON_MERGE
2382 --echo #
2383 SELECT JSON_SET(JSON_ARRAY(JSON_MERGE_PRESERVE('{"a":1}', '{"a":2}')),
2384 '$[0].a', 123);
2385
2386 --echo #
2387 --echo # Bug#22887227: ASSERTION FAILED: THIS_TYPE != JSON_DOM::J_ERROR
2388 --echo #
2389 SELECT NULLIF(CASE WHEN 1 THEN NULL ELSE JSON_ARRAY() END, 1);
2390 SELECT JSON_TYPE(CASE WHEN 1 THEN NULL ELSE JSON_ARRAY() END);
2391 SELECT MIN(CASE 0 WHEN 1 THEN JSON_ARRAY() ELSE NULL END);
2392
2393 --echo #
2394 --echo # Bug #24930829 BITWISE OPERATORS: CRASH IN ITEM_FUNC_BIT_COUNT::VAL_INT
2395 --echo # Test case does not fail anymore after Bug#24930038 fix was pushed
2396 --echo # since a check for null return value was added.
2397 --echo #
2398 --error ER_INVALID_TYPE_FOR_JSON
2399 DO BIT_COUNT(COMPRESS(1) << JSON_DEPTH(1));
2400
2401 --echo #
2402 --echo # Bug#25516881: JSON_UNQUOTE RETURNS WRONG RESULT WHEN INPUT IS UTF-16
2403 --echo #
2404 # Used to return the quoted string unchanged.
2405 SELECT JSON_UNQUOTE(CAST('"abc"' AS CHAR CHARSET utf16));
2406 # Used to accept binary strings.
2407 --error ER_INVALID_JSON_CHARSET
2408 SELECT JSON_UNQUOTE(CAST('ABCD' AS BINARY));
2409 # JSON_UNQUOTE should return utf8mb4 encoded results, but used to return utf16.
2410 SELECT HEX(JSON_UNQUOTE(CAST('"abc"' AS CHAR CHARSET utf16))),
2411 HEX(JSON_UNQUOTE(CAST('abc' AS CHAR CHARSET utf16)));
2412
2413 --echo #
2414 --echo # Bug#25516960: JSON_SEARCH AND JSON_CONTAINS_PATH REJECT ONE-OR-ALL
2415 --echo # ARGUMENT IF IT IS UTF-16
2416 --echo #
2417 SELECT JSON_SEARCH('["a", "a"]', CAST('one' AS CHAR CHARSET utf16), 'a') AS c1,
2418 JSON_SEARCH('["a", "a"]', CAST('all' AS CHAR CHARSET utf16), 'a') AS c2,
2419 JSON_CONTAINS_PATH('{"a":"x"}', CAST('one' AS CHAR CHARSET utf16),
2420 '$.a', '$.b') AS c3,
2421 JSON_CONTAINS_PATH('{"a":"x"}', CAST('all' AS CHAR CHARSET utf16),
2422 '$.a', '$.b') AS c4;
2423
2424 --echo #
2425 --echo # Bug#25530204: THE RESULT OF SUM ON JSON_EXTRACT LOST THE DECIMAL PART
2426 --echo #
2427 SELECT SUM(CAST('5.45' AS JSON));
2428
2429 --echo #
2430 --echo # Bug#25518504: SOME BIG NUMBERS IN JSON DOCUMENTS BECOME ZERO
2431 --echo #
2432 # These statements returned zero instead of raising an error.
2433 --error ER_INVALID_JSON_TEXT_IN_PARAM
2434 SELECT CAST('2e308' AS JSON);
2435 --error ER_INVALID_JSON_TEXT_IN_PARAM
2436 SELECT CAST('-2e308' AS JSON);
2437 # These statements correctly returned an error even before the fix.
2438 --error ER_INVALID_JSON_TEXT_IN_PARAM
2439 SELECT CAST('2e309' AS JSON);
2440 --error ER_INVALID_JSON_TEXT_IN_PARAM
2441 SELECT CAST('-2e309' AS JSON);
2442
2443 --echo #
2444 --echo # Bug#25818544: Assert failure when using result from NULLIF as JSON
2445 --echo #
2446 SELECT JSON_EXTRACT(NULLIF(JSON_OBJECT('a', 123), JSON_ARRAY()), '$.a');
2447 SELECT JSON_EXTRACT(NULLIF('a', 'a'), '$');
2448 SELECT JSON_EXTRACT(NULLIF('1', '2'), '$');
2449
2450 --echo #
2451 --echo # WL#9831: Ranges in JSON path expressions
2452 --echo #
2453
2454 SELECT JSON_EXTRACT('[1,2,3,4]', '$[0 to 0]');
2455 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to 1]');
2456 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to 2]');
2457 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to 3]');
2458 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to 4]');
2459 SELECT JSON_EXTRACT('[1,2,3,4]', '$[0 to 2]');
2460 SELECT JSON_EXTRACT('[1,2,3,4]', '$[0 to 3]');
2461 SELECT JSON_EXTRACT('[1,2,3,4]', '$[0 to 4]');
2462 SELECT JSON_EXTRACT('[1,2,3,4]', '$[99 to 100]');
2463 SELECT JSON_EXTRACT('[1,2,3,4]', '$[100 to 100]');
2464 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last to last]');
2465 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-1 to last-1]');
2466 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-1 to last]');
2467 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-3 to last-1]');
2468 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-4 to last]');
2469 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-4 to 5]');
2470 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last to 2]');
2471 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-1 to 2]');
2472 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-1 to 3]');
2473 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-100 to last-99]');
2474 SELECT JSON_EXTRACT('[1,2,3,4]', '$[last-100 to last-100]');
2475 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to last-1]');
2476 SELECT JSON_EXTRACT('[1,2,3,4]', '$[2 to last-2]');
2477 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[1 to 2]');
2478 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[1 to 3]');
2479 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[1 to 4]');
2480 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[0 to 2]');
2481 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[0 to 3]');
2482 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[0 to 4]');
2483 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[99 to 100]');
2484 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last-1 to last]');
2485 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last-3 to last-1]');
2486 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last-4 to last]');
2487 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last-4 to 5]');
2488 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last to 2]');
2489 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last-1 to 2]');
2490 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last-1 to 3]');
2491 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[last-100 to last-99]');
2492 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[1 to last-1]');
2493 SELECT JSON_EXTRACT('[1,2,3,4]', '$**[2 to last-2]');
2494
2495 SELECT JSON_EXTRACT('[]', '$[0 to 1]');
2496 SELECT JSON_EXTRACT('[]', '$[last-1 to last]');
2497
2498 SELECT JSON_CONTAINS_PATH('[1,2]', 'one', '$[0 to 2]') AS o,
2499 JSON_CONTAINS_PATH('[1,2]', 'all', '$[0 to 2]') AS a,
2500 JSON_CONTAINS_PATH('[1,2]', 'one', '$**[0 to 2]') AS o_e,
2501 JSON_CONTAINS_PATH('[1,2]', 'all', '$**[0 to 2]') AS a_e;
2502
2503 SELECT JSON_EXTRACT('[1,2,3,4]', '$[2][0 to 10]');
2504 SELECT JSON_EXTRACT('[1,2,3,4]', '$[2][0 to last-2]');
2505 SELECT JSON_EXTRACT('[1,2,3,4]', '$[2][0 to last]');
2506 SELECT JSON_EXTRACT('[1,2,3,4]', '$[2][1 to last]');
2507 SELECT JSON_EXTRACT('[1,2,3,4]', '$[2][last-2 to last]');
2508 SELECT JSON_EXTRACT('[1,2,3,4]', '$[2][last-2 to 0]');
2509 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to 2][last]');
2510 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to 2][0 to 10]');
2511 SELECT JSON_EXTRACT('[1,2,3,4]', '$[1 to 2][last-10 to last]');
2512
2513 SELECT JSON_REMOVE('[1,2,3]', '$[last]');
2514 SELECT JSON_REMOVE('[1,2,3]', '$[last-1]');
2515 SELECT JSON_REMOVE('[1,2,3]', '$[last-2]');
2516 SELECT JSON_REMOVE('[1,2,3]', '$[last-3]');
2517
2518 --error ER_INVALID_JSON_PATH
2519 SELECT JSON_EXTRACT('true', '$[2 to 1]');
2520 --error ER_INVALID_JSON_PATH
2521 SELECT JSON_EXTRACT('true', '$[last to last-1]');
2522 --error ER_INVALID_JSON_PATH_WILDCARD
2523 SELECT JSON_SET('[1,2,3]', '$[0 to 1]', 123);
2524 SELECT JSON_LENGTH('[1,2,3]', '$[0 to 1]');
2525
2526 SELECT j->'$[last]', j->'$[last-1]', j->'$[last-2]',
2527 j->'$**[last]', j->'$**[last-1]', j->'$**[last-2]'
2528 FROM (SELECT JSON_ARRAY(1,2) j) t;
2529
2530 --echo #
2531 --echo # WL#9692: Deprecate the JSON_MERGE function
2532 --echo #
2533 --echo # Expect a deprecation warning.
2534 SELECT JSON_MERGE('{}', '{}');
2535 --echo # Expect a deprecation warning when creating the view.
2536 CREATE VIEW v AS SELECT JSON_MERGE('{}', '{}') AS col;
2537 --echo # The view uses the non-deprecated variant of the function.
2538 SHOW CREATE VIEW v;
2539 SELECT * FROM v;
2540 DROP VIEW v;
2541
2542 --echo #
2543 --echo # Bug#26238736: ASSERTION `__NULL != DYNAMIC_CAST<TARGET>(ARG)' FAILURE
2544 --echo #
2545 SELECT CAST('{"a": [1,2,3], "a": [4,5,6]}' AS JSON);
2546
2547 --echo #
2548 --echo # Bug#26022576: JSON_INSERT DOES NOT HANDLE AUTO-WRAPPING
2549 --echo #
2550 SELECT JSON_INSERT('{"a":1}', '$.a[1]', 123) AS c1,
2551 JSON_INSERT('{"a":1}', '$[0].a[1]', 123) AS c2,
2552 JSON_INSERT('{"a":1}', '$.a[0][1]', 123) AS c3;
2553
2554 --echo #
2555 --echo # Bug#26649978: JSON_INSERT: ASSERTION FAILED: PARENT
2556 --echo #
2557 --echo # c1, c2 and c3 should have the same result due to auto-wrapping,
2558 --echo # but the expressions in c2, c3 and c4 used to hit an assert.
2559 SELECT JSON_INSERT('{"a":1}', '$[1]', 2) AS c1,
2560 JSON_INSERT('{"a":1}', '$[0][1]', 2) AS c2,
2561 JSON_INSERT('{"a":1}', '$[0][0][1]', 2) AS c3,
2562 JSON_INSERT('{"a":1}', '$[last][1]', 2) AS c4;
2563
2564
2565 --echo #
2566 --echo # Bug#27028889: MYSQL NORMALIZES DECIMAL NUMBERS WITH
2567 --echo # ZERO FRACTIONAL PART TO INTEGERS
2568 --echo #
2569
2570 SET @floating_points = '[0.0, -0.0, 1.0, -1.0, 3.14,
2571 999999999999999.0, -999999999999999.0,
2572 1000000000000000.0, -1000000000000000.0,
2573 1e200, -1e200]';
2574
2575 SELECT CAST(CAST(@floating_points AS JSON) AS CHAR) AS x;
2576 SELECT JSON_PRETTY(@floating_points);
2577 SELECT j, JSON_TYPE(j) FROM
2578 JSON_TABLE(CAST(CAST(CAST(@floating_points AS JSON) AS CHAR) AS JSON),
2579 '$[*]' COLUMNS (o FOR ORDINALITY, j JSON PATH '$')) AS jt
2580 ORDER BY o;
2581
2582 --echo #
2583 --echo # Bug#27312444: ASAN: MEMCPY-PARAM-OVERLAP WITH JSON_QUOTE FUNCTION
2584 --echo #
2585 SELECT JSON_QUOTE(RIGHT(REPEAT(_utf8mb4'b', 64), 50));
2586 SELECT JSON_QUOTE(RIGHT(REPEAT(_utf8mb4'\n', 64), 50));
2587 SELECT JSON_QUOTE(RIGHT(REPEAT(_latin1'b', 64), 50));
2588 SELECT JSON_QUOTE(RIGHT(REPEAT(_latin1'\n', 64), 50));
2589
2590 --echo #
2591 --echo # Bug#27891359: JSON_SET RETURNS MALFORMED JSON WHEN IT INCLUDES BINARY DATA
2592 --echo #
2593 SELECT CAST(CAST(CAST(CAST(REPEAT('a', 100) AS BINARY) AS JSON) AS CHAR) AS JSON);
2594
2595 --echo # Bug#31358486: Assertion `!current_thd->is_error() && has_value' failed
2596
2597 --error ER_INVALID_TYPE_FOR_JSON
2598 DO ANY_VALUE(CAST(JSON_TYPE(1) AS JSON));
2599 --error ER_INCORRECT_TYPE
2600 DO ANY_VALUE(JSON_ARRAY(JSON_QUOTE(-1681986720))) ;
2601
2602 CREATE TABLE sink(i INTEGER);
2603 --error ER_TRUNCATED_WRONG_VALUE
2604 INSERT INTO sink SELECT 'x'= JSON_ARRAY(CAST(12345 AS CHAR(4)));
2605 --error ER_TRUNCATED_WRONG_VALUE
2606 INSERT INTO sink SELECT 'x'= JSON_ARRAY(CAST('bad' AS SIGNED));
2607 --error ER_TRUNCATED_WRONG_VALUE
2608 INSERT INTO sink SELECT 'x'= JSON_ARRAY(CAST('bad' AS DECIMAL(10,4)));
2609 --error ER_TRUNCATED_WRONG_VALUE
2610 INSERT INTO sink SELECT 'x'= JSON_ARRAY(CAST('bad' AS DATE));
2611 --error ER_TRUNCATED_WRONG_VALUE
2612 INSERT INTO sink SELECT 'x'= JSON_ARRAY(CAST('bad' AS DOUBLE));
2613 --error ER_GIS_INVALID_DATA
2614 INSERT INTO sink SELECT 'x'= JSON_ARRAY(ST_GEOMFROMTEXT('MULTIPOINT(1 2 3 4)'));
2615
2616 DROP TABLE sink;
2617
2618
2619 --echo #
2620 --echo # Bug#31393934 ASSERTION `OTHER_TYPE != ENUM_JSON_TYPE::J_ERROR' FAILED.
2621 --echo #
2622
2623 SELECT JSON_OBJECT("a",1) IN (1,(LEAD(1,70) OVER()));
2624
2625 --echo #
2626 --echo # Bug #31856260 STILL SEEING A FEW ASSERTION
2627 --echo # `THIS_TYPE != ENUM_JSON_TYPE::J_ERROR' FAILED
2628 --echo #
2629
2630 --error ER_WRONG_VALUE_FOR_TYPE
2631 DO NULLIF(FROM_BASE64(UUID_TO_BIN(0x339ed325) AND ST_ASGEOJSON(POINT(-10946,-1634))), 1);
2632
2633 --error ER_WRONG_VALUE_FOR_TYPE
2634 DO NULLIF(FROM_BASE64(UUID_TO_BIN(0x339ed325) OR ST_ASGEOJSON(POINT(-10946,-1634))), 1);
2635
2636 --error ER_INVALID_JSON_TEXT_IN_PARAM
2637 DO NULLIF(FROM_BASE64(JSON_STORAGE_FREE('') XOR ST_ASGEOJSON(POINT(-10946,-1634))), 1);
2638
2639 --echo #
2640 --echo # Bug#32047630: MISSING ERROR PROPAGATION IN JSON FUNCTIONS
2641 --echo #
2642
2643 --error ER_INVALID_JSON_TEXT_IN_PARAM
2644 SELECT JSON_ARRAY(CAST('' AS JSON) = 1, 1);
2645
2646 --error ER_INVALID_JSON_TEXT_IN_PARAM
2647 SELECT JSON_OBJECT(CAST('' AS JSON), '');
2648 # Used to contain the misleading error:
2649 # JSON documents may not contain NULL member names.
2650 SHOW ERRORS;
2651
2652 --error ER_INVALID_JSON_TEXT_IN_PARAM
2653 SELECT JSON_ARRAY(CAST(CAST('' AS JSON) AS DATE), 1);
2654
2655 --error ER_INVALID_JSON_TEXT_IN_PARAM
2656 SELECT JSON_ARRAY(JSON_SEARCH('{}', CAST('' AS JSON), 'abc'), 1);
2657
2658 --error ER_INVALID_JSON_TEXT_IN_PARAM
2659 SELECT JSON_ARRAY(JSON_EXTRACT('{}', CAST('' AS JSON)), 1);
2660
2661 --echo # Bug#32063203: Problem with prepared statements
2662
2663 # MEMBER OF
2664
2665 CREATE TABLE t1(c1 JSON);
2666 INSERT INTO t1 VALUES ('["a", "b"]'), ('["b", "c"]'), ('["c", "a"]');
2667 SELECT * FROM t1 WHERE 'a' MEMBER OF (c1 -> '$');
2668 prepare s1 from "SELECT * FROM t1 WHERE ? MEMBER OF (c1 -> '$')";
2669 set @x = 'a';
2670 execute s1 using @x # OK - returns ["a", "b"] and ["c", "a"];
2671
2672 DROP TABLE t1;
2673
2674 # JSON_ARRAY_APPEND
2675
2676 SET @j = '["a", ["b", "c"], "d"]';
2677 SELECT JSON_ARRAY_APPEND(@j, '$[1]', 'z'); # OK, returns ["a", ["b", "c", "z"], "d"]
2678 SELECT JSON_ARRAY_APPEND(@j, '$[1]', '"z"'); # OK, returns ["a", ["b", "c", "\"z\""], "d"]
2679 prepare s from "SELECT JSON_ARRAY_APPEND(@j, '$[1]', ?)";
2680 set @x='z';
2681 execute s using @x; # OK - returns ["a", ["b", "c", "z"], "d"]
2682 set @x='"z"';
2683 execute s using @x; # OK - returns ["a", ["b", "c", "\"z\""], "d"]
2684
2685 # JSON_ARRAY_INSERT
2686
2687 SET @j = '["a", ["b", "c"], "d"]';
2688 SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'z'); # OK, returns ["a", "z", ["b", "c"], "d"]
2689 SELECT JSON_ARRAY_INSERT(@j, '$[1]', '"z"'); # OK, returns ["a", "\"z\"", ["b", "c"], "d"]
2690 prepare s from "SELECT JSON_ARRAY_INSERT(@j, '$[1]', ?)";
2691 set @x='z';
2692 execute s using @x; # OK - returns ["a", "z", ["b", "c"], "d"]
2693 set @x='"z"';
2694 execute s using @x; # OK - returns ["a", "\"z\"", ["b", "c"], "d"]
2695
2696 # JSON_OVERLAPS
2697
2698 SELECT JSON_OVERLAPS("true","true"); # OK, returns true
2699 prepare s from "SELECT JSON_OVERLAPS('true',?)";
2700 set @jv='true';
2701 execute s using @jv; # OK, returns true
2702 SELECT JSON_OVERLAPS('"abc"','"abc"'); # OK, returns true
2703 prepare s from "SELECT JSON_OVERLAPS('\"abc\"',?)";
2704 set @jv='"abc"';
2705 execute s using @jv; # OK, returns true
2706 set @jv="abc";
2707 --error ER_INVALID_JSON_TEXT_IN_PARAM
2708 execute s using @jv; # Fails, error 3141
2709
2710 # JSON_ARRAY
2711
2712 set @p1 = 1;
2713 set @p2 = 'abc';
2714 set @p3 = '"abc"';
2715 set @p4 = NULL;
2716 set @p5 = 'true';
2717 set @p6 = DATE'2000-01-01';
2718 SELECT JSON_ARRAY(1, 'abc', '"abc"', NULL, 'true', DATE'2000-01-01');
2719 # OK -> [1, "abc", "\"abc\"", null, true, "2000-01-01"]
2720 prepare s from "SELECT JSON_ARRAY(?, ?, ?, ?, ?, ?)";
2721 execute s using @p1, @p2, @p3, @p4, @p5, @p6;
2722
2723 # JSON_OBJECT
2724
2725 SELECT JSON_OBJECT('a', 1, 'b', 'abc', 'c', '"abc"', 'd', NULL,
2726 'e', 'true', 'f', DATE'2000-01-01'); # OK
2727 prepare s from "SELECT JSON_OBJECT('a', ?, 'b', ?, 'c', ?, 'd', ?,
2728 'e', ?, 'f', ?)";
2729 execute s using @p1, @p2, @p3, @p4, @p5, @p6; # OK
2730
2731 # JSON_CONTAINS
2732
2733 --error ER_INVALID_JSON_TEXT_IN_PARAM
2734 select JSON_CONTAINS('{"a": "x", "b": "y"}', 'x', '$.a'); # Fail
2735 select JSON_CONTAINS('{"a": "x", "b": "y"}', '"x"', '$.a'); # OK, returns true
2736
2737 prepare s from "SELECT JSON_CONTAINS('{""a"": ""x"", ""b"": ""y""}', ?, '$.a')";
2738 set @a='x';
2739 --error ER_INVALID_JSON_TEXT_IN_PARAM
2740 execute s using @a; # Fails
2741 set @a='"x"';
2742 execute s using @a; # OK, returns true
2743
2744 # JSON_SEARCH
2745
2746 SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
2747 SELECT JSON_SEARCH(@j, 'one', 'abc'); # -> OK, returns "$[0]"
2748 SELECT JSON_SEARCH(@j, 'one', '"abc"'); # -> OK, returns NULL
2749
2750 prepare s from "SELECT JSON_SEARCH(@j, 'one', ?)";
2751 set @x='abc';
2752 execute s using @x; # -> OK, returns "$[0]"
2753 set @x='"abc"';
2754 execute s using @x; # -> OK, returns NULL
2755
2756 # JSON_MERGE_PATCH
2757
2758 --error ER_INVALID_JSON_TEXT_IN_PARAM
2759 SELECT JSON_MERGE_PATCH('[1, 2]', 'xyz'); # Fails - error 3141
2760 SELECT JSON_MERGE_PATCH('[1, 2]', '"xyz"'); # OK - returns "xyz"
2761 prepare s from "SELECT JSON_MERGE_PATCH('[1, 2]', ?)";
2762 set @x='xyz';
2763 --error ER_INVALID_JSON_TEXT_IN_PARAM
2764 execute s using @x; # Fails - error 3141
2765 set @x='"xyz"';
2766 execute s using @x; # OK - returns "xyz"
2767
2768 # JSON_MERGE_PRESERVE
2769
2770 --error ER_INVALID_JSON_TEXT_IN_PARAM
2771 SELECT JSON_MERGE_PRESERVE('[1, 2]', 'xyz'); # Fails - error 3141
2772 SELECT JSON_MERGE_PRESERVE('[1, 2]', '"xyz"'); # OK - returns [1, 2, "xyz"]
2773 prepare s from "SELECT JSON_MERGE_PRESERVE('[1, 2]', ?)";
2774 set @x='xyz';
2775 --error ER_INVALID_JSON_TEXT_IN_PARAM
2776 execute s using @x; # Fails - error 3141
2777 set @x='"xyz"';
2778 execute s using @x; # OK - returns [1, 2, "xyz"]
2779
2780 # JSON_INSERT
2781
2782 SELECT JSON_INSERT('{"a" : 1}', '$.b', 'xyz'); # OK - returns {"a": 1, "b": "xyz"}
2783 SELECT JSON_INSERT('{"a" : 1}', '$.b', '"xyz"'); # OK - returns {"a": 1, "b": "\"xyz\""}
2784 prepare s from "SELECT JSON_INSERT('{""a"" : 1}', '$.b', ?)";
2785 set @x='xyz';
2786 execute s using @x; # OK - returns {"a": 1, "b": "xyz"}
2787 set @x='"xyz"';
2788 execute s using @x; # OK - returns {"a": 1, "b": "\"xyz\""}
2789
2790 # JSON_REPLACE
2791
2792 SELECT JSON_REPLACE('{"a" : 1}', '$.a', 'xyz'); # OK - returns {"a": "xyz"}
2793 SELECT JSON_REPLACE('{"a" : 1}', '$.a', '"xyz"'); # OK - returns {"a": "\"xyz\""}
2794 prepare s from "SELECT JSON_REPLACE('{""a"" : 1}', '$.a', ?)";
2795 set @x='xyz';
2796 execute s using @x; # OK - returns {"a": "xyz"}
2797 set @x='"xyz"';
2798 execute s using @x; # OK - returns {"a": "\"xyz\""}
2799
2800 # JSON_SET
2801
2802 SELECT JSON_SET('{"a" : 1}', '$.a', 'xyz'); # OK - returns {"a": "xyz"}
2803 SELECT JSON_SET('{"a" : 1}', '$.a', '"xyz"'); # OK - returns {"a": "\"xyz\""}
2804 prepare s from "SELECT JSON_SET('{""a"" : 1}', '$.a', ?)";
2805 set @x='xyz';
2806 execute s using @x; # OK - returns {"a": "xyz"}
2807 set @x='"xyz"';
2808 execute s using @x; # OK - returns {"a": "\"xyz\""}
2809
2810 --echo #
2811 --echo # Bug #32162329 BOOL JSON_VALUE: ASSERTION `!CURRENT_THD->IS_ERROR()' FAILED.
2812 --echo #
2813
2814 --error ER_INVALID_TYPE_FOR_JSON
2815 DO TO_BASE64
2816 (((JSON_PRETTY(CONVERT(JSON_KEYS(1,1) USING utf8mb4)))
2817 +
2818 (JSON_MERGE_PRESERVE('{ }' ,'{ }')))
2819 );
2820 --error ER_INVALID_TYPE_FOR_JSON
2821 DO TO_BASE64
2822 (((JSON_PRETTY(CONVERT(JSON_KEYS(1,1) USING utf8mb4)))
2823 -
2824 (JSON_MERGE_PRESERVE('{ }' ,'{ }')))
2825 );
2826 --error ER_INVALID_TYPE_FOR_JSON
2827 DO TO_BASE64
2828 (((JSON_PRETTY(CONVERT(JSON_KEYS(1,1) USING utf8mb4)))
2829 *
2830 (JSON_MERGE_PRESERVE('{ }' ,'{ }')))
2831 );
2832 --error ER_INVALID_TYPE_FOR_JSON
2833 DO TO_BASE64
2834 (((JSON_PRETTY(CONVERT(JSON_KEYS(1,1) USING utf8mb4)))
2835 /
2836 (JSON_MERGE_PRESERVE('{ }' ,'{ }')))
2837 );
2838 --error ER_INVALID_TYPE_FOR_JSON
2839 DO TO_BASE64
2840 (((JSON_PRETTY(CONVERT(JSON_KEYS(1,1) USING utf8mb4)))
2841 %
2842 (JSON_MERGE_PRESERVE('{ }' ,'{ }')))
2843 );
2844
2845 --error ER_INVALID_JSON_TEXT_IN_PARAM
2846 DO CAST(JSON_PRETTY('') AS SIGNED) + CAST(JSON_PRETTY('') AS SIGNED);
2847 --error ER_INVALID_JSON_TEXT_IN_PARAM
2848 DO CAST(JSON_PRETTY('') AS SIGNED) - CAST(JSON_PRETTY('') AS SIGNED);
2849 --error ER_INVALID_JSON_TEXT_IN_PARAM
2850 DO CAST(JSON_PRETTY('') AS SIGNED) * CAST(JSON_PRETTY('') AS SIGNED);
2851 --error ER_INVALID_JSON_TEXT_IN_PARAM
2852 DO CAST(JSON_PRETTY('') AS SIGNED) / CAST(JSON_PRETTY('') AS SIGNED);
2853 --error ER_INVALID_JSON_TEXT_IN_PARAM
2854 DO CAST(JSON_PRETTY('') AS SIGNED) % CAST(JSON_PRETTY('') AS SIGNED);
2855
2856 --echo #
2857 --echo # Bug #32206506 ASSERTION `!CURRENT_THD->IS_ERROR()' FAILED.
2858 --echo #
2859
2860 --error ER_INVALID_TYPE_FOR_JSON
2861 do nullif((week(json_merge(88,1,'[false,1001892796]' ,8 ))),(0x4622ae3d ));
2862 --error ER_INVALID_TYPE_FOR_JSON
2863 do truncate(sum(1),json_pretty((not(1))));
2864 --error ER_INCORRECT_TYPE
2865 do ifnull((json_unquote(1)),(json_type(1)));
2866 --error ER_INVALID_TYPE_FOR_JSON
2867 do ((json_depth(from_unixtime(1536999222)))<=>(1));
2868 --error ER_INVALID_JSON_TEXT_IN_PARAM
2869 do ((json_length('key2','key1'))<=>(1));
2870 --error ER_INVALID_TYPE_FOR_JSON
2871 do ((1)not in((json_keys(4353)),(current_role())));
2872 --error ER_INVALID_TYPE_FOR_JSON
2873 do ((1)<=>(json_length(2,'key2')));
2874 --error ER_INVALID_TYPE_FOR_JSON
2875 do aes_decrypt(json_type(1),json_keys('-4*2c*'));
2876 --error ER_INVALID_TYPE_FOR_JSON
2877 do ((1)not in ((json_remove(19,'{}')),((not(1)))));
2878 --error ER_INVALID_TYPE_FOR_JSON
2879 do ((json_storage_size(1))<=(json_storage_size('')));
2880 --error ER_INVALID_JSON_TEXT_IN_PARAM
2881 do ((select(95))<=>(json_contains('{ }',' * }* ')));
2882 --error ER_DATA_OUT_OF_RANGE
2883 do truncate(exp(0xa701c3bc),(not(json_valid('{}'))));
2884 --error ER_INVALID_TYPE_FOR_JSON
2885 do ((' ')<=>(json_type(CURRENT_TIMESTAMP)));
2886 --error ER_DATA_OUT_OF_RANGE
2887 do ((cot(':-%%& 1*'))>>(json_keys('{ }')));
2888 --error ER_INVALID_JSON_TEXT_IN_PARAM
2889 do ((get_format(datetime,'JIS'))<=>(json_type('key3')));
2890 --error ER_INVALID_JSON_TEXT_IN_PARAM
2891 do strcmp(json_storage_size('*1'),json_valid(null));
2892 --error ER_INVALID_TYPE_FOR_JSON
2893 do sha2((!(json_keys(abs('')))),json_pretty(null));
2894 --error ER_WRONG_ARGUMENTS
2895 do ((period_diff(20259,1872))>((not(('{ }')))));
2896 --error ER_DATA_OUT_OF_RANGE
2897 do ((exp(4812))>>(year((!((not(degrees(''))))))));
2898 --error ER_INVALID_JSON_TEXT_IN_PARAM
2899 do pow(json_storage_size(':*'),(!(oct(1))));
2900
2901 --echo #
2902 --echo # Bug#32231393: ITEM_FUNC_IF::VAL_JSON(JSON_WRAPPER*):
2903 --echo # ASSERTION `HAS_VALUE' FAILED
2904 --echo #
2905
2906 --error ER_INVALID_JSON_TEXT_IN_PARAM
2907 DO IF(JSON_DEPTH(''), JSON_OBJECT(), NULL);
2908
2909 --echo #
2910 --echo # Bug #32231620 JSON_WRAPPER::COMPARE CONST:
2911 --echo # ASSERTION `THIS_TYPE != ENUM_JSON_TYPE::J_ERROR' FAILED.
2912
2913 --error ER_INVALID_JSON_PATH_WILDCARD
2914 do if(json_replace(json_object(), '$[0]' ,'false', '$[*]', '{}'),
2915 42,
2916 (json_arrayagg("") and rtrim("")) <=> 0
2917 );
2918
2919 --error ER_INVALID_JSON_PATH_WILDCARD
2920 do if(json_replace(json_object(), '$[0]' ,'false', '$[*]', '{}'),
2921 cast(42 as float),
2922 (json_arrayagg("") and rtrim("")) <=> 0
2923 );
2924
2925 --error ER_INVALID_JSON_PATH_WILDCARD
2926 do if(json_replace(json_object(), '$[0]' ,'false', '$[*]', '{}'),
2927 cast(42 as decimal),
2928 (json_arrayagg("") and rtrim("")) <=> 0
2929 );
2930
2931 --error ER_INVALID_JSON_PATH_WILDCARD
2932 do if(json_replace(json_object(), '$[0]' ,'false', '$[*]', '{}'),
2933 localtimestamp(6),
2934 (json_arrayagg("") and rtrim("")) <=> 0
2935 );
2936
2937 --echo #
2938 --echo # Bug#32449181: CHARACTER SET ENCODING ISSUES WITH JSON_SEARCH
2939 --echo #
2940 SELECT JSON_SEARCH('["abc"]', 'one', CAST('abc' AS CHAR CHARSET utf16));
2941 SELECT JSON_SEARCH('["abc"]', 'one', 'aabc', CAST('a' AS CHAR CHARSET utf16));
2942
2943 --echo # Bug32787464: Json_wrapper::compare const: assertion
2944 --echo # `other_type != enum_json_type::J_ERROR' failed
2945
2946 --error ER_INVALID_JSON_TEXT_IN_PARAM
2947 DO NULLIF(NULLIF(JSON_OBJECTAGG('ab',' *. b$_ 7?C **c3'),
2948 EXTRACT(MINUTE_MICROSECOND FROM json_storage_size('* .$B7$'))
2949 ), 1);
2950 --echo #
2951 --echo # Bug #32864910 ASSERTION `THIS_TYPE != ENUM_JSON_TYPE::J_ERROR' FAILED.
2952 --echo #
2953
2954 --error ER_GIS_INVALID_DATA
2955 do st_geometryn(st_astext(1, 'axis-order=srid-defined'),
2956 st_aswkt(least(not(json_arrayagg('aa')), connection_id())));
2957
2958 # Arg_comparator::compare_decimal
2959 --error ER_CANNOT_CONVERT_STRING
2960 do nullif(json_arrayagg(convert(utc_timestamp() using gb2312)) <=>
2961 instr(1 , 0xf722a67b), -9223372036854775809);
2962
2963 # Arg_comparator::compare_int_signed_unsigned
2964 --error ER_GIS_INVALID_DATA
2965 do nullif(st_within(
2966 st_asbinary(st_aswkt(('B ~')) ,'axis-order=long-lat') ,
2967 st_aswkt('2031-05-29 01:40:44.878673' +
2968 interval(!json_arrayagg('')) second_microsecond,
2969 'axis-order=long-lat')), connection_id());
2970
2971 --echo #
2972 --echo # Bug#32942328: ITEM_FUNC_COALESCE::VAL_JSON:
2973 --echo # ASSERTION `!CURRENT_THD->IS_ERROR() && HAS_VALUE'
2974 --echo #
2975 --error ER_INVALID_JSON_TEXT_IN_PARAM
2976 DO 1 IN (JSON_PRETTY(''), ANY_VALUE(JSON_ARRAYAGG('')));
2977
2978 --echo #
2979 --echo # Bug#35217471 json.json_no_table MTR test fails on valgrind/
2980 --echo # longlong2decimal & int2my_decimal
2981 --echo #
2982 --error ER_CANNOT_CONVERT_STRING
2983 do cast(json_arrayagg(convert(utc_timestamp() using gb2312)) <=>
2984 instr(1 , 0xf722a67b) as char);