ST_Intersects(geography) returns incorrect result when called multiple times

[realityexists]

r10190 on Windows 7 x64

Run the following query:

WITH segment_line AS
(

	SELECT 1 AS id, 'LINESTRING(22.88333 41.96667,24.876111 42.053361)'::geography AS line
	UNION
	SELECT 2 AS id, 'LINESTRING(22.88333 41.96667,21.32667 42.13667)'::geography AS line
	
)
, borders AS
(
	SELECT 'POLYGON((22.94472 41.34667,22.87528 41.99028,22.87389 41.98472,22.87472 41.98333,22.94472 41.34667))'::geography AS border
)
SELECT id, ST_Intersects(line, border)
FROM segment_line, borders
--WHERE id = 2

ST_Intersects returns "false,false". However, if you run it only for line 2 (uncomment the WHERE line) it returns "true", which is correct. On 2.0.1 it correctly returns "false,true" when run for both lines.

[pramsey]

Example that dodges having force the cache:

WITH objs AS
(
 SELECT 
  'LINESTRING(22.88333 41.96667,21.32667 42.13667)'::geography AS line,
  'POLYGON((22.94472 41.34667,22.87528 41.99028,22.87389 41.98472,22.87472 41.98333,22.94472 41.34667))'::geography AS border	
)
SELECT ST_Distance(line, border), _ST_DistanceTree(line, border)
FROM objs;

Basically we have a big disagreement about distance, presumably because the containment test is failing on the tree side.

[pramsey]

Area of problem

[pramsey]

So, a very narrow triangle, the classic place for things to fail. We should be getting a zero edge/edge distance where the edges cross.

[realityexists]

The original polygon wasn't a narrow triangle - see attached. I got it down to that while simplifying the repro and it continued to happen with fewer and fewer points. In this original repro ST_Distance and _ST_DistanceTree seem to agree, but ST_Intersects still returns the wrong result. Perhaps it's two separate problems.

[robe]

Weird does this happen to you too? I'm testing r10191 on PostgreSQL 9.2beta3, compiled by Visual C++ build 1600, 32-bit

WITH segment_line AS
(

	SELECT 1 AS id, 'LINESTRING(22.88333 41.96667,24.876111 42.053361)'::geography AS line
	UNION ALL
	SELECT 2 AS id, 'LINESTRING(22.88333 41.96667,21.32667 42.13667)'::geography AS line
	UNION ALL
	SELECT 3 AS id,  'LINESTRING(22.88333 41.96667,21.32667 42.13667)'::geography AS line
	
)
, borders AS
(
	SELECT 'POLYGON((22.94472 41.34667,22.87528 41.99028,22.87389 41.98472,22.87472 41.98333,22.94472 41.34667))'::geography AS border
)
SELECT id, ST_Intersects(line, border), ST_Distance(line,border), _ST_DistanceTree(line,border)
FROM segment_line, borders

WHERE id IN( 2,3)

2 and 3 should clearly give the same answer since they are the same geometry and hmm they don't. Something definitely screwy with the cache logic.

 id | st_intersects |  st_distance   | _st_distancetree
----+---------------+----------------+------------------
  2 | t             |              0 |   452.5945844566
  3 | f             | 452.5945844566 |   452.5945844566

[realityexists]

Yes, I get the same results on PostgreSQL 9.1.4 Windows x64.

[robe]

ah nevermind what I said. I think I see what Paul is saying. the ST_Distance and ST_Intersects use the tree cache (if more than one row in a query, which it determines when it sees the second row to switch to cache mode) and since the first tree answer is wrong, subsequent answers are wrong. Note the ST_DistanceUncached gives the same answer for both calls.

WITH segment_line AS
(
        SELECT 2 AS id, 'LINESTRING(22.88333 41.96667,21.32667 42.13668)'::geography AS line
        UNION ALL
	SELECT 1 AS id, 'LINESTRING(22.88333 41.96669,24.876111 42.053364)'::geography AS line
	UNION ALL
	SELECT 3 AS id,  'LINESTRING(22.88333 41.96667,21.32667 42.13668)'::geography AS line
	
)
, borders AS
(
	SELECT 'POLYGON((22.94472 41.34667,22.87528 41.99028,22.87389 41.98472,22.87472 41.98333,22.94472 41.34667))'::geography AS border
)
SELECT id, ST_Intersects(line, border), ST_Distance(line,border)
, _ST_DistanceTree(line,border), _ST_DistanceUncached(line,border)
FROM segment_line, borders;

 id | st_intersects |   st_distance    | _st_distancetree | _st_distanceuncached

----+---------------+------------------+------------------+---------------------
  2 | t             |                0 |   452.5945844566 |                    0
  1 | f             | 452.774393150775 | 452.774393150775 |     452.774393150789
  3 | f             |   452.5945844566 |   452.5945844566 |                    0

I can jury rig it to give the right answer by switching the order of your rows

WITH segment_line AS
(
        SELECT 2 AS id, 'LINESTRING(22.88333 41.96667,21.32667 42.13667)'::geography AS line
        UNION ALL
	SELECT 1 AS id, 'LINESTRING(22.88333 41.96667,24.876111 42.053361)'::geography AS lin	
)
, borders AS
(
	SELECT 'POLYGON((22.94472 41.34667,22.87528 41.99028,22.87389 41.98472,22.87472 41.98333,22.94472 41.34667))'::geography AS border
)
SELECT id, ST_Intersects(line, border)
FROM segment_line, borders

 id | st_intersects
----+---------------
  2 | t
  1 | f

[pramsey]

Should be fixed at r10194

[robe]

I know I'm being a bit nit-picky here, so feel free to dismiss my comment, but shouldn't you also have an entry in tickets.sql for such an important bug?

[pramsey]

The commit includes a cunit test which exercises the case, you can add a tickets.sql one too if you like, but the test coverage is there.

[robe]

Okay will add one later. I like the double-testing just because I've run into issues where CUnit passes and the PostgreSQL one fails and also I can't really test the VC++ PostgreSQL compiled ones reliably under CUnit since Cunit is no guarantee of how it will actually behave under PostgreSQL.