polyglot-sql 0.13.3

//! DuckDB `//` integer division: tokenizes to a single operator (so `7 / / 2`
//! and separated/commented slashes are rejected, matching DuckDB), parses to
//! `IntDiv` with correct precedence, and round-trips or lowers per target --
//! preserving fractional operands, exact integer arithmetic, and NULL on zero.
use polyglot_sql::{parse_one, transpile, transpile_with_by_name, DialectType, TranspileOptions};

#[test]
fn duckdb_int_div_round_trips() {
    let out = transpile(
        "SELECT 7 // 2 AS v",
        DialectType::DuckDB,
        DialectType::DuckDB,
    )
    .unwrap();
    assert_eq!(out, vec!["SELECT 7 // 2 AS v"]);
}

#[test]
fn duckdb_int_div_transpiles_like_other_integer_division() {
    // Preserve DuckDB's NULL-on-zero semantics as well as truncation.
    for target in [DialectType::PostgreSQL, DialectType::BigQuery] {
        let out = transpile("SELECT 7 // 2 AS v", DialectType::DuckDB, target).unwrap();
        assert_eq!(
            out,
            vec!["SELECT DIV(7, NULLIF(2, 0)) AS v"],
            "target {target:?}"
        );
    }
}

#[test]
fn duckdb_int_div_emulated_for_sqlite() {
    // SQLite has no DIV function, so // is emulated as truncating division.
    let out = transpile(
        "SELECT 7 // 2 AS v",
        DialectType::DuckDB,
        DialectType::SQLite,
    )
    .unwrap();
    assert_eq!(out, vec!["SELECT CAST(7 / NULLIF(2, 0) AS INTEGER) AS v"]);
}

#[test]
fn duckdb_int_div_binds_tighter_than_addition() {
    let out = transpile(
        "SELECT 1 + 7 // 2 AS v",
        DialectType::DuckDB,
        DialectType::DuckDB,
    )
    .unwrap();
    assert_eq!(out, vec!["SELECT 1 + 7 // 2 AS v"]);
}

#[test]
fn duckdb_int_div_precedence_is_unambiguous_in_the_ast() {
    // `1 + 7 // 2` round-trips the same either way `+`/`//` group (7), so it
    // doesn't actually prove precedence -- assert the AST shape directly,
    // and use `2 + 7 // 2` (5 if // binds tighter, 4 if + does) to show the
    // two groupings aren't interchangeable.
    use polyglot_sql::Expression;
    let expr = parse_one("SELECT 2 + 7 // 2 AS v", DialectType::DuckDB).unwrap();
    let Expression::Select(select) = &expr else {
        panic!("expected a SELECT");
    };
    let Expression::Alias(alias) = &select.expressions[0] else {
        panic!("expected an aliased projection");
    };
    let Expression::Add(add) = &alias.this else {
        panic!(
            "expected the top-level operator to be +, got {:?}",
            alias.this
        );
    };
    assert!(
        matches!(add.right, Expression::IntDiv(_)),
        "expected // to bind tighter than + (Add(2, IntDiv(7, 2))), got {add:?}"
    );
}

#[test]
fn duckdb_int_div_rejects_separated_slashes() {
    // DuckDB requires a contiguous `//`; `a / / b` and `a / /* c */ / b` are
    // invalid DuckDB SQL and must not be accepted as integer division.
    assert!(parse_one("SELECT 7 / / 2", DialectType::DuckDB).is_err());
    assert!(parse_one("SELECT 7 / /* c */ / 2", DialectType::DuckDB).is_err());
}

