github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/function_group_concat.result (about) 1 DROP TABLE IF EXISTS group_concat_01; 2 CREATE TABLE group_concat_01 (grp int, 3 a bigint unsigned, 4 c char(10) NOT NULL, 5 d char(10) NOT NULL); 6 INSERT INTO group_concat_01 VALUES (1,1,'a','a'); 7 INSERT INTO group_concat_01 VALUES (2,2,'b','a'); 8 INSERT INTO group_concat_01 VALUES (2,3,'c','b'); 9 INSERT INTO group_concat_01 VALUES (3,4,'E','a'); 10 INSERT INTO group_concat_01 VALUES (3,5,'C','b'); 11 INSERT INTO group_concat_01 VALUES (3,6,'D','b'); 12 INSERT INTO group_concat_01 VALUES (3,7,'d','d'); 13 INSERT INTO group_concat_01 VALUES (3,8,'d','d'); 14 INSERT INTO group_concat_01 VALUES (3,9,'D','c'); 15 SELECT grp,group_concat(c) FROM group_concat_01 GROUP BY grp; 16 grp group_concat(c, ,) 17 1 a 18 2 b,c 19 3 E,C,D,d,d,D 20 SELECT grp,group_concat(c) FROM group_concat_01 GROUP BY grp; 21 grp group_concat(c, ,) 22 1 a 23 2 b,c 24 3 E,C,D,d,d,D 25 SELECT grp,group_concat(a,c) FROM group_concat_01 GROUP BY grp; 26 grp group_concat(a, c, ,) 27 1 1a 28 2 2b,3c 29 3 4E,5C,6D,7d,8d,9D 30 SELECT grp,group_concat("(",a,":",c,")") FROM group_concat_01 GROUP BY grp; 31 grp group_concat((, a, :, c, ), ,) 32 1 (1:a) 33 2 (2:b),(3:c) 34 3 (4:E),(5:C),(6:D),(7:d),(8:d),(9:D) 35 SELECT grp,group_concat(NULL) FROM group_concat_01 GROUP BY grp; 36 grp group_concat(null, ,) 37 1 null 38 2 null 39 3 null 40 SELECT grp,group_concat(a,NULL) FROM group_concat_01 GROUP BY grp; 41 grp group_concat(a, null, ,) 42 1 null 43 2 null 44 3 null 45 SELECT group_concat(NULL) FROM group_concat_01; 46 group_concat(null, ,) 47 null 48 SELECT group_concat(a,c,NULL) FROM group_concat_01; 49 group_concat(a, c, null, ,) 50 null 51 SELECT group_concat(a,NULL) FROM group_concat_01; 52 group_concat(a, null, ,) 53 null 54 SELECT grp,group_concat(c separator ",") FROM group_concat_01 GROUP BY grp; 55 grp group_concat(c, ,) 56 1 a 57 2 b,c 58 3 E,C,D,d,d,D 59 SELECT grp,group_concat(c separator "---->") FROM group_concat_01 GROUP BY grp; 60 grp group_concat(c, ---->) 61 1 a 62 2 b---->c 63 3 E---->C---->D---->d---->d---->D 64 SELECT grp,group_concat(c ORDER BY c) FROM group_concat_01 GROUP BY grp; 65 grp group_concat(c, ,order by c) 66 1 a 67 2 b,c 68 3 C,D,D,E,d,d 69 SELECT grp,group_concat(c ORDER BY c DESC) FROM group_concat_01 GROUP BY grp; 70 grp group_concat(c, ,order by c desc) 71 1 a 72 2 c,b 73 3 d,d,E,D,D,C 74 SELECT grp,group_concat(d ORDER BY a) FROM group_concat_01 GROUP BY grp; 75 grp group_concat(d, ,order by a) 76 1 a 77 2 a,b 78 3 a,b,b,d,d,c 79 SELECT grp,group_concat(d ORDER BY a DESC) FROM group_concat_01 GROUP BY grp; 80 grp group_concat(d, ,order by a desc) 81 1 a 82 2 b,a 83 3 c,d,d,b,b,a 84 SELECT grp,group_concat(c ORDER BY 1) FROM group_concat_01 GROUP BY grp; 85 grp group_concat(c, ,order by 1) 86 1 a 87 2 b,c 88 3 C,D,D,E,d,d 89 SELECT grp,group_concat(c ORDER BY c separator ",") FROM group_concat_01 GROUP BY grp; 90 grp group_concat(c, ,order by c) 91 1 a 92 2 b,c 93 3 C,D,D,E,d,d 94 SELECT grp,group_concat(c ORDER BY c DESC separator ",") FROM group_concat_01 GROUP BY grp; 95 grp group_concat(c, ,order by c desc) 96 1 a 97 2 c,b 98 3 d,d,E,D,D,C 99 SELECT grp,group_concat(c ORDER BY grp DESC) FROM group_concat_01 GROUP BY grp ORDER BY grp; 100 grp group_concat(c, ,order by grp desc) 101 1 a 102 2 b,c 103 3 E,C,D,d,d,D 104 SELECT grp, group_concat(a separator "")+0 FROM group_concat_01 GROUP BY grp; 105 grp group_concat(a, ) + 0 106 1 1 107 2 23 108 3 456789 109 SELECT grp, group_concat(a separator "")+0.0 FROM group_concat_01 GROUP BY grp; 110 grp group_concat(a, ) + 0.0 111 1 1.0 112 2 23.0 113 3 456789.0 114 SELECT grp, ROUND(group_concat(a separator "")) FROM group_concat_01 GROUP BY grp; 115 grp round(group_concat(a, )) 116 1 1 117 2 23 118 3 456789 119 SELECT group_concat(sum(c)) FROM group_concat_02 group by grp; 120 SQL parser error: table "group_concat_02" does not exist 121 SELECT grp,group_concat(c order by 2) FROM group_concat_02 group by grp; 122 SQL parser error: table "group_concat_02" does not exist 123 DROP TABLE IF EXISTS group_concat_03; 124 DROP TABLE IF EXISTS group_concat_04; 125 CREATE TABLE group_concat_03 ( URL_ID int(11), URL varchar(80)); 126 CREATE TABLE group_concat_04 ( REQ_ID int(11), URL_ID int(11)); 127 INSERT INTO group_concat_03 values (4,'www.host.com'); 128 INSERT INTO group_concat_03 values (5,'www.google.com'); 129 INSERT INTO group_concat_03 values (5,'www.help.com'); 130 INSERT INTO group_concat_04 values (1,4); 131 INSERT INTO group_concat_04 values (5,4); 132 INSERT INTO group_concat_04 values (5,5); 133 SELECT REQ_ID, group_concat(URL) AS URL FROM group_concat_03, group_concat_04 WHERE group_concat_04.URL_ID = group_concat_03.URL_ID group by REQ_ID; 134 req_id URL 135 1 www.host.com 136 5 www.host.com,www.google.com,www.help.com 137 DROP TABLE IF EXISTS group_concat_05; 138 DROP TABLE IF EXISTS group_concat_06; 139 CREATE TABLE group_concat_05(id int); 140 CREATE TABLE group_concat_06(id int); 141 INSERT INTO group_concat_05 values(0),(1); 142 SELECT group_concat(group_concat_05.id) FROM group_concat_05,group_concat_06; 143 group_concat(group_concat_05.id, ,) 144 null 145 DROP TABLE IF EXISTS group_concat_07; 146 CREATE TABLE group_concat_07(bar varchar(32)); 147 INSERT INTO group_concat_07 values('tesgroup_concat_08'); 148 INSERT INTO group_concat_07 values('tesgroup_concat_09'); 149 SELECT group_concat(bar order by concat(bar,bar)) FROM group_concat_07; 150 group_concat(bar, ,order by concat(bar, bar)) 151 tesgroup_concat_08,tesgroup_concat_09 152 SELECT group_concat(bar order by concat(bar,bar) ASC) FROM group_concat_07; 153 group_concat(bar, ,order by concat(bar, bar) asc) 154 tesgroup_concat_08,tesgroup_concat_09 155 SELECT bar FROM group_concat_07 HAVING group_concat(bar)=''; 156 SQL syntax error: column "group_concat_07.bar" must appear in the GROUP BY clause or be used in an aggregate function 157 SELECT bar FROM group_concat_07 HAVING instr(group_concat(bar), "test") > 0; 158 SQL syntax error: column "group_concat_07.bar" must appear in the GROUP BY clause or be used in an aggregate function 159 SELECT bar FROM group_concat_07 HAVING instr(group_concat(bar order by concat(bar,bar) desc), "tesgroup_concat_09,tesgroup_concat_08") > 0; 160 SQL syntax error: column "group_concat_07.bar" must appear in the GROUP BY clause or be used in an aggregate function 161 DROP TABLE IF EXISTS group_concat_08; 162 DROP TABLE IF EXISTS group_concat_09; 163 CREATE TABLE group_concat_08 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL); 164 INSERT INTO group_concat_08 VALUES (1, 1); 165 INSERT INTO group_concat_08 VALUES (1, 2); 166 INSERT INTO group_concat_08 VALUES (1, 3); 167 INSERT INTO group_concat_08 VALUES (1, 4); 168 INSERT INTO group_concat_08 VALUES (1, 5); 169 INSERT INTO group_concat_08 VALUES (2, 1); 170 INSERT INTO group_concat_08 VALUES (2, 2); 171 INSERT INTO group_concat_08 VALUES (2, 3); 172 CREATE TABLE group_concat_09 (id1 tinyint(4) NOT NULL); 173 INSERT INTO group_concat_09 VALUES (1); 174 INSERT INTO group_concat_09 VALUES (2); 175 INSERT INTO group_concat_09 VALUES (3); 176 INSERT INTO group_concat_09 VALUES (4); 177 INSERT INTO group_concat_09 VALUES (5); 178 SELECT group_concat_08.id1, GROUP_CONCAT(group_concat_08.id2 ORDER BY group_concat_08.id2 ASC) AS concat_id FROM group_concat_08, group_concat_09 WHERE group_concat_08.id1 = group_concat_09.id1 AND group_concat_08.id1=1 GROUP BY group_concat_08.id1; 179 id1 concat_id 180 1 1,2,3,4,5 181 SELECT group_concat_08.id1, GROUP_CONCAT(group_concat_08.id2 ORDER BY group_concat_08.id2 ASC) AS concat_id FROM group_concat_08, group_concat_09 WHERE group_concat_08.id1 = group_concat_09.id1 GROUP BY group_concat_08.id1; 182 id1 concat_id 183 1 1,2,3,4,5 184 2 1,2,3 185 DROP TABLE IF EXISTS group_concat_10; 186 CREATE TABLE group_concat_10 (s1 char(10), s2 int not null); 187 INSERT INTO group_concat_10 values ('a',2); 188 INSERT INTO group_concat_10 values ('b',2); 189 INSERT INTO group_concat_10 values ('c',1); 190 INSERT INTO group_concat_10 values ('a',3); 191 INSERT INTO group_concat_10 values ('b',4); 192 INSERT INTO group_concat_10 values ('c',4); 193 SELECT group_concat(distinct s1) FROM group_concat_10; 194 group_concat(distinct s1, ,) 195 a,b,c 196 DROP TABLE IF EXISTS group_concat_11; 197 DROP TABLE IF EXISTS group_concat_12; 198 CREATE TABLE group_concat_11 (a int, c int); 199 INSERT INTO group_concat_11 values (1, 2); 200 INSERT INTO group_concat_11 values (2, 3); 201 INSERT INTO group_concat_11 values (2, 4); 202 INSERT INTO group_concat_11 values (3, 5); 203 CREATE TABLE group_concat_12 (a int, c int); 204 INSERT INTO group_concat_12 values (1, 5); 205 INSERT INTO group_concat_12 values (2, 4); 206 INSERT INTO group_concat_12 values (3, 3); 207 INSERT INTO group_concat_12 values (3, 3); 208 SELECT group_concat(c) FROM group_concat_11; 209 group_concat(c, ,) 210 2,3,4,5 211 SELECT group_concat_12.a,group_concat_12.c FROM group_concat_12,group_concat_11 where group_concat_12.a=group_concat_11.a; 212 a c 213 1 5 214 2 4 215 2 4 216 3 3 217 3 3 218 SELECT group_concat(c order by (SELECT mid(group_concat(c order by a),1,5) FROM group_concat_12 where group_concat_12.a=group_concat_11.a) desc) as grp FROM group_concat_11; 219 not supported: subquery in group_concat ORDER BY 220 DROP TABLE IF EXISTS group_concat_13; 221 DROP TABLE IF EXISTS group_concat_14; 222 CREATE TABLE group_concat_13 ( a int ); 223 CREATE TABLE group_concat_14 ( a int ); 224 INSERT INTO group_concat_13 VALUES (1), (2); 225 INSERT INTO group_concat_14 VALUES (1), (2); 226 SELECT GROUP_CONCAT(group_concat_13.a*group_concat_14.a ORDER BY group_concat_14.a) FROM group_concat_13, group_concat_14 GROUP BY group_concat_13.a; 227 group_concat(group_concat_13.a * group_concat_14.a, ,order by group_concat_14.a) 228 1,2 229 2,4 230 DROP TABLE IF EXISTS group_concat_15; 231 CREATE TABLE group_concat_15 (a int, b text); 232 INSERT INTO group_concat_15 values (1, 'bb'); 233 INSERT INTO group_concat_15 values (1, 'ccc'); 234 INSERT INTO group_concat_15 values (1, 'a'); 235 INSERT INTO group_concat_15 values (1, 'bb'); 236 INSERT INTO group_concat_15 values (1, 'ccc'); 237 INSERT INTO group_concat_15 values (2, 'BB'); 238 INSERT INTO group_concat_15 values (2, 'CCC'); 239 INSERT INTO group_concat_15 values (2, 'A'); 240 INSERT INTO group_concat_15 values (2, 'BB'); 241 INSERT INTO group_concat_15 values (2, 'CCC'); 242 SELECT group_concat(b) FROM group_concat_15 group by a; 243 group_concat(b, ,) 244 bb,ccc,a,bb,ccc 245 BB,CCC,A,BB,CCC 246 SELECT group_concat(distinct b) FROM group_concat_15 group by a; 247 group_concat(distinct b, ,) 248 bb,ccc,a 249 BB,CCC,A 250 SELECT group_concat(b) FROM group_concat_15 group by a; 251 group_concat(b, ,) 252 bb,ccc,a,bb,ccc 253 BB,CCC,A,BB,CCC 254 SELECT group_concat(distinct b) FROM group_concat_15 group by a; 255 group_concat(distinct b, ,) 256 bb,ccc,a 257 BB,CCC,A 258 DROP TABLE IF EXISTS group_concat_16; 259 DROP TABLE IF EXISTS group_concat_17; 260 CREATE TABLE group_concat_16 ( 261 aID smallint(5) unsigned NOT NULL auto_increment, 262 sometitle varchar(255) NOT NULL default '', 263 bID smallint(5) unsigned NOT NULL, 264 PRIMARY KEY (aID), 265 UNIQUE KEY sometitle (sometitle) 266 ); 267 INSERT INTO group_concat_16 SET sometitle = 'title1', bID = 1; 268 INSERT INTO group_concat_16 SET sometitle = 'title2', bID = 1; 269 CREATE TABLE group_concat_17 ( 270 bID smallint(5) unsigned NOT NULL auto_increment, 271 somename varchar(255) NOT NULL default '', 272 PRIMARY KEY (bID), 273 UNIQUE KEY somename (somename) 274 ); 275 INSERT INTO group_concat_17 SET somename = 'test'; 276 SELECT COUNT(*), GROUP_CONCAT(DISTINCT group_concat_17.somename SEPARATOR ' |') 277 FROM group_concat_16 JOIN group_concat_17 ON group_concat_16.bID = group_concat_17.bID; 278 count(*) group_concat(distinct group_concat_17.somename, |) 279 2 test 280 INSERT INTO group_concat_17 SET somename = 'tesgroup_concat_17'; 281 SELECT COUNT(*), GROUP_CONCAT(DISTINCT group_concat_17.somename SEPARATOR ' |') 282 FROM group_concat_16 JOIN group_concat_17 ON group_concat_16.bID = group_concat_17.bID; 283 count(*) group_concat(distinct group_concat_17.somename, |) 284 2 test 285 DELETE FROM group_concat_17 WHERE somename = 'tesgroup_concat_17'; 286 SELECT COUNT(*), GROUP_CONCAT(DISTINCT group_concat_17.somename SEPARATOR ' |') 287 FROM group_concat_16 JOIN group_concat_17 ON group_concat_16.bID = group_concat_17.bID; 288 count(*) group_concat(distinct group_concat_17.somename, |) 289 2 test