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

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> On Mon, Aug 21, 2000 at 04:37:21PM -0400, Tom Lane wrote:
>> That's exactly what you shouldn't even think about.  The entire index
>> and sorting system is predicated on the assumption that '<' and related
>> operators agree with the order induced by a btree index.  You do not get
>> to make the operators behave differently in the free-standing case than
>> when they are used with an index.

> Oh really? Then why do btree's have their own comparator functions,
> seperate from heap sorts, and datum sorts, and explicit use of '<' ?

Strictly and only to save a few function-call cycles.  Some paths in the
btree code need a three-way comparison (is A<B, or A=B, or A>B?) and
about half the time you'd need two calls to type-specific comparator
functions to make that determination if you only had the user-level
operators available.  This does *not* mean that you have license to make
the 3-way comparator's behavior differ from the operators, because the
operators are used too.  Note also that it is a three-way comparison
function, not four-way: there is no provision for answering "none of the
above" (except when a NULL is involved, and that only works because it's
special-cased without calling type-specific code at all).

The reason the sort code doesn't use the comparator routine is strictly
historical, AFAICT.  It really should, for speed reasons; but there may
not be a 3-way comparator associated with a given '<' operator, and
we've got a longstanding convention that a user-selected sort order is
specified by naming a particular '<'-like operator.  It may also be
worth pointing out that the sort code still assumes trichotomy: it
tests A<B, and if that is false it tries B<A, and if that's also false
then it assumes A=B.  There's still no room for an "unordered" response.

> The current code infrastructure allows for the possibility that these
> may need to diverge, requiring the coders to keep them in
> sync. Annoying, that, but useful for edge cases.

It is annoying.  Many of the datatypes where comparison is nontrivial
actually use an underlying 3-way comparison routine that the boolean
comparators call, so as to avoid code-divergence problems.

> Changing this would only require writing another set of operators for
> the parser to drop in, that are used only for sorting,

No, because *the user-level operators must match the index*.  How many
times do I have to repeat that?  The transformation that allows, say,SELECT * FROM tab WHERE foo > 33 AND foo < 42
to be implemented by an indexscan (of an index on foo) is fundamentally
dependent on the assumption that the operators '>' and '<' induce the
same ordering of data values as is stored in the index.  Otherwise you
can't scan a subrange of the index and know that you've hit all the
matching rows.  The planner actually takes considerable care to verify
that the operators appearing in WHERE *do* match the index ordering ---
that's what pg_opclass and pg_amop are all about.  If you invent an
internal set of operators that provide a different index ordering,
you will find that the planner ignores your index.
        regards, tom lane