sql-dialect-fmt-formatter 1.14.0

//! Behavioural tests for the SQL formatter.
//!
//! Beyond a handful of golden expectations, the important invariants are exercised over the whole
//! embedded corpus:
//! * **Idempotency** — `format(format(x)) == format(x)`. A formatter that isn't a fixed point on
//!   its own output is a bug factory.
//! * **Content preservation** — the sequence of significant tokens (everything but trivia and the
//!   synthesized statement terminators) is unchanged, so formatting never drops or invents SQL.
//! * **No new parse errors** — formatting clean input yields clean output.

use sql_dialect_fmt_formatter::{format, FormatOptions, KeywordCase, LineEnding};
use sql_dialect_fmt_lexer::tokenize;
use sql_dialect_fmt_parser::parse;
use sql_dialect_fmt_syntax::SyntaxKind;
use sql_dialect_fmt_test_fixtures::EASY_CASES;

fn fmt(src: &str) -> String {
    format(src, &FormatOptions::default())
}

/// Significant token kinds: drop trivia and the statement terminators the formatter synthesizes.
fn significant_kinds(src: &str) -> Vec<SyntaxKind> {
    tokenize(src)
        .tokens
        .iter()
        .map(|t| t.kind)
        .filter(|k| !k.is_trivia() && *k != SyntaxKind::SEMICOLON)
        .collect()
}

/// The (whitespace-trimmed) text of every comment token in `src`.
fn comment_texts(src: &str) -> Vec<String> {
    tokenize(src)
        .tokens
        .iter()
        .filter(|t| t.kind.is_comment())
        .map(|t| t.text.trim_end().to_string())
        .collect()
}

#[test]
fn formats_a_basic_select() {
    assert_eq!(fmt("select a,b from t"), "SELECT a, b\nFROM t;\n");
}

#[test]
fn upcases_keywords_and_normalizes_spacing() {
    assert_eq!(
        fmt("select   x  from   t  where x=1 and y<>2"),
        "SELECT x\nFROM t\nWHERE x = 1 AND y <> 2;\n"
    );
}

#[test]
fn keyword_case_option_supports_lower_and_preserve() {
    assert_eq!(
        format(
            "select a from t",
            &FormatOptions::default().with_keyword_case(KeywordCase::Lower)
        ),
        "select a\nfrom t;\n"
    );
    assert_eq!(
        format(
            "select a FROM t",
            &FormatOptions::default().with_keyword_case(KeywordCase::Preserve)
        ),
        "select a\nFROM t;\n"
    );
    assert_eq!(
        format(
            "select a FROM t",
            &FormatOptions::default().with_uppercase_keywords(false)
        ),
        "select a\nFROM t;\n"
    );
}

#[test]
fn line_ending_option_controls_printed_newlines() {
    assert_eq!(
        format(
            "select a from t",
            &FormatOptions::default().with_line_ending(LineEnding::Crlf)
        ),
        "SELECT a\r\nFROM t;\r\n"
    );
    assert_eq!(
        format(
            "select a\r\nfrom t",
            &FormatOptions::default().with_line_ending(LineEnding::Auto)
        ),
        "SELECT a\r\nFROM t;\r\n"
    );
}

#[test]
fn format_off_on_directives_preserve_disabled_regions() {
    let src = "select a\n-- sql-dialect-fmt: off\nselect    a,b from t\n-- sql-dialect-fmt: on\nselect c from u";
    let expected = "SELECT a;\n-- sql-dialect-fmt: off\nselect    a,b from t\n-- sql-dialect-fmt: on\nSELECT c\nFROM u;\n";
    let out = fmt(src);
    assert_eq!(out, expected);
    assert_eq!(fmt(&out), out);
}

#[test]
fn disabled_regions_do_not_contribute_parse_errors() {
    let result = sql_dialect_fmt_formatter::format_with_diagnostics(
        "-- fmt: off\nselect from where\n-- fmt: on\nselect 1",
        &FormatOptions::default(),
    );
    assert!(
        result.parse_errors.is_empty(),
        "disabled invalid SQL should not be parsed: {:?}",
        result.parse_errors
    );
    assert!(result.formatted.contains("select from where"));
}

#[test]
fn lenient_statements_case_known_keywords() {
    assert_eq!(
        fmt("alter table t add column c int"),
        "ALTER TABLE t ADD COLUMN c INT;\n"
    );
}

#[test]
fn number_followed_by_standalone_dot_does_not_merge_into_float() {
    assert_eq!(fmt("desc select 0 . "), "DESC SELECT 0 .;\n");
}

#[test]
fn standalone_dot_followed_by_number_does_not_merge_into_float() {
    assert_eq!(fmt("desc . 42 "), "DESC. 42;\n");
}

#[test]
fn adjacent_operator_tokens_do_not_merge() {
    assert_eq!(fmt("desc - > "), "DESC - >;\n");
    assert_eq!(fmt("desc - - "), "DESC - -;\n");
    assert_eq!(fmt("desc - -> "), "DESC - ->;\n");
    assert_eq!(fmt("desc : = "), "DESC: =;\n");
    assert_eq!(fmt("desc : := "), "DESC: :=;\n");
    assert_eq!(fmt("desc : :: "), "DESC: ::;\n");
    assert_eq!(fmt("drop : => "), "DROP: =>;\n");
    assert_eq!(fmt("desc | > "), "DESC | >;\n");
}

#[test]
fn verbatim_statement_with_leading_trivia_is_idempotent() {
    let out = fmt("cluster 'abc' ->>-- c\n'abc' ");
    assert_eq!(fmt(&out), out);
}

#[test]
fn keeps_qualified_names_and_calls_tight() {
    assert_eq!(
        fmt("select count(*), t.a, x::int from s.t"),
        "SELECT count(*), t.a, x::int\nFROM s.t;\n"
    );
}

#[test]
fn distinct_is_part_of_the_header() {
    assert_eq!(
        fmt("select distinct a from t"),
        "SELECT DISTINCT a\nFROM t;\n"
    );
}

