Bug #29070 Error in spatial index
Submitted: 13 Jun 2007 12:11 Modified: 27 Jul 2007 15:59
Reporter: Alejandro Soto Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.41 OS:Solaris (solaris 8)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: gis, spatial

[13 Jun 2007 12:11] Alejandro Soto 
Description:
Distincts row number is returned in a spatial query. I tried rebuilding the spatial index but it doesn't work in solaris 8.

Nevertheless,rebuilding the spatial index in windows xp and linux(Fedora Core 6) works fine and returns the same number of rows. :|

I think that when MySQL use the Spatial Index, the spatial index doesn't return the right results.

How to repeat:
unzip temp_table.zip into a MySQL Database and tried with de selects in the file error_mbrintersects.sql
[13 Jun 2007 12:16] Alejandro Soto 
bug-data-29070.zip FTP uploaded
[14 Jun 2007 20:21] Sveta Smirnova 
Thank you for the report.

Verified as described.
[5 Jul 2007 10:19] Bugs System 
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/30360

ChangeSet@1.2516, 2007-07-05 13:18:54+03:00, gkodinov@magare.gmz +3 -0
  Bug #29070: Error in spatial index 
  The MBR for a key is stored on disk. Therefore it must be in 
  platform independent byte order.
  Fixed by reading and writing to/from the MBR in platform
  independent byte order.
[5 Jul 2007 12:58] Alexander Barkov 
The patch looks ok to push for me.

Please fix a small coding style mismatch before push:

> diff -Nrup a/myisam/sp_key.c b/myisam/sp_key.c
> --- a/myisam/sp_key.c	2006-12-23 21:04:06 +02:00
> +++ b/myisam/sp_key.c	2007-07-05 13:18:53 +03:00
<skip>
> @@ -116,11 +116,14 @@ stored in "well-known binary representat
>  static int sp_mbr_from_wkb(uchar *wkb, uint size, uint n_dims, double *mbr)
>  {
>    uint i;
> +  double dbl_max= DBL_MAX, neg_dbl_max= -DBL_MAX;
>  
>    for (i=0; i < n_dims; ++i)
>    {
> -    mbr[i * 2] = DBL_MAX;
> -    mbr[i * 2 + 1] = -DBL_MAX;
> +    char *mbr_ptr= (char *) (mbr + (i * 2));
> +    float8store(mbr_ptr, dbl_max);
> +    mbr_ptr += sizeof (double);

Wrong coding style, please fix to:

       mbr_ptr+= sizeof(double);

<skip>
[5 Jul 2007 13:07] Bugs System 
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/30378

ChangeSet@1.2516, 2007-07-05 16:06:51+03:00, gkodinov@magare.gmz +3 -0
  Bug #29070: Error in spatial index 
  The MBR for a key is stored on disk. Therefore it must be in 
  platform independent byte order.
  Fixed by reading and writing to/from the MBR in platform
  independent byte order.
[5 Jul 2007 13:23] Alexander Barkov 
Sorry, need to check it more carefully.
Unticking the "review done" checkbox.
[9 Jul 2007 10:24] Bugs System 
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/30523

ChangeSet@1.2516, 2007-07-09 13:24:04+03:00, gkodinov@magare.gmz +3 -0
  Bug #29070: Error in spatial index
  1. Threat MBR for a key as double[] and convert it only
  when about to store it on disk.
  2. Remove the redundant function get_double().
[9 Jul 2007 13:28] Bugs System 
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/30528

ChangeSet@1.2516, 2007-07-09 16:28:38+03:00, gkodinov@magare.gmz +3 -0
  Bug #29070: Error in spatial index
  1. Threat MBR for a key as double[] and convert it only
  when about to store it on disk.
  2. Remove the redundant function get_double().
[9 Jul 2007 14:41] Bugs System 
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/30531

ChangeSet@1.2516, 2007-07-09 17:41:24+03:00, gkodinov@magare.gmz +3 -0
  Bug #29070: Error in spatial index
  1. Threat MBR for a key as double[] and convert it only
  when about to store it on disk.
  2. Remove the redundant function get_double().
[20 Jul 2007 23:45] Bugs System 
Pushed into 5.1.21-beta
[20 Jul 2007 23:49] Bugs System 
Pushed into 5.0.48
[27 Jul 2007 15:59] Paul DuBois 
Noted in 5.0.48, 5.1.21 changelogs.

A byte-order issue in writing a spatial index to disk caused
bad index files on some systems.