Bug #37720 ORDER BY clause not working on a calculation
Submitted: 28 Jun 2008 12:54 Modified: 28 Jun 2008 20:15
Reporter: Oliver Nightingale Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.51 OS:MacOS (10.4.11, PowerPC G5 (3.0))
Assigned to: CPU Architecture:Any
Tags: Calculation, order by

[28 Jun 2008 12:54] Oliver Nightingale 
Description:
I have a table with 2 columns, latitude and longitude, my query will use these two columns to calculate the distance to a given latitude/longitude.  The resulting distances should then be sorted from closest (lowest value of distance) to furthest away.  The query (including the complex distance calculations) is below

SELECT *, (ACOS(least(1,COS(0.898348891269513)*COS(-0.00293599286770486)*COS(RADIANS(stations.lat))*COS(RADIANS(stations.lng))+
 COS(0.898348891269513)*SIN(-0.00293599286770486)*COS(RADIANS(stations.lat))*SIN(RADIANS(stations.lng))+
 SIN(0.898348891269513)*SIN(RADIANS(stations.lat))))*3963.19)
 AS distance FROM stations ORDER BY distance

I would expect to get a list of the stations table columns sorted on distance, instead I get the following

	name	        postcode	lat	                        lng	                         distance
653	Northfleet	DA11 9DY	51.4449195862	0.3372449875	21.863126158711
171	Mudchute	E14 9UW	        51.5005378723	-0.0144590000	6.9188086235556
234	South Quay	E14 9ZP	        51.5005378723	-0.0144590000	6.9188086235556
224	Shadwell	        E1 2QF	        51.5144309998	-0.0583489984	5.5827156346938
96	Gallions   	E6 6FZ	        51.5204391479	0.0670429990	10.679642604536
675	Riddlesdown	CR8 1HN	51.3332710266	-0.1008990034	9.9995899844712
109	Hammer 	W6 8AB	        51.4871368408	-0.2158560008	2.3165020249603

Clearly the distance field is not being sorted.  The calculation itself is correct, however I have tried a similar query but using a simpler calculation, where distance is just lat + lng and the ORDER BY clause did work correctly.

How to repeat:
--create the table
CREATE TABLE IF NOT EXISTS `stations` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `postcode` varchar(255) NOT NULL,
  `lat` decimal(15,10) default NULL,
  `lng` decimal(15,10) default NULL,
  PRIMARY KEY  (`id`)
);

--load table data
--this is just a subset of the data, as there are over 700 rows, if required I can post the whole table dump sql
INSERT INTO `stations` (`id`, `name`, `postcode`, `lat`, `lng`) VALUES
(653, 'Northfleet', 'DA11 9DY', 51.4449195862, 0.3372449875),
(171, 'Mudchute', 'E14 9UW', 51.5005378723, -0.0144590000),
(234, 'South Quay', 'E14 9ZP', 51.5005378723, -0.0144590000),
(224, 'Shadwell', 'E1 2QF', 51.5144309998,-0.0583489984),
(96, 'Gallions', 'E6 6FZ', 51.5204391479, 0.0670429990),	
(675, 'Riddlesdown', 'CR8 1HN', 51.3332710266, -0.1008990034),
(109, 'Hammersmith (District)', 'W6 8AB', 51.4871368408, -0.2158560008);

--run query
SELECT *, (ACOS(least(1,COS(0.898348891269513)*COS(-0.00293599286770486)*COS(RADIANS(stations.lat))*COS(RADIANS(stations.lng))+
 COS(0.898348891269513)*SIN(-0.00293599286770486)*COS(RADIANS(stations.lat))*SIN(RADIANS(stations.lng))+
 SIN(0.898348891269513)*SIN(RADIANS(stations.lat))))*3963.19)
 AS distance FROM stations ORDER BY distance
[28 Jun 2008 13:16] Sveta Smirnova 
Thank you for the report.

I can not repeat described behavior on my side. Please try with current version 5.0.51b and if you still have same problem, run OPTIMIZE TABLE stations, then query again and if you still have the problem provide us your configuration file.
[28 Jun 2008 13:31] Oliver Nightingale 
In what way are you unable to re-create the problem, does the query bring back results that are ordered by distance or is the SQL that I gave for setting up the table and data not working.

