An index range scan inside a transaction ignores the tx overlay REMOVE entries, returning rows deleted or re-keyed in that same transaction #6927

Summary

The point-lookup path subtracts pending in-transaction index removals from the disk result; the ordered/range path does not. LSMTreeIndexCursor collects only the overlay's ADD entries and skips REMOVEs without recording them anywhere, so a range scan inside a transaction still returns entries deleted or re-keyed in that same transaction. The clean symptom is a row that does not match the WHERE clause at all.

Where (HEAD c1785ced6)

LSMTreeIndex.get() does it correctly - engine/src/main/java/com/arcadedb/index/lsm/LSMTreeIndex.java:575-612:

if (value.operation == ...REMOVE) {
  if (isUnique()) return EMPTY_CURSOR;
  hasRemoves = true; ... removedRids.add(value.rid);
}
...
if (removedRids == null || !removedRids.contains(next.getIdentity())) txChanges.add(...)

HashIndex.get() does the same (HashIndex.java:182).

The range path drops the information. engine/src/main/java/com/arcadedb/index/lsm/LSMTreeIndexCursor.java:696, in getClosestEntryInTx(), walks the same getIndexChanges().getIndexKeys() map but only collects ADDs:

// REMOVED: A NON-UNIQUE KEY CAN STILL HOLD OTHER LIVE VALUES, SO SKIP JUST THIS ONE
continue;

and never records the removed RID. fetchNext() (:540-586) then builds the emitted set purely from page bytes plus overlay ADDs:

final Set<RID> mergedRIDs = new HashSet<>();
... for (final var entry : ridState.entrySet()) if (Boolean.FALSE.equals(entry.getValue())) mergedRIDs.add(entry.getKey());
... if (includeTx) while (txCursor.hasNext()) mergedRIDs.add((RID) txCursor.next());

ridState comes only from currentCursor.getValue(), i.e. committed page tombstones, and index.isDeletedEntry(rid) (LSMTreeIndexAbstract.java:258) is just rid.getBucketId() < 0, also disk-only. A key whose only pending change is a REMOVE is emitted from disk unchanged.

Repro

Verified against main at c1785ced6. Person.age with an LSM_TREE index, two records age=20 and age=25:

db.begin();
db.command("sql", "UPDATE Person SET age = 99 WHERE age = 20");
db.query("sql", "SELECT name, age FROM Person WHERE age < 30");

expected 1 row, actual 2 rows including a row that does not satisfy the predicate:

ACTUAL rows=2 : {"name":"a","age":99} {"name":"b","age":25}
(point lookup for comparison) lookupByKey age=20 -> correctly empty

The asymmetry is visible in that last line: Index.get() handles it, the range scan does not.

DocumentIndexer turns an indexed-property update into index.remove(oldKeyValues, rid); index.put(newKeyValues, new RID[]{rid}); (DocumentIndexer.java:636-637), which inside a transaction becomes REMOVE(oldKey,rid) + ADD(newKey,rid) in the overlay (LSMTreeIndex.java:659/:675). The scan reads the still-present disk entry for key 20, resolves the RID (the record exists, age is now 99) and returns it. Nothing re-checks the predicate: SelectExecutionPlanner.needsFilterStep() (:3655) chains a FilterStep only for desc.getRemainingCondition(), and age < 30 is consumed entirely by the index.

The pure-DELETE variant returns the right rows, but only by accident, and noisily - also verified:

2026-08-31 WARNI [GetValueFromIndexEntryStep$1] Record #1:0 not found. Skip it from the result set
expected 1 row; ACTUAL rows=1 : {"name":"b","age":25}

i.e. GetValueFromIndexEntryStep (:152) catches RecordNotFoundException and logs at WARNING once per dead row. Any direct RangeIndex.range(...)/iterator(...) caller has no such net and receives the dead RIDs.

Existing tests cover only the point-lookup path (LSMTreeIndexTest.deleteCreateSameKeySameTx, changePrimaryKeySameTx, both via database.lookupByKey). I found no test running a range or ordered scan with a pending REMOVE in the overlay.

Suggested fix

In getClosestEntryInTx(), collect the visited key's REMOVE entries too - RID-specific removals into a per-key Set<RID>, plus a flag for a key-wide REMOVE (value.rid == null) - store them alongside txCursorKeys, and in fetchNext() subtract them from mergedRIDs after the disk RIDs are added, treating a key-wide REMOVE on a unique index as suppressing the whole key. That mirrors LSMTreeIndex.get()'s removedRids filter. Note the key must still be visited when it has no live ADD, so the "walk to the next candidate when the first is fully tombstoned" loop must not skip a REMOVE-only key that also exists on disk.

Scope / confidence

High - reproduced. Verified that the removal is not already applied to the pages the cursor reads (index changes are buffered in TransactionIndexContext and applied at commit; LSMTreeIndex.put/remove only call tx.addIndexOperation(...) while STATUS.BEGUN), and that the constructor's removedKeys set is seeded only from page-level full-key tombstones, never from getIndexChanges().