#[test]
fn formats_select_top_fetch_and_named_window() {
    assert_eq!(
        fmt("select top 10 * from orders order by created_at desc"),
        "SELECT TOP 10 *\nFROM orders\nORDER BY created_at DESC;\n"
    );
    assert_eq!(
        fmt("select id from orders order by created_at fetch first 5 rows only"),
        "SELECT id\nFROM orders\nORDER BY created_at\nFETCH FIRST 5 ROWS ONLY;\n"
    );
    assert_eq!(
        fmt("select sum(amount) over w from orders window w as (partition by customer_id order by created_at)"),
        "SELECT sum(amount) OVER w\nFROM orders\nWINDOW w AS (PARTITION BY customer_id ORDER BY created_at);\n"
    );
}

#[test]
fn formats_regex_like_quantifiers_and_snowflake_star_modifiers() {
    assert_eq!(
        fmt("select * from users where email rlike '.*@example[.]com'"),
        "SELECT *\nFROM users\nWHERE email RLIKE '.*@example[.]com';\n"
    );
    assert_eq!(
        fmt("select * from users where name ilike any ('A%', 'B%')"),
        "SELECT *\nFROM users\nWHERE name ILIKE ANY ('A%', 'B%');\n"
    );
    assert_eq!(
        fmt("select c.* exclude (internal_id, deleted_at) from customers c"),
        "SELECT c.* EXCLUDE (internal_id, deleted_at)\nFROM customers c;\n"
    );
    assert_eq!(
        fmt("select * replace (upper(name) as name) rename customer_id as id from customers"),
        "SELECT * REPLACE (upper(name) AS name) RENAME customer_id AS id\nFROM customers;\n"
    );
}

