Re: NULLS FIRST problem with numeric fields

"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 Received on Fri Aug 06 2004 - 03:47:36 CDT