[BUG]: Snowflake homonyms should be resolved before dealiasing

[ericvergnaud]

Is there an existing issue for this?

• I have searched the existing issues

Category of Bug / Issue

TranspileLateralColumnAliasError

Current Behavior

Our current implementation of SnowFlake LCAs assumes that homonym aliases have priority over columns, which might not be the case.
Consider for example the following:
SELECT substr(ca_zip, 1, 5) as ca_zip FROM ca WHERE ca_zip in ('12345', '23456')
It's unclear whether the ca_zip in the WHERE clause should resolve to the ca_zip column, or the ca_zip alias (which translates to a substr expression).
Morevover, consider the following:
SELECT substr(some_code, 1, 5) as ca_zip FROM ca WHERE ca_zip in ('12345', '23456')
If a ca_zip column exists, even though not referenced in the query, then SnowFlake might resolve the ca_zip in the WHERE clause to that column rather than the alias.

See #1242 for examples of the current dealiasing logic.

We need to specify the expected behavior accurately and potentially fix our implementation.

Expected Behavior

Dealias following SnowFlake logic

Steps To Reproduce

No response

Relevant log output or Exception details

No response

Sample Query

No response

Operating System

macOS

Version

latest via Databricks CLI