Re: NULLS FIRST problem with numeric fields

"Federico Albesano - Technical Design S.r.l." <federico.albesanoNOSPAM_at_technicaldesign.it> a écrit dans le message de news:cevgcg$sf5$2_at_grillo.cs.interbusiness.it...
> "Michel Cadot" <micadot{at}altern{dot}org> ha scritto nel messaggio
> news:41124dbc$0$1889$636a15ce_at_news.free.fr...
> >
> > Can you post your test case as it seems to work for me (8.1.7.4
> WinNT4SP6):
> >
>
> I better investigated the problem (it's Oracle 8.1.7.0.0 Win2kSP3):
>
> SQL> create table tab(id number(11),resp varchar2(50), constraint pk_tab
> unique (resp));
> SQL> insert into tab values(1,'Pippo');
> SQL> insert into tab values(3,'topolino');
> SQL> insert into tab values(7,'Zio Paperone');
>
> ORDER BY resp NULLS FIRST is OK!!
>
> SQL> ALTER TABLE tab drop CONSTRAINT PK_tab cascade;
> SQL> alter table tab add (constraint pk_tab primary key (resp));
>
> ORDER BY resp NULLS FIRST is also OK!!
>
> SQL> ALTER TABLE tab drop CONSTRAINT PK_tab cascade;
> SQL> alter table tab add (constraint pk_tab unique (id));
> SQL> select * from tab order by id;
>
> ID RESP
> ---------- --------------------------------------------------
> 1 Pippo
> 3 topolino
> 7 Zio Paperone
>
> SQL> select * from tab order by id nulls first;
>
> ID RESP
> ---------- --------------------------------------------------
> 1 Pippo
> 3 topolino
> 7 Zio Paperone
>
> So, with UNIQUE constraint on a numeric field, all is OK !! But.....
>
> SQL> ALTER TABLE tab drop CONSTRAINT PK_tab cascade;
> SQL> alter table tab add (constraint pk_tab primary key (id));
> SQL> select * from tab order by id;
>
> ID RESP
> ---------- --------------------------------------------------
> 1 Pippo
> 3 topolino
> 7 Zio Paperone
>
> SQL> select * from tab order by id nulls first;
>
> ID RESP
> ---------- --------------------------------------------------
> 7 Zio Paperone
> 3 topolino
> 1 Pippo
>
> ...ordering NULLS FIRST is wrong if the ordering field is numeric and there
> is a PRIMARY KEY constraint on it !!
> It seems descending order is right even with NULLS FIRST/LAST:
>
> SQL> select * from tab order by id desc nulls first;
>
> ID RESP
> ---------- --------------------------------------------------
> 7 Zio Paperone
> 3 topolino
> 1 Pippo
>
> SQL> select * from tab order by id desc nulls last;
>
> ID RESP
> ---------- --------------------------------------------------
> 7 Zio Paperone
> 3 topolino
> 1 Pippo
>
> It's very strange and I found no information on Oracle documentation, so I
> tinked it's a bug.
> Is there a patch?
> I don't know where to look for a bug list/resolved on various versions of
> Oracle products.
>
> Thanks a lot and regards
> Federico Albesano
>
>

I make the same test and get the same result in 8.1.7.4 but the correct order in 9.2.0.4. It seems there's a bug in 8i but i don't have time to further investigate. Maybe someone else knows this issue.

--
Regards
Michel Cadot