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 JSON_COMPACT(0.0e0) = -0.0e0;
11 SELECT JSON_COMPACT(CAST(0 AS DECIMAL)) = CAST(-0.0e0 AS DECIMAL);
12 SELECT JSON_COMPACT(0.0e0) = CAST(-0.0e0 AS DECIMAL);
13 SELECT JSON_COMPACT(CAST(0 AS DECIMAL)) = -0.0e0;
14 SELECT JSON_COMPACT(CAST(0 AS SIGNED)) = -0.0e0;
15 SELECT JSON_COMPACT(CAST(0 AS SIGNED)) = CAST(-0.0e0 AS DECIMAL);
16 SELECT JSON_COMPACT(CAST(0 AS UNSIGNED)) = -0.0e0;
17 SELECT JSON_COMPACT(CAST(0 AS UNSIGNED)) = 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 json_compact('"abc"');
23 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
24 select json_compact('abc');
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(json_compact('[123]'));
54
55 --echo
56 --echo # Json expression NULL
57 select JSON_VALID(json_compact(NULL));
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( json_compact(CURDATE()) );
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 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
112 select json_contains_path('{ "a": tru }', 'all', '$' );
113 --echo error ER_INVALID_JSON_PATH
114 select json_contains_path('{ "a": true }', 'all', '$[' );
115 --echo error ER_INVALID_JSON_PATH
116 select json_contains_path('{ "a": true }', 'all', '$a.***[3]' );
117 --echo error ER_JSON_BAD_ONE_OR_ALL_ARG
118 select json_contains_path('{ "a": true }', 'foo', '$.a' );
119 --echo 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 --echo 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 --echo error ER_INVALID_JSON_PATH
234 select json_length( 'true', 'c$' );
235 --echo
236 --echo # invalid json path
237 --echo error ER_INVALID_JSON_PATH
238 select json_length( '{ "foo" : [ true, false ] }', '$.foo[bar]' );
239 --echo
240 --echo # wildcards not allowed in path expressions for this function
241 --echo error ER_INVALID_JSON_PATH_WILDCARD
242 select json_length( 'true', '$.*' );
243 --echo
244 --echo # wildcards not allowed in path expressions for this function
245 --echo error ER_INVALID_JSON_PATH_WILDCARD
246 select json_length( 'true', '$.foo**.bar' );
247
248 # json_length() with non-vacuous path expressions
249
250 # 1
251 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[0]' );
252 # 3
253 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[1]' );
254 # 1
255 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' );
256 # auto-wrapping: 1
257 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ); # auto-wrap scalar
258 select json_length( '[ 1, [ 2, 3, 4 ], {"a": 1} ]', '$[2][0]' ); # ditto object
259
260 # non-existent path: null
261 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][1]' );
262 # 3
263 select json_length( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]' );
264
265 # examples from the wl7909 spec
266 # returns 0
267 SELECT JSON_LENGTH
268 (
269 '{}'
270 );
271
272 # returns 1
273 SELECT JSON_LENGTH
274 (
275 '3'
276 );
277
278 # returns 2
279 SELECT JSON_LENGTH
280 (
281 '{ "a" : 123, "b" : [ 123, 456, 789 ] }'
282 );
283
284 # returns 3
285 SELECT JSON_LENGTH
286 (
287 '{ "a" : 123, "b" : [ 123, 456, 789 ] }',
288 '$.b'
289 );
290
291 # returns null because the path does not exist
292 SELECT JSON_LENGTH
293 (
294 '{ "a" : 123, "b" : [ 123, 456, 789 ] }',
295 '$.c'
296 );
297
298 --echo # ----------------------------------------------------------------------
299 --echo # Test of JSON_DEPTH function.
300 --echo # ----------------------------------------------------------------------
301 select json_depth(null);
302 select json_depth(json_compact(null));
303 #select i, json_depth(j) from t1;
304 select json_depth(json_compact('[]')),
305 json_depth(json_compact('{}')),
306 json_depth(json_compact('null')),
307 json_depth(json_quote('foo'));
308 select json_depth(json_compact('[[2], 3, [[[4]]]]'));
309 select json_depth(json_compact('{"a": {"a1": [3]}, "b": {"b1": {"c": {"d": [5]}}}}'));
310
311 # examples from the wl7909 spec
312 # returns 1
313 SELECT JSON_DEPTH
314 (
315 '{}'
316 );
317
318 # returns 1
319 SELECT JSON_DEPTH
320 (
321 '[]'
322 );
323
324 # returns 1
325 SELECT JSON_DEPTH( '"abc"' );
326
327 # returns 1
328 SELECT JSON_DEPTH( json_compact( '"abc"') );
329
330 --echo error ER_INVALID_TYPE_FOR_JSON
331 SELECT JSON_DEPTH( 1 );
332
333 #Check after fix MDEV-31728
334 --disable_cursor_protocol
335 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
336 SELECT JSON_DEPTH( 'abc' );
337 --enable_cursor_protocol
338
339 # returns 1
340 SELECT JSON_DEPTH( json_compact( 1) );
341
342 # returns 2
343 SELECT JSON_DEPTH
344 (
345 '{ "a" : true, "b" : false, "c" : null }'
346 );
347
348 # returns 2
349 SELECT JSON_DEPTH
350 (
351 '[ "a", true, "b" , false, "c" , null ]'
352 );
353
354 # returns 2
355 SELECT JSON_DEPTH
356 (
357 '{ "a" : true, "b" : {}, "c" : null }'
358 );
359
360 # returns 2
361 SELECT JSON_DEPTH
362 (
363 '[ "a", true, "b" , {}, "c" , null ]'
364 );
365
366 # returns 3
367 SELECT JSON_DEPTH
368 (
369 '{ "a" : true, "b" : { "e" : false }, "c" : null }'
370 );
371
372 # returns 3
373 SELECT JSON_DEPTH
374 (
375 '[ "a", true, "b" , { "e" : false }, "c" , null ]'
376 );
377
378 #Check after fix MDEV-31728
379 --disable_cursor_protocol
380 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
381 SELECT JSON_DEPTH
382 (
383 '[ "a", true, "b" , { "e" : false }, "c" , null'
384 );
385 --enable_cursor_protocol
386
387 --echo # ----------------------------------------------------------------------
388 --echo # Test of JSON_REMOVE function.
389 --echo # ----------------------------------------------------------------------
390
391 # null args
392 select json_remove( null, '$[1]' );
393 select json_remove( null, '$[1]' ) is null;
394 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', null );
395 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', null ) is null;
396 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]', null );
397 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]', null ) is null;
398
399 # too few args
400
401 --echo
402 --echo # not enough args
403 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
404 select json_remove();
405 --echo
406 --echo # not enough args
407 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
408 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]' );
409 --echo
410 --echo # not enough args
411 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
412 select json_remove( '$[1]' );
413
414 # malformed args
415 --echo
416 --echo # invalid json text
417 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
418 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ', '$[1]', '$[2]' );
419 --echo
420 --echo # invalid json path
421 --echo error ER_INVALID_JSON_PATH
422 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1', '$[2]' );
423 --echo
424 --echo # invalid json path
425 --echo error ER_INVALID_JSON_PATH
426 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]', '$[2' );
427 --echo
428 --echo # Vacuous path expression
429 --echo error ER_JSON_VACUOUS_PATH
430 select json_remove( '[ 1, 2, 3 ]', '$' );
431 --echo
432 --echo # Vacuous path expression
433 --echo error ER_JSON_VACUOUS_PATH
434 select json_remove( '[ 1, 2, 3 ]', '$', '$[2]' );
435 --echo
436 --echo # Vacuous path expression
437 --echo error ER_JSON_VACUOUS_PATH
438 select json_remove( '[ 1, 2, 3 ]', '$[1]', '$' );
439
440 # wildcard/ellipsis not allowed in paths
441 --echo error ER_INVALID_JSON_PATH_WILDCARD
442 select json_remove( '[ 1, 2, 3 ]', '$[*]' );
443 --echo error ER_INVALID_JSON_PATH_WILDCARD
444 select json_remove( '[ 1, 2, 3 ]', '$**[2]' );
445 --echo error ER_INVALID_JSON_PATH_WILDCARD
446 select json_remove( '[ 1, 2, 3 ]', '$[2]', '$[*]' );
447 --echo error ER_INVALID_JSON_PATH_WILDCARD
448 select json_remove( '[ 1, 2, 3 ]', '$[2]', '$**[2]' );
449
450 # simple matches
451
452 select json_remove( '[ 1, 2, 3 ]', '$[0]' );
453 select json_remove( '[ 1, 2, 3 ]', '$[1]' );
454 select json_remove( '[ 1, 2, 3 ]', '$[2]' );
455 select json_remove( '[ 1, 2, 3 ]', '$[3]' );
456 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$[1]' );
457
458 # one match nested inside another
459 --echo error ER_INVALID_JSON_PATH_WILDCARD
460 select json_remove( '[ { "a": { "a": true } } ]', '$**.a' );
461
462 # multiple paths
463 select json_remove( '[ { "a": true }, { "b": false }, { "c": null }, { "a": null } ]', '$[0].a', '$[2].c' );
464
465 # ellipsis with matches at different levels
466 --echo error ER_INVALID_JSON_PATH_WILDCARD
467 select json_remove( '[ { "a": true }, { "b": [ { "c": { "a": true } } ] }, { "c": null }, { "a": null } ]', '$**.a' );
468
469 # nonsense path
470 select json_remove( '{"id": 123, "name": "systemQA", "array": [1, 2, 3]}', '$[0]' );
471
472 # examples from wl7909 spec
473 # returns the document {"a": "foo", "b": [true]}
474 SELECT JSON_REMOVE
475 (
476 '{"a" : "foo", "b" : [true, {"c" : 123}]}',
477 '$.b[ 1 ]'
478 );
479
480 # returns {"a": "foo", "b": [true, {}]} due to normalization
481 SELECT JSON_REMOVE
482 (
483 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
484 '$.b[ 1 ].c'
485 );
486
487 # returns {"a": "foo", "b": [true, {}]}
488 SELECT JSON_REMOVE
489 (
490 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
491 '$.b[ 1 ].c'
492 );
493
494 # returns the original document because the path doesn't identify an element
495 SELECT JSON_REMOVE
496 (
497 '{ "a" : "foo", "b" : [ true, { "c" : 123, "d" : 456 } ] }',
498 '$.b[ 1 ].e'
499 );
500
501 --echo # ----------------------------------------------------------------------
502 --echo # Test of JSON_MERGE function.
503 --echo # ----------------------------------------------------------------------
504
505 --echo
506 --echo # not enough args
507 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
508 select json_merge();
509 --echo
510 --echo # not enough args
511 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
512 select json_merge( '[ 1, 2, 3 ]' );
513
514 # null args result in NULL value
515 select json_merge( null, null );
516 select json_merge( null, '[ 1, 2, 3 ]' );
517 select json_merge( '[ 1, 2, 3 ]', null );
518 select json_merge( null, '[ 1, 2, 3 ]', '[ 4, 5, 6 ]' );
519 select json_merge( '[ 1, 2, 3 ]', null, '[ 4, 5, 6 ]' );
520 select json_merge( '[ 1, 2, 3 ]', '[ 4, 5, 6 ]', null );
521
522 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
523 select json_merge( '[1, 2]', '[3, 4' );
524 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
525 select json_merge( '[1, 2', '[3, 4]' );
526
527 # good json_merge() expressions
528 select json_merge( '1', '2' );
529 select json_merge( '1', '[2, 3]' );
530 select json_merge( '[1, 2]', '3' );
531 select json_merge( '1', '{ "a": 2 }' );
532 select json_merge( '{ "a": 2 }', '1' );
533 select json_merge( '[1, 2]', '[3, 4]' );
534 select json_merge( '{ "a": 2 }', '{ "b": 3}' );
535 select json_merge( '[1, 2]', '{ "a": 2 }' );
536 select json_merge( '{ "a": 2 }', '[1, 2]' );
537
538 select json_merge( '{"a": 1, "b": 2 }', '{"b": 3, "d": 4 }' );
539 select json_merge( '{"a": 1, "b": 2 }', '{"b": [3, 4], "d": 4 }' );
540 select json_merge( '{"a": 1, "b": [2, 3] }', '{"b": 4, "d": 4 }' );
541 select json_merge( '{"a": 1, "b": 2 }', '{"b": {"e": 7, "f": 8}, "d": 4 }' );
542 select json_merge( '{"b": {"e": 7, "f": 8}, "d": 4 }', '{"a": 1, "b": 2 }' );
543 select json_merge( '{"a": 1, "b": [2, 9] }', '{"b": [10, 11], "d": 4 }' );
544 select json_merge( '{"a": 1, "b": [2, 9] }', '{"b": {"e": 7, "f": 8}, "d": 4 }' );
545 select json_merge( '{"b": {"e": 7, "f": 8}, "d": 4 }', '{"a": 1, "b": [2, 9] }' );
546 select json_merge( '{"b": {"e": 7, "f": 8}, "d": 4 }', '{ "a": 1, "b": {"e": 20, "g": 21 } }' );
547
548 select json_merge( '1', '2', '3' );
549 select json_merge( '[1, 2 ]', '3', '[4, 5]' );
550 select json_merge
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
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
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
574 (
575 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
576 '{ "b": "bar" }'
577 );
578
579 # returns {"a": {"b": 1, "c": 1}}
580 SELECT JSON_MERGE
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 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
592 select json_type('abc');
593
594 # Enable after fix MDEV-31554
595 --disable_cursor_protocol
596 #select i, json_type(j) from t1;
597 select json_type('{"a": 2}');
598 select json_type('[1,2]');
599 select json_type('"scalar string"');
600 select json_type('true');
601 select json_type('false');
602 select json_type('null');
603 select json_type('1');
604 select json_type('-0');
605 select json_type('-0.0');
606 --echo error ER_INVALID_TYPE_FOR_JSON
607 select json_type(-1);
608 --echo error ER_INVALID_TYPE_FOR_JSON
609 select json_type(CAST(1 AS UNSIGNED));
610 select json_type('32767');
611 --echo error ER_INVALID_TYPE_FOR_JSON
612 select json_type(PI());
613 select json_type('3.14');
614
615 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
616 select json_type(CAST(CAST('2015-01-15' AS DATE) as CHAR CHARACTER SET 'utf8'));
617
618 --echo # ----------------------------------------------------------------------
619 --echo # Test of json_compact(literal)
620 --echo # ----------------------------------------------------------------------
621 select json_type(json_compact(cast('2014-11-25 18:00' as datetime)));
622 select json_type(json_compact(cast('2014-11-25' as date)));
623 select json_type(json_compact(cast('18:00:59' as time)));
624 # select json_type(json_compact(cast('2014-11-25 18:00' as timestamp))); -- cast target type not supported
625
626 # select json_type(json_compact(cast('1999' as year))); -- cast target type not supported
627 select json_type(json_compact(127));
628 select json_type(json_compact(255));
629 select json_type(json_compact(32767));
630 select json_type(json_compact(65535));
631 select json_type(json_compact(8388607));
632 select json_type(json_compact(16777215));
633 select json_type(json_compact(2147483647));
634 select json_type(json_compact(4294967295));
635 select json_type(json_compact(9223372036854775807));
636 select json_type(json_compact(18446744073709551615));
637 select json_type(json_compact(true));
638 select json_type(json_compact(b'10101'));
639
640 select json_type(json_compact(cast(3.14 as decimal(5,2))));
641 select json_type(json_compact(3.14));
642 select json_type(json_compact(3.14E30));
643 # select json_type(json_compact(cast(3.14 as numeral(5,2)))); -- cast target type not supported
644
645 # select json_type(json_compact(cast(3.14 as double))); -- cast target type not supported
646 # select json_type(json_compact(cast(3.14 as float))); -- cast target type not supported
647
648 # select json_type(json_compact(cast(b'10101' as bit(10)))); -- cast target type not supported
649 # select json_type(json_compact(cast('10101abcde' as blob))); -- cast target type not supported
650 select json_type(json_compact(cast('10101abcde' as binary)));
651
652 # select json_type(json_compact(cast('a' as enum('a','b','c')))); -- cast target type not supported
653 # select json_type(json_compact(cast('a,c' as set('a','b','c')))); -- cast target type not supported
654
655 select json_type(json_compact(ST_GeomFromText('POINT(1 1)')));
656 select json_type(json_compact(ST_GeomFromText('LINESTRING(0 0,1 1,2 2)')));
657 select json_type(json_compact(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),
658 (5 5,7 5,7 7,5 7, 5 5))')));
659 select json_type(json_compact(null));
660 select json_type(json_compact(null)) is null; # check that it is an SQL NULL
661 select json_type(null) is null; # is an SQL NULL
662 --enable_cursor_protocol
663
664 #
665 # same, but now show the printable value:
666 #
667 select json_compact(cast('2014-11-25 18:00' as datetime));
668 select json_compact(cast('2014-11-25' as date));
669 select json_compact(cast('18:00:59' as time));
670 # select json_compact(cast('2014-11-25 18:00' as timestamp)); -- cast target type not supported
671
672 # select json_compact(cast('1999' as year)); -- cast target type not supported
673 select json_compact(127);
674 select json_compact(255);
675 select json_compact(32767);
676 select json_compact(65535);
677 select json_compact(8388607);
678 select json_compact(16777215);
679 select json_compact(2147483647);
680 select json_compact(4294967295);
681 select json_compact(9223372036854775807);
682 select json_compact(18446744073709551615);
683 select json_compact(true);
684 select json_compact(b'10101');
685
686 select json_compact(cast(3.14 as decimal(5,2)));
687 select json_compact(3.14);
688 select json_compact(3.14e0);
689 # select json_compact(cast(3.14 as numeral(5,2))); -- cast target type not supported
690
691 # select json_compact(cast(3.14 as double)); -- cast target type not supported
692 # select json_compact(cast(3.14 as float)); -- cast target type not supported
693
694 # select json_compact(cast(b'10101' as bit(10)); -- cast target type not supported
695 # select json_compact(cast('10101abcde' as blob)); -- cast target type not supported
696 select json_compact(cast('10101abcde' as binary));
697
698 # select json_compact(cast('a' as enum('a','b','c')); -- cast target type not supported
699 # select json_compact(cast('a,c' as set('a','b','c')); -- cast target type not supported
700
701 select json_compact(ST_GeomFromText('POINT(1 1)'));
702 select json_compact(ST_GeomFromText('LINESTRING(0 0,1 1,2 2)'));
703 select json_compact(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),
704 (5 5,7 5,7 7,5 7, 5 5))'));
705 select json_compact(null);
706 select json_compact(null) is null; # check that it is an SQL NULL
707
708 --echo # ----------------------------------------------------------------------
709 --echo # Test of JSON_KEYS function.
710 --echo # ----------------------------------------------------------------------
711 # should all give NULL:
712 select json_keys(NULL);
713 select json_keys(NULL, '$.b');
714 select json_keys(NULL, NULL);
715 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a');
716 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', NULL);
717
718 # non NULL results
719 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}');
720 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.b');
721
722 --echo error ER_INVALID_JSON_PATH_WILDCARD
723 select json_keys('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.*.b');
724
725 # Examples from the specification
726 --echo # returns [ "a", "b" ]
727 SELECT JSON_KEYS('{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }');
728
729 --echo # returns []
730 SELECT JSON_KEYS('{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
731 '$.b[1].c');
732
733 --echo # returns NULL
734 SELECT JSON_KEYS('{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
735 '$.a.b[2]');
736
737 --echo error ER_INVALID_JSON_PATH
738 SELECT JSON_KEYS('{"a":1}', '1010');
739 --echo error ER_INVALID_JSON_PATH
740 SELECT JSON_KEYS('{"a":1}', '1010') IS NULL;
741
742 # examples from the wl7909 spec
743 # returns [ "a", "b" ]
744 SELECT JSON_KEYS
745 (
746 '{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }'
747 );
748
749 # returns []
750 SELECT JSON_KEYS
751 (
752 '{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
753 '$.b[1].c'
754 );
755
756 # returns NULL
757 SELECT JSON_KEYS
758 (
759 '{ "a" : "foo", "b" : [ true, { "c" : {} } ] }',
760 '$.a.b[2]'
761 );
762
763 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
764 SELECT JSON_KEYS();
765 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
766 SELECT JSON_KEYS('{}', '$', '$');
767
768 --echo # ----------------------------------------------------------------------
769 --echo # CAST(<json> AS CHAR). See also 'json_conversions.test' for other
770 --echo # conversion tests.
771 --echo # ----------------------------------------------------------------------
772 select cast(json_keys('{"a": 1}') as char);
773 select cast(json_compact(1) as char);
774 select cast(json_keys(NULL) as char);
775 #select cast(j as char) from keys1;
776
777 --echo # ----------------------------------------------------------------------
778 --echo # Path matching with double-quotes
779 --echo # ----------------------------------------------------------------------
780
781 # matches
782 select json_extract( '{ "one potato" : 1 }', '$."one potato"' );
783 # matches
784 select json_extract( '{ "a.b" : 1 }', '$."a.b"' );
785
786 # doesn't match
787 select json_extract( '{ "\\"a\\"": 1}', '$."a"' );
788 # matches
789 select json_extract( '{ "\\"a\\"": 1}', '$."\\"a\\""' );
790 # matches
791 select json_extract( '{ "a": 1}', '$."a"' );
792 # matches
793 select json_extract( '{ "a": 1}', '$.a' );
794
795 # examples from functional spec section on Path Syntax
796
797 # [3, 2]
798 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[0]' );
799 # 2
800 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[0][1]' );
801 # [ { "c": "d" }, 1 ]
802 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[1]' );
803 # { "c": "d" }
804 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[1][0]' );
805 # "d"
806 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.a[1][0].c' );
807 # 7
808 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$."one potato"' );
809 # 6
810 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$.b.c' );
811 # 8
812 select json_extract( '{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }', '$."b.c"' );
813
814
815 --echo # ----------------------------------------------------------------------
816 --echo # Test of JSON_EXTRACT function.
817 --echo # ----------------------------------------------------------------------
818
819 # errors
820 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
821 select json_extract(NULL);
822 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
823 select json_extract('$.b');
824 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
825 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}');
826
827 # Confused argument order
828 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
829 select json_extract('$.a', '{"a": 1, "b": {"e": "foo", "b": 3}}');
830
831 # NULLs
832 select json_extract(NULL, '$.b');
833 select json_extract(NULL, NULL);
834
835 # non-NULLs
836 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a');
837 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.*');
838 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a', '$.b.e');
839 select json_extract('{"a": 1, "b": [1,2,3]}', '$.b[2]');
840 # one path is NULL
841 select json_extract('{"a": 1, "b": {"e": "foo", "b": 3}}', '$.a', NULL);
842
843 # Examples from the specification
844 --echo # returns a JSON value containing just the string "123"
845 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }',
846 '$.b[ 1 ].c');
847
848 --echo # returns a JSON value containing just the number 123
849 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
850 '$.b[ 1 ].c');
851
852 --echo # raises an error because the document is not valid
853 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
854 SELECT JSON_EXTRACT('{ "a" : [ }',
855 '$.b[ 1 ].c');
856
857 --echo # raises an error because the path is invalid
858 --echo error ER_INVALID_JSON_PATH
859 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
860 '$.b[ 1 ].');
861
862 --echo # returns a JSON value containing the number 123 (because of
863 --echo # auto-wrapping the scalar)
864 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
865 '$.b[ 1 ].c[ 0 ]');
866 --echo # returns a JSON value containing the object because of auto-wrapping
867 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : {"not array": 4} } ] }',
868 '$.b[ 1 ].c[ 0 ]');
869
870 --echo # returns null because the path, although valid, does not identify a value
871 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
872 '$.b[ 1 ].c[ 1 ]');
873
874 --echo # returns a JSON value containing the number 123 (due to normalization)
875 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
876 '$.b[ 1 ].c');
877
878 --echo # returns a JSON array [ "foo", true ]
879 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
880 '$.a', '$.b[0]');
881
882 --echo # returns a JSON array [ true ]
883 SELECT JSON_EXTRACT('{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
884 '$.d', '$.b[0]');
885
886 # some examples verifying ellipsis behavior
887
888 # should have same result
889 select json_extract( '[1]', '$[0][0]' );
890 select json_extract( '[1]', '$**[0]' );
891
892 # should have same result
893 select json_extract( '{ "a": 1 }', '$.a[0]' );
894 # Enable after fix MDEV-31554
895 --disable_cursor_protocol
896 select json_extract( '{ "a": 1 }', '$**[0]' );
897 --enable_cursor_protocol
898
899 # should have same result
900 select json_extract( '{ "a": 1 }', '$[0].a' );
901 select json_extract( '{ "a": 1 }', '$**.a' );
902
903 # should have same result
904 select json_extract( '{ "a": 1 }', '$[0].a[0]' );
905 # Enable after fix MDEV-31554
906 --disable_cursor_protocol
907 select json_extract( '{ "a": 1 }', '$**[0]' );
908 --enable_cursor_protocol
909
910 # should have the same result
911 select json_extract( '{ "a": 1 }', '$[0].a' );
912 select json_extract( '{ "a": 1 }', '$**.a' );
913 select json_extract( '{ "a": 1 }', '$[0][0].a' );
914 select json_extract( '{ "a": 1 }', '$[0][0][0].a' );
915
916 # should have the same result
917 SELECT JSON_EXTRACT('[1, [[{"x": [{"a":{"b":{"c":42}}}]}]]]', '$**.a.*');
918 SELECT JSON_EXTRACT('[1, [[{"x": [{"a":{"b":{"c":42}}}]}]]]',
919 '$[1][0][0].x[0].a.*');
920
921 # examples from the wl7909 spec
922 # returns a JSON value containing just the string "123"
923 SELECT JSON_EXTRACT
924 (
925 '{ "a" : "foo", "b" : [ true, { "c" : "123" } ] }',
926 '$.b[ 1 ].c'
927 );
928
929 # returns a JSON value containing just the number 123
930 SELECT JSON_EXTRACT
931 (
932 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
933 '$.b[ 1 ].c'
934 );
935
936 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
937 SELECT JSON_EXTRACT
938 (
939 '{ "a" : [ }',
940 '$.b[ 1 ].c'
941 );
942
943 --echo error ER_INVALID_JSON_PATH
944 SELECT JSON_EXTRACT
945 (
946 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
947 '$.b[ 1 ].'
948 );
949
950 # returns a JSON value containing the number 123 (because of auto-wrapping)
951 SELECT JSON_EXTRACT
952 (
953 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
954 '$.b[ 1 ].c[ 0 ]'
955 );
956
957 # returns null because the path, although valid, does not identify a value
958 SELECT JSON_EXTRACT
959 (
960 '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
961 '$.b[ 1 ].c[ 1 ]'
962 );
963
964 # returns a JSON value containing the number 123 (due to normalization)
965 SELECT JSON_EXTRACT
966 (
967 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
968 '$.b[ 1 ].c'
969 );
970
971 # returns a JSON array ["foo", true]
972 SELECT JSON_EXTRACT
973 (
974 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
975 '$.a', '$.b[0]'
976 );
977
978 # returns a the 'true' literal
979 SELECT JSON_EXTRACT
980 (
981 '{ "a" : "foo", "b" : [ true, { "c" : 123, "c" : 456 } ] }',
982 '$.d', '$.b[0]'
983 );
984
985 # should return NULL
986 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[*].b' ) jdoc;
987
988 # should return NULL
989 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[0].b' ) jdoc;
990
991 # should return 1
992 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[0].a' ) jdoc;
993
994 # should return [1, 2]
995 select json_extract( '[ { "a": 1 }, { "a": 2 } ]', '$[*].a' ) jdoc;
996
997 # should return [1]
998 select json_extract( '[ { "a": 1 }, { "b": 2 } ]', '$[*].a' ) jdoc;
999
1000 # should return [3, 4]
1001 select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[0].a' ) jdoc;
1002
1003 # should return [[3, 4]]
1004 select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[*].a' ) jdoc;
1005
1006 # should return [[3, 4]]
1007 select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[0].a', '$[1].a' ) jdoc;
1008
1009 --echo # ----------------------------------------------------------------------
1010 --echo # Test of JSON_ARRAY_APPEND function.
1011 --echo # ----------------------------------------------------------------------
1012
1013 # NULLs
1014 select json_array_append(NULL, '$.b', json_compact(1));
1015 select json_array_append('[1,2,3]', NULL, json_compact(1));
1016 # Enable after fix MDEV-31554
1017 --disable_cursor_protocol
1018 select json_array_append('[1,2,3]', '$', NULL);
1019 --enable_cursor_protocol
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(json_compact('1'), '$', 3);
1031 SELECT JSON_ARRAY_APPEND(json_compact('{"a": 3}'), '$', 3);
1032
1033 --echo error ER_INVALID_JSON_PATH_WILDCARD
1034 select json_array_append(json_compact('{"a": {"b": [3]}}'), '$**[0]', 6);
1035
1036 # Examples from the specification
1037
1038 --echo # Auto-wrapping, since because the paths identify scalars.
1039 --echo # should return {"a": "foo", "b": ["bar", 4], "c": ["wibble", "grape"]}
1040 # Enable after fix MDEV-31554
1041 --disable_cursor_protocol
1042 SELECT JSON_ARRAY_APPEND('{"a": "foo", "b": "bar", "c": "wibble"}',
1043 '$.b', json_compact(4),
1044 '$.c', json_compact('"grape"'));
1045 --enable_cursor_protocol
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', json_compact(4),
1051 '$.c', json_compact('"grape"'));
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 --echo error ER_INVALID_JSON_PATH_WILDCARD
1060 select json_array_append( '[[], [], []]', '$[*]', 3, '$[*]', 4);
1061 --echo 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 #Enable after fix MDEV-31554
1086 --disable_cursor_protocol
1087 select json_array_append('{"a":1}', '$[0]', 100);
1088 --enable_cursor_protocol
1089 select json_array_append('3', '$[0]', 100);
1090 select json_array_append('3', '$[0][0][0][0]', 100);
1091
1092 --echo # ----------------------------------------------------------------------
1093 --echo # Test of JSON_INSERT function.
1094 --echo # ----------------------------------------------------------------------
1095
1096 # NULLs
1097 select json_insert(NULL, '$.b', json_compact(1));
1098 select json_insert('[1,2,3]', NULL, json_compact(1));
1099 #Enable after fix MDEV-31554
1100 --disable_cursor_protocol
1101 select json_insert('[1,2,3]', '$[3]', NULL);
1102 --enable_cursor_protocol
1103
1104 # wrong # args
1105 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1106 select json_insert(NULL);
1107 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1108 select json_insert(NULL, NULL);
1109 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1110 select json_insert(NULL, NULL, NULL, NULL);
1111
1112 # positive test cases
1113
1114 select json_insert('[1,2,3]', '$[2]', 4);
1115 select json_insert('[1,2,3]', '$[3]', 4);
1116 select json_insert('[1,2,3]', '$[10]', 4);
1117
1118 select json_insert('{"c":4}', '$.c', 4);
1119 # Enable after fix MDEV-31554
1120 --disable_cursor_protocol
1121 select json_insert('{"c":4}', '$.a', 4);
1122 --enable_cursor_protocol
1123
1124 select json_insert('1', '$', 4);
1125 select json_insert('1', '$[0]', 4);
1126 select json_insert('1', '$[1]', 4);
1127 select json_insert('1', '$[10]', '4', '$[11]', 5);
1128
1129 select json_insert('[1,2,3]', '$[2][0]', 4);
1130 select json_insert('[1,2,3]', '$[2][2]', 4);
1131
1132 select json_insert('{"a": 3}', '$.a[0]', 4);
1133 select json_insert('{"a": 3}', '$.a[1]', 4, '$.a[2]', '5');
1134
1135 # wild card & auto-wrap (scalars)
1136 --echo error ER_INVALID_JSON_PATH_WILDCARD
1137 select json_insert(json_compact('{"a": [1], "b": 2}'), '$.*[1]', 6);
1138 --echo error ER_INVALID_JSON_PATH_WILDCARD
1139 select json_insert(json_compact('{"a": 1, "b": 2}'), '$.*[1]', 6);
1140
1141 --echo error ER_INVALID_JSON_PATH_WILDCARD
1142 select json_insert(json_compact('{"a": {"b": 3}}'), '$.a.*[1]', 6);
1143 --echo error ER_INVALID_JSON_PATH_WILDCARD
1144 select json_insert(json_compact('{"a": {"b": [3]}}'), '$.a.*[1]', 6);
1145 --echo error ER_INVALID_JSON_PATH_WILDCARD
1146 select json_insert(json_compact('{"a": {"b": 3}}'), '$**[1]', 6);
1147 --echo error ER_INVALID_JSON_PATH_WILDCARD
1148 select json_insert(json_compact('{"a": {"b": [3]}}'), '$**[1]', 6);
1149
1150 --echo error ER_INVALID_JSON_PATH_WILDCARD
1151 select json_insert(json_compact('[1]'), '$[*][1]', 6);
1152 --echo error ER_INVALID_JSON_PATH_WILDCARD
1153 select json_insert(json_compact('[1]'), '$**[1]', 6);
1154 --echo error ER_INVALID_JSON_PATH_WILDCARD
1155 select json_insert(json_compact('[1, [2], 3]'), '$[*][1]', 6);
1156 --echo error ER_INVALID_JSON_PATH_WILDCARD
1157 select json_insert(json_compact('[1, [2], 3]'), '$**[1]', 6);
1158
1159 --echo error ER_INVALID_JSON_PATH_WILDCARD
1160 select json_insert(json_compact('[[1]]'), '$[*][1]', 6);
1161 --echo error ER_INVALID_JSON_PATH_WILDCARD
1162 select json_insert(json_compact('[[1]]'), '$**[1]', 6);
1163
1164 # auto-wrap object
1165 select json_insert(json_compact('{"a": 3}'), '$[1]', 6);
1166
1167
1168 # Examples from the specification
1169
1170 # returns the original document because the path does exist
1171 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.a', true);
1172
1173 # inserts a number, returns '{ "a" : "foo", "b" : [ 1, 2, 3 ], "c" : 123 }
1174 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.c', 123);
1175
1176 # inserts a string, returns '{ "a" : "foo", "b" : [ 1, 2, 3 ], "c" : "123" }
1177 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.c', '123');
1178
1179 # returns '{ "a" : [ "foo", true ], "b" : [ 1, 2, 3 ] }'
1180 SELECT JSON_INSERT('{ "a" : "foo", "b" : [ 1, 2, 3 ] }', '$.a[1]', true);
1181
1182 # should return { "a" : "foo", "b": true }
1183 SELECT JSON_INSERT('{ "a" : "foo"}', '$.b', true, '$.b', false);
1184
1185 # examples from the wl7909 spec
1186 # returns the original document because the path does exist
1187 SELECT JSON_INSERT
1188 (
1189 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1190 '$.a',
1191 true
1192 );
1193
1194 # inserts a number, returns '{"a": "foo", "b": [1, 2, 3], "c": 123}
1195 SELECT JSON_INSERT
1196 (
1197 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1198 '$.c',
1199 123
1200 );
1201
1202 # inserts a string, returns '{"a": "foo", "b": [1, 2, 3], "c": "123"}
1203 SELECT JSON_INSERT
1204 (
1205 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1206 '$.c',
1207 '123'
1208 );
1209
1210 # returns '{"a": ["foo", true], "b": [1, 2, 3]}'
1211 SELECT JSON_INSERT
1212 (
1213 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1214 '$.a[1]',
1215 true
1216 );
1217
1218 # returns {"a": "foo", "b": true}
1219 SELECT JSON_INSERT
1220 (
1221 '{ "a" : "foo"}',
1222 '$.b', true,
1223 '$.b', false
1224 );
1225
1226 --echo # ----------------------------------------------------------------------
1227 --echo # Test of JSON_ARRAY_INSERT function.
1228 --echo # ----------------------------------------------------------------------
1229
1230 # NULLs
1231 select json_array_insert(NULL, '$.b[1]', 1);
1232 select json_array_insert('[1,2,3]', NULL, 1);
1233 #Enable after fix MDEV-31554
1234 --disable_cursor_protocol
1235 select json_array_insert('[1,2,3]', '$[3]', NULL);
1236 --enable_cursor_protocol
1237
1238 # wrong # args
1239 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1240 select json_array_insert(NULL);
1241 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1242 select json_array_insert(NULL, NULL);
1243 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1244 select json_array_insert(NULL, NULL, NULL, NULL);
1245
1246 # path does not indicate a cell position
1247 --echo error ER_INVALID_JSON_PATH_ARRAY_CELL
1248 select json_array_insert('true', '$', 1);
1249 --echo error ER_INVALID_JSON_PATH_ARRAY_CELL
1250 select json_array_insert('true', '$.a', 1);
1251 --echo error ER_INVALID_JSON_PATH_ARRAY_CELL
1252 select json_array_insert('true', '$.a[1].b', 1);
1253
1254 #Enable after fix MDEV-31554
1255 --disable_cursor_protocol
1256 # nop if there is no array at the path's parent
1257 select json_array_insert( 'true', '$[0]', false );
1258 select json_array_insert( 'true', '$[1]', false );
1259 select json_array_insert( '{ "a": true }', '$.a[0]', false );
1260 select json_array_insert( '{ "a": true }', '$.a[1]', false );
1261
1262 # positive tests
1263 select json_array_insert( '[]', '$[0]', false );
1264 select json_array_insert( '[]', '$[1]', false );
1265 select json_array_insert( '[true]', '$[0]', false );
1266 select json_array_insert( '[true]', '$[1]', false );
1267 select json_array_insert( '[true]', '$[2]', false );
1268 select json_array_insert( '{ "a": [] }', '$.a[0]', false );
1269 select json_array_insert( '{ "a": [] }', '$.a[1]', false );
1270 select json_array_insert( '{ "a": [true] }', '$.a[0]', false );
1271 select json_array_insert( '{ "a": [true] }', '$.a[1]', false );
1272 select json_array_insert( '{ "a": [true] }', '$.a[2]', false );
1273
1274 # insert into the middle of an array
1275 select json_array_insert( '[1, 2, 3, 4]', '$[0]', false );
1276 select json_array_insert( '[1, 2, 3, 4]', '$[1]', false );
1277 select json_array_insert( '[1, 2, 3, 4]', '$[2]', false );
1278 select json_array_insert( '[1, 2, 3, 4]', '$[3]', false );
1279 select json_array_insert( '[1, 2, 3, 4]', '$[4]', false );
1280 select json_array_insert( '[1, 2, 3, 4]', '$[5]', false );
1281 --enable_cursor_protocol
1282
1283 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[0]', false );
1284 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[1]', false );
1285 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[2]', false );
1286 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[3]', false );
1287 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[4]', false );
1288 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.a[5]', false );
1289
1290 # nop
1291 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.b[0]', false );
1292 select json_array_insert( '{ "a": [1, 2, 3, 4] }', '$.b[1]', false );
1293
1294 # no auto-wrapping
1295 select json_array_insert( '"a"', '$[0]', true );
1296 select json_array_insert( '[ "a" ]', '$[0][0]', true );
1297 select json_array_insert( '"a"', '$[1]', true );
1298
1299 --echo error ER_INVALID_JSON_PATH_WILDCARD
1300 select json_insert('[]', '$.a.*[1]', 6);
1301 --echo error ER_INVALID_JSON_PATH_WILDCARD
1302 select json_insert('[]', '$**[1]', 6);
1303 --echo error ER_INVALID_JSON_PATH_WILDCARD
1304 select json_insert('[]', '$[*][1]', 6);
1305
1306 # multiple paths,
1307 #Enable after fix MDEV-31554
1308 --disable_cursor_protocol
1309 select json_array_insert( '[ 1, 2, 3 ]', '$[1]', true, '$[1]', false );
1310 --enable_cursor_protocol
1311 select json_array_insert( '[ 1, 2, 3 ]', '$[1]',
1312 json_compact( '[ "a", "b", "c", "d" ]'), '$[1][2]', false );
1313
1314 # test an error while evaluating the document expression
1315 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
1316 SELECT JSON_ARRAY_INSERT(JSON_EXTRACT('[1', '$'), '$[0]', 1);
1317
1318 # error in reading new value
1319 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
1320 select json_array_insert( '[ 1, 2, 3 ]', '$[1]', json_extract( '[', '$' ) );
1321
1322 --echo # ----------------------------------------------------------------------
1323 --echo # Test of JSON_SET function.
1324 --echo # ----------------------------------------------------------------------
1325
1326 # NULLs
1327 select json_set(NULL, '$.b', json_compact(1));
1328 select json_set('[1,2,3]', NULL, json_compact(1));
1329 select json_set('[1,2,3]', '$[3]', NULL);
1330
1331 # wrong # args
1332 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1333 select json_set(NULL);
1334 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1335 select json_set(NULL, NULL);
1336 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1337 select json_set(NULL, NULL, NULL, NULL);
1338
1339 # Enable after fix MDEV-31554
1340 --disable_cursor_protocol
1341 # Detect errors in nested function calls.
1342 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
1343 SELECT JSON_SET('{}', '$.name', JSON_EXTRACT('', '$'));
1344 --enable_cursor_protocol
1345
1346 # positive test cases
1347
1348 select json_set('[1,2,3]', '$[2]', 4);
1349 select json_set('[1,2,3]', '$[3]', 4);
1350 select json_set('[1,2,3]', '$[10]', 4);
1351
1352 select json_set('{"c":4}', '$.c', 5);
1353 # Enable after fix MDEV-31554
1354 --disable_cursor_protocol
1355 select json_set('{"c":4}', '$.a', 5);
1356 --enable_cursor_protocol
1357
1358 select json_set('1', '$', 4);
1359 select json_set('1', '$[0]', 4);
1360 select json_set('1', '$[1]', 4);
1361 select json_set('1', '$[10]', '4', '$[11]', 5);
1362
1363 select json_set('[1,2,3]', '$[2][0]', 4);
1364 select json_set('[1,2,3]', '$[2][2]', 4);
1365
1366 select json_set('{"a": 3}', '$.a[0]', 4);
1367 select json_set('{"a": 3}', '$.a[1]', 4, '$.a[2]', '5');
1368
1369 # auto-wrap plus ellipsis with nested hits should give: {"a": [{"b": [3, 6]}, 6]}
1370 --echo error ER_INVALID_JSON_PATH_WILDCARD
1371 select json_set(json_compact('{"a": {"b": [3]}}'), '$**[1]', 6);
1372
1373 # Examples from the specification: Include when missing functions are
1374 # available.
1375
1376 # returns { "a" : {}, "b" : [ 1, 2, 3 ] }
1377 SELECT JSON_SET('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1378 '$.a',
1379 JSON_OBJECT());
1380
1381 # # returns { "a" : "foo", "b" : [ 1, 2, 3 ], "c" : [ true, false ] }
1382 # SELECT JSON_SET('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1383 # '$.c',
1384 # JSON_ARRAY( true, false ));
1385
1386 # # returns { "a" : "foo", "b" : [ 1, 2, 3 ], "c" : [ true, false ] }
1387 # SELECT JSON_SET('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1388 # '$.c',
1389 # JSON_ARRAY( json_compact( 'true'), json_compact( 'false') ));
1390
1391 # # returns [ 1, null, null, 2 ]
1392 # SELECT JSON_SET('1', '$[3]', 2);
1393
1394 # should return { "a": { "b": false, "c": true } }
1395 SELECT JSON_SET('{ "a" : "foo"}', '$.a',
1396 JSON_OBJECT( 'b', false ), '$.a.c', true);
1397
1398 # returns { "a" : {}, "b" : [ 1, 2, 3 ] }
1399 select json_set('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1400 '$.a',
1401 json_compact('{}'));
1402
1403 # Enable after fix MDEV-31554
1404 --disable_cursor_protocol
1405 # returns { "a" : "foo", "b" : [ 1, 2, 3 ], "c" : [ true, false ] }
1406 select json_set('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1407 '$.c',
1408 json_compact('[true, false]'));
1409 --enable_cursor_protocol
1410
1411 # returns [ 1, null, null, 2 ]
1412 select json_set('1', '$[3]', 2);
1413
1414 # should return { "a": { "b": false, "c": true } }
1415 select json_set('{ "a" : "foo"}', '$.a',
1416 json_compact('{"b": false}'), '$.a.c', true);
1417
1418 # examples from wl7909 spec
1419 # returns {"a": {}, "b": [1, 2, 3]}
1420 SELECT JSON_SET
1421 (
1422 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1423 '$.a',
1424 JSON_OBJECT()
1425 );
1426
1427 # Enable after fix MDEV-31554
1428 --disable_cursor_protocol
1429 # returns {"a": "foo", "b": [1, 2, 3], "c": [true, false]}
1430 SELECT JSON_SET
1431 (
1432 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1433 '$.c',
1434 JSON_ARRAY( true, false )
1435 );
1436
1437 # returns {"a": "foo", "b": [1, 2, 3], "c": [true, false]}
1438 SELECT JSON_SET
1439 (
1440 '{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1441 '$.c',
1442 JSON_ARRAY( json_compact( 'true'), json_compact( 'false') )
1443 );
1444 --enable_cursor_protocol
1445
1446 # returns [1, 2]
1447 SELECT JSON_SET
1448 (
1449 '1',
1450 '$[3]',
1451 2
1452 );
1453
1454 # returns {"a": {"b": false, "c": true}}
1455 SELECT JSON_SET
1456 (
1457 '{ "a" : "foo"}',
1458 '$.a', JSON_OBJECT( 'b', false ),
1459 '$.a.c', true
1460 );
1461
1462 --echo # ----------------------------------------------------------------------
1463 --echo # Test of JSON_REPLACE function.
1464 --echo # ----------------------------------------------------------------------
1465
1466 # NULLs
1467 select json_replace(NULL, '$.b', json_compact(1));
1468 select json_replace('[1,2,3]', NULL, json_compact(1));
1469 select json_replace('[1,2,3]', '$[2]', NULL);
1470
1471 # wrong # args
1472 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1473 select json_replace(NULL);
1474 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1475 select json_replace(NULL, NULL);
1476 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1477 select json_replace(NULL, NULL, NULL, NULL);
1478
1479 # positive test cases
1480
1481 select json_replace('[1,2,3]', '$[2]', 4);
1482 select json_replace('[1,2,3]', '$[3]', 4);
1483 select json_replace('[1,2,3]', '$[10]', 4);
1484
1485 select json_replace('{"c":4}', '$.c', 5);
1486 select json_replace('{"c":4}', '$.a', 5);
1487
1488 select json_replace('1', '$', 4);
1489 select json_replace('1', '$[0]', 4);
1490 select json_replace('1', '$[1]', 4);
1491 select json_replace('1', '$[10]', '4', '$[11]', 5);
1492
1493 select json_replace('[1,2,3]', '$[2][0]', 4);
1494 select json_replace('[1,2,3]', '$[2][2]', 4);
1495
1496 select json_replace('{"a": 3}', '$.a[0]', 4);
1497 select json_replace('{"a": 3}', '$.a[1]', 4, '$.a[2]', '5');
1498
1499
1500 # Examples from the specification
1501
1502 # returns the original document because the path doesn't exist
1503 SELECT JSON_REPLACE('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1504 '$.c',
1505 true);
1506
1507 # returns '{ "a" : true, "b" : [ 1, 2, 3 ] }'
1508 SELECT JSON_REPLACE('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1509 '$.a[0]',
1510 true);
1511
1512 # returns the original document because the path doesn't exist
1513 SELECT JSON_REPLACE('{ "a" : "foo", "b" : [ 1, 2, 3 ] }',
1514 '$.b[5]',
1515 true);
1516
1517 --echo # ----------------------------------------------------------------------
1518 --echo # Test of JSON_ARRAY function.
1519 --echo # ----------------------------------------------------------------------
1520
1521 # NULLs
1522 select json_array(NULL, '$.b', json_compact(1));
1523 select json_array('[1,2,3]', NULL, json_compact(1));
1524 select json_array('[1,2,3]', '$[3]', NULL);
1525
1526
1527 # Enable after fix MDEV-31554
1528 --disable_cursor_protocol
1529 # positive test cases
1530 select json_array();
1531 select json_array(3.14);
1532 select json_array('[1,2,3]');
1533 select json_array(json_compact('[1,2,3]'));
1534 select json_array(1,2,3);
1535 select json_array(b'0', b'1', b'10');
1536
1537 # returns the empty array: []
1538 SELECT JSON_ARRAY();
1539 --enable_cursor_protocol
1540
1541 --echo # ----------------------------------------------------------------------
1542 --echo # Test of JSON_OBJECT function.
1543 --echo # ----------------------------------------------------------------------
1544
1545 # odd number of args
1546 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1547 select json_object( 'a' );
1548 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1549 select json_object( 'a', 1, 'b' );
1550
1551 # null arg
1552 --echo error ER_JSON_DOCUMENT_NULL_KEY
1553 select json_object( null, 1 );
1554
1555 # Enable after fix MDEV-31554
1556 --disable_cursor_protocol
1557 # positive tests
1558 select json_object();
1559 select json_object( 'a', null );
1560 --enable_cursor_protocol
1561 select json_object( 'a', 1 );
1562 select json_object( 'a', 1, 'b', 'foo' );
1563 select json_object( 'a', 1, 'b', 'foo', 'c', json_compact( '{ "d": "wibble" }') );
1564 select json_object( 'a', true, 'b', false, 'c', json_compact( 'null') );
1565 select json_valid( json_object( '"a"', 1 ) );
1566
1567 # long key
1568 select json_object( REPEAT('a', 64 * 1024), 1 );
1569
1570 # non-string keyNames are cast to CHAR
1571 select json_object(json_array(), json_array());
1572 select json_object( cast(json_array() as char), json_array());
1573
1574 select json_object( 1, json_array());
1575 select json_object( cast(1 as char), json_array());
1576
1577 # Enable after fix MDEV-31554
1578 --disable_cursor_protocol
1579 # returns the empty object: {}
1580 SELECT JSON_OBJECT();
1581 --enable_cursor_protocol
1582
1583 --echo # ----------------------------------------------------------------------
1584 --echo # Test of JSON_SEARCH function.
1585 --echo # ----------------------------------------------------------------------
1586
1587 # wrong number of args
1588 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1589 select json_search();
1590 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1591 select json_search( '{ "a": true }' );
1592 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1593 select json_search( '{ "a": true }', 'one' );
1594
1595 # null args
1596 select json_search( null, 'one', 'foo' );
1597 select json_search( '{ "a": "foo" }', null, 'foo' );
1598 # FIXME. what should happen here?
1599 #select json_search( '{ "a": "foo" }', 'one', null );
1600 select json_search( '{ "a": "foo" }', 'one', 'foo', null, null );
1601 select json_search( '{ "a": "foo" }', 'one', 'foo', null, '$.a', null );
1602
1603 # bad values for the oneOrAll arg
1604 --echo error ER_JSON_BAD_ONE_OR_ALL_ARG
1605 select json_search( '{ "a": "foo" }', 'twof', 'foo' );
1606 --echo error ER_JSON_BAD_ONE_OR_ALL_ARG
1607 select json_search( '{ "a": "foo" }', 'two', 'foo' );
1608
1609 # bad escape arg
1610 --error ER_WRONG_ARGUMENTS
1611 select json_search( '{ "a": "foo" }', 'one', 'foo', 'ab' );
1612
1613 # bad path args
1614 --echo error ER_INVALID_JSON_PATH
1615 select json_search( '{ "a": "foo" }', 'one', 'foo', null, '$a' );
1616 --echo error ER_INVALID_JSON_PATH
1617 select json_search( '{ "a": "foo" }', 'all', 'foo', null, '$.a', '$b' );
1618
1619 --error ER_BAD_FIELD_ERROR
1620 select json_search(a, b, c);
1621
1622 # simple tests for search without path arguments
1623 select json_search( '{ "a": "foobar" }', 'one', 'foo%' );
1624 select json_search( '{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }', 'one', 'foo%' );
1625 select json_search( '{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }', 'all', 'foo%' );
1626 select json_search( '{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }', 'all', 'f__us' );
1627
1628 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'all', 'foo%', null, '$.a' );
1629 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'all', 'foo%', null, '$.a', '$.b' );
1630 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'one', 'foo%', null, '$.a', '$.b' );
1631
1632 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'ALL', 'foo%', null, '$.a' );
1633 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'aLl', 'foo%', null, '$.a', '$.b' );
1634 select json_search( '{ "a": [ "foolish", "folly", "foolhardy" ], "b" : "fool" }', 'ONE', 'foo%', null, '$.a', '$.b' );
1635
1636 # wildcards in the path expression
1637 select json_search
1638 (
1639 '[ { "a": { "b": { "c": "fool" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "food"} } ]',
1640 'all',
1641 'foo%',
1642 null,
1643 '$**.c'
1644 );
1645
1646 select json_search
1647 (
1648 '[ { "a": { "b": { "c": "showtime" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "shoe"} } ]',
1649 'all',
1650 'sho%',
1651 null,
1652 '$**.c'
1653 );
1654
1655 select json_search
1656 (
1657 '[ { "a": { "b": { "c": "showtime" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "shoe"} } ]',
1658 'all',
1659 'sho%e',
1660 null,
1661 '$**.c'
1662 );
1663
1664 select json_search
1665 (
1666 '[ { "a": { "b": { "c": "showtime" } } }, { "b": { "c": "shoulder" } }, { "c": { "c": "shoe"} } ]',
1667 'all',
1668 'sho%',
1669 null,
1670 '$[*].c'
1671 );
1672
1673 select json_search
1674 (
1675 '[ { "a": { "b": { "c": "showtime" } } }, [ { "b": { "c": "shout" } }, { "c": { "c": "shoe"} } ] ]',
1676 'all',
1677 'sho%',
1678 null,
1679 '$[1]**.c'
1680 );
1681
1682 # escape character
1683 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo%bar' );
1684 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar' );
1685 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|' );
1686 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]' );
1687 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]', '$[1]' );
1688 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]', '$[1]', '$[2]' );
1689
1690 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar', null );
1691 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar', null, '$[0]' );
1692 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo\%bar', null, '$[1]' );
1693 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[0]' );
1694 select json_search( '[ "footbar", "foo%bar" ]', 'all', 'foo|%bar', '|', '$[1]' );
1695
1696 # search is case-sensitive
1697 select json_search( '[ "abc", "ABC" ]', 'all', 'aBc' );
1698 select json_search( '[ "abc", "ABC" ]', 'all', 'abc' );
1699 select json_search( '[ "abc", "ABC" ]', 'all', 'ABC' );
1700
1701 # only matches strings, not numerics
1702 select json_search( '[ 10, "10", 1.0, "1.0" ]', 'all', '1%' );
1703
1704 # examples from the wl7909 spec
1705 # returns null because numeric values don't match string values
1706 SELECT JSON_SEARCH
1707 (
1708 '{ "a" : 123, "b" : [ 123, 456 ] }',
1709 'one',
1710 '123'
1711 );
1712
1713 # returns "$.b[2]"
1714 SELECT JSON_SEARCH
1715 (
1716 '{ "a" : "123", "b" : [ 123, "789", "123", "456", "123" ] }',
1717 'one',
1718 '123',
1719 null,
1720 '$.b'
1721 );
1722
1723 # could return either "$.a" or "$.b.key"
1724 SELECT JSON_SEARCH
1725 (
1726 '{ "a" : "123", "b" : { "key" : "123" } }',
1727 'one',
1728 '123'
1729 );
1730
1731 # returns "$.b.key"
1732 SELECT JSON_SEARCH
1733 (
1734 '{ "a" : "1243", "b" : { "key" : "1234" } }',
1735 'one',
1736 '123%'
1737 );
1738
1739 # returns "$.b.c"
1740 SELECT JSON_SEARCH
1741 (
1742 '{ "a" : "1243", "b" : { "key" : "1234", "c": "directorysub%directoryabc" } }',
1743 'one',
1744 'dir%torysub@%dir%',
1745 '@'
1746 );
1747
1748 # returns null because the path doesn't exist
1749 SELECT JSON_SEARCH
1750 (
1751 '{ "a" : "1243", "b" : { "key" : "1234" } }',
1752 'one',
1753 '123%',
1754 null,
1755 '$.c'
1756 );
1757
1758 # returns $."one potato"
1759 SELECT JSON_UNQUOTE
1760 (
1761 JSON_SEARCH
1762 (
1763 '{ "onepotato": "foot", "one potato": "food" , "one \\"potato": "fool" }',
1764 'all',
1765 'food'
1766 )
1767 );
1768
1769 select json_type(case (null is null) when 1 then
1770 json_compact('null') else
1771 json_compact('[1,2,3]') end);
1772
1773 # Enable after fix MDEV-31554
1774 --disable_cursor_protocol
1775 select json_type(case (null is not null) when 1 then
1776 json_compact('null') else
1777 json_compact('[1,2,3]') end);
1778 --enable_cursor_protocol
1779
1780 select json_type( if(null is null,
1781 json_compact('null'),
1782 json_compact('[1,2,3]')) );
1783
1784 # Enable after fix MDEV-31554
1785 --disable_cursor_protocol
1786 select json_type( if(null is not null,
1787 json_compact('null'),
1788 json_compact('[1,2,3]')));
1789 --enable_cursor_protocol
1790
1791 select cast(json_extract(json_compact(concat('[', json_compact('["A",2]'), ']')),
1792 '$[0][1]') as char) = 2;
1793
1794 --echo # ----------------------------------------------------------------------
1795 --echo # Test of aggregate function MAX, MIN.
1796 --echo # ----------------------------------------------------------------------
1797
1798 select max(json_compact('[1,2,3]'));
1799
1800 --echo # ----------------------------------------------------------------------
1801 --echo # Test of JSON_QUOTE, JSON_UNQUOTE
1802 --echo # ----------------------------------------------------------------------
1803
1804 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1805 select json_quote();
1806 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1807 select json_quote('abc', 'def');
1808 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1809 select json_quote(NULL, 'def');
1810 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1811 select json_quote('abc', NULL);
1812
1813 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1814 select json_unquote();
1815 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1816 select json_unquote('"abc"', '"def"');
1817 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1818 select json_unquote(NULL, 'def');
1819 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
1820 select json_unquote('"abc"', NULL);
1821
1822 select json_quote(NULL);
1823 select json_unquote(NULL);
1824
1825 select json_quote('abc');
1826 select json_quote(convert('abc' using ascii));
1827 select json_quote(convert('abc' using latin1));
1828 select json_quote(convert('abc' using utf8));
1829 select json_quote(convert('abc' using utf8mb4));
1830
1831 select json_unquote('abc'); # should do nothing
1832
1833 select json_unquote('"abc"');
1834 # Enable after fix MDEV-31554
1835 --disable_cursor_protocol
1836 select json_unquote(convert('"abc"' using ascii));
1837 select json_unquote(convert('"abc"' using latin1));
1838 --enable_cursor_protocol
1839 select json_unquote(convert('"abc"' using utf8));
1840 select json_unquote(convert('"abc"' using utf8mb4));
1841
1842 select json_quote('"');
1843 select json_unquote('"'); # should do nothing
1844
1845 --echo error ER_INCORRECT_TYPE
1846 select json_quote(123); # integer not allowed
1847 # Enable after fix MDEV-31554
1848 --disable_cursor_protocol
1849 --echo error ER_INCORRECT_TYPE
1850 select json_unquote(123); # integer not allowed
1851 --enable_cursor_protocol
1852
1853 select json_unquote('""'); # empty string
1854 select char_length(json_unquote('""')); # verify empty string
1855 select json_unquote('"" '); # unchanged: no final "
1856 select json_unquote(json_compact(json_quote('abc'))); # round trip
1857
1858 # No change in this JSON string: it is an object
1859 select json_compact('{"abc": "foo"}');
1860 select json_unquote(json_compact('{"abc": "foo"}'));
1861
1862 # This is a JSON string, so it is actually unquoted
1863 select json_extract(json_compact('{"abc": "foo"}'), '$.abc');
1864 select json_unquote(json_extract(json_compact('{"abc": "foo"}'), '$.abc'));
1865
1866 # Bug fix: thse should be the same
1867 select json_unquote('["a", "b", "c"]');
1868 select json_unquote(json_compact('["a", "b", "c"]'));
1869
1870 select charset(json_unquote('"abc"'));
1871
1872 select json_quote(convert(X'e68891' using utf8)); # chinese "I" (wo3)
1873 select json_quote(convert(X'e68891' using utf8mb4)); # chinese "I" (wo3)
1874
1875 select json_compact(json_quote(convert(X'e68891' using utf8)));
1876
1877 select json_unquote(convert(X'e68891' using utf8)); # chinese "I" (wo3)
1878
1879 select json_quote(json_quote(json_quote('abc'))); # deep quote
1880 select json_unquote(json_unquote(json_unquote( # long round trip of it
1881 json_quote(json_quote(json_quote('abc'))))));
1882
1883 # DATE/TIME will lose their quotes, too:
1884 select json_compact(cast('2015-01-15 23:24:25' as datetime));
1885 # Enable after fix MDEV-31554
1886 --disable_cursor_protocol
1887 select json_unquote(json_compact(cast('2015-01-15 23:24:25' as datetime)));
1888 --enable_cursor_protocol
1889
1890 # as well as opaque values:
1891 select json_compact(st_geomfromtext('point(1 1)'));
1892 # Enable after fix MDEV-31554
1893 --disable_cursor_protocol
1894 select json_unquote(json_compact(st_geomfromtext('point(1 1)')));
1895 --enable_cursor_protocol
1896
1897 # examples from the wl7909 spec
1898 # returns the SQL string literal abc
1899 SELECT JSON_UNQUOTE( '"abc"' );
1900
1901 # Enable after fix MDEV-31554
1902 --disable_cursor_protocol
1903 # returns the SQL string literal "abc
1904 SELECT JSON_UNQUOTE( '"abc' );
1905
1906 --echo error ER_INCORRECT_TYPE
1907 SELECT JSON_UNQUOTE( 123 );
1908 --enable_cursor_protocol
1909
1910 # returns the SQL string literal abc
1911 SELECT JSON_UNQUOTE
1912 ( CAST( json_compact( '"abc"') AS CHAR ) );
1913
1914 # returns 1
1915 SELECT JSON_UNQUOTE
1916 (
1917 CAST(
1918 JSON_EXTRACT( '{ "userName" : "fred" }', '$.userName' )
1919 AS CHAR
1920 )
1921 ) = 'fred';
1922
1923 # returns 0
1924 SELECT
1925 CAST(
1926 JSON_EXTRACT( '{ "userName" : "fred" }', '$.userName' )
1927 AS CHAR
1928 ) = 'fred';
1929
1930 # returns "abc"
1931 SELECT JSON_QUOTE( 'abc' );
1932
1933 --echo error ER_INCORRECT_TYPE
1934 SELECT JSON_QUOTE( 123 );
1935
1936 # returns the JSON document consisting of the string scalar "123"
1937 SELECT json_compact( JSON_QUOTE( '123' ));
1938
1939 --echo # ----------------------------------------------------------------------
1940 --echo # Test of JSON_CONTAINS
1941 --echo # ----------------------------------------------------------------------
1942
1943 --echo # should give NULL
1944 select json_contains(NULL, NULL);
1945 select json_contains(json_compact('{"a": 1, "b": 2}'), NULL);
1946 select json_contains(NULL, json_compact('null'));
1947 select json_contains(json_compact('[1]'), json_compact('[1]'), NULL);
1948
1949 --echo # should give 0:
1950 select json_contains(json_compact(3.14), json_compact(3));
1951
1952 --echo # should give 0: not at top level
1953 select json_contains(json_compact('{"a": {"b": 7}}'), json_compact('{"b": 7}'));
1954
1955 --echo # but path argument will fix it:
1956 select json_contains(json_compact('{"a": {"b": 7}}'), json_compact('{"b": 7}'), '$.a');
1957
1958 --echo # but arrays "introspect"
1959 select json_contains(json_compact('[1,[2.0, 3.0]]'), json_compact('[2.0]'));
1960 select json_contains(json_compact('[1, 2, [3, [4, 5]], 6, 7]'), json_compact('5'));
1961
1962 --echo # should give 0: just a key
1963 select json_contains(json_compact('{"a": 1, "b": 2}'), json_compact('"a"'));
1964
1965 --echo # should give 0: one candidate element doesn't match
1966 select json_contains(json_compact('[1]'), json_compact('[1,2]'));
1967
1968 --echo # should all give 1
1969 select json_contains(json_compact('null'), json_compact('null'));
1970 --echo # simple object subset
1971 select json_contains(json_compact('{"a": 1, "b": 2}'), json_compact( '{"a": 1}'));
1972 --echo # simple vector subset
1973 select json_contains(json_compact('[1, 2, 3]'), json_compact('[1, 3]'));
1974 --echo # auto-wrap, should give 1
1975 select json_contains(json_compact('[1, 2, 3]'), json_compact(3));
1976 --echo # ok even with nested cast off elements
1977 select json_contains(json_compact('{"person": {"id": 1, "country": "norway"}}'),
1978 json_compact('{"person": {"country": "norway"}}'));
1979 --echo # vector reordering and duplicates is ok
1980 select json_contains(json_compact('[1,3,5]'), json_compact('[5,3,1,5]'));
1981 --echo # ok even with more elts in candidate than in doc
1982 select json_contains(json_compact('[{"b": 4, "a":7}]'), json_compact('[{"a":7},{"b":4}]'));
1983 select json_contains(json_compact('[{"b": 4, "a":7}, 5]'), json_compact('[5, {"a":7, "b":4}]'));
1984 --echo # ok even with mixed number types that compare equal
1985 select json_contains(json_compact('[{"b": 4, "a":7}, 5.0]'), json_compact('[5, {"a":7.0E0, "b":4}]'));
1986
1987 # Bug discovered by Rick: used to give 1 (true).
1988 select json_contains( '{"customer": "cust3"}', '{"customer": "cust1"}' );
1989
1990 SELECT JSON_CONTAINS('[null,1,[2,3],true,false]', '[null,1,[3],false]');
1991 SELECT JSON_CONTAINS('[null,1,[2,3],true,false]', '[null,1,[4],false]');
1992 SELECT JSON_CONTAINS('[true,false]', '[[true]]');
1993 SELECT JSON_CONTAINS('[1,2]', '[[1]]');
1994 SELECT JSON_CONTAINS('[1,2]', '1', '$.abc');
1995 SELECT JSON_CONTAINS('{}', '{}');
1996 SELECT JSON_CONTAINS('{}', '{"a":1}');
1997 SELECT JSON_CONTAINS('{"a":1}', '{"a":1,"b":2}');
1998
1999 # examples from the wl7909 spec
2000 # returns 1
2001 SELECT JSON_CONTAINS
2002 (
2003 json_compact('[1, 4, 6]'),
2004 json_compact('[1, 6]')
2005 );
2006
2007 # returns 1; even with nested cast off elements
2008 SELECT JSON_CONTAINS
2009 (
2010 json_compact('{"person": {"id": 1, "country": "norway"}}'),
2011 json_compact('{"person": {"country": "norway"}}')
2012 );
2013
2014 # returns 1; reordering and duplicates are ok
2015 SELECT JSON_CONTAINS
2016 (
2017 json_compact('[1,3,5]'),
2018 json_compact('[5,3,1,5]')
2019 );
2020
2021 # return 0; no type conversion is performed
2022 SELECT JSON_CONTAINS
2023 (
2024 json_compact('[3.14]'),
2025 json_compact('[3]')
2026 );
2027
2028 # returns 1, due to auto-wrapping
2029 SELECT JSON_CONTAINS
2030 (
2031 json_compact('[1, 2, 3]'),
2032 json_compact(3)
2033 );
2034
2035 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
2036 SELECT JSON_CONTAINS();
2037 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
2038 SELECT JSON_CONTAINS('[1]');
2039 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
2040 SELECT JSON_CONTAINS('[1]', '[1]', '$', '$[0]');
2041
2042 --echo # ----------------------------------------------------------------------
2043 --echo # Wrong collation from JSON_QUOTE caused problems: Set it in
2044 --echo # Item_func_json_quote::fix_length_and_dec. Bug found by Knut.
2045 --echo # Similar issue for JSON_UNQUOTE and JSON_TYPE.
2046 --echo # ----------------------------------------------------------------------
2047 select json_object("a", ifnull(json_quote('test'), json_compact('null')));
2048 select json_compact(concat('[', json_quote('ab'), ']'));
2049 select json_compact(concat('[', json_unquote('"12"'), ']'));
2050 # Enable after fix MDEV-31554
2051 --disable_cursor_protocol
2052 select json_compact(concat('["', json_type( json_compact(1)), '"]'));
2053 --enable_cursor_protocol
2054
2055 --echo #
2056 --echo # Bug#20912438: ITEM_TYPE_HOLDER::DISPLAY_LENGTH(ITEM*): ASSERTION `0' FAILED
2057 --echo #
2058 (SELECT JSON_KEYS('{ "key80": "2015-04-20 11:53:55"}')) UNION ALL
2059 (SELECT JSON_KEYS('{ "key80": "2015-04-20 11:53:55" }') LIMIT 0);
2060 SELECT json_compact(1) UNION ALL SELECT json_compact(1);
2061
2062 # Exercise NULL handling and error handling in Item_copy_json::copy().
2063 SELECT COUNT(*), json_compact(NULL);
2064 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
2065 SELECT COUNT(*), JSON_EXTRACT('not valid json!', '$');
2066
2067 --echo # ----------------------------------------------------------------------
2068 --echo # Bug report from John E.
2069 --echo # Crash in Item_copy_json::~Item_copy_json
2070 --echo # ----------------------------------------------------------------------
2071 EXPLAIN SELECT COUNT(*), JSON_KEYS('{}');
2072
2073 select json_search( '{ "a": "foo" }', 'one', 'foo', 'a' );
2074 select json_search( '{ "a": "foo" }', 'one', 'foo', null );
2075 select json_search( '{ "a": "foo" }', 'one', 'foo', convert(x'f8' using latin1) );
2076
2077 # bad escape arg
2078 --error ER_WRONG_ARGUMENTS
2079 select json_search( '{ "a": "foo" }', 'one', 'foo', 'ab' );
2080
2081 --echo # ----------------------------------------------------------------------
2082 --echo # Wrong results when Json_path_cache primed is accessed
2083 --echo # during the prepare-phase.
2084 --echo #----------------------------------------------------------------------
2085
2086 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', null ) is null;
2087
2088 prepare stmt1 from 'select json_remove( ''[ 1, { "a": true, "b": false, "c": null }, 5 ]'', null ) is null';
2089 execute stmt1;
2090
2091 --echo error ER_INVALID_JSON_PATH
2092 select json_remove( '[ 1, { "a": true, "b": false, "c": null }, 5 ]', '$.' ) is null;
2093
2094 --echo error ER_INVALID_JSON_PATH
2095 prepare stmt1 from 'select json_remove( ''[ 1, { "a": true, "b": false, "c": null }, 5 ]'', ''$.'' ) is null';
2096
2097 --echo # ----------------------------------------------------------------------
2098 --echo # Bug#20987329 VALUE OF PREPARED STATEMENT PLACEHOLDER FOR PARAMETER
2099 --echo # IN JSON_EXTRACT IS STICKY
2100 --echo #----------------------------------------------------------------------
2101
2102 # should get different results with different parameter values
2103
2104 # json_extract()
2105
2106 # json_contains()
2107
2108 prepare json_stmt1 FROM 'select json_contains( ''{ "keyA": [1, 2, 3], "keyB": [4, 5, 6] }'', ''[2]'', ? )';
2109 set @mypath = '$.keyA';
2110 execute json_stmt1 USING @mypath;
2111 set @mypath = '$.keyB';
2112 execute json_stmt1 USING @mypath;
2113
2114 # json_contains_path()
2115
2116 prepare json_stmt2 FROM 'select json_contains_path( ''{ "keyA": [1, 2, 3] }'', ''all'', ? )';
2117 set @mypath = '$.keyA';
2118 execute json_stmt2 USING @mypath;
2119 set @mypath = '$.keyB';
2120 execute json_stmt2 USING @mypath;
2121
2122 # json_length()
2123
2124 prepare json_stmt3 FROM 'select json_length( ''{ "keyA": [1, 2, 3], "keyB": [1, 2, 3, 4] }'', ? )';
2125 set @mypath = '$.keyA';
2126 execute json_stmt3 USING @mypath;
2127 set @mypath = '$.keyB';
2128 execute json_stmt3 USING @mypath;
2129
2130 # json_keys()
2131
2132 prepare json_stmt4 FROM 'select json_keys( ''[ { "keyA": true }, { "keyB": false } ]'', ? )';
2133 set @mypath = '$[0]';
2134 execute json_stmt4 USING @mypath;
2135 set @mypath = '$[1]';
2136 execute json_stmt4 USING @mypath;
2137
2138 # json_array_append()
2139
2140 prepare json_stmt5 FROM 'select json_array_append( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2141 set @mypath = '$.keyA';
2142 execute json_stmt5 USING @mypath;
2143 set @mypath = '$.keyB';
2144 execute json_stmt5 USING @mypath;
2145
2146 # json_insert()
2147
2148 prepare json_stmt6 FROM 'select json_insert( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2149 set @mypath = '$.keyA[2]';
2150 execute json_stmt6 USING @mypath;
2151 set @mypath = '$.keyB[2]';
2152 execute json_stmt6 USING @mypath;
2153
2154 # json_set()
2155
2156 prepare json_stmt7 FROM 'select json_set( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2157 set @mypath = '$.keyA[2]';
2158 execute json_stmt7 USING @mypath;
2159 set @mypath = '$.keyB[2]';
2160 execute json_stmt7 USING @mypath;
2161
2162 # json_replace()
2163
2164 prepare json_stmt8 FROM 'select json_replace( ''{ "keyA": [1, 2], "keyB": [3, 4] }'', ?, 5 )';
2165 set @mypath = '$.keyA[1]';
2166 execute json_stmt8 USING @mypath;
2167 set @mypath = '$.keyB[1]';
2168 execute json_stmt8 USING @mypath;
2169
2170 # json_search()
2171
2172 prepare json_stmt9 FROM 'select json_search( ''{ "keyA": [ "foot" ], "keyB": [ "food" ] }'', ''all'', ''foo%'', null, ? )';
2173 set @mypath = '$.keyA';
2174 execute json_stmt9 USING @mypath;
2175 set @mypath = '$.keyB';
2176 execute json_stmt9 USING @mypath;
2177
2178 # json_remove()
2179
2180 prepare json_stmt10 FROM 'select json_remove( ''{ "keyA": [ "foot" ], "keyB": [ "food" ] }'', ? )';
2181 set @mypath = '$.keyA';
2182 execute json_stmt10 USING @mypath;
2183 set @mypath = '$.keyB';
2184 execute json_stmt10 USING @mypath;
2185
2186 # similar caching problem for the oneOrAll args
2187
2188 prepare json_stmt11 FROM 'select json_contains_path( ''{ "keyA": true }'', ?, ''$.keyA'', ''$.keyB'' )';
2189 set @mypath = 'one';
2190 execute json_stmt11 USING @mypath;
2191 set @mypath = 'all';
2192 execute json_stmt11 USING @mypath;
2193
2194 prepare json_stmt12 FROM 'select json_search( ''{ "keyA": [ "foot" ], "keyB": [ "food" ] }'', ?, ''foo%'' )';
2195 set @mypath = 'one';
2196 execute json_stmt12 USING @mypath;
2197 set @mypath = 'all';
2198 execute json_stmt12 USING @mypath;
2199
2200 --echo #
2201 --echo # Bug#21128632 JSON_QUOTE(JSON_TYPE(...)) GIVES ERROR 3139 ER_INVALID_JSON_CHARSET
2202 --echo #
2203
2204 select json_quote( json_type( json_object() ) );
2205 select json_quote( json_type( json_compact('{}') ) );
2206
2207 --echo #
2208 --echo # Bug#21148020 OUTPUT FROM JSON_TYPE() IS TRUNCATED
2209 --echo # WHEN EXECUTED IN A VIEW OR JOIN
2210 --echo #
2211
2212 # Enable after fix MDEV-31554
2213 --disable_cursor_protocol
2214 SELECT JSON_TYPE(JSON_OBJECT());
2215 --enable_cursor_protocol
2216 CREATE VIEW v1 AS SELECT JSON_TYPE(JSON_OBJECT());
2217 SELECT * FROM v1;
2218 drop view v1;
2219
2220 # SELECT JSON_TYPE(json_compact(CAST('2015-05-25 11:23:55' AS DATETIME)));
2221 # CREATE VIEW v2 AS SELECT JSON_TYPE(json_compact(CAST('2015-05-25 11:23:55' AS
2222 # DATETIME)));
2223 # SELECT * FROM v2;
2224 # drop view v2;
2225
2226 --echo #
2227 --echo # Bug#21198333 SIG 6 IN ITEM_CACHE_JSON::CACHE_VALUE AT SQL/ITEM.CC:9470
2228 --echo #
2229 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
2230 SELECT MIN(JSON_EXTRACT('not json', '$'));
2231
2232 --echo #
2233 --echo # Bug#21200657 DATA FROM DERIVED TABLE BASED
2234 --echo # ON JSN_QUOTE()/JSN_UNQUOTE() CALL IS TRUNCATED
2235 --echo #
2236 SELECT JSON_QUOTE('This is a string that should not be truncated') AS field1;
2237 SELECT JSON_UNQUOTE(JSON_QUOTE('This is a string that should not be truncated')) AS field1;
2238
2239 SELECT * FROM (SELECT JSON_QUOTE('This is a string that should not be truncated') AS field1) AS DERIVED_TABLE;
2240 SELECT * FROM (SELECT JSON_UNQUOTE("This is a string that should not be truncated") AS field1) AS DERIVED_TABLE;
2241 SELECT * FROM (SELECT JSON_UNQUOTE(JSON_QUOTE('This is a string that should not be truncated')) AS field1) AS DERIVED_TABLE;
2242
2243 --echo #
2244 --echo # Bug#21296173 JSON_OBJECT('KEY', BOOLEAN_LITERAL) USES VALUES 0, 1
2245 --echo # FOR BOOL WHEN USED VIA VIEW
2246 --echo #
2247
2248 # Enable after fix MDEV-31554
2249 --disable_cursor_protocol
2250 SELECT JSON_OBJECT('key1', false, 'key2', true);
2251 SELECT JSON_ARRAY('key1', false, 'key2', true);
2252 --enable_cursor_protocol
2253 CREATE VIEW v1 AS SELECT JSON_OBJECT('key1', false, 'key2', true);
2254 SELECT * FROM v1;
2255 CREATE VIEW v2 AS SELECT JSON_ARRAY('key1', false, 'key2', true);
2256 SELECT * FROM v2;
2257
2258 drop view v1;
2259 drop view v2;
2260
2261 --echo #
2262 --echo # Bug#21293089 JSON_CONTAINS() RETURNS WRONG RESULT WITH EMPTY JSON ARRAY
2263 --echo #
2264 SELECT JSON_CONTAINS('[]', '{"a" : 1}');
2265 SELECT JSON_CONTAINS('[]', '[1, 2, 3, 4, 5]');
2266 SELECT JSON_CONTAINS('[]', '[1, 2, 3, 4, {"a" : 1}]');
2267 SELECT JSON_CONTAINS('[]', '{"a" : [1, 2, 3, 4, 5]}');
2268 SELECT JSON_CONTAINS('[]', '[]');
2269
2270 --echo #
2271 --echo # Bug#21377136 STACK OVERFLOW IN RAPIDJSON::GENERICREADER
2272 --echo #
2273 --echo error ER_JSON_DOCUMENT_TOO_DEEP
2274 SELECT JSON_VALID(REPEAT('[', 100000));
2275 --echo error ER_JSON_DOCUMENT_TOO_DEEP
2276 SELECT JSON_VALID(REPEAT('{"a":', 100000));
2277 --echo error ER_JSON_DOCUMENT_TOO_DEEP
2278 SELECT JSON_VALID(REPEAT('{"a":[', 100000));
2279 --echo error ER_JSON_DOCUMENT_TOO_DEEP
2280 SELECT JSON_VALID(REPEAT('[{"a":', 100000));
2281
2282 --echo #
2283 --echo # Bug#21381806 JSON: ASSERTION FAILED: ARG->NULL_VALUE
2284 --echo #
2285 SELECT JSON_SET(CASE WHEN 1 THEN NULL ELSE NULL END, '{}', '{}');
2286 SELECT JSON_VALID(CASE WHEN 1 THEN NULL ELSE NULL END);
2287 SELECT JSON_ARRAY(CASE WHEN 1 THEN NULL ELSE NULL END);
2288
2289 --echo #
2290 --echo # Bug#21384048 ASSERTION FAILED: N >= 0 && N <= 308
2291 --echo # IN RAPIDJSON::INTERNAL::FASTPATH
2292 --echo #
2293 SELECT JSON_EXTRACT('-1E-36181012216111515851075235238', '$');
2294 SELECT JSON_EXTRACT('1E-36181012216111515851075235238', '$');
2295 SELECT JSON_EXTRACT('1E-325', '$');
2296 SELECT JSON_EXTRACT('1E-324', '$');
2297 SELECT JSON_EXTRACT('1E-323', '$');
2298 SELECT JSON_EXTRACT('1E+308', '$');
2299 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
2300 SELECT JSON_EXTRACT('1E+309', '$');
2301 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
2302 SELECT JSON_EXTRACT('1E+36181012216111515851075235238', '$');
2303 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
2304 SELECT JSON_EXTRACT('-1E+36181012216111515851075235238', '$');
2305
2306 --echo #
2307 --echo # Bug#21383284: ASSERTION IN SELECT_LEX::SETUP_CONDS
2308 --echo #
2309 --error ER_WRONG_ARGUMENTS
2310 SELECT 1 FROM dual WHERE JSON_SEARCH('{}', 'one', 'foo', 'too-long-escape');
2311 --echo error ER_INVALID_JSON_TEXT_IN_PARAM
2312 SELECT 1 FROM dual WHERE JSON_SEARCH('{}', 'one', 'foo', JSON_EXTRACT('', '$'));
2313
2314 --echo #
2315 --echo # Bug#21442624 INCORRECT RESULT FROM JSON_SET WITH AUTO-WRAPPING
2316 --echo #
2317
2318 SELECT JSON_SET('1', '$', 100);
2319 SELECT JSON_SET('1', '$[0]', 100);
2320 SELECT JSON_SET('1', '$[0][0]', 100);
2321 SELECT JSON_SET('1', '$[0][0][0]', 100);
2322
2323 SELECT JSON_SET('[]', '$', 100);
2324 SELECT JSON_SET('[]', '$[0]', 100);
2325 SELECT JSON_SET('[]', '$[0][0]', 100);
2326 SELECT JSON_SET('[]', '$[0][0][0]', 100);
2327
2328 SELECT JSON_SET('[1]', '$', 100);
2329 SELECT JSON_SET('[1]', '$[0]', 100);
2330 SELECT JSON_SET('[1]', '$[0][0]', 100);
2331 SELECT JSON_SET('[1]', '$[0][0][0]', 100);
2332
2333 SELECT JSON_SET('[[1]]', '$', 100);
2334 SELECT JSON_SET('[[1]]', '$[0]', 100);
2335 SELECT JSON_SET('[[1]]', '$[0][0]', 100);
2336 SELECT JSON_SET('[[1]]', '$[0][0][0]', 100);
2337
2338 SELECT JSON_SET('[[[1]]]', '$', 100);
2339 SELECT JSON_SET('[[[1]]]', '$[0]', 100);
2340 SELECT JSON_SET('[[[1]]]', '$[0][0]', 100);
2341 SELECT JSON_SET('[[[1]]]', '$[0][0][0]', 100);
2342
2343 SELECT JSON_REPLACE('1', '$', 100);
2344 SELECT JSON_REPLACE('1', '$[0]', 100);
2345 SELECT JSON_REPLACE('1', '$[0][0]', 100);
2346 SELECT JSON_REPLACE('1', '$[0][0][0]', 100);
2347
2348 SELECT JSON_REPLACE('[]', '$', 100);
2349 SELECT JSON_REPLACE('[]', '$[0]', 100);
2350 SELECT JSON_REPLACE('[]', '$[0][0]', 100);
2351 SELECT JSON_REPLACE('[]', '$[0][0][0]', 100);
2352
2353 SELECT JSON_REPLACE('[1]', '$', 100);
2354 SELECT JSON_REPLACE('[1]', '$[0]', 100);
2355 SELECT JSON_REPLACE('[1]', '$[0][0]', 100);
2356 SELECT JSON_REPLACE('[1]', '$[0][0][0]', 100);
2357
2358 SELECT JSON_REPLACE('[[1]]', '$', 100);
2359 SELECT JSON_REPLACE('[[1]]', '$[0]', 100);
2360 SELECT JSON_REPLACE('[[1]]', '$[0][0]', 100);
2361 SELECT JSON_REPLACE('[[1]]', '$[0][0][0]', 100);
2362
2363 SELECT JSON_REPLACE('[[[1]]]', '$', 100);
2364 SELECT JSON_REPLACE('[[[1]]]', '$[0]', 100);
2365 SELECT JSON_REPLACE('[[[1]]]', '$[0][0]', 100);
2366 SELECT JSON_REPLACE('[[[1]]]', '$[0][0][0]', 100);
2367
2368
2369 --echo #
2370 --echo # Bug#21828321: JSON FUNCS CALL DBUG_ABORT OR EXIT() ON WINDOWS!
2371 --echo #
2372 # LEAST and GREATEST treat JSON arguments as strings for now. They used to hit
2373 # an assertion if used in a JSON context and all arguments were JSON values, or
2374 # a mix of NULLs and JSON values.
2375 # Enable after fix MDEV-31554
2376 --disable_cursor_protocol
2377 SELECT JSON_ARRAY(LEAST(NULL, NULL), GREATEST(NULL, NULL), LEAST(j1, NULL),
2378 GREATEST(NULL, j2), LEAST(j1, j2), GREATEST(j1, j2)) AS j
2379 FROM (SELECT json_compact('1') AS j1, json_compact('2') AS j2) t;
2380 --enable_cursor_protocol
2381
2382 --echo #
2383 --echo # Start of 11.4 tests
2384 --echo #
2385
2386 --echo #
2387 --echo # MDEV-25829 Change default collation to utf8mb4_1400_ai_ci
2388 --echo #
2389
2390 SELECT
2391 JSON_UNQUOTE("String"),
2392 COERCIBILITY(JSON_UNQUOTE("String")),
2393 COLLATION(JSON_UNQUOTE("String"));
2394
2395 --echo #
2396 --echo # End of 11.4 tests
2397 --echo #