AM04.yml

rule: AM04

test_pass_known_number_of_result_columns_1:
  pass_str: select a, b from t

test_fail_unknown_number_of_result_columns_1:
  fail_str: select * from t

test_pass_known_number_of_result_columns_2:
  desc: Columns are specified in CTE so * in final query will return only columns specified earlier.
  pass_str: |
    with cte as (
        select
            a, b
        from
            t
    )
    select * from cte

test_fail_unknown_number_of_result_columns_2:
  fail_str: |
    with cte as (
        select
            *
        from
            t
    )
    select * from cte


test_pass_known_number_of_result_columns_3:
  pass_str: |
    with cte as (
        select
            *
        from
            t
    )
    select a, b from cte

test_pass_known_number_of_result_columns_4:
  desc: |
    CTE1 has * but columns are specified in final select.
    CTE2 has columns specified so cte2.* in final select will return only columns specified in CTE2's body.
  pass_str: |
    with cte1 as (
        select
            *
        from
            t
    ),
    cte2 as (
        select
            a, b
        from
            t
    )
    select
        cte1.a,
        cte2.*
    from cte1
    join cte2
    using (a)

test_fail_unknown_number_of_result_columns_3:
  fail_str: |
    with cte1 as (
        select
            *
        from
            t
    ),
    cte2 as (
        select
            a, b
        from
            t
    )
    select
        cte1.*,
        cte2.*
    from cte1
    join cte2
    using (a)

test_pass_known_number_of_result_columns_5:
  desc: Columns specified in subquery so * in final select will return only those columns.
  pass_str: |
    select * from (
        select
            a, b
        from
            t
    )

test_fail_unknown_number_of_result_columns_4:
  desc: Select t.* will return unknown number of columns.
  fail_str: |
    with cte as (
        select
            a, b
        from
            t
    )
    select
        cte.*,
        t.*
    from cte1
    join t
    using (a)

test_fail_unknown_number_of_result_columns_5:
  desc: Select t_alias.* will return unknown number of columns since the * is used in subquery.
  fail_str: |
    with cte as (
        select
            a, b
        from
            t
    )
    select
        cte.*,
        t_alias.*
    from cte1
    join (select * from t) as t_alias
    using (a)

test_pass_known_number_of_result_columns_6:
  desc: Select t_alias.* will return known number of columns since they are defined in subquery.
  pass_str: |
    select
        t_alias.*
    from cte1
    join (select a from t) as t_alias
    using (a)

test_fail_unknown_number_of_result_columns_6:
  fail_str: |
    select
        t_alias.*
    from t1
    join (select * from t) as t_alias
    using (a)

test_pass_known_number_of_result_columns_7:
  pass_str: |
    with cte as (
        select
            a, b
        from
            t
    )
    select
        cte.*,
        t_alias.a
    from cte1
    join (select * from t) as t_alias
    using (a)

test_fail_unknown_number_of_result_columns_7:
  fail_str: select *, t.*, t.a, b from t

test_pass_known_number_of_result_columns_8:
  pass_str: |
    select a from t1
    union all
    select b from t2

test_fail_unknown_number_of_result_columns_8:
  fail_str: |
    select a from t1
    union all
    select * from t2

test_fail_unknown_number_of_result_columns_9:
  fail_str: |
    select * from t1
    union all
    select b from t2

test_fail_unknown_number_of_result_columns_10:
  fail_str: |
    with cte as (
      select * from t1
      union all
      select b from t2
    )

    select * from cte
    union all
    select b from t3

test_pass_known_number_of_result_columns_9:
  pass_str: |
    with cte as (
      select a from t1
      union all
      select b from t2
    )

    select * from cte
    union all
    select b from t3

test_pass_known_number_of_result_columns_10:
  desc: Columns are specified in cte_orders's body so cte_orders.* will return only these columns.
  pass_str: |
    WITH cte_orders AS (
      SELECT customer_id, total
      FROM orders
    )

    SELECT customers.name, cte_orders.*
    FROM
      customers,
      cte_orders
    WHERE
      clients.id = orders.clientId

