Repository: reata/sqllineage (python)

Issue:
Failed to Parse Source Table From Subquery With More Than One Parenthesis
```bash
➜  ~ sqllineage -e "SELECT * FROM ((SELECT * FROM foo)) dt"          
Statements(#): 1
Source Tables:
    <default>.dt
Target Tables:
    
```

Expected source table would be foo.

Pull request:
fix: failed to parse subquery with nested parenthesis
Closes #213

Patch:
diff --git a/sqllineage/core/models.py b/sqllineage/core/models.py
index 0b54670..a8be030 100644
--- a/sqllineage/core/models.py
+++ b/sqllineage/core/models.py
@@ -19,7 +19,7 @@ from sqlparse.utils import imt
 from sqllineage.exceptions import SQLLineageException
 from sqllineage.utils.entities import ColumnQualifierTuple
 from sqllineage.utils.helpers import escape_identifier_name
-from sqllineage.utils.sqlparse import get_parameters
+from sqllineage.utils.sqlparse import get_parameters, is_subquery
 
 
 class Schema:
@@ -240,20 +240,28 @@ class Column:
                 for cqt in Column._extract_source_columns(tk)
             ]
         elif isinstance(token, Parenthesis):
-            # This is to avoid circular import
-            from sqllineage.runner import LineageRunner
-
-            # (SELECT avg(col1) AS col1 FROM tab3), used after WHEN or THEN in CASE clause
-            src_cols = [
-                lineage[0]
-                for lineage in LineageRunner(token.value).get_column_lineage(
-                    exclude_subquery=False
-                )
-            ]
-            source_columns = [
-                ColumnQualifierTuple(src_col.raw_name, src_col.parent.raw_name)
-                for src_col in src_cols
-            ]
+            if is_subquery(token):
+                # This is to avoid circular import
+                from sqllineage.runner import LineageRunner
+
+                # (SELECT avg(col1) AS col1 FROM tab3), used after WHEN or THEN in CASE clause
+                src_cols = [
+                    lineage[0]
+                    for lineage in LineageRunner(token.value).get_column_lineage(
+                        exclude_subquery=False
+                    )
+                ]
+                source_columns = [
+                    ColumnQualifierTuple(src_col.raw_name, src_col.parent.raw_name)
+                    for src_col in src_cols
+                ]
+            else:
+                # (col1 + col2) AS col3
+                source_columns = [
+                    cqt
+                    for tk in token.tokens[1:-1]
+                    for cqt in Column._extract_source_columns(tk)
+                ]
         elif isinstance(token, Operation):
             # col1 + col2 AS col3
             source_columns = [
diff --git a/sqllineage/utils/sqlparse.py b/sqllineage/utils/sqlparse.py
index 0acf818..9fe7ec0 100644
--- a/sqllineage/utils/sqlparse.py
+++ b/sqllineage/utils/sqlparse.py
@@ -20,6 +20,14 @@ from sqllineage.utils.entities import SubQueryTuple
 def is_subquery(token: TokenList) -> bool:
     flag = False
     if isinstance(token, Parenthesis):
+        # in case of subquery in nested parenthesis, find the innermost one first
+        while True:
+            _, sub_paren = token.token_next_by(i=Parenthesis)
+            if sub_paren is not None:
+                token = sub_paren
+            else:
+                break
+        # check if innermost parenthesis contains SELECT
         _, sub_token = token.token_next_by(m=(DML, "SELECT"))
         if sub_token is not None:
             flag = True


Test patch:
diff --git a/tests/test_columns.py b/tests/test_columns.py
index 73d2f9c..aa5ff3f 100644
--- a/tests/test_columns.py
+++ b/tests/test_columns.py
@@ -209,6 +209,25 @@ FROM tab2"""
     )
 
 
+def test_select_column_using_expression_in_parenthesis():
+    sql = """INSERT OVERWRITE TABLE tab1
+SELECT (col1 + col2) AS col3
+FROM tab2"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("col1", "tab2"),
+                ColumnQualifierTuple("col3", "tab1"),
+            ),
+            (
+                ColumnQualifierTuple("col2", "tab2"),
+                ColumnQualifierTuple("col3", "tab1"),
+            ),
+        ],
+    )
+
+
 def test_select_column_using_expression_with_table_qualifier_without_column_alias():
     sql = """INSERT OVERWRITE TABLE tab1
 SELECT a.col1 + a.col2 + a.col3 + a.col4
diff --git a/tests/test_select.py b/tests/test_select.py
index 17f03be..15ffcf8 100644
--- a/tests/test_select.py
+++ b/tests/test_select.py
@@ -88,6 +88,20 @@ def test_select_count():
 
 def test_select_subquery():
     assert_table_lineage_equal("SELECT col1 FROM (SELECT col1 FROM tab1) dt", {"tab1"})
+    # with an extra space
+    assert_table_lineage_equal("SELECT col1 FROM ( SELECT col1 FROM tab1) dt", {"tab1"})
+
+
+def test_select_subquery_with_two_parenthesis():
+    assert_table_lineage_equal(
+        "SELECT col1 FROM ((SELECT col1 FROM tab1)) dt", {"tab1"}
+    )
+
+
+def test_select_subquery_with_more_parenthesis():
+    assert_table_lineage_equal(
+        "SELECT col1 FROM (((((((SELECT col1 FROM tab1))))))) dt", {"tab1"}
+    )
 
 
 def test_select_subquery_in_case():