Fabric/TSQL TO_DATE rewrite accepts PostgreSQL BC/year-zero values that target DATE cannot represent

[VaibhaveS]

Fabric/TSQL TO_DATE rewrite accepts PostgreSQL BC/year-zero values that target DATE cannot represent (sibling of #227 / #253)

Description

Transpiling PostgreSQL -> Fabric (and TSQL) with TranspileOptions::strict(), PostgreSQL to_date inputs whose normalized result is BC are rewritten to CONVERT(DATE, ..., 23) and returned as Ok(...).

PostgreSQL accepts these values and returns a BC date, while Fabric/TSQL DATE has no BC/year-zero domain and raises:

Msg 241: Conversion failed when converting date and/or time from character string.

This is a sibling of #227 / #253: strict mode returns target SQL even though the source value cannot be represented by the target type.

Root cause (pointer)

The PostgreSQL-to-TSQL/Fabric to_date lowering in src/dialects/tsql.rs maps supported format models to CONVERT(DATE, value, style) but does not validate the target date domain. PostgreSQL's to_date normalization can produce BC dates from a negative or zero year; TSQL DATE cannot represent them.

Affected constructs

• to_date with a negative year, for example '-44-02-01' and YYYY-MM-DD
• to_date with year zero, which PostgreSQL normalizes to 1 BC
• Fabric and TSQL targets

Repro

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

# Cargo.toml
[dependencies]
polyglot-sql = { version = "0.8", 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);

    for sql in [
        "SELECT to_date('-44-02-01', 'YYYY-MM-DD')",
        "SELECT to_date('0000-02-01', 'YYYY-MM-DD')",
    ] {
        let out = pg
            .transpile_with(sql, DialectType::Fabric, TranspileOptions::strict())
            .unwrap();
        println!("{out:?}");
    }
    // actual:
    // ["SELECT CONVERT(DATE, '-44-02-01', 23)"]
    // ["SELECT CONVERT(DATE, '0000-02-01', 23)"]
    // both raise Fabric Msg 241

    let rejected = pg.transpile_with(
        "SELECT age(d) FROM t",
        DialectType::Fabric,
        TranspileOptions::strict(),
    );
    println!("contrast: {rejected:?}");
    // actual: Err(Unsupported { feature: "PostgreSQL AGE", dialect: "fabric" })
}

PostgreSQL results:

0044-02-01 BC
0001-02-01 BC

Expected

This is a REJECTION fix for literal values that normalize outside the target DATE domain: strict mode should return Err(Unsupported) instead of emitting a conversion that must fail.

A general solution should validate target-domain representability for constant PostgreSQL date constructors before selecting a TSQL/Fabric native date rewrite.

Notes

• Reproduced on polyglot-sql 0.8.0; output above was captured directly from transpile_with.
• Same generated behavior for Fabric and TSQL.
• Ordinary AD dates and valid supported format models are not affected.

[tobilg]

This will be fixed in 0.8.1.

[tobilg]

Release 0.8.1 will be published in about two hours.

[VaibhaveS]

Downstream retest on polyglot-sql 0.8.1 at 6725b8a found a remaining canonicalization gap, so this should stay open. Strict transpilation rejects the raw source forms, but the forms produced by PostgreSQL pg_get_querydef still return target SQL. Both to_date('-44-02-01', 'YYYY-MM-DD') and to_date('0000-02-01', 'YYYY-MM-DD') then reach a live Fabric Warehouse and fail with Msg 241 (Conversion failed when converting date and/or time from character string) instead of producing a clean unsupported result.