Repository: reata/sqllineage

Issue:
subquery mistake alias as table name in visualiztion
**Describe the bug**

Alias of a subquery show as a Table node in Visualizes Table/Column Lineage

**SQL**

```sql
CREATE TABLE main.tab1 AS (
  SELECT * FROM (
    SELECT T0.* FROM (SELECT * FROM main.tab0) T0 WHERE T0.num < 100
  )
)
```

**To Reproduce**
*Note here we refer to SQL provided in prior step as stored in a file named `test.sql`*

    sqllineage -f test.sql --dialect=ansi -g

Web UI (Web User Interface): 

<img width="385" alt="alias_table" src="https://github.com/reata/sqllineage/assets/17078500/7110a38e-f4a4-4b85-9074-6b18444c8181">


**Expected behavior**

There should be no table node for alias of subquery.

**Python version (available via `python --version`)**

 - 3.10.13

**SQLLineage version (available via `sqllineage --version`):**

 - 1.4.9


Discussion:
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/514?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 [(`4ac7fde`)](https://app.codecov.io/gh/reata/sqllineage/commit/4ac7fde375f597c541c33d549cf1be4140cdaa2e?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) 99.41% compared to head [(`b0561c9`)](https://app.codecov.io/gh/reata/sqllineage/pull/514?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     #514      +/-   ##
==========================================
- Coverage   99.41%   99.41%   -0.01%     
==========================================
  Files          40       40              
  Lines        2056     2055       -1     
==========================================
- Hits         2044     2043       -1     
  Misses         12       12              
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/reata/sqllineage/pull/514?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/models.py b/sqllineage/core/models.py
index 9101e83..54ae55f 100644
--- a/sqllineage/core/models.py
+++ b/sqllineage/core/models.py
@@ -147,7 +147,15 @@ class Column:
         """
         self._parent: Set[Union[Path, Table, SubQuery]] = set()
         self.raw_name = escape_identifier_name(name)
-        self.source_columns = kwargs.pop("source_columns", ((self.raw_name, None),))
+        self.source_columns = (
+            (
+                escape_identifier_name(raw_name),
+                escape_identifier_name(qualifier) if qualifier is not None else None,
+            )
+            for raw_name, qualifier in kwargs.pop(
+                "source_columns", ((self.raw_name, None),)
+            )
+        )
 
     def __str__(self):
         return (
diff --git a/sqllineage/core/parser/__init__.py b/sqllineage/core/parser/__init__.py
index 6f1dd25..73d24af 100644
--- a/sqllineage/core/parser/__init__.py
+++ b/sqllineage/core/parser/__init__.py
@@ -25,8 +25,7 @@ class SourceHandlerMixin:
                 if len(holder.write) > 1:
                     raise SQLLineageException
                 tgt_tbl = list(holder.write)[0]
-                for idx in range(len(col_grp)):
-                    tgt_col = col_grp[idx]
+                for idx, tgt_col in enumerate(col_grp):
                     tgt_col.parent = tgt_tbl
                     for src_col in tgt_col.to_source_columns(
                         self.get_alias_mapping_from_table_group(tbl_grp, holder)


Test patch:
diff --git a/tests/sql/column/test_column_select_column.py b/tests/sql/column/test_column_select_column.py
index f836a02..d4c96db 100644
--- a/tests/sql/column/test_column_select_column.py
+++ b/tests/sql/column/test_column_select_column.py
@@ -173,3 +173,19 @@ FROM tab2 a"""
             ),
         ],
     )
+
+
+def test_select_column_name_case_insensitive():
+    sql = """INSERT INTO tab1
+SELECT col1,
+COL1
+FROM tab2"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("col1", "tab2"),
+                ColumnQualifierTuple("col1", "tab1"),
+            )
+        ],
+    )
diff --git a/tests/sql/column/test_column_select_from_subquery.py b/tests/sql/column/test_column_select_from_subquery.py
index ae80837..7264e3e 100644
--- a/tests/sql/column/test_column_select_from_subquery.py
+++ b/tests/sql/column/test_column_select_from_subquery.py
@@ -109,3 +109,20 @@ WHERE rn = 1"""
             (ColumnQualifierTuple("col2", "tab2"), ColumnQualifierTuple("rn", "tab1")),
         ],
     )
+
+
+def test_select_column_in_subquery_alias_and_qualifier_case_insensitive():
+    sql = """INSERT INTO tab1
+SELECT DT.col1
+FROM (SELECT col1 FROM tab2) dt"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+    sql = """INSERT INTO tab1
+SELECT dt.col1
+FROM (SELECT col1 FROM tab2) DT"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )