topic Re: Issue with Lateral Column Alias (LCA) in Warehousing & Analytics

Issue with Lateral Column Alias (LCA)

paulocorrea — Tue, 30 Jul 2024 15:06:35 GMT

I have a query using LCA. When referencing another table that has a column with the same name as the column used as LCA, the behavior of the query changes and it starts referencing the table column instead of the column that is already in the select and was previously used. Is there a way to ensure that the column used will always be the one previously defined in the select itself?

Re: Issue with Lateral Column Alias (LCA)

paulocorrea — Thu, 01 Aug 2024 13:16:37 GMT

Hi @Retired_mod,

But in this case I would have to qualify the column that was defined in the select because it is the one that was no longer used when adding a table in the join that has exactly the same column name.

I don't know if there is a standard identifier that can be used to indicate that the column I am referencing is the LCA.

I got around it by adding a 'local -' prefix to the name of each column, but I found Databricks' behavior to be too risky in this case, since including a new column in a table can change the behavior of existing queries.

Re: Issue with Lateral Column Alias (LCA)

paulocorrea — Thu, 01 Aug 2024 13:28:21 GMT

See this other example. The only change was the inclusion of a new table with columns with the same name as the columns defined in the select:

Re: Issue with Lateral Column Alias (LCA)

ClausStier — Fri, 23 Aug 2024 10:59:21 GMT

Hi @Kaniz_Fatma,

we had the same problem as @paulocorrea.

That's why it would be correct for to me to throw an error on ambiguous columns and the LCA could/must be addressed with a default identifier.

Thanks