FirebirdSQL
Computed index on RDB$RECORD_VERSION don't work as expected
When computed index is created on RDB$RECORD_VERSION pseudo-column:
ALTER TABLE COUNTRY ADD TRA_ID BigInt COMPUTED BY (RDB$RECORD_VERSION);
Then query like this that use it:
SELECT r.RDB$RECORD_VERSION,r.COUNTRY, r.CURRENCY
FROM COUNTRY r
where r.RDB$RECORD_VERSION >= 100
will return empty set.
Similar problem could be observed with RDB$RECORD_VERSION in BEFORE triggers. For example:
ALTER TABLE COUNTRY ADD
TRA_ID BigInt ;
CREATE TRIGGER COUNTRY_BIU FOR COUNTRY
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
NEW.TRA_ID = NEW.RDB$RECORD_VERSION ;
END^
This trigger will always store 0 into TRA_ID.
I don't think it should be possible to use RDB$RECORD_VERSION in that way.
Why not? Transaction number is a fixed property of a record version the same way as values of fields. In contrast to something external like current_timestamp or a select from other table it cannot be changed without update.
@mrotteveel It should be declared as illegal? That would be a grave mistake. RDB$RECORD_VERSION is incredibly useful tool for data synchronization. Simple, reliable and cheap. However, for very large datasets it needs index to maintain high performance, as with data volume it can degrade somewhere between linear and exponential curves.
Secondly, right now creating computed index or using RDB$RECORD_VERSION in trigger is legal, so it was intended to work. It doesn't work now because in both cases, the code uses partial record buffer with data only, not with header that contains transaction ID, hence the RDB$RECORD_VERSION value is always zero. It was clear omission when feature was introduced in Firebird 3, and wasn't discovered earlier due to insufficient testing.