LT01-excessive.yml

rule: LT01

test_basic:
  pass_str: SELECT 1

test_basic_template:
  pass_str: |
    {{ 'SELECT 1' }}
  configs:
    core:
      ignore_templated_areas: false

test_basic_fix:
  fail_str: SELECT     1
  fix_str: SELECT 1

test_basic_fail_template:
  fail_str: |
    {{ 'SELECT     1' }}
  configs:
    core:
      ignore_templated_areas: false

test_simple_fix:
  fail_str: |
    select
        1 + 2     + 3     + 4        -- Comment
    from     foo
  fix_str: |
    select
        1 + 2 + 3 + 4        -- Comment
    from foo

test_identifier_fix:
  fail_str: |
    SELECT [thistable] . [col]
    FROM [thisdatabase] . [thisschema]
            . [thistable]
  fix_str: |
    SELECT [thistable].[col]
    FROM [thisdatabase].[thisschema].[thistable]
  configs:
    core:
      dialect: tsql

test_comparison_operator_fix:
  fail_str: |
    SELECT foo
    FROM bar
    WHERE baz > = 10;
  fix_str: |
    SELECT foo
    FROM bar
    WHERE baz >= 10;
  configs:
    core:
      dialect: tsql

test_comparison_operator_pass:
  pass_str: |
    SELECT foo
    FROM bar
    WHERE baz >= 10;
  configs:
    core:
      dialect: tsql

test_casting_operator_fix:
  fail_str: |
    SELECT '1' :: INT;
  fix_str: |
    SELECT '1'::INT;
  configs:
    core:
      dialect: postgres

test_casting_operator_pass:
  pass_str: |
    SELECT '1'::INT;
  configs:
    core:
      dialect: postgres

test_fix_tsql_spaced_chars:
  fail_str: |
    SELECT col1 FROM table1 WHERE 1 > = 1
  fix_str: |
    SELECT col1 FROM table1 WHERE 1 >= 1
  configs:
    core:
      dialect: tsql

# Check CASE Statement parses with newlines properly
# See https://github.com/sqlfluff/sqlfluff/issues/2495
test_pass_postgres_case_statement:
  pass_str: |
    SELECT
        a,
        CASE
            WHEN 1 THEN 'one'
            WHEN 2 THEN 'two'
            ELSE 'other'
        END AS b
    FROM test;
  configs:
    core:
      dialect: postgres

test_excess_space_cast:
  fail_str: |
    select
        '1'    ::   INT as id1,
        '2'::int as id2
    from table_a
  fix_str: |
    select
        '1'::INT as id1,
        '2'::int as id2
    from table_a

test_redshift_at_time_zone:
  pass_str: |
    SELECT
    date_w_tz[0] AT TIME ZONE 'Etc/UTC' AS bar
    FROM foo
  configs:
    core:
      dialect: redshift

test_excess_space_without_align_alias:
  fail_str: |
        SELECT
            a    AS first_column,
            b      AS second_column,
            (a + b) / 2 AS third_column
        FROM foo
  fix_str: |
    SELECT
        a AS first_column,
        b AS second_column,
        (a + b) / 2 AS third_column
    FROM foo
  configs:
    # This is the default config but we're being explicit
    # here for testing.
    layout:
      type:
        alias_expression:
          spacing_before: single

test_excess_space_with_align_alias:
  # NOTE: The config here shouldn't move the table alias
  fail_str: |
        SELECT
            a    AS first_column,
            b      AS second_column,
            (a + b) / 2 AS third_column
        FROM foo   AS bar
  fix_str: |
    SELECT
        a           AS first_column,
        b           AS second_column,
        (a + b) / 2 AS third_column
    FROM foo AS bar
  configs: &align_alias
    layout:
      type:
        alias_expression:
          spacing_before: align
          align_within: select_clause
          align_scope: bracketed

test_missing_keyword_with_align_alias:
  fail_str: |
        SELECT
            a    first_column,
            b      AS second_column,
            (a + b) / 2 AS third_column
        FROM foo
  fix_str: |
    SELECT
        a           first_column,
        b           AS second_column,
        (a + b) / 2 AS third_column
    FROM foo
  configs: *align_alias

