EVOLUTION-MANAGER

Edit File: func_concat.result

DROP TABLE IF EXISTS t1; DROP PROCEDURE IF EXISTS p1; CREATE TABLE t1 ( number INT NOT NULL, alpha CHAR(6) NOT NULL ); INSERT INTO t1 VALUES (1413006,'idlfmv'), (1413065,'smpsfz'),(1413127,'sljrhx'),(1413304,'qerfnd'); SELECT number, alpha, CONCAT_WS('<---->',number,alpha) AS new FROM t1 GROUP BY number; number alpha new 1413006 idlfmv 1413006<---->idlfmv 1413065 smpsfz 1413065<---->smpsfz 1413127 sljrhx 1413127<---->sljrhx 1413304 qerfnd 1413304<---->qerfnd SELECT CONCAT_WS('<---->',number,alpha) AS new FROM t1 GROUP BY new LIMIT 1; new 1413006<---->idlfmv SELECT number, alpha, CONCAT_WS('<->',number,alpha) AS new FROM t1 GROUP BY new LIMIT 1; number alpha new 1413006 idlfmv 1413006<->idlfmv SELECT number, alpha, CONCAT_WS('-',number,alpha,alpha,alpha,alpha,alpha,alpha,alpha) AS new FROM t1 GROUP BY new LIMIT 1; number alpha new 1413006 idlfmv 1413006-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv SELECT number, alpha, CONCAT_WS('<------------------>',number,alpha) AS new FROM t1 GROUP BY new LIMIT 1; number alpha new 1413006 idlfmv 1413006<------------------>idlfmv drop table t1; create table t1 (a char(4), b double, c date, d tinyint(4)); insert into t1 values ('AAAA', 105, '2003-03-01', 1); select * from t1 where concat(A,C,B,D) = 'AAAA2003-03-011051'; a b c d AAAA 105 2003-03-01 1 drop table t1; select 'a' union select concat('a', -4); a a a-4 select 'a' union select concat('a', -4.5); a a a-4.5 select 'a' union select concat('a', -(4 + 1)); a a a-5 select 'a' union select concat('a', 4 - 5); a a a-1 select 'a' union select concat('a', -'3'); a a a-3 select 'a' union select concat('a', -concat('3',4)); a a a-34 select 'a' union select concat('a', -0); a a a0 select 'a' union select concat('a', -0.0); a a a0.0 select 'a' union select concat('a', -0.0000); a a a0.0000 select concat((select x from (select 'a' as x) as t1 ), (select y from (select 'b' as y) as t2 )) from (select 1 union select 2 ) as t3; concat((select x from (select 'a' as x) as t1 ), (select y from (select 'b' as y) as t2 )) ab ab create table t1(f1 varchar(6)) charset=utf8; insert into t1 values ("123456"); select concat(f1, 2) a from t1 union select 'x' a from t1; a 1234562 x drop table t1; CREATE TABLE t1 (c1 varchar(100), c2 varchar(100)); INSERT INTO t1 VALUES ('',''), ('','First'), ('Random','Random'); SELECT * FROM t1 WHERE CONCAT(c1,' ',c2) REGEXP 'First.*'; c1 c2 First DROP TABLE t1; # End of 5.0 tests # # Bug #44743: Join in combination with concat does not always work # CREATE TABLE t1 ( a VARCHAR(100) NOT NULL DEFAULT '0', b VARCHAR(2) NOT NULL DEFAULT '', c VARCHAR(2) NOT NULL DEFAULT '', d TEXT NOT NULL, PRIMARY KEY (a, b, c), KEY (a) ) DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES ('gui_A', 'a', 'b', 'str1'), ('gui_AB', 'a', 'b', 'str2'), ('gui_ABC', 'a', 'b', 'str3'); CREATE TABLE t2 ( a VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (a) ) DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES ('A'), ('AB'), ('ABC'); SELECT CONCAT('gui_', t2.a), t1.d FROM t2 LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; CONCAT('gui_', t2.a) d gui_A str1 gui_AB str2 gui_ABC str3 EXPLAIN SELECT CONCAT('gui_', t2.a), t1.d FROM t2 LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 102 NULL 3 Using index 1 SIMPLE t1 eq_ref PRIMARY,a PRIMARY 318 func,const,const 1 Using where DROP TABLE t1, t2; # # Bug #50096: CONCAT_WS inside procedure returning wrong data # CREATE PROCEDURE p1(a varchar(255), b int, c int) SET @query = CONCAT_WS(",", a, b, c); CALL p1("abcde", "0", "1234"); SELECT @query; @query abcde,0,1234 DROP PROCEDURE p1; # # Bug #40625: Concat fails on DOUBLE values in a Stored Procedure, # while DECIMAL works # CREATE PROCEDURE p1() BEGIN DECLARE v1 DOUBLE(10,3); SET v1= 100; SET @s = CONCAT('########################################', 40 , v1); SELECT @s; END;// CALL p1(); @s ########################################40100.000 CALL p1(); @s ########################################40100.000 DROP PROCEDURE p1; # End of 5.1 tests # # Start of 10.0 tests # # # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery # SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='derived_merge=on'; CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); INSERT INTO t1 VALUES('1234567'); SELECT CONCAT(SUBSTR(t2, 1, 3), SUBSTR(t2, 5)) c1, CONCAT(SUBSTR(t2,1,3),'---',SUBSTR(t2,5)) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; c1 c2 123567 123---567 SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; c2 1234567-1234567 DROP TABLE t1; CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); INSERT INTO t1 VALUES('1234567'); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; c2 1234567-1234567 SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT REVERSE(t) t2 FROM t1) sub; c2 7654321-7654321 SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SOUNDEX(t) t2 FROM t1) sub; c2 - SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; c2 MTIzNDU2Nw==-MTIzNDU2Nw== SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT WEIGHT_STRING(t) t2 FROM t1) sub; c2 1234567-1234567 SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; c2 31323334353637-31323334353637 SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT QUOTE(t) t2 FROM t1) sub; c2 '1234567'-'1234567' DROP TABLE t1; CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); INSERT INTO t1 VALUES(TO_BASE64('abcdefghi')); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FROM_BASE64(t) t2 FROM t1) sub; c2 abcdefghi-abcdefghi DROP TABLE t1; CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); INSERT INTO t1 VALUES(HEX('abcdefghi')); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UNHEX(t) t2 FROM t1) sub; c2 abcdefghi-abcdefghi DROP TABLE t1; CREATE TABLE t1 (t VARCHAR(30) CHARSET latin1); INSERT INTO t1 VALUES('test'); SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; c2 16 SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; c2 33 SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; c2 34 SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; c2 4 SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; c2 9 SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; c2 10 DROP TABLE t1; CREATE TABLE t1 (t VARCHAR(64) CHARSET latin1); INSERT INTO t1 VALUES('123456789'); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MD5(t) t2 FROM t1) sub; c2 25f9e794323b453885f5181f1b624d0b-25f9e794323b453885f5181f1b624d0b SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FORMAT(t,2) t2 FROM t1) sub; c2 123,456,789.00-123,456,789.00 DROP TABLE t1; CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); INSERT INTO t1 VALUES('abcdefghi'); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT INSERT(t,3,4,'xxx') t2 FROM t1) sub; c2 abxxxghi-abxxxghi DROP TABLE t1; CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); INSERT INTO t1 VALUES('abcdefghi'); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LEFT(t,10) t2 FROM t1) sub; c2 abcdefghi-abcdefghi SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RIGHT(t,10) t2 FROM t1) sub; c2 abcdefghi-abcdefghi SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SUBSTR(t,1,10) t2 FROM t1) sub; c2 abcdefghi-abcdefghi SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LTRIM(t) t2 FROM t1) sub; c2 abcdefghi-abcdefghi SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RTRIM(t) t2 FROM t1) sub; c2 abcdefghi-abcdefghi SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TRIM(t) t2 FROM t1) sub; c2 abcdefghi-abcdefghi DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; # # MDEV-13790 UNHEX() of a somewhat complicated CONCAT() returns NULL # SELECT UNHEX(CONCAT('414C2', HEX(8 + ROUND(RAND()*7)), SUBSTR(SHA(UUID()),6,33),HEX(2+ROUND(RAND()*8)))) IS NULL AS c1; c1 0 # # MDEV-13119 Wrong results with CAST(AS CHAR) and subquery # SET optimizer_switch=_utf8'derived_merge=on'; CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); INSERT INTO t1 VALUES('abcdefghi'); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CAST(t AS CHAR CHARACTER SET utf8) t2 FROM t1) sub; c2 abcdefghi-abcdefghi DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; # # MDEV-13120 Wrong results with MAKE_SET() and subquery # CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); INSERT INTO t1 VALUES('abcdefghi'); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MAKE_SET(3,t,t) t2 FROM t1) sub; c2 abcdefghi,abcdefghi-abcdefghi,abcdefghi DROP TABLE t1;