SQLFluff fix deleting CTEs when config includesforbid_subquery_in = both #4782
Description
Search before asking
- I searched the issues and found no similar issues.
What Happened
When using forbid_subquery_in, as part of the subquery to CTE conversion, existing CTEs are being deleted:
Original File/Result without forbid_subquery_in set:
cte_issue.sql
WITH
cte_1 AS (
SELECT
subquery_a.field_a,
subquery_a.field_b
FROM (
SELECT
subquery_b.field_a,
alias_a.field_d,
alias_a.field_b,
alias_b.field_c
FROM table_b AS alias_a
INNER JOIN
(SELECT * FROM table_a) AS subquery_b
ON subquery_b.field_a >= alias_a.field_d
LEFT OUTER JOIN table_b AS alias_b ON alias_a.field_b = alias_b.field_c
) AS subquery_a
),
cte_2 AS (
SELECT *
FROM table_c
WHERE field_a > 0
ORDER BY field_b DESC
),
join_ctes AS (
SELECT * FROM cte_1 LEFT OUTER JOIN cte_2 ON cte_1.field_a = cte_2.field_a
)
SELECT *
FROM join_ctes;
Result with forbid_subquery_in = both; note cte_2 is missing.
Configuration File
[sqlfluff]
dialect = snowflake
processes = 0
[sqlfluff:rules:structure.subquery]
forbid_subquery_in = both
cte_issue.sql
WITH cte_1 AS (
SELECT
subquery_a.field_a,
subquery_a.field_b
FROM subquery_a
),
subquery_b AS (SELECT * FROM table_a),
subquery_a AS (
SELECT
subquery_b.field_a,
alias_a.field_d,
alias_a.field_b,
alias_b.field_c
FROM table_b AS alias_a
INNER JOIN
subquery_b
ON subquery_b.field_a >= alias_a.field_d
LEFT OUTER JOIN table_b AS alias_b ON alias_a.field_b = alias_b.field_c
),
join_ctes AS (
SELECT * FROM cte_1 LEFT OUTER JOIN cte_2 ON cte_1.field_a = cte_2.field_a
)
SELECT *
FROM join_ctes;
Expected Behaviour
The CTE, cte_2, should not be deleted.
Observed Behaviour
The CTE, cte_2, was deleted.
How to reproduce
sqlfluff fix --FIX-EVEN-UNPARSABLE -v --config .sqlfluff cte_issue.sql
cte_issue.zip
Dialect
snowflake
Version
==== sqlfluff ====
sqlfluff: 2.0.5 python: 3.9.6
implementation: cpython verbosity: 1
dialect: snowflake templater: jinja
rules: allConfiguration
[sqlfluff]
dialect = snowflake
processes = 0
[sqlfluff:rules:structure.subquery]
forbid_subquery_in = both
Are you willing to work on and submit a PR to address the issue?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct