Support Lateral Column Alias Reference Analyzing

[maoxingda]

SQL

insert into public.tgt_tbl1
(
    id
)
select
    sq.id
from
    (
        select
            name      as user_name,
            user_name as id -- backward reference
        from
            public.src_tbl1
    ) sq
;

To Reproduce
Note here we refer to SQL provided in prior step as stored in a file named test.sql

from sqllineage.runner import LineageRunner
with open("test.sql") as f:
    sql = f.read()
lr = LineageRunner(sql, dialect='redshift')
lr.print_column_lineage()

public.tgt_tbl1.id <- sq.id <- public.src_tbl1.user_name

Expected behavior

public.tgt_tbl1.id <- sq.id <- public.src_tbl1.name

Python version (available via python --version)

• 3.11.5

SQLLineage version (available via sqllineage --version):

• 1.4.9

[reata]

This feature is officially called lateral column alias reference, see https://aws.amazon.com/about-aws/whats-new/2018/08/amazon-redshift-announces-support-for-lateral-column-alias-reference/

It's not universally supported by every SQL dialects. We will consider adding support but it won't be in high priority.

[reata]

Tried a few open source SQL database/data warehouse, only sparksql support this feature at the end of 2023.

dialect	version	support lateral column alias reference
mysql	8.2.0	no
postgres	16.1	no
hive	3.1.3	no
sparksql	3.5.0	yes
trino	435	no

SparkSQL support was added since 3.4.0 via SPARK-27561, released April 13, 2023.