PG->Fabric (T-SQL): bitwise + regression aggregates (bit_and/or/xor, regr_*, covar_pop/samp, corr) emitted verbatim -> Msg 195

[NivasSA]

PG->Fabric (T-SQL): bitwise + regression aggregates (bit_and/or/xor, regr_*, covar_pop/samp, corr) emitted verbatim -> Msg 195 (sibling of #264)

Description

Transpiling PostgreSQL -> Fabric (and TSQL) with TranspileOptions::strict(), the bitwise
aggregates bit_and / bit_or / bit_xor and the SQL2003 regression aggregates
regr_count/regr_r2/regr_slope/regr_intercept/regr_sxx/regr_syy/regr_sxy/
regr_avgx/regr_avgy, plus covar_pop/covar_samp/corr, are passed through verbatim:
bit_and(i4) -> BIT_AND(i4), regr_count(y,x) -> REGR_COUNT(y,x).

Fabric DW has none of these as built-in aggregates, so it raises
Msg 195: '<FN>' is not a recognized built-in function name.

Sibling of #264 (PG-only scalar functions
emitted verbatim): strict() returns Ok(...) with invalid SQL. The asymmetry is the giveaway
— in the same run, strict() correctly rewrites the boolean aggregates
bool_and/bool_or/every to a MIN/MAX(CASE … END) AS BIT form, but ships the bitwise /
regression aggregates untranslated.

Root cause (pointer)

The Fabric/TSQL aggregate mapping has rewrites for count/sum/avg/min/max, string_agg, and the
boolean aggregates, but no entry (and no strict rejection) for the bitwise / regression /
covariance / correlation aggregate families, so they fall through to verbatim emission.

Affected constructs

• bit_and / bit_or / bit_xor
• regr_count / regr_r2 / regr_slope / regr_intercept / regr_sxx / regr_syy /
  regr_sxy / regr_avgx / regr_avgy; covar_pop / covar_samp; corr
• More generally: PostgreSQL aggregates with no Fabric built-in and no faithful rewrite.

Repro

polyglot-sql 0.5.15, features transpile, dialect-postgresql, dialect-tsql, dialect-fabric:

[dependencies]
polyglot-sql = { version = "0.5.15", default-features = false, features = [
    "transpile", "dialect-postgresql", "dialect-tsql", "dialect-fabric",
] }

use polyglot_sql::{Dialect, DialectType, TranspileOptions};

fn main() {
    let pg = Dialect::get(DialectType::PostgreSQL);

    // --- Flagged: strict() returns Ok(...) with invalid SQL ---
    for q in ["SELECT bit_and(i4) FROM t", "SELECT regr_count(y, x) FROM t"] {
        println!("{:?}", pg.transpile_with(q, DialectType::Fabric, TranspileOptions::strict()).unwrap());
    }
    // actual: ["SELECT BIT_AND(i4) FROM t"]  -> Fabric Msg 195: 'BIT_AND' is not a recognized built-in function name
    //         ["SELECT REGR_COUNT(y, x) FROM t"] -> Msg 195: 'REGR_COUNT' ...

    // --- Contrast: the boolean aggregates ARE rewritten (same run) ---
    let ok = pg.transpile_with("SELECT bool_and(b) FROM t", DialectType::Fabric, TranspileOptions::strict()).unwrap();
    println!("{ok:?}");
    // actual: ["SELECT CAST(MIN(CASE WHEN b <> 0 THEN 1 WHEN NOT b <> 0 THEN 0 ELSE NULL END) AS BIT) FROM t"]
}

Expected

For aggregates Fabric cannot express and that have no faithful rewrite, strict() should return
Err (REJECTION) so callers can fall back / fail fast instead of emitting invalid SQL — exactly
as it already rewrites the boolean aggregates rather than shipping BOOL_AND.

Notes

• Reproduced on polyglot-sql 0.5.15; output captured directly from transpile_with.
• Same generator underlies Fabric and TSQL.
• Not affected: count/sum/avg/min/max, string_agg, bool_and/bool_or/every (rewritten).

[tobilg]

This is fixed in the upcoming version 0.5.16 that will be available in approx. 2h

[NivasSA]

Verified, Closing the Issue. Thanks :)