Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # Tests JSON aggregation functions added in WL#7987 #
4 # #
5 ###############################################################################
6
7
8 --echo #
9 --echo # Setup test.
10 --echo #
11
12 CREATE TABLE t1 (a int, k int, b VARCHAR(10)) CHARSET utf8mb4;
13 INSERT INTO t1 VALUES
14 (1, 1, "alfa"),
15 (1, 2, null),
16 (2, 3, "doi"),
17 (1, 4, "unu"),
18 (3, 5, "trei"),
19 (4, 6, null),
20 (4, 7, null),
21 (1, 8, "one");
22
23 --echo #
24 --echo # Test JSON_ARRAYAGG.
25 --echo #
26
27 FLUSH STATUS;
28 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
29 SHOW SESSION STATUS LIKE 'Handler_update%';
30 SELECT SQL_BIG_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
31 SELECT SQL_SMALL_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
32 SHOW SESSION STATUS LIKE 'Handler_update%';
33 SELECT SQL_BUFFER_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
34 SHOW SESSION STATUS LIKE 'Handler_update%';
35
36 SELECT JSON_ARRAYAGG(b) FROM t1;
37 SELECT SQL_BIG_RESULT JSON_ARRAYAGG(b) FROM t1;
38 SELECT SQL_SMALL_RESULT JSON_ARRAYAGG(b) FROM t1;
39 SELECT SQL_BUFFER_RESULT JSON_ARRAYAGG(b) FROM t1;
40
41 PREPARE p1 FROM "SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a";
42 EXECUTE p1;
43 EXECUTE p1;
44 deallocate prepare p1;
45
46 PREPARE p3 FROM
47 "SELECT SQL_BUFFER_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a";
48 EXECUTE p3;
49 EXECUTE p3;
50 deallocate prepare p3;
51
52 PREPARE p4 FROM "SELECT JSON_ARRAYAGG(b) FROM t1";
53 EXECUTE p4;
54 EXECUTE p4;
55 deallocate prepare p4;
56
57
58 SELECT JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1;
59
60 PREPARE p1 FROM
61 "SELECT a, JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1 GROUP BY a";
62 EXECUTE p1;
63 EXECUTE p1;
64 deallocate prepare p1;
65
66
67 PREPARE p4 FROM
68 "SELECT JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1";
69 EXECUTE p4;
70 EXECUTE p4;
71 deallocate prepare p4;
72
73
74 ANALYZE TABLE t1;
75
76 EXPLAIN SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
77 EXPLAIN SELECT JSON_ARRAYAGG(b) FROM t1;
78
79 --skip_if_hypergraph # Depends on the query plan.
80 EXPLAIN FORMAT=json SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
81 --skip_if_hypergraph # Depends on the query plan.
82 EXPLAIN FORMAT=json SELECT SQL_BIG_RESULT a, JSON_ARRAYAGG(b)
83 FROM t1
84 GROUP BY a;
85 --skip_if_hypergraph # Depends on the query plan.
86 EXPLAIN FORMAT=json SELECT SQL_SMALL_RESULT a, JSON_ARRAYAGG(b)
87 FROM t1
88 GROUP BY a;
89 --skip_if_hypergraph # Depends on the query plan.
90 EXPLAIN FORMAT=json SELECT SQL_BUFFER_RESULT a, JSON_ARRAYAGG(b)
91 FROM t1
92 GROUP BY a;
93
94 --skip_if_hypergraph # Depends on the query plan.
95 EXPLAIN FORMAT=json SELECT JSON_ARRAYAGG(b) FROM t1;
96 --skip_if_hypergraph # Depends on the query plan.
97 EXPLAIN FORMAT=json SELECT SQL_BIG_RESULT JSON_ARRAYAGG(b) FROM t1;
98 --skip_if_hypergraph # Depends on the query plan.
99 EXPLAIN FORMAT=json SELECT SQL_SMALL_RESULT JSON_ARRAYAGG(b) FROM t1;
100 --skip_if_hypergraph # Depends on the query plan.
101 EXPLAIN FORMAT=json SELECT SQL_BUFFER_RESULT JSON_ARRAYAGG(b) FROM t1;
102
103 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a WITH ROLLUP;
104 SELECT a, JSON_ARRAYAGG(b) as jarray
105 FROM t1
106 GROUP BY a
107 HAVING jarray= JSON_ARRAY("trei");
108
109 --echo #
110 --echo # Test JSON_OBJECTAGG.
111 --echo #
112
113 FLUSH STATUS;
114 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
115 SHOW SESSION STATUS LIKE 'Handler_update%';
116 SELECT JSON_OBJECTAGG(k, b) FROM t1;
117 SHOW SESSION STATUS LIKE 'Handler_update%';
118
119 PREPARE p1 FROM "SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a";
120 EXECUTE p1;
121 EXECUTE p1;
122 deallocate prepare p1;
123
124 PREPARE p4 FROM "SELECT JSON_OBJECTAGG(k, b) FROM t1";
125 EXECUTE p4;
126 EXECUTE p4;
127 deallocate prepare p4;
128
129 SELECT a, JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]') FROM t1 GROUP BY a;
130 SELECT JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]') FROM t1;
131
132 PREPARE p1 FROM
133 "SELECT a, JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]')
134 FROM t1
135 GROUP BY a";
136 EXECUTE p1;
137 EXECUTE p1;
138 deallocate prepare p1;
139
140
141 PREPARE p4 FROM
142 "SELECT JSON_MERGE_PRESERVE(JSON_OBJECTAGG(k, b), '[true, false]') FROM t1";
143 EXECUTE p4;
144 EXECUTE p4;
145 deallocate prepare p4;
146
147 ANALYZE TABLE t1;
148
149 EXPLAIN SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
150 EXPLAIN SELECT JSON_OBJECTAGG(k, b) FROM t1;
151
152 --skip_if_hypergraph # Depends on the query plan.
153 EXPLAIN FORMAT=json SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
154 --skip_if_hypergraph # Depends on the query plan.
155 EXPLAIN FORMAT=json SELECT JSON_OBJECTAGG(k, b) FROM t1;
156
157 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a WITH ROLLUP;
158 SELECT a, JSON_OBJECTAGG(k, b) as jobject
159 FROM t1
160 GROUP BY a
161 HAVING jobject = JSON_OBJECT(3, "doi");
162
163 --echo #
164 --echo # NULL values.
165 --echo #
166
167 SELECT a, JSON_ARRAYAGG(null) FROM t1 GROUP BY a;
168 SELECT JSON_ARRAYAGG(null) FROM t1;
169
170 SELECT a, JSON_OBJECTAGG(k, null) FROM t1 GROUP BY a;
171 SELECT JSON_OBJECTAGG(k, null) FROM t1;
172
173
174 --error ER_JSON_DOCUMENT_NULL_KEY
175 SELECT a, JSON_OBJECTAGG(null, b) FROM t1 GROUP BY a;
176
177 --error ER_JSON_DOCUMENT_NULL_KEY
178 SELECT JSON_OBJECTAGG(null, b) FROM t1;
179
180 --echo #
181 --echo # Coverage test for fix_fields: Disable_semijoin_flattening.
182 --echo #
183
184 CREATE TABLE t(a INT);
185 ANALYZE TABLE t1;
186 --skip_if_hypergraph # Depends on the query plan.
187 EXPLAIN format=json SELECT (SELECT 1 FROM t WHERE JSON_ARRAYAGG(1 IN (SELECT 1 FROM t)));
188 SELECT (SELECT 1 FROM t WHERE JSON_ARRAYAGG(1 IN (SELECT 1 FROM t)));
189 DROP TABLE t;
190
191 --echo #
192 --echo # Coverage test for fix_fields: check_cols.
193 --echo #
194
195 --error ER_OPERAND_COLUMNS
196 SELECT JSON_ARRAYAGG((SELECT 1, 1));
197
198 --echo #
199 --echo # Coverage test for fix_fields: resolve_type.
200 --echo #
201
202 CREATE TABLE t2(gid int, a int);
203 --error ER_WRONG_ARGUMENTS
204 SELECT JSON_ARRAYAGG(ST_PointFromText('POINT(10 10)')) FROM t2;
205
206 --echo #
207 --echo # Coverage test for fix_fields: check_sum_func.
208 --echo #
209
210 --error ER_INVALID_GROUP_FUNC_USE
211 SELECT (SELECT JSON_ARRAYAGG(COUNT(a)) FROM t2) FROM t1;
212
213 DROP TABLE t2;
214
215 --echo #
216 --echo # Empty table.
217 --echo #
218
219 TRUNCATE TABLE t1;
220
221 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
222 SELECT JSON_ARRAYAGG(b) FROM t1;
223 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
224 SELECT JSON_OBJECTAGG(k, b) FROM t1;
225
226 --echo #
227 --echo # Tests for max_allowed_packet.
228 --echo #
229
230 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, x INT);
231 INSERT INTO t(x) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
232 INSERT INTO t(x) SELECT t1.x from t t1, t t2, t t3;
233
234 SET GLOBAL net_buffer_length = 1024;
235 SET GLOBAL max_allowed_packet = 1024;
236 CONNECT (con1,localhost,root,,);
237 SELECT JSON_ARRAYAGG(x) FROM t;
238 SELECT JSON_OBJECTAGG(id, x) FROM t;
239 SELECT id % 2 AS i, JSON_ARRAYAGG(x) FROM t GROUP BY i;
240 SELECT id % 2 AS i, JSON_OBJECTAGG(id, x) FROM t GROUP BY i;
241 CONNECTION default;
242 DISCONNECT con1;
243 SET GLOBAL max_allowed_packet = default;
244 SET GLOBAL net_buffer_length = default;
245
246 DROP TABLE t;
247
248 --echo #
249 --echo # Cleanup test.
250 --echo #
251
252 DROP TABLE t1;
253
254
255 --echo #
256 --echo # Bug #24368053
257 --echo # WL#7987: ASSERTION `!TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TABLE-> ...
258 --echo #
259
260 CREATE TABLE C(col_int int);
261 CREATE TABLE CC(col_int int);
262 INSERT INTO CC VALUES (1),(2),(3);
263
264 SELECT JSON_OBJECTAGG(table1.`col_int` ,table1.`col_int`) AS field2,
265 (SELECT JSON_ARRAYAGG(SUBQUERY2_t1.`col_int`)
266 FROM CC AS SUBQUERY2_t1
267 WHERE SUBQUERY2_t1.`col_int` <> table1.`col_int`) AS field5
268 FROM (CC AS table1)
269 WHERE (table1.`col_int` <> ALL (SELECT SUBQUERY4_t1.`col_int`
270 FROM (CC AS SUBQUERY4_t1 STRAIGHT_JOIN C)))
271 GROUP BY
272 field5;
273
274 DROP TABLE C;
275 DROP TABLE CC;
276
277 --echo #
278 --echo # Bug #24367384
279 --echo # WL#7987: INNODB: ASSERTION FAILURE: ROW0SEL.CC:2558:FIELD->PREFIX_LEN > 0 ...
280 --echo #
281 CREATE TABLE BB (pk INT AUTO_INCREMENT PRIMARY KEY, col_varchar_key VARCHAR(1));
282 INSERT INTO BB VALUES(1,'a');
283
284 --skip_if_hypergraph # Different warnings.
285 SELECT (SELECT JSON_ARRAYAGG(`pk`) FROM BB as t1
286 WHERE t1.`col_varchar_key` <> t2.`col_varchar_key`) AS field2
287 FROM BB as t2
288 GROUP BY field2;
289
290 --skip_if_hypergraph # Different warnings.
291 SELECT (SELECT JSON_ARRAYAGG(`pk`) FROM BB as t1
292 WHERE t1.`col_varchar_key` = t2.`col_varchar_key`) AS field2
293 FROM BB as t2
294 GROUP BY field2;
295
296 DROP TABLE BB;
297
298 --echo #
299 --echo # Bug #24365264
300 --echo # WL#7987: SIG 11 IN ITEM::MARK_FIELD_IN_MAP|SQL/ITEM.H
301 --echo #
302
303 CREATE TABLE C (col_int int);
304 INSERT INTO C VALUES (1);
305
306 SELECT *
307 FROM C WHERE col_int < (SELECT JSON_ARRAYAGG(col_int) FROM C )
308 ORDER BY col_int ;
309
310 DROP TABLE C;
311
312
313 --echo #
314 --echo # Bug #24366341
315 --echo # WL#7987: SIG 6 IN JSON_WRAPPER::TYPE|SQL/JSON_DOM.CC
316 --echo #
317
318 CREATE TABLE CC(col_varchar_key varchar(1));
319 INSERT INTO CC VALUES ('a');
320
321 SELECT JSON_ARRAYAGG(col_varchar_key) AS field1 FROM CC HAVING field1 > 9;
322
323 SELECT JSON_OBJECTAGG(col_varchar_key, col_varchar_key) AS field1 FROM CC
324 HAVING (field1 <> 'a' AND field1 != 'e');
325
326 DROP TABLE CC;
327
328 --echo #
329 --echo # with ROLLUP + two/three groups
330 --echo #
331
332 CREATE TABLE tg (g1 int, g2 int, k int, b VARCHAR(10));
333 INSERT INTO tg VALUES
334 (1, 1, 1, "alfa"),
335 (1, 2, 2, null),
336 (2, 3, 3, "doi"),
337 (1, 1, 4, "unu"),
338 (3, 2, 5, "trei"),
339 (4, 3, 6, null),
340 (4, 1, 7, null),
341 (1, 2, 8, "one");
342
343
344 SELECT g1, g2, JSON_ARRAYAGG(g2) FROM tg GROUP BY g1, g2 with rollup;
345 SELECT g1, g2, JSON_OBJECTAGG(k, g1) FROM tg GROUP BY g1, g2 with rollup;
346
347
348 CREATE TABLE tg3 (g1 int, g2 int, g3 int, k int, b VARCHAR(10));
349 INSERT INTO tg3 VALUES
350 (1, 1, 1, 1, "1.1.1"),
351 (1, 1, 2, 2, "1.1.2"),
352 (1, 1, 3, 3, "1.1.3"),
353 (1, 2, 1, 4, "1.2.1"),
354 (1, 2, 2, 5, "1.2.2"),
355 (1, 2, 3, 6, "1.2.3"),
356 (1, 3, 1, 7, "1.3.1"),
357 (1, 3, 2, 8, "1.3.2"),
358 (1, 3, 3, 9, "1.3.3"),
359 (2, 1, 1, 10, "2.1.1"),
360 (2, 1, 2, 11, "2.1.2"),
361 (2, 1, 3, 12, "2.1.3"),
362 (2, 2, 1, 13, "2.2.1"),
363 (2, 2, 2, 14, "2.2.2"),
364 (2, 2, 3, 15, "2.2.3"),
365 (2, 3, 1, 16, "2.3.1"),
366 (2, 3, 2, 17, "2.3.2"),
367 (2, 3, 3, 18, "2.3.3"),
368 (3, 1, 1, 19, "3.1.1"),
369 (3, 1, 2, 20, "3.1.2"),
370 (3, 1, 3, 21, "3.1.3"),
371 (3, 2, 1, 22, "3.2.1"),
372 (3, 2, 2, 23, "3.2.2"),
373 (3, 2, 3, 24, "3.2.3"),
374 (3, 3, 1, 25, "3.3.1"),
375 (3, 3, 2, 26, "3.3.2"),
376 (3, 3, 3, 27, "3.3.3");
377
378
379 SELECT g1, g2, g3, JSON_ARRAYAGG(b) FROM tg3 GROUP BY g1, g2, g3 with rollup;
380 SELECT g1, g2, g3, JSON_OBJECTAGG(k, b) FROM tg3 GROUP BY g1, g2, g3 with rollup;
381
382
383 DROP TABLE tg;
384 DROP TABLE tg3;
385
386 --echo #
387 --echo # Tests duplicates for JSON_OBJECTAGG
388 --echo #
389
390 CREATE TABLE t1 (a int, k int, b VARCHAR(10));
391 INSERT INTO t1 VALUES
392 (1, 1, "1.1"),
393 (1, 1, "1.2"),
394 (1, 1, "1.3"),
395 (2, 2, "2.1"),
396 (2, 2, "2.2"),
397 (2, 2, "2.3");
398
399 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
400 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
401
402 DROP TABLE t1;
403
404 --echo #
405 --echo # Tests with ORDER BY, DISTINCT
406 --echo #
407
408 CREATE TABLE t1 (a int, k int, b VARCHAR(10));
409 INSERT INTO t1 VALUES
410 (2, 8, "2.3"),
411 (1, 7, "1.1"),
412 (3, 6, "3.2"),
413 (2, 5, "2.2"),
414 (3, 9, "3.1"),
415 (1, 4, "1.2"),
416 (3, 3, "3.3"),
417 (2, 2, "2.1"),
418 (1, 1, "1.3");
419
420 SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
421 SELECT DISTINCT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
422 SELECT DISTINCT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a ORDER BY a ASC;
423 SELECT DISTINCT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a ORDER BY a DESC;
424
425 SELECT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
426 SELECT DISTINCT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a;
427 SELECT DISTINCT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a ORDER BY a ASC;
428 SELECT DISTINCT a, JSON_OBJECTAGG(k, b) FROM t1 GROUP BY a ORDER BY a DESC;
429
430 DROP TABLE t1;
431
432 --echo #
433 --echo # Tests with joins
434 --echo #
435
436 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, t1 INT, t2 INT);
437 CREATE TABLE p(id INT PRIMARY KEY AUTO_INCREMENT, p1 INT, p2 INT);
438
439 INSERT INTO t(t1, t2) VALUES (1, 1), (2, 1), (3,3), (1, 4);
440 INSERT INTO p(p1, p2) VALUES (2, 1), (1, 1), (3,3), (2, 4);
441
442 FLUSH STATUS;
443 SHOW SESSION STATUS LIKE 'Handler_update%';
444 #Since the JSON_*AGG does not have the ORDER BY clause order is not predictable
445 disable_result_log;
446 SELECT JSON_ARRAYAGG(t2) FROM t join p on t.t1=p.p1;
447 enable_result_log;
448 SHOW SESSION STATUS LIKE 'Handler_update%';
449 disable_result_log;
450 SELECT t1, JSON_ARRAYAGG(t2) FROM t join p on t.t1=p.p1 group by t1;
451 enable_result_log;
452 SHOW SESSION STATUS LIKE 'Handler_update%';
453
454 # Subquery in ORDER BY with outer reference
455 SELECT (SELECT 1 AS foo ORDER BY JSON_ARRAYAGG(t2)) AS x FROM t;
456 --skip_if_hypergraph # Different warnings.
457 SELECT t1 FROM t ORDER BY (SELECT JSON_ARRAYAGG(t1) FROM t AS t2);
458 SELECT JSON_ARRAYAGG(t1) FROM t
459 ORDER BY (SELECT JSON_ARRAYAGG(t1) FROM t AS t2);
460
461 SELECT (SELECT JSON_ARRAYAGG(t1_outer.t1) FROM t AS t1_inner LIMIT 1) as f
462 FROM t AS t1_outer GROUP BY t1_outer.t2;
463
464 DROP TABLE t;
465 DROP TABLE p;
466
467 --echo #
468 --echo # Tests with JSON
469 --echo #
470
471 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, k INT, j JSON);
472 INSERT INTO t(k, j) VALUES
473 (1, '[1,2,3,4]'),
474 (2, '{"prop1": 1}'),
475 (1, '[3]'),
476 (2, '{"prop2": 2, "prop10": 10}'),
477 (1, '[99]');
478
479
480 SELECT k, JSON_ARRAYAGG(j) FROM t GROUP BY k;
481 SELECT k, JSON_OBJECTAGG(id, j) FROM t GROUP BY k;
482
483 DROP TABLE t;
484
485 --echo #
486 --echo # Coverage tests for val_* functions
487 --echo #
488
489 CREATE TABLE t2(gid int, a int);
490 INSERT INTO t2(gid, a) VALUES (1, 1), (1, 2), (2, 4), (2, 8);
491
492 SELECT gid, 1.0 * JSON_ARRAYAGG(a) FROM t2 GROUP BY gid;
493 SELECT gid, 0x30 << JSON_ARRAYAGG(a) FROM t2 GROUP BY gid;
494 SELECT gid, DATE_ADD(JSON_ARRAYAGG(a), INTERVAL 31 DAY)
495 FROM t2 GROUP BY gid;
496 SELECT gid, ADDTIME(JSON_ARRAYAGG(a), '02:00:00.999998')
497 FROM t2 GROUP BY gid;
498 SELECT gid, SEC_TO_TIME(JSON_ARRAYAGG(a)) FROM t2 GROUP BY gid;
499
500 TRUNCATE TABLE t2;
501
502 SELECT 1.0 * JSON_ARRAYAGG(a) FROM t2;
503 SELECT 0x30 << JSON_ARRAYAGG(a) FROM t2;
504 SELECT DATE_ADD(JSON_ARRAYAGG(a), INTERVAL 31 DAY) FROM t2;
505 SELECT ADDTIME(JSON_ARRAYAGG(a), '02:00:00.999998') FROM t2;
506 SELECT SEC_TO_TIME(JSON_ARRAYAGG(a)) FROM t2;
507
508 DROP TABLE t2;
509
510 --echo #
511 --echo # Bug#34548259 confusing result about json value
512 --echo #
513 CREATE TABLE t1 (a JSON);
514 INSERT INTO t1 SELECT JSON_OBJECTAGG('a', b'01010101');
515 SELECT JSON_TYPE(JSON_EXTRACT(a, '$.a')) FROM t1;
516 SELECT JSON_TYPE(JSON_EXTRACT(JSON_OBJECT('a', b'01010101'), '$.a'));
517 DROP TABLE t1;