My current version is 5.0.51, will 5.0.51b be significantly different to the point where my query will work as expected in the newer version?
[28 Jun 2008 15:48] Sveta Smirnova 
Thank you for the feedback.

> does the query bring back results that are ordered by distance

Yes, the query returns results ordered by distance

> My current version is 5.0.51, will 5.0.51b be significantly different to the point where my query will work as expected in the newer version?

I don't know as I could not repeat the problem, although tried to do it with several different versions since 5.0.45 till current development 5.0.66. This is why I additionally asked about OPTIMIZE TABLE and configuration file. Also would be good if you could reload your SQL dump to, say, different test database and test if problem is repeatable with reloaded table.
[28 Jun 2008 16:46] Oliver Nightingale 
Ok, I ran OPTIMIZE TABLE on stations, which gave the following output:

mysql> optimize table stations;
+--------------------------------+----------+----------+----------+
| Table                          | Op       | Msg_type | Msg_text |
+--------------------------------+----------+----------+----------+
| flatshare_development.stations | optimize | status   | OK       | 
+--------------------------------+----------+----------+----------+
1 row in set (0.35 sec)

then I tried the query again:

mysql> SELECT *, (ACOS(least(1,COS(0.898348891269513)*COS(-0.00293599286770486)*COS(RADIANS(stations.lat))*COS(RADIANS(stations.lng))+  COS(0.898348891269513)*SIN(-0.00293599286770486)*COS(RADIANS(stations.lat))*SIN(RADIANS(stations.lng))+  SIN(0.898348891269513)*SIN(RADIANS(stations.lat))))*3963.19)  AS distance FROM stations ORDER BY distance LIMIT 10;
+-----+-----------------------------+----------+---------------+---------------+-----------------+
| id  | name                        | postcode | lat           | lng           | distance        |
+-----+-----------------------------+----------+---------------+---------------+-----------------+
| 653 | Northfleet                  | DA11 9DY | 51.4449195862 |  0.3372449875 | 21.863126158711 | 
| 171 | Mudchute                    | E14 9UW  | 51.5005378723 | -0.0144590000 | 6.9188086235556 | 
| 234 | South Quay                  | E14 9ZP  | 51.5005378723 | -0.0144590000 | 6.9188086235556 | 
|  60 | Crossharbour & London Arena | E14 9QD  | 51.5005378723 | -0.0144590000 | 6.9188086235556 | 
|  26 | Blackwall                   | E14 9QE  | 51.5005378723 | -0.0144590000 | 6.9188086235556 | 
| 224 | Shadwell                    | E1 2QF   | 51.5144309998 | -0.0583489984 | 5.5827156346938 | 
|  96 | Gallions Reach              | E6 6FZ   | 51.5204391479 |  0.0670429990 | 10.679642604536 | 
| 675 | Riddlesdown                 | CR8 1HN  | 51.3332710266 | -0.1008990034 | 9.9995899844712 | 
| 109 | Hammersmith (District)      | W6 8AB   | 51.4871368408 | -0.2158560008 | 2.3165020249603 | 
|  50 | Chigwell                    | IG7 6QT  | 51.6194801331 |  0.0961980000 | 15.297331752309 | 
+-----+-----------------------------+----------+---------------+---------------+-----------------+
10 rows in set (0.09 sec)

I also tried to recreate the problem in another database using the SQL file I attatched, but I had the same problem, still distance is not being ordered properly.

I've had a look at the change notes for 5.0.51a and b and I can't see anything that seems related to this kind of thing, but then I'm no expert.

Is there any other kind of information I can supply to help diagnose the problem, or anything else I can try to fix this?
[28 Jun 2008 16:51] Sveta Smirnova 
Thank you for the feedback.

Please provide your configuration file.
[28 Jun 2008 16:57] Oliver Nightingale 
configuration file

Attachment: my.cnf (, text), 2.57 KiB.
[28 Jun 2008 17:24] Sveta Smirnova 
Thank you for the feedback.

I still can not repeat described behavior on Intel Mac 10.4.11 32-bit and different Linux machines. Please indicate if you use 32-bit Intel Mac too or provide details about your Mac if it is different.
[28 Jun 2008 17:30] Sveta Smirnova 
Please also add option log-error to your configuration file, restart server and see if there is any error logged.
[28 Jun 2008 17:30] Oliver Nightingale 
It is running on a Mac that does not have Intel, took the following from the system profiler, starting to get out of my depth but I am happy to provide you with any information you might need.

