1DROP TABLE IF EXISTS t1; 2DROP PROCEDURE IF EXISTS p1; 3CREATE TABLE t1 ( number INT NOT NULL, alpha CHAR(6) NOT NULL ); 4INSERT INTO t1 VALUES (1413006,'idlfmv'), 5(1413065,'smpsfz'),(1413127,'sljrhx'),(1413304,'qerfnd'); 6SELECT number, alpha, CONCAT_WS('<---->',number,alpha) AS new 7FROM t1 GROUP BY number; 8number alpha new 91413006 idlfmv 1413006<---->idlfmv 101413065 smpsfz 1413065<---->smpsfz 111413127 sljrhx 1413127<---->sljrhx 121413304 qerfnd 1413304<---->qerfnd 13SELECT CONCAT_WS('<---->',number,alpha) AS new 14FROM t1 GROUP BY new LIMIT 1; 15new 161413006<---->idlfmv 17SELECT number, alpha, CONCAT_WS('<->',number,alpha) AS new 18FROM t1 GROUP BY new LIMIT 1; 19number alpha new 201413006 idlfmv 1413006<->idlfmv 21SELECT number, alpha, CONCAT_WS('-',number,alpha,alpha,alpha,alpha,alpha,alpha,alpha) AS new 22FROM t1 GROUP BY new LIMIT 1; 23number alpha new 241413006 idlfmv 1413006-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv 25SELECT number, alpha, CONCAT_WS('<------------------>',number,alpha) AS new 26FROM t1 GROUP BY new LIMIT 1; 27number alpha new 281413006 idlfmv 1413006<------------------>idlfmv 29drop table t1; 30create table t1 (a char(4), b double, c date, d tinyint(4)); 31insert into t1 values ('AAAA', 105, '2003-03-01', 1); 32select * from t1 where concat(A,C,B,D) = 'AAAA2003-03-011051'; 33a b c d 34AAAA 105 2003-03-01 1 35drop table t1; 36select 'a' union select concat('a', -4); 37a 38a 39a-4 40select 'a' union select concat('a', -4.5); 41a 42a 43a-4.5 44select 'a' union select concat('a', -(4 + 1)); 45a 46a 47a-5 48select 'a' union select concat('a', 4 - 5); 49a 50a 51a-1 52select 'a' union select concat('a', -'3'); 53a 54a 55a-3 56select 'a' union select concat('a', -concat('3',4)); 57a 58a 59a-34 60select 'a' union select concat('a', -0); 61a 62a 63a0 64select 'a' union select concat('a', -0.0); 65a 66a 67a0.0 68select 'a' union select concat('a', -0.0000); 69a 70a 71a0.0000 72select concat((select x from (select 'a' as x) as t1 ), 73(select y from (select 'b' as y) as t2 )) from (select 1 union select 2 ) 74as t3; 75concat((select x from (select 'a' as x) as t1 ), 76(select y from (select 'b' as y) as t2 )) 77ab 78ab 79create table t1(f1 varchar(6)) charset=utf8; 80insert into t1 values ("123456"); 81select concat(f1, 2) a from t1 union select 'x' a from t1; 82a 831234562 84x 85drop table t1; 86CREATE TABLE t1 (c1 varchar(100), c2 varchar(100)); 87INSERT INTO t1 VALUES ('',''), ('','First'), ('Random','Random'); 88SELECT * FROM t1 WHERE CONCAT(c1,' ',c2) REGEXP 'First.*'; 89c1 c2 90 First 91DROP TABLE t1; 92# End of 5.0 tests 93# 94# Bug #44743: Join in combination with concat does not always work 95# 96CREATE TABLE t1 ( 97a VARCHAR(100) NOT NULL DEFAULT '0', 98b VARCHAR(2) NOT NULL DEFAULT '', 99c VARCHAR(2) NOT NULL DEFAULT '', 100d TEXT NOT NULL, 101PRIMARY KEY (a, b, c), 102KEY (a) 103) DEFAULT CHARSET=utf8; 104INSERT INTO t1 VALUES ('gui_A', 'a', 'b', 'str1'), 105('gui_AB', 'a', 'b', 'str2'), ('gui_ABC', 'a', 'b', 'str3'); 106CREATE TABLE t2 ( 107a VARCHAR(100) NOT NULL DEFAULT '', 108PRIMARY KEY (a) 109) DEFAULT CHARSET=latin1; 110INSERT INTO t2 VALUES ('A'), ('AB'), ('ABC'); 111SELECT CONCAT('gui_', t2.a), t1.d FROM t2 112LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; 113CONCAT('gui_', t2.a) d 114gui_A str1 115gui_AB str2 116gui_ABC str3 117EXPLAIN SELECT CONCAT('gui_', t2.a), t1.d FROM t2 118LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; 119id select_type table type possible_keys key key_len ref rows Extra 1201 SIMPLE t2 index NULL PRIMARY 102 NULL 3 Using index 1211 SIMPLE t1 eq_ref PRIMARY,a PRIMARY 318 func,const,const 1 Using where 122DROP TABLE t1, t2; 123# 124# Bug #50096: CONCAT_WS inside procedure returning wrong data 125# 126CREATE PROCEDURE p1(a varchar(255), b int, c int) 127SET @query = CONCAT_WS(",", a, b, c); 128CALL p1("abcde", "0", "1234"); 129SELECT @query; 130@query 131abcde,0,1234 132DROP PROCEDURE p1; 133# 134# Bug #40625: Concat fails on DOUBLE values in a Stored Procedure, 135# while DECIMAL works 136# 137CREATE PROCEDURE p1() 138BEGIN 139DECLARE v1 DOUBLE(10,3); 140SET v1= 100; 141SET @s = CONCAT('########################################', 40 , v1); 142SELECT @s; 143END;// 144CALL p1(); 145@s 146########################################40100.000 147CALL p1(); 148@s 149########################################40100.000 150DROP PROCEDURE p1; 151# End of 5.1 tests 152# 153# Start of 10.0 tests 154# 155# 156# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery 157# 158SET @save_optimizer_switch=@@optimizer_switch; 159SET optimizer_switch='derived_merge=on'; 160CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); 161INSERT INTO t1 VALUES('1234567'); 162SELECT CONCAT(SUBSTR(t2, 1, 3), SUBSTR(t2, 5)) c1, 163CONCAT(SUBSTR(t2,1,3),'---',SUBSTR(t2,5)) c2 164FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; 165c1 c2 166123567 123---567 167SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; 168c2 1691234567-1234567 170DROP TABLE t1; 171CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); 172INSERT INTO t1 VALUES('1234567'); 173SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; 174c2 1751234567-1234567 176SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT REVERSE(t) t2 FROM t1) sub; 177c2 1787654321-7654321 179SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SOUNDEX(t) t2 FROM t1) sub; 180c2 181- 182SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; 183c2 184MTIzNDU2Nw==-MTIzNDU2Nw== 185SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT WEIGHT_STRING(t) t2 FROM t1) sub; 186c2 1871234567-1234567 188SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; 189c2 19031323334353637-31323334353637 191SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT QUOTE(t) t2 FROM t1) sub; 192c2 193'1234567'-'1234567' 194DROP TABLE t1; 195CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); 196INSERT INTO t1 VALUES(TO_BASE64('abcdefghi')); 197SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FROM_BASE64(t) t2 FROM t1) sub; 198c2 199abcdefghi-abcdefghi 200DROP TABLE t1; 201CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); 202INSERT INTO t1 VALUES(HEX('abcdefghi')); 203SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UNHEX(t) t2 FROM t1) sub; 204c2 205abcdefghi-abcdefghi 206DROP TABLE t1; 207CREATE TABLE t1 (t VARCHAR(30) CHARSET latin1); 208INSERT INTO t1 VALUES('test'); 209SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; 210c2 21116 212SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; 213c2 21433 215SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; 216c2 21734 218SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; 219c2 2204 221SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; 222c2 2239 224SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; 225c2 22610 227DROP TABLE t1; 228CREATE TABLE t1 (t VARCHAR(64) CHARSET latin1); 229INSERT INTO t1 VALUES('123456789'); 230SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MD5(t) t2 FROM t1) sub; 231c2 23225f9e794323b453885f5181f1b624d0b-25f9e794323b453885f5181f1b624d0b 233SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FORMAT(t,2) t2 FROM t1) sub; 234c2 235123,456,789.00-123,456,789.00 236DROP TABLE t1; 237CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); 238INSERT INTO t1 VALUES('abcdefghi'); 239SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT INSERT(t,3,4,'xxx') t2 FROM t1) sub; 240c2 241abxxxghi-abxxxghi 242DROP TABLE t1; 243CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); 244INSERT INTO t1 VALUES('abcdefghi'); 245SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LEFT(t,10) t2 FROM t1) sub; 246c2 247abcdefghi-abcdefghi 248SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RIGHT(t,10) t2 FROM t1) sub; 249c2 250abcdefghi-abcdefghi 251SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SUBSTR(t,1,10) t2 FROM t1) sub; 252c2 253abcdefghi-abcdefghi 254SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LTRIM(t) t2 FROM t1) sub; 255c2 256abcdefghi-abcdefghi 257SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RTRIM(t) t2 FROM t1) sub; 258c2 259abcdefghi-abcdefghi 260SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TRIM(t) t2 FROM t1) sub; 261c2 262abcdefghi-abcdefghi 263DROP TABLE t1; 264SET optimizer_switch=@save_optimizer_switch; 265# 266# MDEV-13790 UNHEX() of a somewhat complicated CONCAT() returns NULL 267# 268SELECT UNHEX(CONCAT('414C2', HEX(8 + ROUND(RAND()*7)), SUBSTR(SHA(UUID()),6,33),HEX(2+ROUND(RAND()*8)))) IS NULL AS c1; 269c1 2700 271# 272# MDEV-13119 Wrong results with CAST(AS CHAR) and subquery 273# 274SET optimizer_switch=_utf8'derived_merge=on'; 275CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); 276INSERT INTO t1 VALUES('abcdefghi'); 277SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CAST(t AS CHAR CHARACTER SET utf8) t2 FROM t1) sub; 278c2 279abcdefghi-abcdefghi 280DROP TABLE t1; 281SET optimizer_switch=@save_optimizer_switch; 282# 283# MDEV-13120 Wrong results with MAKE_SET() and subquery 284# 285CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); 286INSERT INTO t1 VALUES('abcdefghi'); 287SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MAKE_SET(3,t,t) t2 FROM t1) sub; 288c2 289abcdefghi,abcdefghi-abcdefghi,abcdefghi 290DROP TABLE t1; 291