BeforeRecordCreateListener does not update index

[vic0824]

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

JDK Version: openjdk 11.0.12 2021-07-20 LTS

OS: CentOS release 6.9 (Final)

Expected behavior

when a BeforeRecordCreateListener sets the value of a property, the corresponding index is updated

Actual behavior

When the listener is invoked, the value of the property is correctly set, but the corresponding value in the index is null.
As a consequence, the next time a record is updated, a DuplicatedKeyException is thrown

Steps to reproduce

Executing the attached testBeforeRecordCreateListenerWithIndex test case, produces the following output:

INSERT INTO Order SET status = ?, processor = ?, parameters: [PENDING, SIR1LRM-7.1]
result = {"status":"PENDING","processor":"SIR1LRM-7.1","id":1,"@cat":"d","@type":"Order","@rid":"#1:0"}
INSERT INTO Order SET status = ?, processor = ?, parameters: [PENDING, SIR1LRM-7.1]
result = {"status":"PENDING","processor":"SIR1LRM-7.1","id":2,"@cat":"d","@type":"Order","@rid":"#2:0"}
UPDATE Order SET status = ? RETURN AFTER WHERE id = ?, parameters: [ERROR, 1]
result = {"status":"ERROR","processor":"SIR1LRM-7.1","id":1,"@cat":"d","@type":"Order","@rid":"#1:0"}
UPDATE Order SET status = ? RETURN AFTER WHERE id = ?, parameters: [ERROR, 2]
result = {"status":"ERROR","processor":"SIR1LRM-7.1","id":2,"@cat":"d","@type":"Order","@rid":"#2:0"}
Duplicated key [ERROR, null] found on index 'Order[status,id]' already assigned to record #1:0
SELECT id, status from Order
result = {"id": 1, "status": "ERROR"}
result = {"id": 2, "status": "PENDING"}
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.server.ServerDatabase.commit(ServerDatabase.java:128)
	at eu.serco.csrs.persistence.ArcadeDBTest.testBeforeRecordCreateListenerWithIndex(ArcadeDBTest.java:159)
	at eu.serco.csrs.persistence.ArcadeDBTest.main(ArcadeDBTest.java:65)

[lvca]

@vic0824 I don't see the test case.

[lvca]

Does your callback return true with no exceptions?

[vic0824]

Sorry, forgot the attachment, I have now edited my post and added testBeforeRecordCreateListenerWithIndex.zip.
To answer your question: yes, I return true always, because what could possibly go wrong? If the select for some reason does not return any record, the variable will be set to 0, otherwise it will be incremented.
This is the listener:

final BeforeRecordCreateListener autoIncrementListener = record -> {
	int maxId = database.query("SQL", "select max(id) from Order").nextIfAvailable().getProperty("max(id)", 0);
	record.asDocument().modify().set("id", maxId + 1);
	return true;
};

maybe I'm not using the correct method to update the record? I could not find any other way and this seems to work, in that it does automatically increment the id at every insert, it just does not seem to update the index, or rather, the index is updated with null, which subsequently creates a duplicate key situation, because the id is what ensures that the index is unique.

[lvca]

I've created this test case from your listener and it passes.

@Test
public void testBeforeCreateEmulateIncrement() {
  final BeforeRecordCreateListener listener = record -> {
    int maxId = database.query("SQL", "select max(counter) from `IndexedVertex`").nextIfAvailable().getProperty("max(counter)", 0);
    record.asVertex().modify().set("counter", maxId + 1);
    return true;
  };

  database.getEvents().registerListener(listener);
  try {
    database.getSchema().createVertexType("IndexedVertex").createProperty("counter", Type.INTEGER).createIndex(Schema.INDEX_TYPE.LSM_TREE, true);

    database.transaction(() -> {
      final MutableVertex v1 = database.newVertex("IndexedVertex").set("id", "test");
      Assertions.assertFalse(v1.has("counter"));
      v1.save();
      Assertions.assertEquals(1, v1.get("counter"));
      Assertions.assertEquals(1, database.countType("IndexedVertex", true));

      // SHOULD OVERWRITE THIS
      database.newVertex("IndexedVertex").set("id", "test2").set("counter", 1).save();

      final MutableVertex v2 = database.newVertex("IndexedVertex").set("id", "test3");
      Assertions.assertFalse(v2.has("counter"));
      v2.save();
      Assertions.assertEquals(3, v2.get("counter"));
      Assertions.assertEquals(3, database.countType("IndexedVertex", true));

      Assertions.assertEquals("test2", database.query("SQL", "select from `IndexedVertex` where counter= 2").nextIfAvailable().getProperty("id"));
    });

  } finally {
    database.getEvents().unregisterListener(listener);
  }
}

How your case is different from this one?

[vic0824]

I have edited my post and uploaded the test case, but it's not there, so I upload it here in this post.
The differences between your test case and mine are:

• I have an index which includes the property that is set by the listener
• I register the listener on the type, you register it on the database

testBeforeRecordCreateListenerWithIndex.zip

[vic0824]

I have modified my test case to register the listener on the database, instead of the type, but the result is the same.
So the issue seems to be caused by the fact that the property set by the listener is included in an index.
If you remove that property from the index, it works.

[vic0824]

I have done some more investigations, and I have found that the problem is not related to the listener, but it is a general problem with the execution of an UPDATE statement when there is a composite unique index on a pair of STRING and INTEGER properties.
I have opened issue #812.

[lvca]

Thanks so much for providing the test case to reproduce it. I've followed up on #812. Closing this as not reproducible.