Inconsistent results when using empty geometries with different SRIDS

[samuelspurling]

I first noticed this issue when upgrading from PostGIS 2 to 3.

Running the following on PostGIS 2 results in false, whereas on PostGIS 3 it returns an mixed SRID error (which I understand should probably be the correct response).

SELECT st_dwithin(st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'), 27700), st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'), 0), 0.01);	

This caused some errors in our codebase (our fault for mixing the SRIDs). However, I have now realised that I think it returns the incorrect response sometimes in PostGIS 3 also.

In PostGIS 2 it used the wrapper around the st_dwithin function with:

SELECT $1 OPERATOR(public.&&) public.ST_Expand($2,$3) AND $2 OPERATOR(public.&&) public.ST_Expand($1,$3) AND public._ST_DWithin($1, $2, $3)

which filtered out all the empty geometries as they would not get past the && operator with the bounding boxes. Basically this meant our dodgy code managed to fluke its way through before.

However, because of the addition of using support functions in PostGIS 3 this now means that the response for empty geometries with mixed SRIDs is now not consistent. When using an index, it is filtered out with no error but when called without using an index it returns an error.

Proof of concept below:

-- Mixed SRID set of a LINESTRING of SRID 27700 and MULTILINESTRING EMPTY of SRID 0 result in error
SELECT st_dwithin(st_setsrid(ST_GeomFromText('LINESTRING (798.7163221763004 206.6033707592214, 862.7163221763004 208.6033707592214)'), 27700), st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'), 0), 0.01);	
-- ERROR: LWGEOM_dwithin: Operation on mixed SRID geometries (LineString, 27700) != (MultiLineString, 0)

-- Create a dummy test data set of 100,000 LINESTRINGs with an index on the geom column
CREATE TABLE test_table_lines (
	initial_geom geometry(point, 27700) NOT NULL,
	geom geometry(linestring, 27700)
);

CREATE INDEX test_table_lines_geom_idx ON public.test_table_lines USING gist (geom);

INSERT INTO test_table_lines
SELECT (st_dump(st_generatepoints(st_geomfromtext('POLYGON((0 0, 0 1000, 1000 1000, 1000 0, 0 0))', 27700), 100000))).geom;

UPDATE test_table_lines
SET geom = st_makeline(
	initial_geom,
	st_setsrid(
		st_makepoint(
			st_x(initial_geom) + floor(random() * 100 + 1)::int,
			st_y(initial_geom) + floor(random() * 100 + 1)::int
		),
	27700)
);

-- Now the mixed SRID set does not error, even though the data input matches the top query
SELECT *
FROM test_table_lines t
WHERE st_dwithin(st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'), 0), t.geom, 1);

[robe]

Okay so with the index, no fail.

And when drop index

DROP INDEX test_table_lines_geom_idx;

SELECT *
FROM test_table_lines t
WHERE st_dwithin(st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'), 0), t.geom, 1);

Bam fail with:

ERROR:  LWGEOM_dwithin: Operation on mixed SRID geometries (MultiLineString, 0) != (LineString, 27700)
SQL state: XX000

That said yes it's a regression and the behavior is not the same. That said is this something we can even fix even if we cared to fix it without some dier consequences? I think the answer is NO.

So marking this as a won't fix.