MySQL Bugs: #39454: COALESCE with Longtext field only returns the first line

Bug #39454	COALESCE with Longtext field only returns the first line
Submitted:	15 Sep 2008 13:07	Modified:	30 Sep 2010 1:46
Reporter:	David Poor	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.51a-commmunity-nt	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	COALESCE, longtext

Description:
My apologies if this is not a bug or if it has already been reported. (I could find no similar situation documented anywhere.) 

I am getting only the first line of a longtext field when it is part of a COALESCE set.

I have a table, longtexttable, with two columns:
ContentsID (int(3))
Contents (longtext)

I add a single entry:
ContentsID = 1
Contents =
This is the first entry, line 1 of three
This is the first entry, line 2 of three
This is the first entry, line 3 of three

A normal query, "Select contents from longtexttable where ContentsID = 1;" retrieves the entire field with all three lines.

However, I want to use the table to get the first non-null entry from a series of possible values, so I am using the COALESCE function. Lets say I am interested in entry ContentsID=4, but want to use the entry in ContentsID=1 if it does not exist.

Select COALESCE((Select contents from longtexttable where ContentsID = 4),
(Select contents from longtexttable where ContentsID = 1));

This properly recognizes that the first Select is null, and therefore returns the contents from the second select (ContentsID=1). However it returns only the first line "This is the first entry, line 1 of three".

I also get only the first line if I try the COALESCE with just a single select: "Select COALESCE((Select contents from longtexttable where ContentsID = 1));"

How to repeat:
create table `longtext` (  `ContentsID` int (3) NOT NULL , `Contents` longtext NOT NULL );  
insert into `longtext`(`ContentsID`,`Contents`) values ( '1','This is the first entry, line 1 of three\r\nThis is the first entry, line 2 of three\r\nThis is the first entry, line 3 of three');
Select contents from longtexttable where ContentsID = 1;
Select COALESCE((Select contents from longtexttable where ContentsID = 4),      (Select contents from longtexttable where ContentsID = 1));
Select COALESCE((Select contents from longtexttable where ContentsID = 1));

Suggested fix:
WORKAROUND: 
Select COALESCE((Select replace(contents,'\r\n','') from longtexttable where ContentsID = 4),(Select replace(contents,'\r\n','') from longtexttable where ContentsID = 1));

In the replication, the table name should be "longtexttable" throughout.  (Sloppy documentation on my part!)

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.57, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Not repeatable in 5.1.49 nor 5.5.6rc

mysql> Select contents from longtexttable where ContentsID = 1;
+----------------------------------------------------------------------------------------------------------------------------+
| contents                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------+
| This is the first entry,line 1 of three
This is the first entry, line 2 of three
This is the first entry,line 3 of three |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> Select COALESCE((Select contents from longtexttable where ContentsID = 4),(Select contents from longtexttable where ContentsID = 1));
+-------------------------------------------------------------------------------------------------------------------------------+
| COALESCE((Select contents from longtexttable where ContentsID = 4),(Select contents from longtexttable where ContentsID = 1)) |
+-------------------------------------------------------------------------------------------------------------------------------+
| This is the first entry,line 1 of three
This is the first entry, line 2 of three
This is the first entry,line 3 of three    |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> Select COALESCE((Select contents from longtexttable where ContentsID = 1));
+----------------------------------------------------------------------------------------------------------------------------+
| COALESCE((Select contents from longtexttable where ContentsID = 1))                                                        |
+----------------------------------------------------------------------------------------------------------------------------+
| This is the first entry,line 1 of three
This is the first entry, line 2 of three
This is the first entry,line 3 of three |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)