scythe-codegen 0.17.0

// Auto-generated by test-generator. Do not edit.
// Category: expressions

#[allow(unused_imports)]
use scythe_codegen as _codegen;
#[allow(unused_imports)]
use syn as _syn;

#[allow(dead_code)]
fn normalize_whitespace(s: &str) -> String {
    s.split_whitespace().collect::<Vec<_>>().join(" ")
}

#[test]
fn test_case_numeric_branches() {
    // From: testing_data/expressions/case/02_case_numeric_branches.json
    // "CASE expression with numeric column references preserves the column type"
    let schema_sql = &[
        "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, description TEXT, category TEXT NOT NULL)",
    ];

    let query_sql = "-- @name GetAdjustedPrice\n-- @returns :many\nSELECT CASE WHEN price > 100 THEN price * 2 ELSE price END AS adjusted FROM products";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetAdjustedPrice", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "adjusted", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for adjusted"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for adjusted");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "case_numeric_branches", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "case_numeric_branches", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "case_numeric_branches"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "case_numeric_branches",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "case_numeric_branches"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "case_numeric_branches"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "case_numeric_branches",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "case_numeric_branches", e
                ),
            },
        }
    }
}

#[test]
fn test_case_type_from_branches() {
    // From: testing_data/expressions/case/01_case_type_from_branches.json
    // "CASE expression infers String type from string literal branches"
    let schema_sql = &[
        "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, description TEXT, category TEXT NOT NULL)",
    ];

    let query_sql = "-- @name GetProductTier\n-- @returns :many\nSELECT CASE WHEN price > 100 THEN 'expensive' ELSE 'cheap' END AS tier FROM products";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetProductTier", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "tier", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for tier"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for tier");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "case_type_from_branches", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "case_type_from_branches", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "case_type_from_branches"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "case_type_from_branches",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "case_type_from_branches"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "case_type_from_branches"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "case_type_from_branches",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "case_type_from_branches", e
                ),
            },
        }
    }
}

#[test]
fn test_case_widens_to_decimal() {
    // From: testing_data/expressions/case/03_case_widens_to_decimal.json
    // "CASE arms of different numeric widths widen to the wider type instead of taking the first arm's type"
    let schema_sql =
        &["CREATE TABLE order_items (id SERIAL PRIMARY KEY, msrp NUMERIC NOT NULL, on_sale BOOLEAN NOT NULL)"];

    let query_sql = "-- @name GetEffectivePrice\n-- @returns :many\nSELECT CASE WHEN on_sale THEN 0 ELSE msrp END AS effective_price FROM order_items";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetEffectivePrice", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "effective_price", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "decimal",
        "column neutral_type for effective_price"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for effective_price");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "case_widens_to_decimal", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "case_widens_to_decimal", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "case_widens_to_decimal"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "case_widens_to_decimal",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "case_widens_to_decimal"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "case_widens_to_decimal"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "case_widens_to_decimal",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "case_widens_to_decimal", e
                ),
            },
        }
    }
}

#[test]
fn test_cast_preserves_nullability() {
    // From: testing_data/expressions/cast/03_cast_preserves_nullability.json
    // "Casting a nullable column preserves the nullable status in the output"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name GetShortBio\n-- @returns :one\nSELECT bio::varchar(100) AS short_bio FROM users WHERE id = $1";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetShortBio", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "short_bio", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for short_bio"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for short_bio");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "cast_preserves_nullability", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "cast_preserves_nullability", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "cast_preserves_nullability"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "cast_preserves_nullability",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "cast_preserves_nullability"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "cast_preserves_nullability"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "cast_preserves_nullability",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "cast_preserves_nullability", e
                ),
            },
        }
    }
}

#[test]
fn test_cast_text() {
    // From: testing_data/expressions/cast/02_cast_text.json
    // "Casting a nullable column to text preserves nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetAgeAsText\n-- @returns :one\nSELECT age::text AS age_str FROM users WHERE id = $1";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetAgeAsText", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "age_str", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for age_str"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for age_str");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "cast_text", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "cast_text", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "cast_text"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "cast_text",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "cast_text"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "cast_text"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "cast_text",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "cast_text", e
                ),
            },
        }
    }
}

#[test]
fn test_explicit_cast() {
    // From: testing_data/expressions/cast/01_explicit_cast.json
    // "Explicit cast changes the Rust type mapping while preserving nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserBigId\n-- @returns :one\nSELECT id::bigint AS big_id FROM users WHERE id = $1";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetUserBigId", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "big_id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int64",
        "column neutral_type for big_id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for big_id");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "explicit_cast", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "explicit_cast", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "explicit_cast"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "explicit_cast",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "explicit_cast"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "explicit_cast"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "explicit_cast",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "explicit_cast", e
                ),
            },
        }
    }
}

