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.
