Edge indexes become invalid in certain scenario #2

[ruslan-butyk-fntext]

ArcadeDB version: 25.12.1
Steps to reproduce: execute next chunks of SQL commands in separate transactions:

• transaction №1

CREATE VERTEX TYPE duct;
CREATE VERTEX TYPE trs;
CREATE PROPERTY duct.id STRING;
CREATE INDEX ON duct (id) UNIQUE;
CREATE PROPERTY trs.id STRING;
CREATE INDEX ON trs (id) UNIQUE;
CREATE EDGE TYPE trs_duct;
CREATE PROPERTY trs_duct.from_id STRING;
CREATE INDEX ON trs_duct (from_id) NOTUNIQUE;
CREATE PROPERTY trs_duct.to_id STRING;
CREATE INDEX ON trs_duct (to_id) NOTUNIQUE;
CREATE PROPERTY trs_duct.swap STRING;
CREATE PROPERTY trs_duct.order_number INTEGER;
CREATE INDEX ON trs_duct (from_id,to_id,swap,order_number) UNIQUE;

• transaction №2

INSERT INTO duct (id) VALUES ('duct_1');
INSERT INTO trs (id) VALUES ('trs_1');
CREATE EDGE trs_duct from #4:0 to #1:0 SET from_id='trs_1', to_id='duct_1', swap='N', order_number=1;

• transaction №3

DELETE FROM trs_duct WHERE (from_id='trs_1') AND (to_id='duct_1') AND (swap='N') AND (order_number=1);
CREATE EDGE trs_duct from #4:0 to #1:0 SET from_id='trs_1', to_id='duct_1', swap='N', order_number=1;

After that execute the previous transaction again

• transaction №4

DELETE FROM trs_duct WHERE (from_id='trs_1') AND (to_id='duct_1') AND (swap='N') AND (order_number=1);
CREATE EDGE trs_duct from #4:0 to #1:0 SET from_id='trs_1', to_id='duct_1', swap='N', order_number=1;

you will get DuplicatedKeyException.

Expected behavior:
After executing transaction №4 there should be no DuplicatedKeyException error.

[lvca]

Bug Analysis: Issue #3097 - DELETE Query with Composite Index

Executive Summary

Issue #3097 reports DuplicatedKeyException when deleting and recreating edges with the same property values. While commit a81163a (included in v25.12.1) fixed the RID reuse issue, a separate bug remains that causes DELETE queries to return incorrect counts when using composite unique indexes.

Root Cause

The bug is in FetchFromIndexStep.init() at lines 342-352:

if (secondValue.equals(thirdValue) && fromKeyIncluded && toKeyIncluded
    && index.getPropertyNames().size() == convertedFrom.length)
  cursor = index.get(convertedFrom);  // Path 1: Exact match
else if (index.supportsOrderedIterations()) {
  cursor = index.range(...);  // Path 2: Range query
} else if (additionalRangeCondition == null && allEqualities((AndBlock) condition)) {
  cursor = index.iterator(isOrderAsc(), convertedFrom, true);  // Path 3: PREFIX MATCH BUG!
}

The Problem: When Path 1 fails (even though all conditions are equalities), the code falls through to Path 3 which calls index.iterator(). This method performs a PREFIX match instead of an exact match, returning records that match only the first N-1 fields.

Test Evidence

Test 1: Direct Index Access (CompositeIndexExactMatchTest)

index.get(new Object[]{"a", "b", "c", 1})   → finds 1 record ✅
index.get(new Object[]{"a", "b", "c", 999}) → finds 0 records ✅

Conclusion: The index itself works correctly.

Test 2: SELECT vs DELETE Behavior (DeleteWithCompositeIndexTest)

Query: WHERE field1='a' AND field2='b' AND field3='c' AND field4=999
Actual data: field1='a', field2='b', field3='c', field4=1

SELECT result: 0 records found ✅
DELETE result: 1 record counted, 0 records actually deleted ❌

Conclusion: SELECT uses Path 1 (exact match), DELETE uses Path 3 (prefix match).

Test 3: Edge-Specific Test (EdgeIndexStaleEntryTest)

Query: DELETE WHERE from_id='trs_1' AND to_id='duct_1' AND swap='N' AND order_number=2
Actual data: order_number=1

Result: Returns count=1 but record still exists ❌

Conclusion: Same bug affects edge indexes.

Why This Causes Issue #3097

1. User deletes edge with DELETE WHERE ... AND order_number=1
2. FetchFromIndexStep uses prefix match, finds the record
3. Record is counted but NOT actually deleted (filtered out downstream)
4. User creates new edge with same values
5. Index still contains old entry → DuplicatedKeyException

Impact

• Severity: High - Data integrity issue
• Scope: All DELETE queries using composite indexes with multiple equality conditions
• Symptoms:
  • DELETE returns wrong count
  • Records not deleted when they should be
  • Stale index entries remain
  • Subsequent inserts fail with DuplicatedKeyException

Affected Code

File: engine/src/main/java/com/arcadedb/query/sql/executor/FetchFromIndexStep.java

Lines: 342-352

Proposed Fix

Ensure that when all conditions are equalities and we have the full key, we MUST use index.get() for exact match. The fallback to index.iterator() should only be used for actual range queries or prefix searches.

Options:

1. Fix the condition at line 342 to ensure it always succeeds for full equality matches
2. Remove Path 3 entirely for composite indexes (force use of Path 1 or throw error)
3. Add a check before Path 3 to verify if we're doing a prefix search or exact match

Test Files Created

1. engine/src/test/java/com/arcadedb/graph/EdgeIndexStaleEntryTest.java

   • Tests DELETE with wrong conditions on edge indexes

2. engine/src/test/java/com/arcadedb/graph/DeleteWithCompositeIndexTest.java

   • Tests DELETE with wrong conditions on document indexes
   • Compares SELECT vs DELETE behavior

3. engine/src/test/java/com/arcadedb/index/CompositeIndexExactMatchTest.java

   • Tests direct index.get() behavior
   • Confirms index works correctly

Resolution

The Fix

Changed: Line 342 in FetchFromIndexStep.java

Before:

if (secondValue.equals(thirdValue) && fromKeyIncluded && toKeyIncluded
    && index.getPropertyNames().size() == convertedFrom.length)

After:

if (java.util.Arrays.equals(convertedFrom, convertedTo) && fromKeyIncluded && toKeyIncluded
    && convertedFrom != null && index.getPropertyNames().size() == convertedFrom.length)

Why This Works:

• secondValue and thirdValue are PCollection execution results (Lists/Collections)
• Using .equals() on different Collection instances fails even with same content
• Converting to arrays first and using Arrays.equals() properly compares content
• This ensures the code uses index.get() for exact match instead of falling back to index.iterator() for prefix match

Test Improvements

Fixed test cases that were incorrectly reading DELETE results:

• DELETE queries return a ResultSet with a single Result containing a "count" property
• Tests were counting the number of Result objects (always 1) instead of reading the "count" property value
• Fixed all test cases to properly read: result.getProperty("count")

Impact

This fix resolves issue #3097 by ensuring:

1. DELETE queries with composite indexes use exact match (not prefix match)
2. No stale index entries are left when DELETE with correct conditions is executed
3. Subsequent inserts with same values don't fail with DuplicatedKeyException

[ruslan-butyk-fntext]

This issue is still reproducible on 26.1.1
@lvca please have a look.

[ruslan-butyk-fntext]

Just checked on 26.2.1 and for some reason it's still reproducible in Web Studio @robfrank please have a look.

[robfrank]

I can't reproduce on 26.2.1, all is fine on my side on web studio