Bug #29070 Error in spatial index
Submitted: 13 Jun 2007 14:11 Modified: 27 Jul 2007 17:59
Reporter: Alejandro Soto
Status: Closed
Category:Server Severity:S1 (Critical)
Version:5.0.41 OS:Sun Solaris (solaris 8)
Assigned to: Georgi Kodinov Target Version:
Tags: spatial, gis

[13 Jun 2007 14: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 14:16] Alejandro Soto 
bug-data-29070.zip FTP uploaded
[14 Jun 2007 22:21] Sveta Smirnova 
Thank you for the report.

Verified as described.
[5 Jul 2007 12: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 14: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 15: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 15:23] Alexander Barkov 
Sorry, need to check it more carefully.
Unticking the "review done" checkbox.
[9 Jul 2007 12: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 15: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 16: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().
[21 Jul 2007 1:45] Bugs System 
Pushed into 5.1.21-beta
[21 Jul 2007 1:49] Bugs System 
Pushed into 5.0.48
[27 Jul 2007 17: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.