Allow join on different types if upcast is safe

[CaselIT]

Description

It would be nice to allow joining between different data types when upcast is safe, for example i{8,16,32}->i64, u{8,16,32}->u64 etc

Example:

dfI32 = pl.DataFrame({'a': [1,2,3], 'b': list('abc')}).cast({'a': pl.Int32})
dfI64 = pl.DataFrame({'a': [1,2,3], 'c': list('def')})
dfI64.join(dfI32, on='a') # a would keep Int64 type
dfI32.join(dfI64, on='a') # a would become Int64 type (or this could be depending on join type)

Currently both of these error with an exception like

ComputeError: datatypes of join keys don't match - `a`: i64 on left does not match `a`: i32 on right

[CaselIT]

This could also be a kwargs that defaults to false, so that polars by itself does no type conversion, but users can opt into safe upcasts if the want to

[tfiasco]

Is there any progress on this feature? In our company, many data types are uncertain, which has become an obstacle for me in promoting Polars internally.

In Pandas, this operation succeeds without error:

import pandas as pd

df1 = pd.DataFrame({'a': list(range(5))})
df2 = df1.copy()
df2['a'] = df1['a'].astype("UInt8")
result = pd.merge(df1, df2, on='a')

Pandas handles the type mismatch by implicitly casting the join keys, allowing the merge to succeed.

[ritchie46]

We will look into this. Indeed between integer <-> integers it is safe (floats not so much).

[coastalwhite]

Is there maybe a way to instead have a align_types expression. I think that would be more scalable and explicit than doing this?

[ritchie46]

Is there maybe a way to instead have a align_types expression.

That would need to be called before, and return a struct. (if it would be done in the separate expressions it would be way too magic for my liking. I think this is fine as we type resolve more operations.

[landisrm]

Yes, please can we have this! I'm having a tough time mixing pl.read_database calls with pulls using duckdb; one returns my id columns in i32 and the other in i64

[leoliu0]

This would be a great feature. In my case, I wrote a function to cast all integer columns to i64 as an initial step to circumvent this issue before performing any further operations.