#[test]
fn test_cast_composite_field_access() {
    // From: testing_data/expressions/cast_advanced/03_composite_field_access.json
    // "Accessing fields from a composite type column — fields are nullable even if composite is NOT NULL"
    let schema_sql = &[
        "CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);",
        "CREATE TABLE contacts (id SERIAL PRIMARY KEY, home_address address NOT NULL);",
    ];

    let query_sql = "-- @name GetContactAddressFields\n-- @returns :many\nSELECT id, (home_address).street, (home_address).city FROM contacts";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetContactAddressFields", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "street", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for street"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for street");
    assert_eq!(analyzed.columns[2].name, "city", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for city"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for city");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "cast_composite_field_access", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "cast_composite_field_access", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "cast_composite_field_access"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "cast_composite_field_access",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "cast_composite_field_access"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "cast_composite_field_access"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "cast_composite_field_access",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "cast_composite_field_access", e
                ),
            },
        }
    }
}

#[test]
fn test_cast_pg_double_colon() {
    // From: testing_data/expressions/cast_advanced/01_pg_cast_syntax.json
    // "PostgreSQL :: cast syntax works alongside CAST() — confirms type resolution for double-colon casts"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active');",
    ];

    let query_sql = "-- @name ListUsersWithCasts\n-- @returns :many\nSELECT id, id::bigint AS big_id, name::varchar(10) AS short_name FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "ListUsersWithCasts", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "big_id", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int64",
        "column neutral_type for big_id"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for big_id");
    assert_eq!(analyzed.columns[2].name, "short_name", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for short_name"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for short_name");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "cast_pg_double_colon", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "cast_pg_double_colon", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "cast_pg_double_colon"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "cast_pg_double_colon",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "cast_pg_double_colon"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "cast_pg_double_colon"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "cast_pg_double_colon",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "cast_pg_double_colon", e
                ),
            },
        }
    }
}

#[test]
fn test_cast_union_type_widening() {
    // From: testing_data/expressions/cast_advanced/02_implicit_union_widening.json
    // "Type widening in UNION — result uses the wider type"
    let schema_sql = &[
        "CREATE TABLE small_nums (val SMALLINT NOT NULL);",
        "CREATE TABLE big_nums (val BIGINT NOT NULL);",
    ];

    let query_sql =
        "-- @name GetAllNums\n-- @returns :many\nSELECT val FROM small_nums UNION ALL SELECT val FROM big_nums";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetAllNums", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "val", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int64", "column neutral_type for val");
    assert!(!analyzed.columns[0].nullable, "column nullable for val");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "cast_union_type_widening", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "cast_union_type_widening", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "cast_union_type_widening"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "cast_union_type_widening",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "cast_union_type_widening"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "cast_union_type_widening"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "cast_union_type_widening",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "cast_union_type_widening", e
                ),
            },
        }
    }
}

#[test]
fn test_try_cast_nullable_on_failure() {
    // From: testing_data/expressions/cast_advanced/04_try_cast_nullable_on_failure.json
    // "TRY_CAST is nullable even over a NOT NULL operand, because it returns NULL on a conversion failure instead of erroring -- that is the entire point of the syntax"
    let schema_sql = &["CREATE TABLE imports (id INTEGER NOT NULL, raw_amount VARCHAR NOT NULL);"];

    let query_sql =
        "-- @name GetParsedAmount\n-- @returns :many\nSELECT TRY_CAST(raw_amount AS INTEGER) AS parsed FROM imports";

    let catalog =
        scythe_core::catalog::Catalog::from_ddl_with_dialect(schema_sql, &scythe_core::dialect::SqlDialect::Snowflake)
            .unwrap()
            .with_engine("snowflake");
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetParsedAmount", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "parsed", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int64",
        "column neutral_type for parsed"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for parsed");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "snowflake";
    let all_backends = ["java-jdbc", "kotlin-jdbc", "php-pdo"];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "try_cast_nullable_on_failure", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "try_cast_nullable_on_failure", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "try_cast_nullable_on_failure"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "try_cast_nullable_on_failure",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "try_cast_nullable_on_failure"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "try_cast_nullable_on_failure"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "try_cast_nullable_on_failure",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "try_cast_nullable_on_failure", e
                ),
            },
        }
    }
}

#[test]
fn test_coalesce_preserves_type() {
    // From: testing_data/expressions/coalesce/02_coalesce_preserves_type.json
    // "COALESCE preserves the SQL type of the first argument while adjusting nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserBioOrDefault\n-- @returns :one\nSELECT COALESCE(bio, 'default') AS bio FROM users WHERE id = $1";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetUserBioOrDefault", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "bio", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for bio");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "coalesce_preserves_type", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "coalesce_preserves_type", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "coalesce_preserves_type"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "coalesce_preserves_type",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "coalesce_preserves_type"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "coalesce_preserves_type"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "coalesce_preserves_type",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "coalesce_preserves_type", e
                ),
            },
        }
    }
}

