MySQL Bugs: #7533: CONCAT fails if one of the columns is NULL

Bug #7533	CONCAT fails if one of the columns is NULL
Submitted:	25 Dec 2004 10:52	Modified:	27 Dec 2004 13:34
Reporter:	Anthony Marston	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.8	OS:	Windows (Windows XP)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
I have a MyISAM tables constructed as follows:

CREATE TABLE IF NOT EXISTS `crs_student` (
  `student_id` smallint(5) unsigned NOT NULL default '0',
  `first_name` varchar(40) collate latin1_general_ci NOT NULL default '',
  `last_name` varchar(40) collate latin1_general_ci NOT NULL default '',
  `initials` varchar(4) collate latin1_general_ci default NULL,
  `year` decimal(1,0) unsigned NOT NULL default '0',
  PRIMARY KEY  (`student_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `crs_student` VALUES (1, 'Anthony', 'Abbot', 'J', 1);
INSERT INTO `crs_student` VALUES (2, 'Bartholomew', 'Barclay', NULL, 1);
INSERT INTO `crs_student` VALUES (3, 'Christopher', 'Carter', NULL, 1);

If I run the following query:

SELECT student_id, CONCAT(first_name, " ", initials, " ", last_name) AS student_name, year FROM crs_student WHERE (student_id='1') OR (student_id='2') OR (student_id='3') 

it shows the following:

student_id | student_name    | year
-----------------------------------
1             | Anthony J Abbot | 1
2             |                        | 1
3             |                        | 1
-----------------------------------

If I remove the initials column from the CONCAT statement then student_name is constructed correctly.

If I change the initials column to NOT NULL default "" then it works correctly.

How to repeat:
Create table with sample data using above code, then try the SELECT statement.

Suggested fix:
That CONCAT works with NULL columns as well as empty columns.

I can't repeat it with 4.1.9-debug.

mysql> SELECT student_id, CONCAT(first_name, " ", initials, " ", last_name) AS
    -> student_name, year FROM crs_student WHERE (student_id='1') OR (student_id='2')
    -> OR (student_id='3');
+------------+-----------------+------+
| student_id | student_name    | year |
+------------+-----------------+------+
|          1 | Anthony J Abbot |    1 |
|          2 | NULL            |    1 |
|          3 | NULL            |    1 |
+------------+-----------------+------+
3 rows in set (0.00 sec)

Your example shows that for student_id=2 and student_id=3 the result of the CONCAT statement is NULL even though two of the three named columns are not NULL.

+------------+-----------------+------+
| student_id  | student_name     | year |
+------------+-----------------+------+
|              1 | Anthony J Abbot  |    1 |
|              2 | NULL                 |    1 |
|              3 | NULL                 |    1 |
+------------+-----------------+------+

The output for student_id=2 should be 'Bartholomew Barclay' with a NULL middle initial. Just because one of the columns is NULL should not result in the entire output being NULL.

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

Additional info:

http://dev.mysql.com/doc/mysql/en/String_functions.html