duplicate columns cause join WHERE expressions to fail

[ukclivecox]

Describe the bug

When there are duplicate columns after a join SQL expressions using table aliases fail and the SQL returns zero rows.

To Reproduce

# test for left join on multiple tables with rows
import daft

df1 = daft.from_pydict({"idx":[1,2],"val":[10,20]})

df2 = daft.from_pydict({"idx":[3],"score":[0.1]})

df3 = daft.from_pydict({"idx":[1],"score":[0.1]})

df_sql = daft.sql("select * from df1 left join df2 on (df1.idx=df2.idx) left join df3 on (df1.idx=df3.idx) where df3.score > 0").show()

This produces:

If one renames one table column so there are no duplicates this works.

# test for left join on multiple tables with rows
import daft

df1 = daft.from_pydict({"idx":[1,2],"val":[10,20]})

df2 = daft.from_pydict({"idx":[3],"score1":[0.1]})

df3 = daft.from_pydict({"idx":[1],"score":[0.1]})

df_sql = daft.sql("select * from df1 left join df2 on (df1.idx=df2.idx) left join df3 on (df1.idx=df3.idx) where df3.score > 0").show()

I assume this has to do with renaming of dataframe columns to prefix with right.?

Expected behavior

Table aliases work as expected.

Component(s)

SQL

Additional context

Testing with nightly build 0.3.8+dev0019.e4c6f3fa that has additions for joins from #3066

[universalmind303]

removing the sql label as this appears to be the case when joining through the dataframe api as well.

import daft

df1 = daft.from_pydict({"idx":[1,2],"val":[10,20]})
df2 = daft.from_pydict({"idx":[3],"score":[0.1]})
df3 = daft.from_pydict({"idx":[1],"score":[0.1]})

df = (df1
    .join(df2, on="idx", how="left")
    .join(df3, on="idx", how="left")
    .where(col('score') > 0)
    .collect()
)
df

╭───────┬───────┬─────────┬─────────────╮
│ idx   ┆ val   ┆ score   ┆ right.score │
│ ---   ┆ ---   ┆ ---     ┆ ---         │
│ Int64 ┆ Int64 ┆ Float64 ┆ Float64     │
╞═══════╪═══════╪═════════╪═════════════╡
╰───────┴───────┴─────────┴─────────────╯
(No data to display: Materialized dataframe has no rows)

[ukclivecox]

I would add that one could make the case that the Dataframe API has different semantics and you need to use the right. format for where clauses for duplicate columns. However, for SQL this doesn't make sense so needs to be fixed even if Dataframe API stays the same. But I assume the two ideally would act the same - though with SQL there can be various ways to identify a column in the SQL expression.