OR searches run one SQL query per term; batch them into one statement #104

Counterpart of VH-Lab/DID-matlab#233. Keep the two in step.

What happens

SQLiteDB._search_doc_ids evaluates q1 | q2 | ... | qn by running one SQL query per operand and taking the union of the results in Python. After #103, a long OR chain is flattened with or_operands instead of recursed through, but it is still one query per operand.

ORed base.id lookups are the exception: _search_base_ids already runs one query per 1,000 ids.

In Python this is cheap. Measured on 50,000 documents, 1,000 base.name terms took 0.066 s, about 0.07 ms per term, because sqlite3 runs in-process. In MATLAB each query is far more expensive, so the fix matters there first. Python should follow so that both libraries:

• produce the same SQL;
• have the same performance profile.

Proposal

Same design as DID-matlab#233: run the OR's SQL-expressible operands as per-operand SELECTs joined with UNION, in batches under SQLite's 500-member compound limit. Alternatively, batch same-field exact_string operands into IN (...).

Python-specific points:

• Negation is applied in Python as a complement, not in SQL. So only non-negated SQL operands can go into a batch. Negated and brute-force operands keep their current path.
• Add a test that the batched path returns exactly what the per-operand path returns, for every operation.
• Record any difference from the MATLAB implementation in the sqlitedb bridge decision_log.

Generated by Claude Code