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

Tom -
Thanks for the review. Here's a new version of the patch, fixing the two
you objected to. Unfotunately, I seem to have found another corner case
in the existing code that needs fixing. Here's the one line version:

Use of an index in an ORDER BY DESC result changes placement of NULLs
(and NaNs, now) from last returned to first returned tuples

Long version:

While examining the output from ORDER BY queries, both using and not using
an index, I came across a discrepancy: the explicit handling of NULLs in
the tuplesort case always sorts NULLs to the end, regardless of direction
of sort. Intellectually, I kind of like that: "We don't know what these are,
let's just tack them on the end". I implemented NaN sorting to emulate that
behavior. This also has the pleasant property that NULL (or NaN) are never
returned as > or < any other possible value, should be expected.

However, if an index is involved, the index gets built, and the NULL
values are stored at one end of the index. So, when a ORDER BY DESC is
requested, the index is just read backwards, sending the NULLs (and NaNs)
first. (They're still not returned from a query with a clause such as
WHERE f1 > 0.)

An example of the output is attached, from the regress float8 table (with
a NULL and non-finites added. Don't need the non-finites to to display
the problem, though, since it's NULLs as well) Note the blank row,
which is the NULL, moves from the bottom to the top in the last case,
using the index.

So, what way should we go here? Make ASC/DESC actual mirrors of each other
in the direct sort case, as well? Hack the index scan to know about nodes
that always go to the end? Document it as a quirk? (Not likely: selection of
plan should never affect output.)

To make the direct sort the same as the index read would work for NULL,
but for NaN would either require allowing NaN to be returned as >
Infinity, which doesn't happen now, or add another ordering operator
that is only used for the sort case (use of '>' and '<' seems to be
hardcoded all the way to the parser)

Thoughts?

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