After creating the index of GIST, ST_Equals gives the incorrect answer.

[Wenjing]

Consider the following statements:

DROP TABLE IF EXISTS t; 
CREATE TABLE t (id int, geom geometry);
INSERT INTO t (id, geom) VALUES (1,ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
INSERT INTO t (id, geom) VALUES (2,NULL);
INSERT INTO t (id, geom) VALUES (3,ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
INSERT INTO t (id, geom) VALUES (4,NULL);
SELECT COUNT(*)         FROM t As a1          RIGHT OUTER JOIN t As a2 ON ST_Equals(a1.geom, a2.geom)          WHERE   a1.id <> a2.id;
-- result{2}
CREATE INDEX idx ON t USING GIST (geom);
SELECT COUNT(*)         FROM t As a1          RIGHT OUTER JOIN t As a2 ON ST_Equals(a1.geom, a2.geom)          WHERE   a1.id <> a2.id;
-- actual{0}; expected{2}

After creating the index of GIST, ST_Equals gives the incorrect answer. The correct equal pairs could be (1,3), (3,1), while PostGIS gives 0 rows using COUNT(*).

The version is the newest on GitHub:

POSTGIS="3.5.0dev 3.4.0rc1-818-g6c2e935b6" [EXTENSION] PGSQL="170" GEOS="3.13.0dev-CAPI-1.18.0" PROJ="8.2.1
NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org/ USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/
share/proj/proj.db" LIBXML="2.9.13"

[pramsey]

Smaller replicator

DROP TABLE IF EXISTS t; 
CREATE TABLE t AS 
  SELECT 1 AS id, 'POINT EMPTY'::geometry AS geom;
CREATE INDEX idx ON t USING GIST (geom);
SELECT * FROM t WHERE geom ~= 'POINT EMPTY'::geometry;
SET enable_seqscan = false;
SELECT * FROM t WHERE geom ~= 'POINT EMPTY'::geometry;

[pramsey]

​https://github.com/postgis/postgis/pull/755

[Paul Ramsey <pramsey@…>]

In 38d9c69/git:

More support for EMPTY keys in 2d index. Closes #5632.