rust-store

SQL pushdown limits by dialect

The problem

You are choosing a backend — or mixing several — and you need to know, before committing, what each one can execute natively and what the engine will refuse, degrade or hand back to your host. “It translates MongoDB commands to SQL” is not enough: the interesting part is the boundary.

Why rust-store

The engine fixes MongoDB’s dialect as canonical. A query is planned into MongoDB command JSON, and a pure function translates that command into parameterized SQL for MySQL, PostgreSQL and SQLite. The public entry point is dialectTranslate / dialect_translate (Rust: rust_store_core::dialect::translate), and it returns { backend, stmts, warnings, unsupported }. It never emits SQL that is quietly missing a clause: a segment it cannot translate safely leaves stmts and is surfaced as an explicit error or an unsupported entry.

Walkthrough

const { Registry } = require('rust-store-node');

const reg = new Registry();
reg.register({
  name: 'Course',
  collection: 'courses',
  fields: { status: { type: 'string' }, price: { type: 'float' } },
  relations: {},
});

const plan = reg.planQuery('Course($condition:@c0,$sort:@s0){ status, price }', {
  c0: { price: { $gte: 10 } },
  s0: { price: -1 },
}, null);
const cmd = plan.commands[0]; // MongoDB command JSON (the canonical dialect)

for (const backend of ['mysql', 'postgres', 'sqlite']) {
  try {
    const out = reg.dialectTranslate(backend, cmd);
    // out.stmts       : statements that WERE pushed down
    // out.warnings    : notes (e.g. a $regex flag the backend cannot express)
    // out.unsupported : [{ code, field, reason }] — segments deliberately NOT in stmts
    for (const u of out.unsupported) {
      console.warn('not pushed down:', u.code, u.field); // host does a fallback sort
    }
  } catch (err) {
    // Hard failure: the command (or a stage) cannot be translated at all.
    // Reject it — never run partial SQL as if it were the whole query.
    console.error('translation refused:', String(err));
  }
}

Cross-source work is reported the same way, as structured events rather than a silent wrong answer:

const fed = reg.planFederated(
  'User{ _id, name, orders($sort:@s0){ code } }',
  { s0: { code: 1 } },
  null,
  { sources: { default: 'mongo', analytics: 'postgres' } },
);

for (const ev of fed.degraded) {
  // ev = { code, layer, message, hint }, layer = "federation"
  // code: 'crossSourceChildPaging' | 'crossSourceSort'
  // `degraded` does not block: the host sorts / paginates in memory after mergeFederated.
  console.warn(ev.code, ev.hint);
}

What each backend can do:

Feature MongoDB MySQL PostgreSQL SQLite
Plan target native aggregation pipeline parameterized SQL (?) parameterized SQL ($n) parameterized SQL (?)
Identifier / table naming database in the location triple back-quoted, qualified `ns`.`t` double-quoted "ns"."t" double-quoted "ns"."t"
Offset without a limit native LIMIT 18446744073709551615 OFFSET ? OFFSET $n LIMIT -1 OFFSET ?
Read-after-write native none — host runs UPDATE + find RETURNING RETURNING
Non-integer literal binding native dynamic typing CAST($n AS double precision) dynamic typing
AVG IEEE-754 double AVG(CAST(x AS DOUBLE)) AVG(CAST(x AS DOUBLE PRECISION)) AVG(CAST(x AS REAL))
$regex with $options: "i" native flags REGEXP_LIKE(col, ?, 'i') (MySQL 8.0+) col ~* $n flags unsupported: warning, semantics degrade to case-sensitive
Root $group / $having $group / $match GROUP BY / HAVING GROUP BY / HAVING GROUP BY / HAVING
$group.by object dot-path executes error error error
Relation-rolling agg computed column $lookup + $addFields derived table LEFT JOIN (… GROUP BY fk) same same
Relation aggregate predicate $lookup + sentinel surrogate keys EXISTS / NOT EXISTS same same
Per-parent top-N (relation $skip / $limit) native pipeline ROW_NUMBER() OVER (PARTITION BY fk ORDER BY …) same same
Relation crossing namespaces of one source stripped → in-memory federation pushed down as a qualified JOIN same same
Root $sort key that cannot be mapped executes natively not pushed down (unsupported) same same

Operators and clauses that push down as SQL: $eq / $ne (null-aware — $eq: null becomes IS NULL plus the __present existence check), $gt / $gte / $lt / $lte, $in / $nin, $exists (via the __present sentinel), $not, and $regex. Operators with no translation — $expr, $elemMatch, $all, $where and any other — are an explicit error, never a dropped condition. The U1–U4 shapes (array-field filter, object deep-equality, object dot-path filter, object dot-path sort) raise on every backend.

When pushdown is impossible there are exactly two outcomes:

The feedback event that reports degraded work is degraded — planFederated returns it as plan.degraded, a list of { code, layer, message, hint }. The codes the federation planner emits are crossSourceChildPaging (a relation $sort / $skip / $limit that cannot be pushed per parent across sources) and crossSourceSort (a root $sort on a cross-source relation field), both with layer: "federation".

MongoDB-specific $lookup and aggregation differences:

Pitfalls

See also