Repository: reata/sqllineage

Issue:
Can't handle parenthesized from clause
Using `sqllineage==1.3.5`

Given auto generated query from a third party package. 

```
query = """
    WITH tbl1 AS (
         SELECT table1.id AS user_id
           FROM db1.table1
        )
    SELECT tbl1.user_id,
    table1.foo,
    table2.bar,
    FROM ((tbl1
     LEFT JOIN db1.table2 USING (user_id))
     LEFT JOIN db1.table3 USING (user_id))
"""

```

**Input**

```
from sqllineage.runner import LineageRunner

[...]

result = LineageRunner(query)

for tbl in result.source_tables:
    print(tbl)
```

**Output (incorrect)**

```
db1.table1
```

However if I remove the extra brackets from the query:

```
query = """
    WITH tbl1 AS (
         SELECT table1.id AS user_id
           FROM db1.table1
        )
    SELECT tbl1.user_id,
    table1.foo,
    table2.bar,
    FROM tbl1
     LEFT JOIN db1.table2 USING (user_id)
     LEFT JOIN db1.table3 USING (user_id)
"""
```

**Output:**

```
db1.table1
db1.table2
db1.table3
```

Discussion:
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/419?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) Report
> Merging [#419](https://app.codecov.io/gh/reata/sqllineage/pull/419?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) (4dd0968) into [master](https://app.codecov.io/gh/reata/sqllineage/commit/27de8ca8657b6749743e97d4fa0d6c1e3e0b511d?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) (27de8ca) will **increase** coverage by `0.00%`.
> The diff coverage is `100.00%`.

```diff
@@           Coverage Diff           @@
##           master     #419   +/-   ##
=======================================
  Coverage   99.49%   99.49%           
=======================================
  Files          41       41           
  Lines        1984     1999   +15     
=======================================
+ Hits         1974     1989   +15     
  Misses         10       10           
```


| [Files Changed](https://app.codecov.io/gh/reata/sqllineage/pull/419?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) | Coverage Δ | |
|---|---|---|
| [sqllineage/core/parser/sqlfluff/utils.py](https://app.codecov.io/gh/reata/sqllineage/pull/419?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-c3FsbGluZWFnZS9jb3JlL3BhcnNlci9zcWxmbHVmZi91dGlscy5weQ==) | `99.40% <100.00%> (+0.03%)` | :arrow_up: |
| [sqllineage/core/parser/sqlparse/handlers/source.py](https://app.codecov.io/gh/reata/sqllineage/pull/419?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-c3FsbGluZWFnZS9jb3JlL3BhcnNlci9zcWxwYXJzZS9oYW5kbGVycy9zb3VyY2UucHk=) | `100.00% <100.00%> (ø)` | |



Patch:
diff --git a/sqllineage/core/parser/sqlfluff/utils.py b/sqllineage/core/parser/sqlfluff/utils.py
index b4ff92a..40f869f 100644
--- a/sqllineage/core/parser/sqlfluff/utils.py
+++ b/sqllineage/core/parser/sqlfluff/utils.py
@@ -162,17 +162,25 @@ def list_from_expression(segment: BaseSegment) -> List[BaseSegment]:
 
 def get_from_expression_element(segment: BaseSegment) -> Optional[BaseSegment]:
     """
-    from_clause as grandparent, from_expression as parent, or from_expression_element itself
+    capable of handing:
+        from_clause as grandparent
+        from_expression/join_clause as parent
+        from_expression_element itself
     """
     from_expression_element = None
-    if from_expression := segment.get_child("from_expression"):
-        if bracketed := from_expression.get_child("bracketed"):
-            if seg := bracketed.get_child("from_expression_element"):
+    if segment.type == "from_clause":
+        if from_expression := segment.get_child("from_expression"):
+            non_bracket = from_expression
+            while non_bracket.get_child("bracketed"):
+                non_bracket = non_bracket.get_child("bracketed")
+            if seg := non_bracket.get_child("from_expression_element"):
                 from_expression_element = seg
-        elif seg := from_expression.get_child("from_expression_element"):
+            elif seg := non_bracket.get_child("from_expression"):
+                if sub_seg := seg.get_child("from_expression_element"):
+                    from_expression_element = sub_seg
+    elif segment.type in ("from_expression", "join_clause"):
+        if seg := segment.get_child("from_expression_element"):
             from_expression_element = seg
-    elif seg := segment.get_child("from_expression_element"):
-        from_expression_element = seg
     elif segment.type == "from_expression_element":
         from_expression_element = segment
     return from_expression_element
@@ -318,8 +326,17 @@ def has_alias(segment: BaseSegment) -> bool:
 
 
 def list_join_clause(segment: BaseSegment) -> List[BaseSegment]:
+    """
+    traverse from_clause, recursively goes into bracket by default
+    """
     if from_expression := segment.get_child("from_expression"):
-        return [seg for seg in from_expression.segments if seg.type == "join_clause"]
+        if bracketed := from_expression.get_child("bracketed"):
+            join_clauses = bracketed.get_children("join_clause")
+            if inner_bracket := bracketed.get_child("bracketed"):
+                join_clauses = list_join_clause(inner_bracket) + join_clauses
+            return join_clauses
+        else:
+            return from_expression.get_children("join_clause")
     return []
 
 
diff --git a/sqllineage/core/parser/sqlparse/handlers/source.py b/sqllineage/core/parser/sqlparse/handlers/source.py
index 3b467d7..1ed403c 100644
--- a/sqllineage/core/parser/sqlparse/handlers/source.py
+++ b/sqllineage/core/parser/sqlparse/handlers/source.py
@@ -82,8 +82,15 @@ class SourceHandler(SourceHandlerMixin, NextTokenBaseHandler):
                 # SELECT * FROM (VALUES ...), no operation needed
                 pass
             else:
-                # SELECT * FROM (tab2), which is valid syntax
-                self._handle(token.tokens[1], holder)
+                # SELECT * FROM (tab2) or SELECT * FROM (tab2 JOIN tab1), which is valid syntax
+                using_flag = False
+                for t in token.tokens:
+                    if t.is_keyword and t.normalized == "USING":
+                        using_flag = True
+                    if isinstance(t, Identifier) or (
+                        isinstance(t, Parenthesis) and using_flag is False
+                    ):
+                        self._handle(t, holder)
         elif token.ttype == Literal.String.Single:
             self.tables.append(Path(token.value))
         elif isinstance(token, Function):


Test patch:
diff --git a/tests/sql/table/test_create.py b/tests/sql/table/test_create.py
index 956e44a..ae5776a 100644
--- a/tests/sql/table/test_create.py
+++ b/tests/sql/table/test_create.py
@@ -17,12 +17,6 @@ def test_create_as():
     )
 
 
-def test_create_as_with_parenthesis_around_table_name():
-    assert_table_lineage_equal(
-        "CREATE TABLE tab1 AS SELECT * FROM (tab2)", {"tab2"}, {"tab1"}
-    )
-
-
 def test_create_like():
     assert_table_lineage_equal("CREATE TABLE tab1 LIKE tab2", {"tab2"}, {"tab1"})
 
diff --git a/tests/sql/table/test_select.py b/tests/sql/table/test_select.py
index 33bdeed..6a2dfe4 100644
--- a/tests/sql/table/test_select.py
+++ b/tests/sql/table/test_select.py
@@ -78,6 +78,33 @@ def test_select_count():
     assert_table_lineage_equal("SELECT count(*) FROM tab1", {"tab1"})
 
 
+def test_select_parenthesized_from_table():
+    sql = "SELECT * FROM (tab1)"
+    assert_table_lineage_equal(sql, {"tab1"})
+
+
+def test_select_parenthesized_from_table_join():
+    sql = """SELECT tab1.id
+FROM (tab1
+    LEFT JOIN tab2 ON tab1.id = tab2.id)"""
+    assert_table_lineage_equal(sql, {"tab1", "tab2"})
+
+
+def test_select_parenthesized_from_table_join_using():
+    sql = """SELECT tab1.id
+FROM (tab1
+    LEFT JOIN tab2 USING (id))"""
+    assert_table_lineage_equal(sql, {"tab1", "tab2"})
+
+
+def test_select_parenthesized_from_table_join_recursive():
+    sql = """SELECT tab1.id
+FROM ((tab1
+    LEFT JOIN tab2 ON tab1.id = tab2.id)
+    LEFT JOIN tab3 ON tab1.id = tab3.id)"""
+    assert_table_lineage_equal(sql, {"tab1", "tab2", "tab3"})
+
+
 def test_select_subquery():
     assert_table_lineage_equal("SELECT col1 FROM (SELECT col1 FROM tab1) dt", {"tab1"})
     # with an extra space