Missing null values in a GroupConcat

Question

I have a table where I registry when an event start and when ends. I want a list of the events that ends between my date range or are in progress yet, but those that hasn't ended yet are wrong because the null value is missed in the group concat.

How can I get the null values with grouping by entity_id?

| id | entity_id | start                | end                  |
|----|-----------|----------------------|----------------------|
| 1  | 10        | 2020-05-22T23:50:00Z | 2020-05-23T00:15:00Z |
| 2  | 20        | 2020-05-22T23:30:00Z | 2020-05-23T00:50:00Z |
| 3  | 10        | 2020-05-23T01:00:00Z | 2020-05-23T01:10:00Z |
| 4  | 20        | 2020-05-23T01:30:00Z | 2020-05-23T01:50:00Z |
| 5  | 20        | 2020-05-23T02:00:00Z | null                 |
| 6  | 10        | 2020-05-23T05:00:00Z | null                 |

My query:

SELECT entity_id,   
SUBSTRING_INDEX(GROUP_CONCAT(`start` ORDER BY `start` DESC SEPARATOR '||'), '||', 1) AS `start`,
SUBSTRING_INDEX(GROUP_CONCAT(`end` ORDER BY `start` DESC SEPARATOR '||'), '||', 1) AS `end`
FROM my_table
WHERE `start` <= '2020-05-23 23:59:59'  
AND ( `end` IS NULL OR `end` BETWEEN '2020-05-23 00:00:00' AND '2020-05-23 23:59:59' )
GROUP BY entity_id ;

This is the query result

| entity_id | start               | end                 |
|-----------|---------------------|---------------------|
| 10        | 2020-05-23 05:00:00 | 2020-05-23 01:10:00 |
| 20        | 2020-05-23 02:00:00 | 2020-05-23 01:50:00 |

But this is what should be

| entity_id | start               | end                 |
|-----------|---------------------|---------------------|
| 10        | 2020-05-23 05:00:00 | null                |
| 20        | 2020-05-23 02:00:00 | null                |

I created an example: http://sqlfiddle.com/#!9/e04be2/1/0

[GROUP_CONCAT()](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat) skips NULL values, this is documented: *This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.* — Akina, Jun 02 '20 at 07:33

score 2 · Accepted Answer · answered Jun 02 '20 at 07:35

2

GROUP_CONCAT() skips NULL values.

You can use IFNULL to convert the null values to an explicit string 'null'.

SELECT entity_id,   
SUBSTRING_INDEX(GROUP_CONCAT(`start` ORDER BY `start` DESC SEPARATOR '||'), '||', 1) AS `start`,
SUBSTRING_INDEX(GROUP_CONCAT(IFNULL(`end`, 'null') ORDER BY `start` DESC SEPARATOR '||'), '||', 1) AS `end`
FROM my_table
WHERE `start` <= '2020-05-23 23:59:59'  
AND ( `end` IS NULL OR `end` BETWEEN '2020-05-23 00:00:00' AND '2020-05-23 23:59:59' )
GROUP BY entity_id ;

answered Jun 02 '20 at 07:35

Barmar

741,623
53
500
612

That could work, but not in this particular case. In my api (java) that would not map to the java model because "end" is a date, not a string. I need the null, as null – FranAguiar Jun 02 '20 at 07:44
Since `GROUP_CONCAT()` returns a string, you can't have an actuall NULL value in it. – Barmar Jun 02 '20 at 07:57
Even the non-null value is a string, not a date. – Barmar Jun 02 '20 at 07:58
1

@FranAguiar Revert from `'null'` back to `null` after `SUBSTRING_INDEX` using outer `NULLIF()` function. *In my api (java) that would not map to the java model because "end" is a date, not a string* Nothing prevents the use of some pre-defined datatype literal (for example, in far future) as "there must be null" marker. – Akina Jun 02 '20 at 08:51
Yes, my idea was to try that approach, with a date in the future as default. Thanks – FranAguiar Jun 02 '20 at 11:42
Now I'm wondering why you're using `GROUP_CONCAT` and `SUBSTRING_INDEX` in the first place. Reviewing your query, it seems like you just want the last row by start date for each entity id. – Barmar Jun 02 '20 at 16:18
See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 – Barmar Jun 02 '20 at 16:18

Missing null values in a GroupConcat

1 Answers1