Full join on dataframe with only index yields dropped rows

Describe the bug
If I do a full join between a dataframe with content and one only consisting of an index column that index column only bits get dropped.

To Reproduce
See commented out empty additional column. When that is included then we see results in the final dataframe.

ctx = dfn.SessionContext()
    key_frame = ctx.from_pydict(
        {
            "log_time": [1, 3, 5, 7, 9, 11, 13, 15, 17, 19],
            "key_frame": [True, True, True, True, True, True, True, True, True, True]
        }
    )
    query_times = ctx.from_pydict(
        {
            "log_time": [2, 4, 6, 8, 10],
            #"empty": [0, 0, 0, 0, 0]
        }
    )

    print(key_frame)
    print(query_times)
    merged = query_times.join(key_frame, left_on="log_time", right_on="log_time", how="full")
    print(merged)

DataFrame()
+----------+-----------+
| log_time | key_frame |
+----------+-----------+
| 1        | true      |
| 3        | true      |
| 5        | true      |
| 7        | true      |
| 9        | true      |
| 11       | true      |
| 13       | true      |
| 15       | true      |
| 17       | true      |
| 19       | true      |
+----------+-----------+
DataFrame()
+----------+
| log_time |
+----------+
| 2        |
| 4        |
| 6        |
| 8        |
| 10       |
+----------+
DataFrame()
+----------+----------+-----------+
| log_time | log_time | key_frame |
+----------+----------+-----------+
|          | 1        | true      |
|          | 3        | true      |
|          | 5        | true      |
|          | 7        | true      |
|          | 9        | true      |
|          | 11       | true      |
|          | 13       | true      |
|          | 15       | true      |
|          | 17       | true      |
|          | 19       | true      |
+----------+----------+-----------+

Expected behavior
When doing a full join I get back all rows. Effectively merging the dataframes.

Here is a somewhat equivalent in pandas

key_frame_df = pd.DataFrame({
        "log_time": [1, 3, 5, 7, 9, 11, 13, 15, 17, 19],
        "key_frame": [True, True, True, True, True, True, True, True, True, True]
    })
    
    query_times_df = pd.DataFrame({
        "log_time": [2, 4, 6, 8, 10],
        # "empty": [0, 0, 0, 0, 0]  # commented out like in original
    })
    
    
    # Perform full outer join (equivalent to DataFusion's "full" join)
    merged_df = pd.merge(query_times_df, key_frame_df, on="log_time", how="outer")
    print("\nMerged DataFrame (full outer join):")
    print(merged_df)

Merged DataFrame (full outer join):
    log_time key_frame
0          1      True
1          2       NaN
2          3      True
3          4       NaN
4          5      True
5          6       NaN
6          7      True
7          8       NaN
8          9      True
9         10       NaN
10        11      True
11        13      True
12        15      True
13        17      True
14        19      True

Actually the behavior in pyarrow is maybe a more direct comparison

key_table = pa.table(key_frame)
  query_table = pa.table(query_times)
  merged_table = query_table.join(key_table, keys="log_time", join_type="full outer")
  print(ctx.from_arrow(merged_table))

Thanks for the detailed report! I investigated the behavior and confirmed that the root cause is the schema merging during full outer joins.

When one of the input DataFrames contains only the join key (no extra columns), the join schema builder drops the duplicated join key column from that side. Since that side contributes no additional fields, the corresponding rows end up empty and are removed from the final output. This leads to incorrect results for full joins.

I can contribute by adding:

A regression test showing that a full join between
df(log_time)
df(log_time, key_frame)
must return all rows from both sides.
A schema validation test verifying that join key rows from the “index-only” DataFrame are preserved.

If helpful, I can also explore patching the join schema builder to ensure a full join retains rows from both sides even when one side has only the join key.

I'll prepare a PR with the regression test shortly.

