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