Re: [SQL] Howto to force NULL rows at the bottom ?

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We are pretty cheezy about using > and < for ORDER BY.  Any chance to
> look up the actual comparison symbol from the cache and do something
> based on "<" or ">"?  Do we have a pg_operator oid or something else
> there?  If it is not one of those, we can just order them
> however we want to.

We're already ordering them "however we want to" ;-).

After further thought I think the goal of making explicit sort order
always match btree index results is unreachable, because the explicit
sort hasn't got enough information.  All it has is an operator ID, and
that's about all it can possibly have, at least in the "USING operator"
case.  But btree ordering doesn't depend on an operator ID, it depends
on an opclass.  The counterexample goes like this: I could easily make
two different opclasses, "int_forward" and "int_reverse", that both work
on int4 data but produce opposite btree sort orders.  They're even built
from the same operators, just lined up differently.  Now, how shall an
explicit sort decide which btree ordering to conform to?  Indeed, if
I make two indexes on the same table using the two opclasses, it's not
even predictable which ordering an index-driven sort will return.

This counterexample is a bit farfetched of course, but it shows that
there is no theoretically-pure answer.  We have to make some unprovable
assumptions about what to do.
        regards, tom lane