#[test]
fn test_coalesce_type_inference() {
    // From: testing_data/expressions/coalesce/01_coalesce_type_inference.json
    // "COALESCE infers the type from the first argument and literal default makes it non-nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserAge\n-- @returns :one\nSELECT COALESCE(age, 0) AS age FROM users WHERE id = $1";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetUserAge", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "age", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for age");
    assert!(!analyzed.columns[0].nullable, "column nullable for age");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "coalesce_type_inference", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "coalesce_type_inference", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "coalesce_type_inference"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "coalesce_type_inference",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "coalesce_type_inference"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "coalesce_type_inference"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "coalesce_type_inference",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "coalesce_type_inference", e
                ),
            },
        }
    }
}

#[test]
fn test_coalesce_widens_to_decimal() {
    // From: testing_data/expressions/coalesce/03_coalesce_widens_to_decimal.json
    // "COALESCE widens across its arguments' types instead of taking only the first non-unknown argument's type"
    let schema_sql = &["CREATE TABLE order_items (id SERIAL PRIMARY KEY, discount NUMERIC)"];

    let query_sql =
        "-- @name GetEffectiveDiscount\n-- @returns :many\nSELECT COALESCE(0, discount) AS d FROM order_items";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetEffectiveDiscount", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "d", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "decimal", "column neutral_type for d");
    assert!(!analyzed.columns[0].nullable, "column nullable for d");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "coalesce_widens_to_decimal", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "coalesce_widens_to_decimal", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "coalesce_widens_to_decimal"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "coalesce_widens_to_decimal",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "coalesce_widens_to_decimal"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "coalesce_widens_to_decimal"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "coalesce_widens_to_decimal",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "coalesce_widens_to_decimal", e
                ),
            },
        }
    }
}

#[test]
fn test_age_nullable_operand() {
    // From: testing_data/expressions/date_functions/07_age_nullable_operand.json
    // "AGE over a nullable operand is nullable -- the #163 gap: the existing date_age_interval fixture only used NOT NULL arguments, so it couldn't tell a sound implementation from an unsound one"
    let schema_sql = &["CREATE TABLE accounts (id SERIAL PRIMARY KEY, deleted_at TIMESTAMPTZ)"];

    let query_sql =
        "-- @name GetTimeSinceDeletion\n-- @returns :many\nSELECT AGE(NOW(), deleted_at) AS elapsed FROM accounts";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetTimeSinceDeletion", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "elapsed", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "interval",
        "column neutral_type for elapsed"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for elapsed");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "age_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "age_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "age_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "age_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "age_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "age_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "age_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "age_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_date_age_interval() {
    // From: testing_data/expressions/date_functions/04_age_interval.json
    // "AGE function returns PgInterval, non-nullable when both arguments are non-null"
    let schema_sql =
        &["CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);"];

    let query_sql =
        "-- @name AgeInterval\n-- @returns :many\nSELECT id, AGE(NOW(), occurred_at) AS elapsed FROM events";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "AgeInterval", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "elapsed", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "interval",
        "column neutral_type for elapsed"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for elapsed");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "date_age_interval", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "date_age_interval", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "date_age_interval"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "date_age_interval",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "date_age_interval"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "date_age_interval"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "date_age_interval",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "date_age_interval", e
                ),
            },
        }
    }
}

#[test]
fn test_date_current_functions() {
    // From: testing_data/expressions/date_functions/01_current_date_timestamp.json
    // "CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() return non-nullable date/time values"
    let schema_sql =
        &["CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);"];

    let query_sql = "-- @name CurrentDateTimestamp\n-- @returns :one\nSELECT CURRENT_DATE AS today, CURRENT_TIMESTAMP AS now_ts, NOW() AS now_fn";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "CurrentDateTimestamp", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "today", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "date",
        "column neutral_type for today"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for today");
    assert_eq!(analyzed.columns[1].name, "now_ts", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "datetime_tz",
        "column neutral_type for now_ts"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for now_ts");
    assert_eq!(analyzed.columns[2].name, "now_fn", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "datetime_tz",
        "column neutral_type for now_fn"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for now_fn");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "date_current_functions", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "date_current_functions", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "date_current_functions"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "date_current_functions",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "date_current_functions"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "date_current_functions"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "date_current_functions",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "date_current_functions", e
                ),
            },
        }
    }
}

