fv-streams-sql 0.9.0

The SQL front door for FusionVault Streams: a streaming SQL statement parses to a small typed logical plan and lowers to the same topology the TOML frontend produces — one runtime, two front doors. No DataFusion planner, no fork; expressions compile through the value dialect as the steps already do.
Documentation
//! Planning a `SELECT`: its projection, the `ROW_NUMBER()` top-N idiom, and a windowed aggregate
//! with its `HAVING`.

use super::functions::{is_agg_name, is_window_fn, parse_agg_select, parse_window_fn};
use super::join::{parse_from, resolve_join_qualifiers, table_source};
use super::{column_name_of, AggSpec, Plan, Proj, Win};
use crate::expr;
use sqlparser::ast::{BinaryOperator, Expr, GroupByExpr, Select, SelectItem, SetExpr, TableFactor, Value};

pub(super) fn plan_select(select: &Select) -> Result<Plan, String> {
    // The ROW_NUMBER top-N idiom (a subquery + `WHERE rn <= n`) → the topN operator, projected by
    // the outer SELECT (the rank filter is consumed by the operator).
    if let Some(topn) = detect_topn(select)? {
        return parse_projection(topn, &select.projection);
    }

    // FROM: one table, optionally with one JOIN.
    if select.from.len() != 1 {
        return Err(format!(
            "expected one FROM item (comma joins are not supported — use `JOIN … ON …`), got {}",
            select.from.len()
        ));
    }
    let mut source = parse_from(&select.from[0])?;
    // a JOIN: every `t.col` is resolved against the two inputs on a copy of the query, so the
    // qualifier decides which side a column comes from (the planner knows no schemas; the join keeps
    // a right column the query names under a name only the right can have)
    let resolved;
    let select = if let Plan::Join {
        join_key, right_rename, ..
    } = &mut source
    {
        let (q, renames) = resolve_join_qualifiers(select, join_key)?;
        *right_rename = renames;
        resolved = q;
        &resolved
    } else {
        select
    };

    // WHERE → filter (reads the input columns, so it sits below the aggregate/projection).
    let filtered = match &select.selection {
        Some(pred) => Plan::Filter {
            input: Box::new(source),
            predicate: expr::to_dialect(pred)?,
        },
        None => source,
    };

    // GROUP BY <window-fn>(…), <keys> → a windowed keyed aggregate.
    let group_exprs = match &select.group_by {
        GroupByExpr::Expressions(exprs, _) if !exprs.is_empty() => Some(exprs.as_slice()),
        GroupByExpr::All(_) => return Err("GROUP BY ALL is not supported".into()),
        _ => None,
    };
    if let Some(gexprs) = group_exprs {
        return plan_aggregate(filtered, gexprs, &select.projection, select.having.as_ref());
    }
    if select.having.is_some() {
        return Err("HAVING without GROUP BY has no aggregate to filter — use WHERE".into());
    }

    parse_projection(filtered, &select.projection)
}

/// The `SELECT` projection over an already-built input → a `Project` node.
fn parse_projection(input: Plan, projection: &[SelectItem]) -> Result<Plan, String> {
    let mut star = false;
    let mut items = Vec::new();
    for item in projection {
        match item {
            SelectItem::Wildcard(_) => star = true,
            SelectItem::UnnamedExpr(e) => {
                let out = column_name_of(e).ok_or("a projected expression needs an alias (`<expr> AS name`)")?;
                match e {
                    Expr::Identifier(_) | Expr::CompoundIdentifier(_) => items.push(Proj { out, expr: None }),
                    _ => items.push(Proj {
                        out,
                        expr: Some(expr::to_dialect(e)?),
                    }),
                }
            }
            SelectItem::ExprWithAlias { expr: e, alias } => {
                items.push(Proj {
                    out: alias.value.clone(),
                    expr: Some(expr::to_dialect(e)?),
                });
            }
            // `t.*` over a single-table query is `*`: the qualifier names the one input.
            SelectItem::QualifiedWildcard(_, _) => star = true,
            _ => return Err("multi-alias projections are not supported — list the columns".into()),
        }
    }
    let mut seen = std::collections::HashSet::new();
    if let Some(dup) = items.iter().find(|p| !seen.insert(p.out.as_str())) {
        return Err(format!(
            "two SELECT columns are named `{}` — give one an alias (`… AS other_name`)",
            dup.out
        ));
    }
    if star && items.iter().any(|p| p.expr.is_none()) {
        return Err("`SELECT *, <bare column>` is redundant — `*` already keeps every column; with `*` add only computed columns (`*, <expr> AS name`)".into());
    }
    Ok(Plan::Project {
        input: Box::new(input),
        star,
        items,
    })
}

