L045 false positive when CTE used in WHERE clause

[rnorberg]

Rule L045 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:

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:

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

sqlfluff --version
# sqlfluff, version 0.5.1

python3 --version
# Python 3.6.8

Configuration

None (default)

[barrywhart]

I'll take a look at this.