#[test]
fn test_date_extract() {
    // From: testing_data/expressions/date_functions/02_extract.json
    // "EXTRACT returns numeric (decimal) on PostgreSQL 14+, not the double precision every version of date_part() returns"
    let schema_sql =
        &["CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);"];

    let query_sql = "-- @name ExtractDateParts\n-- @returns :many\nSELECT id, EXTRACT(YEAR FROM occurred_at) AS yr, EXTRACT(MONTH FROM occurred_at) AS mo FROM events";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "ExtractDateParts", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "yr", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "decimal",
        "column neutral_type for yr"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for yr");
    assert_eq!(analyzed.columns[2].name, "mo", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "decimal",
        "column neutral_type for mo"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for mo");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "date_extract", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "date_extract", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "date_extract"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "date_extract",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "date_extract"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "date_extract"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "date_extract",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "date_extract", e
                ),
            },
        }
    }
}

#[test]
fn test_date_extract_mysql_returns_int() {
    // From: testing_data/expressions/date_functions/05_extract_mysql_returns_int.json
    // "EXTRACT returns an integer on MySQL, unlike PostgreSQL's numeric -- the analyzer must not apply one dialect's answer to every engine"
    let schema_sql = &["CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, occurred_at TIMESTAMP NOT NULL);"];

    let query_sql = "-- @name ExtractDatePartsMysql\n-- @returns :many\nSELECT id, EXTRACT(YEAR FROM occurred_at) AS yr FROM events";

    let catalog =
        scythe_core::catalog::Catalog::from_ddl_with_dialect(schema_sql, &scythe_core::dialect::SqlDialect::MySQL)
            .unwrap()
            .with_engine("mysql");
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "ExtractDatePartsMysql", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "yr", "column name");
    assert_eq!(analyzed.columns[1].neutral_type, "int64", "column neutral_type for yr");
    assert!(!analyzed.columns[1].nullable, "column nullable for yr");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "mysql";
    let all_backends = [
        "rust-sqlx",
        "typescript-kysely",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "date_extract_mysql_returns_int", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "date_extract_mysql_returns_int", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "date_extract_mysql_returns_int"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "date_extract_mysql_returns_int",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "date_extract_mysql_returns_int"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "date_extract_mysql_returns_int"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "date_extract_mysql_returns_int",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "date_extract_mysql_returns_int", e
                ),
            },
        }
    }
}

#[test]
fn test_date_trunc() {
    // From: testing_data/expressions/date_functions/03_date_trunc.json
    // "DATE_TRUNC preserves the timezone type of the input timestamp"
    let schema_sql =
        &["CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);"];

    let query_sql = "-- @name DateTruncMonth\n-- @returns :many\nSELECT id, DATE_TRUNC('month', occurred_at) AS month_start FROM events";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "DateTruncMonth", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "month_start", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "datetime_tz",
        "column neutral_type for month_start"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for month_start");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "date_trunc", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "date_trunc", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "date_trunc"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "date_trunc",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "date_trunc"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "date_trunc"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "date_trunc",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "date_trunc", e
                ),
            },
        }
    }
}

#[test]
fn test_to_date_nullable_operand() {
    // From: testing_data/expressions/date_functions/06_to_date_nullable_operand.json
    // "TO_DATE over a nullable operand is nullable -- the operand was previously ignored entirely"
    let schema_sql = &["CREATE TABLE imports (id SERIAL PRIMARY KEY, raw_date TEXT)"];

    let query_sql =
        "-- @name GetParsedDate\n-- @returns :many\nSELECT TO_DATE(raw_date, 'YYYY-MM-DD') AS parsed FROM imports";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetParsedDate", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "parsed", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "date",
        "column neutral_type for parsed"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for parsed");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "to_date_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "to_date_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "to_date_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "to_date_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "to_date_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "to_date_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "to_date_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "to_date_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_fractional_and_exponent_literals() {
    // From: testing_data/expressions/literals/01_fractional_and_exponent_literals.json
    // "Numeric literals are typed from their text: a fractional literal is decimal, an exponent literal is float64, and a small plain integer literal is int32, matching PostgreSQL's own literal typing"
    let schema_sql = &["CREATE TABLE dummy (id SERIAL PRIMARY KEY)"];

    let query_sql =
        "-- @name GetLiteralTypes\n-- @returns :many\nSELECT 1 AS whole, 0.5 AS half, 1e10 AS huge FROM dummy";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetLiteralTypes", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "whole", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for whole"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for whole");
    assert_eq!(analyzed.columns[1].name, "half", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "decimal",
        "column neutral_type for half"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for half");
    assert_eq!(analyzed.columns[2].name, "huge", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "float64",
        "column neutral_type for huge"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for huge");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "fractional_and_exponent_literals", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "fractional_and_exponent_literals", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "fractional_and_exponent_literals"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "fractional_and_exponent_literals",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "fractional_and_exponent_literals"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "fractional_and_exponent_literals"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "fractional_and_exponent_literals",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "fractional_and_exponent_literals", e
                ),
            },
        }
    }
}

