SQLFluff AM06 (Inconsistent column references in 'GROUP BY/ORDER BY' clauses) when they seem ok

Question

I can't understand why SQLFluff shows AM06 error (Inconsistent column references in 'GROUP BY/ORDER BY' clauses) with my code in DBT having only these order bys:

with table2 as (

    select
        h.id,
        h.id2
    from table3 as h
    order by
        h.parentid asc,
        h.createddate asc

)

select
    s.id,
    p.id
from table1 as s
left join table2 as p on p.id = s.parentid
order by
    p.shop_id asc,
    p.supplier_id asc,
    p.supplier_configuration_number asc,
    s.createddate asc,
    s.id asc

Can anyone tell me what is wrong with them?

Answer 1

Today I found the answer to my problem. It was the .sqlfluff configuration that was causing this.

I had:

[sqlfluff:rules:ambiguous.column_references]  # Number in group by
group_by_and_order_by_style = implicit

I changed the value to explicit and the warning is gone. Thanks for all the help.

Answer 2

Try writing ,-- in front of the 2nd ORDER BY

Answer 3

To resolve this issue:

Ensure that all the columns you're referencing in the ORDER BY clause of the main query (p.shop_id, p.supplier_id, p.supplier_configuration_number) exist in the CTE table2 or the main query's SELECT clause. If they don't, you may be trying to order by columns that don't exist in the context of the main query.

Always use table aliases when referencing columns, especially when joining multiple tables that might have columns with the same names. This way, you ensure clarity and avoid ambiguity.

If there's more to your SQL code than this snippet, double-check other parts of the code for potential inconsistencies in column referencing.

If you're sure your SQL code is correct, ensure you're using the latest version of SQLFluff, as there might have been bug fixes or improvements that address your issue. It's also possible to raise an issue on SQLFluff's GitHub repository if you think there's a bug in the linter.

Try this

with table2 as (
   select
       h.id,
       h.id2,
       h.parentid,
       h.createddate,
       -- Assuming these columns exist in table3
       h.shop_id,
       h.supplier_id,
       h.supplier_configuration_number
   from table3 as h
   order by
       h.parentid asc,
       h.createddate asc
)

select
   s.id,
   p.id,
   p.shop_id,                -- Added these columns to ensure they exist in the SELECT clause
   p.supplier_id,
   p.supplier_configuration_number
from table1 as s
left join table2 as p on p.id = s.parentid
order by
   p.shop_id asc,
   p.supplier_id asc,
   p.supplier_configuration_number asc,
   s.createddate asc,
   s.id asc