Re: Re: [GENERAL] +/- Inf for float8's

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 rules of 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
values or 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(at)rice(dot)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(at)gmx(dot)net 75262 Uppsala
> http://yi.org/peter-e/ Sweden
>