#[test]
fn test_greatest_least_widen() {
    // From: testing_data/expressions/math_functions/06_greatest_least_widen.json
    // "GREATEST and LEAST widen across every argument's type instead of taking only the first argument's type"
    let schema_sql =
        &["CREATE TABLE order_items (id SERIAL PRIMARY KEY, price INTEGER NOT NULL, msrp NUMERIC NOT NULL)"];

    let query_sql = "-- @name GetPriceBounds\n-- @returns :many\nSELECT GREATEST(price, msrp) AS g, LEAST(price, msrp) AS l FROM order_items";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetPriceBounds", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "g", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "decimal", "column neutral_type for g");
    assert!(!analyzed.columns[0].nullable, "column nullable for g");
    assert_eq!(analyzed.columns[1].name, "l", "column name");
    assert_eq!(analyzed.columns[1].neutral_type, "decimal", "column neutral_type for l");
    assert!(!analyzed.columns[1].nullable, "column nullable for l");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "greatest_least_widen", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "greatest_least_widen", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "greatest_least_widen"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "greatest_least_widen",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "greatest_least_widen"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "greatest_least_widen"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "greatest_least_widen",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "greatest_least_widen", e
                ),
            },
        }
    }
}

#[test]
fn test_math_abs_ceil_floor() {
    // From: testing_data/expressions/math_functions/01_abs_ceil_floor.json
    // "ABS, CEIL, and FLOOR math functions preserve non-nullability"
    let schema_sql = &[
        "CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION NOT NULL, quantity INTEGER NOT NULL);",
    ];

    let query_sql = "-- @name AbsCeilFloor\n-- @returns :many\nSELECT id, ABS(quantity) AS abs_qty, CEIL(value) AS ceil_val, FLOOR(value) AS floor_val FROM measurements";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "AbsCeilFloor", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "abs_qty", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for abs_qty"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for abs_qty");
    assert_eq!(analyzed.columns[2].name, "ceil_val", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "float64",
        "column neutral_type for ceil_val"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for ceil_val");
    assert_eq!(analyzed.columns[3].name, "floor_val", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "float64",
        "column neutral_type for floor_val"
    );
    assert!(!analyzed.columns[3].nullable, "column nullable for floor_val");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "math_abs_ceil_floor", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "math_abs_ceil_floor", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "math_abs_ceil_floor"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "math_abs_ceil_floor",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "math_abs_ceil_floor"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "math_abs_ceil_floor"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "math_abs_ceil_floor",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "math_abs_ceil_floor", e
                ),
            },
        }
    }
}

#[test]
fn test_math_power_sqrt() {
    // From: testing_data/expressions/math_functions/03_power_sqrt.json
    // "POWER and SQRT math functions return f64 non-nullable on non-null input"
    let schema_sql = &[
        "CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION NOT NULL, quantity INTEGER NOT NULL);",
    ];

    let query_sql = "-- @name PowerSqrt\n-- @returns :many\nSELECT id, POWER(value, 2) AS squared, SQRT(value) AS root FROM measurements";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "PowerSqrt", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "squared", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "float64",
        "column neutral_type for squared"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for squared");
    assert_eq!(analyzed.columns[2].name, "root", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "float64",
        "column neutral_type for root"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for root");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "math_power_sqrt", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "math_power_sqrt", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "math_power_sqrt"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "math_power_sqrt",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "math_power_sqrt"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "math_power_sqrt"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "math_power_sqrt",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "math_power_sqrt", e
                ),
            },
        }
    }
}

#[test]
fn test_math_power_sqrt_nullable_operand() {
    // From: testing_data/expressions/math_functions/05_power_sqrt_nullable_operand.json
    // "POWER and SQRT over a nullable operand are nullable -- the #163 gap: the existing math_power_sqrt fixture only used a NOT NULL operand, so it couldn't tell a sound implementation from an unsound one"
    let schema_sql = &["CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION);"];

    let query_sql = "-- @name PowerSqrtNullable\n-- @returns :many\nSELECT POWER(value, 2) AS squared, SQRT(value) AS root FROM measurements";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "PowerSqrtNullable", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "squared", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "float64",
        "column neutral_type for squared"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for squared");
    assert_eq!(analyzed.columns[1].name, "root", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "float64",
        "column neutral_type for root"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for root");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "math_power_sqrt_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "math_power_sqrt_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "math_power_sqrt_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "math_power_sqrt_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "math_power_sqrt_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "math_power_sqrt_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "math_power_sqrt_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "math_power_sqrt_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_math_round() {
    // From: testing_data/expressions/math_functions/02_round.json
    // "ROUND with cast to numeric returns rust_decimal::Decimal non-nullable"
    let schema_sql = &[
        "CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION NOT NULL, quantity INTEGER NOT NULL);",
    ];

    let query_sql =
        "-- @name RoundValue\n-- @returns :many\nSELECT id, ROUND(value::numeric, 2) AS rounded FROM measurements";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "RoundValue", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "rounded", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "decimal",
        "column neutral_type for rounded"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for rounded");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "math_round", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "math_round", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "math_round"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "math_round",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "math_round"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "math_round"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "math_round",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "math_round", e
                ),
            },
        }
    }
}

