Bug #17922 Concat with NULL content are no concatening
Submitted: 5 Mar 2006 3:48 Modified: 5 Mar 2006 14:24
Reporter: Luis A S Junior Camargo Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18 OS:Windows (Windows 2000 SP4)
Assigned to: CPU Architecture:Any

[5 Mar 2006 3:48] Luis A S Junior Camargo 
Description:
The CONCAT function does not work if one of values is NULL, in this case the result is always NULL.

How to repeat:
mysql> select concat(NULL,'test');
+----------------------+
| concat(NULL,'test')  |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select concat('test',NULL);
+---------------------+
| concat('test',NULL) |
+---------------------+
| NULL                |
+---------------------+
1 row in set (0.00 sec)

Suggested fix:
Interpret the NULL values same as ''
[5 Mar 2006 6:19] Valeriy Kravchuk 
Thank you for a problem report. Sorry, but it is not a bug. It is inteded, documented and correct behaviour. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html):

"CONCAT() returns NULL if any argument is NULL."

It is Oracle that equals '' with NULL, and thus just use '' when NULL is concatenated. It was wrong decision, according to one of Codd rules for proper RDBMS.
[5 Mar 2006 14:24] Luis A S Junior Camargo 
Sorry for the post, only to comment, i have solved my problem with function CONCAT_WS:

mysql> select concat_ws('',NULL,'test');
+---------------------------+
| concat_ws('',NULL,'test') |
+---------------------------+
| test                      |
+---------------------------+
1 row in set (0.00 sec)

very thanks, goodbye