#[test]
fn long_select_list_breaks_one_item_per_line() {
    let src = "select alpha, bravo, charlie, delta, echo, foxtrot, golf, hotel from t";
    let out = format(src, &FormatOptions::default().with_line_width(40));
    insta::assert_snapshot!(out, @"
    SELECT
        alpha,
        bravo,
        charlie,
        delta,
        echo,
        foxtrot,
        golf,
        hotel
    FROM t;
    ");
}

#[test]
fn multiple_statements_are_separated_and_terminated() {
    assert_eq!(fmt("select 1; select 2"), "SELECT 1;\nSELECT 2;\n");
    assert_eq!(fmt("select 1;\n\n\nselect 2"), "SELECT 1;\n\nSELECT 2;\n");
    assert_eq!(
        fmt("select 1;\n-- group\nselect 2"),
        "SELECT 1;\n-- group\nSELECT 2;\n"
    );
    assert_eq!(
        fmt("select 1;\n\n-- group\nselect 2"),
        "SELECT 1;\n\n-- group\nSELECT 2;\n"
    );
}

#[test]
fn magic_trailing_comma_forces_the_list_to_explode() {
    // The list would fit on one line, but the author's trailing comma means "keep it exploded".
    insta::assert_snapshot!(fmt("select a, b, from t"), @"
    SELECT
        a,
        b,
    FROM t;
    ");
}

#[test]
fn magic_trailing_comma_explodes_even_a_single_item() {
    assert_eq!(fmt("select a, from t"), "SELECT\n    a,\nFROM t;\n");
}

#[test]
fn no_trailing_comma_stays_inline_when_it_fits() {
    assert_eq!(fmt("select a, b from t"), "SELECT a, b\nFROM t;\n");
}

#[test]
fn function_arguments_honor_a_magic_trailing_comma() {
    // The trailing comma after `b` explodes the argument list, which in turn forces the SELECT
    // list to break (a multiline item can't sit inline).
    insta::assert_snapshot!(fmt("select f(a, b,) from t"), @"
    SELECT
        f(
            a,
            b,
        )
    FROM t;
    ");
}

#[test]
fn function_arguments_stay_inline_without_a_trailing_comma() {
    assert_eq!(fmt("select f(a, b) from t"), "SELECT f(a, b)\nFROM t;\n");
}

#[test]
fn comment_before_synthesized_closing_paren_does_not_force_verbatim() {
    insta::assert_snapshot!(fmt("select f(\n a\n -- why\n) from t"), @"
    SELECT
        f(
            a -- why
        )
    FROM t;
    ");
}

#[test]
fn formats_expression_literals_and_bind_markers() {
    assert_eq!(
        fmt("select interval '1' day + interval 2 hours from t where id=? and tenant_id=:tenant_id"),
        "SELECT INTERVAL '1' DAY + INTERVAL 2 HOURS\nFROM t\nWHERE id = ? AND tenant_id = :tenant_id;\n"
    );
    assert_eq!(
        fmt("select [1,2,{'nested':true}], {'a':1,'b':[2,3]} from t"),
        "SELECT [1, 2, {'nested': TRUE}], {'a': 1, 'b': [2, 3]}\nFROM t;\n"
    );
}

#[test]
fn values_rows_honor_a_magic_trailing_comma() {
    insta::assert_snapshot!(fmt("values (1, 2,), (3, 4)"), @"
    VALUES
        (
            1,
            2,
        ),
        (3, 4);
    ");
}

#[test]
fn aggregate_distinct_quantifier_is_kept() {
    assert_eq!(
        fmt("select count(distinct x) from t"),
        "SELECT count(DISTINCT x)\nFROM t;\n"
    );
    assert_eq!(
        fmt("select listagg(distinct x, ',') from t"),
        "SELECT listagg(DISTINCT x, ',')\nFROM t;\n"
    );
}

#[test]
fn empty_argument_list_stays_tight() {
    assert_eq!(
        fmt("select current_timestamp() from t"),
        "SELECT current_timestamp()\nFROM t;\n"
    );
}

#[test]
fn joins_each_go_on_their_own_line() {
    insta::assert_snapshot!(
        fmt("select a.x, b.y from a inner join b on a.id = b.id left join c on b.k = c.k"),
        @"
    SELECT a.x, b.y
    FROM a
    INNER JOIN b ON a.id = b.id
    LEFT JOIN c ON b.k = c.k;
    ",
    );
}

#[test]
fn long_logical_predicates_wrap_by_operator() {
    let out = format(
        "select * from orders where customer_id = 1 and status = 'open' and created_at >= '2024-01-01' and region = 'us-east-1'",
        &FormatOptions::default().with_line_width(80),
    );
    insta::assert_snapshot!(out, @"
    SELECT *
    FROM orders
    WHERE customer_id = 1
        AND status = 'open'
        AND created_at >= '2024-01-01'
        AND region = 'us-east-1';
    ");
}

#[test]
fn long_window_specs_wrap_inside_over() {
    let out = format(
        "select sum(amount) over (partition by customer_id, account_id order by created_at desc, event_id) as running_total from orders",
        &FormatOptions::default().with_line_width(80),
    );
    insta::assert_snapshot!(out, @"
    SELECT
        sum(
            amount
        ) OVER (
            PARTITION BY customer_id, account_id
            ORDER BY created_at DESC, event_id
        ) AS running_total
    FROM orders;
    ");
}

#[test]
fn in_list_honors_a_magic_trailing_comma() {
    insta::assert_snapshot!(fmt("select * from t where x in (1, 2, 3,)"), @"
    SELECT *
    FROM t
    WHERE x IN (
        1,
        2,
        3,
    );
    ");
}

#[test]
fn in_list_stays_inline_without_a_trailing_comma() {
    assert_eq!(
        fmt("select * from t where x in (1, 2, 3)"),
        "SELECT *\nFROM t\nWHERE x IN (1, 2, 3);\n"
    );
}

#[test]
fn hierarchical_query_puts_start_with_and_connect_by_on_their_own_lines() {
    insta::assert_snapshot!(
        fmt("select id, name from emp start with manager_id is null \
             connect by prior id = manager_id"),
        @"
    SELECT id, name
    FROM emp
    START WITH manager_id IS NULL
    CONNECT BY PRIOR id = manager_id;
    "
    );
}

#[test]
fn in_subquery_stays_inline() {
    assert_eq!(
        fmt("select * from t where x in (select id from s)"),
        "SELECT *\nFROM t\nWHERE x IN (SELECT id FROM s);\n"
    );
}

#[test]
fn order_by_items_wrap_when_they_do_not_fit() {
    let out = format(
        "select * from t order by alpha, bravo desc, charlie nulls last",
        &FormatOptions::default().with_line_width(30),
    );
    insta::assert_snapshot!(out, @"
    SELECT *
    FROM t
    ORDER BY
        alpha,
        bravo DESC,
        charlie NULLS LAST;
    ");
}

#[test]
fn short_case_stays_on_one_line() {
    assert_eq!(
        fmt("select case when a then 1 else 2 end from t"),
        "SELECT CASE WHEN a THEN 1 ELSE 2 END\nFROM t;\n"
    );
}

#[test]
fn long_case_breaks_one_arm_per_line() {
    let out = format(
        "select case when a > 10 then 'big' when a > 0 then 'small' else 'zero' end as label from t",
        &FormatOptions::default().with_line_width(40),
    );
    insta::assert_snapshot!(out, @"
    SELECT
        CASE
            WHEN a > 10 THEN 'big'
            WHEN a > 0 THEN 'small'
            ELSE 'zero'
        END AS label
    FROM t;
    ");
}

#[test]
fn simple_case_keeps_its_operand() {
    assert_eq!(
        fmt("select case status when 1 then 'a' when 2 then 'b' end from t"),
        "SELECT CASE status WHEN 1 THEN 'a' WHEN 2 THEN 'b' END\nFROM t;\n"
    );
}

#[test]
fn cte_bodies_are_indented_and_one_per_line() {
    insta::assert_snapshot!(
        fmt("with a as (select x from t), b as (select y from u) select * from a"),
        @"
    WITH a AS (
        SELECT x
        FROM t
    ),
    b AS (
        SELECT y
        FROM u
    )
    SELECT *
    FROM a;
    ",
    );
}

#[test]
fn short_cte_stays_inline() {
    assert_eq!(
        fmt("with recursive r as (select 1) select * from r"),
        "WITH RECURSIVE r AS (SELECT 1)\nSELECT *\nFROM r;\n"
    );
}

#[test]
fn derived_table_subquery_is_indented() {
    insta::assert_snapshot!(
        fmt("select * from (select id from users where active) u"),
        @"
    SELECT *
    FROM (
        SELECT id
        FROM users
        WHERE active
    ) u;
    ",
    );
}

#[test]
fn set_operations_put_each_query_and_operator_on_its_own_line() {
    insta::assert_snapshot!(fmt("select a from t union all select a from u"), @"
    SELECT a
    FROM t
    UNION ALL
    SELECT a
    FROM u;
    ");
}

#[test]
fn chained_set_operations_flatten() {
    assert_eq!(
        fmt("select 1 union select 2 except select 3"),
        "SELECT 1\nUNION\nSELECT 2\nEXCEPT\nSELECT 3;\n"
    );
}

#[test]
fn insert_values_go_below_the_header() {
    assert_eq!(
        fmt("insert into t (a, b) values (1, 2), (3, 4)"),
        "INSERT INTO t (a, b)\nVALUES (1, 2), (3, 4);\n"
    );
}

#[test]
fn insert_select_puts_the_query_below() {
    assert_eq!(
        fmt("insert into t select a, b from u"),
        "INSERT INTO t\nSELECT a, b\nFROM u;\n"
    );
}

#[test]
fn update_set_and_where_each_on_their_own_line() {
    assert_eq!(
        fmt("update t set a = 1, b = a + 2 where id = 5"),
        "UPDATE t\nSET a = 1, b = a + 2\nWHERE id = 5;\n"
    );
}

#[test]
fn delete_where_goes_below() {
    assert_eq!(
        fmt("delete from t where x > 0"),
        "DELETE FROM t\nWHERE x > 0;\n"
    );
}

#[test]
fn merge_clauses_each_go_on_their_own_line() {
    insta::assert_snapshot!(
        fmt("merge into target t using source s on t.id = s.id when matched then update set t.v = s.v when not matched then insert (id, v) values (s.id, s.v)"),
        @"
    MERGE INTO target t
    USING source s
    ON t.id = s.id
    WHEN MATCHED THEN UPDATE SET t.v = s.v
    WHEN NOT MATCHED THEN INSERT (id, v) VALUES (s.id, s.v);
    ",
    );
}

#[test]
fn create_view_puts_the_query_after_as() {
    assert_eq!(
        fmt("create or replace view v as select a, b from t"),
        "CREATE OR REPLACE VIEW v AS\nSELECT a, b\nFROM t;\n"
    );
}

#[test]
fn create_table_as_select_is_a_ctas() {
    assert_eq!(
        fmt("create table t as select a from u"),
        "CREATE TABLE t AS\nSELECT a\nFROM u;\n"
    );
}

#[test]
fn create_table_column_defs_wrap_one_per_line() {
    let out = format(
        "create table t (id int, name varchar(100) not null)",
        &FormatOptions::default().with_line_width(30),
    );
    insta::assert_snapshot!(out, @"
    CREATE TABLE t (
        id int,
        name varchar(100) NOT NULL
    );
    ");
}

#[test]
fn drop_statement_is_inline() {
    assert_eq!(
        fmt("drop table if exists db.s.t"),
        "DROP TABLE IF EXISTS db.s.t;\n"
    );
}

#[test]
fn within_group_aggregate_is_kept() {
    assert_eq!(
        fmt("select listagg(x, ',') within group (order by x) from t"),
        "SELECT listagg(x, ',') WITHIN GROUP (ORDER BY x)\nFROM t;\n"
    );
}

#[test]
fn pivot_and_unpivot_are_kept() {
    assert_eq!(
        fmt("select * from t pivot (sum(amount) for month in ('jan', 'feb')) as p"),
        "SELECT *\nFROM t PIVOT (sum(amount) FOR month IN ('jan', 'feb')) AS p;\n"
    );
    assert_eq!(
        fmt("select * from sales unpivot (amount for quarter in (q1, q2))"),
        "SELECT *\nFROM sales UNPIVOT (amount FOR quarter IN (q1, q2));\n"
    );
}

#[test]
fn sql_procedure_body_is_recursively_formatted() {
    let src = "create or replace procedure p(x int) returns int language sql as $$\nbegin\n  return x;\nend\n$$";
    let out = fmt(src);
    assert_eq!(
        out,
        "CREATE OR REPLACE PROCEDURE p (x int) RETURNS int LANGUAGE SQL AS $$\nBEGIN\n    RETURN x;\nEND;\n$$;\n"
    );
    assert_eq!(fmt(&out), out, "not idempotent");
}

#[test]
#[cfg(feature = "embedded-javascript")]
fn javascript_routine_body_is_formatted_when_supported() {
    let src = "create function f() returns string language javascript as $$ return 'x'; $$";
    let out = fmt(src);
    assert_eq!(
        out,
        "CREATE FUNCTION f () RETURNS string LANGUAGE JAVASCRIPT AS $$\nreturn \"x\";\n$$;\n"
    );
    assert_eq!(fmt(&out), out);
}

#[test]
#[cfg(not(feature = "embedded-javascript"))]
fn javascript_routine_body_stays_verbatim_without_feature() {
    let src = "create function f() returns string language javascript as $$ return 'x'; $$";
    assert_eq!(
        fmt(src),
        "CREATE FUNCTION f () RETURNS string LANGUAGE JAVASCRIPT AS $$ return 'x'; $$;\n"
    );
}

#[test]
#[cfg(feature = "embedded-javascript")]
fn javascript_routine_body_preserves_template_literal_indentation() {
    let src = "create procedure p() returns string language javascript as $$ const sqlText = `\nSELECT\n    col\nFROM t\n`; return snowflake.createStatement({sqlText}).execute(); $$";
    let out = fmt(src);
    assert_eq!(
        out,
        "CREATE PROCEDURE p () RETURNS string LANGUAGE JAVASCRIPT AS $$\nconst sqlText = `\nSELECT\n    col\nFROM t\n`;\nreturn snowflake.createStatement({ sqlText }).execute();\n$$;\n"
    );
    assert_eq!(fmt(&out), out);
}

#[test]
#[cfg(feature = "embedded-javascript")]
fn documented_javascript_procedure_api_body_is_formatted() {
    let src = "create procedure stproc1() returns string not null language javascript as -- \"$$\" is delimiter\n$$ var statement = snowflake.createStatement({sqlText: \"select 1\"}); return statement.execute().next(); $$";
    let out = fmt(src);
    assert_eq!(
        out,
        "CREATE PROCEDURE stproc1 () RETURNS string NOT NULL LANGUAGE JAVASCRIPT AS -- \"$$\" is delimiter\n$$\nvar statement = snowflake.createStatement({ sqlText: \"select 1\" });\nreturn statement.execute().next();\n$$;\n"
    );
    assert_eq!(fmt(&out), out);
}

#[test]
#[cfg(all(feature = "embedded-javascript", feature = "embedded-python"))]
fn single_quoted_routine_bodies_are_formatted_when_safe() {
    let js = "create function f() returns string language javascript as ' return ''x''; '";
    let js_out = fmt(js);
    assert_eq!(
        js_out,
        "CREATE FUNCTION f () RETURNS string LANGUAGE JAVASCRIPT AS '\nreturn \"x\";\n';\n"
    );
    assert_eq!(fmt(&js_out), js_out);

    let py =
        "create function py_f() returns string language python as 'def f():\n    return ''ok'''";
    let py_out = fmt(py);
    assert_eq!(
        py_out,
        "CREATE FUNCTION py_f () RETURNS string LANGUAGE PYTHON AS '\ndef f():\n    return \"ok\"\n';\n"
    );
    assert_eq!(fmt(&py_out), py_out);

    let sql = "create procedure p() returns string language sql as 'begin return ''ok''; end'";
    let sql_out = fmt(sql);
    assert_eq!(
        sql_out,
        "CREATE PROCEDURE p () RETURNS string LANGUAGE SQL AS '\nBEGIN\n    RETURN ''ok'';\nEND;\n';\n"
    );
    assert_eq!(fmt(&sql_out), sql_out);
}

#[test]
#[cfg(feature = "embedded-brace-formatters")]
fn single_quoted_java_and_scala_bodies_are_formatted_when_safe() {
    let java =
        "create function jq() returns int language java as 'class C { public static int run() { return 1; } }'";
    let java_out = fmt(java);
    assert_eq!(
        java_out,
        "CREATE FUNCTION jq () RETURNS int LANGUAGE JAVA AS '\nclass C {\n    public static int run() {\n        return 1;\n    }\n}\n';\n"
    );
    assert_eq!(fmt(&java_out), java_out);

    let scala =
        "create function sq() returns int language scala as 'class C { def run(): Int = { 1 } }'";
    let scala_out = fmt(scala);
    assert_eq!(
        scala_out,
        "CREATE FUNCTION sq () RETURNS int LANGUAGE SCALA AS '\nclass C {\n    def run(): Int = {\n        1\n    }\n}\n';\n"
    );
    assert_eq!(fmt(&scala_out), scala_out);
}

#[test]
#[cfg(not(feature = "embedded-brace-formatters"))]
fn java_and_scala_bodies_stay_verbatim_without_brace_feature() {
    let java =
        "create function jq() returns int language java as 'class C { public static int run() { return 1; } }'";
    assert_eq!(
        fmt(java),
        "CREATE FUNCTION jq () RETURNS int LANGUAGE JAVA AS 'class C { public static int run() { return 1; } }';\n"
    );

    let scala = "create function sq() returns int language scala as $$ class C { def run(): Int = { 1 } } $$";
    assert_eq!(
        fmt(scala),
        "CREATE FUNCTION sq () RETURNS int LANGUAGE SCALA AS $$ class C { def run(): Int = { 1 } } $$;\n"
    );
}

#[test]
fn sql_expression_quoted_body_stays_verbatim() {
    let src = "create function add1(n float) returns float language sql as 'n + 1'";
    let out = fmt(src);
    assert_eq!(
        out,
        "CREATE FUNCTION add1 (n float) RETURNS float LANGUAGE SQL AS 'n + 1';\n"
    );
    assert_eq!(fmt(&out), out);
}

#[test]
#[cfg(feature = "embedded-javascript")]
fn invalid_javascript_routine_body_stays_verbatim() {
    let src = "create function f() returns string language javascript as $$ if ( $$";
    let out = fmt(src);
    assert_eq!(
        out,
        "CREATE FUNCTION f () RETURNS string LANGUAGE JAVASCRIPT AS $$ if ( $$;\n"
    );
    assert_eq!(fmt(&out), out);
}

#[test]
#[cfg(feature = "embedded-python")]
fn python_routine_body_is_formatted_when_supported() {
    let py = "create procedure py_p() returns string language python RUNTIME_VERSION = '3.12' PACKAGES = ('snowflake-snowpark-python') HANDLER = 'main' as $$\ndef main(session):\n    return 'ok'\n$$";
    let py_out = fmt(py);
    assert_eq!(
        py_out,
        "CREATE PROCEDURE py_p () RETURNS string LANGUAGE PYTHON RUNTIME_VERSION = '3.12' PACKAGES = ('snowflake-snowpark-python') HANDLER = 'main' AS $$\ndef main(session):\n    return \"ok\"\n$$;\n"
    );
    assert_eq!(fmt(&py_out), py_out);
}

#[test]
#[cfg(not(feature = "embedded-python"))]
fn python_routine_body_stays_verbatim_without_feature() {
    let src = "create function py_f() returns string language python as $$\ndef f():\n    return 'ok'\n$$";
    assert_eq!(
        fmt(src),
        "CREATE FUNCTION py_f () RETURNS string LANGUAGE PYTHON AS $$\ndef f():\n    return 'ok'\n$$;\n"
    );
}

#[test]
#[cfg(feature = "embedded-brace-formatters")]
fn java_and_scala_routine_bodies_are_formatted_when_supported() {
    let java = "create function java_f(x int) returns int language java HANDLER = 'C.run' as $$ class C { public static int run(int x) { return x + 1; } } $$";
    let java_out = fmt(java);
    assert_eq!(
        java_out,
        "CREATE FUNCTION java_f (\n    x int\n) RETURNS int LANGUAGE JAVA HANDLER = 'C.run' AS $$\nclass C {\n    public static int run(int x) {\n        return x + 1;\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&java_out), java_out);

    let java_table = "create or replace procedure java_tabular() returns table(id number, name string) language java runtime_version = '17' packages = ('com.snowflake:snowpark:latest') handler = 'Proc.run' target_path = '@stage/proc.jar' as $$ class Proc { public static com.snowflake.snowpark_java.DataFrame run(com.snowflake.snowpark_java.Session session) { return session.table(\"T\"); } } $$";
    let java_table_out = fmt(java_table);
    assert_eq!(
        java_table_out,
        "CREATE OR REPLACE PROCEDURE java_tabular () RETURNS TABLE (id number, name string) LANGUAGE JAVA RUNTIME_VERSION = '17' PACKAGES = ('com.snowflake:snowpark:latest') HANDLER = 'Proc.run' TARGET_PATH = '@stage/proc.jar' AS $$\nclass Proc {\n    public static com.snowflake.snowpark_java.DataFrame run(com.snowflake.snowpark_java.Session session) {\n        return session.table(\"T\");\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&java_table_out), java_table_out);

    let scala = "create procedure scala_p() returns string language scala RUNTIME_VERSION = '2.12' PACKAGES = ('com.snowflake:snowpark:latest') HANDLER = 'Main.run' as $$\nclass Main { def run(session: com.snowflake.snowpark.Session): String = \"ok\" }\n$$";
    let scala_out = fmt(scala);
    assert_eq!(
        scala_out,
        "CREATE PROCEDURE scala_p () RETURNS string LANGUAGE SCALA RUNTIME_VERSION = '2.12' PACKAGES = ('com.snowflake:snowpark:latest') HANDLER = 'Main.run' AS $$\nclass Main {\n    def run(session: com.snowflake.snowpark.Session): String = \"ok\"\n}\n$$;\n"
    );
    assert_eq!(fmt(&scala_out), scala_out);
}

#[test]
#[cfg(feature = "embedded-brace-formatters")]
fn java_and_scala_text_blocks_do_not_break_brace_formatting() {
    let java = "create function j() returns string language java as $$ class C { public static String run() { String sql = \"\"\"select { not_a_block }\"\"\"; return sql; } } $$";
    let java_out = fmt(java);
    assert_eq!(
        java_out,
        "CREATE FUNCTION j () RETURNS string LANGUAGE JAVA AS $$\nclass C {\n    public static String run() {\n        String sql = \"\"\"select { not_a_block }\"\"\";\n        return sql;\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&java_out), java_out);

    let scala = "create function s() returns string language scala as $$ class C { def run(): String = { val q = \"\"\"select } as text\"\"\"; q } } $$";
    let scala_out = fmt(scala);
    assert_eq!(
        scala_out,
        "CREATE FUNCTION s () RETURNS string LANGUAGE SCALA AS $$\nclass C {\n    def run(): String = {\n        val q = \"\"\"select } as text\"\"\";\n        q\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&scala_out), scala_out);
}

#[test]
#[cfg(feature = "embedded-brace-formatters")]
fn java_and_scala_char_literals_do_not_change_brace_depth() {
    let java = r#"create function j() returns string language java as $$ class C { public static char run() { char close = '}'; char quote = '\''; return close; } } $$"#;
    let java_out = fmt(java);
    assert_eq!(
        java_out,
        "CREATE FUNCTION j () RETURNS string LANGUAGE JAVA AS $$\nclass C {\n    public static char run() {\n        char close = '}';\n        char quote = '\\'';\n        return close;\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&java_out), java_out);

    let scala = r#"create function s() returns string language scala as $$ class C { def run(): Char = { val open = '{'; val slash = '\\'; open } } $$"#;
    let scala_out = fmt(scala);
    assert_eq!(
        scala_out,
        "CREATE FUNCTION s () RETURNS string LANGUAGE SCALA AS $$\nclass C {\n    def run(): Char = {\n        val open = '{';\n        val slash = '\\\\';\n        open\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&scala_out), scala_out);
}

#[test]
#[cfg(feature = "embedded-brace-formatters")]
fn java_and_scala_comments_do_not_break_brace_formatting() {
    let java = "create function j() returns int language java as $$ class C { // entry\n public static int run() { /* keep */ return 1; } } $$";
    let java_out = fmt(java);
    assert_eq!(
        java_out,
        "CREATE FUNCTION j () RETURNS int LANGUAGE JAVA AS $$\nclass C {\n    // entry\n    public static int run() {\n        /* keep */ return 1;\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&java_out), java_out);

    let scala = "create function s() returns int language scala as $$ class C { def run(): Int = { // value\n 1 } } $$";
    let scala_out = fmt(scala);
    assert_eq!(
        scala_out,
        "CREATE FUNCTION s () RETURNS int LANGUAGE SCALA AS $$\nclass C {\n    def run(): Int = {\n        // value\n        1\n    }\n}\n$$;\n"
    );
    assert_eq!(fmt(&scala_out), scala_out);
}

#[test]
fn invalid_python_java_and_scala_routine_bodies_stay_verbatim() {
    let py = "create function py_f() returns string language python as $$ def f(: $$";
    let py_out = fmt(py);
    assert_eq!(
        py_out,
        "CREATE FUNCTION py_f () RETURNS string LANGUAGE PYTHON AS $$ def f(: $$;\n"
    );
    assert_eq!(fmt(&py_out), py_out);

    let java = "create function java_f() returns string language java as $$ class C { $$";
    let java_out = fmt(java);
    assert_eq!(
        java_out,
        "CREATE FUNCTION java_f () RETURNS string LANGUAGE JAVA AS $$ class C { $$;\n"
    );
    assert_eq!(fmt(&java_out), java_out);

    let scala = "create function scala_f() returns string language scala as $$ class C { $$";
    let scala_out = fmt(scala);
    assert_eq!(
        scala_out,
        "CREATE FUNCTION scala_f () RETURNS string LANGUAGE SCALA AS $$ class C { $$;\n"
    );
    assert_eq!(fmt(&scala_out), scala_out);
}

#[test]
fn unquoted_scripting_body_is_formatted_as_a_routine_body() {
    let src = "create procedure p() returns string language sql as begin return 'x'; end";
    let out = fmt(src);
    assert_eq!(
        out,
        "CREATE PROCEDURE p () RETURNS string LANGUAGE SQL AS\nBEGIN\n    RETURN 'x';\nEND;\n"
    );
    assert_eq!(fmt(&out), out);
}

#[test]
fn session_set_and_execute_immediate_format_inline() {
    assert_eq!(
        fmt("set target_table = 'MART.X'"),
        "SET target_table = 'MART.X';\n"
    );
    assert_eq!(
        fmt("execute immediate 'insert into t values (1)' using (x)"),
        "EXECUTE IMMEDIATE 'insert into t values (1)' USING (x);\n"
    );
    assert_eq!(
        fmt("execute immediate $$select a,b from t$$ using (x)"),
        "EXECUTE IMMEDIATE $$\nSELECT a, b\nFROM t;\n$$ USING (x);\n"
    );
}

#[test]
fn grouping_sets_and_cube_are_kept() {
    assert_eq!(
        fmt("select a, count(*) from t group by grouping sets ((a, b), (c), ())"),
        "SELECT a, count(*)\nFROM t\nGROUP BY GROUPING SETS ((a, b), (c), ());\n"
    );
    assert_eq!(
        fmt("select a from t group by cube(a, b)"),
        "SELECT a\nFROM t\nGROUP BY cube(a, b);\n"
    );
}

#[test]
fn named_arguments_are_kept() {
    assert_eq!(
        fmt("select object_construct('a', 1, b => 2) from t"),
        "SELECT object_construct('a', 1, b => 2)\nFROM t;\n"
    );
}

#[test]
fn lateral_flatten_and_table_function_format() {
    assert_eq!(
        fmt("select f.value from t, lateral flatten(input => t.items) f"),
        "SELECT f.value\nFROM t, LATERAL FLATTEN(INPUT => t.items) f;\n"
    );
    assert_eq!(
        fmt("select * from table(flatten(input => parse_json(x)))"),
        "SELECT *\nFROM TABLE(FLATTEN(INPUT => parse_json(x)));\n"
    );
}

#[test]
fn is_distinct_from_is_kept() {
    assert_eq!(
        fmt("select * from t where a is distinct from b and c is not distinct from d"),
        "SELECT *\nFROM t\nWHERE a IS DISTINCT FROM b AND c IS NOT DISTINCT FROM d;\n"
    );
}

#[test]
fn semi_structured_path_keys_keep_their_case() {
    // `order` is a keyword but here it is a case-sensitive JSON key — it must not be up-cased.
    assert_eq!(
        fmt("select payload:order:status::string from t"),
        "SELECT payload:order:status::string\nFROM t;\n"
    );
}

#[test]
fn from_values_is_a_table_source() {
    assert_eq!(
        fmt("select c1 from values (1, 'a'), (2, 'b') as t(c1, c2)"),
        "SELECT c1\nFROM VALUES (1, 'a'), (2, 'b') AS t (c1, c2);\n"
    );
}

#[test]
fn tablesample_is_kept() {
    assert_eq!(
        fmt("select * from t tablesample bernoulli(25) repeatable(99)"),
        "SELECT *\nFROM t TABLESAMPLE BERNOULLI(25) REPEATABLE(99);\n"
    );
}

#[test]
fn pivot_value_aliases_are_kept() {
    assert_eq!(
        fmt("select * from sales pivot (sum(amt) for m in (1 as jan, 2 as feb)) p"),
        "SELECT *\nFROM sales PIVOT (sum(amt) FOR m IN (1 AS jan, 2 AS feb)) p;\n"
    );
}

#[test]
fn copy_into_puts_from_and_options_on_their_own_lines() {
    insta::assert_snapshot!(
        fmt("copy into raw.orders from @raw.stage/orders/ file_format = (type = json) on_error = continue"),
        @"
    COPY INTO raw.orders
    FROM @raw.stage/orders/
    FILE_FORMAT = (TYPE = json)
    ON_ERROR = continue;
    ",
    );
}

#[test]
fn asof_join_and_match_condition_are_kept() {
    assert_eq!(
        fmt("select * from q asof join t match_condition (q.ts >= t.ts) on q.sym = t.sym"),
        "SELECT *\nFROM q\nASOF JOIN t MATCH_CONDITION (q.ts >= t.ts) ON q.sym = t.sym;\n"
    );
}

#[test]
fn match_recognize_lays_out_one_clause_per_line() {
    insta::assert_snapshot!(
        fmt("select * from t match_recognize(partition by a order by b \
             measures match_number() as mn, first(price) as fp one row per match \
             after match skip past last row pattern(strt down+ up+) \
             define down as price < prev(price), up as price > prev(price))"),
        @"
    SELECT *
    FROM t MATCH_RECOGNIZE (
        PARTITION BY a
        ORDER BY b
        MEASURES match_number() AS mn, first(price) AS fp
        ONE ROW PER MATCH
        AFTER MATCH SKIP PAST LAST ROW
        PATTERN (strt down+ up+)
        DEFINE down AS price < prev(price), up AS price > prev(price)
    );
    "
    );
}

#[test]
fn flow_operator_chains_statements_one_step_per_line() {
    insta::assert_snapshot!(
        fmt("select a, b from t ->> select a from $1 where b > 0 ->> select count(*) from $1"),
        @"
    SELECT a, b
    FROM t
    ->> SELECT a
    FROM $1
    WHERE b > 0
    ->> SELECT count(*)
    FROM $1;
    "
    );
}

#[test]
fn flow_operator_allows_show_statement_chains() {
    assert_eq!(
        fmt("show tables in schema db.s ->> select \"name\" from $1 where \"kind\" = 'TABLE'"),
        "SHOW TABLES IN SCHEMA db.s\n->> SELECT \"name\"\nFROM $1\nWHERE \"kind\" = 'TABLE';\n"
    );
}

#[test]
fn changes_clause_attaches_to_its_table() {
    assert_eq!(
        fmt("select * from t changes(information => default) at(timestamp => 'x')"),
        "SELECT *\nFROM t CHANGES (information => default) AT (TIMESTAMP => 'x');\n"
    );
}

#[test]
fn keywords_used_as_function_names_are_callable() {
    // FIRST/LAST/LEFT are reserved words elsewhere but here name functions: keep them lower-case
    // and hugging their parens, like any other call.
    assert_eq!(
        fmt("select first(price), last(price), left(s, 2) from t"),
        "SELECT first(price), last(price), left(s, 2)\nFROM t;\n"
    );
}

#[test]
fn multi_table_insert_first_puts_each_branch_on_its_own_line() {
    insta::assert_snapshot!(
        fmt("insert first when sev >= 9 then into high else into low select sev from events"),
        @"
    INSERT FIRST
    WHEN sev >= 9 THEN INTO high
    ELSE
    INTO low
    SELECT sev
    FROM events;
    ",
    );
}

#[test]
fn time_travel_at_before_is_kept() {
    assert_eq!(
        fmt("select * from orders before (statement => 'abc') o"),
        "SELECT *\nFROM orders BEFORE (STATEMENT => 'abc') o;\n"
    );
}

#[test]
fn contextual_keywords_stay_identifiers_outside_their_clause() {
    // `at`/`before`/`asof`/`grouping`/`sets` are soft keywords: up-cased only where the grammar
    // recognizes them as a clause, and ordinary (lowercase) identifiers everywhere else.
    assert_eq!(
        fmt("select asof, at, before, grouping, sets from t"),
        "SELECT asof, at, before, grouping, sets\nFROM t;\n"
    );
}

#[test]
fn group_by_all_stays_inline() {
    assert_eq!(
        fmt("select a from t group by all"),
        "SELECT a\nFROM t\nGROUP BY ALL;\n"
    );
}

#[test]
fn empty_input_formats_to_empty() {
    assert_eq!(fmt(""), "");
    assert_eq!(fmt("   \n\t "), "");
}

#[test]
fn unary_minus_does_not_get_a_trailing_space() {
    assert_eq!(
        fmt("select -1, a - b from t"),
        "SELECT -1, a - b\nFROM t;\n"
    );
}

#[test]
fn statements_with_comments_keep_them() {
    let src = "select /* keep me */ a from t -- trailing note\n";
    let out = fmt(src);
    assert!(out.contains("/* keep me */"), "block comment lost: {out:?}");
    assert!(
        out.contains("-- trailing note"),
        "line comment lost: {out:?}"
    );
}

#[test]
fn leading_comment_sits_on_its_own_line() {
    let out = fmt("-- header\nselect a from t");
    assert!(
        out.starts_with("-- header\n"),
        "leading comment misplaced: {out:?}"
    );
    assert!(out.contains("FROM t;"), "{out:?}");
}

#[test]
fn banner_comment_does_not_explode_the_select_list() {
    // A statement-level leading comment is hoisted above the header group, so the list stays inline.
    assert_eq!(
        fmt("-- header\nselect a, b, c from t"),
        "-- header\nSELECT a, b, c\nFROM t;\n"
    );
}

#[test]
fn trailing_line_comment_attaches_to_its_column() {
    // A `--` comment after a column (even after the comma) trails that column's line, and forces
    // the list to break so the comment ends its line.
    insta::assert_snapshot!(fmt("select a, -- first\n b from t"), @"
    SELECT
        a, -- first
        b
    FROM t;
    ");
}

#[test]
fn inline_block_comment_stays_inline() {
    assert_eq!(
        fmt("select a /* note */ + b from t"),
        "SELECT a /* note */ + b\nFROM t;\n"
    );
}

#[test]
fn comment_only_input_is_not_dropped() {
    let out = fmt("-- just a note\n");
    assert!(
        out.contains("-- just a note"),
        "comment-only input lost: {out:?}"
    );
}

#[test]
fn comments_are_never_dropped_on_the_embedded_corpus() {
    for case in EASY_CASES {
        for (label, sql) in case.sqls() {
            let out = fmt(sql);
            for comment in comment_texts(sql) {
                assert!(
                    out.contains(&comment),
                    "comment {comment:?} dropped for {}/{label}\n--- out ---\n{out}",
                    case.name
                );
            }
        }
    }
}

#[test]
fn is_idempotent_on_the_embedded_corpus() {
    for case in EASY_CASES {
        for (label, sql) in case.sqls() {
            let once = fmt(sql);
            let twice = fmt(&once);
            assert_eq!(
                once, twice,
                "formatting is not idempotent for {}/{label}",
                case.name
            );
        }
    }
}

#[test]
fn preserves_significant_tokens_on_the_embedded_corpus() {
    for case in EASY_CASES {
        for (label, sql) in case.sqls() {
            let out = fmt(sql);
            let name = case.name;
            assert_eq!(
                significant_kinds(sql),
                significant_kinds(&out),
                "formatting changed the significant tokens for {name}/{label}\n--- in ---\n{sql}\n--- out ---\n{out}",
            );
        }
    }
}

#[test]
fn clean_input_stays_clean_after_formatting() {
    for case in EASY_CASES {
        for (label, sql) in case.sqls() {
            if !parse(sql).errors().is_empty() {
                continue; // only assert about inputs the parser already accepts
            }
            let out = fmt(sql);
            let name = case.name;
            assert!(
                parse(&out).errors().is_empty(),
                "formatting introduced parse errors for {name}/{label}\n--- out ---\n{out}",
            );
        }
    }
}

#[test]
fn lexically_invalid_input_is_verbatim() {
    for sql in ["'", "\"", "$$body", "SELECT 'oops"] {
        assert_eq!(fmt(sql), sql);
    }
}

#[test]
fn multiline_tokens_with_line_trailing_space_are_verbatim() {
    let sql = "$$ \n$$ ";
    assert_eq!(fmt(sql), sql);
}

#[test]
fn statement_end_comments_format_idempotently() {
    let once = fmt("desc t -- c\nx");
    assert_eq!(once, "DESC t -- c\nx;\n");
    assert_eq!(fmt(&once), once);
}

#[test]
fn line_comment_before_structured_child_formats_idempotently() {
    let once = fmt("'abc' //* b */$$\n(a)$$ $$$$$$ ");
    assert_eq!(once, "'abc' //* b */$$\n(a);\n$$ $$;\n$$$$;\n");
    assert_eq!(fmt(&once), once);
}

#[test]
fn statement_end_directives_stay_on_the_code_line() {
    let noqa = fmt("select a -- noqa: LT01");
    assert_eq!(noqa, "SELECT a; -- noqa: LT01\n");
    assert_eq!(fmt(&noqa), noqa);

    let out_fmt = fmt("select a -- sql-dialect-fmt: skip");
    assert_eq!(out_fmt, "SELECT a; -- sql-dialect-fmt: skip\n");
    assert_eq!(fmt(&out_fmt), out_fmt);
}

#[test]
fn mid_statement_leading_comment_starts_on_its_own_line() {
    let once = fmt("create select select\n-- y\nselect ");
    assert_eq!(once, "CREATE SELECT SELECT\n-- y\nSELECT;\n");
    assert_eq!(fmt(&once), once);
}