Column lineage does not traverse through CTE containing uppercase letters

[martinswan]

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:

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:

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.

[maoxingda]

Caused by capitalization, if the SQL is modified to all lowercase, the result will be accurate.

[martinswan]

Thanks @maoxingda for your helpful response!

Would you consider it a bug that it needs to be lowercase, or is transforming to lowercase a best-practice that is (or should be) documented?

[maoxingda]

It's because most databases are case-insensitive, so the underlying layer of sqllineage uniformly converts all identifiers to lowercase. Therefore, currently, lineage analysis is not sensitive to uppercase SQL.

[martinswan]

Thanks. I'll close this issue since it sounds like it's a known bug that has already been considered.

[reata]

Thanks @maoxingda for the background. Yes we want to enforce case-insensitivity in lineage analysis in short term.

However, I'm afraid this is indeed a bug introduced via fbad73a and released in v1.4.9.

Converting all identifiers to lower case is just implementation details, the real intention is that for all the following SQL, they should generate the same lineage result:

WITH CTE AS (SELECT name FROM person) SELECT name FROM cte;
WITH cte AS (SELECT name FROM person) SELECT name FROM CTE;
WITH cte AS (SELECT name FROM person) SELECT name FROM cTe;
WITH ctE AS (SELECT name FROM person) SELECT name FROM cte;
WITH CtE AS (SELECT name FROM person) SELECT name FROM Cte;

because in a case-insensitive SQL database, they're indeed the same.