Repository: reata/sqllineage

Issue:
lineage inaccurate when CTE used in subquery
```
>>> from sqllineage.runner import LineageRunner

>>> sql_1="""

... with unique_nicknames as (select nickname from nicknames group by nickname having count(distinct name) = 1)

... select name, min(replacements) as smallest_step, max(replacements) as largest_step from (

...       select names.fname, [nicknames.name](http://nicknames.name/), count(*) as replacements from unique_nicknames join nicknames on nicknames.nickname = unique_nicknames.nickname join names on names.fname = nicknames.nickname group by names.fname, [nicknames.name](http://nicknames.name/)

... ) as replaceable group by name

... """

>>> sql_2="""

... select name, min(replacements) as smallest_step, max(replacements) as largest_step from (

...       with unique_nicknames as (select nickname from nicknames group by nickname having count(distinct name) = 1)

...       select names.fname, [nicknames.name](http://nicknames.name/), count(*) as replacements from unique_nicknames join nicknames on nicknames.nickname = unique_nicknames.nickname join names on names.fname = nicknames.nickname group by names.fname, [nicknames.name](http://nicknames.name/)

... ) as replaceable group by name

... """

>>> LineageRunner(sql=sql_1,dialect="redshift").source_tables

[Table: <default>.names, Table: <default>.nicknames]

>>> LineageRunner(sql=sql_2,dialect="redshift").source_tables

[Table: <default>.nicknames]

>>>
```

Discussion:
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/493?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Comparison is base [(`843ab2e`)](https://app.codecov.io/gh/reata/sqllineage/commit/843ab2eb58e3e8f4d68c5e67afc7f69341c3ade7?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) 99.40% compared to head [(`e092d82`)](https://app.codecov.io/gh/reata/sqllineage/pull/493?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) 99.26%.


<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##           master     #493      +/-   ##
==========================================
- Coverage   99.40%   99.26%   -0.15%     
==========================================
  Files          40       40              
  Lines        2031     2029       -2     
==========================================
- Hits         2019     2014       -5     
- Misses         12       15       +3     
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/reata/sqllineage/pull/493?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).


Patch:
diff --git a/sqllineage/core/parser/sqlfluff/extractors/select.py b/sqllineage/core/parser/sqlfluff/extractors/select.py
index 72c1eda..5c9fe72 100644
--- a/sqllineage/core/parser/sqlfluff/extractors/select.py
+++ b/sqllineage/core/parser/sqlfluff/extractors/select.py
@@ -75,7 +75,14 @@ class SelectExtractor(BaseExtractor, SourceHandlerMixin):
 
         # By recursively extracting each subquery of the parent and merge, we're doing Depth-first search
         for sq in subqueries:
-            holder |= SelectExtractor(self.dialect).extract(
+            from .cte import CteExtractor
+
+            extractor_cls = (
+                CteExtractor
+                if sq.query.get_child("with_compound_statement")
+                else SelectExtractor
+            )
+            holder |= extractor_cls(self.dialect).extract(
                 sq.query, AnalyzerContext(cte=holder.cte, write={sq})
             )
 
diff --git a/sqllineage/core/parser/sqlfluff/utils.py b/sqllineage/core/parser/sqlfluff/utils.py
index b17c29c..406b17d 100644
--- a/sqllineage/core/parser/sqlfluff/utils.py
+++ b/sqllineage/core/parser/sqlfluff/utils.py
@@ -36,7 +36,9 @@ def is_subquery(segment: BaseSegment) -> bool:
             segment if segment.type == "bracketed" else segment.segments[0]
         )
         # check if innermost parenthesis contains SELECT
-        if token.get_child("select_statement", "set_expression"):
+        if token.get_child(
+            "select_statement", "set_expression", "with_compound_statement"
+        ):
             return True
         elif expression := token.get_child("expression"):
             if expression.get_child("select_statement"):
@@ -152,7 +154,6 @@ def list_subqueries(segment: BaseSegment) -> List[SubQueryTuple]:
     elif segment.type == "from_expression_element":
         as_segment, target = extract_as_and_target_segment(segment)
         if is_subquery(target):
-            as_segment, target = extract_as_and_target_segment(segment)
             subquery = [
                 SubQueryTuple(
                     extract_innermost_bracketed(target)


Test patch:
diff --git a/tests/sql/table/test_cte.py b/tests/sql/table/test_cte.py
index 422ddde..fa41670 100644
--- a/tests/sql/table/test_cte.py
+++ b/tests/sql/table/test_cte.py
@@ -85,3 +85,15 @@ SELECT col3 from tab3"""
         sql,
         {"tab1", "tab2", "tab3"},
     )
+
+
+def test_cte_within_subquery():
+    sql = """SELECT sq.col1
+FROM (WITH cte1 AS (SELECT col1 FROM tab1)
+      SELECT col1
+      FROM cte1
+               INNER JOIN tab2 ON cte1.col1 = tab2.col1) AS sq"""
+    assert_table_lineage_equal(
+        sql,
+        {"tab1", "tab2"},
+    )