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

> -----Original Message-----
> From: owner-pgsql-sql(at)postgreSQL(dot)org [mailto:owner-pgsql-sql(at)postgreSQL(dot)
> org]On Behalf Of Tom Lane
>
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > Always test everything you say. The NULLs will come out at the end no
> > matter which way you order it. There is a TODO item for this, but I
> > suspect that the function manager clean up needs to be completed first,
>
> I believe this is not an fmgr issue. The sort comparison routine (in
> current sources, comparetup_heap() in backend/utils/sort/tuplesort.c)
> checks for nulls before it calls the supplied comparison operator,
> and it has a hard-wired decision that NULL sorts after non-NULL.
> DESC order doesn't affect this at all (since that just chooses a
> different operator).
>
> To my mind, DESC doesn't have anything to do with whether NULLs sort
> before or after non-NULLs; if we want a control for that, it should
> be something else. It would be pretty easy to drive this off a SET
> variable, if you didn't mind having it a global setting rather than
> per-query.
>
> The SQL spec seems to leave this up to the implementor:
>

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.

Should index scan and sequential scan be consistent at least ?

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp