Skip to main content

fv_streams_sql/
plan.rs

1//! The typed streaming **logical plan** — our own IR, the seam between the SQL front-end and the
2//! `StageDef` lowering. Small and streaming-aware by construction (unlike DataFusion's `LogicalPlan`);
3//! a future optimiser is a pass over this, and a second dialect is another producer of it.
4
5use sqlparser::ast::{
6    BinaryOperator, DateTimeField, DuplicateTreatment, Expr, Function, FunctionArg, FunctionArgExpr, FunctionArguments,
7    GroupByExpr, JoinConstraint, JoinOperator, Query, Select, SelectItem, SetExpr, Statement, TableFactor,
8    TableWithJoins, Value,
9};
10use sqlparser::dialect::GenericDialect;
11use sqlparser::parser::Parser;
12
13use crate::expr;
14
15/// One projected output column.
16#[derive(Debug, Clone, PartialEq)]
17pub struct Proj {
18    /// The output column name.
19    pub out: String,
20    /// `Some(dialect_expr)` when computed or renamed (lowers to an `applyExpression`); `None` for a
21    /// bare pass-through column named `out` (kept by the `select`).
22    pub expr: Option<String>,
23}
24
25/// One aggregate in a `SELECT` over a window — `op` over `column` (empty for `count(*)`), out `alias`.
26#[derive(Debug, Clone, PartialEq)]
27pub struct AggSpec {
28    pub op: String,
29    pub column: String,
30    pub alias: String,
31    /// The call as written (`count(*)`, `sum(price)`), for `HAVING` to match by.
32    pub call: String,
33}
34
35/// The windowing a `GROUP BY <window-fn>(…)` selects. An optional trailing `INTERVAL` on any window
36/// function is the **allowed lateness** — how long a window stays open for late events after its end
37/// (the engine's `allowedLatenessMs`); `None` leaves it at the engine default. q4/q5 need a large
38/// lateness so no out-of-order bid is dropped from a closed window.
39#[derive(Debug, Clone, PartialEq)]
40pub enum Win {
41    /// `TUMBLE(ts, INTERVAL <size> [, INTERVAL <lateness>])` — fixed windows.
42    Tumble { size_ms: i64, lateness_ms: Option<i64> },
43    /// `HOP(ts, INTERVAL <slide>, INTERVAL <size> [, INTERVAL <lateness>])` — sliding windows.
44    Hop {
45        slide_ms: i64,
46        size_ms: i64,
47        lateness_ms: Option<i64>,
48    },
49    /// `SESSION(ts, INTERVAL <gap> [, INTERVAL <lateness>])` — dynamic session windows.
50    Session { gap_ms: i64, lateness_ms: Option<i64> },
51}
52
53/// The early-firing trigger a trailing `EMIT CHANGES EVERY …` clause selects — the 5a updating
54/// aggregate. sqlparser does not know `EMIT`, so a small lexical pre-pass ([`split_emit`]) strips
55/// and parses this clause before the rest of the statement reaches sqlparser (design §10 / A).
56#[derive(Debug, Clone, Copy, PartialEq)]
57pub enum Trigger {
58    EveryRows(u64),
59    EveryMs(i64),
60}
61
62/// How a two-input `JOIN` maps to an operator. An interval predicate (`ts BETWEEN …`) in the `ON`
63/// makes it a stream×stream interval join; a plain equi-join makes the right side a **bounded
64/// table** — the 5b lookup join.
65#[derive(Debug, Clone, PartialEq)]
66pub enum JoinKind {
67    /// `JOIN … ON k=k AND ts BETWEEN … ` → `streamJoin`.
68    Interval { time_column: String, window_ms: i64 },
69    /// `JOIN <bounded> ON k=k` → the 5b `lookupJoin` (right = the bounded table).
70    Lookup,
71}
72
73/// The typed logical plan. 5c-i covers the stateless shape (source → filter → project → sink);
74/// 5c-ii adds the windowed aggregate, the updating aggregate, and the joins.
75#[derive(Debug, Clone, PartialEq)]
76pub enum Plan {
77    /// `FROM <dataset>` — a source dataset by name.
78    Source(String),
79    /// `FROM <left> JOIN <right> ON <key> [interval]` — a two-input join.
80    Join {
81        left: Box<Plan>,
82        right: Box<Plan>,
83        join_key: String,
84        kind: JoinKind,
85        /// Right columns the query names with the right's qualifier → their output name (the
86        /// join's `rightRename`), so `a.ts` can never mean the left's `ts`.
87        right_rename: std::collections::BTreeMap<String, String>,
88    },
89    /// `WHERE <predicate>` — the predicate as a value-dialect string.
90    Filter { input: Box<Plan>, predicate: String },
91    /// `SELECT …`. `star` = `SELECT *` (pass-through, no step); else the projected columns.
92    Project {
93        input: Box<Plan>,
94        star: bool,
95        items: Vec<Proj>,
96    },
97    /// `SELECT … FROM … GROUP BY <window-fn>(ts,…), <keys>` — a windowed keyed aggregate. A
98    /// `Some(trigger)` makes it the **5a updating aggregate** (early firing).
99    Aggregate {
100        input: Box<Plan>,
101        time_column: String,
102        window: Win,
103        group_by: Vec<String>,
104        /// Pre-aggregate computed columns (an aggregate's expression argument, e.g. a `CASE` fed to
105        /// `count(DISTINCT …)`) — lowered to `applyExpression` steps BEFORE the aggregate.
106        pre: Vec<Proj>,
107        aggs: Vec<AggSpec>,
108        /// Post-aggregate computed columns — a `SELECT` expression over the window/group/aggregates
109        /// (e.g. `day = substring(epochToIso(windowStart), 1, 10)`), lowered to `applyExpression`
110        /// steps AFTER the aggregate (the q15/q16 day/minute columns).
111        post: Vec<Proj>,
112        /// `HAVING <predicate>` — a filter over the aggregate's output rows (the window, the group
113        /// columns, the aggregate aliases), lowered to a `filter` step AFTER the aggregate and its
114        /// post columns.
115        having: Option<String>,
116        trigger: Option<Trigger>,
117    },
118    /// `SELECT … FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC]) AS rn FROM s)
119    /// WHERE rn <= n` — the streaming top-N idiom → the `topN` operator.
120    TopN {
121        input: Box<Plan>,
122        group_by: Vec<String>,
123        order_by: String,
124        /// A second `ORDER BY` column → the tie-break (`tieBy`, ascending).
125        tie_by: Option<String>,
126        descending: bool,
127        n: usize,
128    },
129    /// A CTE (`WITH <name> AS (<subquery>)`): the sub-plan `input` materialises as its own stage
130    /// under `name`, and downstream references to `name` read that stage. The multi-stage boundary —
131    /// the SQL equivalent of a named intermediate dataset in the TOML `[[transforms]]` DAG.
132    Named { name: String, input: Box<Plan> },
133    /// `INSERT INTO <dataset>` — the terminal sink dataset by name.
134    Sink { input: Box<Plan>, name: String },
135}
136
137/// Parse one streaming SQL statement into a [`Plan`]. Fails LOUDLY, streaming-aware, on anything
138/// outside the supported subset.
139pub fn parse(sql: &str) -> Result<Plan, String> {
140    // Strip a trailing `EMIT CHANGES EVERY …` (sqlparser does not know it) before parsing the rest.
141    let (sql, trigger) = split_emit(sql)?;
142    let plan = parse_statement(&sql)?;
143    match trigger {
144        Some(t) => attach_trigger(plan, t),
145        None => Ok(plan),
146    }
147}
148
149fn parse_statement(sql: &str) -> Result<Plan, String> {
150    let mut stmts = Parser::parse_sql(&GenericDialect {}, sql).map_err(|e| format!("SQL parse error: {e}"))?;
151    if stmts.len() != 1 {
152        return Err(format!("expected exactly one statement, got {}", stmts.len()));
153    }
154    match stmts.pop().expect("one") {
155        Statement::Insert(insert) => {
156            let sink = object_name(&insert.table.to_string());
157            let query = insert
158                .source
159                .ok_or("INSERT INTO <sink> needs a SELECT (`INSERT INTO out SELECT …`)")?;
160            let input = plan_query(&query)?;
161            Ok(Plan::Sink {
162                input: Box::new(input),
163                name: sink,
164            })
165        }
166        Statement::Query(_) => Err("a streaming statement must write to a sink: `INSERT INTO <sink> SELECT …`".into()),
167        other => Err(format!(
168            "unsupported statement `{}` — the supported shape is `INSERT INTO <sink> SELECT … FROM … [WHERE …]`",
169            statement_kind(&other)
170        )),
171    }
172}
173
174fn plan_query(query: &Query) -> Result<Plan, String> {
175    // `WITH name AS (subquery), …` — each CTE becomes a named intermediate stream (the multi-stage
176    // boundary). SQL scoping: a CTE sees only the CTEs declared before it, so bodies are bound in
177    // order against the ones already built; the outer body binds against all of them.
178    let ctes = parse_ctes(query.with.as_ref())?;
179    if query.order_by.is_some() {
180        return Err("ORDER BY over an unbounded stream has no streaming meaning — use `ROW_NUMBER() OVER (PARTITION BY …) <= n` for a top-N".into());
181    }
182    if query.limit_clause.is_some() {
183        return Err("LIMIT over an unbounded stream is not supported — use a top-N (`ROW_NUMBER() … <= n`)".into());
184    }
185    let select = match query.body.as_ref() {
186        SetExpr::Select(s) => s.as_ref(),
187        SetExpr::SetOperation { .. } => {
188            return Err("UNION / INTERSECT / EXCEPT are not supported — a stage reads one input; write one pipeline per branch into the same sink (`INSERT INTO out SELECT … FROM a` and `INSERT INTO out SELECT … FROM b`), which is UNION ALL by construction".into())
189        }
190        _ => return Err("only a SELECT body is supported".into()),
191    };
192    Ok(bind_ctes(plan_select(select)?, &ctes))
193}
194
195/// Parse a `WITH` clause into `(name, plan)` in declaration order. Each CTE body is planned and then
196/// bound against the CTEs declared before it (SQL scoping), so later stages can read earlier ones.
197/// Recursive CTEs (`WITH RECURSIVE`) are not supported — there is no streaming fixpoint.
198fn parse_ctes(with: Option<&sqlparser::ast::With>) -> Result<Vec<(String, Plan)>, String> {
199    let Some(with) = with else { return Ok(Vec::new()) };
200    if with.recursive {
201        return Err("WITH RECURSIVE is not supported — a streaming CTE has no fixpoint".into());
202    }
203    let mut bound: Vec<(String, Plan)> = Vec::new();
204    for cte in &with.cte_tables {
205        let name = object_name(&cte.alias.name.value);
206        let plan = bind_ctes(plan_query(&cte.query)?, &bound);
207        bound.push((name, plan));
208    }
209    Ok(bound)
210}
211
212/// Replace every `Source(name)` that names a CTE with a `Named { name, <cte plan> }` boundary, so
213/// lowering materialises it as its own stage. Recurses through the whole plan; a non-CTE `Source`
214/// (a base dataset) is left untouched.
215fn bind_ctes(plan: Plan, ctes: &[(String, Plan)]) -> Plan {
216    if ctes.is_empty() {
217        return plan;
218    }
219    let boxed = |p: Box<Plan>| Box::new(bind_ctes(*p, ctes));
220    match plan {
221        Plan::Source(name) => match ctes.iter().find(|(n, _)| *n == name) {
222            Some((_, cte)) => Plan::Named {
223                name,
224                input: Box::new(cte.clone()),
225            },
226            None => Plan::Source(name),
227        },
228        Plan::Named { name, input } => Plan::Named {
229            name,
230            input: boxed(input),
231        },
232        Plan::Join {
233            left,
234            right,
235            join_key,
236            kind,
237            right_rename,
238        } => Plan::Join {
239            left: boxed(left),
240            right: boxed(right),
241            join_key,
242            kind,
243            right_rename,
244        },
245        Plan::Filter { input, predicate } => Plan::Filter {
246            input: boxed(input),
247            predicate,
248        },
249        Plan::Project { input, star, items } => Plan::Project {
250            input: boxed(input),
251            star,
252            items,
253        },
254        Plan::Aggregate {
255            input,
256            time_column,
257            window,
258            group_by,
259            pre,
260            aggs,
261            post,
262            having,
263            trigger,
264        } => Plan::Aggregate {
265            input: boxed(input),
266            time_column,
267            window,
268            group_by,
269            having,
270            pre,
271            aggs,
272            post,
273            trigger,
274        },
275        Plan::TopN {
276            input,
277            group_by,
278            order_by,
279            tie_by,
280            descending,
281            n,
282        } => Plan::TopN {
283            input: boxed(input),
284            group_by,
285            order_by,
286            tie_by,
287            descending,
288            n,
289        },
290        Plan::Sink { input, name } => Plan::Sink {
291            input: boxed(input),
292            name,
293        },
294    }
295}
296
297fn plan_select(select: &Select) -> Result<Plan, String> {
298    // The ROW_NUMBER top-N idiom (a subquery + `WHERE rn <= n`) → the topN operator, projected by
299    // the outer SELECT (the rank filter is consumed by the operator).
300    if let Some(topn) = detect_topn(select)? {
301        return parse_projection(topn, &select.projection);
302    }
303
304    // FROM: one table, optionally with one JOIN.
305    if select.from.len() != 1 {
306        return Err(format!(
307            "expected one FROM item (comma joins are not supported — use `JOIN … ON …`), got {}",
308            select.from.len()
309        ));
310    }
311    let mut source = parse_from(&select.from[0])?;
312    // a JOIN: every `t.col` is resolved against the two inputs on a copy of the query, so the
313    // qualifier decides which side a column comes from (the planner knows no schemas; the join keeps
314    // a right column the query names under a name only the right can have)
315    let resolved;
316    let select = if let Plan::Join {
317        join_key, right_rename, ..
318    } = &mut source
319    {
320        let (q, renames) = resolve_join_qualifiers(select, join_key)?;
321        *right_rename = renames;
322        resolved = q;
323        &resolved
324    } else {
325        select
326    };
327
328    // WHERE → filter (reads the input columns, so it sits below the aggregate/projection).
329    let filtered = match &select.selection {
330        Some(pred) => Plan::Filter {
331            input: Box::new(source),
332            predicate: expr::to_dialect(pred)?,
333        },
334        None => source,
335    };
336
337    // GROUP BY <window-fn>(…), <keys> → a windowed keyed aggregate.
338    let group_exprs = match &select.group_by {
339        GroupByExpr::Expressions(exprs, _) if !exprs.is_empty() => Some(exprs.as_slice()),
340        GroupByExpr::All(_) => return Err("GROUP BY ALL is not supported".into()),
341        _ => None,
342    };
343    if let Some(gexprs) = group_exprs {
344        return plan_aggregate(filtered, gexprs, &select.projection, select.having.as_ref());
345    }
346    if select.having.is_some() {
347        return Err("HAVING without GROUP BY has no aggregate to filter — use WHERE".into());
348    }
349
350    parse_projection(filtered, &select.projection)
351}
352
353/// The `SELECT` projection over an already-built input → a `Project` node.
354fn parse_projection(input: Plan, projection: &[SelectItem]) -> Result<Plan, String> {
355    let mut star = false;
356    let mut items = Vec::new();
357    for item in projection {
358        match item {
359            SelectItem::Wildcard(_) => star = true,
360            SelectItem::UnnamedExpr(e) => {
361                let out = column_name_of(e).ok_or("a projected expression needs an alias (`<expr> AS name`)")?;
362                match e {
363                    Expr::Identifier(_) | Expr::CompoundIdentifier(_) => items.push(Proj { out, expr: None }),
364                    _ => items.push(Proj {
365                        out,
366                        expr: Some(expr::to_dialect(e)?),
367                    }),
368                }
369            }
370            SelectItem::ExprWithAlias { expr: e, alias } => {
371                items.push(Proj {
372                    out: alias.value.clone(),
373                    expr: Some(expr::to_dialect(e)?),
374                });
375            }
376            // `t.*` over a single-table query is `*`: the qualifier names the one input.
377            SelectItem::QualifiedWildcard(_, _) => star = true,
378            _ => return Err("multi-alias projections are not supported — list the columns".into()),
379        }
380    }
381    let mut seen = std::collections::HashSet::new();
382    if let Some(dup) = items.iter().find(|p| !seen.insert(p.out.as_str())) {
383        return Err(format!(
384            "two SELECT columns are named `{}` — give one an alias (`… AS other_name`)",
385            dup.out
386        ));
387    }
388    if star && items.iter().any(|p| p.expr.is_none()) {
389        return Err("`SELECT *, <bare column>` is redundant — `*` already keeps every column; with `*` add only computed columns (`*, <expr> AS name`)".into());
390    }
391    Ok(Plan::Project {
392        input: Box::new(input),
393        star,
394        items,
395    })
396}
397
398/// Recognise `FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC]) AS rn FROM s)
399/// WHERE rn <= n` → a `TopN` over the inner source. `Ok(None)` when it is not that shape.
400fn detect_topn(select: &Select) -> Result<Option<Plan>, String> {
401    if select.from.len() != 1 || !select.from[0].joins.is_empty() {
402        return Ok(None);
403    }
404    let TableFactor::Derived { subquery, .. } = &select.from[0].relation else {
405        return Ok(None);
406    };
407    let SetExpr::Select(inner) = subquery.body.as_ref() else {
408        return Ok(None);
409    };
410    // find ROW_NUMBER() OVER (…) AS <rn>
411    let mut rn_fn = None;
412    for item in &inner.projection {
413        if let SelectItem::ExprWithAlias {
414            expr: Expr::Function(f),
415            alias,
416        } = item
417        {
418            if f.name.to_string().eq_ignore_ascii_case("row_number") {
419                rn_fn = Some((f, alias.value.clone()));
420            }
421        }
422    }
423    let Some((f, rn)) = rn_fn else { return Ok(None) }; // no ROW_NUMBER → not a top-N
424    let Some(sqlparser::ast::WindowType::WindowSpec(ws)) = &f.over else {
425        return Err("ROW_NUMBER() needs `OVER (PARTITION BY … ORDER BY …)`".into());
426    };
427    let group_by: Vec<String> = ws.partition_by.iter().filter_map(column_name_of).collect();
428    let ob = ws
429        .order_by
430        .first()
431        .ok_or("ROW_NUMBER() OVER needs an ORDER BY column")?;
432    let order_by = column_name_of(&ob.expr).ok_or("ROW_NUMBER() ORDER BY must be a column")?;
433    let descending = ob.options.asc == Some(false); // DESC
434                                                    // a second ORDER BY column is the tie-break (ascending, like the operator's `tieBy`).
435    let tie_by = match ws.order_by.get(1) {
436        Some(t) => Some(column_name_of(&t.expr).ok_or("ROW_NUMBER() tie-break must be a column")?),
437        None => None,
438    };
439    if inner.from.len() != 1 || !inner.from[0].joins.is_empty() {
440        return Err("the ROW_NUMBER subquery must read exactly one source".into());
441    }
442    let source = table_source(&inner.from[0].relation)?;
443    // the subquery's WHERE (e.g. `type = 'bid'`) filters before the rank.
444    let input = match &inner.selection {
445        Some(pred) => Plan::Filter {
446            input: Box::new(source),
447            predicate: expr::to_dialect(pred)?,
448        },
449        None => source,
450    };
451    let n = rank_limit(select.selection.as_ref(), &rn)?;
452    Ok(Some(Plan::TopN {
453        input: Box::new(input),
454        group_by,
455        order_by,
456        tie_by,
457        descending,
458        n,
459    }))
460}
461
462/// The `n` from an outer `WHERE <rn> <= n` (or `<rn> = 1`) top-N filter.
463fn rank_limit(where_: Option<&Expr>, rn: &str) -> Result<usize, String> {
464    let e = where_.ok_or_else(|| format!("a ROW_NUMBER top-N needs an outer `WHERE {rn} <= n`"))?;
465    let is_rn = |x: &Expr| column_name_of(x).as_deref() == Some(rn);
466    match e {
467        Expr::BinaryOp {
468            left,
469            op: BinaryOperator::LtEq,
470            right,
471        } if is_rn(left) => literal_usize(right),
472        Expr::BinaryOp {
473            left,
474            op: BinaryOperator::Eq,
475            right,
476        } if is_rn(left) => match literal_usize(right)? {
477            1 => Ok(1),
478            _ => Err("a `= n` top-N filter only supports n = 1 — use `<= n`".into()),
479        },
480        _ => Err(format!("the top-N filter must be `{rn} <= n`")),
481    }
482}
483
484fn literal_usize(e: &Expr) -> Result<usize, String> {
485    match e {
486        Expr::Value(v) => match &v.value {
487            Value::Number(s, _) => s.trim().parse().map_err(|_| format!("`{s}` is not a whole number")),
488            other => Err(format!("expected a number, got `{other}`")),
489        },
490        other => Err(format!("expected a number, got `{other}`")),
491    }
492}
493
494/// `GROUP BY <window-fn>(ts,…), <keys>` + aggregate projections → a windowed keyed aggregate.
495fn plan_aggregate(
496    input: Plan,
497    group_exprs: &[Expr],
498    projection: &[SelectItem],
499    having: Option<&Expr>,
500) -> Result<Plan, String> {
501    let mut window: Option<(String, Win)> = None;
502    let mut group_by = Vec::new();
503    for g in group_exprs {
504        match g {
505            Expr::Function(f) if is_window_fn(f) => {
506                if window.is_some() {
507                    return Err("only one window function may appear in GROUP BY".into());
508                }
509                window = Some(parse_window_fn(f)?);
510            }
511            Expr::Identifier(id) => group_by.push(id.value.clone()),
512            Expr::CompoundIdentifier(p) => group_by.push(p.last().expect("non-empty").value.clone()),
513            other => {
514                return Err(format!(
515                    "unsupported GROUP BY item `{other}` — a window function (`TUMBLE/HOP/SESSION`) or a plain column"
516                ))
517            }
518        }
519    }
520    let (time_column, window) = window.ok_or(
521        "a streaming GROUP BY needs a window: `GROUP BY <keys>, TUMBLE|HOP|SESSION(<time column>, INTERVAL …)`; for results before a window closes, add `EMIT CHANGES EVERY <n> ROWS|MS`",
522    )?;
523    let (pre, aggs, post) = parse_agg_select(projection)?;
524    if aggs.is_empty() {
525        return Err("a windowed GROUP BY needs at least one aggregate in SELECT (count/sum/avg/min/max)".into());
526    }
527    // HAVING reads the aggregate's OUTPUT: an aggregate call in it must match a projected alias
528    // (`HAVING n > 5` where `count(*) AS n`) — the value dialect has no aggregate functions.
529    let having = match having {
530        Some(h) => Some(having_to_dialect(h, &aggs)?),
531        None => None,
532    };
533    Ok(Plan::Aggregate {
534        input: Box::new(input),
535        time_column,
536        window,
537        group_by,
538        pre,
539        aggs,
540        post,
541        having,
542        trigger: None,
543    })
544}
545
546/// `HAVING` in the value dialect, with any aggregate call rewritten to the alias that projects it
547/// (`HAVING count(*) > 5` → `n > 5` when `count(*) AS n`); an aggregate call with no projected
548/// alias is a loud error naming the fix.
549fn having_to_dialect(h: &Expr, aggs: &[AggSpec]) -> Result<String, String> {
550    fn rewrite(e: &Expr, aggs: &[AggSpec]) -> Result<Expr, String> {
551        Ok(match e {
552            Expr::Function(f) if is_agg_name(&f.name.to_string().to_lowercase()) => {
553                let call = f.to_string().to_lowercase().replace(' ', "");
554                let hit = aggs.iter().find(|a| a.call.to_lowercase().replace(' ', "") == call).ok_or_else(|| {
555                    format!("HAVING `{f}` has no matching aggregate in SELECT — project it (`{f} AS name`) and filter on the name")
556                })?;
557                Expr::Identifier(sqlparser::ast::Ident::new(hit.alias.clone()))
558            }
559            Expr::BinaryOp { left, op, right } => Expr::BinaryOp {
560                left: Box::new(rewrite(left, aggs)?),
561                op: op.clone(),
562                right: Box::new(rewrite(right, aggs)?),
563            },
564            Expr::UnaryOp { op, expr } => Expr::UnaryOp {
565                op: *op,
566                expr: Box::new(rewrite(expr, aggs)?),
567            },
568            Expr::Nested(inner) => Expr::Nested(Box::new(rewrite(inner, aggs)?)),
569            other => other.clone(),
570        })
571    }
572    expr::to_dialect(&rewrite(h, aggs)?)
573}
574
575/// Strip a trailing `EMIT CHANGES EVERY <n> ROWS|MS` clause (case-insensitive, outside string
576/// literals). Returns the SQL without it and the parsed trigger, if any.
577fn split_emit(sql: &str) -> Result<(String, Option<Trigger>), String> {
578    let lower = sql.to_ascii_lowercase();
579    let bytes = lower.as_bytes();
580    let is_word = |b: u8| b.is_ascii_alphanumeric() || b == b'_';
581    let mut in_str = false;
582    let mut at = None;
583    let mut i = 0usize;
584    while i < bytes.len() {
585        if bytes[i] == b'\'' {
586            in_str = !in_str;
587            i += 1;
588            continue;
589        }
590        if !in_str
591            && lower[i..].starts_with("emit")
592            && (i == 0 || !is_word(bytes[i - 1]))
593            && (i + 4 >= bytes.len() || !is_word(bytes[i + 4]))
594        {
595            at = Some(i); // the last unquoted `emit` wins (it is a trailing clause)
596        }
597        i += 1;
598    }
599    let Some(at) = at else {
600        return Ok((sql.to_string(), None));
601    };
602    let head = sql[..at].trim_end().to_string();
603    let trigger = parse_emit(&sql[at..])?;
604    Ok((head, Some(trigger)))
605}
606
607fn parse_emit(clause: &str) -> Result<Trigger, String> {
608    let toks: Vec<String> = clause.split_whitespace().map(|s| s.to_ascii_lowercase()).collect();
609    let shape = "`EMIT CHANGES EVERY <n> ROWS` or `EMIT CHANGES EVERY <n> MS`";
610    if toks.len() < 2 || toks[0] != "emit" || toks[1] != "changes" {
611        return Err(format!("unrecognised EMIT clause — use {shape}"));
612    }
613    if toks.len() == 2 {
614        return Err(format!("`EMIT CHANGES` needs a cadence: {shape}"));
615    }
616    if toks.len() != 5 || toks[2] != "every" {
617        return Err(format!("EMIT clause: expected {shape}"));
618    }
619    let n: u64 = toks[3]
620        .parse()
621        .map_err(|_| format!("EMIT cadence `{}` is not a whole number", toks[3]))?;
622    match toks[4].as_str() {
623        "rows" | "row" => Ok(Trigger::EveryRows(n)),
624        "ms" | "milliseconds" | "millisecond" => Ok(Trigger::EveryMs(n as i64)),
625        other => Err(format!("EMIT cadence unit `{other}` — use ROWS or MS")),
626    }
627}
628
629/// Attach a trigger to the plan's windowed aggregate (the 5a updating aggregate). The trigger is
630/// valid only on a windowed (`TUMBLE`/`HOP`) aggregate — the operator that early-fires.
631fn attach_trigger(plan: Plan, trigger: Trigger) -> Result<Plan, String> {
632    match plan {
633        Plan::Sink { input, name } => Ok(Plan::Sink {
634            input: Box::new(attach_trigger(*input, trigger)?),
635            name,
636        }),
637        Plan::Aggregate {
638            input,
639            time_column,
640            window,
641            group_by,
642            pre,
643            aggs,
644            post,
645            having,
646            trigger: _,
647        } => {
648            if matches!(window, Win::Session { .. }) {
649                return Err("EMIT CHANGES is supported on windowed aggregates (TUMBLE / HOP), not sessions".into());
650            }
651            Ok(Plan::Aggregate {
652                input,
653                time_column,
654                window,
655                group_by,
656                pre,
657                aggs,
658                post,
659                having,
660                trigger: Some(trigger),
661            })
662        }
663        _ => Err("EMIT CHANGES needs a windowed GROUP BY aggregate (`GROUP BY <keys>, TUMBLE(ts, INTERVAL …)`)".into()),
664    }
665}
666
667fn is_window_fn(f: &Function) -> bool {
668    matches!(f.name.to_string().to_uppercase().as_str(), "TUMBLE" | "HOP" | "SESSION")
669}
670
671/// `TUMBLE(ts, INTERVAL <size>)` / `HOP(ts, INTERVAL <slide>, INTERVAL <size>)` /
672/// `SESSION(ts, INTERVAL <gap>)` → (event-time column, window).
673fn parse_window_fn(f: &Function) -> Result<(String, Win), String> {
674    let name = f.name.to_string().to_uppercase();
675    let args = fn_args(f)?;
676    let time_col = |e: &Expr| -> Result<String, String> {
677        match e {
678            Expr::Identifier(id) => Ok(id.value.clone()),
679            Expr::CompoundIdentifier(p) => Ok(p.last().expect("non-empty").value.clone()),
680            _ => Err(format!("{name}(…)'s first argument must be the event-time column")),
681        }
682    };
683    // an optional trailing INTERVAL beyond the base arity is the allowed lateness.
684    let lateness = |base: usize| -> Result<Option<i64>, String> {
685        match args.len() {
686            n if n == base => Ok(None),
687            n if n == base + 1 => Ok(Some(interval_ms(args[base])?)),
688            _ => Err(String::new()), // arity handled by the arm guards below
689        }
690    };
691    match name.as_str() {
692        "TUMBLE" if args.len() == 2 || args.len() == 3 => Ok((
693            time_col(args[0])?,
694            Win::Tumble {
695                size_ms: interval_ms(args[1])?,
696                lateness_ms: lateness(2)?,
697            },
698        )),
699        "HOP" if args.len() == 3 || args.len() == 4 => Ok((
700            time_col(args[0])?,
701            Win::Hop {
702                slide_ms: interval_ms(args[1])?,
703                size_ms: interval_ms(args[2])?,
704                lateness_ms: lateness(3)?,
705            },
706        )),
707        "SESSION" if args.len() == 2 || args.len() == 3 => Ok((
708            time_col(args[0])?,
709            Win::Session {
710                gap_ms: interval_ms(args[1])?,
711                lateness_ms: lateness(2)?,
712            },
713        )),
714        "TUMBLE" | "SESSION" => Err(format!(
715            "{name}(ts, INTERVAL <size> [, INTERVAL <lateness>]) takes 2 or 3 arguments"
716        )),
717        "HOP" => {
718            Err("HOP(ts, INTERVAL <slide>, INTERVAL <size> [, INTERVAL <lateness>]) takes 3 or 4 arguments".into())
719        }
720        other => Err(format!("unsupported window function `{other}`")),
721    }
722}
723
724fn is_agg_name(name: &str) -> bool {
725    matches!(name, "count" | "sum" | "avg" | "min" | "max")
726}
727
728/// Classify a windowed `SELECT`'s projections: aggregate calls (→ `aggs`, with any expression
729/// arguments hoisted to `pre` computed columns), bare group columns (skipped — GROUP BY captured
730/// them), and other computed columns over the window/group/aggregates (→ `post` applyExpression
731/// steps after the aggregate, e.g. `day = substring(epochToIso(windowStart), 1, 10)`).
732#[allow(clippy::type_complexity)]
733fn parse_agg_select(projection: &[SelectItem]) -> Result<(Vec<Proj>, Vec<AggSpec>, Vec<Proj>), String> {
734    let mut pre: Vec<Proj> = Vec::new();
735    let mut aggs = Vec::new();
736    let mut post: Vec<Proj> = Vec::new();
737    for item in projection {
738        let (e, alias) = match item {
739            SelectItem::ExprWithAlias { expr, alias } => (expr, Some(alias.value.clone())),
740            SelectItem::UnnamedExpr(e) => (e, None),
741            SelectItem::Wildcard(_) => return Err("`SELECT *` is not allowed with GROUP BY".into()),
742            _ => return Err("unsupported projection in a GROUP BY query".into()),
743        };
744        match e {
745            Expr::Function(f) if is_agg_name(&f.name.to_string().to_lowercase()) => {
746                aggs.push(agg_of(f, alias, &mut pre)?);
747            }
748            // a bare group column — already in the aggregate's output.
749            Expr::Identifier(_) | Expr::CompoundIdentifier(_) => {}
750            // a computed column over the window/group/aggregates → a post-aggregate applyExpression.
751            _ => {
752                let out =
753                    alias.ok_or("a computed SELECT column in a GROUP BY query needs an alias (`<expr> AS name`)")?;
754                post.push(Proj {
755                    out,
756                    expr: Some(expr::to_dialect(e)?),
757                });
758            }
759        }
760    }
761    Ok((pre, aggs, post))
762}
763
764fn agg_of(f: &Function, alias: Option<String>, pre: &mut Vec<Proj>) -> Result<AggSpec, String> {
765    let name = f.name.to_string().to_lowercase();
766    let alias = alias.ok_or_else(|| format!("aggregate `{name}(…)` needs an alias (`{name}(…) AS name`)"))?;
767    let distinct = matches!(
768        &f.args,
769        FunctionArguments::List(l) if l.duplicate_treatment == Some(DuplicateTreatment::Distinct)
770    );
771    let op = match (name.as_str(), distinct) {
772        ("count", false) => "count",
773        ("count", true) => "countDistinct",
774        ("sum", false) => "sum",
775        ("avg", false) => "avg",
776        ("min", false) => "min",
777        ("max", false) => "max",
778        (other, true) => return Err(format!("DISTINCT is only supported on `count()`, not `{other}()`")),
779        (other, false) => {
780            return Err(format!(
781                "unsupported aggregate `{other}` — count/sum/avg/min/max/count(DISTINCT …)"
782            ))
783        }
784    };
785    let args = fn_args(f)?;
786    // count(*) has no column; a bare column feeds the aggregate directly; an EXPRESSION argument
787    // (CASE, arithmetic) becomes a synthetic pre-aggregate column (the q15/q16 null-mask shape).
788    let column = match args.first() {
789        None => String::new(),
790        Some(Expr::Identifier(id)) => id.value.clone(),
791        Some(Expr::CompoundIdentifier(p)) => p.last().expect("non-empty").value.clone(),
792        Some(other) => {
793            let synth = format!("_fv_agg{}", pre.len());
794            pre.push(Proj {
795                out: synth.clone(),
796                expr: Some(expr::to_dialect(other)?),
797            });
798            synth
799        }
800    };
801    Ok(AggSpec {
802        call: f.to_string(),
803        op: op.to_string(),
804        column,
805        alias,
806    })
807}
808
809/// The plain `Expr` arguments of a function call (a `*` wildcard contributes none — `count(*)`).
810fn fn_args(f: &Function) -> Result<Vec<&Expr>, String> {
811    let list = match &f.args {
812        FunctionArguments::List(l) => &l.args,
813        FunctionArguments::None => return Ok(Vec::new()),
814        FunctionArguments::Subquery(_) => return Err("subquery arguments are not supported".into()),
815    };
816    let mut out = Vec::new();
817    for a in list {
818        match a {
819            FunctionArg::Unnamed(FunctionArgExpr::Expr(e)) => out.push(e),
820            FunctionArg::Unnamed(FunctionArgExpr::Wildcard) => {}
821            _ => return Err("unsupported function argument (named / qualified wildcard)".into()),
822        }
823    }
824    Ok(out)
825}
826
827/// An `INTERVAL '<n>' <unit>` as milliseconds.
828fn interval_ms(e: &Expr) -> Result<i64, String> {
829    let Expr::Interval(iv) = e else {
830        return Err(format!("expected an INTERVAL (e.g. `INTERVAL '10' SECOND`), got `{e}`"));
831    };
832    let n: i64 = match iv.value.as_ref() {
833        Expr::Value(v) => match &v.value {
834            Value::SingleQuotedString(s) | Value::Number(s, _) => s
835                .trim()
836                .parse()
837                .map_err(|_| format!("interval value `{s}` is not an integer"))?,
838            other => return Err(format!("unsupported interval value `{other}`")),
839        },
840        other => return Err(format!("unsupported interval value `{other}`")),
841    };
842    let unit = match &iv.leading_field {
843        Some(DateTimeField::Millisecond) => 1,
844        Some(DateTimeField::Second) => 1_000,
845        Some(DateTimeField::Minute) => 60_000,
846        Some(DateTimeField::Hour) => 3_600_000,
847        Some(DateTimeField::Day) => 86_400_000,
848        other => {
849            return Err(format!(
850                "unsupported interval unit `{other:?}` — use MILLISECOND / SECOND / MINUTE / HOUR / DAY"
851            ))
852        }
853    };
854    Ok(n * unit)
855}
856
857/// The name a JOIN input answers to as a qualifier: its alias when it has one (an alias replaces the
858/// table's name, as in SQL), else the table's name.
859fn side_names(tf: &TableFactor) -> Vec<String> {
860    match tf {
861        TableFactor::Table { alias: Some(a), .. } => vec![a.name.value.clone()],
862        TableFactor::Table { name, .. } => vec![object_name(&name.to_string())],
863        _ => Vec::new(),
864    }
865}
866
867/// Resolve every table-qualified column of a JOIN query — in the projection, WHERE, GROUP BY and
868/// HAVING (the ON clause is parsed on its own) — against the two inputs: `l.c` is the left's `c`;
869/// `r.c` is the right's, kept by the join as `<r>_c` (its `rightRename`) unless `c` is the join key
870/// (equal on both sides). A bare `r.c` in the projection keeps the output name `c`, as SQL names it.
871/// An unknown qualifier, a qualifier both inputs answer to, and `t.*` are refused. Returns the
872/// rewritten query and the join's renames.
873fn resolve_join_qualifiers(
874    select: &Select,
875    join_key: &str,
876) -> Result<(Select, std::collections::BTreeMap<String, String>), String> {
877    use std::ops::ControlFlow;
878    let left = side_names(&select.from[0].relation);
879    let right = side_names(&select.from[0].joins[0].relation);
880    if let Some(both) = left.iter().find(|n| right.contains(n)) {
881        return Err(format!(
882            "both sides of the JOIN answer to `{both}` — give them different aliases (`FROM {both} a JOIN {both} b`)"
883        ));
884    }
885    let right_q = right.first().cloned().unwrap_or_default();
886    let mut renames = std::collections::BTreeMap::new();
887    // `t.c` → the column's name in the join's output, or why it cannot be resolved
888    let mut resolve = |parts: &[sqlparser::ast::Ident]| -> Result<String, String> {
889        let [q, c] = parts else {
890            return Err(format!(
891                "`{}`: a column is `table.column` or `column`",
892                parts.iter().map(|p| p.value.as_str()).collect::<Vec<_>>().join(".")
893            ));
894        };
895        let (q, c) = (q.value.as_str(), c.value.as_str());
896        if left.iter().any(|n| n == q) || c == join_key && right.iter().any(|n| n == q) {
897            Ok(c.to_string())
898        } else if right.iter().any(|n| n == q) {
899            let out = format!("{right_q}_{c}");
900            renames.insert(c.to_string(), out.clone());
901            Ok(out)
902        } else {
903            Err(format!(
904                "`{q}.{c}`: `{q}` is not a table of this JOIN (it names {})",
905                left.iter()
906                    .chain(&right)
907                    .map(|n| format!("`{n}`"))
908                    .collect::<Vec<_>>()
909                    .join(", ")
910            ))
911        }
912    };
913    let mut q = select.clone();
914    let mut failed: Option<String> = None;
915    // a bare `r.c` projected keeps its SQL name `c`
916    for item in q.projection.iter_mut() {
917        match item {
918            SelectItem::UnnamedExpr(Expr::CompoundIdentifier(parts)) => {
919                let out = resolve(parts)?;
920                let name = parts.last().map(|p| p.value.clone()).unwrap_or_default();
921                *item = if out == name {
922                    SelectItem::UnnamedExpr(Expr::Identifier(sqlparser::ast::Ident::new(out)))
923                } else {
924                    SelectItem::ExprWithAlias {
925                        expr: Expr::Identifier(sqlparser::ast::Ident::new(out)),
926                        alias: sqlparser::ast::Ident::new(name),
927                    }
928                };
929            }
930            SelectItem::QualifiedWildcard(kind, _) => {
931                let t = kind.to_string();
932                let t = t.trim_end_matches(".*");
933                return Err(format!(
934                    "`{t}.*` in a JOIN is not supported — list its columns (`{t}.col`, …), or use `*`"
935                ));
936            }
937            _ => {}
938        }
939    }
940    let mut rewrite = |e: &mut Expr| {
941        if let Expr::CompoundIdentifier(parts) = e {
942            match resolve(parts) {
943                Ok(out) => *e = Expr::Identifier(sqlparser::ast::Ident::new(out)),
944                Err(err) => {
945                    failed.get_or_insert(err);
946                    return ControlFlow::Break(());
947                }
948            }
949        }
950        ControlFlow::Continue(())
951    };
952    let _ = sqlparser::ast::visit_expressions_mut(&mut q.projection, &mut rewrite);
953    let _ = sqlparser::ast::visit_expressions_mut(&mut q.selection, &mut rewrite);
954    let _ = sqlparser::ast::visit_expressions_mut(&mut q.group_by, &mut rewrite);
955    let _ = sqlparser::ast::visit_expressions_mut(&mut q.having, &mut rewrite);
956    match failed {
957        Some(err) => Err(err),
958        None => Ok((q, renames)),
959    }
960}
961
962/// `FROM <left> [JOIN <right> ON …]` → a `Source` or a `Join`.
963fn parse_from(twj: &TableWithJoins) -> Result<Plan, String> {
964    let left = table_source(&twj.relation)?;
965    match twj.joins.len() {
966        0 => Ok(left),
967        1 => {
968            let j = &twj.joins[0];
969            let right = table_source(&j.relation)?;
970            let (join_key, kind) = parse_join_operator(&j.join_operator)?;
971            Ok(Plan::Join {
972                left: Box::new(left),
973                right: Box::new(right),
974                join_key,
975                kind,
976                right_rename: Default::default(),
977            })
978        }
979        _ => Err("only a single JOIN is supported".into()),
980    }
981}
982
983fn table_source(tf: &TableFactor) -> Result<Plan, String> {
984    match tf {
985        TableFactor::Table { name, .. } => Ok(Plan::Source(object_name(&name.to_string()))),
986        other => Err(format!("unsupported FROM item `{other}` — a plain dataset name")),
987    }
988}
989
990/// `JOIN … ON a.k = b.k [AND ts BETWEEN … ]` → (join key, kind). An interval predicate makes it a
991/// stream×stream interval join; without one, the right side is a bounded table (the 5b lookup join).
992fn parse_join_operator(op: &JoinOperator) -> Result<(String, JoinKind), String> {
993    let on = match op {
994        JoinOperator::Inner(JoinConstraint::On(e)) | JoinOperator::Join(JoinConstraint::On(e)) => e,
995        _ => return Err("only `INNER JOIN … ON <a.k = b.k> [AND <ts> BETWEEN …]` is supported".into()),
996    };
997    let mut join_key: Option<String> = None;
998    let mut interval: Option<(String, i64)> = None;
999    for c in split_and(on) {
1000        match c {
1001            Expr::BinaryOp {
1002                left,
1003                op: BinaryOperator::Eq,
1004                right,
1005            } => {
1006                join_key = Some(same_column(left, right)?);
1007            }
1008            Expr::Between {
1009                expr,
1010                negated: false,
1011                low,
1012                high,
1013            } => {
1014                interval = Some(between_interval(expr, low, high)?);
1015            }
1016            other => {
1017                return Err(format!(
1018                    "unsupported JOIN ON predicate `{other}` — an equi-key `a.k = b.k` and optionally `ts BETWEEN …`"
1019                ))
1020            }
1021        }
1022    }
1023    let join_key = join_key.ok_or("JOIN ON needs an equi-key (`a.k = b.k`, same column name on both sides)")?;
1024    let kind = match interval {
1025        Some((time_column, window_ms)) => JoinKind::Interval { time_column, window_ms },
1026        None => JoinKind::Lookup,
1027    };
1028    Ok((join_key, kind))
1029}
1030
1031/// Flatten an `AND` chain into its conjuncts.
1032fn split_and(e: &Expr) -> Vec<&Expr> {
1033    match e {
1034        Expr::BinaryOp {
1035            left,
1036            op: BinaryOperator::And,
1037            right,
1038        } => {
1039            let mut v = split_and(left);
1040            v.extend(split_and(right));
1041            v
1042        }
1043        Expr::Nested(inner) => split_and(inner),
1044        other => vec![other],
1045    }
1046}
1047
1048/// `a.k = b.k` → `k`. The two sides must name the SAME column (the join key present on both inputs).
1049fn same_column(l: &Expr, r: &Expr) -> Result<String, String> {
1050    let lc = column_name_of(l);
1051    let rc = column_name_of(r);
1052    match (lc, rc) {
1053        (Some(a), Some(b)) if a == b => Ok(a),
1054        (Some(a), Some(b)) => Err(format!(
1055            "the JOIN key columns must share a name (`{a}` vs `{b}`) — rename one upstream"
1056        )),
1057        _ => Err("JOIN key must be `a.col = b.col` (plain columns)".into()),
1058    }
1059}
1060
1061/// `ts BETWEEN b.ts - INTERVAL … AND b.ts + INTERVAL …` → (event-time column, window ms).
1062fn between_interval(expr: &Expr, low: &Expr, high: &Expr) -> Result<(String, i64), String> {
1063    let time_column = column_name_of(expr)
1064        .ok_or("the interval join's BETWEEN must be over an event-time column (`a.ts BETWEEN …`)")?;
1065    // the window is the magnitude of the interval on either bound (symmetric ±window).
1066    let ms = interval_in(high).or_else(|_| interval_in(low))?;
1067    Ok((time_column, ms))
1068}
1069
1070/// Find an `INTERVAL` inside a bound like `b.ts + INTERVAL '5' SECOND` and return it in ms.
1071fn interval_in(e: &Expr) -> Result<i64, String> {
1072    match e {
1073        Expr::Interval(_) => interval_ms(e),
1074        Expr::BinaryOp { left, right, .. } => interval_in(right).or_else(|_| interval_in(left)),
1075        Expr::Nested(inner) => interval_in(inner),
1076        _ => Err("the interval join's BETWEEN needs an `INTERVAL` bound (`b.ts + INTERVAL '5' SECOND`)".into()),
1077    }
1078}
1079
1080/// The output name a bare projected expression takes (a column keeps its own name; a compound
1081/// `a.b` takes `b`). `None` for a computed expression that needs an explicit alias.
1082fn column_name_of(e: &Expr) -> Option<String> {
1083    match e {
1084        Expr::Identifier(id) => Some(id.value.clone()),
1085        Expr::CompoundIdentifier(parts) => parts.last().map(|p| p.value.clone()),
1086        _ => None,
1087    }
1088}
1089
1090/// An object name (`schema.name` → `name`) reduced to the last part — datasets are referenced by a
1091/// single name in the pipeline config.
1092fn object_name(raw: &str) -> String {
1093    raw.rsplit('.').next().unwrap_or(raw).trim_matches('"').to_string()
1094}
1095
1096fn statement_kind(s: &Statement) -> &'static str {
1097    match s {
1098        Statement::Insert(_) => "INSERT",
1099        Statement::Query(_) => "SELECT",
1100        Statement::CreateTable(_) => "CREATE TABLE",
1101        _ => "statement",
1102    }
1103}