1# The include statement below is a temp one for tests that are yet to 2#be ported to run with InnoDB, 3#but needs to be kept for tests that would need MyISAM in future. 4--source include/force_myisam_default.inc 5 6# 7# Test of problem with CONCAT_WS() and long separators. 8# 9 10--disable_warnings 11DROP TABLE IF EXISTS t1; 12DROP PROCEDURE IF EXISTS p1; 13--enable_warnings 14--source include/turn_off_only_full_group_by.inc 15 16CREATE TABLE t1 ( number INT NOT NULL, alpha CHAR(6) NOT NULL ); 17INSERT INTO t1 VALUES (1413006,'idlfmv'), 18(1413065,'smpsfz'),(1413127,'sljrhx'),(1413304,'qerfnd'); 19 20SELECT number, alpha, CONCAT_WS('<---->',number,alpha) AS new 21FROM t1 GROUP BY number; 22 23SELECT CONCAT_WS('<---->',number,alpha) AS new 24FROM t1 GROUP BY new LIMIT 1; 25 26SELECT number, alpha, CONCAT_WS('<->',number,alpha) AS new 27FROM t1 GROUP BY new LIMIT 1; 28 29SELECT number, alpha, CONCAT_WS('-',number,alpha,alpha,alpha,alpha,alpha,alpha,alpha) AS new 30FROM t1 GROUP BY new LIMIT 1; 31 32SELECT number, alpha, CONCAT_WS('<------------------>',number,alpha) AS new 33FROM t1 GROUP BY new LIMIT 1; 34 35--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 36drop table t1; 37 38# 39# Bug #5540: a problem with double type 40# 41 42create table t1 (a char(4), b double, c date, d tinyint(4)); 43insert into t1 values ('AAAA', 105, '2003-03-01', 1); 44select * from t1 where concat(A,C,B,D) = 'AAAA2003-03-011051'; 45drop table t1; 46 47# BUG#6825 48select 'a' union select concat('a', -4); 49select 'a' union select concat('a', -4.5); 50 51select 'a' union select concat('a', -(4 + 1)); 52select 'a' union select concat('a', 4 - 5); 53 54select 'a' union select concat('a', -'3'); 55select 'a' union select concat('a', -concat('3',4)); 56 57select 'a' union select concat('a', -0); 58--replace_result a-0.0 a0.0 59select 'a' union select concat('a', -0.0); 60 61--replace_result a-0.0000 a0.0000 62select 'a' union select concat('a', -0.0000); 63 64# 65# Bug#16716: subselect in concat() may lead to a wrong result 66# 67select concat((select x from (select 'a' as x) as t1 ), 68 (select y from (select 'b' as y) as t2 )) from (select 1 union select 2 ) 69 as t3; 70 71# End of 4.1 tests 72 73# 74# Bug#15962: CONCAT() in UNION may lead to a data trucation. 75# 76create table t1(f1 varchar(6)) charset=utf8; 77insert into t1 values ("123456"); 78select concat(f1, 2) a from t1 union select 'x' a from t1; 79drop table t1; 80 81# 82# Bug #36488: regexp returns false matches, concatenating with previous rows 83# 84CREATE TABLE t1 (c1 varchar(100), c2 varchar(100)); 85INSERT INTO t1 VALUES ('',''), ('','First'), ('Random','Random'); 86SELECT * FROM t1 WHERE CONCAT(c1,' ',c2) REGEXP 'First.*'; 87DROP TABLE t1; 88 89--echo # End of 5.0 tests 90 91 92--echo # 93--echo # Bug #44743: Join in combination with concat does not always work 94--echo # 95CREATE TABLE t1 ( 96 a VARCHAR(100) NOT NULL DEFAULT '0', 97 b VARCHAR(2) NOT NULL DEFAULT '', 98 c VARCHAR(2) NOT NULL DEFAULT '', 99 d TEXT NOT NULL, 100 PRIMARY KEY (a, b, c), 101 KEY (a) 102) DEFAULT CHARSET=utf8; 103 104INSERT INTO t1 VALUES ('gui_A', 'a', 'b', 'str1'), 105 ('gui_AB', 'a', 'b', 'str2'), ('gui_ABC', 'a', 'b', 'str3'); 106 107CREATE TABLE t2 ( 108 a VARCHAR(100) NOT NULL DEFAULT '', 109 PRIMARY KEY (a) 110) DEFAULT CHARSET=latin1; 111 112INSERT INTO t2 VALUES ('A'), ('AB'), ('ABC'); 113 114SELECT CONCAT('gui_', t2.a), t1.d FROM t2 115 LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; 116 117EXPLAIN SELECT CONCAT('gui_', t2.a), t1.d FROM t2 118 LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; 119 120DROP TABLE t1, t2; 121 122 123--echo # 124--echo # Bug #50096: CONCAT_WS inside procedure returning wrong data 125--echo # 126 127CREATE PROCEDURE p1(a varchar(255), b int, c int) 128 SET @query = CONCAT_WS(",", a, b, c); 129 130CALL p1("abcde", "0", "1234"); 131SELECT @query; 132 133DROP PROCEDURE p1; 134 135--echo # 136--echo # Bug #40625: Concat fails on DOUBLE values in a Stored Procedure, 137--echo # while DECIMAL works 138--echo # 139 140DELIMITER //; 141CREATE PROCEDURE p1() 142BEGIN 143 DECLARE v1 DOUBLE(10,3); 144 SET v1= 100; 145 SET @s = CONCAT('########################################', 40 , v1); 146 SELECT @s; 147END;// 148DELIMITER ;// 149 150CALL p1(); 151CALL p1(); 152 153DROP PROCEDURE p1; 154 155--echo # End of 5.1 tests 156