Skip to main content

quarb_sql/
export.rs

1//! The reverse direction: Quarb → SQL.
2//!
3//! Walks the query's *reflection arbor* — the locked vocabulary is
4//! the stable surface for query-rewriting tooling — and emits an
5//! equivalent `SELECT` statement, refusing what the SQL query core
6//! cannot express. Fragments and pure macros expand before
7//! reflection, so they translate for free.
8//!
9//! The translatable subset mirrors the importer: `/table/*`
10//! branches with predicates (→ `WHERE`), witness joins (`<=>` with
11//! `$*1` equality → `JOIN ... ON`, remaining conditions → `WHERE`,
12//! `$*k` select fields qualified), `rec(...)` select lists,
13//! whole-table and grouped aggregates (`GROUP BY`, a filter after
14//! the reduction → `HAVING`), `sort_by`/`reverse`/`top` →
15//! `ORDER BY`/`LIMIT`, `@| [..n]` → `LIMIT`, and single-column
16//! `unique` → `DISTINCT`.
17//!
18//! Deliberately refused: `-->` resolution chains — the foreign-key
19//! targets live in the *schema*, not the query text, so a chain
20//! cannot be compiled to a join without a database at hand; spell
21//! the join explicitly with `<=>` to export it. Also refused:
22//! registers beyond the grouped-aggregate pattern, windows,
23//! captures, and regex matching (`LIKE` dialects disagree).
24//!
25//! Notes carry the standing divergences: truthiness (`[::c]` →
26//! `IS NOT NULL`, but Quarb also treats `0` and `''` as falsy),
27//! `*=` → `LIKE` case-folding dialects, and join cardinality
28//! (Quarb's existential binding never multiplies rows; SQL `JOIN`
29//! does when several left rows match).
30
31use crate::{SqlError, Translation};
32use quarb::reflect::QueryArbor;
33use quarb::{AstAdapter, NodeId, Value};
34
35/// Translate a Quarb query to a SQL `SELECT` statement.
36pub fn export(quarb: &str) -> Result<Translation, SqlError> {
37    refuse_marker(quarb)?;
38    let arbor =
39        QueryArbor::parse(quarb).map_err(|e| SqlError::Syntax(format!("parsing Quarb: {e}")))?;
40    refuse_groups(&arbor)?;
41    let mut ex = Exporter {
42        arbor,
43        notes: Vec::new(),
44        strict: false,
45        dialect: None,
46        from_table: String::new(),
47        join_on_cols: Vec::new(),
48        join_table: None,
49        join_every: false,
50        column_types: ColumnTypes::default(),
51        from_sql: String::new(),
52        join_sql: None,
53        in_on: false,
54        aggregate: false,
55        keyed_hops: false,
56        from_marks: Vec::new(),
57        join_marks: Vec::new(),
58    };
59    let query = ex.query()?;
60    Ok(Translation {
61        query,
62        notes: ex.notes,
63    })
64}
65
66/// The exporter rewrites `__LEFT__` (the join's left table) and
67/// `__AGG__` (the HAVING aggregate) as internal placeholders.
68/// Query text containing either marker would be rewritten inside
69/// its own string literals — and could spoof the emitted SQL — so
70/// such queries stay on the scan path.
71fn refuse_marker(quarb: &str) -> Result<(), SqlError> {
72    for marker in ["__LEFT__", "__AGG__"] {
73        if quarb.contains(marker) {
74            return Err(SqlError::Unsupported(format!(
75                "query text contains the reserved marker \"{marker}\""
76            )));
77        }
78    }
79    Ok(())
80}
81
82/// A pushdown plan: SQL whose execution is provably identical to
83/// the Quarb query's — plus the table whose primary key must order
84/// the rows (`None` for a single aggregate row, where order is
85/// moot). The driver appends the `ORDER BY`, since the key lives in
86/// its catalog.
87pub struct Pushdown {
88    pub sql: String,
89    pub order_table: Option<String>,
90    /// Present when the plan contains a witness JOIN: the joined
91    /// table and the columns its ON equalities bind on it
92    /// (collected structurally from the arbor, so query text
93    /// cannot spoof them).
94    /// The plan is only sound if those columns form a unique key
95    /// of the joined table — each FROM row must find at most one
96    /// witness, else SQL multiplies rows where Quarb's existential
97    /// binding does not. The *driver* must verify against its
98    /// catalog before executing, and fall back to the scan if it
99    /// cannot.
100    pub join_left: Option<(String, Vec<String>)>,
101    /// Present when the plan is an every-witness JOIN (`<=>*`,
102    /// ruling 2026-11-11): the joined table. Such a JOIN is what the
103    /// query asks, so it carries no uniqueness obligation
104    /// (`join_left` is absent); its obligation is the ORDER: the
105    /// rows of one driver stand in the joined table's own order,
106    /// after the driver's (`order_table`'s key, then this table's).
107    /// A driver that cannot order by both must decline the plan.
108    pub order_then: Option<String>,
109    /// What the plan takes for granted about column types. Quarb
110    /// compares by value (a numeric-looking text and a number meet
111    /// as numbers, a number and other text never meet); SQL
112    /// compares by the column's type, and the loosely typed engines
113    /// (SQLite, MySQL) coerce by rules of their own. The plan is
114    /// the scan's only where the columns it compares with a number
115    /// are numeric-typed and those it compares with text are not:
116    /// the caller asks its catalog and scans when one is otherwise.
117    /// (A strictly typed engine rejects the mismatch itself, which
118    /// sends the caller to the scan as well.)
119    pub column_types: ColumnTypes,
120    /// The tables the plan reads (the FROM table, then a joined one).
121    pub tables: Vec<String>,
122}
123
124/// The column types a pushed plan assumes (see
125/// [`Pushdown::column_types`]).
126#[derive(Clone, Debug, Default, PartialEq, Eq)]
127pub struct ColumnTypes {
128    /// Columns compared with a numeric literal.
129    pub numeric: Vec<String>,
130    /// Columns compared with a text literal.
131    pub text: Vec<String>,
132    /// Columns the plan extracts a JSON path from on the strength
133    /// of the column's declaration alone (the step carried no
134    /// `<json>`): the pushed `json_extract` is the scan's only where
135    /// the catalog declares the column json, since the scan grafts
136    /// a leaf only as declared (spec: Grafting).
137    pub json: Vec<String>,
138}
139
140impl ColumnTypes {
141    pub fn is_empty(&self) -> bool {
142        self.numeric.is_empty() && self.text.is_empty() && self.json.is_empty()
143    }
144
145    /// The assumptions a catalog does not bear out: the columns
146    /// compared with a number that are not numeric-typed, and those
147    /// compared with text that are. `catalog` lists every column of
148    /// the tables read, with whether its type is numeric and
149    /// whether it is declared json; names meet without regard to
150    /// case. A column the catalog does not list is unknown, and
151    /// counts as unmet.
152    pub fn unmet(&self, catalog: &[(String, bool, bool)]) -> Vec<String> {
153        let met = |column: &str, pick: fn(&(String, bool, bool)) -> bool, want: bool| {
154            let mut found = catalog
155                .iter()
156                .filter(|(name, ..)| name.eq_ignore_ascii_case(column))
157                .peekable();
158            found.peek().is_some() && found.all(|entry| pick(entry) == want)
159        };
160        let numeric = |e: &(String, bool, bool)| e.1;
161        let json = |e: &(String, bool, bool)| e.2;
162        let mut out: Vec<String> = Vec::new();
163        for (columns, pick, want) in [
164            (
165                &self.numeric,
166                numeric as fn(&(String, bool, bool)) -> bool,
167                true,
168            ),
169            (&self.text, numeric, false),
170            (&self.json, json, true),
171        ] {
172            for c in columns {
173                if !met(c, pick, want) && !out.contains(c) {
174                    out.push(c.clone());
175                }
176            }
177        }
178        out
179    }
180}
181
182/// The target SQL dialect, for the one construct whose emitted
183/// SQL is not portable: a filter that navigates *into* a JSON
184/// column, which each engine extracts with its own operator.
185/// The rest of a pushdown is dialect-agnostic. A query with no
186/// JSON-column filter emits identical SQL regardless of dialect.
187#[derive(Clone, Copy, PartialEq, Eq, Debug)]
188pub enum Dialect {
189    Postgres,
190    MySql,
191    Sqlite,
192    Mssql,
193    Oracle,
194}
195
196/// Attempt the pushdown translation: `Some` only when every
197/// construct in the query is in the verified-safe set. Anything
198/// else — including everything `export` would merely annotate with
199/// a divergence note — returns `None`, and the caller scans.
200///
201/// `dialect` enables JSON-column-path pushdown for that engine
202/// (fixed-path string equality only); `None` keeps such filters
203/// on the client-side graft.
204pub fn pushdown(quarb: &str, dialect: Option<Dialect>) -> Option<Pushdown> {
205    pushdown_explained(quarb, dialect).ok()
206}
207
208/// [`pushdown`], keeping the refusal: the error names the first
209/// construct that kept the query on the scan path.
210pub fn pushdown_explained(quarb: &str, dialect: Option<Dialect>) -> Result<Pushdown, SqlError> {
211    refuse_marker(quarb)?;
212    let arbor =
213        QueryArbor::parse(quarb).map_err(|e| SqlError::Syntax(format!("parsing Quarb: {e}")))?;
214    refuse_groups(&arbor)?;
215    let mut ex = Exporter {
216        arbor,
217        notes: Vec::new(),
218        strict: true,
219        dialect,
220        from_table: String::new(),
221        join_on_cols: Vec::new(),
222        join_table: None,
223        join_every: false,
224        column_types: ColumnTypes::default(),
225        from_sql: String::new(),
226        join_sql: None,
227        in_on: false,
228        aggregate: false,
229        keyed_hops: false,
230        from_marks: Vec::new(),
231        join_marks: Vec::new(),
232    };
233    let sql = ex.query()?;
234    let order_table = if ex.aggregate {
235        None
236    } else {
237        // Rows come back in the driver's document order — the FROM
238        // table's (driver-first correlation).
239        Some(ex.from_table.clone())
240    };
241    // an every-witness JOIN multiplies rows by design: no uniqueness
242    // to verify, the joined table's order to keep instead
243    let join_left = if ex.join_every {
244        None
245    } else {
246        ex.join_table.clone().map(|t| (t, ex.join_on_cols.clone()))
247    };
248    let order_then = if ex.join_every && !ex.aggregate {
249        ex.join_table.clone()
250    } else {
251        None
252    };
253    let mut tables = vec![ex.from_table.clone()];
254    tables.extend(ex.join_table.clone());
255    Ok(Pushdown {
256        sql,
257        order_table,
258        join_left,
259        order_then,
260        column_types: ex.column_types.clone(),
261        tables,
262    })
263}
264
265/// The dialect-specific SQL that extracts a JSON scalar at a
266/// fixed object path, unquoted to text. `path` holds plain
267/// object keys (identifier-safe, per `json_path`), so no
268/// per-dialect path-escaping is needed. Each engine's operator
269/// returns the value as text and yields NULL for an absent path
270/// or a non-scalar — matching the graft, which excludes those
271/// rows too.
272fn json_extract(dialect: Dialect, qual: Option<&str>, col: &str, path: &[String]) -> String {
273    let qcol = match qual {
274        Some(q) => format!("{q}.{col}"),
275        None => col.to_string(),
276    };
277    match dialect {
278        // `#>>` takes a text-array path and returns text; the
279        // `::jsonb` cast lets it work on json, jsonb, and
280        // text-holding-JSON columns alike (an invalid-JSON row
281        // errors, and the driver falls back to the scan).
282        Dialect::Postgres => format!("({qcol}::jsonb #>> '{{{}}}')", path.join(",")),
283        // JSON_UNQUOTE(JSON_EXTRACT(...)) is the `->>` shorthand
284        // spelled out — portable across MySQL and MariaDB, where
285        // the `->>` operator itself is not.
286        Dialect::MySql => {
287            format!("JSON_UNQUOTE(JSON_EXTRACT({qcol}, '$.{}'))", path.join("."))
288        }
289        Dialect::Sqlite => format!("json_extract({qcol}, '$.{}')", path.join(".")),
290        // JSON_VALUE returns a scalar as text (lax mode: NULL on a
291        // missing path or a non-scalar, no error).
292        Dialect::Mssql | Dialect::Oracle => format!("JSON_VALUE({qcol}, '$.{}')", path.join(".")),
293    }
294}
295
296/// The columns SQLite's `json_each` answers: inside a subquery over
297/// it an unqualified name of these is its own.
298const JSON_EACH_COLUMNS: &[&str] = &[
299    "key", "value", "type", "atom", "id", "parent", "fullkey", "path", "json", "root",
300];
301
302/// Wrap a JSON-path condition in the dialect's validity guard,
303/// where a cheap one exists. SQLite and MySQL error the whole
304/// statement on one malformed-JSON row — which turned every
305/// pushdown over a dirty table into a silent fallback scan — and
306/// the graft excludes such rows anyway (no parse, no subtree), so
307/// the guard is observationally identical. SQL Server gets ISJSON;
308/// Oracle's JSON_VALUE is lax already; PostgreSQL has no guard
309/// short of the ::jsonb cast itself (an invalid row errors and the
310/// caller falls back to the scan).
311fn json_valid_guard(dialect: Dialect, qual: Option<&str>, col: &str, cond: String) -> String {
312    let qcol = match qual {
313        Some(q) => format!("{q}.{col}"),
314        None => col.to_string(),
315    };
316    match dialect {
317        Dialect::Sqlite => format!("(json_valid({qcol}) AND {cond})"),
318        Dialect::MySql => format!("(JSON_VALID({qcol}) AND {cond})"),
319        Dialect::Mssql => format!("(ISJSON({qcol}) = 1 AND {cond})"),
320        Dialect::Postgres | Dialect::Oracle => format!("({cond})"),
321    }
322}
323
324/// The dialect's JSON type probe at a fixed path — non-NULL iff
325/// the path exists. A JSON `null` value reports its type ('null'),
326/// so existence still sees it, matching the graft, where a
327/// null-valued key is a node. Only the dialects with a native
328/// probe participate.
329fn json_type_probe(
330    dialect: Dialect,
331    qual: Option<&str>,
332    col: &str,
333    path: &[String],
334) -> Option<String> {
335    let qcol = match qual {
336        Some(q) => format!("{q}.{col}"),
337        None => col.to_string(),
338    };
339    match dialect {
340        Dialect::Sqlite => Some(format!("json_type({qcol}, '$.{}')", path.join("."))),
341        Dialect::Postgres => Some(format!(
342            "jsonb_typeof({qcol}::jsonb #> '{{{}}}')",
343            path.join(",")
344        )),
345        Dialect::MySql | Dialect::Mssql | Dialect::Oracle => None,
346    }
347}
348
349/// Flip a comparison for a swapped operand order (`lit OP path` →
350/// `path OP' lit`).
351fn flip_op(op: &str) -> String {
352    match op {
353        "<" => ">".into(),
354        "<=" => ">=".into(),
355        ">" => "<".into(),
356        ">=" => "<=".into(),
357        other => other.into(),
358    }
359}
360
361/// SQL keywords that must not appear as a bare identifier or `AS`
362/// alias — quoting them portably differs by dialect, so strict
363/// mode refuses and export mode quotes with double quotes plus a
364/// note.
365const SQL_KEYWORDS: &[&str] = &[
366    "all",
367    "and",
368    "as",
369    "asc",
370    "by",
371    "case",
372    "cross",
373    "desc",
374    "distinct",
375    "else",
376    "end",
377    "except",
378    "exists",
379    "from",
380    "group",
381    "having",
382    "in",
383    "index",
384    "inner",
385    "intersect",
386    "into",
387    "is",
388    "join",
389    "left",
390    "like",
391    "limit",
392    "not",
393    "null",
394    "offset",
395    "on",
396    "or",
397    "order",
398    "outer",
399    "right",
400    "select",
401    "set",
402    "table",
403    "then",
404    "union",
405    "unique",
406    "update",
407    "using",
408    "values",
409    "when",
410    "where",
411];
412
413/// A bare SQL identifier, portable across the target dialects: a
414/// letter or underscore, then letters, digits, and underscores,
415/// and not a reserved word.
416fn is_plain_ident(name: &str) -> bool {
417    !name.is_empty()
418        && name
419            .chars()
420            .next()
421            .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
422        && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_')
423        && !SQL_KEYWORDS.contains(&name.to_ascii_lowercase().as_str())
424}
425
426/// The SELECT under construction.
427#[derive(Default)]
428struct Select {
429    select: Vec<String>,
430    distinct: bool,
431    from: String,
432    join: Option<(String, String)>, // (table, ON condition)
433    /// The join keeps every FROM row (`<=>?`, the outer
434    /// correlation): a LEFT JOIN.
435    join_outer: bool,
436    wheres: Vec<String>,
437    group_by: Option<String>,
438    having: Option<String>,
439    order_by: Option<(String, bool)>, // (expr, desc)
440    limit: Option<String>,
441}
442
443impl Select {
444    fn render(&self) -> String {
445        let mut out = String::from("SELECT ");
446        if self.distinct {
447            out.push_str("DISTINCT ");
448        }
449        if self.select.is_empty() {
450            out.push('*');
451        } else {
452            out.push_str(&self.select.join(", "));
453        }
454        out.push_str(&format!(" FROM {}", self.from));
455        if let Some((t, on)) = &self.join {
456            let kind = if self.join_outer { "LEFT JOIN" } else { "JOIN" };
457            out.push_str(&format!(" {kind} {t} ON {on}"));
458        }
459        if !self.wheres.is_empty() {
460            out.push_str(&format!(" WHERE {}", self.wheres.join(" AND ")));
461        }
462        if let Some(g) = &self.group_by {
463            out.push_str(&format!(" GROUP BY {g}"));
464        }
465        if let Some(h) = &self.having {
466            out.push_str(&format!(" HAVING {h}"));
467        }
468        if let Some((e, desc)) = &self.order_by {
469            out.push_str(&format!(" ORDER BY {e}"));
470            if *desc {
471                out.push_str(" DESC");
472            }
473        }
474        if let Some(n) = &self.limit {
475            out.push_str(&format!(" LIMIT {n}"));
476        }
477        out
478    }
479}
480
481/// A partial-pushdown plan: the leading row predicates as a WHERE
482/// clause for `table`'s fetch. The caller runs the *original* query
483/// against the filtered adapter — the engine re-applies the pushed
484/// predicates (a no-op on pre-filtered rows), so no rewriting.
485pub struct Partial {
486    pub table: String,
487    pub where_sql: String,
488    /// As [`Pushdown::column_types`]: the prefilter keeps every row
489    /// the scan would only where these assumptions hold.
490    pub column_types: ColumnTypes,
491}
492
493/// Attempt a partial pushdown: `Some` when the query's leading row
494/// predicates translate strictly and the rest of the query provably
495/// cannot observe the filtering. The gates, each with its reason:
496/// a single `/table/*` branch with no correlations (other shapes
497/// address other data); only the *leading* run of expression
498/// predicates pushes (a positional predicate before an expression
499/// one sees unfiltered rows on the scan path); the table's name
500/// appears exactly once among the query's steps (a second reach —
501/// `^`-anchored subcontexts, self-references — would see the
502/// filtered subset); no crosslink or resolution axes anywhere
503/// (backlinks and reverse resolution into the table would too);
504/// and no `:` / `:::` metadata anywhere (a filtered `:::n-rows`
505/// would lie).
506pub fn partial_pushdown(quarb: &str, dialect: Option<Dialect>) -> Option<Partial> {
507    partial_pushdown_explained(quarb, dialect).ok()
508}
509
510/// [`partial_pushdown`], keeping the refusal reason. `dialect`
511/// enables the JSON-column prefilters — the strict fixed-path
512/// string equality, plus the relaxed superset forms (numeric
513/// comparisons, existence) that are safe here and only here,
514/// because the caller re-runs the original query over the
515/// fetched subset.
516pub fn partial_pushdown_explained(
517    quarb: &str,
518    dialect: Option<Dialect>,
519) -> Result<Partial, SqlError> {
520    partial_pushdown_with(quarb, dialect, false)
521}
522
523/// [`partial_pushdown_explained`] for an adapter that reads steps by
524/// key and filters only the table's listing
525/// (`RelationalModel::with_filtered_listing`): crosslinks and
526/// resolutions no longer refuse the prefilter, since a step into the
527/// filtered table reads its row unfiltered. What still refuses: a
528/// second navigation into the table, and a `<--` or `=>` scope that
529/// could list it.
530pub fn partial_pushdown_keyed(quarb: &str, dialect: Option<Dialect>) -> Result<Partial, SqlError> {
531    partial_pushdown_with(quarb, dialect, true)
532}
533
534fn partial_pushdown_with(
535    quarb: &str,
536    dialect: Option<Dialect>,
537    keyed_hops: bool,
538) -> Result<Partial, SqlError> {
539    refuse_marker(quarb)?;
540    let arbor =
541        QueryArbor::parse(quarb).map_err(|e| SqlError::Syntax(format!("parsing Quarb: {e}")))?;
542    refuse_groups(&arbor)?;
543    let mut ex = Exporter {
544        arbor,
545        notes: Vec::new(),
546        strict: true,
547        dialect,
548        from_table: String::new(),
549        join_on_cols: Vec::new(),
550        join_table: None,
551        join_every: false,
552        column_types: ColumnTypes::default(),
553        from_sql: String::new(),
554        join_sql: None,
555        in_on: false,
556        aggregate: false,
557        keyed_hops,
558        from_marks: Vec::new(),
559        join_marks: Vec::new(),
560    };
561    ex.partial()
562}
563
564/// Refuse a query carrying path-pattern groups: neither the SQL
565/// translation nor the pushdown safe set covers them, and the shape
566/// checks below count only `step` children — an unguarded group
567/// would silently vanish from the translation. Refused queries fall
568/// back to the scan path, which evaluates groups correctly.
569fn refuse_groups(arbor: &QueryArbor) -> Result<(), SqlError> {
570    let mut stack = vec![arbor.root()];
571    while let Some(n) = stack.pop() {
572        if arbor.name(n).as_deref() == Some("group") {
573            return Err(SqlError::Unsupported(
574                "path patterns (groups and quantifiers)".into(),
575            ));
576        }
577        stack.extend(arbor.children(n));
578    }
579    Ok(())
580}
581
582struct Exporter {
583    arbor: QueryArbor,
584    notes: Vec<String>,
585    /// Pushdown mode: refuse every construct whose SQL semantics
586    /// are not provably identical to Quarb's (LIKE case folding,
587    /// truthiness, group/distinct/sort ordering).
588    strict: bool,
589    /// The target dialect for JSON-column-path pushdown; `None`
590    /// leaves such filters on the client-side graft.
591    dialect: Option<Dialect>,
592    from_table: String,
593    join_on_cols: Vec<String>,
594    join_table: Option<String>,
595    /// The correlation is `<=>*`: every witness, the SQL JOIN itself.
596    join_every: bool,
597    /// What the comparisons assume of column types (see
598    /// [`Pushdown::column_types`]).
599    column_types: ColumnTypes,
600    from_sql: String,
601    join_sql: Option<String>,
602    in_on: bool,
603    aggregate: bool,
604    /// The adapter reads steps by key (the pushed-down WHERE filters
605    /// the table's listing only), so crosslinks and resolutions are
606    /// safe beside the prefilter.
607    keyed_hops: bool,
608    /// The names the driver's rows are marked under (`/tracks/* .t`)
609    /// and the names the joined expression files its witness under
610    /// (`<=> /albums/*[…] .al`): `$$.t::col` is the FROM table's
611    /// column, `$$.al::col` the joined table's.
612    from_marks: Vec<String>,
613    join_marks: Vec<String>,
614}
615
616impl Exporter {
617    fn kids(&self, n: NodeId, kind: &str) -> Vec<NodeId> {
618        self.arbor
619            .children(n)
620            .into_iter()
621            .filter(|&c| self.arbor.name(c).as_deref() == Some(kind))
622            .collect()
623    }
624
625    fn kid(&self, n: NodeId, kind: &str) -> Option<NodeId> {
626        self.kids(n, kind).into_iter().next()
627    }
628
629    fn prop(&self, n: NodeId, key: &str) -> Option<Value> {
630        self.arbor.property(n, key)
631    }
632
633    fn prop_s(&self, n: NodeId, key: &str) -> String {
634        self.prop(n, key).map(|v| v.to_string()).unwrap_or_default()
635    }
636
637    fn kind(&self, n: NodeId) -> String {
638        self.arbor.name(n).unwrap_or_default()
639    }
640
641    /// Whether `n` is a bare `null` literal operand.
642    fn is_null_literal(&self, n: NodeId) -> bool {
643        self.kind(n) == "literal" && self.prop_s(n, "type") == "null"
644    }
645
646    /// The partial-pushdown analysis (see [`partial_pushdown`]).
647    fn partial(&mut self) -> Result<Partial, SqlError> {
648        let root = self.arbor.root();
649        let q = self
650            .kid(root, "query")
651            .ok_or_else(|| SqlError::Unsupported("empty query".into()))?;
652        // A correlation does not forbid the prefilter: the driver's
653        // own predicates restrict the driver's rows whatever the join
654        // then binds. What must not happen is the join (or anything
655        // else) reaching the filtered table a second time — a
656        // self-join would see only the filtered rows — and the
657        // whole-query gates below refuse exactly that: they walk the
658        // correlation entries too.
659        let branches = self.kids(q, "branch");
660        if branches.len() != 1 {
661            return Err(SqlError::Unsupported("a branch union".into()));
662        }
663        let (table, preds) = self.table_branch_lead(branches[0], self.keyed_hops)?;
664
665        // Whole-query gates.
666        let all = self.walk_all(root);
667        let mut table_mentions = 0;
668        for n in &all {
669            match self.kind(*n).as_str() {
670                "step" => {
671                    let axis = self.prop_s(*n, "axis");
672                    // A crosslink, a resolution or a value match may
673                    // land in (or search) the filtered table and would
674                    // see only its filtered rows: `=>` and the scoped
675                    // `<--` carry their scope as text, so the table
676                    // count below cannot see them.
677                    if matches!(axis.as_str(), "->" | "<-" | "--" | "-->" | "<--" | "=>") {
678                        // Where steps are read by key they are exact
679                        // beside the prefilter. A scope is a listing,
680                        // though: it is safe only when it plainly
681                        // lists another table (`/other/…`), and an
682                        // unscoped `=>` walks every table.
683                        let scope = self.prop_s(*n, "scope");
684                        let lists_other_table = scope
685                            .strip_prefix('/')
686                            .and_then(|rest| rest.split(['/', '[', '<']).next())
687                            .is_some_and(|first| {
688                                !first.is_empty()
689                                    && first != table
690                                    && first.chars().all(|c| c.is_alphanumeric() || c == '_')
691                            });
692                        let safe = self.keyed_hops
693                            && if scope.is_empty() {
694                                axis != "=>"
695                            } else {
696                                lists_other_table
697                            };
698                        if !safe {
699                            return Err(SqlError::Unsupported(
700                                "crosslink/resolution axes could reach \
701                                 the filtered table"
702                                    .into(),
703                            ));
704                        }
705                    }
706                    if self.prop_s(*n, "matcher") == table {
707                        table_mentions += 1;
708                    }
709                }
710                "projection" if self.prop_s(*n, "kind") != "property" => {
711                    return Err(SqlError::Unsupported(
712                        "metadata would observe the filtering \
713                         (:::n-rows, :index)"
714                            .into(),
715                    ));
716                }
717                _ => {}
718            }
719        }
720        if table_mentions != 1 {
721            return Err(SqlError::Unsupported(
722                "the table is reached more than once".into(),
723            ));
724        }
725
726        // The leading run of expression predicates. Strict
727        // translation first; a predicate the strict translator
728        // refuses tries the relaxed prefilter ladder — superset
729        // semantics: the fetched set may hold extra rows, because
730        // the caller re-runs the ORIGINAL query, which re-applies
731        // every predicate; it must never lose a matching row. An
732        // untranslatable predicate is skipped, not fatal — a
733        // shorter conjunction is still a superset filter. A
734        // positional predicate (index/range) ends the run:
735        // predicates after it filter a positionally-selected
736        // subsequence.
737        let mut conds = Vec::new();
738        for p in preds {
739            if self.prop_s(p, "kind") != "expr" {
740                break;
741            }
742            match self.predicate_cond(p, None) {
743                Ok(c) => conds.push(c),
744                Err(_) => {
745                    if let Some(c) = self.prefilter_cond(p) {
746                        conds.push(c);
747                    }
748                }
749            }
750        }
751        if conds.is_empty() {
752            return Err(SqlError::Unsupported(
753                "no leading expression predicates to push".into(),
754            ));
755        }
756        Ok(Partial {
757            table,
758            where_sql: conds.join(" AND "),
759            column_types: self.column_types.clone(),
760        })
761    }
762
763    /// The relaxed prefilter for one refused expression predicate:
764    /// the AND of whatever conjuncts translate, each a superset of
765    /// the rows its Quarb counterpart keeps. None when nothing
766    /// does — the predicate stays entirely on the engine.
767    fn prefilter_cond(&mut self, p: NodeId) -> Option<String> {
768        let parts: Vec<String> = self
769            .arbor
770            .children(p)
771            .into_iter()
772            .filter_map(|c| {
773                self.pred_expr(c, None)
774                    .ok()
775                    .or_else(|| self.prefilter_expr(c))
776            })
777            .collect();
778        if parts.is_empty() {
779            None
780        } else {
781            Some(parts.join(" AND "))
782        }
783    }
784
785    /// One conjunct's superset prefilter — fixed-path JSON shapes
786    /// only, per dialect. Every form here is proven to keep every
787    /// row the graft would match (extras are fine; the engine
788    /// re-checks). The proofs lean on three graft facts: absent
789    /// paths match no comparison; `value_eq`/`value_cmp` coerce
790    /// numeric-looking strings numerically; and Str-to-Str
791    /// ordering is bytewise.
792    fn prefilter_expr(&mut self, e: NodeId) -> Option<String> {
793        let dialect = self.dialect?;
794        match self.kind(e).as_str() {
795            // A bare truthy path: existence (with `::`, value
796            // truthiness — existence is its superset). The type
797            // probe sees JSON null too, as the graft does.
798            "path" => {
799                // The property form (`/col/a::b`) is truthy on a
800                // scalar and on a list with an item: the key is
801                // present either way, so existence is its superset
802                // too.
803                let (col, path) = self
804                    .json_path_loose(e)
805                    .or_else(|| self.json_property_path(e))?;
806                self.note_json_column(e, &col);
807                let probe = json_type_probe(dialect, None, &col, &path)?;
808                Some(json_valid_guard(
809                    dialect,
810                    None,
811                    &col,
812                    format!("{probe} IS NOT NULL"),
813                ))
814            }
815            "compare" => {
816                let op = self.prop_s(e, "op");
817                let kids = self.arbor.children(e);
818                if kids.len() != 2 {
819                    return None;
820                }
821                for (pi, li) in [(0usize, 1usize), (1, 0)] {
822                    let Some((col, path)) = self.json_path(kids[pi]) else {
823                        continue;
824                    };
825                    if self.kind(kids[li]) != "literal" {
826                        continue;
827                    }
828                    let ty = self.prop_s(kids[li], "type");
829                    if ty == "null" {
830                        return None;
831                    }
832                    let raw = self
833                        .prop(kids[li], "value")
834                        .map(|v| v.to_string())
835                        .unwrap_or_default();
836                    let op = if pi == 0 { op.clone() } else { flip_op(&op) };
837                    self.note_json_column(kids[pi], &col);
838                    return self.json_prefilter(dialect, &col, &path, &op, &ty, &raw);
839                }
840                // The property form, `/col/a::b OP literal`.
841                for (pi, li) in [(0usize, 1usize), (1, 0)] {
842                    let Some((col, path)) = self.json_property_path(kids[pi]) else {
843                        continue;
844                    };
845                    if self.kind(kids[li]) != "literal" {
846                        continue;
847                    }
848                    let ty = self.prop_s(kids[li], "type");
849                    if ty == "null" {
850                        return None;
851                    }
852                    let raw = self
853                        .prop(kids[li], "value")
854                        .map(|v| v.to_string())
855                        .unwrap_or_default();
856                    let op = if pi == 0 { op.clone() } else { flip_op(&op) };
857                    self.note_json_column(kids[pi], &col);
858                    return self.json_property_prefilter(dialect, &col, &path, &op, &ty, &raw);
859                }
860                None
861            }
862            _ => None,
863        }
864    }
865
866    /// The superset prefilter for `/col/a::b OP literal`, the
867    /// property form of a JSON path. It is [`json_prefilter`] with
868    /// the two places where a property differs from a child's value:
869    ///
870    /// - an ARRAY of scalars is a list, and a list in a comparison
871    ///   matches when any item does (`[::tags = 'a']` keeps
872    ///   `{"tags": ["a", "b"]}`); the child form's `/tags::` has no
873    ///   value there. So every form lets the arrays through for the
874    ///   engine to decide;
875    /// - `!=` keeps a row whose key is ABSENT (null is not the
876    ///   literal), where the child form has no node and no thread.
877    ///   Its floor is the existence of the node the property is
878    ///   read on; on the column itself there is none (a cell that
879    ///   is no JSON still answers a null property).
880    ///
881    /// [`json_prefilter`]: Self::json_prefilter
882    fn json_property_prefilter(
883        &self,
884        d: Dialect,
885        col: &str,
886        path: &[String],
887        op: &str,
888        lit_ty: &str,
889        raw: &str,
890    ) -> Option<String> {
891        if !matches!(op, "=" | "!=" | "<" | "<=" | ">" | ">=") {
892            return None;
893        }
894        if op == "!=" {
895            let owner = &path[..path.len() - 1];
896            if owner.is_empty() {
897                return None;
898            }
899            let probe = json_type_probe(d, None, col, owner)?;
900            return Some(json_valid_guard(
901                d,
902                None,
903                col,
904                format!("{probe} IS NOT NULL"),
905            ));
906        }
907        let ty = json_type_probe(d, None, col, path)?;
908        let numeric_lit = raw.trim().parse::<f64>().is_ok_and(|f| f.is_finite())
909            && (lit_ty != "text" || !raw.trim().is_empty());
910        if numeric_lit {
911            let num: f64 = raw.trim().parse().ok()?;
912            if num.abs() >= 9_007_199_254_740_992.0 {
913                return None;
914            }
915            let lit = raw.trim();
916            match d {
917                Dialect::Sqlite => {
918                    let ex = json_extract(d, None, col, path);
919                    Some(json_valid_guard(
920                        d,
921                        None,
922                        col,
923                        format!("({ex} {op} {lit} OR {ty} IN ('text', 'array'))"),
924                    ))
925                }
926                Dialect::Postgres => {
927                    let text = json_extract(d, None, col, path);
928                    Some(format!(
929                        "(CASE WHEN {ty} = 'number' THEN ({text})::float8 {op} {lit} \
930                         WHEN {ty} IN ('string', 'array') THEN true ELSE false END)"
931                    ))
932                }
933                Dialect::MySql | Dialect::Mssql | Dialect::Oracle => None,
934            }
935        } else if lit_ty == "text" {
936            if raw.contains('\'') || raw.contains('\\') {
937                return None;
938            }
939            match d {
940                Dialect::Sqlite => {
941                    let ex = json_extract(d, None, col, path);
942                    Some(json_valid_guard(
943                        d,
944                        None,
945                        col,
946                        format!("({ex} {op} '{raw}' OR {ty} = 'array')"),
947                    ))
948                }
949                // Equality alone: a string equals the literal or it
950                // does not (a looser collation keeps more, never
951                // fewer); ordering runs into collation, as in the
952                // child form.
953                Dialect::Postgres if op == "=" => {
954                    let text = json_extract(d, None, col, path);
955                    Some(format!(
956                        "(CASE WHEN {ty} = 'string' THEN {text} = '{raw}' \
957                         WHEN {ty} = 'array' THEN true ELSE false END)"
958                    ))
959                }
960                _ => None,
961            }
962        } else {
963            None
964        }
965    }
966
967    /// Build the dialect's superset prefilter for
968    /// `json_path OP literal`.
969    fn json_prefilter(
970        &self,
971        d: Dialect,
972        col: &str,
973        path: &[String],
974        op: &str,
975        lit_ty: &str,
976        raw: &str,
977    ) -> Option<String> {
978        if !matches!(op, "=" | "!=" | "<" | "<=" | ">" | ">=") {
979            return None;
980        }
981        // `!=` matches every present path whose value isn't the
982        // literal — including JSON null and cross-typed values —
983        // so its floor is existence.
984        if op == "!=" {
985            let probe = json_type_probe(d, None, col, path)?;
986            return Some(json_valid_guard(
987                d,
988                None,
989                col,
990                format!("{probe} IS NOT NULL"),
991            ));
992        }
993        let numeric_lit = raw.trim().parse::<f64>().is_ok_and(|f| f.is_finite())
994            && (lit_ty != "text" || !raw.trim().is_empty());
995        if numeric_lit {
996            let num: f64 = raw.trim().parse().ok()?;
997            // Past 2^53 the engines' exact integers and the
998            // graft's f64 part ways at the boundary.
999            if num.abs() >= 9_007_199_254_740_992.0 {
1000                return None;
1001            }
1002            let lit = raw.trim();
1003            match d {
1004                // Typed extract: JSON numbers compare numerically
1005                // (both sides parse the same source text to f64);
1006                // string values escape through the type probe —
1007                // the graft may coerce them, the server must not
1008                // drop them.
1009                Dialect::Sqlite => {
1010                    let ex = json_extract(d, None, col, path);
1011                    let ty = json_type_probe(d, None, col, path)?;
1012                    Some(json_valid_guard(
1013                        d,
1014                        None,
1015                        col,
1016                        format!("({ex} {op} {lit} OR {ty} = 'text')"),
1017                    ))
1018                }
1019                // float8 is the graft's f64; jsonb numbers print
1020                // exactly, so the cast parses the same decimal.
1021                // Strings escape; everything else can't match.
1022                Dialect::Postgres => {
1023                    let text = json_extract(d, None, col, path);
1024                    let ty = json_type_probe(d, None, col, path)?;
1025                    Some(format!(
1026                        "(CASE WHEN {ty} = 'number' THEN ({text})::float8 {op} {lit} \
1027                         WHEN {ty} = 'string' THEN true ELSE false END)"
1028                    ))
1029                }
1030                Dialect::MySql | Dialect::Mssql | Dialect::Oracle => None,
1031            }
1032        } else if lit_ty == "text" {
1033            if raw.contains('\'') || raw.contains('\\') {
1034                return None;
1035            }
1036            match d {
1037                // Typed extract again: string values order bytewise
1038                // on both sides; numbers and booleans sit below
1039                // text in SQLite's cross-type order, so `<` keeps
1040                // them (extras, re-checked) and `>` drops them
1041                // (the graft refuses to order Str against them).
1042                Dialect::Sqlite => {
1043                    let ex = json_extract(d, None, col, path);
1044                    Some(json_valid_guard(d, None, col, format!("{ex} {op} '{raw}'")))
1045                }
1046                // Text ordering elsewhere runs into collation; the
1047                // engine keeps those.
1048                _ => None,
1049            }
1050        } else {
1051            None
1052        }
1053    }
1054
1055    /// [`json_path`], loosened for existence: the projection may
1056    /// be absent (a bare structural path) as well as the bare
1057    /// `::`.
1058    /// Whether a JSON path under a column reads the column as JSON
1059    /// on the strength of its declaration alone: the column step
1060    /// (the first step) carries no `<json>` trait. Such a column is
1061    /// an assumption of the plan ([`ColumnTypes::json`]); with the
1062    /// trait the graft is declared on the step and holds on both
1063    /// roads.
1064    fn note_json_column(&mut self, o: NodeId, col: &str) {
1065        let declared_on_hop = self.kids(o, "step").first().is_some_and(|s| {
1066            self.kids(*s, "trait").iter().any(|t| {
1067                matches!(self.prop(*t, "alts"), Some(Value::List(alts))
1068                    if alts.iter().any(|a| matches!(a, Value::Str(x) if x == "json")))
1069            })
1070        });
1071        if !declared_on_hop && !self.column_types.json.iter().any(|c| c == col) {
1072            self.column_types.json.push(col.to_string());
1073        }
1074    }
1075
1076    fn json_path_loose(&self, o: NodeId) -> Option<(String, Vec<String>)> {
1077        if self.json_path(o).is_some() {
1078            return self.json_path(o);
1079        }
1080        if self.kind(o) != "path" || self.kid(o, "projection").is_some() {
1081            return None;
1082        }
1083        let steps = self.kids(o, "step");
1084        if steps.len() < 2 {
1085            return None;
1086        }
1087        let mut names = Vec::with_capacity(steps.len());
1088        for s in &steps {
1089            if self.prop_s(*s, "axis") != "/"
1090                || self.prop_s(*s, "matcher-kind") != "name"
1091                || self.kid(*s, "predicate").is_some()
1092            {
1093                return None;
1094            }
1095            let name = self.prop_s(*s, "matcher");
1096            let plain = !name.is_empty()
1097                && name
1098                    .chars()
1099                    .next()
1100                    .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
1101                && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_');
1102            if !plain {
1103                return None;
1104            }
1105            names.push(name);
1106        }
1107        let col = names.remove(0);
1108        Some((col, names))
1109    }
1110
1111    fn walk_all(&self, n: NodeId) -> Vec<NodeId> {
1112        let mut out = vec![n];
1113        let mut i = 0;
1114        while i < out.len() {
1115            out.extend(self.arbor.children(out[i]));
1116            i += 1;
1117        }
1118        out
1119    }
1120
1121    fn query(&mut self) -> Result<String, SqlError> {
1122        let root = self.arbor.root();
1123        let q = self
1124            .kid(root, "query")
1125            .ok_or_else(|| SqlError::Unsupported("empty query".into()))?;
1126        let mut sel = Select::default();
1127
1128        // Correlations: exactly one becomes the JOIN's left table.
1129        let corrs = self.kids(q, "query");
1130        let branches = self.kids(q, "branch");
1131        if branches.len() != 1 {
1132            return Err(SqlError::Unsupported(
1133                "a branch union (SQL has UNION, but result shapes differ; export one branch)"
1134                    .into(),
1135            ));
1136        }
1137
1138        if corrs.len() > 1 {
1139            return Err(SqlError::Unsupported(
1140                "more than one correlation context".into(),
1141            ));
1142        }
1143        if let Some(corr) = corrs.first() {
1144            // Driver-first: the query's own branch is the FROM
1145            // table (the driver); the correlation entry is the
1146            // joined table, whose `$$` equalities form the ON.
1147            let (ltable, lpreds) = self.table_branch(branches[0])?;
1148            let (rtable, rpreds) = self.table_branch(self.kids(*corr, "branch")[0])?;
1149            self.from_marks = self.row_marks(branches[0])?;
1150            self.join_marks = self.row_marks(self.kids(*corr, "branch")[0])?;
1151            // a joined expression with a pipeline of its own binds
1152            // what that pipeline leaves: not a row of its table
1153            if self.kid(*corr, "pipeline").is_some() {
1154                return Err(SqlError::Unsupported(
1155                    "a joined expression with a pipeline of its own".into(),
1156                ));
1157            }
1158            // The SQL renderings of the two table names (validated
1159            // or quoted); the raw names stay in the plan metadata,
1160            // which the driver matches against its catalog.
1161            let lsql = self.sql_ident(&ltable, "table name")?;
1162            let rsql = self.sql_ident(&rtable, "table name")?;
1163            self.from_sql = lsql.clone();
1164            self.join_sql = Some(rsql.clone());
1165            // The driver's own predicates are plain WHERE.
1166            let driver_conds: Vec<NodeId> = lpreds;
1167            // Split the joined expression's predicates: `$$`
1168            // equalities form the ON, the rest the WHERE.
1169            let mut on = Vec::new();
1170            let mut wheres = Vec::new();
1171            for p in rpreds {
1172                self.split_join_pred(p, &rsql, &mut on, &mut wheres)?;
1173            }
1174            // `<=>?` — the outer correlation keeps a driver row no
1175            // joined row admits, its witness null: a LEFT JOIN. The
1176            // joined expression's own predicates then select the
1177            // witness, so they belong to the ON; in a WHERE they
1178            // would discard the unmatched rows again.
1179            let outer = matches!(self.prop(*corr, "outer"), Some(Value::Bool(true)));
1180            if outer {
1181                on.append(&mut wheres);
1182            }
1183            sel.join_outer = outer;
1184            if on.is_empty() {
1185                return Err(SqlError::Unsupported(
1186                    "a correlation without a '$$' equality (no JOIN condition)".into(),
1187                ));
1188            }
1189            for p in driver_conds {
1190                let cond = self.predicate_cond(p, Some(&lsql.clone()))?;
1191                wheres.push(cond);
1192            }
1193            // `<=>*` — every witness: the JOIN as SQL means it, one
1194            // row per matching pair; the plain `<=>` binds one.
1195            self.join_every = matches!(self.prop(*corr, "every"), Some(Value::Bool(true)));
1196            if !self.join_every {
1197                self.notes.push(
1198                    "JOIN: Quarb's binding is existential (one row per FROM row, \
1199                     bound to its first witness); SQL multiplies rows when \
1200                     several joined rows match"
1201                        .to_string(),
1202                );
1203            }
1204            sel.from = lsql.clone();
1205            self.from_table = ltable;
1206            self.join_table = Some(rtable);
1207            sel.join = Some((rsql.clone(), on.join(" AND ")));
1208            sel.wheres = wheres;
1209            // A terminal projection on the driving branch is a
1210            // one-column select of the FROM table.
1211            if let Some(proj) = self.kid(branches[0], "projection") {
1212                let col = self.projection_col(proj)?;
1213                let col = self.sql_ident(&col, "column name")?;
1214                sel.select.push(format!("{lsql}.{col}"));
1215            }
1216            if self
1217                .kid(self.kids(*corr, "branch")[0], "projection")
1218                .is_some()
1219            {
1220                return Err(SqlError::Unsupported(
1221                    "a projection on the joined expression (project the witness \
1222                     in the pipeline: '$$1::col')"
1223                        .into(),
1224                ));
1225            }
1226            self.pipeline(q, &mut sel, Some((&lsql, &rsql)))?;
1227        } else {
1228            let (table, preds) = self.table_branch(branches[0])?;
1229            self.from_marks = self.row_marks(branches[0])?;
1230            sel.from = self.sql_ident(&table, "table name")?;
1231            self.from_table = table;
1232            for p in preds {
1233                let cond = self.predicate_cond(p, None)?;
1234                sel.wheres.push(cond);
1235            }
1236            // A terminal projection is a one-column select.
1237            if let Some(proj) = self.kid(branches[0], "projection") {
1238                let col = self.projection_col(proj)?;
1239                sel.select.push(self.sql_ident(&col, "column name")?);
1240            }
1241            self.pipeline(q, &mut sel, None)?;
1242        }
1243        Ok(sel.render())
1244    }
1245
1246    /// A `/table/*[preds]` branch: the table name and the row-step
1247    /// predicate nodes.
1248    fn table_branch(&mut self, b: NodeId) -> Result<(String, Vec<NodeId>), SqlError> {
1249        self.table_branch_lead(b, false)
1250    }
1251
1252    /// [`table_branch`]; with `more`, the branch may go on after
1253    /// `/table/*[…]` (a step, a cell): the prefilter concerns its
1254    /// first two steps only, and the steps after them are held to
1255    /// the whole-query gates.
1256    fn table_branch_lead(
1257        &mut self,
1258        b: NodeId,
1259        more: bool,
1260    ) -> Result<(String, Vec<NodeId>), SqlError> {
1261        let steps = self.kids(b, "step");
1262        if steps.len() < 2 || (steps.len() > 2 && !more) {
1263            return Err(SqlError::Unsupported(
1264                "navigation beyond /table/* (SQL sees tables and rows)".into(),
1265            ));
1266        }
1267        let (t, rows) = (steps[0], steps[1]);
1268        if self.prop_s(t, "axis") != "/"
1269            || self.prop_s(t, "matcher-kind") != "name"
1270            || self.prop_s(rows, "axis") != "/"
1271            || self.prop_s(rows, "matcher-kind") != "any"
1272        {
1273            return Err(SqlError::Unsupported(
1274                "navigation beyond /table/* (SQL sees tables and rows)".into(),
1275            ));
1276        }
1277        if self.kid(t, "predicate").is_some() {
1278            return Err(SqlError::Unsupported(
1279                "a predicate on the table step".into(),
1280            ));
1281        }
1282        Ok((self.prop_s(t, "matcher"), self.kids(rows, "predicate")))
1283    }
1284
1285    /// The names a `/table/*` branch marks its rows under: the marks
1286    /// standing after the row step. A mark anywhere else names a
1287    /// node SQL has no row for (the table) and is not translated.
1288    fn row_marks(&self, b: NodeId) -> Result<Vec<String>, SqlError> {
1289        let kids = self.arbor.children(b);
1290        let rows = self.kids(b, "step").get(1).copied();
1291        let after = rows
1292            .and_then(|r| kids.iter().position(|k| *k == r))
1293            .unwrap_or(kids.len());
1294        let mut names = Vec::new();
1295        for (i, k) in kids.iter().enumerate() {
1296            if self.kind(*k) != "mark" {
1297                continue;
1298            }
1299            if i < after {
1300                return Err(SqlError::Unsupported(
1301                    "a mark before the rows (SQL sees tables and rows)".into(),
1302                ));
1303            }
1304            // (a mark with no name is read by position alone, and
1305            // a read by position is not translated)
1306            if let Some(Value::Str(n)) = self.prop(*k, "name") {
1307                names.push(n);
1308            }
1309        }
1310        Ok(names)
1311    }
1312
1313    /// A path operand read through the capsa's node side (`$$.al`,
1314    /// `$$.2`, `((@))`): the mark's name where the anchor is one
1315    /// mark by name, and whether it is any mark anchor at all.
1316    fn mark_anchor(&self, o: NodeId) -> (Option<String>, bool) {
1317        let named = match self.prop(o, "mark") {
1318            Some(Value::Str(n)) => Some(n),
1319            _ => None,
1320        };
1321        let any = named.is_some()
1322            || [
1323                "mark-index",
1324                "mark-top",
1325                "mark-back",
1326                "marks-all",
1327                "marks-name",
1328                "edge-end",
1329                "node-id",
1330            ]
1331            .iter()
1332            .any(|k| self.prop(o, k).is_some());
1333        (named, any)
1334    }
1335
1336    /// One row predicate as a WHERE condition (qualify columns with
1337    /// `qualifier` when joining).
1338    fn predicate_cond(&mut self, p: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
1339        if self.prop_s(p, "kind") != "expr" {
1340            return Err(SqlError::Unsupported(
1341                "a positional predicate on rows (SQL rows are unordered; ORDER BY + LIMIT)".into(),
1342            ));
1343        }
1344        let parts: Vec<String> = self
1345            .arbor
1346            .children(p)
1347            .into_iter()
1348            .map(|c| self.pred_expr(c, qual))
1349            .collect::<Result<_, _>>()?;
1350        Ok(parts.join(" AND "))
1351    }
1352
1353    fn pred_expr(&mut self, e: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
1354        match self.kind(e).as_str() {
1355            "and" | "or" => {
1356                let op = self.kind(e).to_uppercase();
1357                let kids: Vec<String> = self
1358                    .arbor
1359                    .children(e)
1360                    .into_iter()
1361                    .map(|c| self.pred_expr(c, qual))
1362                    .collect::<Result<_, _>>()?;
1363                Ok(format!("({})", kids.join(&format!(" {op} "))))
1364            }
1365            "not" => {
1366                // SQL's `NOT` propagates UNKNOWN: `NOT (x = 5)` is
1367                // UNKNOWN for a NULL `x` and drops the row, but Quarb's
1368                // negation keeps it (the inner `value_eq` is false, so
1369                // its negation is true). Not provably identical without
1370                // the schema — the pushdown paths refuse it and scan.
1371                if self.strict {
1372                    return Err(SqlError::Semantics(
1373                        "SQL NOT propagates UNKNOWN, dropping the NULL rows \
1374                         Quarb keeps"
1375                            .into(),
1376                    ));
1377                }
1378                let inner: Vec<String> = self
1379                    .arbor
1380                    .children(e)
1381                    .into_iter()
1382                    .map(|c| self.pred_expr(c, qual))
1383                    .collect::<Result<_, _>>()?;
1384                Ok(format!("NOT ({})", inner.join(" AND ")))
1385            }
1386            "parens" => {
1387                let inner: Vec<String> = self
1388                    .arbor
1389                    .children(e)
1390                    .into_iter()
1391                    .map(|c| self.pred_expr(c, qual))
1392                    .collect::<Result<_, _>>()?;
1393                Ok(format!("({})", inner.join(" AND ")))
1394            }
1395            "compare" => {
1396                let op = self.prop_s(e, "op");
1397                let kids = self.arbor.children(e);
1398                // A comparison against a bare `null` literal. Quarb's
1399                // `value_eq` treats NULL as an ordinary value
1400                // (`value_eq(NULL, NULL)` is true, `value_eq(NULL, x)`
1401                // false), so `= null` keeps exactly the NULL rows and
1402                // `!= null` the non-NULL rows. SQL's `= NULL` / `<>
1403                // NULL` are always UNKNOWN and drop every row; the
1404                // `IS [NOT] NULL` forms are provably identical (and
1405                // portable across every target dialect).
1406                if matches!(op.as_str(), "=" | "!=")
1407                    && (self.is_null_literal(kids[0]) || self.is_null_literal(kids[1]))
1408                {
1409                    let other = if self.is_null_literal(kids[0]) {
1410                        kids[1]
1411                    } else {
1412                        kids[0]
1413                    };
1414                    let col = self.operand(other, qual)?;
1415                    return Ok(if op == "=" {
1416                        format!("{col} IS NULL")
1417                    } else {
1418                        format!("{col} IS NOT NULL")
1419                    });
1420                }
1421                // JSON-column-path pushdown: `[/col/a/b:: = 'lit']`
1422                // navigates into a JSON column and compares a fixed
1423                // path to a string literal. Only this exact shape —
1424                // fixed object path, string equality, and a literal
1425                // that does NOT look numeric — is provably identical
1426                // to the client-side graft (each engine's scalar
1427                // extractor unquotes to text, and an absent path or
1428                // non-string value excludes the row on both sides,
1429                // matching Quarb's `value_eq` on non-numeric text).
1430                // Numeric casts, `!=`, wildcards, and deeper
1431                // predicates fall through to the ordinary operand
1432                // logic below, which refuses the navigation; the
1433                // partial-pushdown prefilter ladder picks those up
1434                // with superset semantics. Enabled only when a
1435                // dialect is set.
1436                if op == "="
1437                    && let Some(dialect) = self.dialect
1438                {
1439                    for (pi, li) in [(0usize, 1usize), (1, 0)] {
1440                        if self.is_text_literal(kids[li])
1441                            && let Some((col, path)) = self.json_path(kids[pi])
1442                        {
1443                            self.note_json_column(kids[pi], &col);
1444                            // Quarb's value_eq compares numeric-looking
1445                            // strings numerically — '150' matches the
1446                            // JSON number 150, and the string "150.0".
1447                            // No engine's text extractor coerces that
1448                            // way, so the pushed compare would drop
1449                            // rows the graft keeps. Refuse; the
1450                            // prefilter ladder handles it as a
1451                            // superset.
1452                            let raw = self
1453                                .prop(kids[li], "value")
1454                                .map(|v| v.to_string())
1455                                .unwrap_or_default();
1456                            if raw.trim().parse::<f64>().is_ok() {
1457                                return Err(SqlError::Semantics(
1458                                    "a numeric-looking text literal against a \
1459                                     JSON path compares numerically in Quarb \
1460                                     (value coercion); no SQL extractor \
1461                                     matches that"
1462                                        .into(),
1463                                ));
1464                            }
1465                            // An extractor answers a container as
1466                            // its JSON text (SQLite `[1,2]`,
1467                            // PostgreSQL `[1, 2]`), so a literal that
1468                            // is such a text equalled the array or
1469                            // the object itself, which has no value
1470                            // in the graft: the push kept a row the
1471                            // scan drops. No scalar's text opens
1472                            // with a bracket or a brace that a
1473                            // container's does not, so such a
1474                            // literal rides the prefilter.
1475                            if raw.starts_with('[') || raw.starts_with('{') {
1476                                return Err(SqlError::Semantics(
1477                                    "a text literal that opens like a JSON container \
1478                                     could equal an array's or an object's own text \
1479                                     in the extractor, which has no value in Quarb"
1480                                        .into(),
1481                                ));
1482                            }
1483                            let lit = self.operand(kids[li], qual)?;
1484                            let extract = json_extract(dialect, qual, &col, &path);
1485                            return Ok(json_valid_guard(
1486                                dialect,
1487                                qual,
1488                                &col,
1489                                format!("{extract} = {lit}"),
1490                            ));
1491                        }
1492                    }
1493                    // The property form, `[/col/a::b = 'lit']`: the
1494                    // same question on a scalar, and on an array of
1495                    // scalars the list, which equals the literal when
1496                    // any item does (an array that holds a container
1497                    // is no property). SQLite says both in one
1498                    // condition, exactly: the scalar branch by the
1499                    // value's type and text, the array branch by
1500                    // `json_each`. The other engines' array branches
1501                    // are not verified live, so there the shape rides
1502                    // the prefilter (PostgreSQL) or the scan.
1503                    for (pi, li) in [(0usize, 1usize), (1, 0)] {
1504                        if self.is_text_literal(kids[li])
1505                            && let Some((col, path)) = self.json_property_path(kids[pi])
1506                        {
1507                            self.note_json_column(kids[pi], &col);
1508                            let raw = self
1509                                .prop(kids[li], "value")
1510                                .map(|v| v.to_string())
1511                                .unwrap_or_default();
1512                            if raw.trim().parse::<f64>().is_ok() {
1513                                return Err(SqlError::Semantics(
1514                                    "a numeric-looking text literal against a \
1515                                     JSON path compares numerically in Quarb \
1516                                     (value coercion); no SQL extractor \
1517                                     matches that"
1518                                        .into(),
1519                                ));
1520                            }
1521                            // `json_each` names its own columns; an
1522                            // unqualified JSON column of one of those
1523                            // names would read as them inside it.
1524                            let shadowed = qual.is_none()
1525                                && JSON_EACH_COLUMNS.contains(&col.to_ascii_lowercase().as_str());
1526                            if dialect != Dialect::Sqlite || shadowed {
1527                                return Err(SqlError::Semantics(
1528                                    "a property of a JSON value may be a list (an \
1529                                     array of scalars equals the literal when any \
1530                                     item does); only SQLite's condition carries \
1531                                     that branch whole; this shape rides the \
1532                                     partial prefilter"
1533                                        .into(),
1534                                ));
1535                            }
1536                            let lit = self.operand(kids[li], qual)?;
1537                            let qcol = match qual {
1538                                Some(q) => format!("{q}.{col}"),
1539                                None => col.clone(),
1540                            };
1541                            let at = format!("'$.{}'", path.join("."));
1542                            let ty = format!("json_type({qcol}, {at})");
1543                            let ex = json_extract(dialect, qual, &col, &path);
1544                            return Ok(json_valid_guard(
1545                                dialect,
1546                                qual,
1547                                &col,
1548                                format!(
1549                                    "(({ty} = 'text' AND {ex} = {lit}) OR ({ty} = 'array' \
1550                                     AND EXISTS (SELECT 1 FROM json_each({qcol}, {at}) \
1551                                     WHERE json_each.type = 'text' AND json_each.value = {lit}) \
1552                                     AND NOT EXISTS (SELECT 1 FROM json_each({qcol}, {at}) \
1553                                     WHERE json_each.type IN ('array', 'object'))))"
1554                                ),
1555                            ));
1556                        }
1557                    }
1558                }
1559                // Any other JSON-path comparison: name the real
1560                // reason, not the generic flat-rows refusal — only
1561                // fixed-path string equality is provably identical
1562                // (Quarb coerces numeric-looking values; no
1563                // engine's extractor matches that). The partial
1564                // prefilter ladder covers these shapes.
1565                if self.dialect.is_some() {
1566                    for side in [kids[0], kids[1]] {
1567                        if self.json_path(side).is_some()
1568                            || self.json_path_loose(side).is_some()
1569                            || self.json_property_path(side).is_some()
1570                        {
1571                            return Err(SqlError::Semantics(
1572                                "a JSON-path comparison pushes whole only as \
1573                                 fixed-path string equality (Quarb coerces \
1574                                 numeric-looking values; no extractor matches \
1575                                 that); this shape rides the partial prefilter"
1576                                    .into(),
1577                            ));
1578                        }
1579                    }
1580                }
1581                // Quarb compares by value; SQL by the column's type.
1582                // A column against a numeric literal is the scan's
1583                // comparison only on a numeric-typed column, and
1584                // against a text literal only on a column that is
1585                // not (MySQL reads `n = 'abc'` as `n = 0`): the
1586                // catalog decides, so both are recorded for the
1587                // caller. A numeric-looking TEXT literal meets
1588                // numbers as a number in Quarb and compares as text
1589                // in SQL, on every engine, so it does not push.
1590                if self.strict {
1591                    for (ci, li) in [(0usize, 1usize), (1, 0)] {
1592                        let Some(column) = self.column_operand(kids[ci]) else {
1593                            continue;
1594                        };
1595                        if self.kind(kids[li]) != "literal" {
1596                            continue;
1597                        }
1598                        let numeric = self
1599                            .prop(kids[li], "value")
1600                            .is_some_and(|v| Value::Str(v.to_string()).numeric_reading().is_some());
1601                        let list = match (self.prop_s(kids[li], "type").as_str(), numeric) {
1602                            ("null", _) => continue,
1603                            ("text", true) => {
1604                                return Err(SqlError::Semantics(
1605                                    "a numeric-looking text literal compares \
1606                                     numerically in Quarb (value coercion); SQL \
1607                                     compares it as text"
1608                                        .into(),
1609                                ));
1610                            }
1611                            ("text", false) => &mut self.column_types.text,
1612                            (_, true) => &mut self.column_types.numeric,
1613                            (_, false) => continue,
1614                        };
1615                        if !list.contains(&column) {
1616                            list.push(column);
1617                        }
1618                    }
1619                }
1620                let l = self.operand(kids[0], qual)?;
1621                // Ruling #33: a pattern literal on `=` pushes as
1622                // LIKE — a `%` per star, each literal segment
1623                // escaped, in order. Anchoring matches exactly:
1624                // LIKE anchors both ends, a flanking star lifts
1625                // one. The case-folding caveat is `*=`'s; `!=`
1626                // with a pattern rides the scan (NOT LIKE drops
1627                // the NULL rows Quarb keeps).
1628                if self.kind(kids[1]) == "pattern" {
1629                    if op.as_str() != "=" {
1630                        return Err(SqlError::Unsupported(format!(
1631                            "a pattern literal on '{op}' (only '=' pushes)"
1632                        )));
1633                    }
1634                    // PostgreSQL's LIKE is case-sensitive under its
1635                    // deterministic collations — provably what the
1636                    // pattern test does — so the strict push is
1637                    // sound there (and a prefix pattern is
1638                    // index-sargable server-side). Everywhere else
1639                    // case folding is ambient state (SQLite pragma,
1640                    // MySQL/MSSQL collation), so strict refuses.
1641                    if self.strict && self.dialect != Some(Dialect::Postgres) {
1642                        return Err(SqlError::Semantics(
1643                            "LIKE case folding differs per engine \
1644                             (provably case-sensitive only on PostgreSQL)"
1645                                .into(),
1646                        ));
1647                    }
1648                    if !self.strict {
1649                        self.notes.push(
1650                            "pattern → LIKE: Quarb's pattern test is \
1651                             case-sensitive; LIKE folds case on \
1652                             SQLite/MySQL but not PostgreSQL"
1653                                .to_string(),
1654                        );
1655                    }
1656                    let mut like = String::new();
1657                    for kid in self.arbor.children(kids[1]) {
1658                        match self.kind(kid).as_str() {
1659                            "star" => like.push('%'),
1660                            _ => {
1661                                let raw = self
1662                                    .prop(kid, "value")
1663                                    .map(|v| v.to_string())
1664                                    .unwrap_or_default();
1665                                like.push_str(
1666                                    &raw.replace('\\', "\\\\")
1667                                        .replace('%', "\\%")
1668                                        .replace('_', "\\_")
1669                                        .replace('\'', "''"),
1670                                );
1671                            }
1672                        }
1673                    }
1674                    return Ok(format!("{l} LIKE '{like}' ESCAPE '\\'"));
1675                }
1676                let r = self.operand(kids[1], qual)?;
1677                Ok(match op.as_str() {
1678                    "=" => format!("{l} = {r}"),
1679                    "!=" => {
1680                        // Quarb keeps rows whose operand is NULL (its
1681                        // `value_eq` is false there, so `!=` is true);
1682                        // SQL's `<>` is UNKNOWN for a NULL operand and
1683                        // drops those rows. Not provably identical
1684                        // without the schema, so pushdown refuses it;
1685                        // the display translation keeps `<>` and notes
1686                        // the divergence.
1687                        if self.strict {
1688                            return Err(SqlError::Semantics(
1689                                "'!=' drops the NULL rows Quarb keeps \
1690                                 (SQL '<>' is UNKNOWN for NULL; use '!= null' \
1691                                 for IS NOT NULL)"
1692                                    .into(),
1693                            ));
1694                        }
1695                        self.notes.push(
1696                            "'!=' → '<>': Quarb keeps rows whose column is NULL; \
1697                             SQL's '<>' drops them (use '!= null' for IS NOT NULL)"
1698                                .to_string(),
1699                        );
1700                        format!("{l} <> {r}")
1701                    }
1702                    "<" | "<=" | ">" | ">=" => format!("{l} {op} {r}"),
1703                    "*=" => {
1704                        // The same PostgreSQL rung as the pattern
1705                        // literal above: LIKE is provably
1706                        // case-sensitive there, so `*=` pushes
1707                        // strict too.
1708                        if self.strict && self.dialect != Some(Dialect::Postgres) {
1709                            return Err(SqlError::Semantics(
1710                                "LIKE case folding differs per engine \
1711                                 (provably case-sensitive only on PostgreSQL)"
1712                                    .into(),
1713                            ));
1714                        }
1715                        // The pattern must be a text literal: a
1716                        // column or computed operand holds a value,
1717                        // not a pattern, and SQL LIKE cannot express
1718                        // "contains that value" portably.
1719                        if !self.is_text_literal(kids[1]) {
1720                            return Err(SqlError::Unsupported(
1721                                "'*=' with a non-literal pattern".into(),
1722                            ));
1723                        }
1724                        self.notes.push(
1725                            "*= → LIKE: Quarb's substring test is case-sensitive; LIKE \
1726                             folds case on SQLite/MySQL but not PostgreSQL"
1727                                .to_string(),
1728                        );
1729                        // Escape LIKE's metacharacters, then quote.
1730                        // The explicit ESCAPE makes '\' the escape
1731                        // everywhere — SQLite, MSSQL, and Oracle
1732                        // have no default escape character.
1733                        let raw = self
1734                            .prop(kids[1], "value")
1735                            .map(|v| v.to_string())
1736                            .unwrap_or_default();
1737                        let pat = raw
1738                            .replace('\\', "\\\\")
1739                            .replace('%', "\\%")
1740                            .replace('_', "\\_")
1741                            .replace('\'', "''");
1742                        format!("{l} LIKE '%{pat}%' ESCAPE '\\'")
1743                    }
1744                    "==" | "!==" | "=~" | "!~" => {
1745                        return Err(SqlError::Semantics(
1746                            "regex matching means a different REGEXP engine per \
1747                             backend; regexes run engine-side"
1748                                .into(),
1749                        ));
1750                    }
1751                    other => {
1752                        return Err(SqlError::Unsupported(format!("the '{other}' comparison")));
1753                    }
1754                })
1755            }
1756            // A bare truthy operand.
1757            _ => {
1758                if self.strict {
1759                    return Err(SqlError::Semantics(
1760                        "truthiness diverges (0 and '' are falsy in Quarb)".into(),
1761                    ));
1762                }
1763                self.notes.push(
1764                    "truthiness: '[::c]' exports as IS NOT NULL, but Quarb also treats \
1765                     0 and '' as falsy"
1766                        .to_string(),
1767                );
1768                Ok(format!("{} IS NOT NULL", self.operand(e, qual)?))
1769            }
1770        }
1771    }
1772
1773    fn operand(&mut self, o: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
1774        match self.kind(o).as_str() {
1775            "literal" => {
1776                let v = self.prop(o, "value").unwrap_or(Value::Null);
1777                match self.prop_s(o, "type").as_str() {
1778                    "text" => {
1779                        let s = v.to_string();
1780                        // No single escaping is portable across the
1781                        // pushdown's target dialects: MySQL (default
1782                        // sql_mode) and BigQuery read `\` as an escape
1783                        // while SQLite/PostgreSQL/DuckDB take it
1784                        // literally, and BigQuery rejects the `''`
1785                        // quote-doubling the others require. A literal
1786                        // carrying either character cannot be pushed as
1787                        // provably identical SQL, so refuse it and let
1788                        // the caller scan. (The display translation
1789                        // keeps its best-effort `''`-doubling.)
1790                        if self.strict && (s.contains('\'') || s.contains('\\')) {
1791                            return Err(SqlError::Semantics(
1792                                "a text literal with a quote or backslash \
1793                                 has no escaping portable across SQL dialects"
1794                                    .into(),
1795                            ));
1796                        }
1797                        Ok(format!("'{}'", s.replace('\'', "''")))
1798                    }
1799                    "null" => Ok("NULL".to_string()),
1800                    _ => Ok(v.to_string()),
1801                }
1802            }
1803            "path" => {
1804                if self.kid(o, "step").is_some() {
1805                    // A step here is either navigation (refused) or
1806                    // a resolution chain (refused with the reason).
1807                    let s = self.kids(o, "step")[0];
1808                    if self.prop_s(s, "axis") == "-->" {
1809                        return Err(SqlError::Unsupported(
1810                            "a '-->' resolution chain: the foreign-key targets live in \
1811                             the schema, not the query — spell the join with '<=>' to \
1812                             export it"
1813                                .into(),
1814                        ));
1815                    }
1816                    return Err(SqlError::Unsupported(
1817                        "navigation inside a predicate (SQL rows are flat)".into(),
1818                    ));
1819                }
1820                let p = self
1821                    .kid(o, "projection")
1822                    .ok_or_else(|| SqlError::Unsupported("an empty path operand".into()))?;
1823                let col = self.projection_col(p)?;
1824                let col = self.sql_ident(&col, "column name")?;
1825                // a read through a mark: the row the mark names
1826                let (named, any) = self.mark_anchor(o);
1827                if any {
1828                    let Some(name) = named else {
1829                        return Err(SqlError::Unsupported(
1830                            "a mark read by position, or of several marks".into(),
1831                        ));
1832                    };
1833                    if self.join_marks.contains(&name) {
1834                        // `$$.al::col`: the witness, as `$$1::col`
1835                        if self.in_on {
1836                            return Err(SqlError::Unsupported(
1837                                "the joined expression's own mark inside its ON".into(),
1838                            ));
1839                        }
1840                        let Some(join) = self.join_sql.clone() else {
1841                            return Err(SqlError::Unsupported(
1842                                "a join mark outside a correlation join".into(),
1843                            ));
1844                        };
1845                        return Ok(format!("{join}.{col}"));
1846                    }
1847                    if self.from_marks.contains(&name) {
1848                        // `$$.t::col`: the driver's row, as `$$0::col`
1849                        // in the ON
1850                        return Ok(if self.join_sql.is_some() {
1851                            format!("{}.{col}", self.from_sql.clone())
1852                        } else {
1853                            match qual {
1854                                Some(q) => format!("{q}.{col}"),
1855                                None => col,
1856                            }
1857                        });
1858                    }
1859                    return Err(SqlError::Unsupported(format!(
1860                        "a read of the mark '.{name}' (not a row of the query's tables)"
1861                    )));
1862                }
1863                Ok(match qual {
1864                    Some(q) => format!("{q}.{col}"),
1865                    None => col,
1866                })
1867            }
1868            "context" => {
1869                // `$$1::col` — the joined expression's witness, in
1870                // pipeline position: the joined table's column.
1871                // Anything else is outside the verified-safe set.
1872                let p = self
1873                    .kid(o, "projection")
1874                    .ok_or_else(|| SqlError::Unsupported("a bare '$$N' reference".into()))?;
1875                let col = self.projection_col(p)?;
1876                let col = self.sql_ident(&col, "column name")?;
1877                if matches!(self.prop(o, "index"), Some(Value::Int(0))) {
1878                    // `$$0::col` — the driver, legal only inside the
1879                    // joined expression's ON bracket (the parser
1880                    // refuses it anywhere else).
1881                    if !self.in_on {
1882                        return Err(SqlError::Unsupported(
1883                            "a '$$0' reference outside the join's ON".into(),
1884                        ));
1885                    }
1886                    if self.kid(o, "step").is_some() {
1887                        return Err(SqlError::Unsupported(
1888                            "a '$$0' reference beyond a plain column ($$0::col)".into(),
1889                        ));
1890                    }
1891                    return Ok(format!("{}.{col}", self.from_sql.clone()));
1892                }
1893                if self.in_on {
1894                    return Err(SqlError::Unsupported(
1895                        "a '$$N' reference inside the ON (the driver is '$$0')".into(),
1896                    ));
1897                }
1898                let Some(join) = self.join_sql.clone() else {
1899                    return Err(SqlError::Unsupported(
1900                        "a '$$N' reference outside a correlation join".into(),
1901                    ));
1902                };
1903                match self.prop(o, "index") {
1904                    Some(Value::Int(1)) => Ok(format!("{join}.{col}")),
1905                    None => Err(SqlError::Unsupported("a bare '$$N' reference".into())),
1906                    Some(v) => Err(SqlError::Unsupported(format!(
1907                        "pushdown: $${v} beyond a two-branch correlation"
1908                    ))),
1909                }
1910            }
1911            "invoking" => {
1912                // `$$__::col` — the invoking position of an enclosing
1913                // query body, never the driver (that is `$$0`, a
1914                // context of index 0, since draft 153). SQL has no
1915                // enclosing body to read.
1916                Err(SqlError::Unsupported(
1917                    "a '$$__' reference (the driver inside the ON is '$$0')".into(),
1918                ))
1919            }
1920            "arith" => {
1921                let op = self.prop_s(o, "op");
1922                let kids = self.arbor.children(o);
1923                let l = self.operand(kids[0], qual)?;
1924                let r = self.operand(kids[1], qual)?;
1925                Ok(match op.as_str() {
1926                    "+" | "-" | "*" => format!("({l} {op} {r})"),
1927                    "div" => format!("({l} / {r})"),
1928                    "mod" => format!("({l} % {r})"),
1929                    other => return Err(SqlError::Unsupported(format!("'{other}' arithmetic"))),
1930                })
1931            }
1932            other => Err(SqlError::Unsupported(format!(
1933                "the '{other}' operand (registers, topics, and captures are Quarb-side state)"
1934            ))),
1935        }
1936    }
1937
1938    fn projection_col(&mut self, p: NodeId) -> Result<String, SqlError> {
1939        match self.prop_s(p, "kind").as_str() {
1940            "property" => match self.prop(p, "key") {
1941                Some(k) => Ok(k.to_string()),
1942                None => Err(SqlError::Unsupported(
1943                    "the bare '::' projection (name the column)".into(),
1944                )),
1945            },
1946            other => Err(SqlError::Unsupported(format!(
1947                "the {other} metadata projection"
1948            ))),
1949        }
1950    }
1951
1952    /// The column a plain column operand names (`::col`, `$*1::col`):
1953    /// a path without a step, or a context reference, projecting a
1954    /// named property.
1955    /// A numeric reducer (`sum`, `mean`, `min`, `max`) pushed over a
1956    /// column is the scan's only on a numeric-typed column: the
1957    /// scan lifts unit text (`'18 parasang'` under a declared unit)
1958    /// to quantities and orders texts as texts, where SQL sums a
1959    /// text column by its leading digits and orders it by
1960    /// collation. Recorded for the caller's catalog as a
1961    /// comparison's assumption is.
1962    fn assume_numeric(&mut self, column: Option<String>) {
1963        if !self.strict {
1964            return;
1965        }
1966        if let Some(c) = column
1967            && !self.column_types.numeric.contains(&c)
1968        {
1969            self.column_types.numeric.push(c);
1970        }
1971    }
1972
1973    fn column_operand(&self, o: NodeId) -> Option<String> {
1974        match self.kind(o).as_str() {
1975            "path" if self.kid(o, "step").is_none() => {}
1976            "context" => {}
1977            _ => return None,
1978        }
1979        let p = self.kid(o, "projection")?;
1980        if self.prop_s(p, "kind") != "property" {
1981            return None;
1982        }
1983        self.prop(p, "key").map(|k| k.to_string())
1984    }
1985
1986    /// Whether `o` is a text string literal (`'London'`).
1987    fn is_text_literal(&self, o: NodeId) -> bool {
1988        self.kind(o) == "literal" && self.prop_s(o, "type") == "text"
1989    }
1990
1991    /// If `o` is the one JSON-column-path shape pushdown handles —
1992    /// `/col/seg/seg…::`, a plain-navigation path (every step `/`
1993    /// and a plain-identifier object key, no wildcards, no nested
1994    /// predicate), at least one segment past the column, ending in
1995    /// the bare `::` projection — return `(column, [json segments])`.
1996    /// Anything else is `None` (and falls back to the graft).
1997    fn json_path(&self, o: NodeId) -> Option<(String, Vec<String>)> {
1998        if self.kind(o) != "path" {
1999            return None;
2000        }
2001        let steps = self.kids(o, "step");
2002        if steps.len() < 2 {
2003            return None;
2004        }
2005        // The projection must be the bare `::` (default value of
2006        // the JSON leaf), not `::key` or a `:::`/`:` metadata form.
2007        let proj = self.kid(o, "projection")?;
2008        if self.prop_s(proj, "kind") != "property" || self.prop(proj, "key").is_some() {
2009            return None;
2010        }
2011        let mut names = Vec::with_capacity(steps.len());
2012        for s in &steps {
2013            if self.prop_s(*s, "axis") != "/"
2014                || self.prop_s(*s, "matcher-kind") != "name"
2015                || self.kid(*s, "predicate").is_some()
2016            {
2017                return None;
2018            }
2019            let name = self.prop_s(*s, "matcher");
2020            // Plain object keys only — no array indices, no
2021            // characters that would need per-dialect path escaping.
2022            let plain = !name.is_empty()
2023                && name
2024                    .chars()
2025                    .next()
2026                    .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
2027                && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_');
2028            if !plain {
2029                return None;
2030            }
2031            names.push(name);
2032        }
2033        let col = names.remove(0);
2034        Some((col, names))
2035    }
2036
2037    /// The property form of a JSON-column path, `/col/seg…::key`:
2038    /// plain-navigation steps as in [`json_path`], at least the
2039    /// column's own, ending in the projection of a plain-identifier
2040    /// property. It asks for the same value as `/col/seg…/key::`
2041    /// where that is a scalar; on an array of scalars it is the
2042    /// list (see [`json_property_prefilter`]). Returns
2043    /// `(column, [json segments, the key last])`.
2044    ///
2045    /// [`json_path`]: Self::json_path
2046    /// [`json_property_prefilter`]: Self::json_property_prefilter
2047    fn json_property_path(&self, o: NodeId) -> Option<(String, Vec<String>)> {
2048        if self.kind(o) != "path" {
2049            return None;
2050        }
2051        let steps = self.kids(o, "step");
2052        if steps.is_empty() {
2053            return None;
2054        }
2055        let proj = self.kid(o, "projection")?;
2056        if self.prop_s(proj, "kind") != "property" {
2057            return None;
2058        }
2059        let key = self.prop(proj, "key")?.to_string();
2060        let plain = |name: &str| {
2061            !name.is_empty()
2062                && name
2063                    .chars()
2064                    .next()
2065                    .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
2066                && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_')
2067        };
2068        if !plain(&key) {
2069            return None;
2070        }
2071        let mut names = Vec::with_capacity(steps.len() + 1);
2072        for s in &steps {
2073            if self.prop_s(*s, "axis") != "/"
2074                || self.prop_s(*s, "matcher-kind") != "name"
2075                || self.kid(*s, "predicate").is_some()
2076            {
2077                return None;
2078            }
2079            let name = self.prop_s(*s, "matcher");
2080            if !plain(&name) {
2081                return None;
2082            }
2083            names.push(name);
2084        }
2085        names.push(key);
2086        let col = names.remove(0);
2087        Some((col, names))
2088    }
2089
2090    /// A safe `AS` alias: bare when it is a plain identifier and
2091    /// not an SQL keyword; otherwise strict mode refuses (quoting
2092    /// dialects disagree) and export mode double-quotes, noting it.
2093    fn alias(&mut self, name: &str) -> Result<String, SqlError> {
2094        if is_plain_ident(name) {
2095            return Ok(name.to_string());
2096        }
2097        if self.strict {
2098            return Err(SqlError::Unsupported(format!(
2099                "pushdown: field name {name:?} needs SQL quoting \
2100                 (dialects disagree); rename the field"
2101            )));
2102        }
2103        self.notes
2104            .push(format!("field name {name:?} double-quoted (ANSI)"));
2105        Ok(format!("\"{}\"", name.replace('"', "\"\"")))
2106    }
2107
2108    /// A table or column name rendered into SQL. Bare when it is a
2109    /// plain identifier; otherwise strict mode refuses — a name
2110    /// like `a OR b` would silently rewrite the emitted SQL's
2111    /// meaning, breaking the provably-identical guarantee (and a
2112    /// portable quoting does not exist) — and export mode
2113    /// double-quotes with a note.
2114    fn sql_ident(&mut self, name: &str, what: &str) -> Result<String, SqlError> {
2115        if is_plain_ident(name) {
2116            return Ok(name.to_string());
2117        }
2118        if self.strict {
2119            return Err(SqlError::Unsupported(format!(
2120                "pushdown: {what} {name:?} is not a plain SQL identifier"
2121            )));
2122        }
2123        self.notes
2124            .push(format!("{what} {name:?} double-quoted (ANSI)"));
2125        Ok(format!("\"{}\"", name.replace('"', "\"\"")))
2126    }
2127
2128    /// Split a joined-side predicate: `col = $*1::col2` equalities
2129    /// become the ON condition; everything else the WHERE.
2130    /// `ltable` and `rtable` are the tables' SQL renderings.
2131    fn split_join_pred(
2132        &mut self,
2133        p: NodeId,
2134        rtable: &str,
2135        on: &mut Vec<String>,
2136        wheres: &mut Vec<String>,
2137    ) -> Result<(), SqlError> {
2138        if self.prop_s(p, "kind") != "expr" {
2139            return Err(SqlError::Unsupported(
2140                "a positional predicate on the joined side".into(),
2141            ));
2142        }
2143        // Flatten top-level ANDs; each conjunct routes to ON or
2144        // WHERE.
2145        fn conjuncts(ex: &Exporter, e: NodeId, out: &mut Vec<NodeId>) {
2146            if ex.kind(e) == "and" {
2147                for c in ex.arbor.children(e) {
2148                    conjuncts(ex, c, out);
2149                }
2150            } else {
2151                out.push(e);
2152            }
2153        }
2154        let mut parts = Vec::new();
2155        for c in self.arbor.children(p) {
2156            conjuncts(self, c, &mut parts);
2157        }
2158        for e in parts {
2159            let uses_driver = self.subtree_has_driver(e);
2160            self.in_on = true;
2161            let cond = self.pred_expr(e, Some(rtable));
2162            self.in_on = false;
2163            let cond = cond?;
2164            if uses_driver && self.kind(e) == "compare" && self.prop_s(e, "op") == "=" {
2165                // Record which joined-table columns the ON binds —
2166                // the uniqueness obligation (see
2167                // Pushdown::join_left). Collected from the arbor's
2168                // bare-column operand nodes, never from the
2169                // rendered SQL text, so neither a literal nor an
2170                // unusual column name can corrupt the obligation.
2171                self.collect_join_cols(e)?;
2172                on.push(cond);
2173            } else {
2174                wheres.push(cond);
2175            }
2176        }
2177        Ok(())
2178    }
2179
2180    /// The joined-table columns the ON equality binds: every bare
2181    /// column operand (`::col`) in `e`'s subtree, appended to the
2182    /// join obligation with their raw (catalog) names.
2183    fn collect_join_cols(&mut self, e: NodeId) -> Result<(), SqlError> {
2184        // The `$$0` side is the driver — not part of the joined
2185        // table's obligation.
2186        if self.is_driver(e) {
2187            return Ok(());
2188        }
2189        if self.kind(e) == "path"
2190            && self.kid(e, "step").is_none()
2191            && let Some(p) = self.kid(e, "projection")
2192        {
2193            let col = self.projection_col(p)?;
2194            self.join_on_cols.push(col);
2195        }
2196        for c in self.arbor.children(e) {
2197            self.collect_join_cols(c)?;
2198        }
2199        Ok(())
2200    }
2201
2202    /// `$$0`: a context operand of index 0 (the driver), or a read
2203    /// of the driver's row through its mark (`$$.t::col`).
2204    fn is_driver(&self, n: NodeId) -> bool {
2205        (self.kind(n) == "context" && matches!(self.prop(n, "index"), Some(Value::Int(0))))
2206            || (self.kind(n) == "path"
2207                && matches!(self.mark_anchor(n).0, Some(name) if self.from_marks.contains(&name)))
2208    }
2209
2210    fn subtree_has_driver(&self, n: NodeId) -> bool {
2211        self.is_driver(n)
2212            || self
2213                .arbor
2214                .children(n)
2215                .into_iter()
2216                .any(|c| self.subtree_has_driver(c))
2217    }
2218
2219    /// Consume the pipeline into SELECT clauses.
2220    fn pipeline(
2221        &mut self,
2222        q: NodeId,
2223        sel: &mut Select,
2224        join: Option<(&str, &str)>,
2225    ) -> Result<(), SqlError> {
2226        let Some(pipe) = self.kid(q, "pipeline") else {
2227            return Ok(());
2228        };
2229        let commands: Vec<NodeId> = self.arbor.children(pipe);
2230        let mut i = 0;
2231        // A pending per-row value (`| ::col`) feeding an aggregate,
2232        // and the column it names where it is one.
2233        let mut pending_col: Option<String> = None;
2234        let mut pending_name: Option<String> = None;
2235        // After a grouped reduction: (key, agg_sql, alias).
2236        let mut grouped: Option<(String, String, String)> = None;
2237        // A pushed key awaiting its fold: (the regula's name, key_sql).
2238        let mut pushed_key: Option<(String, String)> = None;
2239
2240        while i < commands.len() {
2241            let s = commands[i];
2242            match self.kind(s).as_str() {
2243                "expr" => {
2244                    let kids = self.arbor.children(s);
2245                    pending_col = Some(self.operand(kids[0], join.map(|(l, _)| l))?);
2246                    pending_name = self.column_operand(kids[0]);
2247                }
2248                "cmd" => {
2249                    let name = self.prop_s(s, "name");
2250                    match name.as_str() {
2251                        "rec" | "record" => {
2252                            sel.select = self.record_fields(s, join)?;
2253                        }
2254                        // A reducing aggregate on the plain pipe:
2255                        // the grouped reduction.
2256                        "count" | "sum" | "mean" | "min" | "max" => {
2257                            let (key, _, _) = grouped.as_ref().ok_or_else(|| {
2258                                SqlError::Unsupported(format!(
2259                                    "'| {name}' outside a group (use '@| {name}')"
2260                                ))
2261                            })?;
2262                            // Quarb's count counts every member,
2263                            // nulls included: COUNT(*), never the
2264                            // NULL-skipping COUNT(col).
2265                            let col = if name == "count" {
2266                                pending_col = None;
2267                                None
2268                            } else {
2269                                self.assume_numeric(pending_name.take());
2270                                pending_col.take()
2271                            };
2272                            let agg = sql_agg(&name, col)?;
2273                            grouped = Some((key.clone(), agg.clone(), agg));
2274                        }
2275                        other => {
2276                            // sort/unique/top and kin are spellable
2277                            // in SQL but with the backend's own
2278                            // collation and duplicate semantics —
2279                            // name the divergence, not a missing
2280                            // feature.
2281                            if matches!(other, "sort" | "sort_by" | "unique" | "top" | "bottom") {
2282                                return Err(SqlError::Semantics(format!(
2283                                    "'{other}' means the backend's collation and \
2284                                     duplicate semantics; one engine-side ordering \
2285                                     keeps it identical everywhere"
2286                                )));
2287                            }
2288                            return Err(SqlError::Unsupported(format!(
2289                                "the '{other}' pipeline command"
2290                            )));
2291                        }
2292                    }
2293                }
2294                "push" => {
2295                    // An alias for the grouped aggregate.
2296                    if let Some((k, agg, _)) = grouped.take() {
2297                        let alias = self.prop_s(s, "name");
2298                        grouped = Some((k, agg, alias));
2299                    } else {
2300                        return Err(SqlError::Unsupported(
2301                            "a register push (Quarb-side state)".into(),
2302                        ));
2303                    }
2304                }
2305                // `| .k(::k)` before a fold by `.k`: the GROUP BY key
2306                // (a projection body is a subcontext of one branch).
2307                "expr-push" if grouped.is_none() && pushed_key.is_none() => {
2308                    let kids = self.arbor.children(s);
2309                    let key = self.operand(kids[0], join.map(|(l, _)| l))?;
2310                    pushed_key = Some((self.prop_s(s, "name"), key));
2311                }
2312                "subcontext" if grouped.is_none() && pushed_key.is_none() => {
2313                    let key = self.pushed_column(s, join.map(|(l, _)| l))?;
2314                    pushed_key = Some((self.prop_s(s, "name"), key));
2315                }
2316                "filter" => {
2317                    if grouped.is_some() {
2318                        // HAVING: `$_` / the alias refer to the
2319                        // aggregate.
2320                        let cond = self.having_cond(s)?;
2321                        sel.having = Some(cond);
2322                    } else {
2323                        return Err(SqlError::Unsupported(
2324                            "a mid-pipeline filter (put it in the row predicate)".into(),
2325                        ));
2326                    }
2327                }
2328                "recall" => {
2329                    // `| %.` finalizes the grouped record.
2330                    if self.prop_s(s, "ref") != "%." {
2331                        return Err(SqlError::Unsupported(
2332                            "a register recall (Quarb-side state)".into(),
2333                        ));
2334                    }
2335                    let (key, agg, alias) = grouped.clone().ok_or_else(|| {
2336                        SqlError::Unsupported("'%.', with nothing grouped".into())
2337                    })?;
2338                    let alias = self.alias(&alias)?;
2339                    sel.select = vec![key.clone(), format!("{agg} AS {alias}")];
2340                    // The HAVING condition compared the aggregate
2341                    // through $_ — substitute the real expression.
2342                    if let Some(h) = sel.having.take() {
2343                        sel.having = Some(h.replace("__AGG__", &agg));
2344                    }
2345                }
2346                "agg" if self.prop(s, "group").is_some() => {
2347                    // `@|{.k} agg`: the fold by the pushed key is the
2348                    // grouped reduction, GROUP BY key.
2349                    let name = self.prop_s(s, "name");
2350                    if !matches!(name.as_str(), "count" | "sum" | "mean" | "min" | "max") {
2351                        return Err(SqlError::Unsupported(format!(
2352                            "the '{name}' command per group (GROUP BY reduces)"
2353                        )));
2354                    }
2355                    let selector = self.prop_s(s, "group");
2356                    if selector.contains(';') {
2357                        return Err(SqlError::Unsupported(
2358                            "a fold by several regulae (one GROUP BY key translates)".into(),
2359                        ));
2360                    }
2361                    let (reg, key) = pushed_key.take().ok_or_else(|| {
2362                        SqlError::Unsupported("a fold by a regula no push of a key made".into())
2363                    })?;
2364                    if selector != "." && selector != format!(".{reg}") {
2365                        return Err(SqlError::Unsupported(format!(
2366                            "a fold by '{selector}' (the pushed key is '.{reg}')"
2367                        )));
2368                    }
2369                    if self.strict {
2370                        return Err(SqlError::Semantics(
2371                            "GROUP BY result order is unordered in SQL".into(),
2372                        ));
2373                    }
2374                    sel.group_by = Some(key.clone());
2375                    let col = if name == "count" {
2376                        pending_col = None;
2377                        None
2378                    } else {
2379                        self.assume_numeric(pending_name.take());
2380                        pending_col.take()
2381                    };
2382                    let agg = sql_agg(&name, col)?;
2383                    grouped = Some((key, agg.clone(), agg));
2384                }
2385                "agg" => {
2386                    let name = self.prop_s(s, "name");
2387                    match name.as_str() {
2388                        "count" | "sum" | "mean" | "min" | "max" => {
2389                            // Quarb's count counts every row, nulls
2390                            // included: COUNT(*) regardless of a
2391                            // pending column.
2392                            let col = if name == "count" {
2393                                pending_col = None;
2394                                None
2395                            } else {
2396                                self.assume_numeric(pending_name.take());
2397                                pending_col.take()
2398                            };
2399                            sel.select = vec![sql_agg(&name, col)?];
2400                            self.aggregate = true;
2401                        }
2402                        "sort_by" => {
2403                            if self.strict {
2404                                return Err(SqlError::Semantics(
2405                                    "ORDER BY collations differ per engine".into(),
2406                                ));
2407                            }
2408                            // A sort after a positional selection
2409                            // (or a second sort) cannot render: the
2410                            // fixed SELECT shape orders before
2411                            // LIMIT, and has one ORDER BY.
2412                            if sel.limit.is_some() {
2413                                return Err(SqlError::Unsupported(
2414                                    "a sort after a positional selection (SQL orders \
2415                                     before LIMIT)"
2416                                        .into(),
2417                                ));
2418                            }
2419                            if sel.order_by.is_some() {
2420                                return Err(SqlError::Unsupported(
2421                                    "a second sort (SQL has a single ORDER BY)".into(),
2422                                ));
2423                            }
2424                            let kids = self.arbor.children(s);
2425                            // The options are the command's own:
2426                            // `--desc` is the direction, a named
2427                            // collation (`--locale=tag`, ruling
2428                            // 2026-11-11) is the engine's order and no
2429                            // backend's; a second key has no place in
2430                            // the one ORDER BY this shape holds.
2431                            let mut desc = false;
2432                            for &k in &kids[1..] {
2433                                if self.kind(k) != "option" {
2434                                    return Err(SqlError::Unsupported(
2435                                        "a sort by several keys".into(),
2436                                    ));
2437                                }
2438                                match self.prop_s(k, "name").as_str() {
2439                                    "--desc" | "-d" => desc = true,
2440                                    "--locale" => {
2441                                        return Err(SqlError::Semantics(
2442                                            "sort_by(--locale=…) names the engine's collation; \
2443                                             a backend's differs"
2444                                                .into(),
2445                                        ));
2446                                    }
2447                                    // a case order is that
2448                                    // collation's (ruling 2026-11-12)
2449                                    "--upper-first" | "-u" | "--lower-first" | "-l"
2450                                    | "--ignore-case" | "-i" => {
2451                                        return Err(SqlError::Semantics(
2452                                            "sort_by's case order is the engine's collation's; \
2453                                             a backend's differs"
2454                                                .into(),
2455                                        ));
2456                                    }
2457                                    other => {
2458                                        return Err(SqlError::Unsupported(format!(
2459                                            "the '{other}' option of sort_by"
2460                                        )));
2461                                    }
2462                                }
2463                            }
2464                            let e = self.operand(kids[0], join.map(|(l, _)| l))?;
2465                            sel.order_by = Some((e, desc));
2466                        }
2467                        "reverse" => match &mut sel.order_by {
2468                            Some((_, desc)) => *desc = true,
2469                            None => {
2470                                return Err(SqlError::Unsupported(
2471                                    "reverse without an ORDER BY (rows are unordered)".into(),
2472                                ));
2473                            }
2474                        },
2475                        "top" => {
2476                            if self.strict {
2477                                return Err(SqlError::Semantics(
2478                                    "ORDER BY collations differ per engine".into(),
2479                                ));
2480                            }
2481                            if sel.limit.is_some() {
2482                                return Err(SqlError::Unsupported(
2483                                    "'top' after a positional selection (SQL orders \
2484                                     before LIMIT)"
2485                                        .into(),
2486                                ));
2487                            }
2488                            if sel.order_by.is_some() {
2489                                return Err(SqlError::Unsupported(
2490                                    "a second sort (SQL has a single ORDER BY)".into(),
2491                                ));
2492                            }
2493                            let kids = self.arbor.children(s);
2494                            // the options name the engine's collation
2495                            // and its case order: no backend's (they
2496                            // were dropped, and the SQL ordered as the
2497                            // backend pleased)
2498                            if let Some(&k) = kids
2499                                .get(2..)
2500                                .and_then(|rest| rest.iter().find(|&&k| self.kind(k) == "option"))
2501                            {
2502                                return Err(SqlError::Semantics(format!(
2503                                    "top({}) names the engine's collation; a backend's differs",
2504                                    self.prop_s(k, "name")
2505                                )));
2506                            }
2507                            let n = self.prop_s(kids[0], "value");
2508                            let e = self.operand(kids[1], join.map(|(l, _)| l))?;
2509                            sel.order_by = Some((e, true));
2510                            sel.limit = Some(n);
2511                        }
2512                        "unique" => {
2513                            if self.strict {
2514                                return Err(SqlError::Unsupported(
2515                                    "pushdown: DISTINCT result order is unordered in SQL".into(),
2516                                ));
2517                            }
2518                            // Quarb dedups the limited rows; SQL
2519                            // applies DISTINCT before LIMIT.
2520                            if sel.limit.is_some() {
2521                                return Err(SqlError::Unsupported(
2522                                    "'unique' after a positional selection (SQL applies \
2523                                     DISTINCT before LIMIT)"
2524                                        .into(),
2525                                ));
2526                            }
2527                            if let Some(c) = pending_col.take() {
2528                                sel.select = vec![c];
2529                            }
2530                            sel.distinct = true;
2531                        }
2532                        other => {
2533                            return Err(SqlError::Unsupported(format!("the '{other}' aggregate")));
2534                        }
2535                    }
2536                }
2537                "select" => {
2538                    if self.strict {
2539                        return Err(SqlError::Unsupported(
2540                            "pushdown: LIMIT without a guaranteed order".into(),
2541                        ));
2542                    }
2543                    // `@| [..n]` → LIMIT.
2544                    if sel.limit.is_some() {
2545                        return Err(SqlError::Unsupported(
2546                            "a second positional selection".into(),
2547                        ));
2548                    }
2549                    let p = self.arbor.children(s)[0];
2550                    match (self.prop_s(p, "kind").as_str(), self.prop(p, "to")) {
2551                        ("range", Some(Value::Int(n)))
2552                            if self.prop(p, "from").is_none() && n > 0 =>
2553                        {
2554                            sel.limit = Some(n.to_string());
2555                        }
2556                        _ => {
2557                            return Err(SqlError::Unsupported(
2558                                "positional selection beyond '@| [..n]'".into(),
2559                            ));
2560                        }
2561                    }
2562                }
2563                other => {
2564                    return Err(SqlError::Unsupported(format!(
2565                        "the '{other}' command (windows, subcontexts, and registers are \
2566                         Quarb-side state)"
2567                    )));
2568                }
2569            }
2570            i += 1;
2571        }
2572        // A pending column with no aggregate is a one-column select.
2573        if let Some(c) = pending_col
2574            && sel.select.is_empty()
2575        {
2576            sel.select = vec![c];
2577        }
2578        // A grouped reduction never recorded (`@|{.k} count` with no
2579        // `| .n | %.`) answers one value per group: the aggregate
2580        // alone, grouped by the key.
2581        if let Some((_, agg, _)) = grouped
2582            && sel.select.is_empty()
2583        {
2584            sel.select = vec![agg];
2585        }
2586        Ok(())
2587    }
2588
2589    /// The column a pushed projection body names — `.k(::k)` is a
2590    /// subcontext whose one branch is the bare projection; any
2591    /// other body is Quarb-side state.
2592    fn pushed_column(&mut self, s: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
2593        let body = || {
2594            let q = self.kid(s, "query")?;
2595            let b = self.kid(q, "branch")?;
2596            let p = self.kid(b, "projection")?;
2597            let bare = self.kids(q, "branch").len() == 1
2598                && self.kids(q, "pipeline").is_empty()
2599                && self.arbor.children(b).len() == 1
2600                && self.prop_s(p, "kind") == "property";
2601            bare.then_some(p)
2602        };
2603        let p = body()
2604            .ok_or_else(|| SqlError::Unsupported("a pushed body (Quarb-side state)".into()))?;
2605        let col = self.projection_col(p)?;
2606        let col = self.sql_ident(&col, "column name")?;
2607        Ok(match qual {
2608            Some(q) => format!("{q}.{col}"),
2609            None => col,
2610        })
2611    }
2612
2613    /// A HAVING filter: `$_` and `$.name` refer to the aggregate.
2614    fn having_cond(&mut self, s: NodeId) -> Result<String, SqlError> {
2615        let kids = self.arbor.children(s);
2616        if kids.len() != 1 || self.kind(kids[0]) != "compare" {
2617            return Err(SqlError::Unsupported(
2618                "HAVING translates for a single comparison".into(),
2619            ));
2620        }
2621        let e = kids[0];
2622        let op = self.prop_s(e, "op");
2623        let cmp_kids = self.arbor.children(e);
2624        let l = match self.kind(cmp_kids[0]).as_str() {
2625            "topic" | "recall" => "__AGG__".to_string(),
2626            _ => {
2627                return Err(SqlError::Unsupported(
2628                    "HAVING compares the aggregate ($_ or its register)".into(),
2629                ));
2630            }
2631        };
2632        let r = self.operand(cmp_kids[1], None)?;
2633        Ok(format!("{l} {op} {r}"))
2634    }
2635
2636    /// `rec(...)` fields as a select list.
2637    fn record_fields(
2638        &mut self,
2639        s: NodeId,
2640        join: Option<(&str, &str)>,
2641    ) -> Result<Vec<String>, SqlError> {
2642        let kids = self.arbor.children(s);
2643        let mut fields = Vec::new();
2644        let mut i = 0;
2645        while i < kids.len() {
2646            let k = kids[i];
2647            if self.kind(k) == "literal" {
2648                let name = self.prop_s(k, "value");
2649                let value = self.operand(kids[i + 1], join.map(|(l, _)| l))?;
2650                let name = self.alias(&name)?;
2651                fields.push(format!("{value} AS {name}"));
2652                i += 2;
2653            } else {
2654                let value = self.operand(k, join.map(|(l, _)| l))?;
2655                fields.push(value);
2656                i += 1;
2657            }
2658        }
2659        Ok(fields)
2660    }
2661}
2662
2663fn sql_agg(name: &str, col: Option<String>) -> Result<String, SqlError> {
2664    let f = match name {
2665        "count" => "COUNT",
2666        "sum" => "SUM",
2667        "mean" => "AVG",
2668        "min" => "MIN",
2669        "max" => "MAX",
2670        _ => unreachable!("checked by caller"),
2671    };
2672    match col {
2673        Some(c) => Ok(format!("{f}({c})")),
2674        // Only COUNT aggregates bare rows; SUM(*) and friends are
2675        // not SQL.
2676        None if f == "COUNT" => Ok("COUNT(*)".to_string()),
2677        None => Err(SqlError::Unsupported(format!(
2678            "'{name}' over row nodes (project a column first: '| ::col @| {name}')"
2679        ))),
2680    }
2681}
2682
2683#[cfg(test)]
2684mod null_and_literal_tests {
2685    use super::{Dialect, export, partial_pushdown, pushdown};
2686
2687    // Grouped pipeline that never pushes, so `partial_pushdown` hinges
2688    // only on the leading predicate (mirrors the crate's partial gate).
2689    const GROUPED: &str = " | ::x | .g(::x) @|{.g} count | .n | %.";
2690
2691    #[test]
2692    fn pattern_literal_pushes_as_like() {
2693        // Ruling #33: segments in order, a `%` per star, LIKE's
2694        // metacharacters escaped. Strict push only where LIKE is
2695        // provably case-sensitive (PostgreSQL); everywhere else the
2696        // pattern rides the scan.
2697        let q = "/users/*[::name == 'app-'*]::id";
2698        let p = pushdown(q, Some(Dialect::Postgres)).unwrap();
2699        assert_eq!(
2700            p.sql,
2701            "SELECT id FROM users WHERE name LIKE 'app-%' ESCAPE '\\'"
2702        );
2703        assert!(pushdown(q, Some(Dialect::Sqlite)).is_none());
2704        assert!(pushdown(q, Some(Dialect::MySql)).is_none());
2705        // contains and multi-segment shapes
2706        let p = pushdown("/users/*[::name == *'web'*]::id", Some(Dialect::Postgres)).unwrap();
2707        assert!(p.sql.contains("LIKE '%web%'"), "{}", p.sql);
2708        let p = pushdown(
2709            "/users/*[::name == *'a'*'.gz']::id",
2710            Some(Dialect::Postgres),
2711        )
2712        .unwrap();
2713        assert!(p.sql.contains("LIKE '%a%.gz'"), "{}", p.sql);
2714        // LIKE metacharacters in a segment are escaped
2715        let p = pushdown("/users/*[::name == *'50%_x'*]::id", Some(Dialect::Postgres)).unwrap();
2716        assert!(p.sql.contains("LIKE '%50\\%\\_x%'"), "{}", p.sql);
2717        // `!=` with a pattern rides the scan
2718        assert!(pushdown("/users/*[::name !== *'web'*]::id", Some(Dialect::Postgres)).is_none());
2719        // the `*=` alias gains the same PostgreSQL rung
2720        let p = pushdown("/users/*[::name *= 'web']::id", Some(Dialect::Postgres)).unwrap();
2721        assert!(p.sql.contains("LIKE '%web%'"), "{}", p.sql);
2722        assert!(pushdown("/users/*[::name *= 'web']::id", Some(Dialect::Sqlite)).is_none());
2723    }
2724
2725    #[test]
2726    fn json_column_path_pushdown_per_dialect() {
2727        // `[/col/a/b:: = 'lit']` navigates into a JSON column; each
2728        // dialect extracts the fixed path to text and compares.
2729        // Verified live against all five engines to match the graft.
2730        let q = "/orders/*[/data/meta/tier:: = 'gold']::id";
2731        let sql = |d| pushdown(q, Some(d)).unwrap().sql;
2732        assert_eq!(
2733            sql(Dialect::Postgres),
2734            "SELECT id FROM orders WHERE ((data::jsonb #>> '{meta,tier}') = 'gold')"
2735        );
2736        assert_eq!(
2737            sql(Dialect::MySql),
2738            "SELECT id FROM orders WHERE (JSON_VALID(data) AND \
2739             JSON_UNQUOTE(JSON_EXTRACT(data, '$.meta.tier')) = 'gold')"
2740        );
2741        assert_eq!(
2742            sql(Dialect::Sqlite),
2743            "SELECT id FROM orders WHERE (json_valid(data) AND \
2744             json_extract(data, '$.meta.tier') = 'gold')"
2745        );
2746        assert_eq!(
2747            sql(Dialect::Mssql),
2748            "SELECT id FROM orders WHERE (ISJSON(data) = 1 AND \
2749             JSON_VALUE(data, '$.meta.tier') = 'gold')"
2750        );
2751        assert_eq!(
2752            sql(Dialect::Oracle),
2753            "SELECT id FROM orders WHERE (JSON_VALUE(data, '$.meta.tier') = 'gold')"
2754        );
2755        // With no dialect, the JSON navigation is not pushable — it
2756        // falls back to the client-side graft.
2757        assert!(pushdown(q, None).is_none());
2758    }
2759
2760    #[test]
2761    fn json_pushdown_only_string_equality() {
2762        // Numeric comparison, `!=`, and a wildcard step stay off the
2763        // pushdown path (they are not provably identical to the
2764        // graft), so they refuse and scan even with a dialect set.
2765        let d = Some(Dialect::Sqlite);
2766        assert!(pushdown("/o/*[/data/n:: > 2]::id", d).is_none());
2767        assert!(pushdown("/o/*[/data/tier:: != 'gold']::id", d).is_none());
2768        assert!(pushdown("/o/*[/data/items/*/sku:: = 'A1']::id", d).is_none());
2769    }
2770
2771    #[test]
2772    fn json_pushdown_refuses_numeric_looking_literal() {
2773        // value_eq coerces numeric-looking strings ('150' matches
2774        // the JSON number 150 and the string "150.0"); no engine's
2775        // extractor does, so the push would drop rows.
2776        let q = "/orders/*[/data/total:: = '150']::id";
2777        assert!(pushdown(q, Some(Dialect::Sqlite)).is_none());
2778        assert!(pushdown(q, Some(Dialect::Postgres)).is_none());
2779    }
2780
2781    #[test]
2782    fn partial_json_prefilters_sqlite() {
2783        // Exact string equality rides the strict gate.
2784        let p = partial_pushdown(
2785            "/orders/*[/payload/customer/geo/city:: = 'Lyon']::id",
2786            Some(Dialect::Sqlite),
2787        )
2788        .unwrap();
2789        assert_eq!(p.table, "orders");
2790        assert_eq!(
2791            p.where_sql,
2792            "(json_valid(payload) AND \
2793             json_extract(payload, '$.customer.geo.city') = 'Lyon')"
2794        );
2795        // Numeric comparison: typed compare, text values escape.
2796        let p = partial_pushdown(
2797            "/orders/*[/payload/total:: > 150]::id",
2798            Some(Dialect::Sqlite),
2799        )
2800        .unwrap();
2801        assert_eq!(
2802            p.where_sql,
2803            "(json_valid(payload) AND (json_extract(payload, '$.total') > 150 \
2804             OR json_type(payload, '$.total') = 'text'))"
2805        );
2806        // Bare existence.
2807        let p = partial_pushdown("/orders/*[/payload/gift]::id", Some(Dialect::Sqlite)).unwrap();
2808        assert_eq!(
2809            p.where_sql,
2810            "(json_valid(payload) AND json_type(payload, '$.gift') IS NOT NULL)"
2811        );
2812        // != floors at existence.
2813        let p = partial_pushdown(
2814            "/orders/*[/payload/status:: != 'x']::id",
2815            Some(Dialect::Sqlite),
2816        )
2817        .unwrap();
2818        assert_eq!(
2819            p.where_sql,
2820            "(json_valid(payload) AND json_type(payload, '$.status') IS NOT NULL)"
2821        );
2822        // A flipped literal flips the operator.
2823        let p = partial_pushdown(
2824            "/orders/*[150 < /payload/total::]::id",
2825            Some(Dialect::Sqlite),
2826        )
2827        .unwrap();
2828        assert!(p.where_sql.contains("> 150"), "{}", p.where_sql);
2829        // Without a dialect, JSON predicates stay on the engine.
2830        assert!(partial_pushdown("/orders/*[/payload/gift]::id", None).is_none());
2831    }
2832
2833    #[test]
2834    fn partial_json_prefilters_postgres() {
2835        let p = partial_pushdown(
2836            "/orders/*[/payload/total:: > 150]::id",
2837            Some(Dialect::Postgres),
2838        )
2839        .unwrap();
2840        assert_eq!(
2841            p.where_sql,
2842            "(CASE WHEN jsonb_typeof(payload::jsonb #> '{total}') = 'number' \
2843             THEN ((payload::jsonb #>> '{total}'))::float8 > 150 \
2844             WHEN jsonb_typeof(payload::jsonb #> '{total}') = 'string' \
2845             THEN true ELSE false END)"
2846        );
2847        let p = partial_pushdown("/orders/*[/payload/gift]::id", Some(Dialect::Postgres)).unwrap();
2848        assert_eq!(
2849            p.where_sql,
2850            "(jsonb_typeof(payload::jsonb #> '{gift}') IS NOT NULL)"
2851        );
2852    }
2853
2854    #[test]
2855    fn json_property_path_pushdown() {
2856        // `[/col/a::b = 'lit']`, the property form: the scalar's
2857        // question and, on an array of scalars, the list's (any item
2858        // equals). SQLite's condition says both whole.
2859        let q = "/orders/*[/payload/customer/geo::city = 'Lyon']::id";
2860        assert_eq!(
2861            pushdown(q, Some(Dialect::Sqlite)).unwrap().sql,
2862            "SELECT id FROM orders WHERE (json_valid(payload) AND \
2863             ((json_type(payload, '$.customer.geo.city') = 'text' AND \
2864             json_extract(payload, '$.customer.geo.city') = 'Lyon') OR \
2865             (json_type(payload, '$.customer.geo.city') = 'array' AND \
2866             EXISTS (SELECT 1 FROM json_each(payload, '$.customer.geo.city') \
2867             WHERE json_each.type = 'text' AND json_each.value = 'Lyon') AND \
2868             NOT EXISTS (SELECT 1 FROM json_each(payload, '$.customer.geo.city') \
2869             WHERE json_each.type IN ('array', 'object')))))"
2870        );
2871        // a property of the column's own value
2872        assert!(
2873            pushdown(
2874                "/orders/*[/payload::city = 'Lyon']::id",
2875                Some(Dialect::Sqlite)
2876            )
2877            .unwrap()
2878            .sql
2879            .contains("json_extract(payload, '$.city') = 'Lyon'")
2880        );
2881        // the other engines' array branches are not verified: no
2882        // whole push there, and none with no dialect
2883        for d in [
2884            Dialect::Postgres,
2885            Dialect::MySql,
2886            Dialect::Mssql,
2887            Dialect::Oracle,
2888        ] {
2889            assert!(pushdown(q, Some(d)).is_none(), "{d:?}");
2890        }
2891        assert!(pushdown(q, None).is_none());
2892        // a numeric-looking literal compares by value: no whole push
2893        assert!(
2894            pushdown(
2895                "/orders/*[/payload::total = '150']::id",
2896                Some(Dialect::Sqlite)
2897            )
2898            .is_none()
2899        );
2900        // a column named as one of json_each's own columns would be
2901        // read as that column inside the subquery
2902        assert!(
2903            pushdown(
2904                "/orders/*[/value::city = 'Lyon']::id",
2905                Some(Dialect::Sqlite)
2906            )
2907            .is_none()
2908        );
2909    }
2910
2911    #[test]
2912    fn partial_json_property_prefilters() {
2913        let sqlite = |q: &str| partial_pushdown(q, Some(Dialect::Sqlite)).map(|p| p.where_sql);
2914        // a comparison lets the arrays through with the text values:
2915        // a list matches when any item does, and the engine decides
2916        assert_eq!(
2917            sqlite("/orders/*[/payload::total > 150]::id").unwrap(),
2918            "(json_valid(payload) AND (json_extract(payload, '$.total') > 150 \
2919             OR json_type(payload, '$.total') IN ('text', 'array')))"
2920        );
2921        assert_eq!(
2922            sqlite("/orders/*[/payload/customer::tier < 'm']::id").unwrap(),
2923            "(json_valid(payload) AND (json_extract(payload, '$.customer.tier') < 'm' \
2924             OR json_type(payload, '$.customer.tier') = 'array'))"
2925        );
2926        // a bare property: present, as the child form
2927        assert_eq!(
2928            sqlite("/orders/*[/payload::gift]::id").unwrap(),
2929            "(json_valid(payload) AND json_type(payload, '$.gift') IS NOT NULL)"
2930        );
2931        // `!=` keeps a row whose key is absent: its floor is the node
2932        // the property is read on, and on the column itself there is
2933        // none
2934        assert_eq!(
2935            sqlite("/orders/*[/payload/customer::tier != 'x']::id").unwrap(),
2936            "(json_valid(payload) AND json_type(payload, '$.customer') IS NOT NULL)"
2937        );
2938        assert!(sqlite("/orders/*[/payload::status != 'x']::id").is_none());
2939        // PostgreSQL: the equality as a prefilter, the arrays through
2940        assert_eq!(
2941            partial_pushdown(
2942                "/orders/*[/payload/customer/geo::city = 'Lyon']::id",
2943                Some(Dialect::Postgres)
2944            )
2945            .unwrap()
2946            .where_sql,
2947            "(CASE WHEN jsonb_typeof(payload::jsonb #> '{customer,geo,city}') = 'string' \
2948             THEN (payload::jsonb #>> '{customer,geo,city}') = 'Lyon' \
2949             WHEN jsonb_typeof(payload::jsonb #> '{customer,geo,city}') = 'array' \
2950             THEN true ELSE false END)"
2951        );
2952        assert_eq!(
2953            partial_pushdown(
2954                "/orders/*[/payload::total > 150]::id",
2955                Some(Dialect::Postgres)
2956            )
2957            .unwrap()
2958            .where_sql,
2959            "(CASE WHEN jsonb_typeof(payload::jsonb #> '{total}') = 'number' \
2960             THEN ((payload::jsonb #>> '{total}'))::float8 > 150 \
2961             WHEN jsonb_typeof(payload::jsonb #> '{total}') IN ('string', 'array') \
2962             THEN true ELSE false END)"
2963        );
2964        // no type probe, no prefilter
2965        assert!(
2966            partial_pushdown("/orders/*[/payload::total > 150]::id", Some(Dialect::MySql))
2967                .is_none()
2968        );
2969    }
2970
2971    #[test]
2972    fn json_pushdown_refuses_a_container_looking_literal() {
2973        // An extractor answers an array or an object as its JSON
2974        // text, so `'[1,2]'` equalled the array itself, which has no
2975        // value in the graft: the whole push kept a row the scan
2976        // drops. It rides the prefilter (a superset, re-checked).
2977        for q in [
2978            "/orders/*[/data/a:: = '[1,2]']::id",
2979            "/orders/*[/data/a:: = '{\"k\":1}']::id",
2980        ] {
2981            assert!(pushdown(q, Some(Dialect::Sqlite)).is_none(), "{q}");
2982            assert!(pushdown(q, Some(Dialect::Postgres)).is_none(), "{q}");
2983        }
2984        assert_eq!(
2985            partial_pushdown("/orders/*[/data/a:: = '[1,2]']::id", Some(Dialect::Sqlite))
2986                .unwrap()
2987                .where_sql,
2988            "(json_valid(data) AND json_extract(data, '$.a') = '[1,2]')"
2989        );
2990    }
2991
2992    #[test]
2993    fn partial_skips_untranslatable_keeps_prefix() {
2994        // A refused predicate no prefilter covers is skipped — the
2995        // shorter conjunction is still a superset (the engine
2996        // re-applies the original) — rather than aborting the plan.
2997        let p = partial_pushdown(
2998            "/orders/*[::status = 'shipped'][/payload/line_items/*/sku:: = 'X']::id",
2999            Some(Dialect::Sqlite),
3000        )
3001        .unwrap();
3002        assert_eq!(p.where_sql, "status = 'shipped'");
3003    }
3004
3005    #[test]
3006    fn null_literal_compares_use_is_null() {
3007        // `= null` / `!= null` are provably identical to Quarb's
3008        // value_eq(NULL, …) semantics, never the always-UNKNOWN
3009        // `x = NULL`; they translate — and push — in both modes.
3010        assert_eq!(
3011            export("/t/*[::x = null] | ::x").unwrap().query,
3012            "SELECT x FROM t WHERE x IS NULL"
3013        );
3014        assert_eq!(
3015            export("/t/*[::x != null] | ::x").unwrap().query,
3016            "SELECT x FROM t WHERE x IS NOT NULL"
3017        );
3018        assert_eq!(
3019            pushdown("/t/*[::x = null] | ::x", None).unwrap().sql,
3020            "SELECT x FROM t WHERE x IS NULL"
3021        );
3022        assert_eq!(
3023            pushdown("/t/*[::x != null] | ::x", None).unwrap().sql,
3024            "SELECT x FROM t WHERE x IS NOT NULL"
3025        );
3026    }
3027
3028    #[test]
3029    fn ne_and_not_refuse_pushdown_but_display_diverges() {
3030        // `!=` against a non-null value and `not(...)` drop the NULL
3031        // rows Quarb keeps under SQL three-valued logic: the pushdown
3032        // paths refuse (and scan), full and partial alike.
3033        assert!(pushdown("/t/*[::x != 5] | ::x", None).is_none());
3034        assert!(pushdown("/t/*[!::x = 5] | ::x", None).is_none());
3035        assert!(partial_pushdown(&format!("/t/*[::x != 5]{GROUPED}"), None).is_none());
3036        assert!(partial_pushdown(&format!("/t/*[!::x = 5]{GROUPED}"), None).is_none());
3037        // The display translation still emits `<>`, flagged with a note.
3038        let t = export("/t/*[::x != 5] | ::x").unwrap();
3039        assert_eq!(t.query, "SELECT x FROM t WHERE x <> 5");
3040        assert!(t.notes.iter().any(|n| n.contains("NULL")));
3041    }
3042
3043    #[test]
3044    fn unescapable_text_literal_refuses_pushdown() {
3045        // A backslash (MySQL/BigQuery escape) or an apostrophe
3046        // (BigQuery rejects '' doubling) has no portable escaping, so
3047        // pushdown refuses; a clean literal still pushes.
3048        assert!(pushdown("/files/*[::path = \"C:\\temp\"] | ::path", None).is_none());
3049        assert!(pushdown("/t/*[::name = \"it's\"] | ::name", None).is_none());
3050        assert!(partial_pushdown(&format!("/t/*[::name = \"it's\"]{GROUPED}"), None).is_none());
3051        assert_eq!(
3052            pushdown("/t/*[::name = \"rare\"] | ::name", None)
3053                .unwrap()
3054                .sql,
3055            "SELECT name FROM t WHERE name = 'rare'"
3056        );
3057    }
3058}