MySQL GROUP_CONCAT vs. COALESCE concerning NULL va

Question

UPDATE

I just noticed that in the server the column table3.note values are NULL and on my local machine they are empty strings. After this embarassing discovery I made some testing and everything works the same on both platforms.

And this is what they produce if I have two cells and the second one contains an actual value (the first is NULL):

//1st
GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `table3_note`
//var_dump(): array(2) { [0]=> string(0) "" [1]=> string(4) "Test" } 

//2nd
GROUP_CONCAT(`table3`.`note`) SEPARATOR ';') AS `table3_note`
//var_dump(): array(1) { [0]=> string(4) "Test" }

So the 1st query (COALESCE) retrieves NULLs as empty strings and the 2nd strips all NULL values from the result set. (This is unacceptable because I have many arrays and they need to be synchronized.)

The original problem is solved because of my mistake. I would still like to know why GROUP_CONCAT ignores NULLs even if checked.

Here's the query that works properly (doesn't strip the NULLs):

SELECT `table1`.*
  GROUP_CONCAT(COALESCE(`table3`.`id`, '') SEPARATOR ';') AS `t3_id`,
  GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `t3_note`,
  FROM `table1`
    LEFT JOIN `table3` ON `table3`.`id` = `table1`.`id`
      GROUP BY `table1`.`id`

So why this one ignores NULLs? (More query option that ignore NULL values are on the original question section.)

SELECT `table1`.*
  GROUP_CONCAT(`table3`.`id` SEPARATOR ';') AS `t3_id`,
  GROUP_CONCAT(`table3`.`note` SEPARATOR ';') AS `t3_note`,
  FROM `table1`
    LEFT JOIN `table3` ON `table3`.`id` = `table1`.`id`
      GROUP BY `table1`.`id`

Original question (not important, my bad...)

A part of my query that uses three tables (1:n relationship, I'm mapping multiple rows from table2 and table3 to a single table1 row). Two alternatives for fetching a single cell value:

 //1st
 GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `table3_note`

 //2nd
 GROUP_CONCAT(`table3`.`note`) SEPARATOR ';') AS `table3_note`

Both work fine on local machine but only the first one on the server. On my local machine I get a correct amount of empty array values when using the 1st or 2nd option (using var_dump()). On the server the 2nd option returns only an empty array if there are no values on any table3_note (there are many table3_ids and other fields not showed in the query).

So the question is why? Both functions claim to return NULL if there are no non-null values according the manual.

• http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
• http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Is the following information relevevant or am I missing something from the manual?

• Local machine: MySQL Client API version 5.1.44
• Server: MySQL Client API version 5.0.51a

Is the answer so simple that the server handles the COALESCE function like my local machine, but the GROUP_CONCAT function is handled differently because of the non matching MySQL Client API versions?

I now have a working solution so this isn't a real question in a sense that I need to fix this. I'd just like to know why this is as it is. And are there any pitfalls in using COALESCE like I'm using? Is there a danger that arrays are not properly synchronized when printing them using a for loop? (At least a quick testing didn't reveal any problems.)

---

Final notes. I tried using these and some other methods (IFNULL, IS NULL etc.) like suggested for example in these questions:

• GROUP_CONCAT return NULL if any value is NULL
• Rows with null value for group_concat not returned

But the result was the same: works on a local machine but not on the server. Queries below:

//another option for the query
IF(SUM(`table3`.`note` IS NULL) = 0, GROUP_CONCAT(`table3`.`note` SEPARATOR ';'), NULL) AS `table3_note`

//and another one...
ISNULL(GROUP_CONCAT(`table3`.`note` SEPARATOR ';'), '') AS `table3_note`

MySQL manual also says:

Unless otherwise stated, group functions ignore NULL values.

Does this mean COALESCE doesn't ignore NULL values like GROUP_CONCAT does, even if checked? This still doesn't explain the different behaviours of the server and local machine. Or does it?

Answer 1

Here's the query that works properly (doesn't strip the NULLs):

SELECT `table1`.*
  GROUP_CONCAT(COALESCE(`table3`.`id`, '') SEPARATOR ';') AS `t3_id`,
  GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `t3_note`,
  FROM `table1`
    LEFT JOIN `table3` ON `table3`.`id` = `table1`.`id`
      GROUP BY `table1`.`id`

Original question is left intact. It's quite messy and difficult to understand but the query above works for me.