mysql group_concat null_MySQL GROUP_CONCAT與COALESCE有關NULL值

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.

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:

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`

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?

解決方案

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.