H2 - Why GROUP_CONCAT rejects rows with NULL values?

Question

Run this script:

drop table if exists foo cascade;
create table foo(
       id int NOT NULL auto_increment,
       start date NOT NULL,
       end date
);

insert into foo(start,end) values('2007-01-01', '2007-12-31');
insert into foo(start,end) values('2007-01-01', NULL);

SELECT
     COUNT(*) nb,
     GROUP_CONCAT(
             id || '-' || start || '-' || end
             SEPARATOR CHAR(10)
      ) final
FROM
     foo;

Why does H2 returns this

-------------------------------
|NB |  FINAL                  |
-------------------------------
|2  | 1-2007-01-01-2007-12-31 |
-------------------------------

instead of

-------------------------------
|NB |  FINAL                  |
-------------------------------
|2  | 1-2007-01-01-2007-12-31 |
|   | 2-2007-01-01-NULL       |
-------------------------------

?

How should I change my query for having expected result ?

Why should `NB` have the value 2 on the first row of your desired result? Also, what would make the rows separate rows? You'll need something to `GROUP BY`. — Joachim Isaksson, Jul 09 '13 at 16:47
@JoachimIsaksson I expect one row only. The first column with 2 and the second column with one string containing a CHAR(10). — Stephan, Jul 09 '13 at 22:17

score 4 · Accepted Answer · answered Jul 10 '13 at 07:41

For H2, the operator || by default returns NULL if one operators is NULL. This behavior is the same as in many other database, including PostgreSQL.

However, MySQL doesn't work like that. H2 supports a compatibility mode for MySQL where the behavior is different. Please note H2 is still not 100% compatible with MySQL when using the MySQL mode, but this aspect is changed.

If you run the statements above with the MySQL mode (set mode mysql or append ;mode=mysql in the database URL) then the result is:

2   1-2007-01-01-2007-12-31
    2-2007-01-01-

so that NULL is converted to an empty string.

Probably even better is to not rely on the MySQL mode, but use CONCAT instead of ||. This is the more standard way, as it should work with other databases as well:

concat(id, '-', start, '-', end)

H2 - Why GROUP_CONCAT rejects rows with NULL values?

1 Answers1