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 TAA01761 for ; Sun, 5 Dec 1999 19:53:30 -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 TAA07825; Sun, 5 Dec 1999 19:51:32 -0500 (EST) To: Peter Eisentraut cc: 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 23:56:28 +0100 (CET) Date: Sun, 05 Dec 1999 19:51:32 -0500 Message-ID: <7823.944441492@sss.pgh.pa.us> From: 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: Whether a sort key value that is null is considered greater or less than a non-null value is implementation-defined, but all sort key values that are null shall either be considered greater than all non-null values or be considered less than all non-null values. regards, tom lane