L042 loop limit on fixes reached when CTE itself contains a subquery

[pkhetrapal]

Search before asking

• [X] I searched the issues and found no similar issues.

What Happened

While running sqlfluff fix --dialect snowflake on a sql file, I get

==== finding fixable violations ====
WARNING    Loop limit on fixes reached [10].                                                                                                                                                              
==== no fixable linting violations found ====                                                                                                                                                             
All Finished 📜 🎉!
  [22 unfixable linting violations found]

INSERT OVERWRITE INTO dwh.test_table

WITH cte1 AS (
	SELECT *
	FROM (SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY r ORDER BY updated_at DESC) AS latest
		FROM mongo.temp
	WHERE latest = 1
))

SELECT * FROM cte1 WHERE 1=1;

All of the 22 violations are a mix of L002, L003 and L004.

Expected Behaviour

sqlfluff should be able to fix the violations

Observed Behaviour

Even if I try to fix the violations manually, it still shows the same error.

How to reproduce

I will try to generate a sql file that will be able to reproduce the issue

Dialect

Snowflake

Version

1.1.0

Configuration

# https://docs.sqlfluff.com/en/stable/rules.html

[sqlfluff]
exclude_rules = L029, L031, L034

[sqlfluff:indentation]
indented_joins = true
indented_using_on = true

[sqlfluff:rules:L002]
tab_space_size = 4

[sqlfluff:rules:L003]
hanging_indents = true
indent_unit = tab
tab_space_size = 4

[sqlfluff:rules:L004]
indent_unit = tab
tab_space_size = 4

[sqlfluff:rules:L010]
capitalisation_policy = upper

[sqlfluff:rules:L011]
aliasing = explicit

[sqlfluff:rules:L012]
aliasing = explicit

[sqlfluff:rules:L014]
extended_capitalisation_policy = lower

[sqlfluff:rules:L016]
ignore_comment_clauses = true
ignore_comment_lines = true
indent_unit = tab
tab_space_size = 4

[sqlfluff:rules:L019]
comma_style = trailing

[sqlfluff:rules:L022]
comma_style = trailing

[sqlfluff:rules:L028]
single_table_references = unqualified

[sqlfluff:rules:L030]
extended_capitalisation_policy = upper

[sqlfluff:rules:L040]
capitalisation_policy = upper

[sqlfluff:rules:L042]
forbid_subquery_in = both

[sqlfluff:rules:L054]
group_by_and_order_by_style = explicit

[sqlfluff:rules:L063]
extended_capitalisation_policy = upper

[sqlfluff:rules:L066]
min_alias_length = 3
max_alias_length = 15

[sqlfluff:templater:jinja:context]
params = {"DB": "DEMO"}

Are you willing to work on and submit a PR to address the issue?

• [X] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[tunetheweb]

Unfortunately there is not much we can do without the SQL that produces this error (ideally a minimal reproducible example SQL) so will need to close this issue if we don’t get that.

[pkhetrapal]

I have updated the issue with a sample query. The query is very vague but it reproduces the error. Let me know if it helps.

[tunetheweb]

Looks like this simpler example also produces it:

WITH cte1 AS (
	SELECT a
	FROM (SELECT a)
)

SELECT a FROM cte1

This only has one linting failure:

$ sqlfluff lint test.sql --dialect snowflake                       
== [test.sql] FAIL                                                                                                                                                            
L:   3 | P:   7 | L042 | from_expression_element clauses should not contain
                       | subqueries. Use CTEs instead
All Finished 📜 🎉!

So basically L042 gets in a recursive loop when trying to fix CTEs that also break L042.

For now you can manually fix that (or exclude L042 for this query) to prevent the error.

[barrywhart]

Another good test query:

WITH cte1 AS (
    SELECT *
    FROM (SELECT * FROM mongo.temp)
)

SELECT * FROM cte1

[barrywhart]

PR #3697 avoids the looping behavior. Lint issues are still flagged, but the rule does not attempt to fix it if it would cause a loop. We should still try and figure out why this is happening, so the rule can actually autofix the code, but that's lower priority (and probably a separate PR).