Debian Codegraph @

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

raw
1 set default_storage_engine=innodb;
2 # ----------------------------------------------------------------------
3 # Test of aggregate function GROUP_CONCAT
4 # ----------------------------------------------------------------------
5 create table t2(j json);
6 insert into t2 values (json_array(ST_GeomFromText('LineString(0 0, 1 1, 2 2)')));
7 select group_concat(j order by j) from t2;
8 group_concat(j order by j)
9 [{"type": "LineString", "coordinates": [[0.0, 0.0], [1.0, 1.0], [2.0, 2.0]]}]
10 drop table t2;
11 create table t (c varchar(20)) charset utf8mb4;
12 insert into t values ('\\');
13 select char_length(c) from t;
14 char_length(c)
15 1
16 insert into t values (X'0C');
17 select sum(char_length(c)) from t;
18 sum(char_length(c))
19 2
20 insert into t values ('"');
21 select sum(char_length(c)) from t;
22 sum(char_length(c))
23 3
24 insert into t values ('\a');
25 select sum(char_length(c)) from t;
26 sum(char_length(c))
27 4
28 insert into t values ('\b');
29 select sum(char_length(c)) from t;
30 sum(char_length(c))
31 5
32 insert into t values ('\t');
33 select sum(char_length(c)) from t;
34 sum(char_length(c))
35 6
36 insert into t values ('\n');
37 select sum(char_length(c)) from t;
38 sum(char_length(c))
39 7
40 insert into t values ('\r');
41 select sum(char_length(c)) from t;
42 sum(char_length(c))
43 8
44 insert into t values (X'10');
45 select sum(char_length(c)) from t;
46 sum(char_length(c))
47 9
48 # '\b' and X'10' order equal in the default collation, so order on
49 # hex(c) as a tie-breaker to get stable results.
50 select group_concat(c order by c, hex(c)) from t;
51 group_concat(c order by c, hex(c))
52 ,, ,
53 , , ,",\,a
54 select char_length(group_concat(c)) from t;
55 char_length(group_concat(c))
56 17
57 select json_quote(group_concat(c order by c, hex(c))) from t;
58 json_quote(group_concat(c order by c, hex(c)))
59 "\b,\u0010,\t,\n,\f,\r,\",\\,a"
60 select char_length(json_quote(group_concat(c))) from t;
61 char_length(json_quote(group_concat(c)))
62 31
63 select convert(group_concat(c) using utf8mb4) =
64 json_unquote(group_concat(c)) as should_be_equal from t;
65 should_be_equal
66 1
67 delete from t;
68 alter table t add column (j json);
69 insert into t values (NULL, NULL);
70 select json_quote(c), json_quote(j) from t;
71 json_quote(c) json_quote(j)
72 NULL NULL
73 select json_unquote(c), json_unquote(j) from t;
74 json_unquote(c) json_unquote(j)
75 NULL NULL
76 drop table t;
77 create table t(j json, c varchar(20)) charset utf8mb4;
78 show create table t;
79 Table Create Table
80 t CREATE TABLE `t` (
81 `j` json DEFAULT NULL,
82 `c` varchar(20) DEFAULT NULL
83 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
84 insert into t values (cast('[1,2,3]' as json), '[a,b,c]');
85 insert into t values (cast(7 as json), '7'), (cast(2 as json), '2');
86 insert into t values (cast(7 as json), '7'), (cast(2 as json), '2');
87 select group_concat(j), group_concat(distinct j), group_concat(c) from t;
88 group_concat(j) group_concat(distinct j) group_concat(c)
89 [1, 2, 3],7,2,7,2 [1, 2, 3],2,7 [a,b,c],7,2,7,2
90 select group_concat(j order by j), group_concat(distinct j order by j), group_concat(c order by c) from t;
91 group_concat(j order by j) group_concat(distinct j order by j) group_concat(c order by c)
92 [1, 2, 3],2,2,7,7 [1, 2, 3],2,7 [a,b,c],2,2,7,7
93 insert into t values (NULL, NULL);
94 select group_concat(j), group_concat(distinct j), group_concat(c) from t;
95 group_concat(j) group_concat(distinct j) group_concat(c)
96 [1, 2, 3],7,2,7,2 [1, 2, 3],2,7 [a,b,c],7,2,7,2
97 #
98 # Bug#22992666 NULL VALUE IN JSON COLUMN CORRUPTS GROUP_CONCAT
99 # WITH ORDER BY CLAUSE
100 #
101 SELECT GROUP_CONCAT(j ORDER BY j) AS c1,
102 GROUP_CONCAT(DISTINCT j ORDER BY j) AS c2,
103 GROUP_CONCAT(c ORDER BY c) AS c3
104 FROM t;
105 c1 c2 c3
106 [1, 2, 3],2,2,7,7 [1, 2, 3],2,7 [a,b,c],2,2,7,7
107 drop table t;