Upsert with None values fails on "Invalid literal value: None" #2426

Activity

1. kevinjqliu commented on Sep 4, 2025

   

   kevinjqliu

   on Sep 4, 2025

   Contributor

   More actions

   thanks for the report!

   i can reproduce it locally. the issue seems to be related to the foo column as the join_cols.
   We hit this branch

   iceberg-python/pyiceberg/table/upsert_util.py

   Lines 33 to 37 in 52d810e

   	def create_match_filter(df: pyarrow_table, join_cols: list[str]) -> BooleanExpression:
   	unique_keys = df.select(join_cols).group_by(join_cols).aggregate([])
   	if len(join_cols) == 1:
   	return In(join_cols[0], unique_keys[0].to_pylist())

   Seems like the In expression cannot handle None/Null values
   

   iceberg-python/pyiceberg/expressions/__init__.py

   Lines 678 to 682 in 52d810e

   	class In(SetPredicate[L]):
   	def __new__( # type: ignore # pylint: disable=W0221
   	cls, term: Union[str, UnboundTerm[Any]], literals: Union[Iterable[L], Iterable[Literal[L]]]
   	) -> BooleanExpression:
   	literals_set: Set[Literal[L]] = _to_literal_set(literals)
2. mdwint commented on Sep 4, 2025

   

   mdwint

   on Sep 4, 2025

   Author

   More actions

   I will try implementing a fix tomorrow, but I may come back with some questions since I don't know the codebase yet.

   I'm guessing IsNull would be the correct filter predicate. Does that sound right?
3. mdwint commented on Sep 4, 2025

   

   mdwint

   on Sep 4, 2025

   Author

   More actions

   I've made some progress fixing the filter expressions in main...mdwint:iceberg-python:fix/upsert-with-nulls

   The tests now confirm that the right expressions are produced. For example, in the last test case I can print this:

   Or(
     left=And(
       left=IsNull(term=Reference(name='foo')),
       right=EqualTo(term=Reference(name='bar'), literal=LongLiteral(1))
     ),
     right=And(
       left=EqualTo(term=Reference(name='foo'), literal=literal('lemon')),
       right=IsNull(term=Reference(name='bar'))
     )
   )
   

   However, that last test case fails to update the row {"foo": None, "bar": 1, ...} with join_cols=["foo", "bar"]:

   tests/table/test_upsert.py:741: in test_upsert_with_nulls
       assert upd.rows_updated == 1
   E   assert 0 == 1
   E    +  where 0 = UpsertResult(rows_updated=0, rows_inserted=1).rows_updated
   

   So I think the filter expressions are now correct, but the upsert logic still doesn't handle updates with null values correctly. Wild guess: maybe it has to do with the way null values are compared (null doesn't equal itself?). Any ideas?
4. mdwint commented on Sep 5, 2025

   

   mdwint

   on Sep 5, 2025

   Author

   More actions

   So I think the filter expressions are now correct, but the upsert logic still doesn't handle updates with null values correctly. Wild guess: maybe it has to do with the way null values are compared (null doesn't equal itself?).

   I've isolated the problem to step 3 in get_rows_to_update:

   iceberg-python/pyiceberg/table/upsert_util.py

   Lines 123 to 124 in 075a966

   	# Step 3: Perform an inner join to find which rows from source exist in target
   	matching_indices = source_index.join(target_index, keys=list(join_cols_set), join_type="inner")

   This join ignores None values. It returns no matches, but I want it to match bar: [[1]] foo: [[null]]:

   (Pdb) p source_index
   pyarrow.Table
   bar: int32
   foo: large_string
   __source_index: int64
   ----
   bar: [[1,null]]
   foo: [[null,"lemon"]]
   __source_index: [[0,1]]
   
   (Pdb) p target_index
   pyarrow.Table
   bar: int32
   foo: large_string
   __target_index: int64
   ----
   bar: [[1]]
   foo: [[null]]
   __target_index: [[0]]
   
   (Pdb) p matching_indices
   pyarrow.Table
   bar: int32
   foo: large_string
   __source_index: int64
   __target_index: int64
   ----
   bar: []
   foo: []
   __source_index: []
   __target_index: []
5. mdwint commented on Sep 5, 2025

   

   mdwint

   on Sep 5, 2025

   Author

   More actions

   Computing the inner join in Python solved the issue. I've now opened #2429 for review.
6. github-actions commented on Mar 18, 2026

   

   github-actionsBot

   on Mar 18, 2026 – with GitHub Actions

   More actions

   This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.
7. github-actions commented on Sep 14, 2026

   

   github-actionsBot

   on Sep 14, 2026 – with GitHub Actions

   More actions

   This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.
8. github-actions

   added

   stale

   on Sep 14, 2026
9. github-actions commented on Sep 28, 2026

   

   github-actionsBot

   on Sep 28, 2026 – with GitHub Actions

   More actions

   This issue has been closed because it has not received any activity in the last 14 days since being marked as 'stale'
10. github-actions

    closed this as not plannedon Sep 28, 2026