Issue after update with compose index before compaction

[vic0824]

ArcadeDB Version:

v23.7.1 (build 3213507/1692051585396/main)

OS and JDK Version:

Windows 10 10.0 - OpenJDK 64-Bit Server VM 11.0.20.1 (Corretto-11.0.20.9.1)

Expected behavior

After Inserting documents in async mode, updating one document, updating again the same document, the index should be updated accordingly.

Actual behavior

if the number of documents inserted asynchronously is > parallel level, the index is not updated.
If the number of docs inserted is < parallel level, the index is updated correctly.

Steps to reproduce

Execute the attached testWhereAfterAsyncInsert test case.
Sorry, it's a bit verbose, but if I try to simplify it, it does not reproduce the problem.
If you find what is the actual root cause of the problem, we can design a simpler test case.
In the test case I execute the first update asynchronously and the second update synchronously. but I have verified that also the other two combinations (both async or both sync) produce a failure.

testWhereAfterAsyncInsert.zip

[vic0824]

I have also found that if I change the index on (status, id) to not unique, the problem does not occur.

[vic0824]

But now other things start to break, e.g. updates are not reflected in indexes anymore, so this is not a viable solution.

[lvca]

Testing your code I get this:

org.opentest4j.AssertionFailedError: 
Expected :1
Actual   :2
	at com.arcadedb.server.AsyncUpdateTest.testWhereAfterAsyncInsert(AsyncUpdateTest.java:176)

Is it the same output as you?

[vic0824]

Yes, the query should return the record with Id = 1, but it returns the record with Id = 2

[vic0824]

The resubmitOrder method call sets status = PENDING where id = 1, so the query SELECT id, processor, trigger, vstart, vstop, status FROM Order WHERE status = ? ORDER BY id ASC LIMIT ? called with parameters 'PENDING' and 1 should return the record with id = 1, but it doesn't, because the composite index has not been updated.

[vic0824]

By the way, in the meantime I have completed the migration of my project from Hsqldb to ArcadeDB and I've put it into production (with the bug described in this issue).
I will be presenting it at a conference later this month, and in my powerpoint presentation I have put in some good words about ArcadeDB. I cannot publish the presentation yet (it will be done some time after the conference), but if you want I can share a copy privately with you.

[vic0824]

Additional info on the test case: if I replace WHERE status = 'PENDING' in the last select query with WHERE status != 'ERROR', then it works. I hope this can help you troubleshooting the problem.

[vic0824]

As a workaround, I have replaced the update with a combination of delete + insert, but it's not very efficient and the code is unnecessarily verbose.

[lvca]

@vic0824 That's great! is the presentation going to be public at a certain point?

About the issue, it's in my todo list for today. I keep you posted.

[vic0824]

Yes, the presentation is going to be published after the conference.

[lvca]

@vic0824 I'm back on this issue. I've the test case in the ArcadeDB test suite now (local branch) and it fails as you mentioned. I tried to put a

database.async().waitCompletion();

Before the query, but it's the same. So it's not an async timing issue, but rather the index is not updated in async mode? Is this what you were trying to tell me?

[lvca]

I removed the async part transforming the method in synchronous, and I've got the same error. Maybe it's not in the async API, there is something with the way the index iterator works. Checking on it.

[lvca]

Ok, I found the issue. During the update to PENDING,1, the entry in the index is not correctly appended at the end of the key entries. It was a corner case with composite + update of the same key + before compaction. Checking for regressions, otherwise I should be able to push the change.

[lvca]

Fixed