MySQL Bugs: #45919: Wrong spatial key for big objects

Bug #45919	Wrong spatial key for big objects
Submitted:	2 Jul 2009 16:40	Modified:	12 May 2015 12:16
Reporter:	Andi Kalsch	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S1 (Critical)
Version:	5.1.35 Source distribution (beta)	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	geo, gis, spatial, spatial key

Description:
It seems that the spatial key will be malformed for big multi geometries (MultiPolygon, GeometryCollection) when many objects (for more than 1000) will be indexed.

"Malformed" means that some big objects are treated like in the main MySQL branch - they are found by their MBR instead of their proper geometry.

The behavior is not predictable, because 
1) small objects are treated correctly in all cases
2) big objects are treated correctly for smaller indexes

How to repeat:
I cannot repeat the bug without a link to a table of geo objects which is very huge - ~1 GByte.

The sql which shows how I reproduce the bug locally.

SELECT @geom:=geom
FROM feature_geom WHERE feature = 'relation' AND feature_id = 68156;

SET @p:=GeomFromText('Point(49.64185 6.69685)');

# will be correct - output:
Contains(@geom, @p)
0
SELECT Contains(@geom, @p);

drop table if exists geom;
create table geom like feature_geom;
alter table geom drop key geom;

INSERT INTO geom
SELECT * FROM feature_geom 
WHERE feature = 'relation' AND feature_id >= 68156
ORDER BY feature_id;

alter table geom add spatial key(geom);

# will be correct - output:
count(*)
0
SELECT count(*) FROM geom WHERE Contains(geom, @p);

drop table if exists geom;
create table geom like feature_geom;
alter table geom drop key geom;

# Let's do nearly the same but start with populating from index 10
INSERT INTO geom
SELECT * FROM feature_geom 
WHERE feature = 'relation' AND feature_id >= 10
ORDER BY feature_id;

alter table geom add spatial key(geom);

# will be incorrect - output: 
count(*)
1
SELECT count(*) FROM geom WHERE Contains(geom, @p);

Suggested fix:
It seems that lazy memory management creates an index just for the MBR and ignores the complex geo object.

I cannot suggest a fix.

I have to research a little more and try to post a better way to reproduce it.

Thank you for the report.

Please provide output of SHOW CREATE TABLE geom and dump of it after

INSERT INTO geom
SELECT * FROM feature_geom 
WHERE feature = 'relation' AND feature_id >= 68156
ORDER BY feature_id;

and

INSERT INTO geom
SELECT * FROM feature_geom 
WHERE feature = 'relation' AND feature_id >= 10
ORDER BY feature_id;

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

Posted by developer:
 
Not enough info to reproduce. Closing.