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
//! Windowed aggregates: tumbling, sliding and session windows, EMIT, HAVING, and allowed lateness.

use super::*;

#[test]
fn having_lowers_to_a_filter_after_the_aggregate_matching_calls_to_their_aliases() {
    let stages = compile_stages(
        "INSERT INTO out SELECT auction, count(*) AS n, max(price) AS top FROM b WHERE type = 'bid' \
             GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) HAVING count(*) > 5 AND top >= 100",
    )
    .unwrap();
    let steps = &stages[0].steps;
    assert_eq!(steps[0]["op"], "filter", "WHERE first");
    assert_eq!(steps[1]["op"], "windowedAggregate");
    assert_eq!(steps[2]["op"], "filter", "HAVING after the aggregate");
    assert_eq!(
        steps[2]["expression"], "((n > 5) && (top >= 100))",
        "the aggregate call is rewritten to its alias"
    );
    let err = compile_stages(
        "INSERT INTO out SELECT auction, count(*) AS n FROM b GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) HAVING sum(price) > 1",
    )
    .unwrap_err();
    assert!(
        err.contains("no matching aggregate") && err.contains("sum(price)"),
        "{err}"
    );
    let err = compile_stages("INSERT INTO out SELECT * FROM b HAVING price > 1").unwrap_err();
    assert!(err.contains("WHERE"), "{err}");
}

#[test]
fn a_tumbling_windowed_aggregate_lowers_to_the_windowedaggregate_step() {
    let s = &compile_stages(
        "INSERT INTO out SELECT auction, count(*) AS n, max(price) AS top \
             FROM bids WHERE type = 'bid' GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND)",
    )
    .unwrap()[0];
    assert_eq!(s.inputs, vec!["bids".to_string()]);
    assert_eq!(s.output, "out");
    assert_eq!(s.steps[0], json!({"op": "filter", "expression": "(type == 'bid')"}));
    assert_eq!(
        s.steps[1],
        json!({
            "op": "windowedAggregate",
            "timeColumn": "dateTime",
            "windowMs": 10000,
            "keyBy": true,
            "groupBy": ["auction"],
            "aggs": [
                {"op": "count", "column": "", "alias": "n"},
                {"op": "max", "column": "price", "alias": "top"},
            ],
        })
    );
}

#[test]
fn hop_is_a_sliding_window_and_session_is_a_session_aggregate() {
    let hop = &compile_stages(
        "INSERT INTO o SELECT auction, count(*) AS n FROM b GROUP BY auction, HOP(ts, INTERVAL '2' SECOND, INTERVAL '10' SECOND)",
    )
    .unwrap()[0];
    assert_eq!(
        hop.steps[0],
        json!({"op": "windowedAggregate", "timeColumn": "ts", "windowMs": 10000, "slideMs": 2000,
               "keyBy": true, "groupBy": ["auction"], "aggs": [{"op": "count", "column": "", "alias": "n"}]})
    );
    let sess = &compile_stages(
        "INSERT INTO o SELECT bidder, sum(price) AS s FROM b GROUP BY bidder, SESSION(ts, INTERVAL '5' SECOND)",
    )
    .unwrap()[0];
    assert_eq!(
        sess.steps[0],
        json!({"op": "sessionAggregate", "timeColumn": "ts", "gapMs": 5000,
               "keyBy": true, "groupBy": ["bidder"], "aggs": [{"op": "sum", "column": "price", "alias": "s"}]})
    );
}

#[test]
fn emit_changes_makes_it_the_updating_aggregate_q17_shape() {
    // q17: per-auction, per-day, updated as they change.
    let s = &compile_stages(
        "INSERT INTO out SELECT auction, count(*) AS n FROM bids WHERE type = 'bid' \
             GROUP BY auction, TUMBLE(dateTime, INTERVAL '1' DAY) EMIT CHANGES EVERY 5000 ROWS",
    )
    .unwrap()[0];
    assert_eq!(s.steps[0], json!({"op": "filter", "expression": "(type == 'bid')"}));
    assert_eq!(
        s.steps[1],
        json!({
            "op": "windowedAggregate",
            "timeColumn": "dateTime",
            "windowMs": 86_400_000,
            "keyBy": true,
            "groupBy": ["auction"],
            "aggs": [{"op": "count", "column": "", "alias": "n"}],
            "emit": {"everyRows": 5000},
        }),
        "EMIT CHANGES EVERY n ROWS → the emit block the engine's parse_trigger reads"
    );

    // EVERY n MS → the processing-time trigger.
    let ms = &compile_stages(
        "INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, TUMBLE(ts, INTERVAL '10' SECOND) EMIT CHANGES EVERY 500 MS",
    )
    .unwrap()[0];
    assert_eq!(ms.steps[0]["emit"], json!({"everyMs": 500}));
}

#[test]
fn emit_is_rejected_where_it_has_no_meaning_and_needs_a_cadence() {
    // EMIT on a stateless SELECT (no aggregate)
    assert!(compile("INSERT INTO o SELECT * FROM b EMIT CHANGES EVERY 1 ROWS")
        .unwrap_err()
        .contains("windowed"));
    // EMIT CHANGES with no cadence
    assert!(compile(
        "INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, TUMBLE(ts, INTERVAL '1' SECOND) EMIT CHANGES"
    )
    .unwrap_err()
    .to_lowercase()
    .contains("cadence"));
    // EMIT on a session aggregate (the operator that early-fires is the windowed one)
    assert!(compile("INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, SESSION(ts, INTERVAL '5' SECOND) EMIT CHANGES EVERY 1 ROWS")
        .unwrap_err()
        .to_lowercase()
        .contains("session"));
}

