null_safe_equality_join.yaml

id: null_safe_equality_join
category: nulls_types
difficulty: hard
probes: >
  Matching NULL to NULL requires the null-safe equality operator (<=> /
  eqNullSafe). A plain == silently drops the NULL-keyed pair. This is the
  inverse of join_anti_null_key: same operator, opposite required behaviour,
  so a model cannot pattern-match its way through both.
tags: [null_semantics, eqNullSafe, join]

prompt: |
  Join `left_t` to `right_t` on the code column, treating NULL as a value that
  matches NULL. Return columns: code, lval, rval, for matched pairs only.

fixtures:
  - name: left_t
    schema: code STRING, lval INT
    rows:
      - ["a", 1]
      - ["b", 2]
      - [null, 3]
  - name: right_t
    schema: code STRING, rval INT
    rows:
      - ["a", 10]
      - ["c", 20]
      - [null, 30]

solution: |
  from pyspark.sql import functions as F

  def solve(spark, left_t, right_t):
      # eqNullSafe: NULL <=> NULL is true, so the (null, 3)/(null, 30) pair joins.
      cond = left_t["code"].eqNullSafe(right_t["code"])
      return (
          left_t.join(right_t, cond, "inner")
          .select(left_t["code"].alias("code"), "lval", "rval")
      )

compare:
  mode: rows