test_skip_alias_with_align_alias:
  fail_str: |
        SELECT
            a   ,
            b   ,
            (a   +   b) /   2
        FROM foo
  fix_str: |
    SELECT
        a,
        b,
        (a + b) / 2
    FROM foo
  configs: *align_alias

test_excess_space_with_align_alias_wider:
  # NOTE: The config here SHOULD move the table alias
  # NOTE: The combined LT01 also fixes the missing space
  # between `USING` and `(a)`.
  fail_str: |
    SELECT
        a    AS first_column,
        b      AS second_column,
        (a      +      b)      /      2 AS third_column
    FROM foo   AS first_table
    JOIN my_tbl AS second_table USING(a)
  fix_str: |
    SELECT
        a           AS first_column,
        b           AS second_column,
        (a + b) / 2 AS third_column
    FROM foo        AS first_table
    JOIN my_tbl     AS second_table USING (a)
  configs: &align_alias_wider
    layout:
      type:
        alias_expression:
          spacing_before: align
          align_within: select_statement
          align_scope: bracketed

test_align_alias_boundary:
  # The alias inside the expression shouldn't move.
  fail_str: |
    SELECT
        a    AS first_column,
        (SELECT b AS c)      AS second_column
  fix_str: |
    SELECT
        a               AS first_column,
        (SELECT b AS c) AS second_column
  configs: *align_alias

test_align_alias_inline_pass:
  # The aliases on the same line shouldn't panic.
  pass_str: SELECT a AS b, c AS d FROM tbl
  configs: *align_alias

test_align_alias_inline_fail:
  # The aliases on the same line shouldn't panic.
  fail_str: SELECT a   AS   b  ,   c   AS   d    FROM tbl
  fix_str: SELECT a AS b, c AS d FROM tbl
  configs: *align_alias

test_pass_snowflake_semi_structured:
  pass_str: "SELECT to_array(a.b:c) FROM d"
  configs:
    core:
      dialect: snowflake

test_fail_snowflake_semi_structured_single:
  fail_str: |
    SELECT
      to_array(a.b : c) as d,
      e : f : g::string as h
    FROM j
  fix_str: |
    SELECT
      to_array(a.b:c) as d,
      e:f:g::string as h
    FROM j
  configs:
    core:
      dialect: snowflake

test_fail_snowflake_semi_structured_multi:
  fail_str: |
    SELECT
      to_array(a.b    :    c) as d,
      e    :    f    :    g::string as h
    FROM j
  fix_str: |
    SELECT
      to_array(a.b:c) as d,
      e:f:g::string as h
    FROM j
  configs:
    core:
      dialect: snowflake

test_pass_bigquery_specific:
  # Test a selection of bigquery specific spacings work.
  # Specifically EXCEPT & qualified functions.
  pass_str: |
    SELECT * EXCEPT (order_id);
    SELECT NET.HOST(LOWER(url)) AS host FROM urls;
  configs:
    core:
      dialect: bigquery

test_pass_bigquery_specific_arrays_1:
  # An example of _no whitespace_ after an array type
  pass_str: |
    SELECT ARRAY<FLOAT64>[1, 2, 3] AS floats;
  configs:
    core:
      dialect: bigquery

test_pass_bigquery_specific_arrays_2:
  # An example of _whitespace_ after an array type
  pass_str: |
    CREATE TEMPORARY FUNCTION DoSomething(param1 STRING, param2 STRING)
    RETURNS ARRAY<STRING> LANGUAGE js AS """Some JS""";

    SELECT DoSomething(col1) FROM table1
  configs:
    core:
      dialect: bigquery

test_pass_bigquery_array_function:
  # Test spacing of Array Generator function brackets
  pass_str: |
    SELECT ARRAY(SELECT 1 FROM table1);
  configs:
    core:
      dialect: bigquery

test_pass_bigquery_specific_structs:
  # Test spacing of complex STRUCT brackets
  pass_str: |
    create table testing.array_struct_tbl (
        address_array_of_nested_structs
        ARRAY<STRUCT<coll STRUCT<col1_1 STRING, col1_2 INT64>, col2 STRING>>
    )
  configs:
    core:
      dialect: bigquery

