use super::*;
#[test]
fn a_cte_computing_a_key_then_a_lookup_join_is_two_stages_the_q13_shape() {
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");
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)"}),
]
);
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() {
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);
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"}]})
);
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() {
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() {
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() {
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();
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")
);
}