Repository: reata/sqllineage

Issue:
Incorrect Column Lineage With Nested Cast
SQL:
```sql
INSERT OVERWRITE TABLE tab1
SELECT cast(cast(col1 AS int) AS int) AS col1
FROM tab2;
```

Result:
```bash
$ sqllineage -f foo.sql -l column
<default>.tab1.col1 <- <default>.tab2.cast
```

Expected:
```
<default>.tab1.col1 <- <default>.tab2.col1
```

Tested against v1.3.4, casting without nested structure works fine.

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

```diff
@@           Coverage Diff           @@
##           master     #241   +/-   ##
=======================================
  Coverage   99.01%   99.01%           
=======================================
  Files          20       20           
  Lines         915      918    +3     
=======================================
+ Hits          906      909    +3     
  Misses          9        9           
```


| [Impacted Files](https://codecov.io/gh/reata/sqllineage/pull/241?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) | Coverage Δ | |
|---|---|---|
| [sqllineage/core/models.py](https://codecov.io/gh/reata/sqllineage/pull/241/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-c3FsbGluZWFnZS9jb3JlL21vZGVscy5weQ==) | `100.00% <100.00%> (ø)` | |
| [sqllineage/utils/sqlparse.py](https://codecov.io/gh/reata/sqllineage/pull/241/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-c3FsbGluZWFnZS91dGlscy9zcWxwYXJzZS5weQ==) | `100.00% <100.00%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/reata/sqllineage/pull/241?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/reata/sqllineage/pull/241?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None). Last update [bef4d3a...cccd4db](https://codecov.io/gh/reata/sqllineage/pull/241?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).


Patch:
diff --git a/sqllineage/core/models.py b/sqllineage/core/models.py
index 9dd1821..dc12336 100644
--- a/sqllineage/core/models.py
+++ b/sqllineage/core/models.py
@@ -288,11 +288,14 @@ class Column:
             ]
         elif isinstance(token, Identifier):
             real_name = token.get_real_name()
-            if real_name is None or (
-                real_name == "decimal" and isinstance(token.tokens[-1], Function)
-            ):
+            if (
                 # real name is None: col1=1 AS int
+                real_name is None
                 # real_name is decimal: case when col1 > 0 then col2 else col3 end as decimal(18, 0)
+                or (real_name == "decimal" and isinstance(token.tokens[-1], Function))
+                # real_name is cast: cast(col1 AS string) AS string
+                or (real_name == "cast" and isinstance(token.tokens[0], Function))
+            ):
                 source_columns = [
                     cqt
                     for tk in token.get_sublists()
diff --git a/sqllineage/utils/sqlparse.py b/sqllineage/utils/sqlparse.py
index 0f5d322..c55aac0 100644
--- a/sqllineage/utils/sqlparse.py
+++ b/sqllineage/utils/sqlparse.py
@@ -17,16 +17,21 @@ from sqlparse.utils import recurse
 from sqllineage.utils.entities import SubQueryTuple
 
 
+def get_innermost_parenthesis(token: Parenthesis):
+    # in case of subquery in nested parenthesis, find the innermost one first
+    while True:
+        idx, sub_paren = token.token_next_by(i=Parenthesis)
+        if sub_paren is not None and idx == 1:
+            token = sub_paren
+        else:
+            break
+    return token
+
+
 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:
-            idx, sub_paren = token.token_next_by(i=Parenthesis)
-            if sub_paren is not None and idx == 1:
-                token = sub_paren
-            else:
-                break
+        token = get_innermost_parenthesis(token)
         # check if innermost parenthesis contains SELECT
         _, sub_token = token.token_next_by(m=(DML, "SELECT"))
         if sub_token is not None:
@@ -68,7 +73,9 @@ def get_subquery_parentheses(
             elif is_subquery(tk):
                 subquery.append(SubQueryTuple(tk, token.get_real_name()))
     if is_subquery(target):
-        subquery = [SubQueryTuple(target, token.get_real_name())]
+        subquery = [
+            SubQueryTuple(get_innermost_parenthesis(target), token.get_real_name())
+        ]
     return subquery
 
 


Test patch:
diff --git a/tests/test_columns.py b/tests/test_columns.py
index 892034d..dc632c5 100644
--- a/tests/test_columns.py
+++ b/tests/test_columns.py
@@ -395,6 +395,16 @@ FROM (SELECT col1 FROM tab2)"""
     )
 
 
+def test_select_column_in_subquery_with_two_parenthesis():
+    sql = """INSERT OVERWRITE TABLE tab1
+SELECT col1
+FROM ((SELECT col1 FROM tab2)) dt"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+
+
 def test_select_column_from_table_join():
     sql = """INSERT OVERWRITE TABLE tab1
 SELECT tab2.col1,
@@ -567,6 +577,26 @@ FROM tab2"""
     )
 
 
+@pytest.mark.parametrize(
+    "dtype", ["string", "timestamp", "date", "datetime", "decimal(18, 0)"]
+)
+def test_nested_cast_to_data_type(dtype):
+    sql = f"""INSERT OVERWRITE TABLE tab1
+SELECT cast(cast(col1 AS {dtype}) AS {dtype}) AS col1
+FROM tab2"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+    sql = f"""INSERT OVERWRITE TABLE tab1
+SELECT cast(cast(cast(cast(cast(col1 AS {dtype}) AS {dtype}) AS {dtype}) AS {dtype}) AS {dtype}) AS col1
+FROM tab2"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+
+
 @pytest.mark.parametrize(
     "dtype", ["string", "timestamp", "date", "datetime", "decimal(18, 0)"]
 )