SELECT * WITH(POLYGON with HOLES) : Error

[Boni Gopalan]

The test dataset contains 1000 random rectangles within X  [0,10000], Y  [0,10000]. I would like to query for rectangles within a polygon consisting of an outer ring and a hole within.

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1))'
)
) = 1

Result: 1000 [As expected]

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 1

Result: 248 [As expected]


SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 1

Result: 1000 [Expected : 752 since 248 falls inside the hole]


If I run

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1)'
)
) = 1
AND
within(tile.LOCATION,
GeomFromText(
'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 0

Then result is 0

Where as

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 1
AND
within(tile.LOCATION,
GeomFromText(
'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 0

Result is 752, exactly what I’d expect!!

Can anyone help me understand this behavior?