From: Hiroshi Inoue <Inoue@tpf.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Stoyan Genov <genov@digsys.bg>
Cc: Dirk Lutzebaeck <lutzeb@aeccom.com>
Cc: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] Howto to force NULL rows at the bottom ?
Date: Mon, 6 Dec 1999 11:06:11 +0900
Message-ID: <001a01bf3f8e$77793000$2801007e@cadzone.tpf.co.jp> (raw)
In-Reply-To: <7823.944441492@sss.pgh.pa.us>
> -----Original Message-----
> From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL.
> org]On Behalf Of Tom Lane
>
> Peter Eisentraut <peter_e@gmx.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@tpf.co.jp
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: Inoue@tpf.co.jp, tgl@sss.pgh.pa.us, peter_e@gmx.net, genov@digsys.bg, lutzeb@aeccom.com
Subject: Re: [SQL] Howto to force NULL rows at the bottom ?
In-Reply-To: <001a01bf3f8e$77793000$2801007e@cadzone.tpf.co.jp>
References: <7823.944441492@sss.pgh.pa.us> <001a01bf3f8e$77793000$2801007e@cadzone.tpf.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox