Repository: sqlfluff/sqlfluff

Issue:
L045 false positive when CTE used in WHERE clause
Rule [`L045`](https://github.com/sqlfluff/sqlfluff/blob/master/src/sqlfluff/core/rules/std/L045.py) incorrectly raises an exception when a CTE is used in a correlated subquery in the `WHERE` clause. Here's an example of one such query that results in this false positive:

```sql
WITH max_date_cte AS (
    SELECT MAX(row_updated_date) AS max_date
    FROM warehouse.loaded_monthly
)

SELECT stuff
FROM warehouse.updated_weekly
WHERE row_updated_date <= (SELECT max_date FROM max_date_cte);
```

## Expected Behaviour

I expect running `sqlfluff lint` on the query above to return with exit code 0 (no linting failure).

## Observed Behaviour

Running `sqlfluff lint` on the query above results in a failure state, failing rule L045.

## Steps to Reproduce

To quickly reproduce the error in bash:

```bash
q=$'WITH mycte AS (\n    SELECT MAX(col1) AS maxdate\n    FROM loaded_monthly\n)\n\nSELECT stuff\nFROM loaded_weekly\nWHERE row_updated_date <= (SELECT maxdate FROM mycte);'
echo "$q" | sqlfluff lint - --rules L045
```

```
== [stdin] FAIL
L:   1 | P:   1 | L045 | Query defines a CTE (common-table expression) but does
                       | not use it
```

## Version

```bash
sqlfluff --version
# sqlfluff, version 0.5.1

python3 --version
# Python 3.6.8
```
## Configuration

None (default)


Discussion:

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index cfb79e5cd..593c1d389 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -7,6 +7,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 ## [Unreleased]
 ### Changed
+- Fix false positive when CTE used in WHERE clause ([#944](https://github.com/sqlfluff/sqlfluff/issues/944))
 - Logging and readout now includes more detail and a notification of dbt compilation.
 
 ## [0.5.1] - 2021-04-09
diff --git a/src/sqlfluff/core/rules/analysis/select_crawler.py b/src/sqlfluff/core/rules/analysis/select_crawler.py
index b481c80dc..df7619702 100644
--- a/src/sqlfluff/core/rules/analysis/select_crawler.py
+++ b/src/sqlfluff/core/rules/analysis/select_crawler.py
@@ -1,6 +1,6 @@
 """Tools for more complex analysis of SELECT statements."""
 from collections import defaultdict
-from typing import Dict, List, NamedTuple, Optional, Union
+from typing import Dict, Generator, List, NamedTuple, Optional, Union
 
 from cached_property import cached_property
 
@@ -52,9 +52,26 @@ class SelectCrawler:
         If we find a SELECT, return info list. Otherwise, return table name
         or function call string.
         """
+        for o in cls.crawl(segment, queries, dialect, False):
+            return o
+        assert False, "Should be unreachable"
+
+    @classmethod
+    def crawl(
+        cls,
+        segment: BaseSegment,
+        queries: Dict[str, List["SelectCrawler"]],
+        dialect: Dialect,
+        recurse_into=True,
+    ) -> Generator[Union[str, List["SelectCrawler"]], None, None]:
+        """Find SELECTs, table refs, or value table function calls in segment.
+
+        For each SELECT, yield a list of SelectCrawlers. As we find table
+        references or function call strings, yield those.
+        """
         buff = []
         for seg in segment.recursive_crawl(
-            "table_reference", "select_statement", recurse_into=False
+            "table_reference", "select_statement", recurse_into=recurse_into
         ):
             if seg is segment:
                 # If we are starting with a select_statement, recursive_crawl()
@@ -64,10 +81,10 @@ class SelectCrawler:
             if seg.type == "table_reference":
                 if not seg.is_qualified() and seg.raw in queries:
                     # It's a CTE.
-                    return queries[seg.raw]
+                    yield queries[seg.raw]
                 else:
                     # It's an external table.
-                    return seg.raw
+                    yield seg.raw
             else:
                 assert seg.type == "select_statement"
                 buff.append(SelectCrawler(seg, dialect))
@@ -77,8 +94,8 @@ class SelectCrawler:
             # same as an external table. Return the "table" part as a string.
             table_expr = segment.get_child("table_expression")
             if table_expr:
-                return table_expr.raw
-        return buff
+                yield table_expr.raw
+        yield buff
 
     def __init__(self, select_statement, dialect):
         self.select_statement = select_statement
diff --git a/src/sqlfluff/core/rules/std/L045.py b/src/sqlfluff/core/rules/std/L045.py
index 8c5700efc..e5edca6d6 100644
--- a/src/sqlfluff/core/rules/std/L045.py
+++ b/src/sqlfluff/core/rules/std/L045.py
@@ -51,6 +51,14 @@ 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(


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L045.yml b/test/fixtures/rules/std_rule_cases/L045.yml
index 19a4af701..77ebfc289 100644
--- a/test/fixtures/rules/std_rule_cases/L045.yml
+++ b/test/fixtures/rules/std_rule_cases/L045.yml
@@ -80,3 +80,31 @@ test_7:
 test_8:
   # Issue 915: Crash on statements that don't have a SELECT
   pass_str: CREATE TABLE my_table (id INTEGER)
+
+test_9:
+  # Issue 944: Detecting use of CTE in nested SELECT
+  pass_str: |
+    WITH max_date_cte AS (
+        SELECT MAX(row_updated_date) AS max_date
+        FROM warehouse.loaded_monthly
+    )
+
+    SELECT stuff
+    FROM warehouse.updated_weekly
+    WHERE row_updated_date <= (SELECT max_date FROM max_date_cte)
+
+test_10:
+  # Variant on test_9, the WHERE with a nested SELECT is in a CTE
+  pass_str: |
+    WITH max_date_cte AS (
+      SELECT MAX(row_updated_date) AS max_date
+      FROM warehouse.loaded_monthly
+    ),
+    uses_max_date_cte AS (
+      SELECT stuff
+          FROM warehouse.updated_weekly
+          WHERE row_updated_date <= (SELECT max_date FROM max_date_cte)
+    )
+
+    SELECT stuff
+    FROM uses_max_date_cte