Unable to select right table join key from join output

[cpcloud]

Checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of Polars.

Reproducible example

left = pl.DataFrame({"x": [1, 2, 3, 4]}).lazy()
right = pl.DataFrame({"x": [3, 2, 1]}).lazy()

left.join(right, on="x", how="left").select(
    # what goes here to select right["x"]
)

Log output

No response

Issue description

I would like to be able to operate on columns coming from the right side of a join, without having to rename them beforehand.

Expected behavior

I would expect there to be some way to select the right side join key without having to rename.

Installed versions

--------Version info---------
Polars:               0.20.0
Index type:           UInt32
Platform:             Linux-6.1.67-x86_64-with-glibc2.38
Python:               3.10.13 (main, Aug 24 2023, 12:59:26) [GCC 12.3.0]

----Optional dependencies----
adbc_driver_manager:  <not installed>
cloudpickle:          3.0.0
connectorx:           <not installed>
deltalake:            0.14.0
fsspec:               2023.6.0
gevent:               <not installed>
matplotlib:           3.8.2
numpy:                1.26.2
openpyxl:             <not installed>
pandas:               2.1.4
pyarrow:              14.0.1
pydantic:             2.5.2
pyiceberg:            <not installed>
pyxlsb:               <not installed>
sqlalchemy:           1.4.50
xlsx2csv:             <not installed>
xlsxwriter:           <not installed>

[ritchie46]

The right key is redundant they will always be the same so polars doesn't create it. Select "x".

[cpcloud]

The right key is redundant they will always be the same so polars doesn't create it. Select "x".

Is it?

Joins in other systems don't work that way and I think that's only true when the join is an inner join and the predicates are all equality predicates. Here's DuckDB:

D create or replace table t as select unnest([1, 2, 3, 4]) x;
D create or replace table s as select unnest([3, 2, 1]) x;
D select t.x as t_x, s.x as s_x from t left join s using (x);
┌───────┬───────┐
│  t_x  │  s_x  │
│ int32 │ int32 │
├───────┼───────┤
│     1 │     1 │
│     2 │     2 │
│     3 │     3 │
│     4 │       │
└───────┴───────┘

s.x, the right key, has different values than t.x.

[cmdlineluser]

This functionality was also asked about on SO earlier: https://stackoverflow.com/questions/77684593/

User wanted ham_right without having to manually .with_columns

df = pl.DataFrame({
    "foo": [1, 2, 3],
    "bar": [6.0, 7.0, 8.0],
    "ham": ["a", "b", "c"],
})

other_df = pl.DataFrame({
    "apple": ["x", None, "z"],
    "ham": ["a", "b", "d"],
})

df.join(other_df.with_columns(pl.col("ham").name.suffix("_right")), on="ham", how="left")

# shape: (3, 5)
# ┌─────┬─────┬─────┬───────┬───────────┐
# │ foo ┆ bar ┆ ham ┆ apple ┆ ham_right │
# │ --- ┆ --- ┆ --- ┆ ---   ┆ ---       │
# │ i64 ┆ f64 ┆ str ┆ str   ┆ str       │
# ╞═════╪═════╪═════╪═══════╪═══════════╡
# │ 1   ┆ 6.0 ┆ a   ┆ x     ┆ a         │
# │ 2   ┆ 7.0 ┆ b   ┆ null  ┆ b         │
# │ 3   ┆ 8.0 ┆ c   ┆ null  ┆ null      │
# └─────┴─────┴─────┴───────┴───────────┘

[ritchie46]

Oh, right. I thought about inner join. My bad.

[ritchie46]

Yeap, good point. We changed this behavior for outer joins. Might need to do that for left as well.

[deanm0000]

join_asof too please.

#6165

[mcrumiller]

If someone is diving in to determine which columns are returned, #9621 is another good one to address. Currently when you join on an expression, you may or may not end up with an unexpected literal column returned.

[mcrumiller]

@cpcloud for the time being, left joins will always either return the left-column's value or a null, so you can proxy this with something like:

import polars as pl
from polars import col, when

left = pl.DataFrame({"x": [1, 2, 3, 4]})
right = pl.DataFrame({"x": [3, 2, 1]})

def join_with_right_columns(left, right, on, *args, **kwargs):
    return left.join(right.with_columns(join_success=pl.lit(True)), on=on, *args, **kwargs).with_columns(
        when(col("join_success").is_not_null()).then(x).alias(f"{x}_right")
        for x in on
    ).drop("join_success")

out = join_with_right_columns(left, right, on="x", how="left")
print(out)

shape: (4, 2)
┌─────┬─────────┐
│ x   ┆ x_right │
│ --- ┆ ---     │
│ i64 ┆ i64     │
╞═════╪═════════╡
│ 1   ┆ 1       │
│ 2   ┆ 2       │
│ 3   ┆ 3       │
│ 4   ┆ null    │
└─────┴─────────┘

[Arengard]

Hi Phillip just copy the the join column :)

left = pl.DataFrame({"x": [1, 2, 3, 4]}).lazy()
right = pl.DataFrame({"x": [3, 2, 1], "right_x": [3, 2, 1]}).lazy()

left.join(right, on="x", how="left").collect()

[deanm0000]

import polars as pl
from polars import col, when

left = pl.DataFrame({"x": [1, 2, 3, 4]})
right = pl.DataFrame({"x": [3, 2, 1]})

def join_with_right_columns(left, right, on, *args, **kwargs):
    return left.join(right.with_columns(join_success=pl.lit(True)), on=on, *args, **kwargs).with_columns(
        when(col("join_success").is_not_null()).then(x).alias(f"{x}_right")
        for x in on
    ).drop("join_success")

out = join_with_right_columns(left, right, on="x", how="left")
print(out)

shape: (4, 2)
┌─────┬─────────┐
│ x   ┆ x_right │
│ --- ┆ ---     │
│ i64 ┆ i64     │
╞═════╪═════════╡
│ 1   ┆ 1       │
│ 2   ┆ 2       │
│ 3   ┆ 3       │
│ 4   ┆ null    │
└─────┴─────────┘

Wouldn't it be simpler to just do right.with_columns(pl.col(on).name.suffix("_right")

[stinodego]

Closing in favor of #13441

[cpcloud]

@stinodego Did you mean to link a different issue?

[stinodego]

Yes, updated. We will implement a left_coalesce join and the default join will be as you request it.