Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We have a TODO item:
> * Make NULL's come out at the beginning or end depending on the
> ORDER BY direction
The tricky part of this is to know which direction you are talking
about, when all you've been given is an operator that might have an
arbitrary name (ie, not necessarily '<' or '>'). So it's not all that
clear which end to put the NULLs at.
Actually, I've been messing around with that code in hopes of speeding
up sorting a little bit. Up to now the sort comparison routines depend
on invoking the datatype's ordering operator '<', which they may have to
do twice; whereas if they invoked the datatype's btree 3-way comparator
function there'd only be one function call and one underlying comparison
operation. So I have code pending commit that tries to look up the
associated comparator function and use that instead, if there is one.
How's that relevant, you ask? Well, to make this work for both '<' and
'>' (ie, ASC or DESC sort), the sort comparator has to distinguish which
way it's sorting and negate the 3-way comparison result or not. It
knows which case applies from the pg_amop entry that it found the
operator in (ie, BTLessOp or BTGreaterOp). So for all btree-compatible
sort operators, it would now be a pretty simple matter to make the NULLs
come out at the same end that a btree index scan would make them come
out at. The semantics are defined by the system catalogs and we don't
have to depend on anything as klugy as looking at the operator name.
This still leaves us up in the air for sort operators that aren't linked
to btree comparison routines. Would it be OK to punt for those, and
just sort the NULLs at the end no matter what sort operator you mention?
There's no issue of getting different results for an indexscan vs
explicit sort plan in this situation, since there can't be any btree
index available...
regards, tom lane
