MySQL Bugs: #21516: MySQL sorts -INF as greater than +INF

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

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;

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.

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.

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