Repository: sqlfluff/sqlfluff

Issue:
sqlfluff error - CRITICAL   [L045] Applying rule L045 threw an Exception: maximum recursion depth exceeded in comparison
## Expected Behaviour
No `CRITICAL   [L045] Applying rule L045 threw an Exception: maximum recursion depth exceeded in comparison` error is thrown.

## Observed Behaviour
`CRITICAL   [L045] Applying rule L045 threw an Exception: maximum recursion depth exceeded in comparison` error is thrown.

stack trace:
```bash
== [models/analytics/cams/staging/web/sl_web_spot_pages.sql] FAIL
L:  17 | P:   1 | L014 | Inconsistent capitalisation of unquoted identifiers.
...
...
...
L:  99 | P:  15 | L014 | Inconsistent capitalisation of unquoted identifiers.
CRITICAL   [L045] Applying rule L045 threw an Exception: maximum recursion depth exceeded
Traceback (most recent call last):
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/rules/base.py", line 290, in crawl
    res = self._eval(
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/rules/std/L045.py", line 78, in _eval
    self._visit_sources(queries.pop(None), dialect, queries)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/rules/std/L045.py", line 59, in _visit_sources
    cls._visit_sources(source, dialect, queries)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/rules/std/L045.py", line 59, in _visit_sources
    cls._visit_sources(source, dialect, queries)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/rules/std/L045.py", line 59, in _visit_sources
    cls._visit_sources(source, dialect, queries)
  [Previous line repeated 957 more times]
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/rules/std/L045.py", line 55, in _visit_sources
    for source in SelectCrawler.crawl(
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/rules/analysis/select_crawler.py", line 73, in crawl
    for seg in segment.recursive_crawl(
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 705, in recursive_crawl
    yield from seg.recursive_crawl(*seg_type, recurse_into=recurse_into)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 705, in recursive_crawl
    yield from seg.recursive_crawl(*seg_type, recurse_into=recurse_into)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 705, in recursive_crawl
    yield from seg.recursive_crawl(*seg_type, recurse_into=recurse_into)
  [Previous line repeated 6 more times]
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 697, in recursive_crawl
    if self.is_type(*seg_type):
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in is_type
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in <genexpr>
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in is_type
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in <genexpr>
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in is_type
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in <genexpr>
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in is_type
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
  File "/opt/anaconda3/envs/surfline-dbt/lib/python3.8/site-packages/sqlfluff/core/parser/segments/base.py", line 365, in <genexpr>
    return any(base_class.is_type(*seg_type) for base_class in cls.__bases__)
RecursionError: maximum recursion depth exceeded
```

**dbt model SQL _before_ linting**
```sql
-- filename: sl_web_spot_pages.sql

{{
	config(
        materialized = 'incremental',
        sort = 'received_at',
        unique_key = 'page_id'
	)
}}

with user_entitlement as (

    select * from {{ ref('sl_web_user_entitlement_base') }}

),

has_cam_entitlement as (

    select * from {{ ref('spots_has_cam_map') }}

),

pages as (

    select * from {{ ref('base_web_pages') }}

),

pages_xf as (

    select
        pages.received_at,
        pages.page_id,
        pages.spot_id,
        pages.user_id,
        pages.anonymous_id,
        pages.cam_id,
        pages.is_single_cam,
        pages.is_mobile_view,
        pages.cam_name_primary,
        user_entitlement.subscription_entitlement

    from pages
    left join user_entitlement
        on pages.user_id = user_entitlement.user_id
            and pages.received_at >= user_entitlement.entitlement_from
            and pages.received_at < user_entitlement.entitlement_to
    where pages.spot_id is not null
        and pages.name = 'Spot Report'

    {% if is_incremental() %}

        -- Only get new data (not including today's data)
        and pages.received_at > (select max (received_at) from {{ this }} )
        and pages.received_at < current_date

    {% else %}

        and pages.received_at >= date_trunc('year', current_date - 1) - interval '1 year'

    {% endif %}

),

final as (

    select
        pages_xf.received_at,
        pages_xf.page_id,
        pages_xf.spot_id,
        has_cam_entitlement.has_cam,
        pages_xf.user_id,
        pages_xf.anonymous_id,
        pages_xf.cam_id,
        pages_xf.is_single_cam,
        pages_xf.is_mobile_view,
        pages_xf.cam_name_primary,
        pages_xf.subscription_entitlement

    from pages_xf
    -- inner join because some spot_ids not in MongoDB
    inner join has_cam_entitlement using (spot_id)

)

select * from final

```