#[test]
fn the_word_emit_inside_a_string_literal_is_not_a_trigger() {
    // the quote-aware pre-pass must not strip `emit` when it is data, not a clause.
    let s = &compile_stages("INSERT INTO o SELECT channel FROM b WHERE channel = 'emit changes'").unwrap()[0];
    assert_eq!(
        s.steps[0],
        json!({"op": "filter", "expression": "(channel == 'emit changes')"})
    );
    assert_eq!(
        s.steps.last().unwrap(),
        &json!({"op": "select", "columns": ["channel"]})
    );
}

#[test]
fn count_distinct_and_a_case_argument_become_a_pre_column_the_q15_shape() {
    let s = &compile_stages(
        "INSERT INTO o SELECT auction, count(*) AS total, count(DISTINCT bidder) AS bidders, \
             count(DISTINCT CASE WHEN price < 10000 THEN bidder END) AS r1bidders \
             FROM b GROUP BY auction, TUMBLE(ts, INTERVAL '1' DAY)",
    )
    .unwrap()[0];
    // the CASE argument becomes a synthetic pre-aggregate applyExpression column.
    assert_eq!(
        s.steps[0],
        json!({"op": "applyExpression", "column": "_fv_agg0", "expression": "((price < 10000) ? bidder : null)"})
    );
    assert_eq!(
        s.steps[1],
        json!({
            "op": "windowedAggregate", "timeColumn": "ts", "windowMs": 86_400_000, "keyBy": true,
            "groupBy": ["auction"],
            "aggs": [
                {"op": "count", "column": "", "alias": "total"},
                {"op": "countDistinct", "column": "bidder", "alias": "bidders"},
                {"op": "countDistinct", "column": "_fv_agg0", "alias": "r1bidders"},
            ],
        })
    );
    // DISTINCT is only on count()
    assert!(
        compile("INSERT INTO o SELECT sum(DISTINCT x) AS s FROM b GROUP BY k, TUMBLE(ts, INTERVAL '1' SECOND)")
            .is_err()
    );
}

#[test]
fn a_post_aggregate_computed_column_lowers_after_the_aggregate_the_q15_day_shape() {
    let s = &compile_stages(
        "INSERT INTO out SELECT count(*) AS total, count(DISTINCT CASE WHEN price < 10000 THEN bidder END) AS r1, \
             substring(epochToIso(windowStart), 1, 10) AS day \
             FROM events WHERE type = 'bid' GROUP BY TUMBLE(dateTime, INTERVAL '1' DAY)",
    )
    .unwrap()[0];
    // order: filter, pre (CASE), windowedAggregate, post (day).
    assert_eq!(s.steps[0], json!({"op": "filter", "expression": "(type == 'bid')"}));
    assert_eq!(
        s.steps[1],
        json!({"op": "applyExpression", "column": "_fv_agg0", "expression": "((price < 10000) ? bidder : null)"})
    );
    assert_eq!(s.steps[2]["op"], "windowedAggregate");
    assert_eq!(
        s.steps[2]["aggs"][1],
        json!({"op": "countDistinct", "column": "_fv_agg0", "alias": "r1"})
    );
    assert_eq!(
        s.steps[3],
        json!({"op": "applyExpression", "column": "day", "expression": "substring(epochToIso(windowStart), 1, 10)"})
    );
}

#[test]
fn a_group_by_without_a_window_is_rejected_with_a_pointer_to_windows_or_emit() {
    let e = compile("INSERT INTO o SELECT auction, count(*) AS n FROM b GROUP BY auction").unwrap_err();
    assert!(e.contains("window") || e.contains("EMIT"), "got: {e}");
    // an interval with an unknown unit is a loud error, not a silent zero.
    assert!(compile("INSERT INTO o SELECT count(*) AS n FROM b GROUP BY TUMBLE(ts, INTERVAL '10' YEAR)").is_err());
}

#[test]
fn a_trailing_interval_on_a_window_is_the_allowed_lateness() {
    // q4/q5 need a large lateness so no out-of-order bid is dropped from a closed window; the
    // extra INTERVAL beyond the base arity lowers to the engine's `allowedLatenessMs`.
    let hop = &compile_stages(
        "INSERT INTO o SELECT k, count(*) AS n FROM b \
             GROUP BY k, HOP(ts, INTERVAL '2' SECOND, INTERVAL '10' SECOND, INTERVAL '5000' SECOND)",
    )
    .unwrap()[0];
    assert_eq!(hop.steps[0]["allowedLatenessMs"], json!(5_000_000));
    assert_eq!(hop.steps[0]["windowMs"], json!(10_000));
    assert_eq!(hop.steps[0]["slideMs"], json!(2_000));
    let tumble = &compile_stages(
        "INSERT INTO o SELECT k, max(v) AS m FROM b GROUP BY k, TUMBLE(ts, INTERVAL '10' SECOND, INTERVAL '30' SECOND)",
    )
    .unwrap()[0];
    assert_eq!(tumble.steps[0]["allowedLatenessMs"], json!(30_000));
    // no trailing interval → no lateness field (the engine default stands).
    let plain =
        &compile_stages("INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, TUMBLE(ts, INTERVAL '10' SECOND)")
            .unwrap()[0];
    assert_eq!(plain.steps[0].get("allowedLatenessMs"), None);
    // too many intervals is a loud arity error.
    assert!(compile("INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, TUMBLE(ts, INTERVAL '1' SECOND, INTERVAL '2' SECOND, INTERVAL '3' SECOND)").is_err());
}