From: Tomas Ulin
Date: November 7 2004 10:31am
Subject: Re: Odd group by behavior
List-Archive: http://lists.mysql.com/cluster/1054
Message-Id: <418DF967.7090709@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

If you can give me a fully reproducable example, including create, 
insert,selects.  I will try to reproduce on latest source and file a 
bug-report if neccessary.

(btw, several bugs on group by, limit... has been fixed since 4.1.4)

BR,

T

Chad Martin wrote:

> I suspect I'm breaking some internal mathematical limitations in 
> Cluster.  The following behavior does not occur using MyISAM.  I'm 
> using a nightly build, version 4.1.4.
>
> The system table contains, among other things, cartesian (x, y, z) 
> coordinates of points in space.  I'm trying to perform a fairly 
> complex query based on the distance between these points.
>
> mysql> select a.id, b.id, sqrt(power(a.x-b.x,2) + power(a.y-b.y,2) + 
> power(a.z-b.z,2)) from system as a, system as b where a.id <> b.id 
> limit 10;
> +-----+-----+--------------------------------------------------------------+ 
>
> | id  | id  | sqrt(power(a.x-b.x,2) + power(a.y-b.y,2) + 
> power(a.z-b.z,2)) |
> +-----+-----+--------------------------------------------------------------+ 
>
> | 373 | 336 |                                 
> 177330280520551399424.000000 |
> | 112 | 336 |                                 
> 195355459375818637312.000000 |
> | 197 | 336 |                                 
> 424913106711317250048.000000 |
> | 232 | 336 |                                 
> 640758371265336180736.000000 |
> | 116 | 336 |                                 
> 191529956457724805120.000000 |
> | 267 | 336 |                                 
> 356497850414861713408.000000 |
> | 294 | 336 |                                 
> 312374621370193215488.000000 |
> | 465 | 336 |                                 
> 306278671058715803648.000000 |
> | 341 | 336 |                                 
> 269244036786907316224.000000 |
> | 460 | 336 |                                 
> 189630168063358402560.000000 |
> +-----+-----+--------------------------------------------------------------+ 
>
> 10 rows in set (0.00 sec)
>
> mysql> select a.id, min(sqrt(power(a.x-b.x,2) + power(a.y-b.y,2) + 
> power(a.z-b.z,2))) from system as a, system as b where a.id <> b.id 
> group by a.id limit 10;
> +----+-------------------------------------------------------------------+ 
>
> | id | min(sqrt(power(a.x-b.x,2) + power(a.y-b.y,2) + 
> power(a.z-b.z,2))) |
> +----+-------------------------------------------------------------------+ 
>
> |  1 |                                               
> 100000000000.000000 |
> |  2 |                                               
> 100000000000.000000 |
> |  3 |                                               
> 100000000000.000000 |
> |  4 |                                               
> 100000000000.000000 |
> |  5 |                                               
> 100000000000.000000 |
> |  6 |                                               
> 100000000000.000000 |
> |  7 |                                               
> 100000000000.000000 |
> |  8 |                                               
> 100000000000.000000 |
> |  9 |                                               
> 100000000000.000000 |
> | 10 |                                               
> 100000000000.000000 |
> +----+-------------------------------------------------------------------+ 
>
> 10 rows in set (0.38 sec)
>
> mysql> select min(sqrt(power(a.x-b.x,2) + power(a.y-b.y,2) + 
> power(a.z-b.z,2))) from system as a, system as b where a.id <> b.id;
> +-------------------------------------------------------------------+
> | min(sqrt(power(a.x-b.x,2) + power(a.y-b.y,2) + power(a.z-b.z,2))) |
> +-------------------------------------------------------------------+
> |                                         148428224781635776.000000 |
> +-------------------------------------------------------------------+
> 1 row in set (0.20 sec)
>
> As soon as I start using the group by function, I start getting bogus 
> results.  The values of x, y and z are on the order of 1e19 or so.  
> Does anybody have an idea what's going on?
>
> Thanks,
> Chad Martin
> Arete Studios, Inc.
>