Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by hub.org (8.9.3/8.9.3) with ESMTP id VAA14141 for ; Sun, 5 Dec 1999 21:03:22 -0500 (EST) (envelope-from Inoue@tpf.co.jp) Received: from cadzone ([126.0.1.40] (may be forged)) by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP id LAA00950; Mon, 06 Dec 1999 11:01:21 +0900 From: "Hiroshi Inoue" To: "Tom Lane" , "Peter Eisentraut" Cc: "Stoyan Genov" , "Dirk Lutzebaeck" , 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> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 In-reply-to: <7823.944441492@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4 Importance: Normal > -----Original Message----- > From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL. > org]On Behalf Of Tom Lane > > Peter Eisentraut 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