DuplicatedKeyException: Duplicated key [zone1, device1] found on index 'zone_device[from_id,to_id]' already assigned to record

[ruslan-butyk-fntext]

ArcadeDB: 25.3.1

Preconditions:

1. Up and running ArcadeDB server with available empty database

Steps to reproduce:

1. Open ArcadeDB Studio and execute next commands
2. SQL: CREATE VERTEX TYPE zone;
3. SQL: CREATE PROPERTY zone.id STRING;
4. SQL: CREATE VERTEX TYPE device;
5. SQL: CREATE PROPERTY device.id STRING;
6. SQL: CREATE EDGE TYPE zone_device;
7. SQL: CREATE PROPERTY zone_device.from_id STRING;
8. SQL: CREATE PROPERTY zone_device.to_id STRING;
9. SQL: CREATE INDEX ON zone_device (from_id, to_id) UNIQUE;
10. SQL: CREATE VERTEX zone SET id='zone1';
11. SQL: CREATE VERTEX zone SET id='zone2';
12. SQL: CREATE VERTEX device SET id='device1';
13. SQL: CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';
14. SQL_SCRIPT (Important to run next commands under the same transaction:

DELETE FROM zone_device WHERE from_id='zone1' and to_id='device1';
CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone2') TO (SELECT FROM device WHERE id='device1') SET from_id='zone2', to_id='device1';
CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';

Actual behaviour:
Found duplicate key in index Duplicated key [zone1, device1] found on index 'zone_device[from_id,to_id]' already assigned to record #7:0

Expected behaviour:
Command successfully executed without error.

[tolgaulas]

Utterly not supposed to happen :) It should work outside the "transaction", right @ruslan-butyk-fntext, i mean on sequential executions.

[ruslan-butyk-fntext]

Hi @tolgaulas! Yes, it works outside of the "transaction". But in my case, I need it inside of the "transaction" to be able to rollback those changes.

[tolgaulas]

My two cents, the engine algorithm, ignores (forgot?) to update indices within the transaction scope.. I would consider this as bug since its very fundemental. ( I mean why would you delete and then recreate the same vertex in a transaction? It intuitively sounds illogical, but i bet there should be at least one justification use case for it :)

[lvca]

I agree this sounds like a bug to me. I remember having this kind of test in our CI, I'll build a test case with these exact steps to reproduce it.

[lvca]

I've just created a new test case and works. It splits the execution in 2 script executed under the same transaction:

  @Test
  public void testDeleteOverwriteCompositeKeyInTx() {
    database.transaction(() -> {
      database.command("sqlscript", """
          CREATE VERTEX TYPE zone;
          CREATE PROPERTY zone.id STRING;
          CREATE VERTEX TYPE device;
          CREATE PROPERTY device.id STRING;
          CREATE EDGE TYPE zone_device;
          CREATE PROPERTY zone_device.from_id STRING;
          CREATE PROPERTY zone_device.to_id STRING;
          CREATE INDEX ON zone_device (from_id, to_id) UNIQUE;
          CREATE VERTEX zone SET id='zone1';
          CREATE VERTEX zone SET id='zone2';
          CREATE VERTEX device SET id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';
          """);

      database.command("sqlscript", """
          DELETE FROM zone_device WHERE from_id='zone1' and to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone2') TO (SELECT FROM device WHERE id='device1') SET from_id='zone2', to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';          
          """);
    });
  }

[lvca]

@ruslan-butyk-fntext are you able to reproduce it with code? If you're using studio, how do you manage to use the same TX?

[ruslan-butyk-fntext]

@lvca I use the same TX only for this part

DELETE FROM zone_device WHERE from_id='zone1' and to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone2') TO (SELECT FROM device WHERE id='device1') SET from_id='zone2', to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';

Other part should be executed in different TX.
Please move this part

CREATE VERTEX TYPE zone;
          CREATE PROPERTY zone.id STRING;
          CREATE VERTEX TYPE device;
          CREATE PROPERTY device.id STRING;
          CREATE EDGE TYPE zone_device;
          CREATE PROPERTY zone_device.from_id STRING;
          CREATE PROPERTY zone_device.to_id STRING;
          CREATE INDEX ON zone_device (from_id, to_id) UNIQUE;
          CREATE VERTEX zone SET id='zone1';
          CREATE VERTEX zone SET id='zone2';
          CREATE VERTEX device SET id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';

out of the current TX.
I guess this should be enough:

@Test
  public void testDeleteOverwriteCompositeKeyInTx() {
    database.command("sqlscript", """
          CREATE VERTEX TYPE zone;
          CREATE PROPERTY zone.id STRING;
          CREATE VERTEX TYPE device;
          CREATE PROPERTY device.id STRING;
          CREATE EDGE TYPE zone_device;
          CREATE PROPERTY zone_device.from_id STRING;
          CREATE PROPERTY zone_device.to_id STRING;
          CREATE INDEX ON zone_device (from_id, to_id) UNIQUE;
          CREATE VERTEX zone SET id='zone1';
          CREATE VERTEX zone SET id='zone2';
          CREATE VERTEX device SET id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';
          """);

      database.command("sqlscript", """
          DELETE FROM zone_device WHERE from_id='zone1' and to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone2') TO (SELECT FROM device WHERE id='device1') SET from_id='zone2', to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';          
          """);
  }

Sorry, my "the same transaction" might be confusing :)

[lvca]

Got it, I was able to reproduce it with this:

@Test
  public void testDeleteOverwriteCompositeKeyInTx() {
    database.transaction(() -> {
      database.command("sqlscript", """
          CREATE VERTEX TYPE zone;
          CREATE PROPERTY zone.id STRING;
          CREATE VERTEX TYPE device;
          CREATE PROPERTY device.id STRING;
          CREATE EDGE TYPE zone_device;
          CREATE PROPERTY zone_device.from_id STRING;
          CREATE PROPERTY zone_device.to_id STRING;
          CREATE INDEX ON zone_device (from_id, to_id) UNIQUE;
          CREATE VERTEX zone SET id='zone1';
          CREATE VERTEX zone SET id='zone2';
          CREATE VERTEX device SET id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';
          """);
    });

    database.transaction(() -> {
      database.command("sqlscript", """
          DELETE FROM zone_device WHERE from_id='zone1' and to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone2') TO (SELECT FROM device WHERE id='device1') SET from_id='zone2', to_id='device1';
          CREATE EDGE zone_device FROM (SELECT FROM zone WHERE id='zone1') TO (SELECT FROM device WHERE id='device1') SET from_id='zone1', to_id='device1';          
          """);
    });
  }

[lvca]

Found the issue and testing the final solution.

[lvca]

Fixed. It will be in v25.3.2 that should be released asap.