From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: 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 23:15:19 -0500
Message-ID: <14944.944453719@sss.pgh.pa.us> (raw)
In-Reply-To: <199912060334.WAA25609@candle.pha.pa.us>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We are pretty cheezy about using > and < for ORDER BY. Any chance to
> look up the actual comparison symbol from the cache and do something
> based on "<" or ">"? Do we have a pg_operator oid or something else
> there? If it is not one of those, we can just order them
> however we want to.
We're already ordering them "however we want to" ;-).
After further thought I think the goal of making explicit sort order
always match btree index results is unreachable, because the explicit
sort hasn't got enough information. All it has is an operator ID, and
that's about all it can possibly have, at least in the "USING operator"
case. But btree ordering doesn't depend on an operator ID, it depends
on an opclass. The counterexample goes like this: I could easily make
two different opclasses, "int_forward" and "int_reverse", that both work
on int4 data but produce opposite btree sort orders. They're even built
from the same operators, just lined up differently. Now, how shall an
explicit sort decide which btree ordering to conform to? Indeed, if
I make two indexes on the same table using the two opclasses, it's not
even predictable which ordering an index-driven sort will return.
This counterexample is a bit farfetched of course, but it shows that
there is no theoretically-pure answer. We have to make some unprovable
assumptions about what to do.
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, pgman@candle.pha.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: <14944.944453719@sss.pgh.pa.us>
References: <199912060334.WAA25609@candle.pha.pa.us> <14944.944453719@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