I tried this in directly on datafusion, and it works as expected ... which is good 😅

    let a: ArrayRef = Arc::new(StringArray::from(vec!["a", "b", "c", "d"]));
    let b: ArrayRef = Arc::new(Int32Array::from(vec![1, 10, 10, 100]));
    let batch = RecordBatch::try_from_iter(vec![("a", a), ("b", b)])?;

    let bb: ArrayRef = Arc::new(Int32Array::from(vec![2, 20, 30, 40]));
    let batch2 = RecordBatch::try_from_iter(vec![("b", bb)])?;

    ctx.register_batch("t", batch)?;
    let df = ctx.table("t").await?;

    ctx.register_batch("t2", batch2)?;
    let df2 = ctx.table("t2").await?;

    df.join(df2, JoinType::Full, &["b"], &["b"], None)?.show().await?;

results

+---+-----+----+
| a | b   | b  |
+---+-----+----+
| a | 1   |    |
| b | 10  |    |
| c | 10  |    |
| d | 100 |    |
|   |     | 2  |
|   |     | 20 |
|   |     | 30 |
|   |     | 40 |
+---+-----+----+

also following your example, I see that all rows are there, but the values from the left side are dropped

>>> merged.to_pandas()                                                                                                                                                                    19:51:47 [50/903]
    log_time key_frame
0        NaN      True
1        NaN      True
2        NaN      True
3        NaN      True
4        NaN      True
5        NaN      True
6        NaN      True
7        NaN      True
8        NaN      True
9        NaN      True
10       2.0      None
11       4.0      None
12       6.0      None
13       8.0      None
14      10.0      None

and when using the empty column we get the following

>>> merged2.to_pandas()
    log_time  empty key_frame
0        2.0    0.0      None
1        4.0    0.0      None
2        6.0    0.0      None
3        8.0    0.0      None
4       10.0    0.0      None
5        NaN    NaN      True
6        NaN    NaN      True
7        NaN    NaN      True
8        NaN    NaN      True
9        NaN    NaN      True
10       NaN    NaN      True
11       NaN    NaN      True
12       NaN    NaN      True
13       NaN    NaN      True
14       NaN    NaN      True

I am thinking that the left log_time column values are just getting dropped
I will take a closer look in the datafusion-python side :)

fix: use coalesce instead of drop_duplicate_keys for join

thinking more about this , I think we should just not allow drop_duplicate_keys when doing a full outer join.
I think that would be the right behavior because
(1) For a FULL OUTER JOIN, the left and right join keys are NOT equivalent columns
(2) That would resemble the expected from upstream and what other databases do. For example, postgresql https://www.postgresql.org/docs/8.2/queries-table-expressions.html
(3) User can decide on how to merge columns afterwards.

then we can allow something like this:

batch = pa.RecordBatch.from_arrays(
        [pa.array([1, 2, 3]), pa.array(['a', 'b', 'c'])],
        names=["num", "name"],
    )
    batch2 = pa.RecordBatch.from_arrays(
        [pa.array([1, 3, 5]), pa.array([True, True, False])],
        names=["num", "value"],
    )

    df1 = ctx.create_dataframe([[batch]], "l")
    df2 = ctx.create_dataframe([[batch2]], "r")
    df3 = df1.join(df2, on="num", how="full", drop_duplicate_keys=False)
    df3.show()

+-----+------+-----+-------+
| num | name | num | value |
+-----+------+-----+-------+
| 1   | a    | 1   | true  |
| 3   | c    | 3   | true  |
| 2   | b    |     |       |
|     |      | 5   | false |
+-----+------+-----+-------+

then user can do as they want, like coalescing

    df5 = df3.select_exprs(
        "coalesce(l.num, r.num) as num",
        "l.name",
        "r.value"
    )
    df5.show()

+-----+------+-------+
| num | name | value |
+-----+------+-------+
| 1   | a    | true  |
| 3   | c    | true  |
| 2   | b    |       |
| 5   |      | false |
+-----+------+-------+

then no weird semantics + no workarounds + give user the power to decide what to do.

Also created PR for it here #1320

@timsaucer

hey @timsaucer @ntjohnson1 , would you mind sharing your thoughts on this?

I'm traveling all day today so I can't think carefully on this. I'd be fine with this approach if it didn't require the select_exprs since that feels a little unergonomic. The need to track the data frame names separately from the variable seems unfortunate.

I'd like to instead do df.select(F.coalesce(df1.col("num"), df2.col("num")).alias("num") unfortunately I don't think we currently have a syntax like that (or a nice alternative) to dedup columns that have the same name.

So if there was a way to resolve the columns without a specially crafted string I think the proposed direction seems reasonable.

The need to track the data frame names separately from the variable seems unfortunate.

that is the case because of the way we are registering the dataframes with the SessionContext

    df1 = ctx.create_dataframe([[batch]], "l") # the dataframe is named "L" when referenced

In the example, we are doing this because we need it to disambiguate columns with the same name ... which is also our case 😅 (we have two columns that don't represent the same values but have the same name)

I'd like to instead do df.select(F.coalesce(df1.col("num"), df2.col("num")).alias("num") unfortunately I don't think we currently have a syntax like that (or a nice alternative) to dedup columns that have the same name.

right, unfortunately datafusion-python doesn't support that syntax atm (what is that btw? polars? spark-api?), but that is a good feature request though

we can do the following and no crafted string :)

    import datafusion.functions as F
    df6 = df3.select(
        F.coalesce(col("l.num"), col("r.num")).alias("num"),
        col("l.name"),
        col("r.value"),
    )
    df6.show()

then we get

+-----+------+-------+
| num | name | value |
+-----+------+-------+
| 1   | a    | true  |
| 3   | c    | true  |
| 2   | b    |       |
| 5   |      | false |
+-----+------+-------+

fix: use coalesce instead of drop_duplicate_keys for join

@ntjohnson1

I've opened a PR that uses coalesce instead of dropping the left column. Using coalesce seems the right approach since:

It solves this issue (as suggested by the op @ntjohnson1)
Is in line with other major libraries such as pyarrow and polars

@mesejo

I've opened a PR that uses coalesce instead of dropping the left column. Using coalesce seems the right approach since:

cool! thanks @mesejo ! yeah it is definitely an option :)

For me, it depends on how much SQL like we want/need to be + let the user decide the next operation (and hopefully an optimizer fuse operators together where it makes sense)

looking at how polars does it, it is interesting to see that columns get properly "renamed" such that users can also decide afterwards

import polars as pl

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

out = df.join(other_df, on="ham", how="full")
print(out)

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

I haven't checked your PR yet @mesejo but if we are going that direction, we might as well consider column renaming (?)

From an end result perspective I prefer the coalesce direction. I think resolve_duplicates might be a nicer keyword. With my user hat on I don't care too much about HOW that happens under the hood so maybe this also makes things more flexible if we change later. Additionally, special caseing kwargs to not manage certain situations adds complexity.

I think if the user wants to decide the details of how things are handled they can opt out of resolve_duplicates. I just opened a PR for datafusion to try and make resolving ambiguous column dropping and reference clearer so they can actually do so without just doing it in SQL.

@kosiew

I saw you took a look at one of the PRs @kosiew so not sure if you have opinions.

@renato

The current Python join wrapper drops mutually named keys by default for all join types, including full, by projecting away one side’s qualified key column when drop_duplicate_keys is true.

datafusion-python/src/dataframe.rs

