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

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> IIRC,NULLs are greater than NON_NULLs in btree handling.
> If ORDER BY .. ASC uses an index scan,NULLs will come out
> at the bottom and if ORDER BY .. DESC uses an index scan,
> NULLs will come out at the top.

Oooh, you are right.

> Should index scan and sequential scan be consistent at least ?

Indeed the SQL spec seems to require that ;-)

I am not sure how we can resolve this.  btree cannot easily work
differently than it does --- we could make either choice for where nulls
appear in the index, but once we've done that we have no real choice
about what ORDER BY on the index will do, in either direction.  So
it seems that we really do need to make nulls sort differently in
ASC and DESC sorts.  But the explicit-sort routine has no idea whether
ASC or DESC is involved ... indeed can't, because we may have a
"USING operator" clause in there and no ASC or DESC anywhere.  Shall
explicit-sort try to guess whether the operator it's given represents
the fore or aft direction of a btree index?  The operator might not be
one that is btree-indexable at all, so I don't see how that can work.

Looks messy.  Anyone have an idea?
        regards, tom lane