#[test]
fn test_round_preserves_float_type() {
    // From: testing_data/expressions/math_functions/04_round_preserves_float_type.json
    // "ROUND over a double precision argument returns double precision on PostgreSQL, not numeric"
    let schema_sql = &["CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION NOT NULL);"];

    let query_sql = "-- @name GetRoundedValue\n-- @returns :many\nSELECT ROUND(value) AS rounded FROM measurements";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetRoundedValue", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "rounded", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "float64",
        "column neutral_type for rounded"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for rounded");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "round_preserves_float_type", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "round_preserves_float_type", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "round_preserves_float_type"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "round_preserves_float_type",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "round_preserves_float_type"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "round_preserves_float_type"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "round_preserves_float_type",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "round_preserves_float_type", e
                ),
            },
        }
    }
}

#[test]
fn test_nullif_always_nullable() {
    // From: testing_data/expressions/nullif/01_nullif_always_nullable.json
    // "NULLIF always produces a nullable result because it can return NULL when arguments match"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name GetActiveStatus\n-- @returns :many\nSELECT NULLIF(status, 'inactive') AS active_status FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetActiveStatus", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "active_status", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for active_status"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for active_status");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "nullif_always_nullable", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "nullif_always_nullable", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "nullif_always_nullable"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "nullif_always_nullable",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "nullif_always_nullable"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "nullif_always_nullable"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "nullif_always_nullable",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "nullif_always_nullable", e
                ),
            },
        }
    }
}

#[test]
fn test_nullif_on_non_null() {
    // From: testing_data/expressions/nullif/02_nullif_on_non_null.json
    // "NULLIF on a NOT NULL column still produces nullable result"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetNonEmptyName\n-- @returns :many\nSELECT NULLIF(name, '') AS name FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetNonEmptyName", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "name", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for name");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "nullif_on_non_null", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "nullif_on_non_null", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "nullif_on_non_null"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "nullif_on_non_null",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "nullif_on_non_null"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "nullif_on_non_null"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "nullif_on_non_null",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "nullif_on_non_null", e
                ),
            },
        }
    }
}

#[test]
fn test_arithmetic() {
    // From: testing_data/expressions/operators/01_arithmetic.json
    // "Arithmetic on two NOT NULL columns produces a non-nullable result"
    let schema_sql = &[
        "CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, price INTEGER NOT NULL)",
    ];

    let query_sql = "-- @name GetLineTotal\n-- @returns :many\nSELECT price * quantity AS line_total FROM order_items";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetLineTotal", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "line_total", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for line_total"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for line_total");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "arithmetic", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "arithmetic", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "arithmetic"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "arithmetic",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "arithmetic"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "arithmetic"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "arithmetic",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "arithmetic", e
                ),
            },
        }
    }
}

#[test]
fn test_arithmetic_widens_to_decimal() {
    // From: testing_data/expressions/operators/04_arithmetic_widens_to_decimal.json
    // "Arithmetic between an INTEGER and a NUMERIC column widens to decimal instead of taking the left operand's type"
    let schema_sql =
        &["CREATE TABLE order_items (id SERIAL PRIMARY KEY, price INTEGER NOT NULL, msrp NUMERIC NOT NULL)"];

    let query_sql =
        "-- @name GetDiscountedPrice\n-- @returns :many\nSELECT price * msrp AS a, msrp * price AS b FROM order_items";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetDiscountedPrice", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "a", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "decimal", "column neutral_type for a");
    assert!(!analyzed.columns[0].nullable, "column nullable for a");
    assert_eq!(analyzed.columns[1].name, "b", "column name");
    assert_eq!(analyzed.columns[1].neutral_type, "decimal", "column neutral_type for b");
    assert!(!analyzed.columns[1].nullable, "column nullable for b");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "arithmetic_widens_to_decimal", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "arithmetic_widens_to_decimal", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "arithmetic_widens_to_decimal"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "arithmetic_widens_to_decimal",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "arithmetic_widens_to_decimal"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "arithmetic_widens_to_decimal"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "arithmetic_widens_to_decimal",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "arithmetic_widens_to_decimal", e
                ),
            },
        }
    }
}

