Bug: Running Data Manipulation Clauses yields different results depending on how often database is opened and closed

[andreasdamm-shure]

Ladybug version

0.14.3

What operating system are you using?

Windows 11 and Amazon Linux 2023

What happened?

Running the given queries should delete all nodes and rels in the database. That behavior is observed when only opening the database once after creation, running the queries and then closing it.

When opening the database for each node to be modified and then closing it, many nodes are left undeleted but all the rels are gone.

Are there known steps to reproduce?

Unzip undead-nodes-bug.zip and run npm install.

Running "npm run good" creates undead-nodes.kz which when observed with the CLI contains no nodes.

Running "npm run bad" creates undead-nodes.kz which when observed with the CLI contains many nodes but no rels.

[adsharma]

Root Cause

The bug is in CSR

NodeGroup::checkpointVersionInfo (called via getNumDeletionsForNodeInPersistentData):
src/storage/table/csr_node_group.cpp:973

return persistentChunkGroup->getNumDeletions(&DUMMY_CHECKPOINT_TRANSACTION, startRow, length);

The DUMMY_CHECKPOINT_TRANSACTION has:

• startTS = START_TRANSACTION_ID - 1
• transactionID = DUMMY_TRANSACTION_ID = 0

src/storage/table/version_info.cpp:206-208
const auto isDeletedWithinSameTransaction = deletion == transactionID;
const auto isDeletedByPrevCommittedTransaction = deletion <= startTS;

return isDeletedWithinSameTransaction || isDeletedByPrevCommittedTransaction;

When a deletion is committed, its version is set to the commitTS (which is >= START_TRANSACTION_ID). But the DUMMY_CHECKPOINT_TRANSACTION's startTS = START_TRANSACTION_ID - 1, so:
deletion <= startTS is always FALSE for committed deletions!

This means during checkpoint, deletions in committed persistent data are not visible, so:

1. The CSR length column isn't updated to reflect deletions
2. When the database is reopened, checkIfNodeHasRels still sees the old (incorrect) CSR length
3. Nodes that should have no relationships are reported as having them

Why the Good Case Works

In the "good" case, all operations happen in a single database session without closing. The local storage and version info track deletions properly during the session. The "bad" case reopens the database, forcing recovery from checkpointed data where deletions were lost.

Fix

The checkpoint logic needs to use a transaction that can see all committed deletions, or the deletion tracking needs to be updated differently during checkpoint.

[adsharma]

The key code segment in the repro is:

switch (argv[2]) {
  case "good":
    {
      const db = new Database(dbPath);
      const c = new Connection(db);
      for (let i = 1; i <= 1200; i++) {
        await complete(c, [i]);
      }
      await c.close();
      await db.close();
    }
    break;
  case "bad":
    {
      for (let i = 1; i <= 1200; i++) {
        const db = new Database(dbPath);
        const c = new Connection(db);
        await complete(c, [i]);
        await c.close();
        await db.close();
      }
    }
    break;

where complete() performs a bunch of deletions.