**dbt model SQL _after_ linting** - Not sure if the UPPER-CASING I get from `sqlfluff fix` is a result of the above or not. What's weird to me is the UPPER-CASING is not consistent 🤔 .
```sql
-- filename: sl_web_spot_pages.sql

{{
	config(
        materialized = 'incremental',
        sort = 'received_at',
        unique_key = 'page_id'
	)
}}

with user_entitlement as (

    select * from {{ ref('sl_web_user_entitlement_base') }}

),

HAS_CAM_ENTITLEMENT as (

    select * from {{ ref('spots_has_cam_map') }}

),

PAGES as (

    select * from {{ ref('base_web_pages') }}

),

PAGES_XF as (

    select
        PAGES.RECEIVED_AT,
        PAGES.PAGE_ID,
        PAGES.SPOT_ID,
        PAGES.USER_ID,
        PAGES.ANONYMOUS_ID,
        PAGES.CAM_ID,
        PAGES.IS_SINGLE_CAM,
        PAGES.IS_MOBILE_VIEW,
        PAGES.CAM_NAME_PRIMARY,
        USER_ENTITLEMENT.SUBSCRIPTION_ENTITLEMENT

    from PAGES
    left join USER_ENTITLEMENT
        on PAGES.USER_ID = USER_ENTITLEMENT.USER_ID
            and PAGES.RECEIVED_AT >= USER_ENTITLEMENT.ENTITLEMENT_FROM
            and PAGES.RECEIVED_AT < USER_ENTITLEMENT.ENTITLEMENT_TO
    where PAGES.SPOT_ID is not null
        and PAGES.NAME = 'Spot Report'

    {% if is_incremental() %}

        -- Only get new data (not including today's data)
        and PAGES.RECEIVED_AT > (select max(RECEIVED_AT) from {{ this }} )
        and PAGES.RECEIVED_AT < current_date

    {% else %}

        and pages.received_at >= date_trunc('year', current_date - 1) - interval '1 year'

    {% endif %}

),

FINAL as (

    select
        PAGES_XF.RECEIVED_AT,
        PAGES_XF.PAGE_ID,
        PAGES_XF.SPOT_ID,
        HAS_CAM_ENTITLEMENT.HAS_CAM,
        PAGES_XF.USER_ID,
        PAGES_XF.ANONYMOUS_ID,
        PAGES_XF.CAM_ID,
        PAGES_XF.IS_SINGLE_CAM,
        PAGES_XF.IS_MOBILE_VIEW,
        PAGES_XF.CAM_NAME_PRIMARY,
        PAGES_XF.SUBSCRIPTION_ENTITLEMENT

    from PAGES_XF
    -- inner join because some spot_ids not in MongoDB
    inner join HAS_CAM_ENTITLEMENT using (SPOT_ID)

)

select * from FINAL

```


## Steps to Reproduce

## Version
Include the output of `sqlfluff --version` along with your Python version

I have installed `sqlfluff` from source using `pip install git+https://github.com/sqlfluff/sqlfluff.git` (currently commit: `ff0dcb40e9fc26cba6643be7f2332d3a58c22e5d`) so that I can take advantage of my recent PR #868 instead of having to wait for the next release.

## Configuration
```
# For SQLFluff Rules reference, see:
# https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
[sqlfluff]
verbose = 0
nocolor = False
dialect = postgres
templater = dbt
rules = None
exclude_rules = L032,L033,L034,L037,L044
recurse = 0
output_line_length = 120
runaway_limit = 10
ignore = parsing
ignore_templated_areas = True

[sqlfluff:indentation]
indented_joins = False
template_blocks_indent = True

# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
only_aliases = True

# Some rules have their own specific config.
# All SQLFluff rules can be found at: https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
# When a rule in not listed below, we inherit the default behavior from above.
[sqlfluff:rules:L003]
lint_templated_tokens = True

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy = lower

[sqlfluff:rules:L014]  # Unquoted identifiers
capitalisation_policy = lower
unquoted_identifiers_policy = all

[sqlfluff:rules:L016]
# Setting to True allows us to copy/paste long URLs as comments
ignore_comment_lines = True

[sqlfluff:rules:L030]  # Function names
capitalisation_policy = lower

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy = lower

[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join

```


