Thread: Howto to force NULL rows at the bottom ?
Hi, how can I force NULL rows at the bottom of the result list in an ordered SELECT? with ORDER BY I get NULL Peter Jane but I want Peter Jane NULL -- Dirk
No way to do this currently, but it is on our TODO list. > > Hi, > > how can I force NULL rows at the bottom of the result list in an > ordered SELECT? > > with ORDER BY I get > > NULL > Peter > Jane > > but I want > > Peter > Jane > NULL > > -- > Dirk > > ************ > > -- 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, Pennsylvania19026
Hi, If you bother about the NULLs positioned at the bottom of the selection, and not how non-NULL data is ordered, you can just use ORDER BY field DESC - this will position NULLs at the bottom, ordering non-NULL data from the highest values to the lowest. Regards, Stoyan Genov > Hi, > > how can I force NULL rows at the bottom of the result list in an > ordered SELECT? > > with ORDER BY I get > > NULL > Peter > Jane > > but I want > > Peter > Jane > NULL > > -- > Dirk > > ************ >
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, because there are a lot of subtle and not so subtle problems with NULLs all over the place. -Peter On 1999-12-05, Stoyan Genov mentioned: > Hi, > > If you bother about the NULLs positioned at the bottom of the selection, > and not how > non-NULL data is ordered, you can just use ORDER BY field DESC - this will > position > NULLs at the bottom, ordering non-NULL data from the highest values to the > lowest. > > Regards, > Stoyan Genov > > > Hi, > > > > how can I force NULL rows at the bottom of the result list in an > > ordered SELECT? > > > > with ORDER BY I get > > > > NULL > > Peter > > Jane > > > > but I want > > > > Peter > > Jane > > NULL > > > > -- > > Dirk > > > > ************ > > > > > > ************ > > -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> 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
thanall non-null values or be considered less than all non-null values.
regards, tom lane
> -----Original Message----- > From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL. > org]On Behalf Of Tom Lane > > Peter Eisentraut <peter_e@gmx.net> 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
"Hiroshi Inoue" <Inoue@tpf.co.jp> 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
> 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. 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. -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> 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
Ok, I think I will do the sorting in perl since the total number of rows can be sucked into mem in my application... Dirk
Sorry, sorry, sorry :-(((
I HAVE tested it, but with the WRONG test.
Please do not laugh too loud...
lorna:[11:03]:/usr/home/genov/
: createdb tmp
lorna:[11:03]:/usr/home/genov/
: psql tmp
Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute queryYou are
currentlyconnected to the database: tmp
tmp=> select version();
version
--------------------------------------------------------------------
PostgreSQL 6.5.2 on i386-unknown-freebsd3.1, compiled by gcc 2.7.2.1
(1 row)
tmp=> create table a (d datetime default now()+'@60 days', t text);
CREATE
tmp=> insert into a (t) values (NULL);
INSERT 458155 1
tmp=> \g
INSERT 458156 1
tmp=> \g
INSERT 458157 1
tmp=> insert into a (t) values ('a');
INSERT 458158 1
tmp=> insert into a(t) values ('b');
INSERT 458159 1
tmp=> insert into a(t) values ('c');
INSERT 458160 1
tmp=> select * from a;
d |t
----------------------------+-
Fri 04 Feb 11:04:45 2000 EET|
Fri 04 Feb 11:04:51 2000 EET|
Fri 04 Feb 11:04:52 2000 EET|
Fri 04 Feb 11:05:08 2000 EET|a
Fri 04 Feb 11:05:14 2000 EET|b
Fri 04 Feb 11:05:26 2000 EET|c
(6 rows)
tmp=> select * from a order by t;
d |t
----------------------------+-
Fri 04 Feb 11:05:08 2000 EET|a
Fri 04 Feb 11:05:14 2000 EET|b
Fri 04 Feb 11:05:26 2000 EET|c
Fri 04 Feb 11:04:45 2000 EET|
Fri 04 Feb 11:04:51 2000 EET|
Fri 04 Feb 11:04:52 2000 EET|
(6 rows)
tmp=> select * from a order by t desc;
d |t
----------------------------+-
Fri 04 Feb 11:05:26 2000 EET|c
Fri 04 Feb 11:05:14 2000 EET|b
Fri 04 Feb 11:05:08 2000 EET|a
Fri 04 Feb 11:04:45 2000 EET|
Fri 04 Feb 11:04:51 2000 EET|
Fri 04 Feb 11:04:52 2000 EET|
(6 rows)
tmp=>
I just DID NOT put the "order by" clause in the first select, so the tuples
with the NULL
values appeared first. But that isn't ordered selection :-(
I apologize. I am really sorry.
Regards,
Stoyan Genov
> 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, Pennsylvania19026