test_pass_known_number_of_result_columns_11:
  desc: Columns are specified in cte_orders's body so cte_orders.* will return only these columns.
  pass_str: |
    WITH cte_orders AS (
      SELECT customer_id, total
      FROM orders
    )

    SELECT *
    FROM
      cte_orders AS orders

test_fail_unknown_number_of_result_columns_11:
  fail_str: |
    WITH cte_orders AS (
      SELECT *
      FROM orders
    )

    SELECT *
    FROM
      cte_orders AS orders

test_fail_unknown_number_of_result_columns_12:
  desc: CTE is unused. We select * from orders table which means it's unknown what columns will be returned.
  fail_str: |
    WITH cte_orders AS (
      SELECT customer_id, total
      FROM orders
    )

    SELECT *
    FROM
      orders AS cte_orders

test_fail_unknown_number_of_result_columns_13:
  fail_str: SELECT p.* FROM races, UNNEST(participants) AS p

test_pass_known_number_of_result_columns_12:
  pass_str: SELECT p FROM races, UNNEST(participants) AS p

test_fail_unknown_number_of_result_columns_14:
  fail_str: SELECT * FROM a JOIN b

test_fail_unknown_number_of_result_columns_15:
  desc: We know what columns will cte return but we don't know what columns will be returned from joined table b.
  fail_str: |
    WITH cte AS (
      SELECT a
      FROM t
    )
    SELECT *
    FROM cte
    JOIN b

test_pass_known_number_of_result_columns_13:
  desc: Both CTEs define returned columns so * in final select will return known number of columns.
  pass_str: |
    WITH cte1 AS (
      SELECT a
      FROM t
    ),
    cte2 AS (
      SELECT b
      FROM u
    )
    SELECT *
    FROM cte1
    JOIN cte2

test_pass_known_number_of_result_columns_14:
  pass_str: select a, b from `d.t`
  configs:
    core:
      dialect: bigquery

test_fail_unknown_number_of_result_columns_16:
  fail_str: select * from `d.t`
  configs:
    core:
      dialect: bigquery

test_pass_known_number_of_result_columns_15:
  # Issue 915: Crash on statements that don't have a SELECT
  pass_str: CREATE TABLE my_table (id INTEGER)

test_fail_unknown_number_of_result_columns_17:
  # Issue 930: Infinite recursion if CTE queries itself.
  fail_str: |
    with
      hubspot__engagement_calls as (
        select * from hubspot__engagement_calls
      )

      select * from hubspot__engagement_calls

test_fail_unknown_number_of_result_columns_18:
  # Another test for issue #930
  fail_str: |
    with
    hubspot__contacts as (
      select * from ANALYTICS.PUBLIC_intermediate.hubspot__contacts
    ),
    final as (
      select *
      from
        hubspot__contacts
        where not coalesce(_fivetran_deleted, false)
    )
    select * from final

test_pass_nested_ctes_1:
  # Test for issue 1984
  pass_str: |
    with a as (
        with b as (
            select 1 from c
        )

        select * from b
    )

    select * from a

test_fail_nested_ctes_1:
  # Test for issue 1984
  fail_str: |
    with a as (
        with b as (
            select * from c
        )

        select * from b
    )

    select * from a

test_fail_nested_ctes_2:
  # Test for issue 1984
  fail_str: |
    with a as (
        with b as (
            select 1 from t1
        ),
        c AS (
          SELECT *
          FROM u
        )

        select b.*, c.* from b join c
    )

    select * from a

test_pass_nested_ctes_3:
  # Test for issue 1984
  pass_str:
    with a as (
        with b as (
            select * from c
        )

        select 1 from b
    )

    select * from a

test_pass_nested_ctes_4:
  # Test for issue 1984
  pass_str:
    with a as (
        with b as (
            select * from c
        )

        select * from b
    )

    select 1 from a