#[test]
fn duckdb_int_div_on_float_operand_lowers_to_float_division() {
    // DuckDB's // is ordinary float division when either operand is
    // non-integer (`7.0 // 2` is `3.5`, not `3`), so it lowers to `/` --
    // never to a truncating DIV/intDiv/CAST(... AS INTEGER) -- on every
    // target, including Vertica's own `//`. Negated, parenthesized, and
    // exponent-form literals count too.
    for target in [
        DialectType::PostgreSQL,
        DialectType::BigQuery,
        DialectType::SQLite,
        DialectType::Vertica,
        DialectType::ClickHouse,
        DialectType::MySQL,
        DialectType::DataFusion,
    ] {
        for sql in [
            "SELECT 7.0 // 2 AS v",
            "SELECT -7.0 // 2 AS v",
            "SELECT (7.0) // 2 AS v",
            "SELECT 7e0 // 2 AS v",
            "SELECT 7 // 2.0 AS v",
            "SELECT CAST(7 AS DOUBLE) // 2 AS v",
            "SELECT CAST(7 AS FLOAT) // 2 AS v",
            "SELECT CAST(7 AS DECIMAL(10, 1)) // 2 AS v",
            "SELECT (7.0 + 0) // 2 AS v",
            "SELECT (7 // 2.0) // 2 AS v",
        ] {
            if target == DialectType::Vertica && sql == "SELECT CAST(7 AS FLOAT) // 2 AS v" {
                // Vertica rejects narrowing numeric casts independently of //.
                let error = transpile(sql, DialectType::DuckDB, target).unwrap_err();
                assert!(error.to_string().contains("Narrow numeric CAST"));
                continue;
            }
            let out = transpile(sql, DialectType::DuckDB, target)
                .unwrap_or_else(|e| panic!("{sql} -> {target:?}: {e}"))
                .remove(0);
            assert!(out.contains(" / "), "{sql} -> {target:?}: got {out}");
            for truncating in ["DIV", "intDiv", "AS INTEGER", "//"] {
                assert!(
                    !out.contains(truncating),
                    "{sql} -> {target:?}: still truncates: {out}"
                );
            }
        }
    }
    // The int/int case is unaffected.
    assert_eq!(
        transpile(
            "SELECT 7 // 2 AS v",
            DialectType::DuckDB,
            DialectType::Vertica
        )
        .unwrap(),
        vec!["SELECT 7 // NULLIF(2, 0) AS v"]
    );
}

#[test]
fn duckdb_int_div_protects_zero_divisors() {
    for target in [
        DialectType::PostgreSQL,
        DialectType::BigQuery,
        DialectType::ClickHouse,
        DialectType::SQLite,
    ] {
        for divisor in [
            "0",
            "(0)",
            "-0",
            "CAST(0 AS INTEGER)",
            "(1 - 1)",
            "CAST(z AS INTEGER)",
        ] {
            let sql = format!("SELECT 7 // {divisor} AS v FROM t");
            let out = transpile(&sql, DialectType::DuckDB, target).unwrap();
            assert!(out[0].contains("NULLIF("), "{sql} -> {target:?}: {out:?}");
        }
    }
}

#[test]
fn duckdb_int_div_rejects_unresolved_types_in_every_mode() {
    for options in [TranspileOptions::default(), TranspileOptions::strict()] {
        for target in [
            "postgresql",
            "bigquery",
            "clickhouse",
            "sqlite",
            "datafusion",
        ] {
            for sql in [
                "SELECT x // 2 FROM t",
                "SELECT x // 2 FROM (SELECT 7.0 AS x) t",
                "SELECT 7.0 // x FROM t",
                "SELECT (x + 7.0) // 2 FROM t",
            ] {
                let error = transpile_with_by_name(sql, "duckdb", target, &options).unwrap_err();
                assert!(
                    error.to_string().contains("unresolved operand types"),
                    "{sql}: {error}"
                );
            }
        }
    }
    // Explicitly typed columns remain usable without requiring a schema.
    let out = transpile_with_by_name(
        "SELECT CAST(x AS DOUBLE) // CAST(y AS INTEGER) FROM t",
        "duckdb",
        "sqlite",
        &TranspileOptions::strict(),
    )
    .unwrap();
    assert!(!out[0].starts_with("SELECT CAST(CAST("), "{out:?}");
    assert!(out[0].contains("NULLIF("), "{out:?}");
}

