Query Row History

[jeroiraz]

What would you like to be added or enhanced

immutability provides a new perspective about changes, actually immutability makes changes explicit as no in-place mutations are made but appended. The final state may be interpreted as the latest appended change or in general, as a function of all updates made to a same entity.

Note: immudb is not just immutable but cryptographically verifiable.

At the SQL layer, immudb provides a special statement for updating rows based on its primary key i.e. UPSERT

Currently, it's only possible to query the latest row state using standard SQL grammar thus providing a way to query historical row values may be required in order to fetch all the updates made to a given row.

The following illustrative examples may describe a possible approach to solve this problem:

SELECT * FROM (N? (FORWARD|BACKWARD)? UPDATES (OFFSET M)? AT mytable)

SELECT * FROM mytable WHERE name = @name
-- would be equivalent to
SELECT * FROM (1 UPDATES AT mytable) WHERE name = @name
-- or
SELECT * FROM (1 BACKWARD UPDATES AT mytable) WHERE name = @name
-- or
SELECT * FROM (1 BACKWARD UPDATES OFFSET 0 AT mytable) WHERE name = @name

-- return latest row value
1 BACKWARD UPDATES OFFSET 0 AT mytable

-- return first row value
1 FORWARD UPDATES OFFSET 0 AT mytable

-- return second row value
1 FORWARD UPDATES OFFSET 1 AT mytable

-- return first two row values
2 FORWARD UPDATES OFFSET 0 AT mytable
-- or
2 FORWARD UPDATES AT mytable

-- return all row values from newst to older
BACKWARD UPDATES OFFSET 0 AT mytable

-- return all row values from older to newst
FORWARD UPDATES OFFSET 0 AT mytable

-- return N row values from newst to older
N BACKWARD UPDATES OFFSET 0 AT mytable

-- return N row values from older to newst
N FORWARD UPDATES OFFSET 0 AT mytable

[salvalopez]

This is a a very good point and will be very usefull for me :). Thank you for open the feature request!

[federico-razzoli]

Do immudb row versions have start and end timestamp? If so, I'd recommend using the standard SQL(1) syntax for system-period tables(2) instead. MariaDB page is simple, with several examples, but major DBMSs implement more or less the same syntax:
https://mariadb.com/kb/en/system-versioned-tables/#

This would allow several advantages:

• If immudb at some point decides to implement DELETE, this syntax will allow to see deleted rows.
• We would know when each version started/ceased to exist.
• We would be able to join two tables for the same time period, no matter how many changes happened since then.
• We would be able to join a table with itself at different time periods.
• We would be able to get statistics over time: for example the number of current rows minus the number of rows that existed 1 hour ago.

All tables could be system-versioned. In the case of immudb, a user shouldn't have to use any special syntax when a table is created.

(1) Actually I don't know if it is standard SQL or just a de facto standard feature.
(2) Please note that I'm proposing system-period tables, not application-period tables.

[iambudi]

Hi @jeroiraz ,

SELECT * FROM (N? (FORWARD|BACKWARD)? UPDATES (OFFSET M)? AT mytable)

That query approach looks good. What if we use alternative more aligned with existing sql

SELECT * FROM products WHERE id = 1 LIMIT 1 BACKWARD|FORWARD OFFSET 0;

Other than that, I'm wondering how this approach deal with table joins?

[jeroiraz]

Hi @jeroiraz ,

SELECT * FROM (N? (FORWARD|BACKWARD)? UPDATES (OFFSET M)? AT mytable)

That query approach looks good. What if we use alternative more aligned with existing sql

SELECT * FROM products WHERE id = 1 LIMIT 1 BACKWARD|FORWARD OFFSET 0;

Other than that, I'm wondering how this approach deal with table joins?

thanks @iambudi for the feedback.

In the grammar BACKWARD|FORWARD clauses are close to the data source, as it's specifying how to scan over it. And it will be simpler to implement historical queries over physical tables.

Then joins will work as usual but taking into account that the rows fetched from the datasource may contain the same primary key multiple times, once per row update in the specified historical range...

[iambudi]

@jeroiraz thank you, it makes sense. Hope the implementation will soon be started :)

[iambudi]

Hi, i'm curious if this feature has already been started and have ETA?
Thank you.