//! Regression tests for PostgreSQL → Fabric transpilation.
use polyglot_sql::{transpile, Dialect, DialectType, TranspileOptions};
fn pg_to_fabric(sql: &str) -> String {
transpile(sql, DialectType::PostgreSQL, DialectType::Fabric)
.unwrap_or_else(|e| panic!("transpile failed for {sql:?}: {e}"))
.into_iter()
.next()
.expect("expected at least one statement")
}
fn pg_to_fabric_strict(sql: &str) -> String {
Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.unwrap_or_else(|e| panic!("strict transpile failed for {sql:?}: {e}"))
.into_iter()
.next()
.expect("expected at least one statement")
}
fn tsql_to_fabric(sql: &str) -> String {
transpile(sql, DialectType::TSQL, DialectType::Fabric)
.unwrap_or_else(|e| panic!("transpile failed for {sql:?}: {e}"))
.into_iter()
.next()
.expect("expected at least one statement")
}
#[test]
fn postgres_json_operators_map_to_valid_fabric_json_functions() {
let cases = [
(
"SELECT a -> 'name' FROM t",
"SELECT JSON_QUERY(a, '$.name') FROM t",
),
(
"SELECT a ->> 'name' FROM t",
"SELECT JSON_VALUE(a, '$.name') FROM t",
),
(
"SELECT a #> '{x,y}' FROM t",
"SELECT JSON_QUERY(a, '$.x.y') FROM t",
),
(
"SELECT a #>> '{x,y}' FROM t",
"SELECT JSON_VALUE(a, '$.x.y') FROM t",
),
(
"SELECT data -> 'a' -> 'b' FROM t",
"SELECT JSON_QUERY(data, '$.a.b') FROM t",
),
(
"SELECT data -> 'a' ->> 'b' FROM t",
"SELECT JSON_VALUE(data, '$.a.b') FROM t",
),
(
"SELECT data -> 0 FROM t",
"SELECT JSON_QUERY(data, '$[0]') FROM t",
),
(
"SELECT a #> '{x,0}' FROM t",
"SELECT JSON_QUERY(a, '$.x[0]') FROM t",
),
(
"SELECT a -> 'first name' FROM t",
"SELECT JSON_QUERY(a, '$.\"first name\"') FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_json_constructors_and_aggregates_map_to_fabric_json_functions() {
let cases = [
(
"SELECT jsonb_build_object('a', 1)",
"SELECT JSON_OBJECT('a': 1)",
),
(
"SELECT json_build_object('a', 1, 'b', value) FROM t",
"SELECT JSON_OBJECT('a': 1, 'b': value) FROM t",
),
(
"SELECT json_agg(a) FROM t",
"SELECT JSON_ARRAYAGG(a) FROM t",
),
(
"SELECT jsonb_agg(a) FROM t",
"SELECT JSON_ARRAYAGG(a) FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_json_array_elements_projection_maps_to_fabric_openjson() {
assert_eq!(
pg_to_fabric_strict("SELECT jsonb_array_elements('[1,2]'::jsonb)"),
"SELECT value FROM OPENJSON(CAST('[1,2]' AS VARCHAR(MAX)))"
);
}
#[test]
fn postgres_json_row_shapes_without_scalar_fabric_equivalent_fail_in_strict_mode() {
let cases = [
("SELECT row_to_json(t) FROM t", "ROW_TO_JSON"),
(
"SELECT jsonb_array_elements(data) FROM t",
"JSONB_ARRAY_ELEMENTS",
),
];
for (sql, expected) in cases {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.expect_err("strict mode should reject unsupported PostgreSQL JSON row shapes");
assert!(
err.to_string().contains(expected),
"unexpected error for {sql}: {err}"
);
}
}
#[test]
fn postgres_positional_parameters_render_as_tsql_style_placeholders_for_fabric() {
assert_eq!(pg_to_fabric("SELECT $1, $2"), "SELECT @P1, @P2");
}
#[test]
fn postgres_positional_parameters_render_as_tsql_style_placeholders_in_fabric_predicates() {
assert_eq!(
pg_to_fabric("SELECT id FROM t WHERE a = $1 AND b < $2"),
"SELECT id FROM t WHERE a = @P1 AND b < @P2"
);
}
#[test]
fn postgres_current_temporal_niladics_map_to_fabric_expressions() {
let cases = [
(
"SELECT current_date AS c FROM t",
"SELECT CAST(GETDATE() AS DATE) AS c FROM t",
),
(
"SELECT current_time AS c FROM t",
"SELECT CAST(GETDATE() AS TIME) AS c FROM t",
),
(
"SELECT localtimestamp AS c FROM t",
"SELECT GETDATE() AS c FROM t",
),
("SELECT now() AS c FROM t", "SELECT GETDATE() AS c FROM t"),
(
"SELECT current_timestamp AS c FROM t",
"SELECT GETDATE() AS c FROM t",
),
(
"SELECT clock_timestamp() AS c FROM t",
"SELECT SYSDATETIME() AS c FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_math_and_string_functions_map_to_fabric_semantics() {
let cases = [
("SELECT log(x) FROM t", "SELECT LOG10(x) FROM t"),
("SELECT ln(x) FROM t", "SELECT LOG(x) FROM t"),
("SELECT log(2, x) FROM t", "SELECT LOG(x, 2) FROM t"),
("SELECT log10(x) FROM t", "SELECT LOG10(x) FROM t"),
(
"SELECT div(7, 2)",
"SELECT CAST(CAST(CAST(7 AS FLOAT) / 2 AS INT) AS DECIMAL)",
),
(
"SELECT cbrt(27)",
"SELECT POWER(CAST(27 AS FLOAT), 1.0 / 3.0)",
),
(
"SELECT repeat('ab', 3) FROM t",
"SELECT REPLICATE('ab', 3) FROM t",
),
("SELECT chr(65) FROM t", "SELECT CHAR(65) FROM t"),
(
"SELECT overlay('hello' placing 'XX' from 2 for 2)",
"SELECT STUFF('hello', 2, 2, 'XX')",
),
("SELECT btrim(' hi ')", "SELECT TRIM(' hi ')"),
(
"SELECT btrim('xxhixx', 'x')",
"SELECT TRIM('x' FROM 'xxhixx')",
),
(
"SELECT trim(both 'x' from 'xxhelloxx')",
"SELECT TRIM('x' FROM 'xxhelloxx')",
),
(
"SELECT trim(leading 'x' from 'xxhello')",
"SELECT LTRIM('xxhello', 'x')",
),
(
"SELECT trim(trailing 'x' from 'helloxx')",
"SELECT RTRIM('helloxx', 'x')",
),
(
"SELECT md5('abc')",
"SELECT LOWER(CONVERT(VARCHAR(32), HASHBYTES('MD5', 'abc'), 2))",
),
("SELECT octet_length('hello')", "SELECT DATALENGTH('hello')"),
(
"SELECT bit_length('hello')",
"SELECT DATALENGTH('hello') * 8",
),
(
"SELECT starts_with('hello', 'he')",
"SELECT CAST(CASE WHEN LEFT('hello', LEN('he')) = 'he' THEN 1 ELSE 0 END AS BIT)",
),
(
"SELECT to_hex(255)",
"SELECT LOWER(CONVERT(VARCHAR(MAX), CAST(255 AS VARBINARY(MAX)), 2))",
),
(
"SELECT encode('abc', 'hex')",
"SELECT LOWER(CONVERT(VARCHAR(MAX), CAST('abc' AS VARBINARY(MAX)), 2))",
),
(
"SELECT to_number('123.45', '999.99')",
"SELECT TRY_CONVERT(DECIMAL(18, 2), '123.45')",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_math_functions_without_fabric_equivalent_fail_in_strict_mode() {
let cases = [
("SELECT erf(x)", "ERF"),
("SELECT gcd(12, 8)", "GCD"),
("SELECT lcm(4, 6)", "LCM"),
("SELECT width_bucket(5, 0, 10, 5)", "WIDTH_BUCKET"),
];
for (sql, expected) in cases {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.expect_err("strict mode should reject unsupported PostgreSQL math functions");
assert!(
err.to_string().contains(expected),
"unexpected error for {sql}: {err}"
);
}
}
#[test]
fn postgres_string_functions_with_unsupported_fabric_shapes_fail_in_strict_mode() {
let cases = [
("SELECT encode('abc', 'base64')", "ENCODE"),
("SELECT quote_literal(name) FROM t", "QUOTE_LITERAL"),
("SELECT to_number('123.45', format_mask)", "TO_NUMBER"),
];
for (sql, expected) in cases {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.expect_err("strict mode should reject unsupported PostgreSQL string function shapes");
assert!(
err.to_string().contains(expected),
"unexpected error for {sql}: {err}"
);
}
}
#[test]
fn postgres_system_functions_without_fabric_equivalent_fail_in_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT pg_typeof(a) FROM t",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject unsupported PostgreSQL system functions");
assert!(
err.to_string().contains("PG_TYPEOF"),
"unexpected error: {err}"
);
}
#[test]
fn postgres_regex_operators_map_to_fabric_patindex_predicates_in_default_mode() {
let cases = [
(
"SELECT name ~ '^a.*z$' FROM t",
"SELECT CAST(CASE WHEN PATINDEX('^a.*z$', name) > 0 THEN 1 ELSE 0 END AS BIT) FROM t",
),
(
"SELECT name ~* 'abc' FROM t",
"SELECT CAST(CASE WHEN PATINDEX(LOWER('abc'), LOWER(name)) > 0 THEN 1 ELSE 0 END AS BIT) FROM t",
),
(
"SELECT name !~ 'abc' FROM t",
"SELECT CAST(CASE WHEN NOT PATINDEX('abc', name) > 0 THEN 1 ELSE 0 END AS BIT) FROM t",
),
(
"SELECT name !~* 'abc' FROM t",
"SELECT CAST(CASE WHEN NOT PATINDEX(LOWER('abc'), LOWER(name)) > 0 THEN 1 ELSE 0 END AS BIT) FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_similar_to_maps_to_fabric_like_when_pattern_is_compatible() {
let cases = [
(
"SELECT name SIMILAR TO 'a%' FROM t",
"SELECT CAST(CASE WHEN name LIKE 'a%' THEN 1 ELSE 0 END AS BIT) FROM t",
),
(
"SELECT id FROM t WHERE name SIMILAR TO 'Brand#[1-3][0-9]'",
"SELECT id FROM t WHERE name LIKE 'Brand#[1-3][0-9]'",
),
(
"SELECT name NOT SIMILAR TO 'z%' FROM t",
"SELECT CAST(CASE WHEN NOT name LIKE 'z%' THEN 1 ELSE 0 END AS BIT) FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_complex_similar_to_patterns_still_fail_for_fabric_in_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT id FROM t WHERE name SIMILAR TO '%(b|d)%'",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject SQL-regex SIMILAR TO patterns");
assert!(
err.to_string().contains("regular expression predicates"),
"unexpected error: {err}"
);
}
#[test]
fn postgres_array_semantics_without_fabric_equivalent_fail_in_strict_mode() {
let cases = [
("SELECT ARRAY[1, 2, 3]", "array literals"),
("SELECT (ARRAY[1,2,3])[1]", "array subscripts"),
("SELECT array_length(ARRAY[1,2,3], 1)", "ARRAY_LENGTH"),
("SELECT unnest(ARRAY[1,2,3])", "UNNEST"),
(
"SELECT array_to_string(ARRAY['a','b'], ',')",
"ARRAY_TO_STRING",
),
("SELECT cardinality(ARRAY[1,2,3])", "CARDINALITY"),
("SELECT string_to_array('a,b,c', ',')", "STRING_TO_ARRAY"),
];
for (sql, expected) in cases {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.expect_err("strict mode should reject unsupported PostgreSQL array semantics");
assert!(
err.to_string().contains(expected),
"unexpected error for {sql}: {err}"
);
}
}
#[test]
fn default_postgres_array_length_preserves_dimension_argument_for_fabric() {
assert_eq!(
pg_to_fabric("SELECT array_length(ARRAY[1,2,3], 1)"),
"SELECT ARRAY_LENGTH(ARRAY[1, 2, 3], 1)"
);
}
#[test]
fn native_fabric_single_arg_log_keeps_natural_log_semantics() {
let out = transpile(
"SELECT LOG(x) FROM t",
DialectType::Fabric,
DialectType::Fabric,
)
.expect("Fabric LOG should transpile")
.into_iter()
.next()
.expect("expected one statement");
assert_eq!(out, "SELECT LOG(x) FROM t");
}
#[test]
fn postgres_extract_and_date_part_fields_map_to_valid_fabric_dateparts() {
let cases = [
(
"SELECT EXTRACT(doy FROM ts) AS c FROM t",
"SELECT DATEPART(DAYOFYEAR, ts) AS c FROM t",
),
(
"SELECT date_part('dow', ts) AS c FROM t",
"SELECT DATEPART(WEEKDAY, ts) AS c FROM t",
),
(
"SELECT date_part('hour', ts) AS c FROM t",
"SELECT DATEPART(hour, ts) AS c FROM t",
),
(
"SELECT date_part('isoweek', ts) AS c FROM t",
"SELECT DATEPART(ISO_WEEK, ts) AS c FROM t",
),
(
"SELECT EXTRACT(epoch FROM ts) AS c FROM t",
"SELECT DATEDIFF(SECOND, CAST('1970-01-01' AS DATETIME2), ts) AS c FROM t",
),
(
"SELECT date_part('epoch', ts) AS c FROM t",
"SELECT DATEDIFF(SECOND, CAST('1970-01-01' AS DATETIME2), ts) AS c FROM t",
),
(
"SELECT EXTRACT(isodow FROM ts) AS c FROM t",
"SELECT (((DATEPART(WEEKDAY, ts) + @@DATEFIRST - 2) % 7) + 1) AS c FROM t",
),
(
"SELECT date_part('isodow', ts) AS c FROM t",
"SELECT (((DATEPART(WEEKDAY, ts) + @@DATEFIRST - 2) % 7) + 1) AS c FROM t",
),
(
"SELECT date_part('year'::text, ts) AS c FROM t",
"SELECT DATEPART(year, ts) AS c FROM t",
),
(
"SELECT date_part('month'::text, ts) AS c FROM t",
"SELECT DATEPART(month, ts) AS c FROM t",
),
(
"SELECT date_part('dow'::text, ts) AS c FROM t",
"SELECT DATEPART(WEEKDAY, ts) AS c FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_date_trunc_fields_map_to_valid_fabric_datetrunc_dateparts() {
let cases = [
(
"SELECT date_trunc('day', ts) AS c FROM t",
"SELECT DATETRUNC(DAY, ts) AS c FROM t",
),
(
"SELECT date_trunc('month', ts) AS c FROM t",
"SELECT DATETRUNC(MONTH, ts) AS c FROM t",
),
(
"SELECT date_trunc('year', ts) AS c FROM t",
"SELECT DATETRUNC(YEAR, ts) AS c FROM t",
),
(
"SELECT date_trunc('hour', ts) AS c FROM t",
"SELECT DATETRUNC(HOUR, ts) AS c FROM t",
),
(
"SELECT date_trunc('week', ts) AS c FROM t",
"SELECT DATETRUNC(WEEK, ts) AS c FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_date_bin_maps_to_fabric_date_bucket_for_simple_strides() {
let out = pg_to_fabric_strict(
"SELECT date_bin('15 minutes', completed_at, TIMESTAMP '2001-01-01') AS b FROM order_fulfillment_history",
);
assert_eq!(
out,
"SELECT DATE_BUCKET(MINUTE, 15, completed_at, CAST('2001-01-01' AS DATETIME2(6))) AS b FROM order_fulfillment_history"
);
}
#[test]
fn postgres_to_timestamp_and_to_date_formats_map_to_valid_fabric_convert_signatures() {
let cases = [
(
"SELECT make_date(2020, 1, 1) AS c FROM t",
"SELECT DATEFROMPARTS(2020, 1, 1) AS c FROM t",
),
(
"SELECT to_timestamp('2020-01-01', 'YYYY-MM-DD') AS c FROM t",
"SELECT CONVERT(DATETIME2, '2020-01-01', 23) AS c FROM t",
),
(
"SELECT to_timestamp('2020-01-01 12:13:14', 'YYYY-MM-DD HH24:MI:SS') AS c FROM t",
"SELECT CONVERT(DATETIME2, '2020-01-01 12:13:14', 120) AS c FROM t",
),
(
"SELECT to_timestamp('2020-01-01T12:13:14.123456', 'YYYY-MM-DDTHH24:MI:SS.US') AS c FROM t",
"SELECT CONVERT(DATETIME2, '2020-01-01T12:13:14.123456', 126) AS c FROM t",
),
(
"SELECT to_date('2020-01-01', 'YYYY-MM-DD') AS c FROM t",
"SELECT CONVERT(DATE, '2020-01-01', 23) AS c FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_unsupported_date_time_functions_fail_for_fabric_in_strict_mode() {
let cases = [
"SELECT age(ts) AS c FROM t",
"SELECT age(d1, timestamp '2000-01-01') AS c FROM t",
];
for sql in cases {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.expect_err("strict mode should reject unsupported PostgreSQL date/time functions");
assert!(
err.to_string().contains("AGE"),
"unexpected error for PostgreSQL AGE in {sql}: {err}"
);
}
}
#[test]
fn postgres_to_char_formats_map_to_fabric_dotnet_format_strings() {
let cases = [
(
"SELECT to_char(ts, 'YYYY-MM-DD') AS c FROM t",
"SELECT FORMAT(ts, 'yyyy-MM-dd') AS c FROM t",
),
(
"SELECT to_char(ts, 'YYYY-MM-DD HH24:MI:SS.US') AS c FROM t",
"SELECT FORMAT(ts, 'yyyy-MM-dd HH:mm:ss.ffffff') AS c FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_format_string_interpolation_maps_to_fabric_concat() {
let cases = [
(
"SELECT format('%s-%s', a, b) FROM t",
"SELECT CONCAT(a, '-', b) FROM t",
),
(
"SELECT format('x=%s', f1) FROM text_tbl",
"SELECT CONCAT('x=', f1) FROM text_tbl",
),
(
"SELECT format('%s', f1) FROM text_tbl",
"SELECT CONCAT(f1, '') FROM text_tbl",
),
(
"SELECT format('literal %% %s', f1) FROM text_tbl",
"SELECT CONCAT('literal % ', f1) FROM text_tbl",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_advanced_format_specifiers_fail_for_fabric() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile(
"SELECT format('%I', table_name) FROM metadata",
DialectType::Fabric,
)
.expect_err("PostgreSQL identifier quoting format should not map to Fabric FORMAT");
assert!(
err.to_string().contains("PostgreSQL format()"),
"unexpected error: {err}"
);
}
#[test]
fn postgres_uuid_generators_map_to_fabric_newid() {
let cases = [
("SELECT gen_random_uuid()", "SELECT NEWID()"),
("SELECT uuid_generate_v4()", "SELECT NEWID()"),
("SELECT uuidv4()", "SELECT NEWID()"),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_unsupported_to_timestamp_format_fails_for_fabric_in_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT to_timestamp('2020-01', 'YYYY-MM') AS c FROM t",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject unsupported Fabric timestamp parse formats");
assert!(
err.to_string().contains("no CONVERT style mapping"),
"unexpected error: {err}"
);
}
#[test]
fn postgres_unsupported_extract_fields_fail_for_fabric_in_strict_mode() {
let cases = [
"SELECT EXTRACT(millennium FROM ts) AS c FROM t",
"SELECT date_part('millennium'::text, ts) AS c FROM t",
];
for sql in cases {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.expect_err("strict mode should reject unsupported Fabric dateparts");
assert!(
err.to_string().contains("MILLENNIUM"),
"unexpected error for {sql}: {err}"
);
}
}
#[test]
fn postgres_unsupported_date_trunc_fields_fail_for_fabric_in_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT date_trunc('millennium', ts) AS c FROM t",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject unsupported Fabric DATETRUNC dateparts");
assert!(
err.to_string().contains("MILLENNIUM"),
"unexpected error: {err}"
);
}
// ---------------------------------------------------------------------------
// PostgreSQL LATERAL joins -> Fabric APPLY
// ---------------------------------------------------------------------------
#[test]
fn postgres_lateral_joins_map_to_fabric_apply() {
let lateral_subquery = "(SELECT v FROM lineitem WHERE l_orderkey = o.id)";
let cross_apply =
"SELECT o.id, t.v FROM orders AS o CROSS APPLY (SELECT v FROM lineitem WHERE l_orderkey = o.id) AS t";
let outer_apply =
"SELECT o.id, t.v FROM orders AS o OUTER APPLY (SELECT v FROM lineitem WHERE l_orderkey = o.id) AS t";
let cases = [
(
format!("SELECT o.id, t.v FROM orders o CROSS JOIN LATERAL {lateral_subquery} t"),
cross_apply,
),
(
format!("SELECT o.id, t.v FROM orders o JOIN LATERAL {lateral_subquery} t ON true"),
cross_apply,
),
(
format!(
"SELECT o.id, t.v FROM orders o INNER JOIN LATERAL {lateral_subquery} t ON true"
),
cross_apply,
),
(
format!(
"SELECT o.id, t.v FROM orders o LEFT JOIN LATERAL {lateral_subquery} t ON true"
),
outer_apply,
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(&sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_parenthesized_lateral_joins_map_to_fabric_apply() {
let cases = [
(
"SELECT * FROM (orders o JOIN LATERAL (SELECT 1 AS x) a ON true)",
"SELECT * FROM (orders AS o CROSS APPLY (SELECT 1 AS x) AS a)",
),
(
"SELECT * FROM (orders o LEFT JOIN LATERAL (SELECT 1 AS x) a ON true)",
"SELECT * FROM (orders AS o OUTER APPLY (SELECT 1 AS x) AS a)",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_comma_lateral_maps_to_fabric_cross_apply() {
assert_eq!(
pg_to_fabric("SELECT o.id, t.v FROM orders o, LATERAL (SELECT v FROM lineitem WHERE l_orderkey = o.id) t"),
"SELECT o.id, t.v FROM orders AS o CROSS APPLY (SELECT v FROM lineitem WHERE l_orderkey = o.id) AS t"
);
}
#[test]
fn postgres_lateral_join_on_predicate_pushes_into_fabric_apply_rhs() {
let cases = [
(
"SELECT o.id, t.v FROM orders o JOIN LATERAL (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) t ON t.v > 0",
"SELECT o.id, t.v FROM orders AS o CROSS APPLY (SELECT * FROM (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) AS _polyglot_lateral_source WHERE _polyglot_lateral_source.v > 0) AS t",
),
(
"SELECT o.id, t.v FROM orders o LEFT JOIN LATERAL (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) t ON t.v > 0",
"SELECT o.id, t.v FROM orders AS o OUTER APPLY (SELECT * FROM (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) AS _polyglot_lateral_source WHERE _polyglot_lateral_source.v > 0) AS t",
),
(
"SELECT o.id, t.x FROM orders o JOIN LATERAL (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) t(x) ON t.x > 0",
"SELECT o.id, t.x FROM orders AS o CROSS APPLY (SELECT * FROM (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) AS _polyglot_lateral_source(x) WHERE _polyglot_lateral_source.x > 0) AS t(x)",
),
(
"SELECT o.id, t.v FROM orders o JOIN LATERAL (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) t ON o.id > 0",
"SELECT o.id, t.v FROM orders AS o CROSS APPLY (SELECT * FROM (SELECT v FROM lineitem WHERE lineitem.l_orderkey = o.id) AS _polyglot_lateral_source WHERE o.id > 0) AS t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_lateral_using_inside_joined_table_fails_fabric_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT * FROM (orders o JOIN LATERAL (SELECT 1 AS id) a USING (id))",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject residual LATERAL");
assert!(
err.to_string().contains("LATERAL joins and subqueries"),
"unexpected error: {err}"
);
}
#[test]
fn fabric_preserves_native_apply_generation() {
let cases = [
(
"SELECT x.a, x.b, t.v FROM x CROSS APPLY (SELECT v FROM t) t",
"SELECT x.a, x.b, t.v FROM x CROSS APPLY (SELECT v AS v FROM t) AS t",
),
(
"SELECT x.a, x.b, t.v FROM x OUTER APPLY (SELECT v FROM t) t",
"SELECT x.a, x.b, t.v FROM x OUTER APPLY (SELECT v AS v FROM t) AS t",
),
];
for (sql, expected) in cases {
assert_eq!(tsql_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_framed_named_window_inlines_frame_stripped_copy_for_fabric_ranking_function() {
let out = pg_to_fabric(
"SELECT row_number() OVER w AS rn, avg(o_totalprice) OVER w AS av \
FROM orders \
WINDOW w AS (PARTITION BY o_custkey ORDER BY o_orderdate NULLS FIRST \
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)",
);
assert_eq!(
out,
"SELECT ROW_NUMBER() OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS rn, AVG(o_totalprice) OVER w AS av FROM orders WINDOW w AS (PARTITION BY o_custkey ORDER BY o_orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)"
);
}
#[test]
fn postgres_inline_window_frame_is_stripped_for_fabric_ranking_function() {
let out = pg_to_fabric(
"SELECT row_number() OVER (PARTITION BY o_custkey ORDER BY o_orderdate NULLS FIRST \
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rn \
FROM orders",
);
assert_eq!(
out,
"SELECT ROW_NUMBER() OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS rn FROM orders"
);
}
#[test]
fn postgres_framed_named_window_inlines_for_all_fabric_frame_incompatible_functions() {
let out = pg_to_fabric(
"SELECT rank() OVER w AS r, dense_rank() OVER w AS dr, ntile(4) OVER w AS nt, \
lead(o_totalprice) OVER w AS lead_price, lag(o_totalprice) OVER w AS lag_price, \
percent_rank() OVER w AS pr, cume_dist() OVER w AS cd, \
avg(o_totalprice) OVER w AS av \
FROM orders \
WINDOW w AS (PARTITION BY o_custkey ORDER BY o_orderdate NULLS FIRST \
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)",
);
assert_eq!(
out,
"SELECT RANK() OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS r, DENSE_RANK() OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS dr, NTILE(4) OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS nt, LEAD(o_totalprice) OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS lead_price, LAG(o_totalprice) OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS lag_price, PERCENT_RANK() OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS pr, CUME_DIST() OVER (PARTITION BY o_custkey ORDER BY o_orderdate) AS cd, AVG(o_totalprice) OVER w AS av FROM orders WINDOW w AS (PARTITION BY o_custkey ORDER BY o_orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)"
);
}
// ---------------------------------------------------------------------------
// PostgreSQL NULLS FIRST/LAST -> Fabric CASE sort key
// ---------------------------------------------------------------------------
#[test]
fn postgres_null_ordering_rewrites_for_fabric() {
let cases = [
(
"SELECT id FROM t ORDER BY id ASC",
"SELECT id FROM t ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END, id ASC",
),
(
"SELECT id FROM t ORDER BY id ASC NULLS LAST",
"SELECT id FROM t ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END, id ASC",
),
(
"SELECT id FROM t ORDER BY id ASC NULLS FIRST",
"SELECT id FROM t ORDER BY id ASC",
),
(
"SELECT id FROM t ORDER BY id DESC",
"SELECT id FROM t ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END DESC, id DESC",
),
(
"SELECT id FROM t ORDER BY id DESC NULLS FIRST",
"SELECT id FROM t ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END DESC, id DESC",
),
(
"SELECT id FROM t ORDER BY id DESC NULLS LAST",
"SELECT id FROM t ORDER BY id DESC",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_random_ordering_does_not_add_null_sort_key_for_fabric() {
let out = pg_to_fabric(r#"SELECT * FROM "test_table" ORDER BY RANDOM() LIMIT 5"#);
assert_eq!(out, "SELECT TOP 5 * FROM [test_table] ORDER BY RAND()");
}
#[test]
fn postgres_set_operation_modifiers_wrap_for_fabric() {
let cases = [
(
"SELECT c_custkey FROM customer EXCEPT SELECT o_custkey FROM orders ORDER BY c_custkey LIMIT 100",
"SELECT TOP 100 * FROM (SELECT c_custkey FROM customer EXCEPT SELECT o_custkey FROM orders) AS _l_0 ORDER BY CASE WHEN c_custkey IS NULL THEN 1 ELSE 0 END, c_custkey",
),
(
"SELECT c_custkey FROM customer UNION ALL SELECT o_custkey FROM orders ORDER BY c_custkey LIMIT 100",
"SELECT TOP 100 * FROM (SELECT c_custkey FROM customer UNION ALL SELECT o_custkey FROM orders) AS _l_0 ORDER BY CASE WHEN c_custkey IS NULL THEN 1 ELSE 0 END, c_custkey",
),
(
"SELECT c_custkey FROM customer INTERSECT SELECT o_custkey FROM orders ORDER BY c_custkey LIMIT 100",
"SELECT TOP 100 * FROM (SELECT c_custkey FROM customer INTERSECT SELECT o_custkey FROM orders) AS _l_0 ORDER BY CASE WHEN c_custkey IS NULL THEN 1 ELSE 0 END, c_custkey",
),
(
"SELECT c_custkey FROM customer EXCEPT SELECT o_custkey FROM orders ORDER BY c_custkey LIMIT 100 OFFSET 2",
"SELECT * FROM (SELECT c_custkey FROM customer EXCEPT SELECT o_custkey FROM orders) AS _l_0 ORDER BY CASE WHEN c_custkey IS NULL THEN 1 ELSE 0 END, c_custkey OFFSET 2 ROWS FETCH NEXT 100 ROWS ONLY",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
// ---------------------------------------------------------------------------
// PostgreSQL WITH RECURSIVE -> Fabric WITH
// ---------------------------------------------------------------------------
#[test]
fn postgres_recursive_cte_omits_recursive_keyword_for_fabric() {
let out = pg_to_fabric(
"WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM r WHERE n < 10) SELECT n FROM r",
);
assert_eq!(
out,
"WITH r(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM r WHERE n < 10) SELECT n FROM r"
);
}
// ---------------------------------------------------------------------------
// PostgreSQL MOD function -> Fabric modulo operator
// ---------------------------------------------------------------------------
#[test]
fn postgres_mod_function_maps_to_fabric_percent_operator() {
let cases = [
("SELECT mod(a, 7) AS m FROM t", "SELECT a % 7 AS m FROM t"),
(
"SELECT mod(a + 1, b * 2) AS m FROM t",
"SELECT (a + 1) % (b * 2) AS m FROM t",
),
("SELECT a % 7 AS m FROM t", "SELECT a % 7 AS m FROM t"),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
// ---------------------------------------------------------------------------
// PostgreSQL string position functions -> Fabric CHARINDEX
// ---------------------------------------------------------------------------
#[test]
fn postgres_string_position_maps_to_fabric_charindex() {
let cases = [
(
"SELECT position('green' IN c) AS p FROM t",
"SELECT CHARINDEX('green', c) AS p FROM t",
),
(
"SELECT position(x IN y) AS p FROM t",
"SELECT CHARINDEX(x, y) AS p FROM t",
),
(
"SELECT strpos(c, 'green') AS p FROM t",
"SELECT CHARINDEX('green', c) AS p FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
// ---------------------------------------------------------------------------
// PostgreSQL row-value IN subquery -> Fabric correlated EXISTS
// ---------------------------------------------------------------------------
#[test]
fn postgres_row_value_in_subquery_maps_to_fabric_exists() {
let out = pg_to_fabric("SELECT a FROM t WHERE (a, b) IN (SELECT x, y FROM u WHERE q < 10)");
assert_eq!(
out,
"SELECT a FROM t WHERE EXISTS(SELECT 1 FROM u WHERE u.x = t.a AND u.y = t.b AND q < 10)"
);
}
#[test]
fn postgres_row_value_not_in_subquery_is_not_rewritten_for_fabric() {
let out = pg_to_fabric("SELECT a FROM t WHERE (a, b) NOT IN (SELECT x, y FROM u WHERE q < 10)");
assert_eq!(
out,
"SELECT a FROM t WHERE NOT EXISTS(SELECT 1 FROM u WHERE (u.x = t.a OR u.x IS NULL OR t.a IS NULL) AND (u.y = t.b OR u.y IS NULL OR t.b IS NULL) AND q < 10)"
);
}
#[test]
fn postgres_row_value_in_subquery_arity_mismatch_is_not_rewritten_for_fabric() {
let out = pg_to_fabric("SELECT a FROM t WHERE (a, b) IN (SELECT x FROM u)");
assert_eq!(out, "SELECT a FROM t WHERE (a, b) IN (SELECT x FROM u)");
}
#[test]
fn postgres_row_value_in_subquery_rewrites_cast_projections_for_fabric() {
let out = pg_to_fabric_strict(
"SELECT f1 FROM t WHERE (f1, f2) IN (SELECT CAST(a AS BIGINT), b FROM t2 WHERE q < 10)",
);
assert_eq!(
out,
"SELECT f1 FROM t WHERE EXISTS(SELECT 1 FROM t2 WHERE CAST(t2.a AS BIGINT) = t.f1 AND t2.b = t.f2 AND q < 10)"
);
}
#[test]
fn postgres_row_value_in_subquery_rewrites_wrapped_subqueries_for_fabric() {
let out = pg_to_fabric_strict("SELECT f1 FROM t WHERE (f1, f2) IN ((SELECT a, b FROM t2))");
assert_eq!(
out,
"SELECT f1 FROM t WHERE EXISTS(SELECT 1 FROM t2 WHERE t2.a = t.f1 AND t2.b = t.f2)"
);
}
#[test]
fn postgres_row_value_in_subquery_preserves_qualified_join_projections_for_fabric() {
let out = pg_to_fabric_strict(
"SELECT f1 FROM t WHERE (f1, f2) IN (SELECT u.a, v.b FROM u JOIN v ON u.id = v.id)",
);
assert_eq!(
out,
"SELECT f1 FROM t WHERE EXISTS(SELECT 1 FROM u JOIN v ON u.id = v.id WHERE u.a = t.f1 AND v.b = t.f2)"
);
}
#[test]
fn postgres_row_value_equality_subquery_rewrites_in_predicate_contexts_for_fabric() {
let out = pg_to_fabric_strict("SELECT f1 FROM t WHERE ROW(f1, f2) = (SELECT a, b FROM t2)");
assert_eq!(
out,
"SELECT f1 FROM t WHERE EXISTS(SELECT 1 FROM t2 WHERE t2.a = t.f1 AND t2.b = t.f2)"
);
}
#[test]
fn postgres_row_value_equality_subquery_rewrites_inside_scalar_boolean_for_fabric() {
let out = pg_to_fabric_strict("SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM t");
assert_eq!(
out,
"SELECT CAST(CASE WHEN EXISTS(SELECT 1 WHERE f1 = 1 AND f2 = 2) THEN 1 ELSE 0 END AS BIT) AS eq FROM t"
);
}
#[test]
fn postgres_row_value_subquery_arity_mismatch_fails_fabric_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT f1 FROM t WHERE (f1, f2) IN (SELECT a FROM t2)",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject unrewriteable row-value comparisons");
assert!(
err.to_string().contains("row-value subquery comparisons"),
"unexpected error: {err}"
);
}
// ---------------------------------------------------------------------------
// PostgreSQL unqualified NUMERIC/DECIMAL casts -> Fabric scaled DECIMAL
// ---------------------------------------------------------------------------
#[test]
fn postgres_unqualified_numeric_cast_uses_scaled_fabric_decimal() {
let out = pg_to_fabric("SELECT round(avg(p)::numeric, 2) AS r FROM t GROUP BY g");
assert_eq!(
out,
"SELECT ROUND(CAST(AVG(p) AS DECIMAL(38, 10)), 2) AS r FROM t GROUP BY g"
);
}
#[test]
fn postgres_single_arg_round_uses_fabric_length_argument() {
let cases = [
(
"SELECT f1, round(f1) AS round_f1 FROM float8_tbl",
"SELECT f1, ROUND(f1, 0) AS round_f1 FROM float8_tbl",
),
(
"SELECT round(f1::numeric, 0) AS r FROM float8_tbl",
"SELECT ROUND(CAST(f1 AS DECIMAL(38, 10)), 0) AS r FROM float8_tbl",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_plain_numeric_and_decimal_casts_use_scaled_fabric_decimal() {
let cases = [
(
"SELECT p::numeric AS n FROM t",
"SELECT CAST(p AS DECIMAL(38, 10)) AS n FROM t",
),
(
"SELECT p::decimal AS n FROM t",
"SELECT CAST(p AS DECIMAL(38, 10)) AS n FROM t",
),
(
"SELECT CAST(p AS numeric) AS n FROM t",
"SELECT CAST(p AS DECIMAL(38, 10)) AS n FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_explicit_numeric_cast_preserves_fabric_precision_and_scale() {
let out = pg_to_fabric("SELECT p::numeric(38, 10) AS n FROM t");
assert_eq!(out, "SELECT CAST(p AS DECIMAL(38, 10)) AS n FROM t");
}
#[test]
fn postgres_json_and_jsonb_casts_map_to_fabric_varchar_max() {
let sql = "SELECT x::jsonb, y::json FROM t";
let expected = "SELECT CAST(x AS VARCHAR(MAX)), CAST(y AS VARCHAR(MAX)) FROM t";
assert_eq!(pg_to_fabric(sql), expected);
assert_eq!(pg_to_fabric_strict(sql), expected);
}
#[test]
fn postgres_approximate_numeric_casts_map_to_fabric_float_and_real() {
let sql = "SELECT x::float8, y::double precision, z::real, w::float4 FROM t";
let expected =
"SELECT CAST(x AS FLOAT), CAST(y AS FLOAT), CAST(z AS REAL), CAST(w AS REAL) FROM t";
assert_eq!(pg_to_fabric(sql), expected);
assert_eq!(pg_to_fabric_strict(sql), expected);
}
#[test]
fn postgres_bitwise_xor_maps_to_fabric_caret_operator() {
let sql = "SELECT a # b FROM t";
let expected = "SELECT a ^ b FROM t";
assert_eq!(pg_to_fabric(sql), expected);
assert_eq!(pg_to_fabric_strict(sql), expected);
}
#[test]
fn postgres_type_function_casts_map_to_fabric_casts() {
let cases = [
(
"SELECT s, sum(v) + numeric(sum(g)) AS mixed FROM t GROUP BY s",
"SELECT s, SUM(v) + CAST(SUM(g) AS DECIMAL(38, 10)) AS mixed FROM t GROUP BY s",
),
(
"SELECT decimal(5) AS d, numeric(5) AS n",
"SELECT CAST(5 AS DECIMAL(38, 10)) AS d, CAST(5 AS DECIMAL(38, 10)) AS n",
),
(
"SELECT int4(1.5) AS a, float8(4) AS b",
"SELECT CAST(1.5 AS INT) AS a, CAST(4 AS FLOAT) AS b",
),
(
"SELECT int8(1) AS a, float4(4) AS b, bool(1) AS c, text(1) AS d",
"SELECT CAST(1 AS BIGINT) AS a, CAST(4 AS REAL) AS b, CAST(1 AS BIT) AS c, CAST(1 AS VARCHAR(MAX)) AS d",
),
(
"SELECT smallint(1) AS s, integer(1) AS i, varchar(1) AS v, uuid(id) AS u FROM t",
"SELECT CAST(1 AS SMALLINT) AS s, CAST(1 AS INT) AS i, CAST(1 AS VARCHAR) AS v, CAST(id AS UNIQUEIDENTIFIER) AS u FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_type_function_cast_with_unsafe_arity_fails_fabric_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT numeric(1, 2) AS n",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject residual PostgreSQL type-name function casts");
assert!(
err.to_string()
.contains("PostgreSQL type-name function casts"),
"unexpected error: {err}"
);
}
#[test]
fn postgres_qualified_or_quoted_type_named_functions_are_not_casts_for_fabric() {
let out = pg_to_fabric(r#"SELECT public.numeric(1) AS q, "numeric"(1) AS quoted"#);
assert_eq!(out, "SELECT public.numeric(1) AS q, NUMERIC(1) AS quoted");
}
// ---------------------------------------------------------------------------
// PostgreSQL statistical aggregates -> Fabric T-SQL names
// ---------------------------------------------------------------------------
#[test]
fn postgres_statistical_aggregates_map_to_fabric_names() {
let cases = [
(
"SELECT stddev_samp(x) AS s FROM t",
"SELECT STDEV(x) AS s FROM t",
),
(
"SELECT stddev_pop(x) AS s FROM t",
"SELECT STDEVP(x) AS s FROM t",
),
(
"SELECT var_samp(x) AS s FROM t",
"SELECT VAR(x) AS s FROM t",
),
(
"SELECT var_pop(x) AS s FROM t",
"SELECT VARP(x) AS s FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
// ---------------------------------------------------------------------------
// PostgreSQL boolean aggregates -> Fabric T-SQL CASE aggregates
// ---------------------------------------------------------------------------
#[test]
fn postgres_boolean_aggregates_map_to_fabric_case_aggregates() {
let cases = [
(
"SELECT bool_and(x > 0) AS s FROM t",
"SELECT CAST(MIN(CASE WHEN x > 0 THEN 1 WHEN NOT x > 0 THEN 0 ELSE NULL END) AS BIT) AS s FROM t",
),
(
"SELECT bool_or(x > 0) AS s FROM t",
"SELECT CAST(MAX(CASE WHEN x > 0 THEN 1 WHEN NOT x > 0 THEN 0 ELSE NULL END) AS BIT) AS s FROM t",
),
(
"SELECT every(x > 0) AS s FROM t",
"SELECT CAST(MIN(CASE WHEN x > 0 THEN 1 WHEN NOT x > 0 THEN 0 ELSE NULL END) AS BIT) AS s FROM t",
),
(
"SELECT bool_and(x) AS s FROM t",
"SELECT CAST(MIN(CASE WHEN x <> 0 THEN 1 WHEN NOT x <> 0 THEN 0 ELSE NULL END) AS BIT) AS s FROM t",
),
(
"SELECT bool_or(x > 0) FILTER (WHERE y > 0) AS s FROM t",
"SELECT CAST(MAX(CASE WHEN y > 0 AND x > 0 THEN 1 WHEN y > 0 AND NOT x > 0 THEN 0 ELSE NULL END) AS BIT) AS s FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_unsupported_aggregates_fail_for_fabric_in_strict_mode() {
let cases = [
("SELECT bit_and(i4) FROM t", "BIT_AND"),
("SELECT bit_or(i4) FROM t", "BIT_OR"),
("SELECT bit_xor(i4) FROM t", "BIT_XOR"),
("SELECT regr_avgx(y, x) FROM t", "REGR_AVGX"),
("SELECT regr_avgy(y, x) FROM t", "REGR_AVGY"),
("SELECT regr_count(y, x) FROM t", "REGR_COUNT"),
("SELECT regr_intercept(y, x) FROM t", "REGR_INTERCEPT"),
("SELECT regr_r2(y, x) FROM t", "REGR_R2"),
("SELECT regr_slope(y, x) FROM t", "REGR_SLOPE"),
("SELECT regr_sxx(y, x) FROM t", "REGR_SXX"),
("SELECT regr_sxy(y, x) FROM t", "REGR_SXY"),
("SELECT regr_syy(y, x) FROM t", "REGR_SYY"),
("SELECT covar_pop(y, x) FROM t", "COVAR_POP"),
("SELECT covar_samp(y, x) FROM t", "COVAR_SAMP"),
("SELECT corr(y, x) FROM t", "CORR"),
];
for (sql, expected) in cases {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
.expect_err("strict mode should reject unsupported PostgreSQL aggregate");
assert!(
err.to_string().contains(expected),
"expected {expected} in error for {sql}, got: {err}"
);
}
}
#[test]
fn postgres_scalar_boolean_values_map_to_fabric_case_values() {
let cases = [
(
"SELECT (l_quantity > 30) AS b FROM tpch.lineitem",
"SELECT CAST(CASE WHEN (l_quantity > 30) THEN 1 ELSE 0 END AS BIT) AS b FROM tpch.lineitem",
),
(
"SELECT COUNT(*) AS c FROM tpch.lineitem GROUP BY (l_quantity > 30)",
"SELECT COUNT_BIG(*) AS c FROM tpch.lineitem GROUP BY CAST(CASE WHEN (l_quantity > 30) THEN 1 ELSE 0 END AS BIT)",
),
(
"SELECT (l_quantity > 30) AS b, COUNT(*) AS c FROM tpch.lineitem WHERE l_orderkey < 1000 GROUP BY (l_quantity > 30) ORDER BY b",
"SELECT CAST(CASE WHEN (l_quantity > 30) THEN 1 ELSE 0 END AS BIT) AS b, COUNT_BIG(*) AS c FROM tpch.lineitem WHERE l_orderkey < 1000 GROUP BY CAST(CASE WHEN (l_quantity > 30) THEN 1 ELSE 0 END AS BIT) ORDER BY CASE WHEN b IS NULL THEN 1 ELSE 0 END, b",
),
(
"SELECT l_quantity FROM tpch.lineitem ORDER BY (l_quantity > 30)",
"SELECT l_quantity FROM tpch.lineitem ORDER BY CASE WHEN CAST(CASE WHEN (l_quantity > 30) THEN 1 ELSE 0 END AS BIT) IS NULL THEN 1 ELSE 0 END, CAST(CASE WHEN (l_quantity > 30) THEN 1 ELSE 0 END AS BIT)",
),
(
"SELECT COUNT(*) OVER (PARTITION BY (l_quantity > 30)) AS c FROM tpch.lineitem",
"SELECT COUNT_BIG(*) OVER (PARTITION BY CAST(CASE WHEN (l_quantity > 30) THEN 1 ELSE 0 END AS BIT)) AS c FROM tpch.lineitem",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_absolute_value_operator_maps_to_fabric_abs_function() {
let cases = [
(
"SELECT f1, @ f1 AS abs_f1 FROM float4_tbl",
"SELECT f1, ABS(f1) AS abs_f1 FROM float4_tbl",
),
("SELECT @ -5.0 AS x", "SELECT ABS(-5.0) AS x"),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_top_level_values_map_to_fabric_derived_table() {
assert_eq!(
pg_to_fabric("VALUES (1, 2), (3, 8)"),
"SELECT * FROM (VALUES (1, 2), (3, 8)) AS _v(column1, column2)"
);
}
#[test]
fn postgres_values_set_operation_arms_map_to_fabric_derived_tables() {
let cases = [
(
"VALUES (1, 2), (3, 8) UNION ALL SELECT 4, 57",
"SELECT * FROM (VALUES (1, 2), (3, 8)) AS _v(column1, column2) UNION ALL SELECT 4, 57",
),
(
"SELECT 4, 57 UNION ALL VALUES (1, 2), (3, 8)",
"SELECT 4, 57 UNION ALL SELECT * FROM (VALUES (1, 2), (3, 8)) AS _v(column1, column2)",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_from_values_for_fabric_stays_derived_table() {
assert_eq!(
pg_to_fabric("SELECT * FROM (VALUES (1, 2), (3, 8)) AS v(a, b)"),
"SELECT * FROM (VALUES (1, 2), (3, 8)) AS v(a, b)"
);
}
#[test]
fn postgres_predicate_boolean_contexts_stay_predicates_for_fabric() {
let out = pg_to_fabric(
"SELECT COUNT(*) AS c FROM tpch.lineitem WHERE l_quantity > 30 HAVING COUNT(*) > 0",
);
assert_eq!(
out,
"SELECT COUNT_BIG(*) AS c FROM tpch.lineitem WHERE l_quantity > 30 HAVING COUNT_BIG(*) > 0"
);
}
// ---------------------------------------------------------------------------
// PostgreSQL aggregate FILTER clauses -> Fabric conditional aggregates
// ---------------------------------------------------------------------------
#[test]
fn postgres_aggregate_filters_map_to_fabric_conditional_aggregates() {
let cases = [
(
"SELECT count(*) FILTER (WHERE x > 5) AS c FROM t",
"SELECT COUNT_BIG(CASE WHEN x > 5 THEN 1 END) AS c FROM t",
),
(
"SELECT count(value) FILTER (WHERE x > 5) AS c FROM t",
"SELECT COUNT_BIG(CASE WHEN x > 5 THEN value END) AS c FROM t",
),
(
"SELECT count(DISTINCT value) FILTER (WHERE x > 5) AS c FROM t",
"SELECT COUNT_BIG(DISTINCT CASE WHEN x > 5 THEN value END) AS c FROM t",
),
(
"SELECT sum(v) FILTER (WHERE x > 5) AS s FROM t",
"SELECT SUM(CASE WHEN x > 5 THEN v END) AS s FROM t",
),
(
"SELECT avg(v) FILTER (WHERE x > 5) AS a FROM t",
"SELECT AVG(CASE WHEN x > 5 THEN v END) AS a FROM t",
),
(
"SELECT count(*) FILTER (WHERE flag = 'R') OVER (PARTITION BY g) AS c FROM t",
"SELECT COUNT_BIG(CASE WHEN flag = 'R' THEN 1 END) OVER (PARTITION BY g) AS c FROM t",
),
(
"SELECT sum(v) FILTER (WHERE x > 5) OVER (PARTITION BY g) AS s FROM t",
"SELECT SUM(CASE WHEN x > 5 THEN v END) OVER (PARTITION BY g) AS s FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
// ---------------------------------------------------------------------------
// PostgreSQL STRING_AGG inline ORDER BY -> Fabric WITHIN GROUP
// ---------------------------------------------------------------------------
#[test]
fn postgres_string_agg_order_by_maps_to_fabric_within_group() {
let cases = [
(
"SELECT string_agg(name, ', ' ORDER BY name) AS s FROM t",
"SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 0 END, name) AS s FROM t",
),
(
"SELECT string_agg(name, ', ' ORDER BY name DESC) AS s FROM t",
"SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 0 END DESC, name DESC) AS s FROM t",
),
(
"SELECT string_agg(name, ', ') AS s FROM t",
"SELECT STRING_AGG(name, ', ') AS s FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_typed_string_agg_separator_maps_to_fabric_literal() {
let cases = [
(
"SELECT string_agg(s, ','::text ORDER BY g) FROM t",
"SELECT STRING_AGG(s, ',') WITHIN GROUP (ORDER BY CASE WHEN g IS NULL THEN 1 ELSE 0 END, g) FROM t",
),
(
"SELECT string_agg(s, ','::varchar) FROM t",
"SELECT STRING_AGG(s, ',') FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_grouped_ordered_set_percentiles_map_to_fabric_windows() {
let cases = [
(
"SELECT store_id, percentile_cont(0.5) WITHIN GROUP (ORDER BY pick_seconds) AS med FROM order_fulfillment_history GROUP BY store_id",
"SELECT DISTINCT store_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pick_seconds) OVER (PARTITION BY store_id) AS med FROM order_fulfillment_history",
),
(
"SELECT store_id, percentile_disc(0.9) WITHIN GROUP (ORDER BY pick_seconds) AS p90 FROM order_fulfillment_history GROUP BY store_id",
"SELECT DISTINCT store_id, PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY pick_seconds) OVER (PARTITION BY store_id) AS p90 FROM order_fulfillment_history",
),
(
"SELECT store_id, percentile_cont(0.5) WITHIN GROUP (ORDER BY pick_seconds) AS med, percentile_disc(0.9) WITHIN GROUP (ORDER BY pick_seconds DESC) AS p90 FROM order_fulfillment_history GROUP BY store_id",
"SELECT DISTINCT store_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pick_seconds) OVER (PARTITION BY store_id) AS med, PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY pick_seconds DESC) OVER (PARTITION BY store_id) AS p90 FROM order_fulfillment_history",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn postgres_mode_within_group_fails_fabric_strict_mode() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT store_id, mode() WITHIN GROUP (ORDER BY status) AS mode_status FROM order_fulfillment_history GROUP BY store_id",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict mode should reject MODE() ordered-set aggregates for Fabric");
assert!(
err.to_string().contains("MODE ordered-set aggregates"),
"unexpected error: {err}"
);
}
// ---------------------------------------------------------------------------
// PostgreSQL LIMIT/OFFSET -> Fabric TOP/OFFSET/FETCH
// ---------------------------------------------------------------------------
#[test]
fn limit_without_offset_uses_top() {
let out = pg_to_fabric("SELECT id FROM t ORDER BY id LIMIT 5");
assert_eq!(
out,
"SELECT TOP 5 id FROM t ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END, id"
);
}
#[test]
fn limit_with_offset_uses_offset_fetch() {
let out = pg_to_fabric("SELECT id FROM t ORDER BY id LIMIT 5 OFFSET 2");
assert_eq!(
out,
"SELECT id FROM t ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END, id OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY"
);
}
#[test]
fn fetch_first_with_ties_uses_top_with_ties_for_fabric() {
let out = pg_to_fabric_strict(
"SELECT store_id FROM orders ORDER BY promised_at FETCH FIRST 5 ROWS WITH TIES",
);
assert_eq!(
out,
"SELECT TOP (5) WITH TIES store_id FROM orders ORDER BY CASE WHEN promised_at IS NULL THEN 1 ELSE 0 END, promised_at"
);
}
#[test]
fn postgres_overlaps_predicate_rewrites_to_fabric_range_predicate() {
let out = pg_to_fabric_strict(
"SELECT 1 WHERE (DATE '2020-01-01', DATE '2020-02-01') OVERLAPS (DATE '2020-01-15', DATE '2020-03-01')",
);
assert!(
!out.contains("OVERLAPS"),
"Fabric output should not contain OVERLAPS: {out}"
);
assert!(
out.contains("CASE WHEN CAST('2020-01-01' AS DATE) <= CAST('2020-02-01' AS DATE)"),
"expected normalized left range start in output: {out}"
);
assert!(
out.contains(" AND "),
"expected conjunctive range predicate in output: {out}"
);
}
#[test]
fn offset_without_limit_keeps_rows_keyword() {
let out = pg_to_fabric("SELECT id FROM t ORDER BY id OFFSET 2");
assert_eq!(
out,
"SELECT id FROM t ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END, id OFFSET 2 ROWS"
);
}
#[test]
fn noop_limits_are_omitted_for_fabric() {
let cases = [
("SELECT f1 FROM t LIMIT NULL", "SELECT f1 FROM t"),
("SELECT f1 FROM t LIMIT ALL", "SELECT f1 FROM t"),
("SELECT f1 FROM t LIMIT (NULL)", "SELECT f1 FROM t"),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
#[test]
fn noop_limits_keep_offset_only_for_fabric() {
let cases = [
(
"SELECT f1 FROM t ORDER BY f1 LIMIT NULL OFFSET 2",
"SELECT f1 FROM t ORDER BY CASE WHEN f1 IS NULL THEN 1 ELSE 0 END, f1 OFFSET 2 ROWS",
),
(
"SELECT f1 FROM t ORDER BY f1 LIMIT ALL OFFSET 2",
"SELECT f1 FROM t ORDER BY CASE WHEN f1 IS NULL THEN 1 ELSE 0 END, f1 OFFSET 2 ROWS",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric_strict(sql), expected, "failed for {sql}");
}
}
// ---------------------------------------------------------------------------
// BPCHAR → CHAR normalisation
// ---------------------------------------------------------------------------
#[test]
fn bpchar_cast_no_length_maps_to_char() {
let out = pg_to_fabric("SELECT CAST(x AS BPCHAR)");
assert_eq!(out, "SELECT CAST(x AS CHAR)");
}
#[test]
fn bpchar_cast_with_length_maps_to_char() {
let out = pg_to_fabric("SELECT CAST(x AS BPCHAR(3))");
assert_eq!(out, "SELECT CAST(x AS CHAR(3))");
}
#[test]
fn bpchar_double_colon_no_length_maps_to_char() {
let out = pg_to_fabric("SELECT x::bpchar");
assert_eq!(out, "SELECT CAST(x AS CHAR)");
}
#[test]
fn bpchar_double_colon_with_length_maps_to_char() {
let out = pg_to_fabric("SELECT x::bpchar(3)");
assert_eq!(out, "SELECT CAST(x AS CHAR(3))");
}
#[test]
fn bpchar_ddl_column_no_length_maps_to_char() {
let out = pg_to_fabric("CREATE TABLE t (x BPCHAR)");
assert_eq!(out, "CREATE TABLE t (x CHAR)");
}
#[test]
fn bpchar_ddl_column_with_length_maps_to_char() {
let out = pg_to_fabric("CREATE TABLE t (x BPCHAR(3))");
assert_eq!(out, "CREATE TABLE t (x CHAR(3))");
}
// ---------------------------------------------------------------------------
// = ANY(ARRAY[...]) / = ANY((...)) → IN
// ---------------------------------------------------------------------------
#[test]
fn any_eq_array_brackets_rewrites_to_in() {
let out = pg_to_fabric("SELECT * FROM t WHERE col = ANY(ARRAY['a', 'b', 'c'])");
assert_eq!(out, "SELECT * FROM t WHERE col IN ('a', 'b', 'c')");
}
#[test]
fn any_eq_tuple_rewrites_to_in() {
let out = pg_to_fabric("SELECT * FROM t WHERE col = ANY(('a', 'b', 'c'))");
assert_eq!(out, "SELECT * FROM t WHERE col IN ('a', 'b', 'c')");
}
#[test]
fn any_eq_empty_array_rewrites_to_always_false() {
let out = pg_to_fabric("SELECT * FROM t WHERE col = ANY(ARRAY[])");
assert_eq!(out, "SELECT * FROM t WHERE 1 = 0");
}
#[test]
fn any_eq_outer_array_cast_rewrites_to_in_with_element_casts() {
let out = pg_to_fabric("SELECT * FROM t WHERE col = ANY((ARRAY['a', 'b'])::text[])");
assert_eq!(
out,
"SELECT * FROM t WHERE col IN (CAST('a' AS VARCHAR(MAX)), CAST('b' AS VARCHAR(MAX)))"
);
}
#[test]
fn any_eq_outer_array_cast_empty_rewrites_to_always_false() {
let out = pg_to_fabric("SELECT * FROM t WHERE col = ANY((ARRAY[])::text[])");
assert_eq!(out, "SELECT * FROM t WHERE 1 = 0");
}
#[test]
fn any_eq_ruleutils_outer_array_cast_rewrites_to_in() {
let out = pg_to_fabric(
"SELECT s FROM t WHERE ((s)::text = ANY ((ARRAY['a'::character varying, 'b'::character varying])::text[]))",
);
assert_eq!(
out,
"SELECT s FROM t WHERE (CAST((s) AS VARCHAR(MAX)) IN (CAST(CAST('a' AS VARCHAR) AS VARCHAR(MAX)), CAST(CAST('b' AS VARCHAR) AS VARCHAR(MAX))))"
);
}
#[test]
fn any_neq_array_not_rewritten() {
let out = pg_to_fabric("SELECT * FROM t WHERE col <> ANY(ARRAY['a', 'b'])");
assert_eq!(out, "SELECT * FROM t WHERE col <> ANY(ARRAY['a', 'b'])");
}
#[test]
fn strict_any_neq_array_rejects_non_subquery_rhs() {
let pg = Dialect::get(DialectType::PostgreSQL);
let err = pg
.transpile_with(
"SELECT * FROM t WHERE col <> ANY(ARRAY['a', 'b'])",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict Fabric transpilation should reject ANY over array literals");
assert!(err
.to_string()
.contains("ANY over non-subquery expressions"));
}
#[test]
fn strict_any_array_agg_in_case_wrapper_rejects_unsupported_array_semantics() {
let pg = Dialect::get(DialectType::PostgreSQL);
let err = pg
.transpile_with(
"SELECT (1 = any(array_agg(f1))) = any (SELECT false) FROM t",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict Fabric transpilation should reject ANY over ARRAY_AGG");
let message = err.to_string();
assert!(message.contains("ARRAY_AGG"), "unexpected error: {message}");
assert!(
message.contains("ANY over non-subquery expressions"),
"unexpected error: {message}"
);
}
#[test]
fn any_eq_subquery_not_rewritten() {
let out = pg_to_fabric("SELECT * FROM t WHERE col = ANY(SELECT id FROM s)");
assert_eq!(out, "SELECT * FROM t WHERE col = ANY (SELECT id FROM s)");
}
// ---------------------------------------------------------------------------
// PostgreSQL interval arithmetic -> Fabric DATEADD
// ---------------------------------------------------------------------------
#[test]
fn date_minus_date_rewrites_to_datediff() {
let cases = [
(
"SELECT '2020-01-01'::date - '2019-01-01'::date",
"SELECT DATEDIFF(DAY, CAST('2019-01-01' AS DATE), CAST('2020-01-01' AS DATE))",
),
(
"SELECT DATE '2020-01-01' - DATE '2019-01-01'",
"SELECT DATEDIFF(DAY, CAST('2019-01-01' AS DATE), CAST('2020-01-01' AS DATE))",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn date_integer_arithmetic_rewrites_to_day_dateadd() {
let cases = [
(
"SELECT DATE '2020-01-01' + 7",
"SELECT DATEADD(DAY, 7, CAST('2020-01-01' AS DATE))",
),
(
"SELECT 7 + DATE '2020-01-01'",
"SELECT DATEADD(DAY, 7, CAST('2020-01-01' AS DATE))",
),
(
"SELECT DATE '2020-01-01' - 7",
"SELECT DATEADD(DAY, -7, CAST('2020-01-01' AS DATE))",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn schema_less_column_integer_arithmetic_is_not_treated_as_date_arithmetic() {
let cases = [
(
"SELECT shipdate + 7 FROM lineitem",
"SELECT shipdate + 7 FROM lineitem",
),
(
"SELECT shipdate - 7 FROM lineitem",
"SELECT shipdate - 7 FROM lineitem",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn date_minus_interval_with_precision_rewrites_to_dateadd() {
let out =
pg_to_fabric("SELECT l_shipdate <= DATE '1998-12-01' - INTERVAL '3' DAY (3) FROM lineitem");
assert_eq!(
out,
"SELECT CAST(CASE WHEN l_shipdate <= DATEADD(DAY, -3, CAST('1998-12-01' AS DATE)) THEN 1 ELSE 0 END AS BIT) FROM lineitem"
);
}
#[test]
fn date_minus_interval_placeholder_rewrites_to_unquoted_dateadd_amount() {
let out = pg_to_fabric(
"SELECT l_shipdate <= DATE '1998-12-01' - INTERVAL ':1' DAY (3) FROM lineitem",
);
assert_eq!(
out,
"SELECT CAST(CASE WHEN l_shipdate <= DATEADD(DAY, -:1, CAST('1998-12-01' AS DATE)) THEN 1 ELSE 0 END AS BIT) FROM lineitem"
);
}
#[test]
fn date_minus_cast_interval_rewrites_to_dateadd() {
let out = pg_to_fabric("SELECT shipdate - CAST('3 day' AS INTERVAL) FROM lineitem");
assert_eq!(out, "SELECT DATEADD(DAY, -3, shipdate) FROM lineitem");
}
#[test]
fn date_plus_cast_interval_rewrites_to_dateadd() {
let out = pg_to_fabric("SELECT shipdate + CAST('3 day' AS INTERVAL) FROM lineitem");
assert_eq!(out, "SELECT DATEADD(DAY, 3, shipdate) FROM lineitem");
assert_eq!(
pg_to_fabric_strict("SELECT shipdate + CAST('3 day' AS INTERVAL) FROM lineitem"),
"SELECT DATEADD(DAY, 3, shipdate) FROM lineitem"
);
}
#[test]
fn scalar_interval_cast_maps_to_fabric_varchar_max_by_default() {
let out = pg_to_fabric("SELECT x::interval FROM t");
assert_eq!(out, "SELECT CAST(x AS VARCHAR(MAX)) FROM t");
}
#[test]
fn strict_scalar_interval_cast_rejects_for_fabric() {
let err = Dialect::get(DialectType::PostgreSQL)
.transpile_with(
"SELECT x::interval FROM t",
DialectType::Fabric,
TranspileOptions::strict(),
)
.expect_err("strict Fabric transpilation should reject scalar INTERVAL casts");
assert!(err.to_string().contains("INTERVAL casts"));
}
#[test]
fn date_plus_abbreviated_time_intervals_rewrite_to_dateadd() {
let cases = [
(
"SELECT ts + interval '30 min' AS c FROM t",
"SELECT DATEADD(MINUTE, 30, ts) AS c FROM t",
),
(
"SELECT now() + interval '30 min' AS c FROM t",
"SELECT DATEADD(MINUTE, 30, GETDATE()) AS c FROM t",
),
(
"SELECT now() - interval '30 min' AS c FROM t",
"SELECT DATEADD(MINUTE, -30, GETDATE()) AS c FROM t",
),
(
"SELECT ts + interval '5 sec' AS c FROM t",
"SELECT DATEADD(SECOND, 5, ts) AS c FROM t",
),
(
"SELECT now() + interval '3 hrs' AS c FROM t",
"SELECT DATEADD(HOUR, 3, GETDATE()) AS c FROM t",
),
(
"SELECT now() + interval '4 mins' AS c FROM t",
"SELECT DATEADD(MINUTE, 4, GETDATE()) AS c FROM t",
),
(
"SELECT now() + interval '5 secs' AS c FROM t",
"SELECT DATEADD(SECOND, 5, GETDATE()) AS c FROM t",
),
];
for (sql, expected) in cases {
assert_eq!(pg_to_fabric(sql), expected, "failed for {sql}");
}
}
#[test]
fn date_plus_cast_abbreviated_interval_rewrites_to_dateadd() {
let out = pg_to_fabric("SELECT shipdate + CAST('4 mins' AS INTERVAL) FROM lineitem");
assert_eq!(out, "SELECT DATEADD(MINUTE, 4, shipdate) FROM lineitem");
}
#[test]
fn date_plus_month_interval_rewrites_to_month_dateadd() {
let out = pg_to_fabric("SELECT DATE '1993-07-01' + INTERVAL '3 months'");
assert_eq!(out, "SELECT DATEADD(MONTH, 3, CAST('1993-07-01' AS DATE))");
}
#[test]
fn date_plus_mon_interval_rewrites_to_month_dateadd() {
let out = pg_to_fabric("SELECT DATE '1993-07-01' + INTERVAL '3 mon'");
assert_eq!(out, "SELECT DATEADD(MONTH, 3, CAST('1993-07-01' AS DATE))");
}
#[test]
fn date_plus_mons_interval_rewrites_to_month_dateadd() {
let out = pg_to_fabric("SELECT DATE '1993-07-01' + INTERVAL '3 mons'");
assert_eq!(out, "SELECT DATEADD(MONTH, 3, CAST('1993-07-01' AS DATE))");
}
#[test]
fn date_minus_mons_interval_rewrites_to_negative_month_dateadd() {
let out = pg_to_fabric("SELECT DATE '1993-07-01' - INTERVAL '3 mons'");
assert_eq!(out, "SELECT DATEADD(MONTH, -3, CAST('1993-07-01' AS DATE))");
}
#[test]
fn date_plus_cast_mons_interval_rewrites_to_month_dateadd() {
let out = pg_to_fabric("SELECT shipdate + CAST('3 mons' AS INTERVAL) FROM lineitem");
assert_eq!(out, "SELECT DATEADD(MONTH, 3, shipdate) FROM lineitem");
}
#[test]
fn date_minus_double_colon_mons_interval_rewrites_to_month_dateadd() {
let out = pg_to_fabric("SELECT shipdate - '3 mons'::INTERVAL FROM lineitem");
assert_eq!(out, "SELECT DATEADD(MONTH, -3, shipdate) FROM lineitem");
}
#[test]
fn date_minus_double_colon_interval_rewrites_to_dateadd() {
let out = pg_to_fabric("SELECT shipdate - '3 day'::INTERVAL FROM lineitem");
assert_eq!(out, "SELECT DATEADD(DAY, -3, shipdate) FROM lineitem");
}