Bug #34388 CONCAT Change result DataType
Submitted: 7 Feb 2008 18:07 Modified: 8 Feb 2008 8:00
Reporter: Mathieu Tremblay Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.45 OS:Microsoft Windows (XP)
Assigned to:

[7 Feb 2008 18:07] Mathieu Tremblay
Description:
When using CONCAT() function with UNSIGNED DataType the result is change to varbinary datatype

See How to repeat

DESC Testview;
_____________________________________________________________
Field   |Type             |Null |Key  Default Extra
Number  |int(10) unsigned |NO   |     |0
OK      |varchar(15)      |YES  |     |
Error   |varbinary(21)    |YES  |     |
_____________________________________________________________

How to repeat:
DROP VIEW IF EXISTS `testview`;
DROP TABLE IF EXISTS `testtable`;

CREATE TABLE `testtable` (
  `Number` int(10) unsigned NOT NULL auto_increment,
  `Error` int(10) unsigned default NULL,
  `Str` VARCHAR(10) default '',
  PRIMARY KEY  (`Number`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

ALTER TABLE `testtable` DISABLE KEYS;
INSERT INTO `testtable` (`Number`,`Error`,`Str`) VALUES
 (1,NULL,NULL),
 (2,1,'test123'),
 (3,2,'test234');
ALTER TABLE `testtable` ENABLE KEYS;

CREATE OR REPLACE VIEW `TestView` AS
SELECT Number,CONCAT("first",Str)AS OK,
CONCAT(Error,Str)AS Error
FROM TestTable;

DESC Testtable;
DESC Testview;
[8 Feb 2008 8:00] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

CONCAT() returns a binary string if any of its arguments are binary strings. Type of Error field in testtable is int(10), so behavior is expected.