I've noticed something strange and I'm not quite sure what to make of it. The result set below is basically the output of an SQL Select statement on a table where my application writes its log messages. I've edited out the sensitive material like site and task details and provided you with the field datatypes rather than the fieldnames, except for RDB$DB_KEY
which is built into Firebird tables. What I want you to focus on is the order of rows which I have listed despite using Order by RDB$DB_KEY in my SQL.
Timestamp Field Varchar Field RDB$DB_KEY
======================== ====================== =================
19.12.2013, 10:40:40.000 Site_BC DB_2 connected 00000083:00000100
19.12.2013, 10:40:40.000 Site_BC DB_1 connected 00000083:000000fc
19.12.2013, 10:40:40.000 DB_1 tasks completed 00000083:000000fd
19.12.2013, 10:40:40.000 Site_A DB_2 connected 00000083:000000fe
Now, I could have sworn that 0
comes before 1
in the ASCII table, so the order of the rows (supposedly sorted in ascending order by the values in the RDB$DB_KEY field) didn't seem right to me.
I did some basic research and apparently this RDB$DB_KEY field is an array of byte (although I'm not sure). I've tried casting it as a varchar and as a char but it seems the Firebird Cast
doesn't support conversion from this datatype.
Can anybody help me to get these rows sorted properly? I know I could add another column of integer datatype and then sort by that, but I thought I'd ask anyway just in case somebody knows how to sort by this mixed blessing called RDB$DB_KEY.
I'm using Firebird version 2.5.
The
RDB$DB_KEY
output in your select is just formatted for presentation on the client. It is not the actual key(!). The actualRDB$DB_KEY
is (for tables) an eight byte array (or a 64 bit number).Now as to the ordering, I am not 100% sure about the exact implementation, but either the RDB$DB_KEY is big-endian, or this presentation is big-endian). In big-endian
0x0100
comes before0x00fc
(in little endian it would be0x0001
and0xfc00
).But as I already indicated in the comments, ordering by
RDB$DB_KEY
almost never makes sense. TheRDB$DB_KEY
represents a 'snapshot' of the physical location of a record that is only really valid for the duration of a transaction (simplified). The order of theRDB$DB_KEY
of multiple records usually does not reflect the insert order (and if it would, that order might be different after a back up and restore), but only the relative position on storage (for the currently visible record version).