#[test]
fn duckdb_int_div_preserves_nested_operators_and_large_integer_sql() {
    for sql in [
        "SELECT 8 // 2 // 2 AS v",
        "SELECT 8 // (4 // 2) AS v",
        "SELECT ROUND(8 // 2 // 2, 0) AS v",
        "SELECT 9007199254740995 // 2 AS v",
        "SELECT -9007199254740995 // 2 AS v",
    ] {
        let out =
            transpile_with_by_name(sql, "duckdb", "sqlite", &TranspileOptions::strict()).unwrap();
        assert!(!out[0].contains("DIV("), "{sql}: {out:?}");
        assert!(!out[0].contains("AS REAL"), "{sql}: {out:?}");
        parse_one(&out[0], DialectType::SQLite).unwrap();
    }
    let out = transpile_with_by_name(
        "SELECT 8 // (4 // 0) AS v",
        "duckdb",
        "postgresql",
        &TranspileOptions::strict(),
    )
    .unwrap();
    assert_eq!(
        out,
        ["SELECT DIV(8, NULLIF((DIV(4, NULLIF(0, 0))), 0)) AS v"]
    );
}

#[test]
fn duckdb_fractional_int_div_protects_zero_for_clickhouse() {
    for sql in [
        "SELECT 7.0 // 0",
        "SELECT 7 // 0.0",
        "SELECT 7e0 // -0",
        "SELECT CAST(7 AS DOUBLE) // CAST(z AS DOUBLE) FROM t",
    ] {
        let out = transpile_with_by_name(sql, "duckdb", "clickhouse", &TranspileOptions::strict())
            .unwrap();
        assert!(out[0].contains(" / NULLIF("), "{sql}: {out:?}");
        assert!(!out[0].contains("intDiv"), "{sql}: {out:?}");
    }
}

#[test]
fn duckdb_integer_division_rejects_targets_without_a_lowering() {
    for target in [DialectType::MySQL, DialectType::Snowflake] {
        assert!(transpile("SELECT 7 // 2", DialectType::DuckDB, target).is_err());
    }
}

#[test]
fn duckdb_integer_division_lowers_to_plain_division_for_datafusion() {
    // DataFusion's `/` already has DuckDB's `//` type-dependent semantics
    // (integer operands truncate toward zero, a floating operand gives float
    // division). Operand types must be resolved to exclude decimal arithmetic
    // and functions whose return types differ between the two engines.
    for (sql, expected) in [
        ("SELECT 7 // 2 AS v", "SELECT 7 / nullif(2, 0) AS v"),
        ("SELECT -7 // 2 AS v", "SELECT -7 / nullif(2, 0) AS v"),
        ("SELECT 7 // 0 AS v", "SELECT 7 / nullif(0, 0) AS v"),
        (
            "SELECT CAST(x AS INTEGER) // CAST(y AS INTEGER) FROM t",
            "SELECT CAST(x AS INT) / nullif(CAST(y AS INT), 0) FROM t",
        ),
        (
            "SELECT 8 // 2 // 2 AS v",
            "SELECT 8 / nullif(2, 0) / nullif(2, 0) AS v",
        ),
    ] {
        for options in [TranspileOptions::default(), TranspileOptions::strict()] {
            let out = transpile_with_by_name(sql, "duckdb", "datafusion", &options)
                .unwrap_or_else(|e| panic!("{sql}: {e}"));
            assert_eq!(out, vec![expected], "{sql}");
        }
    }
    // A DECIMAL operand produces DOUBLE in DuckDB, while DataFusion would keep
    // decimal arithmetic, so the cast is still needed -- including next to an
    // explicitly typed column, and when the DECIMAL sits on either side of a lowered
    // integer // (which stays integer-typed in DataFusion).
    for (sql, expected) in [
        (
            "SELECT CAST(7 AS DECIMAL(10, 1)) // 2 AS v",
            "SELECT CAST(CAST(7 AS DECIMAL(10, 1)) AS DOUBLE) / nullif(2, 0) AS v",
        ),
        (
            "SELECT CAST(x AS INTEGER) // CAST(2 AS DECIMAL(10, 1)) AS v FROM t",
            "SELECT CAST(CAST(x AS INT) AS DOUBLE) / nullif(CAST(2 AS DECIMAL(10, 1)), 0) AS v FROM t",
        ),
        (
            "SELECT 7 // 2 // CAST(3 AS DECIMAL(10, 1)) AS v",
            "SELECT CAST(7 / nullif(2, 0) AS DOUBLE) / nullif(CAST(3 AS DECIMAL(10, 1)), 0) AS v",
        ),
        (
            "SELECT CAST(7 AS DECIMAL(10, 1)) // (2 // 1) AS v",
            "SELECT CAST(CAST(7 AS DECIMAL(10, 1)) AS DOUBLE) / nullif((2 / nullif(1, 0)), 0) AS v",
        ),
    ] {
        let out = transpile(sql, DialectType::DuckDB, DialectType::DataFusion).unwrap();
        assert_eq!(out, vec![expected], "{sql}");
    }
}

