Repository: reata/sqllineage (python)

Issue:
Using JOIN with ON/USING keyword fails to determine source tables when followed by a parenthesis
I have the following queries:

```
create table target_table as (
    select *
    from source_table_a
    join source_table_b
    on (source_table_a.some_id = source_table_b.some_id))
)
```
```
create table target_table as (
    select *
    from source_table_a
    join source_table_b
    using (some_id)
)
```
This causes the table lineage to not extract `source_table_a` and `source_table_b` as sources. However, when the parenthesis are removed around `source_table_a.some_id = source_table_b.some_id)` and `some_id` it works properly.

Any help or insight would be greatly appreciated! 😀

Pull request description:
Regression on Nested Parenthesis Around SubQuery
Closes #233 

Patch:
diff --git a/sqllineage/core/handlers/source.py b/sqllineage/core/handlers/source.py
index 06492eb..80e3f80 100644
--- a/sqllineage/core/handlers/source.py
+++ b/sqllineage/core/handlers/source.py
@@ -17,7 +17,7 @@ from sqllineage.core.holders import SubQueryLineageHolder
 from sqllineage.core.models import Column, Path, SubQuery, Table
 from sqllineage.exceptions import SQLLineageException
 from sqllineage.utils.constant import EdgeType
-from sqllineage.utils.sqlparse import get_subquery_parentheses
+from sqllineage.utils.sqlparse import get_subquery_parentheses, is_subquery
 
 
 class SourceHandler(NextTokenBaseHandler):
@@ -65,9 +65,13 @@ class SourceHandler(NextTokenBaseHandler):
                     self._get_dataset_from_identifier(identifier, holder)
                 )
         elif isinstance(token, Parenthesis):
-            # SELECT col1 FROM (SELECT col2 FROM tab1), the subquery will be parsed as Parenthesis
-            # This syntax without alias for subquery is invalid in MySQL, while valid for SparkSQL
-            self.tables.append(SubQuery.of(token, None))
+            if is_subquery(token):
+                # SELECT col1 FROM (SELECT col2 FROM tab1), the subquery will be parsed as Parenthesis
+                # This syntax without alias for subquery is invalid in MySQL, while valid for SparkSQL
+                self.tables.append(SubQuery.of(token, None))
+            else:
+                # SELECT * FROM (tab2), which is valid syntax
+                self._handle(token.tokens[1], holder)
         elif token.ttype == Literal.String.Single:
             self.tables.append(Path(token.value))
         else:
diff --git a/sqllineage/utils/sqlparse.py b/sqllineage/utils/sqlparse.py
index 9fe7ec0..0f5d322 100644
--- a/sqllineage/utils/sqlparse.py
+++ b/sqllineage/utils/sqlparse.py
@@ -22,8 +22,8 @@ def is_subquery(token: TokenList) -> bool:
     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:
+            idx, sub_paren = token.token_next_by(i=Parenthesis)
+            if sub_paren is not None and idx == 1:
                 token = sub_paren
             else:
                 break


Test patch:
diff --git a/tests/test_others.py b/tests/test_others.py
index bfe9f2d..1184a93 100644
--- a/tests/test_others.py
+++ b/tests/test_others.py
@@ -41,6 +41,24 @@ def test_create_as():
     )
 
 
+def test_create_as_with_parenthesis_around_select_statement():
+    assert_table_lineage_equal(
+        "CREATE TABLE tab1 AS (SELECT * FROM tab2)", {"tab2"}, {"tab1"}
+    )
+
+
+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_as_with_parenthesis_around_both():
+    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"})