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
//! CTEs: each one a stage, wired to the stages that read it.

use super::*;

#[test]
fn a_cte_computing_a_key_then_a_lookup_join_is_two_stages_the_q13_shape() {
    // q13: enrich bids against a bounded side table on a COMPUTED key (auction % N). The key
    // must exist on the stream before the join → a `WITH` stage, then the join stage.
    let stages = compile_stages(
        "INSERT INTO out \
             WITH bids AS (SELECT *, auction % 30011 AS key FROM events WHERE type = 'bid') \
             SELECT * FROM bids JOIN side ON bids.key = side.key",
    )
    .unwrap();
    assert_eq!(stages.len(), 2, "the CTE is its own stage, then the join");
    // stage 0 = the CTE: filter + the computed key, output under the CTE name.
    assert_eq!(stages[0].output, "bids");
    assert_eq!(stages[0].inputs, vec!["events".to_string()]);
    assert_eq!(
        stages[0].steps,
        vec![
            json!({"op": "filter", "expression": "(type == 'bid')"}),
            json!({"op": "applyExpression", "column": "key", "expression": "(auction % 30011)"}),
        ]
    );
    // stage 1 = the lookup join, reading the CTE and the bounded table.
    assert_eq!(stages[1].output, "out");
    assert_eq!(stages[1].inputs, vec!["bids".to_string(), "side".to_string()]);
    assert_eq!(stages[1].steps, vec![json!({"op": "lookupJoin", "joinKey": "key"})]);
}

#[test]
fn a_windowed_count_cte_then_a_per_window_top_n_is_two_stages_the_q5_shape() {
    // q5 hot items: a sliding windowed count per auction (a CTE), then top-1 per window.
    let stages = compile_stages(
        "INSERT INTO out \
             WITH counts AS ( \
               SELECT auction, count(*) AS n FROM events WHERE type = 'bid' \
               GROUP BY auction, HOP(dateTime, INTERVAL '2' SECOND, INTERVAL '10' SECOND) \
             ) \
             SELECT auction FROM ( \
               SELECT auction, n, ROW_NUMBER() OVER (PARTITION BY windowStart ORDER BY n DESC, auction) AS rn \
               FROM counts \
             ) WHERE rn <= 1",
    )
    .unwrap();
    assert_eq!(stages.len(), 2);
    // stage 0 = the sliding windowed count.
    assert_eq!(stages[0].output, "counts");
    assert_eq!(stages[0].inputs, vec!["events".to_string()]);
    assert_eq!(
        stages[0].steps[0],
        json!({"op": "filter", "expression": "(type == 'bid')"})
    );
    assert_eq!(
        stages[0].steps[1],
        json!({"op": "windowedAggregate", "timeColumn": "dateTime", "windowMs": 10000, "slideMs": 2000,
               "keyBy": true, "groupBy": ["auction"], "aggs": [{"op": "count", "column": "", "alias": "n"}]})
    );
    // stage 1 = top-1 per window over the counts, projected to the hot auction.
    assert_eq!(stages[1].output, "out");
    assert_eq!(stages[1].inputs, vec!["counts".to_string()]);
    assert_eq!(
        stages[1].steps[0],
        json!({"op": "topN", "n": 1, "orderBy": "n", "direction": "desc", "keyBy": true,
               "groupBy": ["windowStart"], "tieBy": "auction"})
    );
    assert_eq!(stages[1].steps[1], json!({"op": "select", "columns": ["auction"]}));
}

#[test]
fn a_cte_feeding_an_aggregate_nests_two_aggregates_the_q4_shape() {
    // q4-shaped: a per-auction max in a CTE, then an average over those maxima — an aggregate
    // reading another aggregate's output (two windowed stages).
    let stages = compile_stages(
        "INSERT INTO out \
             WITH perAuction AS ( \
               SELECT category, max(price) AS finalPrice FROM catBids \
               GROUP BY category, TUMBLE(dateTime, INTERVAL '10' SECOND) \
             ) \
             SELECT category, avg(finalPrice) AS avgFinal FROM perAuction \
             GROUP BY category, TUMBLE(windowStart, INTERVAL '10' SECOND)",
    )
    .unwrap();
    assert_eq!(stages.len(), 2);
    assert_eq!(stages[0].output, "perAuction");
    assert_eq!(stages[0].inputs, vec!["catBids".to_string()]);
    assert_eq!(stages[0].steps[0]["op"], "windowedAggregate");
    assert_eq!(
        stages[0].steps[0]["aggs"],
        json!([{"op": "max", "column": "price", "alias": "finalPrice"}])
    );
    assert_eq!(stages[1].output, "out");
    assert_eq!(stages[1].inputs, vec!["perAuction".to_string()]);
    assert_eq!(
        stages[1].steps[0]["aggs"],
        json!([{"op": "avg", "column": "finalPrice", "alias": "avgFinal"}])
    );
}

#[test]
fn a_chain_of_ctes_wires_stage_to_stage_in_dependency_order() {
    // a → b(reads a) → out(reads b): three stages, upstream always before downstream.
    let stages = compile_stages(
        "INSERT INTO out \
             WITH a AS (SELECT bidder, price FROM events WHERE type = 'bid'), \
                  b AS (SELECT bidder, price * 0.9 AS net FROM a WHERE price > 100) \
             SELECT * FROM b",
    )
    .unwrap();
    assert_eq!(
        stages.iter().map(|s| s.output.as_str()).collect::<Vec<_>>(),
        vec!["a", "b", "out"]
    );
    assert_eq!(stages[0].inputs, vec!["events".to_string()]);
    assert_eq!(stages[1].inputs, vec!["a".to_string()]);
    assert_eq!(stages[2].inputs, vec!["b".to_string()]);
}

#[test]
fn a_cte_referenced_twice_is_still_one_stage() {
    // both join sides read the same CTE → the CTE stage is emitted once (dedup by name).
    let stages = compile_stages(
        "INSERT INTO out \
             WITH e AS (SELECT bidder, auction, dateTime FROM events WHERE type = 'bid') \
             SELECT l.bidder FROM e AS l JOIN e AS r \
             ON l.auction = r.auction AND l.dateTime BETWEEN r.dateTime - INTERVAL '5' SECOND AND r.dateTime + INTERVAL '5' SECOND",
    )
    .unwrap();
    // one stage for e, one for the self-interval-join — not two copies of e.
    assert_eq!(stages.iter().filter(|s| s.output == "e").count(), 1);
    assert_eq!(stages.len(), 2);
    assert_eq!(stages[1].inputs, vec!["e".to_string(), "e".to_string()]);
}

#[test]
fn recursive_ctes_are_rejected() {
    assert!(
        compile("INSERT INTO o WITH RECURSIVE r AS (SELECT * FROM b) SELECT * FROM r")
            .unwrap_err()
            .contains("RECURSIVE")
    );
}