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,
7    FunctionArguments, GroupByExpr, JoinConstraint, JoinOperator, Query, Select, SelectItem, SetExpr,
8    Statement, TableFactor, 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}
32
33/// The windowing a `GROUP BY <window-fn>(…)` selects. An optional trailing `INTERVAL` on any window
34/// function is the **allowed lateness** — how long a window stays open for late events after its end
35/// (the engine's `allowedLatenessMs`); `None` leaves it at the engine default. q4/q5 need a large
36/// lateness so no out-of-order bid is dropped from a closed window.
37#[derive(Debug, Clone, PartialEq)]
38pub enum Win {
39    /// `TUMBLE(ts, INTERVAL <size> [, INTERVAL <lateness>])` — fixed windows.
40    Tumble { size_ms: i64, lateness_ms: Option<i64> },
41    /// `HOP(ts, INTERVAL <slide>, INTERVAL <size> [, INTERVAL <lateness>])` — sliding windows.
42    Hop { slide_ms: i64, size_ms: i64, lateness_ms: Option<i64> },
43    /// `SESSION(ts, INTERVAL <gap> [, INTERVAL <lateness>])` — dynamic session windows.
44    Session { gap_ms: i64, lateness_ms: Option<i64> },
45}
46
47/// The early-firing trigger a trailing `EMIT CHANGES EVERY …` clause selects — the 5a updating
48/// aggregate. sqlparser does not know `EMIT`, so a small lexical pre-pass ([`split_emit`]) strips
49/// and parses this clause before the rest of the statement reaches sqlparser (design §10 / A).
50#[derive(Debug, Clone, Copy, PartialEq)]
51pub enum Trigger {
52    EveryRows(u64),
53    EveryMs(i64),
54}
55
56/// How a two-input `JOIN` maps to an operator. An interval predicate (`ts BETWEEN …`) in the `ON`
57/// makes it a stream×stream interval join; a plain equi-join makes the right side a **bounded
58/// table** — the 5b lookup join.
59#[derive(Debug, Clone, PartialEq)]
60pub enum JoinKind {
61    /// `JOIN … ON k=k AND ts BETWEEN … ` → `streamJoin`.
62    Interval { time_column: String, window_ms: i64 },
63    /// `JOIN <bounded> ON k=k` → the 5b `lookupJoin` (right = the bounded table).
64    Lookup,
65}
66
67/// The typed logical plan. 5c-i covers the stateless shape (source → filter → project → sink);
68/// 5c-ii adds the windowed aggregate, the updating aggregate, and the joins.
69#[derive(Debug, Clone, PartialEq)]
70pub enum Plan {
71    /// `FROM <dataset>` — a source dataset by name.
72    Source(String),
73    /// `FROM <left> JOIN <right> ON <key> [interval]` — a two-input join.
74    Join {
75        left: Box<Plan>,
76        right: Box<Plan>,
77        join_key: String,
78        kind: JoinKind,
79    },
80    /// `WHERE <predicate>` — the predicate as a value-dialect string.
81    Filter { input: Box<Plan>, predicate: String },
82    /// `SELECT …`. `star` = `SELECT *` (pass-through, no step); else the projected columns.
83    Project {
84        input: Box<Plan>,
85        star: bool,
86        items: Vec<Proj>,
87    },
88    /// `SELECT … FROM … GROUP BY <window-fn>(ts,…), <keys>` — a windowed keyed aggregate. A
89    /// `Some(trigger)` makes it the **5a updating aggregate** (early firing).
90    Aggregate {
91        input: Box<Plan>,
92        time_column: String,
93        window: Win,
94        group_by: Vec<String>,
95        /// Pre-aggregate computed columns (an aggregate's expression argument, e.g. a `CASE` fed to
96        /// `count(DISTINCT …)`) — lowered to `applyExpression` steps BEFORE the aggregate.
97        pre: Vec<Proj>,
98        aggs: Vec<AggSpec>,
99        /// Post-aggregate computed columns — a `SELECT` expression over the window/group/aggregates
100        /// (e.g. `day = substring(epochToIso(windowStart), 0, 10)`), lowered to `applyExpression`
101        /// steps AFTER the aggregate (the q15/q16 day/minute columns).
102        post: Vec<Proj>,
103        trigger: Option<Trigger>,
104    },
105    /// `SELECT … FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC]) AS rn FROM s)
106    /// WHERE rn <= n` — the streaming top-N idiom → the `topN` operator.
107    TopN {
108        input: Box<Plan>,
109        group_by: Vec<String>,
110        order_by: String,
111        /// A second `ORDER BY` column → the tie-break (`tieBy`, ascending).
112        tie_by: Option<String>,
113        descending: bool,
114        n: usize,
115    },
116    /// A CTE (`WITH <name> AS (<subquery>)`): the sub-plan `input` materialises as its own stage
117    /// under `name`, and downstream references to `name` read that stage. The multi-stage boundary —
118    /// the SQL equivalent of a named intermediate dataset in the TOML `[[transforms]]` DAG.
119    Named { name: String, input: Box<Plan> },
120    /// `INSERT INTO <dataset>` — the terminal sink dataset by name.
121    Sink { input: Box<Plan>, name: String },
122}
123
124/// Parse one streaming SQL statement into a [`Plan`]. Fails LOUDLY, streaming-aware, on anything
125/// outside the supported subset.
126pub fn parse(sql: &str) -> Result<Plan, String> {
127    // Strip a trailing `EMIT CHANGES EVERY …` (sqlparser does not know it) before parsing the rest.
128    let (sql, trigger) = split_emit(sql)?;
129    let plan = parse_statement(&sql)?;
130    match trigger {
131        Some(t) => attach_trigger(plan, t),
132        None => Ok(plan),
133    }
134}
135
136fn parse_statement(sql: &str) -> Result<Plan, String> {
137    let mut stmts = Parser::parse_sql(&GenericDialect {}, sql).map_err(|e| format!("SQL parse error: {e}"))?;
138    if stmts.len() != 1 {
139        return Err(format!("expected exactly one statement, got {}", stmts.len()));
140    }
141    match stmts.pop().expect("one") {
142        Statement::Insert(insert) => {
143            let sink = object_name(&insert.table.to_string());
144            let query = insert
145                .source
146                .ok_or("INSERT INTO <sink> needs a SELECT (`INSERT INTO out SELECT …`)")?;
147            let input = plan_query(&query)?;
148            Ok(Plan::Sink {
149                input: Box::new(input),
150                name: sink,
151            })
152        }
153        Statement::Query(_) => Err(
154            "a streaming statement must write to a sink: `INSERT INTO <sink> SELECT …`".into(),
155        ),
156        other => Err(format!(
157            "unsupported statement `{}` — the supported shape is `INSERT INTO <sink> SELECT … FROM … [WHERE …]`",
158            statement_kind(&other)
159        )),
160    }
161}
162
163fn plan_query(query: &Query) -> Result<Plan, String> {
164    // `WITH name AS (subquery), …` — each CTE becomes a named intermediate stream (the multi-stage
165    // boundary). SQL scoping: a CTE sees only the CTEs declared before it, so bodies are bound in
166    // order against the ones already built; the outer body binds against all of them.
167    let ctes = parse_ctes(query.with.as_ref())?;
168    if query.order_by.is_some() {
169        return Err("ORDER BY over an unbounded stream has no streaming meaning — use `ROW_NUMBER() OVER (PARTITION BY …) <= n` for a top-N".into());
170    }
171    if query.limit_clause.is_some() {
172        return Err("LIMIT over an unbounded stream is not supported — use a top-N (`ROW_NUMBER() … <= n`)".into());
173    }
174    let select = match query.body.as_ref() {
175        SetExpr::Select(s) => s.as_ref(),
176        SetExpr::SetOperation { .. } => {
177            return Err("UNION / INTERSECT / EXCEPT are not supported — express as separate pipelines".into())
178        }
179        _ => return Err("only a SELECT body is supported".into()),
180    };
181    Ok(bind_ctes(plan_select(select)?, &ctes))
182}
183
184/// Parse a `WITH` clause into `(name, plan)` in declaration order. Each CTE body is planned and then
185/// bound against the CTEs declared before it (SQL scoping), so later stages can read earlier ones.
186/// Recursive CTEs (`WITH RECURSIVE`) are not supported — there is no streaming fixpoint.
187fn parse_ctes(with: Option<&sqlparser::ast::With>) -> Result<Vec<(String, Plan)>, String> {
188    let Some(with) = with else { return Ok(Vec::new()) };
189    if with.recursive {
190        return Err("WITH RECURSIVE is not supported — a streaming CTE has no fixpoint".into());
191    }
192    let mut bound: Vec<(String, Plan)> = Vec::new();
193    for cte in &with.cte_tables {
194        let name = object_name(&cte.alias.name.value);
195        let plan = bind_ctes(plan_query(&cte.query)?, &bound);
196        bound.push((name, plan));
197    }
198    Ok(bound)
199}
200
201/// Replace every `Source(name)` that names a CTE with a `Named { name, <cte plan> }` boundary, so
202/// lowering materialises it as its own stage. Recurses through the whole plan; a non-CTE `Source`
203/// (a base dataset) is left untouched.
204fn bind_ctes(plan: Plan, ctes: &[(String, Plan)]) -> Plan {
205    if ctes.is_empty() {
206        return plan;
207    }
208    let boxed = |p: Box<Plan>| Box::new(bind_ctes(*p, ctes));
209    match plan {
210        Plan::Source(name) => match ctes.iter().find(|(n, _)| *n == name) {
211            Some((_, cte)) => Plan::Named { name, input: Box::new(cte.clone()) },
212            None => Plan::Source(name),
213        },
214        Plan::Named { name, input } => Plan::Named { name, input: boxed(input) },
215        Plan::Join { left, right, join_key, kind } => Plan::Join {
216            left: boxed(left),
217            right: boxed(right),
218            join_key,
219            kind,
220        },
221        Plan::Filter { input, predicate } => Plan::Filter { input: boxed(input), predicate },
222        Plan::Project { input, star, items } => Plan::Project { input: boxed(input), star, items },
223        Plan::Aggregate { input, time_column, window, group_by, pre, aggs, post, trigger } => Plan::Aggregate {
224            input: boxed(input),
225            time_column,
226            window,
227            group_by,
228            pre,
229            aggs,
230            post,
231            trigger,
232        },
233        Plan::TopN { input, group_by, order_by, tie_by, descending, n } => Plan::TopN {
234            input: boxed(input),
235            group_by,
236            order_by,
237            tie_by,
238            descending,
239            n,
240        },
241        Plan::Sink { input, name } => Plan::Sink { input: boxed(input), name },
242    }
243}
244
245fn plan_select(select: &Select) -> Result<Plan, String> {
246    // The ROW_NUMBER top-N idiom (a subquery + `WHERE rn <= n`) → the topN operator, projected by
247    // the outer SELECT (the rank filter is consumed by the operator).
248    if let Some(topn) = detect_topn(select)? {
249        return parse_projection(topn, &select.projection);
250    }
251
252    // FROM: one table, optionally with one JOIN.
253    if select.from.len() != 1 {
254        return Err(format!(
255            "expected one FROM item (comma joins are not supported — use `JOIN … ON …`), got {}",
256            select.from.len()
257        ));
258    }
259    let source = parse_from(&select.from[0])?;
260    if select.having.is_some() {
261        return Err("HAVING is not supported yet".into());
262    }
263
264    // WHERE → filter (reads the input columns, so it sits below the aggregate/projection).
265    let filtered = match &select.selection {
266        Some(pred) => Plan::Filter {
267            input: Box::new(source),
268            predicate: expr::to_dialect(pred)?,
269        },
270        None => source,
271    };
272
273    // GROUP BY <window-fn>(…), <keys> → a windowed keyed aggregate.
274    let group_exprs = match &select.group_by {
275        GroupByExpr::Expressions(exprs, _) if !exprs.is_empty() => Some(exprs.as_slice()),
276        GroupByExpr::All(_) => return Err("GROUP BY ALL is not supported".into()),
277        _ => None,
278    };
279    if let Some(gexprs) = group_exprs {
280        return plan_aggregate(filtered, gexprs, &select.projection);
281    }
282
283    parse_projection(filtered, &select.projection)
284}
285
286/// The `SELECT` projection over an already-built input → a `Project` node.
287fn parse_projection(input: Plan, projection: &[SelectItem]) -> Result<Plan, String> {
288    let mut star = false;
289    let mut items = Vec::new();
290    for item in projection {
291        match item {
292            SelectItem::Wildcard(_) => star = true,
293            SelectItem::UnnamedExpr(e) => {
294                let out = column_name_of(e)
295                    .ok_or("a projected expression needs an alias (`<expr> AS name`)")?;
296                match e {
297                    Expr::Identifier(_) | Expr::CompoundIdentifier(_) => {
298                        items.push(Proj { out, expr: None })
299                    }
300                    _ => items.push(Proj { out, expr: Some(expr::to_dialect(e)?) }),
301                }
302            }
303            SelectItem::ExprWithAlias { expr: e, alias } => {
304                items.push(Proj { out: alias.value.clone(), expr: Some(expr::to_dialect(e)?) });
305            }
306            _ => return Err("`table.*` / multi-alias projections are not supported — list the columns".into()),
307        }
308    }
309    if star && items.iter().any(|p| p.expr.is_none()) {
310        return Err("`SELECT *, <bare column>` is redundant — `*` already keeps every column; with `*` add only computed columns (`*, <expr> AS name`)".into());
311    }
312    Ok(Plan::Project { input: Box::new(input), star, items })
313}
314
315/// Recognise `FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC]) AS rn FROM s)
316/// WHERE rn <= n` → a `TopN` over the inner source. `Ok(None)` when it is not that shape.
317fn detect_topn(select: &Select) -> Result<Option<Plan>, String> {
318    if select.from.len() != 1 || !select.from[0].joins.is_empty() {
319        return Ok(None);
320    }
321    let TableFactor::Derived { subquery, .. } = &select.from[0].relation else {
322        return Ok(None);
323    };
324    let SetExpr::Select(inner) = subquery.body.as_ref() else {
325        return Ok(None);
326    };
327    // find ROW_NUMBER() OVER (…) AS <rn>
328    let mut rn_fn = None;
329    for item in &inner.projection {
330        if let SelectItem::ExprWithAlias { expr: Expr::Function(f), alias } = item {
331            if f.name.to_string().eq_ignore_ascii_case("row_number") {
332                rn_fn = Some((f, alias.value.clone()));
333            }
334        }
335    }
336    let Some((f, rn)) = rn_fn else { return Ok(None) }; // no ROW_NUMBER → not a top-N
337    let Some(sqlparser::ast::WindowType::WindowSpec(ws)) = &f.over else {
338        return Err("ROW_NUMBER() needs `OVER (PARTITION BY … ORDER BY …)`".into());
339    };
340    let group_by: Vec<String> = ws.partition_by.iter().filter_map(column_name_of).collect();
341    let ob = ws.order_by.first().ok_or("ROW_NUMBER() OVER needs an ORDER BY column")?;
342    let order_by = column_name_of(&ob.expr).ok_or("ROW_NUMBER() ORDER BY must be a column")?;
343    let descending = ob.options.asc == Some(false); // DESC
344    // a second ORDER BY column is the tie-break (ascending, like the operator's `tieBy`).
345    let tie_by = match ws.order_by.get(1) {
346        Some(t) => Some(column_name_of(&t.expr).ok_or("ROW_NUMBER() tie-break must be a column")?),
347        None => None,
348    };
349    if inner.from.len() != 1 || !inner.from[0].joins.is_empty() {
350        return Err("the ROW_NUMBER subquery must read exactly one source".into());
351    }
352    let source = table_source(&inner.from[0].relation)?;
353    // the subquery's WHERE (e.g. `type = 'bid'`) filters before the rank.
354    let input = match &inner.selection {
355        Some(pred) => Plan::Filter { input: Box::new(source), predicate: expr::to_dialect(pred)? },
356        None => source,
357    };
358    let n = rank_limit(select.selection.as_ref(), &rn)?;
359    Ok(Some(Plan::TopN {
360        input: Box::new(input),
361        group_by,
362        order_by,
363        tie_by,
364        descending,
365        n,
366    }))
367}
368
369/// The `n` from an outer `WHERE <rn> <= n` (or `<rn> = 1`) top-N filter.
370fn rank_limit(where_: Option<&Expr>, rn: &str) -> Result<usize, String> {
371    let e = where_.ok_or_else(|| format!("a ROW_NUMBER top-N needs an outer `WHERE {rn} <= n`"))?;
372    let is_rn = |x: &Expr| column_name_of(x).as_deref() == Some(rn);
373    match e {
374        Expr::BinaryOp { left, op: BinaryOperator::LtEq, right } if is_rn(left) => literal_usize(right),
375        Expr::BinaryOp { left, op: BinaryOperator::Eq, right } if is_rn(left) => match literal_usize(right)? {
376            1 => Ok(1),
377            _ => Err("a `= n` top-N filter only supports n = 1 — use `<= n`".into()),
378        },
379        _ => Err(format!("the top-N filter must be `{rn} <= n`")),
380    }
381}
382
383fn literal_usize(e: &Expr) -> Result<usize, String> {
384    match e {
385        Expr::Value(v) => match &v.value {
386            Value::Number(s, _) => s.trim().parse().map_err(|_| format!("`{s}` is not a whole number")),
387            other => Err(format!("expected a number, got `{other}`")),
388        },
389        other => Err(format!("expected a number, got `{other}`")),
390    }
391}
392
393/// `GROUP BY <window-fn>(ts,…), <keys>` + aggregate projections → a windowed keyed aggregate.
394fn plan_aggregate(input: Plan, group_exprs: &[Expr], projection: &[SelectItem]) -> Result<Plan, String> {
395    let mut window: Option<(String, Win)> = None;
396    let mut group_by = Vec::new();
397    for g in group_exprs {
398        match g {
399            Expr::Function(f) if is_window_fn(f) => {
400                if window.is_some() {
401                    return Err("only one window function may appear in GROUP BY".into());
402                }
403                window = Some(parse_window_fn(f)?);
404            }
405            Expr::Identifier(id) => group_by.push(id.value.clone()),
406            Expr::CompoundIdentifier(p) => group_by.push(p.last().expect("non-empty").value.clone()),
407            other => {
408                return Err(format!(
409                    "unsupported GROUP BY item `{other}` — a window function (`TUMBLE/HOP/SESSION`) or a plain column"
410                ))
411            }
412        }
413    }
414    let (time_column, window) = window.ok_or(
415        "a streaming GROUP BY needs a window: `GROUP BY TUMBLE/HOP/SESSION(ts, INTERVAL …)` (an unbounded aggregate needs `… EMIT CHANGES` — the 5a updating aggregate)",
416    )?;
417    let (pre, aggs, post) = parse_agg_select(projection)?;
418    if aggs.is_empty() {
419        return Err("a windowed GROUP BY needs at least one aggregate in SELECT (count/sum/avg/min/max)".into());
420    }
421    Ok(Plan::Aggregate {
422        input: Box::new(input),
423        time_column,
424        window,
425        group_by,
426        pre,
427        aggs,
428        post,
429        trigger: None,
430    })
431}
432
433/// Strip a trailing `EMIT CHANGES EVERY <n> ROWS|MS` clause (case-insensitive, outside string
434/// literals). Returns the SQL without it and the parsed trigger, if any.
435fn split_emit(sql: &str) -> Result<(String, Option<Trigger>), String> {
436    let lower = sql.to_ascii_lowercase();
437    let bytes = lower.as_bytes();
438    let is_word = |b: u8| b.is_ascii_alphanumeric() || b == b'_';
439    let mut in_str = false;
440    let mut at = None;
441    let mut i = 0usize;
442    while i < bytes.len() {
443        if bytes[i] == b'\'' {
444            in_str = !in_str;
445            i += 1;
446            continue;
447        }
448        if !in_str
449            && lower[i..].starts_with("emit")
450            && (i == 0 || !is_word(bytes[i - 1]))
451            && (i + 4 >= bytes.len() || !is_word(bytes[i + 4]))
452        {
453            at = Some(i); // the last unquoted `emit` wins (it is a trailing clause)
454        }
455        i += 1;
456    }
457    let Some(at) = at else {
458        return Ok((sql.to_string(), None));
459    };
460    let head = sql[..at].trim_end().to_string();
461    let trigger = parse_emit(&sql[at..])?;
462    Ok((head, Some(trigger)))
463}
464
465fn parse_emit(clause: &str) -> Result<Trigger, String> {
466    let toks: Vec<String> = clause.split_whitespace().map(|s| s.to_ascii_lowercase()).collect();
467    let shape = "`EMIT CHANGES EVERY <n> ROWS` or `EMIT CHANGES EVERY <n> MS`";
468    if toks.len() < 2 || toks[0] != "emit" || toks[1] != "changes" {
469        return Err(format!("unrecognised EMIT clause — use {shape}"));
470    }
471    if toks.len() == 2 {
472        return Err(format!("`EMIT CHANGES` needs a cadence: {shape}"));
473    }
474    if toks.len() != 5 || toks[2] != "every" {
475        return Err(format!("EMIT clause: expected {shape}"));
476    }
477    let n: u64 = toks[3]
478        .parse()
479        .map_err(|_| format!("EMIT cadence `{}` is not a whole number", toks[3]))?;
480    match toks[4].as_str() {
481        "rows" | "row" => Ok(Trigger::EveryRows(n)),
482        "ms" | "milliseconds" | "millisecond" => Ok(Trigger::EveryMs(n as i64)),
483        other => Err(format!("EMIT cadence unit `{other}` — use ROWS or MS")),
484    }
485}
486
487/// Attach a trigger to the plan's windowed aggregate (the 5a updating aggregate). The trigger is
488/// valid only on a windowed (`TUMBLE`/`HOP`) aggregate — the operator that early-fires.
489fn attach_trigger(plan: Plan, trigger: Trigger) -> Result<Plan, String> {
490    match plan {
491        Plan::Sink { input, name } => Ok(Plan::Sink {
492            input: Box::new(attach_trigger(*input, trigger)?),
493            name,
494        }),
495        Plan::Aggregate {
496            input,
497            time_column,
498            window,
499            group_by,
500            pre,
501            aggs,
502            post,
503            trigger: _,
504        } => {
505            if matches!(window, Win::Session { .. }) {
506                return Err("EMIT CHANGES is supported on windowed aggregates (TUMBLE / HOP), not sessions".into());
507            }
508            Ok(Plan::Aggregate {
509                input,
510                time_column,
511                window,
512                group_by,
513                pre,
514                aggs,
515                post,
516                trigger: Some(trigger),
517            })
518        }
519        _ => Err("EMIT CHANGES needs a windowed GROUP BY aggregate (`GROUP BY <keys>, TUMBLE(ts, INTERVAL …)`)".into()),
520    }
521}
522
523fn is_window_fn(f: &Function) -> bool {
524    matches!(f.name.to_string().to_uppercase().as_str(), "TUMBLE" | "HOP" | "SESSION")
525}
526
527/// `TUMBLE(ts, INTERVAL <size>)` / `HOP(ts, INTERVAL <slide>, INTERVAL <size>)` /
528/// `SESSION(ts, INTERVAL <gap>)` → (event-time column, window).
529fn parse_window_fn(f: &Function) -> Result<(String, Win), String> {
530    let name = f.name.to_string().to_uppercase();
531    let args = fn_args(f)?;
532    let time_col = |e: &Expr| -> Result<String, String> {
533        match e {
534            Expr::Identifier(id) => Ok(id.value.clone()),
535            Expr::CompoundIdentifier(p) => Ok(p.last().expect("non-empty").value.clone()),
536            _ => Err(format!("{name}(…)'s first argument must be the event-time column")),
537        }
538    };
539    // an optional trailing INTERVAL beyond the base arity is the allowed lateness.
540    let lateness = |base: usize| -> Result<Option<i64>, String> {
541        match args.len() {
542            n if n == base => Ok(None),
543            n if n == base + 1 => Ok(Some(interval_ms(args[base])?)),
544            _ => Err(String::new()), // arity handled by the arm guards below
545        }
546    };
547    match name.as_str() {
548        "TUMBLE" if args.len() == 2 || args.len() == 3 => Ok((
549            time_col(args[0])?,
550            Win::Tumble { size_ms: interval_ms(args[1])?, lateness_ms: lateness(2)? },
551        )),
552        "HOP" if args.len() == 3 || args.len() == 4 => Ok((
553            time_col(args[0])?,
554            Win::Hop { slide_ms: interval_ms(args[1])?, size_ms: interval_ms(args[2])?, lateness_ms: lateness(3)? },
555        )),
556        "SESSION" if args.len() == 2 || args.len() == 3 => Ok((
557            time_col(args[0])?,
558            Win::Session { gap_ms: interval_ms(args[1])?, lateness_ms: lateness(2)? },
559        )),
560        "TUMBLE" | "SESSION" => Err(format!("{name}(ts, INTERVAL <size> [, INTERVAL <lateness>]) takes 2 or 3 arguments")),
561        "HOP" => Err("HOP(ts, INTERVAL <slide>, INTERVAL <size> [, INTERVAL <lateness>]) takes 3 or 4 arguments".into()),
562        other => Err(format!("unsupported window function `{other}`")),
563    }
564}
565
566fn is_agg_name(name: &str) -> bool {
567    matches!(name, "count" | "sum" | "avg" | "min" | "max")
568}
569
570/// Classify a windowed `SELECT`'s projections: aggregate calls (→ `aggs`, with any expression
571/// arguments hoisted to `pre` computed columns), bare group columns (skipped — GROUP BY captured
572/// them), and other computed columns over the window/group/aggregates (→ `post` applyExpression
573/// steps after the aggregate, e.g. `day = substring(epochToIso(windowStart), 0, 10)`).
574#[allow(clippy::type_complexity)]
575fn parse_agg_select(projection: &[SelectItem]) -> Result<(Vec<Proj>, Vec<AggSpec>, Vec<Proj>), String> {
576    let mut pre: Vec<Proj> = Vec::new();
577    let mut aggs = Vec::new();
578    let mut post: Vec<Proj> = Vec::new();
579    for item in projection {
580        let (e, alias) = match item {
581            SelectItem::ExprWithAlias { expr, alias } => (expr, Some(alias.value.clone())),
582            SelectItem::UnnamedExpr(e) => (e, None),
583            SelectItem::Wildcard(_) => return Err("`SELECT *` is not allowed with GROUP BY".into()),
584            _ => return Err("unsupported projection in a GROUP BY query".into()),
585        };
586        match e {
587            Expr::Function(f) if is_agg_name(&f.name.to_string().to_lowercase()) => {
588                aggs.push(agg_of(f, alias, &mut pre)?);
589            }
590            // a bare group column — already in the aggregate's output.
591            Expr::Identifier(_) | Expr::CompoundIdentifier(_) => {}
592            // a computed column over the window/group/aggregates → a post-aggregate applyExpression.
593            _ => {
594                let out = alias
595                    .ok_or("a computed SELECT column in a GROUP BY query needs an alias (`<expr> AS name`)")?;
596                post.push(Proj { out, expr: Some(expr::to_dialect(e)?) });
597            }
598        }
599    }
600    Ok((pre, aggs, post))
601}
602
603fn agg_of(f: &Function, alias: Option<String>, pre: &mut Vec<Proj>) -> Result<AggSpec, String> {
604    let name = f.name.to_string().to_lowercase();
605    let alias = alias.ok_or_else(|| format!("aggregate `{name}(…)` needs an alias (`{name}(…) AS name`)"))?;
606    let distinct = matches!(
607        &f.args,
608        FunctionArguments::List(l) if l.duplicate_treatment == Some(DuplicateTreatment::Distinct)
609    );
610    let op = match (name.as_str(), distinct) {
611        ("count", false) => "count",
612        ("count", true) => "countDistinct",
613        ("sum", false) => "sum",
614        ("avg", false) => "avg",
615        ("min", false) => "min",
616        ("max", false) => "max",
617        (other, true) => return Err(format!("DISTINCT is only supported on `count()`, not `{other}()`")),
618        (other, false) => return Err(format!("unsupported aggregate `{other}` — count/sum/avg/min/max/count(DISTINCT …)")),
619    };
620    let args = fn_args(f)?;
621    // count(*) has no column; a bare column feeds the aggregate directly; an EXPRESSION argument
622    // (CASE, arithmetic) becomes a synthetic pre-aggregate column (the q15/q16 null-mask shape).
623    let column = match args.first() {
624        None => String::new(),
625        Some(Expr::Identifier(id)) => id.value.clone(),
626        Some(Expr::CompoundIdentifier(p)) => p.last().expect("non-empty").value.clone(),
627        Some(other) => {
628            let synth = format!("_fv_agg{}", pre.len());
629            pre.push(Proj { out: synth.clone(), expr: Some(expr::to_dialect(other)?) });
630            synth
631        }
632    };
633    Ok(AggSpec { op: op.to_string(), column, alias })
634}
635
636/// The plain `Expr` arguments of a function call (a `*` wildcard contributes none — `count(*)`).
637fn fn_args(f: &Function) -> Result<Vec<&Expr>, String> {
638    let list = match &f.args {
639        FunctionArguments::List(l) => &l.args,
640        FunctionArguments::None => return Ok(Vec::new()),
641        FunctionArguments::Subquery(_) => return Err("subquery arguments are not supported".into()),
642    };
643    let mut out = Vec::new();
644    for a in list {
645        match a {
646            FunctionArg::Unnamed(FunctionArgExpr::Expr(e)) => out.push(e),
647            FunctionArg::Unnamed(FunctionArgExpr::Wildcard) => {}
648            _ => return Err("unsupported function argument (named / qualified wildcard)".into()),
649        }
650    }
651    Ok(out)
652}
653
654/// An `INTERVAL '<n>' <unit>` as milliseconds.
655fn interval_ms(e: &Expr) -> Result<i64, String> {
656    let Expr::Interval(iv) = e else {
657        return Err(format!("expected an INTERVAL (e.g. `INTERVAL '10' SECOND`), got `{e}`"));
658    };
659    let n: i64 = match iv.value.as_ref() {
660        Expr::Value(v) => match &v.value {
661            Value::SingleQuotedString(s) | Value::Number(s, _) => {
662                s.trim().parse().map_err(|_| format!("interval value `{s}` is not an integer"))?
663            }
664            other => return Err(format!("unsupported interval value `{other}`")),
665        },
666        other => return Err(format!("unsupported interval value `{other}`")),
667    };
668    let unit = match &iv.leading_field {
669        Some(DateTimeField::Millisecond) => 1,
670        Some(DateTimeField::Second) => 1_000,
671        Some(DateTimeField::Minute) => 60_000,
672        Some(DateTimeField::Hour) => 3_600_000,
673        Some(DateTimeField::Day) => 86_400_000,
674        other => {
675            return Err(format!(
676                "unsupported interval unit `{other:?}` — use MILLISECOND / SECOND / MINUTE / HOUR / DAY"
677            ))
678        }
679    };
680    Ok(n * unit)
681}
682
683/// `FROM <left> [JOIN <right> ON …]` → a `Source` or a `Join`.
684fn parse_from(twj: &TableWithJoins) -> Result<Plan, String> {
685    let left = table_source(&twj.relation)?;
686    match twj.joins.len() {
687        0 => Ok(left),
688        1 => {
689            let j = &twj.joins[0];
690            let right = table_source(&j.relation)?;
691            let (join_key, kind) = parse_join_operator(&j.join_operator)?;
692            Ok(Plan::Join { left: Box::new(left), right: Box::new(right), join_key, kind })
693        }
694        _ => Err("only a single JOIN is supported".into()),
695    }
696}
697
698fn table_source(tf: &TableFactor) -> Result<Plan, String> {
699    match tf {
700        TableFactor::Table { name, .. } => Ok(Plan::Source(object_name(&name.to_string()))),
701        other => Err(format!("unsupported FROM item `{other}` — a plain dataset name")),
702    }
703}
704
705/// `JOIN … ON a.k = b.k [AND ts BETWEEN … ]` → (join key, kind). An interval predicate makes it a
706/// stream×stream interval join; without one, the right side is a bounded table (the 5b lookup join).
707fn parse_join_operator(op: &JoinOperator) -> Result<(String, JoinKind), String> {
708    let on = match op {
709        JoinOperator::Inner(JoinConstraint::On(e)) | JoinOperator::Join(JoinConstraint::On(e)) => e,
710        _ => {
711            return Err(
712                "only `INNER JOIN … ON <a.k = b.k> [AND <ts> BETWEEN …]` is supported".into(),
713            )
714        }
715    };
716    let mut join_key: Option<String> = None;
717    let mut interval: Option<(String, i64)> = None;
718    for c in split_and(on) {
719        match c {
720            Expr::BinaryOp { left, op: BinaryOperator::Eq, right } => {
721                join_key = Some(same_column(left, right)?);
722            }
723            Expr::Between { expr, negated: false, low, high } => {
724                interval = Some(between_interval(expr, low, high)?);
725            }
726            other => {
727                return Err(format!(
728                    "unsupported JOIN ON predicate `{other}` — an equi-key `a.k = b.k` and optionally `ts BETWEEN …`"
729                ))
730            }
731        }
732    }
733    let join_key = join_key.ok_or("JOIN ON needs an equi-key (`a.k = b.k`, same column name on both sides)")?;
734    let kind = match interval {
735        Some((time_column, window_ms)) => JoinKind::Interval { time_column, window_ms },
736        None => JoinKind::Lookup,
737    };
738    Ok((join_key, kind))
739}
740
741/// Flatten an `AND` chain into its conjuncts.
742fn split_and(e: &Expr) -> Vec<&Expr> {
743    match e {
744        Expr::BinaryOp { left, op: BinaryOperator::And, right } => {
745            let mut v = split_and(left);
746            v.extend(split_and(right));
747            v
748        }
749        Expr::Nested(inner) => split_and(inner),
750        other => vec![other],
751    }
752}
753
754/// `a.k = b.k` → `k`. The two sides must name the SAME column (the join key present on both inputs).
755fn same_column(l: &Expr, r: &Expr) -> Result<String, String> {
756    let lc = column_name_of(l);
757    let rc = column_name_of(r);
758    match (lc, rc) {
759        (Some(a), Some(b)) if a == b => Ok(a),
760        (Some(a), Some(b)) => Err(format!(
761            "the JOIN key columns must share a name (`{a}` vs `{b}`) — rename one upstream"
762        )),
763        _ => Err("JOIN key must be `a.col = b.col` (plain columns)".into()),
764    }
765}
766
767/// `ts BETWEEN b.ts - INTERVAL … AND b.ts + INTERVAL …` → (event-time column, window ms).
768fn between_interval(expr: &Expr, low: &Expr, high: &Expr) -> Result<(String, i64), String> {
769    let time_column = column_name_of(expr)
770        .ok_or("the interval join's BETWEEN must be over an event-time column (`a.ts BETWEEN …`)")?;
771    // the window is the magnitude of the interval on either bound (symmetric ±window).
772    let ms = interval_in(high).or_else(|_| interval_in(low))?;
773    Ok((time_column, ms))
774}
775
776/// Find an `INTERVAL` inside a bound like `b.ts + INTERVAL '5' SECOND` and return it in ms.
777fn interval_in(e: &Expr) -> Result<i64, String> {
778    match e {
779        Expr::Interval(_) => interval_ms(e),
780        Expr::BinaryOp { left, right, .. } => interval_in(right).or_else(|_| interval_in(left)),
781        Expr::Nested(inner) => interval_in(inner),
782        _ => Err("the interval join's BETWEEN needs an `INTERVAL` bound (`b.ts + INTERVAL '5' SECOND`)".into()),
783    }
784}
785
786/// The output name a bare projected expression takes (a column keeps its own name; a compound
787/// `a.b` takes `b`). `None` for a computed expression that needs an explicit alias.
788fn column_name_of(e: &Expr) -> Option<String> {
789    match e {
790        Expr::Identifier(id) => Some(id.value.clone()),
791        Expr::CompoundIdentifier(parts) => parts.last().map(|p| p.value.clone()),
792        _ => None,
793    }
794}
795
796/// An object name (`schema.name` → `name`) reduced to the last part — datasets are referenced by a
797/// single name in the pipeline config.
798fn object_name(raw: &str) -> String {
799    raw.rsplit('.').next().unwrap_or(raw).trim_matches('"').to_string()
800}
801
802fn statement_kind(s: &Statement) -> &'static str {
803    match s {
804        Statement::Insert(_) => "INSERT",
805        Statement::Query(_) => "SELECT",
806        Statement::CreateTable(_) => "CREATE TABLE",
807        _ => "statement",
808    }
809}