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
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 4.0.12
• OS: Windows (Windows 2000)
• 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