summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/func_concat.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/func_concat.result')
-rw-r--r--mysql-test/main/func_concat.result289
1 files changed, 289 insertions, 0 deletions
diff --git a/mysql-test/main/func_concat.result b/mysql-test/main/func_concat.result
new file mode 100644
index 00000000..4303c97d
--- /dev/null
+++ b/mysql-test/main/func_concat.result
@@ -0,0 +1,289 @@
+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 )) as exp from (select 1 union select 2 )
+as t3;
+exp
+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;