The Phantom WHERE Clause — JavaScript Bug Hunt

Briefing

The internal ORM of Ledgerline builds parameterised SQL. Since last week's refactor, filtered reports return rows that should have been excluded, and reusing a base query leaks conditions between reports.

The mock SQL engine and fixtures are locked — they faithfully implement AND/OR and $n placeholders. Fix queryBuilder.js.

Bug report

BUG-8804 · Priority: High · Reported by: finance (wrong report sent to a client!)

• .where("status", "paid").where("region", "EU") returns paid OR EU rows — the conditions must combine with AND.
• Building two different queries from one shared base makes the second query inherit the first one's filters.
• With two conditions both placeholders render as $1 — the engine then binds the wrong values.

Logs

[sql] SELECT * FROM invoices WHERE status = $1 OR region = $1
[sql] params = ["paid", "EU"]
[report] expected 1 row, got 5

The code as shipped

src/db/queryBuilder.js (editable)

// Tiny parameterised query builder for the invoices table.
function Query(conditions) {
  this.conditions = conditions;
}

Query.prototype.where = function (field, value) {
  this.conditions.push({ field: field, value: value });
  return new Query(this.conditions);
};

Query.prototype.build = function () {
  if (this.conditions.length === 0) {
    return { sql: "SELECT * FROM invoices", params: [] };
  }
  var parts = [];
  var params = [];
  for (var i = 0; i < this.conditions.length; i++) {
    parts.push(this.conditions[i].field + " = $1");
    params.push(this.conditions[i].value);
  }
  return {
    sql: "SELECT * FROM invoices WHERE " + parts.join(" OR "),
    params: params,
  };
};

exports.query = function () {
  return new Query([]);
};

Read-only context: src/db/engine.js, src/db/fixtures.js.