UPDATE statement corrupts Composite unique index on (STRING, INTEGER) properties

[vic0824]

ArcadeDB Version: v23.1.2-SNAPSHOT (build 75a9319/1674650185130/main)

JDK Version: openjdk version "11.0.12" 2021-07-20 LTS

OS: Windows 10 Enterprise 22H2

Expected behavior

a composite unique index on (STRING, INTEGER) properties is correctly updated when an UPDATE statement is executed to modify the value of one of the indexed properties.

Actual behavior

If the value of one of the indexed properties is updated (preserving the uniqueness), a DuplicatedKeyException is thrown, showing that the indexed value of the other property was null, instead of the correct value. The value of the other property guaranteed the uniqueness, so by replacing the correct value with null, the engine has created the conditions for a duplicate key.

Steps to reproduce

1. Create a database
2. Create a document type
3. Create 3 properties associated to the Document type: id (INTEGER), processor (STRING), status (STRING)
4. Create a composite unique index on (status, id)
5. Insert two documents into the type: {"id":"1","status":"PENDING","processor":"SIR1LRM-7.1"} and {"id":"2","status":"PENDING","processor":"SIR1LRM-7.1"} (this should add two entries into the index: [PENDING, 1] and [PENDING, 2])
6. update the document with id = 1, setting the status value to "ERROR" (this should update the index, which should now contain [ERROR, 1] and [PENDING, 2])
7. update the document with id = 2, setting the status value to "ERROR" (this should update the index, which should now contain [ERROR, 1] and [ERROR, 2])
8. verify that the following exception is thrown: com.arcadedb.exception.DuplicatedKeyException: Duplicated key [ERROR, null] found on index 'Order[status,id]' already assigned to record #1:0 and that the second update has been rolled back
9. repeat the test, reversing the order of the properties in the index, i.e. creating an index on (id, status), and verify that the second update is performed correctly and no exception is thrown

The attached testCompositeIndex method demonstrates steps 1 - 8 and produces the following result:

INSERT INTO Order SET id = ?, status = ?, processor = ?; parameters: 1, SIR1LRM-7.1, PENDING
result = {"id":"1","status":"PENDING","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#1:0"}
INSERT INTO Order SET id = ?, status = ?, processor = ?; parameters: 2, SIR1LRM-7.1, PENDING
result = {"id":"2","status":"PENDING","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#2:0"}
UPDATE Order SET status = ? RETURN AFTER WHERE id = ?, parameters: [ERROR, 1]
result = {"id":"1","status":"ERROR","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#1:0"}
UPDATE Order SET status = ? RETURN AFTER WHERE id = ?, parameters: [ERROR, 2]
result = {"id":"2","status":"ERROR","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#2:0"}
Exception in thread "main" com.arcadedb.exception.DuplicatedKeyException: Duplicated key [ERROR, null] found on index 'Order[status,id]' already assigned to record #1:0
	at com.arcadedb.database.TransactionIndexContext.checkUniqueIndexKeys(TransactionIndexContext.java:338)
	at com.arcadedb.database.TransactionIndexContext.checkUniqueIndexKeys(TransactionIndexContext.java:373)
	at com.arcadedb.database.TransactionIndexContext.commit(TransactionIndexContext.java:162)
	at com.arcadedb.database.TransactionContext.commit1stPhase(TransactionContext.java:479)
	at com.arcadedb.database.TransactionContext.commit(TransactionContext.java:114)
	at com.arcadedb.database.EmbeddedDatabase.lambda$commit$2(EmbeddedDatabase.java:366)
	at com.arcadedb.database.EmbeddedDatabase.executeInReadLock(EmbeddedDatabase.java:1326)
	at com.arcadedb.database.EmbeddedDatabase.commit(EmbeddedDatabase.java:361)
	at com.arcadedb.database.EmbeddedDatabase.transaction(EmbeddedDatabase.java:1000)
	at com.arcadedb.database.EmbeddedDatabase.transaction(EmbeddedDatabase.java:965)
	at com.arcadedb.server.ServerDatabase.transaction(ServerDatabase.java:301)
	at eu.serco.csrs.persistence.ArcadeDBTest.testCompositeIndex(ArcadeDBTest.java:153)
	at eu.serco.csrs.persistence.ArcadeDBTest.main(ArcadeDBTest.java:67)