#[test]
fn duckdb_source_division_inside_int_div_stays_float_for_datafusion() {
    // DuckDB's `/` is always DOUBLE, but DataFusion's `/` on two integers
    // truncates, so a source division inside a `//` operand gets its dividend
    // cast to DOUBLE: `(7 / 2) // 2` is 1.75 in DuckDB, not 1. A lowered `//`
    // is left as the integer it is, including user-written NULLIF divisors.
    for (sql, expected) in [
        (
            "SELECT (7 / 2) // 2 AS v",
            "SELECT (CAST(7 AS DOUBLE) / 2) / nullif(2, 0) AS v",
        ),
        (
            "SELECT 7 // (4 / 2) AS v",
            "SELECT 7 / nullif((CAST(4 AS DOUBLE) / 2), 0) AS v",
        ),
        (
            "SELECT 7.5 / 2 // 2 AS v",
            "SELECT CAST(7.5 AS DOUBLE) / 2 / nullif(2, 0) AS v",
        ),
        (
            "SELECT 7 // 2 // 2 AS v",
            "SELECT 7 / nullif(2, 0) / nullif(2, 0) AS v",
        ),
    ] {
        let out = transpile(sql, DialectType::DuckDB, DialectType::DataFusion).unwrap();
        assert_eq!(out, vec![expected], "{sql}");
    }
}

// Engine execution coverage lives in the Python bindings' optional test suite.
const DATAFUSION_NESTED_DIVISION_CASES: &[(&str, &str)] = &[
    (
        "SELECT ABS(7 / 2) // 2 AS v",
        "SELECT ABS(CAST(7 AS DOUBLE) / 2) / nullif(2, 0) AS v",
    ),
    (
        "SELECT COALESCE(7 / 2, 0) // 2 AS v",
        "SELECT coalesce(CAST(7 AS DOUBLE) / 2, 0) / nullif(2, 0) AS v",
    ),
    (
        "SELECT (CASE WHEN TRUE THEN 7 / 2 ELSE 0 END) // 2 AS v",
        "SELECT (CASE WHEN TRUE THEN CAST(7 AS DOUBLE) / 2 ELSE 0 END) / nullif(2, 0) AS v",
    ),
    (
        "SELECT CAST(7 / 2 AS DOUBLE) // 2 AS v",
        "SELECT CAST(CAST(7 AS DOUBLE) / 2 AS DOUBLE) / nullif(2, 0) AS v",
    ),
    (
        "SELECT (x / 2) // 2 AS v FROM (VALUES (7)) AS t(x)",
        "SELECT (CAST(x AS DOUBLE) / 2) / nullif(2, 0) AS v FROM (VALUES (7)) AS t(x)",
    ),
    (
        "SELECT ABS(x / y) // 2 AS v FROM (VALUES (7, 2)) AS t(x, y)",
        "SELECT ABS(CAST(x AS DOUBLE) / y) / nullif(2, 0) AS v FROM (VALUES (7, 2)) AS t(x, y)",
    ),
    (
        "SELECT (CAST(1 AS DECIMAL(10, 1)) / 3) // 2 AS v",
        "SELECT (CAST(CAST(1 AS DECIMAL(10, 1)) AS DOUBLE) / 3) / nullif(2, 0) AS v",
    ),
    (
        "SELECT (7 / NULLIF(2, 0)) // 2 AS v",
        "SELECT (CAST(7 AS DOUBLE) / nullif(2, 0)) / nullif(2, 0) AS v",
    ),
    (
        "SELECT (7 / NULLIF(2, 0)) // CAST(2 AS DECIMAL(10, 1)) AS v",
        "SELECT (CAST(7 AS DOUBLE) / nullif(2, 0)) / nullif(CAST(2 AS DECIMAL(10, 1)), 0) AS v",
    ),
    (
        "SELECT ABS(7 // 2) // 2 AS v",
        "SELECT ABS(7 / nullif(2, 0)) / nullif(2, 0) AS v",
    ),
    (
        "SELECT COALESCE(NULL, 7 // 2) // 2 AS v",
        "SELECT coalesce(NULL, 7 / nullif(2, 0)) / nullif(2, 0) AS v",
    ),
    (
        "SELECT (7 // CAST(2 AS FLOAT)) // 2 AS v",
        "SELECT (7 / nullif(CAST(2 AS REAL), 0)) / nullif(2, 0) AS v",
    ),
    (
        "SELECT CAST(7 / 2 AS INTEGER) // 2 AS v",
        "SELECT CAST(ROUND(CAST(7 AS DOUBLE) / 2, 0) AS INT) / nullif(2, 0) AS v",
    ),
    (
        "SELECT TRY_CAST(-7 / 2 AS INTEGER) // 2 AS v",
        "SELECT TRY_CAST(ROUND(CAST(-7 AS DOUBLE) / 2, 0) AS INT) / nullif(2, 0) AS v",
    ),
    (
        "SELECT CAST(CAST(7 AS DECIMAL(10, 1)) / 2 AS INTEGER) // 2 AS v",
        "SELECT CAST(ROUND(CAST(CAST(7 AS DECIMAL(10, 1)) AS DOUBLE) / 2, 0) AS INT) / nullif(2, 0) AS v",
    ),
];

