Change join_where to not allow ambiguous naming but do allow interchangeable order

[thomasfrederikhoeck]

Description

The new functionallity in #18365 is really a cool addition - nice work!

When looking at the docstring I noticed that the order of the columns in the *predicates matter. I think this is a potential for errors when coming from either SQL or DuckDB. I would suggest the following instead:

1. Raise if both DataFrames have a column named the same which is used in the join predicate. The following should raise a "Ambiguous Column" error:

df1 = pl.DataFrame({"id": [1, 2, 3]})
df2 = pl.DataFrame({"id": [1, 2, 3]})

df.join_where(df, pl.col("id") >= pl.col("id"))

where as the following would work:

df1 = pl.DataFrame({"id": [1, 2, 3]})
df2 = pl.DataFrame({"id": [1, 2, 3]})
df1.join_where(df2.rename({"id":"id_2"}), pl.col("id") >= pl.col("id_2"))

The following would also be allowed since the non-unique name is not in the join-predicates:

df1 = pl.DataFrame({"id": [1, 2, 3], "id_2": [2, 3, 4]})
df2 = pl.DataFrame({"id": [1, 2, 3]})

df.join_where(df, pl.col("id") >= pl.col("id_2"))

2. Disregard if column is left- or right-handside:

Since the columns are now guaranteed to be unique you make the order not matter such that the following test should be succesfull:

df1 = pl.DataFrame({"id": [1, 2, 3]})
df2 = pl.DataFrame({"id_2": [1, 2, 3]})


polars.testing.assert_frame_equal(
    df1.join_where(df2, pl.col("id") >= pl.col("id_2")),
    df1.join_where(df2, pl.col("id_2") <= pl.col("id")),
    check_row_order = False
)

[cmdlineluser]

There is some work going on here:

• refactor: Change join_where semantics #18640

[ritchie46]

I think this can be closed now?

[thomasfrederikhoeck]

@ritchie46 Yes, I will close! The semantics you landed on seem very sound!