testCompositeIndex.zip

[lvca]

Thanks again @vic0824 for the detailed use case, I was able to reproduce the issue right away.

The issue is on this line:

arcadedb/engine/src/main/java/com/arcadedb/database/DocumentIndexer.java

Line 97 in 495d5f6

break;

When the key is matched, it shouldn't break the loop, otherwise a partial keys is filled.

Testing the solution if breaks some regression test.

[vic0824]

There is still a problem with the update statement and composite indexes.
Using v23.1.2-SNAPSHOT (build 3ec7c70/1674980798778/main), I have verified that a select which contains an equals in the WHERE clause does not return the correct result when the property in the WHERE clause is part of the index.
Steps to reproduce it:

1. create a database
2. create a document type
3. Create 3 properties associated to the Document type: id (INTEGER), processor (STRING), status (STRING)
4. Create a composite unique index on (status, id)
5. Insert two documents into the type: {"id":"1","status":"PENDING","processor":"SIR1LRM-7.1"} and {"id":"2","status":"PENDING","processor":"SIR1LRM-7.1"} (this should add two entries into the index: [PENDING, 1] and [PENDING, 2])
6. update the document with id = 1, setting the status value to "ERROR" (this should update the index, which should now contain [ERROR, 1] and [PENDING, 2])
7. execute SELECT id, processor, status FROM Order WHERE status = 'PENDING'; this query should return the record with id = 2
8. verify that the query returns the record with id = 1 and status = 'ERROR'
9. drop the index
10. repeat the select query and verify that it returns the correct result, i.e. the record with id = 2 and status = 'PENDING'

The attached testWhereEqualsAfterUpdate test case produces the following result:

INSERT INTO Order SET id = ?, status = ?, processor = ?; parameters: 1, SIR1LRM-7.1, PENDING
result = {"id":"1","status":"PENDING","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#1:0"}
INSERT INTO Order SET id = ?, status = ?, processor = ?; parameters: 2, SIR1LRM-7.1, PENDING
result = {"id":"2","status":"PENDING","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#2:0"}
UPDATE Order SET status = ? RETURN AFTER WHERE id = ?, parameters: [ERROR, 1]
result = {"id":"1","status":"ERROR","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#1:0"}
SELECT id, processor, status FROM Order WHERE status = ?, parameters: [PENDING]
result = {"id": "1", "processor": "SIR1LRM-7.1", "status": "ERROR"}
index dropped
SELECT id, processor, status FROM Order WHERE status = ?, parameters: [PENDING]
result = {"id": "2", "processor": "SIR1LRM-7.1", "status": "PENDING"}

testWhereEqualsAfterUpdate.zip

[lvca]

Reproduced.

[lvca]

Added new test case taken from your code. We'd need more tests with composite index, partial match, etc.

[vic0824]

One more test case for you: ORDER BY [indexed_property] DESC does not work after an UPDATE when there is a composite index; instead, ORDER BY [...] ASC works.
The attached test case (executed on v23.1.2-SNAPSHOT (build 7b0d322/1675083959582/main)) produces the following result:

INSERT INTO Order SET id = ?, status = ?, processor = ?; parameters: 1, SIR1LRM-7.1, PENDING
result = {"id":"1","status":"PENDING","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#1:0"}
INSERT INTO Order SET id = ?, status = ?, processor = ?; parameters: 2, SIR1LRM-7.1, PENDING
result = {"id":"2","status":"PENDING","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#2:0"}
UPDATE Order SET status = 'ERROR' RETURN AFTER
result = {"id":"1","status":"ERROR","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#1:0"}
result = {"id":"2","status":"ERROR","processor":"SIR1LRM-7.1","@cat":"d","@type":"Order","@rid":"#2:0"}
SELECT id, processor, status FROM Order ORDER BY status DESC
SELECT id, processor, status FROM Order ORDER BY status ASC
result = {"id": "1", "processor": "SIR1LRM-7.1", "status": "ERROR"}
result = {"id": "2", "processor": "SIR1LRM-7.1", "status": "ERROR"}

testOrderByDescAfterUpdate.zip