#[test]
fn duckdb_datafusion_nested_division_regressions() {
    for &(sql, expected) in DATAFUSION_NESTED_DIVISION_CASES {
        for options in [TranspileOptions::default(), TranspileOptions::strict()] {
            let out = transpile_with_by_name(sql, "duckdb", "datafusion", &options)
                .unwrap_or_else(|e| panic!("{sql}: {e}"));
            assert_eq!(out, [expected], "{sql}");
        }
    }
}

#[test]
fn duckdb_datafusion_rejects_ambiguous_division_in_both_modes() {
    for sql in [
        "SELECT x // y FROM t",
        "SELECT x // CAST(2 AS DECIMAL(10, 1)) FROM t",
        "SELECT x // 3 FROM (SELECT CAST(1 AS DECIMAL(10, 1)) AS x) t",
        "SELECT (x // 3) * 3 = 1 FROM (SELECT CAST(1 AS DECIMAL(10, 1)) AS x) t",
        "SELECT ROUND(7, 0) // 2",
        "SELECT ROUND(7 // 2, 0) // 2",
        "SELECT ABS(x) // 2 FROM t",
        "SELECT COALESCE(x, 7 / 2) // 2 FROM t",
        "SELECT (CASE WHEN TRUE THEN 7 / 2 ELSE x END) // 2 FROM t",
        "SELECT NULLIF(7, 2.5) // 2",
    ] {
        for options in [TranspileOptions::default(), TranspileOptions::strict()] {
            let error = transpile_with_by_name(sql, "duckdb", "datafusion", &options).unwrap_err();
            assert!(
                error.to_string().contains("unresolved operand types"),
                "{sql}: {error}"
            );
        }
    }
}

#[test]
fn duckdb_datafusion_division_rewrites_are_scoped_to_int_div() {
    assert_eq!(
        transpile(
            "SELECT 7 / 2, (7 / 2) // 2",
            DialectType::DuckDB,
            DialectType::DataFusion
        )
        .unwrap(),
        ["SELECT 7 / 2, (CAST(7 AS DOUBLE) / 2) / nullif(2, 0)"],
    );
    // Both divisions retain their original syntax in the source dialect.
    assert_eq!(
        transpile(
            "SELECT ABS(7 / NULLIF(2, 0)) // 2",
            DialectType::DuckDB,
            DialectType::DuckDB
        )
        .unwrap(),
        ["SELECT ABS(7 / NULLIF(2, 0)) // 2"],
    );
}

