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
//! `FROM` and `JOIN`: the inputs, the `ON` condition, and the qualified columns of a join query.

use super::functions::interval_ms;
use super::{column_name_of, object_name, JoinKind, Plan};
use sqlparser::ast::{
    BinaryOperator, Expr, JoinConstraint, JoinOperator, Select, SelectItem, TableFactor, TableWithJoins,
};

/// The name a JOIN input answers to as a qualifier: its alias when it has one (an alias replaces the
/// table's name, as in SQL), else the table's name.
fn side_names(tf: &TableFactor) -> Vec<String> {
    match tf {
        TableFactor::Table { alias: Some(a), .. } => vec![a.name.value.clone()],
        TableFactor::Table { name, .. } => vec![object_name(&name.to_string())],
        _ => Vec::new(),
    }
}

/// Resolve every table-qualified column of a JOIN query — in the projection, WHERE, GROUP BY and
/// HAVING (the ON clause is parsed on its own) — against the two inputs: `l.c` is the left's `c`;
/// `r.c` is the right's, kept by the join as `<r>_c` (its `rightRename`) unless `c` is the join key
/// (equal on both sides). A bare `r.c` in the projection keeps the output name `c`, as SQL names it.
/// An unknown qualifier, a qualifier both inputs answer to, and `t.*` are refused. Returns the
/// rewritten query and the join's renames.
pub(super) fn resolve_join_qualifiers(
    select: &Select,
    join_key: &str,
) -> Result<(Select, std::collections::BTreeMap<String, String>), String> {
    use std::ops::ControlFlow;
    let left = side_names(&select.from[0].relation);
    let right = side_names(&select.from[0].joins[0].relation);
    if let Some(both) = left.iter().find(|n| right.contains(n)) {
        return Err(format!(
            "both sides of the JOIN answer to `{both}` — give them different aliases (`FROM {both} a JOIN {both} b`)"
        ));
    }
    let right_q = right.first().cloned().unwrap_or_default();
    let mut renames = std::collections::BTreeMap::new();
    // `t.c` → the column's name in the join's output, or why it cannot be resolved
    let mut resolve = |parts: &[sqlparser::ast::Ident]| -> Result<String, String> {
        let [q, c] = parts else {
            return Err(format!(
                "`{}`: a column is `table.column` or `column`",
                parts.iter().map(|p| p.value.as_str()).collect::<Vec<_>>().join(".")
            ));
        };
        let (q, c) = (q.value.as_str(), c.value.as_str());
        if left.iter().any(|n| n == q) || c == join_key && right.iter().any(|n| n == q) {
            Ok(c.to_string())
        } else if right.iter().any(|n| n == q) {
            let out = format!("{right_q}_{c}");
            renames.insert(c.to_string(), out.clone());
            Ok(out)
        } else {
            Err(format!(
                "`{q}.{c}`: `{q}` is not a table of this JOIN (it names {})",
                left.iter()
                    .chain(&right)
                    .map(|n| format!("`{n}`"))
                    .collect::<Vec<_>>()
                    .join(", ")
            ))
        }
    };
    let mut q = select.clone();
    let mut failed: Option<String> = None;
    // a bare `r.c` projected keeps its SQL name `c`
    for item in q.projection.iter_mut() {
        match item {
            SelectItem::UnnamedExpr(Expr::CompoundIdentifier(parts)) => {
                let out = resolve(parts)?;
                let name = parts.last().map(|p| p.value.clone()).unwrap_or_default();
                *item = if out == name {
                    SelectItem::UnnamedExpr(Expr::Identifier(sqlparser::ast::Ident::new(out)))
                } else {
                    SelectItem::ExprWithAlias {
                        expr: Expr::Identifier(sqlparser::ast::Ident::new(out)),
                        alias: sqlparser::ast::Ident::new(name),
                    }
                };
            }
            SelectItem::QualifiedWildcard(kind, _) => {
                let t = kind.to_string();
                let t = t.trim_end_matches(".*");
                return Err(format!(
                    "`{t}.*` in a JOIN is not supported — list its columns (`{t}.col`, …), or use `*`"
                ));
            }
            _ => {}
        }
    }
    let mut rewrite = |e: &mut Expr| {
        if let Expr::CompoundIdentifier(parts) = e {
            match resolve(parts) {
                Ok(out) => *e = Expr::Identifier(sqlparser::ast::Ident::new(out)),
                Err(err) => {
                    failed.get_or_insert(err);
                    return ControlFlow::Break(());
                }
            }
        }
        ControlFlow::Continue(())
    };
    let _ = sqlparser::ast::visit_expressions_mut(&mut q.projection, &mut rewrite);
    let _ = sqlparser::ast::visit_expressions_mut(&mut q.selection, &mut rewrite);
    let _ = sqlparser::ast::visit_expressions_mut(&mut q.group_by, &mut rewrite);
    let _ = sqlparser::ast::visit_expressions_mut(&mut q.having, &mut rewrite);
    match failed {
        Some(err) => Err(err),
        None => Ok((q, renames)),
    }
}

