MySQL Bugs: #35414: MBRContains fails to select records when using index

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

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)

A more consise better organized demonstration of the problem

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

Thank you for the report.

Could  you please provide dumpe of table networks?

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.

Thank you for the feedback. Could you please test if the latest release 5.0.77 presents the same issue?. Thanks in advance.

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

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

Thank you for the feedback.

I can not repeat described behavior with data provided by original reporter. Please provide complete test case.

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

Duplicate to Bug#64533 Query using spatial index returns empty result