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 WAA30592 for ; Sun, 5 Dec 1999 22:11:21 -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 WAA14625; Sun, 5 Dec 1999 22:08:27 -0500 (EST) To: "Hiroshi Inoue" cc: "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 Mon, 6 Dec 1999 11:06:11 +0900 <001a01bf3f8e$77793000$2801007e@cadzone.tpf.co.jp> Date: Sun, 05 Dec 1999 22:08:27 -0500 Message-ID: <14623.944449707@sss.pgh.pa.us> From: Tom Lane "Hiroshi Inoue" writes: > 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. Oooh, you are right. > Should index scan and sequential scan be consistent at least ? Indeed the SQL spec seems to require that ;-) I am not sure how we can resolve this. btree cannot easily work differently than it does --- we could make either choice for where nulls appear in the index, but once we've done that we have no real choice about what ORDER BY on the index will do, in either direction. So it seems that we really do need to make nulls sort differently in ASC and DESC sorts. But the explicit-sort routine has no idea whether ASC or DESC is involved ... indeed can't, because we may have a "USING operator" clause in there and no ASC or DESC anywhere. Shall explicit-sort try to guess whether the operator it's given represents the fore or aft direction of a btree index? The operator might not be one that is btree-indexable at all, so I don't see how that can work. Looks messy. Anyone have an idea? regards, tom lane