Order by for big (>34 digits) int128 values is broken when index on that field is used.

[AlexPeshkoff]

Due to use of DECFLOAT(34) representation of numeric really big int128 values have same key in index. That is not dangerous when working with bitmaps on index (not more then small slowdown) but can be easily seen when index is used in ORDER BY:

create table biTest(dat int128);
insert into biTest values(12345678901234567890123456789012345678);
insert into biTest values(12345678901234567890123456789012345679);
insert into biTest values(12345678901234567890123456789012345677);
commit;
create index it on biTest(dat);
commit;

-- wrong order
select dat from biTest order by dat;

                                      DAT 

=============================================
12345678901234567890123456789012345678
12345678901234567890123456789012345679
12345678901234567890123456789012345677

alter index it inactive;
-- correct order
select dat from biTest order by dat;

                                      DAT 

=============================================
12345678901234567890123456789012345677
12345678901234567890123456789012345678
12345678901234567890123456789012345679

[AlexPeshkoff]

No backporting supposed for this issue - it requires new ODS, at least minor (i.e. 13.1 in FB5).

[mrotteveel]

@AlexPeshkoff I assume you meant Firebird 4 instead of Firebird 5 there

[AlexPeshkoff]

On 7/1/22 18:34, Mark Rotteveel wrote: @AlexPeshkoff <https://github.com/AlexPeshkoff> I assume you meant Firebird 4 instead of Firebird 5 there

5 in both cases. It was decided not to touch minor ODS in point releaes

[mrotteveel]

@AlexPeshkoff Ah, I had totally missed that Firebird 5 uses ODS 13.1, instead of ODS 14.