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