[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
 Keywords:              |
------------------------+---------------------------
 Reported by Tom van Tilburg in the maillist:
 https://lists.osgeo.org/pipermail/postgis-devel/2018-December/027568.html

 Same query outputs different values for st_intersects depending on the
 plan.

 {{{
 crap=# Select ST_Intersects(ag, bg), acid, bcid FROM
 (
 WITH data AS (
 SELECT 1 as cid,720::bigint as blockid, ST_GeomFromText('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))')
 geom
 UNION ALL
 SELECT 2, 720, ST_GeomFromText('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))')
 UNION ALL
 SELECT 3, 720, ST_GeomFromText('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))')
 )
 SELECT
 a.cid as acid, b.cid as bcid,
 a.geom as ag, b.geom as bg
 FROM data a, data b
 WHERE a.cid != b.cid
 AND a.blockid = b.blockid
 AND a.blockid::int = 720
 ) _A;
 st_intersects | acid | bcid
 ---------------+------+------
 f             |    3 |    1
 t             |    2 |    1
 f             |    3 |    2
 t             |    1 |    2
 f             |    2 |    3
 t             |    1 |    3
 (6 rows)

 crap=# set enable_hashjoin=off;
 SET
 crap=# Select ST_Intersects(ag, bg), acid, bcid FROM
 (
 WITH data AS (
 SELECT 1 as cid,720::bigint as blockid, ST_GeomFromText('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))')
 geom
 UNION ALL
 SELECT 2, 720, ST_GeomFromText('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))')
 UNION ALL
 SELECT 3, 720, ST_GeomFromText('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))')
 )
 SELECT
 a.cid as acid, b.cid as bcid,
 a.geom as ag, b.geom as bg
 FROM data a, data b
 WHERE a.cid != b.cid
 AND a.blockid = b.blockid
 AND a.blockid::int = 720
 ) _A;
 st_intersects | acid | bcid
 ---------------+------+------
 t             |    1 |    2
 t             |    1 |    3
 t             |    2 |    1
 t             |    2 |    3
 f             |    3 |    1
 t             |    3 |    2
 (6 rows)
 }}}


 I've tested it with PG11 - Postgis trunk and with PG10 - Postgis 2.4.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4269>
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.