MySQL Bugs: #32032: OpenGIS Contains() does not work on MultiPolygons; disconnects in some cases.

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

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.

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             +---------+

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?

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.

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.

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.

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.

Changing status to PQ as the bug is fixed and is pushed to the future GIS tree

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).

Noted in 5.6.1 changelog.

Contains() failed for multipolygon geometries.