/// Recognise `FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC]) AS rn FROM s)
/// WHERE rn <= n` → a `TopN` over the inner source. `Ok(None)` when it is not that shape.
fn detect_topn(select: &Select) -> Result<Option<Plan>, String> {
    if select.from.len() != 1 || !select.from[0].joins.is_empty() {
        return Ok(None);
    }
    let TableFactor::Derived { subquery, .. } = &select.from[0].relation else {
        return Ok(None);
    };
    let SetExpr::Select(inner) = subquery.body.as_ref() else {
        return Ok(None);
    };
    // find ROW_NUMBER() OVER (…) AS <rn>
    let mut rn_fn = None;
    for item in &inner.projection {
        if let SelectItem::ExprWithAlias {
            expr: Expr::Function(f),
            alias,
        } = item
        {
            if f.name.to_string().eq_ignore_ascii_case("row_number") {
                rn_fn = Some((f, alias.value.clone()));
            }
        }
    }
    let Some((f, rn)) = rn_fn else { return Ok(None) }; // no ROW_NUMBER → not a top-N
    let Some(sqlparser::ast::WindowType::WindowSpec(ws)) = &f.over else {
        return Err("ROW_NUMBER() needs `OVER (PARTITION BY … ORDER BY …)`".into());
    };
    let group_by: Vec<String> = ws.partition_by.iter().filter_map(column_name_of).collect();
    let ob = ws
        .order_by
        .first()
        .ok_or("ROW_NUMBER() OVER needs an ORDER BY column")?;
    let order_by = column_name_of(&ob.expr).ok_or("ROW_NUMBER() ORDER BY must be a column")?;
    let descending = ob.options.asc == Some(false); // DESC
                                                    // a second ORDER BY column is the tie-break (ascending, like the operator's `tieBy`).
    let tie_by = match ws.order_by.get(1) {
        Some(t) => Some(column_name_of(&t.expr).ok_or("ROW_NUMBER() tie-break must be a column")?),
        None => None,
    };
    if inner.from.len() != 1 || !inner.from[0].joins.is_empty() {
        return Err("the ROW_NUMBER subquery must read exactly one source".into());
    }
    let source = table_source(&inner.from[0].relation)?;
    // the subquery's WHERE (e.g. `type = 'bid'`) filters before the rank.
    let input = match &inner.selection {
        Some(pred) => Plan::Filter {
            input: Box::new(source),
            predicate: expr::to_dialect(pred)?,
        },
        None => source,
    };
    let n = rank_limit(select.selection.as_ref(), &rn)?;
    Ok(Some(Plan::TopN {
        input: Box::new(input),
        group_by,
        order_by,
        tie_by,
        descending,
        n,
    }))
}

/// The `n` from an outer `WHERE <rn> <= n` (or `<rn> = 1`) top-N filter.
fn rank_limit(where_: Option<&Expr>, rn: &str) -> Result<usize, String> {
    let e = where_.ok_or_else(|| format!("a ROW_NUMBER top-N needs an outer `WHERE {rn} <= n`"))?;
    let is_rn = |x: &Expr| column_name_of(x).as_deref() == Some(rn);
    match e {
        Expr::BinaryOp {
            left,
            op: BinaryOperator::LtEq,
            right,
        } if is_rn(left) => literal_usize(right),
        Expr::BinaryOp {
            left,
            op: BinaryOperator::Eq,
            right,
        } if is_rn(left) => match literal_usize(right)? {
            1 => Ok(1),
            _ => Err("a `= n` top-N filter only supports n = 1 — use `<= n`".into()),
        },
        _ => Err(format!("the top-N filter must be `{rn} <= n`")),
    }
}

