strange sort behavior

[fraenkg]

Their is a strange sort behavior for large integer values in result table of sql window.

create a table like this:
create table tst01 (
id integer,
val integer
);
insert into tst01 (id, val)
select 1, 100000000100000000100000010 from dual union
select 2, 100000000100000000100000020 from dual union
select 3, 100000000100000000100000030 from dual union
select 4, 100000000100000000100000040 from dual union
select 5, 100000000100000000100000050 from dual union
select 6, 100000000100000000100000060 from dual union
select 7, 100000000100000000100000070 from dual union
select 8, 100000000100000000100000080 from dual union
select 9, 100000000100000000100000090 from dual union
select 10, 100000000100000000100000100 from dual
;

.. and select ordered
select * from tst01 order by val;

And now order in result table ...

...reverse order in result table

As you can see, ID is no longer sorted ascending, although val corresponds to this value.

Tested with v12.0.5 32 bit on windows 10

 

[Marco Kalter]

This is a client-side scientific number precision issue. To use large integers with full Oracle Server precision, you can enable the "Number fields to_char" SQL Window preference. Now all numbers are fetched as character strings with full precision. In this case client-side sorting will be performed as character strings as well, so you would still need to use a where clause to sort in the correct order.

 

[Marcel Hoefs]

Hi Marco,

This is merely a workaround as it disables a lot of features related to numeric columns. Not just sorting but also e.g. the numeric aggregation features of the data grid. In the following posts I have described and tested a number of limitations in PL/SQL Developer caused by the limited support for large numbers:

Bug(?) 5: inconsistent large number behavior

Column totals sum, min, max and avg grayed out

I hope these limitations will be fixed soon!