ST_Intersects() in WHERE clause misses results

[xowap]

As explained in this ​stackexchange question, I get inaccurate results when doing a ST_Intersects.

To summarize how to reproduce the issue described in the question, here is a small SQL script

CREATE TABLE "demo" (
    "id" serial NOT NULL PRIMARY KEY,
    "geom" geography(GEOMETRYCOLLECTION,4326),
    "poly" geography(POLYGON,4326),
    "point" geography(POINT,4326)
);

INSERT INTO "demo" ("geom", "poly", "point") VALUES
('GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography, 'POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05))'::geography, NULL),
('GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))'::geography, NULL, 'POINT(40.0045845585 70.0036813508)'::geography);

-- Does not work
SELECT id, ST_AsText(geom) FROM demo WHERE ST_Intersects(geom, 'GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography);
-- Works
SELECT id, ST_AsText(geom) FROM demo WHERE ST_Intersects(geom, 'POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05))'::geography);
-- Works
SELECT id, ST_AsText(geom) FROM demo WHERE ST_Intersects("point", 'GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography);

As indicated in the comments, I have built PostGIS from branches/2.1 at revision 12308.

[robe]

Yah this looks to still be an issue. I suspect its an uncaught case of our poly in poly case noted in #2556 that is tripped up by the geometry collection casing

[pramsey]

I've found the source of this problem (yah, plane) but only got part way through the fix. Still unsure what caused the FLIPPING in your example though robe, the problem I found was just a straight up error in logic (poor handling of multi-* things and collections in the tree distance code) so the failures should be deterministic.

[pramsey]

Any collection type is going to exhibit this, it's pretty non-good.

[robe]

and … you'll work on this at sprint right

[pramsey]

Does r12340 do anything for you?

[xowap]

@pramsey: yes, it looks pretty much fixed for me

The test set works, as well as my real-life application that was initially trying to make that query

[pramsey]

Applied to 2.1 branch at r12343