Bug #35414 MBRContains fails to select records when using index
Submitted: 18 Mar 2008 19:16 Modified: 8 Aug 2012 11:01
Reporter: Jim Knepley Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: GIS Severity:S3 (Non-critical)
Version:5.0.51 and 5.0.51a OS:Other (Linux / Solaris)
Assigned to: Assigned Account CPU Architecture:Any

[18 Mar 2008 19:16] Jim Knepley
Description:
Given this table:
CREATE TABLE _netbounds_test (
  id INTEGER UNSIGNED NOT NULL,
  netpoly POLYGON NOT NULL,
  PRIMARY KEY (id),
  SPATIAL INDEX i_net (netpoly)
);

Populated with data similar to the method described here:
http://jcole.us/blog/archives/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-g...

...

Queries including the "WHERE" clause:
> MBRContains(t.netpoly, POINTFROMWKB(POINT(INET_ATON('10.1.204.29'),0)))
Seem to use an index, but return no data.

Queries including the "WHERE" clause:
> MBRContains(t.netpoly, POINTFROMWKB(POINT(INET_ATON('10.1.204.29'),0)))=1
Returns data, but does not use an index and is therefore very slow.

How to repeat:
CREATE TABLE _netbounds_test
(
  id INTEGER UNSIGNED NOT NULL,
  netpoly POLYGON NOT NULL,
  ip_from INTEGER UNSIGNED NOT NULL,
  ip_to   INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  SPATIAL INDEX i_net (netpoly)
);

INSERT INTO _netbounds_test ( id, netpoly )
SELECT id, GEOMFROMWKB(POLYGON(LINESTRING(
  POINT(net, -1),
  POINT(net + POWER(2,32-cidr)-1, -1),
  POINT(net + POWER(2,32-cidr)-1,  1),
  POINT(net,  1),
  POINT(net, -1)
)))
FROM networks;
(where "networks" contains a collection of RFC 1918 IP subnets, in this case with two networks: 10.0.0.0/10 and 10.1.204.0/23)

SELECT id, ASTEXT(netpoly)
FROM _netbounds_test t
WHERE MBRContains(t.netpoly, POINTFROMWKB(POINT(INET_ATON('10.1.204.29'),0)));

SELECT id, ASTEXT(netpoly)
FROM _netbounds_test t
WHERE MBRContains(t.netpoly, POINTFROMWKB(POINT(INET_ATON('10.1.204.29'),0)))=1;

("explain" on the last two queries shows the differences in the execution plan)
[19 Mar 2008 16:35] Jim Knepley
A more consise better organized demonstration of the problem

Attachment: Spatial trouble.txt (text/plain), 3.88 KiB.

[22 Mar 2008 6:41] Sveta Smirnova
Thank you for the report.

Could  you please provide dumpe of table networks?
[11 Jun 2008 2:38] Miguel Solorzano
Thank you for the feedback. Could you please provide the complete dump (create table and insert data) of the the table networks so we can execute the below command:

INSERT INTO _netbounds_test ( id, netpoly )
SELECT id, GEOMFROMWKB(POLYGON(LINESTRING(
  POINT(net, -1),
  POINT(net + POWER(2,32-cidr)-1, -1),
  POINT(net + POWER(2,32-cidr)-1,  1),
  POINT(net,  1),
  POINT(net, -1)
)))
FROM networks;

thanks in advance.
[15 Apr 2009 21:59] Miguel Solorzano
Thank you for the feedback. Could you please test if the latest release 5.0.77 presents the same issue?. Thanks in advance.
[15 May 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[16 May 2012 9:50] Stephen Reid
This bug still appears to be present in 5.5.20. Simplification of the problem is as follows

SET @bbox = GeomFromText('Polygon((0 0,0 50,50 50,100 0,0 0))');
SELECT FROM locations WHERE MBRContains(@bbox , location );

The above returns no rows but DOES use the spatial index on the location field

SELECT FROM locations WHERE MBRContains(@bbox , location ) = 1;

returns the correct rows but DOES NOT use the spatial index on the location field
[23 May 2012 17:12] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior with data provided by original reporter. Please provide complete test case.
[24 Jun 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[8 Aug 2012 11:01] Alexander Barkov
Duplicate to Bug#64533 Query using spatial index returns empty result