st_union with GROUP BY doesn't work

[GeoffreyBoulay]

Hello,

I'm working on a java application with production database on postgis and test database on H2gis.

I would like to know, why this query return the given exception.

SELECT t.id as id, ST_asGeoJson(ST_union(c.geometry)) as geojson
FROM 
      town t
      INNER JOIN city c ON t.id = c.town_id
GROUP BY t.id

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: La colonne "C.GEOMETRY" doit être dans la liste du GROUP BY
Column "C.GEOMETRY" must be in the GROUP BY list; SQL statement:

ST_union is an aggregation function !

And when I remove the group by condition and replace the id column by constants it's works

SELECT 'foo' as id, ST_asGeoJson(ST_union(c.geometry)) as geojson
FROM 
      town t
      INNER JOIN city c ON t.id = c.town_id

This query work with Postgis.

Does it an H2gis issue ?

Regards,

[ebocher]

In H2GIS ST_Union is a scalar function not an aggregate function as PostGIS offers. You must use ST_UNION(ST_ACCUM(the_geom)) to collect the geometries.