test_cte_reference_outer_5:
  pass_str:
    with a as (
        select 1 from b
    )

    select * from (
        select * from a
    )

test_cte_tricky_nesting_6:
  pass_str:
    with b as (
        select 1 from c
    )

    select * from (
        with a as (
            select * from b
        )

        select * from a
    )

test_nested_and_same_level_ctes_7:
  pass_str:
    with a as (
      with c as (
        select 1 from d
      ),

      b as (
        select * from c
      )

      select * from b
    )

    select * from a

test_nested_cte_references_outer_8:
  pass_str:
    with c as (
      select 1 from d
    ),

    a as (
      with b as (
        select * from c
      )

      select * from b
    )

    select * from a

test_pass_join_inside_cte_with_unqualified:
  pass_str:
    with cte as (
        select
            *
        from
            t1
        inner join t2
    )

    select a, b from cte;

test_pass_known_number_of_columns_in_two_join_subqueries:
  pass_str:
    select
        *
    from (
        select a
        from foo
    ) t1
    inner join (
        select b
        from bar
    ) t2;

test_fail_two_join_subqueries_one_with_unknown_number_of_columns:
  fail_str:
    select
        *
    from (
        select *
        from foo
    ) t1
    inner join (
        select b
        from bar
    ) t2;

test_fail_no_source_table:
  fail_str: |
    SELECT *

test_query_on_snowflake_stage:
  pass_str:
    select mycolumn1
    from @public.mytable1
  configs:
    core:
      dialect: snowflake

test_snowflake_delete_cte:
  pass_str: |
    DELETE FROM MYTABLE1
        USING (
            WITH MYCTE AS (SELECT COLUMN2 FROM MYTABLE3)
            SELECT COLUMN3 FROM MYTABLE3
        ) X
    WHERE COLUMN1 = X.COLUMN3
  configs:
    core:
      dialect: snowflake

test_pass_exasol_values_clause_cte_1:
  pass_str: |
    WITH txt AS (
        VALUES (1)
        AS t (id)
    )

    SELECT *
    FROM txt
  configs:
    core:
      dialect: exasol

test_pass_exasol_values_clause_cte_2:
  pass_str: |
    WITH txt AS (
        VALUES (1, 'foo')
        AS t (id, name)
    )

    SELECT *
    FROM txt
  configs:
    core:
      dialect: exasol

test_pass_exasol_values_clause:
  pass_str:
    SELECT *
    FROM (
        VALUES (1, 2), (3, 4)
    ) AS t(c1, c2)
  configs:
    core:
      dialect: exasol

test_exasol_invalid_foreign_key_from:
  pass_str: |
    SELECT *
    WITH INVALID FOREIGN KEY (nr)
      FROM T1 REFERENCING T2 (id)
  configs:
    core:
      dialect: exasol

test_pass_cte_no_select_final_statement:
  pass_str:
    WITH mycte AS (
      SELECT
          foo,
          bar
      FROM mytable1
    )

    UPDATE sometable
    SET
        sometable.baz = mycte.bar
    FROM
        mycte;

test_tsql_select_system_as_identifier:
  pass_str: |
    SELECT @@IDENTITY AS 'Identity'
  configs:
    core:
      dialect: tsql

test_pass_sparksql_values_clause_cte_1:
  pass_str: |
    WITH txt AS (
        VALUES (1)
        AS t (id)
    )

    SELECT *
    FROM txt
  configs:
    core:
      dialect: sparksql

test_pass_sparksql_values_clause_cte_2:
  pass_str: |
    WITH txt AS (
        VALUES (1, 'foo')
        AS t (id, name)
    )

    SELECT *
    FROM txt
  configs:
    core:
      dialect: sparksql

test_pass_sparksql_values_clause:
  pass_str:
    SELECT *
    FROM (
        VALUES (1, 2), (3, 4)
    ) AS t(c1, c2)
  configs:
    core:
      dialect: sparksql