Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA45905 for ; Sun, 5 Dec 1999 23:18:53 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id XAA14946; Sun, 5 Dec 1999 23:15:19 -0500 (EST) To: Bruce Momjian cc: Hiroshi Inoue , Peter Eisentraut , Stoyan Genov , Dirk Lutzebaeck , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Howto to force NULL rows at the bottom ? In-reply-to: Your message of Sun, 5 Dec 1999 22:34:20 -0500 (EST) <199912060334.WAA25609@candle.pha.pa.us> Date: Sun, 05 Dec 1999 23:15:19 -0500 Message-ID: <14944.944453719@sss.pgh.pa.us> From: Tom Lane Bruce Momjian 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