Issue merging on multiple columns

[Pankick]

Issue

When merging two data frames on multiple columns, the result is wrong.
This issue was caught on merging two large parquet data, but is reproducible with small samples below:

import pandas as pd
import numpy as np
import duckdb
duckdb.__version__

'0.2.9'

df1 = pd.DataFrame({
    'id_1': [1, 1, 1, 2, 2],
    'agedate': np.array(['2010-01-01','2010-02-01','2010-03-01','2020-02-01', '2020-03-01']).astype('datetime64[D]'),
    'age': [1, 2, 3, 1, 2],
    'v': [1.1, 1.2, 1.3, 2.1, 2.2]
})

df2 = pd.DataFrame({
    'id_1': [1, 1, 2],
    'agedate': np.array(['2010-01-01','2010-02-01', '2020-03-01']).astype('datetime64[D]'),
    'v2': [11.1, 11.2, 21.2]
})

Results when merging with Pandas:

pd_result = df1.merge(df2, on=['id_1', 'agedate'], how='left')
pd_result

	id_1	agedate	age	v	v2
0	1	2010-01-01	1	1.1	11.1
1	1	2010-02-01	2	1.2	11.2
2	1	2010-03-01	3	1.3	NaN
3	2	2020-02-01	1	2.1	NaN
4	2	2020-03-01	2	2.2	21.2

Results when merging with duckdb:

con = duckdb.connect()
con.register('df1', df1)
con.register('df2', df2)
query = """SELECT * from df1
LEFT  OUTER JOIN df2
ON (df1.id_1=df2.id_1 and df1.agedate=df2.agedate)"""

result = con.execute(query).fetchdf()
result

	id_1	agedate	age	v	v2
0	1.0	2010-01-01	1	1.1	11.1
1	1.0	2010-02-01	2	1.2	11.2
2	2.0	2020-03-01	2	2.2	21.2
3	NaN	NaT	3	1.3	NaN
4	NaN	NaT	1	2.1	NaN

This happens either merging with parquet data or in-memory data.

[hannes]

Maybe @pdet can have a look when he's awake again?

[pdet]

The issue here is that the data frames that are being joined have columns with the same name, so the id_1 agedate we see in the result from duckdb are actually from df2.
I'll make a change to add a sufix counter of fetchdf() outputs if there are multiple columns with the same name.

@Pankick Meanwhile, a workaround for this issue is projecting the columns you want. e.g.,

SELECT df1.id_1, df1.agedate ,age,v,v2 from df1
LEFT  OUTER JOIN df2
ON (df1.id_1=df2.id_1 and df1.agedate=df2.agedate) 

[omo]

Unsolicited triage bot here: It seems this can be closed? @pdet

[pdet]

Looks like it, thanks for pointing it out! :-)