Discussion:

Patch:
diff --git a/src/sqlfluff/core/rules/analysis/select_crawler.py b/src/sqlfluff/core/rules/analysis/select_crawler.py
index df7619702..6eb5bf326 100644
--- a/src/sqlfluff/core/rules/analysis/select_crawler.py
+++ b/src/sqlfluff/core/rules/analysis/select_crawler.py
@@ -81,7 +81,10 @@ class SelectCrawler:
             if seg.type == "table_reference":
                 if not seg.is_qualified() and seg.raw in queries:
                     # It's a CTE.
-                    yield queries[seg.raw]
+                    # :TRICKY: Pop the CTE from "queries" to help callers avoid
+                    # infinite recursion. We could make this behavior optional
+                    # someday, if necessary.
+                    yield queries.pop(seg.raw)
                 else:
                     # It's an external table.
                     yield seg.raw
diff --git a/src/sqlfluff/core/rules/std/L045.py b/src/sqlfluff/core/rules/std/L045.py
index e5edca6d6..2ad473980 100644
--- a/src/sqlfluff/core/rules/std/L045.py
+++ b/src/sqlfluff/core/rules/std/L045.py
@@ -51,25 +51,12 @@ class Rule_L045(BaseRule):
         queries: Dict[str, List[SelectCrawler]],
     ):
         for select_info in select_info_list:
-            # Process nested SELECTs.
             for source in SelectCrawler.crawl(
                 select_info.select_statement, queries, dialect
             ):
                 if isinstance(source, list):
                     cls._visit_sources(source, dialect, queries)
 
-            # Process the query's sources.
-            for alias_info in select_info.select_info.table_aliases:
-                # Does the query read from a CTE? If so, visit the CTE.
-                for target_segment in alias_info.from_expression_element.get_children(
-                    "table_expression", "join_clause"
-                ):
-                    target = target_segment.raw
-                    if target in queries:
-                        select_info_target = queries.pop(target)
-                        if isinstance(select_info_target, list):
-                            cls._visit_sources(select_info_target, dialect, queries)
-
     def _eval(self, segment, dialect, **kwargs):
         if segment.is_type("statement"):
             queries = SelectCrawler.gather(segment, dialect)


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L045.yml b/test/fixtures/rules/std_rule_cases/L045.yml
index 77ebfc289..e00860aa0 100644
--- a/test/fixtures/rules/std_rule_cases/L045.yml
+++ b/test/fixtures/rules/std_rule_cases/L045.yml
@@ -108,3 +108,52 @@ test_10:
 
     SELECT stuff
     FROM uses_max_date_cte
+
+test_11:
+  # Issue 963: Infinite recursion when a CTE references itself
+  pass_str: |
+    with pages_xf as (
+      select pages.received_at
+      from pages
+      where pages.received_at > (select max(received_at) from pages_xf )
+    ),
+    final as (
+      select pages_xf.received_at
+      from pages_xf
+    )
+
+    select * from final
+
+test_12:
+  # Variant on test_11 where there *is* an unused CTE
+  fail_str: |
+    with pages_xf as (
+      select pages.received_at
+      from pages
+      where pages.received_at > (select max(received_at) from pages_xf )
+    ),
+    final as (
+      select pages_xf.received_at
+      from pages_xf
+    ),
+    unused as (
+      select pages.received_at from pages
+    )
+
+    select * from final
+
+test_13:
+  # Variant on test_11 where the CTE references itself indirectly
+  pass_str: |
+    with pages_xf as (
+      select pages.received_at
+      from pages
+      where pages.received_at > (select max(received_at) from final )
+    ),
+
+    final as (
+      select pages_xf.received_at
+      from pages_xf
+    )
+
+    select * from final