Repository: reata/sqllineage

Issue:
Column lineage does not traverse through CTE containing uppercase letters
For SQL code with a CTE, column lineage is ending at the CTE and not traversing through to the source tables or expressions referenced in the CTE.

For example, I have the following code in test.sql:

```sql
WITH EmployeeDepartmentCTE AS (
    SELECT
        E.EmployeeName,
        D.DepartmentName
    FROM Employees AS E
    INNER JOIN Departments AS D
        ON E.DepartmentID = D.DepartmentID
)

INSERT INTO EmployeeDepartment

SELECT
    EmployeeName,
    DepartmentName
FROM EmployeeDepartmentCTE;
```

When I run the following command I get the subsequent output:
```shell
sqllineage -f test.sql -l column
```

output:
```
<default>.employeedepartment.departmentname <- <default>.employeedepartmentcte.departmentname
<default>.employeedepartment.employeename <- <default>.employeedepartmentcte.employeename
```

expected output:
```
<default>.employeedepartment.departmentname <- <default>.department.departmentname
<default>.employeedepartment.employeename <- <default>.employee.employeename
```

**Python version:**
 - 3.12.1
 
**SQLLineage version:**
 - 1.4.9

**Additional context**
If there are multiple CTE's with one referencing another, the lineage stops at the first CTE.

Discussion:
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/550?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 [(`fcea36b`)](https://app.codecov.io/gh/reata/sqllineage/commit/fcea36bc8ec703fda9eb52674ff2be7b6b3130b4?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) 99.49% compared to head [(`cfb5d2c`)](https://app.codecov.io/gh/reata/sqllineage/pull/550?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) 99.49%.


<details><summary>Additional details and impacted files</summary>


```diff
@@           Coverage Diff           @@
##           master     #550   +/-   ##
=======================================
  Coverage   99.49%   99.49%           
=======================================
  Files          41       41           
  Lines        2196     2197    +1     
=======================================
+ Hits         2185     2186    +1     
  Misses         11       11           
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/reata/sqllineage/pull/550?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 fd96c60..8eb012f 100644
--- a/sqllineage/core/models.py
+++ b/sqllineage/core/models.py
@@ -58,7 +58,7 @@ class Table:
             self.raw_name = escape_identifier_name(table_name)
             if schema:
                 warnings.warn("Name is in schema.table format, schema param is ignored")
-        self.alias = kwargs.pop("alias", self.raw_name)
+        self.alias = escape_identifier_name(kwargs.pop("alias", self.raw_name))
 
     def __str__(self):
         return f"{self.schema}.{self.raw_name}"
diff --git a/sqllineage/core/parser/sqlfluff/extractors/base.py b/sqllineage/core/parser/sqlfluff/extractors/base.py
index 1f1ca36..275e3f1 100644
--- a/sqllineage/core/parser/sqlfluff/extractors/base.py
+++ b/sqllineage/core/parser/sqlfluff/extractors/base.py
@@ -156,7 +156,7 @@ class BaseExtractor:
                     )
                 if "." not in table_identifier.raw:
                     cte_dict = {s.alias: s for s in holder.cte}
-                    cte = cte_dict.get(table_identifier.raw)
+                    cte = cte_dict.get(escape_identifier_name(table_identifier.raw))
                     if cte is not None:
                         # could reference CTE with or without alias
                         tables.append(
diff --git a/sqllineage/core/parser/sqlparse/handlers/source.py b/sqllineage/core/parser/sqlparse/handlers/source.py
index 8f5b77c..f6c62b4 100644
--- a/sqllineage/core/parser/sqlparse/handlers/source.py
+++ b/sqllineage/core/parser/sqlparse/handlers/source.py
@@ -27,6 +27,7 @@ from sqllineage.core.parser.sqlparse.utils import (
     is_values_clause,
 )
 from sqllineage.exceptions import SQLLineageException
+from sqllineage.utils.helpers import escape_identifier_name
 
 
 class SourceHandler(SourceHandlerMixin, NextTokenBaseHandler):
@@ -151,7 +152,9 @@ class SourceHandler(SourceHandlerMixin, NextTokenBaseHandler):
             else:
                 cte_dict = {s.alias: s for s in holder.cte}
                 if "." not in identifier.value:
-                    cte = cte_dict.get(identifier.get_real_name())
+                    cte = cte_dict.get(
+                        escape_identifier_name(identifier.get_real_name())
+                    )
                     if cte is not None:
                         # could reference CTE with or without alias
                         read = SqlParseSubQuery.of(


Test patch:
diff --git a/tests/sql/column/test_column_select_column.py b/tests/sql/column/test_column_select_column.py
index f3aa3ea..c946aa4 100644
--- a/tests/sql/column/test_column_select_column.py
+++ b/tests/sql/column/test_column_select_column.py
@@ -96,6 +96,30 @@ FROM tab2 AS t"""
     )
 
 
+def test_select_column_with_table_qualifier_case_insensitive():
+    sql = """INSERT INTO tab1
+SELECT TA.col1
+FROM tab2 AS ta"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+    sql = """INSERT INTO tab1
+SELECT ta.col1
+FROM tab2 AS TA"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+    sql = """INSERT INTO tab1
+SELECT Ta.col1
+FROM tab2 AS tA"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+
+
 def test_select_columns():
     sql = """INSERT INTO tab1
 SELECT col1,
diff --git a/tests/sql/column/test_column_select_from_cte.py b/tests/sql/column/test_column_select_from_cte.py
index d892e44..17fa581 100644
--- a/tests/sql/column/test_column_select_from_cte.py
+++ b/tests/sql/column/test_column_select_from_cte.py
@@ -101,3 +101,27 @@ def test_cte_inside_insert_in_parenthesis():
         ],
         test_sqlparse=False,
     )
+
+
+def test_select_column_from_cte_case_insensitive():
+    sql = """WITH CTE1 AS (SELECT col1 FROM tab2)
+INSERT INTO tab1
+SELECT col1 FROM cte1"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+    sql = """WITH cte1 AS (SELECT col1 FROM tab2)
+INSERT INTO tab1
+SELECT col1 FROM CTE1"""
+    assert_column_lineage_equal(
+        sql,
+        [(ColumnQualifierTuple("col1", "tab2"), ColumnQualifierTuple("col1", "tab1"))],
+    )
+    sql = """WITH CTe1 AS (SELECT col1 FROM tab2)
+INSERT INTO tab1
+SELECT col1 FROM cTE1"""
+    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 7264e3e..389b0a0 100644
--- a/tests/sql/column/test_column_select_from_subquery.py
+++ b/tests/sql/column/test_column_select_from_subquery.py
@@ -126,3 +126,10 @@ FROM (SELECT col1 FROM tab2) DT"""
         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"))],
+    )