skade-flight/tests/powerbi_matrix.rs
//! THE POWER BI MATRIX — the SQL shapes Power Query's `Odbc.DataSource` SQL92
//! generator and the Flight SQL ODBC driver put on the wire, each run over
//! real TCP by the stock client against a skade warehouse, each one a
//! `functional_status` row (`skade-flight/powerbi/<group>`).
//!
//! Shapes marked `documented` come from Microsoft's Power Query SDK docs
//! (Odbc.DataSource, SqlCapabilities, AstVisitor) and the SQL92 generator;
//! `inferred` ones are what that generator produces for the listed M
//! operations. Reds not ours are named in KNOWN_RED with the reason; the
//! test fails on an unexpected red AND on a known red turning green.
//!
//! Table `people(id BIGINT, name VARCHAR, score DOUBLE, ts TIMESTAMP, d DATE,
//! org_id INT)` × 5 rows, `orgs(org_id INT, org VARCHAR)` × 2.
use std::sync::Arc;
use std::time::Duration;
use arrow_array::{Date32Array, Float64Array, Int32Array, Int64Array, RecordBatch, StringArray, TimestampMicrosecondArray};
use arrow_schema::{DataType, Field, Schema, TimeUnit};
use futures::TryStreamExt;
use skade_flight::arrow_flight::sql::client::FlightSqlServiceClient;
use skade_flight::{Auth, Engine};
use tonic::transport::Channel;
fn people() -> (Schema, RecordBatch) {
let schema = Schema::new(vec![
Field::new("id", DataType::Int64, false),
Field::new("name", DataType::Utf8, true),
Field::new("score", DataType::Float64, true),
Field::new("ts", DataType::Timestamp(TimeUnit::Microsecond, None), true),
Field::new("d", DataType::Date32, true),
Field::new("org_id", DataType::Int32, true),
]);
let day = 86_400_000_000i64;
let t0 = 1_704_198_896_000_000i64; // 2024-01-02T12:34:56
let batch = RecordBatch::try_new(
Arc::new(schema.clone()),
vec![
Arc::new(Int64Array::from(vec![1, 2, 3, 4, 5])),
Arc::new(StringArray::from(vec![Some("Ada"), Some("bob"), None, Some("Dee"), Some("eve")])),
Arc::new(Float64Array::from(vec![Some(9.5), Some(3.0), None, Some(7.25), Some(1.0)])),
Arc::new(TimestampMicrosecondArray::from(vec![Some(t0), Some(t0 + day), None, Some(t0 + 40 * day), Some(t0 + 400 * day)])),
Arc::new(Date32Array::from(vec![Some(19724), Some(19725), None, Some(19764), Some(20124)])),
Arc::new(Int32Array::from(vec![Some(1), Some(1), Some(2), None, Some(2)])),
],
)
.unwrap();
(schema, batch)
}
fn orgs() -> (Schema, RecordBatch) {
let schema = Schema::new(vec![
Field::new("org_id", DataType::Int32, false),
Field::new("org", DataType::Utf8, false),
]);
let batch = RecordBatch::try_new(
Arc::new(schema.clone()),
vec![Arc::new(Int32Array::from(vec![1, 2])), Arc::new(StringArray::from(vec!["nordisk", "vetra"]))],
)
.unwrap();
(schema, batch)
}
/// One shape: group, id, SQL, expected row count (None = just has to run).
struct Shape {
group: &'static str,
id: &'static str,
sql: &'static str,
rows: Option<usize>,
}
macro_rules! s {
($g:expr, $id:expr, $sql:expr) => {
Shape { group: $g, id: $id, sql: $sql, rows: None }
};
($g:expr, $id:expr, $sql:expr, $n:expr) => {
Shape { group: $g, id: $id, sql: $sql, rows: Some($n) }
};
}
fn shapes() -> Vec<Shape> {
vec![
// ── navigator: what Power BI runs when you click a table ─────────
s!("navigator", "select_star_quoted", r#"select * from "main"."people""#, 5),
s!("navigator", "select_star_catalog_qualified", r#"select * from "skade"."main"."people""#, 5),
s!("navigator", "preview_top", r#"select top 1000 * from "main"."people""#, 5),
s!("navigator", "preview_limit", r#"select * from "main"."people" limit 1000"#, 5),
s!("navigator", "columns_only", r#"select "id", "name", "score", "ts", "d", "org_id" from "main"."people""#, 5),
s!("navigator", "row_count", r#"select count(*) from "main"."people""#, 1),
// ── projection ───────────────────────────────────────────────────
s!("projection", "subset_aliased", r#"select "id" as "id", "name" as "name" from "main"."people""#, 5),
s!("projection", "rename", r#"select "id" as "Person Id", "score" as "Points" from "main"."people""#, 5),
s!("projection", "derived_table", r#"select "t"."id" from (select "id", "name" from "main"."people") as "t""#, 5),
s!("projection", "computed_column", r#"select "id", "score" * 2 as "double_score" from "main"."people""#, 5),
// ── filter ───────────────────────────────────────────────────────
s!("filter", "eq_number", r#"select * from "main"."people" where "id" = 3"#, 1),
s!("filter", "eq_string", r#"select * from "main"."people" where "name" = 'Ada'"#, 1),
s!("filter", "gt_and_lt", r#"select * from "main"."people" where "score" > 2 and "score" < 8"#, 2),
s!("filter", "or", r#"select * from "main"."people" where "id" = 1 or "id" = 5"#, 2),
s!("filter", "not", r#"select * from "main"."people" where not ("id" = 1)"#, 4),
s!("filter", "in_list", r#"select * from "main"."people" where "id" in (1, 2, 3)"#, 3),
s!("filter", "not_in_list", r#"select * from "main"."people" where "id" not in (1, 2)"#, 3),
s!("filter", "is_null", r#"select * from "main"."people" where "name" is null"#, 1),
s!("filter", "is_not_null", r#"select * from "main"."people" where "score" is not null"#, 4),
s!("filter", "like_prefix", r#"select * from "main"."people" where "name" like 'A%'"#, 1),
s!("filter", "like_contains", r#"select * from "main"."people" where "name" like '%e%'"#, 2),
s!("filter", "like_escape", r#"select * from "main"."people" where "name" like '%\_%' escape '\'"#, 0),
s!("filter", "between", r#"select * from "main"."people" where "id" between 2 and 4"#, 3),
s!("filter", "date_literal", r#"select * from "main"."people" where "d" = date '2024-01-02'"#, 1),
s!("filter", "date_escape", r#"select * from "main"."people" where "d" >= {d '2024-02-01'}"#, 2),
s!("filter", "timestamp_escape", r#"select * from "main"."people" where "ts" < {ts '2024-01-03 00:00:00'}"#, 1),
s!("filter", "timestamp_string_compare", r#"select * from "main"."people" where "ts" >= '2024-02-01 00:00:00'"#, 2),
s!("filter", "string_case_insensitive", r#"select * from "main"."people" where lower("name") = 'ada'"#, 1),
// ── sort ─────────────────────────────────────────────────────────
s!("sort", "order_by_asc", r#"select * from "main"."people" order by "name""#, 5),
s!("sort", "order_by_desc_multi", r#"select * from "main"."people" order by "score" desc, "id" asc"#, 5),
s!("sort", "order_by_ordinal", r#"select "id", "name" from "main"."people" order by 2"#, 5),
s!("sort", "order_by_nulls_last", r#"select * from "main"."people" order by "score" desc nulls last"#, 5),
// ── top / limit ──────────────────────────────────────────────────
s!("top", "top_n", r#"select top 2 * from "main"."people" order by "id""#, 2),
s!("top", "limit_n", r#"select * from "main"."people" order by "id" limit 2"#, 2),
s!("top", "limit_offset", r#"select * from "main"."people" order by "id" limit 2 offset 2"#, 2),
s!("top", "distinct_top", r#"select distinct top 1 "org_id" from "main"."people" where "org_id" is not null"#, 1),
// ── aggregate ────────────────────────────────────────────────────
s!("aggregate", "count_star", r#"select count(*) as "n" from "main"."people""#, 1),
s!("aggregate", "count_column", r#"select count("score") from "main"."people""#, 1),
s!("aggregate", "count_distinct", r#"select count(distinct "org_id") from "main"."people""#, 1),
s!("aggregate", "sum_avg_min_max", r#"select sum("score"), avg("score"), min("score"), max("score") from "main"."people""#, 1),
s!("aggregate", "group_by", r#"select "org_id", count(*) from "main"."people" group by "org_id""#, 3),
s!("aggregate", "group_by_having", r#"select "org_id", count(*) from "main"."people" group by "org_id" having count(*) > 1"#, 2),
s!("aggregate", "group_by_alias_order", r#"select "org_id" as "o", sum("score") as "s" from "main"."people" group by "org_id" order by "s" desc"#, 3),
s!("aggregate", "group_by_expression", r#"select {fn YEAR("ts")} as "y", count(*) from "main"."people" group by {fn YEAR("ts")}"#, 3),
s!("aggregate", "distinct", r#"select distinct "org_id" from "main"."people""#, 3),
// ── join ─────────────────────────────────────────────────────────
s!("join", "inner", r#"select "p"."name", "o"."org" from "main"."people" as "p" inner join "main"."orgs" as "o" on "p"."org_id" = "o"."org_id""#, 4),
s!("join", "left_outer", r#"select "p"."id", "o"."org" from "main"."people" as "p" left outer join "main"."orgs" as "o" on "p"."org_id" = "o"."org_id""#, 5),
s!("join", "oj_escape", r#"select "p"."id" from {oj "main"."people" as "p" left outer join "main"."orgs" as "o" on "p"."org_id" = "o"."org_id"}"#, 5),
s!("join", "cross", r#"select count(*) from "main"."people", "main"."orgs""#, 1),
s!("join", "subquery_in", r#"select * from "main"."people" where "org_id" in (select "org_id" from "main"."orgs" where "org" = 'vetra')"#, 2),
s!("join", "exists", r#"select * from "main"."people" as "p" where exists (select 1 from "main"."orgs" as "o" where "o"."org_id" = "p"."org_id")"#, 4),
// ── cast / convert ───────────────────────────────────────────────
s!("cast", "cast_varchar", r#"select cast("id" as varchar) from "main"."people""#, 5),
s!("cast", "cast_double", r#"select cast("id" as double) from "main"."people""#, 5),
s!("cast", "cast_bigint", r#"select cast("score" as bigint) from "main"."people""#, 5),
s!("cast", "cast_date", r#"select cast("ts" as date) from "main"."people""#, 5),
s!("cast", "cast_decimal", r#"select cast("score" as decimal(10, 2)) from "main"."people""#, 5),
s!("cast", "convert_escape", r#"select {fn CONVERT("id", SQL_VARCHAR)} from "main"."people""#, 5),
// ── string functions ─────────────────────────────────────────────
s!("string", "upper_lower", r#"select upper("name"), lower("name") from "main"."people""#, 5),
s!("string", "fn_ucase_lcase", r#"select {fn UCASE("name")}, {fn LCASE("name")} from "main"."people""#, 5),
s!("string", "fn_length", r#"select {fn LENGTH("name")} from "main"."people""#, 5),
s!("string", "char_length", r#"select char_length("name") from "main"."people""#, 5),
s!("string", "fn_substring", r#"select {fn SUBSTRING("name", 1, 2)} from "main"."people""#, 5),
s!("string", "substring_sql", r#"select substring("name" from 1 for 2) from "main"."people""#, 5),
s!("string", "concat_pipes", r#"select "name" || '-' || cast("id" as varchar) from "main"."people""#, 5),
s!("string", "fn_concat", r#"select {fn CONCAT("name", 'x')} from "main"."people""#, 5),
s!("string", "trim_family", r#"select trim("name"), {fn LTRIM("name")}, {fn RTRIM("name")} from "main"."people""#, 5),
s!("string", "fn_locate", r#"select {fn LOCATE('e', "name")} from "main"."people""#, 5),
s!("string", "replace", r#"select {fn REPLACE("name", 'a', 'o')} from "main"."people""#, 5),
s!("string", "left_right", r#"select {fn LEFT("name", 1)}, {fn RIGHT("name", 1)} from "main"."people""#, 5),
s!("string", "coalesce_ifnull", r#"select coalesce("name", ''), {fn IFNULL("score", 0)} from "main"."people""#, 5),
s!("string", "case_when", r#"select case when "score" > 5 then 'high' when "score" is null then 'none' else 'low' end from "main"."people""#, 5),
// ── date functions ───────────────────────────────────────────────
s!("date", "fn_year_month_day", r#"select {fn YEAR("d")}, {fn MONTH("d")}, {fn DAYOFMONTH("d")} from "main"."people""#, 5),
s!("date", "fn_hour_minute_second", r#"select {fn HOUR("ts")}, {fn MINUTE("ts")}, {fn SECOND("ts")} from "main"."people""#, 5),
s!("date", "fn_quarter_week_doy_dow", r#"select {fn QUARTER("d")}, {fn WEEK("d")}, {fn DAYOFYEAR("d")}, {fn DAYOFWEEK("d")} from "main"."people""#, 5),
s!("date", "extract", r#"select extract(year from "ts"), extract(month from "d") from "main"."people""#, 5),
s!("date", "date_trunc", r#"select date_trunc('month', "ts") from "main"."people""#, 5),
s!("date", "fn_now_curdate", r#"select {fn NOW()}, {fn CURDATE()}, {fn CURTIME()}"#, 1),
s!("date", "current_functions", r#"select current_timestamp, current_date"#, 1),
s!("date", "fn_timestampadd", r#"select {fn TIMESTAMPADD(SQL_TSI_DAY, 1, "ts")} from "main"."people""#, 5),
s!("date", "date_arith_interval", r#"select "ts" + interval '1 day' from "main"."people""#, 5),
s!("date", "fn_dayname_monthname", r#"select {fn DAYNAME("d")}, {fn MONTHNAME("d")} from "main"."people""#, 5),
// ── numeric ──────────────────────────────────────────────────────
s!("numeric", "arith", r#"select "score" + 1, "score" - 1, "score" * 2, "score" / 2, "id" % 2 from "main"."people""#, 5),
s!("numeric", "fn_round_floor_ceiling", r#"select {fn ROUND("score", 0)}, {fn FLOOR("score")}, {fn CEILING("score")} from "main"."people""#, 5),
s!("numeric", "fn_abs_sqrt_power_mod", r#"select {fn ABS("score")}, {fn SQRT("score")}, {fn POWER("score", 2)}, {fn MOD("id", 2)} from "main"."people""#, 5),
s!("numeric", "fn_truncate_sign", r#"select {fn TRUNCATE("score", 0)}, {fn SIGN("score")} from "main"."people""#, 5),
// ── misc ─────────────────────────────────────────────────────────
s!("misc", "select_literal", r#"select 1"#, 1),
s!("misc", "union_all", r#"select "id" from "main"."people" union all select "org_id" from "main"."orgs""#, 7),
s!("misc", "union", r#"select "org_id" from "main"."people" union select "org_id" from "main"."orgs""#, 3),
s!("misc", "unqualified_table", r#"select count(*) from people"#, 1),
// (information_schema lists the `t$snapshots`/`t$manifests`/`t$history` metadata tables too —
// 8 rows for 2 tables. `$history` arrived with the iceberg-rust main fork (0.11, 2026-10-03);
// it was 6 rows on 0.9.)
// ── documented: verbatim Power Query SQL92 generator output (MS diagnostics /
// captured ODBC traces), column names substituted. CRLF + 4-space indents
// as the engine emits them. "ITBL"/"OTBL" derived tables, "C1".. computed.
s!("pq", "full_load_all_columns", "select \"id\",\r\n \"name\",\r\n \"score\",\r\n \"ts\",\r\n \"d\",\r\n \"org_id\"\r\nfrom \"main\".\"people\"", 5),
s!("pq", "preview_limit_1000", "select \"id\", \"name\" from \"main\".\"people\" LIMIT 1000", 5),
s!("pq", "row_count_c1", "select COUNT(1) as \"C1\"\r\nfrom \"main\".\"people\" LIMIT 1", 1),
s!("pq", "first_row", "select \"id\", \"name\" from \"main\".\"people\" order by \"id\" LIMIT 1", 1),
s!("pq", "skip_limit_offset", "select \"id\" from \"main\".\"people\" order by \"id\" LIMIT 1 OFFSET 2", 1),
s!("pq", "offset_without_limit", "select \"id\" from \"main\".\"people\" order by \"id\" OFFSET 3", 2),
s!("pq", "projection_after_sort_itbl", "select \"name\"\r\nfrom \r\n(\r\n select \"id\",\r\n \"name\",\r\n \"score\"\r\n from \"main\".\"people\"\r\n) as \"ITBL\"\r\norder by \"ITBL\".\"id\"", 5),
s!("pq", "null_safe_sort_datetime", "select \"ts\"\r\nfrom \r\n(\r\n select \"ts\",\r\n case\r\n when not \"ts\" is null\r\n then \"ts\"\r\n else {ts '1899-12-28 00:00:00'}\r\n end as \"C1\",\r\n case\r\n when \"ts\" is null\r\n then 0\r\n else 1\r\n end as \"C2\"\r\n from \"main\".\"people\"\r\n) as \"ITBL\"\r\norder by \"ITBL\".\"C1\",\r\n \"ITBL\".\"C2\"", 5),
s!("pq", "eq_string_not_null", "select \"name\", \"id\" from \"main\".\"people\" where \"name\" = 'Ada' and not \"name\" is null order by \"id\" LIMIT 2", 1),
s!("pq", "eq_number_widened_double", "select \"id\" from \"main\".\"people\" where cast(\"id\" as DOUBLE) = 3 and not \"id\" is null", 1),
s!("pq", "in_with_null", "select \"id\" from \"main\".\"people\" where \"org_id\" in (null, 2)", 2),
s!("pq", "nullable_equals", "select \"id\" from \"main\".\"people\" where (\"name\" = 'Ada' or \"name\" is null) and (cast(\"org_id\" as BIGINT) = 1 or \"org_id\" is null)", 1),
s!("pq", "ts_le_escape", "select \"id\" from \"main\".\"people\" where \"ts\" <= {ts '2024-01-03 01:00:00'}", 1),
s!("pq", "date_eq_escape", "select \"id\" from \"main\".\"people\" where \"d\" = {d '2024-01-02'}", 1),
s!("pq", "group_count_c1", "select \"org_id\",\r\n COUNT(1) as \"C1\"\r\nfrom \"main\".\"people\"\r\ngroup by \"org_id\"\r\norder by \"org_id\"", 3),
s!("pq", "round_sum_cast_double", "select round(SUM(cast(\"score\" as DOUBLE)), 2) as \"C1\" from \"main\".\"people\"", 1),
s!("pq", "round_avg_limit1", "select round(AVG(cast(\"score\" as DOUBLE)), 8) as \"C1\" from \"main\".\"people\" LIMIT 1", 1),
s!("pq", "max_min_max_ts", "select \"org_id\", MAX(\"score\") as \"C1\", MIN(\"score\") as \"C2\", MAX(\"ts\") as \"C3\" from \"main\".\"people\" group by \"org_id\" order by \"org_id\" LIMIT 5", 3),
s!("pq", "avg_decimal_itbl", "select \"org_id\", round(\"C1\", 6) as \"C2\"\r\nfrom \r\n(\r\n select \"org_id\",\r\n AVG(cast(\"score\" as DECIMAL)) as \"C1\"\r\n from \"main\".\"people\"\r\n group by \"org_id\"\r\n) as \"ITBL\"\r\norder by \"org_id\"", 3),
s!("pq", "distinct_via_group_by_limit", "select \"org_id\"\r\nfrom \r\n(\r\n select \"org_id\"\r\n from \"main\".\"people\"\r\n group by \"org_id\" LIMIT 5\r\n) as \"ITBL\"\r\norder by \"org_id\"", 3),
s!("pq", "left_join_null_safe_on", "select \"OTBL\".\"id\",\r\n \"ITBL\".\"org\" as \"C1\"\r\nfrom \"main\".\"people\" as \"OTBL\"\r\nleft outer join \"main\".\"orgs\" as \"ITBL\" on (cast(\"OTBL\".\"org_id\" as DOUBLE) = cast(\"ITBL\".\"org_id\" as DOUBLE) or \"OTBL\".\"org_id\" is null and \"ITBL\".\"org_id\" is null)\r\norder by \"id\" LIMIT 5", 5),
s!("pq", "join_then_group_itbl", "select \"org\",\r\n COUNT(1) as \"C1\"\r\nfrom \r\n(\r\n select \"ITBL\".\"org\"\r\n from \"main\".\"people\" as \"OTBL\"\r\n left outer join \"main\".\"orgs\" as \"ITBL\" on (\"OTBL\".\"org_id\" = \"ITBL\".\"org_id\")\r\n where (\"ITBL\".\"org\" = 'vetra' and not \"ITBL\".\"org\" is null)\r\n) as \"ITBL\"\r\ngroup by \"org\"\r\norder by \"org\" LIMIT 5", 1),
s!("pq", "directquery_relationship_join", "select \"OTBL\".\"id\" from \"main\".\"people\" as \"OTBL\" inner join ( select \"org_id\", \"org\" from \"main\".\"orgs\" where \"org\" = 'nordisk' ) as \"ITBL\" on (\"OTBL\".\"org_id\" = \"ITBL\".\"org_id\")", 2),
s!("pq", "arith_c1_c2", "select \"id\", cast(\"id\" as DOUBLE) + 90 as \"C1\", round(cast(\"id\" as DOUBLE) / 90, 8) as \"C2\" from \"main\".\"people\"", 5),
s!("pq", "cast_chain", "select \"score\", cast(\"score\" as VARCHAR) as \"C1\", cast(cast(\"id\" as VARCHAR) as INTEGER) as \"C2\", cast(FLOOR(\"score\") as INTEGER) as \"C3\", cast(\"score\" as DECIMAL(38,6)) as \"C4\" from \"main\".\"people\"", 5),
s!("pq", "bool_case_filter", "select \"C1\"\r\nfrom \r\n(\r\n select case\r\n when \"score\" > 5 and not \"score\" is null\r\n then true\r\n else false\r\n end as \"C1\"\r\n from \"main\".\"people\"\r\n) as \"ITBL\"\r\nwhere \"C1\" = false", 3),
s!("pq", "directquery_visual_limit_501", "select \"org_id\", COUNT(1) as \"C1\" from \"main\".\"people\" group by \"org_id\" limit 501", 3),
s!("pq", "directquery_table_limit_1000001", "select \"id\" from \"main\".\"people\" LIMIT 1000001", 5),
s!("pq", "three_part_name", "select \"id\" from \"skade\".\"main\".\"people\"", 5),
s!("pq", "date_from_timestampadd_diff", "select \"ts\",\r\n \"C1\",\r\n case\r\n when \"C1\" is not null\r\n then { fn timestampadd(SQL_TSI_DAY, { fn timestampdiff(SQL_TSI_DAY, {d '2000-01-01'}, \"C1\") }, {ts '2000-01-01 00:00:00'}) }\r\n else {ts '1899-12-28 00:00:00'}\r\n end as \"C2\"\r\nfrom \r\n(\r\n select \"ts\",\r\n \"ts\" as \"C1\"\r\n from \"main\".\"people\"\r\n) as \"ITBL\"\r\ngroup by \"ts\",\r\n \"C1\"", 5),
s!("pq", "timestampadd_month_diff_frac", "select {fn timestampadd(SQL_TSI_MONTH, 1, \"ts\")}, {fn timestampdiff(SQL_TSI_FRAC_SECOND, \"ts\", \"ts\")}, {fn timestampdiff(SQL_TSI_MONTH, {d '2023-11-15'}, \"d\")} from \"main\".\"people\"", 5),
s!("pq", "string_escapes_full", "select \"name\", {fn UCASE(\"name\")} as \"C1\", {fn LCASE(\"name\")} as \"C2\", {fn LENGTH(\"name\")} as \"C3\", {fn SUBSTRING(\"name\", 2 + 1, 2)} as \"C4\", {fn LTRIM({fn CONCAT(' ', \"name\")})} as \"C5\", {fn REPLACE(\"name\", \"name\", 'X')} as \"C6\", {fn REPEAT(\"name\", 3)} as \"C7\", {fn LOCATE('e', \"name\")} - 1 as \"C8\", {fn CHAR(\"org_id\" + 64)} as \"C9\", {fn ASCII(\"name\")} as \"C10\" from \"main\".\"people\"", 5),
s!("pq", "date_escapes_full", "select \"ts\", {fn YEAR(\"ts\")} as \"C1\", {fn MONTH(\"ts\")} as \"C2\", {fn DAYOFMONTH(\"ts\")} as \"C3\", {fn HOUR(\"ts\")} as \"C4\", {fn QUARTER(\"ts\")} as \"C5\", {fn WEEK(\"ts\")} as \"C6\", {fn DAYOFYEAR(\"ts\")} as \"C7\", {fn CURDATE()} as \"C8\", {fn NOW()} as \"C9\" from \"main\".\"people\"", 5),
s!("pq", "like_escape_underscore", "select \"id\" from \"main\".\"people\" where \"name\" like '%\\_%' {escape '\\'}", 0),
s!("pq", "insert_space_sign_log", "select {fn INSERT(\"name\", 2, 1, 'ZZ')}, {fn SPACE(3)}, {fn SIGN(\"score\")}, {fn LOG(\"score\")}, {fn RAND()} from \"main\".\"people\"", 5),
s!("pq", "fractional_vs_cast_int_eq", "select \"id\" from \"main\".\"people\" where cast(\"id\" as DOUBLE) = 3.5", 0),
s!("pq", "fractional_vs_cast_int_lt", "select \"id\" from \"main\".\"people\" where cast(\"id\" as DOUBLE) < 3.5", 3),
s!("pq", "fractional_vs_cast_int_ge", "select \"id\" from \"main\".\"people\" where cast(\"id\" as DOUBLE) >= 3.5", 2),
s!("misc", "information_schema", r#"select table_name from information_schema.tables where table_schema = 'main' order by 1"#, 8),
s!("date", "fn_timestampadd_expr", r#"select {fn TIMESTAMPADD(SQL_TSI_HOUR, "id", "ts")} from "main"."people""#, 5),
]
}
/// Reds that are not this crate's, named with the reason.
const KNOWN_RED: &[(&str, &str, &str)] = &[];
struct Cell {
group: &'static str,
id: &'static str,
ok: bool,
detail: String,
}
async fn run(c: &mut FlightSqlServiceClient<Channel>, sql: &str) -> Result<usize, String> {
let info = c.execute(sql.to_string(), None).await.map_err(|e| e.to_string())?;
let ticket = info.endpoint[0].ticket.clone().ok_or("no ticket")?;
let mut stream = c.do_get(ticket).await.map_err(|e| e.to_string())?;
let mut n = 0;
while let Some(b) = stream.try_next().await.map_err(|e| e.to_string())? {
n += b.num_rows();
}
Ok(n)
}
#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
async fn powerbi_matrix_over_flight_sql() {
let dir = tempfile::tempdir().unwrap();
let wh = skade::Warehouse::open(dir.path().join("wh")).await.unwrap();
let (s, b) = people();
wh.create_table("people", &s).await.unwrap().append(&[b]).await.unwrap();
let (s, b) = orgs();
wh.create_table("orgs", &s).await.unwrap().append(&[b]).await.unwrap();
let engine = Arc::new(Engine::with_options(wh, Duration::from_secs(60), 2).await.unwrap());
let listener = tokio::net::TcpListener::bind("127.0.0.1:0").await.unwrap();
let addr = listener.local_addr().unwrap();
let _server = tokio::spawn(skade_flight::serve_on(engine, Arc::new(Auth::open()), listener, Default::default(), futures::future::pending()));
let channel = Channel::from_shared(format!("http://{addr}")).unwrap().connect().await.unwrap();
let mut c = FlightSqlServiceClient::new(channel);
let mut cells: Vec<Cell> = Vec::new();
for sh in shapes() {
let r = run(&mut c, sh.sql).await;
let (ok, detail) = match (&r, sh.rows) {
(Ok(n), Some(want)) if *n != want => (false, format!("{} rows, want {want}", n)),
(Ok(n), _) => (true, format!("{n} rows")),
(Err(e), _) => (false, e.replace('\n', " ")),
};
skade_flight::functional_status(&format!("skade-flight/powerbi/{}", sh.group), sh.id, ok, sh.sql);
cells.push(Cell { group: sh.group, id: sh.id, ok, detail });
}
// Render.
let mut out = String::new();
let mut groups: Vec<&str> = cells.iter().map(|c| c.group).collect();
groups.dedup();
for g in groups {
let gc: Vec<&Cell> = cells.iter().filter(|c| c.group == g).collect();
let green = gc.iter().filter(|c| c.ok).count();
out.push_str(&format!("{:<11} {:>2}/{:<2} ", g, green, gc.len()));
for c in &gc {
out.push_str(if c.ok { "✅" } else { "❌" });
}
out.push('\n');
}
for c in cells.iter().filter(|c| !c.ok) {
out.push_str(&format!(" ❌ {}/{}: {}\n", c.group, c.id, c.detail));
}
eprintln!("\n{out}");
#[cfg(feature = "testmatrix")]
{
let rows = skade_flight::drain_functional_rows();
eprintln!("testmatrix: {} functional rows drained", rows.len());
if let Ok(p) = std::env::var("SKADE_FLIGHT_MATRIX_OUT") {
use nornir_testmatrix::TestSink;
nornir_testmatrix::JsonFileSink::new(&p).append(&rows).unwrap();
}
}
let known = |c: &Cell| KNOWN_RED.iter().find(|(g, id, _)| *g == c.group && *id == c.id);
let unexpected_red: Vec<&Cell> = cells.iter().filter(|c| !c.ok && known(c).is_none()).collect();
let unexpected_green: Vec<&Cell> = cells.iter().filter(|c| c.ok && known(c).is_some()).collect();
assert!(unexpected_red.is_empty(), "{} unexpected red:\n{out}", unexpected_red.len());
assert!(unexpected_green.is_empty(), "known red turned green, delete the row(s): {:?}", unexpected_green.iter().map(|c| (c.group, c.id)).collect::<Vec<_>>());
}