Bug #32032 OpenGIS Contains() does not work on MultiPolygons; disconnects in some cases.
Submitted: 1 Nov 2007 14:38 Modified: 8 Aug 2012 10:34
Reporter: Justin Bronn Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S3 (Non-critical)
Version:5.1.23-beta-GIS-log, 4.1.23 OS:Any (SunOS 5.11 snv_71, Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: contains, disconnect, gis, mbrcontains, multipolygon, opengis, performance, polygon
Triage: Triaged: D2 (Serious)

[1 Nov 2007 14:38] Justin Bronn
Description:
The beta OpenGIS function Contains() appears to not function correctly on MultiPolygon geometries.  Specifically, it appears to revert to MBR-based functionality.  In certain cases, the server forces a disconnect because of the query.

I compiled the source on Solaris 5.11, x86 (compiled as 32-bit: "./configure --prefix=/usr/local/mysql; gmake install").  Have yet to confirm on other platforms.

How to repeat:
mysql> set @mpoly = GeomFromText('MULTIPOLYGON(((0 0, 0 5, 5 5, 5 0, 0 0)), ((6 6, 6 11, 11 11, 11 6, 6 6)))');
mysql> set @pnt = GeomFromText('POINT(5 10)');
mysql> SELECT MBRContains(@mpoly, @pnt); -- should be true
+---------------------------+
| MBRContains(@mpoly, @pnt) |
+---------------------------+
|                         1 |
+---------------------------+
mysql> SELECT Contains(@mpoly, @pnt); -- should be false
+------------------------+
| Contains(@mpoly, @pnt) |
+------------------------+
|                      1 |
+------------------------+

Now for the more worrisome aspect:

mysql> set @pnt = GeomFromText('POINT(100 100)');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Contains(@mpoly, @pnt); -- should force a disconnect
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
I haven't poked around in the source, so unfortunately, no suggestions.
[1 Nov 2007 16:03] Hartmut Holzgraefe
Visualization: the two squares form the multi-polygon, 
and the 'X' is the point which is obviously outside
the polygons

                       1 1
   0 1 2 3 4 5 6 7 8 9 0 1
 0 +---------+
   |         |
 1 |         |
   |         |
 2 |         |
   |         |
 3 |         |
   |         |
 4 |         |
   |         |
 5 +---------+
 
 6             +---------+
               |         |
 7             |         |
               |         |
 8             |         |
               |         |
 9             |         |
               |         |
10          X  |         |
               |         |
11             +---------+
[1 Nov 2007 16:05] Hartmut Holzgraefe
Verified the wrong result but not the disconnect.
The CONTAINS() query takes about 25s(!) on my machine though, maybe this massive delay can eventually cause disconnects depending on server hardware and load?
[1 Nov 2007 16:32] Justin Bronn
I neglected to mention that the disconnect is immediate and occurs as soon as the query is executed (at least on Solaris) -- it is not the result of a timeout.
[4 Nov 2007 21:31] John Powell
I have had the same disconnect problem, both on a build I made on ubuntu linux and on a downloaded windows binary. In my case I am using a real world spatial database with millions of geometric objects in, mulitpolygons, polygons, lines and points. The contains and intersects functions appear to work correctly and my queries work fine if only around 50 rows are returned. However, queries that would return bigger result sets completely hang my system, cpu 100%, massive ram usage until either the query loses its connection or mysqld crashes. I am currently writing a stored procedure to provide a verifiable test case for this and will include my system logs.
[24 Nov 2007 15:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38427

ChangeSet@1.2639, 2007-11-24 19:26:05+04:00, holyfoot@mysql.com +1 -0
  Bug #32032 OpenGIS Contains() does not work on MultiPolygons; disconnects in some cases.
  
  Gis_multi_* objects have an error in ::store_shapes() function
  implementation that produced a very long list of objects,
  which usually leads to crash.
[31 May 2008 2:06] Jackey Cheung
I'm using the win32 binary (mysql-5.1.23-beta-GIS-GIS-win32) on an XP SP2.

It returns NULL while checking against POINT(100 100). Also, it seems to return NULL when check against very polygons with huge amount of nodes.
[8 Jun 2009 10:44] Tomas Ulin
Changing status to PQ as the bug is fixed and is pushed to the future GIS tree
[8 Aug 2012 10:34] Alexander Barkov
This bug was earlier partially fixed in mysql-5.1 (the crash),
as well as in mysql-5.6.0 (with adding of precise GIS operations).
[5 Oct 2012 16:06] Paul Dubois
Noted in 5.6.1 changelog.

Contains() failed for multipolygon geometries.