Lines 650 to 715 in fcd7056

    
           fn join( 
        
               &self, 
        
               right: PyDataFrame, 
        
               how: &str, 
        
               left_on: Vec<PyBackedStr>, 
        
               right_on: Vec<PyBackedStr>, 
        
               drop_duplicate_keys: bool, 
        
           ) -> PyDataFusionResult<Self> { 
        
               let join_type = match how { 
        
                   "inner" => JoinType::Inner, 
        
                   "left" => JoinType::Left, 
        
                   "right" => JoinType::Right, 
        
                   "full" => JoinType::Full, 
        
                   "semi" => JoinType::LeftSemi, 
        
                   "anti" => JoinType::LeftAnti, 
        
                   how => { 
        
                       return Err(PyDataFusionError::Common(format!( 
        
                           "The join type {how} does not exist or is not implemented" 
        
                       ))); 
        
                   } 
        
               }; 
        
               let left_keys = left_on.iter().map(|s| s.as_ref()).collect::<Vec<&str>>(); 
        
               let right_keys = right_on.iter().map(|s| s.as_ref()).collect::<Vec<&str>>(); 
        
               let mut df = self.df.as_ref().clone().join( 
        
                   right.df.as_ref().clone(), 
        
                   join_type, 
        
                   &left_keys, 
        
                   &right_keys, 
        
                   None, 
        
               )?; 
        
               if drop_duplicate_keys { 
        
                   let mutual_keys = left_keys 
        
                       .iter() 
        
                       .zip(right_keys.iter()) 
        
                       .filter(|(l, r)| l == r) 
        
                       .map(|(key, _)| *key) 
        
                       .collect::<Vec<_>>(); 
        
                   let fields_to_drop = mutual_keys 
        
                       .iter() 
        
                       .map(|name| { 
        
                           df.logical_plan() 
        
                               .schema() 
        
                               .qualified_fields_with_unqualified_name(name) 
        
                       }) 
        
                       .filter(|r| r.len() == 2) 
        
                       .map(|r| r[1]) 
        
                       .collect::<Vec<_>>(); 
        
                   let expr: Vec<Expr> = df 
        
                       .logical_plan() 
        
                       .schema() 
        
                       .fields() 
        
                       .into_iter() 
        
                       .enumerate() 
        
                       .map(|(idx, _)| df.logical_plan().schema().qualified_field(idx)) 
        
                       .filter(|(qualifier, f)| !fields_to_drop.contains(&(*qualifier, f))) 
        
                       .map(|(qualifier, field)| Expr::Column(Column::from((qualifier, field)))) 
        
                       .collect(); 
        
                   df = df.select(expr)?; 
        
               } 
        
               Ok(Self::new(df))

The user guide also documents this default key-dropping behavior without noting any join-type exceptions.

datafusion-python/docs/source/user-guide/common-operations/joins.rst

Lines 109 to 136 in fcd7056

    
           It is common to join two DataFrames on a common column name. Starting in 
        
           version 51.0.0, ``datafusion-python``` will now drop duplicate column names by 
        
           default. This reduces problems with ambiguous column selection after joins. 
        
           You can disable this feature by setting the parameter ``drop_duplicate_keys`` 
        
           to ``False``. 
        
           .. ipython:: python 
        
               left = ctx.from_pydict( 
        
                   { 
        
                       "id": [1, 2, 3], 
        
                       "customer": ["Alice", "Bob", "Charlie"], 
        
                   } 
        
               ) 
        
               right = ctx.from_pylist([ 
        
                   {"id": 1, "name": "CityCabs"}, 
        
                   {"id": 2, "name": "MetroRide"}, 
        
                   {"id": 5, "name": "UrbanGo"}, 
        
               ]) 
        
               left.join(right, "id", how="inner") 
        
           In contrast to the above example, if we wish to get both columns: 
        
           .. ipython:: python 
        
               left.join(right, "id", how="inner", drop_duplicate_keys=False)

Given that, I agree with @renato’s concern: for full outer joins the two key columns are not equivalent, so dropping one of them can remove the only way to represent unmatched rows. Disallowing drop_duplicate_keys=True (or forcing it to False) for how="full" would avoid silent data loss and keep both key columns available for user-controlled coalescing or renaming afterward, aligning with SQL-style expectations.

@mesejo’s coalesce-based approach also addresses the correctness gap and matches behaviors in other libraries. If we keep drop_duplicate_keys=True as the default, applying coalesce for outer joins instead of dropping one side would preserve rows while still returning a single key column; the trade-off is slightly higher compute and less user control over per-column treatment. Either way, documenting the chosen semantics explicitly for full joins will help set user expectations.

fix: use coalesce instead of drop_duplicate_keys for join (#1318)

Allow dropping qualified columns (#19549)

Allow dropping qualified columns (apache#19549)

Navigation Menu

Repository navigation

Full join on dataframe with only index yields dropped rows #1305

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	fn join(
	&self,
	right: PyDataFrame,
	how: &str,
	left_on: Vec<PyBackedStr>,
	right_on: Vec<PyBackedStr>,
	drop_duplicate_keys: bool,
	) -> PyDataFusionResult<Self> {
	let join_type = match how {
	"inner" => JoinType::Inner,
	"left" => JoinType::Left,
	"right" => JoinType::Right,
	"full" => JoinType::Full,
	"semi" => JoinType::LeftSemi,
	"anti" => JoinType::LeftAnti,
	how => {
	return Err(PyDataFusionError::Common(format!(
	"The join type {how} does not exist or is not implemented"
	)));
	}
	};

	let left_keys = left_on.iter().map(\|s\| s.as_ref()).collect::<Vec<&str>>();
	let right_keys = right_on.iter().map(\|s\| s.as_ref()).collect::<Vec<&str>>();

	let mut df = self.df.as_ref().clone().join(
	right.df.as_ref().clone(),
	join_type,
	&left_keys,
	&right_keys,
	None,
	)?;

	if drop_duplicate_keys {
	let mutual_keys = left_keys
	.iter()
	.zip(right_keys.iter())
	.filter(\|(l, r)\| l == r)
	.map(\|(key, _)\| *key)
	.collect::<Vec<_>>();

	let fields_to_drop = mutual_keys
	.iter()
	.map(\|name\| {
	df.logical_plan()
	.schema()
	.qualified_fields_with_unqualified_name(name)
	})
	.filter(\|r\| r.len() == 2)
	.map(\|r\| r[1])
	.collect::<Vec<_>>();

	let expr: Vec<Expr> = df
	.logical_plan()
	.schema()
	.fields()
	.into_iter()
	.enumerate()
	.map(\|(idx, _)\| df.logical_plan().schema().qualified_field(idx))
	.filter(\|(qualifier, f)\| !fields_to_drop.contains(&(*qualifier, f)))
	.map(\|(qualifier, field)\| Expr::Column(Column::from((qualifier, field))))
	.collect();
	df = df.select(expr)?;
	}

	Ok(Self::new(df))

	It is common to join two DataFrames on a common column name. Starting in
	version 51.0.0, ``datafusion-python``` will now drop duplicate column names by
	default. This reduces problems with ambiguous column selection after joins.
	You can disable this feature by setting the parameter ``drop_duplicate_keys``
	to ``False``.

	.. ipython:: python

	left = ctx.from_pydict(
	{
	"id": [1, 2, 3],
	"customer": ["Alice", "Bob", "Charlie"],
	}
	)

	right = ctx.from_pylist([
	{"id": 1, "name": "CityCabs"},
	{"id": 2, "name": "MetroRide"},
	{"id": 5, "name": "UrbanGo"},
	])

	left.join(right, "id", how="inner")

	In contrast to the above example, if we wish to get both columns:

	.. ipython:: python

	left.join(right, "id", how="inner", drop_duplicate_keys=False)

Full join on dataframe with only index yields dropped rows #1305

Description

Activity

IshaGudewar commented on Dec 4, 2025

renato2099 commented on Dec 7, 2025

renato2099 commented on Dec 13, 2025

renato2099 commented on Dec 13, 2025

ntjohnson1 commented on Dec 13, 2025

renato2099 commented on Dec 14, 2025

mesejo commented on Dec 17, 2025

renato2099 commented on Dec 17, 2025

ntjohnson1 commented on Dec 29, 2025

ntjohnson1 commented on Dec 29, 2025

kosiew commented on Dec 31, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions