RF03.yml

rule: RF03

# Mixed qualification of references.
test_fail_single_table_mixed_qualification_of_references:
  fail_str: SELECT my_tbl.bar, baz FROM my_tbl
  fix_str: SELECT my_tbl.bar, my_tbl.baz FROM my_tbl

test_pass_single_table_consistent_references_1:
  pass_str: SELECT bar FROM my_tbl

test_pass_single_table_consistent_references_2:
  pass_str: SELECT my_tbl.bar FROM my_tbl

test_pass_on_tableless_table:
  # tests particular code branch for coverage
  pass_str: SELECT (SELECT MAX(bar) FROM tbl) + 1 AS col

test_fail_single_table_mixed_qualification_of_references_subquery:
  # NOTE: Even though there's a subquery here, we can still fix it
  # because there is no ambiguity about which table we're referencing.
  fail_str: SELECT * FROM (SELECT my_tbl.bar, baz FROM my_tbl)
  fix_str: SELECT * FROM (SELECT my_tbl.bar, my_tbl.baz FROM my_tbl)

test_pass_lateral_table_ref:
  pass_str: |
    SELECT
      tbl.a,
      tbl.b,
      tbl.a + tbl.b AS col_created_right_here,
      col_created_right_here + 1 AS sub_self_ref
    FROM tbl

test_pass_single_table_consistent_references_1_subquery:
  pass_str: SELECT * FROM (SELECT bar FROM my_tbl)

test_pass_single_table_consistent_references_2_subquery:
  pass_str: SELECT * FROM (SELECT my_tbl.bar FROM my_tbl)

test_fail_single_table_reference_when_unqualified_config:
  fail_str: SELECT my_tbl.bar FROM my_tbl
  fix_str: SELECT bar FROM my_tbl
  configs:
    rules:
      references.consistent:
        single_table_references: unqualified

test_fail_single_table_reference_when_qualified_config:
  fail_str: SELECT bar FROM my_tbl WHERE foo
  fix_str: SELECT my_tbl.bar FROM my_tbl WHERE my_tbl.foo
  configs:
    rules:
      references.consistent:
        single_table_references: qualified

test_pass_single_table_reference_in_subquery:
  # Catch issues with subqueries properly
  pass_str: |
    SELECT * FROM db.sc.tbl2
    WHERE a NOT IN (SELECT a FROM db.sc.tbl1)

test_value_table_functions_do_not_require_qualification:
  pass_str: |
    select
        a.*,
        _t_start
    from a
    left join unnest(generate_timestamp_array(
            '2020-01-01', '2020-01-30', interval 1 day)) as _t_start
        on true
  configs:
    core:
      dialect: bigquery
    rules:
      references.consistent:
        force_enable: true

test_object_references_1a:
  # This should fail as "a" is an unreferenced object
  # We don't try to be smart.
  fail_str: SELECT a.bar, b FROM my_tbl
  fix_str: SELECT a.bar, my_tbl.b FROM my_tbl

test_object_references_1b:
  # This should not-fail as "a" is potentially a STRUCT
  pass_str: SELECT a.bar, b FROM my_tbl
  configs:
    core:
      dialect: bigquery

test_object_references_1c:
  # This should fail as even though "a" is potenitally a STRUCT
  # The config has been set to enforce the rule
  fail_str: SELECT a.bar, b FROM my_tbl AS c
  fix_str: SELECT c.a.bar, c.b FROM my_tbl AS c
  configs:
    core:
      dialect: bigquery
    rules:
      references.consistent:
        force_enable: true
        single_table_references: qualified

test_object_references_1d:
  # "a" is not a named table and therefore is a STRUCT
  pass_str: SELECT a.bar, b FROM my_tbl
  configs:
    core:
      dialect: bigquery
    rules:
      references.consistent:
        force_enable: true

test_object_references_1e:
  pass_str: SELECT my_tbl.a.bar, my_tbl.b FROM my_tbl
  configs:
    core:
      dialect: bigquery
    rules:
      references.consistent:
        force_enable: true

test_object_references_struct_inconsistent_fix_a:
  fail_str: SELECT a.bar, my_tbl.b FROM my_tbl
  fix_str: SELECT my_tbl.a.bar, my_tbl.b FROM my_tbl
  configs:
    core:
      dialect: bigquery
    rules:
      references.consistent:
        force_enable: true

test_object_references_1f:
  # This should not-fail as "a" is potentially a STRUCT
  pass_str: SELECT a.bar, b FROM my_tbl
  configs:
    core:
      dialect: hive

