[LCA]: Ability replace Lateral Alias with actual column name and Expression

[sundarshankar89]

Is there an existing issue for this?

• [X] I have searched the existing issues

Category of feature request

Transpile

Problem statement

Given the following Snowflake SQL:

SELECT column_a as customer_id
FROM mytable
WHERE customer_id = '123'

The following Spark SQL should be returned because of the different alias scoping used in Spark SQL

SELECT column_a as customer_id
FROM mytable
WHERE column_a = '123'

https://www.databricks.com/blog/introducing-support-lateral-column-alias

Proposed Solution

NA

Additional Context

No response

[sundarshankar89]

@ericvergnaud this might be an issue you can tackle.

[sundarshankar89]

more such examples are present under lca_utils.py on how we tackle we haven't covered all combinations but you can refer how we do preprocessing using python prototype.

[ericvergnaud]

@sundarshankar89 re the output, did you mean:

SELECT column_a as customer_id
FROM mytable
WHERE column_a = '123'

?

[sundarshankar89]

Just edited the description.

Also checkout test_lca_utils.py it has few more examples with LCA in sourcesystem.