[BUG] Filtering with where returns wrong results

[mindreframer]

For simplicity I will use bun.js.

helpers file: https://github.com/mindreframer/arcadex/blob/main/jstester/tests/helpers.js

import { describe, test, expect, beforeAll, afterAll } from 'bun:test';
import {
  createDatabase,
  cleanupDatabase,
  uniqueDbName,
  command,
  query
} from './helpers.js';

describe('ULTRA MINIMAL ArcadeDB Bug - Plain SQL Only', () => {
  const dbName = uniqueDbName('ultra_minimal');

  beforeAll(async () => {
    await cleanupDatabase(dbName);
    await createDatabase(dbName);
  });

  afterAll(async () => {
    await cleanupDatabase(dbName);
  });

  test('absolute minimal reproduction', async () => {
    console.log('\n=== SETUP ===');

    // 1. Create two types with LINK and COMPOSITE INDEX 
    await command(dbName, `CREATE DOCUMENT TYPE Parent`);
    await command(dbName, `CREATE DOCUMENT TYPE Child`);
    await command(dbName, `CREATE PROPERTY Child.uid STRING`);
    await command(dbName, `CREATE PROPERTY Child.status STRING (default 'synced')`);
    await command(dbName, `CREATE PROPERTY Child.version INTEGER (default 1)`);
    await command(dbName, `CREATE PROPERTY Child.parent LINK Parent`);

    // THE KEY: Composite index on (status, version) 
    await command(dbName, `CREATE INDEX ON Child (status, version) NOTUNIQUE`);

    // 2. Create parent
    const p = await command(dbName, `INSERT INTO Parent SET name = 'p1' RETURN @this`);
    const pRid = p.result[0]['@rid'];

    // 3. Insert 3 children WITHOUT explicit status (use default)
    await command(dbName, `INSERT INTO Child SET uid = 'c1', parent = ${pRid}`);
    await command(dbName, `INSERT INTO Child SET uid = 'c2', parent = ${pRid}`);
    await command(dbName, `INSERT INTO Child SET uid = 'c3', parent = ${pRid}`);

    console.log('Created 3 children with default status=synced');

    // 4. Mark c1 and c2 as pending
    await command(dbName, `UPDATE Child SET status = 'pending' WHERE uid = 'c1'`);
    await command(dbName, `UPDATE Child SET status = 'pending' WHERE uid = 'c2'`);

    console.log('\n=== BEFORE BUG ===');

    // 5. Verify WHERE works - should find 2 pending
    const before = await query(dbName, `SELECT uid, status FROM Child WHERE status = 'pending'`);
    console.log('Pending (WHERE):', before.result.length, '→', before.result.map(r => r.uid));
    expect(before.result.length).toBe(2);

    console.log('\n=== TRIGGER BUG ===');

    // 6. Update c1 with parameterized multi-field UPDATE (including version field in composite index)
    await command(dbName, `UPDATE Child SET version = :version, status = :status WHERE uid = :uid`, {
      uid: 'c1',
      version: 2,
      status: 'synced'
    });

    console.log('Updated c1 to synced (multi-field parameterized UPDATE)');

    console.log('\n=== AFTER BUG ===');

    // 7. Check without WHERE - should show c2 pending, c1+c3 synced
    const all = await query(dbName, `SELECT uid, status FROM Child ORDER BY uid`);
    console.log('All (no WHERE):', all.result);

    // 8. BUG: WHERE status='pending' should find c2 but finds 0
    const pending = await query(dbName, `SELECT uid, status FROM Child WHERE status = 'pending'`);
    console.log('Pending (WHERE):', pending.result.length, '→', pending.result.map(r => r.uid));

    // 9. BUG: WHERE status='synced' should find c1+c3 but finds only c1
    const synced = await query(dbName, `SELECT uid, status FROM Child WHERE status = 'synced'`);
    console.log('Synced (WHERE):', synced.result.length, '→', synced.result.map(r => r.uid));

    console.log('\n=== RESULT ===');
    console.log(`Pending: expected 1 (c2), got ${pending.result.length}`);
    console.log(`Synced: expected 2 (c1,c3), got ${synced.result.length}`);

    expect(pending.result.length).toBe(1);
    expect(synced.result.length).toBe(2);
  });
});

=== SETUP ===
CREATE DOCUMENT TYPE Parent;
CREATE DOCUMENT TYPE Child;
CREATE PROPERTY Child.uid STRING;
CREATE PROPERTY Child.status STRING (default 'synced');
CREATE PROPERTY Child.version INTEGER (default 1);
CREATE PROPERTY Child.parent LINK Parent;
CREATE INDEX ON Child (status, version) NOTUNIQUE;
INSERT INTO Parent SET name = 'p1' RETURN @this;
INSERT INTO Child SET uid = 'c1', parent = #1:0;
INSERT INTO Child SET uid = 'c2', parent = #1:0;
INSERT INTO Child SET uid = 'c3', parent = #1:0;
Created 3 children with default status=synced
UPDATE Child SET status = 'pending' WHERE uid = 'c1';
UPDATE Child SET status = 'pending' WHERE uid = 'c2';

=== BEFORE BUG ===
SELECT uid, status FROM Child WHERE status = 'pending';
Pending (WHERE): 2 → [ "c1", "c2" ]

=== TRIGGER BUG ===
UPDATE Child SET version = :version, status = :status WHERE uid = :uid;
Updated c1 to synced (multi-field parameterized UPDATE)

=== AFTER BUG ===
SELECT uid, status FROM Child ORDER BY uid;
All (no WHERE): [
  {
    uid: "c1",
    status: "synced",
  }, {
    uid: "c2",
    status: "pending",
  }, {
    uid: "c3",
    status: "synced",
  }
]
SELECT uid, status FROM Child WHERE status = 'pending';
Pending (WHERE): 0 → []
SELECT uid, status FROM Child WHERE status = 'synced';
Synced (WHERE): 1 → [ "c1" ]

=== RESULT ===
Pending: expected 1 (c2), got 0
Synced: expected 2 (c1,c3), got 1

I suspect this has something to do with the parameterized multi-field UPDATE .

Good thing: it's consistent and quite reproducible.

Cheers!

[gramian]

Could you try if the compound index is the reason, I mean, using either no idex or two separate indexes?

[mindreframer]

@gramian Yes, indeed. the status index is the issue.
When I remove it, the result is correct.

but even with single-prop index:

await command(dbName, CREATE INDEX ON Child (status) NOTUNIQUE);

i get the same bug.

hmm... this means there is an issue with the maintenance of the index

[robfrank]

Should be fixed by the PR

Thanks a lot @mindreframer !