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

[2 Jul 2009 16:40] Andi Kalsch
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.
[2 Jul 2009 18:00] Andi Kalsch
I have to research a little more and try to post a better way to reproduce it.
[3 Jul 2009 9:45] Sveta Smirnova
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;
[3 Aug 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".
[12 May 2015 12:16] Norvald Ryeng
Posted by developer:
 
Not enough info to reproduce. Closing.