#[test]
fn test_arithmetic_with_nullable() {
    // From: testing_data/expressions/operators/03_arithmetic_with_nullable.json
    // "Arithmetic involving a nullable column produces a nullable result"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetNextAge\n-- @returns :many\nSELECT age + 1 AS next_age FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetNextAge", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "next_age", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for next_age"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for next_age");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "arithmetic_with_nullable", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "arithmetic_with_nullable", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "arithmetic_with_nullable"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "arithmetic_with_nullable",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "arithmetic_with_nullable"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "arithmetic_with_nullable"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "arithmetic_with_nullable",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "arithmetic_with_nullable", e
                ),
            },
        }
    }
}

#[test]
fn test_between_nullable_operand() {
    // From: testing_data/expressions/operators/07_between_nullable_operand.json
    // "BETWEEN over a nullable column is nullable -- NULL BETWEEN 1 AND 10 is NULL, not false"
    let schema_sql = &["CREATE TABLE entries (id SERIAL PRIMARY KEY, score INTEGER)"];

    let query_sql =
        "-- @name GetScoreRange\n-- @returns :many\nSELECT id, score BETWEEN 1 AND 10 AS in_range FROM entries";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetScoreRange", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "in_range", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "bool",
        "column neutral_type for in_range"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for in_range");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "between_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "between_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "between_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "between_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "between_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "between_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "between_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "between_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_comparison_nullable_operand() {
    // From: testing_data/expressions/operators/05_comparison_nullable_operand.json
    // "A comparison over a nullable column is itself nullable -- SQL is three-valued, a NULL operand yields NULL, not false"
    let schema_sql = &["CREATE TABLE people (id SERIAL PRIMARY KEY, age INTEGER)"];

    let query_sql = "-- @name GetAdultFlags\n-- @returns :many\nSELECT id, age >= 18 AS is_adult FROM people";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetAdultFlags", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "is_adult", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "bool",
        "column neutral_type for is_adult"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for is_adult");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "comparison_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "comparison_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "comparison_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "comparison_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "comparison_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "comparison_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "comparison_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "comparison_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_in_list_nullable_operand() {
    // From: testing_data/expressions/operators/06_in_list_nullable_operand.json
    // "IN-list over a nullable column is nullable -- NULL IN (1, 2) is NULL, not false"
    let schema_sql = &["CREATE TABLE items (id SERIAL PRIMARY KEY, category TEXT)"];

    let query_sql =
        "-- @name GetCategoryMatches\n-- @returns :many\nSELECT id, category IN ('a', 'b') AS matched FROM items";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetCategoryMatches", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "matched", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "bool",
        "column neutral_type for matched"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for matched");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "in_list_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "in_list_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "in_list_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "in_list_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "in_list_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "in_list_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "in_list_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "in_list_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_string_concat() {
    // From: testing_data/expressions/operators/02_string_concat.json
    // "String concatenation of NOT NULL columns produces a non-nullable String"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name GetUserDisplay\n-- @returns :many\nSELECT name || ' <' || email || '>' AS display FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetUserDisplay", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "display", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for display"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for display");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "string_concat", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "string_concat", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "string_concat"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "string_concat",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "string_concat"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "string_concat"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "string_concat",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "string_concat", e
                ),
            },
        }
    }
}

#[test]
fn test_like_nullable_operand() {
    // From: testing_data/expressions/pattern_matching/02_like_nullable_operand.json
    // "LIKE over a nullable column is nullable -- the #163 gap: the existing like_returns_bool fixture only used a NOT NULL column, so it couldn't tell a sound implementation from an unsound one"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, bio TEXT)"];

    let query_sql =
        "-- @name GetBioStartsWithA\n-- @returns :many\nSELECT id, bio LIKE 'A%' AS starts_with_a FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetBioStartsWithA", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "starts_with_a", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "bool",
        "column neutral_type for starts_with_a"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for starts_with_a");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "like_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "like_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "like_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "like_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "like_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "like_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "like_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "like_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_like_returns_bool() {
    // From: testing_data/expressions/pattern_matching/01_like_returns_bool.json
    // "LIKE expression in SELECT returns a non-nullable bool when the input column is NOT NULL"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name GetUsersStartingWithA\n-- @returns :many\nSELECT id, name LIKE 'A%' AS starts_with_a FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetUsersStartingWithA", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "starts_with_a", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "bool",
        "column neutral_type for starts_with_a"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for starts_with_a");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "like_returns_bool", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "like_returns_bool", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "like_returns_bool"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "like_returns_bool",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "like_returns_bool"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "like_returns_bool"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "like_returns_bool",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "like_returns_bool", e
                ),
            },
        }
    }
}

