From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hiroshi Inoue <Inoue@tpf.co.jp>
Cc: 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: Sun, 05 Dec 1999 22:08:27 -0500
Message-ID: <14623.944449707@sss.pgh.pa.us> (raw)
In-Reply-To: <001a01bf3f8e$77793000$2801007e@cadzone.tpf.co.jp>
"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
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: tgl@sss.pgh.pa.us, Inoue@tpf.co.jp, peter_e@gmx.net, genov@digsys.bg, lutzeb@aeccom.com
Subject: Re: [SQL] Howto to force NULL rows at the bottom ?
In-Reply-To: <14623.944449707@sss.pgh.pa.us>
References: <001a01bf3f8e$77793000$2801007e@cadzone.tpf.co.jp> <14623.944449707@sss.pgh.pa.us>
* 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