Re: Re: [GENERAL] +/- Inf for float8's - Mailing list pgsql-hackers

On Tue, Aug 22, 2000 at 02:16:44PM +0200, Peter Eisentraut wrote:
> Ross J. Reedstrom writes:
> 
> > Fixing sorts is a bit tricker, but can be done: Currently, I've hacked
> > the float8lt and float8gt code to sort NaN to after +/-Infinity. (since
> > NULLs are special cased, they end up sorting after NaN). I don't see
> > any problems with this solution, and it give the desired behavior.
> 
> SQL 99, part 5, section 17.2 specifies that the sort order for ASC and
> DESC is defined in terms of the particular type's < and > operators.
> Therefore the NaN's must always be at the end. (Before or after NULL is
> implementation-defined, btw.)


I'm not sure what your suggesting, Peter.  Which is 'the end'? And how does
'Therefore' follow from considering the type behavior of NaN and the < and
> operators ? 

I think your suggesting that NaN always sort to one end, either greater
than Infinity or less than -Infinity, regardless of sort direction. 
Therefore, depending on the direction of ORDER BY, NaNs will be returned
either be first or last, not always last, as I've currently implemented.

I agree with this, but my reason comes from the required treatment of NULLs.  

My reasoning is as follows:

The standard says (17.2):
    The relative position of rows X and Y in the result is determined by    comparing XV(i) and YV(i) according to the
rulesof Subclause 8.2,    "<comparison predicate>", in ISO/IEC 9075-2, where the <comp op>    is the applicable <comp
op>for K(i), [...]
 

and Subclause 8.2 says:
        2) Numbers are compared with respect to their algebraic value.

However, NaN is _not_ algebraically > or < any other number: in fact,
General Rule 1. of subclause 8.2 does deal with this:
            5) X <comp op> Y is_unknown if X <comp op> Y is neither               true_ nor false_ .

So, we're left with not knowing where to put NaN.

However, the only other case where the comparision is unknown is:
           a) If either XV or YV is the null value, then                 X <comp op> Y is unknown_ .

And, going back to section 17.2:
    [...] where the <comp op> is the applicable <comp op> for K(i),    with the following special treatment of null
values.Whether a    sort key value that is null is considered greater or less than a    non-null value is
implementation-defined,but all sort key values    that are null shall either be considered greater than all non-null
valuesor be considered less than all non-null values.
 

So, NULLs go at one end (less or greater), always, so NaN should as well.
And NULL will go outside them, since NULLs are required to be considered
greater than (in our case) all non-null values (including NaN).

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

> Ross J. Reedstrom writes:
> 
> > Fixing sorts is a bit tricker, but can be done: Currently, I've hacked
> > the float8lt and float8gt code to sort NaN to after +/-Infinity. (since
> > NULLs are special cased, they end up sorting after NaN). I don't see
> > any problems with this solution, and it give the desired behavior.
> 
> SQL 99, part 5, section 17.2 specifies that the sort order for ASC and
> DESC is defined in terms of the particular type's < and > operators.
> Therefore the NaN's must always be at the end. (Before or after NULL is
> implementation-defined, btw.)
> 
> 
> -- 
> Peter Eisentraut                  Sernanders väg 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden
>