Skip to main content

fv_streams_sql/
lib.rs

1//! # fv-streams-sql — the SQL front door
2//!
3//! A streaming SQL statement parses to a **small typed logical plan** ([`plan::Plan`]) and lowers to
4//! the *same* [`Topology`] of [`StageDef`](fv_streams_types::StageDef)s the TOML frontend produces (whose `steps` are the engine's
5//! own JSON vocabulary). One runtime, two front doors: SQL inherits checkpointing, the exchange,
6//! exactness and `--join` because it emits the identical steps a hand-written pipeline would.
7//!
8//! Deliberately **not** DataFusion's `LogicalPlan`/physical layer (design §10): our own IR models
9//! streaming (DataFusion's does not), so there is no fork risk, the errors are streaming-aware and
10//! ours, and the IR is the seam a future optimiser plugs into. Scalar expressions lower to the value
11//! dialect strings the `expression` steps already carry, compiled through `fv-value-datafusion` at
12//! build time exactly as the TOML path does.
13//!
14//! ```
15//! let topo = fv_streams_sql::compile(
16//!     "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'",
17//! ).unwrap();
18//! assert_eq!(topo.stages.len(), 1);
19//! ```
20
21use fv_streams_types::Topology;
22
23mod expr;
24mod lower;
25mod plan;
26
27pub use plan::Plan;
28pub mod reference;
29
30/// Compile one streaming SQL statement into a [`Topology`]. Datasets (the `FROM`/`INSERT INTO`
31/// targets) are referenced by name; their bindings (topic, connector, schema) stay in the pipeline
32/// config, exactly as the TOML frontend resolves them. Fails LOUDLY with a streaming-aware message
33/// on anything outside the supported subset.
34pub fn compile(sql: &str) -> Result<Topology, String> {
35    let plan = plan::parse(sql)?;
36    let plan = optimize(plan);
37    let stages = lower::lower(&plan)?;
38    Ok(Topology { stages })
39}
40
41/// The optimiser seam: passes over the typed IR (filter pushdown, projection pruning, …) go here —
42/// streaming-aware because the IR is. Empty today; the boundary exists so a mature optimiser is
43/// additive, on our own IR, never a rewrite (design §10).
44fn optimize(plan: Plan) -> Plan {
45    plan
46}
47
48/// Lower a plan to `StageDef`s without wrapping in a `Topology` — for tests that assert the steps.
49#[cfg(test)]
50pub(crate) fn compile_stages(sql: &str) -> Result<Vec<fv_streams_types::StageDef>, String> {
51    lower::lower(&optimize(plan::parse(sql)?))
52}
53
54#[cfg(test)]
55mod tests {
56    use super::*;
57    use serde_json::json;
58
59    #[test]
60    fn a_filter_projection_lowers_to_the_same_steps_the_toml_would() {
61        let stages =
62            compile_stages("INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'")
63                .unwrap();
64        assert_eq!(stages.len(), 1);
65        let s = &stages[0];
66        assert_eq!(s.inputs, vec!["bids".to_string()]);
67        assert_eq!(s.output, "out");
68        assert_eq!(
69            s.steps,
70            vec![
71                json!({"op": "filter", "expression": "(type == 'bid')"}),
72                json!({"op": "applyExpression", "column": "eur", "expression": "(price * 0.908)"}),
73                json!({"op": "select", "columns": ["auction", "eur"]}),
74            ]
75        );
76    }
77
78    #[test]
79    fn select_star_is_a_pass_through_no_steps() {
80        let s = &compile_stages("INSERT INTO out SELECT * FROM bids").unwrap()[0];
81        assert!(s.steps.is_empty());
82        assert_eq!(s.inputs, vec!["bids".to_string()]);
83        assert_eq!(s.output, "out");
84    }
85
86    #[test]
87    fn star_plus_a_computed_column_keeps_all_columns_the_q1_shape() {
88        // `SELECT *, <expr> AS name` = keep every column + add one (Nexmark q1) → applyExpression, no select.
89        let s = &compile_stages("INSERT INTO out SELECT *, price * 0.908 AS priceEur FROM events WHERE type = 'bid'")
90            .unwrap()[0];
91        assert_eq!(
92            s.steps,
93            vec![
94                json!({"op": "filter", "expression": "(type == 'bid')"}),
95                json!({"op": "applyExpression", "column": "priceEur", "expression": "(price * 0.908)"}),
96            ]
97        );
98        // a bare column with `*` is redundant → a loud error.
99        assert!(compile("INSERT INTO o SELECT *, auction FROM b").is_err());
100    }
101
102    #[test]
103    fn boolean_comparison_and_case_render_in_the_value_dialect() {
104        let s = &compile_stages(
105            "INSERT INTO out SELECT CASE WHEN price < 100 THEN 1 ELSE 0 END AS band FROM b WHERE a > 1 AND c = 2",
106        )
107        .unwrap()[0];
108        // SQL AND/= → dialect &&/== ; CASE → ternary.
109        assert_eq!(
110            s.steps[0],
111            json!({"op": "filter", "expression": "((a > 1) && (c == 2))"})
112        );
113        assert_eq!(
114            s.steps[1],
115            json!({"op": "applyExpression", "column": "band", "expression": "((price < 100) ? 1 : 0)"})
116        );
117    }
118
119    #[test]
120    fn a_bare_rename_and_a_function_call_lower_correctly() {
121        let s = &compile_stages("INSERT INTO out SELECT bidder AS who, round(price * 0.9) AS p FROM b").unwrap()[0];
122        assert_eq!(
123            s.steps[0],
124            json!({"op": "applyExpression", "column": "who", "expression": "bidder"})
125        );
126        assert_eq!(
127            s.steps[1],
128            json!({"op": "applyExpression", "column": "p", "expression": "round((price * 0.9))"})
129        );
130        assert_eq!(s.steps[2], json!({"op": "select", "columns": ["who", "p"]}));
131    }
132
133    #[test]
134    fn having_lowers_to_a_filter_after_the_aggregate_matching_calls_to_their_aliases() {
135        let stages = compile_stages(
136            "INSERT INTO out SELECT auction, count(*) AS n, max(price) AS top FROM b WHERE type = 'bid' \
137             GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) HAVING count(*) > 5 AND top >= 100",
138        )
139        .unwrap();
140        let steps = &stages[0].steps;
141        assert_eq!(steps[0]["op"], "filter", "WHERE first");
142        assert_eq!(steps[1]["op"], "windowedAggregate");
143        assert_eq!(steps[2]["op"], "filter", "HAVING after the aggregate");
144        assert_eq!(
145            steps[2]["expression"], "((n > 5) && (top >= 100))",
146            "the aggregate call is rewritten to its alias"
147        );
148        let err = compile_stages(
149            "INSERT INTO out SELECT auction, count(*) AS n FROM b GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) HAVING sum(price) > 1",
150        )
151        .unwrap_err();
152        assert!(
153            err.contains("no matching aggregate") && err.contains("sum(price)"),
154            "{err}"
155        );
156        let err = compile_stages("INSERT INTO out SELECT * FROM b HAVING price > 1").unwrap_err();
157        assert!(err.contains("WHERE"), "{err}");
158    }
159
160    #[test]
161    fn a_qualified_wildcard_is_star_and_qualified_columns_drop_their_table() {
162        let stages = compile_stages("INSERT INTO out SELECT b.* FROM b WHERE b.type = 'bid'").unwrap();
163        assert_eq!(stages[0].steps.len(), 1, "b.* keeps every column: only the filter");
164        assert_eq!(stages[0].steps[0]["expression"], "(type == 'bid')");
165        let stages = compile_stages("INSERT INTO out SELECT b.auction, b.price * 2 AS twice FROM b").unwrap();
166        let ops: Vec<&str> = stages[0].steps.iter().map(|s| s["op"].as_str().unwrap()).collect();
167        assert_eq!(ops, vec!["applyExpression", "select"]);
168    }
169
170    #[test]
171    fn in_between_and_like_lower_to_the_dialect() {
172        let stages = compile_stages(
173            "INSERT INTO out SELECT * FROM b WHERE auction IN (1, 2, 3) AND price BETWEEN 10 AND 20 \
174             AND channel LIKE 'Goo%le_' AND NOT (name ILIKE '%x') AND bidder NOT IN (7)",
175        )
176        .unwrap();
177        let e = stages[0].steps[0]["expression"].as_str().unwrap().to_string();
178        assert!(e.contains("(auction in [1, 2, 3])"), "{e}");
179        assert!(e.contains("((price >= 10) && (price <= 20))"), "{e}");
180        assert!(e.contains("regexMatch(channel, '^Goo.*le.$')"), "{e}");
181        assert!(e.contains("!((regexMatch(name, '(?i)^.*x$')))"), "{e}");
182        assert!(e.contains("!(bidder in [7])"), "{e}");
183        let err = compile_stages("INSERT INTO out SELECT * FROM b WHERE channel LIKE name").unwrap_err();
184        assert!(err.contains("string-literal pattern"), "{err}");
185    }
186
187    #[test]
188    fn the_unsupported_shapes_are_rejected_with_streaming_aware_errors() {
189        // a bare SELECT with no sink
190        assert!(compile("SELECT * FROM b").unwrap_err().contains("INSERT INTO"));
191        // GROUP BY without a window / EMIT
192        assert!(compile("INSERT INTO o SELECT count(*) AS n FROM b GROUP BY x")
193            .unwrap_err()
194            .contains("window"));
195        // a comma join (only `JOIN … ON …` is supported)
196        assert!(compile("INSERT INTO o SELECT * FROM a, b").is_err());
197        // a set operation
198        assert!(compile("INSERT INTO o SELECT * FROM a UNION SELECT * FROM b")
199            .unwrap_err()
200            .contains("UNION"));
201        // an unbounded ORDER BY → points at ROW_NUMBER top-N
202        assert!(compile("INSERT INTO o SELECT * FROM b ORDER BY price")
203            .unwrap_err()
204            .contains("ROW_NUMBER"));
205    }
206
207    #[test]
208    fn a_tumbling_windowed_aggregate_lowers_to_the_windowedaggregate_step() {
209        let s = &compile_stages(
210            "INSERT INTO out SELECT auction, count(*) AS n, max(price) AS top \
211             FROM bids WHERE type = 'bid' GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND)",
212        )
213        .unwrap()[0];
214        assert_eq!(s.inputs, vec!["bids".to_string()]);
215        assert_eq!(s.output, "out");
216        assert_eq!(s.steps[0], json!({"op": "filter", "expression": "(type == 'bid')"}));
217        assert_eq!(
218            s.steps[1],
219            json!({
220                "op": "windowedAggregate",
221                "timeColumn": "dateTime",
222                "windowMs": 10000,
223                "keyBy": true,
224                "groupBy": ["auction"],
225                "aggs": [
226                    {"op": "count", "column": "", "alias": "n"},
227                    {"op": "max", "column": "price", "alias": "top"},
228                ],
229            })
230        );
231    }
232
233    #[test]
234    fn hop_is_a_sliding_window_and_session_is_a_session_aggregate() {
235        let hop = &compile_stages(
236            "INSERT INTO o SELECT auction, count(*) AS n FROM b GROUP BY auction, HOP(ts, INTERVAL '2' SECOND, INTERVAL '10' SECOND)",
237        )
238        .unwrap()[0];
239        assert_eq!(
240            hop.steps[0],
241            json!({"op": "windowedAggregate", "timeColumn": "ts", "windowMs": 10000, "slideMs": 2000,
242                   "keyBy": true, "groupBy": ["auction"], "aggs": [{"op": "count", "column": "", "alias": "n"}]})
243        );
244        let sess = &compile_stages(
245            "INSERT INTO o SELECT bidder, sum(price) AS s FROM b GROUP BY bidder, SESSION(ts, INTERVAL '5' SECOND)",
246        )
247        .unwrap()[0];
248        assert_eq!(
249            sess.steps[0],
250            json!({"op": "sessionAggregate", "timeColumn": "ts", "gapMs": 5000,
251                   "keyBy": true, "groupBy": ["bidder"], "aggs": [{"op": "sum", "column": "price", "alias": "s"}]})
252        );
253    }
254
255    #[test]
256    fn emit_changes_makes_it_the_updating_aggregate_q17_shape() {
257        // q17: per-auction, per-day, updated as they change.
258        let s = &compile_stages(
259            "INSERT INTO out SELECT auction, count(*) AS n FROM bids WHERE type = 'bid' \
260             GROUP BY auction, TUMBLE(dateTime, INTERVAL '1' DAY) EMIT CHANGES EVERY 5000 ROWS",
261        )
262        .unwrap()[0];
263        assert_eq!(s.steps[0], json!({"op": "filter", "expression": "(type == 'bid')"}));
264        assert_eq!(
265            s.steps[1],
266            json!({
267                "op": "windowedAggregate",
268                "timeColumn": "dateTime",
269                "windowMs": 86_400_000,
270                "keyBy": true,
271                "groupBy": ["auction"],
272                "aggs": [{"op": "count", "column": "", "alias": "n"}],
273                "emit": {"everyRows": 5000},
274            }),
275            "EMIT CHANGES EVERY n ROWS → the emit block the engine's parse_trigger reads"
276        );
277
278        // EVERY n MS → the processing-time trigger.
279        let ms = &compile_stages(
280            "INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, TUMBLE(ts, INTERVAL '10' SECOND) EMIT CHANGES EVERY 500 MS",
281        )
282        .unwrap()[0];
283        assert_eq!(ms.steps[0]["emit"], json!({"everyMs": 500}));
284    }
285
286    #[test]
287    fn emit_is_rejected_where_it_has_no_meaning_and_needs_a_cadence() {
288        // EMIT on a stateless SELECT (no aggregate)
289        assert!(compile("INSERT INTO o SELECT * FROM b EMIT CHANGES EVERY 1 ROWS")
290            .unwrap_err()
291            .contains("windowed"));
292        // EMIT CHANGES with no cadence
293        assert!(compile(
294            "INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, TUMBLE(ts, INTERVAL '1' SECOND) EMIT CHANGES"
295        )
296        .unwrap_err()
297        .to_lowercase()
298        .contains("cadence"));
299        // EMIT on a session aggregate (the operator that early-fires is the windowed one)
300        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")
301            .unwrap_err()
302            .to_lowercase()
303            .contains("session"));
304    }
305
306    #[test]
307    fn the_word_emit_inside_a_string_literal_is_not_a_trigger() {
308        // the quote-aware pre-pass must not strip `emit` when it is data, not a clause.
309        let s = &compile_stages("INSERT INTO o SELECT channel FROM b WHERE channel = 'emit changes'").unwrap()[0];
310        assert_eq!(
311            s.steps[0],
312            json!({"op": "filter", "expression": "(channel == 'emit changes')"})
313        );
314        assert_eq!(
315            s.steps.last().unwrap(),
316            &json!({"op": "select", "columns": ["channel"]})
317        );
318    }
319
320    #[test]
321    fn count_distinct_and_a_case_argument_become_a_pre_column_the_q15_shape() {
322        let s = &compile_stages(
323            "INSERT INTO o SELECT auction, count(*) AS total, count(DISTINCT bidder) AS bidders, \
324             count(DISTINCT CASE WHEN price < 10000 THEN bidder END) AS r1bidders \
325             FROM b GROUP BY auction, TUMBLE(ts, INTERVAL '1' DAY)",
326        )
327        .unwrap()[0];
328        // the CASE argument becomes a synthetic pre-aggregate applyExpression column.
329        assert_eq!(
330            s.steps[0],
331            json!({"op": "applyExpression", "column": "_fv_agg0", "expression": "((price < 10000) ? bidder : null)"})
332        );
333        assert_eq!(
334            s.steps[1],
335            json!({
336                "op": "windowedAggregate", "timeColumn": "ts", "windowMs": 86_400_000, "keyBy": true,
337                "groupBy": ["auction"],
338                "aggs": [
339                    {"op": "count", "column": "", "alias": "total"},
340                    {"op": "countDistinct", "column": "bidder", "alias": "bidders"},
341                    {"op": "countDistinct", "column": "_fv_agg0", "alias": "r1bidders"},
342                ],
343            })
344        );
345        // DISTINCT is only on count()
346        assert!(compile(
347            "INSERT INTO o SELECT sum(DISTINCT x) AS s FROM b GROUP BY k, TUMBLE(ts, INTERVAL '1' SECOND)"
348        )
349        .is_err());
350    }
351
352    #[test]
353    fn a_post_aggregate_computed_column_lowers_after_the_aggregate_the_q15_day_shape() {
354        let s = &compile_stages(
355            "INSERT INTO out SELECT count(*) AS total, count(DISTINCT CASE WHEN price < 10000 THEN bidder END) AS r1, \
356             substring(epochToIso(windowStart), 1, 10) AS day \
357             FROM events WHERE type = 'bid' GROUP BY TUMBLE(dateTime, INTERVAL '1' DAY)",
358        )
359        .unwrap()[0];
360        // order: filter, pre (CASE), windowedAggregate, post (day).
361        assert_eq!(s.steps[0], json!({"op": "filter", "expression": "(type == 'bid')"}));
362        assert_eq!(
363            s.steps[1],
364            json!({"op": "applyExpression", "column": "_fv_agg0", "expression": "((price < 10000) ? bidder : null)"})
365        );
366        assert_eq!(s.steps[2]["op"], "windowedAggregate");
367        assert_eq!(
368            s.steps[2]["aggs"][1],
369            json!({"op": "countDistinct", "column": "_fv_agg0", "alias": "r1"})
370        );
371        assert_eq!(
372            s.steps[3],
373            json!({"op": "applyExpression", "column": "day", "expression": "substring(epochToIso(windowStart), 1, 10)"})
374        );
375    }
376
377    #[test]
378    fn a_group_by_without_a_window_is_rejected_with_a_pointer_to_windows_or_emit() {
379        let e = compile("INSERT INTO o SELECT auction, count(*) AS n FROM b GROUP BY auction").unwrap_err();
380        assert!(e.contains("window") || e.contains("EMIT"), "got: {e}");
381        // an interval with an unknown unit is a loud error, not a silent zero.
382        assert!(compile("INSERT INTO o SELECT count(*) AS n FROM b GROUP BY TUMBLE(ts, INTERVAL '10' YEAR)").is_err());
383    }
384
385    #[test]
386    fn a_plain_equi_join_lowers_to_the_5b_lookup_join() {
387        let s = &compile_stages(
388            "INSERT INTO out SELECT bids.auction, side.value FROM bids JOIN side ON bids.key = side.key",
389        )
390        .unwrap()[0];
391        // inputs[0]=stream, inputs[1]=table (FROM … JOIN …).
392        assert_eq!(s.inputs, vec!["bids".to_string(), "side".to_string()]);
393        // `side.value` is the table's `value` — kept by the join as `side_value`, whatever the stream
394        // carries — and projected back under its SQL name
395        assert_eq!(
396            s.steps,
397            vec![
398                json!({"op": "lookupJoin", "joinKey": "key", "rightRename": {"value": "side_value"}}),
399                json!({"op": "applyExpression", "column": "value", "expression": "side_value"}),
400                json!({"op": "select", "columns": ["auction", "value"]}),
401            ]
402        );
403    }
404
405    #[test]
406    fn a_join_with_a_time_interval_lowers_to_the_stream_join() {
407        let s = &compile_stages(
408            "INSERT INTO out SELECT a.x, b.y FROM a JOIN b \
409             ON a.k = b.k AND a.ts BETWEEN b.ts - INTERVAL '5' SECOND AND b.ts + INTERVAL '5' SECOND",
410        )
411        .unwrap()[0];
412        assert_eq!(s.inputs, vec!["a".to_string(), "b".to_string()]);
413        assert_eq!(
414            s.steps[0],
415            json!({"op": "streamJoin", "joinKey": "k", "timeColumn": "ts", "windowMs": 5000, "keyBy": true, "rightRename": {"y": "b_y"}})
416        );
417    }
418
419    #[test]
420    fn a_qualified_column_in_a_join_reads_its_own_side() {
421        // REGRESSION (streams#55): `a.b` reduced to `b` — in a JOIN, `p.ts` and `a.ts` were both the
422        // left's `ts`, and the right's value was unreachable, with no error.
423        let s = &compile_stages(
424            "INSERT INTO out SELECT p.ts AS person_ts, a.ts AS auction_ts, a.id FROM p JOIN a \
425             ON p.id = a.id AND p.ts BETWEEN a.ts - INTERVAL '5' SECOND AND a.ts + INTERVAL '5' SECOND \
426             WHERE a.price > 10",
427        )
428        .unwrap()[0];
429        assert_eq!(
430            s.steps,
431            vec![
432                json!({"op": "streamJoin", "joinKey": "id", "timeColumn": "ts", "windowMs": 5000, "keyBy": true,
433                       "rightRename": {"price": "a_price", "ts": "a_ts"}}),
434                json!({"op": "filter", "expression": "(a_price > 10)"}),
435                json!({"op": "applyExpression", "column": "person_ts", "expression": "ts"}),
436                json!({"op": "applyExpression", "column": "auction_ts", "expression": "a_ts"}),
437                json!({"op": "select", "columns": ["person_ts", "auction_ts", "id"]}),
438            ],
439            "the left's ts, the right's ts (renamed by the join), the key once"
440        );
441    }
442
443    #[test]
444    fn a_join_refuses_what_it_cannot_resolve() {
445        let join = |select: &str| {
446            compile_stages(&format!(
447                "INSERT INTO out SELECT {select} FROM p JOIN a ON p.id = a.id \
448                 AND p.ts BETWEEN a.ts - INTERVAL '5' SECOND AND a.ts + INTERVAL '5' SECOND"
449            ))
450            .expect_err("refused")
451        };
452        let err = join("p.ts, a.ts");
453        assert!(err.contains("two SELECT columns are named `ts`"), "{err}");
454        let err = join("x.ts");
455        assert!(
456            err.contains("`x` is not a table of this JOIN") && err.contains("`p`, `a`"),
457            "{err}"
458        );
459        let err = join("a.*");
460        assert!(err.contains("`a.*` in a JOIN is not supported"), "{err}");
461        let err = compile_stages(
462            "INSERT INTO out SELECT e.ts FROM e JOIN e ON e.id = e.id \
463             AND e.ts BETWEEN e.ts - INTERVAL '5' SECOND AND e.ts + INTERVAL '5' SECOND",
464        )
465        .expect_err("refused");
466        assert!(err.contains("both sides of the JOIN answer to `e`"), "{err}");
467    }
468
469    #[test]
470    fn row_number_top_n_lowers_to_the_topn_operator() {
471        let s = &compile_stages(
472            "INSERT INTO out SELECT auction, price FROM ( \
473               SELECT auction, price, ROW_NUMBER() OVER (PARTITION BY auction ORDER BY price DESC) AS rn FROM bids \
474             ) WHERE rn <= 1",
475        )
476        .unwrap()[0];
477        assert_eq!(s.inputs, vec!["bids".to_string()]);
478        assert_eq!(
479            s.steps[0],
480            json!({"op": "topN", "n": 1, "orderBy": "price", "direction": "desc", "keyBy": true, "groupBy": ["auction"]})
481        );
482        assert_eq!(
483            s.steps.last().unwrap(),
484            &json!({"op": "select", "columns": ["auction", "price"]})
485        );
486        // ORDER BY ASC and n=10 top-N.
487        let asc = &compile_stages(
488            "INSERT INTO o SELECT k FROM (SELECT k, v, ROW_NUMBER() OVER (PARTITION BY k ORDER BY v) AS rn FROM t) WHERE rn <= 10",
489        )
490        .unwrap()[0];
491        assert_eq!(asc.steps[0]["direction"], "asc");
492        assert_eq!(asc.steps[0]["n"], 10);
493    }
494
495    #[test]
496    fn a_join_key_with_mismatched_column_names_is_a_loud_error() {
497        assert!(compile("INSERT INTO o SELECT * FROM a JOIN b ON a.x = b.y")
498            .unwrap_err()
499            .contains("share a name"));
500    }
501
502    #[test]
503    fn compile_wraps_the_stages_in_a_topology() {
504        let topo = compile("INSERT INTO out SELECT * FROM bids").unwrap();
505        assert_eq!(topo.stages.len(), 1);
506        assert_eq!(topo.stages[0].output, "out");
507    }
508
509    // ── Multi-stage (5c-v): a CTE materialises as its own stage, referenced by name downstream — the
510    // SQL equivalent of the TOML `[[transforms]]` DAG. ──
511
512    #[test]
513    fn a_cte_computing_a_key_then_a_lookup_join_is_two_stages_the_q13_shape() {
514        // q13: enrich bids against a bounded side table on a COMPUTED key (auction % N). The key
515        // must exist on the stream before the join → a `WITH` stage, then the join stage.
516        let stages = compile_stages(
517            "INSERT INTO out \
518             WITH bids AS (SELECT *, auction % 30011 AS key FROM events WHERE type = 'bid') \
519             SELECT * FROM bids JOIN side ON bids.key = side.key",
520        )
521        .unwrap();
522        assert_eq!(stages.len(), 2, "the CTE is its own stage, then the join");
523        // stage 0 = the CTE: filter + the computed key, output under the CTE name.
524        assert_eq!(stages[0].output, "bids");
525        assert_eq!(stages[0].inputs, vec!["events".to_string()]);
526        assert_eq!(
527            stages[0].steps,
528            vec![
529                json!({"op": "filter", "expression": "(type == 'bid')"}),
530                json!({"op": "applyExpression", "column": "key", "expression": "(auction % 30011)"}),
531            ]
532        );
533        // stage 1 = the lookup join, reading the CTE and the bounded table.
534        assert_eq!(stages[1].output, "out");
535        assert_eq!(stages[1].inputs, vec!["bids".to_string(), "side".to_string()]);
536        assert_eq!(stages[1].steps, vec![json!({"op": "lookupJoin", "joinKey": "key"})]);
537    }
538
539    #[test]
540    fn a_windowed_count_cte_then_a_per_window_top_n_is_two_stages_the_q5_shape() {
541        // q5 hot items: a sliding windowed count per auction (a CTE), then top-1 per window.
542        let stages = compile_stages(
543            "INSERT INTO out \
544             WITH counts AS ( \
545               SELECT auction, count(*) AS n FROM events WHERE type = 'bid' \
546               GROUP BY auction, HOP(dateTime, INTERVAL '2' SECOND, INTERVAL '10' SECOND) \
547             ) \
548             SELECT auction FROM ( \
549               SELECT auction, n, ROW_NUMBER() OVER (PARTITION BY windowStart ORDER BY n DESC, auction) AS rn \
550               FROM counts \
551             ) WHERE rn <= 1",
552        )
553        .unwrap();
554        assert_eq!(stages.len(), 2);
555        // stage 0 = the sliding windowed count.
556        assert_eq!(stages[0].output, "counts");
557        assert_eq!(stages[0].inputs, vec!["events".to_string()]);
558        assert_eq!(
559            stages[0].steps[0],
560            json!({"op": "filter", "expression": "(type == 'bid')"})
561        );
562        assert_eq!(
563            stages[0].steps[1],
564            json!({"op": "windowedAggregate", "timeColumn": "dateTime", "windowMs": 10000, "slideMs": 2000,
565                   "keyBy": true, "groupBy": ["auction"], "aggs": [{"op": "count", "column": "", "alias": "n"}]})
566        );
567        // stage 1 = top-1 per window over the counts, projected to the hot auction.
568        assert_eq!(stages[1].output, "out");
569        assert_eq!(stages[1].inputs, vec!["counts".to_string()]);
570        assert_eq!(
571            stages[1].steps[0],
572            json!({"op": "topN", "n": 1, "orderBy": "n", "direction": "desc", "keyBy": true,
573                   "groupBy": ["windowStart"], "tieBy": "auction"})
574        );
575        assert_eq!(stages[1].steps[1], json!({"op": "select", "columns": ["auction"]}));
576    }
577
578    #[test]
579    fn a_cte_feeding_an_aggregate_nests_two_aggregates_the_q4_shape() {
580        // q4-shaped: a per-auction max in a CTE, then an average over those maxima — an aggregate
581        // reading another aggregate's output (two windowed stages).
582        let stages = compile_stages(
583            "INSERT INTO out \
584             WITH perAuction AS ( \
585               SELECT category, max(price) AS finalPrice FROM catBids \
586               GROUP BY category, TUMBLE(dateTime, INTERVAL '10' SECOND) \
587             ) \
588             SELECT category, avg(finalPrice) AS avgFinal FROM perAuction \
589             GROUP BY category, TUMBLE(windowStart, INTERVAL '10' SECOND)",
590        )
591        .unwrap();
592        assert_eq!(stages.len(), 2);
593        assert_eq!(stages[0].output, "perAuction");
594        assert_eq!(stages[0].inputs, vec!["catBids".to_string()]);
595        assert_eq!(stages[0].steps[0]["op"], "windowedAggregate");
596        assert_eq!(
597            stages[0].steps[0]["aggs"],
598            json!([{"op": "max", "column": "price", "alias": "finalPrice"}])
599        );
600        assert_eq!(stages[1].output, "out");
601        assert_eq!(stages[1].inputs, vec!["perAuction".to_string()]);
602        assert_eq!(
603            stages[1].steps[0]["aggs"],
604            json!([{"op": "avg", "column": "finalPrice", "alias": "avgFinal"}])
605        );
606    }
607
608    #[test]
609    fn a_chain_of_ctes_wires_stage_to_stage_in_dependency_order() {
610        // a → b(reads a) → out(reads b): three stages, upstream always before downstream.
611        let stages = compile_stages(
612            "INSERT INTO out \
613             WITH a AS (SELECT bidder, price FROM events WHERE type = 'bid'), \
614                  b AS (SELECT bidder, price * 0.9 AS net FROM a WHERE price > 100) \
615             SELECT * FROM b",
616        )
617        .unwrap();
618        assert_eq!(
619            stages.iter().map(|s| s.output.as_str()).collect::<Vec<_>>(),
620            vec!["a", "b", "out"]
621        );
622        assert_eq!(stages[0].inputs, vec!["events".to_string()]);
623        assert_eq!(stages[1].inputs, vec!["a".to_string()]);
624        assert_eq!(stages[2].inputs, vec!["b".to_string()]);
625    }
626
627    #[test]
628    fn a_cte_referenced_twice_is_still_one_stage() {
629        // both join sides read the same CTE → the CTE stage is emitted once (dedup by name).
630        let stages = compile_stages(
631            "INSERT INTO out \
632             WITH e AS (SELECT bidder, auction, dateTime FROM events WHERE type = 'bid') \
633             SELECT l.bidder FROM e AS l JOIN e AS r \
634             ON l.auction = r.auction AND l.dateTime BETWEEN r.dateTime - INTERVAL '5' SECOND AND r.dateTime + INTERVAL '5' SECOND",
635        )
636        .unwrap();
637        // one stage for e, one for the self-interval-join — not two copies of e.
638        assert_eq!(stages.iter().filter(|s| s.output == "e").count(), 1);
639        assert_eq!(stages.len(), 2);
640        assert_eq!(stages[1].inputs, vec!["e".to_string(), "e".to_string()]);
641    }
642
643    #[test]
644    fn a_trailing_interval_on_a_window_is_the_allowed_lateness() {
645        // q4/q5 need a large lateness so no out-of-order bid is dropped from a closed window; the
646        // extra INTERVAL beyond the base arity lowers to the engine's `allowedLatenessMs`.
647        let hop = &compile_stages(
648            "INSERT INTO o SELECT k, count(*) AS n FROM b \
649             GROUP BY k, HOP(ts, INTERVAL '2' SECOND, INTERVAL '10' SECOND, INTERVAL '5000' SECOND)",
650        )
651        .unwrap()[0];
652        assert_eq!(hop.steps[0]["allowedLatenessMs"], json!(5_000_000));
653        assert_eq!(hop.steps[0]["windowMs"], json!(10_000));
654        assert_eq!(hop.steps[0]["slideMs"], json!(2_000));
655        let tumble = &compile_stages(
656            "INSERT INTO o SELECT k, max(v) AS m FROM b GROUP BY k, TUMBLE(ts, INTERVAL '10' SECOND, INTERVAL '30' SECOND)",
657        )
658        .unwrap()[0];
659        assert_eq!(tumble.steps[0]["allowedLatenessMs"], json!(30_000));
660        // no trailing interval → no lateness field (the engine default stands).
661        let plain = &compile_stages(
662            "INSERT INTO o SELECT k, count(*) AS n FROM b GROUP BY k, TUMBLE(ts, INTERVAL '10' SECOND)",
663        )
664        .unwrap()[0];
665        assert_eq!(plain.steps[0].get("allowedLatenessMs"), None);
666        // too many intervals is a loud arity error.
667        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());
668    }
669
670    #[test]
671    fn recursive_ctes_are_rejected() {
672        assert!(
673            compile("INSERT INTO o WITH RECURSIVE r AS (SELECT * FROM b) SELECT * FROM r")
674                .unwrap_err()
675                .contains("RECURSIVE")
676        );
677    }
678}