Repository: reata/sqllineage

Issue:
CTE (Common Table Expressions) within subqueries
**Hope to support CTE (Common Table Expressions) in subqueries. Thank you.**

```sql
insert into
    public.tgt_tbl1
(
    id
)
with
    t1 as
    (
        with
            t2 as
            (
                select
                    id
                from
                    public.src_tbl1
            )
        select
            id
        from
            t2
    )
select
    id
from
    t1
;
```

Discussion:
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/494?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 [(`a2b0344`)](https://app.codecov.io/gh/reata/sqllineage/commit/a2b03449ab6d8722eeb3cd9869d89a2af1b7c098?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) 99.40% compared to head [(`bab83aa`)](https://app.codecov.io/gh/reata/sqllineage/pull/494?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) 99.41%.


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


```diff
@@           Coverage Diff           @@
##           master     #494   +/-   ##
=======================================
  Coverage   99.40%   99.41%           
=======================================
  Files          40       40           
  Lines        2032     2034    +2     
=======================================
+ Hits         2020     2022    +2     
  Misses         12       12           
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/reata/sqllineage/pull/494?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/base.py b/sqllineage/core/parser/sqlfluff/extractors/base.py
index 74ad8bc..86c9a53 100644
--- a/sqllineage/core/parser/sqlfluff/extractors/base.py
+++ b/sqllineage/core/parser/sqlfluff/extractors/base.py
@@ -101,6 +101,25 @@ class BaseExtractor:
         """
         return extractor_cls(self.dialect).extract(segment, context)
 
+    def extract_subquery(
+        self, subqueries: List[SubQuery], holder: SubQueryLineageHolder
+    ):
+        """
+        extract subqueries collected from statement-level segment
+        """
+        from .cte import CteExtractor
+        from .select import SelectExtractor
+
+        for sq in subqueries:
+            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})
+            )
+
     @staticmethod
     def _init_holder(context: AnalyzerContext) -> SubQueryLineageHolder:
         """
diff --git a/sqllineage/core/parser/sqlfluff/extractors/cte.py b/sqllineage/core/parser/sqlfluff/extractors/cte.py
index 0427a3b..4356dcd 100644
--- a/sqllineage/core/parser/sqlfluff/extractors/cte.py
+++ b/sqllineage/core/parser/sqlfluff/extractors/cte.py
@@ -59,11 +59,6 @@ class CteExtractor(BaseExtractor):
                         if segment_has_alias:
                             holder.add_cte(SqlFluffSubQuery.of(sub_segment, identifier))
 
-        # By recursively extracting each extractor of the parent and merge, we're doing Depth-first search
-        for sq in subqueries:
-            holder |= SelectExtractor(self.dialect).extract(
-                sq.query,
-                AnalyzerContext(cte=holder.cte, write={sq}),
-            )
+        self.extract_subquery(subqueries, holder)
 
         return holder
diff --git a/sqllineage/core/parser/sqlfluff/extractors/select.py b/sqllineage/core/parser/sqlfluff/extractors/select.py
index 5c9fe72..24dcc61 100644
--- a/sqllineage/core/parser/sqlfluff/extractors/select.py
+++ b/sqllineage/core/parser/sqlfluff/extractors/select.py
@@ -70,21 +70,9 @@ class SelectExtractor(BaseExtractor, SourceHandlerMixin):
                             subqueries.append(sq)
                         self._handle_table(seg, holder)
                         self._handle_column(seg)
-
         self.end_of_query_cleanup(holder)
 
-        # By recursively extracting each subquery of the parent and merge, we're doing Depth-first search
-        for sq in subqueries:
-            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})
-            )
+        self.extract_subquery(subqueries, holder)
 
         return holder
 


Test patch:
diff --git a/tests/sql/table/test_cte.py b/tests/sql/table/test_cte.py
index fa41670..c9ab271 100644
--- a/tests/sql/table/test_cte.py
+++ b/tests/sql/table/test_cte.py
@@ -97,3 +97,15 @@ FROM (WITH cte1 AS (SELECT col1 FROM tab1)
         sql,
         {"tab1", "tab2"},
     )
+
+
+def test_cte_within_cte():
+    sql = """WITH cte1 AS
+         (WITH cte2 AS
+                   (SELECT id
+                    FROM tab1)
+          SELECT id
+          FROM cte2)
+SELECT id
+FROM cte1"""
+    assert_table_lineage_equal(sql, {"tab1"})