MySql query: by simply appending another column to the query, it then returns nothing

The following query is working for me and returns the data in the format '1755|1755.jpg' :

SELECT (CAST(GROUP_CONCAT(CONCAT(photoId, '|', photoFileName)) AS CHAR(10000) CHARACTER SET utf8)) AS recentPhotoList
                              FROM
                              (
                                SELECT photoId, photoFileName
                                FROM photo
                                WHERE photoAlbumId = _albumId
                                AND photoPublishDate >= DATE_ADD(tStartDate, INTERVAL -20 MINUTE)
                                AND photoPublishDate <= tStartDate
                                ORDER BY photoPublishDate DESC
                                LIMIT 0,1
                              ) as subQuery
                            );

However, by simply appending another column ('photoCaption') to the query, it then returns nothing?!? Why is this? Why is adding an additional column causing it to do this?

Here is the modified query that causes issues:

SELECT (CAST(GROUP_CONCAT(CONCAT(photoId, '|', photoFileName, '|', photoCaption)) AS CHAR(10000) CHARACTER SET utf8)) AS recentPhotoList
                              FROM
                              (
                                SELECT photoId, photoFileName, photoCaption
                                FROM photo
                                WHERE photoAlbumId = _albumId
开发者_Python百科                                AND photoPublishDate >= DATE_ADD(tStartDate, INTERVAL -20 MINUTE)
                                AND photoPublishDate <= tStartDate
                                ORDER BY photoPublishDate DESC
                                LIMIT 0,1
                              ) as subQuery
                            );

---

I think that if photoCaption is null then the concat returns null. Use coalesce around photoCaption to solve this.

---

photoCaption is NULL i think.

Maybe you can check the results of your subquery to see what is going on.

SELECT CONCAT('bla', '|','-' , '|','|', 'bla', NULL) as foobar;

returns NULL

SELECT CONCAT('bla', '|','-' , '|','|', 'bla') as foobar;

returns bla|-||bla

Good luck