Bug #21516 MySQL sorts -INF as greater than +INF
Submitted: 8 Aug 2006 18:02
Reporter: Sveta Smirnova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.1, 5.0, 4.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution
Triage: Needs Triage: D5 (Feature request)

[8 Aug 2006 18:02] Sveta Smirnova
Description:
change_double_for_sort returns values ordered as:
+INF
NaN
-INF
normal values

How to repeat:
CREATE TABLE `dt` (
  `d` double
);

insert into dt values(1e308), (-1e308), (5.55);
mysql> select * from dt order by d;
+---------+
| d       |
+---------+
| -1e+308 |
|    5.55 |
|  1e+308 |
+---------+
3 rows in set (0.00 sec)

mysql> select d*10 as dd from dt order by dd;
+---------+
| dd      |
+---------+
|  1.#INF |
| -1.#INF |
|    55.5 |
+---------+
3 rows in set (0.03 sec)

Suggested fix:
The following diff for filesort.cc from 5.1.11 modifies
change_double_for_sort() so that the sort order is:
-INF < regular doubles < +INF < NaN

1500,1505c1500,1501
< { /* Set high and move exponent
one up */
< ushort exp_part=(((ushort) tmp[0] << 8) | (ushort) tmp[1] |
< (ushort) 32768);
< exp_part+= (ushort) 1 << (16-1-DBL_EXP_DIG);
< tmp[0]= (uchar) (exp_part >> 8);
< tmp[1]= (uchar) exp_part;
---
> { /* Set high */
> tmp[0] |= 128;
[9 Aug 2006 18:56] James Day
Note that the draft SQL2003 standard requires in section 4.4.1 Introduction to numbers that:

"For every numeric type, the least value is less than zero and the greatest value is greater than zero."

To the extent that these values are possible, MySQL appears to be non-conforming in its ordering of them.

The same appears to apply if MySQL orders -0 as anything other than less than +0, since all positive numbers are greater than all negative numbers, even if their approximate value is zero. No requirement to preserve -0 instead of converting it to +0, though, since that is also a valid approximate value.
[17 Aug 2006 19:00] Trudy Pelzer
Adding proper (and complete) support for handling +/-INF 
and NaN values is required in order to sort such values 
in a proper manner. This is a reasonable feature request
that we will consider adding in a future version.
[18 Aug 2006 22:25] Mark Callaghan
I will welcome the change. And then you can ask CERN to switch and store their data in MySQL.
http://www.informationweek.com/story/IWK20020208S0007