Bug #480 Select Concat(Col1, col2,...) .... returns NULL if value of COL1 is NULL
Submitted: 22 May 2003 17:28 Modified: 23 May 2003 2:51
Reporter: Bernard Simmons Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.12 OS:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[22 May 2003 17:28] Bernard Simmons
Description:
The query "Select Concat(Col1, col2,...) ...." will return a value of  NULL if value of COL1 is NULL reguardless if the values of col2,... are not NULL.

How to repeat:
1. Create a table with any number of columns defining Col1, Col2 and Col3 as "varchar 50 NULL default NULL".
2. Insert any number of rows into the table make sure that some of the rows have the value for Col1 set to NULL.
3. Execute the select Statement "Select Concat(Col1, col2,...) as "Name" ...."  and for the rows where Col1 is NULL a value of NULL will be returned for "Name"
[22 May 2003 18:01] Bernard Simmons
Updated this bug report to include the exact version of MySql and Windows Operating System. Additionally I have repeated this bug using phpMyAdmin 2.5.0 as well as my own program
[23 May 2003 2:32] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is doccumented behaviour of CONCAT() function.

From Manual chapter 6.3.2 String Functions

CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL

Use CONCAT_WS() instead or wrap NULLable paremeters with IFNULL() function.
[23 May 2003 2:51] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php