#[test]
#[ignore = "requires POLYGLOT_DUCKDB and POLYGLOT_SQLITE CLI paths"]
fn duckdb_integer_division_sqlite_execution() {
    use std::process::Command;
    let duckdb = std::env::var("POLYGLOT_DUCKDB").expect("set POLYGLOT_DUCKDB");
    let sqlite = std::env::var("POLYGLOT_SQLITE").expect("set POLYGLOT_SQLITE");
    for (sql, expected) in [
        ("SELECT 7 // 2 AS v", "3"),
        ("SELECT CAST(7 AS DOUBLE) // 2 AS v", "3.5"),
        ("SELECT CAST(7 AS DECIMAL(10, 1)) // 2 AS v", "3.5"),
        ("SELECT (7.0 + 0) // 2 AS v", "3.5"),
        ("SELECT (7 // 2.0) // 2 AS v", "1.75"),
        (
            "SELECT CAST(x AS DOUBLE) // 2 AS v FROM (SELECT 7.0 AS x) t",
            "3.5",
        ),
        ("SELECT 8 // 2 // 2 AS v", "2"),
        ("SELECT 8 // (4 // 2) AS v", "4"),
        ("SELECT 9007199254740995 // 2 AS v", "4503599627370497"),
        ("SELECT -9007199254740995 // 2 AS v", "-4503599627370497"),
        (
            "SELECT 9223372036854775807 // 2 AS v",
            "4611686018427387903",
        ),
        (
            "SELECT -9223372036854775808 // 2 AS v",
            "-4611686018427387904",
        ),
        ("SELECT 7 // 0 AS v", "NULL"),
        ("SELECT 7 // (0) AS v", "NULL"),
        ("SELECT 7 // -0 AS v", "NULL"),
        ("SELECT 7 // CAST(0 AS INTEGER) AS v", "NULL"),
        ("SELECT 7 // (1 - 1) AS v", "NULL"),
        ("SELECT 7.0 // 0 AS v", "NULL"),
        ("SELECT 7 // 0.0 AS v", "NULL"),
        ("SELECT 8 // (4 // 0) AS v", "NULL"),
        (
            "SELECT 7 // CAST(z AS INTEGER) AS v FROM (SELECT 0 AS z) t",
            "NULL",
        ),
    ] {
        let generated =
            transpile_with_by_name(sql, "duckdb", "sqlite", &TranspileOptions::strict()).unwrap();
        for (engine, query, args) in [
            (
                &duckdb,
                sql,
                vec![
                    "-init",
                    "/dev/null",
                    "-noheader",
                    "-csv",
                    "-nullvalue",
                    "NULL",
                    ":memory:",
                ],
            ),
            (
                &sqlite,
                generated[0].as_str(),
                vec!["-noheader", "-csv", "-nullvalue", "NULL", ":memory:"],
            ),
        ] {
            let result = Command::new(engine).args(args).arg(query).output().unwrap();
            assert!(
                result.status.success(),
                "{query}: {}",
                String::from_utf8_lossy(&result.stderr)
            );
            assert_eq!(
                String::from_utf8_lossy(&result.stdout).trim(),
                expected,
                "{engine}: {query}"
            );
        }
    }
}

#[test]
fn other_dialects_integer_division_is_unaffected() {
    // The float/zero guards are about DuckDB's // semantics specifically.
    // MySQL's and Vertica's integer division genuinely truncates float
    // operands, so their existing lowering must keep working.
    assert_eq!(
        transpile("SELECT 7.5 DIV 2", DialectType::MySQL, DialectType::MySQL).unwrap(),
        vec!["SELECT DIV(7.5, 2)"]
    );
    assert_eq!(
        transpile(
            "SELECT 7.5 // 2",
            DialectType::Vertica,
            DialectType::PostgreSQL
        )
        .unwrap(),
        vec!["SELECT DIV(7.5, 2)"]
    );
}

#[test]
fn plain_division_is_unchanged_in_duckdb_and_elsewhere() {
    let out = transpile(
        "SELECT 7 / 2 AS v",
        DialectType::DuckDB,
        DialectType::DuckDB,
    )
    .unwrap();
    assert_eq!(out, vec!["SELECT 7 / 2 AS v"]);
    let out = transpile(
        "SELECT 7 / 2 AS v",
        DialectType::PostgreSQL,
        DialectType::PostgreSQL,
    )
    .unwrap();
    assert_eq!(out, vec!["SELECT 7 / 2 AS v"]);
}