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::{Refusal, 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        order: PlanOrder::default(),
55        aggregate: false,
56        keyed_hops: false,
57        from_marks: Vec::new(),
58        join_marks: Vec::new(),
59        join_alias: None,
60        sums: Vec::new(),
61    };
62    let query = ex.query()?;
63    Ok(Translation {
64        query,
65        notes: ex.notes,
66    })
67}
68
69/// A part SQL has no form for, under the word its refusal is told
70/// by.
71fn not_pushed(word: Refusal, reason: String) -> SqlError {
72    SqlError::NotPushed {
73        word,
74        mismatch: false,
75        reason,
76    }
77}
78
79/// A part SQL can spell, with other semantics: the reason names the
80/// divergence.
81fn mismatch(word: Refusal, reason: String) -> SqlError {
82    SqlError::NotPushed {
83        word,
84        mismatch: true,
85        reason,
86    }
87}
88
89/// The exporter rewrites `__LEFT__` (the join's left table) and
90/// `__AGG__` (the HAVING aggregate) as internal placeholders.
91/// Query text containing either marker would be rewritten inside
92/// its own string literals — and could spoof the emitted SQL — so
93/// such queries stay on the scan path.
94fn refuse_marker(quarb: &str) -> Result<(), SqlError> {
95    for marker in ["__LEFT__", "__AGG__"] {
96        if quarb.contains(marker) {
97            return Err(not_pushed(
98                Refusal::Name,
99                format!("query text contains the reserved marker \"{marker}\""),
100            ));
101        }
102    }
103    Ok(())
104}
105
106/// A pushdown plan: SQL whose execution is provably identical to
107/// the Quarb query's — plus the table whose primary key must order
108/// the rows (`None` for a single aggregate row, where order is
109/// moot). The driver appends the `ORDER BY`, since the key lives in
110/// its catalog.
111/// A sum or a mean over a column, as a plan's statement writes it
112/// (`text`: `SUM(unit_price)`). The engine adds a float column by
113/// the careful sum, in key order; SQLite adds the same way, and its
114/// SUM is the engine's to the last digit. PostgreSQL and DuckDB add
115/// plainly and across workers, in no fixed order, so a float sum
116/// there moves in its last digits from one run to the next: their
117/// doors ask the catalog whether the column is a float and rewrite
118/// the sum in the dialect's ordered form (`sum(x ORDER BY key)`),
119/// which is the same number every run. The plan says which sums it
120/// holds; the door does the rest.
121///
122/// `table` is the table the column is looked up in. `order` is what
123/// the scan adds by: the tables whose keys order the rows, each with
124/// the name the statement gives it (the driver; after it the joined
125/// table where every witness is bound, under its alias where it is
126/// joined to itself).
127#[derive(Debug, Clone, PartialEq, Eq)]
128pub struct FloatSum {
129    pub text: String,
130    pub mean: bool,
131    pub table: String,
132    pub column: String,
133    pub column_sql: String,
134    pub order: Vec<(String, String)>,
135}
136
137pub struct Pushdown {
138    pub sql: String,
139    /// The plan's sums and means over a column, for a source that
140    /// adds floats in its own order: see [`FloatSum`].
141    pub sums: Vec<FloatSum>,
142    pub order_table: Option<String>,
143    /// Present when the plan contains a witness JOIN: the joined
144    /// table and the columns its ON equalities bind on it
145    /// (collected structurally from the arbor, so query text
146    /// cannot spoof them).
147    /// The plan is only sound if those columns form a unique key
148    /// of the joined table — each FROM row must find at most one
149    /// witness, else SQL multiplies rows where Quarb's existential
150    /// binding does not. The *driver* must verify against its
151    /// catalog before executing, and fall back to the scan if it
152    /// cannot.
153    pub join_left: Option<(String, Vec<String>)>,
154    /// Present when the plan is an every-witness JOIN (`<=>*`,
155    /// ruling 2026-11-11): the joined table. Such a JOIN is what the
156    /// query asks, so it carries no uniqueness obligation
157    /// (`join_left` is absent); its obligation is the ORDER: the
158    /// rows of one driver stand in the joined table's own order,
159    /// after the driver's (`order_table`'s key, then this table's).
160    /// A driver that cannot order by both must decline the plan.
161    pub order_then: Option<String>,
162    /// The name that second table bears in the statement where it
163    /// is not its own: a table joined to itself is aliased, and the
164    /// driver writes its key under the alias.
165    pub order_then_as: Option<String>,
166    /// The plan's own sort, in SQL, where it has one: `(n IS NULL),
167    /// n` for `sort_by(::n)`, a null key last as the engine puts it.
168    /// The driver writes it before the keys, which keep the ties in
169    /// the tables' order as the engine's stable sort does.
170    pub order_first: Option<String>,
171    /// The keys descending: the context reversed (`@| reverse`).
172    pub order_desc: bool,
173    /// The plan is a fold: one row a group, in the order each group
174    /// first appears, which the driver writes as the least key of
175    /// each (`ORDER BY MIN(key)`).
176    pub order_groups: bool,
177    /// The first rows alone (`@| [..n]`), after the order.
178    pub limit: Option<u64>,
179    /// The plan's rows are records the query built (`| %(…)`, the
180    /// `%.` of a fold): a row of one column is then a record of one
181    /// field, where a projection's is the bare value.
182    pub record: bool,
183    /// The plan's rows are the records of the rows' own properties
184    /// (`| %`): a NULL cell is no property of its row, and is left
185    /// out of that row's record, as the scan leaves it out.
186    pub properties: bool,
187    /// The column a sort, a fold or a distinct count goes by, with
188    /// the word of that construct. The plan is the scan's only where
189    /// the catalog says the column is numeric (it is among
190    /// `column_types.numeric`), and unlike a comparison the database
191    /// would not refuse it over a text column: it would run, and
192    /// answer its collation's order or its own groups. A caller with
193    /// no catalog to ask scans; one whose catalog says the column is
194    /// not numeric scans and tells of it under the word.
195    pub keyed_by: Option<(Refusal, String)>,
196    /// What the plan takes for granted about column types. Quarb
197    /// compares by value (a numeric-looking text and a number meet
198    /// as numbers, a number and other text never meet); SQL
199    /// compares by the column's type, and the loosely typed engines
200    /// (SQLite, MySQL) coerce by rules of their own. The plan is
201    /// the scan's only where the columns it compares with a number
202    /// are numeric-typed and those it compares with text are not:
203    /// the caller asks its catalog and scans when one is otherwise.
204    /// (A strictly typed engine rejects the mismatch itself, which
205    /// sends the caller to the scan as well.)
206    pub column_types: ColumnTypes,
207    /// The tables the plan reads (the FROM table, then a joined one).
208    pub tables: Vec<String>,
209}
210
211/// The column types a pushed plan assumes (see
212/// [`Pushdown::column_types`]).
213#[derive(Clone, Debug, Default, PartialEq, Eq)]
214pub struct ColumnTypes {
215    /// Columns compared with a numeric literal.
216    pub numeric: Vec<String>,
217    /// Columns compared with a text literal.
218    pub text: Vec<String>,
219    /// Columns the plan extracts a JSON path from on the strength
220    /// of the column's declaration alone (the step carried no
221    /// `<json>`): the pushed `json_extract` is the scan's only where
222    /// the catalog declares the column json, since the scan grafts
223    /// a leaf only as declared (spec: Grafting).
224    pub json: Vec<String>,
225    /// Whether the plan reads a JSON value as a child node of its
226    /// column (`/col/key::`, or the bare `/col/key` of an existence
227    /// test), with `<json>` on the step or without: the scan's only
228    /// where the mount's grafts list scalars as children, which the
229    /// property reading (`?graft=json2`) does not. The property
230    /// form (`/col::key`) answers under both readings and does not
231    /// set it. No assumption about the catalog ([`is_empty`] and
232    /// [`unmet`] leave it out): the front end holds it against its
233    /// mount's reading.
234    ///
235    /// [`is_empty`]: Self::is_empty
236    /// [`unmet`]: Self::unmet
237    pub json_children: bool,
238    /// Whether the plan compares two values of the row with each
239    /// other (`[::a = ::b]`, a join's key equality, a column against
240    /// an arithmetic): the engine compares them by value, SQL by
241    /// their types, and the two agree only where every column
242    /// compared is numeric. The columns are among [`numeric`]; this
243    /// says that no typed engine enforces the assumption by itself
244    /// (two text columns compare without complaint, as texts), so a
245    /// source whose catalog is not asked does not get the plan.
246    ///
247    /// [`numeric`]: Self::numeric
248    pub columns_compared: bool,
249    /// The columns among [`numeric`] that a computation assumed
250    /// (`::a + 1`), and those a reducer assumed (`::a @| sum`): what
251    /// a front end names when it tells an assumption the catalog did
252    /// not bear out. A column in neither was assumed by a
253    /// comparison.
254    ///
255    /// [`numeric`]: Self::numeric
256    pub computed: Vec<String>,
257    pub reduced: Vec<String>,
258}
259
260/// One column of a source's catalog, as a plan's assumptions are
261/// held against it: its name; whether its type is numeric; whether
262/// it is declared json; and whether the database compares its text
263/// exactly, by characters, case and trailing spaces included (false
264/// for a `char(n)` of PostgreSQL, which ignores the padding it
265/// answers with, and for a column of SQLite declared under a
266/// collation).
267pub type ColumnFact = (String, bool, bool, bool);
268
269impl ColumnTypes {
270    pub fn is_empty(&self) -> bool {
271        self.numeric.is_empty() && self.text.is_empty() && self.json.is_empty()
272    }
273
274    /// The assumptions a catalog does not bear out: the columns
275    /// compared with a number (or with each other, or computed
276    /// with) that are not numeric-typed, and those compared with
277    /// text that are, or whose text the database does not compare
278    /// exactly. `catalog` lists every column of the tables read
279    /// ([`ColumnFact`]); names meet without regard to case. A column
280    /// the catalog does not list is unknown, and counts as unmet.
281    pub fn unmet(&self, catalog: &[ColumnFact]) -> Vec<String> {
282        let met = |column: &str, pick: fn(&ColumnFact) -> bool, want: bool| {
283            let mut found = catalog
284                .iter()
285                .filter(|(name, ..)| name.eq_ignore_ascii_case(column))
286                .peekable();
287            found.peek().is_some() && found.all(|entry| pick(entry) == want)
288        };
289        let numeric = |e: &ColumnFact| e.1;
290        let json = |e: &ColumnFact| e.2;
291        // a text comparison: no number, and compared exactly
292        let exact_text = |e: &ColumnFact| !e.1 && e.3;
293        let mut out: Vec<String> = Vec::new();
294        for (columns, pick, want) in [
295            (&self.numeric, numeric as fn(&ColumnFact) -> bool, true),
296            (&self.text, exact_text, true),
297            (&self.json, json, true),
298        ] {
299            for c in columns {
300                if !met(c, pick, want) && !out.contains(c) {
301                    out.push(c.clone());
302                }
303            }
304        }
305        out
306    }
307}
308
309/// The target SQL dialect, for the one construct whose emitted
310/// SQL is not portable: a filter that navigates *into* a JSON
311/// column, which each engine extracts with its own operator.
312/// The rest of a pushdown is dialect-agnostic. A query with no
313/// JSON-column filter emits identical SQL regardless of dialect.
314#[derive(Clone, Copy, PartialEq, Eq, Debug)]
315pub enum Dialect {
316    Postgres,
317    MySql,
318    Sqlite,
319    Mssql,
320    Oracle,
321}
322
323/// Attempt the pushdown translation: `Some` only when every
324/// construct in the query is in the verified-safe set. Anything
325/// else — including everything `export` would merely annotate with
326/// a divergence note — returns `None`, and the caller scans.
327///
328/// `dialect` enables JSON-column-path pushdown for that engine
329/// (fixed-path string equality only); `None` keeps such filters
330/// on the client-side graft.
331pub fn pushdown(quarb: &str, dialect: Option<Dialect>) -> Option<Pushdown> {
332    pushdown_explained(quarb, dialect).ok()
333}
334
335/// [`pushdown`], keeping the refusal: the error names the first
336/// construct that kept the query on the scan path.
337pub fn pushdown_explained(quarb: &str, dialect: Option<Dialect>) -> Result<Pushdown, SqlError> {
338    refuse_marker(quarb)?;
339    let arbor =
340        QueryArbor::parse(quarb).map_err(|e| SqlError::Syntax(format!("parsing Quarb: {e}")))?;
341    refuse_groups(&arbor)?;
342    let mut ex = Exporter {
343        arbor,
344        notes: Vec::new(),
345        strict: true,
346        dialect,
347        from_table: String::new(),
348        join_on_cols: Vec::new(),
349        join_table: None,
350        join_every: false,
351        column_types: ColumnTypes::default(),
352        from_sql: String::new(),
353        join_sql: None,
354        in_on: false,
355        order: PlanOrder::default(),
356        aggregate: false,
357        keyed_hops: false,
358        from_marks: Vec::new(),
359        join_marks: Vec::new(),
360        join_alias: None,
361        sums: Vec::new(),
362    };
363    let sql = ex.query()?;
364    let order_table = if ex.aggregate && !ex.order.groups {
365        None
366    } else {
367        // Rows come back in the driver's document order — the FROM
368        // table's (driver-first correlation).
369        Some(ex.from_table.clone())
370    };
371    // an every-witness JOIN multiplies rows by design: no uniqueness
372    // to verify, the joined table's order to keep instead
373    let join_left = if ex.join_every {
374        None
375    } else {
376        ex.join_table.clone().map(|t| (t, ex.join_on_cols.clone()))
377    };
378    let order_then = if ex.join_every && !ex.aggregate {
379        ex.join_table.clone()
380    } else {
381        None
382    };
383    let mut tables = vec![ex.from_table.clone()];
384    tables.extend(ex.join_table.clone());
385    // the plan's own sort: a null key last, the ties left to the
386    // keys; reversed, every term turns
387    let order_first = ex.order.sort.as_ref().map(|(e, desc)| {
388        let nulls = if ex.order.reversed { " DESC" } else { "" };
389        let dir = if *desc != ex.order.reversed {
390            " DESC"
391        } else {
392            ""
393        };
394        format!("({e} IS NULL){nulls}, {e}{dir}")
395    });
396    // A sum's column is the driver's or, under the joined table's
397    // name in the statement, the joined table's; the order the scan
398    // adds in is the driver's key and, where every witness is bound,
399    // the joined table's after it (a first witness is one row a
400    // driver).
401    let sums: Vec<FloatSum> = {
402        let from = ex.from_table.clone();
403        let joined: Option<(String, String)> = ex
404            .join_table
405            .clone()
406            .map(|t| (t.clone(), ex.join_alias.clone().unwrap_or(t)));
407        let mut order = vec![(from.clone(), from.clone())];
408        if let (true, Some(j)) = (ex.join_every, joined.as_ref()) {
409            order.push(j.clone());
410        }
411        ex.sums
412            .iter()
413            .map(|(mean, column, column_sql)| {
414                let table = match (column_sql.rsplit_once('.'), joined.as_ref()) {
415                    (Some((qual, _)), Some((t, name))) if qual == name => t.clone(),
416                    _ => from.clone(),
417                };
418                FloatSum {
419                    text: format!("{}({column_sql})", if *mean { "AVG" } else { "SUM" }),
420                    mean: *mean,
421                    table,
422                    column: column.clone(),
423                    column_sql: column_sql.clone(),
424                    order: order.clone(),
425                }
426            })
427            .collect()
428    };
429    Ok(Pushdown {
430        sql,
431        sums,
432        order_table,
433        join_left,
434        order_then_as: order_then.as_ref().and(ex.join_alias.clone()),
435        order_then,
436        order_first,
437        order_desc: ex.order.reversed,
438        order_groups: ex.order.groups,
439        limit: ex.order.limit,
440        record: ex.order.record,
441        properties: ex.order.properties,
442        keyed_by: ex.order.keyed_by.clone(),
443        column_types: ex.column_types.clone(),
444        tables,
445    })
446}
447
448/// The dialect-specific SQL that extracts a JSON scalar at a
449/// fixed object path, unquoted to text. `path` holds plain
450/// object keys (identifier-safe, per `json_path`), so no
451/// per-dialect path-escaping is needed. Each engine's operator
452/// returns the value as text and yields NULL for an absent path
453/// or a non-scalar — matching the graft, which excludes those
454/// rows too.
455fn json_extract(dialect: Dialect, qual: Option<&str>, col: &str, path: &[String]) -> String {
456    let qcol = match qual {
457        Some(q) => format!("{q}.{col}"),
458        None => col.to_string(),
459    };
460    match dialect {
461        // `#>>` takes a text-array path and returns text; the
462        // `::jsonb` cast lets it work on json, jsonb, and
463        // text-holding-JSON columns alike (an invalid-JSON row
464        // errors, and the driver falls back to the scan).
465        Dialect::Postgres => format!("({qcol}::jsonb #>> '{{{}}}')", path.join(",")),
466        // JSON_UNQUOTE(JSON_EXTRACT(...)) is the `->>` shorthand
467        // spelled out — portable across MySQL and MariaDB, where
468        // the `->>` operator itself is not.
469        Dialect::MySql => {
470            format!("JSON_UNQUOTE(JSON_EXTRACT({qcol}, '$.{}'))", path.join("."))
471        }
472        Dialect::Sqlite => format!("json_extract({qcol}, '$.{}')", path.join(".")),
473        // JSON_VALUE returns a scalar as text (lax mode: NULL on a
474        // missing path or a non-scalar, no error).
475        Dialect::Mssql | Dialect::Oracle => format!("JSON_VALUE({qcol}, '$.{}')", path.join(".")),
476    }
477}
478
479/// The columns SQLite's `json_each` answers: inside a subquery over
480/// it an unqualified name of these is its own.
481const JSON_EACH_COLUMNS: &[&str] = &[
482    "key", "value", "type", "atom", "id", "parent", "fullkey", "path", "json", "root",
483];
484
485/// Wrap a JSON-path condition in the dialect's validity guard,
486/// where a cheap one exists. SQLite and MySQL error the whole
487/// statement on one malformed-JSON row — which turned every
488/// pushdown over a dirty table into a silent fallback scan — and
489/// the graft excludes such rows anyway (no parse, no subtree), so
490/// the guard is observationally identical. SQL Server gets ISJSON;
491/// Oracle's JSON_VALUE is lax already; PostgreSQL has no guard
492/// short of the ::jsonb cast itself (an invalid row errors and the
493/// caller falls back to the scan).
494fn json_valid_guard(dialect: Dialect, qual: Option<&str>, col: &str, cond: String) -> String {
495    let qcol = match qual {
496        Some(q) => format!("{q}.{col}"),
497        None => col.to_string(),
498    };
499    match dialect {
500        Dialect::Sqlite => format!("(json_valid({qcol}) AND {cond})"),
501        Dialect::MySql => format!("(JSON_VALID({qcol}) AND {cond})"),
502        Dialect::Mssql => format!("(ISJSON({qcol}) = 1 AND {cond})"),
503        Dialect::Postgres | Dialect::Oracle => format!("({cond})"),
504    }
505}
506
507/// The dialect's JSON type probe at a fixed path — non-NULL iff
508/// the path exists. A JSON `null` value reports its type ('null'),
509/// so existence still sees it, matching the graft, where a
510/// null-valued key is a node. Only the dialects with a native
511/// probe participate.
512fn json_type_probe(
513    dialect: Dialect,
514    qual: Option<&str>,
515    col: &str,
516    path: &[String],
517) -> Option<String> {
518    let qcol = match qual {
519        Some(q) => format!("{q}.{col}"),
520        None => col.to_string(),
521    };
522    match dialect {
523        Dialect::Sqlite => Some(format!("json_type({qcol}, '$.{}')", path.join("."))),
524        Dialect::Postgres => Some(format!(
525            "jsonb_typeof({qcol}::jsonb #> '{{{}}}')",
526            path.join(",")
527        )),
528        Dialect::MySql | Dialect::Mssql | Dialect::Oracle => None,
529    }
530}
531
532/// The literals no match of `pattern` can do without: each is a
533/// substring of every text the regex matches. Read from the regex
534/// crate's own parse (the engine compiles with its defaults): a
535/// literal of a concatenation, of a group, of a repetition that
536/// runs at least once. An alternation, a class (a letter under
537/// `(?i)` is one) and an optional part promise nothing.
538pub(crate) fn required_literals(pattern: &str) -> Vec<String> {
539    use regex_syntax::hir::{Hir, HirKind};
540    fn walk(h: &Hir, out: &mut Vec<String>) {
541        match h.kind() {
542            HirKind::Literal(lit) => {
543                if let Ok(s) = std::str::from_utf8(&lit.0)
544                    && !s.is_empty()
545                    && !out.iter().any(|o| o == s)
546                {
547                    out.push(s.to_string());
548                }
549            }
550            HirKind::Capture(c) => walk(&c.sub, out),
551            HirKind::Concat(parts) => parts.iter().for_each(|p| walk(p, out)),
552            HirKind::Repetition(r) if r.min >= 1 => walk(&r.sub, out),
553            _ => {}
554        }
555    }
556    let Ok(hir) = regex_syntax::parse(pattern) else {
557        return Vec::new();
558    };
559    let mut out = Vec::new();
560    walk(&hir, &mut out);
561    out
562}
563
564/// "`l` holds `lit`", as a filter that keeps every row whose text
565/// holds the literal by bytes and case, and may keep more (the
566/// engine runs the regex over what is fetched):
567/// - SQLite's `instr` is exactly that test;
568/// - PostgreSQL's `LIKE` is case-sensitive there;
569/// - MySQL's `instr` follows the column's collation, which folds
570///   case and accents by default: it keeps every text that holds
571///   the literal exactly, and some that hold it in another case;
572/// - under no dialect (the doors of DuckDB, BigQuery and Athena)
573///   `strpos`, which the three have, by characters and case.
574///
575/// `None` for SQL Server and Oracle, and for a literal a SQL string
576/// would not carry as written on every server (a quote, a
577/// backslash, a control character).
578fn contains_sql(dialect: Option<Dialect>, l: &str, lit: &str) -> Option<String> {
579    if lit
580        .chars()
581        .any(|c| c.is_control() || c == '\\' || c == '\'')
582    {
583        return None;
584    }
585    match dialect {
586        Some(Dialect::Sqlite | Dialect::MySql) => Some(format!("instr({l}, '{lit}') > 0")),
587        Some(Dialect::Postgres) => {
588            let pat = lit.replace('%', "\\%").replace('_', "\\_");
589            Some(format!("{l} LIKE '%{pat}%' ESCAPE '\\'"))
590        }
591        None => Some(format!("strpos({l}, '{lit}') > 0")),
592        Some(Dialect::Mssql | Dialect::Oracle) => None,
593    }
594}
595
596/// Flip a comparison for a swapped operand order (`lit OP path` →
597/// `path OP' lit`).
598fn flip_op(op: &str) -> String {
599    match op {
600        "<" => ">".into(),
601        "<=" => ">=".into(),
602        ">" => "<".into(),
603        ">=" => "<=".into(),
604        other => other.into(),
605    }
606}
607
608/// SQL keywords that must not appear as a bare identifier or `AS`
609/// alias — quoting them portably differs by dialect, so strict
610/// mode refuses and export mode quotes with double quotes plus a
611/// note.
612const SQL_KEYWORDS: &[&str] = &[
613    "all",
614    "and",
615    "as",
616    "asc",
617    "by",
618    "case",
619    "cross",
620    "desc",
621    "distinct",
622    "else",
623    "end",
624    "except",
625    "exists",
626    "from",
627    "group",
628    "having",
629    "in",
630    "index",
631    "inner",
632    "intersect",
633    "into",
634    "is",
635    "join",
636    "left",
637    "like",
638    "limit",
639    "not",
640    "null",
641    "offset",
642    "on",
643    "or",
644    "order",
645    "outer",
646    "right",
647    "select",
648    "set",
649    "table",
650    "then",
651    "union",
652    "unique",
653    "update",
654    "using",
655    "values",
656    "when",
657    "where",
658];
659
660/// A bare SQL identifier, portable across the target dialects: a
661/// letter or underscore, then letters, digits, and underscores,
662/// and not a reserved word.
663fn is_plain_ident(name: &str) -> bool {
664    !name.is_empty()
665        && name
666            .chars()
667            .next()
668            .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
669        && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_')
670        && !SQL_KEYWORDS.contains(&name.to_ascii_lowercase().as_str())
671}
672
673/// The SELECT under construction.
674#[derive(Default)]
675struct Select {
676    select: Vec<String>,
677    distinct: bool,
678    from: String,
679    join: Option<(String, String)>, // (table, ON condition)
680    /// The join keeps every FROM row (`<=>?`, the outer
681    /// correlation): a LEFT JOIN.
682    join_outer: bool,
683    wheres: Vec<String>,
684    group_by: Option<String>,
685    having: Option<String>,
686    order_by: Option<(String, bool)>, // (expr, desc)
687    limit: Option<String>,
688}
689
690impl Select {
691    fn render(&self) -> String {
692        let mut out = String::from("SELECT ");
693        if self.distinct {
694            out.push_str("DISTINCT ");
695        }
696        if self.select.is_empty() {
697            out.push('*');
698        } else {
699            out.push_str(&self.select.join(", "));
700        }
701        out.push_str(&format!(" FROM {}", self.from));
702        if let Some((t, on)) = &self.join {
703            let kind = if self.join_outer { "LEFT JOIN" } else { "JOIN" };
704            out.push_str(&format!(" {kind} {t} ON {on}"));
705        }
706        if !self.wheres.is_empty() {
707            out.push_str(&format!(" WHERE {}", self.wheres.join(" AND ")));
708        }
709        if let Some(g) = &self.group_by {
710            out.push_str(&format!(" GROUP BY {g}"));
711        }
712        if let Some(h) = &self.having {
713            out.push_str(&format!(" HAVING {h}"));
714        }
715        if let Some((e, desc)) = &self.order_by {
716            out.push_str(&format!(" ORDER BY {e}"));
717            if *desc {
718                out.push_str(" DESC");
719            }
720        }
721        if let Some(n) = &self.limit {
722            out.push_str(&format!(" LIMIT {n}"));
723        }
724        out
725    }
726}
727
728/// A partial-pushdown plan: the leading row predicates as a WHERE
729/// clause for `table`'s fetch. The caller runs the *original* query
730/// against the filtered adapter — the engine re-applies the pushed
731/// predicates (a no-op on pre-filtered rows), so no rewriting.
732pub struct Partial {
733    pub table: String,
734    pub where_sql: String,
735    /// As [`Pushdown::column_types`]: the prefilter keeps every row
736    /// the scan would only where these assumptions hold.
737    pub column_types: ColumnTypes,
738}
739
740/// Attempt a partial pushdown: `Some` when the query's leading row
741/// predicates translate strictly and the rest of the query provably
742/// cannot observe the filtering. The gates, each with its reason:
743/// a single `/table/*` branch with no correlations (other shapes
744/// address other data); only the *leading* run of expression
745/// predicates pushes (a positional predicate before an expression
746/// one sees unfiltered rows on the scan path); the table's name
747/// appears exactly once among the query's steps (a second reach —
748/// `^`-anchored subcontexts, self-references — would see the
749/// filtered subset); no crosslink or resolution axes anywhere
750/// (backlinks and reverse resolution into the table would too);
751/// and no `:` / `:::` metadata anywhere (a filtered `:::n-rows`
752/// would lie).
753pub fn partial_pushdown(quarb: &str, dialect: Option<Dialect>) -> Option<Partial> {
754    partial_pushdown_explained(quarb, dialect).ok()
755}
756
757/// [`partial_pushdown`], keeping the refusal reason. `dialect`
758/// enables the JSON-column prefilters — the strict fixed-path
759/// string equality, plus the relaxed superset forms (numeric
760/// comparisons, existence) that are safe here and only here,
761/// because the caller re-runs the original query over the
762/// fetched subset.
763pub fn partial_pushdown_explained(
764    quarb: &str,
765    dialect: Option<Dialect>,
766) -> Result<Partial, SqlError> {
767    partial_pushdown_with(quarb, dialect, false)
768}
769
770/// [`partial_pushdown_explained`] for an adapter that reads steps by
771/// key and filters only the table's listing
772/// (`RelationalModel::with_filtered_listing`): crosslinks and
773/// resolutions no longer refuse the prefilter, since a step into the
774/// filtered table reads its row unfiltered. What still refuses: a
775/// second navigation into the table, and a `<--` or `=>` scope that
776/// could list it.
777pub fn partial_pushdown_keyed(quarb: &str, dialect: Option<Dialect>) -> Result<Partial, SqlError> {
778    partial_pushdown_with(quarb, dialect, true)
779}
780
781fn partial_pushdown_with(
782    quarb: &str,
783    dialect: Option<Dialect>,
784    keyed_hops: bool,
785) -> Result<Partial, SqlError> {
786    refuse_marker(quarb)?;
787    let arbor =
788        QueryArbor::parse(quarb).map_err(|e| SqlError::Syntax(format!("parsing Quarb: {e}")))?;
789    refuse_groups(&arbor)?;
790    let mut ex = Exporter {
791        arbor,
792        notes: Vec::new(),
793        strict: true,
794        dialect,
795        from_table: String::new(),
796        join_on_cols: Vec::new(),
797        join_table: None,
798        join_every: false,
799        column_types: ColumnTypes::default(),
800        from_sql: String::new(),
801        join_sql: None,
802        in_on: false,
803        order: PlanOrder::default(),
804        aggregate: false,
805        keyed_hops,
806        from_marks: Vec::new(),
807        join_marks: Vec::new(),
808        join_alias: None,
809        sums: Vec::new(),
810    };
811    ex.partial()
812}
813
814/// Refuse a query carrying path-pattern groups: neither the SQL
815/// translation nor the pushdown safe set covers them, and the shape
816/// checks below count only `step` children — an unguarded group
817/// would silently vanish from the translation. Refused queries fall
818/// back to the scan path, which evaluates groups correctly.
819fn refuse_groups(arbor: &QueryArbor) -> Result<(), SqlError> {
820    let mut stack = vec![arbor.root()];
821    while let Some(n) = stack.pop() {
822        if arbor.name(n).as_deref() == Some("group") {
823            return Err(not_pushed(
824                Refusal::Walk,
825                "path patterns (groups and quantifiers)".into(),
826            ));
827        }
828        stack.extend(arbor.children(n));
829    }
830    Ok(())
831}
832
833/// The order a strict plan asks its driver for ([`Pushdown`]'s
834/// `order_first`, `order_desc`, `order_groups`, `limit`).
835#[derive(Default)]
836struct PlanOrder {
837    /// `sort_by(::n)`: the key as SQL, and whether `--desc`.
838    sort: Option<(String, bool)>,
839    /// `@| reverse`.
840    reversed: bool,
841    /// `@| [..n]`.
842    limit: Option<u64>,
843    /// A fold by one key.
844    groups: bool,
845    /// The numeric column the plan sorts, folds or counts distinct
846    /// by, and the word of the construct.
847    keyed_by: Option<(Refusal, String)>,
848    /// The rows are records the query built.
849    record: bool,
850    /// The records are the rows' own properties (`| %`): a NULL
851    /// cell is left out of its row's record.
852    properties: bool,
853}
854
855struct Exporter {
856    arbor: QueryArbor,
857    notes: Vec<String>,
858    /// Pushdown mode: refuse every construct whose SQL semantics
859    /// are not provably identical to Quarb's (LIKE case folding,
860    /// truthiness, group/distinct/sort ordering).
861    strict: bool,
862    /// The target dialect for JSON-column-path pushdown; `None`
863    /// leaves such filters on the client-side graft.
864    dialect: Option<Dialect>,
865    from_table: String,
866    join_on_cols: Vec<String>,
867    join_table: Option<String>,
868    /// The correlation is `<=>*`: every witness, the SQL JOIN itself.
869    join_every: bool,
870    /// What the comparisons assume of column types (see
871    /// [`Pushdown::column_types`]).
872    column_types: ColumnTypes,
873    from_sql: String,
874    join_sql: Option<String>,
875    in_on: bool,
876    /// The order of a strict plan, for its driver.
877    order: PlanOrder,
878    aggregate: bool,
879    /// The adapter reads steps by key (the pushed-down WHERE filters
880    /// the table's listing only), so crosslinks and resolutions are
881    /// safe beside the prefilter.
882    keyed_hops: bool,
883    /// The names the driver's rows are marked under (`/tracks/* .t`)
884    /// and the names the joined expression files its witness under
885    /// (`<=> /albums/*[…] .al`): `$$.t::col` is the FROM table's
886    /// column, `$$.al::col` the joined table's.
887    from_marks: Vec<String>,
888    join_marks: Vec<String>,
889    /// The joined table's name in the statement where it is not its
890    /// own: the alias of a table joined to itself.
891    join_alias: Option<String>,
892    /// The sums and means over a column the plan writes: the mean
893    /// flag, the column's name, the column as the statement writes
894    /// it. See [`FloatSum`].
895    sums: Vec<(bool, String, String)>,
896}
897
898impl Exporter {
899    fn kids(&self, n: NodeId, kind: &str) -> Vec<NodeId> {
900        self.arbor
901            .children(n)
902            .into_iter()
903            .filter(|&c| self.arbor.name(c).as_deref() == Some(kind))
904            .collect()
905    }
906
907    fn kid(&self, n: NodeId, kind: &str) -> Option<NodeId> {
908        self.kids(n, kind).into_iter().next()
909    }
910
911    fn prop(&self, n: NodeId, key: &str) -> Option<Value> {
912        self.arbor.property(n, key)
913    }
914
915    fn prop_s(&self, n: NodeId, key: &str) -> String {
916        self.prop(n, key).map(|v| v.to_string()).unwrap_or_default()
917    }
918
919    fn kind(&self, n: NodeId) -> String {
920        self.arbor.name(n).unwrap_or_default()
921    }
922
923    /// Whether `n` is a bare `null` literal operand.
924    fn is_null_literal(&self, n: NodeId) -> bool {
925        self.kind(n) == "literal" && self.prop_s(n, "type") == "null"
926    }
927
928    /// The partial-pushdown analysis (see [`partial_pushdown`]).
929    fn partial(&mut self) -> Result<Partial, SqlError> {
930        let root = self.arbor.root();
931        let q = self
932            .kid(root, "query")
933            .ok_or_else(|| not_pushed(Refusal::Name, "empty query".into()))?;
934        // A correlation does not forbid the prefilter: the driver's
935        // own predicates restrict the driver's rows whatever the join
936        // then binds. What must not happen is the join (or anything
937        // else) reaching the filtered table a second time — a
938        // self-join would see only the filtered rows — and the
939        // whole-query gates below refuse exactly that: they walk the
940        // correlation entries too.
941        let branches = self.kids(q, "branch");
942        if branches.len() != 1 {
943            return Err(not_pushed(Refusal::Union, "a branch union".into()));
944        }
945        let (table, preds) = self.table_branch_lead(branches[0], self.keyed_hops)?;
946
947        // Whole-query gates.
948        let all = self.walk_all(root);
949        let mut table_mentions = 0;
950        for n in &all {
951            match self.kind(*n).as_str() {
952                "step" => {
953                    let axis = self.prop_s(*n, "axis");
954                    // A crosslink, a resolution or a value match may
955                    // land in (or search) the filtered table and would
956                    // see only its filtered rows: `=>` and the scoped
957                    // `<--` carry their scope as text, so the table
958                    // count below cannot see them.
959                    if matches!(axis.as_str(), "->" | "<-" | "--" | "-->" | "<--" | "=>") {
960                        // Where steps are read by key they are exact
961                        // beside the prefilter. A scope is a listing,
962                        // though: it is safe only when it plainly
963                        // lists another table (`/other/…`), and an
964                        // unscoped `=>` walks every table.
965                        let scope = self.prop_s(*n, "scope");
966                        let lists_other_table = scope
967                            .strip_prefix('/')
968                            .and_then(|rest| rest.split(['/', '[', '<']).next())
969                            .is_some_and(|first| {
970                                !first.is_empty()
971                                    && first != table
972                                    && first.chars().all(|c| c.is_alphanumeric() || c == '_')
973                            });
974                        let safe = self.keyed_hops
975                            && if scope.is_empty() {
976                                axis != "=>"
977                            } else {
978                                lists_other_table
979                            };
980                        if !safe {
981                            return Err(not_pushed(
982                                Refusal::Condition,
983                                "crosslink/resolution axes could reach \
984                                 the filtered table"
985                                    .into(),
986                            ));
987                        }
988                    }
989                    if self.prop_s(*n, "matcher") == table {
990                        table_mentions += 1;
991                    }
992                }
993                "projection" if self.prop_s(*n, "kind") != "property" => {
994                    return Err(not_pushed(
995                        Refusal::Condition,
996                        "metadata would observe the filtering \
997                         (:::n-rows, :index)"
998                            .into(),
999                    ));
1000                }
1001                _ => {}
1002            }
1003        }
1004        if table_mentions != 1 {
1005            return Err(not_pushed(
1006                Refusal::Condition,
1007                "the table is reached more than once".into(),
1008            ));
1009        }
1010
1011        // The leading run of expression predicates. Strict
1012        // translation first; a predicate the strict translator
1013        // refuses tries the relaxed prefilter ladder — superset
1014        // semantics: the fetched set may hold extra rows, because
1015        // the caller re-runs the ORIGINAL query, which re-applies
1016        // every predicate; it must never lose a matching row. An
1017        // untranslatable predicate is skipped, not fatal — a
1018        // shorter conjunction is still a superset filter. A
1019        // positional predicate (index/range) ends the run:
1020        // predicates after it filter a positionally-selected
1021        // subsequence.
1022        let mut conds = Vec::new();
1023        for p in preds {
1024            if self.prop_s(p, "kind") != "expr" {
1025                break;
1026            }
1027            match self.predicate_cond(p, None) {
1028                Ok(c) => conds.push(c),
1029                Err(_) => {
1030                    if let Some(c) = self.prefilter_cond(p) {
1031                        conds.push(c);
1032                    }
1033                }
1034            }
1035        }
1036        if conds.is_empty() {
1037            return Err(not_pushed(
1038                Refusal::Nothing,
1039                "no leading expression predicates to push".into(),
1040            ));
1041        }
1042        Ok(Partial {
1043            table,
1044            where_sql: conds.join(" AND "),
1045            column_types: self.column_types.clone(),
1046        })
1047    }
1048
1049    /// The relaxed prefilter for one refused expression predicate:
1050    /// the AND of whatever conjuncts translate, each a superset of
1051    /// the rows its Quarb counterpart keeps. None when nothing
1052    /// does — the predicate stays entirely on the engine.
1053    fn prefilter_cond(&mut self, p: NodeId) -> Option<String> {
1054        let parts: Vec<String> = self
1055            .arbor
1056            .children(p)
1057            .into_iter()
1058            .filter_map(|c| {
1059                self.pred_expr(c, None)
1060                    .ok()
1061                    .or_else(|| self.prefilter_expr(c))
1062            })
1063            .collect();
1064        if parts.is_empty() {
1065            None
1066        } else {
1067            Some(parts.join(" AND "))
1068        }
1069    }
1070
1071    /// One conjunct's superset prefilter — fixed-path JSON shapes
1072    /// only, per dialect. Every form here is proven to keep every
1073    /// row the graft would match (extras are fine; the engine
1074    /// re-checks). The proofs lean on three graft facts: absent
1075    /// paths match no comparison; `value_eq`/`value_cmp` coerce
1076    /// numeric-looking strings numerically; and Str-to-Str
1077    /// ordering is bytewise.
1078    fn prefilter_expr(&mut self, e: NodeId) -> Option<String> {
1079        // (the JSON forms are a dialect's; a conjunction, a
1080        // disjunction and a regex's literals need none)
1081        let dialect = self.dialect;
1082        match self.kind(e).as_str() {
1083            // A bare truthy path: existence (with `::`, value
1084            // truthiness — existence is its superset). The type
1085            // probe sees JSON null too, as the graft does.
1086            "path" => {
1087                // The property form (`/col/a::b`) is truthy on a
1088                // scalar and on a list with an item: the key is
1089                // present either way, so existence is its superset
1090                // too.
1091                let dialect = dialect?;
1092                let (col, path) = self
1093                    .json_path_loose(e)
1094                    .or_else(|| self.json_property_path(e))?;
1095                self.note_json_column(e, &col);
1096                let probe = json_type_probe(dialect, None, &col, &path)?;
1097                Some(json_valid_guard(
1098                    dialect,
1099                    None,
1100                    &col,
1101                    format!("{probe} IS NOT NULL"),
1102                ))
1103            }
1104            // A conjunction keeps what either side keeps and more
1105            // when one side is left out; a disjunction needs both.
1106            "and" | "or" => {
1107                let kind = self.kind(e);
1108                let sides: Vec<Option<String>> = self
1109                    .arbor
1110                    .children(e)
1111                    .into_iter()
1112                    .map(|c| {
1113                        self.pred_expr(c, None)
1114                            .ok()
1115                            .or_else(|| self.prefilter_expr(c))
1116                    })
1117                    .collect();
1118                if kind == "or" {
1119                    let sides: Option<Vec<String>> = sides.into_iter().collect();
1120                    return sides
1121                        .filter(|s| !s.is_empty())
1122                        .map(|s| format!("({})", s.join(" OR ")));
1123                }
1124                let sides: Vec<String> = sides.into_iter().flatten().collect();
1125                (!sides.is_empty()).then(|| format!("({})", sides.join(" AND ")))
1126            }
1127            "compare" => {
1128                let op = self.prop_s(e, "op");
1129                let kids = self.arbor.children(e);
1130                if kids.len() != 2 {
1131                    return None;
1132                }
1133                if op == "==" {
1134                    return self.regex_prefilter(dialect, &kids);
1135                }
1136                let dialect = dialect?;
1137                for (pi, li) in [(0usize, 1usize), (1, 0)] {
1138                    let Some((col, path)) = self.json_path(kids[pi]) else {
1139                        continue;
1140                    };
1141                    if self.kind(kids[li]) != "literal" {
1142                        continue;
1143                    }
1144                    let ty = self.prop_s(kids[li], "type");
1145                    if ty == "null" {
1146                        return None;
1147                    }
1148                    let raw = self
1149                        .prop(kids[li], "value")
1150                        .map(|v| v.to_string())
1151                        .unwrap_or_default();
1152                    let op = if pi == 0 { op.clone() } else { flip_op(&op) };
1153                    self.note_json_column(kids[pi], &col);
1154                    return self.json_prefilter(dialect, &col, &path, &op, &ty, &raw);
1155                }
1156                // The property form, `/col/a::b OP literal`.
1157                for (pi, li) in [(0usize, 1usize), (1, 0)] {
1158                    let Some((col, path)) = self.json_property_path(kids[pi]) else {
1159                        continue;
1160                    };
1161                    if self.kind(kids[li]) != "literal" {
1162                        continue;
1163                    }
1164                    let ty = self.prop_s(kids[li], "type");
1165                    if ty == "null" {
1166                        return None;
1167                    }
1168                    let raw = self
1169                        .prop(kids[li], "value")
1170                        .map(|v| v.to_string())
1171                        .unwrap_or_default();
1172                    let op = if pi == 0 { op.clone() } else { flip_op(&op) };
1173                    self.note_json_column(kids[pi], &col);
1174                    return self.json_property_prefilter(dialect, &col, &path, &op, &ty, &raw);
1175                }
1176                // SQL Server: a text column equal to a text literal.
1177                // The whole plan leaves the comparison to the engine
1178                // there (the collation folds case, accents and
1179                // trailing spaces), but what the collation calls
1180                // equal holds every row the engine calls equal: a
1181                // superset, which the engine checks again. A literal
1182                // that reads as a number is left out: the engine
1183                // meets it with '1.0' and '01' too.
1184                if dialect == Dialect::Mssql && op == "=" {
1185                    for (ci, li) in [(0usize, 1usize), (1, 0)] {
1186                        let plain = self.column_operand(kids[ci]).is_some()
1187                            && self.is_text_literal(kids[li])
1188                            && self.prop(kids[li], "value").is_some_and(|v| {
1189                                Value::Str(v.to_string()).numeric_reading().is_none()
1190                            });
1191                        if plain {
1192                            let l = self.operand(kids[ci], None).ok()?;
1193                            let r = self.operand(kids[li], None).ok()?;
1194                            return Some(format!("{l} = {r}"));
1195                        }
1196                    }
1197                }
1198                None
1199            }
1200            _ => None,
1201        }
1202    }
1203
1204    /// The superset prefilter for `::col == (/regex/)`: every literal
1205    /// the regex cannot match without is a substring of the text it
1206    /// matches, so the rows that hold them all are a superset of the
1207    /// rows the engine keeps; the engine runs the regex itself over
1208    /// what is fetched. A NULL holds no substring and matches no
1209    /// regex. The column is recorded as a text one, for the caller's
1210    /// catalog check: a number would be searched in the database's
1211    /// rendering of it, not the engine's.
1212    fn regex_prefilter(&mut self, dialect: Option<Dialect>, kids: &[NodeId]) -> Option<String> {
1213        if !self.is_text_literal(kids[1]) {
1214            return None;
1215        }
1216        let column = self.column_operand(kids[0])?;
1217        let pattern = self.prop(kids[1], "value")?.to_string();
1218        let literals = required_literals(&pattern);
1219        let l = self.operand(kids[0], None).ok()?;
1220        let conds: Vec<String> = literals
1221            .iter()
1222            .filter_map(|lit| contains_sql(dialect, &l, lit))
1223            .collect();
1224        if conds.is_empty() {
1225            return None;
1226        }
1227        if !self.column_types.text.contains(&column) {
1228            self.column_types.text.push(column);
1229        }
1230        Some(conds.join(" AND "))
1231    }
1232
1233    /// The superset prefilter for `/col/a::b OP literal`, the
1234    /// property form of a JSON path. It is [`json_prefilter`] with
1235    /// the two places where a property differs from a child's value:
1236    ///
1237    /// - an ARRAY of scalars is a list, and a list in a comparison
1238    ///   matches when any item does (`[::tags = 'a']` keeps
1239    ///   `{"tags": ["a", "b"]}`); the child form's `/tags::` has no
1240    ///   value there. So every form lets the arrays through for the
1241    ///   engine to decide;
1242    /// - `!=` keeps a row whose key is ABSENT (null is not the
1243    ///   literal), where the child form has no node and no thread.
1244    ///   Its floor is the existence of the node the property is
1245    ///   read on; on the column itself there is none (a cell that
1246    ///   is no JSON still answers a null property).
1247    ///
1248    /// [`json_prefilter`]: Self::json_prefilter
1249    fn json_property_prefilter(
1250        &self,
1251        d: Dialect,
1252        col: &str,
1253        path: &[String],
1254        op: &str,
1255        lit_ty: &str,
1256        raw: &str,
1257    ) -> Option<String> {
1258        if !matches!(op, "=" | "!=" | "<" | "<=" | ">" | ">=") {
1259            return None;
1260        }
1261        if op == "!=" {
1262            let owner = &path[..path.len() - 1];
1263            if owner.is_empty() {
1264                return None;
1265            }
1266            let probe = json_type_probe(d, None, col, owner)?;
1267            return Some(json_valid_guard(
1268                d,
1269                None,
1270                col,
1271                format!("{probe} IS NOT NULL"),
1272            ));
1273        }
1274        let ty = json_type_probe(d, None, col, path)?;
1275        let numeric_lit = raw.trim().parse::<f64>().is_ok_and(|f| f.is_finite())
1276            && (lit_ty != "text" || !raw.trim().is_empty());
1277        if numeric_lit {
1278            let num: f64 = raw.trim().parse().ok()?;
1279            if num.abs() >= 9_007_199_254_740_992.0 {
1280                return None;
1281            }
1282            let lit = raw.trim();
1283            match d {
1284                Dialect::Sqlite => {
1285                    let ex = json_extract(d, None, col, path);
1286                    Some(json_valid_guard(
1287                        d,
1288                        None,
1289                        col,
1290                        format!("({ex} {op} {lit} OR {ty} IN ('text', 'array'))"),
1291                    ))
1292                }
1293                Dialect::Postgres => {
1294                    let text = json_extract(d, None, col, path);
1295                    Some(format!(
1296                        "(CASE WHEN {ty} = 'number' THEN ({text})::float8 {op} {lit} \
1297                         WHEN {ty} IN ('string', 'array') THEN true ELSE false END)"
1298                    ))
1299                }
1300                Dialect::MySql | Dialect::Mssql | Dialect::Oracle => None,
1301            }
1302        } else if lit_ty == "text" {
1303            if raw.contains('\'') || raw.contains('\\') {
1304                return None;
1305            }
1306            match d {
1307                Dialect::Sqlite => {
1308                    let ex = json_extract(d, None, col, path);
1309                    Some(json_valid_guard(
1310                        d,
1311                        None,
1312                        col,
1313                        format!("({ex} {op} '{raw}' OR {ty} = 'array')"),
1314                    ))
1315                }
1316                // Equality alone: a string equals the literal or it
1317                // does not (a looser collation keeps more, never
1318                // fewer); ordering runs into collation, as in the
1319                // child form.
1320                Dialect::Postgres if op == "=" => {
1321                    let text = json_extract(d, None, col, path);
1322                    Some(format!(
1323                        "(CASE WHEN {ty} = 'string' THEN {text} = '{raw}' \
1324                         WHEN {ty} = 'array' THEN true ELSE false END)"
1325                    ))
1326                }
1327                _ => None,
1328            }
1329        } else {
1330            None
1331        }
1332    }
1333
1334    /// Build the dialect's superset prefilter for
1335    /// `json_path OP literal`.
1336    fn json_prefilter(
1337        &self,
1338        d: Dialect,
1339        col: &str,
1340        path: &[String],
1341        op: &str,
1342        lit_ty: &str,
1343        raw: &str,
1344    ) -> Option<String> {
1345        if !matches!(op, "=" | "!=" | "<" | "<=" | ">" | ">=") {
1346            return None;
1347        }
1348        // `!=` matches every present path whose value isn't the
1349        // literal — including JSON null and cross-typed values —
1350        // so its floor is existence.
1351        if op == "!=" {
1352            let probe = json_type_probe(d, None, col, path)?;
1353            return Some(json_valid_guard(
1354                d,
1355                None,
1356                col,
1357                format!("{probe} IS NOT NULL"),
1358            ));
1359        }
1360        let numeric_lit = raw.trim().parse::<f64>().is_ok_and(|f| f.is_finite())
1361            && (lit_ty != "text" || !raw.trim().is_empty());
1362        if numeric_lit {
1363            let num: f64 = raw.trim().parse().ok()?;
1364            // Past 2^53 the engines' exact integers and the
1365            // graft's f64 part ways at the boundary.
1366            if num.abs() >= 9_007_199_254_740_992.0 {
1367                return None;
1368            }
1369            let lit = raw.trim();
1370            match d {
1371                // Typed extract: JSON numbers compare numerically
1372                // (both sides parse the same source text to f64);
1373                // string values escape through the type probe —
1374                // the graft may coerce them, the server must not
1375                // drop them.
1376                Dialect::Sqlite => {
1377                    let ex = json_extract(d, None, col, path);
1378                    let ty = json_type_probe(d, None, col, path)?;
1379                    Some(json_valid_guard(
1380                        d,
1381                        None,
1382                        col,
1383                        format!("({ex} {op} {lit} OR {ty} = 'text')"),
1384                    ))
1385                }
1386                // float8 is the graft's f64; jsonb numbers print
1387                // exactly, so the cast parses the same decimal.
1388                // Strings escape; everything else can't match.
1389                Dialect::Postgres => {
1390                    let text = json_extract(d, None, col, path);
1391                    let ty = json_type_probe(d, None, col, path)?;
1392                    Some(format!(
1393                        "(CASE WHEN {ty} = 'number' THEN ({text})::float8 {op} {lit} \
1394                         WHEN {ty} = 'string' THEN true ELSE false END)"
1395                    ))
1396                }
1397                Dialect::MySql | Dialect::Mssql | Dialect::Oracle => None,
1398            }
1399        } else if lit_ty == "text" {
1400            if raw.contains('\'') || raw.contains('\\') {
1401                return None;
1402            }
1403            match d {
1404                // Typed extract again: string values order bytewise
1405                // on both sides; numbers and booleans sit below
1406                // text in SQLite's cross-type order, so `<` keeps
1407                // them (extras, re-checked) and `>` drops them
1408                // (the graft refuses to order Str against them).
1409                Dialect::Sqlite => {
1410                    let ex = json_extract(d, None, col, path);
1411                    Some(json_valid_guard(d, None, col, format!("{ex} {op} '{raw}'")))
1412                }
1413                // Text ordering elsewhere runs into collation; the
1414                // engine keeps those.
1415                _ => None,
1416            }
1417        } else {
1418            None
1419        }
1420    }
1421
1422    /// [`json_path`], loosened for existence: the projection may
1423    /// be absent (a bare structural path) as well as the bare
1424    /// `::`.
1425    /// Whether a JSON path under a column reads the column as JSON
1426    /// on the strength of its declaration alone: the column step
1427    /// (the first step) carries no `<json>` trait. Such a column is
1428    /// an assumption of the plan ([`ColumnTypes::json`]); with the
1429    /// trait the graft is declared on the step and holds on both
1430    /// roads.
1431    fn note_json_column(&mut self, o: NodeId, col: &str) {
1432        let declared_on_hop = self.kids(o, "step").first().is_some_and(|s| {
1433            self.kids(*s, "trait").iter().any(|t| {
1434                matches!(self.prop(*t, "alts"), Some(Value::List(alts))
1435                    if alts.iter().any(|a| matches!(a, Value::Str(x) if x == "json")))
1436            })
1437        });
1438        if !declared_on_hop && !self.column_types.json.iter().any(|c| c == col) {
1439            self.column_types.json.push(col.to_string());
1440        }
1441        // The child form (the bare `::`, or no projection): what the
1442        // property reading leaves empty.
1443        let keyed = self
1444            .kid(o, "projection")
1445            .is_some_and(|p| self.prop(p, "key").is_some());
1446        if !keyed {
1447            self.column_types.json_children = true;
1448        }
1449    }
1450
1451    fn json_path_loose(&self, o: NodeId) -> Option<(String, Vec<String>)> {
1452        if self.json_path(o).is_some() {
1453            return self.json_path(o);
1454        }
1455        if self.kind(o) != "path" || self.kid(o, "projection").is_some() {
1456            return None;
1457        }
1458        let steps = self.kids(o, "step");
1459        if steps.len() < 2 {
1460            return None;
1461        }
1462        let mut names = Vec::with_capacity(steps.len());
1463        for s in &steps {
1464            if self.prop_s(*s, "axis") != "/"
1465                || self.prop_s(*s, "matcher-kind") != "name"
1466                || self.kid(*s, "predicate").is_some()
1467            {
1468                return None;
1469            }
1470            let name = self.prop_s(*s, "matcher");
1471            let plain = !name.is_empty()
1472                && name
1473                    .chars()
1474                    .next()
1475                    .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
1476                && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_');
1477            if !plain {
1478                return None;
1479            }
1480            names.push(name);
1481        }
1482        let col = names.remove(0);
1483        Some((col, names))
1484    }
1485
1486    fn walk_all(&self, n: NodeId) -> Vec<NodeId> {
1487        let mut out = vec![n];
1488        let mut i = 0;
1489        while i < out.len() {
1490            out.extend(self.arbor.children(out[i]));
1491            i += 1;
1492        }
1493        out
1494    }
1495
1496    fn query(&mut self) -> Result<String, SqlError> {
1497        let root = self.arbor.root();
1498        let q = self
1499            .kid(root, "query")
1500            .ok_or_else(|| not_pushed(Refusal::Name, "empty query".into()))?;
1501        let mut sel = Select::default();
1502
1503        // Correlations: exactly one becomes the JOIN's left table.
1504        let corrs = self.kids(q, "query");
1505        let branches = self.kids(q, "branch");
1506        if branches.len() != 1 {
1507            return Err(not_pushed(
1508                Refusal::Union,
1509                "a branch union (SQL has UNION, but result shapes differ; export one branch)"
1510                    .into(),
1511            ));
1512        }
1513
1514        if corrs.len() > 1 {
1515            return Err(not_pushed(
1516                Refusal::Join,
1517                "more than one correlation context".into(),
1518            ));
1519        }
1520        if let Some(corr) = corrs.first() {
1521            // Driver-first: the query's own branch is the FROM
1522            // table (the driver); the correlation entry is the
1523            // joined table, whose `$$` equalities form the ON.
1524            let (ltable, lpreds) = self.table_branch(branches[0])?;
1525            let (rtable, rpreds) = self.table_branch(self.kids(*corr, "branch")[0])?;
1526            self.from_marks = self.row_marks(branches[0])?;
1527            self.join_marks = self.row_marks(self.kids(*corr, "branch")[0])?;
1528            // a joined expression with a pipeline of its own binds
1529            // what that pipeline leaves: not a row of its table
1530            if self.kid(*corr, "pipeline").is_some() {
1531                return Err(not_pushed(
1532                    Refusal::Join,
1533                    "a joined expression with a pipeline of its own".into(),
1534                ));
1535            }
1536            // The SQL renderings of the two table names (validated
1537            // or quoted); the raw names stay in the plan metadata,
1538            // which the driver matches against its catalog.
1539            let lsql = self.sql_ident(&ltable, "table name")?;
1540            let rtable_sql = self.sql_ident(&rtable, "table name")?;
1541            // A table joined to itself stands in the statement
1542            // twice, and one of the two needs a name of its own:
1543            // the joined side is aliased, and everything said of it
1544            // (the ON, the witness's columns, the second key of the
1545            // order) is said under the alias. (It was written under
1546            // the one name, `FROM employees JOIN employees ON
1547            // employees.manager_id = employees.id`, which every
1548            // database rejects as ambiguous: such a query always
1549            // fell back to the scan.)
1550            self.join_alias = (ltable == rtable).then(|| {
1551                if is_plain_ident(&rtable) {
1552                    format!("{rtable}_2")
1553                } else {
1554                    "joined_2".to_string()
1555                }
1556            });
1557            let rsql = self
1558                .join_alias
1559                .clone()
1560                .unwrap_or_else(|| rtable_sql.clone());
1561            self.from_sql = lsql.clone();
1562            self.join_sql = Some(rsql.clone());
1563            // The driver's own predicates are plain WHERE.
1564            let driver_conds: Vec<NodeId> = lpreds;
1565            // Split the joined expression's predicates: `$$`
1566            // equalities form the ON, the rest the WHERE.
1567            let mut on = Vec::new();
1568            let mut wheres = Vec::new();
1569            for p in rpreds {
1570                self.split_join_pred(p, &rsql, &mut on, &mut wheres)?;
1571            }
1572            // `<=>?` — the outer correlation keeps a driver row no
1573            // joined row admits, its witness null: a LEFT JOIN. The
1574            // joined expression's own predicates then select the
1575            // witness, so they belong to the ON; in a WHERE they
1576            // would discard the unmatched rows again.
1577            let outer = matches!(self.prop(*corr, "outer"), Some(Value::Bool(true)));
1578            if outer {
1579                on.append(&mut wheres);
1580            }
1581            sel.join_outer = outer;
1582            if on.is_empty() {
1583                return Err(not_pushed(
1584                    Refusal::Join,
1585                    "a correlation without a '$$' equality (no JOIN condition)".into(),
1586                ));
1587            }
1588            for p in driver_conds {
1589                let cond = self.predicate_cond(p, Some(&lsql.clone()))?;
1590                wheres.push(cond);
1591            }
1592            // `<=>*` — every witness: the JOIN as SQL means it, one
1593            // row per matching pair; the plain `<=>` binds one.
1594            self.join_every = matches!(self.prop(*corr, "every"), Some(Value::Bool(true)));
1595            if !self.join_every {
1596                self.notes.push(
1597                    "JOIN: Quarb's binding is existential (one row per FROM row, \
1598                     bound to its first witness); SQL multiplies rows when \
1599                     several joined rows match"
1600                        .to_string(),
1601                );
1602            }
1603            sel.from = lsql.clone();
1604            self.from_table = ltable;
1605            self.join_table = Some(rtable);
1606            // (no AS: Oracle takes a table alias without the word)
1607            let joined = match &self.join_alias {
1608                Some(alias) => format!("{rtable_sql} {alias}"),
1609                None => rtable_sql.clone(),
1610            };
1611            sel.join = Some((joined, on.join(" AND ")));
1612            sel.wheres = wheres;
1613            // A terminal projection on the driving branch is a
1614            // one-column select of the FROM table.
1615            let mut projected = None;
1616            if let Some(proj) = self.kid(branches[0], "projection") {
1617                let name = self.projection_col(proj)?;
1618                let col = self.sql_ident(&name, "column name")?;
1619                let col = format!("{lsql}.{col}");
1620                sel.select.push(col.clone());
1621                projected = Some((col, name));
1622            }
1623            if self
1624                .kid(self.kids(*corr, "branch")[0], "projection")
1625                .is_some()
1626            {
1627                return Err(not_pushed(
1628                    Refusal::Join,
1629                    "a projection on the joined expression (project the witness \
1630                     in the pipeline: '$$1::col')"
1631                        .into(),
1632                ));
1633            }
1634            self.pipeline(q, &mut sel, Some((&lsql, &rsql)), projected)?;
1635        } else {
1636            let (table, preds) = self.table_branch(branches[0])?;
1637            self.from_marks = self.row_marks(branches[0])?;
1638            sel.from = self.sql_ident(&table, "table name")?;
1639            self.from_table = table;
1640            for p in preds {
1641                let cond = self.predicate_cond(p, None)?;
1642                sel.wheres.push(cond);
1643            }
1644            // A terminal projection is a one-column select.
1645            let mut projected = None;
1646            if let Some(proj) = self.kid(branches[0], "projection") {
1647                let name = self.projection_col(proj)?;
1648                let col = self.sql_ident(&name, "column name")?;
1649                sel.select.push(col.clone());
1650                projected = Some((col, name));
1651            }
1652            self.pipeline(q, &mut sel, None, projected)?;
1653        }
1654        // A whole plan answers values: a column, a record, a
1655        // reduction. A query that ends on row nodes is answered by
1656        // their locators, which are the scan's to write (ruling
1657        // 2026-11-18); it was pushed as SELECT * and printed each
1658        // row as a record, where the scan printed `/books/1`. Its
1659        // conditions still go ahead of the scan through the partial
1660        // plan, and nothing the reader wrote is refused, so the
1661        // refusal carries no code.
1662        if self.strict && sel.select.is_empty() {
1663            return Err(not_pushed(
1664                Refusal::Nodes,
1665                "a selection of row nodes is answered by their locators".into(),
1666            ));
1667        }
1668        Ok(sel.render())
1669    }
1670
1671    /// A `/table/*[preds]` branch: the table name and the row-step
1672    /// predicate nodes.
1673    fn table_branch(&mut self, b: NodeId) -> Result<(String, Vec<NodeId>), SqlError> {
1674        self.table_branch_lead(b, false)
1675    }
1676
1677    /// [`table_branch`]; with `more`, the branch may go on after
1678    /// `/table/*[…]` (a step, a cell): the prefilter concerns its
1679    /// first two steps only, and the steps after them are held to
1680    /// the whole-query gates.
1681    fn table_branch_lead(
1682        &mut self,
1683        b: NodeId,
1684        more: bool,
1685    ) -> Result<(String, Vec<NodeId>), SqlError> {
1686        let steps = self.kids(b, "step");
1687        if steps.len() < 2 || (steps.len() > 2 && !more) {
1688            return Err(not_pushed(
1689                Refusal::Walk,
1690                "navigation beyond /table/* (SQL sees tables and rows)".into(),
1691            ));
1692        }
1693        let (t, rows) = (steps[0], steps[1]);
1694        if self.prop_s(t, "axis") != "/"
1695            || self.prop_s(t, "matcher-kind") != "name"
1696            || self.prop_s(rows, "axis") != "/"
1697            || self.prop_s(rows, "matcher-kind") != "any"
1698        {
1699            return Err(not_pushed(
1700                Refusal::Walk,
1701                "navigation beyond /table/* (SQL sees tables and rows)".into(),
1702            ));
1703        }
1704        if self.kid(t, "predicate").is_some() {
1705            return Err(not_pushed(
1706                Refusal::Walk,
1707                "a predicate on the table step".into(),
1708            ));
1709        }
1710        Ok((self.prop_s(t, "matcher"), self.kids(rows, "predicate")))
1711    }
1712
1713    /// The names a `/table/*` branch marks its rows under: the marks
1714    /// standing after the row step. A mark anywhere else names a
1715    /// node SQL has no row for (the table) and is not translated.
1716    fn row_marks(&self, b: NodeId) -> Result<Vec<String>, SqlError> {
1717        let kids = self.arbor.children(b);
1718        let rows = self.kids(b, "step").get(1).copied();
1719        let after = rows
1720            .and_then(|r| kids.iter().position(|k| *k == r))
1721            .unwrap_or(kids.len());
1722        let mut names = Vec::new();
1723        for (i, k) in kids.iter().enumerate() {
1724            if self.kind(*k) != "mark" {
1725                continue;
1726            }
1727            if i < after {
1728                return Err(not_pushed(
1729                    Refusal::Walk,
1730                    "a mark before the rows (SQL sees tables and rows)".into(),
1731                ));
1732            }
1733            // (a mark with no name is read by position alone, and
1734            // a read by position is not translated)
1735            if let Some(Value::Str(n)) = self.prop(*k, "name") {
1736                names.push(n);
1737            }
1738        }
1739        Ok(names)
1740    }
1741
1742    /// A path operand read through the capsa's node side (`$$.al`,
1743    /// `$$.2`, `((@))`): the mark's name where the anchor is one
1744    /// mark by name, and whether it is any mark anchor at all.
1745    fn mark_anchor(&self, o: NodeId) -> (Option<String>, bool) {
1746        let named = match self.prop(o, "mark") {
1747            Some(Value::Str(n)) => Some(n),
1748            _ => None,
1749        };
1750        let any = named.is_some()
1751            || [
1752                "mark-index",
1753                "mark-top",
1754                "mark-back",
1755                "marks-all",
1756                "marks-name",
1757                "edge-end",
1758                "node-id",
1759            ]
1760            .iter()
1761            .any(|k| self.prop(o, k).is_some());
1762        (named, any)
1763    }
1764
1765    /// One row predicate as a WHERE condition (qualify columns with
1766    /// `qualifier` when joining).
1767    fn predicate_cond(&mut self, p: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
1768        if self.prop_s(p, "kind") != "expr" {
1769            return Err(not_pushed(
1770                Refusal::Take,
1771                "a positional predicate on rows (SQL rows are unordered; ORDER BY + LIMIT)".into(),
1772            ));
1773        }
1774        let parts: Vec<String> = self
1775            .arbor
1776            .children(p)
1777            .into_iter()
1778            .map(|c| self.pred_expr(c, qual))
1779            .collect::<Result<_, _>>()?;
1780        Ok(parts.join(" AND "))
1781    }
1782
1783    /// A condition beneath a negation inside a join's bracket, in
1784    /// SQL whose three truth values are the engine's there. The
1785    /// engine reads a key comparison (one that reads the other side
1786    /// of the join) as unknown for a null on either side and every
1787    /// other test as true or false; unknown passes through `!`,
1788    /// yields to a true under `||` and to a false under `&&`, and
1789    /// only a true admits the candidate. SQL's AND, OR and NOT keep
1790    /// the same table over TRUE, FALSE and UNKNOWN, and its
1791    /// comparison is unknown for a NULL as the key comparison is. So
1792    /// a key comparison is written as it stands, AND / OR / NOT as
1793    /// themselves, and every other test is made two-valued (`CASE
1794    /// WHEN … THEN 1 ELSE 0 END = 1`), since a NOT above it would
1795    /// otherwise keep SQL's UNKNOWN where the engine has a plain
1796    /// false to turn.
1797    fn join_cond3(&mut self, e: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
1798        let kids = |ex: &mut Self, e: NodeId| -> Result<Vec<String>, SqlError> {
1799            ex.arbor
1800                .children(e)
1801                .into_iter()
1802                .map(|c| ex.join_cond3(c, qual))
1803                .collect()
1804        };
1805        match self.kind(e).as_str() {
1806            kind @ ("and" | "or") => {
1807                let op = kind.to_uppercase();
1808                Ok(format!("({})", kids(self, e)?.join(&format!(" {op} "))))
1809            }
1810            "not" => Ok(format!("NOT ({})", kids(self, e)?.join(" AND "))),
1811            "parens" => Ok(format!("({})", kids(self, e)?.join(" AND "))),
1812            "compare" if self.subtree_has_driver(e) => self.pred_expr(e, qual),
1813            _ => Ok(format!(
1814                "(CASE WHEN {} THEN 1 ELSE 0 END = 1)",
1815                self.pred_expr(e, qual)?
1816            )),
1817        }
1818    }
1819
1820    fn pred_expr(&mut self, e: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
1821        match self.kind(e).as_str() {
1822            "and" | "or" => {
1823                let op = self.kind(e).to_uppercase();
1824                let kids: Vec<String> = self
1825                    .arbor
1826                    .children(e)
1827                    .into_iter()
1828                    .map(|c| self.pred_expr(c, qual))
1829                    .collect::<Result<_, _>>()?;
1830                Ok(format!("({})", kids.join(&format!(" {op} "))))
1831            }
1832            "not" => {
1833                // SQL's `NOT` propagates UNKNOWN: `NOT (x = 5)` is
1834                // UNKNOWN for a NULL `x` and drops the row, but Quarb's
1835                // negation keeps it (the inner `value_eq` is false, so
1836                // its negation is true). A pushed plan says the
1837                // negation the way that keeps the row on every
1838                // engine: the condition as a CASE, which takes
1839                // UNKNOWN to its ELSE. Inside a join's bracket the
1840                // engine's own logic is three-valued for the key
1841                // comparisons and two-valued for the rest: a
1842                // negation with no key comparison beneath it is the
1843                // CASE form like any other, and one over a key
1844                // comparison is written in SQL's own three values
1845                // ([`join_cond3`]). (0.70 left both to the engine.)
1846                //
1847                // [`join_cond3`]: Self::join_cond3
1848                if self.strict && self.in_on && self.subtree_has_driver(e) {
1849                    let inner: Vec<String> = self
1850                        .arbor
1851                        .children(e)
1852                        .into_iter()
1853                        .map(|c| self.join_cond3(c, qual))
1854                        .collect::<Result<_, _>>()?;
1855                    return Ok(format!("NOT ({})", inner.join(" AND ")));
1856                }
1857                let inner: Vec<String> = self
1858                    .arbor
1859                    .children(e)
1860                    .into_iter()
1861                    .map(|c| self.pred_expr(c, qual))
1862                    .collect::<Result<_, _>>()?;
1863                if self.strict {
1864                    return Ok(format!(
1865                        "(CASE WHEN {} THEN 0 ELSE 1 END = 1)",
1866                        inner.join(" AND ")
1867                    ));
1868                }
1869                Ok(format!("NOT ({})", inner.join(" AND ")))
1870            }
1871            "parens" => {
1872                let inner: Vec<String> = self
1873                    .arbor
1874                    .children(e)
1875                    .into_iter()
1876                    .map(|c| self.pred_expr(c, qual))
1877                    .collect::<Result<_, _>>()?;
1878                Ok(format!("({})", inner.join(" AND ")))
1879            }
1880            "compare" => {
1881                let op = self.prop_s(e, "op");
1882                let kids = self.arbor.children(e);
1883                // A comparison against a bare `null` literal. Quarb's
1884                // `value_eq` treats NULL as an ordinary value
1885                // (`value_eq(NULL, NULL)` is true, `value_eq(NULL, x)`
1886                // false), so `= null` keeps exactly the NULL rows and
1887                // `!= null` the non-NULL rows. SQL's `= NULL` / `<>
1888                // NULL` are always UNKNOWN and drop every row; the
1889                // `IS [NOT] NULL` forms are provably identical (and
1890                // portable across every target dialect).
1891                if matches!(op.as_str(), "=" | "!=")
1892                    && (self.is_null_literal(kids[0]) || self.is_null_literal(kids[1]))
1893                {
1894                    let other = if self.is_null_literal(kids[0]) {
1895                        kids[1]
1896                    } else {
1897                        kids[0]
1898                    };
1899                    // A comparison with the literal null is the
1900                    // presence test wherever it stands, inside a join
1901                    // condition too (ruling 2026-11-18: no key
1902                    // comparison, true or false): IS [NOT] NULL. In
1903                    // 0.69 the engine left it unknown there and the
1904                    // plan was refused.
1905                    let col = self.operand(other, qual)?;
1906                    return Ok(if op == "=" {
1907                        format!("{col} IS NULL")
1908                    } else {
1909                        format!("{col} IS NOT NULL")
1910                    });
1911                }
1912                // JSON-column-path pushdown: `[/col/a/b:: = 'lit']`
1913                // navigates into a JSON column and compares a fixed
1914                // path to a string literal. Only this exact shape —
1915                // fixed object path, string equality, and a literal
1916                // that does NOT look numeric — is provably identical
1917                // to the client-side graft (each engine's scalar
1918                // extractor unquotes to text, and an absent path or
1919                // non-string value excludes the row on both sides,
1920                // matching Quarb's `value_eq` on non-numeric text).
1921                // Numeric casts, `!=`, wildcards, and deeper
1922                // predicates fall through to the ordinary operand
1923                // logic below, which refuses the navigation; the
1924                // partial-pushdown prefilter ladder picks those up
1925                // with superset semantics. Enabled only when a
1926                // dialect is set.
1927                if op == "="
1928                    && let Some(dialect) = self.dialect
1929                {
1930                    for (pi, li) in [(0usize, 1usize), (1, 0)] {
1931                        if self.is_text_literal(kids[li])
1932                            && let Some((col, path)) = self.json_path(kids[pi])
1933                        {
1934                            self.note_json_column(kids[pi], &col);
1935                            // Quarb's value_eq compares numeric-looking
1936                            // strings numerically — '150' matches the
1937                            // JSON number 150, and the string "150.0".
1938                            // No engine's text extractor coerces that
1939                            // way, so the pushed compare would drop
1940                            // rows the graft keeps. Refuse; the
1941                            // prefilter ladder handles it as a
1942                            // superset.
1943                            let raw = self
1944                                .prop(kids[li], "value")
1945                                .map(|v| v.to_string())
1946                                .unwrap_or_default();
1947                            if raw.trim().parse::<f64>().is_ok() {
1948                                return Err(mismatch(
1949                                    Refusal::Type,
1950                                    "a numeric-looking text literal against a \
1951                                     JSON path compares numerically in Quarb \
1952                                     (value coercion); no SQL extractor \
1953                                     matches that"
1954                                        .into(),
1955                                ));
1956                            }
1957                            // An extractor answers a container as
1958                            // its JSON text (SQLite `[1,2]`,
1959                            // PostgreSQL `[1, 2]`), so a literal that
1960                            // is such a text equalled the array or
1961                            // the object itself, which has no value
1962                            // in the graft: the push kept a row the
1963                            // scan drops. No scalar's text opens
1964                            // with a bracket or a brace that a
1965                            // container's does not, so such a
1966                            // literal rides the prefilter.
1967                            if raw.starts_with('[') || raw.starts_with('{') {
1968                                return Err(mismatch(
1969                                    Refusal::Type,
1970                                    "a text literal that opens like a JSON container \
1971                                     could equal an array's or an object's own text \
1972                                     in the extractor, which has no value in Quarb"
1973                                        .into(),
1974                                ));
1975                            }
1976                            let lit = self.operand(kids[li], qual)?;
1977                            let extract = json_extract(dialect, qual, &col, &path);
1978                            return Ok(json_valid_guard(
1979                                dialect,
1980                                qual,
1981                                &col,
1982                                format!("{extract} = {lit}"),
1983                            ));
1984                        }
1985                    }
1986                    // The property form, `[/col/a::b = 'lit']`: the
1987                    // same question on a scalar, and on an array of
1988                    // scalars the list, which equals the literal when
1989                    // any item does (an array that holds a container
1990                    // is no property). SQLite says both in one
1991                    // condition, exactly: the scalar branch by the
1992                    // value's type and text, the array branch by
1993                    // `json_each`. The other engines' array branches
1994                    // are not verified live, so there the shape rides
1995                    // the prefilter (PostgreSQL) or the scan.
1996                    for (pi, li) in [(0usize, 1usize), (1, 0)] {
1997                        if self.is_text_literal(kids[li])
1998                            && let Some((col, path)) = self.json_property_path(kids[pi])
1999                        {
2000                            self.note_json_column(kids[pi], &col);
2001                            let raw = self
2002                                .prop(kids[li], "value")
2003                                .map(|v| v.to_string())
2004                                .unwrap_or_default();
2005                            if raw.trim().parse::<f64>().is_ok() {
2006                                return Err(mismatch(
2007                                    Refusal::Type,
2008                                    "a numeric-looking text literal against a \
2009                                     JSON path compares numerically in Quarb \
2010                                     (value coercion); no SQL extractor \
2011                                     matches that"
2012                                        .into(),
2013                                ));
2014                            }
2015                            // `json_each` names its own columns; an
2016                            // unqualified JSON column of one of those
2017                            // names would read as them inside it.
2018                            let shadowed = qual.is_none()
2019                                && JSON_EACH_COLUMNS.contains(&col.to_ascii_lowercase().as_str());
2020                            if dialect != Dialect::Sqlite || shadowed {
2021                                return Err(mismatch(
2022                                    Refusal::Type,
2023                                    "a property of a JSON value may be a list (an \
2024                                     array of scalars equals the literal when any \
2025                                     item does); only SQLite's condition carries \
2026                                     that branch whole; this shape rides the \
2027                                     partial prefilter"
2028                                        .into(),
2029                                ));
2030                            }
2031                            let lit = self.operand(kids[li], qual)?;
2032                            let qcol = match qual {
2033                                Some(q) => format!("{q}.{col}"),
2034                                None => col.clone(),
2035                            };
2036                            let at = format!("'$.{}'", path.join("."));
2037                            let ty = format!("json_type({qcol}, {at})");
2038                            let ex = json_extract(dialect, qual, &col, &path);
2039                            return Ok(json_valid_guard(
2040                                dialect,
2041                                qual,
2042                                &col,
2043                                format!(
2044                                    "(({ty} = 'text' AND {ex} = {lit}) OR ({ty} = 'array' \
2045                                     AND EXISTS (SELECT 1 FROM json_each({qcol}, {at}) \
2046                                     WHERE json_each.type = 'text' AND json_each.value = {lit}) \
2047                                     AND NOT EXISTS (SELECT 1 FROM json_each({qcol}, {at}) \
2048                                     WHERE json_each.type IN ('array', 'object'))))"
2049                                ),
2050                            ));
2051                        }
2052                    }
2053                }
2054                // Any other JSON-path comparison: name the real
2055                // reason, not the generic flat-rows refusal — only
2056                // fixed-path string equality is provably identical
2057                // (Quarb coerces numeric-looking values; no
2058                // engine's extractor matches that). The partial
2059                // prefilter ladder covers these shapes.
2060                if self.dialect.is_some() {
2061                    for side in [kids[0], kids[1]] {
2062                        if self.json_path(side).is_some()
2063                            || self.json_path_loose(side).is_some()
2064                            || self.json_property_path(side).is_some()
2065                        {
2066                            return Err(mismatch(
2067                                Refusal::Type,
2068                                "a JSON-path comparison pushes whole only as \
2069                                 fixed-path string equality (Quarb coerces \
2070                                 numeric-looking values; no extractor matches \
2071                                 that); this shape rides the partial prefilter"
2072                                    .into(),
2073                            ));
2074                        }
2075                    }
2076                }
2077                // Quarb compares by value; SQL by the column's type.
2078                // A column against a numeric literal is the scan's
2079                // comparison only on a numeric-typed column, and
2080                // against a text literal only on a column that is
2081                // not (MySQL reads `n = 'abc'` as `n = 0`): the
2082                // catalog decides, so both are recorded for the
2083                // caller. A numeric-looking TEXT literal meets
2084                // numbers as a number in Quarb and compares as text
2085                // in SQL, on every engine, so it does not push. A
2086                // regex is no value: its refusal below is the
2087                // pattern's, whatever its text reads as.
2088                if self.strict && !matches!(op.as_str(), "==" | "!==") {
2089                    // Two values of the row compared with each other
2090                    // (`[::a = ::b]`, a join's `::album_id =
2091                    // $$0::id`, `[::n + 1 > ::m]`): the engine
2092                    // compares by value, so two texts that read as
2093                    // numbers compare as numbers ('1.0' equals '1',
2094                    // '10' is above '9'), and SQL compares two text
2095                    // columns as texts. The two agree where every
2096                    // column compared is numeric: the catalog
2097                    // decides. (They were pushed unchecked: over
2098                    // text columns the plan answered other rows.)
2099                    let value =
2100                        |k: NodeId| matches!(self.kind(k).as_str(), "path" | "context" | "arith");
2101                    if value(kids[0]) && value(kids[1]) {
2102                        for k in [kids[0], kids[1]] {
2103                            let column = self.column_operand(k);
2104                            self.assume_numeric(column);
2105                        }
2106                        self.column_types.columns_compared = true;
2107                    }
2108                    // A text literal against anything but a plain
2109                    // column: a computed value is a number or
2110                    // nothing, and SQL orders a number below any
2111                    // text where the engine says the two never meet.
2112                    for (ci, li) in [(0usize, 1usize), (1, 0)] {
2113                        if self.is_text_literal(kids[li]) && self.kind(kids[ci]) == "arith" {
2114                            return Err(mismatch(
2115                                Refusal::Type,
2116                                "a text literal compared with a computed value (SQL orders a \
2117                                 number below every text; to the engine the two never meet)"
2118                                    .into(),
2119                            ));
2120                        }
2121                    }
2122                    for (ci, li) in [(0usize, 1usize), (1, 0)] {
2123                        let Some(column) = self.column_operand(kids[ci]) else {
2124                            continue;
2125                        };
2126                        if self.kind(kids[li]) != "literal" {
2127                            continue;
2128                        }
2129                        let numeric = self
2130                            .prop(kids[li], "value")
2131                            .is_some_and(|v| Value::Str(v.to_string()).numeric_reading().is_some());
2132                        let list = match (self.prop_s(kids[li], "type").as_str(), numeric) {
2133                            ("null", _) => continue,
2134                            ("text", true) => {
2135                                return Err(mismatch(
2136                                    Refusal::Type,
2137                                    "a numeric-looking text literal compares \
2138                                     numerically in Quarb (value coercion); SQL \
2139                                     compares it as text"
2140                                        .into(),
2141                                ));
2142                            }
2143                            ("text", false) => &mut self.column_types.text,
2144                            (_, true) => &mut self.column_types.numeric,
2145                            (_, false) => continue,
2146                        };
2147                        if !list.contains(&column) {
2148                            list.push(column);
2149                        }
2150                    }
2151                }
2152                let l = self.operand(kids[0], qual)?;
2153                // Ruling #33: a pattern literal on `=` pushes as
2154                // LIKE — a `%` per star, each literal segment
2155                // escaped, in order. Anchoring matches exactly:
2156                // LIKE anchors both ends, a flanking star lifts
2157                // one. The case-folding caveat is `*=`'s; `!=`
2158                // with a pattern rides the scan (NOT LIKE drops
2159                // the NULL rows Quarb keeps).
2160                if self.kind(kids[1]) == "pattern" {
2161                    if op.as_str() != "=" {
2162                        return Err(not_pushed(
2163                            Refusal::Pattern,
2164                            format!("a pattern literal on '{op}' (only '=' pushes)"),
2165                        ));
2166                    }
2167                    // PostgreSQL's LIKE is case-sensitive under its
2168                    // deterministic collations — provably what the
2169                    // pattern test does — so the strict push is
2170                    // sound there (and a prefix pattern is
2171                    // index-sargable server-side). Everywhere else
2172                    // case folding is ambient state (SQLite pragma,
2173                    // MySQL/MSSQL collation), so strict refuses.
2174                    if self.strict && self.dialect != Some(Dialect::Postgres) {
2175                        return Err(mismatch(
2176                            Refusal::Pattern,
2177                            "LIKE case folding differs per engine \
2178                             (provably case-sensitive only on PostgreSQL)"
2179                                .into(),
2180                        ));
2181                    }
2182                    if !self.strict {
2183                        self.notes.push(
2184                            "pattern → LIKE: Quarb's pattern test is \
2185                             case-sensitive; LIKE folds case on \
2186                             SQLite/MySQL but not PostgreSQL"
2187                                .to_string(),
2188                        );
2189                    }
2190                    // LIKE is the pattern's test over a column whose
2191                    // text PostgreSQL compares as it stands (a
2192                    // `char(n)` is read without its padding there, a
2193                    // `citext` without its case): the catalog says
2194                    let column = self.column_operand(kids[0]);
2195                    self.assume_text(column);
2196                    let mut like = String::new();
2197                    for kid in self.arbor.children(kids[1]) {
2198                        match self.kind(kid).as_str() {
2199                            "star" => like.push('%'),
2200                            _ => {
2201                                let raw = self
2202                                    .prop(kid, "value")
2203                                    .map(|v| v.to_string())
2204                                    .unwrap_or_default();
2205                                like.push_str(
2206                                    &raw.replace('\\', "\\\\")
2207                                        .replace('%', "\\%")
2208                                        .replace('_', "\\_")
2209                                        .replace('\'', "''"),
2210                                );
2211                            }
2212                        }
2213                    }
2214                    return Ok(format!("{l} LIKE '{like}' ESCAPE '\\'"));
2215                }
2216                let r = self.operand(kids[1], qual)?;
2217                // Which sides can be NULL: every side that is no
2218                // literal. And whether this is a key comparison of a
2219                // join condition, which the engine leaves unknown for
2220                // a null on either side, as SQL does: there the plain
2221                // operators are the engine's own.
2222                let nullable: Vec<&str> = [(kids[0], l.as_str()), (kids[1], r.as_str())]
2223                    .into_iter()
2224                    .filter(|(k, _)| self.kind(*k) != "literal")
2225                    .map(|(_, sql)| sql)
2226                    .collect();
2227                let keyed = self.in_on
2228                    && (self.subtree_has_driver(kids[0]) || self.subtree_has_driver(kids[1]));
2229                // A text column against a text literal. The engine
2230                // compares two texts by their characters: case,
2231                // accents and trailing spaces all tell, and an order
2232                // is the order of their UTF-8 bytes. A database
2233                // compares under the column's collation, and what
2234                // that is differs by engine:
2235                // - MySQL and MariaDB fold case and accents and pad
2236                //   with spaces by default ('holst' met 'Holst',
2237                //   'Holst ' and 'hölst'), so the comparison is
2238                //   written over the UTF-8 bytes of both sides, with
2239                //   the collation's own equality kept beside an `=`
2240                //   for the index it can use;
2241                // - PostgreSQL's `=` is exact under its deterministic
2242                //   collations and its order is the collation's: an
2243                //   order is written under "C", the order of bytes;
2244                // - SQL Server folds case by default and no server
2245                //   stood here to hold another form against: the
2246                //   comparison stays with the engine there (an `=`
2247                //   still narrows the fetch, through the partial
2248                //   plan);
2249                // - SQLite compares bytes unless the column is
2250                //   declared under a collation, which its catalog
2251                //   reports; DuckDB compares bytes.
2252                let text_pair = self.strict
2253                    && ((self.is_text_literal(kids[1]) && self.column_operand(kids[0]).is_some())
2254                        || (self.is_text_literal(kids[0])
2255                            && self.column_operand(kids[1]).is_some()));
2256                if text_pair && matches!(op.as_str(), "=" | "!=" | "<" | "<=" | ">" | ">=") {
2257                    match self.dialect {
2258                        Some(Dialect::Mssql) => {
2259                            return Err(mismatch(
2260                                Refusal::Type,
2261                                "a text comparison follows the database's collation on SQL \
2262                                 Server (case, accents and trailing spaces), where the engine \
2263                                 compares the characters"
2264                                    .into(),
2265                            ));
2266                        }
2267                        Some(Dialect::MySql) => {
2268                            let bytes = |s: &str| format!("BINARY CONVERT({s} USING utf8mb4)");
2269                            let (bl, br) = (bytes(&l), bytes(&r));
2270                            return Ok(match op.as_str() {
2271                                "=" => format!("({l} = {r} AND {bl} = {br})"),
2272                                // (a join's key comparison stays
2273                                // unknown for a null, as SQL's is)
2274                                "!=" if keyed => format!("{bl} <> {br}"),
2275                                "!=" => {
2276                                    let column = nullable.first().copied().unwrap_or(l.as_str());
2277                                    format!("({bl} <> {br} OR {column} IS NULL)")
2278                                }
2279                                order => format!("{bl} {order} {br}"),
2280                            });
2281                        }
2282                        Some(Dialect::Postgres) if !matches!(op.as_str(), "=" | "!=") => {
2283                            return Ok(format!("{l} {op} {r} COLLATE \"C\""));
2284                        }
2285                        _ => {}
2286                    }
2287                }
2288                Ok(match op.as_str() {
2289                    // Two NULLs are equal in Quarb (`value_eq`), and
2290                    // SQL's `=` is UNKNOWN for them: an equality of
2291                    // two columns says the second case too. (It was
2292                    // pushed as the bare `a = b`, which dropped the
2293                    // row where both are NULL.)
2294                    "=" if self.strict && !keyed && nullable.len() == 2 => {
2295                        format!("({l} = {r} OR ({l} IS NULL AND {r} IS NULL))")
2296                    }
2297                    "=" => format!("{l} = {r}"),
2298                    // Quarb's `!=` holds for a NULL against a value
2299                    // and fails for two NULLs; SQL's `<>` is UNKNOWN
2300                    // for either. The plan adds the cases `<>` leaves
2301                    // out, in SQL every engine reads.
2302                    "!=" if self.strict && !keyed => match nullable.as_slice() {
2303                        [] => format!("{l} <> {r}"),
2304                        [one] => format!("({l} <> {r} OR {one} IS NULL)"),
2305                        _ => format!(
2306                            "({l} <> {r} OR ({l} IS NULL AND {r} IS NOT NULL) OR ({l} IS NOT \
2307                             NULL AND {r} IS NULL))"
2308                        ),
2309                    },
2310                    "!=" if self.strict => format!("{l} <> {r}"),
2311                    "!=" => {
2312                        // The display translation keeps `<>` and
2313                        // notes the divergence.
2314                        self.notes.push(
2315                            "'!=' → '<>': Quarb keeps rows whose column is NULL; \
2316                             SQL's '<>' drops them (use '!= null' for IS NOT NULL)"
2317                                .to_string(),
2318                        );
2319                        format!("{l} <> {r}")
2320                    }
2321                    "<" | "<=" | ">" | ">=" => format!("{l} {op} {r}"),
2322                    // SQLite's instr is a substring test by bytes,
2323                    // case and all, as `*=` is; the column is a text
2324                    // one (recorded above with the literal: a number
2325                    // would be searched in SQLite's rendering of it,
2326                    // not the engine's). A NULL has no substring on
2327                    // either side.
2328                    "*=" if self.strict
2329                        && self.dialect == Some(Dialect::Sqlite)
2330                        && self.is_text_literal(kids[1])
2331                        && self.column_operand(kids[0]).is_some() =>
2332                    {
2333                        format!("instr({l}, {r}) > 0")
2334                    }
2335                    "*=" => {
2336                        // The same PostgreSQL rung as the pattern
2337                        // literal above: LIKE is provably
2338                        // case-sensitive there, so `*=` pushes
2339                        // strict too.
2340                        if self.strict && self.dialect != Some(Dialect::Postgres) {
2341                            return Err(mismatch(
2342                                Refusal::Pattern,
2343                                "LIKE case folding differs per engine \
2344                                 (a substring test is pushed on SQLite as instr and on \
2345                                 PostgreSQL as LIKE)"
2346                                    .into(),
2347                            ));
2348                        }
2349                        // The pattern must be a text literal: a
2350                        // column or computed operand holds a value,
2351                        // not a pattern, and SQL LIKE cannot express
2352                        // "contains that value" portably.
2353                        if !self.is_text_literal(kids[1]) {
2354                            return Err(not_pushed(
2355                                Refusal::Pattern,
2356                                "'*=' with a non-literal pattern".into(),
2357                            ));
2358                        }
2359                        self.notes.push(
2360                            "*= → LIKE: Quarb's substring test is case-sensitive; LIKE \
2361                             folds case on SQLite/MySQL but not PostgreSQL"
2362                                .to_string(),
2363                        );
2364                        // Escape LIKE's metacharacters, then quote.
2365                        // The explicit ESCAPE makes '\' the escape
2366                        // everywhere — SQLite, MSSQL, and Oracle
2367                        // have no default escape character.
2368                        let raw = self
2369                            .prop(kids[1], "value")
2370                            .map(|v| v.to_string())
2371                            .unwrap_or_default();
2372                        let pat = raw
2373                            .replace('\\', "\\\\")
2374                            .replace('%', "\\%")
2375                            .replace('_', "\\_")
2376                            .replace('\'', "''");
2377                        format!("{l} LIKE '%{pat}%' ESCAPE '\\'")
2378                    }
2379                    "==" | "!==" | "=~" | "!~" => {
2380                        return Err(mismatch(
2381                            Refusal::Pattern,
2382                            "regex matching means a different REGEXP engine per \
2383                             backend; regexes run engine-side"
2384                                .into(),
2385                        ));
2386                    }
2387                    other => {
2388                        return Err(not_pushed(
2389                            Refusal::Operator,
2390                            format!("the '{other}' comparison"),
2391                        ));
2392                    }
2393                })
2394            }
2395            // A bare truthy operand.
2396            _ => {
2397                if self.strict {
2398                    return Err(mismatch(
2399                        Refusal::Type,
2400                        "truthiness diverges (0 and '' are falsy in Quarb)".into(),
2401                    ));
2402                }
2403                self.notes.push(
2404                    "truthiness: '[::c]' exports as IS NOT NULL, but Quarb also treats \
2405                     0 and '' as falsy"
2406                        .to_string(),
2407                );
2408                Ok(format!("{} IS NOT NULL", self.operand(e, qual)?))
2409            }
2410        }
2411    }
2412
2413    fn operand(&mut self, o: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
2414        match self.kind(o).as_str() {
2415            "literal" => {
2416                let v = self.prop(o, "value").unwrap_or(Value::Null);
2417                match self.prop_s(o, "type").as_str() {
2418                    "text" => {
2419                        let s = v.to_string();
2420                        // No single escaping is portable across the
2421                        // pushdown's target dialects: MySQL (default
2422                        // sql_mode) and BigQuery read `\` as an escape
2423                        // while SQLite/PostgreSQL/DuckDB take it
2424                        // literally, and BigQuery rejects the `''`
2425                        // quote-doubling the others require. A literal
2426                        // carrying either character cannot be pushed as
2427                        // provably identical SQL, so refuse it and let
2428                        // the caller scan. (The display translation
2429                        // keeps its best-effort `''`-doubling.)
2430                        if self.strict && (s.contains('\'') || s.contains('\\')) {
2431                            return Err(mismatch(
2432                                Refusal::Name,
2433                                "a text literal with a quote or backslash \
2434                                 has no escaping portable across SQL dialects"
2435                                    .into(),
2436                            ));
2437                        }
2438                        Ok(format!("'{}'", s.replace('\'', "''")))
2439                    }
2440                    "null" => Ok("NULL".to_string()),
2441                    _ => Ok(v.to_string()),
2442                }
2443            }
2444            "path" => {
2445                if self.kid(o, "step").is_some() {
2446                    // A step here is either navigation (refused) or
2447                    // a resolution chain (refused with the reason).
2448                    let s = self.kids(o, "step")[0];
2449                    if self.prop_s(s, "axis") == "-->" {
2450                        return Err(not_pushed(
2451                            Refusal::Resolution,
2452                            "a '-->' resolution chain: the foreign-key targets live in \
2453                             the schema, not the query — spell the join with '<=>' to \
2454                             export it"
2455                                .into(),
2456                        ));
2457                    }
2458                    return Err(not_pushed(
2459                        Refusal::Resolution,
2460                        "navigation inside a predicate (SQL rows are flat)".into(),
2461                    ));
2462                }
2463                let p = self
2464                    .kid(o, "projection")
2465                    .ok_or_else(|| not_pushed(Refusal::Operator, "an empty path operand".into()))?;
2466                let col = self.projection_col(p)?;
2467                let col = self.sql_ident(&col, "column name")?;
2468                // a read through a mark: the row the mark names
2469                let (named, any) = self.mark_anchor(o);
2470                if any {
2471                    let Some(name) = named else {
2472                        return Err(not_pushed(
2473                            Refusal::State,
2474                            "a mark read by position, or of several marks".into(),
2475                        ));
2476                    };
2477                    if self.join_marks.contains(&name) {
2478                        // `$$.al::col`: the witness, as `$$1::col`
2479                        if self.in_on {
2480                            return Err(not_pushed(
2481                                Refusal::Join,
2482                                "the joined expression's own mark inside its ON".into(),
2483                            ));
2484                        }
2485                        let Some(join) = self.join_sql.clone() else {
2486                            return Err(not_pushed(
2487                                Refusal::Join,
2488                                "a join mark outside a correlation join".into(),
2489                            ));
2490                        };
2491                        return Ok(format!("{join}.{col}"));
2492                    }
2493                    if self.from_marks.contains(&name) {
2494                        // `$$.t::col`: the driver's row, as `$$0::col`
2495                        // in the ON
2496                        return Ok(if self.join_sql.is_some() {
2497                            format!("{}.{col}", self.from_sql.clone())
2498                        } else {
2499                            match qual {
2500                                Some(q) => format!("{q}.{col}"),
2501                                None => col,
2502                            }
2503                        });
2504                    }
2505                    return Err(not_pushed(
2506                        Refusal::State,
2507                        format!("a read of the mark '.{name}' (not a row of the query's tables)"),
2508                    ));
2509                }
2510                Ok(match qual {
2511                    Some(q) => format!("{q}.{col}"),
2512                    None => col,
2513                })
2514            }
2515            "context" => {
2516                // `$$1::col` — the joined expression's witness, in
2517                // pipeline position: the joined table's column.
2518                // Anything else is outside the verified-safe set.
2519                let p = self
2520                    .kid(o, "projection")
2521                    .ok_or_else(|| not_pushed(Refusal::Join, "a bare '$$N' reference".into()))?;
2522                let col = self.projection_col(p)?;
2523                let col = self.sql_ident(&col, "column name")?;
2524                if matches!(self.prop(o, "index"), Some(Value::Int(0))) {
2525                    // `$$0::col` — the driver, legal only inside the
2526                    // joined expression's ON bracket (the parser
2527                    // refuses it anywhere else).
2528                    if !self.in_on {
2529                        return Err(not_pushed(
2530                            Refusal::Join,
2531                            "a '$$0' reference outside the join's ON".into(),
2532                        ));
2533                    }
2534                    if self.kid(o, "step").is_some() {
2535                        return Err(not_pushed(
2536                            Refusal::Join,
2537                            "a '$$0' reference beyond a plain column ($$0::col)".into(),
2538                        ));
2539                    }
2540                    return Ok(format!("{}.{col}", self.from_sql.clone()));
2541                }
2542                if self.in_on {
2543                    return Err(not_pushed(
2544                        Refusal::Join,
2545                        "a '$$N' reference inside the ON (the driver is '$$0')".into(),
2546                    ));
2547                }
2548                let Some(join) = self.join_sql.clone() else {
2549                    return Err(not_pushed(
2550                        Refusal::Join,
2551                        "a '$$N' reference outside a correlation join".into(),
2552                    ));
2553                };
2554                match self.prop(o, "index") {
2555                    Some(Value::Int(1)) => Ok(format!("{join}.{col}")),
2556                    None => Err(not_pushed(Refusal::Join, "a bare '$$N' reference".into())),
2557                    Some(v) => Err(not_pushed(
2558                        Refusal::Join,
2559                        format!("$${v} beyond a two-branch correlation"),
2560                    )),
2561                }
2562            }
2563            "invoking" => {
2564                // `$$__::col` — the invoking position of an enclosing
2565                // query body, never the driver (that is `$$0`, a
2566                // context of index 0, since draft 153). SQL has no
2567                // enclosing body to read.
2568                Err(not_pushed(
2569                    Refusal::Join,
2570                    "a '$$__' reference (the driver inside the ON is '$$0')".into(),
2571                ))
2572            }
2573            "arith" => {
2574                let op = self.prop_s(o, "op");
2575                let kids = self.arbor.children(o);
2576                // The engine computes with values: a text that reads
2577                // as a number is one, any other text gives null. SQL
2578                // computes by the column's type (SQLite reads 'abc'
2579                // as 0, so `::a + 1 = 1` answered such rows). A
2580                // column computed with is a numeric one, by the
2581                // catalog; a text literal is not computed with.
2582                if self.strict {
2583                    for &k in &kids {
2584                        if self.is_text_literal(k) {
2585                            return Err(mismatch(
2586                                Refusal::Type,
2587                                "arithmetic with a text literal (the engine reads the number \
2588                                 it spells; SQL's reading differs per engine)"
2589                                    .into(),
2590                            ));
2591                        }
2592                        let column = self.column_operand(k);
2593                        if let Some(c) = &column
2594                            && self.strict
2595                            && !self.column_types.computed.contains(c)
2596                        {
2597                            self.column_types.computed.push(c.clone());
2598                        }
2599                        self.assume_numeric(column);
2600                    }
2601                }
2602                // `div` is the true division: 7 div 2 is 3.5, and a
2603                // division by zero answers inf or nan. SQL's `/`
2604                // divides two integers as integers on most engines,
2605                // and answers NULL or an error for a zero divisor.
2606                // So the plan divides only by a number written in
2607                // the query that is not zero, with the dividend
2608                // read as a float. (It was pushed as the bare `/`:
2609                // `::n div 4` answered 2 where the scan answers 2.5.)
2610                if self.strict && op == "div" {
2611                    let nonzero = self.kind(kids[1]) == "literal"
2612                        && self
2613                            .prop(kids[1], "value")
2614                            .and_then(|v| Value::Str(v.to_string()).numeric_reading())
2615                            .is_some_and(|n| {
2616                                !matches!(n, Value::Int(0))
2617                                    && !matches!(n, Value::Float(f) if f == 0.0)
2618                            });
2619                    if !nonzero {
2620                        return Err(mismatch(
2621                            Refusal::Operator,
2622                            "'div' by anything but a number other than zero (for a zero \
2623                             divisor the engine answers inf or nan, SQL no value or an error)"
2624                                .into(),
2625                        ));
2626                    }
2627                    let l = self.operand(kids[0], qual)?;
2628                    let r = self.operand(kids[1], qual)?;
2629                    let float = match self.dialect {
2630                        Some(Dialect::Sqlite) => "REAL",
2631                        Some(Dialect::Postgres) => "DOUBLE PRECISION",
2632                        Some(Dialect::Mssql) => "FLOAT",
2633                        Some(Dialect::Oracle) => "BINARY_DOUBLE",
2634                        Some(Dialect::MySql) | None => "DOUBLE",
2635                    };
2636                    return Ok(format!("(CAST({l} AS {float}) / {r})"));
2637                }
2638                // `mod` keeps a fraction (9.5 mod 3 is 0.5) and
2639                // answers nan for a zero divisor; SQL's `%` reads
2640                // integers and answers NULL or an error.
2641                if self.strict && op == "mod" {
2642                    return Err(mismatch(
2643                        Refusal::Operator,
2644                        "'mod' (SQL's % reads its operands as integers, and answers no value \
2645                         for a zero divisor)"
2646                            .into(),
2647                    ));
2648                }
2649                let l = self.operand(kids[0], qual)?;
2650                let r = self.operand(kids[1], qual)?;
2651                Ok(match op.as_str() {
2652                    "+" | "-" | "*" => format!("({l} {op} {r})"),
2653                    "div" => format!("({l} / {r})"),
2654                    "mod" => format!("({l} % {r})"),
2655                    other => {
2656                        return Err(not_pushed(
2657                            Refusal::Operator,
2658                            format!("'{other}' arithmetic"),
2659                        ));
2660                    }
2661                })
2662            }
2663            other => Err(not_pushed(
2664                Refusal::State,
2665                format!(
2666                    "the '{other}' operand (registers, topics, and captures are Quarb-side state)"
2667                ),
2668            )),
2669        }
2670    }
2671
2672    fn projection_col(&mut self, p: NodeId) -> Result<String, SqlError> {
2673        match self.prop_s(p, "kind").as_str() {
2674            "property" => match self.prop(p, "key") {
2675                Some(k) => Ok(k.to_string()),
2676                None => Err(not_pushed(
2677                    Refusal::Operator,
2678                    "the bare '::' projection (name the column)".into(),
2679                )),
2680            },
2681            other => Err(not_pushed(
2682                Refusal::Operator,
2683                format!("the {other} metadata projection"),
2684            )),
2685        }
2686    }
2687
2688    /// The column a plain column operand names (`::col`, `$*1::col`):
2689    /// a path without a step, or a context reference, projecting a
2690    /// named property.
2691    /// A numeric reducer (`sum`, `mean`, `min`, `max`) pushed over a
2692    /// column is the scan's only on a numeric-typed column: the
2693    /// scan lifts unit text (`'18 parasang'` under a declared unit)
2694    /// to quantities and orders texts as texts, where SQL sums a
2695    /// text column by its leading digits and orders it by
2696    /// collation. Recorded for the caller's catalog as a
2697    /// comparison's assumption is.
2698    /// The column a reducer folds: assumed numeric, and noted where
2699    /// the reducer is a sum or a mean over a column by its name
2700    /// ([`FloatSum`]).
2701    fn fold_column(
2702        &mut self,
2703        reducer: &str,
2704        name: &mut Option<String>,
2705        col: &mut Option<String>,
2706    ) -> Option<String> {
2707        let column = name.clone();
2708        if let Some(c) = &column
2709            && self.strict
2710            && !self.column_types.reduced.contains(c)
2711        {
2712            self.column_types.reduced.push(c.clone());
2713        }
2714        self.assume_numeric(name.take());
2715        let sql = col.take();
2716        if let (true, Some(column), Some(sql)) =
2717            (matches!(reducer, "sum" | "mean"), column, sql.as_ref())
2718        {
2719            self.sums.push((reducer == "mean", column, sql.clone()));
2720        }
2721        sql
2722    }
2723
2724    fn assume_numeric(&mut self, column: Option<String>) {
2725        if !self.strict {
2726            return;
2727        }
2728        if let Some(c) = column
2729            && !self.column_types.numeric.contains(&c)
2730        {
2731            self.column_types.numeric.push(c);
2732        }
2733    }
2734
2735    /// A column whose text the plan compares as the engine does
2736    /// only where the database compares it exactly: recorded for the
2737    /// caller's catalog, as a text literal's column is.
2738    fn assume_text(&mut self, column: Option<String>) {
2739        if !self.strict {
2740            return;
2741        }
2742        if let Some(c) = column
2743            && !self.column_types.text.contains(&c)
2744        {
2745            self.column_types.text.push(c);
2746        }
2747    }
2748
2749    fn column_operand(&self, o: NodeId) -> Option<String> {
2750        match self.kind(o).as_str() {
2751            "path" if self.kid(o, "step").is_none() => {}
2752            "context" => {}
2753            _ => return None,
2754        }
2755        let p = self.kid(o, "projection")?;
2756        if self.prop_s(p, "kind") != "property" {
2757            return None;
2758        }
2759        self.prop(p, "key").map(|k| k.to_string())
2760    }
2761
2762    /// Whether `o` is a text string literal (`'London'`).
2763    fn is_text_literal(&self, o: NodeId) -> bool {
2764        self.kind(o) == "literal" && self.prop_s(o, "type") == "text"
2765    }
2766
2767    /// If `o` is the one JSON-column-path shape pushdown handles —
2768    /// `/col/seg/seg…::`, a plain-navigation path (every step `/`
2769    /// and a plain-identifier object key, no wildcards, no nested
2770    /// predicate), at least one segment past the column, ending in
2771    /// the bare `::` projection — return `(column, [json segments])`.
2772    /// Anything else is `None` (and falls back to the graft).
2773    fn json_path(&self, o: NodeId) -> Option<(String, Vec<String>)> {
2774        if self.kind(o) != "path" {
2775            return None;
2776        }
2777        let steps = self.kids(o, "step");
2778        if steps.len() < 2 {
2779            return None;
2780        }
2781        // The projection must be the bare `::` (default value of
2782        // the JSON leaf), not `::key` or a `:::`/`:` metadata form.
2783        let proj = self.kid(o, "projection")?;
2784        if self.prop_s(proj, "kind") != "property" || self.prop(proj, "key").is_some() {
2785            return None;
2786        }
2787        let mut names = Vec::with_capacity(steps.len());
2788        for s in &steps {
2789            if self.prop_s(*s, "axis") != "/"
2790                || self.prop_s(*s, "matcher-kind") != "name"
2791                || self.kid(*s, "predicate").is_some()
2792            {
2793                return None;
2794            }
2795            let name = self.prop_s(*s, "matcher");
2796            // Plain object keys only — no array indices, no
2797            // characters that would need per-dialect path escaping.
2798            let plain = !name.is_empty()
2799                && name
2800                    .chars()
2801                    .next()
2802                    .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
2803                && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_');
2804            if !plain {
2805                return None;
2806            }
2807            names.push(name);
2808        }
2809        let col = names.remove(0);
2810        Some((col, names))
2811    }
2812
2813    /// The property form of a JSON-column path, `/col/seg…::key`:
2814    /// plain-navigation steps as in [`json_path`], at least the
2815    /// column's own, ending in the projection of a plain-identifier
2816    /// property. It asks for the same value as `/col/seg…/key::`
2817    /// where that is a scalar; on an array of scalars it is the
2818    /// list (see [`json_property_prefilter`]). Returns
2819    /// `(column, [json segments, the key last])`.
2820    ///
2821    /// [`json_path`]: Self::json_path
2822    /// [`json_property_prefilter`]: Self::json_property_prefilter
2823    fn json_property_path(&self, o: NodeId) -> Option<(String, Vec<String>)> {
2824        if self.kind(o) != "path" {
2825            return None;
2826        }
2827        let steps = self.kids(o, "step");
2828        if steps.is_empty() {
2829            return None;
2830        }
2831        let proj = self.kid(o, "projection")?;
2832        if self.prop_s(proj, "kind") != "property" {
2833            return None;
2834        }
2835        let key = self.prop(proj, "key")?.to_string();
2836        let plain = |name: &str| {
2837            !name.is_empty()
2838                && name
2839                    .chars()
2840                    .next()
2841                    .is_some_and(|c| c.is_ascii_alphabetic() || c == '_')
2842                && name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_')
2843        };
2844        if !plain(&key) {
2845            return None;
2846        }
2847        let mut names = Vec::with_capacity(steps.len() + 1);
2848        for s in &steps {
2849            if self.prop_s(*s, "axis") != "/"
2850                || self.prop_s(*s, "matcher-kind") != "name"
2851                || self.kid(*s, "predicate").is_some()
2852            {
2853                return None;
2854            }
2855            let name = self.prop_s(*s, "matcher");
2856            if !plain(&name) {
2857                return None;
2858            }
2859            names.push(name);
2860        }
2861        names.push(key);
2862        let col = names.remove(0);
2863        Some((col, names))
2864    }
2865
2866    /// A safe `AS` alias: bare when it is a plain identifier and
2867    /// not an SQL keyword; otherwise strict mode refuses (quoting
2868    /// dialects disagree) and export mode double-quotes, noting it.
2869    fn alias(&mut self, name: &str) -> Result<String, SqlError> {
2870        if is_plain_ident(name) {
2871            return Ok(name.to_string());
2872        }
2873        if self.strict {
2874            return Err(not_pushed(
2875                Refusal::Name,
2876                format!(
2877                    "field name {name:?} needs SQL quoting \
2878                 (dialects disagree); rename the field"
2879                ),
2880            ));
2881        }
2882        self.notes
2883            .push(format!("field name {name:?} double-quoted (ANSI)"));
2884        Ok(format!("\"{}\"", name.replace('"', "\"\"")))
2885    }
2886
2887    /// A table or column name rendered into SQL. Bare when it is a
2888    /// plain identifier; otherwise strict mode refuses — a name
2889    /// like `a OR b` would silently rewrite the emitted SQL's
2890    /// meaning, breaking the provably-identical guarantee (and a
2891    /// portable quoting does not exist) — and export mode
2892    /// double-quotes with a note.
2893    fn sql_ident(&mut self, name: &str, what: &str) -> Result<String, SqlError> {
2894        if is_plain_ident(name) {
2895            return Ok(name.to_string());
2896        }
2897        if self.strict {
2898            return Err(not_pushed(
2899                Refusal::Name,
2900                format!("{what} {name:?} is not a plain SQL identifier"),
2901            ));
2902        }
2903        self.notes
2904            .push(format!("{what} {name:?} double-quoted (ANSI)"));
2905        Ok(format!("\"{}\"", name.replace('"', "\"\"")))
2906    }
2907
2908    /// Split a joined-side predicate: `col = $*1::col2` equalities
2909    /// become the ON condition; everything else the WHERE.
2910    /// `ltable` and `rtable` are the tables' SQL renderings.
2911    fn split_join_pred(
2912        &mut self,
2913        p: NodeId,
2914        rtable: &str,
2915        on: &mut Vec<String>,
2916        wheres: &mut Vec<String>,
2917    ) -> Result<(), SqlError> {
2918        if self.prop_s(p, "kind") != "expr" {
2919            return Err(not_pushed(
2920                Refusal::Take,
2921                "a positional predicate on the joined side".into(),
2922            ));
2923        }
2924        // Flatten top-level ANDs; each conjunct routes to ON or
2925        // WHERE.
2926        fn conjuncts(ex: &Exporter, e: NodeId, out: &mut Vec<NodeId>) {
2927            if ex.kind(e) == "and" {
2928                for c in ex.arbor.children(e) {
2929                    conjuncts(ex, c, out);
2930                }
2931            } else {
2932                out.push(e);
2933            }
2934        }
2935        let mut parts = Vec::new();
2936        for c in self.arbor.children(p) {
2937            conjuncts(self, c, &mut parts);
2938        }
2939        for e in parts {
2940            let uses_driver = self.subtree_has_driver(e);
2941            self.in_on = true;
2942            let cond = self.pred_expr(e, Some(rtable));
2943            self.in_on = false;
2944            let cond = cond?;
2945            if uses_driver && self.kind(e) == "compare" && self.prop_s(e, "op") == "=" {
2946                // Record which joined-table columns the ON binds —
2947                // the uniqueness obligation (see
2948                // Pushdown::join_left). Collected from the arbor's
2949                // bare-column operand nodes, never from the
2950                // rendered SQL text, so neither a literal nor an
2951                // unusual column name can corrupt the obligation.
2952                self.collect_join_cols(e)?;
2953                on.push(cond);
2954            } else {
2955                wheres.push(cond);
2956            }
2957        }
2958        Ok(())
2959    }
2960
2961    /// The joined-table columns the ON equality binds: every bare
2962    /// column operand (`::col`) in `e`'s subtree, appended to the
2963    /// join obligation with their raw (catalog) names.
2964    fn collect_join_cols(&mut self, e: NodeId) -> Result<(), SqlError> {
2965        // The `$$0` side is the driver — not part of the joined
2966        // table's obligation.
2967        if self.is_driver(e) {
2968            return Ok(());
2969        }
2970        if self.kind(e) == "path"
2971            && self.kid(e, "step").is_none()
2972            && let Some(p) = self.kid(e, "projection")
2973        {
2974            let col = self.projection_col(p)?;
2975            self.join_on_cols.push(col);
2976        }
2977        for c in self.arbor.children(e) {
2978            self.collect_join_cols(c)?;
2979        }
2980        Ok(())
2981    }
2982
2983    /// `$$0`: a context operand of index 0 (the driver), or a read
2984    /// of the driver's row through its mark (`$$.t::col`).
2985    fn is_driver(&self, n: NodeId) -> bool {
2986        (self.kind(n) == "context" && matches!(self.prop(n, "index"), Some(Value::Int(0))))
2987            || (self.kind(n) == "path"
2988                && matches!(self.mark_anchor(n).0, Some(name) if self.from_marks.contains(&name)))
2989    }
2990
2991    fn subtree_has_driver(&self, n: NodeId) -> bool {
2992        self.is_driver(n)
2993            || self
2994                .arbor
2995                .children(n)
2996                .into_iter()
2997                .any(|c| self.subtree_has_driver(c))
2998    }
2999
3000    /// Consume the pipeline into SELECT clauses. `projected` is the
3001    /// driving path's own terminal projection, where it has one:
3002    /// the column as SQL and its name.
3003    fn pipeline(
3004        &mut self,
3005        q: NodeId,
3006        sel: &mut Select,
3007        join: Option<(&str, &str)>,
3008        projected: Option<(String, String)>,
3009    ) -> Result<(), SqlError> {
3010        let Some(pipe) = self.kid(q, "pipeline") else {
3011            return Ok(());
3012        };
3013        let commands: Vec<NodeId> = self.arbor.children(pipe);
3014        let mut i = 0;
3015        // A pending per-row value (`| ::col`) feeding an aggregate,
3016        // and the column it names where it is one. The path's own
3017        // projection is that value from the start, so the glued
3018        // spelling plans as the piped one: `/t/*::col @| max` is
3019        // `/t/* | ::col @| max` (it was refused as a reducer "over
3020        // row nodes", with a hint to write the piped form).
3021        let (mut pending_col, mut pending_name) = match projected {
3022            Some((col, name)) => (Some(col), Some(name)),
3023            None => (None, None),
3024        };
3025        // After a grouped reduction: (key, agg_sql, alias).
3026        let mut grouped: Option<(String, String, String)> = None;
3027        // A pushed key awaiting its fold: (the regula's name, key_sql).
3028        let mut pushed_key: Option<(String, String)> = None;
3029        // …and the column it is, where it is one plain column.
3030        let mut pushed_key_column: Option<String> = None;
3031        // The regula a fold went by: the name its key has in the
3032        // record `%.` answers.
3033        let mut grouped_reg: Option<String> = None;
3034
3035        while i < commands.len() {
3036            let s = commands[i];
3037            match self.kind(s).as_str() {
3038                "expr" => {
3039                    let kids = self.arbor.children(s);
3040                    pending_col = Some(self.operand(kids[0], join.map(|(l, _)| l))?);
3041                    pending_name = self.column_operand(kids[0]);
3042                }
3043                "cmd" => {
3044                    let name = self.prop_s(s, "name");
3045                    match name.as_str() {
3046                        "rec" | "record" => {
3047                            sel.select = self.record_fields(s, join)?;
3048                            self.order.record = true;
3049                        }
3050                        // `| %` on a row node is the record of its
3051                        // properties, which are the row's columns in
3052                        // the table's order: SELECT *, a record even
3053                        // of one field. It is the plan a bare
3054                        // selection of row nodes had before the
3055                        // ruling of 2026-11-18 gave that one its
3056                        // locators. Last in the pipeline and over
3057                        // the rows themselves: after a projection
3058                        // the thread holds a value, and a joined
3059                        // row's record is the driver's alone.
3060                        "%" if self.strict
3061                            && join.is_none()
3062                            && sel.select.is_empty()
3063                            && pending_col.is_none()
3064                            && grouped.is_none()
3065                            && pushed_key.is_none()
3066                            && !self.aggregate
3067                            && self.arbor.children(s).is_empty()
3068                            && i + 1 == commands.len() =>
3069                        {
3070                            sel.select = vec!["*".to_string()];
3071                            self.order.record = true;
3072                            self.order.properties = true;
3073                        }
3074                        // A reducing aggregate on the plain pipe:
3075                        // the grouped reduction.
3076                        "count" | "sum" | "mean" | "min" | "max" => {
3077                            let (key, _, _) = grouped.as_ref().ok_or_else(|| {
3078                                not_pushed(
3079                                    Refusal::Reducer,
3080                                    format!("'| {name}' outside a group (use '@| {name}')"),
3081                                )
3082                            })?;
3083                            // Quarb's count counts every member,
3084                            // nulls included: COUNT(*); the
3085                            // NULL-skipping COUNT(col) is
3086                            // `count(--values)`.
3087                            let values =
3088                                self.reducer_counts_values(s, &name, pending_col.is_some())?;
3089                            let col = if name == "count" {
3090                                pending_col.take().filter(|_| values)
3091                            } else {
3092                                self.fold_column(&name, &mut pending_name, &mut pending_col)
3093                            };
3094                            let agg = sql_agg(&name, col)?;
3095                            grouped = Some((key.clone(), agg.clone(), agg));
3096                        }
3097                        other => {
3098                            // sort/unique/top and kin are spellable
3099                            // in SQL but with the backend's own
3100                            // collation and duplicate semantics —
3101                            // name the divergence, not a missing
3102                            // feature.
3103                            if matches!(other, "sort" | "sort_by" | "unique" | "top" | "bottom") {
3104                                return Err(mismatch(
3105                                    if other == "unique" {
3106                                        Refusal::Unique
3107                                    } else {
3108                                        Refusal::Sort
3109                                    },
3110                                    format!(
3111                                        "'{other}' means the backend's collation and \
3112                                     duplicate semantics; one engine-side ordering \
3113                                     keeps it identical everywhere"
3114                                    ),
3115                                ));
3116                            }
3117                            return Err(not_pushed(
3118                                Refusal::Operator,
3119                                format!("the '{other}' pipeline command"),
3120                            ));
3121                        }
3122                    }
3123                }
3124                "push" => {
3125                    // An alias for the grouped aggregate.
3126                    if let Some((k, agg, _)) = grouped.take() {
3127                        let alias = self.prop_s(s, "name");
3128                        grouped = Some((k, agg, alias));
3129                    } else {
3130                        return Err(not_pushed(
3131                            Refusal::State,
3132                            "a register push (Quarb-side state)".into(),
3133                        ));
3134                    }
3135                }
3136                // `| .k(::k)` before a fold by `.k`: the GROUP BY key
3137                // (a projection body is a subcontext of one branch).
3138                "expr-push" if grouped.is_none() && pushed_key.is_none() => {
3139                    let kids = self.arbor.children(s);
3140                    let key = self.operand(kids[0], join.map(|(l, _)| l))?;
3141                    pushed_key = Some((self.prop_s(s, "name"), key));
3142                    pushed_key_column = self.column_operand(kids[0]);
3143                }
3144                "subcontext" if grouped.is_none() && pushed_key.is_none() => {
3145                    let key = self.pushed_column(s, join.map(|(l, _)| l))?;
3146                    // (unqualified, the SQL is the column's own name)
3147                    pushed_key_column = join.is_none().then(|| key.clone());
3148                    pushed_key = Some((self.prop_s(s, "name"), key));
3149                }
3150                "filter" => {
3151                    if grouped.is_some() {
3152                        // HAVING: `$_` / the alias refer to the
3153                        // aggregate.
3154                        let cond = self.having_cond(s)?;
3155                        sel.having = Some(cond);
3156                    } else {
3157                        return Err(not_pushed(
3158                            Refusal::State,
3159                            "a mid-pipeline filter (put it in the row predicate)".into(),
3160                        ));
3161                    }
3162                }
3163                "recall" => {
3164                    // `| %.` finalizes the grouped record.
3165                    if self.prop_s(s, "ref") != "%." {
3166                        return Err(not_pushed(
3167                            Refusal::State,
3168                            "a register recall (Quarb-side state)".into(),
3169                        ));
3170                    }
3171                    let (key, agg, alias) = grouped.clone().ok_or_else(|| {
3172                        not_pushed(Refusal::Fold, "'%.', with nothing grouped".into())
3173                    })?;
3174                    let alias = self.alias(&alias)?;
3175                    // The record names the key by its regula (`.a`),
3176                    // which need not be the column's name: the scan
3177                    // answers %(a = …), and so must the plan.
3178                    let key_field = match &grouped_reg {
3179                        Some(reg) if *reg != key => format!("{key} AS {}", self.alias(reg)?),
3180                        _ => key.clone(),
3181                    };
3182                    sel.select = vec![key_field, format!("{agg} AS {alias}")];
3183                    self.order.record = true;
3184                    // The HAVING condition compared the aggregate
3185                    // through $_ — substitute the real expression.
3186                    if let Some(h) = sel.having.take() {
3187                        sel.having = Some(h.replace("__AGG__", &agg));
3188                    }
3189                }
3190                "agg" if self.prop(s, "group").is_some() => {
3191                    // The plan takes its first rows last: a command
3192                    // over the rows a selection kept is the engine's.
3193                    if self.strict && self.order.limit.is_some() {
3194                        return Err(not_pushed(
3195                            Refusal::Take,
3196                            format!(
3197                                "the '{}' command after a positional selection (a plan \
3198                                 takes its first rows last)",
3199                                self.prop_s(s, "name")
3200                            ),
3201                        ));
3202                    }
3203                    // `@|{.k} agg`: the fold by the pushed key is the
3204                    // grouped reduction, GROUP BY key.
3205                    let name = self.prop_s(s, "name");
3206                    if !matches!(name.as_str(), "count" | "sum" | "mean" | "min" | "max") {
3207                        return Err(not_pushed(
3208                            Refusal::Fold,
3209                            format!("the '{name}' command per group (GROUP BY reduces)"),
3210                        ));
3211                    }
3212                    let selector = self.prop_s(s, "group");
3213                    if selector.contains(';') {
3214                        return Err(not_pushed(
3215                            Refusal::Fold,
3216                            "a fold by several regulae (one GROUP BY key translates)".into(),
3217                        ));
3218                    }
3219                    let (reg, key) = pushed_key.take().ok_or_else(|| {
3220                        not_pushed(
3221                            Refusal::Fold,
3222                            "a fold by a regula no push of a key made".into(),
3223                        )
3224                    })?;
3225                    grouped_reg = Some(reg.clone());
3226                    if selector != "." && selector != format!(".{reg}") {
3227                        return Err(not_pushed(
3228                            Refusal::Fold,
3229                            format!("a fold by '{selector}' (the pushed key is '.{reg}')"),
3230                        ));
3231                    }
3232                    if self.strict {
3233                        // SQL gives its groups in no stated order; the
3234                        // engine gives them as each first appears. A
3235                        // fold by one numeric column of the table is
3236                        // pushed with that order asked of the driver
3237                        // (the least key of each group). Numeric: two
3238                        // texts that read as one number are one key
3239                        // to the engine and two to GROUP BY.
3240                        let plain = join.is_none()
3241                            && self.order.sort.is_none()
3242                            && self.order.limit.is_none()
3243                            && !self.order.reversed;
3244                        match pushed_key_column.take() {
3245                            Some(column) if plain => {
3246                                self.assume_numeric(Some(column.clone()));
3247                                self.order.groups = true;
3248                                self.order.keyed_by = Some((Refusal::Fold, column));
3249                            }
3250                            _ => {
3251                                return Err(mismatch(
3252                                    Refusal::Fold,
3253                                    "a fold over a join, or after a sort or a selection, or by \
3254                                     anything but one column"
3255                                        .into(),
3256                                ));
3257                            }
3258                        }
3259                    }
3260                    sel.group_by = Some(key.clone());
3261                    let values = self.reducer_counts_values(s, &name, pending_col.is_some())?;
3262                    let col = if name == "count" {
3263                        // `count(--values)` over a projected column is
3264                        // COUNT(col); the bare count is every row
3265                        pending_col.take().filter(|_| values)
3266                    } else {
3267                        self.fold_column(&name, &mut pending_name, &mut pending_col)
3268                    };
3269                    let agg = sql_agg(&name, col)?;
3270                    grouped = Some((key, agg.clone(), agg));
3271                }
3272                "agg" => {
3273                    // The plan takes its first rows last: a command
3274                    // over the rows a selection kept is the engine's.
3275                    if self.strict && self.order.limit.is_some() {
3276                        return Err(not_pushed(
3277                            Refusal::Take,
3278                            format!(
3279                                "the '{}' command after a positional selection (a plan \
3280                                 takes its first rows last)",
3281                                self.prop_s(s, "name")
3282                            ),
3283                        ));
3284                    }
3285                    let name = self.prop_s(s, "name");
3286                    match name.as_str() {
3287                        "count" | "sum" | "mean" | "min" | "max" => {
3288                            // Quarb's count counts every row, nulls
3289                            // included: COUNT(*) regardless of a
3290                            // pending column. `count(--values)`
3291                            // counts the values other than null:
3292                            // COUNT(col) over a projected column.
3293                            let values =
3294                                self.reducer_counts_values(s, &name, pending_col.is_some())?;
3295                            let col = if name == "count" {
3296                                pending_col.take().filter(|_| values)
3297                            } else {
3298                                self.fold_column(&name, &mut pending_name, &mut pending_col)
3299                            };
3300                            sel.select = vec![sql_agg(&name, col)?];
3301                            self.aggregate = true;
3302                        }
3303                        "sort_by" if self.strict => {
3304                            // A database orders text by its own
3305                            // collation. A sort by one numeric column
3306                            // of the table has one order everywhere:
3307                            // it is pushed, the key recorded for the
3308                            // catalog to confirm, a null key last and
3309                            // the ties in the table's order, which the
3310                            // driver's ORDER BY says after the plan's.
3311                            let kids = self.arbor.children(s);
3312                            let column = kids.first().and_then(|&k| self.column_operand(k));
3313                            let plain = join.is_none()
3314                                && self.order.sort.is_none()
3315                                && self.order.limit.is_none()
3316                                && !self.order.reversed
3317                                && !self.order.groups
3318                                && !self.aggregate
3319                                && grouped.is_none();
3320                            let column = match (column, plain) {
3321                                (Some(column), true) => column,
3322                                // one column, and no place for its
3323                                // order in the plan
3324                                (Some(_), false) => {
3325                                    return Err(not_pushed(
3326                                        Refusal::Sort,
3327                                        "a sort over a join, or after a selection, a fold or \
3328                                         another sort (a plan has one order)"
3329                                            .into(),
3330                                    ));
3331                                }
3332                                (None, _) => {
3333                                    return Err(mismatch(
3334                                        Refusal::Sort,
3335                                        "ORDER BY collations differ per engine".into(),
3336                                    ));
3337                                }
3338                            };
3339                            let mut desc = false;
3340                            for &k in &kids[1..] {
3341                                let named =
3342                                    (self.kind(k) == "option").then(|| self.prop_s(k, "name"));
3343                                match named.as_deref() {
3344                                    Some("--desc" | "-d") => desc = true,
3345                                    _ => {
3346                                        return Err(mismatch(
3347                                            Refusal::Sort,
3348                                            "ORDER BY collations differ per engine".into(),
3349                                        ));
3350                                    }
3351                                }
3352                            }
3353                            let e = self.operand(kids[0], None)?;
3354                            self.assume_numeric(Some(column.clone()));
3355                            self.order.sort = Some((e, desc));
3356                            self.order.keyed_by = Some((Refusal::Sort, column));
3357                        }
3358                        "sort_by" => {
3359                            // A sort after a positional selection
3360                            // (or a second sort) cannot render: the
3361                            // fixed SELECT shape orders before
3362                            // LIMIT, and has one ORDER BY.
3363                            if sel.limit.is_some() {
3364                                return Err(not_pushed(
3365                                    Refusal::Sort,
3366                                    "a sort after a positional selection (SQL orders \
3367                                     before LIMIT)"
3368                                        .into(),
3369                                ));
3370                            }
3371                            if sel.order_by.is_some() {
3372                                return Err(not_pushed(
3373                                    Refusal::Sort,
3374                                    "a second sort (SQL has a single ORDER BY)".into(),
3375                                ));
3376                            }
3377                            let kids = self.arbor.children(s);
3378                            // The options are the command's own:
3379                            // `--desc` is the direction, a named
3380                            // collation (`--locale=tag`, ruling
3381                            // 2026-11-11) is the engine's order and no
3382                            // backend's; a second key has no place in
3383                            // the one ORDER BY this shape holds.
3384                            let mut desc = false;
3385                            for &k in &kids[1..] {
3386                                if self.kind(k) != "option" {
3387                                    return Err(not_pushed(
3388                                        Refusal::Sort,
3389                                        "a sort by several keys".into(),
3390                                    ));
3391                                }
3392                                match self.prop_s(k, "name").as_str() {
3393                                    "--desc" | "-d" => desc = true,
3394                                    "--locale" => {
3395                                        return Err(mismatch(
3396                                            Refusal::Sort,
3397                                            "sort_by(--locale=…) names the engine's collation; \
3398                                             a backend's differs"
3399                                                .into(),
3400                                        ));
3401                                    }
3402                                    // a case order is that
3403                                    // collation's (ruling 2026-11-12)
3404                                    "--upper-first" | "-u" | "--lower-first" | "-l"
3405                                    | "--ignore-case" | "-i" => {
3406                                        return Err(mismatch(
3407                                            Refusal::Sort,
3408                                            "sort_by's case order is the engine's collation's; \
3409                                             a backend's differs"
3410                                                .into(),
3411                                        ));
3412                                    }
3413                                    other => {
3414                                        return Err(not_pushed(
3415                                            Refusal::Sort,
3416                                            format!("the '{other}' option of sort_by"),
3417                                        ));
3418                                    }
3419                                }
3420                            }
3421                            let e = self.operand(kids[0], join.map(|(l, _)| l))?;
3422                            sel.order_by = Some((e, desc));
3423                        }
3424                        // The context reversed: every term of the
3425                        // plan's order turned, the keys too.
3426                        "reverse" if self.strict => {
3427                            if self.order.reversed
3428                                || self.order.limit.is_some()
3429                                || self.order.groups
3430                                || self.aggregate
3431                                || grouped.is_some()
3432                            {
3433                                return Err(not_pushed(
3434                                    Refusal::Sort,
3435                                    "reverse after a positional selection, a fold or another \
3436                                     reverse"
3437                                        .into(),
3438                                ));
3439                            }
3440                            self.order.reversed = true;
3441                        }
3442                        "reverse" => match &mut sel.order_by {
3443                            Some((_, desc)) => *desc = true,
3444                            None => {
3445                                return Err(not_pushed(
3446                                    Refusal::Sort,
3447                                    "reverse without an ORDER BY (rows are unordered)".into(),
3448                                ));
3449                            }
3450                        },
3451                        // `top(k; ::n)` and `bottom(k; ::n)` in a plan:
3452                        // the rows whose key is not null, by the key
3453                        // descending or ascending, the ties in the
3454                        // table's order, and the first k of them. A
3455                        // sort and a cut, so the sort's conditions:
3456                        // one numeric column of the table, nothing
3457                        // ordered or cut before.
3458                        "top" | "bottom" if self.strict => {
3459                            let kids = self.arbor.children(s);
3460                            let count = kids.first().and_then(|&k| {
3461                                (self.kind(k) == "literal")
3462                                    .then(|| self.prop(k, "value"))
3463                                    .flatten()
3464                            });
3465                            let column = kids.get(1).and_then(|&k| self.column_operand(k));
3466                            let plain = kids.len() == 2
3467                                && join.is_none()
3468                                && self.order.sort.is_none()
3469                                && self.order.limit.is_none()
3470                                && !self.order.reversed
3471                                && !self.order.groups
3472                                && !self.aggregate
3473                                && grouped.is_none();
3474                            let (Some(Value::Int(k)), Some(column), true) = (count, column, plain)
3475                            else {
3476                                return Err(not_pushed(
3477                                    Refusal::Sort,
3478                                    "'top' and 'bottom' by anything but one column of the \
3479                                     table, under an option, over a join, or after a sort, a \
3480                                     selection or a fold (a plan has one order)"
3481                                        .into(),
3482                                ));
3483                            };
3484                            if k < 1 {
3485                                return Err(not_pushed(
3486                                    Refusal::Sort,
3487                                    "'top' and 'bottom' of no rows".into(),
3488                                ));
3489                            }
3490                            let e = self.operand(kids[1], None)?;
3491                            sel.wheres.push(format!("{e} IS NOT NULL"));
3492                            self.assume_numeric(Some(column.clone()));
3493                            self.order.sort = Some((e, name == "top"));
3494                            self.order.limit = Some(k as u64);
3495                            self.order.keyed_by = Some((Refusal::Sort, column));
3496                        }
3497                        "top" => {
3498                            if sel.limit.is_some() {
3499                                return Err(not_pushed(
3500                                    Refusal::Sort,
3501                                    "'top' after a positional selection (SQL orders \
3502                                     before LIMIT)"
3503                                        .into(),
3504                                ));
3505                            }
3506                            if sel.order_by.is_some() {
3507                                return Err(not_pushed(
3508                                    Refusal::Sort,
3509                                    "a second sort (SQL has a single ORDER BY)".into(),
3510                                ));
3511                            }
3512                            let kids = self.arbor.children(s);
3513                            // the options name the engine's collation
3514                            // and its case order: no backend's (they
3515                            // were dropped, and the SQL ordered as the
3516                            // backend pleased)
3517                            if let Some(&k) = kids
3518                                .get(2..)
3519                                .and_then(|rest| rest.iter().find(|&&k| self.kind(k) == "option"))
3520                            {
3521                                return Err(mismatch(
3522                                    Refusal::Sort,
3523                                    format!(
3524                                        "top({}) names the engine's collation; a backend's differs",
3525                                        self.prop_s(k, "name")
3526                                    ),
3527                                ));
3528                            }
3529                            let n = self.prop_s(kids[0], "value");
3530                            let e = self.operand(kids[1], join.map(|(l, _)| l))?;
3531                            sel.order_by = Some((e, true));
3532                            sel.limit = Some(n);
3533                        }
3534                        "unique" => {
3535                            if self.strict {
3536                                // A count of the distinct values asks
3537                                // for no order: `| ::n @| unique @|
3538                                // count`, the last two commands of the
3539                                // pipeline. COUNT(DISTINCT n) leaves
3540                                // the NULLs out, and a null is one
3541                                // value to `unique`: the plan adds it.
3542                                // The column is a numeric one: two
3543                                // texts that read as one number
3544                                // ('300', '300.0') are one value to
3545                                // the engine and two to DISTINCT.
3546                                let counted = self.arbor.children(s).is_empty()
3547                                    && i + 2 == commands.len()
3548                                    && commands.get(i + 1).is_some_and(|&c| {
3549                                        self.kind(c) == "agg"
3550                                            && self.prop_s(c, "name") == "count"
3551                                            && self.prop(c, "group").is_none()
3552                                            && self.arbor.children(c).is_empty()
3553                                    });
3554                                match (counted, pending_col.take(), pending_name.take()) {
3555                                    (true, Some(col), Some(name)) => {
3556                                        self.assume_numeric(Some(name.clone()));
3557                                        self.order.keyed_by = Some((Refusal::Unique, name));
3558                                        sel.select = vec![format!(
3559                                            "COUNT(DISTINCT {col}) + COALESCE(MAX(CASE WHEN \
3560                                             {col} IS NULL THEN 1 ELSE 0 END), 0)"
3561                                        )];
3562                                        self.aggregate = true;
3563                                        // the count is taken with it
3564                                        i += 2;
3565                                        continue;
3566                                    }
3567                                    _ => {
3568                                        return Err(not_pushed(
3569                                            Refusal::Unique,
3570                                            "DISTINCT returns rows in no stated order (a count \
3571                                             of the distinct values of a numeric column is \
3572                                             pushed: '| ::col @| unique @| count')"
3573                                                .into(),
3574                                        ));
3575                                    }
3576                                }
3577                            }
3578                            // Quarb dedups the limited rows; SQL
3579                            // applies DISTINCT before LIMIT.
3580                            if sel.limit.is_some() {
3581                                return Err(not_pushed(
3582                                    Refusal::Unique,
3583                                    "'unique' after a positional selection (SQL applies \
3584                                     DISTINCT before LIMIT)"
3585                                        .into(),
3586                                ));
3587                            }
3588                            if let Some(c) = pending_col.take() {
3589                                sel.select = vec![c];
3590                            }
3591                            sel.distinct = true;
3592                        }
3593                        other => {
3594                            return Err(not_pushed(
3595                                Refusal::Reducer,
3596                                format!("the '{other}' aggregate"),
3597                            ));
3598                        }
3599                    }
3600                }
3601                // `@| [..n]` in a plan: the first rows of the order the
3602                // driver writes (the plan's own sort, then the keys),
3603                // so the rows the scan's context begins with.
3604                "select" if self.strict => {
3605                    if self.order.limit.is_some()
3606                        || self.order.groups
3607                        || self.aggregate
3608                        || grouped.is_some()
3609                        || pushed_key.is_some()
3610                    {
3611                        return Err(not_pushed(
3612                            Refusal::Take,
3613                            "a positional selection after a reducer, a fold or another \
3614                             selection"
3615                                .into(),
3616                        ));
3617                    }
3618                    let p = self.arbor.children(s)[0];
3619                    match (self.prop_s(p, "kind").as_str(), self.prop(p, "to")) {
3620                        ("range", Some(Value::Int(n)))
3621                            if self.prop(p, "from").is_none() && n > 0 =>
3622                        {
3623                            self.order.limit = Some(n as u64);
3624                        }
3625                        _ => {
3626                            return Err(not_pushed(
3627                                Refusal::Take,
3628                                "positional selection beyond '@| [..n]'".into(),
3629                            ));
3630                        }
3631                    }
3632                }
3633                "select" => {
3634                    // `@| [..n]` → LIMIT.
3635                    if sel.limit.is_some() {
3636                        return Err(not_pushed(
3637                            Refusal::Take,
3638                            "a second positional selection".into(),
3639                        ));
3640                    }
3641                    let p = self.arbor.children(s)[0];
3642                    match (self.prop_s(p, "kind").as_str(), self.prop(p, "to")) {
3643                        ("range", Some(Value::Int(n)))
3644                            if self.prop(p, "from").is_none() && n > 0 =>
3645                        {
3646                            sel.limit = Some(n.to_string());
3647                        }
3648                        _ => {
3649                            return Err(not_pushed(
3650                                Refusal::Take,
3651                                "positional selection beyond '@| [..n]'".into(),
3652                            ));
3653                        }
3654                    }
3655                }
3656                other => {
3657                    return Err(not_pushed(
3658                        Refusal::Operator,
3659                        format!(
3660                            "the '{other}' command (windows, subcontexts, and registers are \
3661                         Quarb-side state)"
3662                        ),
3663                    ));
3664                }
3665            }
3666            i += 1;
3667        }
3668        // A pending column with no aggregate is a one-column select.
3669        if let Some(c) = pending_col
3670            && sel.select.is_empty()
3671        {
3672            sel.select = vec![c];
3673        }
3674        // A plan answers row nodes as their rows, where the scan
3675        // answers the nodes: a sorted, reversed or cut selection of
3676        // nodes was the scan's to answer, and stays so; with a
3677        // projection or a record after it, it is the plan's.
3678        if self.strict
3679            && sel.select.is_empty()
3680            && (self.order.sort.is_some() || self.order.reversed || self.order.limit.is_some())
3681        {
3682            let word = if self.order.limit.is_some() {
3683                Refusal::Take
3684            } else {
3685                Refusal::Sort
3686            };
3687            return Err(not_pushed(
3688                word,
3689                "a sorted, reversed or cut selection of row nodes (project a column or build \
3690                 a record after it: '| ::col', '| %(…)')"
3691                    .into(),
3692            ));
3693        }
3694        // A grouped reduction never recorded (`@|{.k} count` with no
3695        // `| .n | %.`) answers one value per group: the aggregate
3696        // alone, grouped by the key.
3697        if let Some((_, agg, _)) = grouped
3698            && sel.select.is_empty()
3699        {
3700            sel.select = vec![agg];
3701        }
3702        Ok(())
3703    }
3704
3705    /// The column a pushed projection body names — `.k(::k)` is a
3706    /// subcontext whose one branch is the bare projection; any
3707    /// other body is Quarb-side state.
3708    fn pushed_column(&mut self, s: NodeId, qual: Option<&str>) -> Result<String, SqlError> {
3709        let body = || {
3710            let q = self.kid(s, "query")?;
3711            let b = self.kid(q, "branch")?;
3712            let p = self.kid(b, "projection")?;
3713            let bare = self.kids(q, "branch").len() == 1
3714                && self.kids(q, "pipeline").is_empty()
3715                && self.arbor.children(b).len() == 1
3716                && self.prop_s(p, "kind") == "property";
3717            bare.then_some(p)
3718        };
3719        let p = body()
3720            .ok_or_else(|| not_pushed(Refusal::State, "a pushed body (Quarb-side state)".into()))?;
3721        let col = self.projection_col(p)?;
3722        let col = self.sql_ident(&col, "column name")?;
3723        Ok(match qual {
3724            Some(q) => format!("{q}.{col}"),
3725            None => col,
3726        })
3727    }
3728
3729    /// A HAVING filter: `$_` and `$.name` refer to the aggregate.
3730    fn having_cond(&mut self, s: NodeId) -> Result<String, SqlError> {
3731        let kids = self.arbor.children(s);
3732        if kids.len() != 1 || self.kind(kids[0]) != "compare" {
3733            return Err(not_pushed(
3734                Refusal::Fold,
3735                "HAVING translates for a single comparison".into(),
3736            ));
3737        }
3738        let e = kids[0];
3739        let op = self.prop_s(e, "op");
3740        let cmp_kids = self.arbor.children(e);
3741        let l = match self.kind(cmp_kids[0]).as_str() {
3742            "topic" | "recall" => "__AGG__".to_string(),
3743            _ => {
3744                return Err(not_pushed(
3745                    Refusal::Fold,
3746                    "HAVING compares the aggregate ($_ or its register)".into(),
3747                ));
3748            }
3749        };
3750        let r = self.operand(cmp_kids[1], None)?;
3751        Ok(format!("{l} {op} {r}"))
3752    }
3753
3754    /// `rec(...)` fields as a select list.
3755    fn record_fields(
3756        &mut self,
3757        s: NodeId,
3758        join: Option<(&str, &str)>,
3759    ) -> Result<Vec<String>, SqlError> {
3760        let kids = self.arbor.children(s);
3761        let mut fields = Vec::new();
3762        let mut i = 0;
3763        while i < kids.len() {
3764            let k = kids[i];
3765            if self.kind(k) == "literal" {
3766                let name = self.prop_s(k, "value");
3767                let value = self.operand(kids[i + 1], join.map(|(l, _)| l))?;
3768                let name = self.alias(&name)?;
3769                fields.push(format!("{value} AS {name}"));
3770                i += 2;
3771            } else {
3772                let value = self.operand(k, join.map(|(l, _)| l))?;
3773                fields.push(value);
3774                i += 1;
3775            }
3776        }
3777        Ok(fields)
3778    }
3779}
3780
3781impl Exporter {
3782    /// What a reducer's own arguments ask of the plan. A pushed
3783    /// reducer is the bare command: `count` is COUNT(*), `sum` is
3784    /// SUM(col). `count(--values)` counts the values other than
3785    /// null, which over a projected column is what COUNT(col)
3786    /// counts.
3787    /// Any other option, and any operand, changes what is reduced
3788    /// (`--nulls`, `--of=`, `--by=`), and the reducer stays with the
3789    /// engine. The plan took no notice of them until 0.68:
3790    /// `count(--values)` and `count(--nulls)` were both answered as
3791    /// COUNT(*), where the scan counts five and one of six.
3792    fn reducer_counts_values(
3793        &self,
3794        s: NodeId,
3795        name: &str,
3796        projected: bool,
3797    ) -> Result<bool, SqlError> {
3798        let mut values = false;
3799        for k in self.arbor.children(s) {
3800            if self.kind(k) != "option" {
3801                return Err(not_pushed(
3802                    Refusal::Reducer,
3803                    format!("an operand of '{name}'"),
3804                ));
3805            }
3806            match (name, self.prop_s(k, "name").as_str()) {
3807                ("count", "--values" | "-v") => values = true,
3808                (_, option) => {
3809                    return Err(not_pushed(
3810                        Refusal::Reducer,
3811                        format!("the '{option}' option of '{name}'"),
3812                    ));
3813                }
3814            }
3815        }
3816        // a row node is no value: what the count answers there is
3817        // the engine's to say
3818        if values && !projected {
3819            return Err(not_pushed(
3820                Refusal::Reducer,
3821                "'count(--values)' over row nodes (project a column: '/table/*::col @| \
3822                 count(--values)')"
3823                    .into(),
3824            ));
3825        }
3826        Ok(values)
3827    }
3828}
3829
3830fn sql_agg(name: &str, col: Option<String>) -> Result<String, SqlError> {
3831    let f = match name {
3832        "count" => "COUNT",
3833        "sum" => "SUM",
3834        "mean" => "AVG",
3835        "min" => "MIN",
3836        "max" => "MAX",
3837        _ => unreachable!("checked by caller"),
3838    };
3839    match col {
3840        Some(c) => Ok(format!("{f}({c})")),
3841        // Only COUNT aggregates bare rows; SUM(*) and friends are
3842        // not SQL.
3843        None if f == "COUNT" => Ok("COUNT(*)".to_string()),
3844        None => Err(not_pushed(
3845            Refusal::Reducer,
3846            format!("'{name}' over row nodes (project a column: '/table/*::col @| {name}')"),
3847        )),
3848    }
3849}
3850
3851#[cfg(test)]
3852mod null_and_literal_tests {
3853    use super::{Dialect, export, partial_pushdown, pushdown};
3854
3855    // Grouped pipeline that never pushes, so `partial_pushdown` hinges
3856    // only on the leading predicate (mirrors the crate's partial gate).
3857    const GROUPED: &str = " | ::x | .g(::x) @|{.g} count | .n | %.";
3858
3859    #[test]
3860    fn pattern_literal_pushes_as_like() {
3861        // Ruling #33: segments in order, a `%` per star, LIKE's
3862        // metacharacters escaped. Strict push only where LIKE is
3863        // provably case-sensitive (PostgreSQL); everywhere else the
3864        // pattern rides the scan.
3865        let q = "/users/*[::name == 'app-'*]::id";
3866        let p = pushdown(q, Some(Dialect::Postgres)).unwrap();
3867        assert_eq!(
3868            p.sql,
3869            "SELECT id FROM users WHERE name LIKE 'app-%' ESCAPE '\\'"
3870        );
3871        assert!(pushdown(q, Some(Dialect::Sqlite)).is_none());
3872        assert!(pushdown(q, Some(Dialect::MySql)).is_none());
3873        // contains and multi-segment shapes
3874        let p = pushdown("/users/*[::name == *'web'*]::id", Some(Dialect::Postgres)).unwrap();
3875        assert!(p.sql.contains("LIKE '%web%'"), "{}", p.sql);
3876        let p = pushdown(
3877            "/users/*[::name == *'a'*'.gz']::id",
3878            Some(Dialect::Postgres),
3879        )
3880        .unwrap();
3881        assert!(p.sql.contains("LIKE '%a%.gz'"), "{}", p.sql);
3882        // LIKE metacharacters in a segment are escaped
3883        let p = pushdown("/users/*[::name == *'50%_x'*]::id", Some(Dialect::Postgres)).unwrap();
3884        assert!(p.sql.contains("LIKE '%50\\%\\_x%'"), "{}", p.sql);
3885        // `!=` with a pattern rides the scan
3886        assert!(pushdown("/users/*[::name !== *'web'*]::id", Some(Dialect::Postgres)).is_none());
3887        // the `*=` alias gains the same PostgreSQL rung
3888        let p = pushdown("/users/*[::name *= 'web']::id", Some(Dialect::Postgres)).unwrap();
3889        assert!(p.sql.contains("LIKE '%web%'"), "{}", p.sql);
3890        // and on SQLite it is instr, a substring test by bytes and by
3891        // case as `*=` is; MySQL's INSTR folds case, and scans
3892        assert_eq!(
3893            pushdown("/users/*[::name *= 'web']::id", Some(Dialect::Sqlite))
3894                .unwrap()
3895                .sql,
3896            "SELECT id FROM users WHERE instr(name, 'web') > 0"
3897        );
3898        assert!(pushdown("/users/*[::name *= 'web']::id", Some(Dialect::MySql)).is_none());
3899        assert!(pushdown("/users/*[::name *= 'web']::id", None).is_none());
3900    }
3901
3902    #[test]
3903    fn json_column_path_pushdown_per_dialect() {
3904        // `[/col/a/b:: = 'lit']` navigates into a JSON column; each
3905        // dialect extracts the fixed path to text and compares.
3906        // Verified live against all five engines to match the graft.
3907        let q = "/orders/*[/data/meta/tier:: = 'gold']::id";
3908        let sql = |d| pushdown(q, Some(d)).unwrap().sql;
3909        assert_eq!(
3910            sql(Dialect::Postgres),
3911            "SELECT id FROM orders WHERE ((data::jsonb #>> '{meta,tier}') = 'gold')"
3912        );
3913        assert_eq!(
3914            sql(Dialect::MySql),
3915            "SELECT id FROM orders WHERE (JSON_VALID(data) AND \
3916             JSON_UNQUOTE(JSON_EXTRACT(data, '$.meta.tier')) = 'gold')"
3917        );
3918        assert_eq!(
3919            sql(Dialect::Sqlite),
3920            "SELECT id FROM orders WHERE (json_valid(data) AND \
3921             json_extract(data, '$.meta.tier') = 'gold')"
3922        );
3923        assert_eq!(
3924            sql(Dialect::Mssql),
3925            "SELECT id FROM orders WHERE (ISJSON(data) = 1 AND \
3926             JSON_VALUE(data, '$.meta.tier') = 'gold')"
3927        );
3928        assert_eq!(
3929            sql(Dialect::Oracle),
3930            "SELECT id FROM orders WHERE (JSON_VALUE(data, '$.meta.tier') = 'gold')"
3931        );
3932        // With no dialect, the JSON navigation is not pushable — it
3933        // falls back to the client-side graft.
3934        assert!(pushdown(q, None).is_none());
3935    }
3936
3937    #[test]
3938    fn json_pushdown_only_string_equality() {
3939        // Numeric comparison, `!=`, and a wildcard step stay off the
3940        // pushdown path (they are not provably identical to the
3941        // graft), so they refuse and scan even with a dialect set.
3942        let d = Some(Dialect::Sqlite);
3943        assert!(pushdown("/o/*[/data/n:: > 2]::id", d).is_none());
3944        assert!(pushdown("/o/*[/data/tier:: != 'gold']::id", d).is_none());
3945        assert!(pushdown("/o/*[/data/items/*/sku:: = 'A1']::id", d).is_none());
3946    }
3947
3948    #[test]
3949    fn json_pushdown_refuses_numeric_looking_literal() {
3950        // value_eq coerces numeric-looking strings ('150' matches
3951        // the JSON number 150 and the string "150.0"); no engine's
3952        // extractor does, so the push would drop rows.
3953        let q = "/orders/*[/data/total:: = '150']::id";
3954        assert!(pushdown(q, Some(Dialect::Sqlite)).is_none());
3955        assert!(pushdown(q, Some(Dialect::Postgres)).is_none());
3956    }
3957
3958    #[test]
3959    fn a_regex_names_the_literals_it_cannot_match_without() {
3960        let lits = |p: &str| super::required_literals(p);
3961        assert_eq!(lits("^Ma.*s$"), ["Ma", "s"]);
3962        assert_eq!(lits("(us)+$"), ["us"]);
3963        assert_eq!(lits("a?b"), ["b"]);
3964        assert_eq!(lits("(?:foo|bar)baz"), ["baz"]);
3965        assert_eq!(lits("fo{2,}"), ["f", "o"]);
3966        assert_eq!(lits(r"\d+-\d+"), ["-"]);
3967        assert_eq!(lits(r"\bfoo\b"), ["foo"]);
3968        assert_eq!(lits(r"No\.1"), ["No.1"]);
3969        assert_eq!(lits("na\u{ef}ve"), ["na\u{ef}ve"]);
3970        assert_eq!(lits("(?x) a b"), ["ab"]);
3971        assert_eq!(lits("(a|b)c*d"), ["d"]);
3972        assert_eq!(lits("x{0}y"), ["y"]);
3973        assert_eq!(lits("ab(ab)"), ["ab"]);
3974        // nothing promised: a class, an alternation, an optional
3975        // part, a letter under (?i), a pattern that does not parse
3976        for p in ["(?i)ma", "Ven|Jup", "a*", "[ab]", "(ab)?", ".*", "", "["] {
3977            assert!(lits(p).is_empty(), "{p}");
3978        }
3979    }
3980
3981    #[test]
3982    fn a_regex_is_narrowed_by_its_literals() {
3983        let part = |q: &str, d: Dialect| {
3984            partial_pushdown(q, Some(d)).map(|p| (p.where_sql, p.column_types.text))
3985        };
3986        assert_eq!(
3987            part("/t/*[::a == (/^Ma.*s$/)]::b", Dialect::Sqlite),
3988            Some((
3989                "instr(a, 'Ma') > 0 AND instr(a, 's') > 0".to_string(),
3990                vec!["a".to_string()]
3991            ))
3992        );
3993        assert_eq!(
3994            part("/t/*[::a == (/50%_off/)]::b", Dialect::Postgres).map(|p| p.0),
3995            Some(r"a LIKE '%50\%\_off%' ESCAPE '\'".to_string())
3996        );
3997        // a conjunction keeps the side that translates; a
3998        // disjunction needs both
3999        assert_eq!(
4000            part("/t/*[::n > 2 && ::a == (/on/)]::b", Dialect::Sqlite).map(|p| p.0),
4001            Some("(n > 2 AND instr(a, 'on') > 0)".to_string())
4002        );
4003        assert_eq!(
4004            part(
4005                "/t/*[::a == (/(?i)x/) && ::a == (/on/)]::b",
4006                Dialect::Sqlite
4007            )
4008            .map(|p| p.0),
4009            Some("(instr(a, 'on') > 0)".to_string())
4010        );
4011        assert_eq!(
4012            part("/t/*[::a == (/Ven/) || ::a == (/Jup/)]::b", Dialect::Sqlite).map(|p| p.0),
4013            Some("(instr(a, 'Ven') > 0 OR instr(a, 'Jup') > 0)".to_string())
4014        );
4015        for q in [
4016            // one side promises nothing: the disjunction is not narrowed
4017            "/t/*[::a == (/Ven/) || ::a == (/(?i)jup/)]::b",
4018            // a negation is never narrowed, nor a non-match
4019            "/t/*[!(::a == (/Ven/))]::b",
4020            "/t/*[::a !== (/Ven/)]::b",
4021            // a literal a SQL string would not carry everywhere
4022            "/t/*[::a == (/it's/)]::b",
4023            // no literal at all
4024            "/t/*[::a == (/(?i)ven/)]::b",
4025            "/t/*[::a == (/Ven|Jup/)]::b",
4026        ] {
4027            assert_eq!(part(q, Dialect::Sqlite), None, "{q}");
4028        }
4029        // MySQL's instr follows the collation: it keeps every text
4030        // that holds the literal, and some in another case (0.71)
4031        assert_eq!(
4032            part("/t/*[::a == (/Ven/)]::b", Dialect::MySql).map(|p| p.0),
4033            Some("instr(a, 'Ven') > 0".to_string())
4034        );
4035        // under no dialect (DuckDB's, BigQuery's and Athena's doors)
4036        assert_eq!(
4037            partial_pushdown("/t/*[::a == (/^Ma.*s$/)]::b", None).map(|p| p.where_sql),
4038            Some("strpos(a, 'Ma') > 0 AND strpos(a, 's') > 0".to_string())
4039        );
4040        assert_eq!(
4041            partial_pushdown("/t/*[::n > 2 && ::a == (/on/)]::b", None).map(|p| p.where_sql),
4042            Some("(n > 2 AND strpos(a, 'on') > 0)".to_string())
4043        );
4044        // SQL Server and Oracle: not narrowed
4045        assert_eq!(part("/t/*[::a == (/Ven/)]::b", Dialect::Mssql), None);
4046        assert_eq!(part("/t/*[::a == (/Ven/)]::b", Dialect::Oracle), None);
4047    }
4048
4049    #[test]
4050    fn partial_json_prefilters_sqlite() {
4051        // Exact string equality rides the strict gate.
4052        let p = partial_pushdown(
4053            "/orders/*[/payload/customer/geo/city:: = 'Lyon']::id",
4054            Some(Dialect::Sqlite),
4055        )
4056        .unwrap();
4057        assert_eq!(p.table, "orders");
4058        assert_eq!(
4059            p.where_sql,
4060            "(json_valid(payload) AND \
4061             json_extract(payload, '$.customer.geo.city') = 'Lyon')"
4062        );
4063        // Numeric comparison: typed compare, text values escape.
4064        let p = partial_pushdown(
4065            "/orders/*[/payload/total:: > 150]::id",
4066            Some(Dialect::Sqlite),
4067        )
4068        .unwrap();
4069        assert_eq!(
4070            p.where_sql,
4071            "(json_valid(payload) AND (json_extract(payload, '$.total') > 150 \
4072             OR json_type(payload, '$.total') = 'text'))"
4073        );
4074        // Bare existence.
4075        let p = partial_pushdown("/orders/*[/payload/gift]::id", Some(Dialect::Sqlite)).unwrap();
4076        assert_eq!(
4077            p.where_sql,
4078            "(json_valid(payload) AND json_type(payload, '$.gift') IS NOT NULL)"
4079        );
4080        // != floors at existence.
4081        let p = partial_pushdown(
4082            "/orders/*[/payload/status:: != 'x']::id",
4083            Some(Dialect::Sqlite),
4084        )
4085        .unwrap();
4086        assert_eq!(
4087            p.where_sql,
4088            "(json_valid(payload) AND json_type(payload, '$.status') IS NOT NULL)"
4089        );
4090        // A flipped literal flips the operator.
4091        let p = partial_pushdown(
4092            "/orders/*[150 < /payload/total::]::id",
4093            Some(Dialect::Sqlite),
4094        )
4095        .unwrap();
4096        assert!(p.where_sql.contains("> 150"), "{}", p.where_sql);
4097        // Without a dialect, JSON predicates stay on the engine.
4098        assert!(partial_pushdown("/orders/*[/payload/gift]::id", None).is_none());
4099    }
4100
4101    #[test]
4102    fn partial_json_prefilters_postgres() {
4103        let p = partial_pushdown(
4104            "/orders/*[/payload/total:: > 150]::id",
4105            Some(Dialect::Postgres),
4106        )
4107        .unwrap();
4108        assert_eq!(
4109            p.where_sql,
4110            "(CASE WHEN jsonb_typeof(payload::jsonb #> '{total}') = 'number' \
4111             THEN ((payload::jsonb #>> '{total}'))::float8 > 150 \
4112             WHEN jsonb_typeof(payload::jsonb #> '{total}') = 'string' \
4113             THEN true ELSE false END)"
4114        );
4115        let p = partial_pushdown("/orders/*[/payload/gift]::id", Some(Dialect::Postgres)).unwrap();
4116        assert_eq!(
4117            p.where_sql,
4118            "(jsonb_typeof(payload::jsonb #> '{gift}') IS NOT NULL)"
4119        );
4120    }
4121
4122    #[test]
4123    fn json_property_path_pushdown() {
4124        // `[/col/a::b = 'lit']`, the property form: the scalar's
4125        // question and, on an array of scalars, the list's (any item
4126        // equals). SQLite's condition says both whole.
4127        let q = "/orders/*[/payload/customer/geo::city = 'Lyon']::id";
4128        assert_eq!(
4129            pushdown(q, Some(Dialect::Sqlite)).unwrap().sql,
4130            "SELECT id FROM orders WHERE (json_valid(payload) AND \
4131             ((json_type(payload, '$.customer.geo.city') = 'text' AND \
4132             json_extract(payload, '$.customer.geo.city') = 'Lyon') OR \
4133             (json_type(payload, '$.customer.geo.city') = 'array' AND \
4134             EXISTS (SELECT 1 FROM json_each(payload, '$.customer.geo.city') \
4135             WHERE json_each.type = 'text' AND json_each.value = 'Lyon') AND \
4136             NOT EXISTS (SELECT 1 FROM json_each(payload, '$.customer.geo.city') \
4137             WHERE json_each.type IN ('array', 'object')))))"
4138        );
4139        // a property of the column's own value
4140        assert!(
4141            pushdown(
4142                "/orders/*[/payload::city = 'Lyon']::id",
4143                Some(Dialect::Sqlite)
4144            )
4145            .unwrap()
4146            .sql
4147            .contains("json_extract(payload, '$.city') = 'Lyon'")
4148        );
4149        // the other engines' array branches are not verified: no
4150        // whole push there, and none with no dialect
4151        for d in [
4152            Dialect::Postgres,
4153            Dialect::MySql,
4154            Dialect::Mssql,
4155            Dialect::Oracle,
4156        ] {
4157            assert!(pushdown(q, Some(d)).is_none(), "{d:?}");
4158        }
4159        assert!(pushdown(q, None).is_none());
4160        // a numeric-looking literal compares by value: no whole push
4161        assert!(
4162            pushdown(
4163                "/orders/*[/payload::total = '150']::id",
4164                Some(Dialect::Sqlite)
4165            )
4166            .is_none()
4167        );
4168        // a column named as one of json_each's own columns would be
4169        // read as that column inside the subquery
4170        assert!(
4171            pushdown(
4172                "/orders/*[/value::city = 'Lyon']::id",
4173                Some(Dialect::Sqlite)
4174            )
4175            .is_none()
4176        );
4177    }
4178
4179    #[test]
4180    fn partial_json_property_prefilters() {
4181        let sqlite = |q: &str| partial_pushdown(q, Some(Dialect::Sqlite)).map(|p| p.where_sql);
4182        // a comparison lets the arrays through with the text values:
4183        // a list matches when any item does, and the engine decides
4184        assert_eq!(
4185            sqlite("/orders/*[/payload::total > 150]::id").unwrap(),
4186            "(json_valid(payload) AND (json_extract(payload, '$.total') > 150 \
4187             OR json_type(payload, '$.total') IN ('text', 'array')))"
4188        );
4189        assert_eq!(
4190            sqlite("/orders/*[/payload/customer::tier < 'm']::id").unwrap(),
4191            "(json_valid(payload) AND (json_extract(payload, '$.customer.tier') < 'm' \
4192             OR json_type(payload, '$.customer.tier') = 'array'))"
4193        );
4194        // a bare property: present, as the child form
4195        assert_eq!(
4196            sqlite("/orders/*[/payload::gift]::id").unwrap(),
4197            "(json_valid(payload) AND json_type(payload, '$.gift') IS NOT NULL)"
4198        );
4199        // `!=` keeps a row whose key is absent: its floor is the node
4200        // the property is read on, and on the column itself there is
4201        // none
4202        assert_eq!(
4203            sqlite("/orders/*[/payload/customer::tier != 'x']::id").unwrap(),
4204            "(json_valid(payload) AND json_type(payload, '$.customer') IS NOT NULL)"
4205        );
4206        assert!(sqlite("/orders/*[/payload::status != 'x']::id").is_none());
4207        // PostgreSQL: the equality as a prefilter, the arrays through
4208        assert_eq!(
4209            partial_pushdown(
4210                "/orders/*[/payload/customer/geo::city = 'Lyon']::id",
4211                Some(Dialect::Postgres)
4212            )
4213            .unwrap()
4214            .where_sql,
4215            "(CASE WHEN jsonb_typeof(payload::jsonb #> '{customer,geo,city}') = 'string' \
4216             THEN (payload::jsonb #>> '{customer,geo,city}') = 'Lyon' \
4217             WHEN jsonb_typeof(payload::jsonb #> '{customer,geo,city}') = 'array' \
4218             THEN true ELSE false END)"
4219        );
4220        assert_eq!(
4221            partial_pushdown(
4222                "/orders/*[/payload::total > 150]::id",
4223                Some(Dialect::Postgres)
4224            )
4225            .unwrap()
4226            .where_sql,
4227            "(CASE WHEN jsonb_typeof(payload::jsonb #> '{total}') = 'number' \
4228             THEN ((payload::jsonb #>> '{total}'))::float8 > 150 \
4229             WHEN jsonb_typeof(payload::jsonb #> '{total}') IN ('string', 'array') \
4230             THEN true ELSE false END)"
4231        );
4232        // no type probe, no prefilter
4233        assert!(
4234            partial_pushdown("/orders/*[/payload::total > 150]::id", Some(Dialect::MySql))
4235                .is_none()
4236        );
4237    }
4238
4239    #[test]
4240    fn json_pushdown_refuses_a_container_looking_literal() {
4241        // An extractor answers an array or an object as its JSON
4242        // text, so `'[1,2]'` equalled the array itself, which has no
4243        // value in the graft: the whole push kept a row the scan
4244        // drops. It rides the prefilter (a superset, re-checked).
4245        for q in [
4246            "/orders/*[/data/a:: = '[1,2]']::id",
4247            "/orders/*[/data/a:: = '{\"k\":1}']::id",
4248        ] {
4249            assert!(pushdown(q, Some(Dialect::Sqlite)).is_none(), "{q}");
4250            assert!(pushdown(q, Some(Dialect::Postgres)).is_none(), "{q}");
4251        }
4252        assert_eq!(
4253            partial_pushdown("/orders/*[/data/a:: = '[1,2]']::id", Some(Dialect::Sqlite))
4254                .unwrap()
4255                .where_sql,
4256            "(json_valid(data) AND json_extract(data, '$.a') = '[1,2]')"
4257        );
4258    }
4259
4260    #[test]
4261    fn partial_skips_untranslatable_keeps_prefix() {
4262        // A refused predicate no prefilter covers is skipped — the
4263        // shorter conjunction is still a superset (the engine
4264        // re-applies the original) — rather than aborting the plan.
4265        let p = partial_pushdown(
4266            "/orders/*[::status = 'shipped'][/payload/line_items/*/sku:: = 'X']::id",
4267            Some(Dialect::Sqlite),
4268        )
4269        .unwrap();
4270        assert_eq!(p.where_sql, "status = 'shipped'");
4271    }
4272
4273    #[test]
4274    fn null_literal_compares_use_is_null() {
4275        // `= null` / `!= null` are provably identical to Quarb's
4276        // value_eq(NULL, …) semantics, never the always-UNKNOWN
4277        // `x = NULL`; they translate — and push — in both modes.
4278        assert_eq!(
4279            export("/t/*[::x = null] | ::x").unwrap().query,
4280            "SELECT x FROM t WHERE x IS NULL"
4281        );
4282        assert_eq!(
4283            export("/t/*[::x != null] | ::x").unwrap().query,
4284            "SELECT x FROM t WHERE x IS NOT NULL"
4285        );
4286        assert_eq!(
4287            pushdown("/t/*[::x = null] | ::x", None).unwrap().sql,
4288            "SELECT x FROM t WHERE x IS NULL"
4289        );
4290        assert_eq!(
4291            pushdown("/t/*[::x != null] | ::x", None).unwrap().sql,
4292            "SELECT x FROM t WHERE x IS NOT NULL"
4293        );
4294    }
4295
4296    #[test]
4297    fn ne_and_not_push_in_the_form_that_keeps_the_null_rows() {
4298        // `!=` against a value and `!(...)` hold for a NULL in Quarb
4299        // and are UNKNOWN in SQL. The plan says the cases SQL's
4300        // operators leave out, in SQL every engine reads (the scan's
4301        // answers beside the plan's: tests/sql.rs).
4302        assert_eq!(
4303            pushdown("/t/*[::x != 5] | ::x", None).unwrap().sql,
4304            "SELECT x FROM t WHERE (x <> 5 OR x IS NULL)"
4305        );
4306        assert_eq!(
4307            pushdown("/t/*[::x != ::y] | ::x", None).unwrap().sql,
4308            "SELECT x FROM t WHERE (x <> y OR (x IS NULL AND y IS NOT NULL) OR (x IS NOT NULL \
4309             AND y IS NULL))"
4310        );
4311        assert_eq!(
4312            pushdown("/t/*[!::x = 5] | ::x", None).unwrap().sql,
4313            "SELECT x FROM t WHERE (CASE WHEN x = 5 THEN 0 ELSE 1 END = 1)"
4314        );
4315        // two NULLs are equal in Quarb: an equality of two columns
4316        // says so
4317        assert_eq!(
4318            pushdown("/t/*[::x = ::y] | ::x", None).unwrap().sql,
4319            "SELECT x FROM t WHERE (x = y OR (x IS NULL AND y IS NULL))"
4320        );
4321        // the prefilter takes them too
4322        assert_eq!(
4323            partial_pushdown(&format!("/t/*[::x != 5]{GROUPED}"), None)
4324                .unwrap()
4325                .where_sql,
4326            "(x <> 5 OR x IS NULL)"
4327        );
4328        // The display translation still emits `<>`, flagged with a note.
4329        let t = export("/t/*[::x != 5] | ::x").unwrap();
4330        assert_eq!(t.query, "SELECT x FROM t WHERE x <> 5");
4331        assert!(t.notes.iter().any(|n| n.contains("NULL")));
4332    }
4333
4334    #[test]
4335    fn unescapable_text_literal_refuses_pushdown() {
4336        // A backslash (MySQL/BigQuery escape) or an apostrophe
4337        // (BigQuery rejects '' doubling) has no portable escaping, so
4338        // pushdown refuses; a clean literal still pushes.
4339        assert!(pushdown("/files/*[::path = \"C:\\temp\"] | ::path", None).is_none());
4340        assert!(pushdown("/t/*[::name = \"it's\"] | ::name", None).is_none());
4341        assert!(partial_pushdown(&format!("/t/*[::name = \"it's\"]{GROUPED}"), None).is_none());
4342        assert_eq!(
4343            pushdown("/t/*[::name = \"rare\"] | ::name", None)
4344                .unwrap()
4345                .sql,
4346            "SELECT name FROM t WHERE name = 'rare'"
4347        );
4348    }
4349}