Machine Name:	iMac G5
  Machine Model:	PowerMac12,1
  CPU Type:	PowerPC G5 (3.0)
  Number Of CPUs:	1
  CPU Speed:	1.9 GHz
  L2 Cache (per CPU):	512 KB
  Memory:	1 GB
  Bus Speed:	633 MHz
  Boot ROM Version:	5.2.6f1
[28 Jun 2008 18:18] Oliver Nightingale 
OK, I've bounced the server, this is what came up in the logs...

080628 19:09:56  InnoDB: Starting shutdown...
080628 19:09:58  InnoDB: Shutdown completed; log sequence number 0 3341103
080628 19:09:58 [Note] /Applications/xampp/xamppfiles/sbin/mysqld: Shutdown complete

080628 19:09:58  mysqld ended

080628 19:16:03  mysqld started
080628 19:16:04 [Warning] Setting lower_case_table_names=2 because file system for /Applications/xampp/xamppfiles/var/mysql/ is case insensitive
080628 19:16:04  InnoDB: Started; log sequence number 0 3341103
080628 19:16:04 [Note] /Applications/xampp/xamppfiles/sbin/mysqld: ready for connections.
Version: '5.0.51'  socket: '/Applications/xampp/xamppfiles/var/mysql/mysql.sock'  port: 3306  Source distribution
[28 Jun 2008 18:32] Sveta Smirnova 
Thank you for the feedback.

>  /Applications/xampp/xamppfiles/sbin/mysqld

Could you please try our binaries accessible from http://dev.mysql.com/downloads/mysql/5.0.html to be sure this is MySQL and not XAMPP distribution bug.
[28 Jun 2008 20:03] Oliver Nightingale 
I installed the latest version of MySQL, using a package, recreated the table and loaded data then ran the query, results are ordered correctly, hurrah!

mysql> SELECT *, (ACOS(least(1,COS(0.898348551024557)*COS(-0.00293599284152492)*COS(RADIANS(stations.lat))*COS(RADIANS(stations.lng))+
    ->  COS(0.898348551024557)*SIN(-0.00293599284152492)*COS(RADIANS(stations.lat))*SIN(RADIANS(stations.lng))+
    ->  SIN(0.898348551024557)*SIN(RADIANS(stations.lat))))*3963.19)
    ->  AS distance FROM stations ORDER BY distance asc LIMIT 10;
+----+--------------+----------+---------------+---------------+-----------------+
| id | name         | postcode | lat           | lng           | distance        |
+----+--------------+----------+---------------+---------------+-----------------+
| 11 | Balham       | SW12 9AY | 51.4454307556 | -0.1479270011 | 2.0091501025417 | 
| 16 | Barons Court | W14 9EA  | 51.4884071350 | -0.2064519972 | 2.0167406634622 | 
| 34 | Brixton      | SW9 8HE  | 51.4619827271 | -0.1104869992 | 2.5748401765467 | 
| 17 | Bayswater    | W2 4QH   | 51.5116195679 | -0.1907729954 | 2.9349164957505 | 
| 27 | Bond Street  | W1R 1FE  | 51.5063209534 | -0.1271499991 | 2.9838729836802 | 
| 10 | Baker Street | NW1 5LA  | 51.5220184326 | -0.1597869992 | 3.5076764222636 | 
| 28 | Borough      | SE1 1JX  | 51.5011901855 | -0.0934299976 | 3.8173606555772 | 
| 24 | Blackfriars  | EC4V 4DY | 51.5120468140 | -0.1000699997 | 4.0557848853808 | 
| 13 | Barbican     | EC1A 4JA | 51.5194511414 | -0.0974999964 |  4.498815501067 | 
| 12 | Bank         | EC3V 3LA | 51.5133590698 | -0.0864489973 | 4.5555545178939 | 
+----+--------------+----------+---------------+---------------+-----------------+
10 rows in set (0.00 sec)

Looks like it must be a bug in the xampp distribution of MySQL, I'll contact them and let them know, many thanks for your help.
[28 Jun 2008 20:15] Sveta Smirnova 
Thank you for the feedback.

Closed as "Not a Bug" because last comment.