test_pass_bigquery_specific_struct_access:
  # Test spacing of function access
  pass_str: |
    SELECT
      testFunction(a).b AS field,
      testFunction(a).* AS wildcard,
      testFunction(a).b.c AS field_with_field,
      testFunction(a).b.* AS field_with_wildcard,
      testFunction(a)[OFFSET(0)].* AS field_with_offset_wildcard,
      testFunction(a)[SAFE_OFFSET(0)].* AS field_with_safe_offset_wildcard,
      testFunction(a)[ORDINAL(1)].* AS field_with_ordinal_wildcard,
      testFunction(a)[ORDINAL(1)].a AS field_with_ordinal_field
    FROM table1
  configs:
    core:
      dialect: bigquery

test_pass_bigquery_struct_function_no_spaces:
  # Test struct function does not flag for missing spaces
  # e.g. doesn't flag `STRUCT()` as should be `STRUCT ()`
  pass_str: |
    SELECT
      TO_JSON(STRUCT()),
      TO_JSON(STRUCT(1, 2, 3)),
      STRUCT(1, 2, 3)
    FROM
      table1
  configs:
    core:
      dialect: bigquery

test_postgres_datatype:
  # https://github.com/sqlfluff/sqlfluff/issues/4521
  # https://github.com/sqlfluff/sqlfluff/issues/4565
  pass_str: |
    select
        1::NUMERIC(3, 1),
        2::double precision,
        '2020-01-01'::timestamp with time zone,
        'foo'::character varying,
        B'10101'::bit(3),
        B'10101'::bit varying(3),
        B'10101'::bit varying
  configs:
    core:
      dialect: postgres

test_redshift_datatype:
  pass_str: |
    select
        1::NUMERIC(3, 1),
        2::double precision,
        '2020-01-01'::timestamp with time zone,
        'foo'::character varying,
        'foo'::character varying(MAX),
        'foo'::character varying(255),
        '10101'::binary varying(6)
  configs:
    core:
      dialect: redshift

test_bigquery_datatype:
  pass_str: |
      select 1::NUMERIC(3, 1)
  configs:
    core:
      dialect: bigquery

test_athena_datatype:
  pass_str: |
      select
          1::DECIMAL(3, 1),
          'foo'::VARCHAR(4),
          'bar'::CHAR(3),
          col1::STRUCT<foo: int>,
          col2::ARRAY<int>,
          '2020-01-01'::timestamp with time zone
  configs:
    core:
      dialect: athena

test_hive_datatype:
  pass_str: |
      select
          1::DECIMAL(3, 1),
          1::DEC(3, 1),
          1::NUMERIC(3, 1),
          col1::STRUCT<foo: int>,
          col2::ARRAY<int>,
          col3::ARRAY<int>[4]
  configs:
    core:
      dialect: hive

test_sqlite_datatype:
  pass_str: |
      select
          1::double precision,
          1::DECIMAL(10, 5),
          1::unsigned big int,
          'foo'::varying character(255),
          'foo'::character(20),
          'foo'::nvarchar(200)
  configs:
    core:
      dialect: sqlite

test_sparksql_datatype:
  pass_str: |
      select
          1::DECIMAL(3, 1),
          1::DEC(3, 1),
          1::NUMERIC(3, 1),
          'bar'::CHAR(3),
          col1::STRUCT<foo: int>,
          col2::ARRAY<int>
  configs:
    core:
      dialect: sparksql

test_exasol_datatype:
  pass_str: |
      select
          1::double precision,
          1::DECIMAL(3, 1),
          1::NUMERIC(3, 1),
          'bar'::VARCHAR(2000 CHAR),
          col1::INTERVAL DAY(2) TO SECOND(1)
  configs:
    core:
      dialect: exasol

test_teradata_datatype:
  pass_str: |
      select
          1::DECIMAL(3, 1),
          1::DEC(3, 1),
          1::NUMERIC(3, 1),
          'bar'::CHAR(3)
  configs:
    core:
      dialect: teradata

test_tsql_datatype:
  pass_str: |
      select
          1::DECIMAL(3, 1),
          1::DEC(3, 1),
          1::NUMERIC(3, 1),
          'bar'::character varying(3)
  configs:
    core:
      dialect: tsql