[postgis-tickets] [PostGIS] #4269: Inconsistent result for ST_Intersects depending on the plan

[PostGIS]

#4269: Inconsistent result for ST_Intersects depending on the plan
-------------------------+---------------------------
  Reporter:  Algunenano  |      Owner:  pramsey
      Type:  defect      |     Status:  new
  Priority:  critical    |  Milestone:  PostGIS 2.4.7
 Component:  postgis     |    Version:  2.5.x
Resolution:              |   Keywords:
-------------------------+---------------------------

Comment (by pramsey):

 Just skipping the whole join thing, which I feel is a red herring, you can
 see fun stuff happening in this example:
 {{{
 WITH data AS (
 SELECT 1 as cid, 'POLYGON Z ((122427.759 489209.498
 14.5066901703871,122395.957 489222.943 14.3997094723791,122399.649
 489231.676 4.98556790825181,122431.452 489218.231
 5.09216481886026,122427.759 489209.498 14.5066901703871))'::geometry geom
 UNION ALL
 SELECT 2, 'POLYGON Z ((122427.027 489216.053 5.97863674861823,122419.873
 489219.077 6.03923989893278,122421.6 489223.163
 1.83027232084998,122428.754 489220.138 1.77054008712955,122427.027
 489216.053 5.97863674861823))'::geometry
 UNION ALL
 SELECT 3, 'POLYGON Z ((122422.267 489220.741 0,122424.396 489219.847
 0,122424.396 489219.847 30,122422.267 489220.741 30,122422.267 489220.741
 0))'::geometry
 ORDER BY cid
 )
 SELECT a.cid, b.cid, ST_Relate(a.geom, b.geom) AS relate,
 _ST_Intersects(a.geom, b.geom)
 FROM data a, data b
 ORDER BY a.cid, b.cid;

  cid | cid |  relate   | _st_intersects
 -----+-----+-----------+----------------
    1 |   1 | 2FFF1FFF2 | t
    1 |   2 | 212101212 | t
    1 |   3 | FF2FF1FF2 | t
    2 |   1 | 212101212 | t
    2 |   2 | 2FFF1FFF2 | t
    2 |   3 | FF2FF1FF2 | t
    3 |   1 | FFFFFF212 | f
    3 |   2 | FFFFFF212 | t
    3 |   3 | FFFFFFFF2 | t
 (9 rows)
 }}}
 Note how the relate matrix of polygon 3 vs itself is not the standard
 pattern of the other two. The invalid third polygon is problematic. I
 don't see how this isn't just a known issue... invalid inputs lead to odd
 results, like non-commutative tests on intersects.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4269#comment:8>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.