ST_Union in aggregate form treats NULL as EMPTY

[gabrimonfa]

ST_Union of two geometries, one of which is null, returns null, using the same semantic for null used in SQL.

However, the aggregate form of ST_Union returns the other geometry, treating null geometry as an EMPTY geometry.

\pset null '<<NULL>>'

SELECT ST_AsText(ST_Union(NULL::geometry,'POLYGON((0 0,0 1,1 1,1 0,0 0))'::geometry));
 st_astext
-----------                                                                                                                                                                        
 <<NULL>>                                                                                                                                                                          
(1 riga)
 
SELECT ST_AsText(ST_Union(g)) FROM (SELECT NULL::geometry as g UNION SELECT 'POLYGON((0 0,0 1,1 1,1 0,0 0))'::geometry as g) foo;
           st_astext            
--------------------------------
 POLYGON((0 0,0 1,1 1,1 0,0 0))

Expected result was that it returns null, also because EMPTY geometries do exists. See http://trac.osgeo.org/postgis/wiki/DevWikiEmptyGeometry

If this behavior is intended, we should probably state it clearly in the documentation

[nw]

Aggregates generally ignore nulls where they wouldn't really be meaningful, or keep as a null if that makes sense. I would be surprised if ST_Union returned null if one of the inputs was null.

select string_agg(i,', ') from (select generate_series(1,3)::text as i union all select null union all select generate_series(1,3)::text as i) as i;

string_agg

---

1, 2, 3, 1, 2, 3

(1 row)

I think treating null as empty is the right thing to do here.

[robe]

The general rule is that NULLs are simply ignored in aggregates (so its not empty), its as if the record is just not there. So PostGIS works as expected.

ST_Union(geom1,geom2) IS NOT an aggregate and as such it doesn't throw away anything. In fact it doesn't even go into the function because its marked as a STRICT (meaning any NULL inputs result in NULL out).

I'll just flag this as a documentation issue to be clarified, but IT IS by design.

[gabrimonfa]

Ok, thank you all for the clarification. This behaviour is correct as per SQL standard and the same in almost all implementation.

Since NULL handling maybe a source of confusion at first glance, clarifying it in the documentation would be perfect.

[robe]

done for 2.1 at r11383

[robe]

done for 2.0 at r11618.