#[test]
fn test_concat_mysql_nullable() {
    // From: testing_data/expressions/string_functions/06_concat_mysql_nullable.json
    // "CONCAT is nullable on MySQL when any argument is nullable, unlike PostgreSQL where concat() ignores NULL arguments"
    let schema_sql =
        &["CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, nickname VARCHAR(255));"];

    let query_sql =
        "-- @name GetDisplayNameMysql\n-- @returns :many\nSELECT CONCAT(name, nickname) AS display FROM users";

    let catalog =
        scythe_core::catalog::Catalog::from_ddl_with_dialect(schema_sql, &scythe_core::dialect::SqlDialect::MySQL)
            .unwrap()
            .with_engine("mysql");
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetDisplayNameMysql", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "display", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for display"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for display");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "mysql";
    let all_backends = [
        "rust-sqlx",
        "typescript-kysely",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "concat_mysql_nullable", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "concat_mysql_nullable", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "concat_mysql_nullable"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "concat_mysql_nullable",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "concat_mysql_nullable"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "concat_mysql_nullable"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "concat_mysql_nullable",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "concat_mysql_nullable", e
                ),
            },
        }
    }
}

#[test]
fn test_position_nullable_operand() {
    // From: testing_data/expressions/string_functions/05_position_nullable_operand.json
    // "POSITION is nullable when either operand is nullable -- both operands were previously ignored entirely"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, bio TEXT)"];

    let query_sql = "-- @name GetXPosition\n-- @returns :many\nSELECT POSITION('x' IN bio) AS pos FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetXPosition", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "pos", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for pos");
    assert!(analyzed.columns[0].nullable, "column nullable for pos");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "position_nullable_operand", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "position_nullable_operand", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "position_nullable_operand"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "position_nullable_operand",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "position_nullable_operand"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "position_nullable_operand"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "position_nullable_operand",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "position_nullable_operand", e
                ),
            },
        }
    }
}

#[test]
fn test_string_concat_function() {
    // From: testing_data/expressions/string_functions/02_concat.json
    // "CONCAT function never returns null, coerces null arguments to empty string"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);"];

    let query_sql =
        "-- @name ConcatDisplay\n-- @returns :many\nSELECT id, CONCAT(name, ' <', email, '>') AS display FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "ConcatDisplay", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "display", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for display"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for display");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "string_concat_function", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "string_concat_function", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "string_concat_function"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "string_concat_function",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "string_concat_function"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "string_concat_function"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "string_concat_function",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "string_concat_function", e
                ),
            },
        }
    }
}

#[test]
fn test_string_length_trim() {
    // From: testing_data/expressions/string_functions/04_length_trim.json
    // "LENGTH returns i32 and TRIM returns String, both non-nullable on NOT NULL input"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);"];

    let query_sql = "-- @name LengthTrimNames\n-- @returns :many\nSELECT id, LENGTH(name) AS name_len, TRIM(name) AS trimmed FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "LengthTrimNames", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name_len", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for name_len"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name_len");
    assert_eq!(analyzed.columns[2].name, "trimmed", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for trimmed"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for trimmed");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "string_length_trim", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "string_length_trim", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "string_length_trim"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "string_length_trim",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "string_length_trim"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "string_length_trim"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "string_length_trim",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "string_length_trim", e
                ),
            },
        }
    }
}

#[test]
fn test_string_substring() {
    // From: testing_data/expressions/string_functions/03_substring.json
    // "SUBSTRING function on a NOT NULL column returns non-nullable String"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);"];

    let query_sql =
        "-- @name SubstringPrefix\n-- @returns :many\nSELECT id, SUBSTRING(name FROM 1 FOR 3) AS prefix FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "SubstringPrefix", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "prefix", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for prefix"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for prefix");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "string_substring", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "string_substring", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "string_substring"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "string_substring",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "string_substring"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "string_substring"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "string_substring",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "string_substring", e
                ),
            },
        }
    }
}

#[test]
fn test_string_upper_lower() {
    // From: testing_data/expressions/string_functions/01_upper_lower.json
    // "UPPER and LOWER string functions preserve non-nullability of input"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);"];

    let query_sql = "-- @name UpperLowerNames\n-- @returns :many\nSELECT id, UPPER(name) AS upper_name, LOWER(name) AS lower_name FROM users";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "UpperLowerNames", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "upper_name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for upper_name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for upper_name");
    assert_eq!(analyzed.columns[2].name, "lower_name", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for lower_name"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for lower_name");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "string_upper_lower", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "string_upper_lower", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "string_upper_lower"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "string_upper_lower",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "string_upper_lower"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "string_upper_lower"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "string_upper_lower",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "string_upper_lower", e
                ),
            },
        }
    }
}