/// `FROM <left> [JOIN <right> ON …]` → a `Source` or a `Join`.
pub(super) fn parse_from(twj: &TableWithJoins) -> Result<Plan, String> {
    let left = table_source(&twj.relation)?;
    match twj.joins.len() {
        0 => Ok(left),
        1 => {
            let j = &twj.joins[0];
            let right = table_source(&j.relation)?;
            let (join_key, kind) = parse_join_operator(&j.join_operator)?;
            Ok(Plan::Join {
                left: Box::new(left),
                right: Box::new(right),
                join_key,
                kind,
                right_rename: Default::default(),
            })
        }
        _ => Err("only a single JOIN is supported".into()),
    }
}

pub(super) fn table_source(tf: &TableFactor) -> Result<Plan, String> {
    match tf {
        TableFactor::Table { name, .. } => Ok(Plan::Source(object_name(&name.to_string()))),
        other => Err(format!("unsupported FROM item `{other}` — a plain dataset name")),
    }
}

/// `JOIN … ON a.k = b.k [AND ts BETWEEN … ]` → (join key, kind). An interval predicate makes it a
/// stream×stream interval join; without one, the right side is a bounded table (the 5b lookup join).
fn parse_join_operator(op: &JoinOperator) -> Result<(String, JoinKind), String> {
    let on = match op {
        JoinOperator::Inner(JoinConstraint::On(e)) | JoinOperator::Join(JoinConstraint::On(e)) => e,
        _ => return Err("only `INNER JOIN … ON <a.k = b.k> [AND <ts> BETWEEN …]` is supported".into()),
    };
    let mut join_key: Option<String> = None;
    let mut interval: Option<(String, i64)> = None;
    for c in split_and(on) {
        match c {
            Expr::BinaryOp {
                left,
                op: BinaryOperator::Eq,
                right,
            } => {
                join_key = Some(same_column(left, right)?);
            }
            Expr::Between {
                expr,
                negated: false,
                low,
                high,
            } => {
                interval = Some(between_interval(expr, low, high)?);
            }
            other => {
                return Err(format!(
                    "unsupported JOIN ON predicate `{other}` — an equi-key `a.k = b.k` and optionally `ts BETWEEN …`"
                ))
            }
        }
    }
    let join_key = join_key.ok_or("JOIN ON needs an equi-key (`a.k = b.k`, same column name on both sides)")?;
    let kind = match interval {
        Some((time_column, window_ms)) => JoinKind::Interval { time_column, window_ms },
        None => JoinKind::Lookup,
    };
    Ok((join_key, kind))
}

/// Flatten an `AND` chain into its conjuncts.
fn split_and(e: &Expr) -> Vec<&Expr> {
    match e {
        Expr::BinaryOp {
            left,
            op: BinaryOperator::And,
            right,
        } => {
            let mut v = split_and(left);
            v.extend(split_and(right));
            v
        }
        Expr::Nested(inner) => split_and(inner),
        other => vec![other],
    }
}

/// `a.k = b.k` → `k`. The two sides must name the SAME column (the join key present on both inputs).
fn same_column(l: &Expr, r: &Expr) -> Result<String, String> {
    let lc = column_name_of(l);
    let rc = column_name_of(r);
    match (lc, rc) {
        (Some(a), Some(b)) if a == b => Ok(a),
        (Some(a), Some(b)) => Err(format!(
            "the JOIN key columns must share a name (`{a}` vs `{b}`) — rename one upstream"
        )),
        _ => Err("JOIN key must be `a.col = b.col` (plain columns)".into()),
    }
}

/// `ts BETWEEN b.ts - INTERVAL … AND b.ts + INTERVAL …` → (event-time column, window ms).
fn between_interval(expr: &Expr, low: &Expr, high: &Expr) -> Result<(String, i64), String> {
    let time_column = column_name_of(expr)
        .ok_or("the interval join's BETWEEN must be over an event-time column (`a.ts BETWEEN …`)")?;
    // the window is the magnitude of the interval on either bound (symmetric ±window).
    let ms = interval_in(high).or_else(|_| interval_in(low))?;
    Ok((time_column, ms))
}

/// Find an `INTERVAL` inside a bound like `b.ts + INTERVAL '5' SECOND` and return it in ms.
fn interval_in(e: &Expr) -> Result<i64, String> {
    match e {
        Expr::Interval(_) => interval_ms(e),
        Expr::BinaryOp { left, right, .. } => interval_in(right).or_else(|_| interval_in(left)),
        Expr::Nested(inner) => interval_in(inner),
        _ => Err("the interval join's BETWEEN needs an `INTERVAL` bound (`b.ts + INTERVAL '5' SECOND`)".into()),
    }
}