Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER.
(1) By anonymous on 2021-07-30 17:33:10 [link] [source]
Here is a question asking whether it is possible. I was told no, but no reason was given why this would not be technically feasable. I'd like to imagine that this is a fairly straight forward thing to impliment, because I can not imagine how there could possibly be a technical limitation that would prevent the B-Tree from using the byte signature of a REAL, which is 8 Bytes just like INTEGER.
Hence: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER.
And if it is not feasible, I am extremely curious as to why it would not be and would love an explanation. Thanks everyone and keep up the good work.
(2) By anonymous on 2021-07-30 17:50:32 in reply to 1 [link] [source]
It will not be compatible, I think.
(3) By anonymous on 2021-07-30 18:36:14 in reply to 1 [link] [source]
What about sorting of values when you interpret them as INTEGER? Is it the same as ordering by REAL values?
If no, you still can find row in BTree by key, but can't search by '<', '>' and so on. It can be solved, but it will not be "just interpret real as integer".
(4) By Gunter Hick (gunter_hick) on 2021-07-30 19:41:30 in reply to 1 [link] [source]
The sort order of 64 bit patterns is different if they are interpreted as 2s complement integers (int64) than if they are interpreteed as fp64 values. E.g. 0.0 and -0.0 compare as equal in fp64, but not as int64. Then you have inf and -inf and a whole bunch of NaN that you cannot compare as fp64 at all, but have different values as int64. So a function for comparing fp64 by using int64 comparisons would have to check for the special cases first, and a function generating rowids would have to avoid anything beginning with 0xFFF.
(7) By anonymous on 2021-07-31 11:14:34 in reply to 4 [link] [source]
Interesting run down. Does the B-Tree depend though somehow on the sort order? Or are you speaking generally?
(8) By anonymous on 2021-07-31 11:48:14 in reply to 7 [source]
Yes, the BTree depends on sort order.
The BTree is a replacement for sorted arrays, so it depends on sort order as any sorted array.
When you add new element, the place for it is selected using comparisions with existing elements. And these comparisons depends on sort rule. So different sort rules produces different BTrees on the same input.
If you just want get element by key, you can use any BTree above.
But BTree allows get elements by range (start < x < end) - here sort order is important.
(10) By Gunter Hick (gunter_hick) on 2021-08-02 06:15:26 in reply to 7 [link] [source]
Being able to compare values is central to BTree. Or any other method of retrieving records by key.
Leaf nodes contain records in sorted order and are linked together in sort order.
Internal nodes contain key values in sort order, separeted by node references to the next level node that pertains to that key range.
An ordering function needs to be tri-valued (return exactly one of "less", "equal" and "greater") and transitive ("a <op> b" and "b <op> c" implies "a <op> c").
Trying to compare fp64 NaN bit patterns will not conform, and pretending that all NaN bit patterns are equal is going to disrupt operations considerably, because inserting a records with rowid of "NaN++" is going to overwrite the record with rowid NaN.
(5) By Ryan Smith (cuz) on 2021-07-30 21:17:59 in reply to 1 [link] [source]
There's already been some correct replies, I'll maybe add that adding 1 in an Int64 number could potentially scale by orders of magnitude in its FP counterpart, so no, it is never possible to be replaced just as-is - to say nothing of the difficulty of making it AUTOINCREMENT.
However, if it is FP primary keys you want, there is already a mechanism for that:
CREATE TABLE fptable(
ID REAL NOT NULL PRIMARY KEY,
...
) WITHOUT ROWID;
That's a 100% correct replacement of a int row-id with a float. No shenanigans, no interpreting, no casting, no problems.
(6) By Keith Medcalf (kmedcalf) on 2021-07-30 22:00:02 in reply to 5 [link] [source]
That is almost the same. The exception being that the real primary key must be provided everytime and is not actually the rowid, so you cannot leave it null.
(9.1) By Rico Mariani (rmariani) on 2021-08-01 21:48:58 edited from 9.0 in reply to 6 [link] [source]
Yeah this is just weird. I mean, if you don't need it to be the ROWID then of course just make a REAL pk. But if you do need it to be a rowid, then you must want the autoinc feature or else what's the point. And if that's the case you certainly don't want to +1 the raw bits of a real because that's not going to give you anything like +1.
If we had a feature where you could +whatever for the rowid you could pick the magic value that increments the mantissa by one and then assuming you could start at some base value you could arrange for some range of reals but now this is even weirder. You'll have to do weird memory aliasing to get the real back when you pull it out.
It's far easier to just say "the integer is fixed point" then you do real_value = rowid/scale + offset when you pull it out and it all works fine. This is much less weird than the memory stuff you'd have to do to store the real as an int.
(11) By Simon Slavin (slavin) on 2021-08-02 14:54:56 in reply to 1 [link] [source]
Can someone list the advantages of this ? Why would anyone want to do it ?