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