ST02.yml

rule: ST02

test_pass_case_cannot_be_reduced_1:
  pass_str: |
    select
        fab > 0 as is_fab
    from fancy_table
test_pass_case_cannot_be_reduced_2:
  pass_str: |
    select
        case when fab > 0 then true end as is_fab
    from fancy_table
test_pass_case_cannot_be_reduced_3:
  pass_str: |
    select
        case when fab is not null then false end as is_fab
    from fancy_table
test_pass_case_cannot_be_reduced_4:
  pass_str: |
    select
        case when fab > 0 then true else true end as is_fab
    from fancy_table
test_pass_case_cannot_be_reduced_5:
  pass_str: |
    select
        case when fab <> 0 then 'just a string' end as fab_category
    from fancy_table
test_pass_case_cannot_be_reduced_6:
  pass_str: |
    select
        case
          when fab <> 0 then true
          when fab < 0 then 'not a bool'
        end as fab_category
    from fancy_table
test_pass_case_cannot_be_reduced_7:
  pass_str: |
    select
        foo,
        case
            when
                bar is null then bar
            else '123'
        end as test
    from baz;
test_pass_case_cannot_be_reduced_8:
  pass_str: |
    select
        foo,
        case
            when
                bar is not null then '123'
            else bar
        end as test
    from baz;
test_pass_case_cannot_be_reduced_9:
  pass_str: |
    select
        foo,
        case
            when
                bar is not null then '123'
            when
                foo is not null then '456'
            else bar
        end as test
    from baz;
test_pass_case_cannot_be_reduced_10:
  pass_str: |
    select
        foo,
        case
            when
                bar is not null and abs(foo) > 0 then '123'
            else bar
        end as test
    from baz;
test_pass_case_cannot_be_reduced_11:
  pass_str: |
    SELECT
        dv_runid,
        CASE
            WHEN LEAD(dv_startdateutc) OVER (
                PARTITION BY rowid ORDER BY dv_startdateutc
            ) IS NULL
            THEN 1
            ELSE 0
        END AS loadstate
    FROM d;
test_pass_case_cannot_be_reduced_12:
  pass_str: |
    select
        field_1,
        field_2,
        field_3,
        case
            when coalesce(field_2, field_3) is null then 1 else 0
        end as field_4
    from my_table;
test_pass_case_cannot_be_reduced_13:
  pass_str: |
    SELECT
        CASE
            WHEN item.submitted_timestamp IS NOT NULL
            THEN item.sitting_id
        END
  configs:
    core:
      dialect: postgres
test_fail_unnecessary_case_1:
  fail_str: |
    select
        case
            when fab > 0 then true else false end as is_fab
    from fancy_table
  fix_str: |
    select
        coalesce(fab > 0, false) as is_fab
    from fancy_table
test_fail_unnecessary_case_2:
  fail_str: |
    select
        case
            when fab > 0 then false else true end as is_fab
    from fancy_table
  fix_str: |
    select
        not coalesce(fab > 0, false) as is_fab
    from fancy_table
test_fail_unnecessary_case_3:
  fail_str: |
    select
        case
            when fab > 0 and tot > 0 then true else false end as is_fab
    from fancy_table
  fix_str: |
    select
        coalesce(fab > 0 and tot > 0, false) as is_fab
    from fancy_table
test_fail_unnecessary_case_4:
  fail_str: |
    select
        case
            when fab > 0 and tot > 0 then false else true end as is_fab
    from fancy_table
  fix_str: |
    select
        not coalesce(fab > 0 and tot > 0, false) as is_fab
    from fancy_table
test_fail_unnecessary_case_5:
  fail_str: |
    select
        case
            when not fab > 0 or tot > 0 then false else true end as is_fab
    from fancy_table
  fix_str: |
    select
        not coalesce(not fab > 0 or tot > 0, false) as is_fab
    from fancy_table
test_fail_unnecessary_case_6:
  fail_str: |
    select
        subscriptions_xf.metadata_migrated,

        case  -- BEFORE ST02 FIX
            when perks.perk is null then false
            else true
        end as perk_redeemed,

        perks.received_at as perk_received_at

    from subscriptions_xf
  fix_str: |
    select
        subscriptions_xf.metadata_migrated,

        not coalesce(perks.perk is null, false) as perk_redeemed,

        perks.received_at as perk_received_at

    from subscriptions_xf
test_fail_unnecessary_case_7:
  fail_str: |
    select
        foo,
        case
            when
                bar is null then '123'
            else bar
        end as test
    from baz;
  fix_str: |
    select
        foo,
        coalesce(bar, '123') as test
    from baz;
test_fail_unnecessary_case_8:
  fail_str: |
    select
        foo,
        case
            when
                bar is not null then bar
            else '123'
        end as test
    from baz;
  fix_str: |
    select
        foo,
        coalesce(bar, '123') as test
    from baz;
test_fail_unnecessary_case_9:
  fail_str: |
    select
        foo,
        case
            when
                bar is null then null
            else bar
        end as test
    from baz;
  fix_str: |
    select
        foo,
        bar as test
    from baz;
test_fail_unnecessary_case_10:
  fail_str: |
    select
        foo,
        case
            when
                bar is not null then bar
            else null
        end as test
    from baz;
  fix_str: |
    select
        foo,
        bar as test
    from baz;
test_fail_unnecessary_case_11:
  fail_str: |
    select
        foo,
        case
            when
                bar is not null then bar
        end as test
    from baz;
  fix_str: |
    select
        foo,
        bar as test
    from baz;

test_fail_no_copy_code_out_of_template:
  # The rule wants to replace the case statement with coalesce(), but
  # LintFix.has_template_conflicts() correctly prevents it copying code out
  # of the templated region. Hence, the query is not modified.
  fail_str: |
    select
        foo,
        case
            when
                bar is null then {{ result }}
            else bar
        end as test
    from baz;
  configs:
    core:
      ignore_templated_areas: false
    templater:
      jinja:
        context:
          result: "'123'"