Debian Codegraph @

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

raw
1 #
2 # Setup test.
3 #
4 CREATE TABLE t1 (a int, k int, b VARCHAR(10)) CHARSET utf8mb4;
5 INSERT INTO t1 VALUES
6 (1, 1, "alfa"),
7 (1, 2, null),
8 (2, 3, "doi"),
9 (1, 4, "unu"),
10 (3, 5, "trei"),
11 (4, 6, null),
12 (4, 7, null),
13 (1, 8, "one");
14 #
15 # Test JSON_ARRAYAGG.
16 #
17 FLUSH STATUS;
18 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
19 a JSON_ARRAYAGG(b)
20 1 ["alfa", null, "unu", "one"]
21 2 ["doi"]
22 3 ["trei"]
23 4 [null, null]
24 SHOW SESSION STATUS LIKE 'Handler_update%';
25 Variable_name Value
26 Handler_update 0
27 SELECT SQL_BIG_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
28 a JSON_ARRAYAGG(b)
29 1 ["alfa", null, "unu", "one"]
30 2 ["doi"]
31 3 ["trei"]
32 4 [null, null]
33 SELECT SQL_SMALL_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
34 a JSON_ARRAYAGG(b)
35 1 ["alfa", null, "unu", "one"]
36 2 ["doi"]
37 3 ["trei"]
38 4 [null, null]
39 SHOW SESSION STATUS LIKE 'Handler_update%';
40 Variable_name Value
41 Handler_update 0
42 SELECT SQL_BUFFER_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
43 a JSON_ARRAYAGG(b)
44 1 ["alfa", null, "unu", "one"]
45 2 ["doi"]
46 3 ["trei"]
47 4 [null, null]
48 SHOW SESSION STATUS LIKE 'Handler_update%';
49 Variable_name Value
50 Handler_update 0
51 SELECT JSON_ARRAYAGG(b) FROM t1;
52 JSON_ARRAYAGG(b)
53 ["alfa", null, "doi", "unu", "trei", null, null, "one"]
54 SELECT SQL_BIG_RESULT JSON_ARRAYAGG(b) FROM t1;
55 JSON_ARRAYAGG(b)
56 ["alfa", null, "doi", "unu", "trei", null, null, "one"]
57 SELECT SQL_SMALL_RESULT JSON_ARRAYAGG(b) FROM t1;
58 JSON_ARRAYAGG(b)
59 ["alfa", null, "doi", "unu", "trei", null, null, "one"]
60 SELECT SQL_BUFFER_RESULT JSON_ARRAYAGG(b) FROM t1;
61 JSON_ARRAYAGG(b)
62 ["alfa", null, "doi", "unu", "trei", null, null, "one"]
63 PREPARE p1 FROM "SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a";
64 EXECUTE p1;
65 a JSON_ARRAYAGG(b)
66 1 ["alfa", null, "unu", "one"]
67 2 ["doi"]
68 3 ["trei"]
69 4 [null, null]
70 EXECUTE p1;
71 a JSON_ARRAYAGG(b)
72 1 ["alfa", null, "unu", "one"]
73 2 ["doi"]
74 3 ["trei"]
75 4 [null, null]
76 deallocate prepare p1;
77 PREPARE p3 FROM
78 "SELECT SQL_BUFFER_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a";
79 EXECUTE p3;
80 a JSON_ARRAYAGG(b)
81 1 ["alfa", null, "unu", "one"]
82 2 ["doi"]
83 3 ["trei"]
84 4 [null, null]
85 EXECUTE p3;
86 a JSON_ARRAYAGG(b)
87 1 ["alfa", null, "unu", "one"]
88 2 ["doi"]
89 3 ["trei"]
90 4 [null, null]
91 deallocate prepare p3;
92 PREPARE p4 FROM "SELECT JSON_ARRAYAGG(b) FROM t1";
93 EXECUTE p4;
94 JSON_ARRAYAGG(b)
95 ["alfa", null, "doi", "unu", "trei", null, null, "one"]
96 EXECUTE p4;
97 JSON_ARRAYAGG(b)
98 ["alfa", null, "doi", "unu", "trei", null, null, "one"]
99 deallocate prepare p4;
100 SELECT JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1;
101 JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
102 ["alfa", null, "doi", "unu", "trei", null, null, "one", true, false]
103 PREPARE p1 FROM
104 "SELECT a, JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1 GROUP BY a";
105 EXECUTE p1;
106 a JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
107 1 ["alfa", null, "unu", "one", true, false]
108 2 ["doi", true, false]
109 3 ["trei", true, false]
110 4 [null, null, true, false]
111 EXECUTE p1;
112 a JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
113 1 ["alfa", null, "unu", "one", true, false]
114 2 ["doi", true, false]
115 3 ["trei", true, false]
116 4 [null, null, true, false]
117 deallocate prepare p1;
118 PREPARE p4 FROM
119 "SELECT JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1";
120 EXECUTE p4;
121 JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
122 ["alfa", null, "doi", "unu", "trei", null, null, "one", true, false]
123 EXECUTE p4;
124 JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
125 ["alfa", null, "doi", "unu", "trei", null, null, "one", true, false]
126 deallocate prepare p4;
127 ANALYZE TABLE t1;
128 Table Op Msg_type Msg_text
129 test.t1 analyze status OK
130 EXPLAIN SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
131 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
132 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 100.00 Using filesort
133 Warnings:
134 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1` group by `test`.`t1`.`a`
135 EXPLAIN SELECT JSON_ARRAYAGG(b) FROM t1;
136 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
137 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 100.00 NULL
138 Warnings:
139 Note 1003 /* select#1 */ select json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1`
140 EXPLAIN FORMAT=json SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
141 EXPLAIN
142 {
143 "query_block": {
144 "select_id": 1,
145 "cost_info": {
146 "query_cost": "9.05"
147 },
148 "grouping_operation": {
149 "using_filesort": true,
150 "cost_info": {
151 "sort_cost": "8.00"
152 },
153 "table": {
154 "table_name": "t1",
155 "access_type": "ALL",
156 "rows_examined_per_scan": 8,
157 "rows_produced_per_join": 8,
158 "filtered": "100.00",
159 "cost_info": {
160 "read_cost": "0.25",
161 "eval_cost": "0.80",
162 "prefix_cost": "1.05",
163 "data_read_per_join": "448"
164 },
165 "used_columns": [
166 "a",
167 "b"
168 ]
169 }
170 }
171 }
172 }
173 Warnings:
174 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1` group by `test`.`t1`.`a`
175 EXPLAIN FORMAT=json SELECT SQL_BIG_RESULT a, JSON_ARRAYAGG(b)
176 FROM t1
177 GROUP BY a;
178 EXPLAIN
179 {
180 "query_block": {
181 "select_id": 1,
182 "cost_info": {
183 "query_cost": "9.05"
184 },
185 "grouping_operation": {
186 "using_filesort": true,
187 "cost_info": {
188 "sort_cost": "8.00"
189 },
190 "table": {
191 "table_name": "t1",
192 "access_type": "ALL",
193 "rows_examined_per_scan": 8,
194 "rows_produced_per_join": 8,
195 "filtered": "100.00",
196 "cost_info": {
197 "read_cost": "0.25",
198 "eval_cost": "0.80",
199 "prefix_cost": "1.05",
200 "data_read_per_join": "448"
201 },
202 "used_columns": [
203 "a",
204 "b"
205 ]
206 }
207 }
208 }
209 }
210 Warnings:
211 Note 1003 /* select#1 */ select sql_big_result `test`.`t1`.`a` AS `a`,json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1` group by `test`.`t1`.`a`
212 EXPLAIN FORMAT=json SELECT SQL_SMALL_RESULT a, JSON_ARRAYAGG(b)
213 FROM t1
214 GROUP BY a;
215 EXPLAIN
216 {
217 "query_block": {
218 "select_id": 1,
219 "cost_info": {
220 "query_cost": "9.05"
221 },
222 "grouping_operation": {
223 "using_filesort": true,
224 "cost_info": {
225 "sort_cost": "8.00"
226 },
227 "table": {
228 "table_name": "t1",
229 "access_type": "ALL",
230 "rows_examined_per_scan": 8,
231 "rows_produced_per_join": 8,
232 "filtered": "100.00",
233 "cost_info": {
234 "read_cost": "0.25",
235 "eval_cost": "0.80",
236 "prefix_cost": "1.05",
237 "data_read_per_join": "448"
238 },
239 "used_columns": [
240 "a",
241 "b"
242 ]
243 }
244 }
245 }
246 }
247 Warnings:
248 Note 1003 /* select#1 */ select sql_small_result `test`.`t1`.`a` AS `a`,json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1` group by `test`.`t1`.`a`
249 EXPLAIN FORMAT=json SELECT SQL_BUFFER_RESULT a, JSON_ARRAYAGG(b)
250 FROM t1
251 GROUP BY a;
252 EXPLAIN
253 {
254 "query_block": {
255 "select_id": 1,
256 "cost_info": {
257 "query_cost": "9.05"
258 },
259 "grouping_operation": {
260 "using_filesort": true,
261 "cost_info": {
262 "sort_cost": "8.00"
263 },
264 "buffer_result": {
265 "using_temporary_table": true,
266 "table": {
267 "table_name": "t1",
268 "access_type": "ALL",
269 "rows_examined_per_scan": 8,
270 "rows_produced_per_join": 8,
271 "filtered": "100.00",
272 "cost_info": {
273 "read_cost": "0.25",
274 "eval_cost": "0.80",
275 "prefix_cost": "1.05",
276 "data_read_per_join": "448"
277 },
278 "used_columns": [
279 "a",
280 "b"
281 ]
282 }
283 }
284 }
285 }
286 }
287 Warnings:
288 Note 1003 /* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a`,json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1` group by `test`.`t1`.`a`
289 EXPLAIN FORMAT=json SELECT JSON_ARRAYAGG(b) FROM t1;
290 EXPLAIN
291 {
292 "query_block": {
293 "select_id": 1,
294 "cost_info": {
295 "query_cost": "1.05"
296 },
297 "table": {
298 "table_name": "t1",
299 "access_type": "ALL",
300 "rows_examined_per_scan": 8,
301 "rows_produced_per_join": 8,
302 "filtered": "100.00",
303 "cost_info": {
304 "read_cost": "0.25",
305 "eval_cost": "0.80",
306 "prefix_cost": "1.05",
307 "data_read_per_join": "448"
308 },
309 "used_columns": [
310 "b"
311 ]
312 }
313 }
314 }
315 Warnings:
316 Note 1003 /* select#1 */ select json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1`
317 EXPLAIN FORMAT=json SELECT SQL_BIG_RESULT JSON_ARRAYAGG(b) FROM t1;
318 EXPLAIN
319 {
320 "query_block": {
321 "select_id": 1,
322 "cost_info": {
323 "query_cost": "1.05"
324 },
325 "table": {
326 "table_name": "t1",
327 "access_type": "ALL",
328 "rows_examined_per_scan": 8,
329 "rows_produced_per_join": 8,
330 "filtered": "100.00",
331 "cost_info": {
332 "read_cost": "0.25",
333 "eval_cost": "0.80",
334 "prefix_cost": "1.05",
335 "data_read_per_join": "448"
336 },
337 "used_columns": [
338 "b"
339 ]
340 }
341 }
342 }
343 Warnings:
344 Note 1003 /* select#1 */ select sql_big_result json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1`
345 EXPLAIN FORMAT=json SELECT SQL_SMALL_RESULT JSON_ARRAYAGG(b) FROM t1;
346 EXPLAIN
347 {
348 "query_block": {
349 "select_id": 1,
350 "cost_info": {
351 "query_cost": "1.05"
352 },
353 "table": {
354 "table_name": "t1",
355 "access_type": "ALL",
356 "rows_examined_per_scan": 8,
357 "rows_produced_per_join": 8,
358 "filtered": "100.00",
359 "cost_info": {
360 "read_cost": "0.25",
361 "eval_cost": "0.80",
362 "prefix_cost": "1.05",
363 "data_read_per_join": "448"
364 },
365 "used_columns": [
366 "b"
367 ]
368 }
369 }
370 }
371 Warnings:
372 Note 1003 /* select#1 */ select sql_small_result json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1`
373 EXPLAIN FORMAT=json SELECT SQL_BUFFER_RESULT JSON_ARRAYAGG(b) FROM t1;
374 EXPLAIN
375 {
376 "query_block": {
377 "select_id": 1,
378 "cost_info": {
379 "query_cost": "1.05"
380 },
381 "buffer_result": {
382 "using_temporary_table": true,
383 "table": {
384 "table_name": "t1",
385 "access_type": "ALL",
386 "rows_examined_per_scan": 8,
387 "rows_produced_per_join": 8,
388 "filtered": "100.00",
389 "cost_info": {
390 "read_cost": "0.25",
391 "eval_cost": "0.80",
392 "prefix_cost": "1.05",
393 "data_read_per_join": "448"
394 },
395 "used_columns": [
396 "b"
397 ]
398 }
399 }
400 }
401 }
402 Warnings:
403 Note 1003 /* select#1 */ select sql_buffer_result json_arrayagg(`test`.`t1`.`b`) AS `JSON_ARRAYAGG(b)` from `test`.`t1`
404 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a WITH ROLLUP;
405 a JSON_ARRAYAGG(b)
406 1 ["alfa", null, "unu", "one"]
407 2 ["doi"]
408 3 ["trei"]
409 4 [null, null]
410 NULL ["alfa", null, "unu", "one", "doi", "trei", null, null]
411 SELECT a, JSON_ARRAYAGG(b) as jarray
412 FROM t1
413 GROUP BY a
414 HAVING jarray= JSON_ARRAY("trei");
415 a jarray
416 3 ["trei"]
417 #
418 # Test JSON_OBJECTAGG.
419 #
420 FLUSH STATUS;
421 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
422 a JSON_OBJECTAGG(k, b)
423 1 {"1": "alfa", "2": null, "4": "unu", "8": "one"}
424 2 {"3": "doi"}
425 3 {"5": "trei"}
426 4 {"6": null, "7": null}
427 SHOW SESSION STATUS LIKE 'Handler_update%';
428 Variable_name Value
429 Handler_update 0
430 SELECT JSON_OBJECTAGG(k, b) FROM t1;
431 JSON_OBJECTAGG(k, b)
432 {"1": "alfa", "2": null, "3": "doi", "4": "unu", "5": "trei", "6": null, "7": null, "8": "one"}
433 SHOW SESSION STATUS LIKE 'Handler_update%';
434 Variable_name Value
435 Handler_update 0
436 PREPARE p1 FROM "SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a";
437 EXECUTE p1;
438 a JSON_OBJECTAGG(k, b)
439 1 {"1": "alfa", "2": null, "4": "unu", "8": "one"}
440 2 {"3": "doi"}
441 3 {"5": "trei"}
442 4 {"6": null, "7": null}
443 EXECUTE p1;
444 a JSON_OBJECTAGG(k, b)
445 1 {"1": "alfa", "2": null, "4": "unu", "8": "one"}
446 2 {"3": "doi"}
447 3 {"5": "trei"}
448 4 {"6": null, "7": null}
449 deallocate prepare p1;
450 PREPARE p4 FROM "SELECT JSON_OBJECTAGG(k, b) FROM t1";
451 EXECUTE p4;
452 JSON_OBJECTAGG(k, b)
453 {"1": "alfa", "2": null, "3": "doi", "4": "unu", "5": "trei", "6": null, "7": null, "8": "one"}
454 EXECUTE p4;
455 JSON_OBJECTAGG(k, b)
456 {"1": "alfa", "2": null, "3": "doi", "4": "unu", "5": "trei", "6": null, "7": null, "8": "one"}
457 deallocate prepare p4;
458 SELECT a, JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]') FROM t1 GROUP BY a;
459 a JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
460 1 [{"1": "alfa", "2": null, "4": "unu", "8": "one"}, true, false]
461 2 [{"3": "doi"}, true, false]
462 3 [{"5": "trei"}, true, false]
463 4 [{"6": null, "7": null}, true, false]
464 SELECT JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]') FROM t1;
465 JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
466 [{"1": "alfa", "2": null, "3": "doi", "4": "unu", "5": "trei", "6": null, "7": null, "8": "one"}, true, false]
467 PREPARE p1 FROM
468 "SELECT a, JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
469 FROM t1
470 GROUP BY a";
471 EXECUTE p1;
472 a JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
473 1 [{"1": "alfa", "2": null, "4": "unu", "8": "one"}, true, false]
474 2 [{"3": "doi"}, true, false]
475 3 [{"5": "trei"}, true, false]
476 4 [{"6": null, "7": null}, true, false]
477 EXECUTE p1;
478 a JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
479 1 [{"1": "alfa", "2": null, "4": "unu", "8": "one"}, true, false]
480 2 [{"3": "doi"}, true, false]
481 3 [{"5": "trei"}, true, false]
482 4 [{"6": null, "7": null}, true, false]
483 deallocate prepare p1;
484 PREPARE p4 FROM
485 "SELECT JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]') FROM t1";
486 EXECUTE p4;
487 JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
488 [{"1": "alfa", "2": null, "3": "doi", "4": "unu", "5": "trei", "6": null, "7": null, "8": "one"}, true, false]
489 EXECUTE p4;
490 JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
491 [{"1": "alfa", "2": null, "3": "doi", "4": "unu", "5": "trei", "6": null, "7": null, "8": "one"}, true, false]
492 deallocate prepare p4;
493 ANALYZE TABLE t1;
494 Table Op Msg_type Msg_text
495 test.t1 analyze status OK
496 EXPLAIN SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
497 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
498 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 100.00 Using filesort
499 Warnings:
500 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,json_objectagg(`test`.`t1`.`k`,`test`.`t1`.`b`) AS `JSON_OBJECTAGG(k, b)` from `test`.`t1` group by `test`.`t1`.`a`
501 EXPLAIN SELECT JSON_OBJECTAGG(k, b) FROM t1;
502 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
503 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 100.00 NULL
504 Warnings:
505 Note 1003 /* select#1 */ select json_objectagg(`test`.`t1`.`k`,`test`.`t1`.`b`) AS `JSON_OBJECTAGG(k, b)` from `test`.`t1`
506 EXPLAIN FORMAT=json SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
507 EXPLAIN
508 {
509 "query_block": {
510 "select_id": 1,
511 "cost_info": {
512 "query_cost": "9.05"
513 },
514 "grouping_operation": {
515 "using_filesort": true,
516 "cost_info": {
517 "sort_cost": "8.00"
518 },
519 "table": {
520 "table_name": "t1",
521 "access_type": "ALL",
522 "rows_examined_per_scan": 8,
523 "rows_produced_per_join": 8,
524 "filtered": "100.00",
525 "cost_info": {
526 "read_cost": "0.25",
527 "eval_cost": "0.80",
528 "prefix_cost": "1.05",
529 "data_read_per_join": "448"
530 },
531 "used_columns": [
532 "a",
533 "k",
534 "b"
535 ]
536 }
537 }
538 }
539 }
540 Warnings:
541 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,json_objectagg(`test`.`t1`.`k`,`test`.`t1`.`b`) AS `JSON_OBJECTAGG(k, b)` from `test`.`t1` group by `test`.`t1`.`a`
542 EXPLAIN FORMAT=json SELECT JSON_OBJECTAGG(k, b) FROM t1;
543 EXPLAIN
544 {
545 "query_block": {
546 "select_id": 1,
547 "cost_info": {
548 "query_cost": "1.05"
549 },
550 "table": {
551 "table_name": "t1",
552 "access_type": "ALL",
553 "rows_examined_per_scan": 8,
554 "rows_produced_per_join": 8,
555 "filtered": "100.00",
556 "cost_info": {
557 "read_cost": "0.25",
558 "eval_cost": "0.80",
559 "prefix_cost": "1.05",
560 "data_read_per_join": "448"
561 },
562 "used_columns": [
563 "k",
564 "b"
565 ]
566 }
567 }
568 }
569 Warnings:
570 Note 1003 /* select#1 */ select json_objectagg(`test`.`t1`.`k`,`test`.`t1`.`b`) AS `JSON_OBJECTAGG(k, b)` from `test`.`t1`
571 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a WITH ROLLUP;
572 a JSON_OBJECTAGG(k, b)
573 1 {"1": "alfa", "2": null, "4": "unu", "8": "one"}
574 2 {"3": "doi"}
575 3 {"5": "trei"}
576 4 {"6": null, "7": null}
577 NULL {"1": "alfa", "2": null, "3": "doi", "4": "unu", "5": "trei", "6": null, "7": null, "8": "one"}
578 SELECT a, JSON_OBJECTAGG(k, b) as jobject
579 FROM t1
580 GROUP BY a
581 HAVING jobject = JSON_OBJECT(3, "doi");
582 a jobject
583 2 {"3": "doi"}
584 #
585 # NULL values.
586 #
587 SELECT a, JSON_ARRAYAGG(null) FROM t1 GROUP BY a;
588 a JSON_ARRAYAGG(null)
589 1 [null, null, null, null]
590 2 [null]
591 3 [null]
592 4 [null, null]
593 SELECT JSON_ARRAYAGG(null) FROM t1;
594 JSON_ARRAYAGG(null)
595 [null, null, null, null, null, null, null, null]
596 SELECT a, JSON_OBJECTAGG(k, null) FROM t1 GROUP BY a;
597 a JSON_OBJECTAGG(k, null)
598 1 {"1": null, "2": null, "4": null, "8": null}
599 2 {"3": null}
600 3 {"5": null}
601 4 {"6": null, "7": null}
602 SELECT JSON_OBJECTAGG(k, null) FROM t1;
603 JSON_OBJECTAGG(k, null)
604 {"1": null, "2": null, "3": null, "4": null, "5": null, "6": null, "7": null, "8": null}
605 SELECT a, JSON_OBJECTAGG(null, b) FROM t1 GROUP BY a;
606 ERROR 22032: JSON documents may not contain NULL member names.
607 SELECT JSON_OBJECTAGG(null, b) FROM t1;
608 ERROR 22032: JSON documents may not contain NULL member names.
609 #
610 # Coverage test for fix_fields: Disable_semijoin_flattening.
611 #
612 CREATE TABLE t(a INT);
613 ANALYZE TABLE t1;
614 Table Op Msg_type Msg_text
615 test.t1 analyze status OK
616 EXPLAIN format=json SELECT (SELECT 1 FROM t WHERE JSON_ARRAYAGG(1 IN (SELECT 1 FROM t)));
617 EXPLAIN
618 {
619 "query_block": {
620 "select_id": 1,
621 "message": "No tables used",
622 "optimized_away_subqueries": [
623 {
624 "dependent": true,
625 "cacheable": false,
626 "query_block": {
627 "select_id": 2,
628 "cost_info": {
629 "query_cost": "0.35"
630 },
631 "table": {
632 "table_name": "t",
633 "access_type": "ALL",
634 "rows_examined_per_scan": 1,
635 "rows_produced_per_join": 1,
636 "filtered": "100.00",
637 "cost_info": {
638 "read_cost": "0.25",
639 "eval_cost": "0.10",
640 "prefix_cost": "0.35",
641 "data_read_per_join": "8"
642 },
643 "attached_condition": "(0 <> json_arrayagg(<cache>(<in_optimizer>(1,<exists>(/* select#3 */ select 1 from `test`.`t` where true)))))"
644 },
645 "optimized_away_subqueries": [
646 {
647 "dependent": false,
648 "cacheable": true,
649 "query_block": {
650 "select_id": 3,
651 "cost_info": {
652 "query_cost": "0.35"
653 },
654 "table": {
655 "table_name": "t",
656 "access_type": "ALL",
657 "rows_examined_per_scan": 1,
658 "rows_produced_per_join": 1,
659 "filtered": "100.00",
660 "cost_info": {
661 "read_cost": "0.25",
662 "eval_cost": "0.10",
663 "prefix_cost": "0.35",
664 "data_read_per_join": "8"
665 }
666 }
667 }
668 }
669 ]
670 }
671 }
672 ]
673 }
674 }
675 Warnings:
676 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
677 Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t` where (0 <> json_arrayagg(<cache>(<in_optimizer>(1,<exists>(/* select#3 */ select 1 from `test`.`t` where true)))))) AS `(SELECT 1 FROM t WHERE JSON_ARRAYAGG(1 IN (SELECT 1 FROM t)))`
678 SELECT (SELECT 1 FROM t WHERE JSON_ARRAYAGG(1 IN (SELECT 1 FROM t)));
679 (SELECT 1 FROM t WHERE JSON_ARRAYAGG(1 IN (SELECT 1 FROM t)))
680 NULL
681 Warnings:
682 Warning 3986 Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
683 DROP TABLE t;
684 #
685 # Coverage test for fix_fields: check_cols.
686 #
687 SELECT JSON_ARRAYAGG((SELECT 1, 1));
688 ERROR 21000: Operand should contain 1 column(s)
689 #
690 # Coverage test for fix_fields: resolve_type.
691 #
692 CREATE TABLE t2(gid int, a int);
693 SELECT JSON_ARRAYAGG(ST_PointFromText('POINT(10 10)')) FROM t2;
694 ERROR HY000: Incorrect arguments to json_arrayagg
695 #
696 # Coverage test for fix_fields: check_sum_func.
697 #
698 SELECT (SELECT JSON_ARRAYAGG(COUNT(a)) FROM t2) FROM t1;
699 ERROR HY000: Invalid use of group function
700 DROP TABLE t2;
701 #
702 # Empty table.
703 #
704 TRUNCATE TABLE t1;
705 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
706 a JSON_ARRAYAGG(b)
707 SELECT JSON_ARRAYAGG(b) FROM t1;
708 JSON_ARRAYAGG(b)
709 NULL
710 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
711 a JSON_OBJECTAGG(k, b)
712 SELECT JSON_OBJECTAGG(k, b) FROM t1;
713 JSON_OBJECTAGG(k, b)
714 NULL
715 #
716 # Tests for max_allowed_packet.
717 #
718 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, x INT);
719 INSERT INTO t(x) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
720 INSERT INTO t(x) SELECT t1.x from t t1, t t2, t t3;
721 SET GLOBAL net_buffer_length = 1024;
722 SET GLOBAL max_allowed_packet = 1024;
723 SELECT JSON_ARRAYAGG(x) FROM t;
724 JSON_ARRAYAGG(x)
725 NULL
726 Warnings:
727 Warning 1301 Result of json_arrayagg() was larger than max_allowed_packet (1024) - truncated
728 SELECT JSON_OBJECTAGG(id, x) FROM t;
729 JSON_OBJECTAGG(id, x)
730 NULL
731 Warnings:
732 Warning 1301 Result of json_objectagg() was larger than max_allowed_packet (1024) - truncated
733 SELECT id % 2 AS i, JSON_ARRAYAGG(x) FROM t GROUP BY i;
734 i JSON_ARRAYAGG(x)
735 0 NULL
736 1 NULL
737 Warnings:
738 Warning 1301 Result of json_arrayagg() was larger than max_allowed_packet (1024) - truncated
739 Warning 1301 Result of json_arrayagg() was larger than max_allowed_packet (1024) - truncated
740 SELECT id % 2 AS i, JSON_OBJECTAGG(id, x) FROM t GROUP BY i;
741 i JSON_OBJECTAGG(id, x)
742 0 NULL
743 1 NULL
744 Warnings:
745 Warning 1301 Result of json_objectagg() was larger than max_allowed_packet (1024) - truncated
746 Warning 1301 Result of json_objectagg() was larger than max_allowed_packet (1024) - truncated
747 SET GLOBAL max_allowed_packet = default;
748 SET GLOBAL net_buffer_length = default;
749 DROP TABLE t;
750 #
751 # Cleanup test.
752 #
753 DROP TABLE t1;
754 #
755 # Bug #24368053
756 # WL#7987: ASSERTION `!TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TABLE-> ...
757 #
758 CREATE TABLE C(col_int int);
759 CREATE TABLE CC(col_int int);
760 INSERT INTO CC VALUES (1),(2),(3);
761 SELECT JSON_OBJECTAGG(table1.`col_int` ,table1.`col_int`) AS field2,
762 (SELECT JSON_ARRAYAGG(SUBQUERY2_t1.`col_int`)
763 FROM CC AS SUBQUERY2_t1
764 WHERE SUBQUERY2_t1.`col_int` <> table1.`col_int`) AS field5
765 FROM (CC AS table1)
766 WHERE (table1.`col_int` <> ALL (SELECT SUBQUERY4_t1.`col_int`
767 FROM (CC AS SUBQUERY4_t1 STRAIGHT_JOIN C)))
768 GROUP BY
769 field5;
770 field2 field5
771 {"3": 3} [1, 2]
772 {"2": 2} [1, 3]
773 {"1": 1} [2, 3]
774 Warnings:
775 Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
776 DROP TABLE C;
777 DROP TABLE CC;
778 #
779 # Bug #24367384
780 # WL#7987: INNODB: ASSERTION FAILURE: ROW0SEL.CC:2558:FIELD->PREFIX_LEN > 0 ...
781 #
782 CREATE TABLE BB (pk INT AUTO_INCREMENT PRIMARY KEY, col_varchar_key VARCHAR(1));
783 INSERT INTO BB VALUES(1,'a');
784 SELECT (SELECT JSON_ARRAYAGG(`pk`) FROM BB as t1
785 WHERE t1.`col_varchar_key` <> t2.`col_varchar_key`) AS field2
786 FROM BB as t2
787 GROUP BY field2;
788 field2
789 NULL
790 SELECT (SELECT JSON_ARRAYAGG(`pk`) FROM BB as t1
791 WHERE t1.`col_varchar_key` = t2.`col_varchar_key`) AS field2
792 FROM BB as t2
793 GROUP BY field2;
794 field2
795 [1]
796 DROP TABLE BB;
797 #
798 # Bug #24365264
799 # WL#7987: SIG 11 IN ITEM::MARK_FIELD_IN_MAP|SQL/ITEM.H
800 #
801 CREATE TABLE C (col_int int);
802 INSERT INTO C VALUES (1);
803 SELECT *
804 FROM C WHERE col_int < (SELECT JSON_ARRAYAGG(col_int) FROM C )
805 ORDER BY col_int ;
806 col_int
807 1
808 DROP TABLE C;
809 #
810 # Bug #24366341
811 # WL#7987: SIG 6 IN JSON_WRAPPER::TYPE|SQL/JSON_DOM.CC
812 #
813 CREATE TABLE CC(col_varchar_key varchar(1));
814 INSERT INTO CC VALUES ('a');
815 SELECT JSON_ARRAYAGG(col_varchar_key) AS field1 FROM CC HAVING field1 > 9;
816 field1
817 ["a"]
818 SELECT JSON_OBJECTAGG(col_varchar_key, col_varchar_key) AS field1 FROM CC
819 HAVING (field1 <> 'a' AND field1 != 'e');
820 field1
821 {"a": "a"}
822 DROP TABLE CC;
823 #
824 # with ROLLUP + two/three groups
825 #
826 CREATE TABLE tg (g1 int, g2 int, k int, b VARCHAR(10));
827 INSERT INTO tg VALUES
828 (1, 1, 1, "alfa"),
829 (1, 2, 2, null),
830 (2, 3, 3, "doi"),
831 (1, 1, 4, "unu"),
832 (3, 2, 5, "trei"),
833 (4, 3, 6, null),
834 (4, 1, 7, null),
835 (1, 2, 8, "one");
836 SELECT g1, g2, JSON_ARRAYAGG(g2) FROM tg GROUP BY g1, g2 with rollup;
837 g1 g2 JSON_ARRAYAGG(g2)
838 1 1 [1, 1]
839 1 2 [2, 2]
840 1 NULL [1, 1, 2, 2]
841 2 3 [3]
842 2 NULL [3]
843 3 2 [2]
844 3 NULL [2]
845 4 1 [1]
846 4 3 [3]
847 4 NULL [1, 3]
848 NULL NULL [1, 1, 2, 2, 3, 2, 1, 3]
849 SELECT g1, g2, JSON_OBJECTAGG(k, g1) FROM tg GROUP BY g1, g2 with rollup;
850 g1 g2 JSON_OBJECTAGG(k, g1)
851 1 1 {"1": 1, "4": 1}
852 1 2 {"2": 1, "8": 1}
853 1 NULL {"1": 1, "2": 1, "4": 1, "8": 1}
854 2 3 {"3": 2}
855 2 NULL {"3": 2}
856 3 2 {"5": 3}
857 3 NULL {"5": 3}
858 4 1 {"7": 4}
859 4 3 {"6": 4}
860 4 NULL {"6": 4, "7": 4}
861 NULL NULL {"1": 1, "2": 1, "3": 2, "4": 1, "5": 3, "6": 4, "7": 4, "8": 1}
862 CREATE TABLE tg3 (g1 int, g2 int, g3 int, k int, b VARCHAR(10));
863 INSERT INTO tg3 VALUES
864 (1, 1, 1, 1, "1.1.1"),
865 (1, 1, 2, 2, "1.1.2"),
866 (1, 1, 3, 3, "1.1.3"),
867 (1, 2, 1, 4, "1.2.1"),
868 (1, 2, 2, 5, "1.2.2"),
869 (1, 2, 3, 6, "1.2.3"),
870 (1, 3, 1, 7, "1.3.1"),
871 (1, 3, 2, 8, "1.3.2"),
872 (1, 3, 3, 9, "1.3.3"),
873 (2, 1, 1, 10, "2.1.1"),
874 (2, 1, 2, 11, "2.1.2"),
875 (2, 1, 3, 12, "2.1.3"),
876 (2, 2, 1, 13, "2.2.1"),
877 (2, 2, 2, 14, "2.2.2"),
878 (2, 2, 3, 15, "2.2.3"),
879 (2, 3, 1, 16, "2.3.1"),
880 (2, 3, 2, 17, "2.3.2"),
881 (2, 3, 3, 18, "2.3.3"),
882 (3, 1, 1, 19, "3.1.1"),
883 (3, 1, 2, 20, "3.1.2"),
884 (3, 1, 3, 21, "3.1.3"),
885 (3, 2, 1, 22, "3.2.1"),
886 (3, 2, 2, 23, "3.2.2"),
887 (3, 2, 3, 24, "3.2.3"),
888 (3, 3, 1, 25, "3.3.1"),
889 (3, 3, 2, 26, "3.3.2"),
890 (3, 3, 3, 27, "3.3.3");
891 SELECT g1, g2, g3, JSON_ARRAYAGG(b) FROM tg3 GROUP BY g1, g2, g3 with rollup;
892 g1 g2 g3 JSON_ARRAYAGG(b)
893 1 1 1 ["1.1.1"]
894 1 1 2 ["1.1.2"]
895 1 1 3 ["1.1.3"]
896 1 1 NULL ["1.1.1", "1.1.2", "1.1.3"]
897 1 2 1 ["1.2.1"]
898 1 2 2 ["1.2.2"]
899 1 2 3 ["1.2.3"]
900 1 2 NULL ["1.2.1", "1.2.2", "1.2.3"]
901 1 3 1 ["1.3.1"]
902 1 3 2 ["1.3.2"]
903 1 3 3 ["1.3.3"]
904 1 3 NULL ["1.3.1", "1.3.2", "1.3.3"]
905 1 NULL NULL ["1.1.1", "1.1.2", "1.1.3", "1.2.1", "1.2.2", "1.2.3", "1.3.1", "1.3.2", "1.3.3"]
906 2 1 1 ["2.1.1"]
907 2 1 2 ["2.1.2"]
908 2 1 3 ["2.1.3"]
909 2 1 NULL ["2.1.1", "2.1.2", "2.1.3"]
910 2 2 1 ["2.2.1"]
911 2 2 2 ["2.2.2"]
912 2 2 3 ["2.2.3"]
913 2 2 NULL ["2.2.1", "2.2.2", "2.2.3"]
914 2 3 1 ["2.3.1"]
915 2 3 2 ["2.3.2"]
916 2 3 3 ["2.3.3"]
917 2 3 NULL ["2.3.1", "2.3.2", "2.3.3"]
918 2 NULL NULL ["2.1.1", "2.1.2", "2.1.3", "2.2.1", "2.2.2", "2.2.3", "2.3.1", "2.3.2", "2.3.3"]
919 3 1 1 ["3.1.1"]
920 3 1 2 ["3.1.2"]
921 3 1 3 ["3.1.3"]
922 3 1 NULL ["3.1.1", "3.1.2", "3.1.3"]
923 3 2 1 ["3.2.1"]
924 3 2 2 ["3.2.2"]
925 3 2 3 ["3.2.3"]
926 3 2 NULL ["3.2.1", "3.2.2", "3.2.3"]
927 3 3 1 ["3.3.1"]
928 3 3 2 ["3.3.2"]
929 3 3 3 ["3.3.3"]
930 3 3 NULL ["3.3.1", "3.3.2", "3.3.3"]
931 3 NULL NULL ["3.1.1", "3.1.2", "3.1.3", "3.2.1", "3.2.2", "3.2.3", "3.3.1", "3.3.2", "3.3.3"]
932 NULL NULL NULL ["1.1.1", "1.1.2", "1.1.3", "1.2.1", "1.2.2", "1.2.3", "1.3.1", "1.3.2", "1.3.3", "2.1.1", "2.1.2", "2.1.3", "2.2.1", "2.2.2", "2.2.3", "2.3.1", "2.3.2", "2.3.3", "3.1.1", "3.1.2", "3.1.3", "3.2.1", "3.2.2", "3.2.3", "3.3.1", "3.3.2", "3.3.3"]
933 SELECT g1, g2, g3, JSON_OBJECTAGG(k, b) FROM tg3 GROUP BY g1, g2, g3 with rollup;
934 g1 g2 g3 JSON_OBJECTAGG(k, b)
935 1 1 1 {"1": "1.1.1"}
936 1 1 2 {"2": "1.1.2"}
937 1 1 3 {"3": "1.1.3"}
938 1 1 NULL {"1": "1.1.1", "2": "1.1.2", "3": "1.1.3"}
939 1 2 1 {"4": "1.2.1"}
940 1 2 2 {"5": "1.2.2"}
941 1 2 3 {"6": "1.2.3"}
942 1 2 NULL {"4": "1.2.1", "5": "1.2.2", "6": "1.2.3"}
943 1 3 1 {"7": "1.3.1"}
944 1 3 2 {"8": "1.3.2"}
945 1 3 3 {"9": "1.3.3"}
946 1 3 NULL {"7": "1.3.1", "8": "1.3.2", "9": "1.3.3"}
947 1 NULL NULL {"1": "1.1.1", "2": "1.1.2", "3": "1.1.3", "4": "1.2.1", "5": "1.2.2", "6": "1.2.3", "7": "1.3.1", "8": "1.3.2", "9": "1.3.3"}
948 2 1 1 {"10": "2.1.1"}
949 2 1 2 {"11": "2.1.2"}
950 2 1 3 {"12": "2.1.3"}
951 2 1 NULL {"10": "2.1.1", "11": "2.1.2", "12": "2.1.3"}
952 2 2 1 {"13": "2.2.1"}
953 2 2 2 {"14": "2.2.2"}
954 2 2 3 {"15": "2.2.3"}
955 2 2 NULL {"13": "2.2.1", "14": "2.2.2", "15": "2.2.3"}
956 2 3 1 {"16": "2.3.1"}
957 2 3 2 {"17": "2.3.2"}
958 2 3 3 {"18": "2.3.3"}
959 2 3 NULL {"16": "2.3.1", "17": "2.3.2", "18": "2.3.3"}
960 2 NULL NULL {"10": "2.1.1", "11": "2.1.2", "12": "2.1.3", "13": "2.2.1", "14": "2.2.2", "15": "2.2.3", "16": "2.3.1", "17": "2.3.2", "18": "2.3.3"}
961 3 1 1 {"19": "3.1.1"}
962 3 1 2 {"20": "3.1.2"}
963 3 1 3 {"21": "3.1.3"}
964 3 1 NULL {"19": "3.1.1", "20": "3.1.2", "21": "3.1.3"}
965 3 2 1 {"22": "3.2.1"}
966 3 2 2 {"23": "3.2.2"}
967 3 2 3 {"24": "3.2.3"}
968 3 2 NULL {"22": "3.2.1", "23": "3.2.2", "24": "3.2.3"}
969 3 3 1 {"25": "3.3.1"}
970 3 3 2 {"26": "3.3.2"}
971 3 3 3 {"27": "3.3.3"}
972 3 3 NULL {"25": "3.3.1", "26": "3.3.2", "27": "3.3.3"}
973 3 NULL NULL {"19": "3.1.1", "20": "3.1.2", "21": "3.1.3", "22": "3.2.1", "23": "3.2.2", "24": "3.2.3", "25": "3.3.1", "26": "3.3.2", "27": "3.3.3"}
974 NULL NULL NULL {"1": "1.1.1", "2": "1.1.2", "3": "1.1.3", "4": "1.2.1", "5": "1.2.2", "6": "1.2.3", "7": "1.3.1", "8": "1.3.2", "9": "1.3.3", "10": "2.1.1", "11": "2.1.2", "12": "2.1.3", "13": "2.2.1", "14": "2.2.2", "15": "2.2.3", "16": "2.3.1", "17": "2.3.2", "18": "2.3.3", "19": "3.1.1", "20": "3.1.2", "21": "3.1.3", "22": "3.2.1", "23": "3.2.2", "24": "3.2.3", "25": "3.3.1", "26": "3.3.2", "27": "3.3.3"}
975 DROP TABLE tg;
976 DROP TABLE tg3;
977 #
978 # Tests duplicates for JSON_OBJECTAGG
979 #
980 CREATE TABLE t1 (a int, k int, b VARCHAR(10));
981 INSERT INTO t1 VALUES
982 (1, 1, "1.1"),
983 (1, 1, "1.2"),
984 (1, 1, "1.3"),
985 (2, 2, "2.1"),
986 (2, 2, "2.2"),
987 (2, 2, "2.3");
988 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
989 a JSON_OBJECTAGG(k, b)
990 1 {"1": "1.3"}
991 2 {"2": "2.3"}
992 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
993 a JSON_ARRAYAGG(b)
994 1 ["1.1", "1.2", "1.3"]
995 2 ["2.1", "2.2", "2.3"]
996 DROP TABLE t1;
997 #
998 # Tests with ORDER BY, DISTINCT
999 #
1000 CREATE TABLE t1 (a int, k int, b VARCHAR(10));
1001 INSERT INTO t1 VALUES
1002 (2, 8, "2.3"),
1003 (1, 7, "1.1"),
1004 (3, 6, "3.2"),
1005 (2, 5, "2.2"),
1006 (3, 9, "3.1"),
1007 (1, 4, "1.2"),
1008 (3, 3, "3.3"),
1009 (2, 2, "2.1"),
1010 (1, 1, "1.3");
1011 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
1012 a JSON_ARRAYAGG(b)
1013 1 ["1.1", "1.2", "1.3"]
1014 2 ["2.3", "2.2", "2.1"]
1015 3 ["3.2", "3.1", "3.3"]
1016 SELECT DISTINCT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
1017 a JSON_ARRAYAGG(b)
1018 1 ["1.1", "1.2", "1.3"]
1019 2 ["2.3", "2.2", "2.1"]
1020 3 ["3.2", "3.1", "3.3"]
1021 SELECT DISTINCT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a ORDER BY a ASC;
1022 a JSON_ARRAYAGG(b)
1023 1 ["1.1", "1.2", "1.3"]
1024 2 ["2.3", "2.2", "2.1"]
1025 3 ["3.2", "3.1", "3.3"]
1026 SELECT DISTINCT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a ORDER BY a DESC;
1027 a JSON_ARRAYAGG(b)
1028 3 ["3.2", "3.1", "3.3"]
1029 2 ["2.3", "2.2", "2.1"]
1030 1 ["1.1", "1.2", "1.3"]
1031 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
1032 a JSON_OBJECTAGG(k, b)
1033 1 {"1": "1.3", "4": "1.2", "7": "1.1"}
1034 2 {"2": "2.1", "5": "2.2", "8": "2.3"}
1035 3 {"3": "3.3", "6": "3.2", "9": "3.1"}
1036 SELECT DISTINCT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
1037 a JSON_OBJECTAGG(k, b)
1038 1 {"1": "1.3", "4": "1.2", "7": "1.1"}
1039 2 {"2": "2.1", "5": "2.2", "8": "2.3"}
1040 3 {"3": "3.3", "6": "3.2", "9": "3.1"}
1041 SELECT DISTINCT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a ORDER BY a ASC;
1042 a JSON_OBJECTAGG(k, b)
1043 1 {"1": "1.3", "4": "1.2", "7": "1.1"}
1044 2 {"2": "2.1", "5": "2.2", "8": "2.3"}
1045 3 {"3": "3.3", "6": "3.2", "9": "3.1"}
1046 SELECT DISTINCT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a ORDER BY a DESC;
1047 a JSON_OBJECTAGG(k, b)
1048 3 {"3": "3.3", "6": "3.2", "9": "3.1"}
1049 2 {"2": "2.1", "5": "2.2", "8": "2.3"}
1050 1 {"1": "1.3", "4": "1.2", "7": "1.1"}
1051 DROP TABLE t1;
1052 #
1053 # Tests with joins
1054 #
1055 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, t1 INT, t2 INT);
1056 CREATE TABLE p(id INT PRIMARY KEY AUTO_INCREMENT, p1 INT, p2 INT);
1057 INSERT INTO t(t1, t2) VALUES (1, 1), (2, 1), (3,3), (1, 4);
1058 INSERT INTO p(p1, p2) VALUES (2, 1), (1, 1), (3,3), (2, 4);
1059 FLUSH STATUS;
1060 SHOW SESSION STATUS LIKE 'Handler_update%';
1061 Variable_name Value
1062 Handler_update 0
1063 SELECT JSON_ARRAYAGG(t2) FROM t join p on t.t1=p.p1;
1064 SHOW SESSION STATUS LIKE 'Handler_update%';
1065 Variable_name Value
1066 Handler_update 0
1067 SELECT t1, JSON_ARRAYAGG(t2) FROM t join p on t.t1=p.p1 group by t1;
1068 SHOW SESSION STATUS LIKE 'Handler_update%';
1069 Variable_name Value
1070 Handler_update 0
1071 SELECT (SELECT 1 AS foo ORDER BY JSON_ARRAYAGG(t2)) AS x FROM t;
1072 x
1073 1
1074 SELECT t1 FROM t ORDER BY (SELECT JSON_ARRAYAGG(t1) FROM t AS t2);
1075 t1
1076 1
1077 2
1078 3
1079 1
1080 SELECT JSON_ARRAYAGG(t1) FROM t
1081 ORDER BY (SELECT JSON_ARRAYAGG(t1) FROM t AS t2);
1082 JSON_ARRAYAGG(t1)
1083 [1, 2, 3, 1]
1084 SELECT (SELECT JSON_ARRAYAGG(t1_outer.t1) FROM t AS t1_inner LIMIT 1) as f
1085 FROM t AS t1_outer GROUP BY t1_outer.t2;
1086 f
1087 [1, 2]
1088 [3]
1089 [1]
1090 DROP TABLE t;
1091 DROP TABLE p;
1092 #
1093 # Tests with JSON
1094 #
1095 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, k INT, j JSON);
1096 INSERT INTO t(k, j) VALUES
1097 (1, '[1,2,3,4]'),
1098 (2, '{"prop1": 1}'),
1099 (1, '[3]'),
1100 (2, '{"prop2": 2, "prop10": 10}'),
1101 (1, '[99]');
1102 SELECT k, JSON_ARRAYAGG(j) FROM t GROUP BY k;
1103 k JSON_ARRAYAGG(j)
1104 1 [[1, 2, 3, 4], [3], [99]]
1105 2 [{"prop1": 1}, {"prop2": 2, "prop10": 10}]
1106 SELECT k, JSON_OBJECTAGG(id, j) FROM t GROUP BY k;
1107 k JSON_OBJECTAGG(id, j)
1108 1 {"1": [1, 2, 3, 4], "3": [3], "5": [99]}
1109 2 {"2": {"prop1": 1}, "4": {"prop2": 2, "prop10": 10}}
1110 DROP TABLE t;
1111 #
1112 # Coverage tests for val_* functions
1113 #
1114 CREATE TABLE t2(gid int, a int);
1115 INSERT INTO t2(gid, a) VALUES (1, 1), (1, 2), (2, 4), (2, 8);
1116 SELECT gid, 1.0 * JSON_ARRAYAGG(a) FROM t2 GROUP BY gid;
1117 gid 1.0 * JSON_ARRAYAGG(a)
1118 1 0
1119 2 0
1120 Warnings:
1121 Warning 3156 Invalid JSON value for CAST to DOUBLE from column json_arrayagg at row 1
1122 Warning 3156 Invalid JSON value for CAST to DOUBLE from column json_arrayagg at row 2
1123 SELECT gid, 0x30 << JSON_ARRAYAGG(a) FROM t2 GROUP BY gid;
1124 gid 0x30 << JSON_ARRAYAGG(a)
1125 1 48
1126 2 48
1127 Warnings:
1128 Warning 3156 Invalid JSON value for CAST to INTEGER from column json_arrayagg at row 1
1129 Warning 3156 Invalid JSON value for CAST to INTEGER from column json_arrayagg at row 2
1130 SELECT gid, DATE_ADD(JSON_ARRAYAGG(a), INTERVAL 31 DAY)
1131 FROM t2 GROUP BY gid;
1132 gid DATE_ADD(JSON_ARRAYAGG(a), INTERVAL 31 DAY)
1133 1 NULL
1134 2 NULL
1135 Warnings:
1136 Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_arrayagg at row 1
1137 Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_arrayagg at row 2
1138 SELECT gid, ADDTIME(JSON_ARRAYAGG(a), '02:00:00.999998')
1139 FROM t2 GROUP BY gid;
1140 gid ADDTIME(JSON_ARRAYAGG(a), '02:00:00.999998')
1141 1 NULL
1142 2 NULL
1143 Warnings:
1144 Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_arrayagg at row 1
1145 Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_arrayagg at row 2
1146 SELECT gid, SEC_TO_TIME(JSON_ARRAYAGG(a)) FROM t2 GROUP BY gid;
1147 gid SEC_TO_TIME(JSON_ARRAYAGG(a))
1148 1 00:00:00.000000
1149 2 00:00:00.000000
1150 Warnings:
1151 Warning 3156 Invalid JSON value for CAST to DECIMAL from column json_arrayagg at row 1
1152 Warning 3156 Invalid JSON value for CAST to DECIMAL from column json_arrayagg at row 2
1153 TRUNCATE TABLE t2;
1154 SELECT 1.0 * JSON_ARRAYAGG(a) FROM t2;
1155 1.0 * JSON_ARRAYAGG(a)
1156 NULL
1157 SELECT 0x30 << JSON_ARRAYAGG(a) FROM t2;
1158 0x30 << JSON_ARRAYAGG(a)
1159 NULL
1160 SELECT DATE_ADD(JSON_ARRAYAGG(a), INTERVAL 31 DAY) FROM t2;
1161 DATE_ADD(JSON_ARRAYAGG(a), INTERVAL 31 DAY)
1162 NULL
1163 SELECT ADDTIME(JSON_ARRAYAGG(a), '02:00:00.999998') FROM t2;
1164 ADDTIME(JSON_ARRAYAGG(a), '02:00:00.999998')
1165 NULL
1166 SELECT SEC_TO_TIME(JSON_ARRAYAGG(a)) FROM t2;
1167 SEC_TO_TIME(JSON_ARRAYAGG(a))
1168 NULL
1169 DROP TABLE t2;
1170 #
1171 # Bug#34548259 confusing result about json value
1172 #
1173 CREATE TABLE t1 (a JSON);
1174 INSERT INTO t1 SELECT JSON_OBJECTAGG('a', b'01010101');
1175 SELECT JSON_TYPE(JSON_EXTRACT(a, '$.a')) FROM t1;
1176 JSON_TYPE(JSON_EXTRACT(a, '$.a'))
1177 BLOB
1178 SELECT JSON_TYPE(JSON_EXTRACT(JSON_OBJECT('a', b'01010101'), '$.a'));
1179 JSON_TYPE(JSON_EXTRACT(JSON_OBJECT('a', b'01010101'), '$.a'))
1180 BLOB
1181 DROP TABLE t1;