PG->Fabric (T-SQL): STRING_AGG separator emitted as CAST(',' AS VARCHAR(MAX)) — must be a bare literal

[NivasSA]

PG->Fabric (T-SQL): STRING_AGG separator emitted as CAST(',' AS VARCHAR(MAX)) — must be a bare literal (sibling of #264)

Description

Transpiling PostgreSQL -> Fabric (and TSQL) with TranspileOptions::strict(), the separator
argument of string_agg is wrapped in a cast when it arrives typed:
string_agg(s, ','::text ORDER BY g) emits
STRING_AGG(s, CAST(',' AS VARCHAR(MAX))) WITHIN GROUP (ORDER BY …).

Fabric/T-SQL requires the STRING_AGG separator to be a string literal or variable, not an
expression, so it raises
Msg 8733: Separator parameter for STRING_AGG must be a string literal or variable.

The giveaway is the typed-vs-untyped asymmetry: an UNtyped separator string_agg(s, ',') emits a
bare STRING_AGG(s, ',') (valid), but the typed form string_agg(s, ','::text) — exactly what a
PostgreSQL front end / pg_get_querydef produces — triggers the invalid CAST wrap.

Root cause (pointer)

The general text-literal rendering (text -> CAST(<lit> AS VARCHAR(MAX))) is applied to the
STRING_AGG separator argument, where the target grammar does not permit an expression. The
separator needs to be rendered as a bare literal regardless of its source typing.

Affected constructs

• string_agg(expr, <typed text separator> [ORDER BY …]) -> Fabric / TSQL.
• More generally: function arguments that must be bare literals in the target grammar but get the
  generic text-cast wrap.

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: typed separator -> CAST-wrapped (invalid in Fabric STRING_AGG)
    let bad = pg.transpile_with("SELECT string_agg(s, ','::text ORDER BY g) FROM t",
        DialectType::Fabric, TranspileOptions::strict()).unwrap();
    println!("{bad:?}");
    // actual: ["SELECT STRING_AGG(s, CAST(',' AS VARCHAR(MAX))) WITHIN GROUP (ORDER BY …) FROM t"]
    //         -> Fabric Msg 8733: Separator parameter for STRING_AGG must be a string literal or variable

    // Contrast: untyped separator -> bare literal (valid)
    let ok = pg.transpile_with("SELECT string_agg(s, ',' ORDER BY g) FROM t",
        DialectType::Fabric, TranspileOptions::strict()).unwrap();
    println!("{ok:?}");
    // actual: ["SELECT STRING_AGG(s, ',') WITHIN GROUP (ORDER BY …) FROM t"]
}

Expected

The STRING_AGG separator should be emitted as a bare string literal (',') regardless of
whether the source separator is typed, so Fabric accepts it. (REWRITE — a valid target form
exists.)

Notes

• Reproduced on polyglot-sql 0.5.15; output captured directly from transpile_with.
• The WITHIN GROUP (ORDER BY …) translation is already correct; only the separator wrap is wrong.
• Same generator underlies Fabric and TSQL.

[tobilg]

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

[NivasSA]

Verified. Closing the issue. Thanks