Geometry type comparison bug

[ebocher]

Since the last H2 database version 2.1.212 a new bug appears in the GeoClimate chain

Values of types "GEOMETRY(POLYGON, 2154)" and "GEOMETRY" are not comparable

Currently, I'm not able to isolate this bug and find a way to reproduce it with a test

[katzyn]

In some cases we need to combine values from different sources with different data types, for a UNION, for example.

If both columns have a some subtype of GEOMETRY data type H2 explicitly disallows such combination when combined expressions have different declared SRID.

If only one of them has declared SRID behavior of H2 is inconsistent and depends on geometry type and dimension system constraints due to buggy implementation. If this implementation decided that they are comparable anyway, it produces a geometry data type with a SRID constraint as resulting type. But source expression without a SRID constraint may have values with different SRID, so a data conversion error is possible during query execution.

To resolve this problem we need to determine the expected behavior.

GEOMETRY(type) UNION GEOMETRY(type, SRID) should produce a GEOMETRY(type) or GEOMETRY(type, SRID)?

[ebocher]

Hi @katzyn ,
Thanks a lot for the comment. I'm now able to find a test case.

DROP TABLE IF EXISTS geotable, geotable_area,tmp_geom;
CREATE TABLE geotable (id integer, the_geom GEOMETRY(POINT, 4326));
INSERT INTO geotable VALUES(1, 'SRID=4326;POINT(0 0)'::GEOMETRY),
(2, 'SRID=4326;POINT(10 10)'::GEOMETRY);
CREATE TABLE geotable_area (id integer, the_geom GEOMETRY(GEOMETRY));
INSERT INTO geotable_area VALUES(1, 'POLYGON((0 0, 1 0, 1 1, 0 1,0 0))'::GEOMETRY);
CREATE table tmp_geom (id integer, the_geom GEOMETRY) as 
SELECT a.id ,a.the_geom from geotable as a, geotable_area as b where a.the_geom && b.the_geom;

note that this test works if we set only the GEOMETRY signature on geotable_area

CREATE TABLE geotable_area (id integer, the_geom GEOMETRY);

About your question GEOMETRY(type) UNION GEOMETRY(type, SRID) should produce a GEOMETRY(type) or GEOMETRY(type, SRID)?
I propose to use the minimal commun type so GEOMETRY(type)

Best