test_object_references_1g:
  # This should not-fail as "a" is potentially a STRUCT
  pass_str: SELECT a.bar, b FROM my_tbl
  configs:
    core:
      dialect: redshift

test_tsql_pivot_are_excluded:
  # This should pass as tsql PIVOT columns do not need to be
  # qualified
  pass_str: |
    select
      t1._id
      , [1] as lvl_1
      , [2] as lvl_2
      , [3] as lvl_3
    from
      table1 t1
    pivot
      (max(value) for rn in([1], [2], [3]) ) as pvt
  configs:
    core:
      dialect: tsql


test_date_functions_are_excluded:
  # This should pass as date keywords columns do not need to be
  # qualified
  pass_str: |
    SELECT
        a.[hello],
        DATEDIFF(day, a.[mydate], GETDATE()) AS [test]
    FROM
        mytable AS a
  configs:
    core:
      dialect: tsql

test_select_alias_in_where_clause_1:
  # This should pass for certain dialects
  pass_str: |
    select
        t.col0,
        t.col1 + 1 as alias_col1
    from table1 as t
    where alias_col1 > 5
  configs:
    core:
      dialect: redshift

test_select_alias_in_where_clause_2:
  # This should pass for certain dialects
  pass_str: |
    select
        t.col0,
        t.col1 + 1 as alias_col1
    from table1 as t
    where alias_col1 > 5
  configs:
    core:
      dialect: snowflake

test_pass_group_by_alias:
  pass_str: |
    select
        t.col1 + 1 as alias_col1,
        count(1)
    from table1 as t
    group by alias_col1

test_pass_order_by_alias:
  pass_str: |
    select
        t.col0,
        t.col1 + 1 as alias_col1
    from table1 as t
    order by alias_col1

test_pass_having:
  pass_str: |
    select
        t.col0,
        t.col1 + 1 as alias_col1
    from table1 as t
    having alias_col1 > 5

test_fail_select_alias_in_where_clause_5:
  # This should fail for ansi (and be fixable)
  fail_str: |
    select
        t.col0,
        t.col1 + 1 as alias_col1
    from table1 as t
    where alias_col1 > 5
  fix_str: |
    select
        col0,
        col1 + 1 as alias_col1
    from table1 as t
    where alias_col1 > 5
  configs:
    rules:
      references.consistent:
        single_table_references: unqualified

test_pass_tsql_parameter:
  # This should pass for certain dialects
  pass_str: |
    DECLARE @id_date int;
    SET @id_date = 20211108;

    SELECT sometable.some_column
    FROM sometable
    WHERE 1 = 1
        AND sometable.column_with_date = @id_date
  configs:
    core:
      dialect: tsql

test_pass_tsql_pivot:
  # This should pass for certain dialects
  pass_str: |
    SELECT 1
    FROM
    (
      SELECT DaysToManufacture, StandardCost
      FROM Production.Product
    ) AS SourceTable
    PIVOT
    (
      AVG(StandardCost)
      FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
    ) AS PivotTable;
  configs:
    core:
      dialect: tsql

test_unfixable_ambiguous_reference_subquery:
  # `field_2` could be from the outer query or the inner
  # query (i.e. from `other_table` or `my_alias`) and because
  # it's ambiguous we shouldn't provide a fix.
  fail_str: |
    SELECT (
        SELECT other_table.other_table_field_1
        FROM other_table
        WHERE other_table.id = field_2
    )
    FROM
        (SELECT * FROM some_table) AS my_alias

test_pass_snowflake_flatten_function:
  # Tests a fix for issue 3178. This query passes because the second column
  # could refer to either 'r' or the table returned by FLATTEN().
  pass_str: |
    SELECT
        r.rec:foo::string,
        value:bar::string
    FROM foo.bar AS r, LATERAL FLATTEN(input => rec:result)
  configs:
    core:
      dialect: snowflake

passes_tql_table_variable:
  # Issue 3243
  pass_str: select a, b from @tablevar
  configs:
    core:
      dialect: tsql
    rules:
      references.consistent:
        single_table_references: qualified

fail_but_dont_fix_templated_table_name_consistent:
  fail_str: |
    SELECT
        a,
        {{ "foo" }}.b
    FROM {{ "foo" }}

fail_but_dont_fix_templated_table_name_qualified:
  fail_str: |
    SELECT
        a,
        {{ "foo" }}.b
    FROM {{ "foo" }}
  configs:
    rules:
      references.consistent:
        single_table_references: qualified