fn literal_usize(e: &Expr) -> Result<usize, String> {
    match e {
        Expr::Value(v) => match &v.value {
            Value::Number(s, _) => s.trim().parse().map_err(|_| format!("`{s}` is not a whole number")),
            other => Err(format!("expected a number, got `{other}`")),
        },
        other => Err(format!("expected a number, got `{other}`")),
    }
}

/// `GROUP BY <window-fn>(ts,…), <keys>` + aggregate projections → a windowed keyed aggregate.
fn plan_aggregate(
    input: Plan,
    group_exprs: &[Expr],
    projection: &[SelectItem],
    having: Option<&Expr>,
) -> Result<Plan, String> {
    let mut window: Option<(String, Win)> = None;
    let mut group_by = Vec::new();
    for g in group_exprs {
        match g {
            Expr::Function(f) if is_window_fn(f) => {
                if window.is_some() {
                    return Err("only one window function may appear in GROUP BY".into());
                }
                window = Some(parse_window_fn(f)?);
            }
            Expr::Identifier(id) => group_by.push(id.value.clone()),
            Expr::CompoundIdentifier(p) => group_by.push(p.last().expect("non-empty").value.clone()),
            other => {
                return Err(format!(
                    "unsupported GROUP BY item `{other}` — a window function (`TUMBLE/HOP/SESSION`) or a plain column"
                ))
            }
        }
    }
    let (time_column, window) = window.ok_or(
        "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`",
    )?;
    let (pre, aggs, post) = parse_agg_select(projection)?;
    if aggs.is_empty() {
        return Err("a windowed GROUP BY needs at least one aggregate in SELECT (count/sum/avg/min/max)".into());
    }
    // HAVING reads the aggregate's OUTPUT: an aggregate call in it must match a projected alias
    // (`HAVING n > 5` where `count(*) AS n`) — the value dialect has no aggregate functions.
    let having = match having {
        Some(h) => Some(having_to_dialect(h, &aggs)?),
        None => None,
    };
    Ok(Plan::Aggregate {
        input: Box::new(input),
        time_column,
        window,
        group_by,
        pre,
        aggs,
        post,
        having,
        trigger: None,
    })
}

/// `HAVING` in the value dialect, with any aggregate call rewritten to the alias that projects it
/// (`HAVING count(*) > 5` → `n > 5` when `count(*) AS n`); an aggregate call with no projected
/// alias is a loud error naming the fix.
fn having_to_dialect(h: &Expr, aggs: &[AggSpec]) -> Result<String, String> {
    fn rewrite(e: &Expr, aggs: &[AggSpec]) -> Result<Expr, String> {
        Ok(match e {
            Expr::Function(f) if is_agg_name(&f.name.to_string().to_lowercase()) => {
                let call = f.to_string().to_lowercase().replace(' ', "");
                let hit = aggs.iter().find(|a| a.call.to_lowercase().replace(' ', "") == call).ok_or_else(|| {
                    format!("HAVING `{f}` has no matching aggregate in SELECT — project it (`{f} AS name`) and filter on the name")
                })?;
                Expr::Identifier(sqlparser::ast::Ident::new(hit.alias.clone()))
            }
            Expr::BinaryOp { left, op, right } => Expr::BinaryOp {
                left: Box::new(rewrite(left, aggs)?),
                op: op.clone(),
                right: Box::new(rewrite(right, aggs)?),
            },
            Expr::UnaryOp { op, expr } => Expr::UnaryOp {
                op: *op,
                expr: Box::new(rewrite(expr, aggs)?),
            },
            Expr::Nested(inner) => Expr::Nested(Box::new(rewrite(inner, aggs)?)),
            other => other.clone(),
        })
    }
    expr::to_dialect(&rewrite(h, aggs)?)
}