github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_concat_ws.result (about) 1 DROP TABLE IF EXISTS t1; 2 CREATE TABLE t1 ( number INT NOT NULL, alpha CHAR(6) NOT NULL ); 3 INSERT INTO t1 VALUES (1413006,'idlfmv'), 4 (1413065,'smpsfz'),(1413127,'sljrhx'),(1413304,'qerfnd'); 5 SELECT number, any_value(alpha), CONCAT_WS('<---->',number,any_value(alpha)) AS new 6 FROM t1 GROUP BY number; 7 number any_value(alpha) new 8 1413006 idlfmv 1413006<---->idlfmv 9 1413065 smpsfz 1413065<---->smpsfz 10 1413127 sljrhx 1413127<---->sljrhx 11 1413304 qerfnd 1413304<---->qerfnd 12 SELECT CONCAT_WS('<---->',number,alpha) AS new 13 FROM t1 GROUP BY CONCAT_WS('<---->',number,alpha) LIMIT 1; 14 new 15 1413006<---->idlfmv 16 SELECT any_value(number), any_value(alpha), CONCAT_WS('<->',any_value(number),any_value(alpha)) AS new 17 FROM t1 GROUP BY new LIMIT 1; 18 invalid input: GROUP BY clause cannot contain aggregate functions 19 SELECT any_value(number), any_value(alpha), CONCAT_WS('-',any_value(number),any_value(alpha),any_value(alpha),any_value(alpha),any_value(alpha),any_value(alpha),any_value(alpha),any_value(alpha)) AS new 20 FROM t1 GROUP BY new LIMIT 1; 21 invalid input: GROUP BY clause cannot contain aggregate functions 22 SELECT any_value(number), any_value(alpha), CONCAT_WS('<------------------>',any_value(number),any_value(alpha)) 23 FROM t1 GROUP BY CONCAT_WS('<------------------>',any_value(number),any_value(alpha)) LIMIT 1; 24 invalid input: GROUP BY clause cannot contain aggregate functions 25 drop table t1; 26 select concat_ws(', ','monty','was here','again'); 27 concat_ws(', ','monty','was here','again') 28 monty, was here, again 29 select concat_ws(',','',NULL,'a'); 30 concat_ws(',','',NULL,'a') 31 ,a 32 SELECT CONCAT_WS('"',CONCAT_WS('";"',space(60),space(60),space(60),space(100)), '"'); 33 CONCAT_WS('"',CONCAT_WS('";"',space(60),space(60),space(60),space(100)), '"') 34 ";" ";" ";" "" 35 CREATE TABLE t1(id int(11) NOT NULL,pc int(11) NOT NULL default 0,title varchar(20) default NULL,PRIMARY KEY (id)); 36 INSERT INTO t1 VALUES(1, 0, 'Main'),(2, 1, 'Toys'),(3, 1, 'Games'); 37 SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id LEFT JOIN t1 AS t3 ON t2.pc=t3.id; 38 id col1 39 1 Main 40 2 Main->Toys 41 3 Main->Games 42 SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id LEFT JOIN t1 AS t3 ON t2.pc=t3.id WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%'; 43 id col1 44 2 Main->Toys 45 DROP TABLE t1; 46 CREATE TABLE t1(trackid int(10) unsigned NOT NULL,trackname varchar(100) NOT NULL default '',PRIMARY KEY (trackid)); 47 CREATE TABLE t2(artistid int(10) unsigned NOT NULL,artistname varchar(100) NOT NULL default '',PRIMARY KEY (artistid)); 48 CREATE TABLE t3(trackid int(10) unsigned NOT NULL,artistid int(10) unsigned NOT NULL,PRIMARY KEY (trackid)); 49 INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York'); 50 INSERT INTO t2 VALUES (1, 'Vernon Duke'); 51 INSERT INTO t3 VALUES (1,1); 52 SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid LEFT JOIN t2 ON t2.artistid=t3.artistid WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%'; 53 trackname artistname 54 April In Paris Vernon Duke Vernon Duke 55 Autumn In New York null 56 drop table t1; 57 drop table t2; 58 drop table t3; 59 CREATE TABLE t1 (f2 VARCHAR(20)); 60 CREATE TABLE t2 (f2 VARCHAR(20)); 61 INSERT INTO t1 VALUES ('MIN'),('MAX'); 62 INSERT INTO t2 VALUES ('LOAD'); 63 SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1; 64 concat_name 65 LOAD_MIN 66 LOAD_MAX 67 drop table t1; 68 drop table t2; 69 create table t1 (a int, b int); 70 insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); 71 select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a; 72 a MAX(b) CONCAT_WS(MAX(b), '43', '4', '5') 73 1 4 434445 74 10 43 43434435 75 drop table t1; 76 CREATE TABLE t1 ( 77 col_datetime_2_not_null_key datetime(2) NOT NULL, 78 col_datetime_5 datetime(5) DEFAULT NULL, 79 pk datetime(5) NOT NULL, 80 PRIMARY KEY (pk)); 81 INSERT INTO t1 VALUES 82 ('2001-10-20 00:00:00.00','2001-09-20 11:18:18.03630','2011-07-19 17:37:06.26725'), 83 ('0001-01-01 00:00:00.00','0001-01-01 00:00:00.00000','2011-07-19 17:37:07.26725'), 84 ('0001-01-01 00:00:00.00','0001-01-01 00:00:00.00000','2011-07-19 17:37:08.26725'), 85 ('2000-01-14 17:55:57.03','0001-01-01 00:00:00.00000','2011-07-19 17:37:09.26725'), 86 ('2006-12-27 07:25:14.04',NULL,'2011-07-19 17:37:10.26725'), 87 ('2000-10-17 22:03:12.01','0001-01-01 00:00:00.00000','2011-07-19 17:37:11.26725'), 88 ('0001-01-01 00:00:00.00','0001-01-01 00:00:00.00000','2011-07-19 17:37:12.26725'), 89 ('0001-01-01 00:00:00.00','0001-01-01 00:00:00.00000','2011-07-19 17:37:13.26725'), 90 ('0001-01-01 00:00:00.00','2008-06-04 23:05:50.03642','2011-07-19 17:37:14.26725'), 91 ('2000-10-22 23:52:09.01',NULL,'2011-07-19 17:37:15.26725'); 92 SELECT col_datetime_2_not_null_key AS c1, col_datetime_5 AS c2 93 FROM t1 94 WHERE 95 pk <> CONCAT_WS( ':','%I','%m' )+CONCAT_WS('-','%y','%H','%V','%k','%k' ) 96 OR 97 year(col_datetime_5) < year(utc_timestamp()) 98 ORDER BY 1; 99 invalid input: invalid datetime value %I:%m%y-%H-%V-%k-%k 100 DROP TABLE t1; 101 SELECT CONCAT_WS(1471290948102948112341241204312904-23412412-4141, "a", "b"); 102 CONCAT_WS(1471290948102948112341241204312904-23412412-4141, "a", "b") 103 a1471290948102948112341241180896351b 104 SELECT CONCAT_WS("147129094810294812983120", "@^%#&*@^$@(*&#()!@*", "a", "b"); 105 CONCAT_WS("147129094810294812983120", "@^%#&*@^$@(*&#()!@*", "a", "b") 106 @^%#&*@^$@(*&#()!@*147129094810294812983120a147129094810294812983120b 107 SELECT CONCAT_WS("123", "你好", "français", "にほんご"); 108 CONCAT_WS("123", "你好", "français", "にほんご") 109 你好123français123にほんご 110 SELECT CONCAT_WS(1, 0.213, 213.4131, "abc", "2012-03-21 03:03:02", NULL); 111 CONCAT_WS(1, 0.213, 213.4131, "abc", "2012-03-21 03:03:02", NULL) 112 0.2131213.41311abc12012-03-21 03:03:02 113 create table t1(a INT, b date); 114 create table t2(a INT, b date); 115 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 116 insert into t2 values(1, "2012-10-12"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12"); 117 SELECT t1.a,t1.b, t2.a,t2.b FROM t1 JOIN t2 ON (concat_ws(t1.a, t1.b) = concat_ws(t2.a, t2.b)) HAVING substring(concat_ws(t1.a, t1.b),1,1)='2'; 118 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 119 drop table t1; 120 drop table t2;