github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/function_group_concat.sql (about)

     1  -- @suit
     2  -- @cASe
     3  -- @test function group_concat(str,delim,count)
     4  -- @label:bvt
     5  
     6  
     7  -- @suite
     8  -- @setup
     9  DROP TABLE IF EXISTS group_concat_01;
    10  
    11  CREATE TABLE group_concat_01 (grp int,
    12                                a bigint unsigned,
    13                                c char(10) NOT NULL,
    14                                d char(10) NOT NULL);
    15  
    16  INSERT INTO group_concat_01 VALUES (1,1,'a','a');
    17  INSERT INTO group_concat_01 VALUES (2,2,'b','a');
    18  INSERT INTO group_concat_01 VALUES (2,3,'c','b');
    19  INSERT INTO group_concat_01 VALUES (3,4,'E','a');
    20  INSERT INTO group_concat_01 VALUES (3,5,'C','b');
    21  INSERT INTO group_concat_01 VALUES (3,6,'D','b');
    22  INSERT INTO group_concat_01 VALUES (3,7,'d','d');
    23  INSERT INTO group_concat_01 VALUES (3,8,'d','d');
    24  INSERT INTO group_concat_01 VALUES (3,9,'D','c');
    25  
    26  
    27  -- Test of MO simple request
    28  SELECT grp,group_concat(c) FROM group_concat_01 GROUP BY grp;
    29  SELECT grp,group_concat(c) FROM group_concat_01 GROUP BY grp;
    30  SELECT grp,group_concat(a,c) FROM group_concat_01 GROUP BY grp;
    31  SELECT grp,group_concat("(",a,":",c,")") FROM group_concat_01 GROUP BY grp;
    32  SELECT grp,group_concat(NULL) FROM group_concat_01 GROUP BY grp;
    33  SELECT grp,group_concat(a,NULL) FROM group_concat_01 GROUP BY grp;
    34  SELECT group_concat(NULL) FROM group_concat_01;
    35  SELECT group_concat(a,c,NULL) FROM group_concat_01;
    36  SELECT group_concat(a,NULL) FROM group_concat_01;
    37  
    38  
    39  -- Test of MO with options
    40  SELECT grp,group_concat(c separator ",") FROM group_concat_01 GROUP BY grp;
    41  SELECT grp,group_concat(c separator "---->") FROM group_concat_01 GROUP BY grp;
    42  SELECT grp,group_concat(c ORDER BY c) FROM group_concat_01 GROUP BY grp;
    43  SELECT grp,group_concat(c ORDER BY c DESC) FROM group_concat_01 GROUP BY grp;
    44  SELECT grp,group_concat(d ORDER BY a) FROM group_concat_01 GROUP BY grp;
    45  SELECT grp,group_concat(d ORDER BY a DESC) FROM group_concat_01 GROUP BY grp;
    46  SELECT grp,group_concat(c ORDER BY 1) FROM group_concat_01 GROUP BY grp;
    47  SELECT grp,group_concat(c ORDER BY c separator ",") FROM group_concat_01 GROUP BY grp;
    48  SELECT grp,group_concat(c ORDER BY c DESC separator ",") FROM group_concat_01 GROUP BY grp;
    49  SELECT grp,group_concat(c ORDER BY grp DESC) FROM group_concat_01 GROUP BY grp ORDER BY grp;
    50  
    51  
    52  -- Test transfer to real values
    53  SELECT grp, group_concat(a separator "")+0 FROM group_concat_01 GROUP BY grp;
    54  SELECT grp, group_concat(a separator "")+0.0 FROM group_concat_01 GROUP BY grp;
    55  SELECT grp, ROUND(group_concat(a separator "")) FROM group_concat_01 GROUP BY grp;
    56  
    57  
    58  -- Test errors
    59  SELECT group_concat(sum(c)) FROM group_concat_02 group by grp;
    60  SELECT grp,group_concat(c order by 2) FROM group_concat_02 group by grp;
    61  
    62  
    63  -- @suite
    64  -- @setup
    65  DROP TABLE IF EXISTS group_concat_03;
    66  DROP TABLE IF EXISTS group_concat_04;
    67  CREATE TABLE group_concat_03 ( URL_ID int(11), URL varchar(80));
    68  CREATE TABLE group_concat_04 ( REQ_ID int(11), URL_ID int(11));
    69  
    70  INSERT INTO group_concat_03 values (4,'www.host.com');
    71  INSERT INTO group_concat_03 values (5,'www.google.com');
    72  INSERT INTO group_concat_03 values (5,'www.help.com');
    73  INSERT INTO group_concat_04 values (1,4);
    74  INSERT INTO group_concat_04 values (5,4);
    75  INSERT INTO group_concat_04 values (5,5);
    76  
    77  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;
    78  
    79  
    80  -- @suite
    81  -- @setup
    82  DROP TABLE IF EXISTS group_concat_05;
    83  DROP TABLE IF EXISTS group_concat_06;
    84  CREATE TABLE group_concat_05(id int);
    85  CREATE TABLE group_concat_06(id int);
    86  INSERT INTO group_concat_05 values(0),(1);
    87  
    88  -- check zero rows
    89  SELECT group_concat(group_concat_05.id) FROM group_concat_05,group_concat_06;
    90  
    91  
    92  -- @suite
    93  -- @setup
    94  DROP TABLE IF EXISTS group_concat_07;
    95  CREATE TABLE group_concat_07(bar varchar(32));
    96  INSERT INTO group_concat_07 values('tesgroup_concat_08');
    97  INSERT INTO group_concat_07 values('tesgroup_concat_09');
    98  SELECT group_concat(bar order by concat(bar,bar)) FROM group_concat_07;
    99  SELECT group_concat(bar order by concat(bar,bar) ASC) FROM group_concat_07;
   100  
   101  -- Abnormal test
   102  SELECT bar FROM group_concat_07 HAVING group_concat(bar)='';
   103  SELECT bar FROM group_concat_07 HAVING instr(group_concat(bar), "test") > 0;
   104  SELECT bar FROM group_concat_07 HAVING instr(group_concat(bar order by concat(bar,bar) desc), "tesgroup_concat_09,tesgroup_concat_08") > 0;
   105  
   106  
   107  -- @suite
   108  -- @setup
   109  DROP TABLE IF EXISTS group_concat_08;
   110  DROP TABLE IF EXISTS group_concat_09;
   111  
   112  CREATE TABLE group_concat_08 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL);
   113  INSERT INTO group_concat_08 VALUES (1, 1);
   114  INSERT INTO group_concat_08 VALUES (1, 2);
   115  INSERT INTO group_concat_08 VALUES (1, 3);
   116  INSERT INTO group_concat_08 VALUES (1, 4);
   117  INSERT INTO group_concat_08 VALUES (1, 5);
   118  INSERT INTO group_concat_08 VALUES (2, 1);
   119  INSERT INTO group_concat_08 VALUES (2, 2);
   120  INSERT INTO group_concat_08 VALUES (2, 3);
   121  
   122  CREATE TABLE group_concat_09 (id1 tinyint(4) NOT NULL);
   123  INSERT INTO group_concat_09 VALUES (1);
   124  INSERT INTO group_concat_09 VALUES (2);
   125  INSERT INTO group_concat_09 VALUES (3);
   126  INSERT INTO group_concat_09 VALUES (4);
   127  INSERT INTO group_concat_09 VALUES (5);
   128  
   129  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;
   130  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;
   131  
   132  
   133  -- @suite
   134  -- @setup
   135  DROP TABLE IF EXISTS group_concat_10;
   136  CREATE TABLE group_concat_10 (s1 char(10), s2 int not null);
   137  INSERT INTO group_concat_10 values ('a',2);
   138  INSERT INTO group_concat_10 values ('b',2);
   139  INSERT INTO group_concat_10 values ('c',1);
   140  INSERT INTO group_concat_10 values ('a',3);
   141  INSERT INTO group_concat_10 values ('b',4);
   142  INSERT INTO group_concat_10 values ('c',4);
   143  
   144  -- distinct
   145  SELECT group_concat(distinct s1) FROM group_concat_10;
   146  
   147  
   148  -- @suite
   149  -- @setup
   150  DROP TABLE IF EXISTS group_concat_11;
   151  DROP TABLE IF EXISTS group_concat_12;
   152  CREATE TABLE group_concat_11 (a int, c int);
   153  INSERT INTO group_concat_11 values (1, 2);
   154  INSERT INTO group_concat_11 values (2, 3);
   155  INSERT INTO group_concat_11 values (2, 4);
   156  INSERT INTO group_concat_11 values (3, 5);
   157  
   158  CREATE TABLE group_concat_12 (a int, c int);
   159  INSERT INTO group_concat_12 values (1, 5);
   160  INSERT INTO group_concat_12 values (2, 4);
   161  INSERT INTO group_concat_12 values (3, 3);
   162  INSERT INTO group_concat_12 values (3, 3);
   163  
   164  -- subqueris
   165  SELECT group_concat(c) FROM group_concat_11;
   166  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;
   167  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;
   168  
   169  
   170  -- @suite
   171  -- @setup
   172  DROP TABLE IF EXISTS group_concat_13;
   173  DROP TABLE IF EXISTS group_concat_14;
   174  CREATE TABLE group_concat_13 ( a int );
   175  CREATE TABLE group_concat_14 ( a int );
   176  INSERT INTO group_concat_13 VALUES (1), (2);
   177  INSERT INTO group_concat_14 VALUES (1), (2);
   178  
   179  -- union
   180  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;
   181  
   182  
   183  -- @suite
   184  -- @setup
   185  DROP TABLE IF EXISTS group_concat_15;
   186  CREATE TABLE group_concat_15 (a int, b text);
   187  INSERT INTO group_concat_15 values (1, 'bb');
   188  INSERT INTO group_concat_15 values (1, 'ccc');
   189  INSERT INTO group_concat_15 values (1, 'a');
   190  INSERT INTO group_concat_15 values (1, 'bb');
   191  INSERT INTO group_concat_15 values (1, 'ccc');
   192  INSERT INTO group_concat_15 values (2, 'BB');
   193  INSERT INTO group_concat_15 values (2, 'CCC');
   194  INSERT INTO group_concat_15 values (2, 'A');
   195  INSERT INTO group_concat_15 values (2, 'BB');
   196  INSERT INTO group_concat_15 values (2, 'CCC');
   197  
   198  -- join test
   199  SELECT group_concat(b) FROM group_concat_15 group by a;
   200  SELECT group_concat(distinct b) FROM group_concat_15 group by a;
   201  SELECT group_concat(b) FROM group_concat_15 group by a;
   202  SELECT group_concat(distinct b) FROM group_concat_15 group by a;
   203  
   204  
   205  -- @suite
   206  -- @setup
   207  DROP TABLE IF EXISTS group_concat_16;
   208  DROP TABLE IF EXISTS group_concat_17;
   209  CREATE TABLE group_concat_16 (
   210                                   aID smallint(5) unsigned NOT NULL auto_increment,
   211                                   sometitle varchar(255) NOT NULL default '',
   212                                   bID smallint(5) unsigned NOT NULL,
   213                                   PRIMARY KEY  (aID),
   214                                   UNIQUE KEY sometitle (sometitle)
   215  );
   216  INSERT INTO group_concat_16 SET sometitle = 'title1', bID = 1;
   217  INSERT INTO group_concat_16 SET sometitle = 'title2', bID = 1;
   218  
   219  CREATE TABLE group_concat_17 (
   220                                   bID smallint(5) unsigned NOT NULL auto_increment,
   221                                   somename varchar(255) NOT NULL default '',
   222                                   PRIMARY KEY  (bID),
   223                                   UNIQUE KEY somename (somename)
   224  );
   225  INSERT INTO group_concat_17 SET somename = 'test';
   226  
   227  -- join
   228  SELECT COUNT(*), GROUP_CONCAT(DISTINCT group_concat_17.somename SEPARATOR ' |')
   229  FROM group_concat_16 JOIN group_concat_17 ON group_concat_16.bID = group_concat_17.bID;
   230  INSERT INTO group_concat_17 SET somename = 'tesgroup_concat_17';
   231  SELECT COUNT(*), GROUP_CONCAT(DISTINCT group_concat_17.somename SEPARATOR ' |')
   232  FROM group_concat_16 JOIN group_concat_17 ON group_concat_16.bID = group_concat_17.bID;
   233  DELETE FROM group_concat_17 WHERE somename = 'tesgroup_concat_17';
   234  SELECT COUNT(*), GROUP_CONCAT(DISTINCT group_concat_17.somename SEPARATOR ' |')
   235  FROM group_concat_16 JOIN group_concat_17 ON group_concat_16.bID = group_concat_17.bID;