Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA23984 for ; Mon, 6 Dec 1999 08:40:34 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id IAA18651; Mon, 6 Dec 1999 08:37:04 -0500 (EST) From: Bruce Momjian Message-Id: <199912061337.IAA18651@candle.pha.pa.us> Subject: Re: [SQL] Howto to force NULL rows at the bottom ? In-Reply-To: <14944.944453719@sss.pgh.pa.us> from Tom Lane at "Dec 5, 1999 11:15:19 pm" To: Tom Lane Date: Mon, 6 Dec 1999 08:37:04 -0500 (EST) CC: Hiroshi Inoue , Peter Eisentraut , Stoyan Genov , Dirk Lutzebaeck , pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL65 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > 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. But if we have an operator, shouldn't we just look for < and > and use them if we have those symbols? Seems like it would be better than what we have, if not perfect. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026